Skip to content
C.W.K.
Lesson 01 of 05 · published

One Table, Many Kinds

~15 min · sqlite, schema, json, indexes

Level 0Sketch
0 XP0/43 lessons0/13 achievements
0/100 XP to next level100 XP to go0% complete

Where the studio keeps what it knows

Avatars holds the originals of every soul's avatar, my own face included. It also holds every request that made a picture, every choice Dad made in review, and every publication sent to Pippa. All of it sits in one data root outside the source checkout. Settings.data takes AVATARS_DATA when that is set, and otherwise the family registry's engine_data_root("avatars"). The house sign puts it on the registered service host, in a directory of its own beside the other family engines' data and outside Git.

When the store starts, it makes sure four things exist there:

  • originals/: the native bytes, each file named by its own SHA-256.
  • thumbnails/: small derivatives that can always be rendered again from an original.
  • exports/: delivery copies, such as a YouTube JPG.
  • avatars.db: one SQLite database, switched to WAL mode.

The worker adds a worker.lock beside them when it starts. It matters below.

Two tables

The database has exactly two tables. records holds every domain object the studio has: an id, a kind, a parent, a version, two timestamps, and a JSON body with everything else. requests binds a caller's request id to a fingerprint of what was asked and to the record it created. That one gets its own lesson in this track.

These are the kinds the code writes today:

kindparentwhat it is
assetemptyan original image or clip: hash, size, subjects, references
setempty (the soul is in the body)a collection of frames, with its published map, selection and live revision
batchthe set, or emptyone frozen generation request
taskits batchone image or clip to make, with its state
profilethe soulreference choices, named looks, appearance rules
templateempty (id template-<workflow>)house rules Dad edited for one workflow
publicationthe seta reviewed snapshot sent to Pippa
ownershipemptythe record of authority over the legacy sets moving into Avatars, once
subject-adoptionthe adopted subjectthe same move, once, for one registered subject left out of the cutover

unpack turns a row into one flat dict. The body's fields go in first and the columns go on top, so a stray body field can never shadow the real id, kind or version.

Two indexes, each with a reason

record_kind_parent serves the question the studio asks most often: the tasks of this batch, the publications of this set, the profile of this soul. The second index arrived the next day, on 2026-09-30. record_kind_state indexes an expression, json_extract(body,'$.state'), because the worker asks for active tasks once a second. The comment above it gives the reason: that poll must not grow with the history of every finished task. Without it, every batch ever finished would sit inside every one-second scan.

An expression index helps only a query that uses the same expression. When Store.list is given states, it writes exactly that text into its WHERE clause. Run EXPLAIN QUERY PLAN on the result and SQLite answers SEARCH records USING INDEX record_kind_state (kind=? AND <expr>=?). Spell the expression another way, say body->>'$.state', and the plan still says SEARCH, but it ends at (kind=?): the index narrows on the kind alone, and every task ever written is read and filtered. SCAN is not the warning sign here. The warning is a SEARCH whose parentheses stop short.

Why one generic table fits this app

A table per kind, with typed columns and foreign keys, is the textbook answer. Avatars chose one table, and the choice rests on three facts about this particular app.

  1. One writer. The architecture says it in one sentence: the host registered for Avatars is its single writer. In daily operation that is the engine process. Its worker takes an exclusive flock on worker.lock, and a second worker on the same data root refuses to start with Another Avatars worker already owns this database. With no writers to coordinate across processes, the rules a schema would enforce can live in one codebase.
  2. Short transactions. The store's docstring opens with the rule: SQLite transactions never span network calls. Each transaction reads and writes a few rows and closes. Whatever Ember or Pippa has to say is asked before it opens or after it closes.
  3. Kinds that keep arriving. Both tables are exactly as the first commit wrote them on 2026-09-29. Since then, templates, clips with their duration and poster, and subject adoption all arrived as new kinds or new body fields, with no migration. The only schema change in the store's history is the state index.
Principle: Let the number of writers choose the schema. One process with short transactions can keep its rules in code; many writers need the database to keep them.

