whiskers.git / crates / whiskers-store / migrations / 0001_init.sql
0001_init.sqlannotated0001_init.sqlsource159 lines · 5.8 KB · raw

Whiskers' storage, version 1. SCHEMA.md says what each table is and how it merges.

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;

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;

The words a memory had before they changed: append-only, kept for debugging. Nothing on the way to a 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;

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;

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);

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;

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;

Writing a tombstone deletes the memory, and with it (ON DELETE CASCADE) every mention, revision, 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;

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;

The one append-only log of everything said, as a device wrote it. On a device this holds its own lines and 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;

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);

A picture goes with the last thing that used it: when a memory's last link to it goes (the memory was 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;

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;

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;

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;

Time spent with Whiskers, by device and day, in milliseconds. A device writes only its own rows, so 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;

Small facts about this database itself.

144CREATE TABLE meta (
145    key TEXT PRIMARY KEY,
146    value TEXT NOT NULL
147) STRICT, WITHOUT ROWID;

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;

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;