Waterflow

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 waterflow/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 waterflow.migrations. Plain SQL, not Alembic: Alembic can’t see row-level security, triggers, logins or grants, where most of the security lives.

Steps

cd waterflow
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 waterflow   # "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 waterflow-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.
  • 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

File What it does
20261020000000_baseline.sql The schema of the first release: ledger, books and sharing, roles, policies, triggers, views, exchange rates, budgets, deletion, instance settings, sign-in, invites, the admin’s functions, timezone checks, the lock-down
20261020000100_iso_4217_currencies.sql The full ISO 4217 currency list (155)
20261020120000_entry_edits_are_versions.sql An edit to amounts or accounts alone is a version, with its own line in the edit history
20261020130000_account_details.sql Details by account kind: a note, a credit card’s limit, fee and days, an interest rate
20261020140000_book_change_marker.sql The per-book change marker clients poll
20261020150000_book_change_marker_index.sql An index that keeps the marker cheap
20261021100000_session_devices.sql Signed-in devices: an id and a browser and system label per session
20261021110000_idle_sign_out.sql Each user’s sign-out after inactivity
20261021120000_job_schedule.sql The daily jobs’ schedule and last results
20261021130000_bills.sql A loan’s installment and installment fee, for the monthly bills (app/domain/bills.py)
20261021140000_job_retry.sql A failed daily job runs again an hour later
20261021150000_english_first.sql New accounts are English by default
20261021170000_transaction_time.sql A time of day on every transaction, 00:00 for older ones
20261021180000_remembered_devices.sql Devices remembered at the two-factor step, and what forgets them
20261021190000_installment_plans.sql Credit card purchases paid in installments
20261021200000_account_pins.sql The accounts each member pins to their own Overview
20261021210000_restore_at_setup.sql Restoring a backup at first setup, with the setup code
20261022100000_person_kind.sql The account kind person, alone in its file: Postgres can’t use an enum value in the transaction that adds it
20261022100100_person_accounts.sql Every person is also an account, with its rules, and one for each existing person

Each file starts with a comment saying what it does and why. 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.

Edit this page · Report a problem