baya-monorepo/product/data-model/06-payments-ledger-and-refunds.md

Domain 6 — Payments, Ledger & Refunds

← Database Model

Related: business requirements — Payments & escrow. Ledger postings are explained in depth in Escrow ledger.

This is the most-changed domain. The previous model inferred money state from scattered status flags; this revision makes a double-entry ledger the source of truth and adds the idempotency and clawback primitives that any real-money platform needs before launch.

payment_gateways [CORE]

Role: Config per connected PSP/BNPL provider; credentials encrypted. Why type: multiple gateways run at once (standard IPG, a BNPL provider, a failover) and type (standard/bnpl) selects the flow. Why this matters for Iran: provider cut-offs happen (Toman/Jibit were abruptly suspended in Nov 2024), so the gateway is abstracted to be swappable. BNPL provider secrets (client_id/secret, merchant number, base_url, sandbox flag) live encrypted in config_json. Fields unchanged. Relations: 1:N → payment_transactions, payment_webhook_events.

payment_transactions [CORE]

Role: Every payment attempt against a booking; the succeeded row triggers confirmation. Stores the full gateway_response_json and the Shaparak reference code (definitive proof) for reconciliation and chargebacks. Why hardened: a retried PSP webhook could otherwise insert a second succeeded row and double-confirm.

Field	Type	Notes
baseline fields	…	id, booking_id, customer_id, gateway_id, amount, currency, status, gateway_transaction_id, gateway_reference_code, gateway_response_code, gateway_response_json, is_installment, ip_address, user_agent, timestamps
constraints		NEW: UNIQUE(gateway_reference_code) WHERE NOT NULL; filtered UNIQUE(booking_id) WHERE status='succeeded' — at most one capturing transaction per booking.

Relations: N:1 → bookings, payment_gateways; 1:1 → bnpl_transactions (if BNPL); 1:N → refunds, ledger_entries.

payment_webhook_events [CORE] — NEW

Role: Raw, deduplicated store of every PSP/BNPL callback. Why mandatory: callbacks are at-least-once and retried; without a unique store keyed on the provider's event id, a replayed "payment succeeded" double-confirms a booking and a replayed "settled" double-counts money. The handler upserts here first and no-ops on a duplicate, inside the same transaction that mutates payment state.

Field	Type	Notes
id	BIGINT PK
provider_code	NVARCHAR(50)	zarinpal / snapppay / …
external_event_id	NVARCHAR(200)	UNIQUE(provider_code, external_event_id)
event_type	NVARCHAR(80)
signature_valid	BIT
payload_json	NVARCHAR(MAX)	Raw callback
processing_status	NVARCHAR(20)	received / processed / failed / ignored
related_payment_transaction_id	BIGINT NULL
received_at, processed_at	DATETIME2

Relations: N:1 → payment_gateways; optional → payment_transactions.

refunds [CORE]

Role: Admin-initiated refunds (no customer self-service), always linked to a support ticket. Why these changes: the previous model treated refunds as 1:1 with a transaction and as a single undecomposed amount — but partials exist (shortened visit) and a refund must say how much reverses the platform fee vs the nurse payout, and how the money actually moves (card vs BNPL revert).

Field	Type	Notes
baseline	…	id, payment_transaction_id, booking_id, requested_by_customer_id, ticket_id, amount, refund_percentage, reason_category, reason_notes, status, approval/rejection fields, gateway_refund_reference, processed_at, admin_notes, timestamps
cardinality		CHANGED to 1:N per payment_transaction (app invariant: Σ refunded ≤ captured). The old "1:1" relationship summary was wrong.
platform_fee_refunded_irr	BIGINT	NEW — fee-leg decomposition
nurse_payout_refunded_irr	BIGINT	NEW — payout-leg decomposition (drives clawback if nurse already paid)
refund_channel	NVARCHAR(20)	NEW — psp_card / bnpl_revert / manual_bank
external_revert_reference	NVARCHAR(200) NULL	NEW — BNPL revert id
expected_customer_refund_eta	DATE NULL	NEW — the ~7–10 business-day BNPL window, surfaced in UI/reconciliation
cancellation_policy_code / refund_percentage_applied	…	NEW — snapshot of the policy that produced this refund

Relations: N:1 → payment_transactions, bookings, customer_profiles, tickets; 1:1 → nurse_clawbacks (only when refunding a booking whose nurse was already paid).

ledger_entries [CORE] — NEW

