# SP Calendar — architecture

Self-hosted appointment booking for one owner: a public booking site and a private admin area, on the MacLustr cluster behind the MacLustr Tunnel (https://calendar.spboucher.ai).

# Audit of the environment (2026-11-11)

What was actually inspected before writing code:

# Components

Browser ──HTTPS──▶ Caddy (BHS64) ──WireGuard──▶ node: next start :8660  (sp-calendar-web, PM2)
                                                        │
                                                        ├── PostgreSQL 17 (db sp_calendar)
                                                        │
                                               tsx src/worker (sp-calendar-worker, PM2)
                                                        ├── e-mail outbox → Resend / SMTP
                                                        ├── expiry of unanswered requests and proposals
                                                        ├── ICS subscriptions (external free/busy)
                                                        └── retention purge
                                               scripts/backup.sh (sp-calendar-backup, PM2 cron)

• Web: Next.js 16 (App Router), React 19, TypeScript strict, Tailwind 4. Pages are server-rendered shells with client components that talk to the API; no server actions.
• API /api/v1: three catch-all surfaces, each with one guard that runs before any handler is looked up (src/lib/api/router.ts): public/* (rate-limited, no session), auth/* (WebAuthn), admin/* (session + CSRF). Every body and query is validated with Zod (src/lib/api/schemas.ts).
• Worker: one loop, every task idempotent; worker_heartbeat is read by /api/health and the dashboard.
• No Redis: the job queue is the notification_jobs table (FOR UPDATE SKIP LOCKED), written in the same transaction as the change it announces (transactional outbox). See DECISIONS D-03.

# Availability engine (src/lib/engine/slots.ts)

Pure functions, no I/O, tested alone (tests/engine.test.ts). Inputs are modelled separately:

Algorithm: weekly rules and openings → instants per local date → union → minus closures → slots cut by elapsed time (a slot is always exactly duration real minutes; the hour skipped in spring offers nothing, the hour repeated in autumn is real time) → filtered by notice, horizon, limits (owner-local day and ISO week) and collisions. Buffers do not stack: the gap between two appointments is the larger of the two buffers that face each other.

Instants are stored as timestamptz (UTC); zones as IANA identifiers; wall-clock values are never stored as instants. The browser only formats (Intl) — it never computes availability.

# Double-booking protection (three layers)

1. Owner advisory lock: every path that creates, confirms or moves an appointment runs in withOwnerLock() (pg_advisory_xact_lock), so check-then-write is serialised per owner.
2. Re-check inside the lock with checkSlot() against the state read in that same transaction (approval, automatic confirmation, acceptance of a proposal, rescheduling).
3. Exclusion constraint bookings_no_overlap (EXCLUDE USING gist (user_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE status = 'confirmed'): the database refuses an overlap whatever the code does; error 23P01 is mapped to HTTP 409 slot_taken.

Submissions carry an idempotency key (unique in the database): a replay returns the first outcome.

# Request and booking states

pending ──approve──▶ approved ──cancel──▶ cancelled
   │  ├─decline──▶ declined                 ▲
   │  ├─(time passes)──▶ expired            │
   │  ├─visitor withdraws──▶ cancelled      │
   │  └─owner proposes──▶ reschedule_requested ──visitor accepts (re-checked)──▶ approved
   │                              ├─visitor picks another time──▶ pending
   │                              ├─visitor declines──▶ cancelled
   │                              └─72 h or proposed time passes──▶ expired
booking: confirmed ──▶ cancelled        (a confirmed booking moves in place: same ICS UID, SEQUENCE + 1)

Pending policy per appointment type: hold (the request holds its slot for hold_ttl_hours, then stops blocking while staying pending) or none (never blocks; several requests may wait for the same time, one can be approved). On a conflict at approval nothing is confirmed: the owner proposes another time or declines.

A visitor's request to move a confirmed booking is a linked request (reschedule_of_booking_id): the booking stays where it is until the owner approves, then moves.

# Notifications

enqueue() writes a job with a dedup_key; the worker renders the message from the current database state (a superseded confirmation is dropped, a reminder for a moved booking is obsolete), sends it and records the provider's message id. Failure → retry after 1, 5, 30, 120, 360 min → dead. No transport configured → unconfigured (parked, visible, re-queueable); nothing is ever marked sent without the transport's acceptance. Invitations: RFC 5545, stable UID, growing SEQUENCE, METHOD:REQUEST / CANCEL, UTC times, 75-octet folding.

# External calendars

Implemented connector: ICS subscription, read-only free/busy, refreshed every ~10 minutes with ETag, recurrences expanded by node-ical, replaced atomically per calendar, SSRF-guarded (https only, public addresses only, 8 MB cap), address stored AES-256-GCM encrypted. Freshness is tracked in sync_state; a stale or failing calendar keeps its last known busy periods and, with the default conservative policy, turns automatic confirmations into requests to approve (conflict_flag = external_stale). Outgoing: a private ICS feed of confirmed bookings (token shown once, stored hashed). Google Calendar / Microsoft 365 OAuth and CalDAV are not implemented (no credentials available); the tables (external_calendar_accounts.provider, secret_enc, write_events) already allow them.

# Data model

db/migrations/001_init.sql (commented): users, authenticators, setup_tokens, recovery_codes, webauthn_challenges, sessions, settings, event_types, availability_rules, availability_exceptions, external_calendar_accounts, external_calendars, sync_state, busy_blocks, contacts, bookings, booking_requests, booking_answers, booking_tokens, notification_jobs, audit_events, rate_limits, worker_heartbeat.

# Source map