session: delete leaves pre-V2 legacy rows (session/message/part) orphaned in the database #50260

Activity

1. holny commented on Sep 21, 2026

   

   holny

   on Sep 21, 2026

   Contributor

   More actions

   Going to take a swing at this one. Root cause looks straightforward:

   • Session.remove publishes SessionEvent.Deleted (packages/core/src/session.ts:351-361).
   • The projection at packages/core/src/session/projector.ts:543-545 only handles SessionTable (= session_v2). For databases that came in via the V1→V2 path, packages/core/src/database/v1-migration.bun.ts copies from legacy session / message / part / todo into the V2 tables but never drops the legacy tables, so each delete leaves orphan rows behind.

   Fix direction: extend the SessionEvent.Deleted projection (or add a guarded cleanup that only runs when the legacy tables exist) so the same session_id is also removed from the legacy tables. Cascade still works once the parent session row goes (PRAGMA foreign_key_check should stay clean as the report shows). Add a regression test that simulates a V1→V2 migrated DB and asserts the orphan count drops to zero after Session.remove.

   I'll open a PR off this branch in a day or two.
2. holny commented on Sep 28, 2026

   

   holny

   on Sep 28, 2026

   Contributor

   More actions

   Gentle nudge — this has been open for a week with green checks and no review. Three teams have confirmed the fix (including the Obsidian Copilot maintainers who are blocked on it), and the change is scoped to the deleted-session projector: legacy rows are only touched when the legacy session table actually exists, so fresh V2-only databases are unaffected. Happy to rebase or split if anything needs adjusting.
3. GladSI commented on Oct 25, 2026

   

   GladSI

   on Oct 25, 2026

   More actions

   The legacy block exists without any session delete — and it is most of what a compacted database still holds

   The projector fix you describe is right; one addition, because there is a second half to the leak that does not involve SessionEvent.Deleted at all.

   Measured on 2.1.4, data dir upgraded in place (so both stores exist), nothing ever deleted:

   • session 185 rows, message 29 772, part 124 309, todo 170 — all frozen at 2026-10-17 06:02, i.e. nothing has written them since the V1→V2 conversion, and nothing in V2 reads them outside the opt-in V1Migration service (/api/migration, a noop on node).
   • They still hold 615 MiB of live payload (message 434 MiB + part 181 MiB, plus their indexes). These rows were never orphaned by a delete; they simply have no V2 counterpart, so the projector never sees them.
   • VACUUM reclaims deleted pages only, so those 615 MiB are unrecoverable while the rows stay: this database was 17 203 MiB with a 16 414 MiB freelist, and after VACUUM it is 780 MiB — the legacy generation is by far the largest thing left in the compacted file.

   So a fix that removes legacy rows on session delete leaves the most common upgrade path (a user who never deleted a session) untouched. Dropping the legacy tables after a successful migration — or exposing it through the opencode db cleanup proposed in #54618 — closes that half.
4. opencodejr commented on Oct 28, 2026

   

   opencodejrBot

   on Oct 28, 2026

   More actions

   Thanks for the detailed report. I can confirm this on 2.0.11, and it still happens on the latest dev (fa1260d).

   What I did, in an isolated data dir:

   1. Created a legacy session (one message, one part) with v1 (opencode-ai@1.19.5, opencode import).
   2. Ran v2 opencode session list, which imported it into session_v2 / session_message. The legacy rows stayed.
   3. Ran opencode session delete <id>, which reported success.

   After that, session_v2 and session_message had no rows for the session, but the legacy session, message and part rows were all still in opencode.db, matching what you saw. The script below reproduces it.

   The cause seems to be that the delete step only removes the V2 session row, while the V1 import intentionally leaves the original rows in place. Two earlier fix attempts for this issue were closed without merging (#50270, #50545). Until there's a fix, your SQL cleanup followed by VACUUM (with opencode and its background service stopped) is the workaround. Related: #44028 (sessions split across session and session_v2) and #47591 (deleted space not reclaimed).

   Reproduction script (repro.sh)

   #!/usr/bin/env bash
   # Repro for #50260: deleting a migrated V1 session in V2 leaves legacy session/message/part rows.
   # Needs: bun, git. Runs in an isolated XDG home.
   # V2 command: $OC2 (defaults to `opencode` on PATH, e.g. @opencode/cli@2.0.11)
   # Example: dev checkout: OPENCODE_DISABLE_CHANNEL_DB=1 OC2=/path/to/dev-wrapper ./repro.sh
   set -euo pipefail
   OC2=${OC2:-opencode}
   W=$(mktemp -d /tmp/opencode/repro50260.XXXX)
   export HOME=$W/home XDG_DATA_HOME=$W/data XDG_CONFIG_HOME=$W/config XDG_STATE_HOME=$W/state XDG_CACHE_HOME=$W/cache
   mkdir -p "$HOME" $W/proj && cd $W/proj && git init -q && git -c user.email=a@b -c user.name=a commit -q --allow-empty -m init
   (cd $W && bun add opencode-ai@1.19.5 >/dev/null 2>&1)
   V1=$W/node_modules/.bin/opencode
   NOW=$(date +%s000)
   SID=ses_0000000000001AAAAAAAAAAAAA
   cat > $W/s.json <<J
   {"info":{"id":"$SID","slug":"test","projectID":"global","directory":"$W/proj","title":"legacy session","version":"1.19.5","time":{"created":$NOW,"updated":$NOW}},
    "messages":[
     {"info":{"id":"msg_0000000000001AAAAAAAAAAAAA","sessionID":"$SID","role":"user","time":{"created":$NOW},"agent":"build","model":{"providerID":"x","modelID":"y"}},
      "parts":[{"id":"prt_0000000000001AAAAAAAAAAAAA","sessionID":"$SID","messageID":"msg_0000000000001AAAAAAAAAAAAA","type":"text","text":"hello from v1"}]}
    ]}
   J
   $V1 import $W/s.json
   DB=$XDG_DATA_HOME/opencode/opencode.db
   q(){ bun -e 'import {Database} from "bun:sqlite";const d=new Database(process.argv[1]);for(const st of process.argv[2].split(";").filter(x=>x.trim()))try{console.log(Object.values(d.query(st).get()).join(" = "))}catch(e){console.log(st.trim(),"->",e.message)}' "$DB" "$1"; }
   echo "--- after V1 import:"; q "select 'session',count(*) from session; select 'message',count(*) from message; select 'part',count(*) from part;"
   echo "--- V2 session list (triggers V1->V2 migration):"; $OC2 session list 2>&1 | grep -v "^\s*at " | head -30 || true
   echo "--- after migration:"; q "select 'session_v2',count(*) from session_v2 where id='$SID'; select 'legacy session',count(*) from session where id='$SID'; select 'session_message',count(*) from session_message where session_id='$SID';"
   echo "--- V2 delete:"; $OC2 session delete $SID 2>&1 | tail -3 || true
   echo "--- after delete:"; q "select 'session_v2',count(*) from session_v2 where id='$SID'; select 'session_message',count(*) from session_message where session_id='$SID'; select 'legacy session',count(*) from session where id='$SID'; select 'legacy message',count(*) from message where session_id='$SID'; select 'legacy part',count(*) from part where session_id='$SID';"
   echo "--- stopping background service"; $OC2 service stop >/dev/null 2>&1 || true

   Run it: bash repro.sh
5. opencodejr

   added

   reproduced

   on Oct 28, 2026