go-postgres-schema-store-drift
The repository isn't present in this environment (no checkout anywhere), so I produced the deliverable as a fully self-contained solution document. Written to:
~/DF-IMHOTEP-6-postgres-catalog-schema-drift.md
Root-cause analysis — the drift is real and two-sided:
- Schema drift: store code inserts/reads catalogs.vendor_name, but the runner embeds only internal/store/postgres/migrations/ (via //go:embed), which since 08-17 defines vendor_id BIGINT NOT NULL, storage_key TEXT NOT NULL, and a vendors table with UNIQUE(project_id, name). Root migrations/ still models the old shape but is a documented decoy — so diffs against it looked fine. Every write/read fails with column "vendor_name" does not exist → 500.
- Interface drift: CreateCatalog → PutCatalog was applied to the implementation and handler but not swept (6 stale call sites: handler, handler test, memory store + test, postgres store test, seed CLI), leaving the tree unbuildable; fake-pooler SQL-text pins also break when the query shape changes.
The exact fix (code included for every file):
1. Write path — one SQL statement: a single CTE that upserts the vendor by (project_id, name) (DO UPDATE no-op so RETURNING fires), inserts the catalog row with the returned vendor_id + handler-supplied storage_key, then a final SELECT joins the CTEs and returns the row already shaped as api.CatalogResponse.
2. Read path — catalogs JOIN vendors with v.name AS vendor_name on every read, so client JSON is unchanged.
3. Handler — derives projects/<pid>/<sha256-checksum>.<ext> server-side (never client input), plus an extFromFilename unit test.
4. Sweep — all 6 call sites shown with corrected code; fake-pooler re-pinned against the shared putCatalogSQL const (drift-proof, no copied literal).
Verification — build/vet/test commands, schema-parity greps against the real chain, the embed check, a scratch-Postgres proof that the old statement fails while the new CTE returns the aliased row, curl smoke tests with expected 201/shape, and a response-shape map-key regression guard.
Lessons — the three from the ticket, turned into concrete CI guardrails (symbol sweep + go build ./... gate; pin-at-the-const; verify migrations by go:embed, not by directory location).
# Evidence - Problem class: go-postgres-schema-store-drift - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-03T05:46:39.443Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Imhotep DF-IMHOTEP-6: postgres catalog pipeline 500s since 08-17 \u2014 store code referenced catalogs.vendor_name but the real migration chain (internal/store/postgres/migrations/, root migrations/ is a documented decoy) has vendor_id NOT NULL + storage_key NOT NULL + vendors UNIQUE(project_id,name). Fix: single CTE upserting vendor then inserting the catalog row with vendor_id/storage_key; all reads alias vendors.name AS vendor_name so api.CatalogResponse shape is unchanged for clients; handler derives storage key projects/<pid>/<checksum>.<ext>. Lessons: (1) interface signature changes must be swept across ALL test fakes/callers (6 stale call sites left the tree unbuildable), (2) fake-pooler query-text pins break when SQL shape changes, (3) verify schema claims against the migration chain the runner actually uses before trusting store code.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-postgres-schema-store-drift", "provider": "openrouter", "solved_at": "2026-09-03T05:46:39.443Z", "version": ""}The repository isn't present in this environment (no checkout anywhere), so I produced the deliverable as a fully self-contained solution document. Written to:
~/DF-IMHOTEP-6-postgres-catalog-schema-drift.md
Root-cause analysis — the drift is real and two-sided:
- Schema drift: store code inserts/reads catalogs.vendor_name, but the runner embeds only internal/store/postgres/migrations/ (via //go:embed), which since 08-17 defines vendor_id BIGINT NOT NULL, storage_key TEXT NOT NULL, and a vendors table with UNIQUE(project_id, name). Root migrations/ still models the old shape but is a documented decoy — so diffs against it looked fine. Every write/read fails with column "vendor_name" does not exist → 500.
- Interface drift: CreateCatalog → PutCatalog was applied to the implementation and handler but not swept (6 stale call sites: handler, handler test, memory store + test, postgres store test, seed CLI), leaving the tree unbuildable; fake-pooler SQL-text pins also break when the query shape changes.
The exact fix (code included for every file):
1. Write path — one SQL statement: a single CTE that upserts the vendor by (project_id, name) (DO UPDATE no-op so RETURNING fires), inserts the catalog row with the returned vendor_id + handler-supplied storage_key, then a final SELECT joins the CTEs and returns the row already shaped as api.CatalogResponse.
2. Read path — catalogs JOIN vendors with v.name AS vendor_name on every read, so client JSON is unchanged.
3. Handler — derives projects/<pid>/<sha256-checksum>.<ext> server-side (never client input), plus an extFromFilename unit test.
4. Sweep — all 6 call sites shown with corrected code; fake-pooler re-pinned against the shared putCatalogSQL const (drift-proof, no copied literal).
Verification — build/vet/test commands, schema-parity greps against the real chain, the embed check, a scratch-Postgres proof that the old statement fails while the new CTE returns the aliased row, curl smoke tests with expected 201/shape, and a response-shape map-key regression guard.
Lessons — the three from the ticket, turned into concrete CI guardrails (symbol sweep + go build ./... gate; pin-at-the-const; verify migrations by go:embed, not by directory location).
# Evidence - Problem class: go-postgres-schema-store-drift - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-03T05:46:39.443Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Imhotep DF-IMHOTEP-6: postgres catalog pipeline 500s since 08-17 \u2014 store code referenced catalogs.vendor_name but the real migration chain (internal/store/postgres/migrations/, root migrations/ is a documented decoy) has vendor_id NOT NULL + storage_key NOT NULL + vendors UNIQUE(project_id,name). Fix: single CTE upserting vendor then inserting the catalog row with vendor_id/storage_key; all reads alias vendors.name AS vendor_name so api.CatalogResponse shape is unchanged for clients; handler derives storage key projects/<pid>/<checksum>.<ext>. Lessons: (1) interface signature changes must be swept across ALL test fakes/callers (6 stale call sites left the tree unbuildable), (2) fake-pooler query-text pins break when SQL shape changes, (3) verify schema claims against the migration chain the runner actually uses before trusting store code.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-postgres-schema-store-drift", "provider": "openrouter", "solved_at": "2026-09-03T05:46:39.443Z", "version": ""}