| 1 | -- Apply only to application_id 1129794866, user_version 1, in an IMMEDIATE tx. |
| 2 | -- Source text remains immutable. All new timeline records are metadata-only. |
| 3 | CREATE TABLE memory_observer ( |
| 4 | singleton INTEGER PRIMARY KEY CHECK(singleton=1), |
| 5 | producer TEXT NOT NULL, |
| 6 | epoch TEXT NOT NULL |
| 7 | ); |
| 8 | INSERT INTO memory_observer VALUES(1,lower(hex(randomblob(16))),lower(hex(randomblob(16)))); |
| 9 | CREATE TABLE memory_preferences ( |
| 10 | memory_id TEXT PRIMARY KEY REFERENCES memories(id) ON DELETE CASCADE, |
| 11 | revision INTEGER NOT NULL DEFAULT 1 CHECK(revision>0), |
| 12 | pinned INTEGER NOT NULL DEFAULT 0 CHECK(pinned IN(0,1)), |
| 13 | suppressed INTEGER NOT NULL DEFAULT 0 CHECK(suppressed IN(0,1)) |
| 14 | ); |
| 15 | CREATE TABLE memory_history ( |
| 16 | seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 17 | memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 18 | revision INTEGER NOT NULL, |
| 19 | status TEXT NOT NULL, |
| 20 | recorded_at INTEGER NOT NULL, |
| 21 | UNIQUE(memory_id,revision) |
| 22 | ); |
| 23 | -- Old installations have only their current version, not fabricated history. |
| 24 | INSERT INTO memory_history(memory_id,revision,status,recorded_at) |
| 25 | SELECT id,revision,status,updated_at FROM memories; |
| 26 | CREATE TABLE memory_outbox ( |
| 27 | seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 28 | scope TEXT NOT NULL, |
| 29 | entity_id TEXT NOT NULL, |
| 30 | revision INTEGER NOT NULL, |
| 31 | action TEXT NOT NULL, |
| 32 | recorded_at INTEGER NOT NULL, |
| 33 | trace_id TEXT, |
| 34 | context_id TEXT, |
| 35 | units INTEGER, |
| 36 | unit TEXT, |
| 37 | reason_code TEXT |
| 38 | ); |
| 39 | CREATE INDEX memory_outbox_scope ON memory_outbox(scope,seq); |
| 40 | CREATE TRIGGER memory_history_insert AFTER INSERT ON memories BEGIN |
| 41 | INSERT INTO memory_history(memory_id,revision,status,recorded_at) |
| 42 | VALUES(new.id,new.revision,new.status,new.updated_at); |
| 43 | END; |
| 44 | CREATE TRIGGER memory_history_update AFTER UPDATE OF revision ON memories |
| 45 | WHEN new.revision<>old.revision BEGIN |
| 46 | INSERT INTO memory_history(memory_id,revision,status,recorded_at) |
| 47 | VALUES(new.id,new.revision,new.status,new.updated_at); |
| 48 | END; |
| 49 | -- Existing audited operations and their outbox entries commit together. |
| 50 | CREATE TRIGGER memory_event_outbox AFTER INSERT ON events |
| 51 | WHEN EXISTS(SELECT 1 FROM memories WHERE id=new.entity_id) BEGIN |
| 52 | INSERT INTO memory_outbox(scope,entity_id,revision,action,recorded_at) |
| 53 | SELECT scope,new.entity_id,new.revision,new.action,new.at FROM memories WHERE id=new.entity_id; |
| 54 | END; |
| 55 | CREATE TABLE memory_contexts ( |
| 56 | id TEXT PRIMARY KEY, |
| 57 | scope TEXT NOT NULL, |
| 58 | trace_id TEXT NOT NULL, |
| 59 | request_key TEXT NOT NULL, |
| 60 | input_hash TEXT NOT NULL, |
| 61 | packet_hash TEXT NOT NULL, |
| 62 | stage TEXT NOT NULL CHECK(stage IN('prepared','appended','dispatched')), |
| 63 | created_at INTEGER NOT NULL, |
| 64 | appended_at INTEGER, |
| 65 | dispatched_at INTEGER, |
| 66 | history_sequence INTEGER, |
| 67 | transport_key TEXT, |
| 68 | used_units INTEGER NOT NULL CHECK(used_units>=0), |
| 69 | unit TEXT NOT NULL, |
| 70 | omitted INTEGER NOT NULL CHECK(omitted>=0), |
| 71 | UNIQUE(scope,trace_id,request_key) |
| 72 | ); |
| 73 | CREATE TABLE memory_context_refs ( |
| 74 | context_id TEXT NOT NULL REFERENCES memory_contexts(id) ON DELETE CASCADE, |
| 75 | memory_id TEXT NOT NULL REFERENCES memories(id) ON DELETE CASCADE, |
| 76 | revision INTEGER NOT NULL, |
| 77 | content_hash TEXT NOT NULL, |
| 78 | position INTEGER NOT NULL, |
| 79 | PRIMARY KEY(context_id,memory_id) |
| 80 | ); |
| 81 | CREATE INDEX memory_context_refs_memory ON memory_context_refs(memory_id); |
| 82 | -- Forget removes the whole dependent context, not just one index row. |
| 83 | CREATE TRIGGER forget_context BEFORE DELETE ON memories BEGIN |
| 84 | DELETE FROM memory_contexts WHERE id IN |
| 85 | (SELECT context_id FROM memory_context_refs WHERE memory_id=old.id); |
| 86 | END; |
| 87 | CREATE TABLE memory_hook_receipts ( |
| 88 | scope TEXT NOT NULL, |
| 89 | event_id TEXT NOT NULL, |
| 90 | input_hash TEXT NOT NULL, |
| 91 | recorded_at INTEGER NOT NULL, |
| 92 | PRIMARY KEY(scope,event_id) |
| 93 | ); |
| 94 | PRAGMA user_version=2; |
| 95 | -- Compatibility IDs are stable and never reused after forgetting. The advanced |
| 96 | -- API uses UUIDs; existing numeric memory_get callers go through this alias. |
| 97 | CREATE TABLE memory_numeric_aliases ( |
| 98 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 99 | memory_id TEXT UNIQUE REFERENCES memories(id) ON DELETE SET NULL |
| 100 | ); |
| 101 | INSERT INTO memory_numeric_aliases(memory_id) SELECT id FROM memories ORDER BY rowid; |
| 102 | CREATE TRIGGER memory_numeric_insert AFTER INSERT ON memories BEGIN |
| 103 | INSERT INTO memory_numeric_aliases(memory_id) VALUES(new.id); |
| 104 | END; |
| 105 |