Domain 6 — Payments, Ledger & Refunds
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).