Build Scheduling & Availability Core on node-postgres

Scheduling looks like CRUD until the clocks change and two people click Book at the same second — and then it is recurrence expansion across a 23-hour day, a wall clock that happens twice, a 30-minute DST shift on Lord Howe Island, and a write-skew race that passes every single-threaded test you would think to write. This pack builds the hard core of that: RFC 5545 recurrence expanded in floating wall-clock time, IANA zone-identity handling with a pinned answer to the two ambiguous cases, buffers and multi-seat capacity, and a booking transaction guarded by a per-resource lock and a PostgreSQL exclusion constraint. The proof is `verify.sh`: **39 checks**, including >20,000 property samples over 12 real DST transitions across six zones, the RFC's own published recurrence examples, and 16 concurrent bookers racing against real PostgreSQL — plus one check that asserts the *naive* check-then-insert pattern genuinely does double-book in that same database, so the race test is proven to have teeth rather than assumed to.

**Status: `draft` — no receipt yet.** Every verified pack in this catalog publishes a measured pass rate as *M of N* clean-room builds. This one has not been through the harness, so it publishes nothing: no pass rate, no measured token count, no measured dollar cost. The figures below that are not version pins or citations are labelled as estimates. When it runs, whatever comes out gets published.

Example use cases

• **A dental or physio practice-management tool** where three chairs are three seats on one resource, appointments carry a 15-minute turnaround buffer, and the recurring "Tuesdays 9–5, no Wednesdays" template has to still mean 9am on the Tuesday after the clocks change.
• **A gym or studio class-booking app** where a 6am HIIT class holds twelve people — exactly twelve, including the morning fifty people refresh the page at once — and a cancellation two minutes before must free exactly one spot even if the customer taps Cancel four times.
• **A field-service / trades dispatcher** covering vans across several regions in different zones, where the booking horizon is "up to 30 days out, at least 4 hours' notice", and a job's travel buffer must block the slot on either side without eating into the technician's shift window.
• **An indie SaaS adding "book a call with me" inside its own product** rather than sending users to a third-party scheduling link — same brand, same session, same database, one `POST /bookings`.
• **A tutoring or lessons marketplace** where a tutor in `Pacific/Auckland` and a student in `America/New_York` are looking at the same slot list on opposite DST schedules, and both need to be right.
• **An agency re-stamping one scheduling core across client projects** — clinic, salon, driving school — instead of rediscovering the DST and double-booking bugs once per contract.

Scale envelope

Derived from the shipped design, not aspiration, and labelled as estimates where they are estimates. Nothing here is load-tested — this pack has no receipt yet, so it has no measured performance numbers at all.

The serialisation point is **one `pg_advisory_xact_lock` per resource**. That is the ceiling to reason about, and it is deliberately per-resource rather than global so that a busy clinic does not queue behind an idle one.

• **Bookings per resource (estimate, not load-tested):** a booking transaction is a lock acquisition, four small indexed reads, and one insert. On managed Postgres in the same region that is typically tens of milliseconds, putting a rough order-of-magnitude ceiling in the **low hundreds of bookings per second for a single resource** — sustained. A "resource" is one practitioner, room, chair or van. No real one is booked hundreds of times a second, which is why this ceiling is comfortable rather than clever.
• **Resources in parallel:** unbounded by this design — different resources take different lock keys and never contend. The real limit becomes your `pg.Pool` size (default `max: 10`) and the database's `max_connections` (PostgreSQL's default is 100), both of which you tune, not this pack.
• **Availability reads:** `slotsForResource` is pure computation over the rules for one resource plus one indexed range query for overlapping bookings. Expanding a year of weekly rules is a few hundred instances; the hard cap is 10,000 instances per call. Cache it if you serve the same month to many visitors — this pack does not, deliberately, because cache invalidation on booking is your product's decision.
• **Rows:** one row per booking, one per availability rule, one per exception. Millions of bookings is an ordinary PostgreSQL table for this schema, with a partial GiST index on the confirmed ones. Storage is not the constraint.
• **First ceiling you will actually hit:** a single hot resource (a popular class, a "book the whole shop" flow) where concurrent booking attempts on the *same* resource start queuing on its advisory lock. That is added latency, not incorrect behaviour.
• **Upgrade path when you hit it:** replace the lock with the lock-free variant — try seat 0, catch `23P01`, `ROLLBACK TO SAVEPOINT`, try seat 1 — which is correct by the same exclusion constraint and bounded by `capacity` attempts. `ARCHITECTURE.md` names it as the documented upgrade with the tradeoff (savepoints and a retry loop in the hot path) already stated.

