Content dedup at the note-version level

Content storage

• note_paths / note_versions schema: db/migrations/20250402131258_create_note_tables.sql:3-19. Every version stores the full raw body in note_versions.content (text not null). There is no hash column on note_versions.
• note_paths.latest_content_hash = base64url(sha256(raw content)), computed on every push at internal/case/insertnote/resolve.go:42-43 and used only as a "did the latest version change" guard (resolve.go:80) and by the git mirror to skip unchanged files (internal/gitapi/apply.go:61,75-76).
• So: 398 duplicate notes → 398 duplicate full bodies in note_versions (times however many versions each accumulates). SQLite pays the bytes; noteloader also loads all of them into memory via AllLatestNotes (queries.read.sql:32-37, internal/noteloader/loader.go:199-223).

Embeddings

Two tables, both keyed by version, not by content:

• note_version_embeddings(version_id PK, embedding, model_id, content_hash, tokens) — db/migrations/20260102093534_create_note_version_embeddings.sql. One whole-note vector per version.
• note_version_chunks(version_id, chunk_index, content, embedding, model_id, content_hash) — db/migrations/20260318100000_create_note_version_chunks.sql. Chunk text is stored again here (a third copy of the body, sliced), plus one vector per chunk.

The content_hash columns are already the right dedup key, but today they're used only as a change detector within the same version_id:

• Whole note: NoteContentHash = sha256(title + frontmatter-stripped content + model fingerprint) — internal/case/backjob/generatenoteversionembedding/resolve.go:212-215. The generator skips re-embedding when the stored row for this version has the same hash (resolve.go:56-69).
• Chunks: sha256(chunk content + fingerprint), same-version skip at resolve.go:144-151.
• The regeneration cron does the same per-version comparison (internal/case/cronjob/regeneratenoteembeddings/resolve.go:68-75).

Nothing ever asks "has this hash been embedded under a different version_id?" Two paths with identical bodies (same title — they're file copies, so titles match and the chunk breadcrumbs match too) produce byte-identical hashes and byte-identical vectors, computed and stored twice. For the flat test vault that's 398 redundant whole-note embeddings plus all their chunks — pure wasted GPU/API calls and duplicate blobs, repeated again whenever the model fingerprint changes.

Search

Both search paths dedup by note path only, never by content:

• Site search: vector candidates deduped via seen[c.path] (internal/case/sitesearch/resolve.go:229-236), RRF merge keyed by r.URL (resolve.go:300-328).
• MCP search: same pattern — seen[s.chunk.NotePath] at internal/case/mcp/resolve.go:1217-1222, merge by URL at resolve.go:1283-1307.

Identical bodies produce identical vectors, so all 20 copies of a shared doc score identically against any query and arrive as 20 adjacent results. With vectorTopK = 50 (sitesearch/resolve.go:164) and hybridResultCap = 20 (resolve.go:47), a popular duplicated doc can occupy most of the visible result list, evicting genuinely distinct hits. Same for the MCP MaxMergedResults cap. This is the measured "search noise" cost.

Where the duplication is paid — summary

Schema

create table note_contents (
  hash text primary key,          -- base64url(sha256(raw content)), same encoding as note_paths.latest_content_hash
  content text not null,
  size integer not null,
  created_at datetime not null default current_timestamp
);

-- note_versions: replace content with a reference
alter: note_versions(id, path_id, version, content_hash text not null references note_contents(hash),
                     created_at, created_by_* ...)

Embeddings move from version-keyed to content-keyed:

create table content_embeddings (
  content_hash text not null references note_contents(hash),
  embed_hash blob not null,       -- NoteContentHash(title, content, cfg): title+model fingerprint identity
  embedding blob not null,
  model_id integer not null,
  tokens integer not null,
  primary key (content_hash, embed_hash)
);
-- content_chunks analogous, keyed (content_hash, embed_hash_prefix, chunk_index)

(Practically: keep embed_hash as the primary lookup and treat content_hash as the storage-GC anchor. Because the embedded text includes the title, notes sharing a body but not a title need separate vectors — the two-hash key models that honestly.)

Write/sync path change

internal/case/insertnote/resolve.go already computes the raw hash (line 42-43). Change: insert or ignore into note_contents(hash, content) then insert note_versions with content_hash instead of content (queries.write.sql:27-30 InsertNoteVersion). The existing dedup guard at resolve.go:80 is untouched. Every reader of note_versions.content (AllLatestNotes at queries.read.sql:32, AllLiveNotes at :366, AllNoteVersionsByPathID at :12, version-diff/editor paths) gains a join — mechanical but wide; sqlc regeneration makes the compiler find all of them.

Embedding pipeline change

generatenoteversionembedding becomes: compute embed_hash; select ... from content_embeddings where embed_hash = ?; on hit, done (no per-version copy needed at all — search reads through content). On miss, embed once and insert. The cron (regeneratenoteembeddings/resolve.go:68-77) enqueues per unique embed_hash instead of per version — 946 jobs instead of 1344. Chunk logic is the same per-chunk (generatenoteversionembedding/resolve.go:144-151 already has the hash-skip loop; only the lookup scope widens from "this version" to "any").

Migration / backward compat

1. Additive migration: create note_contents + content_embeddings; add nullable note_versions.content_hash.
2. Backfill in batches: hash each note_versions.content, upsert into note_contents, set content_hash. Copy one embedding row per distinct embed_hash from note_version_embeddings/note_version_chunks.
3. Flip readers to the join; keep note_versions.content for one release as a fallback, then drop via SQLite table-rebuild (the codebase already has this pattern, e.g. db/migrations/20250515071315_add_id_to_note_versions.sql).
4. Old note_version_embeddings tables can be dropped after cutover; nothing external references them.

Backfill rewrites the largest table in the DB — run it as a one-time batched job, not inside a migration transaction, and mind the prod box (single small VM; see prod constraints).