# Phase 1 Findings

Phase 1 scope only. No implementation, deployment, Windmill changes, MCP creation, production credentials, Noco schema changes, or Noco lead-table writes were performed.

## Repositories, Scripts, Modules, Tables, And Workflows Inspected

Repositories and local paths:

- `/root/osm-country-scraper`: primary OSM scraper repo.
- `/root/gmaps-country-scraper`, `/root/foursquare-country-scraper`: compared shared Noco sync patterns.
- `/root/f/website-enrichment/lead-website-enricher-batch.py`: local Windmill mirror for website enrichment.
- `/root/.tmp-codex/saas-enrichment-live.py`: local live-export copy used to identify downstream source-row marks.
- `/root/.tmp-codex/saas-export-live.py`: local live-export copy used to identify campaign/import marks.
- `/root/mcp-shared/osm-source-cleanup`, `/root/mcp-shared/osm-country-scraper-src-config.js`: inspected for OSM-related scratch material; not current production path.

OSM scraper modules inspected:

- `index.js`
- `src/config.js`
- `src/server.js`
- `src/worker.js`
- `src/store.js`
- `src/osm.js`
- `src/nocodb.js`
- `src/taxonomy.js`
- `src/selectors.js`
- `src/keywords.js`
- `src/concept-selectors.js`
- `src/exporters.js`
- `README.md`
- `CONTEXT.md`

Tables inspected:

- NocoDB base `pc0fmz2i62ewg6n` / `Luxeillum Leads`.
- NocoDB table `mswfxq541pe6khe` / `Scraper Leads`.
- Physical table `pc0fmz2i62ewg6n."Scraper Leads"`.
- Related downstream tables identified by code: `saas_leads`, `ReachInbox Leads Hospitality`.

Live workflow/schedule inventory inspected with `wmill schedule list` and `wmill script list`:

- Website enrichment batch/continuous/review schedules.
- Hospitality and SaaS monthly enrichment/import/export scripts.
- ReachInbox reply/follow-up/blocklist scripts.
- Category review script.

## Verified Current OSM Retrieval Path

The scraper resolves country geometry through Nominatim in `src/osm.js:197-238`, using `country`, `format=jsonv2`, `featuretype=country`, and `polygon_geojson=1`.

Overpass queries are built in `src/osm.js:121-142`. For each selector, it queries `node`, `way`, and `relation` in the shard bbox and requests `out tags center qt`. Ways and relations use `center` coordinates from Overpass.

Overpass endpoint behavior is in `src/osm.js:281-340`: POST text query to configured endpoints, `User-Agent`, JSON response parsing, timeout via `AbortController`, fallback across configured endpoints, and failure on Overpass `remark` containing runtime error, parse error, timed out, or out of memory.

Configured defaults in `src/config.js` include:

- Overpass endpoints: `overpass.kumi.systems`, `overpass-api.de`, `overpass.osm.ch`.
- `OVERPASS_DELAY_MS` default 2000.
- `OVERPASS_TIMEOUT_MS` default 45000.
- `OVERPASS_QUERY_TIMEOUT_SEC` default 75.
- `OVERPASS_SELECTOR_BATCH_SIZE` default 25.
- max active jobs 1, max shard workers 2.

## Verified Object Types And Tags

Current Overpass processing includes OSM `node`, `way`, and `relation` objects. `mapElementToLead()` in `src/osm.js:490-539` accepts objects with lat/lon or center lat/lon and rejects objects outside country geometry.

The category is the first truthy value among:

`amenity`, `tourism`, `shop`, `office`, `craft`, `healthcare`, `leisure`, `sport`.

The broad selector compaction key list also includes `club` in `src/osm.js:7-17`.

Subcategory is first truthy among `cuisine`, `tourism:type`, `brand`, `operator`.

Website uses `website` or `contact:website`; phone uses `phone` or `contact:phone`; email uses `email` or `contact:email`.

Closed businesses are not excluded. `deriveBusinessStatus()` in `src/osm.js:542-555` sets `CLOSED` for truthy `abandoned`, `disused`, `demolished`, `razed`, `closed=yes`, or lifecycle namespaces, then still maps the lead.

Missing names are not excluded by the OSM scraper. Live Noco samples showed blank-name OSM rows for categories such as `bicycle_parking` and `information`.

## Verified Category Selectors

Curated concepts live in `src/concept-selectors.js`. The active live job is `concept:broad:all_local_businesses`, verified via `country-scrapers jobs show osm 8ef437c9-c6ed-47ff-9bae-f1dcea1e816b --json`. It is a union of concepts listed in `src/concept-selectors.js:934-983`, including hospitality, dentists, legal, cleaning, gyms, home services, solar, renovation, pools, trades, HVAC, garage doors, and roofing.

