Parivar — Data Model & Schema Reference

Companion to the Technical Architecture blueprint. Authoritative source for tables, columns, enums, and relationships in the NestJS + Postgres rebuild.

This document is reconstructed from the live-database exports (database_enums.csv, database_constraints.csv) and the entity-relationship diagram (Parivar_ERD.mmd), cross-checked against the role-based API reference and the implementation guides. Where the older API reference disagreed with the live schema, the live schema wins and the discrepancy is flagged with ⚠️.

Scope note: only application tables are documented. Supabase-internal schemas (auth.*, storage.*, realtime, vault) are replaced wholesale in the rebuild — see §9.

---

1. How the data model maps to the rebuild

Concern	Current (Supabase)	Rebuild (NestJS + Postgres)
Schema ownership	public schema + Supabase-managed auth/storage	Single app-owned Postgres; one ORM schema (Prisma or TypeORM)
Identity table	auth.users (Supabase)	Own users table (or external IdP) + profiles 1:1
Enums	Postgres enum types	Keep as Postgres enums or ORM-level enums + check constraints
FKs / cascades	As listed in §8	Preserve every FK; make cascade/SET NULL explicit per §8
Soft delete	deleted_at, is_disabled, is_active, status columns	Same columns; enforce in repository base query (global scope)
Audit	customer_activity_log, location_audit_log, assessment_versions, visit_events	Keep; consider an append-only audit module

🔁 Migration note. In Supabase the client queried public.* directly under RLS. In the rebuild no table is client-reachable; every read/write goes through a NestJS service. The ownership columns below (user_id, client_user_id, nurse_id, parent_id, created_by) stop being RLS predicates and become explicit where clauses in services.

---

2. Domain map (table inventory)

41 application tables, grouped into nine bounded contexts. Each maps to the NestJS module noted.

Domain	Tables	Module
Identity & Access	profiles, user_roles, otp_verifications, staff_applications, customer_admin_details, nurses, doctors, reactivation_requests	AuthModule, UsersModule, NurseModule, DoctorModule
Patients	parents, parent_health_info, parent_emergency_contacts, admission_forms	ParentsModule
Visits & Scheduling	visits, visit_events, nurse_working_hours, nurse_leave_requests, monthly_contracts	VisitsModule, NurseModule
Clinical	assessments, assessment_versions, first_visit_assessments, wound_care_forms, clinical_modules, email_notifications	ClinicalModule
Escalations & Safety	escalations, sos_alerts, nurse_locations, location_audit_log	EscalationsModule, NurseModule, AlertsModule
Hospital Coordination	hospital_requests, escort_tasks	HospitalModule
Communication	conversations, conversation_participants, messages, voice_messages, notifications, admin_alerts	NotificationsModule, PariAIModule, AlertsModule
Nurse Operations	nurse_certifications, nurse_document_signatures, nurse_preferences, nurse_bank_details, didit_webhook_events	NurseModule
Billing & Referrals	subscription_plans, subscriptions, payments, withdrawal_requests, referral_accounts, referrals, referral_commissions, referral_settings, referral_wallet_transactions, customer_activity_log	BillingModule, ReferralsModule

---

3. Enumerated types (application domain)

These are the real enum values from database_enums.csv. Use them verbatim — several differ from the older API reference.

Enum	Values	Used by
app_role	customer, nurse, admin, doctor	user_roles.role, RBAC guards
visit_status	pending_admin_assignment, assigned_to_nurse, in_progress, completed, cancelled, report_submitted, approved	visits.status
visit_type	routine, follow_up	visits.visit_type
escalation_type	doctor_review, urgent_health, medication_issue, fall_risk, hospital_support, family_followup, admin_support	escalations.escalation_type
escalation_status	open, under_review, actioned, closed	escalations.status
escalation_severity	low, medium, high, critical	escalations.severity, admin_alerts.severity
general_condition	normal, weak, dizzy, other, stable, improving, deteriorating, critical	assessments.general_condition
blood_sugar_type	fasting, random	assessments.blood_sugar_type, first_visit_assessments.blood_sugar_type
appetite_status	normal, reduced, poor	first-visit / clinical intake

⚠️ Discrepancy vs. the old API reference.

