Spaces:
Sleeping
Sleeping
File size: 3,702 Bytes
dd935c0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | -- Vion Training — notifications & scheduling schema (Phase 5/6)
-- Run this in the Supabase SQL Editor AFTER db/schema.sql. Safe to re-run.
-- ──────────────────────────────────────────────────────────────────────────
-- scheduled_sessions — a rep's planned training slots (drives 5h reminders).
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.scheduled_sessions (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references public.profiles(id) on delete cascade,
scenario_id text, -- optional: a specific scenario to practice
scheduled_at timestamptz not null, -- when the rep plans to train
note text,
reminder_sent boolean not null default false, -- flipped once the 5h email goes out
created_at timestamptz not null default now()
);
create index if not exists scheduled_user_idx on public.scheduled_sessions(user_id);
create index if not exists scheduled_at_idx on public.scheduled_sessions(scheduled_at);
alter table public.scheduled_sessions enable row level security;
-- Owner manages their own slots; admins can read all.
drop policy if exists scheduled_owner_all on public.scheduled_sessions;
create policy scheduled_owner_all on public.scheduled_sessions
for all using (user_id = auth.uid()) with check (user_id = auth.uid());
drop policy if exists scheduled_admin_read on public.scheduled_sessions;
create policy scheduled_admin_read on public.scheduled_sessions
for select using (public.is_admin());
-- ──────────────────────────────────────────────────────────────────────────
-- email_log — idempotency guard so cron retries never double-send an email.
-- Written only server-side (service key); no client access.
-- ──────────────────────────────────────────────────────────────────────────
create table if not exists public.email_log (
id uuid primary key default gen_random_uuid(),
user_id uuid references public.profiles(id) on delete cascade,
kind text not null, -- 'session' | 'weekly' | 'reminder'
ref text not null, -- session id / week key / scheduled id
created_at timestamptz not null default now(),
unique (kind, ref)
);
alter table public.email_log enable row level security; -- no policies => only service key reaches it
-- ──────────────────────────────────────────────────────────────────────────
-- Grants for the new tables (mirror db/schema.sql section 6).
-- ──────────────────────────────────────────────────────────────────────────
grant all privileges on public.scheduled_sessions, public.email_log to service_role;
grant select, insert, update, delete on public.scheduled_sessions to authenticated;
|