opencode/docs/design/v1-v2-database-migration.md

V1 to V2 Database Migration

Approach

• Use the dev branch database schema and migration registry as the V1 baseline.
• Remove migrations that exist only on the V2 branch.
• Generate one canonical migration from the dev schema to the final V2 schema.
• Add explicit data operations to that migration where generated DDL is insufficient.
• Test the migration against a populated database at the exact dev schema.

Preserve

The canonical V1 data remains in its existing tables. In particular, preserve session, message, and part rows.

Preserve workspace rows and existing session.workspace_id values unchanged. The migration must not clear or rebuild workspace relationships.

Keep the todo table and its data unchanged. V2 does not currently migrate todos into another representation, and the generated migration must not drop the table.

Truncate

Truncate these pre-launch V2 tables before applying schema changes:

• event
• event_sequence
• session_message

These rows are not canonical V1 data. Truncating event before adding the required event.created column means the column needs neither a backfill nor a default. After truncation, rebuild session_message from canonical V1 message and part rows rather than retaining its pre-launch V2 contents.

Message Backfill

Backfill canonical V1 history from message and part into session_message. This is the main data transformation in the migration. Preserving the V1 tables alone keeps the data safe but does not make existing history visible through the V2 session APIs, which read session_message.

Reuse each V1 message.id as the corresponding session_message.id. Stable IDs keep the migration deterministic and avoid rewriting other persisted state that may refer to a message.

Within each session, order V1 messages by time_created and then id, matching the existing V1 message index. Assign contiguous session_message.seq values starting at 0.

Map ordinary V1 messages one-to-one by role. Each ordinary V1 user message becomes one V2 user row, and each ordinary V1 assistant message becomes one V2 assistant row. Fold the source message's ordered V1 parts into that row's V2 payload.

Handle semantic marker parts before applying the ordinary mapping. In particular, a V1 user message containing a compaction part and its paired assistant summary represent one compaction operation, not two ordinary messages. Special part mappings must be decided explicitly before implementing the backfill.

V1 synthetic content is represented by user text parts with synthetic: true, not by a separate message role. A V1 user message whose visible text parts are all synthetic should become a V2 synthetic message. If a V1 user message mixes ordinary and synthetic content, preserve the ordinary content in the V2 user row and emit the synthetic content as an adjacent V2 synthetic row. Ignore text parts marked ignored, matching V1 model-history behavior.

Use the V1 compaction user message ID as the ID of the collapsed V2 compaction message. This matches V2's use of the admitted compaction input ID and preserves references to the initiating message.

For a completed compaction, create one V2 compaction row with status: "completed". Set reason from the V1 compaction part's auto flag, join the paired summary assistant's nonempty text parts with blank lines for summary, and serialize the retained V1 tail beginning at tail_start_id for recent. Use an empty recent value when no tail was retained, and use the compaction user message creation time. Do not emit the paired summary assistant as a separate V2 assistant row.

After rebuilding session_message, seed event_sequence with one row per migrated session. Set its watermark to that session's maximum backfilled session_message.seq. This prevents new V2 events from reusing sequence numbers or sorting before migrated history. The event table remains empty.

Drop

Drop these pre-launch V2 tables without preserving or transforming their rows:

• session_input
• session_context_epoch

Do not transfer session_input rows into session_pending.

Create Empty

Let the generated migration create these tables empty:

• instruction_blob
• instruction_entry
• instruction_state
• session_pending
• kv

V1 has no canonical data to backfill into these tables. V2 initializes their state as it runs.

Fork Storage

V1 has no fork-boundary state to backfill. New V2 forks use a required message boundary and persist it in session.fork_boundary. The durable fork event contains no parent sequence. Its resolved boundary is one of:

• before: copy messages before the identified message.
• through: copy messages through the identified message.

Forking an empty session is not supported. session.fork_seq and session.fork_message_id are not part of the final V2 schema.

New nullable session columns, including fork_session_id, fork_boundary, and time_suspended, require no explicit backfill. Existing rows naturally receive NULL when the generated migration adds the columns.

Verification

The canonical migration test should seed representative V1 sessions, messages, parts, todos, projects, accounts, credentials, permissions, shares, and workspaces. After migration, it should verify:

• Preserved rows and encoded values remain unchanged.
• Todo rows remain available in the unchanged todo table.
• event is empty, and stale pre-launch rows are absent from the rebuilt projections.
• Backfilled session_message rows represent the canonical V1 message and part history.
• Each migrated session's event_sequence watermark matches its maximum backfilled message sequence.
• Dropped tables no longer exist.
• New tables exist and are empty.
• The final schema has no ungenerated changes.