• visit_type is routine / follow_up — not regular / first_assessment / on_demand. "First visit" is determined by the absence of a first_visit_assessments row for the parent (see Clinical Reporting doc §2), not by a visit_type. On-demand bookings are a billing path, not an enum value.
• escalation_type is the seven clinical categories above — not clinical / safety / logistical.
• escalation_status is open → under_review → actioned → closed — not open → in_review → resolved.
• general_condition is a richer set than the old stable/improving/deteriorating/critical.

Update DTOs, validators, and any UI dropdowns to these values.

Supabase-internal enums to drop: aal_level, factor_status, factor_type, code_challenge_method, oauth_*, one_time_token_type, equality_op, buckettype, request_status, action are Supabase/Postgres system types and have no place in the rebuilt app schema (their concerns are handled by the Auth module, object storage, and framework layers).

---

4. Identity & Access

profiles — 1:1 with the auth user

Primary user record. user_id is unique and FKs the auth identity.

Column	Type	Notes
id	uuid PK
user_id	uuid FK→users, unique	Auth identity
name	text
email	text
phone	text
is_disabled	bool	Master disable flag (see Account Lifecycle doc)
disabled_at	timestamptz
disabled_reason	text	e.g. self_deactivation
review_request_count	int	Reactivation requests counter

user_roles — role assignment (1:many)

Unique on (user_id, role). A user may hold multiple roles. RBAC reads from here.

Column	Type	Notes
id	uuid PK
user_id	uuid FK→profiles
role	app_role	customer / nurse / admin / doctor

nurses — workforce record

Unique on email and user_id. Scoping key for most nurse data (nurses.user_id = auth.uid()).

Column	Type	Notes
id	uuid PK	Referenced as nurse_id everywhere
user_id	uuid FK→profiles, unique
name	text
email	text, unique
status	text	e.g. available, on_visit, deactivated
is_active	bool
deactivated_at	timestamptz

doctors — clinical-oversight record

Unique on user_id. PK id referenced as doctor_id.

staff_applications — nurse/doctor onboarding

Unique on email and user_id. Application + KYC pipeline source; supports a reactivation application type (Account Lifecycle doc §7).

customer_admin_details — admin-managed customer metadata

Unique on user_id.

otp_verifications — login/2FA codes

6-digit code, 10-min TTL, max 5/email/hour (move rate-limit to Redis — see Architecture doc).

reactivation_requests — disabled-user review requests

Tracks user_id, email, status (pending/approved), created_at. Enforces the 3-requests-per-7-days rule.

---

5. Patients

parents — the patient (elder in Nepal)

Owned by a customer via user_id. Soft-deleted via deleted_at.

Column	Type	Notes
id	uuid PK	parent_id everywhere
user_id	uuid FK→profiles	Owning customer
name	text
age	int	(ERD shows age; old ref showed date_of_birth — confirm which is stored)
relationship	text	mother/father/…
deleted_at	timestamptz	Soft delete

Likely additional columns from the API reference: city, landmark, lat, lng.

admission_forms — long-form intake (1:many on parent, typically 1)

FK parent_id. Holds conditions, medications, allergies (jsonb), and is_diabetic (boolean) which drives conditional blood-sugar capture in reports.

parent_health_info — structured health summary

FK parent_id, unique on parent_id (1:1).

parent_emergency_contacts — contacts (1:many)

FK parent_id.

---

6. Visits & Scheduling

visits — the booking + execution record

Central table. FKs parent_id, nurse_id (null until assigned). client_user_id denormalises the owning customer for scoping.

Column	Type	Notes
id	uuid PK
parent_id	uuid FK→parents
nurse_id	uuid FK→nurses	Null until assigned
client_user_id	uuid	Owning customer (scoping key)
status	visit_status	Lifecycle (see §3)
visit_type	visit_type	routine / follow_up
scheduled_date	date

Lifecycle: pending_admin_assignment → assigned_to_nurse → in_progress → report_submitted → approved; plus completed (ended without report) and cancelled. Admin rejection of a report sends report_submitted → in_progress.

visit_events — append-only timeline

FK visit_id. One row per status transition / actor action.

nurse_working_hours — weekly availability

FK nurse_id. day_of_week (0–6), start_time < end_time.

nurse_leave_requests — leave

FK nurse_id. Full-day/partial; overlap with active visits blocked (409 LEAVE_CONFLICT). Admins may create on behalf of nurses.

