Database and migrations

This page explains how the HostSSH control plane connects to Postgres and how its schema evolves: a one-time SQL baseline applied at go-live, plus a boot-time, forward-only migration runner that converges the schema on every deploy. It also covers the least-privilege role model that keeps the app's runtime credential from being able to rewrite the schema.

For terse operator runbooks (exact SSH commands, the fleet IP/container facts, and a gotchas table), see the skill reference: database-migrations-roles.md. This doc is the conceptual companion — read it once, then keep the runbook handy for incidents.

Where the database lives

The control plane is a Next.js 16 app built with output: 'standalone' and deployed to Coolify on the app box (31.220.104.207) via git push (Coolify auto-deploys on push). Postgres (pgvector, pg18) runs on a separate data box (31.220.104.211) inside the canonical container t6cr90jl6qq19vuz7v3qv1fy. The app reaches it over a WireGuard + socat bridge at 10.10.0.2:5432, database hostssh.

Two distinct roles connect to that database:

• hostssh — the app's runtime role. DML only (SELECT/INSERT/UPDATE/DELETE). This is what DATABASE_URL points at.
• hostssh_migrator — owns the schema and runs all DDL. The migration runner uses it via MIGRATIONS_DATABASE_URL.

The role split is covered in detail under Least-privilege roles below.

The connection layer

control-plane/web/lib/db.ts is a thin, server-only Postgres client built on the postgres (porsager) driver. It is dual-backend and env-gated: behavior depends entirely on whether DATABASE_URL is set.

• useDb = Boolean(process.env.DATABASE_URL). When it's unset, the entire app runs on an in-memory mock — so local development, demos, and the marketing/sign-in surface need no database at all.
• When set, the client is a pool ({ max: 5, idle_timeout: 20 }) cached on globalThis.__hsSql, so Hot Module Reload and multiple imports share a single pool.
• db() returns the client and throws 'DATABASE_URL is not set' if it isn't configured. Every call site must guard with useDb first.

This pool always uses DATABASE_URL (the hostssh DML role). It is deliberately not the connection that runs migrations — the runner opens its own dedicated, privileged connection (see below).

Two schema mechanisms, and why

Schema lives in two places that serve different lifecycle stages:

	Baseline	Migrations
