gps-denied-onboard/_docs/03_implementation/batch_27_cycle1_report.md

Batch 27 / Cycle 1 — Implementation Report

Date: 2026-05-12 Tasks: AZ-304 (C6 Postgres schema — additive 0002 migration: identity, LRU, freshness rules) Story points landed: 3 Status: complete (AZ-304 → In Testing)

Scope summary

Single-task batch — final queued 1-pointer of cycle 1. AZ-304 ships the strictly-additive 0002_c6_tile_identity_and_lru.py migration on top of the AZ-263 baseline, the pinned UUIDv5 namespace + derivation helpers cross-coordinated with satellite-provider, the migration runner the composition root invokes at startup, and bumps the AZ-303 TileMetadataStore contract v1.1.0 → v1.2.0 by adding the TileMetadata.location_hash: UUID | None field.

Before implementation, the spec was rewritten under user-approved Option A to drop the renames / retypes / drops the original draft called for. The as-built migration only adds columns, indices, the tile_freshness_rules table, and widens (loosens) the ck_tiles_freshness_status CHECK to the UNION of AZ-263 and AZ-303 vocabularies. No AZ-263 column, table, index, or CHECK is renamed, retyped, or dropped (data_model.md § 6.1 / § 6.3).

Files added / modified

New (production)

• src/gps_denied_onboard/components/c6_tile_cache/_uuid_namespace.py — pinned TILE_NAMESPACE_UUID = 5b8d0c2e-1a4f-4b3a-8c9d-e7f6a3b2c1d0, derive_tile_id(zoom, x, y, source, flight_id) (UUIDv5 over "{z}/{x}/{y}/{source}/{flight_id_or_zero_uuid}"), derive_location_hash(z, x, y). Cross-workspace coordination point with satellite-provider/SatelliteProvider.Common/Utils/Uuidv5.cs.
• src/gps_denied_onboard/components/c6_tile_cache/migrations.py — apply_migrations(config) -> MigrationResult runner, frozen MigrationResult dataclass, MigrationError. Drives Alembic command.upgrade(cfg, "head") against the AZ-263 env at db/migrations/; one retry on PG SQLSTATE 40001 (serialization failure); structured INFO log on apply / no-op with the resolved namespace UUID emitted for post-mortem drift detection.
• db/migrations/versions/0002_c6_tile_identity_and_lru.py — additive Alembic migration: six new tiles columns (tile_uuid UNIQUE, location_hash, content_sha256 w/ length-64 CHECK, disk_bytes w/ nonneg CHECK, accessed_at, uploaded_at); four new btree indices on tiles; one UNIQUE expression index idx_tiles_natural_key over the COALESCE-zero-uuid natural key; CHECK-widening of ck_tiles_freshness_status to the AZ-263 + AZ-303 UNION vocabulary; four NULLable bbox columns on sector_classifications; new tile_freshness_rules table seeded with the two default thresholds (active_conflict / reject @ 6m, stable_rear / downgrade @ 12m). Reverse downgrade() drops every addition and restores the AZ-263 freshness CHECK to its original vocabulary.

Modified (production)

