# KAI Data-Store Inventory & Health Audit — 2026-06-30

**Scope:** every database / persistent store under `C:\KAI` (the "databases / sandboxes / shoeboxes").
**Mode:** READ-ONLY. No DB was modified, compacted, or VACUUMed. SQLite checks were run on
hot *copies* in tmp (the live `transcripts.db` threw I/O errors over the mount due to its WAL;
copying the `.db`+`-wal`+`-shm` and checking the copy left the original untouched).

---

## TL;DR — what needs fixing, in priority order

1. **`transcripts.db` → `user_profile_memories` table — 81.8% duplicate content (128,668 rows, only 23,355 distinct).** Plain `INSERT` with a random-suffixed id (`userId_timestamp_rand`) so dedup is structurally impossible, AND the table is *exempt from retention* (never pruned). This is the same pathology you already flagged. **Fix: idempotency + retention.** ← top priority.
2. **`harvest.jsonl` — 288 MB / 1.39 M lines, append-only, no idempotency key, soft 1 GB cap that *halts* instead of pruning.** Re-runs append duplicates freely. **Fix: dedup key + real retention/rotation.**
3. **`harvest_queue/ingested/` — 794 processed `.jsonl` files that never get purged.** Pure accumulation. **Fix: archive/delete-after-N-days policy.**
4. **`codebase-hashes.json` — 4.2 MB, grows every boot, never drops hashes for deleted files.** **Fix: prune stale entries.**
5. **A few small state files write in-place (non-atomic): `ecosystem-manager.json`, `self_optimize_state.json`.** Corruption risk on a crash mid-write. **Fix: temp+rename.**

Everything else is either SAFE by design or low-risk. Details below.

---

## A. SQLite databases

### A1. `tools/oracle-discord/transcripts.db` — 112 MB (+6.7 MB WAL) — **PROBLEM**
- **Type:** SQLite (WAL, `synchronous=NORMAL`, `auto_vacuum=INCREMENTAL`).
- **Integrity:** `quick_check` = ok, `integrity_check` = ok. **Not corrupt.**
- **Owner code:** `tools/oracle-discord/shared/transcript-memory.mjs` (connect ~L17–23). Written on **every Discord message** via a ~150 ms batched transaction; read by Leo/Oracle for recall.
- **Tables / row counts:**
  - `user_profile_memories` — **128,668 rows** — per-person message memory. **81.8% duplicate content** (23,355 distinct). Plain `INSERT`, id = `userId_timestamp_random` (L266), so identical content re-inserts under a fresh PK. Multiple ingest paths per message (record human → ingest human → ingest bot reply) each insert independently; the 4 s `DEDUP_WINDOW_MS` only catches same-batch repeats. **Exempt from retention — unbounded.** Top dupes are the bots' own repeated output ("Oracle/Consolidated Task complete…" ×688, "you're a guest here…" ×168, "Skip" ×162).
  - `transcript_fts` (fts5) — **7,780 rows** — searchable transcript. Plain `INSERT` but **retention-pruned** (AI chatter >14 days deleted; humans + digests kept). Was 127,703 rows in the June-22 backup → proof retention works here.
  - `entity_facts` — 0 rows now — **SAFE by design**: app-level UPSERT on `UNIQUE(entity_id,category,key_norm)`, bumps `mentions` instead of duplicating.
  - `user_facts` — 5 rows — **SAFE**: `PRIMARY KEY(user_id,key)` = one value per drawer, dedup by design.
  - `user_fingerprints` — 619 rows — normalized claims for contradiction-matching; no dedup constraint but low volume.
  - `message_meta` — 62 rows — cognition sidecar; **`INSERT OR REPLACE`** keyed by fts rowid = idempotent.
  - `user_profiles` (10), `channel_profiles` (1), `epistemic_cells` (0) — small/legacy.
- **Cadence:** continuous (every message). Retention sweep every 6 h, +30 s after boot.
- **Transactions:** atomic better-sqlite3 transaction; on flush error rows are logged-and-dropped (no retry).
- **VERDICT: DEDUP + GROWTH problem, isolated to `user_profile_memories`.** Rest of the DB is healthy. Fix = add a uniqueness guard (`INSERT OR IGNORE` on a `UNIQUE(userId,channelId,timestamp,content)` index) or collapse the multi-path ingest to one call, AND bring this table under the retention policy. *(Snapshot to `raw-archive/` before any dedup pass.)*

