Database Schema

Agon uses SQLite (WAL mode) as its local database. All datetime values are stored in UTC (timezone-naive).

Entity Relationship Diagram

erDiagram
    users {
        int id PK
        string email UK
        string password_hash
        string full_name
        string role
        bool is_active
        datetime created_at
        datetime updated_at
    }

    clients {
        int id PK
        int location_id
        string email UK
        string password_hash
        string full_name
        string phone
        date dob
        string notes
        string push_token
        bool is_active
        datetime created_at
        datetime updated_at
    }

    instructors {
        int id PK
        int user_id FK
        int location_id
        string bio
        string specialties
        datetime created_at
        datetime updated_at
    }

    class_templates {
        int id PK
        int location_id
        string name
        string description
        int duration_minutes
        int default_capacity
        string color
        int default_instructor_id FK
        bool is_active
        datetime created_at
        datetime updated_at
    }

    scheduled_classes {
        int id PK
        int template_id FK
        int instructor_id FK
        int location_id
        datetime starts_at
        datetime ends_at
        int capacity
        string status
        string meeting_url
        datetime created_at
        datetime updated_at
    }

    bookings {
        int id PK
        int client_id FK
        int scheduled_class_id FK
        int location_id
        string status
        datetime created_at
        datetime updated_at
    }

    checkins {
        int id PK
        int booking_id FK
        int client_id FK
        int scheduled_class_id FK
        int checked_in_by FK
        string method
        datetime checked_in_at
    }

    waitlist {
        int id PK
        int client_id FK
        int scheduled_class_id FK
        int position
        string status
        datetime expires_at
        datetime created_at
        datetime updated_at
    }

    membership_types {
        int id PK
        int location_id
        string name
        string type
        float price
        string currency
        int credits
        int duration_days
        bool is_active
        datetime created_at
        datetime updated_at
    }

    memberships {
        int id PK
        int client_id FK
        int membership_type_id FK
        int location_id
        string status
        date starts_at
        date expires_at
        int credits_remaining
        int credits_used
        string stripe_subscription_id
        datetime created_at
        datetime updated_at
    }

    payments {
        int id PK
        int client_id FK
        int membership_id FK
        float amount
        string currency
        string status
        string provider
        string provider_payment_id
        datetime paid_at
        datetime created_at
        datetime updated_at
    }

    locations {
        int id PK
        string name
        string address
        string phone
        bool is_active
        datetime created_at
        datetime updated_at
    }

    studio_settings {
        int id PK
        string studio_name
        string timezone
        string currency
        string stripe_public_key
        string stripe_secret_key
        string cloudflare_tunnel_token
        int booking_cancellation_hours
        bool waitlist_enabled
        datetime created_at
        datetime updated_at
    }

    consent_log {
        int id PK
        int client_id FK
        string type
        bool granted
        string ip_address
        datetime created_at
    }

    email_templates {
        int id PK
        string name
        string subject
        string body_html
        string variables
        bool is_active
        datetime created_at
        datetime updated_at
    }

    email_event_assignments {
        int id PK
        string event_type
        int template_id FK
    }

    smart_lists {
        int id PK
        string name
        string filters_json
        datetime created_at
        datetime updated_at
    }

    users ||--o{ instructors : "has profile"
    clients ||--o{ bookings : "makes"
    clients ||--o{ memberships : "holds"
    clients ||--o{ waitlist : "joins"
    clients ||--o{ payments : "makes"
    clients ||--o{ consent_log : "logs"
    scheduled_classes ||--o{ bookings : "receives"
    scheduled_classes ||--o{ waitlist : "has"
    scheduled_classes ||--o{ checkins : "records"
    class_templates ||--o{ scheduled_classes : "spawns"
    instructors ||--o{ scheduled_classes : "teaches"
    instructors |o--o{ class_templates : "default for"
    bookings ||--o| checkins : "has"
    memberships ||--o{ payments : "paid via"
    membership_types ||--o{ memberships : "defines"
    email_templates |o--o{ email_event_assignments : "assigned to"

Key Tables

The diagram above shows the original core scheduling/membership/payment schema. Later feature areas added their own tables on top of it, summarized below rather than drawn into the diagram.

Table	Purpose
users	Studio manager and instructor accounts (login credentials)
clients	Clients who book classes via the mobile app
instructors	One-to-one profile linked to a users row
class_templates	Reusable class definition (Yoga, HIIT, Pilates…)
scheduled_classes	A specific occurrence of a template at a date/time
bookings	A client's booking for a scheduled class
checkins	Confirmed attendance record for a booking
waitlist	Queue entry when a class is full
memberships	A client's active subscription or credit pack
membership_types	Template for recurring plans or credit packs
payments	Payment records (Stripe or manual)
locations	Physical studio location (V1: always id=1)
studio_settings	Singleton row with studio configuration
consent_log	GDPR consent events
email_templates	Custom transactional email bodies
email_event_assignments	Maps lifecycle events to custom templates
smart_lists	Saved client filter queries for targeted messaging

Other tables (added by later features)

These were not part of the original diagram above; each belongs to a feature area shipped after the initial schema.

Feature area	Tables
Appointments (1:1 bookings)	appointments, appointment_services, appointment_service_locations, instructor_availability
Retail / POS	products, product_sales, product_sale_items
Gift cards	gift_cards, gift_card_redemptions
Promo codes	promo_codes, promo_code_usages
Invoicing	invoices
Marketing Automations	marketing_automations, marketing_automation_enrollments
Tags & Smart Lists targeting	tags, client_tags, auto_tag_rules
Waivers	waivers, waiver_signatures
SMS messaging	sms_templates, sms_event_assignments
Staff & payroll	roles, staff_pay_components, payroll_runs, payroll_line_items
Stripe billing (recurring memberships)	stripe_customers, stripe_prices, stripe_checkout_sessions, stripe_subscriptions, stripe_webhook_events — alongside the memberships.stripe_subscription_id column shown above
Push/in-app notifications	notification_log
Migration (see Migration overview)	migration_job

ai_usage_log and ai_response_cache also exist (AI Assistant request logging and response caching) but are intentionally left out of both tables above — they're operational/internal rather than studio business data, and their exact shape is more likely to change as the AI Assistant evolves.

Design Notes

UTC-naive datetimes

All DateTime columns store UTC values without timezone info. The application layer always compares with utcnow() from app.utils. Never insert timezone-aware datetimes into the database.

location_id convention

Every business entity carries location_id (default 1). Location 1 (Main Studio) is seeded at migration time. This column is the hook for V2 multi-location support — no schema change needed.

Soft delete vs hard delete

• Soft delete (status/is_active): clients, instructors, class templates, membership types, bookings, memberships, scheduled classes.
• Hard delete: Only on explicit GDPR erasure request. Personal data is anonymised in-place; booking/payment history rows are retained for audit purposes.

See ARCHITECTURE.md in the codebase for the full delete strategy and cascade rules.

Composite indexes

Performance-critical query patterns are covered by composite indexes:

Index	Columns	Query pattern
idx_booking_class_status	scheduled_class_id, status	All confirmed bookings for a class
idx_booking_client_status	client_id, status	Client's booking history
idx_membership_client_status	client_id, status	Client's active membership lookup
idx_class_starts_at_location_status	starts_at, location_id, status	Calendar date-range queries