Seraya Psikologi — Documentation

Booking and payment MVP · Implementation baseline · 96 ADR

91. Capacity Overlap and TransitionBuffer Placement

Status

Accepted for MVP. Closes TBC-CAPACITY-01 and TBC-BUFFER-01 from PRD-GUIDELINE-REVIEW.md (Round 1 P1-02). Implements the overlap detection mechanism and buffer placement left open by ADR 0013-offering-specific-slots.md and ADR 0041-transition-buffer.md. Production launch still requires TBC-STACK-01 (Worker + D1 vs Supabase + Postgres) to be closed before any migration is executed; this ADR is schema-shape-stable across both stacks but the exact DDL syntax in §6 is written for the Postgres family. D1/SQLite equivalent is documented in §7.

Ringkasan eksekutif (Bahasa Indonesia)

Context

ADR 0013 sudah memutuskan bahwa AvailabilitySlot milik satu psikolog dan satu ServiceOffering, dan bahwa active SlotHold serta confirmed Appointment untuk psikolog yang sama tidak boleh overlap tanpa membedakan offering. ADR 0041 sudah memutuskan TransitionBuffer 15 menit dengan admin-configurable per offering, di-snapshot pada transaction, dan diterapkan pada generation, SlotHold, Appointment overlap check, dan reschedule slot claim.

Ticket #3 (vault/Projects/Seraya Psikologi/Tickets/Ticket 03 — Capacity overlap & TransitionBuffer.md) mengangkat dua keputusan yang masih terbuka:

  1. Granularitas slot kandidat. 15 menit menghasilkan kandidat banyak (6 slot untuk satu sesi 60-menit dengan buffer 15-menit) dan memungkinkan kombinasi overlap antar offering yang sulit dibatasi. 60 menit tidak menyisakan ruang untuk buffer. 30 menit menjadi grid terkecil yang (a) membagi sesi 60-menit menjadi tepat 2 unit, (b) membiarkan TransitionBuffer 15-menit menjadi 0 atau 1 slot tambahan, dan (c) membolehkan psikolog yang sama memegang dua sesi adjacent di offering berbeda tanpa semu kecuali buffer overlap.

  2. Mekanisme deteksi overlap. Guide saat ini hanya meminta unique active hold per slot (IMPLEMENTATION-GUIDE.md §8.2). Unique-per-slot tidak cukup karena: (a) satu psikolog dapat memiliki dua AvailabilitySlot untuk offering berbeda dengan [starts_at, ends_at) yang sama; (b) EXISTS-style race pada saat CreateSlotHold dapat memasukkan dua hold sebelum unique constraint sempat di-check.

  3. Placement TransitionBuffer. ADR 0041 line 33–35 masih membuka before/after/both. Tiga kandidat:
    - before-only: sesi 10:00–11:00 + buffer 09:45–10:00. Sesi berikutnya 11:00–12:00 masih boleh di-claim oleh psikolog lain jika psikolog ini tidak klaim, tetapi tidak ada waktu untuk psikolog melakukan transisi ke 11:00. Tidak aman untuk psikolog.
    - after-only: sesi 10:00–11:00 + buffer 11:00–11:15. Psikolog tidak punya waktu untuk prep sebelum 10:00. Tidak aman untuk psikolog.
    - both / simetris mengelilingi appointment: interval efektif [start − buffer, end + buffer). Reservation disimpan dengan starts_at = session_start − buffer dan ends_at = session_end + buffer atau dengan ends_at = session_end + 2×buffer dan deteksi overlap dilakukan pada starts_at − buffer .. ends_at — kedua formulation equivalent untuk overlap detection. Placement simetris menjamin psikolog mendapat transisi sebelum dan sesudah, dan mencegah sesi back-to-back untuk psikolog yang sama.

Round 3 PRD-GUIDELINE-REVIEW.md (Non-Teknis menang pada konflik bisnis) tidak menyentuh placement ini, sehingga keputusan ada di tangan tim teknis berdasarkan implikasi operasional untuk psikolog.

Diskusi multi-perspektif

Privacy (klinis/etis)

Operations (admin/finance)

Engineering (aggregate & schema)

Mengapa CapacityReservation bukan langsung di AvailabilitySlot?

AvailabilitySlot adalah representasi kandidat bookable capacity. Ia harus tetap bisa di-generate/di-withdraw secara massal saat AvailabilityRule/Exception berubah (ADR 0061). Jika uniqueness overlap ditempatkan langsung di AvailabilitySlot, maka:

