# OSM Lead Source Service — Production Completion Handoff

**Date:** 2026-07-25  
**Repository:** `lutzkind/osm-lead-source-service`  
**Authoritative branch:** `main`  
**Production deployed code commit:** `2bb95556a6e92bbc06f6682c3d1ef8efe4889056`  
**Repository note:** `main` also contains this documentation handoff after the deployed code commit.

## 1. Purpose

This repository is the standalone replacement for the legacy Overpass-based
`osm-country-scraper`. The target system is a monthly, self-contained OSM lead
source that downloads checksum-verified Geofabrik extracts, generates and
reconciles candidates, adopts existing OSM-derived NocoDB rows safely, and later
inserts genuinely new rows through a separately approved API-only writer.

Windmill is not part of the product architecture. NocoDB remains the lead system
of record. The isolated sidecar stores source/reconciliation evidence and must
never share NocoDB's PostgreSQL database.

## 2. Current production resources

### Replacement service

- Coolify application: `osm-lead-source-service`
- Application UUID: `ny8uluv6fl1a76hgbgfvokjf`
- Repository: `git@github.com:lutzkind/osm-lead-source-service.git`
- Branch: `main`
- Deployed commit: `2bb95556a6e92bbc06f6682c3d1ef8efe4889056`
- Status after deployment: `running:healthy`
- Persistent volume:
  `ny8uluv6fl1a76hgbgfvokjf-osm-lead-source-data`
- Volume mount: `/var/lib/osm-lead-source`

### Legacy service

- Coolify application: `osm-country-scraper`
- Application UUID: `quteqrx7c1b6metzlcvk56cz`
- Current application status: `running:healthy`
- Do not disable or delete it until its writer state and any active legacy job
  are proven, the replacement read-only evidence is accepted, and formal
  cutover is recorded.

### Sidecar

- No production sidecar database currently exists.
- The required image is PostgreSQL 16 with PostGIS, for example
  `postgis/postgis:16-3.5`.
- Database name must be exactly `osm_lead_source`.
- The current guarded Coolify helper hardcodes `postgres:16`; it must not be used
  for this migration because revision `0001_create_sidecar_schema` executes
  `CREATE EXTENSION IF NOT EXISTS postgis` and creates geometry/GIST objects.

## 3. Completed in the July 25 production session

### Durable storage gate

- PR #25 was merged at
  `69c846e84f11db40ca94c51b88073b83795f2320`.
- The production image now creates and owns the storage root, cache, and report
  directories as the non-root runtime UID/GID `999:999` with mode `0750`.
- A Coolify named volume is attached to `/var/lib/osm-lead-source`.
- A marker written by the runtime survived both a full redeployment and an
  application restart.
- The deployed container remained healthy after both replacement operations.

### Runtime configuration gate

The actual production container was verified with these effective values:

```text
OSM_LEAD_SOURCE_RELEASE_SHA=2bb95556a6e92bbc06f6682c3d1ef8efe4889056
OSM_LEAD_SOURCE_READ_ONLY=true
OSM_LEAD_SOURCE_NOCO_WRITE_ENABLED=false
OSM_LEAD_SOURCE_WRITER_ENABLED=false
OSM_LEAD_SOURCE_STALE_DELETION_ENABLED=false
OSM_LEAD_SOURCE_SCHEDULER_ENABLED=false
OSM_LEAD_SOURCE_SCHEDULER_APPROVED=false
OSM_LEAD_SOURCE_RUN_ON_START=false
OSM_LEAD_SOURCE_CACHE_DIR=/var/lib/osm-lead-source/cache
OSM_LEAD_SOURCE_REPORT_DIR=/var/lib/osm-lead-source/reports
OSM_LEAD_SOURCE_DASHBOARD_SNAPSHOT_PATH=/var/lib/osm-lead-source/dashboard.json
```

Coolify still contains older build-time duplicates for four keys:
`CACHE_DIR`, `REPORT_DIR`, `SCHEDULER_APPROVED`, and `RUN_ON_START`. Their values
match the runtime-only entries and the effective container environment above is
correct. This is configuration-metadata cleanup, not a current execution risk.

### Live Geofabrik compatibility gate

The first production subset exposed a live upstream catalog change before any
country work or mutation occurred:

- Geofabrik now returns path-style downloadable feature IDs such as
  `us/california` for 53 US state/territory entries.
- It also returns hierarchical parent values such as `us/california` for
  `norcal` and `socal`.
- The old parser rejected the complete global catalog before processing the
  selected countries.

PR #27 implemented a bounded generic fix and was squash-merged at
`2bb95556a6e92bbc06f6682c3d1ef8efe4889056`:

