# Database, Migrations, And Data Integrity

Last updated: 2026-06-24

This document defines the production controls for database releases, restore evidence, high-volume indexes, financial state machines, transaction retry behavior and archival policy.

## Release Migration Plan

Every release must generate a migration plan before approval:

```bash
cd backend
go run ./cmd/migration-plan -dir migrations -format json -require-rollback
```

The plan records the ordered migration sequence, SHA-256 checksum for every `.up.sql` file and whether each migration has a matching `.down.sql` rollback file. CI and CD fail if rollback coverage is incomplete.

Production releases must attach the generated `migration-plan.json` to the migration approval artifact and link it from the release notes. The `database_release_migration_plans` table stores dry-run status, rollback verification, approver, risk level and evidence references.

## Dry Run And Rollback Rules

- Run the full migration sequence against a production-like database clone before production approval.
- Compare migration output, row counts for touched tables and application smoke results against the release notes.
- Verify the rollback path for every new migration, or document a risk-approved roll-forward-only exception.
- Keep `BANKING_RUN_MIGRATIONS=false` in production when migrations are controlled by the release pipeline.
- Production approval is valid only when dry-run evidence and rollback verification are linked.

## Restore Drill Evidence

The `database_restore_drill_evidence` table captures backup reference, restored database reference, RPO, RTO, validation status, operator and approver. A passing drill requires approver and approval timestamp.

At minimum, the validation must prove:

- The restored schema has all applied migrations.
- Ledger journal entries remain balanced by currency.
- Account and wallet balances reconcile to ledger lines.
- Audit-chain verification can run on restored data.
- Critical login, account list and admin read paths work against the restored database.

Use `docs/runbooks/database-backup-restore-drill.md` for the drill procedure.

## Index Review

Migration `0027_database_integrity_readiness` adds production-path indexes and seeds the first `database_index_reviews` records. Index reviews must be refreshed at least every 90 days for high-volume paths:

- Transfer list, settlement queues and user transfer history.
- Provider settlement reports and inbound payment queues.
- Audit timeline and audit evidence package exports.
- Active session cleanup and session management.
- Card webhooks and authorization queues.
- Crypto chain transactions and payment status events.

Each review should include the query pattern, observed plan reference, expected volume, risk level and decision.

## Financial State Machines

Financial state transitions are enforced in PostgreSQL for product state machines that carry financial or regulated meaning. Migration `0027_database_integrity_readiness` creates `financial_state_machine_transitions` and trigger enforcement for:

- Payment review cases.
- Wallet balance adjustment requests.
- Savings goals.
- Virtual cards.
- Card authorizations and disputes.
- FX quotes and conversions.
- Crypto chain transactions and travel-rule transfers.
- Inbound payments.

Transfers already have a dedicated payment status transition table and trigger from migration `0015_payment_status_review`.

## Deadlock And Serialization Retry Tests

Money movement repositories must retry PostgreSQL serialization failures (`40001`) and deadlock detection (`40P01`) where retry is safe. Admin wallet adjustment approval also retries idempotency races (`23505`).

Unit tests cover the retry classifiers in transfers, FX, savings and admin wallet adjustment flows. PostgreSQL integration tests still need live contention scenarios as scale testing expands.

## Archival Strategy

Migration `0027_database_integrity_readiness` creates `data_archival_policies` and seeds retention/archive rules for audit, ledger, webhooks, provider reports, sessions and notifications.

Archival jobs must:

- Respect legal holds.
- Export required evidence before deletion.
- Preserve ledger and audit data required for finance, compliance and dispute handling.
- Record archive references and operator approval before destructive cleanup.
- Be tested in staging before production scheduling.

See `docs/data-archival-strategy.md` for operational details.