# Stale Lead Safety Policy

Phase 1 status: policy design only. No rows were deleted or modified.

## Verified Workflow Protection Signals

Verified fields in `Scraper Leads` and downstream code:

| Field or relationship | Evidence | Protection meaning |
| --- | --- | --- |
| `website` | Website enrichment writes it in `lead-website-enricher-batch.py:316-322`; scraper can also insert it | Contact/enrichment asset |
| `Website_Found_By_Scraper` | `lead-website-enricher-batch.py:316-320` | Website enrichment accepted a match |
| `website_last_checked_at` | `lead-website-enricher-batch.py:277-293` | Website workflow touched row |
| `website_enrichment_status`, `website_enrichment_reason` | schema and live script description | Website queue/result state |
| `Website_Review_*` | schema descriptions | Manual/agent review workflow |
| `Enriched_Luxeillum` | schema | Hospitality email enrichment state |
| `Enriched_Saas` | SaaS enrichment marks source rows in `saas-enrichment-live.py:1501-1516` | SaaS email enrichment state |
| `Email_Found` | `saas-enrichment-live.py:1501-1516` | Email discovered/verified |
| `Blocked` | website/enrichment selectors and `saas-enrichment-live.py:1501-1516` | Excluded/blocked source |
| `Blocked_SaaS` | schema | SaaS-specific block state |
| `Offer_URL`, `Prospect_URL` | schema | Generated downstream assets |
| Target SaaS rows | `saas-enrichment-live.py:1468-1516` writes to `saas_leads` and marks source row | External dependent row |
| ReachInbox import | `saas-export-live.py:465-518` marks target rows imported | Campaign/list membership downstream of source |

Unknown ownership is protected. Linked-record relationships were not found in the `Scraper Leads` schema metadata, but downstream references may exist in external systems by copied source ids, emails, websites, or campaign imports.

## Central Decision Contract

```python
def is_safe_to_delete_noco_lead(record, relationships):
    """
    returns one of:
      {"decision": "safe_to_delete", "reasons": [...]}
      {"decision": "protected", "reasons": [...]}
      {"decision": "unknown", "reasons": [...]}
    unknown must be treated by callers as protected.
    """
```

The implementation must fail closed. Any unreadable field, failed relationship lookup, schema drift, or unsupported source returns `unknown`.

## Safe Delete Decision Table

A stale OSM lead can be deleted automatically only if all checks pass. The old OSM scraper can itself insert `website`, `phone`, `email`, `address`, location fields, and category fields. A nonblank source-owned value is not automatic proof of downstream processing, and a blank value is not automatic proof of safety. Protection depends on provenance and workflow evidence.

A field may be considered source-owned only when its current value matches the original adopted OSM/source baseline or the corresponding value in the adopted `raw_tags_json`, no enrichment/review/manual/campaign/downstream marker indicates another writer took ownership, and the sidecar contains complete baseline evidence. A field is protected when enrichment or review wrote or selected it, its value differs from baseline without a proven source refresh, provenance is unknown, associated workflow markers indicate downstream processing, or required evidence cannot be read or verified.

| Check | Safe condition | Protected/unknown condition |
| --- | --- | --- |
| Source identity | `source='OSM'` and active accepted sidecar mapping exists with complete baseline evidence | missing/other source/ambiguous mapping/candidate-only record |
| Source staleness | `stale_confirmed` from two consecutive successful monthly snapshots, at least 60 days since last seen | one missing snapshot, failed/partial/unvalidated import, incomplete extract, unresolved remap candidate, or boundary/completeness uncertainty |
| Noco row state | row still exists and `__nc_deleted` false | missing, already deleted, or API inconsistency |
| Source-owned values | `website`, `phone`, `email`, `address`, location, category, name, status, and source ids match baseline/source refresh evidence or adopted `raw_tags_json` | value differs from baseline without proven source refresh, provenance unknown, or baseline cannot be read |
| Website workflow ownership | no website workflow marker and no selected/reviewed website ownership | `Website_Found_By_Scraper`, `website_last_checked_at`, status/reason, review candidates/summary/status/selected URL, or lookup failure |
| Email/enrichment ownership | enrichment flags false/null and no target-table or source-row enrichment evidence | `Enriched_Luxeillum`, `Enriched_Saas`, `Email_Found`, or unknown flag true |
| Block/workflow flags | false/null and verified unclaimed | `Blocked`, `Blocked_SaaS`, workflow lock, claimed/review status, or lookup failure |
| Generated assets | no generated downstream assets | `Offer_URL`, `Prospect_URL`, or equivalent target-table asset exists |
| Manual/system edits | current values equal source baseline/proven refresh or explicit source-owned audit | `updated_at`/`updated_by`, row meta, review state, or external audit indicates human/other writer; if ambiguous, protected |
| Downstream target rows | no matching source id/domain/email/website in managed target tables | any match or lookup failure |
| Campaign/list membership | none found | any import/campaign evidence or lookup failure |
| Workflow locks | none | any lock/claimed/review status unknown |

Because Noco lacks coordinates and a source-owned "last touched by scraper" field, the deletion policy must be intentionally strict.

## Untouched Proof

For automatic deletion, a row is "untouched" only when:

