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.

​
20260811122612 — Chain queries get the index they actually use

Replaces `idx_pipeline_runs_chain (workspace_id, chain_depth) WHERE chain_depth
0— added speculatively for a "show me the chains" query that never got written — withidx_pipeline_runs_chain_origin (workspace_id, chain_origin), which is the column the three shipping chain queries (GET /api/v1/chains's grouped CTE, the pre-migration "any unrecorded runs" probe, and the touched-issues fan-out) actually select on. Not a table-scan fix: a workspace-leading index already got the planner to the table, so the plan read as healthy while re-checking chain_originagainst every run the workspace had ever recorded.TestChainsList_HotQueriesReachRunsByChainOrigin` pins the planner’s choice directly rather than asserting on wall-clock time.
No behaviour change. Index-only, reversible from the dropped index’s original definition (stated in the migration file) if a chain_depth > 0 query is ever written.

​
20260812155322 – 20260813080612 — Pages

Five migrations for Pages (docs/specs/pages.md), a workspace-scoped, slug-addressable dashboard record holding an ordered list of panels. A page holds no query, no datasource and no credentials — it renders the last payload a producer pushed from inside a crew container, which is why the producer-authority columns (owner_crew_id, producer_kind/producer_ref) carry the whole security model instead of a query to authorize. Together these back crewship page and the /api/v1/pages family — see Pages for the operator model and Autonomy gate on agent-driven creation for page_create’s gate, which the setup-agent’s page-save internal route enforces.

​
20260812233006 & 20260813085342 — Chat composer: suggested prompts and ask forms

Both are plain ADD COLUMNs with no backfill: NULL means “not configured,” and an agent with neither column set behaves exactly as it did before either migration — the built-in role packs. Caps (8 prompts / 4 forms, character limits, template-field validation) live in Go, not in a CHECK, because SQLite can express a length bound but not “a template naming a field that doesn’t exist,” and a rule enforced twice — once precisely, once approximately — is how the two drift.

​
20260813212500 — Attachments gain a publication state

A 201 from the chat attachment upload used to mean “the bytes landed and the row insert was attempted” — recordChatAttachment logged a failed INSERT and answered success anyway. For the chat arc specifically, whose blobs are not content-addressed (they live at the agent-visible path outside <root>/attachments/, which is exactly the tree the reclaimer walks), an unrecorded blob was permanent, un-collectable residency bought with a success response. Adds attachments.state (pending | stored, DEFAULT 'stored' so every existing row — which, under the old ordering, could only exist after its bytes were written — backfills to the truthful value). The upload path is inverted: the row is written first as pending, the bytes are published second, and only then does the row become stored. The rule is now one sentence: a 201 means there is a stored row and bytes at its storage_key. Any other outcome leaves a reclaimable pending row instead of an invisible orphan. No CHECK constraint (a bookkeeping column doesn’t justify the rebuild risk). Both readers fail safe on an unrecognised value instead: the list endpoint requires state = 'stored', the reclaimer requires state <> 'stored' — so a typo can only make an attachment disappear, never make an unpublished one look published.

​
20260813212851 & 20260818215915 — Escalations get a deadline, then two

An escalation is a question an agent asked a human, and until the first of these migrations there was no way for that question to stop being open. internal/sidecar/query.go gives up its long poll after 300s and the agent proceeds without an answer — but nothing told the server, so the row stayed PENDING forever, and an operator answering three hours later delivered an answer to an agent that had already moved on. 20260813212851_escalation_deadline adds escalations.deadline_at and two new terminal states beyond the existing PENDING→RESOLVED: EXPIRED (the deadline passed, no human decided) and CANCELLED (a human withdrew the question). The handler set every deadline to created_at + 300s — the agent’s wait bound. That was also the bug: 300 seconds is right for the agent’s wait and wrong for the human’s — an operator who saw an inbox item, walked to a password manager, and came back seven minutes later got 409 for a question nobody had ever answered. Worse for a CREDENTIAL escalation: only the resolve path disposes of the staged PENDING_APPROVAL credential, so an expired-before- answered credential escalation left an encrypted secret stranded in the vault forever, and the live-name probe counted it against every later proposal of that name. 20260818215915_escalation_answer_deadline splits the one clock into two: deadline_at stays the agent’s 300s wait (bounds the poll and nothing else — expiring it only stamps agent_gave_up_at, the row stays PENDING), and a new answer_deadline_at (7 days) is the human’s answerability window — only that one expiring moves the row to EXPIRED. It also repairs the damage: any PENDING_APPROVAL credential whose escalation is no longer PENDING (unreachable by construction — that resolve path is the only door) is retired to REJECTED + deleted_at, the same disposal a real rejection performs. Neither migration backfills the deadline columns on pre-existing rows — NULL means “no deadline” and old PENDING rows stay answerable indefinitely, the behavior they always had. Rows the buggy 300s window already flipped to EXPIRED are not resurrected; their agents are long gone.