• src/gps_denied_onboard/components/c6_tile_cache/_types.py — TileMetadata.location_hash: UUID | None = None (positional last, default None so AZ-303 v1.1.0 constructors keep working; the impl-task's PostgresFilesystemStore.insert_metadata derives the value when None and the DB NOT-NULL constraint is the safety net).
• db/migrations/env.py — docstring tightened to document the target_metadata deferral (we use Alembic op.* directly; ORM models land in a later cycle).

New (tests)

• tests/unit/c6_tile_cache/test_uuid_namespace.py — 8 deterministic Tier-1 tests covering AC-10 (5 locked tile-id vectors, 2 locked location-hash vectors, idempotent calls, TileSource enum / UUID-typed / None-flight inputs, malformed-flight rejection) and AC-11 (location-hash invariance across source/flight, distinct cells distinct hashes).
• tests/unit/c6_tile_cache/test_postgres_schema.py — @pytest.mark. docker integration tests covering AC-1 through AC-9 + NFR-perf- apply / NFR-perf-noop + MigrationResult frozen smoke. Reads DB_URL env var; consumes the db service from docker-compose. test.yml. Auto-skipped on Tier-1 by tests/conftest.py.
• tests/unit/c6_tile_cache/test_migrations_runner.py — 5 Tier-1 tests covering DSN resolution (config-block, env fallback, missing raises MigrationError) and the SQLSTATE-40001 retry / terminal paths (mocked DBAPIError to keep them deterministic and DB-less).
• tests/unit/c6_tile_cache/fixtures/c6_postgres_schema_v2.sql — copy-pastable DDL fixture the AC-3 schema-shape diff test diffs against. Includes every AZ-263 column / index / CHECK plus every additive item from this migration plus the two seeded tile_freshness_rules rows.

Modified (tests + docs)

• tests/unit/test_ac5_alembic.py — head-revision assertion bumped from 0001_initial to 0002_c6_tile_identity_and_lru; function renamed test_head_revision_is_0001_initial → test_head_revision_matches_latest_migration.
• _docs/02_document/contracts/c6_tile_cache/tile_metadata_store.md — version bumped v1.1.0 → v1.2.0; new TileMetadata.location_hash section; sector_boundaries → sector_classifications corrections; Alembic migration path corrected from c6_tile_cache/_alembic/ to db/migrations/versions/; Change Log v1.2.0 entry.
• _docs/02_document/module-layout.md — c6_tile_cache component now explicitly Owns db/migrations/** (Alembic env was already living there since AZ-263 bootstrap, but not documented). migrations.py / _uuid_namespace.py listed under the component's internal modules.
• _docs/02_tasks/todo/AZ-304_c6_postgres_schema.md → moved to _docs/02_tasks/done/. Spec was rewritten earlier this batch (Option A: strictly-additive 0002 + descope of psycopg_pool/connection.py to AZ-305 + replacement of testcontainers with the existing @pytest.mark.docker / docker-compose.test.yml infra).

Design decisions

1. Strictly additive 0002, no rename / retype / drop. User picked Option A on the schema-reconciliation gate. Every AZ-263 column, table, index, and CHECK survives 0002 byte-identical. The single constraint mutation is the ck_tiles_freshness_status CHECK widening, which is a semantic loosening (more values accepted) and therefore additive per data_model.md § 6.1. Legacy AZ-263 values (stale_warn, stale_reject) remain valid until an ADR-gated future cycle deprecates them. DTO ↔ column mapping (freshness_label ↔ freshness_status, quality_metadata ↔ tile_quality_metadata, (lat, lon) ↔ (latitude, longitude)) is owned by the future PostgresFilesystemStore task (AZ-305) — the schema does NOT pivot to DTO field names.

2. UUIDv5 namespace pinned in code, not config. TILE_NAMESPACE_ UUID = 5b8d0c2e-1a4f-4b3a-8c9d-e7f6a3b2c1d0 lives in the _uuid_namespace module as a Final constant — not in environment / config / DB — so cross-workspace coordination with the C# satellite-provider reduces to a code-level review check on either side. The runner logs the namespace value on every apply / no-op so production drift would surface immediately.

3. psycopg_pool deferred to AZ-305. The original spec assumed AZ-263 pinned psycopg_pool; reality check showed only psycopg[binary]>=3.1 and alembic>=1.13 are pinned. A new third-party dependency would inflate this task's scope and risk; moved to AZ-305 (c6_postgres_filesystem_store) where the runtime connection pool is genuinely needed. The migration runner uses Alembic's existing NullPool SQLAlchemy engine wired by AZ-263 — no pool needed for one-shot startup migrations.

4. Retry-without-sleep on serialization failure. The runner retries once on PG SQLSTATE 40001, but without a time.sleep backoff. Component files are forbidden from calling time.* directly (tests/_meta/test_no_direct_time_in_components.py, Invariant 2 of the replay contract), and migrations run before the injected Clock is constructed, so Clock.sleep is unavailable too. Alembic's NullPool opens a fresh connection on retry, which already introduces natural jitter; a 0-50 ms backoff buys nothing measurable. Documented in migrations.py inline.

5. MigrationError NOT a member of TileCacheError family. Migrations run BEFORE the runtime error consumer (c6_tile_cache. errors) is constructed — making MigrationError a TileCacheError would create a forward-import cycle and conceptually mis-place a startup-phase failure inside a runtime-phase error tree. Documented in the runner module docstring + verified by test_migration_error_is_not_tile_cache_error.

6. module-layout.md updated as scope-creep, user-approved. Drift gate surfaced that module-layout.md referenced a _alembic/ directory and .sql migration files; reality is db/migrations/ .py files and the doc didn't claim ownership of any of it. User picked Option A to expand AZ-304 scope by one doc edit: c6_tile_cache now explicitly Owns db/migrations/**.

AC coverage

AC	Test name(s)	Status
AC-1	test_ac1_apply_to_az263_baseline_advances_alembic_version	Tier-2 (docker)
AC-2	test_ac2_apply_at_head_is_no_op	Tier-2 (docker)
AC-3	test_ac3_schema_shape_diffs_clean_against_fixture	Tier-2 (docker)
AC-4	test_ac4_per_flight_separation_allowed	Tier-2 (docker)
AC-4b	test_ac4b_duplicate_natural_key_rejected + ..._googlemaps	Tier-2 (docker)
AC-5	test_ac5_freshness_check_widening_accepts_union_vocabulary	Tier-2 (docker)
AC-6	test_ac6_downgrade_reverses_cleanly	Tier-2 (docker)
AC-7	test_ac7_default_freshness_rules_seeded	Tier-2 (docker)
AC-8	test_ac8_runner_logs_info_on_apply_and_no_op	Tier-2 (docker)
AC-9	test_ac9_az263_columns_byte_identical_after_upgrade	Tier-2 (docker)
AC-10	test_ac10_namespace_locked_and_locked_vectors_match + companions	Tier-1 passing
AC-11	test_ac11_location_hash_invariant_across_source_and_flight_id + companion	Tier-1 passing
AC-12	covered by AZ-303 test_protocol_conformance.py (DTO default still None)	Tier-1 passing
NFR-perf-apply	test_nfr_perf_apply_under_5s_on_baselined_empty_db	Tier-2 (docker)
NFR-perf-noop	test_nfr_perf_noop_under_100ms	Tier-2 (docker)
NFR-reliability-retry	test_nfr_reliability_retry_once_on_serialization_failure + companion	Tier-1 passing

The Tier-2 docker tests are explicitly skipped on Tier-1 by the project-wide tests/conftest.py mark-filter; they run locally via docker compose -f docker-compose.test.yml up -d db && GPS_DENIED_TIER=2 DB_URL=postgresql://gps_denied:dev@localhost:5432/gps_denied pytest tests/unit/c6_tile_cache/test_postgres_schema.py.

Test run

python3 -m pytest tests/ -q → 1180 passed, 32 skipped (Tier-2 / CUDA / cmake / actionlint / docker), 1 warning in 21.77 s, no failures.

Mypy strict on production + test modules: clean. Ruff + ruff format on the modified set: clean.

Self-review

• Production code: deterministic UUIDv5 with locked namespace + documented cross-repo invariants; no time.* calls in component files (Invariant 2 meta-test passes); no new third-party deps; migration is byte-additive on the AZ-263 baseline; runner emits structured INFO logs with kind + kv per the AZ-266 log schema; MigrationError correctly outside the TileCacheError family.
• Tests: every AC has at least one named assertion (Tier-1 or Tier-2); cross-repo coordination vectors are locked with documented expected UUIDs; no hidden runtime dependencies in Tier-1 (mocked DBAPIError for the retry path).
• Lint / type: ruff + mypy strict clean on the modified set.
• Docs: AZ-304 spec moved to done/; contract bumped v1.2.0; module-layout.md reconciled with reality.

Known gaps

• Tier-2 Postgres tests need a real db service; rolled forward to the Tier-2 validation pass.
• psycopg_pool connection helper deferred to AZ-305 — documented in both this report and the AZ-304 spec.
• target_metadata wiring in db/migrations/env.py deferred until the first task introduces SQLAlchemy ORM models (none today, none in AZ-305 as currently scoped).
• AZ-263 legacy CHECK values (stale_warn, stale_reject) remain valid in ck_tiles_freshness_status; deprecation requires a future ADR-gated cleanup migration.