Skip to main content

Database migrations

Drizzle ORM manages schema in apps/api/src/db/schema/ and generates migrations via drizzle-kit. Full schema reference: architecture/data-model.

Workflow​

Local​

# After editing schema
pnpm --filter @gringo-pay/api db:generate

# Apply to your local dev DB (uses NEON_DB_URL from .dev.vars)
pnpm --filter @gringo-pay/api db:migrate

Production​

Migrations run as the first step of the API deploy job (.github/workflows/cd.yml):

- name: Run database migrations
  run: pnpm --filter @gringo-pay/api db:migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

The API deploy only proceeds if migrations succeed. Wrangler deploy runs after.

Required Postgres extensions​

Enable these once on any Neon branch (dev, test, production) before the first migration runs:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

pgcrypto powers the column-level encryption on sensitive PII (see privacy and data-model). The initial migration includes the CREATE EXTENSION statement.

Encrypted columns​

kyc_verifications.verified_identity_encrypted and user_bank_details.{account_number,sort_code,iban,bic}_encrypted are bytea columns holding the output of pgp_sym_encrypt(<plaintext>, PII_ENCRYPTION_KEY). When writing:

INSERT INTO user_bank_details (..., account_number_encrypted, ...)
VALUES (..., pgp_sym_encrypt($1, $2), ...)

When reading (behind a specific service role):

SELECT pgp_sym_decrypt(account_number_encrypted, $1)::text AS account_number
FROM user_bank_details WHERE ...

The Drizzle schema uses customType wrappers so route handlers see plain strings; the encrypt/decrypt happens at the query layer.

Test database​

Tests run against a local, throwaway Postgres — a postgres:17 container locally (apps/api/docker-compose.yml) and a services: container in CI — reached through the Hyperdrive binding, never a remote/shared branch. apps/api/src/test/global-setup.ts drops and rebuilds the schema from src/db/migrations/*.sql on every run, so it can never drift. Connection defaults to postgres://postgres:postgres@localhost:5432/gringo_test; override with TEST_DATABASE_URL. See testing-db-strategy.

Retention cron​

Retention rules (see privacy § retention) are enforced by a nightly cron running against the production DB:

  1. Purges non-essential columns on rows where users.closed_at + 0d has arrived.
  2. Deletes webhook_events rows older than 90 days past processed_at, unless still referenced by an unresolved transaction.
  3. Hard-deletes KYC + transactions + bank details + the users row itself at users.closed_at + 6y.

The cron lives as a scheduled Worker (crons binding in wrangler.toml). Any migration that changes what "essential" means on users, or changes the retention window, needs to update the cron in the same PR.

Rules​

  • Never edit an already-applied migration. Always generate a new one.
  • Never drop columns or tables without a deprecation window — old Workers versions may still be running during a deploy roll-out.
  • Keep migrations reversible in principle. Drizzle doesn't generate down migrations, but write forwards migrations that could be reversed by a follow-up if needed.
  • Backfills belong in migrations, not in application code. If a new column needs data, backfill it in the same migration that adds it.
  • Adding a new encrypted column — same migration also runs the encrypt on the initial value (using pgp_sym_encrypt inline). Never leave plaintext in a column that's going to be encrypted.
  • Regulatory-retention-bound tables (kyc_verifications, transactions, pix_payments, user_bank_details) — never DROP TABLE these on a live database. Column drops must go through a deprecation window and be reflected in the retention cron.

Rollback​

If a migration is bad but already applied:

  1. Do not try to reverse the migration in place — write a follow-up migration that undoes / fixes it.
  2. If the API deploy is failing because of the new migration, wrangler rollback the API and ship the fix migration.

For encrypted-column mistakes (e.g. wrong plaintext written): a follow-up migration can decrypt, correct, and re-encrypt in a single statement. Do not attempt this from application code.