A local-first multi-agent app development platform, built on PenguinHarness
/**
* Ordered, versioned schema migrations.
*
* `schema.ts` declares the shape a FRESH database is created with. It cannot express a
* CHANGE — `CREATE TABLE IF NOT EXISTS` only ever says "should exist", so re-running it
* converges a database toward the current declaration without ever knowing, or recording,
* which state it came from. That is why a build could not tell a 0.2.4 database from a
* 0.2.7 one, and why the only safe change was an additive one.
*
* A migration says what CHANGED, runs once, and leaves the database stamped with how far
* it has come (`PRAGMA user_version`). Two rules make that stamp trustworthy:
*
* FROZEN DDL. A migration spells out its own SQL and never imports SCHEMA_SQL. Referring
* to the live declaration would make an old migration silently mean something new every
* time the schema moves, which is the property that makes migrations auditable at all.
*
* ONE WAY, IN ORDER. Versions are contiguous from 1 and never renumbered or rewritten
* once released — a database that already stamped version N will never run N again, so
* editing N only changes what NEW databases get, and silently forks the two.
*
* `swapSafe` is this codebase's extra axis, and it exists because of hot updates. A
* pushed platform boots against a live database and is ROLLED BACK to its predecessor if
* it fails; the predecessor then runs on whatever the migration already did. Additive
* work survives that (the older build does not know the new table, so it never touches
* it) — narrowing work does not. Anything that drops, retypes, constrains, or reshapes
* is `swapSafe: false` and must be refused on the swap path rather than half-applied
* (see `migrate`'s `swapPath` option).
*
* DOWN, AND WHO DOES NOT CALL IT. Every migration declares an undo — or declares `null`
* to say it has none, which is a decision the author has to make rather than omit. What
* `down` is NOT is the hot-update rollback mechanism. When a pushed platform fails to boot
* the runtime reverts to its PREDECESSOR and the schema stays where the migration left it:
* undoing DDL inside a process whose boot just failed would run destructive statements
* against a half-known state, and `swapSafe` exists precisely so that not undoing is safe —
* the predecessor does not know the new table or column, so it never touches it. `down` is
* an operator's tool, called deliberately (`rollbackTo`), never by the swap.
*
* It is also LOSSY by nature: undoing "add a table" drops that table with its rows in it.
* Each `down` below names what its rows were.
*
* ADOPTION. Databases created before this file existed are all stamped 0 while sitting in
* genuinely different shapes, so a migration must tolerate finding its work already done.
* That is not only a version-1 concern: `schema.ts` still DECLARES the current shape in
* full and `openDatabase` still runs its ensureColumn list, so a database can arrive at a
* migration with that migration's work already applied by the declarative track. Until
* SCHEMA_SQL is frozen to a baseline and every later change is a migration, every
* migration here stays idempotent — `IF NOT EXISTS`, or the same `ensureColumn` guard the
* declarative track uses. Once that double track is gone, a bare `CREATE TABLE` that
* fails loudly becomes the point.
*/
import type { DatabaseSync } from "node:sqlite";
import { ensureColumn } from "./database.js";
export interface Migration {
/** Contiguous from 1. Never renumbered, never edited once released. */
version: number;
/** Kebab-case, names the change — read in logs and in the stamp's history. */
name: string;
/**
* May this run while a pushed platform boots? True only for strictly additive work,
* which a rollback to the previous platform survives. See the module doc.
*/
swapSafe: boolean;
/** Applied inside a transaction; throw to abort and leave the version unchanged. */
up: (db: DatabaseSync) => void;
/**
* Undoes `up`, or null when this migration cannot be undone — required, not optional, so
* "there is no undo" is something the author states rather than forgets.
*
* Never called by the swap path (see the module doc): a failed boot reverts the platform,
* not the schema. Reached only through `rollbackTo`, and destructive by nature — dropping
* a table takes its rows with it.
*/
down: ((db: DatabaseSync) => void) | null;
}
export const MIGRATIONS: readonly Migration[] = [
{
version: 1,
name: "messaging-bindings",
// Purely additive: one new table plus three indexes, no column of any existing table
// touched. A platform that rolls back to a predecessor without messaging simply never
// queries them.
swapSafe: true,
up(db) {
// 0.2.4 → 0.2.7. Frozen copy of the DDL as of 0.2.7; do not re-derive from schema.ts.
// IF NOT EXISTS only because version 1 adopts unstamped databases, which may already
// be at 0.2.7 (see the module doc's ADOPTION note).
db.exec(`
CREATE TABLE IF NOT EXISTS messaging_bindings (
session_id TEXT NOT NULL,
channel TEXT NOT NULL,
account_id TEXT NOT NULL,
config_json TEXT NOT NULL,
enabled INTEGER NOT NULL DEFAULT 0,
line_per_message INTEGER NOT NULL DEFAULT 0,
last_chat_id TEXT,
last_chat_is_direct INTEGER NOT NULL DEFAULT 1,
last_inbound_message_id TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
PRIMARY KEY (session_id, channel)
);
CREATE INDEX IF NOT EXISTS idx_messaging_by_account ON messaging_bindings(channel, account_id);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires ON auth_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_user ON auth_sessions(user_id);
`);
},
// LOSES every messaging binding: the channel credentials, the enabled flag and the
// last-chat memory all live in the table this drops. The auth_sessions indexes are
// derived and cost nothing to lose.
down(db) {
db.exec(`
DROP INDEX IF EXISTS idx_auth_sessions_user;
DROP INDEX IF EXISTS idx_auth_sessions_expires;
DROP INDEX IF EXISTS idx_messaging_by_account;
DROP TABLE IF EXISTS messaging_bindings;
`);
},
},
{
version: 2,
name: "messaging-delivery-flags",
// Two columns with defaults on an existing table: a rollback to a predecessor that
// does not know them leaves them at their defaults and reads nothing.
swapSafe: true,
up(db) {
// 0.2.7 → 0.2.8. ensureColumn rather than a bare ALTER because the declarative
// track may already have added these (see the module doc's ADOPTION note).
ensureColumn(db, "messaging_bindings", "final_reply_only", "INTEGER NOT NULL DEFAULT 0");
ensureColumn(db, "messaging_bindings", "render_markdown", "INTEGER NOT NULL DEFAULT 1");
},
// LOSES both delivery preferences on every binding; the bindings themselves survive.
// Dropped in reverse order for symmetry with `up`. Neither column is indexed, which is
// what lets SQLite drop them at all.
down(db) {
db.exec("ALTER TABLE messaging_bindings DROP COLUMN render_markdown");
db.exec("ALTER TABLE messaging_bindings DROP COLUMN final_reply_only");
},
},
{
version: 3,
name: "drop-goal-state",
// Narrowing: drops a table. A pushed platform rolled back to 0.2.9 mid-process would
// prepare its goal statements against a table that is gone (its declarative track only
// runs at the runtime's own open, never at a platform boot), so this is the first
// restart-only migration: refused on the swap path, applied by the runtime's open.
swapSafe: false,
up(db) {
// 0.2.9 → 0.2.10. goal_state held goal mode's run state, one row per goal run, read
// back only for the chat page's goal banner; the goal plugin's GOAL.json in the
// Session scratchpad is that record now (see runtime/goal-events.ts). IF EXISTS only
// because a database this build created never had the table.
db.exec(`
DROP INDEX IF EXISTS idx_goal_session;
DROP TABLE IF EXISTS goal_state;
`);
},
// Recreates the table exactly as 0.2.9 declared it — EMPTY. LOSES every goal run ever
// recorded (objective, status, budget, used, rounds per run): the rows only ever fed the
// banner of a finished goal, and a build with this migration reads the goal file instead.
down(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS goal_state (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL,
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
objective TEXT NOT NULL,
status TEXT NOT NULL,
budget INTEGER NOT NULL,
used INTEGER NOT NULL DEFAULT 0,
rounds INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_goal_session ON goal_state(session_id);
`);
},
},
{
version: 4,
name: "machines",
// Three new tables, nothing existing touched: a platform rolled back to one without
// machines never queries them. Swap-safe on its own; a database still behind
// drop-goal-state is refused whole by that one first, which is the rule.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of the machines feature; do not re-derive from schema.ts.
// IF NOT EXISTS because the declarative track may already have created them (ADOPTION).
db.exec(`
CREATE TABLE IF NOT EXISTS machine (
singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
machine_id TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS machines (
address TEXT PRIMARY KEY,
machine_id TEXT,
version TEXT,
installed_at TEXT,
session_pid INTEGER,
remote_port INTEGER,
platform TEXT
);
CREATE TABLE IF NOT EXISTS machine_project (
project_id TEXT PRIMARY KEY REFERENCES projects(project_id) ON DELETE CASCADE,
addresses TEXT NOT NULL
);
`);
},
// LOSES this server's own machine id (every stored reference to it on other machines
// then points at nothing), what was installed where, the sessions held, and which
// machines each Project used.
down(db) {
db.exec(`
DROP TABLE IF EXISTS machine_project;
DROP TABLE IF EXISTS machines;
DROP TABLE IF EXISTS machine;
`);
},
},
{
version: 5,
name: "user-profile",
// Two nullable columns on `users`, no default and nothing existing rewritten: a platform
// rolled back to a predecessor that does not know them never selects or writes them, and
// every existing account simply reads NULL — no profile yet.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of the user-profile feature; do not re-derive from schema.ts.
// SQLite has no ADD COLUMN IF NOT EXISTS, and the declarative track may already have
// added both (ADOPTION), so each one goes through the same table_info guard the
// declarative track uses.
ensureColumn(db, "users", "display_name", "TEXT");
ensureColumn(db, "users", "avatar", "TEXT");
},
// LOSES every stored nickname and avatar: both live entirely in the two columns this
// drops, and nothing else on disk holds a copy. Dropped in reverse order for symmetry
// with `up`; neither column is indexed, which is what lets SQLite drop them at all.
down(db) {
const cols = db.prepare("PRAGMA table_info(users)").all() as { name: string }[];
const has = (name: string): boolean => cols.some((c) => c.name === name);
if (has("avatar")) db.exec("ALTER TABLE users DROP COLUMN avatar");
if (has("display_name")) db.exec("ALTER TABLE users DROP COLUMN display_name");
},
},
{
version: 6,
name: "company-mode-org-caches",
// Additive: seven new tables for company mode (organizations of Agents), no change to any
// existing table. Every row is either rebuildable from the organization's files (desks.toml,
// the tickets' Sessions headers, the calendar files, usage records) or a user's own read
// cursor; a predecessor build never touches them.
swapSafe: true,
up(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS org_sessions ( -- DERIVED CACHE (company mode): desk sessions, rebuilt from each organization's desks.toml (current + previous); trigger_hop is chat-chain accounting and reads 0 after a rebuild
session_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
current INTEGER NOT NULL DEFAULT 1,
trigger_hop INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_org_sessions_org ON org_sessions(project_id, org_id);
CREATE TABLE IF NOT EXISTS org_ticket_sessions ( -- DERIVED CACHE (company mode): ticket <-> contributing session, rebuilt from each ticket's Sessions header
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
session_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
trigger_hop INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, ticket_id, session_id)
);
CREATE INDEX IF NOT EXISTS idx_org_ticket_sessions_session ON org_ticket_sessions(session_id);
CREATE TABLE IF NOT EXISTS org_calendar_state ( -- calendar run state (company mode; the files are declarative intent), same rules as schedule_state
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
name TEXT NOT NULL,
start_at_ms INTEGER NOT NULL,
def_hash TEXT NOT NULL,
last_slot_ms INTEGER,
last_fired_at TEXT,
fired_once INTEGER NOT NULL DEFAULT 0,
missed INTEGER NOT NULL DEFAULT 0,
invalid_reason TEXT,
last_outcome TEXT,
PRIMARY KEY (project_id, org_id, agent_id, name)
);
CREATE TABLE IF NOT EXISTS org_ticket_state ( -- DERIVED CACHE (company mode): the last (status, owner, blocked) seen per ticket, so a change is notified once; rebuilt silently from the ticket files
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
status TEXT NOT NULL,
owner TEXT NOT NULL DEFAULT '',
blocked TEXT NOT NULL DEFAULT '',
blocked_by TEXT NOT NULL DEFAULT '',
PRIMARY KEY (project_id, org_id, ticket_id)
);
CREATE TABLE IF NOT EXISTS org_chat_state ( -- DERIVED CACHE (company mode): tail-scan byte cursor per chat day file
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
date TEXT NOT NULL,
offset_bytes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, date)
);
CREATE TABLE IF NOT EXISTS org_chat_reads ( -- user data (company mode): each user's read cursor in an organization's chat
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
user_id TEXT NOT NULL,
last_read_id TEXT NOT NULL,
PRIMARY KEY (project_id, org_id, user_id)
);
CREATE TABLE IF NOT EXISTS org_budget_state ( -- DERIVED CACHE (company mode): warn / pause marks per employee and period, recomputed from usage and the chart's budgets
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
period TEXT NOT NULL,
warned_at TEXT,
paused_at TEXT,
PRIMARY KEY (project_id, org_id, agent_id, period)
);
`);
},
// Drops the seven tables. LOSES: chat read cursors (each user's "read up to here" marks) and
// the run marks that stop a calendar slot, a ticket change or a budget alert from firing
// twice — a build with this migration re-registers everything from the files, without
// backfilling missed slots, and users see every chat message as unread once.
down(db) {
db.exec(`
DROP INDEX IF EXISTS idx_org_ticket_sessions_session;
DROP INDEX IF EXISTS idx_org_sessions_org;
DROP TABLE IF EXISTS org_budget_state;
DROP TABLE IF EXISTS org_chat_reads;
DROP TABLE IF EXISTS org_chat_state;
DROP TABLE IF EXISTS org_ticket_state;
DROP TABLE IF EXISTS org_calendar_state;
DROP TABLE IF EXISTS org_ticket_sessions;
DROP TABLE IF EXISTS org_sessions;
`);
},
},
{
version: 7,
name: "company-mode-channels",
// The organization's single group chat became channels: a message lives in
// `channels/<channel_id>/<date>.jsonl`, so the tail-scan cursor and each user's read
// cursor are per channel. `org_chat_state` / `org_chat_reads` are dropped for
// `org_channel_state` / `org_channel_reads`: the tables are renamed and the channel
// belongs in the primary key, neither of which SQLite does in place. Nothing is carried
// over: company mode is unreleased, and every row was either rebuildable from the files
// (the scan cursor) or one pass of "everything looks unread" (the read cursor). A
// predecessor build reads neither table's shape, only its own writes, so this is
// swap-safe.
swapSafe: true,
up(db) {
db.exec(`
DROP TABLE IF EXISTS org_chat_state;
DROP TABLE IF EXISTS org_chat_reads;
CREATE TABLE IF NOT EXISTS org_channel_state ( -- DERIVED CACHE (company mode): tail-scan byte cursor per channel and day file
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
date TEXT NOT NULL,
offset_bytes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, channel_id, date)
);
CREATE TABLE IF NOT EXISTS org_channel_reads ( -- user data (company mode): each user's read cursor in one channel
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
user_id TEXT NOT NULL,
last_read_id TEXT NOT NULL,
PRIMARY KEY (project_id, org_id, channel_id, user_id)
);
`);
},
// Recreates the two tables under their old names, exactly as migration 6 declared them —
// EMPTY. LOSES every scan cursor (re-derived by the next pass, which republishes the
// messages it re-reads) and every read cursor (users see the recent days as unread once).
down(db) {
db.exec(`
DROP TABLE IF EXISTS org_channel_state;
DROP TABLE IF EXISTS org_channel_reads;
CREATE TABLE IF NOT EXISTS org_chat_state ( -- DERIVED CACHE (company mode): tail-scan byte cursor per chat day file
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
date TEXT NOT NULL,
offset_bytes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, date)
);
CREATE TABLE IF NOT EXISTS org_chat_reads ( -- user data (company mode): each user's read cursor in an organization's chat
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
user_id TEXT NOT NULL,
last_read_id TEXT NOT NULL,
PRIMARY KEY (project_id, org_id, user_id)
);
`);
},
},
{
version: 8,
name: "company-mode-desk-notices",
// Additive: one new table and its index. A ticket change no longer starts a work run at
// the owner's desk; it is queued here and delivered inside the body of that employee's
// next calendar sweep. A predecessor build never reads the table, so a rollback survives
// it — with whatever rows are in it left undelivered.
swapSafe: true,
up(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS org_desk_notices ( -- DERIVED CACHE (company mode): ticket changes waiting for an employee's next calendar sweep; dropping it loses only the digests not delivered yet
seq INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
change TEXT NOT NULL,
at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_org_desk_notices_agent ON org_desk_notices(project_id, org_id, agent_id);
`);
},
// LOSES every queued notice: the ticket changes an employee has not been told about yet.
// The change itself is in the ticket file and its history either way; what
// goes is the "Since your last sweep" line that would have named it.
down(db) {
db.exec(`
DROP INDEX IF EXISTS idx_org_desk_notices_agent;
DROP TABLE IF EXISTS org_desk_notices;
`);
},
},
{
version: 9,
name: "model-promotions",
// Additive: one new table, nothing existing touched. A predecessor build never reads it
// and prices every row at the number in its Project file, so a rollback survives it.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of the model-promotions feature; do not re-derive from
// schema.ts. IF NOT EXISTS because the declarative track may already have created it
// (ADOPTION).
db.exec(`
CREATE TABLE IF NOT EXISTS model_promotions ( -- NOT a cache rebuildable from files: the only record of a Project row's running promotion (.project_config.toml keeps the list price), written by preset seeding at Project creation, "sync presets" and Penguin Go authorization / sync, and read by cost; lost rows price usage at list until the next sync writes them back
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
provider TEXT NOT NULL,
model_id TEXT NOT NULL,
discount REAL NOT NULL CHECK (discount > 0 AND discount < 1),
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, provider, model_id)
);
`);
},
// LOSES every stored promotion: no file holds a copy of the fractions, so usage is priced
// at the Project files' list prices until the next "sync presets" or Penguin Go sync
// writes them back.
down(db) {
db.exec("DROP TABLE IF EXISTS model_promotions");
},
},
{
version: 10,
name: "model-provider-auth-tokens",
// Additive: one new table, nothing existing touched. A predecessor build never reads it;
// the access token already written into .project_config.toml keeps working until it
// expires, so rollback only loses silent refresh state.
// KEEP until every supported upgrade starts from schema 10 or newer. The release that
// raises the minimum database baseline past 9 owns removing this migration block and its
// version-9 fixture; the table itself remains while provider token refresh uses it.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of the provider-auth-token feature; do not re-derive from
// schema.ts. IF NOT EXISTS because the declarative track may already have created it
// (ADOPTION).
db.exec(`
CREATE TABLE IF NOT EXISTS model_provider_auth_tokens ( -- Server-side OAuth refresh metadata for provider groups. The current request token remains in .project_config.toml as api_key; this table holds only the refresh material that must never be returned to the frontend or written into Project files.
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
provider TEXT NOT NULL,
refresh_token TEXT NOT NULL,
access_token_expires_at TEXT,
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, provider)
);
`);
},
// LOSES every stored refresh token and access-token expiry. Existing access tokens in
// Project files remain until they expire; users re-authorize to restore silent refresh.
down(db) {
db.exec("DROP TABLE IF EXISTS model_provider_auth_tokens");
},
},
{
version: 11,
name: "sessions-sandbox",
// The Session's own sandbox policy (JSON), snapshotted from the server's settings when
// the Session is created, so a later settings change leaves running Sessions alone.
// NULL = a row from before this column: it takes the settings in force at its next
// command and keeps them from then on. Swap-safe: a pushed platform boots against the
// runtime's already-open database, so the column has to arrive by a migration.
swapSafe: true,
up(db) {
ensureColumn(db, "sessions", "sandbox", "TEXT");
},
// Not a DROP: schema.ts declares the column, and dropping it would put every Session
// back under whatever the settings say.
down() {},
},
{
version: 12,
name: "machines-columns",
// A machines table can predate migration 4 — a data root that ran the machines line
// before it was released kept forwards, not sessions — and that migration's IF NOT
// EXISTS adopted it whatever its columns were, so the first connect failed on the row's
// insert. Adds what is missing, nullable; a platform rolled back to 4 declared these
// columns itself, so there is nothing for it to mind.
swapSafe: true,
up(db) {
ensureColumn(db, "machines", "session_pid", "INTEGER");
ensureColumn(db, "machines", "platform", "TEXT");
},
// Nothing to undo: what this added, 4 already declares, and a column it found in place
// was never this migration's to take away.
down() {},
},
{
version: 13,
name: "company-mode-desk-mentions",
// Additive: one new table. A channel mention no longer starts a work run of its own
// (queued in the desk session's memory when the desk was busy, and lost on a restart);
// it waits here until the desk is idle and is delivered with the others in one run.
// A predecessor build never reads the table, so a rollback survives it — with whatever
// rows are in it left undelivered.
swapSafe: true,
up(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS org_desk_mentions ( -- DERIVED CACHE (company mode): channel mentions waiting for an employee's desk to be idle; dropping it loses only the mentions not delivered yet (the messages stay in the channel files)
seq INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
date TEXT NOT NULL,
message_id TEXT NOT NULL,
hop INTEGER NOT NULL,
UNIQUE (project_id, org_id, agent_id, channel_id, message_id)
);
`);
},
// LOSES every mention not delivered yet: the message stays in its channel file, but no
// desk run will name it.
down(db) {
db.exec(`DROP TABLE IF EXISTS org_desk_mentions;`);
},
},
{
version: 14,
name: "usage-record-cost",
// Two columns with defaults on usage_records, nothing existing rewritten: a platform rolled
// back to a predecessor that does not know them keeps inserting rows without a cost, which
// simply read unsettled (cost_settled 0) and are settled by the next build that boots.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of fixing a request's cost at record time; do not re-derive
// from schema.ts. ensureColumn because the declarative track may already have added them
// (ADOPTION). The rows already in the table are costed by the startup settle
// (UsageService.settleUnsettledCosts), not here: pricing them needs each Project's config.
ensureColumn(db, "usage_records", "cost", "REAL");
ensureColumn(db, "usage_records", "cost_settled", "INTEGER NOT NULL DEFAULT 0");
},
// LOSES every fixed cost. A build with this migration adds the columns back unsettled and
// costs every row again at startup, at the prices the Projects store by then — so a price
// changed in between re-prices history, the one thing the columns exist to prevent.
// Dropped in reverse order for symmetry with `up`; neither column is indexed, which is what
// lets SQLite drop them at all.
down(db) {
const cols = db.prepare("PRAGMA table_info(usage_records)").all() as { name: string }[];
const has = (name: string): boolean => cols.some((c) => c.name === name);
if (has("cost_settled")) db.exec("ALTER TABLE usage_records DROP COLUMN cost_settled");
if (has("cost")) db.exec("ALTER TABLE usage_records DROP COLUMN cost");
},
},
{
version: 15,
name: "agent-api",
// Additive: two new tables and an index. The Agent API's per-Agent settings (the switch,
// keyless access, the approval mode API Sessions are created with) and its keys, stored
// hashed. A predecessor build never reads them and serves no /api/amsp route, so a
// rollback survives it: callers get 404 until a build that knows the tables is back.
swapSafe: true,
up(db) {
// Frozen copy of the DDL as of the Agent API feature; do not re-derive from schema.ts.
// IF NOT EXISTS because the declarative track may already have created them (ADOPTION).
db.exec(`
CREATE TABLE IF NOT EXISTS agent_api ( -- per-Agent public API settings (amsp/routes.ts); NOT rebuildable from files
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
enabled INTEGER NOT NULL DEFAULT 0,
open INTEGER NOT NULL DEFAULT 0,
approval_mode TEXT NOT NULL DEFAULT 'allow-all',
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, agent_id)
);
CREATE TABLE IF NOT EXISTS agent_api_keys ( -- one row per key the API tab or the CLI minted; the caller alone holds the key
key_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
token_hash TEXT NOT NULL UNIQUE,
prefix TEXT NOT NULL,
name TEXT NOT NULL,
created_by TEXT NOT NULL REFERENCES users(user_id),
created_at TEXT NOT NULL,
last_used_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_agent_api_keys_agent ON agent_api_keys(project_id, agent_id);
`);
},
// LOSES every Agent's API settings and every key: each exposed Agent reads as disabled
// again (404 to its callers), and every caller needs a newly created key once it is back
// on — no file holds a copy of either.
down(db) {
db.exec(`
DROP INDEX IF EXISTS idx_agent_api_keys_agent;
DROP TABLE IF EXISTS agent_api_keys;
DROP TABLE IF EXISTS agent_api;
`);
},
},
];
/** The highest version this build knows how to reach. */
export const LATEST_VERSION: number = MIGRATIONS.reduce((n, m) => Math.max(n, m.version), 0);
/** How far this database has been migrated. Unstamped (pre-migrations) databases read 0. */
export function schemaVersion(db: DatabaseSync): number {
const row = db.prepare("PRAGMA user_version").get() as { user_version: number } | undefined;
return row?.user_version ?? 0;
}
export class IrreversibleMigrationError extends Error {
constructor(readonly migration: Migration) {
super(
`migration ${migration.version} (${migration.name}) declares no down and cannot be rolled back. ` +
`Restore the database from a backup taken before it was applied.`,
);
this.name = "IrreversibleMigrationError";
}
}
export class RestartRequiredError extends Error {
constructor(readonly migration: Migration) {
super(
`migration ${migration.version} (${migration.name}) is restart-only and cannot be applied ` +
`while a pushed platform boots: it is not safe to leave behind if this boot is rolled back. ` +
`Restart the runtime on a build that carries it, then push again.`,
);
this.name = "RestartRequiredError";
}
}
/**
* Applies every migration this database has not reached yet, in order.
*
* Each migration and its version stamp commit together, so an interrupted run leaves the
* database at the last version that fully applied — never half-migrated. Already-current
* databases do no work and touch nothing.
*
* `swapPath` marks the caller as a booting pushed platform: the first pending migration
* that is not `swapSafe` throws RestartRequiredError BEFORE anything is applied, so the
* push is refused whole rather than partially landed.
*/
export function migrate(
db: DatabaseSync,
{ swapPath = false }: { swapPath?: boolean } = {},
): { from: number; to: number; applied: readonly string[] } {
const from = schemaVersion(db);
const pending = [...MIGRATIONS]
.sort((a, b) => a.version - b.version)
.filter((m) => m.version > from);
if (swapPath) {
const blocked = pending.find((m) => !m.swapSafe);
if (blocked) throw new RestartRequiredError(blocked);
}
const applied: string[] = [];
for (const m of pending) {
db.exec("BEGIN");
try {
m.up(db);
// PRAGMA takes no bound parameter; the value is this file's own integer literal.
db.exec(`PRAGMA user_version = ${m.version}`);
db.exec("COMMIT");
} catch (err) {
db.exec("ROLLBACK");
throw new Error(`migration ${m.version} (${m.name}) failed: ${String(err)}`, { cause: err });
}
applied.push(m.name);
}
return { from, to: schemaVersion(db), applied };
}
/**
* Reverts the database DOWN to `targetVersion`, newest migration first.
*
* An operator's tool, and a destructive one — see each migration's `down` for what its rows
* were. Deliberately not reachable from the swap path: a failed platform boot reverts the
* PLATFORM, never the schema (module doc).
*
* Refused whole, before anything runs, if any migration in range declares no `down`: a
* partial rollback would leave the database at a version whose meaning nobody wrote down.
* Each `down` commits with its version stamp, so an interrupted run stops at a real version.
*/
export function rollbackTo(
db: DatabaseSync,
targetVersion: number,
): { from: number; to: number; reverted: readonly string[] } {
const from = schemaVersion(db);
if (targetVersion < 0) throw new Error(`target version ${targetVersion} is negative`);
if (targetVersion > from) {
throw new Error(`database is at version ${from}, which is already below ${targetVersion}`);
}
const toRevert = [...MIGRATIONS]
.filter((m) => m.version > targetVersion && m.version <= from)
.sort((a, b) => b.version - a.version);
const blocked = toRevert.find((m) => m.down === null);
if (blocked) throw new IrreversibleMigrationError(blocked);
const reverted: string[] = [];
for (const m of toRevert) {
db.exec("BEGIN");
try {
m.down!(db);
// Versions are contiguous, so the predecessor of `m` is exactly m.version - 1.
db.exec(`PRAGMA user_version = ${m.version - 1}`);
db.exec("COMMIT");
} catch (err) {
db.exec("ROLLBACK");
throw new Error(`rollback of migration ${m.version} (${m.name}) failed: ${String(err)}`, {
cause: err,
});
}
reverted.push(m.name);
}
return { from, to: schemaVersion(db), reverted };
}