thedotmack-claude-mem/plans/2026-06-20-sqlite-duplicate-stack-removal.md

Plan: Remove the entire duplicate SQLite stack + rewrite its tests against the shipping path

Scope: src/services/sqlite/ — delete the dead duplicate DB/migration stack AND the parallel free-function CRUD API it propped up. Delete every test coupled to that duplicate and write fresh tests against SessionStore (the path the worker actually runs). Out of scope: installer unification, SearchManager dedup (separate plans).

Philosophy (per maintainer): Do NOT adapt old tests written against the dead abstraction — that's busywork. Delete them, write new ones against SessionStore. The old tests' value is their behavior checklist, not their code; that checklist is captured in Phase 1.

Goal: Remove ~3,000+ src lines of duplication, zero production behavior change, and replace ~2,300 lines of misdirected tests with a smaller, focused suite that exercises the real worker path.

Why this exists: Two parallel SQLite systems live in src/services/sqlite/:

• Shipping (KEEP): SessionStore — the worker does new Database(DB_PATH) → new SessionStore(db), and SessionStore's constructor runs its own inline imperative migrations. All production reads/writes go through SessionStore methods.
• Dead duplicate (DELETE): ClaudeMemDatabase → MigrationRunner (a second, drifted migration engine) plus a free-function CRUD API (Sessions.ts/Observations.ts/… barrels + submodules + transactions.ts) that re-implements the SessionStore methods. Reachable only from tests.

---

Phase 0 — Ground Truth (verified by 6 discovery agents + direct grep; re-confirm gates as you go)

Shipping path (KEEP, never touch)

• src/services/worker/DatabaseManager.ts:17-32 → new Database(DB_PATH) then new SessionStore(this.db).
• SessionStore ctor (SessionStore.ts:34, chain ~49-73) runs the inline migrations (schema_versions 4-32).
• CLI server: worker-service.ts:855 opens new Database(DB_PATH,…) — also no MigrationRunner/ClaudeMemDatabase.
• The worker never imports ClaudeMemDatabase, MigrationRunner, getDatabase, initializeDatabase, the services/sqlite/index.ts barrel, or the free-function CRUD API. Production's only direct imports into the free-fn tree are 5 helper functions (see keep-set below).

SessionStore API the new tests will call (verified signatures)

Constructor: constructor(dbPathOrDb: string | Database = DB_PATH) — accepts ':memory:' AND a raw bun:sqlite Database (adopts it, then migrates → use this to seed a legacy schema first). Public raw handle: store.db. Has close().

Domain	Method (SessionStore.ts line)
Sessions	createSDKSession(contentSessionId, project, userPrompt, customTitle?, platformSource?): number (1692); updateMemorySessionId(sessionDbId, memId|null) (1030); markSessionCompleted(sessionDbId) (1038); ensureMemorySessionIdRegistered(sessionDbId, memId, workerPort?) (1048); getSessionById(id) (1624)
Observations	storeObservation(memId, project, observation{type,title,subtitle,facts[],narrative,concepts[],files_read[],files_modified[],agent_type?,agent_id?,metadata?}, promptNumber?, discoveryTokens?, overrideTimestampEpoch?, generatedByModel?): {id,createdAtEpoch} (1781); storeObservations(memId, project, observations[], summary|null, promptNumber?, …): {observationIds[],summaryId,createdAtEpoch} (1901); getObservationById(id) (1475)
Summaries	storeSummary(memId, project, summary{request,investigated,learned,completed,next_steps,notes}, promptNumber?, …): {id,createdAtEpoch} (1855); getSummaryForSession(memId) (1555)
Prompts	saveUserPrompt(contentSessionId, promptNumber, promptText): number (1754); getPromptNumberFromUserPrompts(contentSessionId): number (1685); findRecentDuplicateUserPrompt(contentSessionId, promptText, windowMs) (1406)

3 old free-functions have no SessionStore method — new tests that need them import the leaf function and pass store.db: computeObservationContentHash (observations/store.ts:8), getFirstObservationCreatedAt (observations/recent.ts:36), getObservationsByFilePath (observations/get.ts:97). All three are in the keep-set anyway.

