Collapse schema.py + migrations.py into single source of truth (follow-up to #713)

[AndriiPasternak31]

Context

Follow-up to #713 (PR #745). #713 closes the prevention loop for schema-vs-migrations drift via a CI gate. The dual-voice plan review for #713 (Codex + Claude subagent) flagged that the gate, while useful as a stopgap, ossifies the underlying dual-source-of-truth pattern — Trinity has two writable schema authorities (src/backend/db/schema.py declarative, src/backend/db/migrations.py versioned) and contributors must keep both in sync by hand.

Codex (during /autoplan dual-voice CEO review):

A CI gate is acceptable only as a temporary brake. As a durable answer, it normalizes the real failure mode: Trinity has two writable schema authorities. Once this lands, future contributors will learn the ritual "touch migration, touch schema.py" instead of the invariant "there is one schema source." That can ossify the bad pattern unless #713 explicitly creates a follow-up to eliminate dual-authoring through generation, a schema dump artifact, or Alembic-style migration ownership.

Claude subagent agreed, noting payback for unification is roughly 5x within 5 years on drift-cost alone, and the real value is eliminating the cognitive tax (53 tables, 36+ migrations, two places to remember per change).

Cost math (rough)

Approach	Cost	Drift risk after
Status quo (post-#713 CI gate)	$0 ongoing	Gate catches table/column/index/trigger drift; constraint-level (NOT NULL/DEFAULT/FK/CHECK), reverse-direction, and trigger-body drift still slip through
Alembic migration	~1 week	Eliminated — Alembic auto-generates migrations from a declarative source
Generated schema.py from migration end-state	~3-5 days	Eliminated — schema.py becomes a snapshot artifact, not a hand-edited file
Schema-dump check-in (snapshot in repo)	~2 days	Reduced — drift becomes a CI-visible diff in a generated artifact

Proposal

Pick one of the three unification paths above by 2026-11 (~6 months from #713 ship). #713's gate buys us time; this issue is the alarm that the time is running.

Three options to evaluate:

1. Migrate to Alembic — full standard solution; biggest behavior change.
2. Generate schema.py from migrations — keep two files, but make schema.py a CI-generated artifact (no hand edits). Smallest diff to current architecture.
3. Repo-checked schema dump artifact — e.g. src/backend/db/schema.sql regenerated by a script, diffed in CI. Lighter than (2) and gives a SQLite-canonical source.

Out of scope here

• Whichever option is chosen, the PR for it should remove the CI gate added by CI gate: fail PRs that add a migration without updating schema.py #713 (it becomes redundant once dual-authoring is eliminated).

References

• CI gate: fail PRs that add a migration without updating schema.py #713 (the prevention CI gate)
• PR ci(schema): schema.py vs migrations.py parity gate (#713) #745 (the gate's implementation)
• Schema Validation: 11 tables + 14 columns missing from schema.py (invariant #3 violated) #691 (the underlying drift that originally motivated CI gate: fail PRs that add a migration without updating schema.py #713)
• PR fix(schema): backfill schema.py to match migrations.py reality (#691) #712 (the backfill that made schema.py faithful again)
• docs/memory/architecture.md — Architectural Invariant Feature/vector log retention #3