| 1 | -- New, authoritative v2 store. NEVER apply this to native index.sqlite3. |
| 2 | CREATE TABLE memories ( |
| 3 | rowid INTEGER PRIMARY KEY, |
| 4 | id TEXT NOT NULL UNIQUE, |
| 5 | scope TEXT NOT NULL, |
| 6 | kind TEXT NOT NULL, |
| 7 | semantic_key TEXT, |
| 8 | status TEXT NOT NULL CHECK(status IN ('candidate','active','stale','superseded','rejected')), |
| 9 | revision INTEGER NOT NULL CHECK(revision > 0), |
| 10 | title TEXT NOT NULL, |
| 11 | body TEXT NOT NULL, |
| 12 | tags TEXT NOT NULL, |
| 13 | payload TEXT NOT NULL CHECK(json_valid(payload)), |
| 14 | content_hash TEXT NOT NULL, |
| 15 | repo_revision TEXT, |
| 16 | importance REAL NOT NULL CHECK(importance BETWEEN 0 AND 1), |
| 17 | confidence REAL NOT NULL CHECK(confidence BETWEEN 0 AND 1), |
| 18 | created_at INTEGER NOT NULL, |
| 19 | updated_at INTEGER NOT NULL, |
| 20 | expires_at INTEGER |
| 21 | ); |
| 22 | CREATE INDEX memories_scope_status ON memories(scope,status,updated_at); |
| 23 | CREATE UNIQUE INDEX one_active_key ON memories(scope,semantic_key) |
| 24 | WHERE status='active' AND semantic_key IS NOT NULL; |
| 25 | CREATE TABLE dependencies ( |
| 26 | memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 27 | path TEXT NOT NULL, |
| 28 | digest TEXT NOT NULL, |
| 29 | PRIMARY KEY(memory_id,path) |
| 30 | ); |
| 31 | CREATE VIRTUAL TABLE memory_fts USING fts5(title,body,tags, content='memories',content_rowid='rowid',tokenize='unicode61 remove_diacritics 2'); |
| 32 | CREATE VIRTUAL TABLE memory_grams USING fts5(title,body, content='memories',content_rowid='rowid',tokenize='trigram'); |
| 33 | INSERT INTO memory_fts(memory_fts,rank) VALUES('secure-delete',1); |
| 34 | INSERT INTO memory_grams(memory_grams,rank) VALUES('secure-delete',1); |
| 35 | CREATE TRIGGER memory_insert AFTER INSERT ON memories BEGIN |
| 36 | INSERT INTO memory_fts(rowid,title,body,tags) VALUES(new.rowid,new.title,new.body,new.tags); |
| 37 | INSERT INTO memory_grams(rowid,title,body) VALUES(new.rowid,new.title,new.body); |
| 38 | END; |
| 39 | CREATE TRIGGER memory_delete AFTER DELETE ON memories BEGIN |
| 40 | INSERT INTO memory_fts(memory_fts,rowid,title,body,tags) VALUES('delete',old.rowid,old.title,old.body,old.tags); |
| 41 | INSERT INTO memory_grams(memory_grams,rowid,title,body) VALUES('delete',old.rowid,old.title,old.body); |
| 42 | END; |
| 43 | -- Text is immutable. Corrections create a replacement, never edit in place. |
| 44 | CREATE TRIGGER immutable_memory_text BEFORE UPDATE OF title,body,tags,payload,scope,kind,semantic_key,content_hash,repo_revision,expires_at,importance,confidence,created_at ON memories BEGIN |
| 45 | SELECT RAISE(ABORT,'memory content is immutable; create a revision'); |
| 46 | END; |
| 47 | CREATE TABLE requests ( |
| 48 | scope TEXT NOT NULL, |
| 49 | request_hash TEXT NOT NULL, |
| 50 | draft_hash TEXT NOT NULL, |
| 51 | memory_id TEXT REFERENCES memories(id) ON DELETE SET NULL, |
| 52 | PRIMARY KEY(scope,request_hash) |
| 53 | ); |
| 54 | CREATE TABLE tombstones ( |
| 55 | scope TEXT NOT NULL, |
| 56 | content_hash TEXT NOT NULL, |
| 57 | forgotten_at INTEGER NOT NULL, |
| 58 | PRIMARY KEY(scope,content_hash) |
| 59 | ); |
| 60 | CREATE TABLE events ( |
| 61 | seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 62 | entity_id TEXT NOT NULL, |
| 63 | action TEXT NOT NULL, |
| 64 | actor_hash TEXT NOT NULL, |
| 65 | revision INTEGER NOT NULL, |
| 66 | at INTEGER NOT NULL |
| 67 | ); |
| 68 | -- Events intentionally contain NO memory text, URI, free-form reason, or payload. |
| 69 | CREATE TABLE validations ( |
| 70 | memory_id TEXT PRIMARY KEY REFERENCES memories(id) ON DELETE CASCADE, |
| 71 | receipt TEXT NOT NULL CHECK(json_valid(receipt)) |
| 72 | ); |
| 73 | CREATE TABLE lineage ( |
| 74 | parent_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 75 | child_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 76 | PRIMARY KEY(parent_id,child_id), CHECK(parent_id != child_id) |
| 77 | ); |
| 78 | CREATE INDEX lineage_child ON lineage(child_id); |
| 79 | CREATE TABLE replacements ( |
| 80 | old_id TEXT PRIMARY KEY REFERENCES memories(id) ON DELETE CASCADE, |
| 81 | new_id TEXT NOT NULL UNIQUE REFERENCES memories(id) ON DELETE CASCADE, |
| 82 | CHECK(old_id != new_id) |
| 83 | ); |
| 84 | CREATE TABLE links ( |
| 85 | from_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 86 | to_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 87 | relation TEXT NOT NULL CHECK(relation IN ('related','supports','contradicts','decision_outcome')), |
| 88 | PRIMARY KEY(from_id,to_id,relation), CHECK(from_id != to_id) |
| 89 | ); |
| 90 | CREATE INDEX links_to ON links(to_id); |
| 91 | CREATE TABLE embeddings ( |
| 92 | memory_id TEXT PRIMARY KEY REFERENCES memories(id) ON DELETE CASCADE, |
| 93 | model TEXT NOT NULL, |
| 94 | dimensions INTEGER NOT NULL CHECK(dimensions BETWEEN 1 AND 8192), |
| 95 | content_hash TEXT NOT NULL, |
| 96 | vector BLOB NOT NULL, |
| 97 | CHECK(length(vector) = dimensions * 4) |
| 98 | ); |
| 99 | CREATE INDEX embedding_model ON embeddings(model,dimensions); |
| 100 | CREATE TABLE checkpoints ( |
| 101 | id TEXT PRIMARY KEY, |
| 102 | scope TEXT NOT NULL, |
| 103 | checkpoint_key TEXT NOT NULL, |
| 104 | revision INTEGER NOT NULL CHECK(revision > 0), |
| 105 | payload TEXT NOT NULL CHECK(json_valid(payload)), |
| 106 | updated_at INTEGER NOT NULL, |
| 107 | expires_at INTEGER NOT NULL, |
| 108 | UNIQUE(scope,checkpoint_key) |
| 109 | ); |
| 110 | CREATE TABLE checkpoint_refs ( |
| 111 | checkpoint_id TEXT NOT NULL REFERENCES checkpoints(id) ON DELETE CASCADE, |
| 112 | memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 113 | revision INTEGER NOT NULL, |
| 114 | content_hash TEXT NOT NULL, |
| 115 | PRIMARY KEY(checkpoint_id,memory_id) |
| 116 | ); |
| 117 | CREATE INDEX checkpoint_refs_memory ON checkpoint_refs(memory_id); |
| 118 | PRAGMA application_id=1129794866; |
| 119 | PRAGMA user_version=1; |
| 120 |