Unify session database: remove channel-based DB path splitting

[jeonghun-jj-lee]

Important

Problem: Channel-based database path splitting fragments session history across multiple .db files (opencode.db, opencode-dev.db, opencode-local.db, branch-named variants) on the same machine. Users who switch between release and dev builds — or run from source — accumulate parallel databases that should be one.

Approach: Remove all channel-based path logic from database.ts:path(). All channels write to opencode.db. A one-time consolidation on first startup merges rows from legacy channel-named DBs (dedup by PK), backs up sources as *.db.merged.

Scope: packages/core/src/database/database.ts (path resolution + consolidation function). No schema changes. No new tables. No migration file needed (this is a path-routing change, not a schema change).

Assumptions: All channel DBs on a given machine share the same schema version (they run identical migrations). Sessions are append-only — no conflicting updates to the same row across DBs.

Acceptance Criteria

• database.ts:path() returns join(Global.Path.data, "opencode.db") for all channels (unless OPENCODE_DB env var is set)
• STABLE_CHANNELS, consolidateLocalDb, OPENCODE_DISABLE_CHANNEL_DB logic removed
• New consolidateChannelDbs(targetPath) function runs once on startup: finds opencode-*.db files, merges via ATTACH + INSERT OR IGNORE per table, renames merged sources to *.db.merged, cleans up WAL/SHM sidecars
• If opencode.db doesn't exist, the largest channel DB is renamed to become it (avoids full copy)
• Consolidation is idempotent (partial failure → retry on next startup)
• Foreign key checks disabled during merge, re-enabled after
• WAL checkpointed on source DBs before ATTACH
• OPENCODE_DB env var override retained (absolute path, relative path, :memory:)
• Merge completes in <5s for combined 700 MB of source DBs
• Existing sessions from both DBs appear in session list after merge

Key Decisions

Decision	Rationale
Merge automatically at startup, not via CLI command	The right behavior should be invisible; most users won't discover a manual command
INSERT OR IGNORE (not REPLACE) for dedup	Preserves the first-written row; sessions are append-only so duplicates are identical
Rename sources to *.db.merged (not delete)	Safety net; user can recover if something goes wrong
Largest DB becomes the base (rename, not copy)	Avoids O(n) copy of the biggest file; merge only inserts the delta from smaller DBs
Checkpoint WAL before ATTACH	SQLite requires WAL to be checkpointed for a clean ATTACH; avoids "database is locked"

Data Contract

Input: ~/.local/share/opencode/opencode-*.db (any file matching the glob)

Output: ~/.local/share/opencode/opencode.db (unified), *.db.merged (backups)

Tables merged (all): session, session_message, session_input, session_context_epoch, session_share, message, part, todo, event, event_sequence, project, project_directory, workspace, account, account_state, control_account, credential, permission, data_migration, migration

Constraints & Invariants

• The consolidation MUST NOT run if OPENCODE_DB env var is set (user has explicit control)
• The consolidation MUST be idempotent
• The consolidation MUST NOT corrupt the target DB on failure (operate in a transaction per source; on error, leave that source un-renamed for next retry)
• The migration table merge uses INSERT OR IGNORE — both DBs have the same migrations applied, so this is a no-op but must not fail

Prior Art

• consolidateLocalDb in the current codebase (same pattern: find branch DBs, pick largest, rename)
• SQLite ATTACH + cross-database INSERT is a well-established pattern for merging databases

Source

ADR: docs/adr/0004-unified-session-database.md

Notes

A standalone merge-opencode-dbs.sh bash script (not in-repo) will be shared for users who want to merge before upgrading. It uses sqlite3 CLI and performs the same ATTACH + INSERT OR IGNORE + backup flow.