Sprint 2 — Audit Foundation, Multi-Tenant, Events, Seal

Operator’s first alembic upgrade head on dev failed. Then a second iteration (with :id::UUID added for an explicit cast) passed on dev BUT failed the CI three-pass guard on a fresh DB — :id was apparently not substituting.

Two distinct issues stacked. First, the original INSERT INTO tenant.tenant (id, ...) VALUES (:id, ...) bound :id as a Python str; psycopg types parameters from Python types, so the value arrived at PG as VARCHAR and the implicit cast into a UUID column failed. Adding :id::UUID looked like the fix — and worked on dev because dev had already stamped past 0003 by the time the corrected code ran. On a fresh DB, the CI guard surfaced the actual bug: SQLAlchemy’s text() parameter regex deliberately skips :name when followed by :: (the PG cast operator), so :id::UUID is parsed as a literal :id followed by ::UUID. The bindparam value never substituted; the INSERT ran with the literal text :id in it.

Inline DEFAULT_TENANT_ID as a literal in the f-string; drop the .bindparams() call entirely. Matches the no-bind-params-in-DDL pattern used elsewhere. DEFAULT_TENANT_ID is a fixed constant from module code (no injection vector), and PG coerces string literals to UUID at INSERT time without needing an explicit cast.

alembic upgrade head errored on dev: could not determine data type of parameter $1 on each CREATE TABLE audit.event_2026_05 PARTITION OF audit.event FOR VALUES FROM (:lo) TO (:hi).

DDL statements don’t carry column context, so psycopg cannot infer the type for a bound parameter inside a partition-bound clause. The driver sends the parameter as TEXT by default, and the partition bound parser refuses without an explicit cast.

Interpolate the bounds directly into the SQL via f-string. The inputs come from int year/month already, formatted through f"{year:04d}-{month:02d}-01 00:00:00+00" — no injection surface. Applied the same treatment to the chain_head seed INSERT for consistency: DDL and seeds inline; app-time DML uses binds.

CI migration guard’s three-pass cycle failed on pass 2 (reverse): 0001.downgrade() tried DROP INDEX audit.ix_audit_event_tenant_occurred but the index no longer existed — 0004.downgrade() had already cascade-dropped it with audit.event.

Each migration’s downgrade() was written in isolation, referencing only the objects that migration created. That’s correct in spirit, but 0004.downgrade() uses DROP TABLE audit.event CASCADE, which removes the index 0001 created. By the time 0001.downgrade() runs, the index is gone — plain DROP fails.

Sweep every downgrade() and add IF EXISTS to every DROP INDEX, DROP TABLE, and DROP SCHEMA. Each migration still only references objects it created, but the drops are now idempotent under whatever the upstream cleanup happened to do. Three-pass cycle now passes end-to-end.

After the migration guard turned green, the obvious-looking next move was to make deploy-dev run alembic upgrade head automatically so dev doesn’t lag behind the image. Pushed the change, watched it fail mid-cycle with a quoting error in the ssh-into-vm bit.

ADR-0011 (Sprint 2 CI/CD strategy) had landed earlier that day with this exact alternative considered AND rejected: “Auto-migration on deploy — simpler operator workflow but removes the safety gate. A bad migration would automatically corrupt the live database before the operator can intervene. Rejected: safety over convenience.” Missed the ADR on first read; the quoting error was incidental.

Reverted the auto-migrate. Restored the original deploy step. The CI migration guard already proves the chain against a fresh PG on every push, so the migration shape is validated automatically — it’s only the live application that stays operator-gated. The retreat to the ADR position took one commit.