**What this comfortably covers as shipped:** a multi-location clinic, a studio with a full class timetable, a trades business with a fleet, a marketplace with thousands of independent providers — anything where load is spread across resources. **What it isn't sized for out of the box:** a single resource under genuine thundering-herd load (ticket-drop shaped, not appointment shaped). That is the upgrade path above, not a change this build makes for you.

**What it costs to skip the pack:** honestly, we do not know yet, and we will not invent it. The verified packs in this catalog publish a measured per-run token cost from their receipts; this one is `draft` and has none. What can be said without measuring: the timezone layer is roughly 35 lines and the recurrence layer a few hundred, so **do not buy this to save typing.** Buy it because the 39 checks encode which 35 lines, and because several of those checks map directly onto bugs that are open right now in products built by companies whose entire business is scheduling.

What you get

Features:

• **Recurring availability on RFC 5545 RRULE** — `FREQ` DAILY/WEEKLY/MONTHLY/YEARLY, `INTERVAL`, `COUNT`, `UNTIL`, `WKST`, `BYDAY` (with ±ordinals), `BYMONTHDAY` (incl. negative), `BYMONTH`, `BYSETPOS`, plus `EXDATE`/`RDATE` — expanded in **floating wall-clock time**, so "09:00 every Tuesday" is 09:00 forever, on both sides of every DST transition
• **Zone-identity time handling with no dependency** — IANA identifiers only (offsets, `EST` and `PST8PDT` are rejected at the boundary), wall-clock ↔ instant conversion built on the runtime's own tz database, and a pinned disambiguation policy for the two cases every scheduler hits twice a year: a wall clock that happens twice, and one that never happens
• **Availability windows** from rules plus one-off `block` (vacation, holiday) and `open` (extra evening) exceptions, all stored in local wall clock so they keep their local meaning across a transition
• **Slot generation** with independent slot length and step, pre/post buffers, minimum notice, a rolling booking horizon, and per-slot remaining capacity — every slot returned as a UTC instant **plus** its resource-local rendering and offset, so a client never re-derives time
• **Multi-seat capacity** — one practitioner or twelve spots in a class, through one code path and one database constraint
• **A booking path safe under real concurrency** — a per-resource transaction lock, seat-lane assignment, and a partial GiST exclusion constraint as a database-level backstop that holds even against code that never calls this pack
• **Idempotent booking and idempotent cancellation** — a retried request returns the same booking; a retried cancel frees exactly one seat, because capacity is counted and never stored
• **A runnable acceptance harness** (`verify.sh`, 39 checks) covering property tests over real DST transitions across six zones, RFC-conformance of the recurrence expansion, and 16-way concurrent booking races against real PostgreSQL — exit 0 = correct build

Screens: **none.** This is a core you embed, not a booking product — see "Non-goals". Everything is a function call: `slotsForResource`, `book`, `cancel`. `ARCHITECTURE.md` §Integration seam names exactly where your route handlers attach.

Build budget: **expected 3 sessions on a $20/mo plan** (Claude Pro or equivalent) — one build session, one verify/fix session, and typically a third because the fix pass on the timezone and concurrency layers runs long. The binding constraint is **Opus-tier prompt weight against Pro's rolling-window and weekly caps, not raw token volume**: five tasks route to `claude-opus-5` (T1 zone, T2 recurrence, T4 slots, T7 booking, T8 verify/fix), everything else runs on Sonnet/Haiku via the routing table in `TASKS.md`/`prompts/01-build.md`. Anthropic does not publish an exact token-per-window figure, so treat the session count as an expectation, not a guarantee. Plan on roughly **560k fresh tokens** (input + output) for one build pass — **this is an estimate, not a measurement**: this pack is `draft` and has no receipt yet, and the number is extrapolated from a comparable verified pack in the catalog. The harness run will overwrite it with the measured figure.

node-postgres versions lock