CapacityReservation adalah agregat yang merepresentasikan "slot waktu yang sedang di-claim" untuk psikolog tertentu. Ia child-of Booking (untuk SlotHold-backed) atau child-of Appointment (untuk confirmed). Granularitas:

Kedua tipe reservation ini share satu unique/overlap constraint pada (psychologist_id, time_range, state).

Mengapa placement simetris, bukan before-only atau after-only?

Before-only membuat sesi back-to-back tanpa transisi (misal 10:00–11:00 dan 11:00–12:00 untuk psikolog yang sama) tanpa waktu untuk psikolog melakukan catatan pasca-sesi dan prep untuk sesi berikutnya. After-only membuat psikolog tidak punya waktu untuk prep sebelum sesi. Simetris mengelilingi appointment memastikan psikolog mendapat transisi sebelum dan sesudah, dan dua sesi adjacent tanpa gap akan terdeteksi sebagai overlap.

Untuk menawarkan konsistensi display: UI menampilkan jam [starts_at + buffer, ends_at − buffer) (jam sesi saja). Reservation internal mencakup buffer.

Mengapa 30 menit, bukan 15 atau 60?

30 menit adalah pilihan yang balance antara granularity operasional (psikolog tidak di-block dari slot adjacent legitimate) dan kemampuan teknis (constraint check pada 3-slot block per sesi).

Mengapa app-level + DB constraint, bukan salah satu saja?

Decision

1. Granularitas candidate slot: 30 menit

2. Capacity uniqueness model: CapacityReservation table dengan canonical overlap detection

Model final:

CapacityReservation(
  id                     uuid PRIMARY KEY,
  psychologist_id        uuid NOT NULL,
  booking_id             uuid,                 -- NULL jika bukan dari Booking
  appointment_id         uuid,                 -- NULL jika bukan confirmed Appointment
  reservation_kind       enum NOT NULL,        -- 'hold' | 'confirmed'
  state                  enum NOT NULL,        -- 'hold_active' | 'confirmed' | 'released' | 'cancelled' | 'expired'
  starts_at              timestamptz NOT NULL, -- = session_start_at (UI-displayed)
  ends_at                timestamptz NOT NULL, -- = session_end_at (UI-displayed)
  buffer_minutes         int  NOT NULL,        -- snapshot from ServiceOffering
  created_at             timestamptz NOT NULL,
  released_at            timestamptz,
  release_reason         enum,                 -- 'hold_expired' | 'booking_cancelled' | 'appointment_cancelled' | 'admin_override'
  version                int  NOT NULL DEFAULT 1
)

Canonical overlap detection: untuk satu psychologist_id, dua CapacityReservation dengan state ∈ {hold_active, confirmed} overlap jika interval starts_at .. ends_at + (2 × buffer_minutes) mereka overlap (Postgres: tstzrange(starts_at, ends_at + (buffer_minutes || ' minutes')::interval, '[)'); D1/SQLite: explicit WHERE clause atau trigger-based check).

Decision: gunakan app-level precheck + DB constraint, dengan DB constraint sebagai hard guarantee. Pada Postgres, ini adalah EXCLUDE USING GIST; pada D1/SQLite, ini adalah trigger BEFORE INSERT/UPDATE yang menolak overlap.

3. TransitionBuffer placement: simetris mengelilingi appointment

4. Concurrency enforcement: app-level + DB constraint

5. Definisi "no overlap" di level psikolog + waktu

Invariant: untuk satu psychologist_id, tidak ada dua CapacityReservation dengan state ∈ {hold_active, confirmed} yang memiliki effective_range = [starts_at, ends_at + 2 × buffer_minutes) overlap. effective_range overlap terdeteksi via range_overlap(a, b) = a.lower < b.upper AND b.lower < a.upper.

Definisi ini berlaku untuk semua kombinasi:

6. Migration schema (Postgres family — D1/SQLite equivalent di §7)

-- 0091_capacity_reservation.sql

CREATE TYPE capacity_reservation_kind AS ENUM ('hold', 'confirmed');
CREATE TYPE capacity_reservation_state AS ENUM (
  'hold_active', 'confirmed', 'released', 'cancelled', 'expired'
);
CREATE TYPE capacity_reservation_release_reason AS ENUM (
  'hold_expired', 'booking_cancelled', 'appointment_cancelled', 'admin_override'
);