Role: The append-only, double-entry financial source of truth. Every money event posts balanced rows sharing a transaction_group_id (Σ debit = Σ credit). Per-nurse payable balance derives by filtering account_type='nurse_payable' + nurse_id. Why: a marketplace that holds escrow and pays out weekly minus commission, with refunds and clawbacks, is exactly the shape double-entry was invented for. The alternative (more money columns + status booleans) cannot answer "how much do we owe nurses right now" or reconcile against the bank, and makes refund/clawback second-class. Cost: one table + posting discipline.

Field	Type	Notes
id	BIGINT PK
transaction_group_id	UNIQUEIDENTIFIER	Groups the balanced legs of one event
account_type	NVARCHAR(40)	escrow_held / platform_revenue / nurse_payable / refund_payable / bnpl_fee_expense / psp_fee_expense / nurse_clawback_receivable / bad_debt
nurse_id	BIGINT FK NULL	Set for nurse_payable/nurse_clawback_receivable
direction	NVARCHAR(6)	debit / credit
amount_irr	BIGINT	Always positive; direction carries the sign
booking_id	BIGINT FK NULL
source_ref_type	NVARCHAR(40)	payment_transaction / refund / nurse_payout / bnpl_transaction / clawback
source_ref_id	BIGINT
memo	NVARCHAR(300) NULL
created_at	DATETIME2	Append-only; never updated

Canonical postings:

Event	Debit	Credit
Card capture	escrow_held (gross)	platform_revenue (commission) + nurse_payable (payout)
BNPL settle	as card, plus bnpl_fee_expense (provider commission)	escrow_held (provider commission) → escrow reflects net cash actually received
Refund (pre-payout)	nurse_payable + platform_revenue (decomposed legs)	refund_payable; later refund_payable ↔ escrow_held on confirm
Clawback (post-payout)	nurse_clawback_receivable (+ platform_revenue leg)	refund_payable
Clawback recovered	nurse_payable (next batch)	nurse_clawback_receivable

Relations: N:1 → bookings; logical links to payment_transactions/refunds/nurse_payouts/bnpl_transactions via source_ref_*.

nurse_clawbacks [CORE] — NEW

Role: A first-class receivable when a booking is refunded/disputed after the nurse was already paid. Why: this was the model's own flagged critical gap — Iranian payouts are real, hard-to-reverse bank transfers, so paying before the dispute window closes can create uncollectable loss with nowhere to record it. Closing it = gate payout on dispute_window_ends_at and record any post-payout recovery here.

Field	Type	Notes
id	BIGINT PK
nurse_id	BIGINT FK → nurse_profiles
booking_id	BIGINT FK → bookings
refund_id	BIGINT FK → refunds
original_payout_id	BIGINT FK → nurse_payouts NULL
amount_irr	BIGINT
status	NVARCHAR(30)	pending / recovered / written_off
recovered_in_payout_id	BIGINT FK NULL	Batch that netted it
created_at, resolved_at	DATETIME2

Relations: N:1 → nurse_profiles, bookings; 1:1 → refunds; → nurse_payouts (original and recovering).

invoices [MVP] — NEW

Role: Minimal official-invoice/receipt record per booking. Why: Iranian commission marketplaces face VAT/مودیان obligations on their commission (the Snapp/Tapsi precedent: the provider's earnings are the provider's own income; the platform's commission is the platform's taxable revenue). The smallest footprint that satisfies bookkeeping without a full tax engine. VAT is 10% (rose from 9% in 1403) and stored as a config-driven rate so a future exemption ruling on the nursing service itself is just a value change. Nurse-side income tax is the nurse's own responsibility.

Field	Type	Notes
id	BIGINT PK
booking_id	BIGINT FK → bookings
invoice_number	NVARCHAR(40) UNIQUE	Official sequential number
issuing_entity_type	NVARCHAR(20)	platform / partner_center
gross_irr	BIGINT
platform_commission_irr	BIGINT	The VAT-relevant line
bnpl_commission_irr	BIGINT NULL
vat_rate	DECIMAL(5,4)	Config-driven (0.10)
vat_irr	BIGINT
moadian_reference_number	NVARCHAR(40) NULL	سامانه مودیان 22-digit ref when issued
moadian_status	NVARCHAR(20) NULL
pdf_storage_key	NVARCHAR(512) NULL
issued_at	DATETIME2

Relations: 1:1 → bookings; N:1 → partner_centers (when issuer).