Database changes

Writing a migration, the rules every migration follows, and a worked example.

In one line: a schema change is a new timestamped SQL file in msaving/backend/migrations/, tested with the test suite and a fresh docker compose up, and applied by every server on its next start, after a backup.

A migration is one SQL file that moves the database from one version to the next. Every database (a laptop, every operator’s server, the tests) is built by running the same files in the same order with the same runner (backend/app/jobs/migrate.py), which records each in msaving.migrations. Plain SQL, not Alembic: Alembic can’t see row-level security, triggers, logins or grants, where most of the security lives.

Steps

cd msaving
git switch dev && git pull && git switch -c feat/add-recurring
# backend/migrations/<UTC timestamp>_add_recurring.sql, e.g. 20261105093000_add_recurring.sql
cd backend && PATH=/usr/lib/postgresql/17/bin:$PATH python -m pytest -q -p no:cacheprovider tests/
cd .. && docker compose up -d --build && docker compose logs msaving   # "1 migration(s) applied", after a backup

Then in the same PR: tests for the new behaviour. In a paired PR to the docs (src/content/docs/ in msaving-landing): a row in Requirements and tests if it’s a new requirement, and the pages that describe the schema.

Rules

• Never edit a migration that’s in a release. Write a new one that fixes it. (Until the first release the baseline is still edited directly.)
• A new table gets RLS, policies and grants in the same file. No table with user data ever exists without RLS, even for one release.
• Grant every new function to the roles that call it. EXECUTE is revoked from PUBLIC by default.
• Every function that reads tables (security definer or trigger) sets search_path = pg_catalog, public, pg_temp.
• Breaking changes in two releases: operators skip versions and old phones keep calling the API.
  1. Add the new column/table and start using it (old clients still work).
  2. In a later release, remove the old one.
• Migrations create roles, not logins. The runner makes the logins.
• Backfills on large tables go in batches. Operators’ databases are small, but don’t lock a household out for minutes.
• It has to work on any Postgres 15+, including an operator’s own Supabase: no extensions beyond pgcrypto.
• Money is bigint minor units. Exposed ids are random UUIDs.

Example: a new user-data table

create table recurring_rules (
  id          uuid primary key default gen_random_uuid(),   -- random, safe to expose
  book_id     uuid not null references books(id) on delete cascade,
  account_id  uuid not null,
  amount      bigint not null,                              -- minor units
  version     int not null default 1,                       -- optimistic concurrency
  -- ...
  unique (id, book_id),
  foreign key (account_id, book_id) references accounts(id, book_id)  -- can't point across books
);

alter table recurring_rules enable row level security;
-- policies: copy the pattern of an existing per-book table in the baseline
-- grants: to app_user only what the API needs

Look at how the baseline does contacts or budgets and follow the same pattern; don’t invent a new one.

Migrations today

The hosted design’s migrations (Supabase lock-down, my_mfa_required(), plans, consent) were replaced by this baseline on 2026-10-20; no instance ever ran them.