Domain 1 — Identity & Access
Related: business requirements — Actors & onboarding.
users [CORE]
Role: The single identity record for every human actor — nurse, customer, admin. role decides which profile sub-table is populated. Phone is the primary login credential (OTP); national ID is filled only after KYC.
Why: One identity table avoids three near-duplicate user tables and lets auth, audit, and notifications treat everyone uniformly. Phone-as-primary matches Iranian OTP norms and is the key Shahkar matches against. National ID stays NULL until verified so an unverified registration can't masquerade as KYC-complete.
Fields unchanged from baseline: id, email (enc, nullable), phone (enc, unique), national_id (enc, nullable), national_id_verified_at, first_name, last_name, gender (promoted here — see note), avatar_url, role (nurse/customer/admin), is_active, email_verified_at, phone_verified_at, last_login_at, last_login_ip, preferred_language, created_at, updated_at, deleted_at.
| Field | Type | Notes |
|---|---|---|
gender | NVARCHAR(10) NULL | NEW/clarified — male/female. Needed because same-gender caregiving is a near-hard requirement in Iranian bodily-care; nurse gender (from here) is matched against booking_requests.required_caregiver_gender. |
shahkar_verified_at | DATETIME2 NULL | NEW — when the phone↔national-id binding was confirmed via Shahkar. Re-set to NULL (re-verify) on phone change. |
Relations: 1:1 → nurse_profiles / customer_profiles (by role); 1:N → user_sessions, user_roles, notifications, ticket_participants. Admin users are referenced across the schema as *_by_admin_id.
user_sessions [CORE]
Role: Refresh-token session records. Why: Enables logout-everywhere and stolen-token revocation without a heavyweight session store. Unchanged: id, user_id, refresh_token_hash, device_info, ip_address, is_revoked, revoked_at, expires_at, created_at. Relations: N:1 → users.
roles / user_roles [CORE]
Role: RBAC for admin staff only (nurses/customers use users.role). Why: A small admin team still needs separable finance/support/moderation permissions and a revocation history. user_roles keeps granted_by/granted_at/revoked_at for an audit trail. Relations: users N:N roles via user_roles.
nurse_profiles [CORE]
Role: Extended data for nurses, plus denormalized search/quality aggregates. Why separated from users: keeps the base identity table lean and isolates the (large) nurse-only attributes and the aggregates that search reads on every query.
| Field | Type | Notes |
|---|---|---|
id | BIGINT PK | |
user_id | BIGINT FK → users UNIQUE | 1:1 |
partner_center_id | BIGINT FK → partner_centers NULL | NEW — the licensed center that legally sponsors this nurse at launch (Asanism model). NULL once Balinyaar holds its own permit. |
bio, years_of_experience, education_level, education_field, specializations_json | … | Unchanged. |
is_verified | BIT NOT NULL DEFAULT 0 | Guarded — set only inside the transaction that confirms all required verification_steps.status='passed'. No direct write API (Principle 12). |
verification_status | — | CUT — duplicated nurse_verifications.status; two copies drifted. nurse_verifications.status is now the single source of truth. |
is_accepting_bookings | BIT NOT NULL DEFAULT 0 | Nurse can pause without losing verified status. |
average_rating, total_reviews, total_completed_bookings | … | Denormalized. Recompute rule now documented: updated on every review status transition (publish → +, hide/reject/unpublish → −) and on booking completion/dispute-reversal, plus a nightly reconciliation job. Fixes the "hide a 1★ review → rating stays inflated" drift. |
response_rate, avg_response_time_hours, profile_completion_score | — | CUT for MVP — analytics columns on no money/safety path, each needing a maintenance job. Compute offline later. |
created_at, updated_at, deleted_at | … |
Relations: 1:1 → users, nurse_verifications; 1:N → nurse_service_variants, nurse_service_areas, nurse_bank_accounts, nurse_credentials, bookings, nurse_payouts, nurse_clawbacks; N:1 → partner_centers.
customer_profiles [CORE]
Role: Lightweight extension for customers. Why intentionally thin: most customer reality lives in their patients, customer_addresses, and bookings. KYC for customers is deferred. Unchanged: id, user_id (unique), default_emergency_contact_name/_phone (enc), created_at, updated_at. CUT for MVP: national_id_verified_at (anti-fraud customer KYC — add when actually built). Relations: 1:1 → users; 1:N → patients, customer_addresses, booking_requests, bookings.
patients [CORE]
Role: The person receiving care, separate from the payer. Why: the payer (adult child, spouse) is usually not the patient (elderly parent, newborn, post-surgical adult); one customer registers many patients, each with its own clinical baseline and longitudinal record. Unchanged: id, customer_id, display_name, first_name, last_name, birth_date, gender, blood_type, initial_medical_notes (enc), is_active, timestamps. Relations: N:1 → customer_profiles; 1:N → booking_requests, patient_care_records. Tenancy invariant: a booking_request.patient_id must belong to the same customer_id.
customer_addresses [CORE]
Role: Saved service locations; the encrypted address + coordinates for EVV distance checks. Why coordinates: EVV check-in compares the nurse's GPS against the booking address within tolerance. Unchanged fields, plus: filtered UNIQUE(customer_id) WHERE is_primary=1 so exactly one primary exists (prevents ambiguous default). Relations: N:1 → customer_profiles, cities, districts; referenced by booking_requests/bookings.
nurse_bank_accounts [CORE]
Role: Payout destination (IBAN/Sheba). Why hardened: the IBAN is the single place real money leaves the platform — the original "admin eyeballs the IBAN" check is exactly the forgeable, money-mule-risk link the research warns about.
| Field | Type | Notes |
|---|---|---|
id, nurse_id, bank_name, account_holder_name (enc), iban (enc), is_primary, is_verified, verified_by_admin_id, verified_at, timestamps | … | Baseline. |
iban_hash | NVARCHAR(64) | NEW — deterministic hash for a UNIQUE constraint (same IBAN must not silently serve two nurses). |
matched_national_id | BIT NULL | NEW — result of an automated IBAN-owner ↔ national-id inquiry (استعلام شبا) via a KYC vendor. First payout is gated on a match, not on admin eyeballing. |
account_holder_from_bank | NVARCHAR(200) NULL | NEW — name returned by the bank inquiry, snapshot. |
ownership_vendor_ref | NVARCHAR(200) NULL | NEW — vendor transaction id for audit. |
Constraints: filtered UNIQUE(nurse_id) WHERE is_primary=1; UNIQUE(iban_hash). Relations: N:1 → nurse_profiles; 1:N → nurse_payouts.