Patrick Toal
108c042921
fix(edge-fn): use user.id instead of claims.sub; fixes 500s and false cert_required fix(migrations): drop broad reservations SELECT policy; add reservation_slots view with security_invoker=false fix(tests): correct weekSlot() keys from start/end to start_time/end_time fix(tests): spread overlap test slots across separate ISO weeks fix(tests): update e2e assertion to match actual authenticated home text fix(app): hide IonMenu before user is authenticated feat(dx): add test:all script running unit, integration, and e2e in sequence docs(claude-md): document SELinux fix, Edge Function auth pattern, security_invoker behaviour
105 lines
3.9 KiB
PL/PgSQL
105 lines
3.9 KiB
PL/PgSQL
-- ============================================================
|
||
-- BOOKING CONFIG: configurable rule parameters (read by Edge Function)
|
||
-- ============================================================
|
||
|
||
create table public.booking_config (
|
||
key text primary key,
|
||
value jsonb not null,
|
||
description text
|
||
);
|
||
|
||
alter table public.booking_config enable row level security;
|
||
|
||
create policy "Authenticated users can read booking config" on public.booking_config
|
||
for select using (auth.role() = 'authenticated');
|
||
|
||
create policy "Admins can manage booking config" on public.booking_config
|
||
for all using (
|
||
exists (
|
||
select 1 from public.members
|
||
where user_id = auth.uid() and role in ('admin', 'boatswain')
|
||
)
|
||
);
|
||
|
||
insert into public.booking_config (key, value, description) values
|
||
('max_sessions_per_week', '2', 'Max pre-booked sessions per member per ISO week (Mon–Sun)'),
|
||
('max_weekend_sessions_per_period', '1', 'Max weekend/holiday sessions per alternating period'),
|
||
('weekend_period_weeks', '2', 'Number of weeks in the alternating weekend period'),
|
||
('open_session_advance_hours', '24', 'Hours before session start where pre-booking limits are waived');
|
||
|
||
-- ============================================================
|
||
-- HOLIDAYS: configurable statutory holiday list
|
||
-- ============================================================
|
||
|
||
create table public.holidays (
|
||
date date primary key,
|
||
name text not null
|
||
);
|
||
|
||
alter table public.holidays enable row level security;
|
||
|
||
create policy "Authenticated users can read holidays" on public.holidays
|
||
for select using (auth.role() = 'authenticated');
|
||
|
||
create policy "Admins can manage holidays" on public.holidays
|
||
for all using (
|
||
exists (
|
||
select 1 from public.members
|
||
where user_id = auth.uid() and role in ('admin', 'boatswain')
|
||
)
|
||
);
|
||
|
||
-- Ontario statutory + civic holidays for 2026 sailing season
|
||
insert into public.holidays (date, name) values
|
||
('2026-05-18', 'Victoria Day'),
|
||
('2026-07-01', 'Canada Day'),
|
||
('2026-08-03', 'Civic Holiday'),
|
||
('2026-09-07', 'Labour Day'),
|
||
('2026-10-12', 'Thanksgiving');
|
||
|
||
-- ============================================================
|
||
-- HELPER: is_weekend_or_holiday (used by Edge Function via RPC)
|
||
-- ============================================================
|
||
|
||
create or replace function public.is_weekend_or_holiday(p_date date)
|
||
returns boolean
|
||
language sql stable security definer
|
||
set search_path = public
|
||
as $$
|
||
select
|
||
extract(dow from p_date) in (0, 6)
|
||
or exists (select 1 from public.holidays where date = p_date);
|
||
$$;
|
||
|
||
grant execute on function public.is_weekend_or_holiday(date) to authenticated;
|
||
|
||
-- ============================================================
|
||
-- LOCK DIRECT INSERT: revoke INSERT on reservations from authenticated
|
||
--
|
||
-- Members must create reservations through the create-reservation Edge Function.
|
||
-- The Edge Function uses the service_role key (bypasses RLS).
|
||
-- Admins retain direct INSERT/UPDATE/DELETE via their existing "all" policy.
|
||
-- ============================================================
|
||
|
||
drop policy if exists "Users can create own reservations" on public.reservations;
|
||
|
||
-- Admins can still manage directly; members go through the Edge Function.
|
||
-- The overlap exclusion constraint and cert check trigger remain as DB-level safety nets.
|
||
|
||
-- ============================================================
|
||
-- RBAC VIEW: reservation_slots
|
||
--
|
||
-- Exposes only boat_id, start_time, end_time, status to all authenticated users.
|
||
-- Hides user_id, reason, comment, member_ids, guest_ids.
|
||
-- Members use this view to check slot availability.
|
||
-- Admins query the reservations table directly for full management.
|
||
-- ============================================================
|
||
|
||
create view public.reservation_slots
|
||
with (security_invoker = false)
|
||
as
|
||
select id, boat_id, start_time, end_time, status
|
||
from public.reservations;
|
||
|
||
grant select on public.reservation_slots to authenticated;
|