Seraya Psikologi — Documentation

Booking and payment MVP · Implementation baseline · 96 ADR

93. Payment Settlement Uniqueness and paid_late Package Effects

Status

Accepted for MVP. Closes TBC-PAY-SETTLEMENT-01 from PRD-GUIDELINE-REVIEW.md (Round 1 P1-10 and Round 2 R2 follow-up). Implements the at-most-one successful settlement per Booking/purchase intent invariant that the existing idempotency-by-provider-event-id (mentioned in ADR 0059 and CONTEXT.md:41) does not catch, and defines the paid_late package creation effects left open by ADR 0059 lines 13–20. Production launch still requires TBC-STACK-01 (Worker + D1 vs Supabase + Postgres) to be closed before any migration is executed; the SQL DDL in §6 is written for the Postgres family with the D1/SQLite equivalent in §7.

Ringkasan eksekutif (Bahasa Indonesia)

Context

CONTEXT.md:41 mengizinkan multiple payment attempts per Booking. ADR 0059 dan praktik idempotency-by-provider-event-id (IMPLEMENTATION-GUIDE.md §8.2) menjamin webhook replay aman, namun tidak mencegah dua Payment berbeda (misal dua checkout attempt ke Snap yang masing-masing membuat order_id berbeda, atau duplicate routing path) sama-sama sukses untuk Booking yang sama. Risiko: double-paid capture, double-issued PackagePurchase, double-confirmed Appointment, atau financial truth mismatch yang harus direkonsiliasi manual.

Untuk package, ADR 0059 line 13–18 sudah memutuskan paid_late/reconciliation path untuk single-session Booking, dan line 17 menyebutkan "reacquire original slot atomically jika masih free". Untuk package Booking, ADR 0059 tidak menjawab apakah PackagePurchase + ordered SessionEntitlement + PackageValidity dibuat pada saat webhook verified (Option A), ditunda sampai Admin resolution (Option B), atau dibuat dengan first session held sebagai pending_schedule (Option C — turunan dari A). Ticket #5 mengangkat keputusan ini dan meminta closure eksplisit.

Ticket #5 acceptance criteria: (1) integration test dua webhook sukses berbeda ID untuk Booking sama → hanya satu yang settle, yang lain masuk reconciliation; (2) integration test paid-late package → Admin resolution flow menciptakan PackagePurchase dengan first session scheduled atau held untuk Admin manual scheduling. ADR ini menjawab kedua acceptance criteria dan menambahkan uniqueness untuk amount/currency/order/merchant, idempotency collision handling, dan crash-window safety.

Diskusi multi-perspektif

Privacy (klinis/etis)

Operations (admin/finance)

Engineering (aggregate & schema)

UX (checkout & Admin)

Decision

1. Invariant: at-most-one successful settlement per Booking/purchase intent

1.1 Pernyataan invariant

Untuk satu Booking.id, terdapat paling banyak satu Payment dengan status = 'paid' (yaitu settled_at IS NOT NULL). Untuk satu PackagePurchase.id, terdapat paling banyak satu Payment dengan status = 'paid' (refleksi dari Booking-level uniqueness melalui FK Booking.id).

1.2 Mekanisme enforcement

1.3 Status enum Payment

Status Settled_at Meaning
pending NULL Payment created saat createCheckout, menunggu webhook
paid NOT NULL Verified capture/settlement event applied
paid_late_slot_reacquired NOT NULL Late verified success; original slot reacquired
paid_late_slot_unavailable NOT NULL Late verified success; slot tidak dapat direacquire; Admin resolution required
paid_late_first_session_pending NOT NULL Package late success; first entitlement held pending Admin scheduling
failed NULL Verified deny/cancel/expire/failure event applied
refunded_full NOT NULL → di-update via RefundAction summary full_refund RefundAction completed
refunded_no_disbursement NOT NULL no_refund RefundAction recorded as audited non-disbursement (Payment tetap paid secara financial, summary menampilkan refunded di Admin UI)

Payment.status adalah derived current projection, tidak pernah di-overwrite historical — perubahan dari paid → refunded_full adalah update field summary (computed dari RefundAction aggregate), bukan rewrite history. PaymentEvent append-only; Payment.settled_at immutable setelah set.

2. Verifikasi amount / currency / order / merchant

2.1 Pernyataan invariant

Sebuah verified PaymentEvent hanya menghasilkan Payment settled jika dan hanya jika event.gross_amount == offer_snapshot.amount_cents AND event.currency == offer_snapshot.currency AND event.order_id == booking.id AND event.merchant_id == configured_merchant_id.

