AkurAI-Notes / docs/adr/005-memory-schema-and-supersession.md

Latest change f558f1454ce351284227fb9f5dfccf682ca84666 - OLI-137: ADR 005 reflects implemented memory schema, RLS and dedupe threshold by Ólafur Búi Ólafsson

# ADR 005: Memory schema and supersession

Status: Accepted, implemented in akurai-index Phase 2 (OLI-137) · Epic OLI-112

## Decisions
1. **Table `memory`:** id, type (`episodic|semantic|procedural`), text, scope (`global|project|agent`), scope_ref, source (issue/commit/note/run id), confidence 0..1, importance 0..1, owner, created_at, valid_from, valid_until, supersedes_id (self FK), status (`active|review|superseded|forgotten`), embedding(model_id, version, vector). One row is one fact; no multi-fact blobs.
2. **Supersession, never overwrite:** a contradicting same-subject fact inserts a new row with `supersedes_id = old.id`, sets `old.valid_until = now()` and `old.status = superseded`. History stays queryable (`as_of` recall). Contradiction = same subject (cosine >= 0.92 + rerank agreement) with a differing assertion; ambiguous cases go to `review`.
3. **Dedupe:** cosine >= 0.92 and reranker agreement merges into the existing row (bumps importance, appends source) instead of inserting.
4. **Review queue:** confidence < 0.6 is stored `review`, excluded from recall until accepted.
5. **Forget:** tombstone row (text blanked, id and audit kept), vector deleted, audit entry; owner/board only (ADR 006).
6. **Recall score:** `rerank * (0.7 + 0.2*importance + 0.1*recency_decay)`, only `active` and `valid_until` null or future, always with citations (`source`).
7. **Lifecycle:** episodic TTL 90 d with decay; weekly consolidation summarises clusters into a semantic fact keeping source links.
8. All reads go through the authz row filter of ADR 006.

## Consequences
Append-mostly table with a supersession chain; recall must filter on status/validity (partial index on `status='active'`). 
## Phase 2 implementation notes (OLI-137)
- **DDL lives in** `akurai-core/src/memory.rs::schema_sql()` (idempotent, applied by memoryd at start). Scope is `project_id` (not `scope_ref`) to match the ADR 006 RLS policies. Extra columns: `conflicts_with`, `embedding_model`, `embedding_version`, generated `tsv`. Tables `ix_issues` (issue vectors), `audit_log` (append-only, ADR 006), `hook_events` (idempotency ledger), `hook_kv`.
- **Authz in SQL:** every request runs in a txn with `SET LOCAL ROLE akurai_app` + `akurai.scopes/projects/actor` GUCs; RLS is forced. Added policy `mem_supersede` (write scope + owner, WITH CHECK status in active/superseded) so writers can supersede their own rows without `memory:forget`; forgetting/review decisions need `memory:forget` and ownership.
- **Dedupe threshold changed 0.92 -> 0.97 (auto-dedupe).** Measured: Qwen3-Reranker scores paraphrase (0.9999) and contradiction ("VPN deferred" vs "cancelled": 0.9992) identically, and cosine of that contradiction is 0.934, so a 0.92 auto-merge silently dropped a contradicting fact. Now: cosine >= 0.97 (and rerank agrees) = duplicate, returns existing id; 0.85 <= cosine < 0.97 on semantic/procedural = stored `review` with `conflicts_with`; approve+supersede or reject through `review/decide`. Without an LLM conflict judge this is the safe default; revisit when the Phase 3 extractor exists.
- **Secrets:** `remember` rejects text that looks like a credential (private key, AWS key, bearer/ghp_/sk-ant-/glpat- tokens, `password=<value>`); rejection is audited. Full gitleaks/PII gate from ADR 006 is still SecOps' ingest gate.
- **Not yet done:** `as_of` recall, weekly consolidation, re-embedding job (`embedding_version` column is in place), TTL sweeper (episodic rows get `valid_until = now()+90d`, recall already honours it).