How to Build a Medical Records App with Lovable

1-- Run in Supabase SQL Editor
2-- IMPORTANT: This is for demonstration only. Not for use with real patient data without HIPAA compliance.
3
4CREATE TYPE user_role AS ENUM ('patient', 'provider', 'admin');
5CREATE TYPE appointment_status AS ENUM ('scheduled', 'confirmed', 'completed', 'cancelled', 'no_show');
6CREATE TYPE prescription_status AS ENUM ('active', 'completed', 'discontinued', 'expired');
7
8CREATE TABLE profiles (
9  id UUID REFERENCES auth.users PRIMARY KEY,
10  full_name TEXT NOT NULL,
11  email TEXT NOT NULL,
12  role user_role NOT NULL DEFAULT 'patient',
13  specialty TEXT,
14  created_at TIMESTAMPTZ DEFAULT now()
15);
16
17CREATE TABLE patients (
18  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
19  user_id UUID REFERENCES auth.users UNIQUE,
20  first_name TEXT NOT NULL,
21  last_name TEXT NOT NULL,
22  date_of_birth DATE NOT NULL,
23  gender TEXT,
24  phone TEXT,
25  email TEXT,
26  address TEXT,
27  emergency_contact_name TEXT,
28  emergency_contact_phone TEXT,
29  blood_type TEXT,
30  allergies TEXT[],
31  medical_history TEXT,
32  insurance_provider TEXT,
33  insurance_id TEXT,
34  created_at TIMESTAMPTZ DEFAULT now(),
35  updated_at TIMESTAMPTZ DEFAULT now()
36);
37
38CREATE TABLE provider_patients (
39  provider_id UUID REFERENCES auth.users NOT NULL,
40  patient_id UUID REFERENCES patients(id) NOT NULL,
41  assigned_at TIMESTAMPTZ DEFAULT now(),
42  PRIMARY KEY (provider_id, patient_id)
43);
44
45CREATE TABLE appointments (
46  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
47  patient_id UUID REFERENCES patients(id) ON DELETE CASCADE NOT NULL,
48  provider_id UUID REFERENCES auth.users NOT NULL,
49  scheduled_at TIMESTAMPTZ NOT NULL,
50  duration_minutes INTEGER NOT NULL DEFAULT 30,
51  status appointment_status DEFAULT 'scheduled',
52  appointment_type TEXT NOT NULL,
53  chief_complaint TEXT,
54  notes TEXT,
55  created_at TIMESTAMPTZ DEFAULT now()
56);
57
58CREATE TABLE prescriptions (
59  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
60  patient_id UUID REFERENCES patients(id) ON DELETE CASCADE NOT NULL,
61  provider_id UUID REFERENCES auth.users NOT NULL,
62  medication_name TEXT NOT NULL,
63  dosage TEXT NOT NULL,
64  frequency TEXT NOT NULL,
65  start_date DATE NOT NULL,
66  end_date DATE,
67  status prescription_status DEFAULT 'active',
68  instructions TEXT,
69  refills_remaining INTEGER DEFAULT 0,
70  created_at TIMESTAMPTZ DEFAULT now()
71);
72
73CREATE TABLE medical_documents (
74  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
75  patient_id UUID REFERENCES patients(id) ON DELETE CASCADE NOT NULL,
76  uploaded_by UUID REFERENCES auth.users NOT NULL,
77  document_name TEXT NOT NULL,
78  document_type TEXT NOT NULL,
79  storage_path TEXT NOT NULL,
80  file_size INTEGER,
81  created_at TIMESTAMPTZ DEFAULT now()
82);
83
84CREATE TABLE audit_logs (
85  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
86  user_id UUID REFERENCES auth.users NOT NULL,
87  action TEXT NOT NULL,
88  table_name TEXT NOT NULL,
89  record_id UUID,
90  patient_id UUID REFERENCES patients(id),
91  ip_address INET,
92  created_at TIMESTAMPTZ DEFAULT now()
93);
94
95-- Enable RLS on ALL tables
96ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
97ALTER TABLE patients ENABLE ROW LEVEL SECURITY;
98ALTER TABLE provider_patients ENABLE ROW LEVEL SECURITY;
99ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;
100ALTER TABLE prescriptions ENABLE ROW LEVEL SECURITY;
101ALTER TABLE medical_documents ENABLE ROW LEVEL SECURITY;
102ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;
103
104-- Helper: check if current user is a provider assigned to a patient
105CREATE OR REPLACE FUNCTION is_my_patient(p_patient_id UUID)
106RETURNS BOOLEAN LANGUAGE sql STABLE SECURITY DEFINER AS $$
107  SELECT EXISTS (
108    SELECT 1 FROM provider_patients
109    WHERE provider_id = auth.uid() AND patient_id = p_patient_id
110  );
111$$;
112
113-- Helper: get patient_id for current user if they are a patient
114CREATE OR REPLACE FUNCTION my_patient_id()
115RETURNS UUID LANGUAGE sql STABLE SECURITY DEFINER AS $$
116  SELECT id FROM patients WHERE user_id = auth.uid() LIMIT 1;
117$$;
118
119-- Patients: providers see assigned patients, patients see own record, admins see all
120CREATE POLICY "Providers see assigned patients" ON patients FOR SELECT USING (
121  is_my_patient(id) OR
122  user_id = auth.uid() OR
123  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
124);
125CREATE POLICY "Providers update assigned patients" ON patients FOR UPDATE USING (is_my_patient(id));
126CREATE POLICY "Admins manage patients" ON patients FOR ALL USING (
127  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
128);
129
130-- Appointments: same access as patients
131CREATE POLICY "Providers see assigned patient appointments" ON appointments FOR SELECT USING (
132  is_my_patient(patient_id) OR patient_id = my_patient_id() OR
133  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
134);
135CREATE POLICY "Providers manage appointments" ON appointments FOR ALL USING (provider_id = auth.uid());
136
137-- Prescriptions: same access pattern
138CREATE POLICY "Authorized access to prescriptions" ON prescriptions FOR SELECT USING (
139  is_my_patient(patient_id) OR patient_id = my_patient_id() OR
140  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
141);
142CREATE POLICY "Providers create prescriptions" ON prescriptions FOR INSERT WITH CHECK (provider_id = auth.uid());
143CREATE POLICY "Providers update own prescriptions" ON prescriptions FOR UPDATE USING (provider_id = auth.uid());
144
145-- Documents
146CREATE POLICY "Authorized access to documents" ON medical_documents FOR SELECT USING (
147  is_my_patient(patient_id) OR patient_id = my_patient_id() OR
148  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
149);
150CREATE POLICY "Providers upload documents" ON medical_documents FOR INSERT WITH CHECK (
151  is_my_patient(patient_id)
152);
153
154-- Audit logs: users see own logs, admins see all
155CREATE POLICY "Users see own audit logs" ON audit_logs FOR SELECT USING (user_id = auth.uid());
156CREATE POLICY "System inserts audit logs" ON audit_logs FOR INSERT WITH CHECK (user_id = auth.uid());
157CREATE POLICY "Admins see all audit logs" ON audit_logs FOR SELECT USING (
158  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
159);

