supabase-schema.sql

-- Booking App for Clinics: run this in Supabase → SQL Editor.

-- One profile per user, created automatically on sign-up.
create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  full_name text,
  phone text,
  role text not null default 'patient' check (role in ('patient', 'staff')),
  created_at timestamptz not null default now()
);

create table public.doctors (
  id bigint generated always as identity primary key,
  name text not null,
  specialty text,
  slot_minutes int not null default 30 check (slot_minutes between 10 and 120),
  active boolean not null default true
);

-- Weekly working hours, e.g. Monday 09:00–13:00 (0 = Sunday).
create table public.availability (
  id bigint generated always as identity primary key,
  doctor_id bigint not null references public.doctors (id) on delete cascade,
  weekday int not null check (weekday between 0 and 6),
  start_time time not null,
  end_time time not null,
  check (end_time > start_time)
);

create table public.appointments (
  id bigint generated always as identity primary key,
  doctor_id bigint not null references public.doctors (id),
  patient_id uuid not null references public.profiles (id) on delete cascade,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  reason text,
  status text not null default 'booked' check (status in ('booked', 'cancelled', 'done')),
  created_at timestamptz not null default now(),
  check (ends_at > starts_at)
);

-- The database itself prevents double booking (cancelled bookings free the slot).
create unique index appointments_no_double_booking
  on public.appointments (doctor_id, starts_at)
  where status <> 'cancelled';

create or replace function public.is_staff()
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from public.profiles where id = auth.uid() and role = 'staff');
$$;

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into public.profiles (id, full_name) values (new.id, new.raw_user_meta_data ->> 'full_name');
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- Patients may see which times are taken, but never who booked them.
create or replace function public.busy_slots(p_doctor bigint, p_from timestamptz, p_to timestamptz)
returns table (starts_at timestamptz) language sql stable security definer set search_path = public as $$
  select a.starts_at from public.appointments a
  where a.doctor_id = p_doctor and a.status <> 'cancelled'
    and a.starts_at >= p_from and a.starts_at < p_to;
$$;
grant execute on function public.busy_slots(bigint, timestamptz, timestamptz) to authenticated;

alter table public.profiles enable row level security;
alter table public.doctors enable row level security;
alter table public.availability enable row level security;
alter table public.appointments enable row level security;

create policy "Read own profile (staff read all)" on public.profiles
  for select to authenticated using (id = auth.uid() or public.is_staff());
create policy "Update own profile, never own role" on public.profiles
  for update to authenticated using (id = auth.uid())
  with check (id = auth.uid() and role = (select p.role from public.profiles p where p.id = auth.uid()));

create policy "Signed-in users see doctors" on public.doctors
  for select to authenticated using (true);
create policy "Staff manage doctors" on public.doctors
  for all to authenticated using (public.is_staff()) with check (public.is_staff());

create policy "Signed-in users see hours" on public.availability
  for select to authenticated using (true);
create policy "Staff manage hours" on public.availability
  for all to authenticated using (public.is_staff()) with check (public.is_staff());

create policy "Patients see own, staff see all" on public.appointments
  for select to authenticated using (patient_id = auth.uid() or public.is_staff());
create policy "Patients book for themselves" on public.appointments
  for insert to authenticated with check (patient_id = auth.uid() and status = 'booked' and starts_at > now());
create policy "Patients cancel own, staff update any" on public.appointments
  for update to authenticated using (patient_id = auth.uid() or public.is_staff())
  with check (public.is_staff() or (patient_id = auth.uid() and status = 'cancelled'));

-- Make yourself staff after signing up once:
-- update public.profiles set role = 'staff'
--   where id = (select id from auth.users where email = 'you@example.com');