# Replacement Architecture Plan

Phase 1 status: proposed future design only. Do not implement until Phase 2 approval.

## Verified Current Context To Preserve

The current live service is `/root/osm-country-scraper`, a Node/Express scraper started by `index.js:9-24`. It resolves countries with Nominatim, queries Overpass, stores runtime state in SQLite, and syncs to NocoDB. Production service inventory shows live `osm-country-scraper` in Coolify. The active OSM job on 2026-07-04 is United States `concept:broad:all_local_businesses`, running since 2026-06-20, with about 2.8M local leads and incremental Noco writes.

The replacement must coexist until cutover and must not require Windmill.

## Proposed Module Boundaries

| Module | Responsibility |
| --- | --- |
| `geofabrik_catalog` | Configured regional extract URLs, expected region metadata, ETag/Last-Modified tracking. |
| `download_cache` | Atomic PBF download, checksum/size validation, retention. |
| `osmium_filter` | Optional prefilter command construction and audit of selected keys/categories. |
| `pbf_parser` | Node/way/relation parsing, relation center/geometry handling, source version capture. |
| `category_rules` | Versioned targeting profiles, rule states, OSM tag-to-category mapping, dry-run impact reports. |
| `normalization` | Website/domain, phone, address, country/state, name, lifecycle normalization. |
| `business_deduper` | Node/way/relation same-business clustering and recreated-object detection. |
| `noco_contract` | Exact payload mapping to current `Scraper Leads` schema. |
| `NocoReadRepository` | Restricted read-only PostgreSQL or fixture/API read adapter for bulk scans and preconditions. |
| `NocoWriteRepository` | NocoDB API-only create/update/delete adapter; never writes directly to Noco PostgreSQL. |
| `adoption` | Existing-row scan, match ranking, sidecar mapping writes. |
| `workflow_protection` | Shared protected-field and relationship policy. |
| `stale_policy` | Shared `is_safe_to_delete_noco_lead()` implementation. |
| `sidecar_store` | PostgreSQL schema for source objects, mappings, snapshots, tombstones, jobs. |
| `reconciliation_engine` | Single engine used by scheduler, API, dry-run, and future MCP tools. |
| `scheduler` | Internal monthly refresh, locks, recovery, retry. |
| `audit_reporting` | Dry-run reports, diffs, contract violations, approval bundles. |
| `mcp_interface` | Future authenticated tools that call reconciliation engine only. |

All scheduled jobs, API actions, and MCP tools must call the same reconciliation engine. No duplicate business logic in MCP handlers.

## Business Profile Configuration

The future service supports two initial production profile names:

- `luxeillum`
- `saas_redesign`

The engine may be built before final rules are approved, but production insertions must remain disabled for a profile until its configuration is explicitly approved. Each profile definition includes:

- versioned profile definitions
- versioned category rules
- region-specific rule overrides
- include and exclude rules
- Noco category and subcategory mappings
- rule provenance and reviewer/approval metadata
- `draft`, `active`, and `retired` states
- dry-run impact reporting before activation

Add a third profile, `legacy_parity`, derived from the verified current selector definitions. `legacy_parity` is for shadow comparison and regression testing only. It must not automatically become a production targeting profile.

The broad current selector compaction behavior that retrieves entire keys through `.+` must not be reproduced as the new production category strategy. If a broad key is needed, it must be expressed as an explicit reviewed rule set with impact reporting.

Changing or disabling a category rule changes profile scope only. It must not classify existing leads as stale or deleted in OSM. Rows that still exist in source but no longer match profile rules become `out_of_scope` and require a separate cleanup dry-run and approval policy.

## Monthly Reconciliation Flow

1. Acquire sidecar advisory lock for region/profile.
2. Download configured Geofabrik `.osm.pbf` extract into a content-addressed cache.
3. Validate size/checksum/metadata and record extract timestamp.
4. Parse nodes, ways, and relations for configured rule keys.
5. Normalize fields and classify by profile.
6. Deduplicate business-level entities across node/way/relation representations.
7. Compare observed and previously tracked identities with the region-scoped
   source-presence table, including identities no longer matching current rules.
8. For every active profile and profile version, evaluate eligibility separately
   and write profile-version-scoped eligibility evidence.
9. Run adoption scan for existing Noco `source=OSM` rows not already mapped.
10. Produce dry-run actions: insert, refresh, protect, remap, tombstone, delete-candidate.
11. Run final Noco pre-insert lookups for exact OSM URL/type/id, domain, phone, name/address.
12. Insert only new leads whose profile eligibility is `active` for the approved profile version.
13. Refresh only source-owned safe fields.
14. Record region-scoped source presence as `present`, `missing`,
    `stale_confirmed`, `remap_candidate`, or `deleted_in_osm`. Failed, partial,
    or uncertain snapshots must not advance missing counters; only complete,
    successful snapshots may do so.
