Skip to main content

​
Migrations Catalog

Crewship runs Go-only migrations against SQLite. The full ordered list lives in internal/database/migrate.go (var migrations), and they apply in version order at startup, tracked in the _migrations table for idempotency. This page is a flat reference for the recent set — useful when reading server logs, restoring from a backup that pre-dates a feature, or auditing what shipped between two releases. For older migrations (v1–v49) read migrate.go directly; this catalog focuses on the Crew Journal era and onward.
Never run prisma migrate. Prisma is for TypeScript type generation only (pnpm db:generate). Database schema is exclusively the responsibility of the Go migrations runner.

​
v50–v53 — Backup & Crew Journal foundations

After v52, journal_entries is the canonical audit stream. After v53, replay/regression analytics have a durable home.

​
v54–v55 — Memory uplift

These two PRs together delivered the Episodic memory uplift: hybrid retrieval (RRF over dense + BM25), archive layer, relation graph, and the health dashboard. The ?q= parameter on the journal API is backed by the FTS5 table from v55.

​
v56–v59 — Chat UI overhaul

After this batch the chat surface has session history, attachments, reactions, edit-and-resend branches, per-user preferences, and origin tagging. See Chat & Sessions.

​
v60–v61 — Unified journal / Runs SSOT

After v61, journal_entries is the single source of truth for runs. journal.ListRuns reconstructs the row shape via aggregation; the legacy table is gone but its content survived in agent_runs_archive for forensic reads. The five new entry types (run.started, run.completed, run.failed, run.cancelled, run.timeout) shipped with the same PR.

​
v62 — Paymaster billing modes

PR #232 originally numbered this migration v60. The merge with PR #234 (which had taken v60 + v61 for the unified journal) renumbered it to v62 — see the Renumbered from v60 to v62 comment in migrate.go. Documentation that pre-dates the merge may still mention v60; the canonical number is v62.
After v62, cost_ledger distinguishes metered API-key calls from flat-rate subscription calls and snapshots the rate card per row. See Paymaster.

​
v63–v99 — gap (read migrate.go directly)

Migrations between v63 and v99 covered routine maintenance, manifest layer expansion, credential vault iterations, eval-runs widening, and the CLI token tier refresh. Read internal/database/migrate.go directly — these are documented inline at the constant/comment level and don’t compose into a single feature story worth a catalog entry. The Agent Evolution stack picks up at v100 below.

​
v100–v107 — Agent Evolution stack (PR-B → PR-G/PR-F)

The Agent Evolution PRD (§6 in PRD-AGENT-EVOLUTION-2026.md, internal spec) ships across 8 migrations. They are functionally orthogonal — operators upgrading from a pre-v100 build to v107 land all eight at once and the system end-state is correct; operators upgrading through intermediate versions also work because each migration is additive (no destructive renames after v104).
v100–v107 landed across multiple PRs in parallel and several were renumbered during merge to avoid version collisions:
  • PR-D ephemeral_agents was originally drafted as v100 on its branch. Bumped to v102, then to v103 as PR-B (v101 autonomy) + PR-C (v102 keeper_phase2) merged ahead. Final landed number on main is v103.
  • PR-E persona_rename + peer_consent were originally v102 + v103 on their branch. Renumbered to v104 + v105 after PR-C and PR-D merged.
  • PR-G self_learning + gdpr_cascade were drafted as v106 + v107 and kept those numbers — by the time they merged, the renumber cascade was already settled.
This renumber cascade is captured in PRD §10.5 (the “two columns vs lifecycle enum” decision log) and PRD §10.3 (the autonomy_level int→string drift). Read those entries to reconcile a doc reference that mentions an old version number.
The migration runner keys on the version number, not the name — so two branches that both claim the same next number will land non-deterministically depending on merge order, and the second one silently skips. user_models was developed alongside a conversation-search branch that takes the next free number (v111); to dodge the collision, user_models deliberately claims max+2 = v112. The gap at v111 is intentional and harmless: the runner only requires versions to be monotonic, not contiguous.
A common confusion: v106 only RECORDS the per-agent self_learning_enabled flag. The downstream consumers (internal/api/keeper_phase2.go::HandleNegativeLearning, internal/api/agent_persona.go::SuggestAgentPersona) read the flag at decision time, not at migration time. Flipping the flag mid-flight via the PATCH /api/v1/agents/{id}/learning endpoint takes effect on the NEXT evaluator run — no agent restart, no migration re-apply.The audit triple (self_learning_set_by_user_id / self_learning_set_at / self_learning_reason) is non-NULL on every flip. The API handler enforces non-empty reason; the column itself is NULLable so the v106 backfill (default 0 for existing rows) doesn’t have to invent a fake actor.
v107 adds the data_subject_id columns but does NOT backfill them for pre-v107 rows. Existing memory_versions and inbox_items rows from before the upgrade stay NULL and miss the GDPR cascade. Tracked as PR-F32 — see PRD §6.1 for the deferred-but-tracked status. An operator upgrading from an older Crewship to a v107+ build should run the cascade against new SAR requests with full confidence, and treat historical data as “needs manual review” until PR-F32 lands.The gdpr_actions audit table is the canonical record. See GDPR — Article 15 + Article 17 for the operator playbook.