CREATE TABLE capacity_reservation (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  psychologist_id     uuid NOT NULL REFERENCES psychologist_profile(id),
  booking_id          uuid REFERENCES booking(id),
  appointment_id      uuid REFERENCES appointment(id),
  reservation_kind    capacity_reservation_kind NOT NULL,
  state               capacity_reservation_state NOT NULL,
  starts_at           timestamptz NOT NULL,
  ends_at             timestamptz NOT NULL,
  buffer_minutes      int NOT NULL CHECK (buffer_minutes >= 0),
  created_at          timestamptz NOT NULL DEFAULT now(),
  released_at         timestamptz,
  release_reason      capacity_reservation_release_reason,
  version             int NOT NULL DEFAULT 1,
  CONSTRAINT capacity_reservation_time_order CHECK (ends_at > starts_at),
  CONSTRAINT capacity_reservation_booking_or_appointment
    CHECK (
      (reservation_kind = 'hold'      AND booking_id IS NOT NULL AND appointment_id IS NULL) OR
      (reservation_kind = 'confirmed' AND appointment_id IS NOT NULL)
    )
);

CREATE INDEX capacity_reservation_psychologist_state_idx
  ON capacity_reservation (psychologist_id, state);

-- Hard guarantee: no two active/confirmed reservations overlap per psychologist.
-- effective_range = [starts_at, ends_at + 2*buffer). Implemented via tstzrange exclusion.
ALTER TABLE capacity_reservation
  ADD CONSTRAINT capacity_reservation_no_overlap
  EXCLUDE USING GIST (
    psychologist_id WITH =,
    tstzrange(
      starts_at,
      ends_at + make_interval(mins => buffer_minutes * 2),
      '[)'
    ) WITH &&
  )
  WHERE (state IN ('hold_active', 'confirmed'));

-- Append-only history: state transitions are UPDATE only, never DELETE.
-- Audit trail is maintained via AuditRecord per IMPLEMENTATION-GUIDE.md §11.

D1/SQLite equivalent §7 menggunakan trigger untuk replicate EXCLUDE constraint.

7. D1/SQLite equivalent

-- 0091_capacity_reservation.sql (D1/SQLite)

CREATE TABLE capacity_reservation (
  id                  TEXT PRIMARY KEY,
  psychologist_id     TEXT NOT NULL REFERENCES psychologist_profile(id),
  booking_id          TEXT REFERENCES booking(id),
  appointment_id      TEXT REFERENCES appointment(id),
  reservation_kind    TEXT NOT NULL CHECK (reservation_kind IN ('hold', 'confirmed')),
  state               TEXT NOT NULL CHECK (state IN ('hold_active', 'confirmed', 'released', 'cancelled', 'expired')),
  starts_at           TEXT NOT NULL,  -- ISO 8601 timestamptz
  ends_at             TEXT NOT NULL,
  buffer_minutes      INTEGER NOT NULL CHECK (buffer_minutes >= 0),
  created_at          TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  released_at         TEXT,
  release_reason      TEXT CHECK (release_reason IN ('hold_expired', 'booking_cancelled', 'appointment_cancelled', 'admin_override') OR release_reason IS NULL),
  version             INTEGER NOT NULL DEFAULT 1,
  CHECK (ends_at > starts_at),
  CHECK (
    (reservation_kind = 'hold'      AND booking_id IS NOT NULL AND appointment_id IS NULL) OR
    (reservation_kind = 'confirmed' AND appointment_id IS NOT NULL)
  )
);

CREATE INDEX capacity_reservation_psychologist_state_idx
  ON capacity_reservation (psychologist_id, state);

-- Trigger-based overlap check: reject INSERT/UPDATE if any active/confirmed row overlaps.
-- Overlap definition: a.starts_at < b.effective_end AND b.starts_at < a.effective_end
-- effective_end = ends_at + (2 * buffer_minutes * 60 seconds)
CREATE TRIGGER capacity_reservation_no_overlap_insert
BEFORE INSERT ON capacity_reservation
WHEN NEW.state IN ('hold_active', 'confirmed')
BEGIN
  SELECT RAISE(ABORT, 'capacity_overlap')
  WHERE EXISTS (
    SELECT 1 FROM capacity_reservation existing
    WHERE existing.psychologist_id = NEW.psychologist_id
      AND existing.state IN ('hold_active', 'confirmed')
      AND existing.id != NEW.id
      AND datetime(existing.starts_at) < datetime(NEW.ends_at, '+' || (NEW.buffer_minutes * 2) || ' minutes')
      AND datetime(NEW.starts_at) < datetime(existing.ends_at, '+' || (existing.buffer_minutes * 2) || ' minutes')
  );
END;

