[Windows] re-embed migration silently fails: deleteShard unlinkSync error swallowed leads to UNIQUE constraint failures and broken vector search

[junyuyuan]

Environment

• OS: Windows 11 (win32 x64)
• opencode-mem: latest (installed via opencode plugin cache, ~/.cache/opencode/packages/opencode-mem@latest)
• opencode: 1.18.9
• Runtime: Bun 1.3.14 (standalone executable)
• Storage: local Turso/libSQL, single project shard with 1114 memories

Summary

When switching the embedding model (dimension change, e.g. Xenova/multilingual-e5-small 768-dim → text-embedding-3-small 1536-dim via remote API), triggering the re-embed migration via POST /api/migration/run with {"strategy":"re-embed"} returns success but reEmbeddedMemories: 0 — all 1114 memories fail with UNIQUE constraint failed: memories.id. The migration silently leaves all vectors at the old dimension while shard_metadata declares the new dimension, making vector search return empty results with no visible error.

Root Cause

The bug is in deleteShard (dist/services/sqlite/shard-manager.js:255-286). On Windows, fs.unlinkSync(fullPath) fails because the .db file is still locked by the running opencode process. The error is caught and only logged, not re-thrown:

// shard-manager.js:271-282
try {
    const fs = require("node:fs");
    if (fs.existsSync(fullPath)) {
        fs.unlinkSync(fullPath);  // throws on Windows (file locked)
    }
}
catch (error) {
    log("Error deleting shard file", {
        dbPath: fullPath,
        error: String(error),
    });
    // BUG: error is swallowed — execution continues
}
const deleteStmt = this.metadataDb.prepare(`DELETE FROM shards WHERE id = ?`);
deleteStmt.run(shardId);  // metadata record deleted, but .db file still on disk

This creates an inconsistent state:

1. The shards row in metadata.db is deleted (so getActiveShard returns null)
2. The .db file remains on disk with old vectors + old shard_metadata

When reEmbedMigration (migration-service.js:172-194) continues:

1. getWriteShard → getActiveShard returns null → createShard is called
2. createShard opens a connection to the same file path (it still exists)
3. initShardDb runs INSERT OR REPLACE INTO shard_metadata → overwrites dimension to 1536
4. But the memories table still has all 1114 old rows with 768-dim vectors
5. insertVector (vector-search.js:23-30) uses INSERT INTO memories (not UPSERT)
6. Every insert hits UNIQUE constraint failed: memories.id → caught + logged → reEmbeddedCount stays 0

The migration returns:

{
  "success": true,
  "strategy": "re-embed",
  "deletedShards": 1,
  "reEmbeddedMemories": 0,   // ← all failed, but success=true
  "duration": 431254
}

Reproduction

1. Start with opencode-mem using a local embedding model (e.g. Xenova/multilingual-e5-small, 768-dim) and accumulate memories
2. Edit opencode-mem.jsonc to switch to a different-dimension model (e.g. remote text-embedding-3-small, 1536-dim):

   {
     "embeddingApiUrl": "https://api.openai.com/v1",
     "embeddingApiKey": "sk-...",
     "embeddingModel": "text-embedding-3-small"
   }

3. Restart opencode
4. Call GET /api/migration/detect → confirms needsMigration: true, storedDimensions: 768, configDimensions: 1536
5. Call POST /api/migration/run with body {"strategy":"re-embed"}
6. Observe: returns success: true, reEmbeddedMemories: 0
7. Check opencode-mem.log → 1114 lines of Migration: error re-embedding memory: {"memoryId":"...","error":"SQLiteError: UNIQUE constraint failed: memories.id"}
8. GET /api/migration/detect now returns needsMigration: false (false positive — metadata was overwritten)
9. memory search returns 0 results for any query (dimension mismatch: 1536-dim query vector vs 768-dim stored vectors)

Impact

• Data integrity: Memory content/tags/metadata are preserved, but all vectors become unsearchable
• Silent failure: Migration reports success: true; detect reports needsMigration: false — the user has no indication anything is wrong
• Platform-specific: Only affects Windows (Unix can unlink locked files). macOS/Linux users won't hit this.
• Recovery: Requires manual workaround (see below) — the built-in re-embed migration cannot fix itself

Suggested Fixes

Option A (minimal): Re-throw the unlinkSync error so migration fails loudly instead of continuing in an inconsistent state:

catch (error) {
    log("Error deleting shard file", { dbPath: fullPath, error: String(error) });
    throw error;  // let caller handle — don't continue with stale file
}

Option B (robust): Use INSERT OR REPLACE (UPSERT) in insertVector so re-embedding existing IDs overwrites instead of failing:

// vector-search.js:24-29
const insertMemory = db.prepare(`
  INSERT OR REPLACE INTO memories (
    id, content, vector, ...
  ) VALUES (?, ?, ?, ...)
`);

Option C (defense-in-depth): In reEmbedMigration, after deleteShard, verify the file is actually gone before proceeding; or explicitly DROP TABLE memories / DELETE FROM memories on the target shard before inserting.

Workaround

The tag-migration endpoint (POST /api/migration/tags/run-batch) iterates all memories and uses UPDATE memories SET vector = ? (api-handlers.js:921) instead of INSERT, so it bypasses the UNIQUE constraint issue. Running it to completion re-embeds all vectors correctly:

# Loop until hasMore: false
curl -X POST http://127.0.0.1:4747/api/migration/tags/run-batch \
  -H "Content-Type: application/json" \
  -d '{"batchSize":25}'

Verified: 1114/1114 memories re-embedded in ~14.5 min, 0 errors, vector search restored.

Related

• It will cause opencode crash on Windows #168 (Windows crash — different issue, but same Windows platform)

Logs

Excerpt from opencode-mem.log (1114 identical errors, one per memory):

[2026-07-30T08:49:34.946Z] Migration: error re-embedding memory: {"memoryId":"mem_1782802419337_8mnu1wn4x","error":"SQLiteError: UNIQUE constraint failed: memories.id"}
[2026-07-30T08:49:35.448Z] Migration: error re-embedding memory: {"memoryId":"mem_1782800273006_pom1jdol5","error":"SQLiteError: UNIQUE constraint failed: memories.id"}
... (1114 lines)

No "Error deleting shard file" log line was emitted in my case (the unlinkSync may have succeeded in deleting the file handle but the connection pool still held the file open, or the file was recreated by createShard before insert). The end result is the same: memories table retains old rows when insertVector runs.

[EyJunge1]

Thanks for the thorough report. The code path shown in the report does not appear to match the current npm release.

opencode-mem@2.22.0 uses the Turso implementation under dist/services/turso. Its re-embed migration creates and verifies a temporary shard before replacing the original, so it does not use the reported deleteShard flow.

The referenced dist/services/sqlite/shard-manager.js likely belongs to an older cached release. Could you check the actual version in the cached package’s package.json? The directory name opencode-mem@latest may remain unchanged even when its contents are outdated.

If the installed version is older than 2.22.0, please clear the OpenCode package cache and reinstall. If it is already 2.22.0, please share the relevant dist path and fresh logs so we can investigate further.