​
20260810120000 — Drop redundant indexes

Removes eleven indexes whose columns are a leading prefix of a longer index on the same table. SQLite uses a multi-column index for any leading prefix of its columns, so (workspace_id) is dead weight once (workspace_id, created_at) exists — never chosen for a read, still maintained on every write, and on SQLite that maintenance happens while holding the single database-wide write lock. They were on the hottest tables in the system: missions, chats, assignments, credentials, journal_entries, attachments, crew_members, credentials, pipelines, workflow_templates, peer_conversations. Nothing reads these names at runtime, and each one’s covering index is named in the migration file. idx_journal_trace_id is a special case: it and idx_journal_trace were the same index under two names, created by two migrations that did not know about each other.
TestSchemaHasNoRedundantIndexes re-derives this from the live schema rather than from a list, so a new composite index that happens to subsume an older narrow one fails the build. If you hit it, drop the narrow index in the same migration rather than adding an exception.
No behaviour change and no data change — index definitions only. Reversible from the original CREATE INDEX statements if a future query genuinely wants the narrower index.

​
20260810153104 / 20260810153105 — credential_audit carries its own workspace

Adds credential_audit.workspace_id (nullable, REFERENCES workspaces(id) ON DELETE CASCADE) plus idx_credential_audit_workspace_time(workspace_id, occurred_at DESC), then backfills existing rows from the credential each one points at. The table had no workspace column, so the admin audit view scoped it by joining through credentials — a shape no index could serve. SQLite either walked the table in time order probing credentials per row, or gathered the whole matching set and built a temporary B-tree to sort it before LIMIT discarded almost all of it, and the page’s COUNT(*) paid for the same join again. audit_logs and keeper_request_events both already carry workspace_id directly; this brings the third audit source into line. The column is nullable because SQLite cannot ADD COLUMN a NOT NULL without a non-NULL default, and a REFERENCES clause added by ALTER TABLE is only legal when the column defaults to NULL. Every writer populates it: the single writer (RecordCredentialEventTx) derives it with a sub-SELECT against credentials in the same INSERT, so it cannot disagree with the credential it describes and no caller has to know the workspace to write an audit row.
Two files rather than one. ADD COLUMN cannot be re-applied — SQLite has no ADD COLUMN IF NOT EXISTS — while a backfill both can and must be: a restore whose ledger was rolled back re-runs it, and the second run has to be a no-op. Splitting them is what lets the backfill be tested by clearing its ledger row and migrating again, the same way 20260802155412_backfill_crew_container_sizes is tested.
No behaviour change. The audit view returns the same rows; it just stops sorting the whole table to do it.

​
20260810154153 — Index the hot foreign keys

With PRAGMA foreign_keys = ON, deleting a parent row makes SQLite check every child table referencing it. When the child’s referencing column is not the leading column of an index, that check is a full table scan — once per deleted parent row, while holding the single database-wide write lock. 48 foreign key columns had no leading index. This adds 16, and the 32 it leaves alone are the point: every index is paid for on each INSERT and UPDATE of the child, and the migration immediately before it exists because eleven unusable indexes had accumulated. Two conditions, both derived from the tree rather than assumed:
  1. The parent is actually hard-deleted. DELETE FROM <parent> in non-test Go finds agents, credentials, missions, crews, chats, projects, milestones, checkpoints, workspaces and assignments. users is not among them — nothing hard-deletes a user row — so the nine unindexed columns referencing users buy nothing today and are skipped.
  2. The child table grows. Scanning a settings table with one row per workspace costs nothing; scanning an append-only audit table costs more every day.
DELETE FROM missions WHERE crew_id = ? removes many missions in one statement, and each one re-checks every child table referencing missions. That is the worst shape in the schema for an unindexed child, and it is why all three mission references are indexed even though eval_runs is small today.
TestUserForeignKeysStayUnindexed pins the exclusion so it stays a decision, and TestNoRegressionInUnindexedForeignKeyCount ratchets the total at 32 so a future table that adds foreign keys nobody sized shows up as a number moving.