What it costs

The price is real, and you can see it in the code. The body has no column constraints, so a missing field surfaces in Python rather than as a refused insert. Relations are a convention carried by parent; no foreign key is declared, even though every connection turns foreign_keys on. Some lookups read a whole kind and filter in Python. save_asset finds earlier copies of the same bytes by listing every asset and comparing sha256, and the library search filters and pages its results in Python as well. At the size of one family's studio that is cheap. If it ever stops being cheap, a sha256 lookup is the first candidate for another expression index.

Two one-time migration scripts in the repository opened the same store directly. They moved authority into Avatars once and are history now; the engine is the writer you plan around.

If WAL, JSON functions or expression indexes are new to you, SQLite Quest teaches them properly. This lesson shows one app's choice and the reasons behind it.

Code

cwkAvatars, backend/store.py: the whole schema, created on start (verbatim, lines 42-49)·python
        with self.tx() as db:
            db.execute("PRAGMA journal_mode=WAL")
            db.execute("CREATE TABLE IF NOT EXISTS records (id TEXT PRIMARY KEY, kind TEXT NOT NULL, parent TEXT NOT NULL DEFAULT '', version INTEGER NOT NULL DEFAULT 1, body TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)")
            db.execute("CREATE INDEX IF NOT EXISTS record_kind_parent ON records(kind,parent)")
            # The worker polls for active tasks every second; the poll must not
            # grow with the history of every finished task.
            db.execute("CREATE INDEX IF NOT EXISTS record_kind_state ON records(kind, json_extract(body,'$.state'))")
            db.execute("CREATE TABLE IF NOT EXISTS requests (id TEXT PRIMARY KEY, fingerprint TEXT NOT NULL, record_id TEXT NOT NULL)")
cwkAvatars, backend/store.py: unpack, get and list, the reads every kind shares (verbatim, lines 62-83)·python
    @staticmethod
    def unpack(row) -> dict:
        return {**json.loads(row["body"]), "id": row["id"], "kind": row["kind"], "parent": row["parent"], "version": row["version"], "created_at": row["created_at"], "updated_at": row["updated_at"]}

    def get(self, id: str, kind: str | None = None) -> dict:
        with self.tx() as db:
            row = db.execute("SELECT * FROM records WHERE id=?", (id,)).fetchone()
        if row is None or (kind is not None and row["kind"] != kind):
            raise StudioError("Record not found", 404)
        return self.unpack(row)

    def list(self, kind: str, parent: str | None = None, *, states: tuple[str, ...] | list[str] | None = None) -> list[dict]:
        sql, params = "SELECT * FROM records WHERE kind=?", [kind]
        if parent is not None:
            sql += " AND parent=?"
            params.append(parent)
        if states:
            sql += f" AND json_extract(body,'$.state') IN ({','.join('?' * len(states))})"
            params.extend(states)
        with self.tx() as db:
            rows = db.execute(sql + " ORDER BY created_at,id", params).fetchall()
        return [self.unpack(row) for row in rows]

External links

Exercise

Take an app you are building that has at least three kinds of object. Sketch it as one records table (id, kind, parent, version, JSON body, two timestamps) plus whatever it needs for request ids. Find the query that runs most often over a growing history, such as a poll for active jobs, and give it an expression index. Load 100,000 finished rows and five active ones into a scratch database, then run EXPLAIN QUERY PLAN on that query before and after you create the index.
Hint
Write the WHERE clause with the same function and JSON path the index uses; when they match, the plan line ends with (kind=? AND <expr>=?), and when they do not, a composite index that leads with kind still gives SEARCH, ending at (kind=?), so read the parentheses, not the verb. Then count your writers. If more than one process writes, put the rules back into the schema: separate tables, typed columns, constraints.

Progress

Progress is local-only — sign in to sync across devices.
← 🖼️ Ember Creates, Avatars RemembersQuiz · 4 questionsNext →Files Before Rows
Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)
Sign in — Please sign in to comment.

No comments yet — be the first.