mattrguerra/meridian-ops-boardmainPrivate on GitHub, published here in full

own-list.ts

Language TypeScript14.8 KB390 lines

apps/worker/src/own-list.ts

/**
* The private list's storage: the decision, the SQL, and the states a read can
* be in.
*
* The decision is a pure function and the route is around it, the same shape as
* decideCheckin and for the same reason: `/own` sits behind Cloudflare Access
* and the test suite cannot mint an assertion for it, so every refusal has to
* be reachable without the route.
*
* Every statement here carries `person`, and it is never taken from a request
* (rule 15). There is no grant list guarding this table (see
* config/own-list.ts for why rule 13 does not apply to a store with one
* reader), so the scoping clause is the access control. A row id in a form post is a handle, not an
* authorisation: an id belonging to somebody else matches zero rows and comes
* back `notfound`, which is also exactly what a deleted row looks like, so the
* answer reveals nothing either way.
*
* The read takes one name, not a list. Deliberately not shaped like
* readCheckinsFor(env, people[]); there is no plural entry point, so a future
* "let someone see everyone's" has to add a function rather than pass a longer
* array, and that function would have to be reviewed.
*/
import { MERIDIAN_TZ } from '@meridian/ninety-client'
import {
OWN_DONE_SHOWN,
OWN_OPEN_CAP,
OWN_TEXT_CAP,
isOwnPriority,
type OwnPriority,
} from '../../../config/own-list.js'
import { WORK_KEY_RE } from '../../dashboard/src/work-key.js'
import type { OwnItem, OwnOutcome, OwnStore } from '../../dashboard/src/own-list-view.js'
import { errorMessage } from '@meridian/shared'
/**
* Just the binding, so the handlers can be called with a fake database.
*
* Optional, and that is a decision rather than an oversight, the same one
* CheckinEnv makes. A Worker deployed before the migration is applied must
* serve the whole board with this card saying the store is not set up, not
* throw, and above all not show an empty list that reads as "you have never
* written anything".
*
* It is the check-in binding. One D1 database holds both tables; the name is
* wider than its first table, and 0002_own_list.sql records why it has not
* been renamed.
*/
export interface OwnListEnv {
CHECKINS?: D1Database
}
/** Everything the route hands in, unparsed except for the person. */
export interface OwnInput {
/** From the verified identity, via ownListPersonFor. Never from the form. */
person: string
authorEmail: string
action: string
id: string
/** The `updated_at` the form was rendered with. '' for an add. */
seen: string
text: string
due: string
priority: string
link: string
}
export interface OwnFields {
text: string
due: string | null
priority: OwnPriority
linkKey: string | null
}
export type OwnDecision =
| { write: 'add'; fields: OwnFields }
| { write: 'edit'; id: number; seen: string; fields: OwnFields }
| { write: 'done' | 'reopen'; id: number; seen: string }
| { write: false; outcome: OwnOutcome }
interface ItemRow {
id: number
text: string
due: string | null
priority: string
link_key: string | null
created_at: string
updated_at: string
edits: number
done_at: string | null
}
const toItem = (r: ItemRow): OwnItem => ({
id: r.id,
text: r.text,
due: r.due,
// The column is CHECK-constrained to the same three values, so anything else
// could not have been written. Narrowed rather than asserted so a row written
// by hand against a loosened schema renders as normal instead of as nothing.
priority: isOwnPriority(r.priority) ? (r.priority as OwnPriority) : 'normal',
linkKey: r.link_key,
createdAt: r.created_at,
updatedAt: r.updated_at,
edits: r.edits,
doneAt: r.done_at,
})
/** A real calendar date, not merely a well-shaped string. */
function validDay(raw: string): boolean {
if (!/^\d{4}-\d{2}-\d{2}$/.test(raw)) return false
const d = new Date(`${raw}T00:00:00Z`)
// 2026-02-31 parses and rolls over to March; comparing back catches it.
return !Number.isNaN(d.getTime()) && d.toISOString().slice(0, 10) === raw
}
/**
* Normalize what somebody typed into the link box, without guessing.
*
* A bare order number gets the prefix it obviously wants, because typing
* `MD2639582` and being refused would be a papercut with no upside. Anything
* that still does not match the shape is refused rather than coerced: the
* difference between convenience and inference is that convenience cannot be
* wrong about what you meant.
*/
export function normalizeLink(raw: string): string | null | 'bad' {
const v = raw.trim()
if (!v) return null
const prefixed = /^(order|task):/i.test(v) ? v : /^\d+$/.test(v) ? `task:${v}` : `order:${v}`
const key = prefixed.replace(/^(ORDER|TASK|Order|Task):/, (m) => m.toLowerCase())
return WORK_KEY_RE.test(key) ? key : 'bad'
}
/**
* Decide what a submission means. No I/O at all.
*
* Every refusal has its own outcome and nothing is defaulted (rule 2). An
* unknown priority is refused rather than quietly becoming `normal`: the form
* always sends one, so an absent one means the post was not the form, and a
* default here would stand in for knowledge nobody has.
*
* A past due date is allowed. You can log something that is already late, and
* refusing it would lose the text for the sake of tidiness.
*/
export function decideOwn(input: OwnInput): OwnDecision {
const action = input.action.trim()
if (action !== 'add' && action !== 'edit' && action !== 'done' && action !== 'reopen') {
return { write: false, outcome: 'badaction' }
}
if (action === 'done' || action === 'reopen' || action === 'edit') {
const id = Number(input.id.trim())
if (!Number.isInteger(id) || id <= 0) return { write: false, outcome: 'badid' }
const seen = input.seen.trim()
if (!seen) return { write: false, outcome: 'badid' }
if (action !== 'edit') return { write: action, id, seen }
const fields = fieldsFrom(input)
return 'outcome' in fields ? { write: false, outcome: fields.outcome } : { write: 'edit', id, seen, fields }
}
const fields = fieldsFrom(input)
return 'outcome' in fields ? { write: false, outcome: fields.outcome } : { write: 'add', fields }
}
function fieldsFrom(input: OwnInput): OwnFields | { outcome: OwnOutcome } {
const text = input.text.trim()
if (!text) return { outcome: 'empty' }
// Refused, not truncated. Silently storing the first 500 characters of what
// somebody typed is the failure mode rule 5 names from the other direction:
// the page would show a sentence that stops mid-word and nothing would say
// why. Refusing costs a retry with the text still in the box.
if (text.length > OWN_TEXT_CAP) return { outcome: 'toolong' }
const dueRaw = input.due.trim()
if (dueRaw && !validDay(dueRaw)) return { outcome: 'baddate' }
const priority = input.priority.trim()
if (!isOwnPriority(priority)) return { outcome: 'badpriority' }
const link = normalizeLink(input.link)
if (link === 'bad') return { outcome: 'badlink' }
return { text, due: dueRaw || null, priority, linkKey: link }
}
// --- reading ------------------------------------------------------------------------
const OPEN_SQL = `SELECT id, text, due, priority, link_key, created_at, updated_at, edits, done_at
FROM own_item
WHERE person = ?1 AND done_at IS NULL
ORDER BY (due IS NULL) ASC, due ASC, id ASC
LIMIT ?2`
const DONE_SQL = `SELECT id, text, due, priority, link_key, created_at, updated_at, edits, done_at
FROM own_item
WHERE person = ?1 AND done_at IS NOT NULL
ORDER BY done_at DESC
LIMIT ?2`
const DONE_COUNT_SQL = `SELECT count(*) AS n FROM own_item WHERE person = ?1 AND done_at IS NOT NULL`
/**
* One person's list.
*
* `(due IS NULL) ASC` because SQLite sorts NULLs first and "no date" has to
* sort last. The card sorts again with sortOpen() (the same order, expressed
* once in each language) because the grouping and the ordering must come from
* one definition, and the card is the thing a reader is actually looking at.
*
* Both LIMITs are the cap plus one, the same trick as the check-in read: `truncated` can then
* only fire if something bypassed the add cap, and a short list says it is
* short rather than passing itself off as the whole thing (rule 5).
*/
export async function readOwnList(
env: OwnListEnv,
person: string,
): Promise<OwnStore> {
if (!env.CHECKINS) return { state: 'unconfigured' }
try {
const [open, done, doneCount] = await env.CHECKINS.batch([
env.CHECKINS.prepare(OPEN_SQL).bind(person, OWN_OPEN_CAP + 1),
env.CHECKINS.prepare(DONE_SQL).bind(person, OWN_DONE_SHOWN),
env.CHECKINS.prepare(DONE_COUNT_SQL).bind(person),
])
const openRows = (open?.results ?? []) as unknown as ItemRow[]
const doneRows = (done?.results ?? []) as unknown as ItemRow[]
const total = Number(((doneCount?.results ?? []) as { n?: number }[])[0]?.n ?? doneRows.length)
return {
state: 'ok',
person,
open: openRows.slice(0, OWN_OPEN_CAP).map(toItem),
done: doneRows.map(toItem),
truncated: openRows.length > OWN_OPEN_CAP,
// Counted rather than inferred from the page size: "50 shown of 63" is a
// fact the card states, and inferring it from a full page would say
// "more" when there were exactly fifty.
doneHidden: Math.max(0, total - doneRows.length),
}
} catch (err) {
// The store's own words and no diagnosis (rule 1): "no such table:
// own_item" is a fact; "you forgot the migration" is a guess the reader
// can make themselves.
return { state: 'failed', error: errorMessage(err ?? 'unknown error') }
}
}
// --- writing ------------------------------------------------------------------------
export interface OwnResult {
outcome: OwnOutcome
}
/**
* Apply one submission.
*
* The stale token. Every action on an existing line carries the `updated_at`
* the form was rendered with, and the statement includes `AND updated_at = ?`.
* This is decideCheckin's claimedDay moved from a day to a row: a tab left open
* while the line changed somewhere else acts on a version the person is no
* longer looking at. Refusing costs a reload; guessing costs the record.
*
* When nothing matched, one follow-up SELECT tells the two cases apart (no row
* at all is `notfound`, a row with a different token is `stale`), and that
* second query only ever runs on the failure path.
*/
export async function saveOwn(
env: OwnListEnv,
input: OwnInput,
now: Date = new Date(),
): Promise<OwnResult> {
const decision = decideOwn(input)
if (!decision.write) return { outcome: decision.outcome }
// An unconfigured store collapses into `failed` on a write, unlike on a read:
// what somebody mid-task needs told is "it did not save", not which of the
// two reasons it did not.
if (!env.CHECKINS) return { outcome: 'failed' }
const db = env.CHECKINS
const at = now.toISOString()
try {
if (decision.write === 'add') {
const { n } = (await db
.prepare(`SELECT count(*) AS n FROM own_item WHERE person = ?1 AND done_at IS NULL`)
.bind(input.person)
.first<{ n: number }>()) ?? { n: 0 }
// Refuses over the cap instead of trimming (rule 5, and RECIPIENT_CAP's
// rule in rule 12). Trimming would throw away the thing just typed, which
// is the single outcome this feature exists to prevent.
if (n >= OWN_OPEN_CAP) return { outcome: 'full' }
const f = decision.fields
await db
.prepare(
`INSERT INTO own_item
(person, author_email, text, due, priority, link_key,
created_at, updated_at, edits, done_at, tz)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?7, 0, NULL, ?8)`,
)
.bind(input.person, input.authorEmail, f.text, f.due, f.priority, f.linkKey, at, MERIDIAN_TZ)
.run()
return { outcome: 'added' }
}
if (decision.write === 'edit') {
const f = decision.fields
const current = await db
.prepare(
`SELECT text, due, priority, link_key FROM own_item WHERE id = ?1 AND person = ?2`,
)
.bind(decision.id, input.person)
.first<{ text: string; due: string | null; priority: string; link_key: string | null }>()
// An unchanged edit writes nothing, so `edits` cannot tick for a no-op
// and the row cannot come back saying "edited" when it was not, the same
// call saveCheckin makes by comparing before it writes.
if (
current &&
current.text === f.text &&
current.due === f.due &&
current.priority === f.priority &&
current.link_key === f.linkKey
) {
return { outcome: 'unchanged' }
}
const res = await db
.prepare(
`UPDATE own_item
SET text = ?3, due = ?4, priority = ?5, link_key = ?6,
updated_at = ?7, edits = edits + 1
WHERE id = ?1 AND person = ?2 AND updated_at = ?8`,
)
.bind(decision.id, input.person, f.text, f.due, f.priority, f.linkKey, at, decision.seen)
.run()
return { outcome: changed(res) ? 'edited' : await whyNot(db, decision.id, input.person, decision.seen) }
}
const done = decision.write === 'done'
const res = await db
.prepare(
done
? `UPDATE own_item SET done_at = ?3, updated_at = ?3
WHERE id = ?1 AND person = ?2 AND done_at IS NULL AND updated_at = ?4`
: `UPDATE own_item SET done_at = NULL, updated_at = ?3
WHERE id = ?1 AND person = ?2 AND done_at IS NOT NULL AND updated_at = ?4`,
)
.bind(decision.id, input.person, at, decision.seen)
.run()
return {
outcome: changed(res)
? done
? 'completed'
: 'reopened'
: await whyNot(db, decision.id, input.person, decision.seen),
}
} catch {
// The page must say it was not saved, loudly. The store's own message is
// deliberately not shown for a write: it lands on somebody's screen
// mid-task, where "D1_ERROR: near ..." is noise. What they need is "it did
// not save, keep your text".
return { outcome: 'failed' }
}
}
/** D1 reports rows touched in `meta.changes`; a stand-in may not. */
function changed(res: unknown): boolean {
const n = (res as { meta?: { changes?: number } } | null)?.meta?.changes
return typeof n === 'number' ? n > 0 : false
}
/**
* Why an update matched nothing: the row is gone, or it moved on.
*
* `notfound` is also what somebody else's row looks like, and the wording is
* correct for both without saying which: `person` is in the WHERE of both this
* query and the update it is explaining, so a guessed id belonging to a
* colleague is indistinguishable from one that was never there.
*/
async function whyNot(
db: D1Database,
id: number,
person: string,
seen: string,
): Promise<OwnOutcome> {
const row = await db
.prepare(`SELECT updated_at FROM own_item WHERE id = ?1 AND person = ?2`)
.bind(id, person)
.first<{ updated_at: string }>()
if (!row) return 'notfound'
return row.updated_at === seen ? 'notfound' : 'stale'
}