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 pg17 container with pgvector enabled, plus Mailpit and a local TEI embedding service (see Getting Started).

Connection

Connection details come from the database.* keys in config.yaml (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.

Postgres TLS is controlled by database.sslmode. Only two values are supported: disable (the default, no TLS) and require (encrypt without server-certificate verification). verify-ca / verify-full are not supported yet. Managed Postgres offerings that require TLS work with require.

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/.

The early history was squashed: 001_baseline is a pg_dump-style baseline replacing the original incremental migrations, and 002_consolidated squashes everything that accumulated after the backend-v0.2.0 release (memory lifecycle status, the OAuth Authorization Server tables). Release migrations shipped since: 003 (per-team embedding providers, v0.4.0), 004 (provider concurrency + model providers, v0.5.0), 005 (search and embedding enhancements, v0.6.0), 006_consolidated (RBAC foundation + resource comments, v0.7.0), 007 (blueprint sync, v0.8.0), 008 (attachment relative paths, v0.8.0), 009 (blueprint source content SHA, v0.8.0), 010 (typed resource relations, v0.8.0), and 011_consolidated (v0.9.0, squashing the post-v0.8.0 increments: user status, per-team search settings, per-team GitHub App configs, per-team email providers, and the removal of AI-tool hook ingestion, billing/subscriptions, and Firebase web push), and 012 (schedules, v0.10.0). A pre-existing pre-v0.3.0 database must be stamped to the matching version so the consolidated files are never re-run against a populated schema.

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

The current set (.up.sql shown; each has a matching .down.sql):

001_baseline.up.sql
002_consolidated.up.sql
003_per_team_embedding_providers.up.sql
004_provider_concurrency_and_model_providers.up.sql
005_search_and_embedding_enhancements.up.sql
006_consolidated.up.sql
007_blueprint_sync.up.sql
008_attachment_relative_path.up.sql
009_blueprint_source_content_sha.up.sql
010_resource_relations.up.sql
011_consolidated.up.sql
012_schedules.up.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. The 001_baseline.up.sql migration enables the extension and creates the table:

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

CREATE TABLE public.embeddings (
    -- ...
    vector_embeddings public.vector(1024) NOT NULL,
    -- ...
);

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

Keyword search and pg_trgm

Keyword (full-text) search is the fallback when a team has no embedding provider. Since v0.6.0 (005_search_and_embedding_enhancements), it runs three passes:

Strict websearch_to_tsquery (AND semantics)
Relaxed OR-semantics rewrite when the strict pass returns nothing
A pg_trgm typo-tolerance pass against titles (word similarity, GIN gin_trgm_ops indexes on prompt, artifact, blueprint, and memory titles)

Migration 005 enables the pg_trgm extension and creates the trigram indexes. It also adds per-provider query_prefix / document_prefix columns to embedding_providers for asymmetric embedding models.

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. CI additionally runs the check as a PR-only migrations job in merge mode against the branch the PR targets (main normally, the release/X.Y.x line for a backport), which catches two parallel PRs claiming the same number, the collision local runs cannot see. The migration-renumbering PR label is the escape hatch for deliberate renumberings such as post-release consolidations.

Adding a migration

Pick the next sequence number (one higher than the current maximum; with 012 as the newest shipped migration, the next one is 013).

Create both files:

touch backend/migrations/013_add_widgets_table.up.sql
touch backend/migrations/013_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.