0001_checkin.sql
Language SQL6.2 KB120 lines
-- The end-of-day note. Three questions, one row per person per day.---- D1 rather than KV, which the rest of the board's state uses (rule 14).---- Everything else the board owns is one JSON blob per concern, read whole and-- written whole: inbox:filters, inbox:read, board:notes, inbox:signatures,-- inbox:replied. That works because each of them is small and none of them is a-- series. This is a series: a row a day, for as long as somebody is new, read-- back as "the last three weeks, including the days she did not write one".-- Doing that in a single KV value means read-modify-write on the whole history-- for every submission, and KV is eventually consistent by up to about a-- minute, so two writes a minute apart can lose one. Losing a note is losing the only copy of something a person-- typed once.---- So: a table, an upsert, and a query with a date range. That is what SQL is.---- Apply it (the database does not exist until somebody creates it):-- cd apps/worker-- npx wrangler d1 create meridian-checkins # prints database_id-- # paste that id into wrangler.jsonc, then:-- npx wrangler d1 migrations apply meridian-checkins --local # a dry run-- npx wrangler d1 migrations apply meridian-checkins --remote # production---- Until the binding exists the board says the note store is not configured, in-- those words, on the Onboarding tab. It does not say "no notes"; see the-- prime invariant in docs/ARCHITECTURE.md. An unconfigured store and an empty-- one are not the same fact and must not render the same way.-- Strict, so a TEXT column cannot quietly hold an integer and a typo in a-- binding cannot write a shape nothing reads back. D1's SQLite supports it.CREATE TABLE IF NOT EXISTS checkin (id INTEGER PRIMARY KEY,-- The roster display name, and the column everything is filed under.---- Not the email, because a seat can change hands. Most addresses here are-- role mailboxes: when accounts@ changes hands, the next holder signs in as-- the same string. Keyed by-- address, that person would inherit the previous holder's history: weeks-- of somebody else's "what I'm unsure about" under their name. Keyed by roster-- name, a seat change starts an empty history and leaves the old one intact-- and correctly attributed. config/checkin.ts is where an address is mapped to-- a name, by hand.person TEXT NOT NULL,-- What actually authenticated, recorded beside it rather than instead of it.-- If these two ever disagree with config/checkin.ts, this column is the-- evidence of which mailbox was signed in as.author_email TEXT NOT NULL,-- YYYY-MM-DD in `day_tz`. The day the note is about, which is not always the-- day it was typed: a note written at 00:30 files against the day that just-- ended (CHECKIN_GRACE_UNTIL_HOUR). The GLOB is here because a malformed day-- would sort into the wrong place in every range query and never throw.day TEXT NOT NULL CHECK (day GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),-- The zone that `day` was computed in, so a row stays legible if the board's-- clock ever moves. Everything is America/Chicago (MERIDIAN_TZ).day_tz TEXT NOT NULL,-- The three answers, in the order they are asked. Column names match-- CHECKIN_QUESTIONS[].id and the form field names; changing one is a-- migration, not an edit.did TEXT NOT NULL,unsure TEXT NOT NULL,need TEXT NOT NULL,submitted_at TEXT NOT NULL, -- ISO 8601 UTC, the first submissionupdated_at TEXT NOT NULL, -- ISO 8601 UTC, the version stored hererevisions INTEGER NOT NULL DEFAULT 1 CHECK (revisions >= 1),-- A row must say something. All three blank is not an answer; it is-- indistinguishable from not having written one, and a day that reads blank-- when she never opened the form is the prime invariant broken in the place-- it matters most. The Worker refuses an all-blank submission too; this is-- the same rule at the storage layer, where nothing can route around it.-- One or two blanks are fine and render as "left blank", explicitly.---- It sits down here because it is a table constraint and SQLite rejects the-- whole CREATE if one appears between two columns. The test that applies-- this file to a real engine is what catches that.CHECK (length(trim(did)) + length(trim(unsure)) + length(trim(need)) > 0),-- One note per person per day. The upsert depends on this constraint; without-- it a second submission appends a second row and the log shows one day-- twice, with no way to tell which is current.UNIQUE (person, day)) STRICT;-- The readers' log is "this person, newest day first, back N days".CREATE INDEX IF NOT EXISTS checkin_person_day ON checkin (person, day DESC);-- Every version ever submitted, including the first. Append-only.---- Edits are kept because the note can be rewritten, and the person reading it-- is usually reading it the same evening. Without this table an edit silently-- replaces text a reader has already read, and neither side can tell that-- happened. With it, the log can say "edited, 2 versions" and show the earlier-- one. It is also the only thing here that cannot be undone from the page,-- which is exactly the property inbox:sent:* has and for the same reason.---- The author is told this on the form, in plain words. A kept history nobody-- mentions is a different feature from a kept history everybody knows about.CREATE TABLE IF NOT EXISTS checkin_revision (-- Autoincrement, unlike the table above: an append-only log wants ids that are-- never reused, so "the row after id 40" means the same thing forever.id INTEGER PRIMARY KEY AUTOINCREMENT,person TEXT NOT NULL,day TEXT NOT NULL CHECK (day GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),revision INTEGER NOT NULL CHECK (revision >= 1),did TEXT NOT NULL,unsure TEXT NOT NULL,need TEXT NOT NULL,author_email TEXT NOT NULL,written_at TEXT NOT NULL, -- ISO 8601 UTCUNIQUE (person, day, revision)) STRICT;CREATE INDEX IF NOT EXISTS checkin_revision_person_dayON checkin_revision (person, day DESC, revision DESC);