Data model
Table map
auth.users (Supabase)
│ 1-1 via handle_new_user() trigger
↓
public.profiles ─────────── one row per diver / staff / admin
│
│ 1-many ┌── events (ONE table: dives, courses,
│ │ adventures — see `kind`)
↓ │── prices (linked by events.price)
public.bookings ──── event_id → events(id)
│ │── rooms (via the event_rooms junction)
│ 1-many │── addons (via the event_addons junction)
↓ │── travel_destinations
public.payments │ (via the event_destinations junction)
(staff ledger) │── cancellation_policies
│ │── trip_templates (reusable trip copy)
│ 1-many └── cert_levels
↓
public.credits ────────── money the shop owes a diver (see payments.md)
public.booking_discounts one diver asking for one discount on one booking;
approving writes a booking_amendments row
(discounts ── event_discounts ── the event's offer)
public.admin_notes ────── event_id XOR booking_id (operational memos)
public.diver_notes ────── per-diver standing facts (allergies, accommodations)
public.admin_audit_log ── append-only changelog of admin mutations
public.duties ─────────── staff/admin assignments per event
public.dive_sites ─────── public catalog rendered on /map
public.push_subscriptions / push_notifications_sent (cron infra)Every child of an event — bookings, duties, event_waivers, event_vehicles, event_ride_groups, event_discounts, notifications — points at it with a plain event_id. There is no eo_dive_id / eo_course_id XOR anywhere in the schema — the only XOR left is admin_notes, which pins a memo to either an event or a booking.
App-owned tables
| Table | Key columns | Notes |
|---|---|---|
profiles | id (= auth.users.id), role | role in ('diver','staff','admin'). Row auto-created by handle_new_user() on signup. Personal + cert + sizing + emergency contact + gear-owned + gear sizes + agreed_to_terms_at. |
bookings | id, user_id, event_id, status, details (jsonb), refund_requested_at | event_id NOT NULL → events(id) (ON DELETE CASCADE). details shape enforced app-side by BookingDetails in src/types/database.ts. Unique per (user, event). After insert, most columns are immutable for divers — a trigger in the baseline schema makes a diver's booking tamper-resistant by design. |
payments | id, user_id, booking_id, amount, status, method, recorded_by | Ledger entries, staff-inserted. status in ('pending','paid','refunded'). |
diver_notes | id, profile_id, created_by, content, edited_* | Per-diver standing facts (allergies, accommodations) — staff/admin can read+insert under their own attribution; admin or own-author can update/delete. profile_id/created_by/created_at frozen by trigger so RLS can't be sidestepped. |
admin_notes | id, event_id | booking_id, created_by, tag, content, resolved_* | The operational memo table — a note pinned to one event or one booking (CHECK admin_notes_target_present: exactly one, the last XOR in the schema). Tags: urgent / payment / gear / logistics / cert / medical / note / general. Resolution flags come as a trio (all null or all set, DB-enforced). Read/insert open to staff+admin (insert requires created_by = auth.uid()); update/delete admin-only. See admin.md. |
admin_audit_log | id, actor_id, action, target_table, target_id, before, after | Append-only audit trail for admin mutations. Insert via DB triggers; reads admin-only. |
duties | id, assignee_id, role, start_date, end_date, event_id | Staff-or-admin shift assignments. Trigger enforces assignee_id references a profile with role in (admin, staff). |
vehicles | id, name, passenger_seats, active | Transport-fleet catalog. passenger_seats is the car's total physical seats, and every one of them is rideable: there is no driver-assignment concept, so divers and on-duty staff compete for the same seats and event_ride_seats() simply subtracts the staff. Staff+admin read, admin write. |
event_vehicles | id, vehicle_id, event_id, notes | Which car is allocated to which event. A car may serve any number of events, at most once each (unique (event_id, vehicle_id)). Staff+admin read, admin write. Assigned on the logistics day view, the event's Transportation tab and the create/edit event forms. |
event_ride_groups | (ride_day, event_id) PK, group_id | Which of a day's events travel together — the events sharing a group_id form one "run" and pool their cars, divers and staff; an event with no row rides alone. group_id has no parent table: the group is the set of rows. Staff read, admin write, set by the Shared transport picker on /admin/logistics. See admin.md. |
packed_pieces | (pack_day, booking_id, item) PK, packed_by, packed_at | The logistics board's shared pack list: a row per piece on the van, so every crew phone reads the same ticks (Realtime-published). A tick inserts, an untick deletes. Staff+admin read, insert (stamped packed_by = auth.uid()) and delete; divers and anon nothing. Cascades with its booking. See admin.md. |
dive_sites | id, name, lat, lng, dive_type | Public catalog rendered on /map; readable by all authenticated users. |
waiver_signatures | id, diver_id, waiver_code, waiver_version, signed_name, signed_at, event_id | Append-only e-signature records. The waiver catalog + global rules live in code (src/config/waivers.ts), not the DB — these rows only record who signed what, when. Annual waivers leave event_id null; per-event waivers set it. Writes go through the sign_waiver() RPC (diver reads own; staff+admin read all). |
event_waivers | id, event_id, waiver_code, mode | Per-event override of a waiver's global rule: mode require adds it, exempt drops it for one event. event_id NOT NULL → events; one override per (event_id, waiver_code). Read by any authenticated user (the registration form needs it); admin write. Edited on the admin Edit-event form. |
cert_levels | id, agency, name, prereq_cert_id | Reference data for the certification picker. Self-referential prerequisite chain. |
cancellation_policies | id, title, cancellation_policy | Bubble-imported reference data linked from events rows via cancel_policy. |
shop_contact | singleton, email, phone, address, maps_url, updated_at/_by | The shop's own contact details. Exactly one row (check (singleton)), shipped EMPTY by 20260905120000 — a shop's address is not something core can guess, and a placeholder would be a wrong address printed on a real registration. Publicly readable (the pages that print the shop's email include ones a diver reaches before approval, and the terms-acceptance page, which runs from an emailed link with no session) and admin-updatable; no insert or delete policy exists, so the row cannot be removed. Empty strings mean "not published yet" and every surface handles that; a malformed email or map URL is refused by a check constraint. Read by the app through ShopContactProvider, and per request by the edge functions that mail the shop (_shared/shop-contact.ts). Admin-managed at /admin/contact. |
contact_channels | id, kind, label, url, sort_order, active | The ways a diver can reach the shop, in the order the Contact tab lists them. Ships empty: which services a shop is on is exactly what this table exists to stop core from deciding. kind is a closed vocabulary (line, whatsapp, telegram, messenger, instagram, wechat, signal, phone, sms, other) pinned to CONTACT_CHANNEL_KINDS — each carries a glyph and brand color that live in code, because shop-authored markup rendered into the page is an XSS hole; other takes any link with a neutral glyph. url holds an https link for a chat service and a bare phone number for phone / sms (a check constraint enforces which), and channelHref adds the tel: / sms: scheme. A null label uses the deployment's own translated wording for that service. Publicly readable, admin-written. |
payment_methods | id, key, label, surcharge_percent, bank_name, bank_code, account_number, account_holder, swift_bic, pay_url, notes, collects_invoice_email, shows_shop_contact, sort_order, active | How divers can pay. key is the stable value bookings.details.payment_method records, so a method can be renamed or repriced without orphaning a booking; everything else is what the register form and the emailed PDF print. Publicly readable (the register form renders before sign-in), admin-written. Admin-managed at /admin/payment-methods; see payments.md § Payment methods. |
trip_templates | catalog | Reusable "what's included" / not-included / transportation / itinerary / prerequisites copy an event links to via events.trip_template_id; surfaces in the booking form. Renamed from dive_travel (20260708050000). |
scheduled_trips | id, title, destination, status, price, addon_ids, room_type_ids | The shop's own curated, dated trips shown on the diver Scheduled Trips tab. Admin-managed base table (admin-only RLS); divers read published rows via list_scheduled_trips(). Carries addon_ids/room_type_ids (into the shop addons/rooms catalog) so divers register self-contained for a cost estimate — same flow as packages, minus tiers/partner. Distinct from packages (open-ended travel abroad) and the events.is_trip flag. See packages.md. |
scheduled_trip_registrations | id, scheduled_trip_id, diver_id, estimated_cost, details, status | One row per diver-registration for a scheduled trip; frozen estimate snapshot in details. No kickback (the shop's own trip). Admin-only base table; divers create via the register-scheduled-trip edge fn and read their own via list_my_scheduled_trip_registrations(). Partial unique index keeps one live registration per diver per trip. |
event_rooms / event_addons / event_destinations / event_discounts | junctions | FK junctions linking rooms / add-ons / destinations / offered discounts to events by event_id. Reconciled by the set_event_relations RPC (the single write path). |
discounts | id, label, description, kind, value, active, sort_order | The shop's discount catalog — kind is percent (1–100) or fixed (a flat amount). Publicly readable so the register form can render the offer before sign-in; admin-written. See payments.md § Discounts. |
booking_discounts | id, booking_id, discount_id, status, amount, amendment_id, requested_*, decided_* | One diver asking for one discount on one booking. No write policy at all — every write goes through request_booking_discount / decide_booking_discount, so no client can mint an approval. An approval stamps the amount and the negative booking_amendments row that is the actual money; discount_id is ON DELETE RESTRICT so a granted discount outlives the shop's decision to stop offering it. |
push_subscriptions | endpoint (unique), user_id, p256dh, auth | One row per device. Diver owns their rows (RLS). |
push_notifications_sent | (user_id, event_id, kind) composite PK | Idempotency ledger for the push cron. Service-role-only. |
trusted_partners | id, name, country, location, website, contact_name, contact_email, vouch_notes, logo_url, default_kickback_rate, active, created_by | Unified registry of vouched partner dive shops — powers both the diver Trusted Partners directory and the shops that host Packages (unified from the old thin trusted_partners + richer partner_shops by 20260708080000). Admin-only RLS on every verb — divers never see contact_email; they read id / name / region (= coalesce(location, country)) / blurb (= vouch_notes) / website for active, reachable rows through the list_trusted_partners() RPC, and message a partner via the contact-trusted-partner edge function (which resolves contact_email server-side). packages.trusted_partner_id FK-references it. See trusted-partners.md and packages.md. |
events table + catalog reference tables
Dives, courses and adventures are ONE table, public.events, discriminated by kind (migrations 20260702000000–20260702000400 collapsed the old Bubble EO_dives / EO_courses pair). Bookings, duties, admin_notes, event_vehicles, and waiver rows all reference it by a single event_id → events(id) (no more eo_dive_id / eo_course_id XOR).
events columns | Notes |
|---|---|
id (uuid), kind, admin_title, display_title, calendar_title | shared identity. kind in ('dive','course','adventure') — the DB's events_kind_check |
price → prices, cancel_policy → cancellation_policies, prereq_cert_id → cert_levels, trip_template_id → trip_templates | catalog links |
capacity, fully_booked, full_payment_deadline, cancel_date, cancelled_at, dive_days, prereqs, req_dives, featured_image | shared |
has_transport (NOT NULL, default true) | shared. False when the shop drives nobody to this event — a dry course held at the shop. The register forms then put no ride question and fetch no seat tally, and create-registration forces details.transportation false. Set in the vehicle section of the admin event form; see admin.md. Every payload to create_events_with_relations must carry it: jsonb_populate_record leaves an absent key NULL rather than falling back to the default |
enters_water (NOT NULL, default true) | shared. False when nobody who registers goes in the water — an EFR/CPR class, an equipment course, a BBQ. The Logistics board then lists those registrants as non-divers rather than divers, which is the split the shop's insurer asks for; see admin.md. Ask it through eventEntersWater() (src/lib/participants.ts), never raw: an adventure answers no by kind whatever the column says. Set on the admin event form, and carried in every create_events_with_relations payload for the same NOT NULL reason as has_transport (20260925100000) |
date-envelope kinds: start_date, end_date, start_time | dives and adventures carry a scalar start/end envelope |
dive-only: nitrox_required, is_boat_dive | the genuinely diving-specific flags |
dive-only (form-gated): is_trip, notes, featured, is_private | is_trip/is_boat_dive are independent boolean not null default false flags (see events-and-bookings.md). The columns sit on every envelope kind; the admin form offers them on dives, under hasDiveFlags |
non-course kinds: gear_rental | free text describing the rental terms. It does not decide whether gear is offered — gear_included does |
gear_included (NOT NULL, default false) | shared. True when the event puts no gear question and bills no gear: the fee covers a set, or nothing goes in the water. create-registration re-reads it and overwrites details.gear, the same way it forces details.transportation. Whether a set is physically packed is packsAGearSet() — this flag and entersTheWater(kind), so an overland outing that bundles "gear" packs none. Replaced a substring match on course titles (20260911120000), which now only pre-ticks the box on the admin form |
course kinds: course_days (date[], max 4 — the days a course runs on; see events-and-bookings.md), course_name, included, schedule, starting_at | discrete session days, no envelope |
series_id groups the batch of occurrences a recurrence rule generated (event_series); each occurrence is otherwise fully independent.
Temporal model: date-envelope kinds use the scalar start_date/end_date/start_time envelope; course kinds use course_days[]. This asymmetry is genuine domain logic — src/lib/events.ts (courseToEvents / groupConsecutive) explodes a course's day-array into calendar segments, while a dive is one segment.
Ask what a kind does
Never branch on kind === 'dive'. The vocabulary and the questions live in src/lib/event-kinds.ts — usesDateEnvelope, usesCourseDays, heldAtShop, isInstructorLed, hasDiveFlags, entersTheWater, hasTerrainConditions, recordsSiteConditions, plus the DATE_ENVELOPE_KINDS / COURSE_DAY_KINDS / SITE_CONDITION_KINDS value lists that queries filter on. That file is deliberately import-free so the Deno edge functions and the push worker share it, and src/types/database.ts carries a compile-time guard pinning it to the DB's events_kind_check.
A kind === 'dive' ? … : … ternary silently means "course" in its else branch, so a new kind inherits course behavior with no compile error and often no visible symptom — an event that is simply never fetched.
Adding a kind touches separate DB vocabularies that no FK keeps in step: events_kind_check, push_notifications_sent_event_type_check (no FK to events, so a miss only shows up as rejected push rows), waivers.applies_to, and the kind check on dive_sites (asserted against SITE_CONDITION_KINDS in src/lib/dive-sites.test.ts).
The reference tables (prices, rooms, addons, trip_templates, cancellation_policies, travel_destinations) were renamed from their Bubble originals and cleaned (migration 20260703000000): each now has a uuid id primary key and the import-cruft columns were dropped — read-mostly catalog data, admin-editable. (dive_travel was later renamed to trip_templates, and travel_destinations lost its unused latitude / longitude / northeast_diving columns — see the migration history below.) Rooms/add-ons/destinations link to events through the junctions event_rooms, event_addons, event_destinations (each (event_id, <ref>_id)), reconciled by the set_event_relations RPC. All dates are interpreted as shop-local (locale.timezone).
Normalization into the uniform AppEvent shape lives in src/lib/events.ts — fetchEventsInRange, fetchEventsForBookings, fetchUpcomingEventDays. Use AppEvent everywhere in the UI rather than reading raw events rows.
dive_sites — the shop's places
One row per place, and the reason "Bat Cave" means the same thing everywhere: events.site_id says where an event goes, and almanac_records.site_id says what it was like there.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | |
name | text | Unique per kind, case-insensitively — (kind, lower(name)) — so casing cannot mint a second Bat Cave |
kind | text | dive / adventure: the events vocabulary narrowed to the kinds that answer recordsSiteConditions. A course runs from the shop and has no site of its own. The almanac's dive/adventure toggle filters on this |
region | text | Optional grouping shown beside the name, e.g. "Longdong" |
notes | text | Entry, parking, hazards — staff-facing |
active | boolean | false retires a site: its history stays, but it stops being offered |
Any authenticated user reads it (the almanac's submission form is a diver surface); admin writes. Curated at /admin/dive-sites.
almanac_records — crowdsourced environmental observations
Divers submit environmental and weather observations for the places the shop goes; staff/admin rule on each one. A record is a site on a date, drawn from the dive_sites catalog below, and the almanac reads them back by calendar date — a day is the unit, and every site observed that day shares a bucket. Only approved records reach the crowd, on /almanac — the page also lists a diver every entry they have filed, on its own Your entries tab, and shows staff the review queue.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | |
created_at / updated_at | timestamptz | |
diver_id | uuid → auth.users | The submitting diver |
site_id | uuid → dive_sites(id) | The place observed. ON DELETE RESTRICT — a site carrying observations is retired (active = false), not deleted |
obs_date | date | The date of the observation |
air_temp_c | numeric(4,1) | Optional |
water_temp_c | numeric(4,1) | Optional |
visibility_m | numeric(4,1) | Optional |
current_strength | text | calm / light / moderate / strong / very_strong |
wave_height_m | numeric(3,1) | Optional |
wave_period_s | numeric(3,1) | Optional |
weather | text | clear / partly_cloudy / cloudy / overcast / rain / thunderstorm / windy / fog / typhoon |
coral_health | text | excellent / good / fair / poor / bleaching |
elevation_m | numeric(5,0) | Adventure-specific |
route_condition | text | dry / wet / muddy / icy / snow / rockfall |
summit_visible | boolean | Adventure-specific |
status | text | pending / approved / rejected |
approved_by | uuid → auth.users | Staff/admin who approved |
approved_at | timestamptz | |
staff_notes | text | Optional moderation notes |
Unique constraint: (site_id, obs_date, diver_id) — one observation per (diver, site, date).
authenticated is granted SELECT only. Every write goes through a SECURITY DEFINER RPC, so the moderation state machine can't be driven from the client — a diver has no way to mint an already-approved record:
submit_almanac_record(...)— files the caller's observation for an event/date, always aspending. Fields are assigned outright rather than coalesced (the form posts the whole record, so a cleared field clears), the date may not be in the future, and a record staff have already ruled on cannot be revised.withdraw_almanac_record(record_id)— deletes a pending record of the caller's own, and nothing else:security definerruns past RLS, so the function checks both itself, and answers "no such record" and "not yours" identically so ids cannot be probed. How a diver takes back an observation filed against the wrong site or day, which an edit cannot fix — site and date are what identify the record. A reviewed one stays: it is part of what the crowd has been shown, and a reading its author could delete afterwards would make every published figure provisional.moderate_almanac_record(record_id, status, staff_notes)— staff/admin only; stampsapproved_by/approved_at.statusmust beapprovedorrejected.almanac_records_in_range(from, to)— the approved records for a window of calendar days, each with its site name and the submitter's name. A date range rather than a list of ids, because the page reads the almanac by day.almanac_pending_records()— the staff review queue; raises for anyone else.
Reads of the table itself are governed by RLS: a diver sees their own rows (any status) plus every approved row; staff and admin see all of them. The Your entries tab reads whole rows for exactly that reason: an edit is seeded from the record, and since submit_almanac_record writes every column, a form filled from half a record would blank the other half on save.
What was seen is not on this row
Wildlife used to be a text[] on this table, filled from one comma-separated box, which made turtle, Turtle, green turtle and ウミガメ four different animals in every tally. It is now almanac_sightings — one row per animal, pointing at a taxa row keyed on its scientific name, with every language's word for it in taxon_names. submit_almanac_record takes p_taxon_ids uuid[], and the read RPCs return wildlife_taxa uuid[] plus wildlife_unmatched text[] (the pre-catalog free text staff have yet to map). A record naming a taxon a diver proposed and nobody has ruled on cannot be approved. See wildlife-taxa.md.
coral_surveys / coral_survey_colonies — crowdsourced coral monitoring
A CoralWatch Coral Health Chart survey, and the measurement that almanac_records.coral_health is not. That column is one ordinal judgment per site-date-diver; it carries no colony count, no depth, no water temperature at the moment of observation, and no reference standard a second observer could reproduce. Both surfaces stay: a diver filing conditions still says how the coral looked, and a diver doing a survey records colonies.
A survey is a header plus its colony rows. See coral-surveys.md for the instrument, the arithmetic and the reasoning.
coral_surveys column | Type | Notes |
|---|---|---|
id | uuid PK | |
created_at / updated_at | timestamptz | |
diver_id | uuid → auth.users | The surveying diver |
site_id | uuid → dive_sites(id) | ON DELETE RESTRICT, as for the almanac |
surveyed_on | date | Unique with site_id and diver_id — a second submission is a revision |
surveyed_at | time | Chart matching is done by eye under ambient light, so time of day is not comparable across a day |
depth_m / water_temp_c | numeric(4,1) | Optional; 0–100 m, −2–40 °C |
survey_method | text | random / transect / quadrat, default random |
transect_length_m | numeric(5,1) | Only meaningful for a transect |
notes | text | Optional |
status, approved_by, approved_at, staff_notes | Moderation, exactly as the almanac |
coral_survey_colonies column | Type | Notes |
|---|---|---|
survey_id | uuid → coral_surveys(id) | ON DELETE CASCADE |
ordinal | integer | Position in the survey, 1–100 |
coral_type | text | branching / boulder / plate / soft |
lightest_hue / darkest_hue | text | Chart columns B / C / D / E |
lightest_level / darkest_level | integer | Chart rows 1–6; 1 is bleached, 6 fully pigmented. darkest_level >= lightest_level is a check constraint — the chart is read palest first, so the reverse is a transposed pair |
diameter_cm | numeric(5,1) | Optional |
Writes are RPC-only: authenticated holds SELECT and nothing else. submit_coral_survey() files or revises (replacing the colony list wholesale), moderate_coral_survey() rules, coral_surveys_in_range() reads approved surveys with their colonies aggregated in, and coral_pending_surveys() is the staff queue.
Row-Level Security
RLS is on for every public.* table. The important patterns:
- Two SECURITY DEFINER helpers do the role checks so policies don't recurse into
profilesRLS:public.is_admin()— caller's profile hasrole = 'admin'public.is_staff_or_admin()— caller's profile hasrole in ('admin','staff')
- Diver-owned rows (
bookings,payments,push_subscriptions): users can read/insert/update/delete rows whereauth.uid() = user_id. - Staff+admin read on
profiles,bookings,payments,admin_notes,diver_notes— broadened from admin-only by thestaff_rolemigration. Writes on those tables stay admin-only exceptadmin_notes/diver_notes(staff can insert their own). bookingsis largely immutable for divers post-insert: the triggerbookings_diver_immutable(in20260423130000_core_rls_and_booking_immutability.sql) blocks diver writes to most columns. Diver can flipstatusto'cancelled'and stamprefund_requested_at; admins can mutate anything via theis_admin()policy.- Service-role bypasses RLS. The push cron + the
create-registrationedge function both use the service-role key for this reason — they need to write under multiple users' identities. - Tables with no user-facing policies (
push_notifications_sent,admin_audit_log) are service-role-only or admin-only — RLS is on but nofor selectto authenticated, so anon/diver reads return zero rows.
Migrations
Forward-only. Never edit a migration that has been make push'd. Applied migrations are locked — the registry in supabase_migrations will refuse the push if a checksum changed.
To evolve a table, write a new migration that does alter table … or adds a column / constraint / policy. File naming is YYYYMMDDHHMMSS_<slug>.sql.
Migration history
fundive ships a single squashed baseline, 20260703000000_baseline_schema.sql, that captures the entire schema — the unified events model, every app-owned table above, all RLS policies and triggers, and the SECURITY DEFINER RPCs (event_ride_seats, sign_waiver, event_confirmed_counts, set_event_relations, …). It collapsed the long per-feature migration lineage the platform grew in early development, so a fresh database is one baseline apply, not a replay of history.
Everything after the baseline is a forward migration (the ls supabase/migrations/ order is the source of truth):
20260705000000_event_ride_seats_reserve_crew.sql— redefinesevent_ride_seats()so capacity reserves the crew's seats:Σ passenger_seats − greatest(#assigned vehicles, #on-duty staff). One seat per vehicle is held as a floor, rising to the full on-duty staff count when staff outnumber the cars, so the seats offered to divers are what's genuinely rideable.20260706000000_trusted_partners.sql— thetrusted_partnerstable, its admin-only RLS, and thelist_trusted_partners()RPC (email-free projection). See trusted-partners.md.20260707000000_dive_trip_boat_flags.sql— addsis_tripandis_boat_dive(bothboolean not null default false) toevents. No keyword backfill — an admin ticks them per event. See events-and-bookings.md.20260707010000_notify_admins_ride_waitlist.sql— trigger that notifies admins when a booking lands on the ride waitlist (details.ride_waitlisted = true).20260707020000_event_ride_seats_authenticated_only.sql— revokes the defaultPUBLIC/anonEXECUTE onevent_ride_seats()and re-grants onlyauthenticated+service_role, so aggregate seat counts aren't exposed to unauthenticated callers (a guest's fetch fails open).20260724000000_ride_groups_shared_transport.sql—event_ride_groups(which events travel together on a day) and a rewrittenevent_ride_seats()measured across the whole run, returningseats / staff / capacity / claimed. Drops the old per-vehicle driver-seat reservation, which contradicted the admin planner. See admin.md.20260724010000_server_side_ride_waitlist.sql—details.ride_waitlistedis recomputed by a BEFORE trigger fromevent_ride_tally()instead of trusted from the client, so a full run can't be hidden from the admins. A run with no car at all counts as "capacity not configured" and is left unflagged, matchingcanRequestRide.20260708020000_trip_board_definer_functions.sql— replaces the Packages diver-facing views withlist_package_board()/list_my_package_referrals()SECURITY DEFINER functions (email-free, kickback-free projections).20260708030000_rename_trip_board_to_packages.sql— renames the "Trip Board" feature to Packages end to end: tablestrips→packages,trip_referrals→package_referrals; RPCslist_trip_board→list_package_board,list_my_trip_referrals→list_my_package_referrals,express_trip_interest→express_package_interest; plus indexes, policies, and the set-code trigger. See packages.md.20260708040000_scheduled_trips.sql— thescheduled_tripstable (the shop's own dated, in-app-bookable trips), its admin-only RLS, and thelist_scheduled_trips()SECURITY DEFINER function (published rows, carrying the linked event's kind so the diver card can build a register link).20260708050000_rename_dive_travel_to_trip_templates.sql— renames thedive_travelreference table totrip_templatesandevents.divetravel_idtoevents.trip_template_id.20260708060000_drop_travel_destinations_coords.sql— drops the unusedlatitude/longitudecolumns fromtravel_destinations.20260708070000_drop_travel_destinations_northeast_diving.sql— drops the unusednortheast_divingcolumn fromtravel_destinations.20260708080000_unify_partner_tables.sql— merges the thintrusted_partnersdirectory into the richerpartner_shopsregistry and renames the result back totrusted_partners(the superset). Repointspackages.partner_shop_id→packages.trusted_partner_id, rebuilds thelist_trusted_partners()and Packages definer functions against the unified table. See trusted-partners.md and packages.md.
BookingDetails JSONB shape
Defined in src/types/database.ts — that file is the source of truth. DB only enforces jsonb_typeof(details) = 'object'. Current shape:
interface BookingDetails {
gear?: {
rent: boolean
included?: boolean // event bundles gear (e.g. OW course)
items?: string[] // chosen gear items (à-la-carte is the
// only way gear is rented)
assistance_note?: string // diver picked "ask a human"; their note
// (when set, rent is false)
// no sizes here: what fits a diver lives
// on their profile, which is what the
// pack list and the fit lookup read
}
room?: { option_id?: string | null; notes?: string | null }
add_ons?: string[] // addons.id list
discount_requests?: string[] // discounts.id list, IN-FLIGHT ONLY: the
// register form sends it,
// create-registration turns each into a
// booking_discounts row and strips the
// key. Never present on a stored booking
transportation?: boolean
payment_method?: string // payment_methods.key the diver chose
pay_deposit_only?: boolean // deposit-only-at-registration flag
nitrox_course_addon?: boolean
charges?: ChargeLine[] // itemized snapshot — see below
total?: number // final-charge snapshot
deposit?: number // prices.deposit_amount snapshot
cancellation_policy_acked_at?: string // gate for submit when policy attached
}discount_requests is the one key that never survives the write. A discount is worth nothing until an admin approves it, so letting the frozen details carry it would give the booking a second, unapproved account of what it owes — see payments.md § Discounts.
Design note: total, deposit, and charges are snapshotted into the booking so later catalog price changes don't retroactively alter what the diver owes — the lesson from removing the full-gear-set package, which had silently rewritten paid divers' amounts because every surface recomputed the breakdown from current prices. cancellation_policy_acked_at is preserved across edits — admin edits to notes/details don't reset the diver's prior ack.
charges (ChargeLine[]) — the itemized breakdown behind total (base, per-item gear, room, each add-on, transport, nitrox course, card surcharge), each { kind, label, amount }. Built once by buildCharges() in src/lib/booking-charges.ts at registration and rendered from the snapshot everywhere: the PDF/email, the diver's Bookings/Payments pages, and the admin event + per-diver views (via the shared <ChargeBreakdown> and BookingPaymentsBlock). Bookings created before this field existed have no snapshot; resolveCharges() reconstructs their lines from the stored selections using current catalog prices (so the figures can drift) — it never mutates stored rows.