Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

3.2 KB
Raw Blame

DB Migrations (server/scripts/db-migrations/)

Ordered, versioned PostgreSQL schema-DELTA migrations (#1029). Run at boot by server/scripts/run-db-migrations.js after ensureSchema() has created the base schema.

When to use this vs. ensureSchema()

Change Where it goes
New table / new column / new index (additive, fresh-install) ensureSchema() in server/lib/db.js and server/scripts/init-db.sql (parity-locked by db.ddlParity.test.js)
Column rename, type change, data transform, embedding-dimension change, dropping a column A migration file here

ensureSchema()'s CREATE/ADD IF NOT EXISTS gates can only express additive fresh-install schema. Anything that has to transform an existing install — something that isn't idempotent as a bare IF NOT EXISTS statement — belongs here so it runs exactly once, in order, and is recorded.

File format

Files are applied in lexical filename order, so prefix with a zero-padded sequence: 001-, 002-, … Each filename is the migration id recorded in the schema_migrations table — never rename or reuse an id once shipped.

Two formats are supported:

.sql

Raw SQL, run verbatim against the transaction client. Use for pure DDL deltas.

-- 001-rename-foo-to-bar.sql
ALTER TABLE widgets RENAME COLUMN foo TO bar;

.js

ESM module exporting async function up(client) (or a default object with an up method). Receives the pg transaction client so you can query + mutate in the same transaction — use this for data transforms, conditional logic, or embedding-dimension changes.

// 002-backfill-bar.js
export async function up(client) {
  const { rows } = await client.query('SELECT id, legacy FROM widgets WHERE bar IS NULL');
  for (const row of rows) {
    await client.query('UPDATE widgets SET bar = $1 WHERE id = $2', [derive(row.legacy), row.id]);
  }
}

Guarantees

  • Atomic per migration. Each migration's statements AND its INSERT INTO schema_migrations share one transaction. A failure rolls back both — the migration is not marked applied, boot aborts loudly, and the next boot retries it. Nothing half-applies.
  • Run once, in order. Already-applied ids (tracked in schema_migrations) are skipped. Idempotent on re-run.
  • Naming. _-prefixed files and *.test.js files are ignored (shared helpers / co-located tests).

Conventions

  • Migrations are forward-only (no down()). PortOS rolls forward.
  • Keep each migration focused on one logical change.
  • Prefer .sql for plain DDL; reach for .js only when you need to read rows, branch, or transform data.

The CoS feedback reference migration is input-gated: it derives rows from the install's existing state.json and therefore has no data.reference seed. A seed would be copied before the migration and could overwrite the meaning of a user's existing state.

The CoS agent migrations follow the same input-gated shape: 020 imports the agents map from data/cos/state.json into cos_agents and verifies every record byte for byte before committing; 021, which runs only after 020 has committed, re-verifies, keeps the original file as state.json.imported, and removes the key. Neither ships a seed.