15. Keep deletion disabled by default; emit delete-candidates only after the
    source-presence two-snapshot/60-day rule and complete workflow checks pass.
16. Write tombstones only for approved deletes.
17. Emit immutable audit report covering both presence and eligibility decisions.

Source presence and profile eligibility are separate records in this flow. No
source-presence column contains `active`, `out_of_scope`, `excluded`, or
`pending_review`; no eligibility column contains `missing`, `stale_confirmed`,
`remap_candidate`, or `deleted_in_osm`.

### Regional presence and overlapping extracts

Presence is keyed by `(region_id, osm_type, osm_id)`. Absence from one region
must not globally mark an object missing when another configured region confirms
it present. Region boundary changes do not independently prove source deletion.
Global deletion eligibility requires complete evidence across every active region
expected to contain the tracked object, or an explicitly resolved
canonical-region assignment. Unresolved overlap or boundary ambiguity fails
closed.

The authoritative source-presence contract is equivalent to:

```sql
osm_source_presence (
  region_id text not null,
  osm_type text not null,
  osm_id bigint not null,
  source_presence_state text not null check (source_presence_state in (
    'present',
    'missing',
    'stale_confirmed',
    'remap_candidate',
    'deleted_in_osm'
  )),
  first_seen_at timestamptz,
  last_seen_at timestamptz,
  first_missing_at timestamptz,
  consecutive_missing_snapshots integer not null default 0,
  last_seen_snapshot_id text references source_snapshot(id),
  first_missing_snapshot_id text references source_snapshot(id),
  latest_snapshot_id text references source_snapshot(id),
  extract_metadata_json jsonb not null default '{}'::jsonb,
  completeness_status text not null,
  primary key (region_id, osm_type, osm_id)
);
```

The source-presence state is the only lifecycle state in this table. Profile
eligibility is stored separately in `osm_profile_eligibility` below.

## Future MCP Control Boundaries

MCP must be authenticated and authorization-scoped. Tools should be read/dry-run by default.

Allowed future MCP tools:

- Search sidecar/Noco mapped leads.
- List profiles, categories, and rule versions.
- Propose category-rule changes.
- Request scoped dry-run refresh.
- Preview adoption, insert, refresh, stale, and delete decisions.
- Initiate approved actions only from an existing dry-run report id.

MCP must not:

- Hold production credentials in config exposed to clients.
- Bypass reconciliation engine.
- Delete rows without sidecar proof and policy approval.
- Modify Noco schema.
- Disable old scraper schedules or services.
- Run unscoped full-refresh actions without approval.
- Expose unrestricted SQL or shell tools.

## Cutover And Coexistence Plan

Shadow mode and read-only adoption can run while the old scraper remains active. The sidecar advisory lock cannot protect NocoDB from the existing scraper because the existing scraper does not participate in that lock. Therefore, the new service must not perform production Noco writes while the old scraper can still synchronize.

Before the first production write:

1. Disable or stop the old scraper's Noco synchronization/service after explicit approval.
2. Confirm no active old scraper execution is still writing or queued to write.
3. Record the cutover timestamp and final old-scraper state.
4. Run a final adoption and duplicate scan.
5. Activate the new writer lease.

The old scraper may remain deployed for rollback, but it must not be able to write concurrently. Re-enabling the old scraper requires disabling the new writer first. The current long-running United States job must not be interrupted by documentation or read-only design work.

## Duplicate Prevention Barriers

- Sidecar stable identity unique `(osm_type, osm_id)` for source objects.
- Region-scoped source presence unique `(region_id, osm_type, osm_id)`.
- Sidecar unique active mapping per Noco row and per OSM object.
- Normalize and compare `osm_url`, not only `osm_id`.
- Domain-level and phone-level indexes.
- Business identity hash from name/domain/phone/address/country/location.
- Node/way/relation clustering before Noco insert.
- Recreated-object detection via same domain/phone/name/address/location.
- Final Noco lookup immediately before POST.
- Idempotency key per intended action and payload hash.
- Transactional sidecar job/action table with states `planned`, `claimed`, `applied`, `failed`, `superseded`.

## Required Automated Tests

