1# The schema
2
3Every table of the database, in prose, and how each merges. The SQL is `migrations/0001_init.sql`; this file is
4what it means. A device and the service have the same schema (the service uses the tables a device does not,
5`thinking_spend` and `voice_spend`, and a device uses `journal_line` for its own lines and the copy it pulls of the
6others; `pending_reflection` is a device's, and is empty on the service). The shape is Mozilla's sync15: a current row for what is, a mirror of what the other side last said (here
7the merge itself is `whiskers-core`'s pure function, so the mirror is the snapshot a device sends and is sent), and
8a tombstone that holds only the id.
9
10A database at version 5 is brought to 6 (`migrations/0006_forgetting_the_turn.sql`, tested with data in `tests/migration.rs`):
11nothing it holds moves. The mentions it had name no turn, so forgetting a memory learned before turns had identities
12finds no conversation to empty (its words in the log are still emptied, by identity, as before); everything learned from now on
13does.
14
15The merge rules are **not** in SQL. They are `MemoryDoc::merge`, `Household::merge` and `ChatState::merge` in
16`whiskers-core`, run on a document read from these tables; the store writes the difference back in one
17transaction. The rules SQL expresses better are the ones about what must not exist, and those are in the schema:
18a tombstone deletes its memory and empties its words from the log (a trigger), a memory with a tombstone cannot be written again (a trigger), a
19memory's rows cannot outlive it (`ON DELETE CASCADE`), a picture goes with the last thing that showed it (a
20trigger), a setting's value and a register's two halves cannot disagree (`CHECK`).
21
22## Memory
23
24**`fact`**: one current row per memory, keyed by `gid` (its identity on every device) and numbered by `id` (this
25holder's own numbering, `AUTOINCREMENT`, so an id is never handed out twice, even after the newest memory is
26forgotten). `text` is a last-writer-wins register stamped `text_at_ms` (zero while it is the words the memory was
27learned in); the later write wins and the smaller words on a tie. `hidden` with `visibility_at_ms` is the
28child's hiding, a last-writer-wins register (a tie stays hidden). `cover_picture` with `cover_at_ms` is the card's
29star, a last-writer-wins register (a tie goes to the smaller picture name; both are null while nobody starred, and
30`CHECK` keeps them together). `icon` is the pixel icon, whichever any side chose, the smaller name if two did.
31`kind`, `who`, `place` and `said_when` are what the model filed it as. `learned_at_ms` is when it was first told.
32
33**`fact_mention`**: append-only, one row each time she said the memory, the first telling included, keyed by
34`(gid, at_ms, device, turn)`. `turn` is the identity of the turn of the conversation she said it in (see **Turns** below), empty
35for a mention made before turns had identities. Merges by union (a device only ever adds its own), so the same telling known twice
36is one row. These are the timeline's entries: the memory page shows the memory under every day it has a row for.
37
38**`fact_revision`**: append-only, the words a memory had before they changed, keyed by `(gid, at_ms, text)` where
39`at_ms` is when the words were *replaced*. That key is what makes the same change one row however it reached a
40device (the device that made it, or another that learned of it when the later words arrived), so revisions merge by
41union. `device` is who replaced them, empty where a device only learned of it. The starting point had a `rev`
42counter; two devices cannot agree on one, so it is not a key. Nothing on the way to a prompt reads this table.
43
44**`fact_embedding`**: the embedding of the *current* words, 32-bit floats little-endian. Dropped when the words
45change (it is of words that are gone) and made again; an embedding computed on one side serves both, the first
46kept.
47
48**`fact_picture`**: which pictures go with a memory, in order. It names a picture; it does not require its bytes
49to be there yet, because pictures sync after the memory that names them.
50
51**`tombstone`**: what the parents forgot: `gid`, `at_ms`, `device`, never content (`at_ms` is zero where only the
52identity is known, as for a memory dropped as a duplicate; a zero is **not a forgetting**, so it empties no conversation and
53drops nothing that waits, see below). The identity decides everything and wins over any
54other state from any device; where two sides know when and by whom, the earlier account is kept, so every order
55agrees. Writing a row deletes the memory and, through `ON DELETE CASCADE`, its mentions, revisions, embedding and
56picture links; the last link to a picture going deletes the picture too unless a logged conversation still shows
57it. Hiding by the child is not this: it is `fact.hidden`, a soft delete that keeps everything.
58
59**`picture`**: the bytes of a picture under the name every device knows it by (made from the time and a counter, so
60one name is one picture). Never replaced.
61
62**`tombstone_turn`**: the turns of a forgotten memory, `(gid, turn)`, identities only. A tombstone carries them
63(`Tombstone.turns`, in the sync documents) to every holder, which writes them here; writing one is what empties the
64turn's words (see **Forgetting reaches the conversation**).
65
66**`soft_action`**: the log of soft deletes, so that one can be undone for a few seconds. Putting a memory away (the
67child hiding it) writes a row in the same transaction: `kind` (`put_away` today), the memory's `gid`, `at_ms`, and
68`undone_at_ms` once it has been moved back (null until then). `Memory::undo(id, now)` restores the memory (a later
69stamp on the same register, so it beats the hide on every device) and spends the row; whether it is allowed (the
70window is `UNDO_WINDOW_MS`, eight seconds, in `whiskers-core`'s `undo.rs`, not in SQL) is the core's rule. The row
71belongs to its memory (`ON DELETE CASCADE`): the parents forgetting a memory deletes its actions, so a forgotten
72memory has nothing to undo and a row for one cannot be written. The parents' forget is not a soft action and is
73never in this table. The rows hold an identity and times, never words.
74
75## The log
76
77**`journal_line`**: append-only; one row per entry a device wrote, `(device, seq)` being that device's own count
78from zero and `position` the order of arrival. `at_ms` (when the line was written), `turn` and `live` are generated from the
79line itself (so there is no second copy of any of them to disagree) and indexed: `journal_line_by_time` for reading a day,
80`journal_line_by_turn` for forgetting, and `journal_line_live_by_time` over the lines that still have content, for clearing by age. On the service it holds every device's lines; the pull is a page of
81it by `position`. On a device it holds the device's own lines and the lines it has pulled of the others'. Never
82changed or removed (except that a device throws away its copy of *others'* lines to take them again).
83
84**`journal_picture`**: which pictures a logged conversation shows, so that a picture the parents' log still
85needs is not deleted with a memory made from it.
86
87**Forgetting also empties the log.** The parents' log is append-only, but a line that carries a forgotten memory's
88words keeps its place, its time and its writer and loses the words (they become empty): `Remembered`, `PutAway`,
89`Restored` (the memory's `fact`), an entry of `Recalled`, and the `description` of a `PictureSeen` of a picture the
90memory had. These lines name the memory by `gid`, so the trigger `tombstone_forgets` finds them; a line written
91before they did is found by its exact words, current or older. `Forgot` carries no words at all. The trigger
92`journal_line_not_of_the_forgotten` empties a line that arrives after the forgetting (from a device that had not
93heard of it), by the same rules; `tombstone_picture` keeps the *names* of the forgotten memory's pictures (no
94content) for the `PictureSeen` case.
95
96**Turns.** A turn of the conversation has an identity (`new_turn_id`: the moment it began and a random part, no words),
97written on every line of it (`Entry.turn`, `journal_line.turn` is generated from it and indexed) and on the mentions
98of any memory learned or said again in it. It is what lets forgetting find, by identity and never by searching for words,
99what was said in the conversation a memory came from.
100
101**Forgetting reaches the conversation.** Writing a `tombstone_turn` row (trigger `tombstone_turn_empties_the_turn`)
102empties what was said in that turn: her words (`Heard.text`, and `Heard.pictures` is let go), what the model wrote
103(`ModelWrote.text`), what was said back (`Said.text`), what a look at her pictures found (`PictureSeen.description`) and a
104fact the model proposed from it that was kept out (`NotRemembered.fact`). The lines stay, with their time, their writer and how
105the turn ended. The pictures she showed in the turn are deleted with the last line that showed them
106(`journal_picture_unused`, as a picture goes with the last memory that has it). A line of the turn that arrives later (a device that had not
107heard of the forgetting pushes its lines) is emptied as it is written (`journal_line_not_of_the_forgotten`), and a tombstone that
108arrives at a device that never saw the memory still empties the turn, because the identities travel with it. The turns of
109*every* mention are emptied, so a memory said again in later turns takes those turns' words with it too. Turns of a
110memory told before turns had identities cannot be found: the data does not say where they are.
111
112**What waits to be thought about** (`pending_reflection`): after a turn the memory work reads the exchange and decides what to remember;
113until it has, the exchange is kept here (`turn`, her words, what was said back, what a look at the picture found, the
114picture names), so that a stop loses none of it, and it is taken off only when it has been dealt with. It holds her words, so a
115forgetting drops it: the exchange of each turn that is emptied, and **every** exchange that waits when a parent forgets
116(`tombstone_forgets`, only for a tombstone with a time): any of them might say the memory again, and the memory work
117would learn it back as a new memory, which is a forgotten memory returning from old words. The memory work asks, right before
118it files a fact, whether the exchange it came from still waits, with the lock held that a forgetting also takes.
119
120## The chat
121
122**`chat_session`**: one row: the running summary, when anything last happened and the version, and `scrubbed`, a JSON
123array of the identities of the forgotten memories this copy has been emptied for. The newest copy of the session wins whole
124(by version, then by activity), **unless it was not emptied for a forgetting the other copy was**: then the result is an empty session that
125has been emptied for both and is newer than either (`ChatState::merge`, one rule in the core, which every backend runs). So no order
126of syncing, and no device that was away and kept talking, brings what was forgotten back through the summary or the recent turns.
127Forgetting empties the chat as a whole (`ChatState::scrub`): the summary and the recent turns may carry what was said about a
128memory, and what they carry cannot be told from the rest by anything but its words, which forgetting does not search for. The moment
129of the last activity stays, so the hello is not said again. The chat is a *document* that its holders keep in memory, not rows a
130trigger could empty; a summary that was being written while the memory was forgotten is refused when it comes back
131(`apply_compression`), and a turn that was being answered is not kept in the chat. When the saved copy is emptied for a forgetting the
132database had not been, the write-ahead log is emptied (`scrub`).
133
134**`chat_turn`**: the recent turns, in order. The chat is *not* an append-only log of every turn, whatever the
135starting point said: the oldest turns are folded into the summary and leave, and a newer copy from another device
136replaces them. What is append-only, forever, is `journal_line`.
137
138**Reading the log is by day.** `SqliteLog::read_range(from, to)` reads the lines written in a range through the index and nothing else
139(the parents' view reads a day; a turn that began in the range ends a little past it, `EXCHANGE_SLACK_MS`), and
140`SqliteLog::days(offset, before, limit)` pages through the days that have lines, newest first, a bounded number at a time, one index lookup
141and one count per day however long the log is. A day is the household's own (`utc_offset_minutes`). `read_groups` reads everything and
142is for tests. Sync's pictures come from `journal_picture`, not from the lines.
143
144**Clearing by age** (`SqliteLog::expire_before`, the household's `log_retention_days`; none, the default, keeps everything, and
145the engine does nothing without it). A line older than the cutoff becomes `{"at_ms":N,"event":"Expired"}`: its place, its time and its
146writer stay, so the counts that sync is made of (`own_count`, a device's `seq`, the service's `position`) do not move, and its
147content is gone, as are the pictures only such lines showed. A line `Cleared { lines, older_than_ms }` is written to the log each time, so
148nothing leaves it unannounced, and a day with cleared lines says so in the page of days. The service clears its own copy the same way
149from the same setting.
150
151## The household
152
153**`household`**: one row per setting (`daily_minutes`, `quiet`, `tokens_per_window`, `token_window_hours`,
154`keep_mic_open`, `voice_daily_chars`, `pin`, `child`, `cat_theme`, `character`, `utc_offset_minutes`, `log_retention_days`, and one `character_voice:<character>` for each character), the value as JSON and its own `stamp`. `character` is the one the child chose (null or no row until she does; the cat look is then the character, and whichever of the two registers was written later is the one shown), and a character's voice is a register of its own so that two devices giving two characters a voice at the same time keep both. A voice is the id the voice service lists; a character with no row speaks in the household's base voice, never another character's. Each
155setting is a last-writer-wins register (the greater value on a tie), so a PIN changed on one device and a limit
156changed on another are both kept. A setting nobody has written has no row.
157
158`utc_offset_minutes` is the household's own day: its offset from UTC, a quarter-hour multiple within fourteen hours (anything else reads as none). A
159device reads it off the local time the shell already gives it (`LocalMoment::utc_offset_minutes`) and writes it when its own zone changes or
160when the household has none; the service ends the voice allowance's day where the household's day ends
161(`Millis::on_household_clock`), which is where the apps end theirs. With none, the day is the UTC day, as it always was.
162`log_retention_days` is how many days of the log are kept; none keeps everything, and zero is refused.
163
164**`time_spent`**: `(device, day)` to `used_ms` and `extra_minutes` (the minutes a grown-up granted). A device
165writes only its own rows, so they cannot conflict and the day's total is their *sum*, never counted twice; a count
166only rises (the write is `MAX`). A device with nothing counted has **no row**: the document never holds a zero, so what a store reads back
167is the document that was kept, and merging it again is no change (a service that restarted used to report a change, and write, on its
168first sync of an unchanged household, because the store dropped a zero the document held). The unit is milliseconds, because that is what a tick is. `device` is the
169device's own id, made once and kept in its `device.id` file, which a backup leaves out on purpose (two devices
170sharing a name would lose minutes in the merge); a restored device makes a new id, and the old id's rows stay in
171the day's sum as another device's. The service's own row is named `service` and is only ever written by a merge.
172Older days are kept as history; the document is the latest day.
173
174## The service's spending
175
176**`thinking_spend`**: when and how many tokens each question cost, inside the window the ledger keeps. **`voice_spend`**:
177the natural voice's characters spent on each day of the household's (its offset from UTC, see `household`). The rules (the rolling window, reserve and refund) are
178`whiskers-ports`'.
179
180**`speech_cache`**: spoken lines kept so a line that is the same every time costs voice characters once, on the
181service and on a device. `key` is a SHA-256 of the voice id and the exact text, `audio` the MP3, `spans` the start and end
182of each character (32-bit little-endian milliseconds), `weight` the bytes it counts against the cap and `used` a counter
183that only rises, so the smallest is the least recently used. The words and the voice are not in the table, so a reader
184sees neither; the line is put back from the request that asks for it, and an entry whose spans are not of that line is
185deleted, not trusted. Over the cap (64 MiB on the service, 32 MiB on a device) the least recently used are deleted in
186the transaction that keeps a new line. Only a line its caller declared fixed is ever written (see the service README), so a
187forgotten memory has nothing here. `voice_spend` also carries `hits` and `saved_chars`: the lines the cache answered that
188day, at zero characters, and what they would have cost.
189
190## The database itself
191
192**`meta`**: small facts about this database (the place a device has reached in the service's log). `user_version`
193is the schema version; a database written by a newer build is refused.
194
195## Forgetting and the files
196
197`PRAGMA secure_delete = ON` (set on every connection) makes SQLite overwrite what it deletes. The database is in
198write-ahead mode, so a deleted row's old bytes can also sit in the `-wal` file; every forgetting is followed by
199`wal_checkpoint(TRUNCATE)`, which moves the log into the file and empties it. `tests/forgetting.rs` searches the
200bytes of both files for a forgotten memory's words, older words, embedding and picture, on a device's database
201and on the service's; `tests/forgetting_turns.rs` does the same for what was said in the turns the memory came from, what waited
202to be thought about, and lines that arrive late or reach a device that never saw the memory; `tests/queue_and_chat.rs` for the chat; and
203`whiskers-engine/tests/forgetting.rs` and `whiskersd/tests/sync.rs` for the prompt built afterwards and for devices and the
204service together.
205
206**The speech cache holds nothing to forget.** `speech_cache` keys are hashes and its values are audio; only a line declared fixed is ever
207kept, and a line that came from a memory or from the model never is (see the service README). The engine's cached picture files
208(`picture-cache/`) are deleted when a forgetting reaches the device. A backup file made before a forgetting still holds what it held: that is
209a copy the parents made, and it is theirs to delete.