> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reflections.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# ADR-0013: Migration governance and drift control

> Keep database schema evolution deterministic and verifiable across local and CI/CD environments.

<Info>**Status:** Accepted **Date:** 2026-02-06 **Deciders:** Reflections Maintainers</Info>

## 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.

## Related ADRs

* [ADR-0004: Primary data platform: Supabase Postgres](/decisions/adr-0004)
* [ADR-0012: CI/CD quality and release gates](/decisions/adr-0012)
* [ADR-0005: Temporal fact and patch lifecycle](/decisions/adr-0005)