Database Schema

ClinicFlow uses Supabase (PostgreSQL) with Row-Level Security enabled. Schema source: backend/src/db/schema.sql.

Entity Relationships

erDiagram
    clinics ||--o{ users : "has staff"
    clinics ||--o{ doctors : employs
    clinics ||--o{ patients : serves
    clinics ||--o{ faqs : configures
    clinics ||--o{ chat_sessions : owns
    clinics ||--o{ audit_logs : tracks
    clinics ||--o{ whatsapp_onboarding_events : logs
    doctors ||--o{ doctor_working_hours : "has"
    doctors ||--o{ doctor_breaks : "has"
    doctors ||--o{ doctor_vacations : "has"
    patients ||--o{ appointments : books
    doctors ||--o{ appointments : attends
    clinics ||--o{ treatment_plans : provides
    treatment_plans ||--o{ treatment_steps : contains
    treatment_plans ||--o{ payment_plans : "has"
    payment_plans ||--o{ payment_installments : "split into"
    clinics ||--o{ recall_campaigns : runs
    recall_campaigns ||--o{ recall_contacts : targets

    clinics {
        uuid id PK
        text name
        text slug UK
        text subscription_plan
        timestamptz subscription_expires_at
        boolean is_active
        text whatsapp_status
        boolean bot_enabled
    }
    users {
        uuid id PK
        uuid clinic_id FK
        text email UK
        text password_hash
        text full_name
        text role
        boolean is_active
    }
    doctors {
        uuid id PK
        uuid clinic_id FK
        text full_name
        text specialty
        text phone
        text email
        boolean is_active
    }
    patients {
        uuid id PK
        uuid clinic_id FK
        text full_name
        text phone
        text dui "encrypted"
        text email
        boolean is_blocked
    }
    appointments {
        uuid id PK
        uuid clinic_id FK
        uuid doctor_id FK
        uuid patient_id FK
        date appointment_date
        time appointment_time
        text status
        text notes
        text idempotency_key UK
        boolean reminder_24h_sent
        boolean reminder_1h_sent
    }

RLS Enforcement

All tables with clinic-owned data have Row-Level Security enabled. Private Postgres functions centralize the checks:

Function	Purpose
`private.request_is_active()`	Validates user and clinic are both `is_active = true`
`private.request_is_admin()`	Checks `app_role = 'admin'`
`private.request_is_system()`	Checks `app_role = 'system'` (for jobs/tokens)