0002_own_list.sql
Language SQL8.6 KB158 lines
-- One person's own private list: the lines they log for themselves so they do-- not lose them. The only table on this board with exactly one reader (rule 15).---- It sits in the My Day card beside the work the five sources already assign-- them, and nobody else can see it. There is no reader list and no leadership-- view.---- D1 and not KV: 0001's argument, and a sharper one.---- 0001 chose a table because the end-of-day note is a series: a row a day, read-- back as a range, where read-modify-write over a whole KV blob can lose a-- submission to eventual consistency (up to about a minute). All of that is-- true here and worse: everyone on the roster writes to this one, not one-- person, so two of them adding a line in the same minute would race each-- other through a shared blob, and the thing lost is the only copy of something-- somebody typed once.---- The same database as the end-of-day note: `meridian-checkins`, binding-- CHECKINS, migrations_dir `migrations`. One database means one-- `wrangler d1 migrations apply`, one optional-binding story, and one "not set-- up" sentence to get right rather than two. The binding name is wider than-- its first table, and that is a recorded trade rather than an oversight: it-- is deployed config, and renaming it would touch every failure sentence in-- checkin.ts. A misnomer is findable in one grep; a half-applied rename is-- not. If a third D1 feature lands, rename it then, on its own.---- No revision table, unlike 0001, deliberately. `checkin_revision` exists-- because a second person reads the note, and an edit-- would silently replace text they had already read. Nobody but the author ever-- reads this table. Keeping superseded versions here would mean storing private-- text its owner believed they had changed, forever, on a surface whose entire-- premise is that it is safe to write in. `edits` records that a line changed,-- which is all anybody here needs. Do not add one for consistency.---- Apply it (the database already exists; this is a second migration on it):-- cd apps/worker-- npx wrangler d1 migrations apply meridian-checkins --local # a dry run-- npx wrangler d1 migrations apply meridian-checkins --remote # production---- Apply it before deploying the Worker. The other order gives every person a-- card reading "your list could not be read: no such table: own_item", which-- is honest, alarming and entirely avoidable. Until the migration lands the-- board says the store could not be read, in SQLite's own words and with no-- diagnosis (rule 1); with no binding at all it says not set up. Those are-- different facts and they have different sentences.CREATE TABLE IF NOT EXISTS own_item (-- Autoincrement: ids are never reused. The complete, reopen and edit forms-- each carry an id, and a tab left open while a line is completed would-- otherwise be able to act on whatever row inherited the number.id INTEGER PRIMARY KEY AUTOINCREMENT,-- The roster display name, and 0001's argument at its strongest.---- 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 open the board and read the previous holder's private list: not a mis-attributed count, an actual disclosure of-- somebody's own words to somebody who was never meant to have them. Keyed by-- roster name, a seat change starts an empty list and leaves the old one-- intact, correctly attributed, and unreachable.---- Consequence, written down rather than discovered later: renaming a person-- in config/meridian-people.ts orphans their rows. The rows stay and stay-- correct; they stop being reachable.person TEXT NOT NULL,-- What actually authenticated, recorded beside the name rather than instead-- of it. If these two ever disagree with the roster, this is the evidence of-- which mailbox was signed in as.author_email TEXT NOT NULL,-- The line itself. Capped below; the route caps it too (OWN_TEXT_CAP).text TEXT NOT NULL,-- YYYY-MM-DD in `tz`, or NULL for "no date". NULL and a past date are-- different facts and the card renders them differently: a line with no date-- is not overdue and never becomes overdue. The GLOB is here because a-- malformed date would sort into the wrong place in every comparison and-- never throw.---- A date, not a timestamp. A time implies something happens at that time, and-- nothing here notifies anybody; the card says so in as many words.due TEXT CHECK (due IS NULL OR due GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),-- Three values, fixed, matching OWN_PRIORITIES in config/own-list.ts. TEXT-- plus a CHECK under STRICT, so a bad value is a refused write rather than a-- row that sorts oddly and renders blank. Not an integer: a number invites an-- average priority or a priority total, and there is no such quantity.---- The route refuses an absent or unknown value rather than defaulting to one-- (rule 2). The form always sends one, so a missing one is a malformed post.priority TEXT NOT NULL CHECK (priority IN ('high', 'normal', 'low')),-- The optional reference to real work, as the same key the rest of the board-- already uses: 'order:MD2639582' or 'task:41837'. board:notes is keyed on-- this exact string and work rows already carry it in data-note-key, so a-- line can be shown against that order later with no translation.---- One column, not two. A kind and an id in separate columns can be half-- filled; this cannot. NULL means no link. '' is refused, because an empty-- link and no link must not become two spellings of the same thing.---- The CHECK is coarser than the route's regex, on purpose. GLOB cannot-- express the character class; WORK_KEY_RE in apps/dashboard/src/work-key.ts-- does, and it is the same regex /note validates with. This CHECK exists so-- that a shape nothing can render cannot be written at all.link_key TEXT CHECK (link_key IS NULL OR (length(link_key) BETWEEN 6 AND 46 AND(link_key GLOB 'order:[A-Za-z0-9_-]*' OR link_key GLOB 'task:[A-Za-z0-9_-]*'))),created_at TEXT NOT NULL, -- ISO 8601 UTC, when it was logged-- ISO 8601 UTC, the version stored here. Also the concurrency token: every-- mutation carries the value the form was rendered with and is refused if it-- no longer matches. Same idea as the check-in's claimedDay, moved from a day-- to a row: refusing costs a reload, guessing costs the record.updated_at TEXT NOT NULL,edits INTEGER NOT NULL DEFAULT 0 CHECK (edits >= 0),-- NULL means open. A timestamp means done, and the line stays: completing-- something must never look like losing it, and reopening is one click. This-- is also what makes the whole feature reversible from the page that wrote it-- (rule 12) without a confirmation step anywhere.done_at TEXT,-- The zone `due` is judged against, so a row stays legible if the board's-- clock ever moves. Everything is America/Chicago (MERIDIAN_TZ).tz TEXT NOT NULL,-- A line must say something. A blank row renders as an empty bullet its owner-- cannot identify and cannot act on, and it would be counted in the "open"-- number above the list, rule 9 broken by a row that is technically present.-- The route refuses one too; this is the same rule at the storage layer,-- where nothing can route around it.---- Both checks sit down here because they are table constraints. SQLite-- rejects the whole CREATE if one appears between two columns, which only-- executing the file reveals; that is why a test executes it.CHECK (length(trim(text)) > 0),CHECK (length(text) <= 500)) STRICT;-- No unique constraint, and that is a decision rather than an omission. 0001-- has one because its upsert depends on it; there is no upsert here. A line is-- not identified by its text: "call the client back" logged twice in a week is-- two reminders, and collapsing them would silently delete the second.-- Identity is the id and nothing else.-- The open list, in the order the card reads it: this person, dated first.CREATE INDEX IF NOT EXISTS own_item_openON own_item (person, due, id) WHERE done_at IS NULL;-- The done list, newest completion first. A separate partial index because-- these are two different questions asked by two different statements.CREATE INDEX IF NOT EXISTS own_item_doneON own_item (person, done_at DESC) WHERE done_at IS NOT NULL;