482 lines
18 KiB
PL/PgSQL
482 lines
18 KiB
PL/PgSQL
begin;
|
|
select plan(64);
|
|
|
|
-- Seed reference data as service_role (bypasses RLS)
|
|
set local role service_role;
|
|
insert into public.services (id, name, duration_minutes, price_from, description, image)
|
|
values ('knotless', 'Knotless Braids', 240, 150, 'Tension-free braids.', 'styles/knotless.jpg');
|
|
insert into public.products (id, name, category, price, image, description, in_stock)
|
|
values ('edge-control', 'Silk Edge Control', 'hair-care', 12, 'products/edge-control.jpg', 'Hold with shine.', true);
|
|
reset role;
|
|
|
|
-- Anyone can read services
|
|
set local role anon;
|
|
select isnt_empty(
|
|
$$ select * from public.services $$,
|
|
'anon can read services'
|
|
);
|
|
|
|
-- Anyone can read products
|
|
select isnt_empty(
|
|
$$ select * from public.products $$,
|
|
'anon can read products'
|
|
);
|
|
|
|
-- Anon cannot write to services
|
|
select throws_ok(
|
|
$$ insert into public.services (id, name, duration_minutes, price_from, description, image)
|
|
values ('hack', 'Hack', 1, 1, 'x', 'x') $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot insert into services'
|
|
);
|
|
|
|
-- Anyone can create a booking
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes)
|
|
values ('knotless', '2026-09-15', '10:00 AM', 'Jane Doe', '555-0100', 'jane@example.com', '') $$,
|
|
'anon can create a booking'
|
|
);
|
|
|
|
-- Anon cannot read bookings (PII protection). No SELECT privilege is granted
|
|
-- to anon on this table at all now, so this is blocked at the grant layer
|
|
-- (permission denied) rather than merely filtered to empty rows by RLS.
|
|
select throws_ok(
|
|
$$ select * from public.bookings $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot read bookings'
|
|
);
|
|
|
|
-- Anon cannot update bookings (no UPDATE privilege granted, regardless of RLS)
|
|
select throws_ok(
|
|
$$ update public.bookings set contact_notes = 'hacked' $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot update bookings'
|
|
);
|
|
|
|
-- Anyone can create an inquiry
|
|
select lives_ok(
|
|
$$ insert into public.inquiries (product_id, contact_name, contact_email, contact_notes)
|
|
values ('edge-control', 'Jane Doe', 'jane@example.com', 'Is this in stock?') $$,
|
|
'anon can create an inquiry'
|
|
);
|
|
|
|
-- Anon cannot read inquiries (PII protection). Same as bookings above: no
|
|
-- SELECT privilege is granted, so this fails at the grant layer.
|
|
select throws_ok(
|
|
$$ select * from public.inquiries $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot read inquiries'
|
|
);
|
|
|
|
-- Anyone can read booked_slots (no PII there)
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots $$,
|
|
'anon can read booked_slots (populated by the trigger from the booking above)'
|
|
);
|
|
|
|
-- Anon cannot write booked_slots directly
|
|
select throws_ok(
|
|
$$ insert into public.booked_slots (date, time) values ('2026-09-16', '1:00 PM') $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot insert into booked_slots directly'
|
|
);
|
|
|
|
-- The admin (matching UID) can read bookings
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select isnt_empty(
|
|
$$ select * from public.bookings $$,
|
|
'admin can read bookings'
|
|
);
|
|
|
|
-- The admin can write to services
|
|
select lives_ok(
|
|
$$ insert into public.services (id, name, duration_minutes, price_from, description, image)
|
|
values ('cornrows', 'Cornrows', 120, 90, 'Classic style.', 'styles/cornrows.jpg') $$,
|
|
'admin can insert into services'
|
|
);
|
|
|
|
-- Double-booking prevention: only one *confirmed* booking may hold a given
|
|
-- (date, time) slot. Uses service_role so grant-layer checks don't interfere
|
|
-- with the uniqueness check under test.
|
|
set local role service_role;
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-01', '2:00 PM', 'A Client', '555-0101', 'a@example.com', '', 'confirmed') $$,
|
|
'first confirmed booking for a slot succeeds'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-01', '2:00 PM', 'B Client', '555-0102', 'b@example.com', '', 'confirmed') $$,
|
|
'23505',
|
|
null,
|
|
'a second confirmed booking for an already-booked slot fails with a uniqueness violation'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-01', '3:00 PM', 'C Client', '555-0103', 'c@example.com', '', 'confirmed') $$,
|
|
'a confirmed booking for a different slot on the same date succeeds'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-01', '2:00 PM', 'D Client', '555-0104', 'd@example.com', '', 'cancelled') $$,
|
|
'a cancelled booking for an already-booked slot still succeeds (partial index only covers confirmed)'
|
|
);
|
|
|
|
-- booked_slots cleanup: mirror row is removed once no confirmed booking
|
|
-- remains for a (date, time), whether via status update or deletion.
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-01' and time = '2:00 PM' $$,
|
|
'booked_slots mirror has an entry for the confirmed slot'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ update public.bookings set status = 'cancelled'
|
|
where date = '2026-10-01' and time = '2:00 PM' and status = 'confirmed' $$,
|
|
'cancelling the confirmed booking succeeds'
|
|
);
|
|
|
|
select is_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-01' and time = '2:00 PM' $$,
|
|
'booked_slots mirror row is removed after the booking is cancelled'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes)
|
|
values ('knotless', '2026-10-02', '9:00 AM', 'E Client', '555-0105', 'e@example.com', '') $$,
|
|
'confirmed booking for the deletion test succeeds'
|
|
);
|
|
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-02' and time = '9:00 AM' $$,
|
|
'booked_slots mirror has an entry before deletion'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ delete from public.bookings where date = '2026-10-02' and time = '9:00 AM' $$,
|
|
'deleting the confirmed booking succeeds'
|
|
);
|
|
|
|
select is_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-02' and time = '9:00 AM' $$,
|
|
'booked_slots mirror row is removed after the booking is deleted'
|
|
);
|
|
|
|
-- Rescheduling: an update that changes date/time while status stays
|
|
-- 'confirmed' must free the OLD slot's mirror row and create one for the
|
|
-- NEW slot. A trigger scoped to `after update of status` would never fire
|
|
-- for this (status doesn't change), which was the bug caught in review.
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes)
|
|
values ('knotless', '2026-10-03', '11:00 AM', 'F Client', '555-0106', 'f@example.com', '') $$,
|
|
'confirmed booking for the reschedule test succeeds'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ update public.bookings set date = '2026-10-04', time = '1:00 PM'
|
|
where date = '2026-10-03' and time = '11:00 AM' and status = 'confirmed' $$,
|
|
'rescheduling the confirmed booking to a new date/time succeeds'
|
|
);
|
|
|
|
select is_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-03' and time = '11:00 AM' $$,
|
|
'booked_slots mirror row for the OLD slot is removed after a reschedule'
|
|
);
|
|
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-04' and time = '1:00 PM' $$,
|
|
'booked_slots mirror row for the NEW slot is created after a reschedule'
|
|
);
|
|
|
|
-- notification_failures: admin-only select; only service_role writes.
|
|
select lives_ok(
|
|
$$ insert into public.notification_failures (kind, record_id, error_message)
|
|
values ('booking', gen_random_uuid(), 'Resend responded 401: unauthorized') $$,
|
|
'service_role can insert into notification_failures'
|
|
);
|
|
|
|
set local role anon;
|
|
select throws_ok(
|
|
$$ select * from public.notification_failures $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot read notification_failures'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into public.notification_failures (kind, record_id, error_message)
|
|
values ('booking', gen_random_uuid(), 'hack') $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot insert into notification_failures'
|
|
);
|
|
|
|
-- salon-images bucket: anyone can read, only admin can write
|
|
set local role anon;
|
|
set local request.jwt.claim.sub = '00000000-0000-0000-0000-000000000000';
|
|
select lives_ok(
|
|
$$ select * from storage.objects where bucket_id = 'salon-images' $$,
|
|
'anon can read salon-images objects'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into storage.objects (bucket_id, name) values ('salon-images', 'test.jpg') $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot upload to salon-images'
|
|
);
|
|
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select lives_ok(
|
|
$$ insert into storage.objects (bucket_id, name) values ('salon-images', 'admin-test.jpg') $$,
|
|
'admin can upload to salon-images'
|
|
);
|
|
|
|
-- Scoped to a single booking (not a blanket service_id filter): several
|
|
-- other knotless bookings above share a slot across statuses (e.g. two
|
|
-- cancelled bookings for 2026-10-01 2:00 PM), and a blanket update would
|
|
-- try to move both of them to 'completed' at once, colliding on the
|
|
-- occupied-slot unique index below -- that's a test-setup hazard, not the
|
|
-- behavior under test here.
|
|
select lives_ok(
|
|
$$ update public.bookings set status = 'completed'
|
|
where date = '2026-10-04' and time = '1:00 PM' and status = 'confirmed' $$,
|
|
'admin can mark a booking completed'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ update public.bookings set status = 'bogus' where service_id = 'knotless' $$,
|
|
'23514',
|
|
null,
|
|
'booking status is still constrained to known values'
|
|
);
|
|
|
|
-- A completed appointment actually happened and must keep occupying its
|
|
-- slot: marking a confirmed booking completed must NOT free booked_slots
|
|
-- (regression test for the confirmed -> completed double-booking bug).
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-05', '10:00 AM', 'Complete Client', '555-0301', 'complete@example.com', '', 'confirmed') $$,
|
|
'confirmed booking for the completed-slot test succeeds'
|
|
);
|
|
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-05' and time = '10:00 AM' $$,
|
|
'booked_slots mirror has an entry for the confirmed slot before completion'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ update public.bookings set status = 'completed'
|
|
where date = '2026-10-05' and time = '10:00 AM' and status = 'confirmed' $$,
|
|
'marking the booking completed succeeds'
|
|
);
|
|
|
|
select isnt_empty(
|
|
$$ select * from public.booked_slots where date = '2026-10-05' and time = '10:00 AM' $$,
|
|
'booked_slots mirror row STILL holds the slot after confirmed -> completed'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status)
|
|
values ('knotless', '2026-10-05', '10:00 AM', 'Double Book Client', '555-0302', 'double@example.com', '', 'confirmed') $$,
|
|
'23505',
|
|
null,
|
|
'a second confirmed booking for a now-completed slot still fails with a uniqueness violation'
|
|
);
|
|
|
|
select isnt_empty(
|
|
$$ select * from public.notification_failures $$,
|
|
'admin can read notification_failures'
|
|
);
|
|
|
|
-- Self-healing unique index migration: replay the exact dedupe logic
|
|
-- 20260812000002 runs before creating bookings_active_slot_idx, proving
|
|
-- that pre-existing duplicate confirmed bookings for a slot get collapsed
|
|
-- to a single confirmed row (the earliest-created one) rather than making
|
|
-- the index creation fail outright.
|
|
-- DDL (drop/create index) requires table ownership, which service_role
|
|
-- doesn't have -- reset to the connecting (owner) role for this block. The
|
|
-- dedupe_confirmed_booking_slots() call further down still exercises the
|
|
-- service_role grant path via an explicit role switch.
|
|
reset role;
|
|
|
|
select lives_ok(
|
|
$$ drop index if exists public.bookings_active_slot_idx $$,
|
|
'temporarily dropping the unique index to simulate pre-migration data succeeds'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes, status, created_at)
|
|
values
|
|
('knotless', '2026-11-01', '4:00 PM', 'Dup Earliest', '555-0201', 'dup1@example.com', '', 'confirmed', now() - interval '1 hour'),
|
|
('knotless', '2026-11-01', '4:00 PM', 'Dup Later', '555-0202', 'dup2@example.com', '', 'confirmed', now()) $$,
|
|
'seeding two duplicate confirmed bookings for the same slot succeeds without the unique index'
|
|
);
|
|
|
|
-- Exercises the same execute grant the migration relies on: only
|
|
-- service_role can call this function (revoked from anon/authenticated).
|
|
set local role service_role;
|
|
select lives_ok(
|
|
$$ select public.dedupe_confirmed_booking_slots() $$,
|
|
'dedupe_confirmed_booking_slots() runs without error'
|
|
);
|
|
|
|
select is(
|
|
(select count(*)::int from public.bookings where date = '2026-11-01' and time = '4:00 PM' and status = 'confirmed'),
|
|
1,
|
|
'the self-heal dedupe leaves exactly one confirmed booking per duplicated slot'
|
|
);
|
|
|
|
select results_eq(
|
|
$$ select contact_name from public.bookings where date = '2026-11-01' and time = '4:00 PM' and status = 'confirmed' $$,
|
|
$$ values ('Dup Earliest'::text) $$,
|
|
'the self-heal dedupe keeps the earliest-created booking as confirmed'
|
|
);
|
|
|
|
-- Back to the owner role for the final DDL step.
|
|
reset role;
|
|
select lives_ok(
|
|
$$ create unique index bookings_active_slot_idx on public.bookings (date, time) where status = 'confirmed' $$,
|
|
'the unique index can be created after the self-heal dedupe removes the duplicate'
|
|
);
|
|
|
|
-- Duration-aware booked_slots: the mirror row now carries the booked
|
|
-- service's duration, so client-side availability computation can detect
|
|
-- overlap (not just an exact time match). knotless has duration_minutes = 240
|
|
-- (seeded at the top of this file).
|
|
set local role service_role;
|
|
|
|
select lives_ok(
|
|
$$ insert into public.bookings (service_id, date, time, contact_name, contact_phone, contact_email, contact_notes)
|
|
values ('knotless', '2026-12-01', '9:00 AM', 'Duration Client', '555-0301', 'duration@example.com', '') $$,
|
|
'confirmed booking for the duration test succeeds'
|
|
);
|
|
|
|
select results_eq(
|
|
$$ select duration_minutes from public.booked_slots where date = '2026-12-01' and time = '9:00 AM' $$,
|
|
$$ values (240) $$,
|
|
'booked_slots mirror row carries the booked services duration_minutes'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ update public.bookings set status = 'completed'
|
|
where date = '2026-12-01' and time = '9:00 AM' and status = 'confirmed' $$,
|
|
'marking the duration-test booking completed succeeds'
|
|
);
|
|
|
|
select results_eq(
|
|
$$ select duration_minutes from public.booked_slots where date = '2026-12-01' and time = '9:00 AM' $$,
|
|
$$ values (240) $$,
|
|
'booked_slots keeps the correct duration_minutes after confirmed -> completed'
|
|
);
|
|
|
|
select lives_ok(
|
|
$$ update public.bookings set status = 'cancelled'
|
|
where date = '2026-12-01' and time = '9:00 AM' and status = 'completed' $$,
|
|
'cancelling the duration-test booking succeeds'
|
|
);
|
|
|
|
select is_empty(
|
|
$$ select * from public.booked_slots where date = '2026-12-01' and time = '9:00 AM' $$,
|
|
'booked_slots mirror row is removed after the duration-test booking is cancelled, regardless of duration'
|
|
);
|
|
|
|
-- site_content: public read, admin-only write. Singleton row seeded by the
|
|
-- migration, so no insert test is needed -- only read and update.
|
|
set local role anon;
|
|
select isnt_empty(
|
|
$$ select * from public.site_content where id = 'default' $$,
|
|
'anon can read site_content'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ update public.site_content set hero_headline = 'hack' where id = 'default' $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot update site_content'
|
|
);
|
|
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select lives_ok(
|
|
$$ update public.site_content set hero_headline = 'Updated Headline' where id = 'default' $$,
|
|
'admin can update site_content'
|
|
);
|
|
|
|
-- testimonials: public read, admin-only write.
|
|
set local role anon;
|
|
select isnt_empty(
|
|
$$ select * from public.testimonials $$,
|
|
'anon can read testimonials'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into public.testimonials (quote, author, avatar_image, sort_order)
|
|
values ('hack', 'Hacker', 'x.jpg', 99) $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot insert into testimonials'
|
|
);
|
|
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select lives_ok(
|
|
$$ insert into public.testimonials (quote, author, avatar_image, sort_order)
|
|
values ('Great service!', 'Test Client', 'test.jpg', 99) $$,
|
|
'admin can insert into testimonials'
|
|
);
|
|
|
|
-- rules: public read, admin-only write.
|
|
set local role anon;
|
|
select isnt_empty(
|
|
$$ select * from public.rules $$,
|
|
'anon can read rules'
|
|
);
|
|
|
|
select throws_ok(
|
|
$$ insert into public.rules (title, body, icon_key, sort_order)
|
|
values ('hack', 'hack', 'deposit', 99) $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot insert into rules'
|
|
);
|
|
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select lives_ok(
|
|
$$ insert into public.rules (title, body, icon_key, sort_order)
|
|
values ('Test Rule', 'Test body.', 'payment', 99) $$,
|
|
'admin can insert into rules'
|
|
);
|
|
|
|
-- products.featured / featured_order: no new RLS policy (existing
|
|
-- products_admin_write already covers all columns), but confirm anon still
|
|
-- can't set them and admin can.
|
|
set local role anon;
|
|
select throws_ok(
|
|
$$ update public.products set featured = true where id = 'edge-control' $$,
|
|
'42501',
|
|
null,
|
|
'anon cannot set products.featured'
|
|
);
|
|
|
|
set local role authenticated;
|
|
set local request.jwt.claim.sub = '2b88676b-f901-459a-9147-b914190c4f81';
|
|
select lives_ok(
|
|
$$ update public.products set featured = true, featured_order = 0 where id = 'edge-control' $$,
|
|
'admin can set products.featured and featured_order'
|
|
);
|
|
|
|
select * from finish();
|
|
rollback;
|