`compactSelectorsForBudget()` in `src/osm.js:160-187` compresses large selector lists into regex selectors like key `amenity` value `.+` when selector count exceeds the threshold and multiple broad business keys are present. This means broad jobs may retrieve many irrelevant POIs, not only curated category values.

## Verified Exclusion And Invalid Handling

The scraper excludes:

- Elements without coordinates/center.
- Elements outside resolved country geometry.
- Selector values rejected during auto-selector discovery if they include lifecycle tokens such as disused/abandoned/proposed/construction/demolished/destroyed/removed/closed/former (`src/selectors.js:168-189`).

The scraper does not exclude:

- Blank names.
- Blank websites/phones/emails.
- Closed/lifecycle-tagged businesses; it marks `business_status`.
- Chains or brands; `brand`/`operator` may become `subcategory`.

## Verified Local Store And Sync Behavior

SQLite schema is in `src/store.js:134-234`. Leads are stored with `UNIQUE(job_id, osm_type, osm_id)`. Upserts preserve existing website, phone, email, address, city/area/state/postcode/country when already nonblank, and replace tags/updated time.

Worker flow:

- `createWorker().start()` primes and wakes worker, then polls every `workerPollMs` (`src/worker.js:35-45`).
- Pending jobs resolve country, build initial geometry-aware shards, and become running (`src/worker.js:110-154`).
- Shards are claimed transactionally with a run token (`src/store.js:1286-1313`).
- Shards split before query if oversized, split after high volume, retry on rate/timeout, and auto-recover failed shards (`src/worker.js:212-327`, `src/worker.js:344-360`).
- Results are written to SQLite before shard split or completion (`src/worker.js:257-283`).

Noco sync:

- Desired fields are listed in `src/nocodb.js:1-34`.
- Sync batches are 50 records (`src/nocodb.js:506`).
- Existing records are looked up by direct fields, website fallback, and phone fallback (`src/nocodb.js:453-718`).
- Create is bulk POST through v2/v1 fallback (`src/nocodb.js:1089-1115`).
- Update is bulk PATCH v2, falling back to per-row v1 patch (`src/nocodb.js:959-980`).
- Auto-sync of running jobs is controlled by `autoSyncIntervalMinutes` (`src/nocodb.js:198-240`).

## Verified Downstream Workflow Dependencies

Website enrichment:

- `lead-website-enricher-batch.py` selects rows with blank `website`, optional source filter, and `Blocked=false` (`lines 392-467`).
- It normalizes OSM rows using `osm_url`, `category`, and `subcategory` (`lines 582-680`).
- It writes `website`, `Website Found by Scraper`, and `Website Last Checked At` for accepted matches (`lines 316-322`).
- It writes last-checked timestamps even for unmatched/invalid rows (`lines 277-293`).

SaaS enrichment:

- The local live copy uses source table `mswfxq541pe6khe`.
- It selects rows with `Blocked=false` and nonblank website, normalizes source rows, creates/updates `saas_leads`, and marks source rows with `Enriched Saas`, `Email Found`, and/or `Blocked` (`saas-enrichment-live.py:1418-1516`, `1606-1882`).

SaaS export:

- The local live copy uses target table `saas_leads`.
- It selects `Origin=<country>`, `imported=false`, and `Verification Status=valid`, sends to ReachInbox lists/campaigns, then patches `imported` and `imported_at` on target rows (`saas-export-live.py:465-518`, `557-584`).

These downstream processes mean source rows with any enrichment or campaign evidence are CRM assets and must be preserved.

## Risks And Unknowns

- The old scraper can insert blank-name and broad irrelevant POIs because broad selector compression can query entire business keys with `.+`.
- NocoDB has no unique constraints for OSM identities, website, phone, or email.
- Current direct duplicate lookup includes `osm_id` without `osm_type`; `osm_url` is safer.
- Noco table lacks lat/lon even though local SQLite stores them, reducing safe adoption/remap confidence.
- Live Windmill source is authoritative, but only local mirrors/live temp copies were inspected for some scripts. Treat unverified workflow ownership as protected.
- The local checked-in SQLite file is older than current runtime schema for `selector_id`; live service API was used for current job state.
- No linked-record columns were found in the `Scraper Leads` model, but external workflow references can still exist by copied ids, emails, websites, and campaigns.

## Questions Not Answerable From Code Alone