​
20260820074400, 20260820124407, 20260820125000 — Two collisions in tenant-scoped uniqueness

Three unrelated tables, one shared shape: something that reads as workspace-scoped was not actually constrained that way. 20260820074400_issue_counters_crew_not_null (#1973): issue_counters.crew_id TEXT PRIMARY KEY REFERENCES crews(id) reads as “required,” but SQLite’s rowid tables accept NULL on any PRIMARY KEY column that isn’t INTEGER PRIMARY KEY — a documented compatibility quirk. The backup scoper filters on crew_id to find a row’s workspace, and a filter over a nullable column silently omits every NULL row: the bundle succeeds, verifies, and comes back short a counter, so a restored crew can re-issue an identifier it already used. Rebuilds the table with crew_id TEXT PRIMARY KEY NOT NULL, dropping rows whose crew_id is NULL or names a crew already gone (an orphan ON DELETE CASCADE should already have removed). 20260820124407_crew_template_slug_workspace_scope (#1796): crew_templates.slug TEXT NOT NULL UNIQUE predates the table’s workspace_id column (bolted on later by v26) and was never rescoped — one global slug namespace on a table that is otherwise workspace-owned. Rebuilds into two partial unique indexes, (workspace_id, slug) WHERE workspace_id IS NOT NULL and (slug) WHERE workspace_id IS NULL (the builtin namespace). One behavior change falls out: before, a user template named X permanently suppressed the builtin X from ever seeding (INSERT OR IGNORE hit the old global UNIQUE and silently gave up); now the builtin seeds and the user’s template shadows it — every single-row lookup in the codebase reads (is_builtin=1 AND workspace_id IS NULL) OR workspace_id = ? ordered so the more specific row wins. 20260820125000_issue_counters_prefix_scope (#1797): the previous migration made crew_id required but the counter was still keyed per-crew, while the namespace it feeds (<prefix>-<n>, unique per workspace) is per-prefix. crews.issue_prefix falls back to the first three letters of the crew slug when unset, so engineering and engine both derive ENG — two such crews in one workspace both mint ENG-1, the loser’s mission insert is rejected, and because the counter upsert and the mission insert share one transaction, the loser’s counter never advances either: it retries the same identifier forever. Rekeys issue_counters to PRIMARY KEY (workspace_id, prefix), collapsing colliding crews onto one shared, interleaved sequence, and backfills next_number as the MAX of every surviving per-crew counter and the high-water mark already found in missions.number — so a crew that changed its prefix in the past doesn’t restart at 1 and collide with its own historical identifiers. All three are the standard SQLite rebuild (CREATE new / copy / DROP / RENAME), safe inside the migration runner’s wrapper transaction because none of the three tables has a dependent foreign key.

​
20260822130000 – 20260824073500 — Conversational onboarding

Six migrations landing the setup-crew onboarding flow (docs/prd/conversational-onboarding.md).

​
20260826190607 — MCP server audience becomes a stored fact

“Available to every agent” used to be inferred: agent_mcp_bindings row count of zero meant open-to-all. Nothing stored that state, so it evaporated the moment the count left zero — one POST /api/v1/agents/{id}/integrations anywhere in the workspace silently flipped the server to opt-in for every other agent that had been relying on the default, with no warning (#2072). Adds default_access ('all' | 'bound-only', DEFAULT 'all', no CHECK — see below) to both workspace_mcp_servers and crew_mcp_servers, then freezes each existing server’s actual current audience: a server with at least one binding is pinned to 'bound-only' (under the old inference it was already opt-in in practice; leaving it 'all' would hand it to every unbound agent on first boot), everything else keeps the default 'all'. Either way, nobody’s effective access moves on upgrade — a binding is now purely additive and can never change what another agent resolves. Also backfills referential integrity mcp_tool_bindings never had: its mcp_server_id is polymorphic (discriminated by mcp_server_scope across two separate ID spaces, so no single REFERENCES clause is expressible), and the hand-written Go delete cascade for an integration never mentioned this table — every per-tool toggle on a deleted server outlived it, invisible and unreachable, and would be silently re-adopted by a future row reusing the same id. This migration sweeps the existing orphans and adds the same BEFORE DELETE / BEFORE INSERT trigger pair v30 already uses for agent_mcp_bindings’s equally polymorphic FK. No CHECK on default_access: SQLite can’t alter one in place, and a two-value vocabulary likely to grow a third (per-crew audiences) would force a full table rebuild later. The vocabulary is enforced where it’s written (workspace and crew integration handlers reject anything else) and every resolver fails closed — only the literal string 'all' opens a server to an unbound agent.

​
20260826205610 — Behaviour-monitor sampling cadence becomes a setting

internal/keeper/behaviorhook could always sample at an arbitrary rate (SetSampleEvery), but nothing in production ever called it — every workspace ran the package’s hardwired “every 5th tool call.” Adds keeper_governance_settings.behavior_sample_every (default 0, the unset sentinel meaning “use governance.DefaultBehaviorSampleEvery,” not “never”) alongside the workspace’s other Keeper posture knobs. The API rejects a written 0 — disabling the monitor is crewship keeper disable, not a cadence of zero that would leave a workspace reading “enabled” while nothing is ever evaluated. Additive column with a NOT NULL DEFAULT, so no backfill pass.

​
20260827145903 — MCP OAuth gets its RFC 8707 / RFC 9207 bindings

Adds credentials.oauth_resource and credentials.oauth_issuer (both TEXT, DEFAULT ''), closing a gap against the MCP authorization spec’s MUST requirements: without a bound resource indicator, a token minted for MCP server A could be replayed by A’s operator against MCP server B (a confused deputy); without a checked issuer, a host talking to more than one authorization server — which Crewship does by construction, one AS per connected MCP server — has no mix-up defence on the OAuth redirect. See OAuth Commands for the connection flow (#2136). oauth_resource is populated from the credential’s RFC 9728 protected- resource metadata at connect time (AutoConnect) — never invented from config — and oauth_issuer is checked against the RFC 9207 iss redirect parameter. Both default to '' rather than NULL so existing COALESCE-free call sites compare directly; the OAuth flow code treats an empty value as “nothing to send” / “nothing to check,” so every credential connected before this migration — MCP and every other OAuth provider (Google, Slack, GitHub, …) alike — keeps working exactly as it did.

​
20260901134904 — Approvals retention window

Adds workspaces.approvals_retention_days. approvals_queue had no retention of any kind — every terminal transition (approve/deny, cancel, the timeout sweeper, the sync-gate timeout) was an in-place UPDATE of status/decided_*; nothing was ever deleted, and rows were also IntentInclude for backup, so they survived into every bundle and restore. See issue #2233. The sweep (internal/harbormaster/retention.go) follows the shape of internal/api/audit_retention.go — a nullable per-workspace override, a daily ticker, bounded batched deletes — and, after a second look prompted by review, resolves the column like the audit pair too, not like run_retention_days:
20260901140000 pins every workspace that predates the migration to an explicit 0 (keep forever), leaving NULL — and so the 90-day default — only for workspaces created afterwards. Same reasoning and same split as 20260811082000 above: without the backfill, the sweeper’s immediate first sweep at boot resolves every existing workspace’s NULL to 90 days and deletes decided approval history before the operator has any chance to set the override. New installs are bounded, existing installs are asked.
Only terminal rows (status IN ('approved','denied','timeout','cancelled')) with a non-NULL decided_at older than the window are ever deleted. A pending row is never touched regardless of age — that is the timeout sweeper’s job (internal/harbormaster/store_sweep.go), and deleting a row an agent is still blocked on would strand the wait instead of failing it deterministically. crewship workspace update --approvals-retention-days N sets the override (PATCH /api/v1/workspaces/{id} — see Workspaces). N above harbormaster.MaxApprovalsRetentionDays (106751 days, ~292 years — the point at which days * 24h overflows an int64 nanosecond time.Duration and wraps negative) is rejected with 400 Bad Request rather than silently accepted and later turned into a cutoff in the future, which would delete every terminal row regardless of age.

​
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

If your CLI is configured for a server (--server, CREWSHIP_SERVER, a profile, or a previous crewship login), crewship db refuses to run until you add --local — it acts on the database file on this host and cannot tell whether that server uses it. See CLI: db.
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.