Location	control-plane/db/*.sql	control-plane/web/lib/migrations/*.ts
Format	Idempotent .sql files	Numbered TypeScript modules
When applied	Once at go-live, out-of-band	Automatically on every boot/deploy
Applied by	scripts/apply-schemas.sh (or SSH + psql)	instrumentation.register()

The reason migrations are embedded TypeScript and not .sql files is the standalone build. The control plane ships as a Next.js output: 'standalone' container whose build context is control-plane/web — so control-plane/db/*.sql is not in the image at build or runtime. Anything that needs to run automatically on deploy must live in the JavaScript module graph, where Next's dependency tracing bundles it into the standalone server. The .sql baseline establishes the initial schema once; everything after is a numbered migration.

The baseline (control-plane/db/*.sql)

These 15 files (schema-auth.sql, schema-accounts.sql, schema-platform.sql, schema-audit-events.sql, and so on) define the go-live schema. Every file is fully idempotent (CREATE/ALTER ... IF NOT EXISTS), so re-running is harmless.

scripts/apply-schemas.sh applies them in dependency order rather than alphabetically — schema-auth.sql (which creates admins) must run before schema-accounts.sql, whose early_access_applications.reviewed_by foreign-keys admins. The explicit order is auth accounts billing brands connections dns-monitors jobs platform support telemetry tenants, after which any unlisted schema-*.sql is appended. Each file runs under psql -v ON_ERROR_STOP=1.

The exact apply commands (local against the bridge, and the SSH + docker exec form for a single file) live in the skill runbook.

The migration runner (control-plane/web/lib/migrations/)

Forward-only, numbered, embedded migrations, auto-applied on boot. The module is split for testability:

File	Role
core.ts	Pure logic — Migration/MigrationStatus types, checksum(), planMigrations(), statusFrom(), validateMigrations(). No DB or filesystem access, so it's directly unit-tested (core.test.ts).
list.ts	The ordered migrations[] array; runs validateMigrations() at module load.
runner.ts	runMigrations() (applies pending migrations) and migrationStatus() (read-only status).
NNNN-name.ts	One migration each — e.g. 0001-audit-events-indexes.ts, 0002-slots.ts.

How a migration is applied

runMigrations() (in runner.ts) does the following:

1. Picks the connection URL: MIGRATIONS_DATABASE_URL || DATABASE_URL. It prefers the privileged migrator role and falls back to the app role. If neither is set, it returns NOT_CONFIGURED and does nothing.
2. Opens a dedicated single-use pool ({ max: 1, idle_timeout: 20 }). max: 1 pins BEGIN/DDL/COMMIT and the advisory-lock acquire/release to one session, and end() in a finally guarantees that session — and any lock it holds — is destroyed on exit, even if the unlock query itself fails. No leaked lock.
3. Takes an advisory lock: SET lock_timeout = '30s', then pg_advisory_lock(4927411::bigint). This serializes migrations across replicas and processes; the bounded lock_timeout means a stuck peer surfaces as a logged error instead of an unbounded hang.
4. Ensures the ledger: CREATE TABLE IF NOT EXISTS schema_migrations (...).
5. Reads recorded checksums under the lock (a peer may have applied while this process waited), then planMigrations() splits the bundled list into pending / applied / drifted.
6. Applies each pending migration (applyOne): BEGIN (unless transactional: false) → run the SQL → INSERT INTO schema_migrations (id, checksum) ... ON CONFLICT (id) DO NOTHING → COMMIT. The DDL and its ledger row commit or roll back together.
7. Reports drift: any already-applied migration whose source checksum no longer matches is logged [migrate][FATAL] and skipped — never silently re-run.

Wiring: instrumentation.ts

register() runs only under the Node.js runtime (NEXT_RUNTIME === 'nodejs'). In production it validates required env (AUTH_SECRET, license keys), then — if DATABASE_URL is set — fires runMigrations() detached (void (async () => …)()). It is detached deliberately: Next awaits register() before serving any request, so awaiting a slow migration would block readiness and risk a probe-timeout crash-loop. The runner never throws into register() (the marketing and sign-in surfaces must stay up regardless); failures are logged and surfaced through /healthz.

How to add a migration

Adding a migration is three steps: write a numbered .ts file, wire it into list.ts, and push.

1. Create control-plane/web/lib/migrations/NNNN-name.ts:

import type { Migration } from './core'

export const migration: Migration = {
  id: '0003_widgets',
  sql: `
    CREATE TABLE IF NOT EXISTS widgets (
      id         text        PRIMARY KEY,
      name       text        NOT NULL,
      created_at timestamptz NOT NULL DEFAULT now()
    );
    CREATE INDEX IF NOT EXISTS idx_widgets_name ON widgets (name);
  `,
}

Rules for the migration itself:

• id must be stable, ascending, and unique (e.g. 0003_widgets). Never edit it once shipped — and never edit the sql of a shipped migration either. Either change registers as drift. To change something, append a new migration.
• sql should be idempotent (IF NOT EXISTS) so a manual or duplicate apply is harmless. It may contain multiple statements.
• No transaction control. Do not put BEGIN/COMMIT/ROLLBACK/SAVEPOINT in your SQL — the runner owns the transaction. validateMigrations() rejects them. (The check only matches transaction keywords at statement start, so PL/pgSQL $$ BEGIN ... END $$ function bodies are fine.)
• transactional: false is for DDL that cannot run inside a transaction — for example CREATE INDEX CONCURRENTLY. Such a migration cannot be rolled back mid-way, so it must be individually idempotent.

2. Register it in control-plane/web/lib/migrations/list.ts — import and append to the array (never reorder or remove existing entries):

import { migration as m0001 } from './0001-audit-events-indexes'
import { migration as m0002 } from './0002-slots'
import { migration as m0003 } from './0003-widgets'

export const migrations: Migration[] = [m0001, m0002, m0003]

validateMigrations(migrations) runs at module load, so an out-of-order id, a duplicate, or stray transaction control fails the build/boot loudly rather than corrupting the ledger at runtime.

3. Push. Coolify auto-deploys, and instrumentation.register() applies the pending migration on boot. Confirm convergence on /healthz.

Note on checksums and drift: checksum() collapses all runs of whitespace to a single space before hashing (SHA-256), so reindenting a migration or an LF↔CRLF checkout does not register as drift. Only a real change to the SQL content does. (.gitattributes pins eol=lf as a second line of defense.) The existing 0002-slots.ts migration is a good template for net-new tables — see the Slots model for what it backs.

The ledger and /healthz

The runner records every applied migration in schema_migrations:

schema_migrations (
  id         text        PRIMARY KEY,
  checksum   text        NOT NULL,
  applied_at timestamptz NOT NULL DEFAULT now()
)

GET /healthz (app/healthz/route.ts, Node runtime, force-dynamic) returns { ok, checks } with a 200 or 503. The relevant checks:

Check	Logic
db	SELECT 1; unreachable → 503.
schema	Required baseline tables present (admins, agents, jobs, deployments, chat_conversations, tenants). Any missing → 503 — catches a partial or unapplied baseline that SELECT 1 alone would miss.
migrations	From migrationStatus() (read-only, shared pool, no lock): checksum drift → 503; pending > 0 → 503; otherwise reports applied/total and the current frontier. A missing ledger table is treated as all-pending.

migrationStatus() reports current as the last id of the contiguous applied prefix — it stops at the first pending or drifted migration, so a later-applied id can never overstate how far the schema has actually converged. signingKey and licenseAllowlist checks are warnings only and never flip ok.

So the operational loop is: push → Coolify deploys → boot applies pending migrations → /healthz shows migrations: ok. If it shows pending: N after a deploy, the runner hasn't converged — almost always the ownership blocker described next.

Least-privilege roles

DDL in Postgres requires ownership of the target table. The control plane's DATABASE_URL credential is internet-facing, and we do not want a leak of it to be able to rewrite or drop the schema — only to read and write rows. So privileges are split (this is "Model B"), set up by scripts/setup-db-roles.sql:

• hostssh_migrator owns the public schema and every object in it. It is the only role that runs DDL, and only the migration runner uses it, via MIGRATIONS_DATABASE_URL.
• hostssh is the app runtime role with DML only. REVOKE CREATE ON SCHEMA public removes its ability to add objects — a credential leak can read/write rows but cannot CREATE/ALTER/DROP.

setup-db-roles.sql is idempotent and runs as a superuser (gmadmin). It:

1. Creates both roles if missing (with LOGIN; it never resets an existing password).
2. Reassigns ownership of every table/sequence/view not already migrator-owned to hostssh_migrator — sweeping up the gmadmin-created baseline tables and the schema_migrations table the app role created on its first boot.
3. Grants the migrator USAGE, CREATE ON SCHEMA public; grants hostssh DML on all existing objects; and uses ALTER DEFAULT PRIVILEGES so every future object a migration creates is automatically DML-accessible to the app role — no per-migration GRANT bookkeeping.
4. Finishes with a SELECT that verifies nothing in public is owned by a non-migrator role.

The classic ownership failure

The baseline tables are created by gmadmin, so the app role hostssh does not own them. If migrations run as hostssh, a DDL migration like 0001's CREATE INDEX fails with 42501 ("must be owner of table audit_events"). The runner annotates this error with a hint pointing at setup-db-roles.sql, and a 42P01 undefined-table error with a hint pointing at the baseline. The fix is to run setup-db-roles.sql as gmadmin, set the migrator password out of band, point MIGRATIONS_DATABASE_URL at the migrator role in Coolify, and redeploy. Exact commands are in the skill runbook.

Note that net-new tables (like 0002-slots's racks and placements) can be created by the app role itself, since it owns what it creates — those apply cleanly even before the role split lands. It's DDL against baseline-owned tables that requires the migrator.

Related

• API reference — the control-plane API, including zod request-body validation (lib/api/).
• Slots model — what migration 0002 backs.
• Skill runbooks: database-migrations-roles.md, deploy-and-cd.md, operations-and-process.md.