Supabase Schema
Table definitions, column types, RLS policies, and data flow for the Supabase database backing ActivityFox.
Supabase Schema
ActivityFox uses Supabase Cloud as the source of truth for activities, sessions, child preferences, and vendor signups. Sharetribe handles users, listings (one per published session), and transactions.
Tables
activities
The program a vendor offers. One activity can have many sessions.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
vendor_id | text NOT NULL | Sharetribe user ID |
title | text NOT NULL | |
description | text | |
category | text NOT NULL | One of the 10 canonical categories |
subcategory | text | |
location_address | text | |
location_lat | decimal | |
location_lng | decimal | |
age_group_min | int | |
age_group_max | int | |
image_urls | jsonb | Default '[]' |
status | text | draft, published, archived. Default draft |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
sessions
A specific scheduled offering of an activity. Each published session creates a Sharetribe listing.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
activity_id | uuid FK | References activities(id) ON DELETE CASCADE |
listing_id | text | Sharetribe listing ID (set on publish) |
label | text NOT NULL | e.g. "Term 1 Mon/Wed 3-5pm" |
start_date | date NOT NULL | |
end_date | date NOT NULL | |
days_of_week | text[] NOT NULL | e.g. {'MO','WE'} |
start_time | time NOT NULL | |
end_time | time NOT NULL | |
total_seats | int NOT NULL | |
seats_remaining | int NOT NULL | |
price_sub_units | int NOT NULL | Price in cents |
currency | text | Default 'AUD' |
caldav_calendar_id | text | Davis calendar path (set on publish) |
status | text | draft, published, cancelled, completed. Default draft |
refund_full_days | int | Full refund if cancelled >X days before. Default 7 |
refund_partial_days | int | Partial refund if cancelled >Y days before. Default 3 |
refund_partial_pct | int | Partial refund percentage. Default 50 |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
child_likes
Per-child activity favourites.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
child_id | text NOT NULL | Sharetribe child user ID |
activity_id | uuid FK | References activities(id) ON DELETE CASCADE |
created_at | timestamptz | Default now() |
UNIQUE constraint on (child_id, activity_id).
child_connections
Same-family child relationships.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
child_id | text NOT NULL | |
connected_child_id | text NOT NULL | |
relationship | text NOT NULL | twin, sibling, close-friend, friend, classmate |
score | int NOT NULL | twin=10, sibling=9, close-friend=8, friend=6, classmate=4 |
created_at | timestamptz | Default now() |
UNIQUE constraint on (child_id, connected_child_id). Connections are stored bidirectionally (both rows inserted in a single transaction).
child_interests
Per-child category interests.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
child_id | text NOT NULL | |
category | text NOT NULL | One of the 10 canonical categories |
created_at | timestamptz | Default now() |
UNIQUE constraint on (child_id, category).
expressions_of_interest
Lightweight commitment tracking. Supplements Sharetribe EOI transactions.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
session_id | uuid FK | References sessions(id) ON DELETE CASCADE |
child_id | text NOT NULL | |
parent_id | text NOT NULL | Sharetribe parent user ID |
transaction_id | text | Sharetribe EOI transaction ID |
status | text | expressed, confirmed, withdrawn, declined. Default expressed |
created_at | timestamptz | Default now() |
updated_at | timestamptz | Default now() |
UNIQUE constraint on (session_id, child_id).
vendor_signups
Vendor interest form submissions from /for-providers.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | gen_random_uuid() |
org_name | text NOT NULL | |
contact_name | text NOT NULL | |
email | text NOT NULL | |
phone | text | |
program_types | text[] NOT NULL | Array of category slugs |
website | text | |
referral_source | text | |
notes | text | |
status | text | Default 'new' |
created_at | timestamptz | Default now() |
Migration
The migration SQL is at server/scripts/supabase-migration.sql. Run it against the Supabase SQL editor or via psql.
Data Flow
Vendor creates Activity (Supabase)
└─ creates Sessions (Supabase)
└─ on publish:
├─ creates Sharetribe listing (for transactions/payments)
├─ creates CalDAV calendar (for date management)
└─ indexes to Meilisearch (for search)Supabase is the source of truth. Sharetribe listings, CalDAV calendars, and Meilisearch indexes are derived from Supabase data.