Data Model
All persistent data lives in Cloudflare D1 (SQLite). The schema is managed through numbered SQL migrations in db/migrations/. System configuration is stored separately in Cloudflare KV to minimise D1 request consumption.
Entity relationship diagram
invites { TEXT id PK TEXT token UK TEXT enquiry_id FK TEXT status INTEGER send_attempts TEXT sent_at TEXT accepted_at TEXT environment TEXT created_at }
age_groups { TEXT id PK TEXT code UK TEXT label TEXT booking_type INTEGER age_min_aug31 INTEGER age_max_aug31 TEXT session_days TEXT session_time INTEGER capacity_per_session INTEGER season_capacity INTEGER season_start_month INTEGER season_end_month INTEGER active INTEGER sort_order }
bookings { TEXT id PK TEXT enquiry_id FK TEXT invite_id FK TEXT age_group_id FK TEXT session_date TEXT session_time TEXT status TEXT attendance_note TEXT environment TEXT created_at }
academy_seasons { TEXT id PK TEXT code UK TEXT label TEXT start_date TEXT end_date INTEGER capacity TEXT status TEXT age_group_id FK }
academy_waitlist { TEXT id PK TEXT enquiry_id FK TEXT season_id FK TEXT token UK INTEGER position TEXT status INTEGER is_returning TEXT rolled_over_from_season_id TEXT response INTEGER send_attempts }
email_templates { TEXT id PK TEXT key UK TEXT name TEXT subject TEXT html TEXT text_content TEXT variables INTEGER active }
membership_otps { TEXT id PK TEXT enquiry_id FK TEXT token UK TEXT used_at TEXT environment }
membership_form_submissions { TEXT id PK TEXT enquiry_id FK TEXT membership_otp_id FK TEXT payload_json TEXT submitted_at }
enquiries ||—o{ invites : “has” enquiries ||—o{ bookings : “has” enquiries ||—o{ academy_waitlist : “has” enquiries ||—o{ membership_otps : “has” enquiries ||—o{ membership_form_submissions : “has” invites ||—o{ bookings : “used for” age_groups ||—o{ bookings : “categorises” age_groups ||—o{ enquiries : “assigned to” age_groups ||—o{ academy_seasons : “linked to” academy_seasons ||—o{ academy_waitlist : “contains” membership_otps ||—o{ membership_form_submissions : “validates”
Table reference
enquiries
The central table. Every inbound request creates exactly one row. All downstream activity (invites, bookings, waitlist entries, memberships) links back here.
| Column | Type | Notes |
|---|---|---|
id | TEXT PK | Prefixed ID: enq_... |
email | TEXT NOT NULL | Enquirer (parent) email |
name | TEXT | Parent name |
dob | TEXT | Athlete date of birth (YYYY-MM-DD) |
phone | TEXT | Optional contact phone |
source | TEXT | Origin: website, form, etc. |
processed | INTEGER | 0 = pending, 1 = processed |
age_group_id | TEXT FK | Resolved from DOB at enquiry time |
invite_id | TEXT FK | The active taster invite (denormalised) |
events | TEXT | JSON array of EnquiryEvent objects |
note | TEXT | Admin notes or “enquiring about: {name}“ |
raw_payload | TEXT | Original POST body as JSON |
environment | TEXT | development / staging / production |
The events column is an append-only log stored as JSON. Every meaningful state change appends a typed event:
type EnquiryEventType =
| 'academy_waitlist_added'
| 'academy_response_received'
| 'booking_invite_sent'
| 'booking_created'
| 'invite_expired'
| 'invite_resent'
| 'attendance_recorded'
| 'membership_invite_sent'
| 'membership_form_submitted'
| 'reminder_sent';
invites
Taster-path only — not used for the Academy. One invite per enquiry (though an enquiry can have its invite resent, tracked via send_attempts).
| Column | Type | Notes |
|---|---|---|
token | TEXT UNIQUE | 48-char hex string, used in the booking URL |
status | TEXT | pending → sent → accepted / expired |
send_attempts | INTEGER | Incremented by the retry cron |
last_send_error | TEXT | Last Resend API error message |
age_groups
The single source of truth for all age-group configuration. No age group data is hardcoded in application logic.
| Column | Type | Notes |
|---|---|---|
booking_type | TEXT | taster or waitlist — drives enquiry routing |
age_min_aug31 | INTEGER | Min age on 31 Aug of the athletics season |
age_max_aug31 | INTEGER | Max age on 31 Aug of the athletics season |
session_days | TEXT | JSON array e.g. ["Tuesday"] or ["Saturday","Tuesday"] |
capacity_per_session | INTEGER | Max bookings per session date |
season_capacity | INTEGER | Academy: total season places |
season_start_month | INTEGER | null = year-round |
active | INTEGER | 1 = included in routing; 0 = ignored |
bookings
Taster sessions only. Created when a parent selects a date via the /book/:token page.
| Column | Type | Notes |
|---|---|---|
session_date | TEXT | YYYY-MM-DD |
status | TEXT | confirmed → attended / no_show / cancelled |
attendance_note | TEXT | Free-text note from admin when recording attendance |
age_group_id | TEXT FK | Denormalised at creation time for reporting stability |
Unique index on (invite_id, session_date) prevents duplicate bookings per invite per date.
academy_seasons
One row per Academy season, typically running April – August.
| Column | Type | Notes |
|---|---|---|
status | TEXT | upcoming → open → closed → archived |
capacity | INTEGER | Total places in the season |
start_date / end_date | TEXT | YYYY-MM-DD; used by the rollover cron |
academy_waitlist
One row per enquiry per season they are considered for. An athlete can appear across multiple seasons via the season rollover cron.
| Column | Type | Notes |
|---|---|---|
token | TEXT UNIQUE | Unique per entry; used in /academy/respond/:token |
position | INTEGER | FCFS order within a season (lower = earlier) |
status | TEXT | waiting → invited → accepted / declined |
is_returning | INTEGER | 1 if athlete was accepted in any prior season |
rolled_over_from_season_id | TEXT | Set when created via the rollover cron |
response | TEXT | yes / no — recorded when parent responds |
email_templates
All email content is admin-editable in D1. Templates use {{variable}} syntax rendered by renderTemplate().
| Column | Type | Notes |
|---|---|---|
key | TEXT UNIQUE | One of 6 fixed keys (see Email Templates doc) |
html | TEXT | Full HTML body with {{variable}} placeholders |
text_content | TEXT | Plain-text fallback |
variables | TEXT | JSON array of variable names — used by admin preview UI |
active | INTEGER | 0 = template disabled (send will fail safely) |
membership_otps
One-time tokens sent to parents who attended a taster and have been invited to join the club.
| Column | Type | Notes |
|---|---|---|
token | TEXT UNIQUE | 48-char hex; used as query param in the membership URL |
used_at | TEXT | Set when the membership form is submitted |
Cloudflare KV — system configuration
Config is stored as a single JSON blob under the key config:system with a 5-minute TTL. On a miss, hard-coded defaults are used.
| Key | Default | Description |
|---|---|---|
site_name | East Grinstead Athletics Club | Used in email templates |
site_url | https://egac.pages.dev | Base URL for email links |
email_from | no-reply@updates… | Resend sender address |
venue_name | East Grinstead Leisure Centre | Used in booking confirmations |
venue_address | King Street, East Grinstead… | Used in booking emails |
weeks_ahead_booking | 8 | How many weeks of dates to show in booking invite |
capacity_per_slot | 2 | Default per-session capacity (overridden by age group) |
academy_max_age | 10 | Upper age limit for Academy routing |
admin_email | (empty) | BCC address for admin notifications |
Environments
Every row in enquiry-related tables carries an environment column (development / staging / production). Admin list queries filter by the current APP_ENV, so test data never surfaces in production views.