Summary

• Closes Indexing creates duplicate chunks within a single run (no UNIQUE on content_hash) #691. Multi-process indexing (mm web watcher + mm CLI / MCP server) on the same SQLite DB used to insert chunk rows that shared (namespace, source_file, content_hash, start_line) but differed only in id. Each process held its own asyncio.Lock; without a storage-layer guard both INSERTs succeeded with fresh UUIDs, and the differ then reused only one of the IDs per re-index — leaving the rest as silent ghosts that survived every subsequent run.
• Fix lands at the storage layer (UNIQUE(namespace, source_file, content_hash, start_line) + INSERT OR IGNORE), with an idempotent startup migration that collapses each pre-existing duplicate group to its most-actively-used row before installing the index.
• start_line is part of the key so legitimate same-content-different-position chunks (e.g. an identical TODO block reused at two locations in one file) are still preserved; race-induced dups always share start_line and are caught.

What was happening

User-reported on a real DB:

total_chunks            1546
distinct_content_hash   1469
duplicate_groups          77
duplicate_rows           154

Every duplicate group was same-namespace + same-source. 64 of 77 groups had identical created_at to second precision, ruling out user-driven re-indexing. Search results visibly repeated content under different chunk ids; chunk counts in mem_stats were inflated.

Reproduction with two multiprocessing.Process workers indexing the same RFC file against the same DB on main:

total=26 distinct_hash=13 duplicate_groups=13   # bug

Same script after this PR:

total=13 distinct_hash=13 duplicate_groups=0    # fixed

The reproduced hashes match the prod DB bit-for-bit, confirming the multi-process race is the actual cause (not a chunker bug — MarkdownChunker.chunk_file on the same file with the same config emits 0 hash duplicates, and so does the full pre-diff pipeline including _merge_short_chunks and _add_overlap).

What changed

packages/memtomem/src/memtomem/storage/sqlite_schema.py

• New _migrate_chunks_uniqueness helper, gated on whether idx_chunks_unique_content already exists. On a clean DB (or already-migrated DB) it's a single index lookup — zero-cost.
• When the index is missing it groups rows by (namespace, source_file, content_hash, start_line), picks the keeper as the row with the highest (access_count + use_count) so we preserve whichever row the differ has been actively reusing across re-indexes (tie-break: oldest created_at, then id), and deletes the loser rowids from chunks_fts and chunks_vec (when present) before deleting from chunks. Sidecars first so an interrupted run never leaves an FTS/vec entry pointing at a non-existent chunks.rowid.
• Then CREATE UNIQUE INDEX IF NOT EXISTS idx_chunks_unique_content ON chunks(namespace, source_file, content_hash, start_line).

packages/memtomem/src/memtomem/storage/sqlite_backend.py

• upsert_chunks: chunk INSERT becomes INSERT OR IGNORE. Race losers are dropped silently at insert time.
• The follow-up SELECT id, rowid FROM chunks WHERE id IN (...) already filters by actual row existence, so chunks_fts and chunks_vec inserts naturally skip dropped rows. Wrapped the sidecar block in if new_rowids: for the case where every to_insert row was a race loser (otherwise placeholders(0) raised).

packages/memtomem/src/memtomem/tools/export_import.py

• on_conflict="duplicate" mode kept for back-compat but documented and commented to reflect that the storage invariant is now authoritative — the mode no longer materialises duplicate rows on disk.

Tests

• test_storage.py::TestChunkUniqueness — sequential and within-batch twin chunks (same content_hash, different id) collapse to one row.
• test_sqlite_schema.py::TestDuplicateChunksMigration — pre-Indexing creates duplicate chunks within a single run (no UNIQUE on content_hash) #691 dup rows are cleaned up on first startup with the most-active row preserved; second startup is a no-op.
• test_export_import_roundtrip.py::TestOnConflictModes::test_duplicate_mode_dedups_at_storage_layer — replaces the old test_duplicate_mode_backcompat; pins the new dedup behaviour.
• Seed helpers in test_chunk_links_{reader,writer,schema}.py were inserting multiple rows with content_hash='' and source_file='' under the same namespace — which now correctly violates the UNIQUE index. Helpers updated to key content_hash off chunk_id so seed-only tests don't double as smoke tests for the constraint.

Test plan

• uv run pytest -m "not ollama" — full suite, 3891 passed.
• uv run ruff check packages/memtomem/src packages/memtomem/tests
• uv run ruff format --check packages/memtomem/src packages/memtomem/tests
• Two-process race repro on a clean DB shows duplicate_groups=0 after fix (was 13).
• Migration test pins keeper selection: row with higher access_count + use_count is preserved over the older but unused row.
• Manual smoke: create_tables runs twice on the same DB without error (idempotency).

🤖 Generated with Claude Code