Database & Migrations

The backend stores all data in PostgreSQL with the pgvector extension for semantic search. The local dev stack (started by make backend-run-dev) runs a pg16 container with pgvector enabled.

Connection

Connection details come from the DB_* environment variables (see Configuration). The pool and migration runner live in internal/database. Data access is implemented in internal/repositories/postgres using the squirrel SQL builder — this is the only layer that issues SQL.

Migrations run automatically on boot

Migrations use golang-migrate and run automatically when the backend starts. There is no separate migrate command to run in normal operation: bring up a fresh database, start the backend, and the schema is created and brought up to date.

Migration files live in backend/migrations/.

File naming

Each migration is a numbered pair of .up.sql / .down.sql files:

NNN_descriptive_name.up.sql     # forward migration
NNN_descriptive_name.down.sql   # rollback

For example:

022_create_embeddings_table.up.sql
022_create_embeddings_table.down.sql
023_add_content_to_embeddings.up.sql
023_add_content_to_embeddings.down.sql

NNN is a zero-padded, strictly increasing sequence number. Every .up.sql must have a matching .down.sql.

pgvector & embeddings

Embeddings are stored in pgvector columns. The vector width is fixed at 1024 in code and locked to the column definition — it is not configurable and there is no environment variable for it. The 022_create_embeddings_table.up.sql migration enables the extension and creates the table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE embeddings (
    -- ...
    embedding vector(1024)
    -- ...
);

See Configuration → Embeddings for how the embedding pipeline and provider work.

Validating migrations

The CI and pre-commit hooks check that migrations are well-formed. Run the same check locally:

make backend-check-migrations

This detects duplicate migration numbers (two files claiming the same NNN), which would otherwise cause non-deterministic ordering.

Adding a migration

Pick the next sequence number (one higher than the current maximum).

Create both files:

touch backend/migrations/056_add_widgets_table.up.sql
touch backend/migrations/056_add_widgets_table.down.sql

Write the forward schema change in .up.sql and the exact rollback in .down.sql.
If you added or changed columns the API exposes, update the OpenAPI spec and regenerate — see API & OpenAPI and Code Generation.
Run make backend-check-migrations, then start the backend so the migration applies, and run the tests.