Skip to main content
Status: Accepted Date: 2026-02-06 Deciders: Reflections Maintainers

​
Context

Schema drift is a primary reliability risk for this system because ingestion, retrieval, and authorization all depend on database contracts. Migration sequencing and deployment behavior must be explicit and testable.

​
Decision

Adopt SQL-first migration governance with explicit drift controls:
  • All schema changes are committed as ordered SQL migrations in supabase/migrations.
  • Local validation relies on reset + lint + migration contract tests.
  • CI migration deployment performs dry-run before apply against linked Supabase project.
  • Rollback files are tracked where high-risk migrations require explicit reverse operations.

​
Alternatives considered

​
Alternative 1: ORM-managed auto migrations only

Pros:
  • Faster authoring for simple schema changes.
Cons:
  • Reduced control/visibility over generated SQL.
  • Harder to reason about advanced Postgres/vector/index behavior.

​
Alternative 2: Manual SQL apply outside source control

Pros:
  • Quick immediate fixes.
Cons:
  • No reproducible schema history.
  • High drift risk between environments.

​
Alternative 3: Snapshot-based schema sync with no migration history

Pros:
  • Simple current-state representation.
Cons:
  • Poor audit trail and rollback discipline.
  • Difficult forensic analysis across releases.

​
Consequences

Benefits:
  • Deterministic schema evolution with auditability.
  • Better confidence that local/CI/prod schemas converge.
  • Easier onboarding via explicit migration history.
Costs:
  • Requires SQL review rigor and migration test maintenance.
  • Rollback authoring adds overhead for risky changes.

​
Implementation notes

  • Migration ordering and rollback shape are validated by contract tests.
  • Supabase config conventions are validated by dedicated tests.
  • The deployment workflow enforces dry-run then apply, with the main release flow depending on migration success.
  • Manual workflow_dispatch remains available as break-glass, guarded by a production confirmation gate.
  • A concurrency group prevents parallel migration applies.