​
20260810160400 — Constrain workspace_members.role

crew_members.role has carried CHECK(role IN ('OWNER','ADMIN','MANAGER','MEMBER','VIEWER')) since v99. workspace_members.role — the column deciding what a member can do across an entire workspace — carried nothing, and would accept any string. Nothing writes a bad one today; the API validates against a whitelist. It is worth constraining anyway because of an asymmetry in how the value is read: So the write tiers fail closed and the read tier fails open, and the schema is the only place that can refuse a garbage value independently of whichever write path appears next.
Enforced with BEFORE INSERT / BEFORE UPDATE triggers, not a CHECK. SQLite cannot add a CHECK to an existing table — that needs the 12-step rebuild and the pinned-connection machinery in migrate_consts_v167_journal_append_only_fks.go, which is a lot of risk to take on the table that decides who owns a workspace, for a constraint the application already upholds. Triggers enforce the same predicate in plain SQL.The one difference is deliberate: a trigger constrains writes and leaves stored rows alone. A rebuilt CHECK would refuse to apply at all if a legacy row held an unexpected role, turning a defensive tightening into a boot failure on the instance that most needs looking at.

​
20260810170000 — Audit retention windows

Adds workspaces.credential_audit_retention_days and workspaces.audit_log_retention_days. credential_audit and audit_logs were the only tables in the schema with no pruning at all — pipeline_runs (v158), inbox_items and journal_entries are all swept, these two never were. The two defaults differ on purpose.
NULL and 0 mean different things in these columns, unlike run_retention_days where both fall back to the default:
  • NULL — no opinion recorded; use the product default.
  • 0 — the operator’s explicit keep forever.
  • n > 0 — keep n days.
Collapsing 0 into the default would make credential_audit pruning impossible to switch off, and “keep this forever” is a retention decision an operator has to be able to express.
20260811082000 pins every workspace that predates the migration to an explicit 0 (keep forever) for credential_audit, leaving NULL — and so the 90-day default — only for workspaces created afterwards.Without it, upgrading is destructive: the sweeper performs one immediate sweep at boot, every existing workspace resolves NULL to 90 days, and a year of credential access history is deleted on the first restart — before the API that sets the override is listening. Shipping a default is fine; applying it retroactively to history that accumulated under “we never delete this” is not. New installs are bounded, existing installs are asked.
The sweeper (internal/api/audit_retention.go) runs daily, deletes in bounded batches so one statement cannot stall every writer, and logs the remaining backlog if it stops at its cap. Because audit_logs is kept forever by default, it also warns once per sweep when a workspace has no window set and the table has grown past a million rows — the signal should reach an operator before the disk does.

​
20260810171000 — Hash the capability tokens

