​
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.

​
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 via /users/{user_id}/balance and /users/{user_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

Mandate / Order owns

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

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 as id_proof_type + id_proof_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. 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).