Test gotchas (from source, save debugging time):

• getSessionSummaryById class method (SessionStore.ts:2435) queries non-existent columns → throws; don't call it (it's deleted in Phase 5 regardless).
• getRecentObservations reads the legacy text column → NULL for rows written by storeObservation. Assert content via getObservationById (title/subtitle/narrative), not text.
• Empty-project guard (project || cwd-derived) exists only in leaf observations/store.ts, not in SessionStore.storeObservation/.storeObservations. The worker path stores empty project as-is. Test the leaf for the guard; don't assert it on SessionStore.
• pending_messages has no SessionStore store method — seed via raw store.db.prepare(INSERT…). For the cleanup test, observer cascade needs memory_session_id set: createSDKSession inserts NULL, so call updateMemorySessionId(id,'obs-memory-N') before storeObservation.

Dead duplicate — DELETE (all production-dead, grep-confirmed)

1. DB/migration stack: Database.ts (ClaudeMemDatabase, the sqlite DatabaseManager singleton — NOT the worker's, getDatabase, initializeDatabase, Migration), migrations/runner.ts (MigrationRunner, 1147 lines), index.ts (barrel, 0 importers), and SchemaRepair (openWithSchemaRepair, imported only by Database.ts).
2. Free-function CRUD API: all 6 barrels + submodule files + transactions.ts (see keep/delete map). transactions.ts confirmed dead: 0 direct prod importers; worker uses sessionStore.storeObservations.
3. Dead SessionStore methods: getSessionSummaryById (2435-2476, dead+broken), storeObservationsAndMarkComplete (2017-2143, 0 call sites — .storeObservationsAndMarkComplete( greps to zero).

Free-function tree — KEEP/DELETE map

KEEP (5 files, trimmed to ONLY the live export — production imports these directly):

File	Keep ONLY	Live importer
observations/store.ts	computeObservationContentHash	SessionStore.ts:15
observations/files.ts	parseFileList	SessionStore.ts:16, ChromaSync.ts:7
observations/get.ts	getObservationsByFilePath	DataRoutes.ts:17
observations/recent.ts	getFirstObservationCreatedAt	DataRoutes.ts:18
prompts/get.ts	findRecentDuplicateUserPrompt	SessionStore.ts:18

Each survivor's only in-tree dep is a type-only ./types.js import; after trimming, those type imports become removable (the survivors use types from ../../../types/database.js, out of tree). Net: keep 5 function bodies, drop their ./types.js import lines.

DELETE ENTIRELY (18 files — only importers are the dead barrels/index/tests):

Observations.ts Sessions.ts Summaries.ts Prompts.ts Timeline.ts Import.ts   # 6 barrels
transactions.ts
sessions/create.ts sessions/get.ts sessions/types.ts
summaries/store.ts summaries/get.ts summaries/recent.ts summaries/types.ts
prompts/store.ts prompts/types.ts
timeline/queries.ts
import/bulk.ts

sessions/, summaries/, timeline/, import/ dirs become empty → remove.

The one risk — CONFIRMED SAFE

Server tables (projects, server_sessions, memory_items, teams, api_keys, audit_log, …) are created by ensureServerStorageSchema (src/storage/sqlite/schema.ts:21-305), called from 13 live server-repo sites — not by MigrationRunner. Deleting the duplicate doesn't touch them.

Anti-pattern guards (every phase)

• ❌ Never delete a src/services/sqlite/ file before grepping its direct importers (excluding the dead index.ts).
• ❌ Don't touch worker/DatabaseManager.ts, SessionStore's migration chain, or src/storage/sqlite/*.
• ❌ Don't write new tests against the leaf free-functions where a SessionStore method exists — the worker uses methods; test the method. (Exceptions: the 3 leaf-only helpers above.)
• ❌ Don't port schema-repair or MigrationRunner-version-specific assertions — those test code the worker never runs.

---

Phase 1 — Write NEW tests against SessionStore (FIRST, so coverage exists before any deletion)

These must pass against the current tree (SessionStore + the 5 survivor leaf functions all exist now). They become the regression guard for Phases 2-5. New-file layout + behavior spec (KEEP items from the old suites, retargeted):

tests/sqlite/session-store-observations.test.ts — new SessionStore(':memory:')

• store returns {id>0, createdAtEpoch>0}; all fields round-trip via getObservationById
• overrideTimestampEpoch honored (epoch + ISO); default = now when omitted
• null subtitle/narrative stored OK; getObservationById returns null for missing id
• subagent: agent_type/agent_id stored when provided; default NULL when omitted; agent_type alone OK
• getFirstObservationCreatedAt(store.db) → null when empty, earliest ISO otherwise (leaf import)

tests/sqlite/session-store-dedup.test.ts

• computeObservationContentHash (leaf import): deterministic, 16 chars, different content→different hash, null title/narrative OK, no field-boundary collision (\x00 separator → 4 distinct hashes — keep verbatim)
• identical (memId,title,narrative) dedupes to same id regardless of time gap (collapse the old two "30s window" tests into ONE — dedup is the UNIQUE index, not time-based)
• different content at same timestamp → distinct ids; content_hash populated (16 chars) on new rows
• storeObservations batch: 3 identical inputs → 3 equal ids, 1 physical row (real worker hot path)
• dedup unaffected by agent fields: 2nd insert w/ different agent_type returns existing id, count stays 1, original agent fields preserved
• (optional, leaf) empty-project guard on observations/store.ts leaf only — note SessionStore stores empty as-is

tests/sqlite/session-store-sessions.test.ts

• createSDKSession → id>0; idempotent (same content_session_id→same id); different→different
• persisted user_prompt tag-stripped + bounded to MAX_STORED_PROMPT_CHARS ending …
• getSessionById round-trips fields; memory_session_id defaults null; null for missing
• custom_title: stored at creation; defaults null; backfilled on idempotent call if unset; not overwritten if set; empty→null
• platform_source: defaults 'claude'; preserves non-default when legacy caller omits it; throws /Platform source conflict/ on explicit conflict
• updateMemorySessionId sets + allows re-update to different value

tests/sqlite/session-store-prompts.test.ts

• saveUserPrompt → id>0, incrementing, distinct across sessions; prompt_text tag-stripped + bounded
• findRecentDuplicateUserPrompt finds dup in window (id/prompt_number/prompt_text)
• getPromptNumberFromUserPrompts: 0 when none; counts; session-isolated; handles 100 prompts

tests/sqlite/session-store-summaries.test.ts

• storeSummary → {id>0,createdAtEpoch>0}; all fields + prompt_number round-trip via getSummaryForSession
• overrideTimestampEpoch honored; default = now; null notes preserved
• getSummaryForSession: by memId; null when none; returns MOST RECENT when multiple

tests/sqlite/session-store-transactions.test.ts — target sessionStore.storeObservations (the real path)

• stores N atomically → ids + null summaryId when no summary; correct createdAtEpoch
• all observations in a batch share one timestamp
• observations + summary together → summaryId non-null, summary retrievable
• empty observations array → 0 ids, null summary; summary-only → 0 ids, summaryId set
• promptNumber applied to all in batch
• DROP the old storeObservationsAndMarkComplete queue-delete/rollback tests — that path is dead (worker completes via SessionCompletionHandler, not this function)

tests/sqlite/session-store-migrations.test.ts — seed legacy via new Database(':memory:') then new SessionStore(rawDb)

• legacy NULL content_hash rows → rewritten to __null_migration_<id>__ (preserved), non-NULL dups deduped to one, ux_observations_session_hash UNIQUE index created (this is SessionStore's v29 addObservationsUniqueContentHashIndex; the existing data-integrity "Migration parity" test is the canonical source — port it here)
• idempotency: constructing SessionStore twice over the same db → no throw, identical schema/version set, data unchanged
• fresh-DB init creates SessionStore's core tables (schema_versions, sdk_sessions, observations, session_summaries, user_prompts, pending_messages) — assert SessionStore's tables, NOT MigrationRunner's server tables
• (optional) PRAGMA foreign_key_list shows on_update=CASCADE,on_delete=CASCADE on a fresh SessionStore db
• (optional) seed a legacy pending_messages with retry_count/completed_at_epoch/worker_pid → SessionStore drops them (v31/v32)
• DROP: MigrationRunner-only server tables (v33/34), specific cross-stack version-number lists, mig-24 drift, #979 old-DatabaseManager conflicts, crash-recovery _new temp tables

tests/infrastructure/cleanup-v12_4_3.test.ts — REWRITE in place: reseed seedDatabase via new SessionStore(dbPath) + methods (raw store.db INSERT for pending_messages only). All behaviors KEEP:

• missing DB → marker skipped:'no-db', null backupPath, zero counts
• purges observer sessions (OBSERVER_SESSIONS_PROJECT) + cascade rows, purges stuck pending (COUNT>=10), wipes chroma dir + sync-state, writes backup; real-project rows survive
• pending preserved when stuck count < 10 (9 survives); idempotent (2nd run no-ops, no 2nd backup)
• proceeds on non-credible statfsSync (bsize=0) with WARN containing 'non-credible' {bsize:0} (keep the spy assertion)
• honors CLAUDE_MEM_SKIP_CLEANUP_V12_4_3=1 (exits, no marker, observer intact)

Verify Phase 1: bun test tests/sqlite/session-store-*.test.ts tests/infrastructure/cleanup-v12_4_3.test.ts → all green against the current tree. New tests must import ONLY SessionStore + the 5 survivor leaf functions — grep them to confirm no import of a to-be-deleted barrel/transactions.ts.

---

Phase 2 — Delete the old tests coupled to the dead stack

rm tests/sqlite/observations.test.ts tests/sqlite/transactions.test.ts \
   tests/sqlite/sessions.test.ts tests/sqlite/prompts.test.ts \
   tests/sqlite/summaries.test.ts tests/sqlite/data-integrity.test.ts \
   tests/services/sqlite/observations/store-subagent-label.test.ts \
   tests/services/sqlite/migration-runner.test.ts \
   tests/services/sqlite/schema-repair.test.ts

(schema-repair.test.ts has NO replacement — it tests ClaudeMemDatabase.openWithSchemaRepair, which the worker never uses.) Verify: grep -rn "ClaudeMemDatabase\|MigrationRunner\|runAllMigrations\|sqlite/transactions\|sqlite/Sessions\|sqlite/Observations" tests/ → ZERO. bun test tests/ green (new suite carries the coverage).

---

Phase 3 — Delete the dead DB/migration stack

Gate:

grep -rn "from .*services/sqlite/Database" src/ --include=*.ts | grep -v "Database.ts:"   # ZERO
grep -rn "services/sqlite/index" src/ tests/ --include=*.ts                                # ZERO
grep -rn "ensureServerStorageSchema" src/ --include=*.ts                                    # 13 callers, none in runner.ts

rm src/services/sqlite/Database.ts src/services/sqlite/migrations/runner.ts src/services/sqlite/index.ts
rmdir src/services/sqlite/migrations
grep -rn "SchemaRepair\|openWithSchemaRepair" src/ --include=*.ts   # expect ZERO after Database.ts gone
rm src/services/sqlite/SchemaRepair.ts   # use actual path from grep; only if zero importers

Verify: bunx tsc --noEmit — no dangling imports.

---

Phase 4 — Delete the duplicate free-function CRUD API + trim survivors

Gate (per group, must be zero non-barrel/non-test importers):

grep -rn "sqlite/transactions\|sqlite/Sessions\|sqlite/Observations\|sqlite/Summaries\|sqlite/Prompts\|sqlite/Timeline\|sqlite/Import" src/ --include=*.ts
grep -rn "sessions/create\|sessions/get\|summaries/store\|summaries/get\|summaries/recent\|prompts/store\|timeline/queries\|import/bulk" src/ --include=*.ts

Delete (18 files):

rm src/services/sqlite/{Observations,Sessions,Summaries,Prompts,Timeline,Import}.ts
rm src/services/sqlite/transactions.ts
rm src/services/sqlite/sessions/{create,get,types}.ts
rm src/services/sqlite/summaries/{store,get,recent,types}.ts
rm src/services/sqlite/prompts/{store,types}.ts
rm src/services/sqlite/timeline/queries.ts src/services/sqlite/import/bulk.ts
rmdir src/services/sqlite/sessions src/services/sqlite/summaries src/services/sqlite/timeline src/services/sqlite/import

Trim the 5 survivor files to keep only the live export + remove their now-dead ./types.js imports:

observations/store.ts  → keep computeObservationContentHash; delete storeObservation; drop ./types.js import
observations/files.ts  → keep parseFileList; delete getFilesForSession; drop ./types.js import
observations/get.ts    → keep getObservationsByFilePath; delete getObservationById/getObservationsByIds/getObservationsForSession; drop ./types.js import
observations/recent.ts → keep getFirstObservationCreatedAt; delete getRecentObservations/getAllRecentObservations; drop ./types.js import
prompts/get.ts         → keep findRecentDuplicateUserPrompt; delete the other 7 prompt getters; drop ./types.js import (it uses LatestPromptResult from ../../../types/database.js)

Then observations/types.ts and prompts/types.ts should have no remaining consumer → grep and delete if zero. Verify: bunx tsc --noEmit clean; bun test tests/sqlite/ green. Confirm the 5 live anchors still resolve (SessionStore.ts:15/16/18, ChromaSync.ts:7, DataRoutes.ts:17/18).

---

Phase 5 — Delete the dead SessionStore methods

Gate: grep -rn "\.getSessionSummaryById(\|\.storeObservationsAndMarkComplete(" src/ tests/ --include=*.ts → ZERO (the standalone sessions/get.ts:getSessionSummaryById is already deleted in Phase 4; the live one is gone with it — confirm nothing else references either name). Delete (higher range first to keep line numbers):

1. SessionStore.ts lines 2435-2476 (getSessionSummaryById)
2. SessionStore.ts lines 2017-2143 (storeObservationsAndMarkComplete) Verify: bunx tsc --noEmit clean; bun test tests/ green.

---

Phase 6 — Final verification gate

1. npm run build-and-sync — succeeds, worker restarts.
2. Worker boots + migrates a fresh DB (temp ~/.claude-mem path) — no errors in worker log.
3. Worker migrates a seeded legacy v22 DB without error (reuse the Phase 1 migrations-test seed helper).
4. bun test — full suite green.
5. bunx tsc --noEmit — no dangling imports of ClaudeMemDatabase, MigrationRunner, getDatabase, initializeDatabase, Database.ts, SchemaRepair, any deleted barrel/submodule, or the two SessionStore methods.
6. Dead-reference sweep: grep -rn "ClaudeMemDatabase\|MigrationRunner\|sqlite/Database\|sqlite/index\|sqlite/transactions\|sqlite/migrations" src/ tests/ --include=*.ts → ZERO.

Done when: all 6 pass; the only migration engine + CRUD API in the tree is SessionStore; the new session-store-* suite covers the shipping behaviors; the 5 leaf helpers remain for their prod callers.

---

Line accounting (approx)

Delete (src)	Lines
migrations/runner.ts	1147
Database.ts	211
index.ts	22
SchemaRepair.ts (orphaned)	~?
free-fn CRUD API (18 files)	~1,500
survivor trims	~150
SessionStore dead methods	169
src subtotal	~3,200

Tests	Lines
old suites deleted (9 files)	~2,340
new session-store-* suites added	~+1,000
test net	~−1,340

Net: ~−4,500 lines, zero production behavior change, real path now directly tested.

Execution order rationale

New tests first (Phase 1) → they pass on the current tree and guard every subsequent deletion. Old tests next (Phase 2) → nothing then references the duplicate. Then peel the duplicate in dependency order: DB stack (3) → free-fn API + survivor trim (4) → dead methods (5) → gate (6). The suite stays green at every step.