Entity Relationship Diagram

IDs follow a strict convention: users.id is a 12-digit numeric VARCHAR(12); every other entity uses a native PostgreSQL uuid (v7). All monetary columns are BIGINT minor units (INR paise). The schema below is a subset focused on relationships — the authoritative definition is backend/crates/diesel_models/src/schema.rs.
benefit_entities.entity_id and benefit_provider_orders.benefit_entity_id are polymorphic — they reference either an insurance_policies.id or a consultations.id. Both are bare uuid columns; the entity’s type is recorded once on benefit_entities.entity_type. There is no database foreign key on those columns; referential integrity is enforced at write time.clinic.clinic_queue lives in a separate Postgres schema — the only non-public one — and therefore carries no foreign keys at all, including to benefit_entities. Its columns are denormalised at admission rather than joined, because Diesel emits allow_tables_to_appear_in_same_query! per schema and a cross-schema join does not compile.

Domain Ownership

Auth / User owns

  • users — created at provisioning by POST /auth/token
  • user_key_store — per-user encryption key for PII at rest
  • dependants — family members (lineage tracked via previous_version_id)

Account owns

  • accounts — the PBA holder row (holder keys a user:<id> or sponsor:<id>)
  • orders — funding ledger; self-contribution or sponsor-funded
  • Balance and ledger are read from the PBA per goal via /goals/{goal_id}/balance and /goals/{goal_id}/ledger

Insurance Policy owns

  • insurance_policies — purchased policies, dependant_ids array for coverage
  • References a benefit (the catalog lives in benefits, not a separate insurance_plans table)

Benefit / Provider owns

  • benefit_providers, benefits — provider catalog + benefit definitions
  • benefit_entities — append-only utilization ledger (one row per issued policy / consultation)
  • benefit_provider_orders — provider-side funding ledger
  • clinic.clinic_queue — the clinic front-desk queue (own schema; tracks the visit, not the entitlement)

Mandate / Order owns

  • mandates — Juspay UPI autopay authorization
  • mandate_executions — one row per scheduled debit (idempotent on idempotency_key)

Consultation owns

  • consultations — chat-doctor sessions tied to a dependant + consultation benefit
  • mrns — provider-issued medical record numbers per dependant

PII & Money Storage Policy

These rules are non-negotiable and enforced at the application layer.
Identity is captured in the optional id_proof text column (<type>:<number>), and auth is a stateless bearer JWT.

Soft-Delete & Timestamps

Every domain table uses a status enum for soft-delete — there is no deleted_at column. Records are retired by transitioning status (e.g. active → inactive, active → discarded, active → deactivated) rather than being physically removed. Each table carries created_at and last_modified_at (not updated_at). The append-only ledgers (benefit_entities) are the exception — they have only created_at and no status. clinic.clinic_queue has both, but retires a row by reaching the terminal COMPLETED rather than an inactive variant. Every domain table feeds public.event_log through an *_audit trigger that calls public.event_logger(), capturing the action (INSERT/UPDATE/DELETE), the old/new row as JSON, and the originating query. event_log is RANGE-partitioned by timestamp (monthly partitions).

Account Status Lifecycle

AccountStatus is pending | active | inactive. account_type is savings | hsa | education | sponsor. The holder column (URN like user:<id> or sponsor:<id>) ties an account to its owner — there is no direct user_id foreign key on accounts.

Order Lifecycle

orders carries two status fields: status (the internal OrderStatus lifecycle) and external_status (the free-form Juspay string, NULL until the first poll). The wallet_txn_data column records the PBA transaction outcome (pending, success:<pba_txn_id>, or failed). EXPRESSED and DECLINED are tip-only and sit outside the main arc. Every other funding flow starts at INITIATED.