- slash-separated IDs are validated and normalized into filesystem-safe internal
  IDs, for example `us/california` becomes `us--california`;
- downloadable hierarchy is reconstructed from path prefixes;
- nested children retain the correct parent relationship;
- malformed paths, missing downloadable ancestors, self-parent relations,
  duplicate raw IDs, and normalization collisions fail closed;
- no internal feature or shard identifier contains `/`.

Validation completed outside GitHub-hosted Actions:

- isolated Coolify build reached `running:healthy`;
- all 555 current downloadable Geofabrik features parsed;
- `IE`, `NZ`, `SG`, and the complete US leaf-shard plan resolved;
- California normalized to `us--california`, with `norcal` and `socal` as its
  children;
- Ruff lint passed;
- Ruff formatting passed;
- Python compile validation passed;
- focused live-catalog and fail-closed assertions passed.

GitHub-hosted Actions remain unavailable before runner allocation. Jobs fail
with no assigned runner, so independent validation is still required until the
account-level Actions issue is restored.

## 4. Controlled read-only subset: current state

The representative subset is:

```text
IE,NZ,SG
```

Coverage:

- `IE`: direct extract;
- `NZ`: direct extract with separate market metadata;
- `SG`: shared regional extract requiring bounded country isolation.

The one-shot preview passed with:

```json
{
  "execute": false,
  "network_access": false,
  "noco_access": false,
  "scheduler_required": false,
  "scheduler_started": false,
  "writes_enabled": false
}
```

A locked temporary Coolify task started the real one-shot at
`2026-07-25T07:15:02Z`:

- Scheduled-task UUID: `p1tomojt26az1zosc0cqpv6q`
- Main execution UUID: `mbwnvi5167sq956d440vgxk4`
- Last verified state when this handoff was created: `running`
- Retry count: `0`
- Scheduler task was disabled at `2026-07-25T07:20:12Z`.
- Later minute executions before disablement were lock-only no-ops; the original
  execution remained the only real market run.
- The lock is under the persistent storage root, so accidental duplicate work is
  prevented.

Do not mark the subset accepted until the main execution has finished and its
reports/dashboard are reviewed. On completion, delete the temporary Coolify
scheduled task rather than leaving an obsolete one-shot definition.

## 5. Safety state

The following are intentionally **not enabled**:

- monthly scheduler;
- scheduler approval;
- startup execution;
- replacement writer;
- NocoDB writes;
- stale deletion;
- legacy-writer cutover;
- update execution;
- delete execution.

No NocoDB row was inserted, updated, adopted, relabelled, or deleted during this
session.

## 6. Immediate next steps

Complete these in order.

### Step 1 — Finish and accept the `IE,NZ,SG` subset

1. Poll execution `mbwnvi5167sq956d440vgxk4` until terminal.
2. Require exit status `0` or investigate the exact fail-closed error.
3. Review dashboard/report evidence for all three countries and their shards.
4. Confirm the Singapore shared-source crop completed with `osmium`.
5. Confirm report/cache/dashboard files exist on the named volume.
6. Restart the application and verify the evidence remains.
7. Rerun the same subset to prove checksum-cache reuse and deterministic output.
8. Confirm again that no Noco mutation occurred.
9. Delete task `p1tomojt26az1zosc0cqpv6q` after evidence is retained.

### Step 2 — Run the first full 58-country read-only cycle

1. Keep every write/scheduler gate false.
2. Use `market-run-once --execute` with country scope `all`.
3. Use an explicit single-run lock and a long bounded task timeout.
4. Monitor disk, memory, duration, source sizes, shard progress, and failures.
5. Retain aggregate source/report hashes and per-country evidence.
6. Review any partial result rather than silently treating it as complete.
7. Do not enable the recurring scheduler yet.

### Step 3 — Generic read-only Noco comparison and adoption report

Using the accepted generated reports, produce a read-only comparison covering:

- exact `(osm_type, osm_id)` matches;
- active mapping collisions;
- likely website/domain duplicates;
- likely phone/name/address matches;
- ambiguous candidates;
- non-OSM ownership conflicts;
- protected workflow rows;
- adoption candidates that preserve existing Noco record IDs and workflow state;
- genuinely new insert candidates.

An unmatched OSM identity is not automatic insert permission.

### Step 4 — Provision the isolated PostGIS sidecar

1. Enhance or expose a guarded Coolify provision action that allows only the
   approved PostGIS image for this exact database request.
2. Create one private database named `osm-lead-source-sidecar` with database
   `osm_lead_source` and Coolify-managed persistent storage.
