Database Model

Revision 2 — 2026-06-20. This is a research-driven refinement of the original 13-domain model. It closes the financial-correctness gaps the previous version flagged in its own _Advices_ section, resolves the two open BNPL questions, and grounds every money decision in verified research on the Iranian payment landscape (SnappPay, Digipay, Tara, Torob Pay, Shaparak/پرداخت‌یار rules). The previous revision is preserved in git history.

Companion documents: business requirements (per-section product requirements) and payments deep-dive (the BNPL/escrow deep-dive with sources).

Contents #

• Diagrams
• Domain 1 — Identity & Access
• Domain 2 — Geographic Data
• Domain 3 — Services & Pricing
• Domain 4 — Verification & Credentials
• Domain 5 — Booking & Scheduling
• Domain 6 — Payments, Ledger & Refunds
• Domain 7 — Payouts to Nurses
• Domain 8 — BNPL / Installments
• Domain 9 — Messaging (Ticket System)
• Domain 10 — Reviews & Patient Records
• Domain 11 — Notifications
• Domain 12 — Audit, Config & Reference
• Domain 13 — Partner Centers (launch) & Future

---

Platform Summary #

Balinyaar is a trust-first home-nursing marketplace in Iran. Independent, individually-verified nurses register, list configurable services with their own pricing, and pass a multi-step verification pipeline anchored on the MoH پروانه صلاحیت حرفه‌ای (professional-competency license). Families search — filtered by city/district and same-gender caregiver preference — pick a nurse and a service variant, submit a booking request, and pay through the platform after the nurse accepts. The platform records the money as an internal escrow ledger state (not platform-held cash — see Principle 2), the nurse performs one or more EVV-verified visits, and the platform pays the nurse weekly, after the dispute window closes, minus a platform commission. All post-booking communication runs through an admin-readable ticket system.

At launch the platform operates under a partner licensed home-nursing center (مرکز مشاوره و ارائه مراقبت‌های پرستاری در منزل) — the Asanism-style model — which is the legal vehicle and the likely merchant-of-record for payments while Balinyaar's own MoH permit is in process.

---

What changed in this revision (decision summary) #

---

Design Principles #