Adds port_exposures.token_hash and pipeline_webhooks.token_hash, each with a partial UNIQUE index, and stops both lookups reading the cleartext column. Neither of those tokens is a credential that accompanies an authenticated request — it is the request’s authorization: Stored in the clear, that made read access to the database file equivalent to holding every live exposure URL and every configured webhook on the instance: a leaked backup, a copied .db, or any read primitive was enough. cli_tokens has stored a digest instead of the secret since Patch J and this brings these two into line. Existing tokens are preserved, not rotated. The digest is computed from the cleartext already in the row, so a URL an agent published last week keeps working across the upgrade. Rotating would have been the smaller change and would have broken every published exposure URL and every already-configured sender on every instance, with no evidence any cleartext leaked.
The backfill is Go, not SQL, and that is why it is not in the .sql file. SQLite has no SHA-256, so the digest cannot be computed in a migration. The file adds the column and the index; the hashing runs the first time the owning component is constructed, which on the server path is before anything serves a request — PortExposeRegistry.LoadFromDB for port_exposures, NewWebhookStore for pipeline_webhooks. Both select on token_hash IS NULL, so they are idempotent and normally read zero rows.
The digest is unkeyed SHA-256, hex, behind an sh1: scheme prefix — not an HMAC. A key protects a digest whose input space is small enough to enumerate offline; these two tokens are 32 bytes of crypto/rand each (internal/pipeline.generateWebhookToken, internal/api.generateExposeToken), so the preimage search is 2^256 wide either way and the key adds nothing.
An earlier revision of this migration keyed the digest off ENCRYPTION_KEY, and that was a latent outage. The documented master-key rotation (CREWSHIP_ENCRYPTION_KEY_VERSION + ENCRYPTION_KEY_V2 + POST /admin/reencrypt) retires ENCRYPTION_KEY as its final step. After that, every presented token hashes under a different scheme than every stored digest — and the cleartext has already been overwritten with redacted:<row id>, so nothing is recoverable. Every webhook sender and every published /exposed/ URL would 404, permanently. The digest now depends on the token and on nothing else, so no key operation can invalidate it.The keyed hk1: scheme is not supported and never shipped: it existed only on the branch that developed this migration. A dev instance that ran the earlier revision holds hk1: digests nothing will match — re-create those webhooks and re-request those exposures, or delete the rows.
The scheme prefix stays for two reasons: it makes a stored digest recognisable as one, so replaying a value read out of the database file at the public endpoint resolves to nothing, and it lets a future algorithm change add a prefix rather than rewrite one. A row the backfill could not hash is not stranded. The backfill continues past a failed UPDATE (SQLite has a single writer; one moment of contention used to abort the whole loop) and counts what it missed. While any row still holds cleartext, WebhookStore.GetByToken arms a second, bounded lookup against the cleartext column for tokens the digest index does not resolve — matching only rows whose token_hash is still empty, whose cleartext is in the table anyway, and re-hashing each one as it is matched. PortExposeRegistry.LoadFromDB does the equivalent at boot: an ACTIVE row whose cleartext survived is hashed and served, and one with nothing left to resolve it is flipped to EXPIRED rather than reported live with a future expiry.
The cleartext columns stay, holding redacted:<row id>. Neither can be dropped: both are NOT NULL UNIQUE, and SQLite’s ALTER TABLE DROP COLUMN refuses a column carrying an index — while port_exposures.token is still named by the INSERT that creates an exposure. SET token = '' is not available either: the second row to be blanked would violate UNIQUE. Deriving the marker from the primary key keeps it unique, obviously dead, and traceable to the row it belonged to.The consequence for webhooks is that the token is now shown once, in the create response. GET, PATCH and the list endpoint return an empty token, because there is nothing left to return. A webhook whose token was lost must be re-created — and crewship routine webhooks url <id> says exactly that and exits non-zero, rather than printing a URL with nothing after the last slash.
pipeline_waitpoints.token is the same kind of secret and is not covered here. It is that table’s primary key, it is the handle carried by inbox_items.source_id and by a WAITING run’s waitpoint_token, and GET …/pipelines/waitpoints reads it back out of the column to rebuild the public POST /api/v1/waitpoint-tokens/{token} callback URL on demand. It is a retrievable shared secret by contract rather than a show-once credential, so hashing it means redesigning that contract first.

​
20260811081130 — Drop a redundant trust-grant index

v20260809120000 (waitpoint_trust_grants) created two indexes on the same rows, and the shorter is a strict prefix of the longer:
SQLite uses an index for any leading prefix of its columns, and the partial predicates are identical — so _live already answers everything _lookup was added for. Being UNIQUE does not stop it covering reads; the constraint is extra, not a restriction. _lookup therefore cost a second B-tree update on every insert, revoke and use-count bump while the planner could never choose it.
Found by TestRedundantIndexPolicy, which re-derives redundancy from the live schema rather than from a list — so it fired the moment this branch met the one that added the pair. The two definitions only read as redundant once you line them up, which is what the machine is better at.

​
How to read this in operation

When crewshipd starts, it logs each migration as it applies:
The _migrations table records the applied set and refuses to apply a version-name pair that disagrees with what the code expects — this catches the classic “two PRs both claimed version N with different SQL” footgun loudly at startup. See Collisions and how to get out of one.

​
Version-skew guard (no accidental downgrade)

Migrations are forward-only. If crewshipd boots over a database whose highest applied migration is newer than the binary knows how to apply — i.e. a newer Crewship already migrated it and you’ve since started an older binary — it refuses to start rather than run against a schema it doesn’t understand (which would silently write partial or malformed rows). The startup error names both versions and the two recovery paths:
The *.pre-migrate-*.bak snapshot is the one Crewship takes automatically before applying pending migrations (see below) — so a bad upgrade is a one-step rollback: put the old binary back and restore the matching snapshot.
The guard runs in the binary that boots, so it only protects you if the version you are rolling back to has it. It was added on 2026-07-08; anything older boots over a newer schema without a word. Verified against a build from 2026-07-06: it started, served traffic, and reported nothing. Restoring the snapshot is what makes a rollback safe — the guard only catches you when you forget.

​
Authoring a migration