monthly_contracts — recurring engagement

FK nurse_id. Recurring/contracted visit arrangement.

---

7. Clinical, Escalations, Hospital, Communication, Nurse Ops, Billing

The clinical tables (assessments, assessment_versions, first_visit_assessments, wound_care_forms, clinical_modules, email_notifications) carry the richest field sets and a full submission/versioning workflow — they are documented in detail in the Clinical Reporting Module companion doc. Summary here:

Table	Cardinality	Key columns	Notes
assessments	per visit (regular report)	visit_id, parent_id, nurse_id, approval_status, is_draft, version_count	Mandatory report; draft + 24h edit window
assessment_versions	per edit	assessment_id, nurse_id, version_number, snapshot jsonb	Audit of each submit/edit
first_visit_assessments	one per parent	parent_id, nurse_id, visit_id, is_locked	Comprehensive intake; lock-once
wound_care_forms	many per visit	parent_id, visit_id, nurse_id, wound_type	Additive wound records
clinical_modules	optional per visit	visit_id, nurse_id, parent_id	Optional clinical add-ons
email_notifications	per assessment event	assessment_id, parent_id, visit_id	Outbound report emails log

Escalations & safety

Table	FKs	Notes
escalations	visit_id, parent_id, nurse_id	escalation_type (7 values), status (open→under_review→actioned→closed), severity
sos_alerts	nurse_id, patient_parent_id	Nurse SOS with lat/lng; fans out admin email + realtime
nurse_locations	nurse_id (unique, 1 row/nurse)	Live GPS; >5 min stale → admin alert
location_audit_log	nurse_id	Historical GPS trail

⚠️ The old API reference's standalone incidents table is not in the live schema. Risk/safety events are modelled via escalations (fall_risk, urgent_health, etc.) and admin_alerts. Drop the incidents concept unless a new table is intentionally added.

Hospital coordination

Table	FKs	Notes
hospital_requests	parent_id	Customer-raised coordination request
escort_tasks	hospital_request_id, parent_id	Admin-managed escort task

Communication

Table	FKs	Notes
conversations	—	Chat thread (PariAI + human)
conversation_participants	conversation_id, user_id	Unique (conversation_id, user_id)
messages	conversation_id, voice_message_id, sender_id→profiles	Text + voice
voice_messages	—	Transcribed audio (Whisper)
notifications	related_visit_id, user_id	Per-user in-app feed
admin_alerts	7 related_* FKs (visit, admission_form, assessment, escalation, escort_task, hospital_request, parent)	Deduped ops alerts; severity-tagged

Nurse operations

Table	FKs	Notes
nurse_certifications	nurse_id	Unique (nurse_id, certification_name)
nurse_document_signatures	nurse_id	Unique (nurse_id, document_type); 3 mandatory docs
nurse_preferences	nurse_id	Settings
nurse_bank_details	nurse_id	ConnectIPS payout details ⚠️ (old ref called this user_bank_details)
didit_webhook_events	nurse_id	KYC webhook log

Billing, payments & referrals

Table	FKs / unique	Notes
subscription_plans	—	Stripe price catalogue (stripe_price_id)
subscriptions	user_id, stripe_price_id	Active subscription per customer
payments	user_id	Payment records
withdrawal_requests	—	Nurse/referrer payouts
referral_accounts	user_id unique, referral_code unique	Referral wallet summary
referrals	referrer_user_id, referred_user_id (unique)	Referral edges
referral_commissions	referral_id	5% commission rows
referral_settings	—	Commission config
referral_wallet_transactions	user_id	Wallet ledger
customer_activity_log	—	Customer audit trail

---

8. Foreign keys & referential integrity

Every FK from database_constraints.csv. Decide a delete policy per relationship (the right column is the recommended rebuild behaviour).

