1-- Whiskers' storage, version 1. SCHEMA.md says what each table is and how it merges.
2
3-- What Whiskers remembers: one current row per memory.
4CREATE TABLE fact (
5    id INTEGER PRIMARY KEY AUTOINCREMENT,
6    gid TEXT NOT NULL UNIQUE CHECK (gid <> ''),
7    text TEXT NOT NULL,
8    text_at_ms INTEGER NOT NULL DEFAULT 0,
9    learned_at_ms INTEGER NOT NULL,
10    kind TEXT NOT NULL CHECK (kind IN ('person', 'pet', 'toy', 'place', 'event', 'thing', 'other')),
11    who TEXT NOT NULL DEFAULT '[]',
12    place TEXT,
13    said_when TEXT,
14    hidden INTEGER NOT NULL DEFAULT 0 CHECK (hidden IN (0, 1)),
15    visibility_at_ms INTEGER NOT NULL DEFAULT 0,
16    icon TEXT,
17    cover_picture TEXT,
18    cover_at_ms INTEGER,
19    CHECK ((cover_picture IS NULL) = (cover_at_ms IS NULL))
20) STRICT;
21
22-- Every time she said it: append-only, merged by union. These are the timeline's entries.
23CREATE TABLE fact_mention (
24    gid TEXT NOT NULL REFERENCES fact (gid) ON DELETE CASCADE,
25    at_ms INTEGER NOT NULL,
26    device TEXT NOT NULL,
27    PRIMARY KEY (gid, at_ms, device)
28) STRICT, WITHOUT ROWID;
29
30-- The words a memory had before they changed: append-only, kept for debugging. Nothing on the way to a
31-- prompt reads this. `at_ms` is when the words were replaced.
32CREATE TABLE fact_revision (
33    gid TEXT NOT NULL REFERENCES fact (gid) ON DELETE CASCADE,
34    at_ms INTEGER NOT NULL,
35    text TEXT NOT NULL,
36    device TEXT NOT NULL DEFAULT '',
37    PRIMARY KEY (gid, at_ms, text)
38) STRICT, WITHOUT ROWID;
39
40-- The embedding of the current words: 32-bit floats, little-endian.
41CREATE TABLE fact_embedding (
42    gid TEXT PRIMARY KEY REFERENCES fact (gid) ON DELETE CASCADE,
43    vector BLOB NOT NULL
44) STRICT, WITHOUT ROWID;
45
46-- Which pictures go with a memory, in order. A name may be here before its bytes have arrived.
47CREATE TABLE fact_picture (
48    gid TEXT NOT NULL REFERENCES fact (gid) ON DELETE CASCADE,
49    position INTEGER NOT NULL,
50    picture_id TEXT NOT NULL,
51    PRIMARY KEY (gid, position)
52) STRICT, WITHOUT ROWID;
53CREATE INDEX fact_picture_by_picture ON fact_picture (picture_id);
54
55-- What the parents forgot: the identity, when and on which device, never content.
56CREATE TABLE tombstone (
57    gid TEXT PRIMARY KEY CHECK (gid <> ''),
58    at_ms INTEGER NOT NULL,
59    device TEXT NOT NULL
60) STRICT, WITHOUT ROWID;
61
62-- A forgotten memory cannot be written again, whatever arrives later.
63CREATE TRIGGER fact_not_forgotten BEFORE INSERT ON fact
64WHEN EXISTS (SELECT 1 FROM tombstone WHERE gid = NEW.gid)
65BEGIN
66    SELECT RAISE(ABORT, 'that memory was forgotten');
67END;
68
69-- Writing a tombstone deletes the memory, and with it (ON DELETE CASCADE) every mention, revision,
70-- embedding and picture link. There is no state that holds both.
71CREATE TRIGGER tombstone_deletes_the_fact AFTER INSERT ON tombstone
72BEGIN
73    DELETE FROM fact WHERE gid = NEW.gid;
74END;
75
76-- A picture, byte for byte, under the name every device knows it by. Never replaced.
77CREATE TABLE picture (
78    id TEXT PRIMARY KEY,
79    bytes BLOB NOT NULL
80) STRICT, WITHOUT ROWID;
81
82-- The one append-only log of everything said, as a device wrote it. On a device this holds its own lines and
83-- the copy of everyone else's it has pulled; on the service, every device's, in arrival order (`position`).
84CREATE TABLE journal_line (
85    position INTEGER PRIMARY KEY AUTOINCREMENT,
86    device TEXT NOT NULL,
87    seq INTEGER NOT NULL,
88    entry TEXT NOT NULL,
89    UNIQUE (device, seq)
90) STRICT;
91
92-- Which pictures a logged conversation shows, so a picture a log line still needs is not deleted with a memory.
93CREATE TABLE journal_picture (
94    device TEXT NOT NULL,
95    seq INTEGER NOT NULL,
96    picture_id TEXT NOT NULL,
97    PRIMARY KEY (device, seq, picture_id),
98    FOREIGN KEY (device, seq) REFERENCES journal_line (device, seq)
99) STRICT, WITHOUT ROWID;
100CREATE INDEX journal_picture_by_picture ON journal_picture (picture_id);
101
102-- A picture goes with the last thing that used it: when a memory's last link to it goes (the memory was
103-- forgotten) and no logged conversation shows it, its bytes go too.
104CREATE TRIGGER picture_unused AFTER DELETE ON fact_picture
105BEGIN
106    DELETE FROM picture
107    WHERE id = OLD.picture_id
108      AND NOT EXISTS (SELECT 1 FROM fact_picture WHERE picture_id = OLD.picture_id)
109      AND NOT EXISTS (SELECT 1 FROM journal_picture WHERE picture_id = OLD.picture_id);
110END;
111
112-- The running chat: one row for the session, and its recent turns in order.
113CREATE TABLE chat_session (
114    id INTEGER PRIMARY KEY CHECK (id = 1),
115    summary TEXT NOT NULL,
116    last_active_ms INTEGER NOT NULL,
117    version INTEGER NOT NULL
118) STRICT;
119
120CREATE TABLE chat_turn (
121    seq INTEGER PRIMARY KEY AUTOINCREMENT,
122    speaker TEXT NOT NULL CHECK (speaker IN ('child', 'whiskers')),
123    text TEXT NOT NULL
124) STRICT;
125
126-- The grown-ups' choices: one row per setting, each with the moment it was written.
127CREATE TABLE household (
128    name TEXT PRIMARY KEY,
129    value TEXT NOT NULL,
130    stamp INTEGER NOT NULL
131) STRICT, WITHOUT ROWID;
132
133-- Time spent with Whiskers, by device and day, in milliseconds. A device writes only its own rows, so
134-- they cannot conflict; the day's total is their sum. Counts only ever rise.
135CREATE TABLE time_spent (
136    device TEXT NOT NULL,
137    day INTEGER NOT NULL,
138    used_ms INTEGER NOT NULL DEFAULT 0 CHECK (used_ms >= 0),
139    extra_minutes INTEGER NOT NULL DEFAULT 0 CHECK (extra_minutes >= 0),
140    PRIMARY KEY (device, day)
141) STRICT, WITHOUT ROWID;
142
143-- Small facts about this database itself.
144CREATE TABLE meta (
145    key TEXT PRIMARY KEY,
146    value TEXT NOT NULL
147) STRICT, WITHOUT ROWID;
148
149-- What the service has spent on thinking: when and how many tokens, inside the window the ledger keeps.
150CREATE TABLE thinking_spend (
151    at_ms INTEGER NOT NULL,
152    tokens INTEGER NOT NULL CHECK (tokens >= 0)
153) STRICT;
154
155-- The natural voice's characters spent on each UTC day.
156CREATE TABLE voice_spend (
157    day INTEGER PRIMARY KEY,
158    chars INTEGER NOT NULL CHECK (chars >= 0)
159) STRICT;