One file per migration. The registry is the directory internal/database/migrations/ — there is no central list to edit, and that is the point: two people adding a migration add two files and cannot conflict.
The v1–v169 block stays declared in the legacyMigrations slice in migrate.go. Those numbers are applied in databases nobody controls, so rewriting them buys nothing and risks a transcription error in the one part of the system that must never drift. The slice also still holds migrations that need Go rather than SQL — schema discovery at apply time, SQLite table rebuilds. Everything expressible as plain SQL is a file.

​
Version numbers are timestamps

Versions v1–v169 were allocated sequentially: the author took the next free integer. Everything after that is a UTC timestamp, YYYYMMDDHHMMSS:
Generate one with:
Why the change: with sequential numbers, two branches open at the same time both take the same next integer, and neither author can tell. That is not an unlucky edge case — it is the default outcome of ordinary parallel work. Timestamps make a collision require two authors generating a migration in the same second. Rails, Django and Flyway all use this scheme for the same reason. Rules the build enforces (internal/database/migrate_version_scheme.go, and the tests beside it):
  • Versions must be strictly ascending, which for timestamps means chronological — so append, never insert.
  • Nothing new may be added at or below v169. That block is closed; those numbers are applied in databases nobody controls.
  • A version above the ceiling must be a plausible YYYYMMDDHHMMSS stamp. Writing {version: 170, …} out of habit fails the build with an explanation rather than shipping a collision.

​
Post-deployment migrations — when a migration would be downtime

Migrations run before the server serves anything. That removes the whole class of problems GitLab has to solve with online DDL — nothing is contending with live queries — but it converts migration duration directly into upgrade downtime, and that duration scales with the customer’s data. Measured on this schema (migrate_scaling_test.go, linear at 20k and 200k rows): Ten minutes of downtime is what a successful customer gets. So a migration whose cost grows per row can go in migrations/post_deploy/ instead, where it runs after the server starts serving, one batch per transaction:
The runner re-executes the statement until it stops changing rows, committing each pass, so no single transaction holds the write lock for the whole table and a restart resumes rather than starting over. The ledger row is written only when the backfill finishes — which is what makes an interrupted run safe to re-enter.
This is not a free speed-up. A post-deployment migration has not run when the new code starts serving, so:
  1. The change must be additive — add a column, add a table. Never drop or rename something the backfill depends on.
  2. The running code must tolerate the change being half-applied for as long as the backfill takes.
  3. The statement must be idempotent and bounded — UPDATE … WHERE col IS NULL LIMIT 500 converges; SET counter = counter + 1 corrupts.
If any of those is uncomfortable, the migration belongs in the normal lane and takes its downtime honestly. Full contract: internal/database/migrations/post_deploy/README.md.
Removing a column later is two releases, never one: release N adds and backfills, release N+1 — once every instance has finished — drops the old column in the normal lane. Skipping the gap breaks whoever upgrades slowly.

​
Seeing what is outstanding

Reports the schema version and any post-deployment migration still running. Reads the local database directly, so it works with the server down.

​
Collisions and how to get out of one

Two guards stand between a collision and your data, and they work at different times. At review time, scripts/lint-migrations (workflow Migration Lint, job “Lint migrations”) fingerprints every migration entry — version, name, and the body of the referenced SQL — and compares against the PR’s base ref. Changing an entry that already exists on main fails the check. So a forked schema does not reach main. At startup, the collision guard refuses to run when the database has a version applied under a different name than the binary declares:
The startup guard is what you hit on a machine that ran a feature branch whose migration was later renumbered before merging — the branch’s number is already recorded in that database. A pre-migration snapshot does not rescue this: it carries the same ledger.
The repair is crewship db repair-ledger:
It moves the ledger row to the version this binary declares for that migration name. No schema or table data is touched — the migration already ran; only the number recording it was wrong. Any version the move frees up is then applied normally on the next start, so nothing is skipped. It refuses when the ledger names a migration the binary does not have at all. That is not a renumbering — the database was migrated by a different Crewship — and the fix there is a newer binary or crewship db restore-snapshot. Stop crewshipd before applying: a running server holds the database open. --dry-run is safe either way.

​
Restoring from a backup that pre-dates a column

The backup subsystem (internal/backup/runner.go) records the migrations applied at backup time. On restore, every migration the target has but the source lacked runs its restoreBackfill hook (when defined) against the freshly-inserted rows — so a restore from a v59 bundle into a v62 server populates the new columns sanely instead of leaving them at the SQL DEFAULT. Pure ADD COLUMN migrations that rely on the DB default need no hook; complex ones (e.g. backfilling a JSON column) provide one.