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;
• 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:

Table	Purpose
users	GitHub identity and attribution records.
web_sessions	Session metadata and optional browser-secret hashes, encrypted hosted credentials, and credential revisions. Planner ownership references session IDs.
channels	Repository identity, optional parent channel, title, creator, archive and generated-description metadata, storage revision, next sequence, and timestamps.
channel_slugs	One canonical title-derived slug per channel plus retained repository-scoped historical aliases.
channel_state	Current sidecar JSON for a channel and its unanswered_decisions count, written with each sidecar from its question records.
channel_snapshots	Complete Yjs checkpoint, canonical source, source hash, epoch, counters, and checkpoint sidecar.
channel_operations	Per-channel operation idempotency and the revision and sequence assigned to each operation.
channel_updates	Ordered post-checkpoint Yjs update journal.
channel_events	Ordered event capability in the adapter contract. Current collaboration commits do not use it for domain replay.
agent_state	Reserved Planner summary and transcript cursor, status, owner session reference, and ownership generation.
storage_leases	Renewable named leases and fencing tokens.
background_job_channels	Job-state revision per channel, independent of collaboration counters.
background_job_targets	Current generation for each registered channel job target.
background_jobs	Versioned requests, lifecycle, attempts, fenced claims, bounded progress, inputs, and sanitized failures.
background_job_artifacts	Immutable validated results committed atomically with job completion.
research_workspaces	Internal request staging: parent channel, exact brief fields, optional published child, attribution, revision, idempotency, and compatibility counters.
research_turns	Internal initial attempt and compatibility turns with evidence and answer job links.
research_messages	Compatibility transcript rows retained for historical workspace references; not a current product thread.
plan_images	Images uploaded to a document through MCP upload_image: SHA-256, type, bytes, size, uploader, and time, keyed by channel and hash.
machines	Paired local agents: owner, label, repositories, auto-run choice, token hash, encrypted GitHub credentials, contact and revocation times.
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, which Automatic prefers; no row is none. Revoking the machine removes it.

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.

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 database is 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.

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.

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. 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.

This lease is why the current deployment model cannot perform a rolling application replacement against one database. 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.

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