raw_entries UNIQUE constraint on entry_json doubles storage (2.8 GB autoindex)

[evansenter]

Problem

raw_entries declares its dedup constraint over the full JSON payload:

UNIQUE(session_id, timestamp, entry_json)

SQLite implements a UNIQUE constraint as an implicit B-tree index (sqlite_autoindex_raw_entries_1), and index keys store the complete column values. Because entry_json is in the key, every raw JSONL entry is written to disk twice — once in the table, once in the index.

Measured on a live database (367,240 rows) via dbstat:

sqlite_autoindex_raw_entries_1   2830.1 MB   <-- larger than the table it indexes
raw_entries                      2231.3 MB
events                            485.1 MB
events_fts_data                    73.9 MB

The autoindex is the single largest object in a 6.1 GB database, and it exceeds the table itself (the index carries the same blobs plus the session_id/timestamp prefix, and packs less densely).

Secondary cost: add_raw_entries_batch relies on INSERT OR IGNORE (storage.py:1153) for dedup, so every insert does a B-tree descent comparing multi-kilobyte JSON blobs. Dedup cost scales with payload size rather than with a fixed-width key.

Proposed fix

Store a hash of the payload and constrain on that instead:

CREATE TABLE raw_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    project_path TEXT,
    timestamp TEXT NOT NULL,
    entry_json TEXT NOT NULL,
    entry_hash TEXT NOT NULL,          -- sha256(entry_json), hex
    ingested_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, timestamp, entry_hash)
);

• Dedup semantics are preserved — sha256 collision risk is negligible at this scale.
• ~2.8 GB of index collapses to roughly 30 MB (64 hex chars + prefix per row).
• Inserts get faster: dedup compares fixed-width hashes, not multi-KB blobs.
• Storing hex costs 64 bytes/row; BLOB via unhex() would halve that if worth the ergonomic hit.

Implementation notes

The schema is defined in two places and both must change — this is called out by the comment at storage.py:186:

1. migrate_v13 — storage.py:604
2. The fresh-database schema path — storage.py:941

A new migrate_v15 is required, and it cannot be a simple ALTER TABLE. SQLite cannot drop a constraint, so the migration must rebuild the table:

1. CREATE TABLE raw_entries_new (...) with the new constraint
2. INSERT INTO raw_entries_new SELECT id, session_id, project_path, timestamp, entry_json, <hash>, ingested_at FROM raw_entries
3. DROP TABLE raw_entries / ALTER TABLE raw_entries_new RENAME TO raw_entries
4. Recreate idx_raw_entries_session and idx_raw_entries_timestamp
5. Bump SCHEMA_VERSION to 15

Two caveats for the migration:

• SQLite has no built-in sha256, so the backfill needs either a Python-side pass in batches or conn.create_function("sha256", 1, ...). A batched Python pass is likely simpler to reason about for a 2.2 GB table.
• The rebuild transiently needs roughly 2× the table size in free space, and DROP TABLE leaves free pages behind — the migration should be followed by a VACUUM to actually return the space to the filesystem.

add_raw_entries_batch (storage.py:1141) needs its tuple signature extended to carry the hash, along with its three call sites: ingest.py:623, server.py:164, and the tests in tests/test_storage.py / tests/test_ingest.py.

Impact

On the database measured above this reclaims ~2.8 GB of the 6.1 GB total, with no loss of data or dedup guarantees.

Found while freeing disk space; PRAGMA freelist_count was 0, confirming the size is live double-stored data rather than fragmentation (a VACUUM recovered only ~100 MB).