### A2. `tools/oracle-discord/transcripts.backup-2026-06-22T17-22-32-582Z.db` — 184 MB
- Frozen backup. `quick_check` = ok. `user_profile_memories` 127,703 / `transcript_fts` 127,703, no `entity_facts` (predates that table). **SAFE** (static). Candidate to move to `raw-archive/` or cold storage — it's a stale on-box copy.

### A3. `data/transcripts.db` — **0 bytes** — dead stub
- Empty. Real DB lives in `tools/oracle-discord/`. **SAFE** (harmless empty file); can be ignored/removed.

### A4. `OpenJarvis-main/~/.openjarvis/telemetry.db` (12 K), `traces.db` (4 K + WAL) — third-party
- Belongs to the vendored **OpenJarvis** subproject, not the KAI fleet (`telemetry`, `sqlite_sequence` tables). `telemetry.db` ok; `traces.db` threw I/O over the mount (live WAL). **Not a KAI store — out of scope**, flagged for completeness.

---

## B. Brain / lattice persistence (the engine's ~13 GB working set on disk)

Written by the **Rust engine** `src/persistence.rs` (+ `src/persistence/compact.rs`), saved by a
background thread **every 60 s if dirty**, on the `save`/`quit` command, and after peer sessions.
**All binary saves are ATOMIC** (write `.tmp` → `fs::rename`, verified at `persistence.rs:136/139,
328/331/333, 361`). `store_or_reinforce` reinforces existing cells instead of duplicating.

| File | Size | Holds | Atomic? | Growth | Verdict |
|---|---|---|---|---|---|
| `data/kai-cells.bin.zst` | 675 MB | all lattice cells (zstd binary) | ✅ tmp+rename, delta saves <30% change | unbounded w/ learning | **SAFE**, watch size |
| `data/kai-synapses.bin.zst` | 75 MB | interned synapse tuples (p,q,w,t,c) | ✅ | unbounded, no explicit compaction | **SAFE**, watch |
| `data/kai-meta.json.zst` | 258 B | tick / dream_count / drive / candidates (zstd, v4) | ✅ | tiny | **SAFE** |
| `data/kai-mind.json` | 112 K | working mem / episodic / global workspace / self-state | ✅ + rotates `kai-mind.backup.json` | bounded | **SAFE** (best-practice example) |
| `data/kai-texts.bin` | 229 MB | mmap text corpus (offloaded cell text) | ✅ | grows w/ cells | **SAFE** |
| `data/kai-cells-delta.bin.zst.merged.*` | 49 K | merged delta backups | n/a | accumulates slowly | low-risk; tidy occasionally |
| `data/mapper.bin` (132 M), `mapper-real.bin` (161 M) | — | legacy embedding/HNSW mapper | — | static | **no active reader found → likely dead/legacy**; verify before archiving |
| `data/dictionary.json` (24 M), `language_warehouse.json` (191 M) | — | vocab / language cache | — | static | **no active reader found → likely legacy**; candidates for cold storage |

**VERDICT:** the live brain persistence is **SAFE and well-engineered** (atomic, delta, backup rotation, reinforce-not-duplicate). The risk here is *clutter*, not corruption: `mapper*.bin`, `dictionary.json`, `language_warehouse.json` (~508 MB combined) appear to be **legacy with no active code reader** — confirm, then move to `raw-archive/` / cold storage to reclaim space.

---

## C. Append-only JSONL logs (`data/`)

| File | Size / lines | Writer | Pattern | Dedup / retention | Verdict |
|---|---|---|---|---|---|
| `harvest.jsonl` | 288 MB / 1.39 M | `overnight-harvester-bulk.mjs`, `swarm-harvester.mjs` (`appendFileSync`) | append-only, **not atomic** | **none**; soft 1 GB cap *halts* | **GROWTH + DUPE risk** → add idempotency key + rotation |
| `harvest_queue/ingested/*.jsonl` | 794 files | ingestion worker | one file per processed batch | **never purged** | **ACCUMULATION** → retention/archive policy |
| `bulk.jsonl` | 7.2 MB / 7,406 | harvester | append-only | none | likely frozen; low-risk, archive |
| `epistemic-rejections.jsonl` | 1.1 MB / 2,309 | lattice rejection path | append-only | none, but low volume | low-risk; cap eventually |
| `kai-transcript.jsonl` | 109 K / 579 | transcript export | append | none | low-risk |
| `mega_chunk_00..18.jsonl` | ~7 MB ×~19 | one-time training-data gen | static | n/a | **frozen archive** → cold storage |
| `autonomous_tmp_arxiv-*.jsonl` | small | arxiv ingest staging | transient | auto-clean | transient, fine |