- PBF fixture with node, way, relation versions of same business.
- Category rule profile separation for both businesses.
- Noco contract snapshot test for exact field names and blank/null payloads.
- Final pre-insert lookup blocks old scraper rows.
- Concurrent insert attempts produce one Noco row and one sidecar action.
- Retry after partial Noco failure is idempotent.
- Protected fields are never overwritten.
- Safe-delete policy fails closed.
- Tombstones block recreation.
- Dry-run and MCP preview use the same reconciliation decisions.
- A present source object becoming `out_of_scope` does not increment missing counters.
- The same OSM object can have different eligibility states for different profiles.
- Absence from one overlapping region does not prove global absence.
- Region-boundary uncertainty fails closed.
- A failed, partial, or uncertain snapshot does not increment missing counters.
- A refresh creates a new baseline and supersedes the previous baseline.
- Only one nonsuperseded baseline exists per mapping.
- Historical baselines remain available.
- A failed refresh leaves the current baseline unchanged.

## Phased Implementation

### Phase 2A - Repository Foundation And Read-Only Shadow Mode

- Create the separate replacement repository.
- Create PostgreSQL sidecar schema and migrations.
- Implement Geofabrik download and validation.
- Implement PBF parsing.
- Implement versioned category profiles.
- Implement `NocoReadRepository`.
- Implement adoption scanner.
- Produce reconciliation reports.
- Perform no Noco writes.
- Perform no MCP writes.

### Phase 2B - Insert-Only Controlled Writer

- Implement the exact Noco contract adapter.
- Run final duplicate checks immediately before writes.
- Use NocoDB API writes only.
- Limit production writes to an approved region/profile.
- Adopt existing rows instead of recreating them.
- Do not perform stale deletion.

### Phase 2C - Safe Refresh And Stale Lifecycle

- Implement provenance-aware source refresh.
- Track missing/stale counters.
- Implement source remapping.
- Write tombstone candidates/evidence.
- Keep deletion disabled by default.

### Phase 2D - Approved Stale Deletion

- Enforce the two-snapshot/60-day rule.
- Run complete workflow checks.
- Require explicit deletion approval.
- Preserve audit and rollback evidence.

### Phase 2E - MCP Integration

- Add authenticated read/search tools.
- Support draft category proposals.
- Support dry-run requests.
- Execute approved actions through the same reconciliation engine.
- Expose no unrestricted SQL or shell tools.

## Phase 2A Implementation Clarifications

### Separate Source Presence From Profile Eligibility

Source presence states:

- `present`
- `missing`
- `stale_confirmed`
- `remap_candidate`
- `deleted_in_osm`

Profile eligibility states:

- `active`
- `out_of_scope`
- `excluded`
- `pending_review`

One OSM object may be active for one profile and out of scope for another.

Eligibility is evaluated per profile and profile version. A rule change affects
eligibility only; `out_of_scope` must never increment source missing counters.
Retiring a profile does not imply that its leads disappeared from OSM.
Previously tracked objects must still be inspected for source presence even when
they are no longer eligible.

```sql
osm_profile_eligibility (
  region_id text not null,
  osm_type text not null,
  osm_id bigint not null,
  profile_id text not null,
  profile_version text not null,
  eligibility_state text not null check (eligibility_state in (
    'active',
    'out_of_scope',
    'excluded',
    'pending_review'
  )),
  evaluated_at timestamptz not null,
  rule_evidence_json jsonb not null default '{}'::jsonb,
  primary key (
    region_id,
    osm_type,
    osm_id,
    profile_id,
    profile_version
  )
);
```

### Track Previously Known Objects

Monthly processing must inspect both:

- Objects matching current profile/category rules.
- Previously tracked OSM identities in the processed region.

An object that still exists but no longer matches a rule must become
`out_of_scope`, not `missing`.

### Region-Scoped Presence

Presence state and missing counters must be scoped using a model equivalent to:

```text
(region_id, osm_type, osm_id)
```

Overlapping extracts and boundary changes must not create false staleness.

Absence from one region cannot globally mark an object missing if another
configured region confirms it present. Global deletion eligibility requires
complete evidence across every active region expected to contain the tracked
object, or an explicitly resolved canonical-region assignment. Unresolved
overlap or boundary ambiguity fails closed.

### Append-Only Provenance Baselines

A validated refresh creates a new baseline version rather than overwriting the
previous baseline.

Include concepts equivalent to:

- `valid_from`
- `superseded_at`
- `source_snapshot_id`
- `previous_baseline_id`

Only one current baseline may exist per accepted mapping, enforced by a partial
unique index.

Baseline rows are immutable after creation except for setting `superseded_at`.
A validated refresh creates a new baseline row and transactionally supersedes
the previous current row. Historical baselines are never overwritten. A failed
refresh leaves the current baseline unchanged, and legacy rows without a
trustworthy initial baseline remain protected.