CREATE TRIGGER capacity_reservation_no_overlap_update
BEFORE UPDATE ON capacity_reservation
WHEN NEW.state IN ('hold_active', 'confirmed')
BEGIN
  SELECT RAISE(ABORT, 'capacity_overlap')
  WHERE EXISTS (
    SELECT 1 FROM capacity_reservation existing
    WHERE existing.psychologist_id = NEW.psychologist_id
      AND existing.state IN ('hold_active', 'confirmed')
      AND existing.id != NEW.id
      AND datetime(existing.starts_at) < datetime(NEW.ends_at, '+' || (NEW.buffer_minutes * 2) || ' minutes')
      AND datetime(NEW.starts_at) < datetime(existing.ends_at, '+' || (existing.buffer_minutes * 2) || ' minutes')
  );
END;

D1/SQLite tidak mendukung EXCLUDE USING GIST; trigger memberikan equivalent enforcement dengan overhead per-insert/update. Untuk scale MVP (ratusan reservation per psikolog per minggu), overhead acceptable.

8. Aggregate & entity mapping

Existing entity Behaviour change
AvailabilitySlot Tetap: representasi kandidat bookable capacity. Tidak ada reservation state.
SlotHold Tetap: state hold + TTL. Saat CreateSlotHold sukses, juga insert CapacityReservation (reservation_kind = 'hold', state = 'hold_active'). Saat ExpireSlotHold atau hold release, update CapacityReservation.state ke released/expired.
Appointment Tetap: appointment record. Saat confirmed, juga insert CapacityReservation (reservation_kind = 'confirmed'). Saat cancelled/rescheduled/no_show final, update reservation state.
Booking Tetap. Tidak ada perubahan.
OfferSnapshot Tetap: snapshot buffer_minutes saat CreateSlotHold/CreateBooking.
AuditRecord Tetap: audit setiap transition CapacityReservation.state.

9. Command changes

Tidak ada command baru. Perubahan pada command existing:

10. Acceptance criteria (test scenarios)

  1. Single-psychologist single-offering happy path: psikolog Fuja, sesi 09:00–10:00, slot A. CreateSlotHold → CapacityReservation row 1 (hold_active). ApplyVerifiedPaymentEvent → reservation 1 released, reservation 2 (confirmed) inserted. No overlap detected.
  2. Same-psychologist different-offering overlap (P1-02 evidence): psikolog Fuja, dua klien hold slot 09:00–10:00 dari offering berbeda (online_individual dan online_couple_A). CreateSlotHold kedua gagal dengan capacity_overlap. Reservation kedua tidak ada di DB.
  3. Same-psychologist different-offering adjacent (legitimate): psikolog Fuja, sesi 09:00–10:00 + sesi 10:30–11:30. Buffer 15 menit di kedua sisi = reservation efektif 08:45–10:15 dan 10:15–11:45. Boundary test: 10:15 == 10:15 (exclusive ends_at) → tidak overlap → allowed.
  4. Same-psychologist different-offering near-overlap (rejected): psikolog Fuja, sesi 09:00–10:00 + sesi 10:15–11:15. Reservation efektif 08:45–10:15 dan 10:00–11:30. Overlap detected pada 10:00–10:15 → rejected.
  5. Cross-psychologist same-time: psikolog A sesi 09:00–10:00, psikolog B sesi 09:00–10:00. Tidak ada overlap pada psychologist_id. Allowed.
  6. Race condition: dua CreateSlotHold concurrent untuk slot overlap pada psikolog yang sama. Satu sukses, satu reject. DB constraint sebagai backstop.
  7. Late-payment reacquisition: hold expired (released), verified PaymentEvent arrives, ApplyVerifiedPaymentEvent attempt atomic claim untuk slot asli → jika masih free, reservation confirmed baru inserted → Appointment confirmed. Jika overlap dengan appointment baru di-held/dikonfirmasi orang lain → paid_late path (ADR 0059).
  8. Cancellation release: DecideCancellation approve → reservation di-mark cancelled, eligible slot AvailabilitySlot tidak auto-available sampai ada RegenerateFutureAvailability atau sampai ends_at < now().
  9. Couple package: 3 appointment (A, B, joint) untuk psikolog Fuja. Masing-masing punya CapacityReservation confirmed. Tidak overlap di antara ketiganya (jadwal A, B, joint terpisah per hari atau per minggu).
  10. Per-offering buffer override: offering A buffer 15 menit, offering B buffer 30 menit. Dua sesi adjacent 09:00–10:00 dan 10:00–11:00 dengan offering berbeda. Reservation efektif A = 08:45–10:15, B = 09:30–11:30. Overlap 09:30–10:15 → rejected.

Consequences

Positive:

Costs and constraints:

Open follow-up

Reference