CCSTA Booking & Dispatch

A school-bus association serving BC's Lower Mainland ran field-trip bookings on Excel quote sheets, phone calls, and paper driver sheets: schools had to call for a price, the office re-typed every trip, and drivers got their day-of details on paper. The pricing rules (bus size by headcount, member vs non-member rates, minimum hours, surcharges) lived in a spreadsheet only the office could read, and nothing stopped a bus or driver from being booked twice on the same day.

A TanStack Start (SSR React) app on Supabase with one surface per role: schools request a quote at /quote and see an itemised ballpark before creating an account; the office prices, approves, and assigns a bus and driver from /admin; drivers open /driver on a phone and get leave-at time, bus number, tap-to-navigate pickup, a tap-to-call day-of contact, and a pre-trip safety checklist. The quote form autosaves a draft to the browser, so an anonymous visitor can see the estimate, create an account, and land back on the pre-filled form. The office's price comes from a server-side PL/pgSQL calculate_estimate function that reads hourly rates and surcharges from rate_config / surcharge_config tables, sizes buses with a seat-based rule (K–4 riders three to a seat, older riders two), adds driver yard time matched from a destinations table, applies minimum billable hours, overtime and a fuel surcharge, then GST. A suggest_assignment function returns only driver+bus pairs that clear bus size, air-brake certification, the driver's availability calendar, and same-day double-booking, and confirm_trip re-checks the clash before writing the trip. Emails are queued into a notification_log table by the workflow functions and drained by a Deno edge function through Resend, so nothing breaks with no email provider configured; driver accounts exist only via an admin-gated invite edge function.

• Authoritative pricing lives in the database: calculate_estimate is a SECURITY DEFINER Postgres function with a pinned search_path, callable only by the quote's owner or an admin, and every rate and surcharge it uses comes from rate_config / surcharge_config, so the office can change the rate sheet without a deploy. The anonymous quote form shows a client-side ballpark using the same seat rule and public non-member rates; the office's price never comes from that number.
• One capacity rule in three places that must agree: the customer's live preview, the server estimate, and the admin's bus-suggestion engine all compute seats = K–4 ÷ 3 + (Grade 5+ and adults) ÷ 2, replacing an earlier two-per-bench model that let an 18-passenger bus take 36 riders.
• Pre-launch security review closed two privilege-escalation vectors: signup trusted a role field in user metadata, and the profiles UPDATE policy had no WITH CHECK, so a user could PATCH their own role via REST. Because RLS can't see the OLD row, the fix pairs a WITH CHECK policy with a BEFORE UPDATE trigger that rejects role changes from any non-admin JWT, and the signup trigger function was revoked from the RPC surface.
• Double-booking is caught twice: suggest_assignment excludes drivers and buses already on a non-cancelled trip that day (plus drivers marked Away and air-brake buses the driver isn't certified for), and confirm_trip re-runs the clash check because the suggestion list can go stale between open and click.
• Built for a phone-first driver app's real failure modes: checklist and availability taps confirm Saved / Couldn't save and revert on failure (it is a safety record); pickup address and day-of contact are copied onto the trip at confirm time so the driver screen never depends on a joined quote; the route preview uses OpenStreetMap tiles and OSRM instead of a paid directions API.

• 33 numbered SQL migrations (000–032) covering schema, RLS policies, SECURITY DEFINER workflow functions, and seed data
• Seat model: 18/47/56-passenger buses carry 9 / 23.67 / 28 bench seats; each seat takes 3 K–4 riders or 2 older riders; seats needed = young ÷ 3 + (older + adults) ÷ 2; the same formula runs in the client preview, calculate_estimate, and suggest_assignment
• Estimate = billable hours (trip time + driver yard time, minimum applied) × hourly rate (by bus size and customer type) × bus count + flat fuel surcharge per bus + overtime beyond a configured hours threshold, then GST; every value is read from rate_config / surcharge_config
• Three roles (customer / driver / admin); all 18 tables created by the migrations have Row-Level Security enabled; new signups are always customer, drivers are invite-only, admins are provisioned manually
• 2 Deno edge functions: invite-driver (admin-JWT gated) and notify-send (drains pending notification_log rows via Resend)
• Michael authored 31 of 349 commits; the remaining 318 are Lovable's bot (frontend scaffold and visual edits)