2.2 Mekanisme enforcement

2.3 Field verification table

Field Source of truth Verification
gross_amount OfferSnapshot.amount_cents (snapshotted at booking creation per ADR 0042) exact match (cents integer)
currency OfferSnapshot.currency (default IDR) exact match
order_id Booking.id (UUID string) exact match
merchant_id Configured Midtrans merchant ID (env var) exact match
signature_key Computed from payload + server key signature verification (existing)

3. Idempotency key scope

3.1 Lifetime scope

Idempotency records (payment_event_idempotency) keyed by (provider_event_id, payment_intent_id) adalah lifetime: tidak ada TTL atau purge selama PaymentEvent masih ada. Karena PaymentEvent append-only dan retained per TBC-PRIVACY-01 audit/legal policy, idempotency record seumur hidup retensi PaymentEvent.

3.2 Same-key/different-payload behavior

3.3 Payload fingerprint

4. Out-of-order / repeated-status / reversal mapping

4.1 Status mapping table

Provider status PaymentEvent.event_type Effect on Payment Effect on Booking/Appointment/Package
capture / settlement (final) capture jika belum settled: status = 'paid', settled_at = now() state transition normal
pending (transient) pending no-op no-op (Booking tetap pending_payment)
deny deny status = 'failed' (jika belum terminal lain) Booking → failed
cancel cancel status = 'failed' Booking → failed
expire expire status = 'failed' Booking → expired, SlotHold released
failure / failure_late failure status = 'failed' Booking → failed
refund (full) refund no-op di Payment (refund adalah RefundAction terpisah) depends on RefundAction decision
partial_refund partial_refund no-op di Payment (partial refund deferred; treat as full_refund atau no_refund action di RefundAction) depends on Admin decision
chargeback chargeback no-op di Payment (financial reversal handled outside booking product) Admin alerted via reconciliation report
challenge / fraud challenge no state change Admin review; Booking tetap pending_payment
recurring / subscription n/a not applicable — launch tidak ada subscription n/a

4.2 Repeated capture / settlement

4.3 Reversal mapping

5. paid_late package creation

5.1 Option analysis

Option Deskripsi Pro Kontra
A PackagePurchase + ordered SessionEntitlement + PackageValidity dibuat saat webhook verified; jika slot reacquire gagal, first SessionEntitlement.state = 'pending_schedule' Financial truth terjaga; Admin resolution tanpa invent new state; konsistensi dengan ADR 0059 Butuh pending_schedule state untuk first entitlement
B Tunda sampai Admin resolution Tidak ada state ambigu Kehilangan financial truth (Payment paid tapi PackagePurchase belum ada → reporting jadi susah); Admin jadi bottleneck untuk hal yang seharusnya otomatis
C Buat dengan first session held sebagai pending_schedule (variant A) Sama dengan A Tidak ada perbedaan dengan A kecuali kalau diinterpretasikan sebagai "Admin must resolve before any entitlement visible" — tapi itu mereintroduce B

Decision: Option A (eagle eye: identik dengan C, tapi C saya perjelas sebagai "Admin resolves ke real slot", sedangkan A adalah "first session held dengan pending_schedule atau scheduled jika reacquire berhasil, Admin resolves via existing reconciliation flow tanpa gate"). Rationale: financial truth, tidak ada new lifecycle state invented, Admin resolution flow sudah ada dari ADR 0059 line 18.

5.2 paid_late package atomic creation

Pada saat verified webhook untuk late payment (hold sudah expired):

  1. Payment.status = 'paid_late_first_session_pending' (atau 'paid_late_slot_reacquired' jika reacquire berhasil — lihat §5.3).
  2. PackagePurchase dibuat dengan status = 'paid' (atau 'paid_late' — konsisten dengan Payment status enum).
  3. PackageValidity dibuat dengan validity_start = now() (bukan original checkout time karena hold sudah expired — slot pertama tidak valid dari waktu itu).
  4. Ordered SessionEntitlement rows:
    - SessionEntitlement #1: state = 'pending_schedule' (jika reacquire gagal) atau 'scheduled' + linked ke Appointment reacquired (jika reacquire berhasil).
    - SessionEntitlement #2..N: state = 'available'.
  5. Transactional effects: Booking original di-mark paid_late_reconciled (status terminal — bukan confirmed karena original slot tidak confirmed; bukan failed karena payment sukses). Booking adalah paid late, package adalah paid late, original slot released (jika reacquire gagal) atau tetap held (jika reacquire berhasil).

5.3 Slot reacquire attempt