**VERDICT:** `harvest.jsonl` and `harvest_queue/ingested/` are the real growth/dup offenders here. The `mega_chunk_*` and `bulk.jsonl` are frozen corpora — move to `raw-archive/` / cold storage.

---

## D. JSON "shoebox" state files (`tools/oracle-discord/state/` and `C:\KAI\state/`)

The bots' working memory — dozens of small files, mostly **full-file rewrite** (read→mutate→write).
Most are tiny status/flag/lock files (`*.flag`, `sim_state_*`, `groq_stt_*`, `voice_*`, `*_claim.json`,
heartbeats) — **SAFE, fixed-size, on-demand**, grouped here rather than listed individually.

The ones that matter:

| File | Size | Pattern | Bounded? | Verdict |
|---|---|---|---|---|
| `codebase-hashes.json` | 4.2 MB | atomic rewrite every boot (`kai-scanner.mjs`) | **no** — keeps hashes for deleted files | **GROWTH** → prune stale entries |
| `ripple_notes.json` | 700 K | rewrite on event | ✅ capped 100 | SAFE |
| `sandbox_drafts.json` | 242 K | rewrite | ✅ 2/user/session | SAFE |
| `self_optimize_state.json` | 27 K | rewrite every 4 s, **non-atomic** | ✅ last 50 actions | **fix: atomic write** |
| `ecosystem-manager.json` | 3.4 K | rewrite on process change, **non-atomic** | ✅ | **fix: atomic write** |
| `oracle_queue.json` / `command_queue.json` | 20 K / 14 K | atomic temp+rename | rebuilt each write | SAFE |
| `oracle_briefings.json` | 14 K | rewrite on briefing | small | SAFE |
| `info_sandbox.json` | 7.8 K | rewrite | small | SAFE |
| `project_snapshot.txt` | 9 MB | one-time scan dump | static | stale; archive |
| `data/claim-store.json` (516 K), `data/codex_index.json` (700 K), `state/drives.json`, `state/world-model.json`, `state/metacognition.json` | small | rewrite, on-demand/periodic | bounded | SAFE |

**VERDICT:** state shoeboxes are mostly **SAFE**. Two get a "make the write atomic" note (`self_optimize_state.json`, `ecosystem-manager.json`); `codebase-hashes.json` needs stale-entry pruning. `project_snapshot.txt` (9 MB) is a stale one-shot dump — archive.

---

## Prioritized fix list (for your go-ahead — nothing changed yet)

| # | Store | Problem | Proposed fix | Risk if ignored |
|---|---|---|---|---|
| 1 | `transcripts.db` `user_profile_memories` | 81.8% dupes, no idempotency, no retention | snapshot→`raw-archive/`; add `UNIQUE(userId,channelId,timestamp,content)` + `INSERT OR IGNORE` (or single ingest path); add table to retention sweep; one-time dedup+VACUUM | DB keeps bloating; recall quality degrades |
| 2 | `harvest.jsonl` | unbounded append, no dedup, halts at 1 GB | idempotency key (source+hash); rotate/prune instead of halt | ingestion stalls at cap; dup learning |
| 3 | `harvest_queue/ingested/` (794 files) | never purged | delete/archive after N days once ingested | slow dir, wasted space |
| 4 | `codebase-hashes.json` (4.2 MB) | grows every boot, keeps dead-file hashes | prune entries for files that no longer exist | slow boot scan |
| 5 | `self_optimize_state.json`, `ecosystem-manager.json` | non-atomic in-place writes | switch to temp+rename | corrupt state on crash mid-write |
| 6 | `mapper*.bin`, `dictionary.json`, `language_warehouse.json`, `mega_chunk_*`, backup `.db`, `project_snapshot.txt` | ~1 GB+ of legacy/frozen on-box data | confirm no live reader → move to `raw-archive/` → cold storage | wasted disk |

**Healthy / no action:** brain persistence (`kai-cells/synapses/meta/mind/texts` — atomic, deduped, backed up), `entity_facts`, `user_facts`, `message_meta`, `transcript_fts` retention, and the small flag/lock state files.

*All findings are read-only. Awaiting go-ahead before any dedup, compaction, retention, or schema change.*