Data Model

Datler stores two kinds of thing: what is observed over time, and the reusable things it is observed about. A Trackable is the definition of something observed, and its entries are the data. A catalog holds the items entries point at: foods, exercises, books, emotions. Every other table organises, plans or shows those two. This note is the map, what each table is for and why the model is shaped as it is; the columns, keys and indexes are in Database Schema.

The schema is the Drizzle schema in packages/core/src/schema.ts. drizzle-kit generates the SQL migrations from it, and the Migration Runner applies them in JS when a profile opens, for the app and the db:* tooling alike. Everything reads and writes the tables through the repositories of @datler/core (Repository Seam). Each profile is one SQLite file (Profiles).

flowchart LR
  trackable["trackable"] --> entry["entry"]
  trackable --> option["trackable_option"]
  trackable --> zone["zone"]
  zone --> achievement["achievement"]
  trackable --> achievement
  trackable -- "membership (tags)" --> trackable
  catalog["catalog"] --> item["catalog_entity"]
  entry -. "an item's id in value_json" .-> item
  entry -. "part_of" .-> entry
  trackable --> template["template"] --> line["template_line"]
  trackable --> schedule["schedule"]
  trackable --> view["view (visualizations)"]

Trackables and what they own

A trackable is one row. Beside its name, description and colour, its definition sits in three JSON layers, each read by its own part of the app:

• value_schema says what an entry holds: a list of fields, each of a kind (Value Types, The List Model).
• capture says how it is logged: where the Rundown asks it, what an empty day means, quick-add amounts and fills, whether it is a Routine, whether its entries put days on hold (Capture Model).
• presentation says how it reads on screen: labels, formatting, record summaries (Presentation Layer).

Its kind says where its values come from: direct, whose entries are logged; derived, worked out from other trackables by the formula in derived_spec (Derived Trackables); or none, a tag, which holds trackables and no values. Its aggregation is its own Reading, how a day of its entries folds into one value. A trackable a module made points back at that module’s install and names its role in it, so the module can find it after a rename and offer it updates until a change makes it yours.

What a trackable owns beside its definition:

• Options. A choice’s options are rows of trackable_option, keyed by the integer code its entries store. Relabelling, recolouring or reordering one touches no entry, and a removed option is tombstoned rather than deleted, so the entries that hold its code keep their label (Stranded Option Data).
• Zones and achievements are rows of their own, zone and achievement. An achievement can name a zone, and a thing that is referenced by id gets a row and a foreign key, which the database protects, rather than a place inside a JSON column, which it cannot (Zones, Achievements).
• Its visualizations, rows of view that name it (below).

Entries

An entry is one row per thing logged; every log appends a row (Entry). It keeps two times, when it happened and when it was written down, which differ for a backfilled entry and are what makes Last mean the last recorded. Its value sits in one of three typed columns, value_number, value_text or value_json, chosen by the kind of value (Database Schema says which holds what). A Reading reads an entry back as an object built from its trackable’s definition, so a total, a chart or a formula reads a path into the object rather than a column (The List Model). Two values are worked out once, when an entry is written, and kept on it: an interval’s length, beside the start and end it comes from, and the amount of an item in the item’s basis unit (base), frozen so that redefining a unit later cannot change what a past entry meant (Units & Dimensions). A measured number is stored in its dimension’s base unit and converted only where it is typed and shown; the unit registry is code, not a table, since its factors are constants.

An entry also carries its own properties: whether it is a Nothing, saying there was none; a note; the routine entry it was part of (Events); and what logged it for you, if anything did (Logged for You).

Tags

Tagging is a membership edge between two trackables, the tag and what it holds. Edges are many-to-many and ordered per edge, and the repository refuses one that would close a cycle, so tags form a graph rather than a tree. There is no parent column: a trackable’s place in the hierarchy is a reading of its edges, and a trackable with no tag is Untagged. A flag on an edge marks the derived member that summarises its tag (Tags).

The catalog

A catalog is a collection of items (Food, Exercise) that owns the attributes every item fills and the basis unit they are measured per, and can name a bundled starter dataset by key. A catalog_entity is one item, its values entered; a recipe is a template, its ingredients logged. An item carries its own named units (an egg of 50 g), which values are guesses, the item it is based on, and a barcode (Catalog Entities, Diet and Nutrition). An entry points at an item by its id inside value_json, so an item’s values are read through the entry rather than copied onto it.

Plans and schedules

• A template is a named batch of entry lines bound to a trackable, never to an item, because its lines carry that trackable’s fields. A template_line holds the line’s values, an optional item, and for a Routine’s template the trackable the line logs and how deep in the outline it sits. Using a template writes ordinary entries (Templates, Events).
• A rundown_layer is one layer of The Rundown: a rule for which days it applies and what it asks.
• A schedule logs entries for you on the days its rule picks (Schedules).

Showing and remembering

• view holds visualizations: each a declarative spec, re-run against the entries whenever it is shown, never a snapshot. One that names a trackable is one of that trackable’s visualizations; one that names none is a visualization of several; pinned puts it on Home as a card of its own (Visualizations, Visualization Persistence).
• app_setting is a key-to-JSON store for preferences and per-profile state (the dashboard, unit preferences, a running routine’s plan). It lives in the profile’s file, so it travels with the profile through a backup or a sync, rather than in the WebView’s storage.
• issue_dismissal is the one stored part of Issues: an issue set aside, with a fingerprint of the entry as it stood, so editing the entry brings the issue back if it still applies.
• module_install records one run of a module: its preset and setup answers.

The tables whose names start with _sync_ are each device’s own sync bookkeeping, and are never synced themselves (Synced Databases).

Design

• Computed, never materialized. No derived value is stored. A derived trackable’s days, a tag’s summary, a food’s values lent through Based on, a Reading, a streak and an issue are all worked out when read, from the entries and the definitions, so a changed entry changes everything that reads it and there is no cache to invalidate. Readings run in memory over entry objects (Readings). What is stored is the definition: a formula, a spec, a rule, never its output. Storing a hot result is a performance lever kept in reserve, not something any read needs today: a profile is one person’s data, read from a file on the device.
• Specificity lives in data. No table, column or type is about a domain. Sleep, diet and strength are trackables, catalogs, templates, fills and rules that a module writes, read by code that knows kinds of value and never what they measure (Modules).
• A link that must outlive its target is a plain id. Sync takes a table’s parents from its foreign keys: it holds back a row whose parent has not arrived and drops one whose parent is gone. A routine’s part must outlive its routine’s entry, so entry.part_of is a plain id; the other plain ids are listed in Database Schema. A link to something that is not there reads as none. Every other reference is a foreign key, and most cascade with their parent.
• Hierarchy is edges. Tags are a graph of membership edges, and a routine’s parts are a tree of part_of links; neither is a parent column on the thing held.

Related

Database Schema · Trackable · Entry · Value Types · Catalog Entities · Tags · Readings · Repository Seam · Glossary