Operations

Database & Migrations

Postgres with pgvector, SQLModel for the tables, Alembic for the history. Plus the scripts that load a textbook into it.

Everyday commands

The Make targets run Alembic inside the app container, which is where the database hostname resolves.

Command	What it does
`make generate-migration message="add x"`	Autogenerate a revision from the models.
`make migrate-up`	Apply everything up to the latest revision.
`make migrate-down version=abc123`	Roll back to a specific revision.
`make generate-local-data`	Wipe, migrate and reload the sample textbook.

Two of these need a running container

migrate-up and migrate-down use exec, so run make run first. generate-migration and generate-local-data start their own throwaway container and work either way.

There is no Make target for inspecting state, so run Alembic directly:

uv run alembic current   # which revision is applied
uv run alembic history   # the full chain
uv run alembic heads     # should always be exactly one

Writing a migration

Autogenerate compares the models to the live database, so migrate up first, then change the model, then generate. Always read the generated file before committing it.

Enum values are never detected

Autogenerate sees new tables and columns. It does not see new values added to an existing enum type, and Twiga uses enums heavily for states, subjects and grade levels. Add them by hand:

op.execute("ALTER TYPE subjectname ADD VALUE IF NOT EXISTS 'chemistry'")

Never add an enum value and use it in the same migration

Alembic runs the whole upgrade in one transaction, and Postgres will not let a newly added enum value be used inside the transaction that added it. Split the schema change and the data change into two revisions.

Downgrades are mostly not real

Postgres cannot drop an enum value, so most of the enum revisions have an empty downgrade. One of them deletes rows on the way down. Treat downgrade as a development convenience, not a production rollback plan.

Loading data

A fresh database has no subjects, no classes and nothing to retrieve from. The seed script gives you a working one.

uv run python -m scripts.database.seed \
  --create --sample-data --vector-data chunks_multilingual.json

The three flags do different jobs and are usually used together.

Flag	Effect
`--create`	Drops every table and enum, enables the pgvector extension, then runs all migrations. Completely destructive.
`--sample-data`	Creates one subject, one class and one textbook resource: Geography, Form 2. No users.
`--vector-data FILE`	Loads 726 pre-embedded chunks of that textbook. Requires `--sample-data` to have run.

Three chunk files ship with the repository:

chunks_multilingual.json and chunks_BAAI.json, both 1024 dimensions, both fine.
chunks_OPENAI.json, 1536 dimensions, which will not load against the default column.

pgvector is enabled by the seed script, not by a migration

The initial migration creates a vector column but never creates the extension. On a brand new database, running alembic upgrade head on its own fails with type vector does not exist. Either seed first, or run CREATE EXTENSION vector; yourself.

Your own textbook

make ingest-book filename=your_book.json

The file is read from scripts/assets/books/, a directory that does not exist until you create it. The JSON must already contain embeddings; ingestion does not compute them. Subject, class and resource rows are reused if they already exist, so re-running is safe.

Changing embedding model

Two scripts exist for this. One re-embeds a JSON file offline, the other re-embeds chunks already in the database. The second one copies the table before touching anything, so the original survives a mistake. Both support --dry-run.

Remember that a new model usually means a new dimension, which means editing the column definition and writing a migration to alter it.

Sharp edges

Alembic prints the full database URL, password included, on every invocation. Redact before pasting output into an issue.
seed.py --create wipes everything, including the Alembic version table. Never point it at a shared database.
alembic.ini contains sqlalchemy.url = placeholder on purpose. It is replaced at runtime from your settings, so editing it has no effect.
The revision chain is linear with a single head. If you see two heads, two branches added migrations in parallel and they need merging.
One migration file has a docstring naming the wrong parent. The down_revision value is the one Alembic reads and it is correct.
Nothing runs migrations on deploy. Neither the Dockerfile nor application startup applies them, despite what docs/en/MIGRATIONS.md suggests. Applying them in production is a manual step.