1. Money is BIGINT in Iranian Rials (IRR). Toman is a display concern only; conversion happens only at a provider's API boundary (e.g. SnappPay quotes Toman) and never internally. No floats anywhere on the money path.
2. The platform never legally holds buyer cash. Funds settle through a licensed PSP/پرداخت‌یار to registered IBANs (the platform's commission IBAN and the nurse's IBAN, via تسهیم settlement-sharing, or to one merchant-of-record account). "Escrow" and "nurse balance" are derived ledger states over money custodied at the provider/partner bank — represented in ledger_entries, never as a Balinyaar-owned cash balance.
3. ledger_entries is the financial source of truth. Every capture, commission, payout, refund, and clawback posts balanced double-entry rows. Per-table money fields (e.g. bookings.gross_price_irr) remain the operational/pricing record; the ledger is the reconciliation truth that answers "how much do we owe nurses right now" and "how much is held but unreleased."
4. Fee split is captured per booking and never derived from live config, so historical reporting survives commission-schedule changes. The booking stores three distinct amounts: gross_price_irr, balinyaar_commission_irr, nurse_payout_amount.
5. PII fields (national ID, IBAN, phone, addresses, clinical data) are marked (encrypted) — column- or application-level. Clinical data has stricter access than financial data.
6. Two-stage clinical disclosure is a hard rule, not a convention. At the request stage the nurse sees only booking_requests.customer_notes. The full encrypted booking_care_instructions are exposed only after the booking is confirmed. Enforced at the authorization layer.
7. Soft deletes on users/nurse_profiles via deleted_at. Audit, payment, ledger, and payout records are never deleted.
8. Audit trail is append-only. All state transitions on bookings, payments, refunds, payouts, verifications, reviews, and platform_configs produce an audit_logs row.
9. Catalog/config tables are rows, not enums (service categories, verification step types, cancellation policies, Iranian holidays) so the business evolves without migrations. They carry name_fa/name_en.
10. Idempotency is mandatory on the money path. Every PSP/BNPL callback is stored raw in payment_webhook_events and deduplicated on external_event_id before any money-state mutation.
11. All timestamps are DATETIME2(7) UTC. Persian-calendar display is a UI concern — except that bank-closure scheduling uses the iranian_holidays table, because PAYA/SATNA transfers fail on holidays.
12. Derived flags must not drift. nurse_profiles.is_verified, denormalized rating aggregates, and the search index are written only by the code path that owns their source of truth, inside the same transaction.
13. Invariants are enforced, not just documented: CHECK constraints (gross = commission + payout, rating BETWEEN 1 AND 5, amounts ≥ 0, end_time > start_time), filtered-UNIQUE for "one primary"/"one active", and tenancy checks (a booking's patient/address must belong to the same customer; its variant to the same nurse).

---

The two questions this revision answers #

These were the two hardest open questions (from whatsInYourMind.txt). Both are resolved against verified research that all mainstream Iranian provider-financed BNPLs use full-upfront settlement — the provider pays the merchant the whole amount minus commission and owns the customer's installments and default risk.

Q1 — A booking paid by installments (BNPL) is cancelled or refunded mid-plan. What happens? #

Money always flows customer ↔ BNPL provider ↔ Balinyaar — never nurse→customer, and never Balinyaar→customer directly for a BNPL order.

1. Balinyaar initiates the reversal through the provider's API (SnappPay revert for full / cancel/update for partial, using the stored external_payment_token).
2. The provider then cancels the customer's unpaid installments, restores their credit, and refunds any already-paid installment to the customer's bank account in ~7–10 business days (asynchronous, owned by the provider).
3. Balinyaar records a refunds row with refund_channel = 'bnpl_revert', carrying external_revert_reference and expected_customer_refund_eta; refund_status stays processing until a reconciliation job confirms.
4. The refund decomposes across the two fee legs — platform_fee_refunded_irr and nurse_payout_refunded_irr — and posts balanced ledger entries.
5. If the nurse has not yet been paid (still inside the dispute window / not in a processed batch): the nurse_payable accrual is simply reversed; nothing leaves Balinyaar. Clean.
6. If the nurse has already been paid: this is the clawback path — a nurse_clawbacks receivable + negative ledger entry; recovered from the next payout batch or written off.

A shortened/partial visit maps to the provider's update endpoint with a reduced amount; record refund_delta_irr and reduce bnpl_transactions.settled_amount_irr.

Q2 — Under BNPL, who pays the nurse and when? #

Balinyaar pays the nurse, on its own normal weekly payout schedule, after EVV completion and after the dispute window closes — exactly the same path as a card-funded booking. The BNPL provider never pays the nurse and is indifferent to the internal split.

The nurse's payout is computed from the booking's gross_price_irr minus balinyaar_commission_irr — never from the BNPL provider's net settled_amount_irr. The provider's commission (bnpl_commission_irr) is a platform cost of accepting BNPL, borne by Balinyaar, and must never touch the nurse's payout. Hence three separately stored amounts:

nurse_payout_amount = gross_price_irr − balinyaar_commission_irr. The nurse receives the identical amount and on the identical schedule whether the family paid by card or by SnappPay.

---

Relationship Summary #

---

Final MVP table list #

Identity & Access: users · user_sessions · roles · user_roles · nurse_profiles · customer_profiles · patients · customer_addresses · nurse_bank_accounts — all [CORE]

Geography: provinces · cities [CORE] · districts [MVP] · nurse_service_areas [CORE]

Services & Pricing: service_categories · service_option_groups · service_option_values · nurse_service_variants · nurse_service_variant_options · nurse_search_index (NEW) — [CORE]; nurse_availability_slots · nurse_availability_exceptions — [MVP]

Verification: nurse_verifications · verification_step_types · verification_steps · verification_documents [CORE]; nurse_credentials (NEW) [MVP]

Booking & Scheduling: booking_requests · bookings · booking_care_instructions · visit_verifications [CORE]; booking_sessions (NEW) · cancellation_policies (NEW) [MVP]

Payments & Ledger: payment_gateways · payment_transactions · payment_webhook_events (NEW) · refunds · ledger_entries (NEW) · nurse_clawbacks (NEW) · nurse_payout_batches · nurse_payouts · nurse_payout_booking_links [CORE]; invoices (NEW) [MVP]

BNPL: bnpl_transactions (NEW — replaces installment_plans) [MVP]; installment_plans · installment_entries CUT; bnpl_settlement_entries [DEFERRED]

Messaging: tickets · ticket_participants · ticket_messages [CORE]

Reviews & Records: reviews [CORE]; review_tags_master · review_tag_links · patient_care_records [MVP]

Notifications: notifications · support_alerts [CORE]

Audit & Config: audit_logs [CORE] · system_events [MVP] · platform_configs [CORE] · iranian_holidays (NEW) [MVP]

Partner / Launch: partner_centers (NEW) [MVP]

Future (modeled, inactive): organizations · organization_nurses · fraud_flags · recurring_booking_schedules · bnpl_settlement_entries — all [DEFERRED]

Net change vs the original 45: −2 cut (installment_plans replaced, installment_entries removed), +10 added (ledger_entries, nurse_clawbacks, payment_webhook_events, nurse_search_index, booking_sessions, cancellation_policies, invoices, partner_centers, nurse_credentials, iranian_holidays), 1 replaced (bnpl_transactions). The financial core is now a single ledger, BNPL is one settlement row, and the clawback / dispute-window / idempotency / license / multi-session gaps are all closed.

---

Key Design Decisions (the reasoning, in one place) #

1. Escrow as a ledger state, not platform cash — because an Iranian پرداخت‌یار legally cannot custody buyer funds. Everything else in the money domain follows from honestly representing "we don't hold the cash; we hold a claim/obligation tracked in the ledger over funds at a licensed provider." This is also why payouts are modeled as provider-side settlement to verified, ownership-checked IBANs.

1. A BNPL order is a net-of-fee inbound payment, full stop — the verified full-upfront settlement model means there is no customer receivable, no default risk, and no installment schedule for Balinyaar to track. Deleting installment_entries removed an entire fragile subsystem and replaced it with one reconciliation row.

1. Three separate money amounts so the platform's two fee deductions (its own commission, and the BNPL provider's discount) are never conflated, and the nurse is paid identically regardless of payment method.

