Skip to content

Schema changes

Tables are defined in nimi_service/models.py; views in nimi_service/views.py. Alembic migrations in nimi-service/db/migrations/versions/ apply them.

Changing tables

# 1. edit nimi_service/models.py
.venv/bin/python -m nimi_service db revision "add foo column"   # autogenerate a migration
# 2. review the generated file: autogenerate can miss renames, data changes, server defaults and
#    CHECK constraint changes
# 3. try it on a scratch database (see Developer setup), including downgrade
.venv/bin/python -m nimi_service db upgrade
.venv/bin/python -m nimi_service db current
  • Each migration runs in its own transaction; a failure rolls that migration back.
  • Never edit a migration that has been applied anywhere — add a new one. (Before any database existed, the history was squashed into 0001_baseline.py.)
  • CHECK constraints (e.g. allowed statuses) are not compared by autogenerate: drop and re-create them by hand in the migration.
  • Back up the real database before upgrading it.

Changing views

Views aren't autogenerated. To change one:

  1. edit it in views.py;
  2. write a migration that calls drop_views(op, OLD) then create_views(op, NEW) with frozen copies of the old and new SQL inside the migration (so replaying old migrations never uses newer SQL);
  3. if it is one of SKILL_VIEWS, nothing else is needed: db upgrade re-grants the skill reader on every run. Re-run dev/review_access.sh for the review role.

Checking for drift

After an upgrade, db revision on an up-to-date database should produce an empty migration (delete it). Comparing pg_dump -s output before and after a refactor is a good way to prove nothing changed.