# Existing OSM Lead Adoption

Phase 1 established the adoption design. Phase 2A.5 now has a bounded,
report-only implementation under review on draft PR #7. No live adoption scan,
active mapping write, or candidate persistence has occurred.

## Verified Existing Signals

Verified from `Scraper Leads` schema and current OSM scraper code:

| Signal | Exists today | Source |
| --- | --- | --- |
| Noco record id | yes, `Id`/`id` | NocoDB |
| Source label | yes, `source`, current OSM payload sends `OSM` | `src/nocodb.js:416` |
| OSM type | yes, `osm_type` | `src/nocodb.js:422` |
| OSM id | yes, `osm_id` | `src/nocodb.js:423` |
| OSM URL | yes, `osm_url` | `src/nocodb.js:424` |
| Website | yes, `website` | scraper plus enrichment |
| Phone | yes, `phone` | scraper |
| Name/address/location | yes | scraper |
| Raw OSM tags | yes, `raw_tags_json` | OSM scraper |
| Coordinates | not present in current Noco schema | local SQLite has `lat`/`lon`, Noco payload does not include them |

The future service cannot rely on coordinates from NocoDB unless they are inferred from sidecar state or raw source data. Do not add Noco columns in this replacement.

## Verified Current Duplicate Behavior

Current local scraper uniqueness is `UNIQUE(job_id, osm_type, osm_id)` in `src/store.js:178-204`, so an OSM object can exist once per scraper job locally.

Current Noco duplicate detection in `src/nocodb.js:453-718` looks up direct IDs/URLs first, then website fallback, then phone fallback. The direct `osm_id` lookup does not include `osm_type`, so node `123` and way `123` are not distinguishable by that one barrier. `osm_url` is safer because it includes type.

The production Noco table has no DB unique index except primary key on `id`.

## Adoption Outcome Rules

Only accepted relationships may become active mappings. `exact`, `high_confidence`, and approved `remapped` outcomes may be written to `noco_lead_mapping`. `ambiguous`, `unmatched`, rejected, and collision results must remain review/reconciliation candidates only; they must not be stored as active mappings and must not trigger deletion or replacement.

| Rank | Rule | Outcome | Automatic? |
| --- | --- | --- | --- |
| 1 | Exact `osm_type` and `osm_id` on `source=OSM`, or exact normalized `osm_url` | `exact` | yes |
| 2 | OSM URL parse gives same type/id even if `osm_type`/`osm_id` columns are blank | `exact` | yes |
| 3 | Exact normalized website domain plus exact normalized phone, same country, and compatible name/category | `high_confidence` | yes, after collision check |
| 4 | Exact normalized website domain plus exact name and exact address | `high_confidence` | yes, after collision check |
| 5 | Exact normalized phone plus exact name and exact address | `high_confidence` | yes, after collision check |
| 6 | Name plus exact address, same country/city/category | `high_confidence` if unique | yes only if unique |
| 7 | Name plus geographic proximity | `ambiguous` until sidecar has coordinates or source tags prove same business | no |
| 8 | Name, city, category, and address similarity | `ambiguous` | no |
| 9 | No reliable signal | `unmatched` | no |

Collision check means a candidate OSM object must not already map to another protected Noco row, and a candidate Noco row must not already map to a different live OSM object unless this is an approved source-remapping case.

## Phase 2A.5 Report-Only Implementation

The implementation normalizes detached scalar row projections and compares them
with region-scoped source identities. Exact OSM identity is type-aware, so a
node, way, and relation with the same numeric id remain distinct. Accepted,
ambiguous, unmatched, and collision results are immutable report evidence only;
none of them can execute a write or become an active mapping in this phase.

Existing active mappings are supplied as bounded read evidence and are checked
in both directions. A Noco row already associated with another source object,
or a source object already associated with another Noco row, becomes a
collision. A single Noco row matching multiple source objects and a source
object with multiple accepted Noco matches also fail closed as collisions.