1. Double-entry over status flags — the previous model could not answer "how much do we owe nurses right now" without fragile joins, and had nowhere to record a refund-after-payout. One append-only ledger + a nurse_clawbacks receivable fixes both and makes bank/Shaparak reconciliation possible.

1. Dispute window gates payout — preferring a holding period over a clawback, because clawback against an already-paid nurse IBAN is largely unenforceable. The clawback path exists for the cases that slip through.

1. Idempotency before money — payment_webhook_events keyed on the provider event id, written first, is the cheapest insurance against the most damaging payments bug (double-confirm / double-settle on callback retries).

1. Multi-session engagements are the norm, not an edge case — booking_sessions makes long elder-care arrangements representable, lets escrow release per completed visit instead of holding a month of money, and makes mid-engagement cancellation accounting clean.

1. Partner center is launch-critical — it is the legal vehicle and likely merchant-of-record; without it the recommended go-to-market and the money flow are not representable.

1. Verified-trust must be queryable — nurse_credentials turns the brand promise into renewal alerts, a real badge, and audit defensibility, surviving the future arrival of an INO/MoH API.

1. Keep the configurable service EAV; cut the analytics scaffolding — the category/option model earns its complexity (admin-extensible pricing dimensions without migrations); response_rate/profile_completion_score/system_events-in-SQL do not, at launch.

---

Open items to confirm before building (not schema blockers) #

• BNPL provider contract: does SnappPay/Digipay permit a multi-vendor marketplace re-disbursing to many nurses as a single merchant? (Publicly undocumented — confirm with sales.) The schema assumes one lump to Balinyaar/the center, internal allocation to nurses, so this is an ops confirmation, not a schema dependency.
• Commission % and settlement SLA per provider (and whether the provider returns its commission on a refund — full or pro-rata).
• PSP/تسهیم provider for MVP (ZarinPal Multiplexing vs Vandar vs Jibit) and whether it permits the hold-then-weekly-payout timing, or whether a bank-grade escrow (Vandar میندو) is needed.
• VAT exemption ruling on the nursing service itself (the commission line is taxable regardless) — vat_rate is config-driven so either ruling is a value change.
• مودیان enrollment thresholds for the platform and high-earning nurses.

Confirm decades-old regulations, provider fee/settlement specifics, and tax thresholds against current primary sources and the provider's compliance team before building the payment integration. See the payments deep-dive for the full source-cited analysis.

↑ Back to top