storage.md

Storage and persistence

Chopin's durable boundary is StorageAdapter in apps/server/src/storage/port.ts. Domain services depend on that port rather than a database driver or query language. PostgreSQL is the only selectable runtime adapter. MemoryStorage exists for unit and contract tests and is not a runtime STORAGE_DRIVER.

State model

The database stores channel metadata, including archive state and generated description provenance and optional parent channel, repository-scoped document slug aliases, collaboration recovery state, domain sidecars, token-free ownership references, parent-scoped research request staging, durable background jobs, ordinary child channels, and the writer lease. Hosted sessions store browser-secret hashes and authenticated ciphertext for GitHub credentials; paired machines store a token hash and their own GitHub credentials the same way. The encryption key stays outside PostgreSQL. Plaintext GitHub tokens, raw browser-cookie secrets, open rooms, Awareness presence, and Copilot SDK sessions never cross the storage boundary. See Session boundary for encryption and restoration.

The versioned sidecar is the atomic domain snapshot associated with a channel. It includes document sequence and plan revision counters, question and comment records, shared drafts, transcript, relationships, creation metadata, MCP update results and client attribution, implementation graphs, active execution, and lifecycle history. Restoration validates the sidecar version and selected shapes before exposing a room, but it does not deeply validate every nested question, passage, note, or transcript-author field. Compatibility conversion is limited to explicitly supported former fields. An invalid optional implementation graph is dropped instead of rejecting the sidecar.

Conversation state version two lives inside that sidecar and has separate decision/research analysis queues. Research offers retain exact source references, topic and placement identities, generation metadata, bounded shared text-CRDT checkpoints, and immutable accepted briefs. Acceptance and its pending delivery intent commit together; subsequent request creation is idempotent under the offer's stable execution key. See Research offers from Chat.

Adapter guarantees

Every adapter must provide:

• atomic, revision-checked channel commits;
• idempotent operation IDs;
• ordered binary update and event replay;
• monotonic storage sequences across checkpoints;
• atomic checkpoint and epoch replacement;
• atomic first-Planner ownership with a generation token;
• active-only channel lists and scans by default, with explicit archived inclusion and direct archived reads;
• idempotent archive and restore transitions and archived-only atomic deletion;
• repository-scoped canonical and historical slug resolution without rebinding aliases;
• expiring session rows with optional opaque encrypted credentials;
• paired machines found by token hash, with revision-checked credential rotation, re-authorization and owner-only revocation that discards credentials;
• revision-checked credential rotation that cannot recreate a deleted session;
• startup removal of expired and metadata-only sessions and every Planner owner reference, except that of a document whose persisted Planner hold names a session the reset keeps;
• plain, lock-free reads of a lease (leases.peek), so a process waiting for the writer lease never queues behind its holder;
• Planner holds, Planner credential digests and MCP sessions that outlive a process; holds are written under the writer lease. The rows an earlier release kept for deferred Planner turns are only closed now, at start, under the writer lease;
• startup clearing of child channel conversations, fenced by the writer lease and idempotent;
• renewable leases whose fencing token protects commits, epoch replacements, checkpoints, jobs, and research request mutations;
• idempotent, independently revisioned publication of generated channel descriptions without changing collaboration revision or channel activity;
• one-level, repository-local child relationships;
• idempotent research request staging with channel-local job links;
• atomic, idempotent publication of one initialized child and its request link; and
• distinct conflict, missing, corrupt, and unavailable failures.

collaboration.commit can append a Yjs update, replace sidecar state, append events, or combine them in one transaction. Transcript, draft, relationship, graph, and lifecycle changes use the same commit even when the document itself does not change. Splitting those writes could restore a document and decision state that no connected client was ever shown.

Counters

The adapter's revision and sequence are storage counters, not the plan revision used by document block operations or the WebSocket document sequence.

• The channel storage revision advances for every accepted durable commit.
• The storage sequence orders commits that may carry updates or events.
• Sidecar-only commits consume a sequence without adding a Yjs journal row, so gaps in channel_updates.sequence are expected.
• A checkpoint records the storage revision and through_sequence it covers.
• The Yjs epoch is retained separately so incompatible collaborative histories cannot be combined.

The generated-description revision is a separate channel metadata counter. It advances when a newer marked document-summary@1 artifact is projected, without advancing collaboration storage revision, storage sequence, Yjs epoch, document sequence, or plan revision. Projection also leaves channels.updated_at unchanged, so catalogue ordering and pagination recency do not move.

Archive and restore update only channel metadata (archived_at and updated_at). They do not advance the collaboration storage revision or sequence, Yjs epoch, document sequence, plan revision, or implementation graph version and revision.