Mengikuti ADR 0059 line 17, ADR 0091 capacity overlap detection:

  1. Di dalam transaction, query CapacityReservation existing untuk (psychologist_id, original_starts_at, original_ends_at, state IN {hold_active, confirmed}) dengan effective_range overlap check.
  2. Jika tidak ada overlap dan original AvailabilitySlot masih state = 'available':
    - INSERT INTO capacity_reservation (reservation_kind = 'confirmed', state = 'confirmed', ...) — atomic claim.
    - INSERT INTO appointment (state = 'confirmed', ...) — linked ke original slot.
    - SessionEntitlement #1.state = 'scheduled', linked ke Appointment ini.
    - Payment.status = 'paid_late_slot_reacquired'.
    - Booking.status = 'paid_late_slot_reacquired'.
  3. Jika ada overlap atau slot unavailable:
    - Tidak insert CapacityReservation.
    - Tidak insert Appointment.
    - SessionEntitlement #1.state = 'pending_schedule'.
    - Payment.status = 'paid_late_first_session_pending'.
    - Booking.status = 'paid_late_slot_unavailable'.
    - Original Booking.snapshotted_slot_id reference di-keep untuk audit (Admin perlu tahu slot mana yang awalnya dimaksud).

5.4 Admin resolution flow untuk paid_late_first_session_pending

Admin workspace melihat PackagePurchase dengan flag requires_first_session_scheduling = true. Resolution options (existing Admin WhatsApp flow per ADR 0067):

Admin tidak boleh otomatis melakukan refund tanpa eksplisit client/Admin decision (consistent dengan ADR 0076 no auto-cutoff).

6. SQL migration (Postgres family)

-- Migration 0093: payment settlement uniqueness and paid_late package effects

-- 6.1 Unique partial index: at-most-one successful settlement per Booking
CREATE UNIQUE INDEX IF NOT EXISTS payment_one_settled_per_booking
  ON payment(booking_id)
  WHERE status = 'paid' AND settled_at IS NOT NULL;

-- 6.2 Idempotency record (lifetime, keyed by provider event id)
CREATE TABLE IF NOT EXISTS payment_event_idempotency (
  payment_intent_id     uuid NOT NULL,
  provider_event_id     text NOT NULL,
  payload_hash          bytea NOT NULL,
  payment_event_id      uuid NOT NULL REFERENCES payment_event(id),
  created_at            timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (provider_event_id, payment_intent_id)
);

-- 6.3 Payment status enum extension (additive, idempotent)
ALTER TYPE payment_status ADD VALUE IF NOT EXISTS 'paid_late_slot_reacquired';
ALTER TYPE payment_status ADD VALUE IF NOT EXISTS 'paid_late_slot_unavailable';
ALTER TYPE payment_status ADD VALUE IF NOT EXISTS 'paid_late_first_session_pending';
ALTER TYPE payment_status ADD VALUE IF NOT EXISTS 'refunded_no_disbursement';

-- 6.4 SessionEntitlement state enum extension (additive)
ALTER TYPE session_entitlement_state ADD VALUE IF NOT EXISTS 'pending_schedule';

-- 6.5 PackagePurchase: requires_first_session_scheduling flag
ALTER TABLE package_purchase
  ADD COLUMN IF NOT EXISTS requires_first_session_scheduling boolean NOT NULL DEFAULT false;

-- 6.6 Payment mismatch log (append-only audit)
CREATE TABLE IF NOT EXISTS payment_event_mismatch_log (
  id                   uuid PRIMARY KEY,
  booking_id           uuid NOT NULL,
  payment_intent_id    uuid,
  provider_event_id    text,
  mismatch_kind        enum NOT NULL,         -- 'amount' | 'currency' | 'order_id' | 'merchant_id' | 'idempotency_collision' | 'other'
  expected_value       jsonb,
  actual_value         jsonb,
  payload_hash         bytea,
  detected_at          timestamptz NOT NULL DEFAULT now(),
  resolved_at          timestamptz,
  resolved_by          uuid,                  -- staff_id
  resolution_action    text
);

CREATE INDEX IF NOT EXISTS payment_mismatch_unresolved
  ON payment_event_mismatch_log(booking_id)
  WHERE resolved_at IS NULL;

-- 6.7 Outbox table (transactional outbox for state transition events)
CREATE TABLE IF NOT EXISTS application_outbox (
  id                   uuid PRIMARY KEY,
  aggregate_type       text NOT NULL,         -- 'payment' | 'booking' | 'package_purchase' | etc
  aggregate_id         uuid NOT NULL,
  event_type           text NOT NULL,
  payload              jsonb NOT NULL,
  created_at           timestamptz NOT NULL DEFAULT now(),
  dispatched_at        timestamptz,
  dispatch_attempts    int NOT NULL DEFAULT 0,
  last_error           text
);

