fix(dedupe): candidates were the type's top-100 by importance, not the same-title rows - #506

rg -n 'idx_memories|CREATE INDEX|activeByTypeAndTitle|saveWithDedupe|TRIM\(title\)' dsh-mneme/src/store.js dsh-mneme/src/service.js

set -eu
printf '%s\n' '--- relevant store schema and query ---'
nl -ba dsh-mneme/src/store.js | sed -n '1,60p;1638,1680p;3360,3395p'
printf '%s\n' '--- saveWithDedupe complete relevant blocks ---'
nl -ba dsh-mneme/src/service.js | sed -n '1330,1435p'
printf '%s\n' '--- package/runtime/database declarations ---'
rg -n -F --glob 'dsh-mneme/package.json' -e 'better-sqlite3' -e 'sqlite' -e 'engines' dsh-mneme/package.json
rg -n -F --glob 'dsh-mneme/src/**' -e 'PRAGMA' -e 'memories (' -e 'CREATE TABLE IF NOT EXISTS memories' -e 'INSERT INTO memories' dsh-mneme/src
printf '%s\n' '--- sizing and benchmark references ---'
rg -n -i --glob 'dsh-mneme/**' -e '100,000|100000|10,000|10000|million|large dataset|scale|benchmark|memories.*count|count.*memories|limit: 100' dsh-mneme/docs dsh-mneme/README.md dsh-mneme/src dsh-mneme/test 2>/dev/null || test "$?" -eq 1
printf '%s\n' '--- standalone SQLite query plans ---'
python3 - <<'PY'
import sqlite3
con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE memories (
  id INTEGER PRIMARY KEY,
  type TEXT NOT NULL,
  title TEXT,
  forgotten INTEGER NOT NULL DEFAULT 0,
  archived INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_memories_type ON memories(type);
''')
for sql, label in [
    ("EXPLAIN QUERY PLAN SELECT id FROM memories WHERE type = 'x' AND TRIM(title) = 'same' AND forgotten = 0 AND archived = 0", "current indexes"),
]:
    print(label)
    print(con.execute(sql).fetchall())
con.execute("CREATE INDEX idx_memories_active_type_trim_title ON memories(type, TRIM(title)) WHERE forgotten = 0 AND archived = 0")
sql = "EXPLAIN QUERY PLAN SELECT id FROM memories WHERE type = 'x' AND TRIM(title) = 'same' AND forgotten = 0 AND archived = 0"
print("matching partial expression index")
print(con.execute(sql).fetchall())
con.execute("DROP INDEX idx_memories_active_type_trim_title")
con.execute("CREATE INDEX idx_memories_type_trim_title ON memories(type, TRIM(title))")
print("matching non-partial expression index")
print(con.execute(sql).fetchall())
PY

printf '%s\n' '--- package and runtime files ---'
find dsh-mneme -maxdepth 2 -type f \( -name 'package.json' -o -name 'package-lock.json' -o -name 'pnpm-lock.yaml' \) -print
for f in dsh-mneme/package.json dsh-mneme/package-lock.json dsh-mneme/pnpm-lock.yaml; do
  if test -f "$f"; then
    printf '%s\n' "--- $f ---"
    rg -n -i -e 'sqlite|engines|node' "$f" || true
  fi
done
printf '%s\n' '--- source sizing and query-plan evidence ---'
rg -n -i --glob 'dsh-mneme/**' -e '1047' -e '5k' -e '5000' -e '5,000' -e 'benchmark' -e 'scale' -e 'limit: 100' -e 'CREATE INDEX' dsh-mneme/src dsh-mneme/test dsh-mneme/docs dsh-mneme/README.md 2>/dev/null || true
printf '%s\n' '--- SQLite query plans ---'
python3 - <<'PY'
import sqlite3
print("sqlite_version", sqlite3.sqlite_version)
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE memories (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT NOT NULL DEFAULT '[]',
  importance INTEGER NOT NULL DEFAULT 3,
  forgotten INTEGER NOT NULL DEFAULT 0,
  archived INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX idx_memories_type ON memories(type);
""")
q = """EXPLAIN QUERY PLAN
SELECT id, type, title, content, tags, importance, forgotten, archived, created_at, updated_at
FROM memories
WHERE type = ? AND TRIM(title) = ? AND forgotten = 0 AND archived = 0"""
print("current", con.execute(q, ("x", "same")).fetchall())
con.execute("""CREATE INDEX idx_memories_active_type_trim_title
ON memories(type, TRIM(title))
WHERE forgotten = 0 AND archived = 0""")
print("partial_expression", con.execute(q, ("x", "same")).fetchall())
con.execute("DROP INDEX idx_memories_active_type_trim_title")
con.execute("CREATE INDEX idx_memories_type_trim_title ON memories(type, TRIM(title))")
print("nonpartial_expression", con.execute(q, ("x", "same")).fetchall())
PY