Every inspected row receives explicit workflow-protection evidence. Missing
source-baseline proof, non-OSM identity fields, unknown or non-OSM source labels,
and populated downstream workflow fields are preserved as protection reasons.
The deterministic report hash includes those reasons and the read audit, but no
connection secret or mutable write instruction.

## Sidecar Mapping Proposal

Do not add NocoDB columns. Store adoption state in the new PostgreSQL sidecar:

```sql
osm_source_object (
  osm_type text not null,
  osm_id bigint not null,
  current_version bigint,
  source_payload_json jsonb not null default '{}'::jsonb,
  source_payload_hash text,
  tags_hash text,
  geometry_hash text,
  primary key (osm_type, osm_id)
);
```

`osm_source_object` represents stable OSM identity and the latest known source
payload facts. It does not authorize missing, stale, remap, or deletion
decisions. Those lifecycle decisions are authoritative only in the
region-scoped `osm_source_presence` model defined in the stale-lead safety
policy. Any global payload timestamps retained as operational summaries are
derived and cannot independently authorize a lifecycle transition.

Source presence is recorded independently for each configured region, so an
object can be present in one region while another region is incomplete or
uncertain. Profile eligibility is recorded independently per profile and
profile version; it is not part of the source object identity or presence
state.

```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
  )
);
```

An object that remains present but no longer matches a profile becomes
`out_of_scope` for that profile version. It does not increment any source
missing counter. Retiring a profile changes eligibility only and does not
imply that its leads disappeared from OSM. Previously tracked objects must
still be inspected for source presence even when no longer eligible for the
current profile.

```sql
noco_lead_mapping (
  id bigserial primary key,
  noco_table_id text not null,
  noco_record_id bigint not null,
  osm_type text not null,
  osm_id bigint not null,
  adoption_outcome text not null check (adoption_outcome in ('exact','high_confidence','remapped')),
  adoption_rule text not null,
  confidence numeric,
  evidence_json jsonb not null default '{}'::jsonb,
  active boolean not null default true,
  first_mapped_at timestamptz not null,
  last_confirmed_at timestamptz not null,
  superseded_by_mapping_id bigint references noco_lead_mapping(id)
);
```

The mapping table remains an identity/adoption relationship. It must not be
used as a substitute for region-scoped source presence or profile eligibility.

```sql
business_identity (
  id bigserial primary key,
  normalized_name text,
  normalized_domain text,
  normalized_phone text,
  normalized_address text,
  country text,
  city text,
  geohash text,
  identity_hash text not null unique
);
```

The active mapping constraints remain:

```sql
create unique index noco_lead_mapping_one_active_noco_row
on noco_lead_mapping (noco_table_id, noco_record_id)
where active = true;

create unique index noco_lead_mapping_one_active_source_object
on noco_lead_mapping (osm_type, osm_id)
where active = true;
```

The active source-object uniqueness above is identity uniqueness, not a
presence claim; regional presence remains separately keyed by
`(region_id, osm_type, osm_id)`.

Historical inactive and superseded mappings are immutable and unlimited. The partial unique indexes constrain only the active view of the world.

All automatic adoptions must write an immutable evidence payload including matched fields, old Noco values, normalized comparison values, run id, and code version.

## Reconciliation Candidate Store

Candidate outcomes are stored separately from accepted mappings. This table supports nullable source references because an unmatched Noco record may have no OSM identity.

```sql
adoption_candidate (
  id bigserial primary key,
  noco_table_id text not null,
  noco_record_id bigint,
  candidate_osm_type text,
  candidate_osm_id bigint,
  result_status text not null check (result_status in (
    'ambiguous',
    'unmatched',
    'rejected',
    'collision',
    'needs_review',
    'accepted'
  )),
  proposed_outcome text check (proposed_outcome in ('exact','high_confidence','remapped')),
  confidence numeric,
  evidence_json jsonb not null default '{}'::jsonb,
  collision_details_json jsonb not null default '{}'::jsonb,
  review_outcome text,
  reviewed_by text,
  reviewed_at timestamptz,
  run_id text not null,
  created_at timestamptz not null
);
```