CREATE INDEX IF NOT EXISTS outbox_pending
  ON application_outbox(created_at)
  WHERE dispatched_at IS NULL;

-- 6.8 Booking status enum extension (additive)
ALTER TYPE booking_status ADD VALUE IF NOT EXISTS 'paid_late_slot_reacquired';
ALTER TYPE booking_status ADD VALUE IF NOT EXISTS 'paid_late_slot_unavailable';
ALTER TYPE booking_status ADD VALUE IF NOT EXISTS 'paid_late_reconciled';

-- 6.9 PackagePurchase status enum extension (additive)
ALTER TYPE package_purchase_status ADD VALUE IF NOT EXISTS 'paid_late';
ALTER TYPE package_purchase_status ADD VALUE IF NOT EXISTS 'closed_refunded';

Rollback (Postgres): drop indexes, drop tables (idempotency, mismatch log, outbox), drop columns, drop enum values (tidak reversible di Postgres; alternative: leave enum values unused, document sebagai deprecated).

7. D1 / SQLite equivalent

D1 (SQLite) tidak mendukung ALTER TYPE ... ADD VALUE. Solusi: enum values didefinisikan sebagai application-level CHECK constraint atau TEXT dengan application-level validation. ADD COLUMN IF NOT EXISTS supported sejak SQLite 3.35.0. Partial unique index supported sejak 3.8.0.

-- 7.1 Unique partial index (sama syntax, supported)
CREATE UNIQUE INDEX IF NOT EXISTS payment_one_settled_per_booking
  ON payment(booking_id)
  WHERE status = 'paid' AND settled_at IS NOT NULL;

-- 7.2 Idempotency record (sama DDL)
CREATE TABLE IF NOT EXISTS payment_event_idempotency (
  payment_intent_id     text NOT NULL,
  provider_event_id     text NOT NULL,
  payload_hash          blob NOT NULL,
  payment_event_id      text NOT NULL REFERENCES payment_event(id),
  created_at            text NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  PRIMARY KEY (provider_event_id, payment_intent_id)
);

-- 7.3 SessionEntitlement state CHECK constraint (SQLite tidak punya enum ALTER)
-- Pre-existing CHECK constraint di session_entitlement harus sudah include 'pending_schedule'.
-- Jika belum, ALTER TABLE recreate:
-- ALTER TABLE session_entitlement RENAME TO session_entitlement_old;
-- CREATE TABLE session_entitlement (... new CHECK ...);
-- INSERT INTO session_entitlement SELECT * FROM session_entitlement_old;
-- DROP TABLE session_entitlement_old;

-- 7.4 PackagePurchase column
ALTER TABLE package_purchase
  ADD COLUMN requires_first_session_scheduling integer NOT NULL DEFAULT 0;
-- (0 = false, 1 = true; SQLite tidak punya boolean native)

-- 7.5 Mismatch log (sama DDL dengan text/timestamp adjustments)
CREATE TABLE IF NOT EXISTS payment_event_mismatch_log (
  id                   text PRIMARY KEY,
  booking_id           text NOT NULL,
  payment_intent_id    text,
  provider_event_id    text,
  mismatch_kind        text NOT NULL CHECK (mismatch_kind IN
                       ('amount','currency','order_id','merchant_id','idempotency_collision','other')),
  expected_value       text,  -- JSON serialized
  actual_value         text,
  payload_hash         blob,
  detected_at          text NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  resolved_at          text,
  resolved_by          text,
  resolution_action    text
);

CREATE INDEX IF NOT EXISTS payment_mismatch_unresolved
  ON payment_event_mismatch_log(booking_id)
  WHERE resolved_at IS NULL;

-- 7.6 Outbox (sama DDL)
CREATE TABLE IF NOT EXISTS application_outbox (
  id                   text PRIMARY KEY,
  aggregate_type       text NOT NULL,
  aggregate_id         text NOT NULL,
  event_type           text NOT NULL,
  payload              text NOT NULL,
  created_at           text NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  dispatched_at        text,
  dispatch_attempts    integer NOT NULL DEFAULT 0,
  last_error           text
);

CREATE INDEX IF NOT EXISTS outbox_pending
  ON application_outbox(created_at)
  WHERE dispatched_at IS NULL;

8. Konsekuensi

Positif:

Biaya dan constraint:

Forbidden:

Open follow-up

Reference