The column-level companion to Data Model, which says what each table is for and why the model is shaped as it is. The source is the Drizzle schema in packages/core/src/schema.ts; pnpm run db:generate turns it into the SQL migrations and their inlined copy in migrations.generated.ts, and the Migration Runner applies them in JS when a profile opens, recording each in __drizzle_migrations.
Every id is text: a UUIDv7 made on the device that creates the row, so two devices working offline never hand out the same one, and ids made by one device sort in the order they were made. Two are not made that way: the Inbox’s is the same fixed id in every profile, so two devices’ Inboxes sync as one (Inbox), and an issue dismissal’s is the issue’s key. Foreign keys are enforced on every connection. JSON columns are text, parsed by the repositories.
Trackables
trackable: the definition of something observed.
| Column | Holds |
|---|---|
id, name, description, color, created_at | |
value_schema | JSON, required: what an entry holds (Value Types). A tag stores { kind: "none" }. |
aggregation | its own Reading: any, count, distinct, sum, mean, median, min, max, first or last (Readings). A derived trackable’s is read from its formula, not from this column. |
capture | JSON, nullable: how it is logged (Capture Model). |
presentation | JSON, nullable: how it reads on screen (Presentation Layer). |
kind | direct, derived or none (a tag). Default direct. |
derived_spec | JSON: the formula, null unless derived (Derived Trackables). |
time_granularity | datetime (the default), or date for a trackable about the whole day, whose entries keep their day and no time. |
visibility | private (the default), protected or public. Stored for sharing; nothing reads it. |
install_id | the module_install that made it; set null if that row goes. |
module_role | its role in that install (main_sleep, napped). |
made_yours_at | when a change to how its entries read stopped it following its module; null while it follows. |
module_snapshot | JSON: its module’s definition of it as last applied, what a newer version is compared with (Modules). |
module_install: one run of a module. id, module (sleep, mind, diet), preset (null for a module with one), answers (JSON setup answers by question id), installed_at.
trackable_option: a choice’s options. Primary key (trackable_id, code); label, color, sort_order, tombstoned. An entry stores the integer code, so relabelling, recolouring and reordering touch no entry, and a removed option is tombstoned so entries holding its code keep their label. A yes/no’s two labels are in its presentation instead.
zone: a labelled range over a trackable’s values (Zones). id, trackable_id, label, color, min_value (inclusive, null for open below), max_value (exclusive, null for open above), unit, sort_order. Lower-inclusive and upper-exclusive is the rule under which zones tile. unit is the unit the bounds were typed in: a bound is a person’s intention, so it is kept as typed rather than converted to the base unit as a value is (Units & Dimensions).
achievement: a rule about which moments of a trackable’s history are worth mentioning (Achievements). id, trackable_id, zone_id (the zone that says which days count; null for every day with a value), rule (JSON), sort_order, created_at.
Entries
entry: one thing logged (Entry).
| Column | Holds |
|---|---|
id, trackable_id | |
happened_at | when it happened, local YYYY-MM-DDTHH:mm, or the day alone for a whole-day trackable. Required. |
recorded_at | when it was written down; defaults to now. Last reads by it. |
value_number | a number in its dimension’s base unit, a choice’s code, a yes/no as 0 or 1, an interval’s length in seconds. |
value_text | a text, or a choice from a fixed list of words. |
value_json | a record’s fields; an interval’s start and end with its other fields beside them; the item an amount field names, with quantity, unit and base beside it. |
is_nothing | it says there was none; it answers its span and carries no value. |
note | free text about this one entry, never its value. |
part_of | the routine entry it was done during, or null (Events). |
logged_by | what logged it for you (nasa-power, a schedule’s id), or null for your own; cleared when you change it (Logged for You). |
How each kind of value is written to these columns, and read back, is entryObject and storedOf in packages/core/src/entry-object.ts (The List Model). An entry with an item that was logged when its trackable was a whole reference holds the item under entity_id and is read the same way. A scalar’s unit is resolved on every read and never stored (Units & Dimensions).
Tags
membership: primary key (group_id, member_id), both trackables; sort_order, per edge; is_aggregator, marking the derived member that summarises its tag (Tags). The repository refuses an edge that would close a cycle, and only a none trackable can hold members.
The catalog
catalog: id, name (unique), attributes_schema (JSON, required: the fields every item fills), base_unit (what items are measured per: g, rep; null for a count), dataset (the key of a bundled starter dataset, never the catalog’s name), source (where its items come from, in words), color, created_at.
catalog_entity: an item (Catalog Entities).
| Column | Holds |
|---|---|
id, name, catalog_id, created_at | |
source | bundled, user (the default) or oneoff. |
unit | its basis unit where it differs from the catalog’s; null inherits. |
attributes | JSON values per one basis unit. |
units | JSON: its named units, each a number of basis units (an egg of 50 g). |
signs | JSON: each number’s sign, about, at_least or at_most, by attribute; one absent is Known. |
based_on_id, based_on_quantity | the item this one is based on, whose values fill the ones it lacks as About, and how many of that item’s basis units one of this one’s is. A plain id. |
barcode | the product’s barcode as digits, a UPC-A written as its EAN-13 (Reading a Label). |
Plans
template: id, name, trackable_id, weighed (what the whole weighs once made, in its items’ basis; null for the sum of its lines), created_at (Templates).
template_line: id, template_id, entity_id (the item, or null for a trackable with no item field), field_values (JSON, the line’s other values; null for all defaults), sort_order, trackable_id (for a Routine’s template, the trackable the line logs; null for a line of the template’s own trackable; a plain id, so a line naming a trackable that is gone is skipped), depth (a line one deeper than the one before goes into that line’s Event, or what it stands for), stands_for (the template a line stands in for, logged as an entry naming it and holding the lines beneath; a plain id).
rundown_layer: id, name, rule (JSON: which days), items (JSON: what it asks, default empty), sort_order (specificity; Every day is lowest) (The Rundown).
schedule: id, name, trackable_id, rule (JSON, the Rundown’s rule shape), start and end (seconds into the day; an end at or before the start is the next day’s; no end for a moment), values (JSON), entity_id (a plain id), asks (wait in the Rundown for a tap rather than log itself), sort_order, created_at (Schedules).
Showing and remembering
view (the visualization table in code): id, name, spec (JSON, required), pinned (on Home as a card of its own), trackable_id (the trackable it belongs to, or null for a visualization of several), sort_order, created_at (Visualizations, Visualization Persistence).
app_setting: key (primary key), value_json, updated_at. Each key’s JSON belongs to the code that reads it (units:preferences, routine:plan, sidebar:groupBy).
issue_dismissal: id (the issue’s key), trackable_id, entry_id (null for an issue about a day), kind, fingerprint (the entry as it stood), dismissed_at (Issues).
Sync bookkeeping, device-local and never synced: _sync_state (one row: whether capture runs, this device, its clock, the cursor and the profile it syncs with, the last error), _sync_outbox (local changes waiting to be sent), _sync_row (each synced row’s latest insert, delete and per-column clocks), _sync_expect (rows a merge is writing, so their capture triggers stay quiet) and _sync_parked (remote changes waiting for a parent) (Synced Databases).
Deletes
A foreign key cascades unless this list says otherwise:
- Deleting a trackable deletes its entries, options, zones, achievements, templates, schedules, issue dismissals and its edges either way in
membership. The repository also deletes its visualizations (theviewforeign key does not cascade, since SQLite cannot add a cascade to a column it adds), deletes the Routine template lines that log it, and takes the entries of other trackables out of its entries’ routines. - Deleting a zone deletes the achievements that name it: a rule about a range that is gone has nothing left to say.
- Deleting an entry deletes its issue dismissals. Its parts are kept or deleted as the person chooses (Events).
- Deleting a catalog deletes its items; deleting an item deletes the component lines and template lines that name it.
- Deleting a module install sets its trackables’
install_idto null. entry.part_of,template_line.trackable_id,catalog_entity.based_on_id,schedule.entity_idand an item’s id insidevalue_jsonare plain ids: a link to something not there reads as none.
Indexes and writes
The only indexes are the primary keys and the unique index on catalog.name. SQLite does not index a foreign key on its own, so reading a trackable’s entries, a trackable’s tags, an item’s components or a template’s lines scans the table. At one person’s history this is fast enough; it is the first place to look when a read gets slow.
The app’s driver runs no transaction, so a write of several rows is not atomic; which writes that replace a set of child rows are diffed against what is stored, and which delete and insert again, is in Repository Seam.
Related
Data Model · Migration Runner · Repository Seam · Architecture Overview · Synced Databases