Child table	FK column	→ Parent	Recommended on-delete
parents	user_id	profiles	RESTRICT (soft-delete parent instead)
admission_forms	parent_id	parents	CASCADE
parent_health_info	parent_id	parents	CASCADE
parent_emergency_contacts	parent_id	parents	CASCADE
visits	parent_id	parents	RESTRICT
visits	nurse_id	nurses	SET NULL (reassign on nurse removal)
visit_events	visit_id	visits	CASCADE
assessments	visit_id / parent_id / nurse_id	visits / parents / nurses	CASCADE (visit) / RESTRICT / SET NULL
assessment_versions	assessment_id / nurse_id	assessments / nurses	CASCADE / SET NULL
first_visit_assessments	parent_id / nurse_id / visit_id	parents / nurses / visits	RESTRICT / SET NULL / CASCADE
clinical_modules	visit_id / nurse_id / parent_id	visits / nurses / parents	CASCADE / SET NULL / RESTRICT
wound_care_forms	parent_id / visit_id / nurse_id	parents / visits / nurses	RESTRICT / CASCADE / SET NULL
email_notifications	assessment_id / parent_id / visit_id	assessments / parents / visits	CASCADE
escalations	visit_id / parent_id / nurse_id	visits / parents / nurses	SET NULL / RESTRICT / SET NULL
sos_alerts	nurse_id / patient_parent_id	nurses / parents	SET NULL / RESTRICT
nurse_locations	nurse_id	nurses	CASCADE
location_audit_log	nurse_id	nurses	CASCADE
hospital_requests	parent_id	parents	RESTRICT
escort_tasks	hospital_request_id / parent_id	hospital_requests / parents	CASCADE / RESTRICT
conversation_participants	conversation_id / user_id	conversations / profiles	CASCADE
messages	conversation_id / voice_message_id	conversations / voice_messages	CASCADE / SET NULL
notifications	related_visit_id / user_id	visits / profiles	SET NULL / CASCADE
admin_alerts	related_{visit,admission_form,assessment,escalation,escort_task,hospital_request,parent}_id	respective	SET NULL (alerts are historical)
nurse_certifications	nurse_id	nurses	CASCADE
nurse_document_signatures	nurse_id	nurses	CASCADE
referrals	referrer_user_id / referred_user_id	profiles	RESTRICT
referral_commissions	referral_id	referrals	CASCADE
referral_wallet_transactions	user_id	referral_accounts	CASCADE
subscriptions	user_id / stripe_price_id	profiles / subscription_plans	RESTRICT
user_roles	user_id	profiles	CASCADE
profiles	user_id	users (auth)	CASCADE

Unique constraints to preserve: profiles.user_id, nurses.email, nurses.user_id, doctors.user_id, customer_admin_details.user_id, staff_applications.email, staff_applications.user_id, referral_accounts.referral_code, referral_accounts.user_id, referrals.referred_user_id, nurse_locations.nurse_id, parent_health_info.parent_id, user_roles(user_id, role), nurse_certifications(nurse_id, certification_name), nurse_document_signatures(nurse_id, document_type), conversation_participants(conversation_id, user_id).

⚠️ The CSV lists user_roles_user_id_role_key as UNIQUE but splits it across two rows; treat it as a composite unique on (user_id, role).

---

9. What replaces the Supabase-managed schemas

Supabase schema	Purpose	Rebuild replacement
auth.*	Users, sessions, OAuth, MFA, OTP tokens	AuthModule: own users table + JWT (access/refresh), Passport OAuth strategies, OTP in otp_verifications + Redis throttle
storage.*	Buckets/objects (reports, wound-photos, staff-documents)	S3-compatible storage + StorageService with pre-signed URLs
realtime	Postgres CDC channels	Socket.IO gateway emitting curated domain events
vault / secrets	Function secrets	Environment variables / secret manager

---

10. Indexing & performance recommendations

Add these in the rebuild (beyond PK/unique/FK indexes):

• visits(status), visits(nurse_id, status), visits(client_user_id), visits(scheduled_date) — dashboard and roster queries.
• assessments(visit_id), assessments(nurse_id, is_draft), assessments(approval_status, admin_viewed) — nurse/admin report lists.
• escalations(status, created_at desc), escalations(assigned_doctor_id) — doctor queue.
• admin_alerts(status, severity, created_at desc) — ops dashboard.
• nurse_locations(nurse_id) (already unique) + a TTL/cleanup job into location_audit_log.
• notifications(user_id, created_at desc) — feed.
• Partial index WHERE deleted_at IS NULL on soft-deletable tables (parents).

Cache hot, rarely-changing reads in Redis: app_config/feature flags, maps key, subscription status, on-call doctor lookup, and OTP/SOS/AI rate-limit counters.

---

End of Data Model & Schema Reference.