Storage against the book: what Designing Data-Intensive Applications says about the unifyStorage work
the owner, 2026-11-08: the book (Kleppmann, Designing Data-Intensive Applications) is the basis for log's data modelling from here on. This page checks what the unifyStorage branch has built so far (#96, #97, PR #98) against it. For each point it gives the book's section, what log does today, and what has to change. Chapters 1 to 4, 7 and 11 to 12 were read in full; chapters 8 and 9 for what applies to several processes sharing one machine.
docs/unifyStorage.md is the plan. This page is the correction to it.
What is already right
- One SQLite database on one machine (ch. 1, "Approaches for Coping with Load"): the book's advice is to keep the database on a single node until scale forces otherwise. log's load is nowhere near that.
- One writer at a time (ch. 7, "Actual Serial Execution"): WAL with
BEGIN IMMEDIATEmakes every write transaction serializable. - Immutable content kept by its hash (ch. 11, "Advantages of immutable events"; ch. 3, log-structured storage): the planned
blobsstore fits. - Cursors as consumer offsets (ch. 11, "Consumer offsets"): each reader keeps its own position in
events. - Claims are taken in one transaction (ch. 7, "Materializing conflicts"): read and write of a claim happen together.
- Refusing to send while behind (ch. 12, "Timeliness and Integrity"): integrity comes before speed, as the book says it should.
What has to change before #98 merges
1. Time is stored as text from three clocks
Book: an index only helps if its sort order is the order you mean (ch. 3, "SSTables and LSM-Trees", "Multi-column indexes"). Wall clocks from different sources can't order events (ch. 8, "Timestamps for ordering events"). Event time and processing time are different things (ch. 11, "Reasoning About Time").
Today: messages.sentAt, interactions.at, chats.lastMessageAt and ledger.at are ISO text in mixed shapes (…Z and …213000+00:00 both appear in log.db). sentAt quietly falls back to the time log received the event. Catch-up filters with a text compare (m.at > gap.fromAt).
Change:
- Store
sentAtMs,receivedAtMsandatMsas UTC milliseconds (INTEGER NOT NULL), normalised once at the boundary. The raw string stays in the raw payload. - Add
timeSource(platformorreceived) so a fallback is visible. - Order by
(sentAtMs, seq). Inside the database,seqdecides order; across platforms, the platform's own ids do. - Indexes become
messagesByChat (chatId, sentAtMs, seq)andmessagesBySender (senderPlatform, senderUserId, sentAtMs, seq).
2. The typed tables have to be derived from the log, and only from it
Book: the event log is the system of record and every other table is derived from it, so any table can be rebuilt by replaying the log (ch. 11, "State, Streams, and Immutability"; ch. 12, "Application code as a derivation function"). Events are never changed in place (ch. 11, "Advantages of immutable events").
Today:
- Full reads and the conversation import write
messages,chats,identitiesandmembershipsdirectly, with noeventsrow, and thenUPDATE messages.textin place. addToPayloadpatches a stored event withjson_patch.appendEventcuts the payload at 20,000 characters. A cut payload is no longer valid JSON, so deriving from it silently gets{}.- Deriving depends on things outside the log: a per-process guess of the account's own id, and reads of
inboxSync.json. - Migration 3 only adds rows, so a bad row is never corrected.
Change:
- Every read, catch-up, backfill and import appends to
eventsfirst (source=read,catchUp,backfill,import). Typed rows come only from deriving events. - A change is a new event (
messageEdited,attachmentsSeen), never an edit of an old one. - The full raw answer is kept, compressed in
blobs(rawHash), never cut. - Anything looked up (a handle, the account's own id) is written into the event when it is appended, so replaying never reads files or caches.
log storage rebuildclears the typed tables and replayseventsinseqorder.log storage verifyreplays into a scratch database and compares counts and row hashes with the live tables. CI runs it on a fixture.- Each derived row records the
extenderVersionthat made it, so rows from an older extender can be re-derived.
3. Identities need stable ids, not names
Book: store an id that means nothing to people and never changes, and keep the name in one place (ch. 2, "Many-to-One and Many-to-Many Relationships").
Today: where a platform gives no id, the person is keyed by their handle (@someone). One person can become two rows (@someone and 12345), and a renamed handle splits them for good. The account's own messages are keyed self:<account> or by a guessed id. The name is copied into messages.senderLabel, events.withWho and others. people and identities hold the same fact twice.
Change:
accounts (platform, account, userId)holds each account's own real id, written when its session starts.handleAliases (platform, handle, userId, firstSeenAt, lastSeenAt). A sender seen only by handle gets apending:id until the real id is known. When it is learned, its rows are re-keyed in one transaction and the merge is logged inidentityMerges.- Names are read through a join on
identities, not copied into every row. peopleis retired.- Every id is stored as text (X ids are larger than JSON numbers can hold exactly).
4. The owner's generation number has to fence every write
Book: a process that held a lease can pause (garbage collection, a sleeping laptop) and wake up still believing it holds it. The storage has to refuse a write that carries an old token, rather than trusting the writer to check (ch. 8, "Process Pauses", "Fencing tokens").
Today: each new owner raises owners.generation, but appendEvent, recordPosition, openGap and markGap never check it, so an owner that has been replaced keeps writing. A process is judged dead on any error, including permission errors. On Windows a reused pid can look alive. After the laptop sleeps, every heartbeat is stale and the first hook can kill a healthy owner.
Change:
- Every owner write runs inside
BEGIN IMMEDIATEtogether withSELECT generation FROM owners. On a mismatch it rolls back and the process exits. - Store the process start time with the pid. Treat a permission error as alive. Require two stale readings 10 s apart before killing an owner.
- Retry the heartbeat on
SQLITE_BUSYinstead of letting one busy database stop the owner.
5. Chat claims have to fence each part of a send
Book: the same fencing rule (ch. 8), and decisions made on a read outside the transaction that acts on them (ch. 7, "Write Skew and Phantoms").
Today: taking a claim is correct, but stillHeld is a plain read before the send. The holder is the bare pid, so two calls inside one MCP server look like the same holder. Only Discord and Slack check stillHeld; X DMs, the X queue and Instagram don't, though rule 134 says every send does.
Change:
- Holder = pid + process start time + a random id per call.
- Each part of a send: one transaction checks holder, generation and expiry, renews the claim and writes an
intentaction with an operation id. Then send. Then recordsentunder the same check. - The operation id is matched against the account's own echo, so a retry after a crash can tell the message already went out (ch. 12, "Operation identifiers").
- Just before a send, refuse if any event for that chat landed after the
seqthe voice gate read up to.
6. A gap can be closed over a newer drop
Book: lost updates and phantoms when a check and an insert are separate (ch. 7, "Preventing Lost Updates", "Write Skew and Phantoms").
Today: openGap checks, then inserts, outside a transaction. If the socket drops again during a catch-up, the existing gap is returned, and markGap(closed) then closes it, so the messages from the second drop are lost and the account reads as current.
Change: a partial unique index on gaps (platform, account) for open statuses, a drops counter that openGap raises, and closing with compare-and-set (WHERE id = ? AND status = 'catchingUp' AND drops = ?). If nothing changed, catch up again.
7. One stable key per message
Book: a retried append has to be recognised by an id that means the same thing end to end (ch. 11, "Idempotence"; ch. 12, "The end-to-end argument").
Today: eventIdOf hashes the time text and the message text, which differ between the watch, the catch-up and a read (time formats, the (audio: …) label). So one message can get several ids. Two different messages with the same time and text collide.
Change: the key is platform:account:conversation:platformMessageId, unique in events. Only a message with no platform id falls back to normalised time plus raw text.
8. Transactions have to be short
Book: serial execution only works if every transaction is short (ch. 7, "Actual Serial Execution").
Today: migration 3, typeAllEvents, the conversation import (20 s on the real data) and saveConversationRows hold the write lock for their whole run, longer than the 5 s every other writer waits. Imports write row by row and mark themselves done afterwards, so a crash in between imports twice.
Change: bulk work runs in batches of about 1,000 rows, each batch with its progress mark in the same transaction, resumable from that mark. Migrations only change the schema; moving data is a resumable job, not part of the migration.
9. Old code and new code share the file
Book: while processes on different versions share a database, new code must read old data and old code must read new data. Only additive, defaulted changes keep both working, and data outlives code (ch. 4, "Dataflow Through Databases", "Modes of Dataflow").
Today: any database newer than the code is refused (schemaTooNew), though every change so far only adds tables and nullable columns. The other way round is worse: watchers still running older code append events without typed rows, and nothing ever types them.
Change:
- A
metatable withminReaderVersion. Only a breaking migration raises it, and code is refused only below it. - Migrations only add. New columns are nullable or defaulted. Nothing is renamed, retyped or dropped in place.
events.typedAt, plus a paced background sweep that derives every event where it is null.- Data migrations are frozen copies, not calls into live code that keeps changing.
Performance: how it is measured and what it must meet
10. Measure response time as a distribution, at the caller, with its tail
Book (ch. 1, "Describing Performance", "Percentiles in Practice"):
- Response time is a distribution. Report percentiles, not the mean: p50, p95, p99 and p99.9.
- Measure what the caller waits for, queueing included, not only the work itself.
- When one request needs several calls, it waits for the slowest, so a rare slow call becomes common (tail latency amplification).
- Percentiles over a rolling window are kept with histograms (HdrHistogram, t-digest). Percentiles are never averaged; histograms are added.
- A load test sends at fixed times whatever the answers, or it hides queueing.
Today:
log timingreports p50, p95 and p99. There is no p99.9, no window, and no count of how many samples a percentile rests on.benchStorage.tsmeasures only the oldeventstable. Its "whole store" figure is one sample. The chat read has 600 samples, so its p99 rests on 6 values and p99.9 can't be computed.- The numbers in PR #98 were 300 serial queries from one process with nothing else writing, so lock waits never showed.
- Targets were set per read, not per call.
Change:
log timingadds p99.9 and showsnbeside every percentile, refusing a percentile with too few samples behind it (p99 needs at least 1,000, p99.9 at least 10,000).spanRollups (tool, stage, windowStartMs, histogram BLOB): HdrHistogram buckets per 10 minutes, added together for any longer window, never averaged.- Lock wait (
SQLITE_BUSY) is its own span. benchStorage.tsseeds a scratch database at 500,000 messages and 50,000 identities with skewed chat sizes, takes at least 10,000 samples per read path, and runs each path while owner processes append at their measured peak, sending at fixed times.- Targets are set per call. A send makes about 20 storage reads; for 1 call in 100 to wait on a slow read, each read has to meet its target at p99.9, not p99. The send's whole storage time is held to p99 ≤ 50 ms.
- Reads that cover a whole person (every identity linked to them) are one query with
INor a join, not one query per identity.
11. Describe the load
Book: name the few numbers that drive the load, skew included, before talking about growth (ch. 1, "Describing Load").
Change: log storage load reports, from the spans and the database:
- writer processes at once, and events per second per owner (live, and at backfill peak);
- write transaction length at p99, and lock waits;
- reads per write for each tool;
- messages per chat (p99 and max) and identities per person.
12. Indexes follow the queries
Book: every index costs a write on every insert; keep the ones a query uses (ch. 3, "Comparing B-Trees and LSM-Trees", "Other Indexing Structures").
Today and change:
messagesBySentAtserves no query: drop it.chatshaschatIdand aUNIQUE (platform, account, conversationId)that say the same thing: keep one.eventsusesAUTOINCREMENT, which costs a write per insert for nothing: a plain rowid does.- The full-text index stores each text a third time: make it external-content over
messages. ledgerRowsfilters by kind and account and sorts byseq: add(kind, account, seq).chatEventsandownTextsSincefilter by platform, account, type and time: add(platform, account, type, receivedAtMs).
13. Online reads fetch rows by key; full scans are batch jobs
Book: online paths read a few rows through an index; scanning everything belongs to batch and analytics (ch. 3, "Transaction Processing or Analytics?"; ch. 2, "Data locality for queries").
Today: loadConversations reads every message, attachment and membership and rebuilds them in memory. Its account filter only applies to chats. Its cache check counts every row.
Change: the full scan is kept only for building the search index, as a streaming read filtered by account. The voice gate and every other per-chat or per-person reader use indexed queries. The cache is keyed on a dataVersion counter raised inside each write transaction.
14. Analytics read rollups, not the live tables
Book: reports aggregate precomputed data instead of competing with live writes (ch. 3, "Aggregation: Data Cubes and Materialized Views").
Change: log timing reads spanRollups and only the columns it needs from spans. Old spans are deleted in batches.
The person model for #95
15. One person across platforms is many-to-many
Book: once data is interconnected, relationships need references and joins (ch. 2, "Many-to-One and Many-to-Many Relationships"). A property graph is a vertices table and an edges table indexed both ways, and recursive queries walk it (ch. 2, "Property Graphs", "Graph Queries in SQL").
Today: identities.personId is one column, with no evidence, confidence, record of who linked it, or way to undo a link. interactions has no target person. A group seen by two accounts is two rooms.
Change:
persons (personId, displayName, createdAt).identityLinks (personId, platform, userId, evidence, confidence, linkedBy, atMs, revokedAtMs), indexed by person and by identity. A person's identities are the links that haven't been revoked.rooms (platform, platformRoomId), with each account's view of a room pointing at it.interactions.targetUserId.- An
edgesview over messages, interactions and memberships. The relationship router queries it withWITH RECURSIVE. SQLite is enough at this size; a graph database isn't needed.
Storage details
16. Blobs and vectors
Book: clustering stores the whole row in the index, which only pays for small rows (ch. 3, "Storing values within the index"). Deleting from immutable storage needs compaction (ch. 3, "SSTables and LSM-Trees"). An encoding that can change needs its version stored with it (ch. 4, Avro's writer's schema).
Change:
blobskeeps its metadataWITHOUT ROWID, and the bytes go in rowidblobChunksof 256 KB to 1 MB. Hashes are stored as 32-byte BLOBs.auto_vacuum = INCREMENTALwith a scheduledincremental_vacuumis the compaction step. The WAL is size-limited.- The SHA-256 is checked on every read.
- int8 vectors carry a format byte (scale, byte order, version).
17. The X live feed becomes events
Book: readers follow a log by offset; the log, not a file of its own, is what they follow (ch. 11, "Consumer offsets"; ch. 12, "Pushing state changes to clients").
Today: live.jsonl is read by byte offset per session, but the offset is taken in bytes and applied in characters, so an emoji or a non-English name skips or repeats events. A half-written line is skipped and lost. Agents are told to tail -F the file.
Change: the badge poll appends badge, notification and pollRefused to events in the owner's transaction. Each hook session reads with a cursor. log inbox follow --after <seq> streams new events over the owner's relay (rule 132), and replaces tail -F. live.jsonl and .hookSessions are retired.
Order of work
- Time as integer milliseconds, and the stable message key (items 1, 7).
- Everything enters through
events; typed tables are derived only; rebuild and verify (item 2). - Accounts, handle aliases and identity merges (item 3).
- Fencing for owners and claims, and the gap fix (items 4, 5, 6).
- Short, resumable bulk work and compatible migrations (items 8, 9).
- Measurement: p99.9, histograms, the 500,000-row benchmark under concurrent writes, per-call budgets, the load report (items 10, 11, 14).
- Indexes and indexed readers (items 12, 13).
- The X live feed as events (item 17).
- The person model for #95 (item 15), then blobs and vectors (item 16).
Each step is measured before and after with the benchmark from step 6 (built first if a step needs its numbers), and lands as its own commit naming #96 or #97.