The schema

Every table of the database, in prose, and how each merges. The SQL is migrations/0001_init.sql; this file is what it means. A device and the service have the same schema (the service uses the tables a device does not, thinking_spend and voice_spend, and a device uses journal_line for its own lines and the copy it pulls of the others; 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 the merge itself is whiskers-core's pure function, so the mirror is the snapshot a device sends and is sent), and a tombstone that holds only the id.

A database at version 5 is brought to 6 (migrations/0006_forgetting_the_turn.sql, tested with data in tests/migration.rs): nothing it holds moves. The mentions it had name no turn, so forgetting a memory learned before turns had identities finds no conversation to empty (its words in the log are still emptied, by identity, as before); everything learned from now on does.

The merge rules are not in SQL. They are MemoryDoc::merge, Household::merge and ChatState::merge in whiskers-core, run on a document read from these tables; the store writes the difference back in one transaction. The rules SQL expresses better are the ones about what must not exist, and those are in the schema: a 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 memory's rows cannot outlive it (ON DELETE CASCADE), a picture goes with the last thing that showed it (a trigger), a setting's value and a register's two halves cannot disagree (CHECK).

Memory

fact: one current row per memory, keyed by gid (its identity on every device) and numbered by id (this holder's own numbering, AUTOINCREMENT, so an id is never handed out twice, even after the newest memory is forgotten). text is a last-writer-wins register stamped text_at_ms (zero while it is the words the memory was learned in); the later write wins and the smaller words on a tie. hidden with visibility_at_ms is the child's hiding, a last-writer-wins register (a tie stays hidden). cover_picture with cover_at_ms is the card's star, a last-writer-wins register (a tie goes to the smaller picture name; both are null while nobody starred, and CHECK keeps them together). icon is the pixel icon, whichever any side chose, the smaller name if two did. kind, who, place and said_when are what the model filed it as. learned_at_ms is when it was first told.

fact_mention: append-only, one row each time she said the memory, the first telling included, keyed by (gid, at_ms, device, turn). turn is the identity of the turn of the conversation she said it in (see Turns below), empty for a mention made before turns had identities. Merges by union (a device only ever adds its own), so the same telling known twice is one row. These are the timeline's entries: the memory page shows the memory under every day it has a row for.

fact_revision: append-only, the words a memory had before they changed, keyed by (gid, at_ms, text) where at_ms is when the words were replaced. That key is what makes the same change one row however it reached a device (the device that made it, or another that learned of it when the later words arrived), so revisions merge by union. device is who replaced them, empty where a device only learned of it. The starting point had a rev counter; two devices cannot agree on one, so it is not a key. Nothing on the way to a prompt reads this table.

fact_embedding: the embedding of the current words, 32-bit floats little-endian. Dropped when the words change (it is of words that are gone) and made again; an embedding computed on one side serves both, the first kept.

fact_picture: which pictures go with a memory, in order. It names a picture; it does not require its bytes to be there yet, because pictures sync after the memory that names them.

tombstone: what the parents forgot: gid, at_ms, device, never content (at_ms is zero where only the identity is known, as for a memory dropped as a duplicate; a zero is not a forgetting, so it empties no conversation and drops nothing that waits, see below). The identity decides everything and wins over any other state from any device; where two sides know when and by whom, the earlier account is kept, so every order agrees. Writing a row deletes the memory and, through ON DELETE CASCADE, its mentions, revisions, embedding and picture links; the last link to a picture going deletes the picture too unless a logged conversation still shows it. Hiding by the child is not this: it is fact.hidden, a soft delete that keeps everything.

picture: the bytes of a picture under the name every device knows it by (made from the time and a counter, so one name is one picture). Never replaced.

tombstone_turn: the turns of a forgotten memory, (gid, turn), identities only. A tombstone carries them (Tombstone.turns, in the sync documents) to every holder, which writes them here; writing one is what empties the turn's words (see Forgetting reaches the conversation).

soft_action: the log of soft deletes, so that one can be undone for a few seconds. Putting a memory away (the child hiding it) writes a row in the same transaction: kind (put_away today), the memory's gid, at_ms, and undone_at_ms once it has been moved back (null until then). Memory::undo(id, now) restores the memory (a later stamp on the same register, so it beats the hide on every device) and spends the row; whether it is allowed (the window is UNDO_WINDOW_MS, eight seconds, in whiskers-core's undo.rs, not in SQL) is the core's rule. The row belongs to its memory (ON DELETE CASCADE): the parents forgetting a memory deletes its actions, so a forgotten memory has nothing to undo and a row for one cannot be written. The parents' forget is not a soft action and is never in this table. The rows hold an identity and times, never words.

The log

journal_line: append-only; one row per entry a device wrote, (device, seq) being that device's own count from zero and position the order of arrival. at_ms (when the line was written), turn and live are generated from the line itself (so there is no second copy of any of them to disagree) and indexed: journal_line_by_time for reading a day, 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 it by position. On a device it holds the device's own lines and the lines it has pulled of the others'. Never changed or removed (except that a device throws away its copy of others' lines to take them again).

