hamid
127 lines
20 KiB
HTML
127 lines
20 KiB
HTML
<!DOCTYPE html>
|
||
<html lang="en">
|
||
<head>
|
||
<meta charset="utf-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1">
|
||
<title>Domain 6 — Payments, Ledger & Refunds — Balinyaar docs</title>
|
||
<link rel="stylesheet" href="../assets/doc.css">
|
||
</head>
|
||
<body>
|
||
<div class="layout">
|
||
<aside class="sidebar">
|
||
<a class="brand" href="../index.html"><span class="dot"></span> Balinyaar docs</a>
|
||
<p class="tagline">Trust-first home-nursing marketplace · Iran</p>
|
||
<nav><div class="group"><div class="label">Start here</div><ul><li><a href="../index.html">Docs home</a></li><li><a href="../overview/platform-summary.html">Platform summary & ground truths</a></li></ul></div><div class="group"><div class="label">Business requirements</div><ul><li><a href="../business/index.html">Overview & MVP scope</a></li><li><a href="../business/01-actors-and-onboarding.html">1. Actors & onboarding</a></li><li><a href="../business/02-nurse-verification.html">2. Nurse verification</a></li><li><a href="../business/03-service-catalog-and-pricing.html">3. Service catalog & pricing</a></li><li><a href="../business/04-search-and-matching.html">4. Search & matching</a></li><li><a href="../business/05-booking-and-scheduling.html">5. Booking & scheduling</a></li><li><a href="../business/06-evv-and-service-delivery.html">6. EVV / service delivery</a></li><li><a href="../business/07-cancellation-and-refunds.html">7. Cancellation & refunds</a></li><li><a href="../business/08-payments-and-escrow.html">8. Payments & escrow</a></li><li><a href="../business/09-installments-bnpl.html">9. Installments / BNPL</a></li><li><a href="../business/10-payouts.html">10. Payouts to nurses</a></li><li><a href="../business/11-reviews-trust-and-safety.html">11. Reviews, trust & safety</a></li><li><a href="../business/12-messaging-and-emergencies.html">12. Messaging & emergencies</a></li><li><a href="../business/13-tax-invoicing-and-legal.html">13. Tax, invoicing & legal</a></li><li><a href="../business/14-notifications-and-admin.html">14. Notifications & admin</a></li></ul></div><div class="group"><div class="label">Database model</div><ul><li><a href="index.html">Overview & decisions</a></li><li><a href="diagrams.html">Diagrams</a></li><li><a href="01-identity-and-access.html">1. Identity & access</a></li><li><a href="02-geography.html">2. Geography</a></li><li><a href="03-services-and-pricing.html">3. Services & pricing</a></li><li><a href="04-verification-and-credentials.html">4. Verification & credentials</a></li><li><a href="05-booking-and-scheduling.html">5. Booking & scheduling</a></li><li><a class="active" href="06-payments-ledger-and-refunds.html">6. Payments, ledger & refunds</a></li><li><a href="07-payouts.html">7. Payouts</a></li><li><a href="08-bnpl.html">8. BNPL / installments</a></li><li><a href="09-messaging.html">9. Messaging</a></li><li><a href="10-reviews-and-records.html">10. Reviews & records</a></li><li><a href="11-notifications.html">11. Notifications</a></li><li><a href="12-audit-config-and-reference.html">12. Audit, config & reference</a></li><li><a href="13-partner-centers-and-future.html">13. Partner centers & future</a></li></ul></div><div class="group"><div class="label">Payments deep-dive</div><ul><li><a href="../payments/index.html">Overview & exec summary</a></li><li><a href="../payments/iranian-payment-reality.html">Iranian payment reality</a></li><li><a href="../payments/escrow-ledger.html">Escrow as a ledger</a></li><li><a href="../payments/bnpl-landscape.html">BNPL landscape & finding</a></li><li><a href="../payments/cancellation-and-payout.html">Cancellation & nurse payout</a></li><li><a href="../payments/integration-notes.html">Integration & schema touchpoints</a></li><li><a href="../payments/sources.html">Recommendations & sources</a></li></ul></div><div class="group"><div class="label">Research & strategy</div><ul><li><a href="../research/index.html">Overview & exec summary</a></li><li><a href="../research/market-and-competitors.html">Market & competitors</a></li><li><a href="../research/problems-and-risks.html">Problems & risks</a></li><li><a href="../research/verification.html">Verification (research)</a></li><li><a href="../research/legal-landscape.html">Legal landscape</a></li><li><a href="../research/go-to-market.html">Go-to-market & sources</a></li></ul></div><div class="group"><div class="label">Notes & more</div><ul><li><a href="../notes/open-questions.html">Open questions</a></li><li><a href="../notes/future-ideas.html">Future ideas</a></li><li><a href="../wireframes/index.html">Wireframes</a></li><li><a href="../fa/index.html">Farsi documents</a></li></ul></div></nav>
|
||
</aside>
|
||
<main class="main"><div class="content">
|
||
<div class="topbar"><button class="theme-toggle" type="button" onclick="__t()">theme</button></div>
|
||
<h1 id="domain-6-payments-ledger-refunds">Domain 6 — Payments, Ledger & Refunds</h1>
|
||
<p><a href="index.html">← Database Model</a></p>
|
||
<p><strong>Related:</strong> business requirements — <a href="../business/08-payments-and-escrow.html">Payments & escrow</a>. Ledger postings are explained in depth in <a href="../payments/escrow-ledger.html">Escrow ledger</a>.</p>
|
||
<p>This is the most-changed domain. The previous model <strong>inferred</strong> money state from scattered status flags; this revision makes a <strong>double-entry ledger</strong> the source of truth and adds the <strong>idempotency</strong> and <strong>clawback</strong> primitives that any real-money platform needs before launch.</p>
|
||
<h3 id="payment_gateways-core"><code>payment_gateways</code> [CORE] <a class="anchor" href="#payment_gateways-core" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> Config per connected PSP/BNPL provider; credentials encrypted. <strong>Why <code>type</code>:</strong> multiple gateways run at once (standard IPG, a BNPL provider, a failover) and <code>type</code> (<code>standard</code>/<code>bnpl</code>) selects the flow. <strong>Why this matters for Iran:</strong> 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 <code>config_json</code>. Fields unchanged. <strong>Relations:</strong> 1:N → <code>payment_transactions</code>, <code>payment_webhook_events</code>.</p>
|
||
<h3 id="payment_transactions-core"><code>payment_transactions</code> [CORE] <a class="anchor" href="#payment_transactions-core" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> Every payment attempt against a booking; the <code>succeeded</code> row triggers confirmation. Stores the full <code>gateway_response_json</code> and the <strong>Shaparak reference code</strong> (definitive proof) for reconciliation and chargebacks. <strong>Why hardened:</strong> a retried PSP webhook could otherwise insert a second <code>succeeded</code> row and double-confirm.</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td>baseline fields</td><td>…</td><td><code>id</code>, <code>booking_id</code>, <code>customer_id</code>, <code>gateway_id</code>, <code>amount</code>, <code>currency</code>, <code>status</code>, <code>gateway_transaction_id</code>, <code>gateway_reference_code</code>, <code>gateway_response_code</code>, <code>gateway_response_json</code>, <code>is_installment</code>, <code>ip_address</code>, <code>user_agent</code>, timestamps</td></tr>
|
||
<tr><td><strong>constraints</strong></td><td></td><td><strong>NEW:</strong> <code>UNIQUE(gateway_reference_code) WHERE NOT NULL</code>; filtered <code>UNIQUE(booking_id) WHERE status='succeeded'</code> — at most one capturing transaction per booking.</td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> N:1 → <code>bookings</code>, <code>payment_gateways</code>; 1:1 → <code>bnpl_transactions</code> (if BNPL); 1:N → <code>refunds</code>, <code>ledger_entries</code>.</p>
|
||
<h3 id="payment_webhook_events-core-new"><code>payment_webhook_events</code> [CORE] — <strong>NEW</strong> <a class="anchor" href="#payment_webhook_events-core-new" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> Raw, deduplicated store of every PSP/BNPL callback. <strong>Why mandatory:</strong> 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 <strong>upserts here first</strong> and no-ops on a duplicate, inside the same transaction that mutates payment state.</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td><code>id</code></td><td>BIGINT PK</td><td></td></tr>
|
||
<tr><td><code>provider_code</code></td><td>NVARCHAR(50)</td><td><code>zarinpal</code> / <code>snapppay</code> / …</td></tr>
|
||
<tr><td><code>external_event_id</code></td><td>NVARCHAR(200)</td><td><strong>UNIQUE(provider_code, external_event_id)</strong></td></tr>
|
||
<tr><td><code>event_type</code></td><td>NVARCHAR(80)</td><td></td></tr>
|
||
<tr><td><code>signature_valid</code></td><td>BIT</td><td></td></tr>
|
||
<tr><td><code>payload_json</code></td><td>NVARCHAR(MAX)</td><td>Raw callback</td></tr>
|
||
<tr><td><code>processing_status</code></td><td>NVARCHAR(20)</td><td><code>received</code> / <code>processed</code> / <code>failed</code> / <code>ignored</code></td></tr>
|
||
<tr><td><code>related_payment_transaction_id</code></td><td>BIGINT NULL</td><td></td></tr>
|
||
<tr><td><code>received_at</code>, <code>processed_at</code></td><td>DATETIME2</td><td></td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> N:1 → <code>payment_gateways</code>; optional → <code>payment_transactions</code>.</p>
|
||
<h3 id="refunds-core"><code>refunds</code> [CORE] <a class="anchor" href="#refunds-core" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> Admin-initiated refunds (no customer self-service), always linked to a support ticket. <strong>Why these changes:</strong> 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 <strong>platform fee</strong> vs the <strong>nurse payout</strong>, and how the money actually moves (card vs BNPL revert).</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td>baseline</td><td>…</td><td><code>id</code>, <code>payment_transaction_id</code>, <code>booking_id</code>, <code>requested_by_customer_id</code>, <code>ticket_id</code>, <code>amount</code>, <code>refund_percentage</code>, <code>reason_category</code>, <code>reason_notes</code>, <code>status</code>, approval/rejection fields, <code>gateway_refund_reference</code>, <code>processed_at</code>, <code>admin_notes</code>, timestamps</td></tr>
|
||
<tr><td><strong>cardinality</strong></td><td></td><td><strong>CHANGED to 1:N</strong> per <code>payment_transaction</code> (app invariant: Σ refunded ≤ captured). The old "1:1" relationship summary was wrong.</td></tr>
|
||
<tr><td><code>platform_fee_refunded_irr</code></td><td>BIGINT</td><td><strong>NEW</strong> — fee-leg decomposition</td></tr>
|
||
<tr><td><code>nurse_payout_refunded_irr</code></td><td>BIGINT</td><td><strong>NEW</strong> — payout-leg decomposition (drives clawback if nurse already paid)</td></tr>
|
||
<tr><td><code>refund_channel</code></td><td>NVARCHAR(20)</td><td><strong>NEW</strong> — <code>psp_card</code> / <code>bnpl_revert</code> / <code>manual_bank</code></td></tr>
|
||
<tr><td><code>external_revert_reference</code></td><td>NVARCHAR(200) NULL</td><td><strong>NEW</strong> — BNPL revert id</td></tr>
|
||
<tr><td><code>expected_customer_refund_eta</code></td><td>DATE NULL</td><td><strong>NEW</strong> — the ~7–10 business-day BNPL window, surfaced in UI/reconciliation</td></tr>
|
||
<tr><td><code>cancellation_policy_code</code> / <code>refund_percentage_applied</code></td><td>…</td><td><strong>NEW</strong> — snapshot of the policy that produced this refund</td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> N:1 → <code>payment_transactions</code>, <code>bookings</code>, <code>customer_profiles</code>, <code>tickets</code>; 1:1 → <code>nurse_clawbacks</code> (only when refunding a booking whose nurse was already paid).</p>
|
||
<h3 id="ledger_entries-core-new"><code>ledger_entries</code> [CORE] — <strong>NEW</strong> <a class="anchor" href="#ledger_entries-core-new" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> The append-only, double-entry financial <strong>source of truth</strong>. Every money event posts <strong>balanced</strong> rows sharing a <code>transaction_group_id</code> (Σ debit = Σ credit). Per-nurse payable balance derives by filtering <code>account_type='nurse_payable'</code> + <code>nurse_id</code>. <strong>Why:</strong> 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.</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td><code>id</code></td><td>BIGINT PK</td><td></td></tr>
|
||
<tr><td><code>transaction_group_id</code></td><td>UNIQUEIDENTIFIER</td><td>Groups the balanced legs of one event</td></tr>
|
||
<tr><td><code>account_type</code></td><td>NVARCHAR(40)</td><td><code>escrow_held</code> / <code>platform_revenue</code> / <code>nurse_payable</code> / <code>refund_payable</code> / <code>bnpl_fee_expense</code> / <code>psp_fee_expense</code> / <code>nurse_clawback_receivable</code> / <code>bad_debt</code></td></tr>
|
||
<tr><td><code>nurse_id</code></td><td>BIGINT FK NULL</td><td>Set for <code>nurse_payable</code>/<code>nurse_clawback_receivable</code></td></tr>
|
||
<tr><td><code>direction</code></td><td>NVARCHAR(6)</td><td><code>debit</code> / <code>credit</code></td></tr>
|
||
<tr><td><code>amount_irr</code></td><td>BIGINT</td><td>Always positive; <code>direction</code> carries the sign</td></tr>
|
||
<tr><td><code>booking_id</code></td><td>BIGINT FK NULL</td><td></td></tr>
|
||
<tr><td><code>source_ref_type</code></td><td>NVARCHAR(40)</td><td><code>payment_transaction</code> / <code>refund</code> / <code>nurse_payout</code> / <code>bnpl_transaction</code> / <code>clawback</code></td></tr>
|
||
<tr><td><code>source_ref_id</code></td><td>BIGINT</td><td></td></tr>
|
||
<tr><td><code>memo</code></td><td>NVARCHAR(300) NULL</td><td></td></tr>
|
||
<tr><td><code>created_at</code></td><td>DATETIME2</td><td>Append-only; never updated</td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Canonical postings:</strong></p>
|
||
<div class="table-wrap"><table><thead><tr><th>Event</th><th>Debit</th><th>Credit</th></tr></thead><tbody>
|
||
<tr><td>Card capture</td><td><code>escrow_held</code> (gross)</td><td><code>platform_revenue</code> (commission) + <code>nurse_payable</code> (payout)</td></tr>
|
||
<tr><td>BNPL settle</td><td>as card, <strong>plus</strong> <code>bnpl_fee_expense</code> (provider commission)</td><td><code>escrow_held</code> (provider commission) → escrow reflects net cash actually received</td></tr>
|
||
<tr><td>Refund (pre-payout)</td><td><code>nurse_payable</code> + <code>platform_revenue</code> (decomposed legs)</td><td><code>refund_payable</code>; later <code>refund_payable</code> ↔ <code>escrow_held</code> on confirm</td></tr>
|
||
<tr><td>Clawback (post-payout)</td><td><code>nurse_clawback_receivable</code> (+ <code>platform_revenue</code> leg)</td><td><code>refund_payable</code></td></tr>
|
||
<tr><td>Clawback recovered</td><td><code>nurse_payable</code> (next batch)</td><td><code>nurse_clawback_receivable</code></td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> N:1 → <code>bookings</code>; logical links to <code>payment_transactions</code>/<code>refunds</code>/<code>nurse_payouts</code>/<code>bnpl_transactions</code> via <code>source_ref_*</code>.</p>
|
||
<h3 id="nurse_clawbacks-core-new"><code>nurse_clawbacks</code> [CORE] — <strong>NEW</strong> <a class="anchor" href="#nurse_clawbacks-core-new" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> A first-class receivable when a booking is refunded/disputed <strong>after</strong> the nurse was already paid. <strong>Why:</strong> 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 <code>dispute_window_ends_at</code> <strong>and</strong> record any post-payout recovery here.</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td><code>id</code></td><td>BIGINT PK</td><td></td></tr>
|
||
<tr><td><code>nurse_id</code></td><td>BIGINT FK → nurse_profiles</td><td></td></tr>
|
||
<tr><td><code>booking_id</code></td><td>BIGINT FK → bookings</td><td></td></tr>
|
||
<tr><td><code>refund_id</code></td><td>BIGINT FK → refunds</td><td></td></tr>
|
||
<tr><td><code>original_payout_id</code></td><td>BIGINT FK → nurse_payouts NULL</td><td></td></tr>
|
||
<tr><td><code>amount_irr</code></td><td>BIGINT</td><td></td></tr>
|
||
<tr><td><code>status</code></td><td>NVARCHAR(30)</td><td><code>pending</code> / <code>recovered</code> / <code>written_off</code></td></tr>
|
||
<tr><td><code>recovered_in_payout_id</code></td><td>BIGINT FK NULL</td><td>Batch that netted it</td></tr>
|
||
<tr><td><code>created_at</code>, <code>resolved_at</code></td><td>DATETIME2</td><td></td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> N:1 → <code>nurse_profiles</code>, <code>bookings</code>; 1:1 → <code>refunds</code>; → <code>nurse_payouts</code> (original and recovering).</p>
|
||
<h3 id="invoices-mvp-new"><code>invoices</code> [MVP] — <strong>NEW</strong> <a class="anchor" href="#invoices-mvp-new" aria-hidden="true">#</a></h3>
|
||
<p><strong>Role:</strong> Minimal official-invoice/receipt record per booking. <strong>Why:</strong> Iranian commission marketplaces face VAT/مودیان obligations on <strong>their commission</strong> (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. <strong>VAT is 10%</strong> (rose from 9% in 1403) and stored as a <strong>config-driven rate</strong> 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.</p>
|
||
<div class="table-wrap"><table><thead><tr><th>Field</th><th>Type</th><th>Notes</th></tr></thead><tbody>
|
||
<tr><td><code>id</code></td><td>BIGINT PK</td><td></td></tr>
|
||
<tr><td><code>booking_id</code></td><td>BIGINT FK → bookings</td><td></td></tr>
|
||
<tr><td><code>invoice_number</code></td><td>NVARCHAR(40) UNIQUE</td><td>Official sequential number</td></tr>
|
||
<tr><td><code>issuing_entity_type</code></td><td>NVARCHAR(20)</td><td><code>platform</code> / <code>partner_center</code></td></tr>
|
||
<tr><td><code>gross_irr</code></td><td>BIGINT</td><td></td></tr>
|
||
<tr><td><code>platform_commission_irr</code></td><td>BIGINT</td><td>The VAT-relevant line</td></tr>
|
||
<tr><td><code>bnpl_commission_irr</code></td><td>BIGINT NULL</td><td></td></tr>
|
||
<tr><td><code>vat_rate</code></td><td>DECIMAL(5,4)</td><td>Config-driven (0.10)</td></tr>
|
||
<tr><td><code>vat_irr</code></td><td>BIGINT</td><td></td></tr>
|
||
<tr><td><code>moadian_reference_number</code></td><td>NVARCHAR(40) NULL</td><td>سامانه مودیان 22-digit ref when issued</td></tr>
|
||
<tr><td><code>moadian_status</code></td><td>NVARCHAR(20) NULL</td><td></td></tr>
|
||
<tr><td><code>pdf_storage_key</code></td><td>NVARCHAR(512) NULL</td><td></td></tr>
|
||
<tr><td><code>issued_at</code></td><td>DATETIME2</td><td></td></tr>
|
||
</tbody></table></div>
|
||
<p><strong>Relations:</strong> 1:1 → <code>bookings</code>; N:1 → <code>partner_centers</code> (when issuer).</p>
|
||
<a class="back-to-top" href="#">↑ Back to top</a>
|
||
</div></main>
|
||
</div>
|
||
<script>
|
||
(function(){var k='balinyaar-docs-theme';var s=localStorage.getItem(k);
|
||
if(s)document.documentElement.setAttribute('data-theme',s);
|
||
else if(matchMedia('(prefers-color-scheme: dark)').matches)document.documentElement.setAttribute('data-theme','dark');})();
|
||
function __t(){var d=document.documentElement;var n=d.getAttribute('data-theme')==='dark'?'light':'dark';
|
||
d.setAttribute('data-theme',n);localStorage.setItem('balinyaar-docs-theme',n);}
|
||
</script>
|
||
|
||
</body>
|
||
</html>
|