- Which future categories should each business profile include or exclude?
- Which current broad-mode categories are intentionally useful vs noise?
- Whether any human users manually edit `Scraper Leads` outside the fields visible in code.
- Whether external systems store Noco record ids outside the inspected scripts.
- Whether old OSM rows before recent schema changes have consistent `source`, `osm_url`, and timestamps.
- What exact approval process should authorize deletion after dry-run.

## Recommended Phase 2 Sequence

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

- Separate repository.
- PostgreSQL sidecar.
- Geofabrik download and validation.
- PBF parsing.
- Versioned category profiles.
- Noco read adapter.
- Adoption scanner.
- Reconciliation reports.
- No Noco writes.
- No MCP writes.

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

- Exact Noco contract adapter.
- Final duplicate checks.
- Noco API writes.
- Limited region/profile.
- Existing rows adopted, not recreated.
- No stale deletion.

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

- Provenance-aware source refresh.
- Missing/stale counters.
- Source remapping.
- Tombstones.
- Deletion still disabled by default.

### Phase 2D - Approved Stale Deletion

- Two-snapshot/60-day rule.
- Complete workflow checks.
- Explicit deletion approval.
- Audit and rollback evidence.

### Phase 2E - MCP Integration

- Authenticated read/search tools.
- Draft category proposals.
- Dry-run requests.
- Approved action execution through the same reconciliation engine.
- No unrestricted SQL or shell tools.

## Review Resolution Summary

| Issue | Resolution |
| --- | --- |
| Stale deletion incorrectly treated any nonblank website/email as protected | `stale-lead-safety-policy.md` now bases protection on field provenance, workflow markers, sidecar baseline evidence, and fail-closed unknown ownership. Nonblank source-owned values can be safe only when they match adopted source evidence. |
| Missing source-baseline model | `existing-osm-lead-adoption.md` now defines `noco_source_baseline` with source payload JSON/hash, source snapshot lineage, field hashes/values, `raw_tags_json` hash, `valid_from`, `superseded_at`, code/rule versions, and provenance evidence. Legacy rows without proof fail closed. |
| One missing monthly snapshot could imply deletion | `stale-lead-safety-policy.md` now requires two consecutive complete successful monthly snapshots, at least 60 days since `last_seen_at`, validated extracts, completed parsing/reconciliation, and no remap/completeness uncertainty. |
| Source staleness and profile scope were conflated | The stale policy and architecture now use separate region-scoped source-presence states (`present`, `missing`, `stale_confirmed`, `remap_candidate`, `deleted_in_osm`) and profile/version-scoped eligibility states (`active`, `out_of_scope`, `excluded`, `pending_review`); profile rule changes cannot masquerade as source deletion. |
| Source presence was not region scoped | The authoritative `osm_source_presence` contract is keyed by `(region_id, osm_type, osm_id)`, records per-region missing evidence, and fails closed for incomplete snapshots, overlapping extracts, and boundary ambiguity. |
| Source baselines were described as append-only but the schema was mutable | `noco_source_baseline` now records source snapshot lineage, `valid_from`, `superseded_at`, and a partial unique current-row index; validated refreshes append transactionally and failed refreshes leave the current row unchanged. |
| Sidecar uniqueness constrained inactive history | `existing-osm-lead-adoption.md` now uses partial unique indexes where `active = true`; inactive/superseded mappings remain immutable and unlimited. |
| Ambiguous/unmatched results were mixed with active mappings | Accepted mappings are limited to `exact`, `high_confidence`, and `remapped`; ambiguous, unmatched, rejected, collision, and review results live in `adoption_candidate`. |
| Noco access strategy was not scalable for 4.17M rows | `noco-compatibility-contract.md` now defines `NocoReadRepository` using restricted read-only PostgreSQL for bulk reads and `NocoWriteRepository` using NocoDB API for all production writes. |
| Writer coexistence assumed advisory locks protected against the old scraper | `replacement-architecture-plan.md` now states the new service must not write while the old scraper can synchronize and defines explicit cutover steps before first production write. |
| Business-profile configuration was underspecified | `replacement-architecture-plan.md` now documents versioned `luxeillum`, `saas_redesign`, and shadow-only `legacy_parity` profiles, with draft/active/retired states, rule provenance, overrides, includes/excludes, mappings, and dry-run impact reports. |
| Phase sequence needed clearer gates | This findings doc and the architecture now define Phase 2A through Phase 2E, keeping writes, stale deletion, and MCP integration behind separate approval gates. |

## Required Consistency Tests

- 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.

## Verification Commands To Run Before Closing

- `git diff --check`
- `git -C /root/osm-country-scraper diff --stat`
- `git -C /root/osm-country-scraper status --short`