journal_picture: which pictures a logged conversation shows, so that a picture the parents' log still needs is not deleted with a memory made from it.

Forgetting also empties the log. The parents' log is append-only, but a line that carries a forgotten memory's words keeps its place, its time and its writer and loses the words (they become empty): Remembered, PutAway, Restored (the memory's fact), an entry of Recalled, and the description of a PictureSeen of a picture the memory had. These lines name the memory by gid, so the trigger tombstone_forgets finds them; a line written before they did is found by its exact words, current or older. Forgot carries no words at all. The trigger journal_line_not_of_the_forgotten empties a line that arrives after the forgetting (from a device that had not heard of it), by the same rules; tombstone_picture keeps the names of the forgotten memory's pictures (no content) for the PictureSeen case.

Turns. A turn of the conversation has an identity (new_turn_id: the moment it began and a random part, no words), written on every line of it (Entry.turn, journal_line.turn is generated from it and indexed) and on the mentions of any memory learned or said again in it. It is what lets forgetting find, by identity and never by searching for words, what was said in the conversation a memory came from.

Forgetting reaches the conversation. Writing a tombstone_turn row (trigger tombstone_turn_empties_the_turn) empties what was said in that turn: her words (Heard.text, and Heard.pictures is let go), what the model wrote (ModelWrote.text), what was said back (Said.text), what a look at her pictures found (PictureSeen.description) and a fact the model proposed from it that was kept out (NotRemembered.fact). The lines stay, with their time, their writer and how the turn ended. The pictures she showed in the turn are deleted with the last line that showed them (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 heard of the forgetting pushes its lines) is emptied as it is written (journal_line_not_of_the_forgotten), and a tombstone that arrives at a device that never saw the memory still empties the turn, because the identities travel with it. The turns of every mention are emptied, so a memory said again in later turns takes those turns' words with it too. Turns of a memory told before turns had identities cannot be found: the data does not say where they are.

What waits to be thought about (pending_reflection): after a turn the memory work reads the exchange and decides what to remember; until it has, the exchange is kept here (turn, her words, what was said back, what a look at the picture found, the picture 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 forgetting drops it: the exchange of each turn that is emptied, and every exchange that waits when a parent forgets (tombstone_forgets, only for a tombstone with a time): any of them might say the memory again, and the memory work would learn it back as a new memory, which is a forgotten memory returning from old words. The memory work asks, right before it files a fact, whether the exchange it came from still waits, with the lock held that a forgetting also takes.

The chat

chat_session: one row: the running summary, when anything last happened and the version, and scrubbed, a JSON array of the identities of the forgotten memories this copy has been emptied for. The newest copy of the session wins whole (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 has been emptied for both and is newer than either (ChatState::merge, one rule in the core, which every backend runs). So no order of syncing, and no device that was away and kept talking, brings what was forgotten back through the summary or the recent turns. Forgetting empties the chat as a whole (ChatState::scrub): the summary and the recent turns may carry what was said about a memory, and what they carry cannot be told from the rest by anything but its words, which forgetting does not search for. The moment of 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 trigger could empty; a summary that was being written while the memory was forgotten is refused when it comes back (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 database had not been, the write-ahead log is emptied (scrub).

chat_turn: the recent turns, in order. The chat is not an append-only log of every turn, whatever the starting point said: the oldest turns are folded into the summary and leave, and a newer copy from another device replaces them. What is append-only, forever, is journal_line.

Reading the log is by day. SqliteLog::read_range(from, to) reads the lines written in a range through the index and nothing else (the parents' view reads a day; a turn that began in the range ends a little past it, EXCHANGE_SLACK_MS), and SqliteLog::days(offset, before, limit) pages through the days that have lines, newest first, a bounded number at a time, one index lookup and one count per day however long the log is. A day is the household's own (utc_offset_minutes). read_groups reads everything and is for tests. Sync's pictures come from journal_picture, not from the lines.

Clearing by age (SqliteLog::expire_before, the household's log_retention_days; none, the default, keeps everything, and the engine does nothing without it). A line older than the cutoff becomes {"at_ms":N,"event":"Expired"}: its place, its time and its writer stay, so the counts that sync is made of (own_count, a device's seq, the service's position) do not move, and its content 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 nothing 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 from the same setting.

The household

household: one row per setting (daily_minutes, quiet, tokens_per_window, token_window_hours, 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 setting is a last-writer-wins register (the greater value on a tie), so a PIN changed on one device and a limit changed on another are both kept. A setting nobody has written has no row.

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 device reads it off the local time the shell already gives it (LocalMoment::utc_offset_minutes) and writes it when its own zone changes or when the household has none; the service ends the voice allowance's day where the household's day ends (Millis::on_household_clock), which is where the apps end theirs. With none, the day is the UTC day, as it always was. log_retention_days is how many days of the log are kept; none keeps everything, and zero is refused.

time_spent: (device, day) to used_ms and extra_minutes (the minutes a grown-up granted). A device writes only its own rows, so they cannot conflict and the day's total is their sum, never counted twice; a count only 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 is 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 first 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 device's own id, made once and kept in its device.id file, which a backup leaves out on purpose (two devices sharing a name would lose minutes in the merge); a restored device makes a new id, and the old id's rows stay in the day's sum as another device's. The service's own row is named service and is only ever written by a merge. Older days are kept as history; the document is the latest day.

The service's spending

thinking_spend: when and how many tokens each question cost, inside the window the ledger keeps. voice_spend: the 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 whiskers-ports'.

speech_cache: spoken lines kept so a line that is the same every time costs voice characters once, on the service 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 of each character (32-bit little-endian milliseconds), weight the bytes it counts against the cap and used a counter that only rises, so the smallest is the least recently used. The words and the voice are not in the table, so a reader sees neither; the line is put back from the request that asks for it, and an entry whose spans are not of that line is deleted, not trusted. Over the cap (64 MiB on the service, 32 MiB on a device) the least recently used are deleted in the transaction that keeps a new line. Only a line its caller declared fixed is ever written (see the service README), so a forgotten memory has nothing here. voice_spend also carries hits and saved_chars: the lines the cache answered that day, at zero characters, and what they would have cost.

The database itself

meta: small facts about this database (the place a device has reached in the service's log). user_version is the schema version; a database written by a newer build is refused.

Forgetting and the files

PRAGMA secure_delete = ON (set on every connection) makes SQLite overwrite what it deletes. The database is in write-ahead mode, so a deleted row's old bytes can also sit in the -wal file; every forgetting is followed by wal_checkpoint(TRUNCATE), which moves the log into the file and empties it. tests/forgetting.rs searches the bytes of both files for a forgotten memory's words, older words, embedding and picture, on a device's database and on the service's; tests/forgetting_turns.rs does the same for what was said in the turns the memory came from, what waited to 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 whiskers-engine/tests/forgetting.rs and whiskersd/tests/sync.rs for the prompt built afterwards and for devices and the service together.

The speech cache holds nothing to forget. speech_cache keys are hashes and its values are audio; only a line declared fixed is ever kept, and a line that came from a memory or from the model never is (see the service README). The engine's cached picture files (picture-cache/) are deleted when a forgetting reaches the device. A backup file made before a forgetting still holds what it held: that is a copy the parents made, and it is theirs to delete.