3. Attach only a runtime URL under
   `OSM_LEAD_SOURCE_PRODUCTION_DATABASE_URL`.
4. Keep the migration approval variable absent by default.
5. Verify the target URL with the repository's production migration validator.
6. Apply migrations only with:

```text
OSM_LEAD_SOURCE_MIGRATION_ENV=production
OSM_LEAD_SOURCE_PRODUCTION_MIGRATION_APPROVAL=apply-osm-lead-source-sidecar-v2
```

7. Verify Alembic head, PostGIS extension, schema/table/index/trigger presence,
   and NocoDB isolation.
8. Prove downgrade/upgrade in a disposable clone or restored backup, not by
   destructively downgrading the sole production database.
9. Configure backup, restore test, retention, and rollback evidence.

### Step 5 — Record legacy cutover evidence

1. Determine the legacy scraper's authoritative active-job and scheduling state.
2. Let any accepted active job finish or explicitly cancel it with evidence.
3. Prevent the legacy service from starting new writer cycles.
4. Record formal proof that the legacy writer is disabled.
5. Keep the legacy container available for rollback until the replacement pilot
   is accepted.

### Step 6 — Approve one capped insert-only pilot

Only after Steps 1–5 are accepted:

1. Select a small region/profile/report hash.
2. Create an unexpired sidecar approval with a deliberately low insert cap.
3. Enable the writer and Noco write gates only for the controlled command.
4. Execute through the NocoDB API, never direct PostgreSQL.
5. Run final duplicate, ownership, workflow, lifecycle, and report-hash checks.
6. Verify inserted lineage and sidecar idempotency records.
7. Rerun the identical command and require zero duplicate inserts.
8. Disable the writer gates again after the pilot.
9. Test rollback/operational recovery before expanding scope.

### Step 7 — Expand and schedule

1. Expand insert scope only after the pilot is accepted.
2. Run at least one successful full manual cycle with accepted reconciliation.
3. Enable the monthly scheduler request and approval as separate changes.
4. Confirm only one effective monthly schedule exists.
5. Add failure, partial-cycle, capacity, and stale-run monitoring.

### Step 8 — Presence history and stale lifecycle

1. Store monthly source snapshots and presence evidence in the sidecar.
2. Treat partial/failed snapshots as incapable of proving absence.
3. Require two distinct complete missing snapshots and at least 60 days.
4. Distinguish source deletion from profile exclusion and boundary ambiguity.
5. Quarantine candidates and enforce workflow/ownership protection.
6. Keep stale deletion disabled until a separate Phase 2D approval.
7. Implement rollback evidence before any delete executor is enabled.

### Step 9 — Retire the legacy service and close the project

Only after the replacement has accepted manual and scheduled operation:

1. retain a final legacy export and operational evidence;
2. stop and remove the legacy Coolify application;
3. remove obsolete legacy schedules, credentials, volumes, and DNS only after
   rollback requirements are satisfied;
4. clean the four duplicate Coolify environment metadata entries;
5. restore GitHub Actions runner allocation and require green CI;
6. remove obsolete one-off workflow metadata if still active;
7. update README, project state, operations docs, and deployed release SHA;
8. create a final release tag;
9. record the first two accepted monthly snapshots; and
10. close the project only when the definition below is met.

## 7. Definition of complete

The project is complete only when all of these are true:

- durable source/report/dashboard evidence survives replacement and restart;
- the representative subset and full 58-country read-only cycle are accepted;
- read-only Noco comparison/adoption evidence is reviewed;
- the isolated PostGIS sidecar is migrated, backed up, restore-tested, and
  proven separate from NocoDB;
- the legacy writer is formally disabled before the replacement writer is armed;
- a capped insert-only pilot succeeds and an identical rerun is idempotent;
- the monthly scheduler runs exactly once per intended cycle;
- provenance and presence history are retained;
- stale deletion remains gated by two complete snapshots, at least 60 days,
  quarantine, workflow protection, rollback, and separate approval;
- the legacy service is retired only after rollback evidence exists;
- production documentation and release metadata match the deployed system; and
- CI is restored and green.

## 8. Non-negotiable constraints

- No Windmill dependency in the product.
- No direct write to NocoDB PostgreSQL.
- No automatic adoption, insert, update, or delete from an unmatched identity.
- No concurrent legacy and replacement writers.
- No scheduler activation before accepted manual evidence.
- No stale deletion from one snapshot or a profile change.
- No plain PostgreSQL substitute for the required PostGIS sidecar.
- No claims of completion while any listed gate remains open.