1Build the authentication and onboarding flow:
2
31. Login/Register page:
4   - Two tabs: "Sign In" and "Create Account"
5   - Email + password fields for both
6   - On register: after supabase.auth.signUp(), show a role selection step
7
82. Role selection step (after sign-up):
9   - Two large clickable cards: "I am a Healthcare Provider" and "I am a Patient"
10   - Provider card: shows stethoscope icon, fields for full_name and specialty
11   - Patient card: shows user icon, fields for full_name
12   - On select: insert into profiles table with chosen role
13
143. Patient registration form (shown to new users with role='patient'):
15   - Multi-step form collecting: personal details (name, DOB, gender, phone, email)
16   - Medical info (blood_type, allergies as comma-separated input, insurance details)
17   - Emergency contact
18   - Insert into patients table with user_id = current user
19
204. Route guards:
21   - /provider/* routes: check profiles.role = 'provider', redirect patients to /patient
22   - /patient/* routes: check profiles.role = 'patient', redirect providers to /provider
23   - /admin/* routes: check role = 'admin'
24
255. After completing onboarding, redirect to role-appropriate dashboard

1Build the patient chart view at /provider/patients/[patientId]:
2
31. Patient header card (full width, above tabs):
4   - Patient photo placeholder (Avatar with initials)
5   - Name, DOB, age (calculated), gender, blood type
6   - Allergy badges (shadcn Badge, red) — show "No Known Allergies" if empty
7   - Insurance info
8   - Emergency contact
9   - Edit button (opens Dialog with full patient edit form)
10
112. shadcn Tabs below the header:
12
13   APPOINTMENTS tab:
14   - DataTable: Date/Time, Type, Status (Badge), Chief Complaint, Notes
15   - "Schedule Appointment" button opening Dialog with: type select, date/time picker, duration select, chief complaint textarea
16   - Status update: click status cell to change (dropdown: scheduled/confirmed/completed/cancelled/no_show)
17
18   PRESCRIPTIONS tab:
19   - DataTable: Medication, Dosage, Frequency, Start Date, End Date, Status Badge, Refills
20   - Status Badge: active=green, completed=gray, discontinued=red, expired=orange
21   - "Add Prescription" button opening Dialog with all prescription fields
22   - "Discontinue" action sets status=discontinued
23
24   DOCUMENTS tab:
25   - Card grid: document name, type icon, date, size, Download button
26   - "Upload Document" button: file dropzone (PDF, JPG, PNG, DICOM), document type select
27   - Upload to Supabase Storage at path: medical/{patientId}/{timestamp}-{filename} (private bucket)
28   - Download: generate signed URL expiring in 15 minutes
29
30   NOTES tab:
31   - Chronological list of clinical notes
32   - Textarea to add new note with timestamp
33   - Notes stored in a patient_notes table
34
353. On page load: insert an audit_log record: action='view_patient_chart', patient_id=patientId

1Build the provider dashboard at /provider/dashboard:
2
31. Stats row (4 shadcn Cards):
4   - My Patients (count of provider_patients for current provider)
5   - Today's Appointments (count where scheduled_at date = today and provider_id = current)
6   - Active Prescriptions (count of active prescriptions for my patients)
7   - Expiring Soon (prescriptions expiring in next 7 days)
8
92. Today's Schedule section:
10   - Timeline list of today's appointments sorted by time
11   - Each item: time, patient name, appointment type, status badge
12   - Quick status update buttons: Confirm, Mark Complete, Cancel
13   - "No appointments today" empty state
14
153. My Patients list:
16   - shadcn DataTable: Patient Name, DOB/Age, Last Visit, Active Prescriptions count, Actions
17   - Actions: "View Chart" button linking to /provider/patients/{id}
18   - Search input filtering by patient name
19   - "Add Patient" button: search by email to find existing patient, then insert into provider_patients
20
214. Expiring Prescriptions alert section (only shown if count > 0):
22   - shadcn Alert with warning icon
23   - List of prescriptions expiring in 7 days with patient names and renewal buttons

1Build the patient portal at /patient/my-records:
2
31. Patient header card:
4   - Patient's own name and basic demographics
5   - Edit button for non-clinical fields only: phone, address, emergency contact
6   - Allergies listed as read-only badges
7
82. shadcn Tabs:
9
10   MY APPOINTMENTS tab:
11   - DataTable: Date, Provider Name, Type, Status Badge, Notes (read-only)
12   - "Request Appointment" button: opens a Dialog with preferred dates/times and reason
13   - Creates an appointment with status='scheduled' (provider must confirm)
14
15   MY MEDICATIONS tab:
16   - Card grid for active prescriptions: medication name, dosage, frequency, refills remaining
17   - Status badges
18   - Read-only — cannot be edited by patient
19
20   MY DOCUMENTS tab:
21   - List of uploaded documents: name, date, type
22   - Download button generating 15-minute signed URL from Supabase Storage
23   - Message: "Documents are uploaded by your healthcare provider"
24
25   MY PROFILE tab:
26   - Read-only view of all patient demographics
27   - Edit button for contact information only (phone, address, emergency contact)
28   - Medical information (allergies, blood type, insurance) shows with an info note: "Contact your provider to update medical information"
29
303. Page header banner: "This portal is for informational purposes only. In a medical emergency, call 911 or your local emergency number."
31
32All data fetches use patient's own user_id. RLS ensures they can only see their own records.