6 · Data and time

Principles

  • State tables are boring. One table per aggregate root plus its children, foreign keys on, no JSON where a column would do, JSON only for genuinely polymorphic payloads (change payloads, reserved-window restrictions).
  • Ids are opaque, URL-safe, generated by the kernel (Ids port), never sequential where they are exposed. Slugs are for events only.
  • Versions on every editable row (version INTEGER), bumped by the unit of work; edits carry expectedVersion.
  • No soft delete except where the domain needs a tombstone (a deleted session still appears in “who moved/deleted this” via the change log, which is enough).
  • Row shapes stay in db/. The adapter maps rows to domain objects; nothing above the adapter imports a row type. This is the boundary the current code is still moving towards (#965), made structural here by the package split.

Tables by module

Module Tables
site site_settings
events events, days, event_settings
venue places, venue_maps
people persons, participations, profile_prompts, profile_answers, profile_comments, labels, participation_labels
identity credentials, passkeys, email_codes, join_tokens, login_sessions, api_tokens
proposals proposals, proposal_hosts, votes, proposal_comments
scheduling sessions, session_hosts, rsvps, booking_rules, reserved_windows, attendance_counts
meetings meetings, availability
personal marks, preferences
notifications notifications, channel_bindings, notification_preferences, delivery_attempts
kernel changes, consumer_cursors, scheduled_jobs, leases, idempotency_keys, files

Every event-scoped table carries event_id so a per-event export or deletion is a filtered copy.

The change log table

CREATE TABLE changes (
  seq            INTEGER PRIMARY KEY AUTOINCREMENT,
  id             TEXT NOT NULL UNIQUE,          -- client-recognisable id
  event_id       TEXT,                          -- NULL for site-level changes
  type           TEXT NOT NULL,                 -- 'scheduling.SessionMoved'
  subject_type   TEXT NOT NULL, subject_id TEXT NOT NULL,
  actor_type     TEXT NOT NULL, actor_id TEXT,  -- person | token | system | deleted
  occurred_at    TEXT NOT NULL,                 -- ISO UTC
  correlation_id TEXT,
  command_key    TEXT,                          -- Idempotency-Key of the causing command
  audience       TEXT NOT NULL,                 -- JSON, computed at write time
  payload        TEXT                           -- JSON, NULL once pruned
);
CREATE INDEX changes_event_seq ON changes(event_id, seq);
CREATE INDEX changes_subject   ON changes(subject_type, subject_id, seq);

Feed resume is WHERE event_id = ? AND seq > ? plus the audience filter in application code (audiences are small JSON; SQLite’s json_each can index the persons case if it ever matters).

SQLite settings

journal_mode=WAL, synchronous=NORMAL, foreign_keys=ON, busy_timeout=5000, one connection for writes, a small pool for reads. Backups are VACUUM INTO or a file copy of the database plus the uploads directory, as today.

Migrations

Drizzle-generated SQL migrations, applied on server start before anything else, forward only. The release-upgrade test tier (migrate a released database forward, exercise CRUD) is kept. A migration that changes a change payload shape bumps the change type version suffix (SessionMoved.v2) rather than rewriting old rows; apply handles both until the retention window has pruned the old ones.

Time

  • Storage and wire: ISO-8601 UTC instants. Durations as ISO-8601 or integer minutes, never floats.
  • Every event has an IANA zone; every Day is a local date in it. “Today” is computed in the event zone through the Clock port.
  • The slot grid is data on the Day (slotGrid and bookableWindow, as in 02); the grid rows and the sessions’ real times are drawn from the same numbers, so labels and blocks cannot disagree.
  • Formatting happens in exactly two places: domain/time (pure, given zone, locale, preference) and the email templates, which call it. The lint rules ban new Date() and locale-dependent formatting outside those files.
  • The fake clock is an offset on the actor context, honoured by Clock, usable in production only with the dev-tools flag, as today.
Next Overview