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

erDiagram enquiries { TEXT id PK TEXT email TEXT name TEXT dob TEXT phone TEXT source INTEGER processed TEXT age_group_id TEXT events TEXT invite_id TEXT note TEXT raw_payload TEXT environment TEXT created_at } invites { TEXT id PK TEXT token UK TEXT enquiry_id 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 TEXT invite_id TEXT age_group_id 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 } academy_waitlist { TEXT id PK TEXT enquiry_id TEXT season_id 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 TEXT token UK TEXT used_at TEXT environment } membership_form_submissions { TEXT id PK TEXT enquiry_id TEXT membership_otp_id 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 : uses 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.