docs/DATABASE_SCHEMA.md
Rama main · réplica de lectura del repositorio original.
# Database Schema
ActaLog uses a relational database to store user data, workouts, movements, and workout history.
## Supported Databases
- **SQLite** (default for development, single-user deployments)
- **PostgreSQL** (recommended for production, multi-user deployments)
- Driver: `pgx/v5` (migrated from lib/pq in v0.8.0)
- Features: Schema isolation, connection pooling, 10-30% performance improvement
- **MariaDB/MySQL** (supported for production, shared hosting)
- Features: Connection pooling, full compatibility verified
## Schema Version
**Current Version:** 1.3.2 (migration head: 0.35.0 — no schema changes in v1.3.1 / v1.3.2)
> v0.35.0 adds per-dialect BEFORE UPDATE / BEFORE DELETE triggers on the `users` table that block writes to protected accounts at the database layer (L3 security). See `docs/security/PROTECTED_USERS.md` for the full protected-user policy and recovery procedures.
## Recent Changes (v0.27.0-beta)
- **Class Scheduling Phase 4**: Documents, packages, credits, waitlist, notifications
- New `documents` table - Document types required by organizations (waivers, liability forms)
- New `user_documents` table - Track user document completion status
- New `class_packages` table - Credit packages (e.g., "10-Class Pack")
- New `user_class_credits` table - User credit balances with expiration
- New `waitlist_entries` table - Waitlist queue for full classes
- New `class_notifications` table - Class reminders and waitlist promotions
- All tables have proper indexes and foreign key constraints
- Multi-database support verified (SQLite, PostgreSQL, MariaDB)
## Recent Changes (v0.26.0-beta)
- **Class Scheduling System (Phases 1-3)**: Core scheduling infrastructure
- New `gym_locations` table - Physical locations within gyms
- New `class_templates` table - Reusable class definitions
- New `schedule_slots` table - Recurring time patterns per template
- New `class_sessions` table - Scheduled class instances
- New `coach_assignments` table - Per-gym coach role assignments
- New `session_coaches` table - Coaches assigned to specific sessions
- New `reservations` table - User bookings with state tracking
## Recent Changes (v0.16.0-beta)
- **Notification Likes Feature**: Social engagement for gym community
- New `notification_likes` table (4 columns, 4 indexes + unique constraint)
- Columns: `id`, `notification_id`, `user_id`, `created_at`
- Foreign keys with CASCADE DELETE to `notifications` and `users`
- Unique constraint prevents duplicate likes from same user
- Indexes for fast lookup by notification_id and user_id
- Migration 0.16.0 creates table structure
- API endpoints: POST/DELETE/GET for like/unlike/list likes
- Frontend component: NotificationLikes.vue with thumbs up icon
- Liking a notification marks it as unread for original recipient
- Only recipient sees like count and liker names
- Users can like their own notifications
## Recent Changes (v0.15.0-beta)
- **Admin Announcements**: Gym-wide notification system
- Admin-only endpoint for creating announcements
- Sends notifications to all users in the system
- Flexible notification types (PR achievements, announcements, streaks, milestones)
- Complete audit trail for announcement operations
- API endpoint: POST `/api/admin/notifications/announce`
## Recent Changes (v0.14.0-beta)
- **Subscription Billing System**: Dual-level subscription management (user + organization)
- New `user_subscriptions` table (15 columns, 4 indexes)
- New `organization_subscriptions` table (15 columns, 4 indexes)
- Three subscription types: Free, Monthly, Annual
- Permanent Free option for founders/staff (never expires)
- Flexible access model: users have write access if EITHER personal OR organization subscription active
- Manual admin payment control (mark as paid/unpaid)
- Immediate read-only mode when expired (no grace period)
- HTTP 402 Payment Required for blocked write operations
- Read operations (GET) allowed when expired for viewing/exporting
- Migration 0.14.0 automatically seeds all existing users with permanent free subscriptions
- Complete audit trail for all subscription operations
- 10 API endpoints (8 admin, 2 user) for subscription management
- **Database Version Management**: System for testing migrations across versions
- SQLite snapshots: `db_versions/actalog_X.Y.Z.db`
- PostgreSQL schemas: `actalog_X_Y_Z` (schema-based versioning)
- MariaDB databases: `actalog_X_Y_Z` (database-based versioning)
- Automation scripts: `create-db-snapshot.sh`, `verify-version-databases.sh`
- Comprehensive documentation: `VERSION_DATABASES.md`, `MIGRATION_TEST_0.14.0.md`
- **Backward Compatibility**: Zero downtime deployment verified on all 3 database engines
## Recent Changes (v0.12.2-beta)
- **No schema changes** in this release
- **PWA Offline Functionality Fix**: Service worker and offline detection improvements
- Fixed service worker caching pattern for API endpoints
- Added robust offline detection in axios interceptor
- Added user-controlled PWA update mechanism
- Added offline save notification system
- Frontend: New UpdatePrompt component and PWA state management store
## Recent Changes (v0.12.1-beta)
- **No schema changes** in this release
- **MySQL/MariaDB Compatibility Fix**: Database-agnostic timestamp functions
- Fixed `addWorkoutMovementWithDistance()` to use `NOW()` for MySQL/MariaDB
- Fixed `refresh_token_repository.go` functions with hardcoded SQLite syntax
- Added `getTimestampFunc()` helper supporting all database drivers
- Documentation: Enhanced Docker host database troubleshooting guides
## Recent Changes (v0.12.0-beta)
- **No schema changes** in this release
- Frontend/PWA enhancements: Mobile overflow fix across 27 views, iOS safe-area handling
- Docker enhancements: OCI-compliant labels added to build scripts
- Admin UI: User Content view Actions column moved to first position
## Recent Changes (v0.11.0-beta)
- **Data Change Audit Logging**: Complete audit trail for data modifications
- New `data_change_logs` table tracks INSERT, UPDATE, DELETE operations
- Captures entity type, entity ID, operation type, before/after values as JSON
- Records user ID, email, and IP address for each change
- Integrated with WOD and Movement services for automatic logging
- Admin-only API endpoints for viewing and filtering logs
- Admin UI for browsing, filtering, and inspecting changes
- Cleanup endpoint for log retention management
- **Admin Features**:
- New Data Change Logs admin view with filtering by entity type, operation, user email
- Paginated data table with before/after value comparison
- Details dialog showing diff view for update operations
- Quick access from admin profile page
## Recent Changes (v0.8.1-beta)
- **No schema changes** in this release (database structure remains identical)
- **Cross-Database Backup/Restore**: Complete database-agnostic system
- Database-agnostic table existence checks (works with SQLite, PostgreSQL, MariaDB)
- Table column introspection for schema evolution support
- Automatic schema difference detection and handling
- **Cross-database migration support**:
- ✅ MariaDB → PostgreSQL
- ✅ SQLite → PostgreSQL
- ✅ MySQL → MariaDB
- ✅ Any combination of supported databases
- **Schema Evolution Support**:
- Restore old backups (v0.6.0+) to newer versions
- Column filtering: Handles removed columns gracefully
- New columns use DEFAULT values from schema
- Automatic data type conversion (especially boolean handling)
- **PostgreSQL Enhancements**:
- Automatic sequence reset after restore (`setval()` + `pg_get_serial_sequence()`)
- Prevents "duplicate key violation" errors on subsequent inserts
- **Data Migration Capabilities**:
- Development (SQLite) → Production (PostgreSQL) via backup/restore
- Emergency recovery to different database type
- Multi-tenant migrations using PostgreSQL schema parameter
- Zero manual SQL intervention required
## Recent Changes (v0.8.0-beta)
- **No schema changes** in this release (database structure remains identical)
- **Database Driver Migration**: PostgreSQL driver migrated from lib/pq to pgx/v5
- BREAKING for PostgreSQL users (see docs/POSTGRESQL_MIGRATION.md)
- Full backward compatibility for SQLite and MySQL/MariaDB
- 10-30% performance improvement for PostgreSQL workloads
- **New Database Features**:
- Schema isolation support via `DB_SCHEMA` environment variable (PostgreSQL)
- Connection pooling configuration: `DB_MAX_OPEN_CONNS`, `DB_MAX_IDLE_CONNS`, `DB_CONN_MAX_LIFETIME`
- Database-agnostic SQL abstraction layer for multi-database compatibility
- **Testing**: All three databases verified working (SQLite, PostgreSQL 16, MariaDB 11)
- **Documentation**: Created comprehensive PostgreSQL migration guide
## Recent Changes (v0.7.6-beta)
- No schema changes in this release
- Backend enhancements: Backup upload for migration, enhanced audit logging, cross-version restore compatibility
- Documentation planning: End-user help docs, admin documentation, test coverage
## Recent Changes (v0.7.5-beta)
- No schema changes in this release
- Backend enhancements: Remember Me functionality, database backup system activation
- Frontend enhancements: Admin user management integration, PR history date fixes
## Recent Changes (v0.7.4-beta)
- No schema changes in this release
- Frontend enhancements: Quick Log buttons on library cards and detail pages
## Recent Changes (v0.7.3-beta)
- No schema changes in this release
- Frontend enhancements: Quick Log integration on Performance screen, improved chart sorting
## Recent Changes (v0.4.6-beta)
- Added session management endpoints and audit logging
- Enhanced admin user management with delete functionality
- Fixed user repository List() method to include all admin fields
- All user security fields now properly exposed to admin interface
## Key Concepts: Organization, Gym, and Location
Understanding the relationship between organizations, gyms, and locations is essential for the scheduling system.
> **Important:** In ActaLog, **"Organization" and "Gym" are the same thing.** The database uses the term `organizations` but this represents your gym, box, or affiliate. The term "Gym Location" refers to physical spaces *within* a gym (like rooms or training areas).
### Terminology Mapping
| Database Table | Business Term | Also Known As | Examples |
|----------------|---------------|---------------|----------|
| `organizations` | **Gym** | Organization, Affiliate, Box, Studio | "CrossFit Downtown", "Iron Tribe Fitness", "F45 Training" |
| `gym_locations` | **Location** | Room, Area, Space, Training Zone | "Main Floor", "Studio A", "Outdoor Rig", "Yoga Room" |
### Quick Reference
- **Organization = Gym** — The business entity (your CrossFit box, fitness studio, etc.)
- **Gym Location = Room/Area** — A physical space within your gym where classes happen
### Conceptual Hierarchy
```
Organization (the gym itself)
└── Gym Locations (physical spaces within the gym)
├── Main Floor (capacity: 20)
├── Studio A (capacity: 15)
└── Outdoor Area (capacity: 30)
```
### Why This Design?
1. **Organization = The Gym Business**
- An organization represents the entire gym/affiliate/box entity
- It owns all resources: coaches, class templates, packages, documents
- Athletes join organizations (memberships)
- Subscriptions are at the organization level
2. **Gym Location = Physical Space Within a Gym**
- A single gym may have multiple training areas
- Different classes may occur in different locations simultaneously
- Each location can have its own capacity limit
- Examples: "6AM CrossFit" in Main Floor, "6AM Yoga" in Studio A
3. **Multi-Gym Support**
- Athletes can belong to multiple organizations (gyms)
- A coach can be assigned to multiple organizations
- Each organization manages its own schedules independently
### Data Flow Example
```
Organization: "CrossFit Downtown"
│
├── Gym Locations:
│ ├── "Main Floor" (capacity: 20)
│ └── "Yoga Studio" (capacity: 12)
│
├── Class Templates:
│ ├── "CrossFit WOD" (default location: Main Floor)
│ └── "Mobility Class" (default location: Yoga Studio)
│
├── Schedule Slots:
│ ├── CrossFit WOD → Mon/Wed/Fri 6:00 AM
│ └── Mobility Class → Tue/Thu 5:30 PM
│
└── Class Sessions (generated from templates + slots):
├── CrossFit WOD - Mon Jan 20, 6:00 AM @ Main Floor
├── CrossFit WOD - Wed Jan 22, 6:00 AM @ Main Floor
└── Mobility Class - Tue Jan 21, 5:30 PM @ Yoga Studio
```
### Foreign Key Relationships
| Child Table | → | Parent Table | Meaning |
|-------------|---|--------------|---------|
| `gym_locations` | → | `organizations` | Locations belong to a gym |
| `class_templates` | → | `organizations` | Class types are gym-specific |
| `class_sessions` | → | `organizations` | Sessions are hosted by a gym |
| `class_sessions` | → | `gym_locations` | Sessions occur at a location |
| `coach_assignments` | → | `organizations` | Coaches are assigned per-gym |
| `user_organizations` | → | `organizations` | Athletes belong to gyms |
## Entity Relationship Diagram
```mermaid
erDiagram
USERS ||--o{ WORKOUTS : creates_templates
USERS ||--o{ USER_WORKOUTS : logs_instances
USERS ||--o{ MOVEMENTS : creates
USERS ||--o{ WODS : creates
USERS ||--o{ REFRESH_TOKENS : has_sessions
USERS ||--o{ AUDIT_LOGS : performs_actions
USERS ||--o{ AUDIT_LOGS : is_target_of
USERS ||--o{ DATA_CHANGE_LOGS : makes_changes
USERS ||--o{ USER_SETTINGS : has_preferences
USERS ||--o{ USER_ORGANIZATIONS : belongs_to
USERS ||--o{ USER_SUBSCRIPTIONS : has_subscription
USERS ||--o{ NOTIFICATIONS : receives
USERS ||--o{ RESERVATIONS : books_classes
USERS ||--o{ COACH_ASSIGNMENTS : assigned_as_coach
USERS ||--o{ USER_CLASS_CREDITS : has_credits
USERS ||--o{ USER_DOCUMENTS : completes_documents
USERS ||--o{ WAITLIST_ENTRIES : waits_for_classes
WORKOUTS ||--o{ WORKOUT_MOVEMENTS : contains
WORKOUTS ||--o{ WORKOUT_WODS : includes
WORKOUTS ||--o{ USER_WORKOUTS : instantiated_as
USER_WORKOUTS ||--o{ USER_WORKOUT_MOVEMENTS : tracks_movement_performance
USER_WORKOUTS ||--o{ USER_WORKOUT_WODS : tracks_wod_performance
MOVEMENTS ||--o{ WORKOUT_MOVEMENTS : included_in_templates
MOVEMENTS ||--o{ USER_WORKOUT_MOVEMENTS : performed_in
WODS ||--o{ WORKOUT_WODS : included_in_templates
WODS ||--o{ USER_WORKOUT_WODS : performed_in
ORGANIZATIONS ||--o{ USER_ORGANIZATIONS : has_members
ORGANIZATIONS ||--o{ ORGANIZATION_SUBSCRIPTIONS : has_subscription
ORGANIZATIONS ||--o{ GYM_LOCATIONS : has_locations
ORGANIZATIONS ||--o{ CLASS_TEMPLATES : has_class_types
ORGANIZATIONS ||--o{ CLASS_SESSIONS : schedules_classes
ORGANIZATIONS ||--o{ COACH_ASSIGNMENTS : assigns_coaches
ORGANIZATIONS ||--o{ CLASS_PACKAGES : offers_packages
ORGANIZATIONS ||--o{ DOCUMENTS : requires_documents
CLASS_TEMPLATES ||--o{ SCHEDULE_SLOTS : has_recurring_times
CLASS_TEMPLATES ||--o{ CLASS_SESSIONS : generates_sessions
CLASS_SESSIONS ||--o{ RESERVATIONS : has_bookings
CLASS_SESSIONS ||--o{ SESSION_COACHES : has_coaches
CLASS_SESSIONS ||--o{ WAITLIST_ENTRIES : has_waitlist
CLASS_SESSIONS ||--o{ CLASS_NOTIFICATIONS : triggers_notifications
GYM_LOCATIONS ||--o{ CLASS_SESSIONS : hosts_sessions
GYM_LOCATIONS ||--o{ SCHEDULE_SLOTS : used_in_slots
CLASS_PACKAGES ||--o{ USER_CLASS_CREDITS : purchased_as
DOCUMENTS ||--o{ USER_DOCUMENTS : completed_by_users
NOTIFICATIONS ||--o{ NOTIFICATION_LIKES : receives_likes
USERS {
int64 id PK
string email UK
string password_hash
string name
date birthday
string profile_image
string role
boolean email_verified
timestamp email_verified_at
int failed_login_attempts
timestamp locked_at
timestamp locked_until
boolean account_disabled
timestamp disabled_at
int64 disabled_by_user_id FK
string disable_reason
timestamp created_at
timestamp updated_at
timestamp last_login_at
}
WORKOUTS {
int64 id PK
string name
text notes
int64 created_by FK
timestamp created_at
timestamp updated_at
}
USER_WORKOUTS {
int64 id PK
int64 user_id FK
int64 workout_id FK
date workout_date
string workout_type
int total_time
text notes
timestamp created_at
timestamp updated_at
}
MOVEMENTS {
int64 id PK
string name UK
text description
string type
boolean is_standard
int64 created_by FK
timestamp created_at
timestamp updated_at
}
WODS {
int64 id PK
string name UK
string source
string type
string regime
string score_type
text description
string url
text notes
boolean is_standard
int64 created_by FK
timestamp created_at
timestamp updated_at
}
WORKOUT_MOVEMENTS {
int64 id PK
int64 workout_id FK
int64 movement_id FK
float weight
int sets
int reps
int time
float distance
boolean is_rx
boolean is_pr
text notes
int order_index
timestamp created_at
timestamp updated_at
}
WORKOUT_WODS {
int64 id PK
int64 workout_id FK
int64 wod_id FK
string score_value
string division
boolean is_pr
int order_index
timestamp created_at
timestamp updated_at
}
USER_WORKOUT_MOVEMENTS {
int64 id PK
int64 user_workout_id FK
int64 movement_id FK
int sets
int reps
float weight
int time
float distance
boolean is_pr
text notes
int order_index
timestamp created_at
timestamp updated_at
}
USER_WORKOUT_WODS {
int64 id PK
int64 user_workout_id FK
int64 wod_id FK
string score_type
string score_value
int time_seconds
int rounds
int reps
float weight
boolean is_pr
text notes
int order_index
timestamp created_at
timestamp updated_at
}
REFRESH_TOKENS {
int64 id PK
int64 user_id FK
string token UK
timestamp expires_at
timestamp created_at
timestamp revoked_at
text device_info
}
AUDIT_LOGS {
int64 id PK
int64 user_id FK
int64 target_user_id FK
string event_type
string ip_address
string user_agent
text details
timestamp created_at
}
DATA_CHANGE_LOGS {
int64 id PK
string entity_type
int64 entity_id
string operation
int64 user_id FK
string user_email
text before_value
text after_value
string ip_address
timestamp created_at
}
ORGANIZATIONS {
int64 id PK
string name UK
text description
timestamp created_at
timestamp updated_at
}
USER_ORGANIZATIONS {
int64 id PK
int64 user_id FK
int64 organization_id FK
string role
timestamp joined_at
timestamp created_at
timestamp updated_at
}
USER_SETTINGS {
int64 id PK
int64 user_id FK
text notification_preferences
string data_export_format
string theme
string font_family
string weight_unit
string distance_unit
string timezone
boolean admin_user_event_notifications
timestamp created_at
timestamp updated_at
}
NOTIFICATIONS {
int64 id PK
int64 user_id FK
int64 organization_id FK
string type
string title
text message
text data
timestamp read_at
timestamp created_at
timestamp updated_at
}
NOTIFICATION_LIKES {
int64 id PK
int64 notification_id FK
int64 user_id FK
timestamp created_at
}
GYM_LOCATIONS {
int64 id PK
int64 organization_id FK
string name
text description
text address
int capacity
boolean is_active
timestamp created_at
timestamp updated_at
}
CLASS_TEMPLATES {
int64 id PK
int64 organization_id FK
string name
text description
int64 workout_id FK
int duration_minutes
int default_capacity
string color
boolean is_active
timestamp created_at
timestamp updated_at
}
SCHEDULE_SLOTS {
int64 id PK
int64 template_id FK
int64 location_id FK
int day_of_week
time start_time
int override_capacity
boolean is_active
timestamp created_at
timestamp updated_at
}
CLASS_SESSIONS {
int64 id PK
int64 organization_id FK
int64 template_id FK
int64 location_id FK
string name
text description
int64 workout_id FK
timestamp start_time
timestamp end_time
int capacity
string status
timestamp cancelled_at
text cancelled_reason
timestamp completed_at
timestamp created_at
timestamp updated_at
}
COACH_ASSIGNMENTS {
int64 id PK
int64 organization_id FK
int64 user_id FK
boolean is_active
timestamp assigned_at
timestamp created_at
timestamp updated_at
}
SESSION_COACHES {
int64 id PK
int64 session_id FK
int64 user_id FK
boolean is_lead
timestamp created_at
}
RESERVATIONS {
int64 id PK
int64 session_id FK
int64 user_id FK
string status
timestamp reserved_at
timestamp checked_in_at
int64 checked_in_by_user_id FK
timestamp cancelled_at
text cancelled_reason
timestamp no_show_marked_at
int64 user_workout_id FK
timestamp created_at
timestamp updated_at
}
DOCUMENTS {
int64 id PK
int64 organization_id FK
string name
text description
string document_type
text url
boolean is_required
int expires_after_days
boolean is_active
timestamp created_at
timestamp updated_at
}
USER_DOCUMENTS {
int64 id PK
int64 user_id FK
int64 document_id FK
string status
timestamp completed_at
timestamp expires_at
int64 verified_by_user_id FK
text notes
timestamp created_at
timestamp updated_at
}
CLASS_PACKAGES {
int64 id PK
int64 organization_id FK
string name
text description
int credits
int price_cents
int validity_days
boolean is_active
timestamp created_at
timestamp updated_at
}
USER_CLASS_CREDITS {
int64 id PK
int64 user_id FK
int64 organization_id FK
int64 package_id FK
int credits_total
int credits_used
timestamp purchased_at
timestamp expires_at
text notes
timestamp created_at
timestamp updated_at
}
WAITLIST_ENTRIES {
int64 id PK
int64 session_id FK
int64 user_id FK
int position
string status
timestamp joined_at
timestamp promoted_at
timestamp expired_at
timestamp created_at
timestamp updated_at
}
CLASS_NOTIFICATIONS {
int64 id PK
int64 user_id FK
int64 session_id FK
int64 reservation_id FK
int64 waitlist_entry_id FK
string notification_type
string status
timestamp scheduled_for
timestamp sent_at
text error_message
timestamp created_at
timestamp updated_at
}
EMAIL_LOGS {
int64 id PK
string recipient_email
string email_type
string subject
boolean success
text error_message
text debug_info
int64 sent_by_user_id FK
timestamp created_at
}
BENCHMARK_DATA {
int64 id PK
string test_key
text test_value
float num_value
int int_value
boolean bool_value
text large_text
text json_blob
float extra_float
int extra_int
string category
int priority
int64 created_by FK
timestamp created_at
timestamp updated_at
}
```
## Logical Data Model
The ActaLog data model uses a **template-based workout system**:
**Workout Template** → **User Workout Instance** → **Performance Tracking**
### Key Principles
1. **Workouts** are reusable templates containing movements and/or WODs
2. **User Workouts** are specific instances of workouts logged by users on specific dates
3. **Movements** are exercise definitions (weightlifting, cardio, gymnastics)
4. **WODs** are benchmark workout definitions (Fran, Murph, etc.)
5. **Performance Tracking** captures actual sets, reps, weights, times for each workout instance
6. **Personal Records (PRs)** are automatically flagged for both movements and WODs
7. Users can create custom movements and WODs in addition to standard pre-seeded ones
8. **Audit Logs** track all security-related events and administrative actions
## Data Dictionary
This section provides detailed field-level documentation including usage context, business meaning, example values, and workflow integration for each table.
---
### users
**Purpose:** Central user account table storing authentication credentials, profile information, and security state. Every person using ActaLog has exactly one record in this table.
**Usage Context:** Used for login authentication, profile display, permission checks, and audit trail attribution. The first user to register becomes the system admin.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated unique identifier. Referenced by all user-owned data (workouts, reservations, etc.). Never exposed to end users; used internally for joins. |
| email | VARCHAR(255) | UNIQUE, NOT NULL | **Login identifier.** User's email address used for authentication and password reset. Must be verified before account is fully active. Example: `athlete@crossfitgym.com` |
| password_hash | VARCHAR(255) | NOT NULL | **Security credential.** Bcrypt hash (cost 12) of user's password. Never stored or logged in plain text. Updated when user changes password. |
| name | VARCHAR(255) | NOT NULL | **Display name.** Shown on leaderboards, coach rosters, and notifications. Athletes set this during registration. Example: `John Smith` |
| birthday | DATE | NULL | **Profile data.** Optional birth date for age-based competition divisions or birthday notifications. Format: `1990-05-15` |
| profile_image | TEXT | NULL | **Avatar URL.** Full URL to user's profile picture. Used in roster views and social features. Example: `/uploads/avatars/user_123.jpg` |
| role | VARCHAR(50) | NOT NULL, DEFAULT 'user' | **Permission level.** Controls access to admin features. Values: `user` (standard athlete), `admin` (full system access). First registered user auto-promoted to admin. |
| email_verified | BOOLEAN | NOT NULL, DEFAULT FALSE | **Account activation flag.** When FALSE, user cannot access main app features. Set to TRUE after clicking email verification link. |
| email_verified_at | TIMESTAMP | NULL | **Verification timestamp.** Records when user confirmed email ownership. Used for audit and support troubleshooting. |
| failed_login_attempts | INT | NOT NULL, DEFAULT 0 | **Security counter.** Incremented on wrong password. Resets to 0 on successful login. At 5 failures, triggers account lockout. |
| locked_at | TIMESTAMP | NULL | **Lockout start time.** Set when failed_login_attempts reaches threshold. Used to display "account locked" message. |
| locked_until | TIMESTAMP | NULL | **Lockout expiration.** Account automatically unlocks after this time (default: 15 minutes after locked_at). User can login normally once passed. |
| account_disabled | BOOLEAN | NOT NULL, DEFAULT FALSE | **Admin ban flag.** When TRUE, user cannot login regardless of credentials. Only admins can set this. Used for policy violations or account termination. |
| disabled_at | TIMESTAMP | NULL | **Ban timestamp.** When admin disabled the account. Appears in admin audit views. |
| disabled_by_user_id | BIGINT | FK → users(id) | **Admin attribution.** Which admin disabled this account. Important for accountability in multi-admin environments. |
| disable_reason | TEXT | NULL | **Ban explanation.** Admin-provided reason for disabling account. Example: `Repeated class no-shows per gym policy` |
| created_at | TIMESTAMP | NOT NULL | **Registration time.** When user account was created. Used for member tenure reports and anniversary features. |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** Auto-updated on any profile change. Used for cache invalidation and sync. |
| last_login_at | TIMESTAMP | NULL | **Activity tracking.** Updated on each successful authentication. Admins use this to identify inactive accounts. |
**Indexes:** PK(id), UNIQUE(email), INDEX(role)
**Security Features:**
- **Password hashing:** Bcrypt cost factor 12 (adaptive security)
- **Account lockout:** 5 failed attempts → 15-minute lock (brute force protection)
- **Manual disable:** Admins can ban users with documented reasons
- **Email verification:** Prevents unauthorized account creation
- **Audit trail:** All auth events logged to audit_logs table
**Business Rules:**
- First registered user automatically receives `admin` role
- Locked accounts auto-unlock after `locked_until` timestamp
- Disabled accounts require admin intervention to re-enable
- Admins cannot disable their own account (prevents lockout)
---
### workouts
**Purpose:** Reusable workout templates that define what exercises/WODs to perform. Think of these as "blueprints" that users instantiate when they log actual workout sessions.
**Usage Context:** Coaches create templates for daily programming. Athletes select templates when logging workouts. Standard templates are pre-seeded; users can create custom templates.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced when users log workouts (user_workouts.workout_id) and by class sessions (class_sessions.workout_id). |
| name | VARCHAR(255) | NOT NULL | **Template title.** Displayed in workout selection lists and history. Should be descriptive. Examples: `Strength - Back Squat 5x5`, `CrossFit Games Open 24.1`, `Recovery Day - Light Cardio` |
| notes | TEXT | NULL | **Programming notes.** Coach instructions, scaling options, or intent explanation. Displayed to athletes before starting workout. Example: `Focus on perfect form. Scale weight if ROM suffers.` |
| created_by | BIGINT | FK → users(id) | **Template author.** NULL for system-seeded templates. Allows filtering "my templates" vs "standard templates" in UI. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** When template was first created. Used for sorting "newest templates" view. |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** Updated when template name, notes, or movements are changed. |
**Indexes:** PK(id), INDEX(created_by), INDEX(name)
**Workflow Integration:**
- Templates contain movements (via `workout_movements`) and/or WODs (via `workout_wods`)
- Athletes instantiate templates via `user_workouts` when logging actual sessions
- Class sessions can reference a workout template for the day's programming
- Deleting a template is RESTRICTED if any user_workouts reference it
---
### user_workouts
**Purpose:** Records of actual workout sessions performed by athletes. Each row represents one workout logged on a specific date.
**Usage Context:** Primary performance tracking table. Created when athletes log workouts from templates or when coaches check in athletes to classes. Forms the basis of performance history and PR tracking.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by performance data in `user_workout_movements` and `user_workout_wods`. Also linked from `reservations.user_workout_id` for class attendance. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Athlete identifier.** The user who performed this workout. Used for filtering "my workouts" and calculating personal records. CASCADE delete removes workouts when user is deleted. |
| workout_id | BIGINT | FK → workouts(id) | **Template reference.** Links to the workout template used. Can be NULL for ad-hoc workouts not based on templates. RESTRICT prevents template deletion if referenced. |
| workout_name | TEXT | NULL | **Denormalized name.** Stores workout name at time of logging. Preserves history if template is later renamed. |
| workout_date | DATE | NOT NULL | **Performance date.** When the athlete performed the workout. Used for calendar views, weekly summaries, and PR date attribution. Example: `2024-01-15` |
| workout_type | VARCHAR(255) | NULL | **Classification.** Categorizes the workout for filtering and stats. Values: `strength`, `metcon`, `cardio`, `mixed`, `skills`, `recovery`. Example: `strength` |
| total_time | INT | NULL | **Duration in seconds.** Total workout time including rest. Used for time-based stats. Example: `3600` (1 hour). Displayed as `60:00` in UI. |
| notes | TEXT | NULL | **Athlete journal.** Personal notes about how the workout felt, modifications made, or factors affecting performance. Example: `Felt strong today. Increased squat weight 10lbs.` |
| created_at | TIMESTAMP | NOT NULL | **Log timestamp.** When athlete logged the workout (may differ from workout_date for backdated entries). |
| updated_at | TIMESTAMP | NOT NULL | **Edit timestamp.** Updated when athlete modifies workout details or adds performance data. |
**Indexes:** PK(id), INDEX(user_id), INDEX(workout_date), COMPOSITE(user_id, workout_date DESC)
**Workflow Integration:**
- Created manually by athletes logging workouts OR automatically when coaches check in class attendees
- Performance details stored in `user_workout_movements` (strength work) and `user_workout_wods` (benchmark WODs)
- Athletes can log multiple workouts per day (morning strength + evening metcon)
- Linked to class attendance via `reservations.user_workout_id`
---
### movements
**Purpose:** Exercise/movement library containing all trackable exercises. Includes 31 pre-seeded CrossFit movements plus user-created custom movements.
**Usage Context:** Athletes select movements when building workouts. Used for PR tracking (per-movement personal records), performance history charts, and workout template construction.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by workout_movements and user_workout_movements for tracking which exercises were performed. |
| name | VARCHAR(255) | UNIQUE, NOT NULL | **Exercise name.** Displayed in movement selection, PR lists, and performance charts. Must be unique system-wide. Examples: `Back Squat`, `Pull-up`, `500m Row`, `Deadlift` |
| description | TEXT | NULL | **Movement guidance.** Instructions, form cues, or scaling options. Displayed when athlete views movement details. Example: `Barbell on upper back, squat below parallel, drive through heels.` |
| type | VARCHAR(50) | NOT NULL | **Category for filtering.** Helps athletes find movements in library. Values: `weightlifting` (barbell/dumbbell), `cardio` (row/run/bike), `gymnastics` (muscle-ups/HSPUs), `bodyweight` (push-ups/squats). |
| is_standard | BOOLEAN | NOT NULL, DEFAULT FALSE | **System vs custom flag.** TRUE for 31 pre-seeded movements, FALSE for user-created. Standard movements cannot be deleted; custom movements only visible to creator. |
| created_by | BIGINT | FK → users(id) | **Custom movement author.** NULL for standard movements. Allows filtering "my movements" in library view. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** When movement was added to library. |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** Updated when description or type is changed. |
**Indexes:** PK(id), UNIQUE(name), INDEX(type), INDEX(is_standard)
**Workflow Integration:**
- Pre-seeded on first database initialization (31 CrossFit movements)
- Users add custom movements via Movements Library → Create Movement
- Deleting a movement is RESTRICTED if any workouts reference it (preserves history)
- Performance tracked in `user_workout_movements` with 1RM calculation
---
### workout_movements
**Purpose:** Junction table defining which movements are included in a workout template, with prescribed weights/reps/sets.
**Usage Context:** Defines the "prescription" for a workout template. When athletes log workouts, these prescriptions are copied to user_workout_movements where they record actual performance.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier for this template-movement link. |
| workout_id | BIGINT | FK → workouts(id), NOT NULL | **Parent template.** Which workout template this movement belongs to. CASCADE delete removes movements when template is deleted. |
| movement_id | BIGINT | FK → movements(id), NOT NULL | **Exercise reference.** Which movement is prescribed. RESTRICT prevents movement deletion if used in templates. |
| weight | DECIMAL(10,2) | NULL | **Prescribed weight.** Target weight in user's preferred unit (lbs/kg). Used as starting point when logging. Example: `185.00` for 185 lbs. |
| sets | INT | NULL | **Prescribed sets.** Number of sets to perform. Example: `5` for 5x5 protocol. |
| reps | INT | NULL | **Prescribed reps.** Reps per set or total reps. Example: `5` for 5x5, or `21-15-9` stored as notes. |
| time | INT | NULL | **Time component (seconds).** For timed efforts like max calories in 60 seconds. Example: `60` for 1-minute max effort. |
| distance | DECIMAL(10,2) | NULL | **Distance component.** For rowing/running prescriptions. Example: `500.00` for 500m row. Unit depends on user settings. |
| is_rx | BOOLEAN | NOT NULL, DEFAULT FALSE | **Prescription flag.** TRUE means this is the "Rx" (prescribed) standard. Athlete can choose to scale. |
| is_pr | BOOLEAN | NOT NULL, DEFAULT FALSE | **PR flag for template.** Rarely used at template level; primarily used in user_workout_movements. |
| instructions | TEXT | DEFAULT '' | **Movement-specific notes.** Coaching cues for this specific movement within the workout. Example: `Pause at bottom for 2 seconds.` |
| notes | TEXT | NULL | **Additional details.** Any other information about this movement in the workout. |
| order_index | INT | NOT NULL, DEFAULT 0 | **Sequence position.** Determines display order. 0 = first movement, 1 = second, etc. Allows reordering movements. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** PK(id), INDEX(workout_id), INDEX(movement_id), COMPOSITE(workout_id, order_index)
**Workflow Integration:**
- Created when coach/athlete adds movements to a workout template
- Copied to `user_workout_movements` when athlete logs the workout
- `order_index` determines the sequence shown in workout view
---
### refresh_tokens
**Purpose:** Manages persistent login sessions for "Remember Me" functionality. Allows users to stay logged in across browser sessions without re-entering credentials.
**Usage Context:** Created when user checks "Remember Me" during login. Enables automatic JWT renewal. Multiple tokens allow login from multiple devices simultaneously.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier for this token record. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Token owner.** Which user this session belongs to. CASCADE delete removes tokens when user is deleted. |
| token | VARCHAR(255) | UNIQUE, NOT NULL | **Secure session key.** 32-byte cryptographically random string sent in HTTP-only cookie. Used to issue new JWT access tokens. Never logged or displayed. |
| expires_at | TIMESTAMP | NOT NULL | **Session expiration.** Token becomes invalid after this time (default: 30 days from creation). User must re-login after expiration. |
| created_at | TIMESTAMP | NOT NULL | **Session start.** When user checked "Remember Me" and logged in. Used for session management UI showing active logins. |
| revoked_at | TIMESTAMP | NULL | **Logout timestamp.** Set when user explicitly logs out or admin revokes session. Non-null means token is invalid even if not expired. |
| device_info | TEXT | NULL | **Client identification.** Browser user-agent string captured at login. Displayed in "Active Sessions" view. Example: `Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)...` |
**Indexes:** PK(id), UNIQUE(token), INDEX(user_id), INDEX(expires_at)
**Security Implementation:**
- Tokens are 32-byte cryptographically secure random strings (256-bit entropy)
- Tokens expire after 30 days (configurable)
- Users can have multiple active tokens (phone + laptop + work computer)
- Tokens are revoked on explicit logout
- Admins can revoke all sessions for a user via user management
### password_resets
Stores password reset tokens (separate repository implementation).
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique reset identifier |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | Reference to users.id |
| token | VARCHAR(255) | UNIQUE, NOT NULL | Password reset token |
| expires_at | TIMESTAMP | NOT NULL | Token expiration (1 hour) |
| used_at | TIMESTAMP | NULL | When token was used |
| created_at | TIMESTAMP | NOT NULL | Token creation time |
**Indexes:**
- PRIMARY KEY (id)
- UNIQUE INDEX idx_password_resets_token (token)
- INDEX idx_password_resets_user_id (user_id)
**Foreign Keys:**
- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
**Security:**
- Tokens are single-use only
- Tokens expire after 1 hour
- Email delivery via SMTP (configurable)
### email_verification_tokens
Stores email verification tokens (separate repository implementation).
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique verification identifier |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | Reference to users.id |
| token | VARCHAR(255) | UNIQUE, NOT NULL | Email verification token |
| expires_at | TIMESTAMP | NOT NULL | Token expiration (24 hours) |
| used_at | TIMESTAMP | NULL | When token was used |
| created_at | TIMESTAMP | NOT NULL | Token creation time |
**Indexes:**
- PRIMARY KEY (id)
- UNIQUE INDEX idx_email_verification_tokens_token (token)
- INDEX idx_email_verification_tokens_user_id (user_id)
**Foreign Keys:**
- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
**Behavior:**
- Sent automatically on user registration
- Sent on email address change
- Tokens expire after 24 hours
- Single-use tokens
### data_change_logs
Stores audit trail of data modifications for WODs, Movements, and other entities (added v0.11.0).
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique log entry identifier |
| entity_type | VARCHAR(100) | NOT NULL | Type of entity modified (wod, movement, etc.) |
| entity_id | BIGINT | NOT NULL | ID of the modified entity |
| operation | VARCHAR(50) | NOT NULL | Operation type: INSERT, UPDATE, DELETE |
| user_id | BIGINT | NULL, FOREIGN KEY | Reference to users.id who made the change |
| user_email | VARCHAR(255) | NULL | Email of user who made the change (denormalized) |
| before_value | TEXT | NULL | JSON snapshot of entity before change |
| after_value | TEXT | NULL | JSON snapshot of entity after change |
| ip_address | VARCHAR(45) | NULL | IP address of the request |
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | When the change occurred |
**Indexes:**
- PRIMARY KEY (id)
- INDEX idx_data_change_logs_entity (entity_type, entity_id)
- INDEX idx_data_change_logs_user_id (user_id)
- INDEX idx_data_change_logs_created_at (created_at)
- INDEX idx_data_change_logs_operation (operation)
**Foreign Keys:**
- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
**Design Notes:**
- `before_value` is NULL for INSERT operations
- `after_value` is NULL for DELETE operations
- Both `before_value` and `after_value` contain JSON for UPDATE operations
- `user_email` is denormalized for query convenience and historical accuracy
- Admin-only access for viewing logs
- Supports retention-based cleanup via API
**Use Cases:**
- Audit trail for compliance and security
- Track who modified what and when
- Ability to see before/after values for updates
- Filter by entity type, operation, user, or date range
### user_subscriptions
Stores individual user-level subscription billing information (added v0.14.0).
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique subscription identifier |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | Reference to users.id |
| subscription_type | VARCHAR(20) | NOT NULL, CHECK | Subscription type: 'free', 'monthly', 'annual' |
| status | VARCHAR(20) | NOT NULL, CHECK | Status: 'active', 'expired', 'cancelled' |
| is_permanent_free | BOOLEAN | NOT NULL, DEFAULT FALSE | Permanent free access (never expires) |
| start_date | TIMESTAMP | NOT NULL | When subscription started |
| end_date | TIMESTAMP | NULL | When subscription expires (NULL for permanent free) |
| last_payment_date | TIMESTAMP | NULL | Last successful payment date |
| next_billing_date | TIMESTAMP | NULL | Next billing date (NULL for free/permanent) |
| cancelled_at | TIMESTAMP | NULL | When subscription was cancelled |
| cancelled_reason | TEXT | NULL | Reason for cancellation |
| notes | TEXT | NULL | Admin notes about subscription |
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Record creation time |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Last update time |
| created_by_user_id | BIGINT | NULL, FOREIGN KEY | Admin who created/modified |
**Indexes:**
- PRIMARY KEY (id)
- INDEX idx_user_subscriptions_user_id (user_id)
- INDEX idx_user_subscriptions_status (status)
- INDEX idx_user_subscriptions_next_billing (next_billing_date)
**Foreign Keys:**
- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
- FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
**Constraints:**
- CHECK (subscription_type IN ('free', 'monthly', 'annual'))
- CHECK (status IN ('active', 'expired', 'cancelled'))
**Business Rules:**
- Users have write access if EITHER personal OR any organization subscription is active
- Permanent free subscriptions never expire (is_permanent_free = TRUE, end_date = NULL)
- Monthly subscriptions: end_date = last_payment_date + 30 days
- Annual subscriptions: end_date = last_payment_date + 365 days
- Admins manually mark subscriptions as paid (updates last_payment_date, extends end_date)
- When subscription expires: immediate read-only mode (no grace period)
- Read-only mode allows: GET requests (view, export, dashboard)
- Read-only mode blocks: POST/PUT/PATCH/DELETE (returns HTTP 402 Payment Required)
- All subscription operations are logged to audit_logs
- Migration 0.14.0 seeds all existing users with permanent free subscriptions
### organization_subscriptions
Stores organization-level subscription billing information (added v0.14.0). Allows gyms/teams to pay for all members.
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique subscription identifier |
| organization_id | BIGINT | NOT NULL, FOREIGN KEY | Reference to organizations.id |
| subscription_type | VARCHAR(20) | NOT NULL, CHECK | Subscription type: 'free', 'monthly', 'annual' |
| status | VARCHAR(20) | NOT NULL, CHECK | Status: 'active', 'expired', 'cancelled' |
| is_permanent_free | BOOLEAN | NOT NULL, DEFAULT FALSE | Permanent free access (never expires) |
| start_date | TIMESTAMP | NOT NULL | When subscription started |
| end_date | TIMESTAMP | NULL | When subscription expires (NULL for permanent free) |
| last_payment_date | TIMESTAMP | NULL | Last successful payment date |
| next_billing_date | TIMESTAMP | NULL | Next billing date (NULL for free/permanent) |
| cancelled_at | TIMESTAMP | NULL | When subscription was cancelled |
| cancelled_reason | TEXT | NULL | Reason for cancellation |
| notes | TEXT | NULL | Admin notes about subscription |
| created_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Record creation time |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Last update time |
| created_by_user_id | BIGINT | NULL, FOREIGN KEY | Admin who created/modified |
**Indexes:**
- PRIMARY KEY (id)
- INDEX idx_org_subscriptions_org_id (organization_id)
- INDEX idx_org_subscriptions_status (status)
- INDEX idx_org_subscriptions_next_billing (next_billing_date)
**Foreign Keys:**
- FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
- FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
**Constraints:**
- CHECK (subscription_type IN ('free', 'monthly', 'annual'))
- CHECK (status IN ('active', 'expired', 'cancelled'))
**Business Rules:**
- All members of an organization benefit from organization subscription
- If any organization a user belongs to has active subscription, user has write access
- Dual-level access check: CheckUserAccess() returns TRUE if EITHER personal OR organization subscription active
- Performance optimization: User subscription checked first (single query), organization subscriptions second
**Subscription Access Logic:**
```
User has write access if:
1. User has active personal subscription (is_permanent_free OR end_date > NOW())
OR
2. User belongs to ≥1 organization with active subscription
```
**API Endpoints (v0.14.0):**
- User: `GET /api/subscriptions/status` - View subscription status
- Admin: `POST /api/admin/subscriptions/user` - Create user subscription
- Admin: `POST /api/admin/subscriptions/user/{id}/mark-paid` - Mark as paid
- Admin: `POST /api/admin/subscriptions/user/{id}/cancel` - Cancel subscription
- Admin: `GET /api/admin/subscriptions/user/{user_id}` - View subscription history
- Organization endpoints follow same pattern
---
### documents
**Purpose:** Defines document types that gyms require members to complete (waivers, liability forms, health questionnaires). Each gym can have different required documents.
**Usage Context:** Admins create document requirements for their organization. Athletes see pending documents in their dashboard. Completion is tracked in user_documents. Can block class registration if required documents incomplete.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by user_documents for completion tracking. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Owning gym.** Documents are gym-specific. Different gyms may have different waiver requirements. CASCADE delete removes documents if gym is deleted. |
| name | VARCHAR(255) | NOT NULL | **Document title.** Displayed to athletes in "Required Documents" list. Examples: `Liability Waiver 2024`, `Health Questionnaire`, `Photo/Video Release` |
| description | TEXT | NULL | **Instructions for athlete.** What the document covers, how to complete it. Example: `Please read carefully and sign acknowledging the inherent risks of CrossFit training.` |
| document_type | VARCHAR(50) | NOT NULL, DEFAULT 'waiver' | **Category.** For filtering and reporting. Values: `waiver` (liability), `liability`, `health` (medical questionnaire), `other`. |
| url | TEXT | NULL | **External link.** URL to PDF or online form (DocuSign, JotForm, etc.). If NULL, document is verified manually by admin. Example: `https://docusign.com/waiver/123` |
| is_required | BOOLEAN | NOT NULL, DEFAULT TRUE | **Enforcement flag.** When TRUE, athletes may be blocked from booking classes until document is completed. FALSE = optional. |
| expires_after_days | INT | NULL | **Renewal period.** Days after completion before document must be re-signed. NULL = never expires. Example: `365` for annual waiver renewal. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Availability flag.** FALSE hides document from new signups but preserves existing completion records. Use for retired document versions. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(organization_id), INDEX(is_active), INDEX(document_type)
**Workflow Integration:**
- Admins create documents via Admin → Documents → Create
- Athletes see required documents on Dashboard or during class registration
- Completion tracked in `user_documents` table
- Optional: Block class booking if required documents not complete
---
### user_documents
**Purpose:** Tracks which documents each athlete has completed, when they expire, and who verified completion.
**Usage Context:** Created when admin marks a document as completed for a user. Used to enforce document requirements before class booking. Expiration dates trigger re-signing reminders.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Athlete.** Which user this completion record belongs to. CASCADE delete removes records when user deleted. |
| document_id | BIGINT | FK → documents(id), NOT NULL | **Document type.** Which document was completed. CASCADE delete removes records if document type deleted. |
| status | VARCHAR(50) | NOT NULL, DEFAULT 'pending' | **Completion state.** Values: `pending` (awaiting completion), `completed` (signed/submitted), `expired` (past expires_at, needs renewal). |
| completed_at | TIMESTAMP | NULL | **Signature timestamp.** When athlete completed the document. Used for audit trail and expiration calculation. |
| expires_at | TIMESTAMP | NULL | **Expiration date.** Calculated as completed_at + document.expires_after_days. After this date, status changes to `expired`. |
| verified_by_user_id | BIGINT | FK → users(id) | **Admin verification.** Which admin/coach verified document completion. Important for accountability. |
| notes | TEXT | NULL | **Admin notes.** Any comments about verification. Example: `Verified original signature on file dated 1/15/2024` |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** When tracking record was created (may differ from completed_at). |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(user_id), INDEX(document_id), INDEX(status), INDEX(expires_at)
**Unique Constraint:** (user_id, document_id) - One completion record per user per document type
**Workflow Integration:**
- Created automatically when athlete's document status needs tracking
- Updated by admin via Admin → Users → [User] → Documents → Mark Complete
- Background job can change status to `expired` when expires_at passes
- Class booking can check for `completed` status on required documents
---
### class_packages
**Purpose:** Defines credit packages (class packs) that athletes can purchase. Each package grants a number of credits usable for class reservations.
**Usage Context:** Admins create packages for their gym (10-class, 20-class, unlimited). Athletes purchase packages, credits are added to user_class_credits. One credit typically = one class reservation.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by user_class_credits.package_id. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Owning gym.** Packages are gym-specific. Credits from Gym A package can only be used at Gym A. |
| name | VARCHAR(255) | NOT NULL | **Package title.** Displayed in purchase UI and credit history. Examples: `10-Class Pack`, `Monthly Unlimited`, `Drop-In Single Class` |
| description | TEXT | NULL | **Marketing copy.** Benefits, restrictions, what's included. Example: `Best value! 10 classes valid for 3 months. Use for any scheduled class.` |
| credits | INT | NOT NULL | **Credits granted.** Number of class credits when purchased. Example: `10` for 10-class pack, `999` for unlimited. |
| price_cents | INT | NOT NULL, DEFAULT 0 | **Price in cents.** Stored as integer to avoid floating-point issues. Example: `15000` = $150.00. Used for display/reporting; actual payment handled externally. |
| validity_days | INT | NULL | **Expiration period.** Days from purchase until credits expire. NULL = never expires. Example: `90` for 3-month validity. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Availability flag.** FALSE removes package from purchase options but preserves existing purchases. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(organization_id), INDEX(is_active)
**Workflow Integration:**
- Admins create packages via Admin → Packages → Create
- Athletes view available packages in Schedule → Buy Credits
- Admin manually records purchase → creates user_class_credits record
- Price is for display; actual payment processing is external (Stripe, cash, etc.)
---
### user_class_credits
**Purpose:** Tracks athlete credit balances. Each row represents a batch of credits from a package purchase or admin grant.
**Usage Context:** Created when admin adds credits to an athlete's account (from package purchase or comp credits). Decremented when athlete books classes. Multiple credit batches per user possible (each with different expiration).
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Credit owner.** Which athlete owns these credits. CASCADE delete removes credits when user deleted. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Valid at gym.** Credits can only be used for classes at this organization. |
| package_id | BIGINT | FK → class_packages(id) | **Source package.** Which package these credits came from. NULL if manually granted by admin. |
| credits_total | INT | NOT NULL | **Original amount.** Credits granted when purchased/added. Never changes after creation. Example: `10` |
| credits_used | INT | NOT NULL, DEFAULT 0 | **Consumed amount.** Incremented each time a credit is spent on a reservation. Example: `3` if 3 classes booked. |
| purchased_at | TIMESTAMP | NOT NULL | **Acquisition date.** When credits were added to account. Used for FIFO credit consumption (oldest credits used first). |
| expires_at | TIMESTAMP | NULL | **Expiration date.** Credits invalid after this date. Calculated as purchased_at + package.validity_days. NULL = never expires. |
| notes | TEXT | NULL | **Admin notes.** Reason for credit grant. Example: `Comp credits for class cancellation`, `Won in member challenge` |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** Updated when credits_used changes. |
**Indexes:** INDEX(user_id), INDEX(organization_id), INDEX(expires_at)
**Calculated Field:** `credits_remaining = credits_total - credits_used`
**Workflow Integration:**
- Admin adds credits: Admin → Users → [User] → Add Credits → Select package or enter manual amount
- Athlete books class → oldest non-expired credits decremented first (FIFO)
- Athlete cancels reservation → credit returned (credits_used decremented)
- Expired credits (expires_at < NOW) are skipped during booking
---
### waitlist_entries
**Purpose:** Queue for athletes waiting for spots in full classes. When someone cancels, the first waitlisted person is automatically promoted to a reservation.
**Usage Context:** Created when athlete clicks "Join Waitlist" on a full class. System monitors cancellations and promotes waitlist entries in order. Athlete notified when promoted.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| session_id | BIGINT | FK → class_sessions(id), NOT NULL | **Target class.** Which class session the athlete is waiting for. CASCADE delete removes entries when session deleted/cancelled. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Waiting athlete.** Who is on the waitlist. CASCADE delete removes entries when user deleted. |
| position | INT | NOT NULL | **Queue position.** Lower number = higher priority. 1 = first in line. Recalculated when people leave waitlist. |
| status | VARCHAR(50) | NOT NULL, DEFAULT 'waiting' | **Entry state.** Values: `waiting` (in queue), `promoted` (converted to reservation), `expired` (class started without promotion), `cancelled` (athlete left waitlist). |
| joined_at | TIMESTAMP | NOT NULL | **Queue entry time.** When athlete joined waitlist. Used for position assignment (earlier = lower position number). |
| promoted_at | TIMESTAMP | NULL | **Reservation conversion time.** When status changed from `waiting` to `promoted`. NULL if never promoted. |
| expired_at | TIMESTAMP | NULL | **Expiration time.** When status changed to `expired` (class started). NULL if not expired. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(session_id), INDEX(user_id), INDEX(status), INDEX(position)
**Unique Constraint:** (session_id, user_id) - Athlete can only be on waitlist once per class
**Workflow Integration:**
- Athlete joins waitlist: Schedule → [Full Class] → Join Waitlist
- Someone cancels reservation → system checks waitlist → promotes position 1
- Promoted athlete receives notification and reservation is created automatically
- Athlete can leave waitlist voluntarily → positions recalculated for remaining entries
---
### class_notifications
**Purpose:** Scheduled notifications for class reminders, waitlist promotions, and class changes. Processed by background job that sends emails/push notifications.
**Usage Context:** Created automatically by system events (reservation made, waitlist promoted, class cancelled). Background scheduler processes pending notifications at scheduled_for time.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Notification recipient.** Who should receive this notification. CASCADE delete removes notifications when user deleted. |
| session_id | BIGINT | FK → class_sessions(id) | **Related class.** Which class session this notification is about. Used for email content. |
| reservation_id | BIGINT | FK → reservations(id) | **Related reservation.** For reminder and check-in notifications. |
| waitlist_entry_id | BIGINT | FK → waitlist_entries(id) | **Related waitlist entry.** For promotion notifications. |
| notification_type | VARCHAR(50) | NOT NULL | **Notification category.** Values: `reminder` (upcoming class), `waitlist_promoted` (spot available), `class_cancelled` (class won't happen), `class_updated` (time/details changed). |
| status | VARCHAR(50) | NOT NULL, DEFAULT 'pending' | **Delivery state.** Values: `pending` (scheduled, not sent), `sent` (delivered successfully), `failed` (delivery error). |
| scheduled_for | TIMESTAMP | NOT NULL | **Delivery time.** When notification should be sent. For reminders, typically 1-24 hours before class. |
| sent_at | TIMESTAMP | NULL | **Actual delivery time.** When notification was successfully sent. NULL if pending or failed. |
| error_message | TEXT | NULL | **Failure reason.** If status = `failed`, contains error details. Example: `Email bounced: invalid address` |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(user_id), INDEX(session_id), INDEX(status), INDEX(scheduled_for)
**Workflow Integration:**
- Created automatically when: reservation made (schedule reminder), waitlist promoted, class cancelled/updated
- Background job queries for `status = 'pending' AND scheduled_for <= NOW()`
- Job attempts delivery → updates status to `sent` or `failed`
- Admin can view notification history in Admin → Email Logs
---
### audit_logs
**Purpose:** Security audit trail recording all authentication events, administrative actions, and sensitive operations. Essential for compliance, security investigations, and troubleshooting.
**Usage Context:** Written automatically by backend on login/logout, admin actions, profile changes, etc. Read by admins via Admin → Audit Logs. Supports filtering by user, event type, and date range.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id) | **Acting user.** Who performed the action. NULL for anonymous events (failed login with unknown email). SET NULL preserves log if user deleted. |
| target_user_id | BIGINT | FK → users(id) | **Affected user.** For admin actions on other users (disable account, change role). NULL if action doesn't affect another user. |
| event_type | VARCHAR(100) | NOT NULL | **Event category.** Examples: `login_success`, `login_failed`, `logout`, `password_change`, `password_reset_request`, `email_verified`, `account_disabled`, `role_changed`, `subscription_created`. |
| ip_address | VARCHAR(50) | NULL | **Client IP.** For security analysis and geographic tracking. Supports IPv4 and IPv6. Example: `192.168.1.100`, `2001:db8::1` |
| user_agent | TEXT | NULL | **Client identification.** Browser/app user agent string. Helps identify device type and detect suspicious access. |
| details | TEXT | NULL | **Event context.** JSON with additional event-specific information. Example for login: `{"method": "password", "remember_me": true}`. Example for role change: `{"old_role": "user", "new_role": "admin"}` |
| created_at | TIMESTAMP | NOT NULL | **Event timestamp.** When the event occurred. Used for timeline analysis and filtering. |
**Indexes:** INDEX(user_id), INDEX(target_user_id), INDEX(event_type), INDEX(created_at DESC), COMPOSITE(user_id, event_type, created_at)
**Event Types:**
- Authentication: `login_success`, `login_failed`, `logout`, `token_refresh`
- Security: `password_change`, `password_reset_request`, `password_reset_complete`, `account_locked`, `account_unlocked`
- Admin: `account_disabled`, `account_enabled`, `role_changed`, `subscription_created`, `subscription_cancelled`
- Profile: `email_change_requested`, `email_verified`, `profile_updated`
---
### benchmark_data
**Purpose:** Test data table for API performance benchmarking and load testing. Contains various data types to simulate realistic payloads.
**Usage Context:** Used by developers for API performance testing. Not used in production workflows. Data created via benchmark API endpoints during load testing.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| test_key | VARCHAR(255) | NOT NULL | **Test identifier.** Key for grouping benchmark runs. Example: `load_test_2024_01_15` |
| test_value | TEXT | NULL | **String payload.** For testing text field performance. |
| num_value | DOUBLE | DEFAULT 0 | **Numeric payload.** For testing floating-point operations. |
| int_value | INTEGER | DEFAULT 0 | **Integer payload.** For testing integer operations and sorting. |
| bool_value | BOOLEAN | DEFAULT FALSE | **Boolean payload.** For testing boolean filtering. |
| large_text | TEXT | DEFAULT '' | **Large text payload.** For stress testing with large strings. |
| json_blob | TEXT | DEFAULT '' | **JSON payload.** For testing JSON serialization/deserialization. |
| extra_float | DOUBLE | DEFAULT 0 | **Additional numeric field.** For testing multi-column operations. |
| extra_int | INTEGER | DEFAULT 0 | **Additional integer field.** For testing multi-column sorting. |
| category | VARCHAR(100) | DEFAULT '' | **Grouping field.** For testing filtered queries. |
| priority | INTEGER | DEFAULT 0 | **Priority field.** For testing ordered retrieval. |
| created_by | BIGINT | FK → users(id), NOT NULL | **Test author.** Who ran the benchmark. |
| created_at | TIMESTAMP | NOT NULL | **Test timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(test_key), INDEX(created_by), INDEX(category), INDEX(priority)
---
### class_sessions
**Purpose:** Actual scheduled class instances that athletes can reserve. Each session is a specific class at a specific time (e.g., "CrossFit WOD on Monday Jan 15 at 6:00 AM").
**Usage Context:** Athletes browse sessions in Schedule view, make reservations. Coaches view their assigned sessions, check in athletes. Sessions can be created from templates (recurring) or as one-off events.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by reservations, waitlist_entries, session_coaches. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Hosting gym.** Which organization offers this class. Used for filtering "classes at my gym". |
| template_id | BIGINT | FK → class_templates(id) | **Source template.** If session was created from a recurring template. NULL for one-off sessions. |
| location_id | BIGINT | FK → gym_locations(id) | **Physical space.** Where within the gym this class takes place. Example: Main Floor, Studio B. |
| name | VARCHAR(255) | NOT NULL | **Class title.** Displayed in schedule. Usually inherited from template. Examples: `CrossFit WOD`, `Olympic Lifting`, `Yoga` |
| description | TEXT | NULL | **Class details.** What to expect, what to bring. Can be overridden from template for special sessions. |
| workout_id | BIGINT | FK → workouts(id) | **Day's programming.** Links to workout template for this session. Allows athletes to see what movements they'll do. |
| start_time | TIMESTAMP | NOT NULL | **Class start.** When class begins. Primary filter for schedule display. Stored in UTC, displayed in user's timezone. |
| end_time | TIMESTAMP | NOT NULL | **Class end.** When class ends. Duration = end_time - start_time. Used for calendar display. |
| capacity | INTEGER | NOT NULL, DEFAULT 20 | **Max spots.** Maximum number of athletes allowed. When reservations reach capacity, new athletes go to waitlist. |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'scheduled' | **Session state.** Values: `scheduled` (upcoming), `in_progress` (started), `completed` (finished), `cancelled` (won't happen). |
| cancelled_at | TIMESTAMP | NULL | **Cancellation time.** When admin/coach cancelled the session. Triggers notifications to reserved athletes. |
| cancelled_reason | TEXT | NULL | **Cancellation explanation.** Why class was cancelled. Included in notification to athletes. Example: `Coach unavailable due to illness` |
| completed_at | TIMESTAMP | NULL | **Completion time.** When coach marked session complete. Triggers attendance finalization. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(organization_id), INDEX(template_id), INDEX(start_time), INDEX(status), COMPOSITE(organization_id, start_time, status)
**Status State Machine:**
```
scheduled → in_progress → completed
↓
cancelled
```
**Workflow Integration:**
- Created by materializer from templates OR manually by admin
- Athletes browse via Schedule → [Gym] → [Date Range]
- Athletes reserve spots → creates reservation record
- Coach starts class → status = `in_progress`
- Coach completes class → status = `completed`, user_workouts created for attended athletes
---
### class_templates
**Purpose:** Reusable class type definitions for recurring schedule patterns. Templates define the "what" (class name, duration, capacity) while schedule_slots define the "when" (day/time).
**Usage Context:** Admins create templates for their gym's class offerings. Templates combined with schedule_slots drive automatic session generation. Example: "CrossFit WOD" template + "MWF 6am, 9am, 5pm" slots.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by schedule_slots and class_sessions. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Owning gym.** Templates are gym-specific. |
| name | VARCHAR(255) | NOT NULL | **Class type name.** What kind of class this is. Examples: `CrossFit WOD`, `Olympic Lifting`, `Fundamentals`, `Open Gym`, `Yoga` |
| description | TEXT | NULL | **Class description.** What the class involves, skill level, what to bring. Shown when athlete views class details. |
| workout_id | BIGINT | FK → workouts(id) | **Default workout.** Workout template to use for sessions (can be overridden per session). |
| duration_minutes | INTEGER | NOT NULL, DEFAULT 60 | **Class length.** How long sessions run. Used to calculate end_time from start_time. Common values: 45, 60, 90. |
| default_capacity | INTEGER | NOT NULL, DEFAULT 20 | **Default max athletes.** Copied to sessions when created. Can be overridden by schedule_slot or individual session. |
| color | VARCHAR(20) | DEFAULT '#00bcd4' | **Calendar color.** For visual distinction in schedule UI. Hex color code. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Availability flag.** FALSE hides template from new session creation but preserves existing sessions. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(organization_id), INDEX(is_active)
**Workflow Integration:**
- Admin creates: Admin → Class Templates → Create
- Admin adds schedule slots: Admin → Class Templates → [Template] → Schedule Slots
- Materializer runs: Creates class_sessions from template + slots for upcoming dates
- Sessions inherit template properties but can be individually customized
---
### coach_assignments
**Purpose:** Assigns users as coaches for a specific gym. Coaches have elevated permissions to manage sessions, view rosters, and check in athletes.
**Usage Context:** Admin assigns coaches per organization. Coach role is per-gym (user can be coach at Gym A but regular member at Gym B). Coaches see their dashboard with upcoming sessions.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Gym scope.** Which gym this coach assignment applies to. User can be coach at multiple gyms. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Coach.** Which user is being assigned as coach. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Active status.** FALSE removes coach privileges without deleting history. Use for temporary or ended coach roles. |
| assigned_at | TIMESTAMP | NOT NULL | **Assignment date.** When user became a coach at this gym. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(organization_id), INDEX(user_id), INDEX(is_active)
**Unique Constraint:** (organization_id, user_id) - User can only have one assignment per gym
**Permissions Granted:**
- View class rosters and attendee details
- Check in athletes to classes
- View athlete workout history (within their gym)
- Create/edit class sessions (if gym allows)
---
### email_logs
**Purpose:** Audit trail for all outgoing emails from the system. Records delivery status for troubleshooting and compliance.
**Usage Context:** Written automatically when system sends any email (verification, password reset, notifications). Admins view via Admin → Email Logs to debug delivery issues.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| recipient_email | VARCHAR(255) | NOT NULL | **Recipient.** Email address the message was sent to. For searching "what emails did user@example.com receive?" |
| email_type | VARCHAR(50) | NOT NULL | **Email category.** Values: `verification` (confirm email), `password_reset`, `notification` (announcements), `class_reminder`, `waitlist_promotion`, `class_cancelled`. |
| subject | VARCHAR(500) | NOT NULL | **Email subject line.** The subject that was sent. Useful for identifying specific emails. |
| success | BOOLEAN | NOT NULL, DEFAULT FALSE | **Delivery status.** TRUE if SMTP accepted the message. FALSE if sending failed. |
| error_message | TEXT | NULL | **Failure details.** If success=FALSE, contains error from SMTP server. Example: `550 User not found` |
| debug_info | TEXT | NULL | **Technical details.** SMTP response, connection info. Used for troubleshooting delivery issues. |
| sent_by_user_id | BIGINT | FK → users(id) | **Triggering user.** For admin-initiated emails (announcements), which admin sent it. NULL for system-triggered emails. |
| created_at | TIMESTAMP | NOT NULL | **Send timestamp.** When email was sent (or attempted). |
**Indexes:** INDEX(email_type), INDEX(recipient_email), INDEX(created_at DESC), INDEX(success)
---
### gym_locations
**Purpose:** Physical spaces within a gym where classes take place. Allows scheduling different class types in different areas.
**Usage Context:** Admins define locations for their gym. Sessions and schedule_slots reference locations. Athletes see location in class details. Example: Main Floor (20 capacity), Yoga Studio (15 capacity).
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by class_sessions and schedule_slots. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Owning gym.** Locations are gym-specific. |
| name | VARCHAR(255) | NOT NULL | **Location name.** Displayed in schedule and class details. Examples: `Main Floor`, `Studio A`, `Outdoor Area`, `Yoga Room` |
| description | TEXT | NULL | **Location details.** What equipment is there, special instructions. Example: `Air conditioned, has rowers and assault bikes.` |
| address | TEXT | NULL | **Physical address.** For gyms with multiple buildings or outdoor locations. |
| capacity | INTEGER | DEFAULT 0 | **Location capacity.** Maximum people that fit. 0 = unlimited. Can override class template capacity if location is smaller. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Availability flag.** FALSE hides location from new session creation (e.g., under renovation). |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(organization_id), INDEX(is_active)
---
### notification_likes
**Purpose:** Tracks social engagement (likes) on notifications/announcements. Allows athletes to acknowledge or appreciate gym announcements.
**Usage Context:** Athletes tap "like" on announcements in their notification feed. Like count displayed on announcement. Used for engagement metrics.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| notification_id | BIGINT | FK → notifications(id), NOT NULL | **Liked item.** Which notification received the like. CASCADE delete removes likes when notification deleted. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Liking user.** Who gave the like. CASCADE delete removes likes when user deleted. |
| created_at | TIMESTAMP | NOT NULL | **Like timestamp.** When user liked the notification. |
**Indexes:** INDEX(notification_id), INDEX(user_id)
**Unique Constraint:** (notification_id, user_id) - One like per user per notification
---
### notifications
**Purpose:** In-app notifications for athletes including gym announcements, PR achievements, workout streaks, and system messages.
**Usage Context:** Created by system events (PR achieved, streak milestone) or admin announcements. Athletes view in Notifications view with read/unread status. Supports organization-scoped announcements.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Recipient.** Which athlete receives this notification. CASCADE delete removes notifications when user deleted. |
| organization_id | BIGINT | FK → organizations(id) | **Gym context.** For org-specific announcements. NULL for personal notifications (PR achievements). |
| type | VARCHAR(50) | NOT NULL | **Notification category.** Values: `announcement` (gym news), `pr_achievement` (new PR), `streak` (consistency milestone), `milestone` (achievement unlocked), `system` (app updates). |
| title | VARCHAR(255) | NOT NULL | **Notification headline.** Bold text in notification list. Example: `New Personal Record!`, `Important Gym Announcement` |
| message | TEXT | NOT NULL | **Notification body.** Full message content. Example: `You hit a new PR on Back Squat: 275 lbs!` |
| data | JSON/TEXT | NULL | **Structured payload.** JSON with additional context. For PR: `{"movement_id": 5, "weight": 275, "previous_pr": 265}`. For navigation or deep linking. |
| read_at | TIMESTAMP | NULL | **Read status.** NULL = unread, timestamp = when user viewed/acknowledged. Used for unread badge count. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** When notification was generated. |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(user_id), COMPOSITE(user_id, read_at), INDEX(created_at DESC), INDEX(type)
---
### organizations
**Purpose:** Gym/affiliate/box entities that athletes can belong to. Organizations can have their own class schedules, coaches, packages, and documents.
**Usage Context:** Admins create organizations representing gyms. Athletes join organizations to access schedules and classes. Multi-gym support allows one athlete to belong to multiple gyms.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. Referenced by many tables (classes, coaches, packages, etc.). |
| name | VARCHAR(255) | UNIQUE, NOT NULL | **Gym name.** Displayed in gym selector, schedules, and membership lists. Examples: `CrossFit Downtown`, `Iron Tribe Fitness`, `F45 Training Studio` |
| description | TEXT | NULL | **Gym description.** About the gym, location, contact info. Displayed on gym profile/selection page. |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** UNIQUE(name)
**Workflow Integration:**
- Admin creates gym: Admin → Organizations → Create
- Athletes join via invitation or admin assignment
- Membership tracked in `user_organizations` junction table
- Organization subscription enables write access for all members
---
### reservations
**Purpose:** Athlete bookings for class sessions. Tracks the full lifecycle from reservation through check-in to attendance or no-show.
**Usage Context:** Created when athlete reserves a spot. Coach updates status on check-in. System marks no-shows after class ends. Links to user_workout when athlete completes class.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| session_id | BIGINT | FK → class_sessions(id), NOT NULL | **Reserved class.** Which session the athlete booked. CASCADE delete removes reservations when session cancelled. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Reserving athlete.** Who made the reservation. CASCADE delete removes reservations when user deleted. |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'reserved' | **Reservation state.** Values: `reserved` (booked, not yet attended), `checked_in` (arrived at class), `cancelled` (athlete cancelled), `no_show` (didn't show up), `attended` (completed class). |
| reserved_at | TIMESTAMP | NOT NULL | **Booking time.** When athlete made the reservation. Used for check-in deadline calculations. |
| checked_in_at | TIMESTAMP | NULL | **Arrival time.** When coach marked athlete as arrived. NULL until checked in. |
| checked_in_by_user_id | BIGINT | FK → users(id) | **Check-in coach.** Which coach confirmed athlete's attendance. Accountability trail. |
| cancelled_at | TIMESTAMP | NULL | **Cancellation time.** When athlete or admin cancelled. Used for late-cancel policy enforcement. |
| cancelled_reason | TEXT | NULL | **Cancellation reason.** Why reservation was cancelled. Example: `Late cancel - illness`, `Class cancelled by gym` |
| no_show_marked_at | TIMESTAMP | NULL | **No-show timestamp.** When system or coach marked athlete as didn't show up. Triggers no-show policy. |
| user_workout_id | BIGINT | FK → user_workouts(id) | **Workout link.** Links to workout log created when athlete checks in. Connects class attendance to performance tracking. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(session_id), INDEX(user_id), INDEX(status)
**Unique Constraint:** (session_id, user_id) - One reservation per athlete per class
**Status State Machine:**
```
reserved → checked_in → attended
↓ ↓
cancelled no_show
```
**Workflow Integration:**
- Athlete reserves: Schedule → [Class] → Reserve → status = `reserved`
- Coach checks in: Roster → [Athlete] → Check In → status = `checked_in`, creates user_workout
- Class completes: status = `attended`, user_workout finalized
- Athlete cancels before class: status = `cancelled`, credit returned
- Athlete doesn't show: status = `no_show`, credit may be forfeited per gym policy
---
### schedule_slots
**Purpose:** Recurring time patterns for class templates. Defines the weekly schedule of when classes occur. Combined with templates to generate actual sessions.
**Usage Context:** Admins define slots like "Monday 6:00 AM, Wednesday 6:00 AM, Friday 6:00 AM" for a template. Materializer uses slots to create class_sessions for upcoming weeks.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| template_id | BIGINT | FK → class_templates(id), NOT NULL | **Parent template.** Which class type this slot schedules. CASCADE delete removes slots when template deleted. |
| location_id | BIGINT | FK → gym_locations(id) | **Location override.** If different from template default. Example: 6am class in Studio A, 5pm class in Main Floor. |
| day_of_week | INTEGER | NOT NULL | **Weekly day.** 0=Sunday, 1=Monday, ..., 6=Saturday. Used by materializer to place sessions on correct days. |
| start_time | TIME | NOT NULL | **Daily start time.** What time the class starts. Example: `06:00:00` for 6:00 AM. End time calculated from template.duration_minutes. |
| override_capacity | INTEGER | NULL | **Capacity override.** If this slot should have different capacity than template default. Example: 6am class limited to 12 due to staffing. |
| is_active | BOOLEAN | NOT NULL, DEFAULT TRUE | **Active flag.** FALSE skips this slot during materialization. Use for temporary schedule changes (holiday closures). |
| created_at | TIMESTAMP | NOT NULL | **Creation timestamp.** |
| updated_at | TIMESTAMP | NOT NULL | **Modification timestamp.** |
**Indexes:** INDEX(template_id), INDEX(day_of_week), INDEX(is_active)
**Day of Week Values:**
| Value | Day |
|-------|-----|
| 0 | Sunday |
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
**Workflow Integration:**
- Admin creates: Class Templates → [Template] → Add Schedule Slot
- Materializer: For each active slot, creates class_sessions for next N weeks
- Schedule changes: Deactivate slots for holidays, reactivate after
---
### session_coaches
**Purpose:** Tracks which coaches are assigned to specific class sessions. Allows multiple coaches per class with lead designation.
**Usage Context:** Coach sees their assigned sessions in Coach Dashboard. Lead coach has primary responsibility. Multiple coaches for larger classes or mentoring.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| session_id | BIGINT | FK → class_sessions(id), NOT NULL | **Assigned class.** Which session the coach is working. CASCADE delete removes assignments when session deleted. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Assigned coach.** Which user is coaching this session. Must have coach_assignments for the gym. |
| is_lead | BOOLEAN | NOT NULL, DEFAULT FALSE | **Lead coach flag.** TRUE for primary coach responsible for the class. Each session should have one lead coach. |
| created_at | TIMESTAMP | NOT NULL | **Assignment timestamp.** When coach was assigned to this session. |
**Indexes:** INDEX(session_id), INDEX(user_id)
**Unique Constraint:** (session_id, user_id) - Coach can only be assigned once per session
**Workflow Integration:**
- Auto-assigned when sessions created from templates (if template has default coach)
- Manually assigned by admin for substitutions or one-off sessions
- Lead coach shown prominently in class details
- Coaches see assigned sessions in their dashboard
---
### user_organizations
**Purpose:** Junction table connecting users to organizations (gym memberships). Enables multi-gym support where athletes can belong to multiple gyms.
**Usage Context:** Created when athlete joins a gym. Used for gym selector, subscription checks, and access control. Role field enables per-gym permissions.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), NOT NULL | **Member.** Which user belongs to the organization. CASCADE delete removes memberships when user deleted. |
| organization_id | BIGINT | FK → organizations(id), NOT NULL | **Gym.** Which organization the user belongs to. RESTRICT prevents org deletion with members. |
| role | VARCHAR(50) | DEFAULT 'member' | **Membership role.** Values: `member` (standard athlete), `coach` (elevated permissions - deprecated, use coach_assignments), `admin` (org admin). |
| joined_at | TIMESTAMP | NOT NULL | **Membership start.** When user became a member. Used for tenure reports. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** |
**Indexes:** INDEX(user_id), INDEX(organization_id), COMPOSITE(user_id, organization_id)
**Unique Constraint:** (user_id, organization_id) - User can only join each org once
**Workflow Integration:**
- Admin adds member: Admin → Organizations → [Org] → Members → Add
- Athlete sees gyms in gym selector dropdown
- Athlete's schedules filtered by their organizations
- Organization subscription benefits all members
- Removing membership doesn't delete user, just removes from gym
---
### user_settings
**Purpose:** User preferences for display, units, timezone, and notifications. Separate from users table to allow easy defaults and clean separation.
**Usage Context:** Created automatically for new users with defaults. Updated via Settings view. Applied throughout app for weight display, time formatting, etc.
| Column | Type | Constraints | Usage Context & Business Meaning |
|--------|------|-------------|----------------------------------|
| id | BIGINT | PK, AUTO_INCREMENT | System-generated identifier. |
| user_id | BIGINT | FK → users(id), UNIQUE, NOT NULL | **Settings owner.** One settings record per user. CASCADE delete removes settings when user deleted. |
| notification_preferences | TEXT | NULL | **Notification config.** JSON object controlling which notifications to receive. Example: `{"email_pr": true, "email_announcements": true, "push_class_reminder": true}` |
| data_export_format | VARCHAR(50) | DEFAULT 'json' | **Export preference.** Format for workout data export. Values: `json`, `csv`. |
| theme | VARCHAR(50) | DEFAULT 'light' | **UI theme.** App appearance. Values: `light`, `dark`, `system` (follow device). |
| font_family | VARCHAR(50) | DEFAULT 'system' | **Font preference.** Display font. Values: `system`, `roboto`, `inter`. |
| weight_unit | VARCHAR(20) | DEFAULT 'lbs' | **Weight display.** How weights are shown/entered. Values: `lbs` (pounds), `kg` (kilograms). Converts automatically. |
| distance_unit | VARCHAR(20) | DEFAULT 'meters' | **Distance display.** How distances are shown. Values: `meters`, `miles`, `km`. Used for running/rowing. |
| timezone | VARCHAR(50) | DEFAULT 'America/New_York' | **User timezone.** IANA timezone for schedule display. Class times converted from UTC. Example: `America/Los_Angeles` |
| admin_user_event_notifications | BOOLEAN | NOT NULL, DEFAULT TRUE | **Admin email flag.** For admins only: receive emails when new users register, password resets, etc. |
| created_at | TIMESTAMP | NOT NULL | **Record creation.** |
| updated_at | TIMESTAMP | NOT NULL | **Last modification.** Updated when user changes any setting. |
**Indexes:** UNIQUE(user_id)
**Workflow Integration:**
- Created automatically with defaults when user registers
- User modifies via Settings → Preferences
- Weight unit applied to all weight displays and inputs (auto-conversion)
- Timezone applied to all schedule/class time displays
- Notifications preferences checked before sending any notification
## Standard Movements
The application pre-seeds 31 standard CrossFit movements on initialization:
### Weightlifting (11 movements)
- Back Squat
- Front Squat
- Overhead Squat
- Deadlift
- Sumo Deadlift
- Clean
- Power Clean
- Snatch
- Power Snatch
- Clean and Jerk
- Thruster
### Gymnastics (8 movements)
- Pull-up
- Chest-to-Bar Pull-up
- Bar Muscle-up
- Ring Muscle-up
- Handstand Push-up
- Strict Handstand Push-up
- Toes-to-Bar
- Knees-to-Elbow
### Bodyweight (6 movements)
- Push-up
- Sit-up
- Air Squat
- Burpee
- Box Jump
- Wall Ball
### Cardio (6 movements)
- Rowing
- Running
- Assault Bike
- Ski Erg
- Jump Rope
- Swimming
**Note:** Users can also create custom movements via the movements API.
## Migration History
Database migrations are managed through `internal/repository/migrations.go` and tracked in the `schema_migrations` table.
### v0.1.0 - Initial Schema
**Description:** Base schema with users, workouts, movements, workout_movements tables
**Tables Created:**
- users (basic auth fields)
- workouts (user-specific instances)
- movements (exercise definitions)
- workout_movements (junction table)
### v0.2.0 - Password Reset
**Description:** Add password reset token fields to users table
**Changes:**
- Added `reset_token` (VARCHAR/TEXT)
- Added `reset_token_expires_at` (TIMESTAMP)
**Features Enabled:** Password reset via email
### v0.3.0 - Personal Records
**Description:** Add PR tracking to workout_movements
**Changes:**
- Added `is_pr` (BOOLEAN) to workout_movements table
**Features Enabled:**
- Automatic PR detection on workout creation
- Manual PR flag toggling
- PR history views
### v0.3.1 - Email Verification
**Description:** Add email verification fields to users table
**Changes:**
- Added `email_verified` (BOOLEAN, DEFAULT FALSE)
- Added `email_verified_at` (TIMESTAMP)
- Added `verification_token` (VARCHAR/TEXT)
- Added `verification_token_expires_at` (TIMESTAMP)
**Features Enabled:**
- Email verification on registration
- Re-verification on email change
- Verification status tracking
### v0.3.2 - Remember Me
**Description:** Add refresh_tokens table for persistent sessions
**Changes:**
- Created `refresh_tokens` table with:
- id, user_id, token, expires_at, created_at, revoked_at, device_info
**Features Enabled:**
- Remember Me checkbox on login
- 30-day persistent sessions
- Multi-device session management
- Token revocation on logout
### v0.3.3 - User Profiles
**Description:** Add birthday field to users table for profile editing
**Changes:**
- Added `birthday` (DATE) to users table
**Features Enabled:**
- User profile editing (name, email, birthday)
- Profile information display
### v0.11.0 - Data Change Audit Logging
**Description:** Add data_change_logs table for tracking entity modifications
**Changes:**
- Created `data_change_logs` table with:
- id, entity_type, entity_id, operation, user_id, user_email
- before_value, after_value (JSON), ip_address, created_at
**Features Enabled:**
- Complete audit trail for WOD and Movement modifications
- Before/after value comparison for updates
- Admin-only access to change history
- Filtering by entity type, operation, user, date range
- Log retention management via cleanup API
## API Endpoints
### Authentication
- `POST /api/auth/register` - User registration
- `POST /api/auth/login` - User login (with optional Remember Me)
- `POST /api/auth/logout` - User logout (revokes refresh token)
- `POST /api/auth/refresh` - Refresh JWT using refresh token
- `POST /api/auth/forgot-password` - Request password reset
- `POST /api/auth/reset-password` - Reset password with token
- `GET /api/auth/verify-email?token=...` - Verify email address
- `POST /api/auth/resend-verification` - Resend verification email
### Users
- `GET /api/users/profile` - Get current user profile
- `PUT /api/users/profile` - Update user profile (name, email, birthday)
### Movements
- `GET /api/movements` - List all movements
- `GET /api/movements/search?q=...` - Search movements by name
- `POST /api/movements` - Create custom movement (authenticated)
### Workouts
- `POST /api/workouts` - Create new workout
- `GET /api/workouts` - List user's workouts
- `GET /api/workouts/{id}` - Get single workout
- `PUT /api/workouts/{id}` - Update workout
- `DELETE /api/workouts/{id}` - Delete workout
- `GET /api/workouts/prs` - Get personal records (aggregated by movement)
- `GET /api/workouts/pr-movements?limit=5` - Get recent PR-flagged movements
- `POST /api/workouts/movements/{id}/toggle-pr` - Toggle PR flag
### Performance Tracking
- `GET /api/performance/search?q=...` - Unified search for movements and WODs
- `GET /api/performance/movements/{id}` - Get movement performance history with calculated 1RM
- Returns: `performances` array with `calculated_1rm` and `formula` for each record
- Returns: `best_1rm` - Overall best estimated 1RM across all performances
- Returns: `best_formula` - Formula used for best 1RM (Actual 1RM, Epley, or Wathan)
- `GET /api/performance/wods/{id}` - Get WOD performance history
### Data Change Logs (Admin Only)
- `GET /api/data-change-logs` - List data change logs with pagination and filters
- Query params: entity_type, entity_id, operation, user_id, user_email, start_date, end_date, limit, offset
- `GET /api/data-change-logs/{id}` - Get single data change log entry
- `GET /api/data-change-logs/entity/{entity_type}/{entity_id}` - Get change history for specific entity
- `POST /api/admin/data-change-logs/cleanup` - Delete old logs (retention_days parameter)
## Security Considerations
1. **Password Storage:** Bcrypt hashing with cost factor ≥12
2. **SQL Injection:** All queries use parameterized statements (sqlx)
3. **Authentication:** JWT tokens with configurable expiration
4. **Refresh Tokens:** Secure random generation, single-use on revocation
5. **Email Tokens:** 32-byte cryptographically secure tokens
6. **Authorization:** Users can only access their own workouts and data
7. **CORS:** Configurable allowed origins via environment variable
8. **Cascading Deletes:** User data properly deleted on account deletion
## Performance Optimization
1. **Indexes:** Proper indexes on foreign keys and query patterns
2. **Composite Indexes:** Multi-column indexes for user_id + workout_date queries
3. **Eager Loading:** Movement details loaded with workouts to avoid N+1 queries
4. **Connection Pooling:** Database connection pool managed by database/sql
5. **Prepared Statements:** Reusable prepared statements for common queries
## Backup and Recovery
1. **SQLite Development:** Database file (`actalog.db`) can be backed up directly
2. **PostgreSQL Production:** Use pg_dump for regular backups
3. **Migration Tracking:** schema_migrations table preserves migration history
4. **Data Export:** Users can export their workout data (planned feature)
## Future Enhancements
Potential future schema additions (not yet implemented):
- **workout_templates** table for pre-defined benchmark WODs (Fran, Murph, etc.)
- **user_settings** table for preferences (theme, units, notifications)
- **social features** (followers, activity feed, leaderboards)
- **workout_comments** for notes and reflections over time
- **scheduled_backups** table for remote backup scheduling
## Triggers
### Protected-user triggers (migration 0.35.0)
`protected_users_no_update` and `protected_users_no_delete` are BEFORE-UPDATE/DELETE triggers on the `users` table that raise an error when the target row's email is in the protected list. Per-dialect implementations:
- **SQLite:** `RAISE(ABORT, ...)`
- **PostgreSQL:** PL/pgSQL function with `RAISE EXCEPTION`
- **MySQL/MariaDB:** `SIGNAL SQLSTATE '45000'`
Error message text is contract-locked: `protected user: writes blocked at db layer`. The L4 service-layer wrapper pattern-matches this string. See `docs/security/PROTECTED_USERS.md` and `internal/repository/protected_triggers_sql.go` for the canonical SQL.
## Version History
- **v0.35.0**: Protected-user BEFORE UPDATE / BEFORE DELETE triggers on `users` table (L3 security, all three dialects)
- **v0.27.0-beta** (Current Schema): Class scheduling Phase 4 - documents, packages, credits, waitlist, notifications
- **v0.26.0-beta**: Class scheduling Phases 1-3 - locations, templates, sessions, coaches, reservations
- **v0.16.0-beta**: Notification likes feature
- **v0.14.0-beta**: Subscription billing system (user + organization subscriptions)
- **v0.11.0-beta**: Data change audit logging with before/after value tracking
- **Application v0.10.0-beta**: Docker deployment system and Wodify performance import
- **Application v0.8.x-beta**: Cross-database backup/restore, PostgreSQL pgx driver migration
- **v0.3.3-beta**: User profile editing with birthday field
- **Application v0.7.2-beta**: No schema changes (1RM calculation and display enhancements)
- **Application v0.7.1-beta**: No schema changes (Wodify import date fixes)
- **Application v0.4.1-beta**: No schema changes (bug fixes and deployment improvements)
- **Application v0.4.0-beta**: No schema changes (backend refactoring for template architecture)
- **v0.3.2-beta**: Remember Me functionality with refresh tokens
- **v0.3.1-beta**: Email verification system
- **v0.3.0-beta**: Personal Records (PR) tracking
- **v0.2.0-beta**: Password reset functionality
- **v0.1.0**: Initial schema design
**Note:** Schema version may differ from application version when releases contain only bug fixes or code refactoring without database changes.