Data loss during session import: INSERT OR REPLACE on project triggers ON DELETE CASCADE

[logser13]

Summary

opencode-github-sync 3.0.1 can delete previously imported sessions during shard import when multiple sessions reference the same OpenCode project (for example project_id = "global").

The importer currently uses INSERT OR REPLACE in src/core/sessions.ts. In SQLite, REPLACE deletes the conflicting row before inserting the replacement. OpenCode's session.project_id -> project.id foreign key uses ON DELETE CASCADE, so replacing the shared project row cascades into deleting sessions that were imported earlier in the same run.

This is a data-loss bug because the import can report success while most imported sessions are gone from the local DB.

Environment

• opencode-github-sync: 3.0.1
• OpenCode Desktop / CLI: 1.18.30
• Node.js: 24.15.0
• Windows 11

Relevant schema

CREATE TABLE project (
  id text PRIMARY KEY,
  ...
);

CREATE TABLE session (
  id text PRIMARY KEY,
  project_id text NOT NULL,
  ...,
  FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE
);

Reproduction

1. Have two or more session shards whose session.project_id is the same, e.g. global, and whose shard also contains the same project.id = global row.
2. Import the shards into a local OpenCode DB with importSessions().
3. The importer calls the generic upsert() for project for every shard.
4. upsert() currently executes:

db.prepare(
  `INSERT OR REPLACE INTO "${table}" (${quoted}) VALUES (${placeholders})`
).run(...values)

5. When the second shard replaces project.id = global, SQLite deletes the existing project row first. Because session.project_id has ON DELETE CASCADE, the session imported from the previous shard is deleted.
6. This repeats for subsequent shards, so only the last imported session for that shared project survives.

Observed result

In a real import with 11 session shards sharing project_id = global, the importer reported:

{"imported":10,"skippedOlder":1,"failed":[]}

but the database contained only 1 session afterwards.

The issue was reproduced against a backup copy of the OpenCode DB, so the result was not caused by OpenCode running concurrently.

Expected result

All non-skipped session shards should remain present after a successful import, and an import result that reports success should not silently delete sessions imported earlier in the same operation.

Tested fix

Replacing INSERT OR REPLACE with a true SQLite UPSERT avoids the implicit delete and therefore avoids the cascade:

const assignments = columns
  .map((column) => `"${column}" = excluded."${column}"`)
  .join(", ");

db.prepare(
  `INSERT INTO "${table}" (${quoted}) VALUES (${placeholders}) ` +
  `ON CONFLICT DO UPDATE SET ${assignments}`
).run(...values);

With that change, importing the same data produced:

project = 1
session = 11
message = 2913
part = 10271
failed = 0

So the same 11 session shards were retained successfully.

Suggested regression test

A minimal test should import two shards that share one project:

1. Import session A with project_id = global.
2. Import session B with project_id = global.
3. Assert project global exists.
4. Assert session A still exists.
5. Assert session B exists.
6. Assert messages/parts for both sessions still exist.

It may also be worth avoiding INSERT OR REPLACE for other imported tables with foreign-key dependents, since the same delete-and-reinsert semantics can trigger cascades elsewhere.