Repository Public
1# SP Calendar — architecture23Self-hosted appointment booking for one owner: a public booking site and a private admin area, on the MacLustr4cluster behind the MacLustr Tunnel (`https://calendar.spboucher.ai`).56## Audit of the environment (2026-11-11)78What was actually inspected before writing code:910| Item | Finding |11|---|---|12| DNS | `calendar.spboucher.ai A 51.161.112.61` (BHS64, tunnel gateway) already in place; no route yet (TLS error until `mld deploy`). |13| Deployment | `mld` on M1M32: manifests in `~/dispatch/apps/<app>.json`, PM2 processes, Caddy route on BHS64, health check. Port 8660 free in every manifest. |14| Sibling apps read | `clip-spboucher-ai` (Next 16, `postgres`, SQL migrations, tsx worker, PM2 manifest), `sp-wallet` (WebAuthn with `@simplewebauthn/server` 14.1.0, design system `docs/v9/DESIGN.md`), `sp-drive` (same WebAuthn library), `chat-spboucher-ai` (memory notes only). |15| Runtimes | Nodes: Node 26, pnpm 12, PostgreSQL 17, PM2. `Temporal` is native in Node 26 (used server-side only). |16| E-mail | Other apps use Resend; a verified sending domain `mail.spboucher.ai` appears in a retired app's manifest. No SMTP server on the cluster. |17| Not available | Google / Microsoft OAuth client credentials; no shared auth service between spboucher.ai apps (each app has its own). |1819## Components2021```22Browser ──HTTPS──▶ Caddy (BHS64) ──WireGuard──▶ node: next start :8660 (sp-calendar-web, PM2)23 │24 ├── PostgreSQL 17 (db sp_calendar)25 │26 tsx src/worker (sp-calendar-worker, PM2)27 ├── e-mail outbox → Resend / SMTP28 ├── expiry of unanswered requests and proposals29 ├── ICS subscriptions (external free/busy)30 └── retention purge31 scripts/backup.sh (sp-calendar-backup, PM2 cron)32```3334- **Web**: Next.js 16 (App Router), React 19, TypeScript strict, Tailwind 4. Pages are server-rendered shells with35 client components that talk to the API; no server actions.36- **API** `/api/v1`: three catch-all surfaces, each with one guard that runs before any handler is looked up37 (`src/lib/api/router.ts`): `public/*` (rate-limited, no session), `auth/*` (WebAuthn), `admin/*` (session + CSRF).38 Every body and query is validated with Zod (`src/lib/api/schemas.ts`).39- **Worker**: one loop, every task idempotent; `worker_heartbeat` is read by `/api/health` and the dashboard.40- **No Redis**: the job queue is the `notification_jobs` table (`FOR UPDATE SKIP LOCKED`), written in the same41 transaction as the change it announces (transactional outbox). See DECISIONS D-03.4243## Availability engine (`src/lib/engine/slots.ts`)4445Pure functions, no I/O, tested alone (`tests/engine.test.ts`). Inputs are modelled separately:4647| Input | Source | Nature |48|---|---|---|49| weekly opening hours | `availability_rules` (default schedule, or one per type) | wall-clock, owner's zone |50| exceptions: closures and extra openings | `availability_exceptions` (range, all-day, weekly recurring) | wall-clock |51| private events | `busy_blocks` (`source = manual`) | instants |52| external calendars | `busy_blocks` (`source = external`) of calendars set to block | instants |53| requests holding a slot | `booking_requests` pending with unexpired hold, or open proposal | instants + buffers |54| confirmed appointments | `bookings` (`status = confirmed`) | instants + buffers |55| type rules | duration, step, buffers, notice, horizon, daily / weekly limits | — |5657Algorithm: weekly rules and openings → instants per local date → union → minus closures → slots cut by **elapsed**58time (a slot is always exactly `duration` real minutes; the hour skipped in spring offers nothing, the hour repeated59in autumn is real time) → filtered by notice, horizon, limits (owner-local day and ISO week) and collisions.60Buffers do not stack: the gap between two appointments is the larger of the two buffers that face each other.6162Instants are stored as `timestamptz` (UTC); zones as IANA identifiers; wall-clock values are never stored as63instants. The browser only formats (`Intl`) — it never computes availability.6465## Double-booking protection (three layers)66671. **Owner advisory lock**: every path that creates, confirms or moves an appointment runs in68 `withOwnerLock()` (`pg_advisory_xact_lock`), so check-then-write is serialised per owner.692. **Re-check inside the lock** with `checkSlot()` against the state read in that same transaction (approval,70 automatic confirmation, acceptance of a proposal, rescheduling).713. **Exclusion constraint** `bookings_no_overlap` (`EXCLUDE USING gist (user_id WITH =, tstzrange(starts_at,72 ends_at) WITH &&) WHERE status = 'confirmed'`): the database refuses an overlap whatever the code does;73 error 23P01 is mapped to HTTP 409 `slot_taken`.7475Submissions carry an idempotency key (unique in the database): a replay returns the first outcome.7677## Request and booking states7879```80pending ──approve──▶ approved ──cancel──▶ cancelled81 │ ├─decline──▶ declined ▲82 │ ├─(time passes)──▶ expired │83 │ ├─visitor withdraws──▶ cancelled │84 │ └─owner proposes──▶ reschedule_requested ──visitor accepts (re-checked)──▶ approved85 │ ├─visitor picks another time──▶ pending86 │ ├─visitor declines──▶ cancelled87 │ └─72 h or proposed time passes──▶ expired88booking: confirmed ──▶ cancelled (a confirmed booking moves in place: same ICS UID, SEQUENCE + 1)89```9091Pending policy per appointment type: `hold` (the request holds its slot for `hold_ttl_hours`, then stops blocking92while staying pending) or `none` (never blocks; several requests may wait for the same time, one can be approved).93On a conflict at approval nothing is confirmed: the owner proposes another time or declines.9495A visitor's request to move a **confirmed** booking is a linked request (`reschedule_of_booking_id`): the booking96stays where it is until the owner approves, then moves.9798## Notifications99100`enqueue()` writes a job with a `dedup_key`; the worker renders the message from the **current** database state101(a superseded confirmation is dropped, a reminder for a moved booking is obsolete), sends it and records the102provider's message id. Failure → retry after 1, 5, 30, 120, 360 min → `dead`. No transport configured →103`unconfigured` (parked, visible, re-queueable); nothing is ever marked sent without the transport's acceptance.104Invitations: RFC 5545, stable UID, growing SEQUENCE, `METHOD:REQUEST` / `CANCEL`, UTC times, 75-octet folding.105106## External calendars107108Implemented connector: **ICS subscription**, read-only free/busy, refreshed every ~10 minutes with `ETag`,109recurrences expanded by `node-ical`, replaced atomically per calendar, SSRF-guarded (https only, public addresses110only, 8 MB cap), address stored AES-256-GCM encrypted. Freshness is tracked in `sync_state`; a stale or failing111calendar keeps its last known busy periods and, with the default conservative policy, turns automatic112confirmations into requests to approve (`conflict_flag = external_stale`).113Outgoing: a private ICS feed of confirmed bookings (token shown once, stored hashed).114Google Calendar / Microsoft 365 OAuth and CalDAV are **not implemented** (no credentials available); the tables115(`external_calendar_accounts.provider`, `secret_enc`, `write_events`) already allow them.116117## Data model118119`db/migrations/001_init.sql` (commented): users, authenticators, setup_tokens, recovery_codes,120webauthn_challenges, sessions, settings, event_types, availability_rules, availability_exceptions,121external_calendar_accounts, external_calendars, sync_state, busy_blocks, contacts, bookings, booking_requests,122booking_answers, booking_tokens, notification_jobs, audit_events, rate_limits, worker_heartbeat.123124## Source map125126| Path | Role |127|---|---|128| `src/lib/engine/slots.ts` | availability engine |129| `src/lib/services/state.ts` | loads what the engine needs from the database |130| `src/lib/services/booking.ts` | submit, approve, decline, propose, cancel, reschedule, expire |131| `src/lib/services/outbox.ts`, `notify.ts`, `src/lib/mail/*`, `src/lib/ics.ts` | notifications |132| `src/lib/services/external.ts` | ICS subscriptions |133| `src/lib/services/maintenance.ts` | retention, erasure of a contact |134| `src/lib/auth.ts` | passkeys, sessions, CSRF, enrolment tokens |135| `src/lib/api/*` | routers, schemas, handlers |136| `src/components/public/*`, `src/components/admin/*` | interface |137| `src/worker/index.ts` | background loop |138| `tests/` (Vitest, real PostgreSQL), `e2e/` (Playwright) | tests |139