See the counter glossary in Architecture before changing recovery or acknowledgement behavior. See Background jobs for target and claim generations, attempts, failures, and background-job channel revisions.

PostgreSQL schema

The migration runner owns chopin_migrations, including a checksum for each applied migration. The application schema contains:

| machine_choices | Each person's chosen machine for one document; no row is automatic. Revoking the machine or deleting the document removes it. | | machine_favorites | Each person's one favorite machine, their own or a teammate's shared one, which Automatic prefers; no row is none. Revoking the machine removes it; its owner no longer sharing it removes teammates' (not its owner's). | | document_versions | Activity history: one row per editing session with its author (absent for Chopin), the canonical MDX it left, revision, hash, and start and end times. | | workflow_run_stages | Every stage of each workflow run a document's Chat shows, keyed by document, run ID and stage ID, with its order, status, name, kind, parent stage IDs and start and end seconds. Bounded per stage, not per run. A run's rows go when Chat stops showing its card, or with the document. | | planner_link_holds | One row per document whose remote Planner link left work or a hand-over window for the next process: the owner's browser login session (the row goes with it) or the paired machine (and no login, which a restart cannot keep), user, generation, its held sessions (waiting request IDs, with the kind and decisions of each question so its answer can be given again, unacknowledged answers, held stops), when it was suspended, handover_until (the length of its hand-over window after the write, which the next process counts again from its own start and then rewrites the row without, so no process after it is given the window), and the fencing token it was written under. Written when a link drops with work kept and at the cut, removed while a live link backs the hold and by everything that ends one, and with the document. Holds no secret. | | planner_model_choices | One row per document whose people chose a model or a thinking level for its Planner: either or both (a row with neither is removed), who chose last and when, and the fencing token it was written under. Written under the writer lease before the choice is answered or shown to the room, replaced whole by the next choice, and removed with the document. The strings are not checked here: the server resolves them against the catalog the harness or attached client reports at the turn that uses them, so a model that has gone stays stored, and returns to force if it comes back. Holds no secret. | | planner_credentials | The SHA-256 digest, machine, document and expiry of each credential a paired machine handed to a Planner backend, written before the credential is handed out. Rows go when they expire, with the machine's end or withdrawal, and with the document. No token is stored. | | planner_deferred_turns | Kept, but no longer written or read: one row per Planner request an earlier release acknowledged before its turn had started (composer, invoke_planner, invoke_system, comment or draft), with its state (waiting, started, ended, interrupted, cancelled) and the fencing token of its latest transition. Nothing is held for a Planner now. At start the writer lease's holder closes the open rows (see A live database from an earlier release). Rows go with the document. | | mcp_sessions | MCP sessions: ID, user, client name and version, creation and last-seen times. Idle sessions are swept. | | decision_answers | Saved answers to decisions the Planner left open, one row per document and decision: the saved generation, when it was saved, the Planner turn claimed to carry it and when that turn was delivered. A newer generation replaces the row and waits again; a delivered row stays marked so it never runs twice. Rows go with the document. | | chat_attachments | The images and text files sent with Chat messages, one row per file: its bytes, its sniffed type (a PNG, JPEG, WebP or GIF image, or plain text, held by a check, never HTML or SVG), its name and size, who uploaded it and when. A row has no message until one is sent with it (then the message's request ID and the file's place in it); until then only its uploader reads it, and one nobody sent within a day is forgotten. Deleting the message or the document removes it. | | chat_threads | One row per Chat thread in a document, keyed by (channel_id, root_id): the root message snapshot, fork timestamp, creator, reply entries, compaction records, latest context usage, fork provenance (bootstrapEntries, Atomic branched session entries/leaf or remote from), update time and writer-lease fencing token. Stored outside the document sidecar so an earlier release reads the sidecar unchanged. Rows go with the document. | | chat_session_state | One row per document with top-level Chat compactions, context usage, Atomic session entries/leaf and per-entry fork points (entry_forks_json), fenced by the writer lease and removed with the document. | | plan_images | Images a document holds, one row per document and SHA-256 of the image's bytes: its type (PNG, JPEG, WebP, GIF, AVIF, BMP or ICO), the bytes, their size, and who stored them and when. size is always the length of bytes and has no limit of its own in the table; what brings an image bounds it: the 1 MiB of upload_image, and for an image a tool made, whichever harness ran the tool, the server's MAX_STORED_IMAGE_BYTES (10 MiB by default) and, over the agent connection, its 2 MiB message limit. The server refuses a larger one before it decodes it, so the table never holds one. Rows go with the document. |

channel_state.unanswered_decisions is maintained in JavaScript rather than a generated column. Sidecar text may contain an escaped NUL or an unpaired UTF-16 surrogate, which JSONB rejects, so SQL cannot re-parse every stored sidecar. Migration 017 backfills existing rows with the same rule, and every adapter write that replaces a sidecar writes its count in the same statement.

The channels.parent_channel_id foreign key records navigational containment. Storage rejects a parent from another repository and rejects a grandchild. research_workspaces.published_channel_id is unique and restricts deletion so a published request cannot silently lose its child link.

Generated-description columns on channels are all absent or form one complete projection: value, independent description revision, source plan revision and hash, generator version, source job ID, and projection timestamp. A newer job does not clear the current projection when it is enqueued or fails, so the last completed description remains visible and searchable.

Display titles may repeat. Slug values remain unique per repository, with one canonical slug per channel. Authored title changes commit the document state, display title and slug in the same fenced collaboration transaction. A rename promotes a new collision-suffixed slug but retains all former slugs as aliases that cannot be assigned to another channel while it exists. Archived channels continue to reserve their aliases.

Files sent with a Chat message (see Files in Chat) are rows of chat_attachments, kept with the document and never in the sidecar: the sidecar's transcript entry carries only their names, kinds, types and sizes. The same rows are the memory adapter's, and the storage contract holds both to the same rules.

An image, whether a Chat attachment or one of a document's images, is stored once, as it is, and read at Slack's thumbnail widths (64, 80, 160, 360, 480, 720, 800, 960 and 1024 pixels: ?w=480 on its address): the copy is made with Bun.Image the first time it is read, never wider than the image, and kept in a bounded cache in memory (apps/server/src/images/resize.ts), so nothing about it is stored and a restart only makes it again. A GIF is served whole, so it keeps moving. An image too large to store (MAX_STORED_IMAGE_BYTES for an image a tool made, 1 MiB for an upload_image, CHAT_ATTACHMENT_MAX_BYTES for an attachment, which the browser makes smaller before it uploads) is stored as a smaller copy of itself, so a release that never made copies reads everything this one stores. Where the browser made the copy, chat_attachments.original_size, original_width and original_height (migration 040, additive and nullable) keep what the sender's file was, and the attachment carries it as original so the viewer can say it is shown smaller.

A transcript entry keeps what the composer's @ and / point at (a command, a heading of the document, a Decision, a file of the repository) under pointers, and only references to other documents and to research cards under references. A server from before pointers restores references strictly and refuses a sidecar with any other kind in it, so a pointer in that list would leave a rolled-back server unable to open the document; under a key it does not know, it reads the message as the words it was written in. A server that knows pointers merges them back by where each begins in the text, which restores the list that was stored, exactly (apps/server/src/chat/stored-pointers.ts, held by rollback.test.ts against the real older reader).

Archival and deletion

Channel list and scan operations exclude archived rows unless includeArchived is true. Direct UUID lookup and repository-scoped slug resolution do not apply that filter, so an archived document and its historical aliases remain readable. Navigation selection and repository-level Research Workspace compatibility listing apply the same active-only default; the current browser does not use that listing as a product surface.

Archive and restore lock and update the channel row and are no-ops when it is already in the requested state. Deletion also locks the row and refuses an active channel. A parent cannot be deleted while it still has a child, and a published child cannot be deleted while its research request or investigation record retains that link. Deleting an eligible archived channel is one transaction: foreign keys cascade every channel-owned sidecar, snapshot, journal, event, operation, Planner state, implementation state, slug, background job and artifact, and research staging record. A saved navigation reference is set to null.

Full deletion leaves no live tombstone. It removes historical aliases and the MCP creation idempotency identity, so former slugs can be reused and the same MCP idempotency key can create a fresh document at its deterministic UUID. Reads and implementation lifecycle reports for the deleted document return unavailable.

Commit and acknowledgement

For a browser update, the plan service batches and validates the document before calling the adapter. PostgreSQL locks the channel row, verifies the expected storage revision and writer fencing token, checks operation idempotency, assigns the next revision and sequence, and writes the update and sidecar atomically. Only then does the service acknowledge or broadcast the mutation.

Server-authored document edits, decisions, transcript entries, implementation graph changes, and lifecycle reports follow the same persistence-before-publication rule.

Research request mutations also commit before their parent-channel research:changed invalidation. Background artifacts remain the lifecycle authority for execution; internal request staging links those jobs rather than copying their state. A completed evidence job can be reconciled idempotently into its private answer job after interruption or the next request read.

When the initial answer artifact completes, publication locks the parent and request, verifies the current answer generation and artifact, creates a deterministic child channel with its complete revision-zero checkpoint, and sets published_channel_id in the same fenced transaction. A failed transaction exposes neither half. Repeating it returns the same child. The complete job lifecycle and extension contract are documented in Background jobs and workers.

A completed marked document-description artifact is likewise reconciled idempotently after job settlement. Only output:"description" V1 artifacts are eligible; readable markerless legacy summaries never populate channel metadata. The projection records source and job provenance under the writer fence but is not a collaboration commit. Generated text is untrusted model output.

Checkpoints and retention

A checkpoint contains canonical MDX, its source hash, complete Yjs bytes, the epoch, generation, covered sequence, storage revision, and a sidecar copy. Saving it atomically replaces the prior checkpoint and deletes channel_updates entries through its covered sequence.

Checkpointing does not prune channel_operations or channel_events. The current schema therefore retains operation idempotency records and any stored events until a separate retention policy or channel deletion removes them. MCP document-update replay records retain the original result source in the sidecar until channel deletion. Repeated rewrites therefore increase sidecar and checkpoint size; no automatic replay-record pruning is currently applied. Operators should account for that behavior in database monitoring and backups. Deleting a document removes it from the live database, not from backups already captured by an operator. Those copies remain governed by the operator's backup retention and restoration policy.

The collaboration tables are a recovery store, not a user-visible edit history. Canonical MDX and current domain records are durable, but the compacted Yjs journal is not an audit log of every keystroke.

Activity history is separate and derived. After a commit changes a document's source, DocumentHistory records the new canonical MDX in document_versions. Changes by the same author less than ten minutes apart extend that author's latest session; another author, Chopin, or a longer pause starts a new one. Browser edits are credited to the member whose batch committed and MCP rewrites to the calling user; Planner and other server-authored edits have no author. Recording happens after acknowledgement, outside the fenced commit, and a failure is logged without affecting the edit, so history can miss a change but never blocks one. Each session keeps a complete copy of the source; no retention policy is applied yet. Restoring a session goes through the same fenced rewrite as an MCP update, credited to whoever restored it, and carries today's decision, questionnaire, research and investigation projections across unchanged.

Opening History asks for one-line summaries of the newest twenty sessions that lack one. A version-summary background job, which needs the document's active Planner owner and uses HISTORY_MODEL, writes them in one turn from the blocks each session removed and added. A summary is stored with the source hash it describes and is shown only while the session still has that source, so a session that grows is summarised again the next time History asks.

Recovery

PostgreSQL recovery reads channel metadata, sidecar, checkpoint, journal, events, and Planner state from one repeatable-read snapshot. The room then:

1. validates the sidecar and checkpoint metadata;
2. loads the complete Yjs document and verifies that it projects to the stored canonical MDX and source hash;
3. replays ordered journal updates from the same epoch;
4. projects and validates the resulting current document; and
5. recovers broken anchors conservatively before returning the open snapshot.

A browser-created channel may have metadata but no snapshot until its first plan:open. MCP creation publishes its initial checkpoint atomically with the metadata.

Planner ownership and sessions

PostgreSQL stores only the process-session ID, user ID, expiry, and timestamps. The serving process alone holds the cookie verifier and GitHub credentials. agent_state.owner_session_id can therefore refer to a current process session without making the database row an authentication credential.

After acquiring the writer lease, every application start deletes all web_sessions rows and clears Planner owner references. Reserved summary and transcript cursor fields and the generation remain unchanged; the current runtime does not advance the summary or cursor, and startup forces status to unavailable. A later owner creates a fresh Copilot SDK session from bounded transcript context and the current document.

The reset keeps one owner reference: a document whose planner_link_holds row names the session that owns it, when that session has credentials and has not expired. A hold is the persisted state of a remote Planner whose link was live when a process released the lease, and the next process rebuilds it, so clearing its owner would end the Planner it is meant to hand over. A session the reset deletes takes its holds with it and its documents' owners are cleared as before. A paired machine's hold names the machine, not a login: its delegated login is a metadata-only session the reset deletes, the document's owner is cleared with it, and the machine's backend claims the document again when it attaches.

A child document has no Chat of its own. Next, still under the writer lease and before any room opens, CollaborationStore.dropChildConversations empties each child's transcript and removes its workflow runs, conversation analysis, card mirrors, and effect outbox from both channel_state and the latest channel_snapshots row, and resets that child's reserved summary and transcript cursor. Question records, comment threads, and the document are untouched. A child whose decision records cite a Chat message or conversation thread would fail restoration without it, so it is left unchanged and its ID is logged; the runtime still never serves or routes to its Chat. A second run finds nothing to clear.

Active external implementation runs are different: their graph lock, run identity, progress, and history remain durable across application restart.

Writer lease

One database-wide chopin:writer lease permits one active application process. A second process refuses startup, unless it was started with HANDOVER=wait: it then waits for the holder's release. The holder renews the lease every 10 seconds on its own connection, outside the shared pool. A renewal attempt that takes more than about five seconds fails and is retried until the safety deadline, 25 seconds after the last successful renewal was sent. A renewal that gets no reply at all is abandoned after six seconds: its connection is closed, its server session is terminated from the shared pool, and the next attempt opens a new connection. PostgreSQL also ends a renewal transaction that sits idle for two seconds, so a session whose client vanished cannot keep the lease row locked. The holder drains and stops if the lease is lost or the deadline passes, and logs when it last renewed and the last renewal error.

Adapter fencing prevents an expired holder from committing even before shutdown completes. Each fenced write holds the lease row FOR KEY SHARE and a per-lease advisory lock, so writes for one lease still run one at a time. A takeover takes the row FOR UPDATE and waits for any write in flight, while renewal only extends expires_at and never waits for a write. Renewal locks the row FOR NO KEY UPDATE before it checks the expiry, so a renewal that waited behind a takeover cannot extend a lease that expired meanwhile. Once the holder starts shutting down, a renewal still in flight neither re-arms the safety deadline nor schedules a retry.

leases.peek(name) reads the lease row with a plain query. It takes no lock, so it neither waits for a holder's writes nor delays them, and reports whether the lease is free (released or expired) on the database's own clock. A process that waits for the lease polls it and calls acquire only once the lease is free.

Fencing tokens compare only within one lease, and every row that is written is written under the writer lease. Nothing waits for a Planner now, so nothing writes or reads deferred Planner turns; see A live database from an earlier release.

This lease is why only one process serves at a time, and the hand-over between two is the holder's release followed by the waiting process's acquisition; see Deploys nobody can see. Several processes serving at once is not supported. See Self-hosting for upgrade and backup guidance.

Migrations

Migrations live with the PostgreSQL adapter and run in order. Each filename and content checksum is recorded in chopin_migrations; changing an applied file is treated as corruption rather than a new migration.

Run migrations from source with:

bun run migrate

The Docker image's default command applies migrations before starting. Replacing that command also bypasses automatic migration. Migration configuration currently loads the complete application configuration, so all required GitHub and OAuth variables must be present even for a standalone migration job.

A live database from an earlier release

An earlier release held a person's Planner request that could not start at once as a row in planner_deferred_turns and marked its Chat entry deliveryState. Chopin holds nothing for a Planner now (a message starts a turn, is given to the running one, or is refused), so a live database may still hold such rows and entries, and they must neither break boot nor reading.

• What changes, and when. Once the process is ready, the holder of the writer lease runs deferredTurns.retire, one fenced statement: every waiting row becomes cancelled, every started row interrupted, and the count is logged once. It is idempotent, so it runs at every start and finds nothing the second time. Only the lease holder writes, so an old process serving beside the new one in an overlapping deploy is never changed under it.
• What a message becomes. A message that had waited stays in its Chat as an ordinary posted message that no Planner took, and its sender asks again if they still want an answer. The stored deliveryState and deliveryFailure of an entry are dropped as the Chat is restored, so it shows no mark; nothing writes them again, and the transcript this release saves is read by the previous release as one without them.
• Additive and reversible. It adds no migration: the table, its columns and its rows stay (only state and resolved_at change), so the previous release, which refuses migration IDs it does not know, runs against this database as it is and reads a table whose rows are all closed. Rolling back needs no database restore for this.
• Deploy note. Deploy when no Planner message is waiting for a machine that is away, or tell the people who sent one to ask again. SELECT count(*) FROM planner_deferred_turns WHERE state IN ('waiting','started') says how many the first start will leave as ordinary messages.

Adding an adapter

1. Implement every store in StorageAdapter under apps/server/src/storage/<driver>.
2. Own the provider's schema and migrations in that directory.
3. Add configuration parsing and one registry entry.
4. Run apps/server/src/storage/contract.ts against the real provider.
5. Add that provider's contract and lifecycle run to CI.

Adapters are compiled into Chopin. Runtime loading of arbitrary storage packages is not supported.

The PostgreSQL contract and process-lifecycle tests run with:

bun run test:postgres