# 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. 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](authentication.md#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](conversation-research.md).

## 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;
- revision-checked credential rotation that cannot recreate a deleted session;
- startup removal of expired and metadata-only sessions and every Planner owner reference;
- 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](architecture.md) before changing
recovery or acknowledgement behavior. See
[Background jobs](background-jobs.md) 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.                                                  |

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

Channel titles have a case-insensitive unique constraint within each repository.
Slug values are also unique per repository, with one canonical slug per channel.
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 titles and 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 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](background-jobs.md).

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.

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 while serving and
drains and stops if renewal fails or its safety deadline passes. Adapter fencing
prevents an expired holder from committing even before shutdown completes.

This lease is why the current deployment model cannot perform a rolling
application replacement against one database. See
[Self-hosting](self-hosting.md) 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:

```bash
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:

```bash
bun run test:postgres
```