> ## Documentation Index
> Fetch the complete documentation index at: https://docs.useduro.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Model

> Three Postgres schemas, two Prisma clients, and an entity map you can hold in your head.

Duro's data lives in one Postgres 16 database, partitioned into **three schemas**. This is the single most important structural decision in the system, so it gets its own page.

## Three schemas, two clients

```mermaid theme={null}
flowchart TB
    subgraph db["One Postgres database"]
        subgraph core["schema: core"]
            T["tenants · members · sessions<br/>kyc_documents · audit_logs<br/>store_configs · email_templates<br/>notifications · customer_identities<br/>otp_challenges · saved_payment_methods"]
        end
        subgraph sandbox["schema: sandbox (test mode)"]
            S["plans · customers · subscriptions<br/>invoices · payment_attempts<br/>dunning_schedules · promo_codes<br/>payment_links · webhook_endpoints<br/>webhook_deliveries · events ·<br/>checkout_sessions · store_settings"]
        end
        subgraph live["schema: live (live mode)"]
            L["…identical tables to sandbox…"]
        end
    end

    CC["core client<br/>@duro/db → generated/core"] --> core
    DC["data client<br/>@duro/db → generated/data"] --> sandbox
    DC --> live
```

* **`core`** — tenant identity and everything mode-agnostic: who the merchant is, their team, their KYC, their branding, their customers' phone identities. A business is the same business whether it's testing or live.
* **`sandbox`** and **`live`** — the *business* data, duplicated per mode. A plan, a subscription, an invoice exists in exactly one mode. There is **no `mode` column.** Test data lives in a different schema than live data, full stop.

Two generated Prisma clients back this: `database.core` for the core schema, and `database.data(mode)` which returns a client pointed at `sandbox` or `live`. A controller that holds a `data('test')` client physically cannot read live rows — the connection's `search_path` doesn't include the live schema.

<Note>
  This is isolation **by construction**, not by convention. The classic bug — forgetting `WHERE mode = 'live'` in one query out of three hundred — is impossible here, because the column doesn't exist. The schema boundary is the filter.
</Note>

## The core schema: who you are

```mermaid theme={null}
erDiagram
    Tenant ||--o{ Member : "has team"
    Tenant ||--o{ Session : "active logins"
    Tenant ||--o{ KycDocument : "CAC, MEMART, status report"
    Tenant ||--o| StoreConfig : "appearance draft/published"
    Tenant ||--o{ EmailTemplate : "overrides"
    Tenant ||--o{ Notification : "in-app bell"
    Tenant ||--o{ AuditLog : "who did what"
    CustomerIdentity ||--o{ SavedPaymentMethod : "cross-merchant wallet"
    CustomerIdentity ||--o{ OtpChallenge : "WhatsApp verification"

    Tenant {
        string id PK
        string name
        KycTier tier "unverified | verified"
        KycStatus kycStatus "none | review_pending | approved | rejected"
    }
    Member {
        string id PK
        string email UK
        MemberRole role "owner|admin|developer|finance|read_only"
    }
    CustomerIdentity {
        string id PK
        string phone UK "the universal key"
        datetime verifiedAt
    }
```

`CustomerIdentity` is deliberately **not** scoped to a tenant. It is the cross-merchant wallet — keyed by phone, verified over WhatsApp, holding payment methods reusable everywhere. [See identity →](/identity/universal-identity)

## The data schema: the money

Every table below exists once in `sandbox` and once in `live`. Every row carries a `tenantId` so a single schema holds all tenants, partitioned by composite index.

```mermaid theme={null}
erDiagram
    Plan ||--o{ Subscription : "subscribed to"
    Plan ||--o{ PaymentLink : "shareable"
    Plan ||--o{ PromoCode : "scoped discount"
    Customer ||--o{ Subscription : "owns"
    Customer ||--o{ PaymentMethod : "tokens"
    Subscription ||--o{ Invoice : "billed"
    Invoice ||--o{ PaymentAttempt : "charge tries"
    Invoice ||--o| DunningSchedule : "if it fails"
    WebhookEndpoint ||--o{ WebhookDelivery : "fans out"

    Plan {
        int amount "minor units (kobo)"
        PlanInterval interval "hour…year"
        int intervalCount
        int trialDays
        int maxCycles "null = forever"
    }
    Subscription {
        SubscriptionStatus status
        datetime currentPeriodStart
        datetime currentPeriodEnd
        int cyclesCompleted
        json scheduledChange
        datetime renewalReminderSentAt
    }
    Invoice {
        InvoiceStatus status "draft|open|paid|uncollectible|void"
        int amount
        string idempotencyKey "per (tenant, period)"
    }
    DunningSchedule {
        DunningState state "scheduled|in_flight|recovered|exhausted|paused"
        int attemptsMade
        Rail rail "card|transfer|ussd|virtual_account|direct_debit"
        bool paydayAware
    }
```

A few choices worth calling out:

<AccordionGroup>
  <Accordion title="Money is integer minor units" icon="coins">
    Every amount is an `Int` of kobo — ₦5,000.00 is `500000`. No floats anywhere near money. Currency is a sibling field, default `NGN`, with per-plan FX supported.
  </Accordion>

  <Accordion title="The invoice idempotency key prevents double-billing" icon="key">
    An invoice's `idempotencyKey` is `renewal_{subscriptionId}_{periodStart}`, unique per `(tenantId, idempotencyKey)`. The renewal scanner can fire twice for the same period and the second insert is rejected by the database, not by application logic.
  </Accordion>

  <Accordion title="The dunning schedule is one-to-one with the invoice" icon="rotate-right">
    A failing invoice gets exactly one `DunningSchedule` carrying the whole recovery state — attempts made, the chosen rail, whether to be payday-aware, the last action taken. It is the recovery state machine's memory.
  </Accordion>

  <Accordion title="Events are the spine" icon="bolt">
    Every meaningful change writes a row to `events`. The webhook system consumes that table; the dashboard activity feed reads it; analytics aggregate it. One append-only log, many readers. [See webhooks →](/webhooks/delivery)
  </Accordion>
</AccordionGroup>

## Indexing for the hot paths

Reads that happen on every request or every scan are indexed to match:

| Query                                            | Index                                |
| ------------------------------------------------ | ------------------------------------ |
| Cursor pagination of any list                    | `(tenantId, createdAt, id)` compound |
| Renewal scan: due subscriptions                  | `(status, currentPeriodEnd)`         |
| Dunning scan: retries due now                    | `(state, nextAttemptAt)`             |
| Portal: a phone's subscriptions across merchants | `(phone)` on customers               |
| Checkout: reuse a customer by contact            | `(tenantId, email)`                  |
| A customer's transactions                        | `(customerId)` on payment\_attempts  |

Pagination is always **keyset** (cursor on `[createdAt desc, id desc]`), never `OFFSET` — so page 500 costs the same as page 1.

Next: the [request lifecycle](/architecture/request-lifecycle) — how a raw HTTP request becomes a tenant-scoped, authenticated, idempotent operation.