An accepted review result creates or supersedes a row in `noco_lead_mapping`; the candidate row remains immutable evidence. Rejected, ambiguous, and unmatched candidates remain reportable but do not participate in active uniqueness constraints.

## Source Baseline And Provenance Sidecar

Adoption must establish a source-owned baseline before any future refresh or stale deletion can treat Noco values as source-owned. The baseline is separate from the accepted mapping because it records field-level provenance evidence for the Noco row as adopted.

```sql
noco_source_baseline (
  id bigserial primary key,
  noco_table_id text not null,
  noco_record_id bigint not null,
  mapping_id bigint not null references noco_lead_mapping(id),
  source_snapshot_id text references source_snapshot(id),
  previous_baseline_id bigint references noco_source_baseline(id),
  source_payload_json jsonb not null,
  source_payload_hash text not null,
  raw_tags_json_hash text,
  source_owned_field_hashes jsonb not null,
  source_owned_field_values jsonb,
  valid_from timestamptz not null,
  superseded_at timestamptz,
  code_version text not null,
  rule_version text not null,
  provenance_evidence_json jsonb not null
);

create unique index noco_source_baseline_one_current_mapping
on noco_source_baseline (mapping_id)
where superseded_at is null;
```

Baseline rows are append-only and immutable after creation except for setting
`superseded_at`. A validated source refresh creates a new row, points it to the
previous current row with `previous_baseline_id`, and supersedes the previous
row transactionally. Historical baselines are never overwritten. A failed,
partial, or uncertain refresh does not change the current baseline. Legacy
rows without a trustworthy initial baseline remain protected from automatic
stale deletion.

The service must distinguish:

- `unchanged_source_owned`: current Noco value equals the adopted source baseline or the corresponding value in adopted `raw_tags_json`.
- `proven_source_refresh`: value changed only because a validated source snapshot refreshed the same accepted mapping and updated the baseline evidence.
- `enrichment_owned`: enrichment, review, campaign, or target-table evidence wrote or selected the value.
- `manual_owned`: Noco metadata, review fields, or explicit audit evidence indicates a human/manual change.
- `unknown`: required evidence is absent, unreadable, inconsistent, or predates a complete baseline.

Legacy rows without complete baseline evidence fail closed: they may be reported for review or manual adoption, but they are protected from automatic stale deletion.

## Source Remapping

Example:

```text
node:111 disappears
way:999 appears
same name, domain, phone, address, and location
```

Required behavior:

1. Do not delete or recreate the Noco row.
2. For each affected region, mark `node:111` as a `remap_candidate` in
   `osm_source_presence`; do not infer global deletion from one region.
3. Create `way:999` source object.
4. Add a new active mapping from the existing Noco row to `way:999` with `adoption_outcome='remapped'`.
5. Supersede the old mapping, preserving historical evidence.
6. Do not overwrite protected Noco process fields.
7. Refresh only source-owned fields that pass the Noco compatibility policy.

If location is unavailable in Noco and sidecar has no prior geometry, the remap is ambiguous unless exact domain/phone/name/address are all present and unique.

## Required Tests

- Exact OSM URL adoption with blank `osm_type`/`osm_id`.
- Same numeric OSM id across node/way/relation does not collide.
- Website-domain adoption refuses multi-row collisions.
- Phone adoption requires name/address corroboration.
- Ambiguous fuzzy matches are reported but not adopted.
- Remapping preserves Noco `Id` and protected fields.
- Sidecar active uniqueness prevents one OSM object mapping to two active Noco rows.
- Partial unique indexes allow unlimited inactive historical mappings.
- Ambiguous and unmatched candidates are stored outside `noco_lead_mapping`.
- Legacy rows without complete baseline evidence are protected.