# Phase 2A.2 Sidecar Schema

## Purpose and isolation

The sidecar stores source evidence, regional presence, profile decisions,
accepted adoption relationships, provenance, shadow-run reports, and future
tombstone evidence for the replacement service. It is separate from NocoDB;
this phase does not read or write NocoDB and does not create a production
database.

The schema is `osm_lead_source`. Every sidecar table and view is explicitly
qualified with that schema. The disposable test service is PostgreSQL 17 with
PostGIS 3.5, bound to `127.0.0.1:55432`; `docker compose down -v` removes its
state.

## Relationships

```mermaid
erDiagram
  source_region ||--o{ source_snapshot : contains
  source_snapshot ||--o{ source_snapshot_object : observes
  osm_source_object ||--o{ source_snapshot_object : identity
  source_region ||--o{ osm_source_presence : scopes
  osm_source_object ||--o{ osm_source_presence : identity
  osm_source_presence ||--o{ osm_profile_eligibility : evaluates
  profile_version ||--o{ osm_profile_eligibility : versions
  noco_lead_mapping ||--o{ noco_source_baseline : proves
  noco_lead_mapping ||--o{ reconciliation_action : references
  reconciliation_run ||--o{ adoption_candidate : records
  adoption_candidate ||--o{ adoption_candidate_review : reviews
  reconciliation_run ||--o{ reconciliation_action : plans
  reconciliation_run ||--o{ reconciliation_report : reports
```

## Table responsibilities

- `source_region` contains configured region identity only; the migration has
  no seed data.
- `source_snapshot` records one extract-processing attempt and its completeness
  (`complete`, `partial`, `failed`, or `unknown`). A `complete` row requires a
  SHA-256 hash, positive size, and non-null downloaded, validated, parser,
  and reconciliation timestamps in that order. Partial, failed, and unknown
  rows may retain incomplete processing state.
- Snapshot attempts are finalized evidence at insertion time: the database
  rejects updates and deletes for every `source_snapshot` row. A later attempt
  is represented by a new row rather than rewriting status, timestamps, hash,
  or size.
- `osm_source_object` is stable typed OSM identity and latest payload facts.
  Node, way, and relation share no numeric namespace, and this table has no
  lifecycle or deletion state. Geometry is PostGIS geometry with SRID 4326.
- `source_snapshot_object` is immutable observation evidence. It records what a
  complete or incomplete snapshot observed; absence rows are never invented.
- `osm_source_presence` is authoritative, region-scoped lifecycle state. Its
  `source_presence_state` is separate from profile eligibility.
- `profile_version` stores metadata only. Phase 2A.4 owns rule behavior.
- `osm_profile_eligibility` stores profile/version decisions and cannot alter
  presence counters or state.
- `business_identity` stores a future normalized identity key without
  implementing normalization.
- `noco_lead_mapping` contains only accepted `exact`, `high_confidence`, or
  `remapped` relationships. Partial unique indexes allow one active mapping
  per Noco row and per typed OSM object while retaining unlimited inactive
  history. Mapping identity and adoption evidence are immutable; active rows
  can be deactivated once and cannot be reactivated. Mapping timestamps must
  be chronological, and a supersession target must be a live active mapping
  for the same Noco table and record.
- `adoption_candidate` and append-only `adoption_candidate_review` keep
  ambiguous, unmatched, collision, rejected, and review evidence outside the
  accepted mapping table.
- `noco_source_baseline` is append-only provenance. One current baseline per
  mapping is enforced; historical rows remain readable and a current row can
  be superseded only once. New baselines must match their mapping and point to
  the immediate prior, already-superseded baseline with increasing dates.
- `reconciliation_run`, `reconciliation_action`, and
  `reconciliation_report` store future shadow-run metadata and report-only
  planned actions. No action can be claimed or executed here.
- `osm_lead_tombstone` is future evidence storage. Its existence does not
  authorize deletion and this phase has no tombstone writer or delete executor.

## Safety constraints and indexes

Presence transitions that advance missing evidence require a `complete` latest
snapshot. `present` requires a latest observation. `missing` starts exactly one
missing snapshot; later missing transitions must use a new, later snapshot and
increase the counter by one. `stale_confirmed` requires two distinct complete
missing snapshots, neither containing the object, last-seen evidence that
precedes them and is at least 60 days old, matched regions, and no boundary
ambiguity. `last_seen_at` is exactly the immutable observation `observed_at`,
and `first_missing_at` is exactly the complete snapshot's
`reconciliation_completed_at`; callers cannot backdate either value. Partial
snapshots may prove presence but never absence. Composite snapshot/region
foreign keys and observation checks reject contradictory or cross-region
references.

Important indexes include snapshot lookup by region/time, a partial index for
latest complete snapshots, typed OSM geometry GIST, observations by typed OSM
identity, active mapping uniqueness, and current-baseline uniqueness.

## Immutable evidence

Schema-qualified PostgreSQL trigger functions reject updates/deletes for
source snapshots, snapshot observations, candidate evidence, candidate reviews,
and reports.
Baselines allow only a one-way `superseded_at` update, and tombstones allow only
one-way supersession fields. Functions set a safe `search_path` and no generic
SQL execution function exists.

## Migration and rollback

```text
export OSM_LEAD_SOURCE_MIGRATION_ENV=test
export OSM_LEAD_SOURCE_TEST_DATABASE_URL=postgresql+psycopg://test_user:test_password@127.0.0.1:55432/osm_lead_source_test
python -m alembic upgrade head
python -m alembic downgrade base
python -m alembic upgrade head
```

Revision `0001_create_sidecar_schema` is hand-authored and deterministic.
Downgrade drops views, triggers, functions, indexes, tables, and the sidecar
schema in dependency order. It does not drop PostGIS, which may be shared by
other schemas.

Test network policy is also fail-closed: only tests marked both `database` and
`integration` may use the exact host and port from validated
`OSM_LEAD_SOURCE_TEST_DATABASE_URL`. CI uses passwordless trust authentication
only for the exact test environment and test database, keeps the URL inside a
non-traced wrapper, and publishes PostgreSQL only on `127.0.0.1:5432`.

## Explicitly deferred

Geofabrik downloads, PBF parsing, profile rules, Noco reads/writes, direct Noco
PostgreSQL access, adoption scanning, reconciliation logic, workers,
scheduling, FastAPI, MCP, deployment, stale deletion, and all application
action execution remain outside Phase 2A.2.