How to Build a Online Test Booking with Lovable

1Create a Supabase schema for an online test booking system.
2
3Tables:
4- exams: id (uuid pk), name (text), description (text), duration_minutes (int), passing_score (int), is_active (bool default true), created_at
5- exam_slots: id (uuid pk), exam_id (references exams), slot_datetime (timestamptz), location (text, or 'Online'), total_seats (int), remaining_seats (int), price_cents (int default 0), notes (text), is_active (bool default true), created_at
6- bookings: id (uuid pk), slot_id (references exam_slots), candidate_name (text not null), candidate_email (text not null), confirmation_code (text unique not null), status (text default 'confirmed' check: confirmed|cancelled|no_show), booked_at (timestamptz default now())
7
8Constraints:
9- CHECK (exam_slots.remaining_seats >= 0)
10- CHECK (exam_slots.remaining_seats <= exam_slots.total_seats)
11
12RLS:
13- exams: SELECT public
14- exam_slots: SELECT public; INSERT/UPDATE/DELETE where auth.uid() in (SELECT id FROM admins)
15- bookings: SELECT public (candidates need to look up by code); INSERT handled by reserve_slot function
16
17Create a table: admins (id uuid pk references auth.users)
18
19Create the atomic reserve function:
20CREATE OR REPLACE FUNCTION reserve_slot(
21  p_slot_id uuid,
22  p_candidate_name text,
23  p_candidate_email text
24) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER AS $$
25DECLARE
26  v_slot exam_slots;
27  v_code text;
28  v_booking_id uuid;
29BEGIN
30  SELECT * INTO v_slot FROM exam_slots WHERE id = p_slot_id AND is_active = true FOR UPDATE;
31  IF NOT FOUND THEN
32    RETURN jsonb_build_object('success', false, 'error', 'Slot not found');
33  END IF;
34  IF v_slot.remaining_seats <= 0 THEN
35    RETURN jsonb_build_object('success', false, 'error', 'No seats available');
36  END IF;
37  v_code := upper(substring(md5(random()::text || clock_timestamp()::text) from 1 for 8));
38  UPDATE exam_slots SET remaining_seats = remaining_seats - 1 WHERE id = p_slot_id;
39  INSERT INTO bookings (slot_id, candidate_name, candidate_email, confirmation_code)
40    VALUES (p_slot_id, p_candidate_name, p_candidate_email, v_code)
41    RETURNING id INTO v_booking_id;
42  RETURN jsonb_build_object('success', true, 'booking_id', v_booking_id, 'confirmation_code', v_code);
43END;
44$$;

1Build a public exam slot listing page at src/pages/ExamSlots.tsx.
2
3Requirements:
4- Fetch all active exams and their upcoming slots (slot_datetime > now(), is_active = true)
5- Group slots by exam using a join: exams with nested exam_slots
6- Render each exam as a section with an Accordion or simple heading
7- Each slot displayed as a Card with:
8  - Date and time (formatted: 'Monday June 9 at 10:00 AM')
9  - Location Badge (green for Online, gray for in-person)
10  - Total seats and remaining seats: '12 of 30 seats remaining'
11  - Price (free if price_cents = 0, otherwise '$X.XX')
12  - Remaining seats progress bar (shadcn/ui Progress component): remaining/total
13  - 'Book This Slot' Button — disabled if remaining_seats = 0, shows 'Sold Out' Badge instead
14  - 'Fully Booked' Badge when remaining_seats = 0
15- 'Book This Slot' opens a booking Dialog (built in next step)
16- Subscribe to Supabase Realtime on exam_slots table. When remaining_seats changes, update the displayed count and progress bar without a full page reload
17- Add a search/filter Input at the top to filter by exam name

1Build a BookingDialog component at src/components/BookingDialog.tsx.
2
3Props: slotId: string, examName: string, slotDatetime: string, open, onOpenChange, onSuccess: (code: string) => void
4
5Dialog content when booking is open (not yet confirmed):
6- Show slot summary at top: exam name, formatted date/time, location
7- Form fields (react-hook-form + zod):
8  - candidate_name: required, min 2 chars
9  - candidate_email: required, valid email format
10  - Checkbox: 'I confirm I meet the prerequisites for this exam'
11- 'Reserve My Seat' submit Button with loading spinner
12
13On submit:
14- Call supabase.rpc('reserve_slot', { p_slot_id: slotId, p_candidate_name, p_candidate_email })
15- If result.success is false:
16  - If error is 'No seats available': show Alert 'This slot just filled up. Please choose another.' and close dialog
17  - Other errors: show error message in the form
18- If result.success is true:
19  - Call onSuccess(result.confirmation_code)
20  - Close this dialog and show ConfirmationCard
21
22ConfirmationCard component (shown after successful booking):
23- Large checkmark icon
24- 'You are registered!' heading
25- Exam name and slot date/time
26- Confirmation code in a large monospace Alert: e.g. 'A3B7F2E9'
27- Instruction: 'Save this code. You will need it to manage your booking.'
28- 'Add to calendar' link that generates a .ics file download
29- 'Look up another booking' link

1Create a Supabase Edge Function at supabase/functions/send-booking-confirmation/index.ts.
2
3The function receives a database webhook payload (POST request) when a new row is inserted into bookings.
4
5Payload shape (Supabase webhook): { type: 'INSERT', table: 'bookings', record: { id, slot_id, candidate_name, candidate_email, confirmation_code, booked_at } }
6
7Function logic:
81. Parse the webhook payload
92. Fetch exam and slot details using the slot_id: join exam_slots and exams to get exam name, slot_datetime, location
103. Format the email content:
11   - Subject: 'Your exam booking is confirmed: [exam name]'
12   - Body text: name, exam name, date/time, location, confirmation code (large), instruction to save the code
134. Call Resend API: POST https://api.resend.com/emails with Authorization: Bearer [RESEND_API_KEY from Deno.env]
145. Return 200 on success, log errors to console
15
16Email body HTML:
17- Keep it simple: use inline styles, no external CSS
18- Show confirmation code in a box with background #f3f4f6, monospace font, large size
19- Include a 'Manage your booking' link pointing to /booking-lookup?code=[confirmation_code]
20
21After deploying the function, set up the database webhook in Supabase:
22Dashboard → Database → Webhooks → Create webhook → Table: bookings, Event: INSERT, URL: [Edge Function URL]

1Build two pages:
2
31. Admin panel at src/pages/AdminPanel.tsx (requires admin role):
4- Show all exams with a 'Create Exam' Button
5- For each exam, show a DataTable of its slots with columns: datetime, location, seats remaining/total, booking count, active toggle
6- 'Add Slot' Button per exam opens a Dialog:
7  - Fields: slot_datetime (DatePicker + TimePicker), location, total_seats (number input), price_cents (number input labeled as dollars)
8  - Insert into exam_slots, set remaining_seats = total_seats
9- Each slot row has a 'View Registrations' Button that opens a Sheet listing all confirmed bookings for that slot: candidate name, email, confirmation code, booked_at
10- Admin can mark a booking as 'no_show' from the Sheet
11
122. Booking lookup page at src/pages/BookingLookup.tsx (public):
13- Single Input for confirmation code with a 'Find My Booking' Button
14- On submit: SELECT from bookings JOIN exam_slots JOIN exams WHERE confirmation_code = ? AND status = 'confirmed'
15- Show booking details Card: exam name, datetime, location, candidate name, status Badge
16- 'Cancel Booking' Button with AlertDialog — on confirm, set status = 'cancelled' and INCREMENT exam_slots.remaining_seats by 1
17- If not found: show 'No booking found with that code'