Database Schema & Security Model

ClinicFlow uses a multi-tenant PostgreSQL database hosted on Supabase (backend/src/db/schema.sql). The schema comprises 50 production tables guarded by PostgreSQL Row-Level Security (RLS) policies and private helper functions.

Complete Entity Relationship Architecture

erDiagram
    %% Core SaaS Multi-Tenancy
    clinics ||--o{ users : "has staff"
    clinics ||--o{ doctors : "employs"
    clinics ||--o{ patients : "serves"

    %% Clinical & Dental Engine
    patients ||--o{ dental_chart_entries : "has findings"
    patients ||--o{ periogram_snapshots : "has perio records"
    patients ||--o{ treatment_plans : "undergoes"
    treatment_plans ||--o{ treatment_steps : "contains steps"
    treatment_plans ||--o{ payment_plans : "financed by"

    %% Appointments & Reception
    doctors ||--o{ appointments : "conducts"
    patients ||--o{ appointments : "books"
    doctors ||--o{ doctor_blocks : "blocks schedule"

    %% Financial & El Salvador DTE
    clinics ||--o{ dte_invoices : "issues tax documents"
    dte_invoices ||--o{ dte_invoice_items : "contains items"
    patients ||--o{ patient_tax_profiles : "has tax details"
    doctors ||--o{ doctor_commission_ledger : "accrues commission"

    %% Communication & AI Assistant
    clinics ||--o{ chat_sessions : "maintains NLU state"
    patients ||--o{ conversation_messages : "exchanges messages"
    clinics ||--o{ copilot_actions : "logs assistant actions"

Table Categorization Index (50 Tables)

1. Core Tenant & Staff Management (7 Tables)

clinics, users, doctors, doctor_working_hours, doctor_breaks, doctor_vacations, clinic_features.

2. Patient Administration & Appointments (6 Tables)

patients, appointments, public.appointment_drafts, doctor_blocks, waitlist, patient_tax_profiles.

3. Clinical Dental Engine (8 Tables)

dental_chart_entries, dental_chart_current, dental_procedure_catalog, dental_chart_meta, periogram_snapshots, treatment_plans, treatment_steps, treatment_plan_templates.

4. Financial, Billing & Doctor Commissions (8 Tables)

payment_plans, payments, payment_installments, doctor_commission_rules, doctor_commission_overrides, doctor_commission_ledger, ls_orders, billing_items.

5. El Salvador Electronic Tax Invoicing DTE (7 Tables)

dte_settings, dte_credentials, dte_sequences, dte_invoices, dte_invoice_items, dte_payloads, dte_contingency_events.

6. Communication & AI Chatbot (7 Tables)

chat_sessions, faqs, whatsapp_onboarding_events, whatsapp_outbox, conversation_messages, assistant_memory, copilot_actions.

7. Security, Auth & Audit Logs (7 Tables)

refresh_tokens, audit_logs, sa_audit_log, invite_codes, demo_requests, public.jwks, private.schema_migrations.

Custom Database RPC Functions

Function / RPC	Purpose
`public.jwt_clinic_id()`	Extracts signed `clinic_id` claim from ES256 JWT for RLS policy enforcement.
`private.jwt_app_role()`	Extracts user role (`admin`, `staff`, `system`) from JWT context.
`private.request_is_active()`	Validates that both the calling user and clinic are `is_active = true`.
`private.request_is_admin()`	Evaluates whether the caller possesses clinic administrator privileges.
`private.request_is_system()`	Permits background workers using service-role keys to bypass RLS safely.
`record_payment_transaction(...)`	Atomic insertion of patient payments and installment ledger reconciliation.
`add_dental_finding(...)`	Atomic insert of tooth finding with current chart materialization.
`generate_treatment_plan_from_findings(...)`	Auto-converts untreated dental findings into a structured treatment plan.
`block_schedule_transaction(...)`	Atomically blocks doctor availability and cancels conflicting bookings.
`increment_dte_sequence(...)`	Thread-safe sequential control counter generator for El Salvador DTE tax documents.

Row-Level Security (RLS) Policy Pattern

Every tenant table applies the standardized clinic_isolation RLS policy:

CREATE POLICY "clinic_isolation" ON public.patients
  FOR ALL TO authenticated
  USING (clinic_id = public.jwt_clinic_id() AND private.request_is_active())
  WITH CHECK (clinic_id = public.jwt_clinic_id() AND private.request_is_active());