- It is source `OSM`.
- It has an active accepted sidecar mapping to a `stale_confirmed` OSM source.
- It has complete source-baseline evidence for all source-owned fields needed by the decision.
- It has no enrichment-owned, review-owned, manual-owned, campaign-owned, generated-asset, target-table relation, notes, row meta, or non-source update evidence.
- Its source-owned fields equal the adopted baseline, the corresponding value in adopted `raw_tags_json`, or a proven source-refresh payload recorded by sidecar hashes.

Rows produced by the old scraper before sidecar adoption must not be deleted until a baseline snapshot has recorded the inserted/source-owned values.

## Separate Source Presence And Profile Eligibility Lifecycles

Profile membership and source existence are separate dimensions. A source
object may be present while it is active for `luxeillum` and out of scope for
`saas_redesign`. Changing category rules can move a row out of scope, but it
cannot prove the OSM object disappeared.

### Source presence lifecycle

Source presence is region scoped and authoritative for missing, stale, remap,
and deletion decisions.

| State | Meaning |
| --- | --- |
| `present` | Object is confirmed in the complete regional source snapshot |
| `missing` | Object is absent from one complete relevant snapshot |
| `stale_confirmed` | Required complete snapshots and minimum elapsed time have passed |
| `remap_candidate` | Old identity is absent and a possible replacement exists |
| `deleted_in_osm` | Strong source evidence indicates deletion |

### Profile eligibility lifecycle

Eligibility is evaluated independently for each profile and profile version.

| State | Meaning |
| --- | --- |
| `active` | Matches an approved profile version |
| `out_of_scope` | Exists in OSM but no longer matches the profile |
| `excluded` | Explicitly rejected by an exclusion rule |
| `pending_review` | Eligibility is ambiguous or requires approval |

Automatic deletion consideration depends on `source presence =
stale_confirmed`, never on profile eligibility. An `out_of_scope`, `excluded`,
or `pending_review` eligibility result must not advance a source missing
counter.

Automatic OSM staleness requires all of the following:

- The relevant regional Geofabrik extract was completely downloaded and validated.
- Parsing and reconciliation completed successfully.
- The object was absent from two consecutive successful monthly source snapshots for the applicable region set.
- At least 60 days elapsed since the object was last seen.
- No unresolved source-remapping candidate exists.
- No extract-boundary or source-completeness uncertainty exists.

A failed, partial, interrupted, unvalidated, or otherwise uncertain snapshot
must not advance missing counts. One missing monthly snapshot can only produce
`missing`, never `stale_confirmed`.

### Overlapping regions and boundary changes

- Absence from one configured region must not globally mark an object missing if
  another configured region still confirms it present.
- A region boundary change must 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.
- Any unresolved overlap, region-boundary ambiguity, or uncertain expected-region
  assignment fails closed and blocks stale or deletion decisions.

For each `(region_id, osm_type, osm_id)`, the sidecar must store:

- `last_seen_at`
- `first_missing_at`
- `consecutive_missing_snapshots`
- source snapshot ids
- extract metadata
- completeness status

`out_of_scope` rows must not be automatically deleted from NocoDB merely because targeting configuration changed. Any cleanup based on profile changes requires a separate dry-run and explicit approval policy.

```sql
source_snapshot (
  id text primary key,
  region_id text not null,
  source_url text not null,
  downloaded_at timestamptz not null,
  validated_at timestamptz,
  parser_completed_at timestamptz,
  reconciliation_completed_at timestamptz,
  completeness_status text not null check (completeness_status in ('complete','partial','failed','unknown')),
  extract_metadata_json jsonb not null
);

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

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

`osm_source_presence` is the authoritative lifecycle store. The object
identity table, if present, stores stable OSM identity and payload facts only;
global lifecycle timestamps or deletion fields are derived summaries and
cannot independently authorize source decisions. `osm_profile_eligibility`
must not be used to infer source absence.

## Tombstone Model

```sql
osm_lead_tombstone (
  id bigserial primary key,
  noco_table_id text not null,
  previous_noco_record_id bigint not null,
  deleted_at timestamptz not null,
  deleted_by_service_run_id text not null,
  deletion_reason text not null,
  prior_osm_identities jsonb not null,
  normalized_business_identity jsonb not null,
  last_known_name text,
  last_known_domain text,
  last_known_phone text,
  last_known_address text,
  last_known_lat double precision,
  last_known_lon double precision,
  last_known_country text,
  source_payload_hash text not null,
  safety_evidence_json jsonb not null,
  superseded_at timestamptz,
  superseded_reason text
);
```

Tombstone superseding is allowed only when a future OSM object is proven to be the same live business and the intended action is to preserve/adopt an existing protected Noco row or create a new row after explicit approval. A tombstone must prevent automatic recreation from the same OSM identity or same normalized business identity.

## Required Tests

- `unknown` returned on missing schema field.
- Rows with any website workflow field are protected.
- Rows with enrichment flags are protected.
- Rows with offer/prospect URLs are protected.
- Exact untouched stale OSM row is safe only with complete sidecar evidence.
- One missing monthly snapshot produces `missing`, not deletion.
- A present source object can be `out_of_scope` for one profile without changing source presence.
- The same source 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 validated 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.
- Relationship lookup failure returns unknown/protected.
- Tombstoned source is not recreated on next refresh.
- Remap candidate prevents deletion and creates sidecar remap proposal.