-- AK-P16b ยท P16 Booking โ€” HARD DOUBLE-BOOKING GUARD (NOT in the Prisma schema) -- ============================================================================= -- SHIPPED: since 20260717090000_ak_p16b_booking_overlap_constraint this -- constraint is part of the tracked migration chain (that migration is the -- authoritative copy, incl. a pre-flight overlap check). This file remains the -- feature-branch template for `psql -f` on db-push-only databases. -- -- The race-protection backstop for DoD 2: a PostgreSQL EXCLUDE USING gist -- constraint that makes it physically impossible to persist two OVERLAPPING active -- appointments for the SAME provider WITHIN A TENANT (tenant-scoped like the -- slot engine; tenant-agnostic would leak cross-tenant busy state). This cannot be expressed in the Prisma schema -- (Prisma has no exclusion-constraint primitive), so it stays raw SQL. -- -- This is the second layer. The first layer is the application Serializable -- transaction in BookingSubmitService that re-checks slot availability and returns -- Klartext alternatives; this constraint guarantees correctness even under a lost -- race / concurrent writer, surfacing as a unique-violation the submit path maps to -- the same "slot just taken" Klartext. -- -- `held` counts as occupying (a past appointment that took place still blocks its -- slot for history integrity). `cancelled` / `no_show` / `rescheduled` free the slot. -- -- Forward-only (DoD 28). Idempotent + DO-guarded (reproducible from an empty DB, -- P3018-safe โ€” order-independent except for the extension, which is IF NOT EXISTS). -- -- Inverse SQL: -- ALTER TABLE "Appointment" DROP CONSTRAINT IF EXISTS appointment_no_overlap; -- -- (leave btree_gist installed; other modules may rely on it) -- ============================================================================= -- gist over a scalar (providerUserId) + a range (tsrange) needs the btree_gist -- extension to put the equality column into the same gist index. CREATE EXTENSION IF NOT EXISTS btree_gist; DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'appointment_no_overlap' ) THEN -- One active appointment per provider per time range. The half-open range -- [startAt, endAt) means back-to-back appointments (end == next start) do NOT -- conflict. Partial WHERE so only occupying statuses participate. EXECUTE ' ALTER TABLE "Appointment" ADD CONSTRAINT appointment_no_overlap EXCLUDE USING gist ( "tenantId" WITH =, "providerUserId" WITH =, tsrange("startAt", "endAt", ''[)'') WITH && ) WHERE (status IN (''scheduled'', ''held'')) '; END IF; END $$; -- ============================================================================= -- Verify (after `prisma db push`): -- psql "$DIRECT_URL" -f prisma/rls/booking-constraints.sql -- -- then attempt two overlapping inserts for one providerUserId โ†’ 2nd raises -- -- 23P01 exclusion_violation (mapped to Klartext "slot just taken"). -- =============================================================================