# EventCrew — Core Database Schema

> Source: Design Document v1.3 — Section 3.5  
> All 12 findings from the database review (v1.3) are incorporated.  
> Last updated: March 2026

---

## Primary Key Convention: ULID

> **All tables use ULID (Universally Unique Lexicographically Sortable Identifier) as primary key — NO UUID v4.**
>
> **Reason:** UUID v4 is random, causing B-tree index fragmentation in InnoDB on every INSERT. ULID is monotonically increasing (time-ordered) and preserves index locality.
>
> - Laravel: use `Str::ulid()` or the `HasUlids` trait
> - Migrations: `$table->ulid('id')->primary()`
>
> Externally visible IDs (URLs, barcodes, API) use ULID. Internal pivot tables may use auto-increment integer PK for join performance.

---

## Table of Contents

1. [3.5.1 Foundation](#351-foundation)
2. [3.5.2 Locations](#352-locations)
3. [3.5.3 Festival Sections, Time Slots & Shifts](#353-festival-sections-time-slots--shifts)
4. [3.5.4 Volunteer Profile & History](#354-volunteer-profile--history)
5. [3.5.5 Crowd Types, Persons & Crowd Lists](#355-crowd-types-persons--crowd-lists)
6. [3.5.6 Accreditation Engine](#356-accreditation-engine)
7. [3.5.7 Artists & Advancing](#357-artists--advancing)
8. [3.5.8 Communication & Briefings](#358-communication--briefings)
9. [3.5.9 Forms, Check-In & Operational](#359-forms-check-in--operational)
10. [3.5.10 Database Design Rules & Index Strategy](#3510-database-design-rules--index-strategy)

---

## 3.5.1 Foundation

### `users`

| Column              | Type               | Notes                |
| ------------------- | ------------------ | -------------------- |
| `id`                | ULID               | PK, `HasUlids` trait |
| `name`              | string             |                      |
| `email`             | string             | unique               |
| `password`          | string             | hashed               |
| `timezone`          | string             |                      |
| `locale`            | string             |                      |
| `avatar`            | string nullable    |                      |
| `email_verified_at` | timestamp nullable |                      |
| `deleted_at`        | timestamp nullable | Soft delete          |

**Relations:** `belongsToMany` organisations (via `organisation_user`), `belongsToMany` events (via `event_user_roles`)  
**Soft delete:** yes

---

### `organisations`

| Column           | Type               | Notes                                  |
| ---------------- | ------------------ | -------------------------------------- |
| `id`             | ULID               | PK                                     |
| `name`           | string             |                                        |
| `slug`           | string             | unique                                 |
| `billing_status` | string             |                                        |
| `settings`       | JSON               | Display prefs only — no queryable data |
| `created_at`     | timestamp          |                                        |
| `deleted_at`     | timestamp nullable | Soft delete                            |

**Relations:** `hasMany` events, crowd_types, accreditation_categories  
**Soft delete:** yes

---

### `organisation_user`

| Column            | Type    | Notes                             |
| ----------------- | ------- | --------------------------------- |
| `id`              | int AI  | PK — integer for join performance |
| `user_id`         | ULID FK | → users                           |
| `organisation_id` | ULID FK | → organisations                   |
| `role`            | string  | Spatie role via pivot             |

**Type:** Pivot table — integer PK

---

### `user_invitations`

| Column               | Type             | Notes                             |
| -------------------- | ---------------- | --------------------------------- |
| `id`                 | ULID             | PK                                |
| `email`              | string           |                                   |
| `invited_by_user_id` | ULID FK          | → users                           |
| `organisation_id`    | ULID FK          | → organisations                   |
| `event_id`           | ULID FK nullable | → events                          |
| `role`               | string           |                                   |
| `token`              | ULID             | unique — sent in invitation email |
| `status`             | enum             | `pending\|accepted\|expired`      |
| `expires_at`         | timestamp        |                                   |

**Indexes:** `(token)`, `(email, status)`  
**Logic:** On accept: look up existing account by email or create new one.

---

### `events`

| Column            | Type               | Notes                                                                     |
| ----------------- | ------------------ | ------------------------------------------------------------------------- |
| `id`              | ULID               | PK                                                                        |
| `organisation_id` | ULID FK            | → organisations                                                           |
| `name`            | string             |                                                                           |
| `slug`            | string             |                                                                           |
| `start_date`      | date               |                                                                           |
| `end_date`        | date               |                                                                           |
| `timezone`        | string             |                                                                           |
| `status`          | enum               | `draft\|published\|registration_open\|buildup\|showday\|teardown\|closed` |
| `deleted_at`      | timestamp nullable | Soft delete                                                               |

**Relations:** `belongsTo` organisation, `hasMany` festival_sections, time_slots, persons, artists, briefings  
**Indexes:** `(organisation_id, status)`  
**Soft delete:** yes

---

### `event_user_roles`

| Column     | Type    | Notes                             |
| ---------- | ------- | --------------------------------- |
| `id`       | int AI  | PK — integer for join performance |
| `user_id`  | ULID FK | → users                           |
| `event_id` | ULID FK | → events                          |
| `role`     | string  |                                   |

**Type:** Pivot table — integer PK

---

## 3.5.2 Locations

> **New table (resolves review finding #3):** `locations` was referenced by `shifts` but never defined. Locations are event-scoped and reusable across sections.

### `locations`

| Column                | Type                   | Notes    |
| --------------------- | ---------------------- | -------- |
| `id`                  | ULID                   | PK       |
| `event_id`            | ULID FK                | → events |
| `name`                | string                 |          |
| `address`             | string nullable        |          |
| `lat`                 | decimal(10,8) nullable |          |
| `lng`                 | decimal(11,8) nullable |          |
| `description`         | text nullable          |          |
| `access_instructions` | text nullable          |          |

**Indexes:** `(event_id)`  
**Usage:** Referenced by `shifts.location_id`

---

## 3.5.3 Festival Sections, Time Slots & Shifts

> Three-layer Crescat model. Critical improvement: `time_slot_id` denormalised onto `shift_assignments` for DB-enforceable conflict detection (finding #2). Shift swaps split into two tables (finding #10).

### `festival_sections`

| Column       | Type               | Notes       |
| ------------ | ------------------ | ----------- |
| `id`         | ULID               | PK          |
| `event_id`   | ULID FK            | → events    |
| `name`       | string             |             |
| `sort_order` | int                |             |
| `deleted_at` | timestamp nullable | Soft delete |

**Relations:** `hasMany` shifts  
**Indexes:** `(event_id, sort_order)`  
**Soft delete:** yes

---

### `time_slots`

| Column           | Type    | Notes                                                                               |
| ---------------- | ------- | ----------------------------------------------------------------------------------- |
| `id`             | ULID    | PK                                                                                  |
| `event_id`       | ULID FK | → events                                                                            |
| `name`           | string  |                                                                                     |
| `person_type`    | enum    | `CREW\|VOLUNTEER\|PRESS\|PHOTO\|PARTNER` — controls visibility in registration form |
| `date`           | date    |                                                                                     |
| `start_time`     | time    |                                                                                     |
| `end_time`       | time    |                                                                                     |
| `duration_hours` | decimal |                                                                                     |

**Relations:** `hasMany` shifts  
**Indexes:** `(event_id, person_type, date)`

---

### `shifts`

| Column                    | Type               | Notes                                                                 |
| ------------------------- | ------------------ | --------------------------------------------------------------------- |
| `id`                      | ULID               | PK                                                                    |
| `festival_section_id`     | ULID FK            | → festival_sections                                                   |
| `time_slot_id`            | ULID FK            | → time_slots                                                          |
| `location_id`             | ULID FK nullable   | → locations                                                           |
| `slots_total`             | int                |                                                                       |
| `slots_open_for_claiming` | int                | Number of slots visible & claimable in volunteer portal               |
| `assigned_crew_id`        | ULID FK nullable   | → users                                                               |
| `events_during_shift`     | JSON               | Array of performance_ids — opaque reference list, no filtering needed |
| `status`                  | string             |                                                                       |
| `deleted_at`              | timestamp nullable | Soft delete                                                           |

**Relations:** `belongsTo` festival_section, time_slot, location; `hasMany` shift_assignments  
**Indexes:** `(festival_section_id, time_slot_id)`, `(time_slot_id, status)`  
**Soft delete:** yes

---

### `shift_assignments`

| Column             | Type               | Notes                                                                |
| ------------------ | ------------------ | -------------------------------------------------------------------- |
| `id`               | ULID               | PK                                                                   |
| `shift_id`         | ULID FK            | → shifts                                                             |
| `person_id`        | ULID FK            | → persons                                                            |
| `time_slot_id`     | ULID FK            | Denormalised from shifts — enables DB-enforceable conflict detection |
| `status`           | enum               | `pending_approval\|approved\|rejected\|cancelled\|completed`         |
| `auto_approved`    | bool               |                                                                      |
| `assigned_by`      | ULID FK nullable   | → users                                                              |
| `assigned_at`      | timestamp nullable |                                                                      |
| `approved_by`      | ULID FK nullable   | → users                                                              |
| `approved_at`      | timestamp nullable |                                                                      |
| `rejection_reason` | text nullable      |                                                                      |
| `deleted_at`       | timestamp nullable | Soft delete                                                          |

**Unique constraint:** `UNIQUE(person_id, time_slot_id)` — DB-enforceable conflict detection  
**Indexes:** `(shift_id, status)`, `(person_id, status)`, `(person_id, time_slot_id)`  
**Soft delete:** yes

---

### `volunteer_availabilities`

| Column         | Type      | Notes        |
| -------------- | --------- | ------------ |
| `id`           | ULID      | PK           |
| `person_id`    | ULID FK   | → persons    |
| `time_slot_id` | ULID FK   | → time_slots |
| `submitted_at` | timestamp |              |

**Purpose:** Volunteer selects available Time Slots — basis for shift matching  
**Unique constraint:** `UNIQUE(person_id, time_slot_id)`  
**Indexes:** `(time_slot_id)`

---

### `shift_absences`

> **New table (finding #10 split)**

| Column                | Type               | Notes                   |
| --------------------- | ------------------ | ----------------------- |
| `id`                  | ULID               | PK                      |
| `shift_assignment_id` | ULID FK            | → shift_assignments     |
| `person_id`           | ULID FK            | → persons               |
| `reason`              | enum               | `sick\|personal\|other` |
| `reported_at`         | timestamp          |                         |
| `status`              | enum               | `open\|filled\|closed`  |
| `closed_at`           | timestamp nullable |                         |

**Purpose:** Volunteer reports absence — shift slot becomes available. Triggers waitlist notification.  
**Indexes:** `(shift_assignment_id)`, `(status)`

---

### `shift_swap_requests`

> **New table (finding #10 split)**

| Column               | Type               | Notes                                               |
| -------------------- | ------------------ | --------------------------------------------------- |
| `id`                 | ULID               | PK                                                  |
| `from_assignment_id` | ULID FK            | → shift_assignments                                 |
| `to_person_id`       | ULID FK            | → persons                                           |
| `message`            | text nullable      |                                                     |
| `status`             | enum               | `pending\|accepted\|rejected\|cancelled\|completed` |
| `reviewed_by`        | ULID FK nullable   | → users                                             |
| `reviewed_at`        | timestamp nullable |                                                     |
| `auto_approved`      | bool               |                                                     |

**Logic:** A asks B to swap. After both agree: coordinator confirms (or auto-approve).  
**Indexes:** `(from_assignment_id)`, `(to_person_id, status)`

---

### `shift_waitlist`

| Column        | Type               | Notes     |
| ------------- | ------------------ | --------- |
| `id`          | ULID               | PK        |
| `shift_id`    | ULID FK            | → shifts  |
| `person_id`   | ULID FK            | → persons |
| `position`    | int                |           |
| `added_at`    | timestamp          |           |
| `notified_at` | timestamp nullable |           |

**Unique constraint:** `UNIQUE(shift_id, person_id)`  
**Logic:** On vacancy: position 1 is automatically notified.  
**Indexes:** `(shift_id, position)`

---

## 3.5.4 Volunteer Profile & History

### `volunteer_profiles`

| Column                    | Type            | Notes                                 |
| ------------------------- | --------------- | ------------------------------------- |
| `id`                      | ULID            | PK                                    |
| `user_id`                 | ULID FK unique  | → users — 1:1                         |
| `bio`                     | text nullable   |                                       |
| `photo_url`               | string nullable |                                       |
| `tshirt_size`             | string nullable |                                       |
| `first_aid`               | bool            |                                       |
| `driving_licence`         | bool            |                                       |
| `allergies`               | text nullable   |                                       |
| `access_requirements`     | text nullable   |                                       |
| `emergency_contact_name`  | string nullable |                                       |
| `emergency_contact_phone` | string nullable |                                       |
| `reliability_score`       | decimal(3,2)    | 0.00–5.00, computed via scheduled job |
| `is_ambassador`           | bool            |                                       |

**Unique constraint:** `UNIQUE(user_id)` — platform-wide, 1:1 with users

---

### `volunteer_festival_history`

| Column               | Type             | Notes           |
| -------------------- | ---------------- | --------------- |
| `id`                 | ULID             | PK              |
| `user_id`            | ULID FK          | → users         |
| `event_id`           | ULID FK          | → events        |
| `organisation_id`    | ULID FK          | → organisations |
| `hours_planned`      | decimal nullable |                 |
| `hours_completed`    | decimal nullable |                 |
| `no_show_count`      | int              |                 |
| `coordinator_rating` | tinyint          | 1–5             |
| `coordinator_notes`  | text nullable    |                 |
| `would_reinvite`     | bool             |                 |

**Note:** Never visible to the volunteer themselves.  
**Unique constraint:** `UNIQUE(user_id, event_id)`  
**Indexes:** `(user_id, event_id)`

---

### `post_festival_evaluations`

| Column                   | Type             | Notes     |
| ------------------------ | ---------------- | --------- |
| `id`                     | ULID             | PK        |
| `event_id`               | ULID FK          | → events  |
| `person_id`              | ULID FK          | → persons |
| `shift_id`               | ULID FK nullable | → shifts  |
| `overall_rating`         | tinyint          | 1–5       |
| `shift_rating`           | tinyint          | 1–5       |
| `would_return`           | bool             |           |
| `feedback_text`          | text nullable    |           |
| `improvement_suggestion` | text nullable    |           |
| `submitted_at`           | timestamp        |           |
| `is_anonymous`           | bool             |           |

**Indexes:** `(event_id, is_anonymous)`, `(person_id)`

---

### `festival_retrospectives`

> **Finding #8:** All KPIs as concrete columns instead of a JSON blob. Enables trend analysis across multiple years.

| Column                     | Type           | Notes                                 |
| -------------------------- | -------------- | ------------------------------------- |
| `id`                       | ULID           | PK                                    |
| `event_id`                 | ULID FK unique | → events                              |
| `generated_at`             | timestamp      |                                       |
| `volunteers_planned`       | int            |                                       |
| `volunteers_completed`     | int            |                                       |
| `no_show_count`            | int            |                                       |
| `no_show_pct`              | decimal(5,2)   |                                       |
| `avg_overall_satisfaction` | decimal(3,2)   |                                       |
| `avg_shift_satisfaction`   | decimal(3,2)   |                                       |
| `would_return_pct`         | decimal(5,2)   |                                       |
| `sections_understaffed`    | int            |                                       |
| `sections_overstaffed`     | int            |                                       |
| `top_feedback`             | JSON           | Array of strings — free-text feedback |
| `notes`                    | text nullable  |                                       |

---

## 3.5.5 Crowd Types, Persons & Crowd Lists

> **Finding #1 (identity fragmentation):** `persons` gets nullable `user_id` as canonical link to platform account.  
> **Finding #4:** `crowd_list_persons` pivot added.  
> **Finding #9:** `persons.email` as indexed deduplication key.

### `crowd_types`

| Column            | Type            | Notes                                                      |
| ----------------- | --------------- | ---------------------------------------------------------- |
| `id`              | ULID            | PK                                                         |
| `organisation_id` | ULID FK         | → organisations                                            |
| `name`            | string          |                                                            |
| `system_type`     | enum            | `CREW\|GUEST\|ARTIST\|VOLUNTEER\|PRESS\|PARTNER\|SUPPLIER` |
| `color`           | string          | hex                                                        |
| `icon`            | string nullable |                                                            |
| `is_active`       | bool            |                                                            |

**Indexes:** `(organisation_id, system_type)`

---

### `persons`

| Column           | Type               | Notes                                                                |
| ---------------- | ------------------ | -------------------------------------------------------------------- |
| `id`             | ULID               | PK                                                                   |
| `user_id`        | ULID FK nullable   | → users — nullable: external guests/artists have no platform account |
| `event_id`       | ULID FK            | → events                                                             |
| `crowd_type_id`  | ULID FK            | → crowd_types                                                        |
| `company_id`     | ULID FK nullable   | → companies                                                          |
| `name`           | string             |                                                                      |
| `email`          | string             | Indexed deduplication key                                            |
| `phone`          | string nullable    |                                                                      |
| `status`         | enum               | `invited\|applied\|pending\|approved\|rejected\|no_show`             |
| `is_blacklisted` | bool               |                                                                      |
| `admin_notes`    | text nullable      |                                                                      |
| `custom_fields`  | JSON               | Event-specific fields — not queryable                                |
| `deleted_at`     | timestamp nullable | Soft delete                                                          |

**Unique constraint:** `UNIQUE(event_id, user_id) WHERE user_id IS NOT NULL`  
**Indexes:** `(event_id, crowd_type_id, status)`, `(email, event_id)`, `(user_id, event_id)`  
**Soft delete:** yes

---

### `companies`

| Column            | Type               | Notes                                     |
| ----------------- | ------------------ | ----------------------------------------- |
| `id`              | ULID               | PK                                        |
| `organisation_id` | ULID FK            | → organisations                           |
| `name`            | string             |                                           |
| `type`            | enum               | `supplier\|partner\|agency\|venue\|other` |
| `contact_name`    | string nullable    |                                           |
| `contact_email`   | string nullable    |                                           |
| `contact_phone`   | string nullable    |                                           |
| `deleted_at`      | timestamp nullable | Soft delete                               |

**Note:** Shared across events within an organisation.  
**Indexes:** `(organisation_id)`  
**Soft delete:** yes

---

### `crowd_lists`

| Column                 | Type             | Notes                |
| ---------------------- | ---------------- | -------------------- |
| `id`                   | ULID             | PK                   |
| `event_id`             | ULID FK          | → events             |
| `crowd_type_id`        | ULID FK          | → crowd_types        |
| `name`                 | string           |                      |
| `type`                 | enum             | `internal\|external` |
| `recipient_company_id` | ULID FK nullable | → companies          |
| `auto_approve`         | bool             |                      |
| `max_persons`          | int nullable     |                      |

**Relations:** `hasMany` persons via `crowd_list_persons` pivot  
**Indexes:** `(event_id, type)`

---

### `crowd_list_persons`

> **New pivot table (finding #4)**

| Column             | Type             | Notes                             |
| ------------------ | ---------------- | --------------------------------- |
| `id`               | int AI           | PK — integer for join performance |
| `crowd_list_id`    | ULID FK          | → crowd_lists                     |
| `person_id`        | ULID FK          | → persons                         |
| `added_at`         | timestamp        |                                   |
| `added_by_user_id` | ULID FK nullable | → users                           |

**Unique constraint:** `UNIQUE(crowd_list_id, person_id)`  
**Indexes:** `(person_id)`

---

## 3.5.6 Accreditation Engine

> **Finding #5:** `event_accreditation_items` activates org-level items per event. Accreditation items are now configured at org level and activated per event with event-specific limits.

### `accreditation_categories`

| Column            | Type            | Notes                                                    |
| ----------------- | --------------- | -------------------------------------------------------- |
| `id`              | ULID            | PK                                                       |
| `organisation_id` | ULID FK         | → organisations                                          |
| `name`            | string          | e.g. Wristband, Food & Beverage, Communication, Clothing |
| `sort_order`      | int             |                                                          |
| `icon`            | string nullable |                                                          |

**Indexes:** `(organisation_id)`

---

### `accreditation_items`

| Column                      | Type                  | Notes                      |
| --------------------------- | --------------------- | -------------------------- |
| `id`                        | ULID                  | PK                         |
| `accreditation_category_id` | ULID FK               | → accreditation_categories |
| `name`                      | string                |                            |
| `is_date_dependent`         | bool                  |                            |
| `barcode_type`              | enum                  | `qr\|code128\|ean13`       |
| `ticket_visual_url`         | string nullable       |                            |
| `cost_price`                | decimal(8,2) nullable |                            |
| `sort_order`                | int                   |                            |

**Note:** Org-level items, activated per event via `event_accreditation_items`.

---

### `event_accreditation_items`

> **New table (finding #5)**

| Column                    | Type          | Notes                 |
| ------------------------- | ------------- | --------------------- |
| `id`                      | ULID          | PK                    |
| `event_id`                | ULID FK       | → events              |
| `accreditation_item_id`   | ULID FK       | → accreditation_items |
| `max_quantity_per_person` | int nullable  |                       |
| `total_budget_quantity`   | int nullable  |                       |
| `is_active`               | bool          |                       |
| `notes`                   | text nullable |                       |

**Unique constraint:** `UNIQUE(event_id, accreditation_item_id)`  
**Indexes:** `(event_id, is_active)`

---

### `accreditation_assignments`

| Column                  | Type               | Notes                                                     |
| ----------------------- | ------------------ | --------------------------------------------------------- |
| `id`                    | ULID               | PK                                                        |
| `person_id`             | ULID FK            | → persons                                                 |
| `accreditation_item_id` | ULID FK            | → accreditation_items                                     |
| `event_id`              | ULID FK            | → events — FK to event_accreditation_items for validation |
| `date`                  | date nullable      | For date-dependent items                                  |
| `quantity`              | int                |                                                           |
| `is_handed_out`         | bool               |                                                           |
| `handed_out_at`         | timestamp nullable |                                                           |
| `handed_out_by_user_id` | ULID FK nullable   | → users                                                   |

**Indexes:** `(person_id, event_id)`, `(accreditation_item_id, is_handed_out)`

---

### `access_zones`

| Column        | Type          | Notes                           |
| ------------- | ------------- | ------------------------------- |
| `id`          | ULID          | PK                              |
| `event_id`    | ULID FK       | → events                        |
| `name`        | string        | e.g. Backstage, VIP, Main Stage |
| `zone_code`   | varchar(20)   | unique per event                |
| `description` | text nullable |                                 |

**Note:** Day-coupling via `access_zone_days` pivot.  
**Indexes:** `(event_id)`

---

### `access_zone_days`

> **New table (finding #8: replaces JSON `days` column)**

| Column           | Type    | Notes                             |
| ---------------- | ------- | --------------------------------- |
| `id`             | int AI  | PK — integer for join performance |
| `access_zone_id` | ULID FK | → access_zones                    |
| `day_date`       | date    |                                   |

**Purpose:** Queryable — which zones are active on date X?  
**Unique constraint:** `UNIQUE(access_zone_id, day_date)`  
**Indexes:** `(day_date)`

---

### `person_access_zones`

| Column           | Type              | Notes                             |
| ---------------- | ----------------- | --------------------------------- |
| `id`             | int AI            | PK — integer for join performance |
| `person_id`      | ULID FK           | → persons                         |
| `access_zone_id` | ULID FK           | → access_zones                    |
| `valid_from`     | datetime          |                                   |
| `valid_to`       | datetime nullable |                                   |

**Indexes:** `(person_id)`, `(access_zone_id)`

---

## 3.5.7 Artists & Advancing

> **Finding #8:** `stages.active_days` JSON replaced by `stage_days` pivot. `milestone_flags` JSON remains (opaque toggle-set, never filtered).

### `artists`

| Column              | Type               | Notes                                                          |
| ------------------- | ------------------ | -------------------------------------------------------------- |
| `id`                | ULID               | PK                                                             |
| `event_id`          | ULID FK            | → events                                                       |
| `name`              | string             |                                                                |
| `booking_status`    | enum               | `concept\|requested\|option\|confirmed\|contracted\|cancelled` |
| `star_rating`       | tinyint            | 1–5                                                            |
| `project_leader_id` | ULID FK nullable   | → users                                                        |
| `milestone_flags`   | JSON               | Binary toggle-set — OK as JSON                                 |
| `advance_open_from` | datetime nullable  |                                                                |
| `advance_open_to`   | datetime nullable  |                                                                |
| `portal_token`      | ULID unique        | Access to artist portal without account                        |
| `deleted_at`        | timestamp nullable | Soft delete                                                    |

**Relations:** `hasMany` performances, advance_sections, artist_contacts, artist_riders  
**Soft delete:** yes

---

### `performances`

| Column            | Type    | Notes                           |
| ----------------- | ------- | ------------------------------- |
| `id`              | ULID    | PK                              |
| `artist_id`       | ULID FK | → artists                       |
| `stage_id`        | ULID FK | → stages                        |
| `date`            | date    |                                 |
| `start_time`      | time    |                                 |
| `end_time`        | time    |                                 |
| `booking_status`  | string  |                                 |
| `check_in_status` | enum    | `expected\|checked_in\|no_show` |

**Note:** B2B detection via overlap query on `stage_id + date + time window`.  
**Indexes:** `(stage_id, date, start_time, end_time)`

---

### `stages`

| Column     | Type         | Notes    |
| ---------- | ------------ | -------- |
| `id`       | ULID         | PK       |
| `event_id` | ULID FK      | → events |
| `name`     | string       |          |
| `color`    | string       | hex      |
| `capacity` | int nullable |          |

**Note:** Day-activation via `stage_days` pivot (finding #8).  
**Relations:** `hasMany` performances  
**Indexes:** `(event_id)`

---

### `stage_days`

> **New table (finding #8: replaces `stages.active_days` JSON)**

| Column     | Type    | Notes                             |
| ---------- | ------- | --------------------------------- |
| `id`       | int AI  | PK — integer for join performance |
| `stage_id` | ULID FK | → stages                          |
| `day_date` | date    |                                   |

**Unique constraint:** `UNIQUE(stage_id, day_date)`

---

### `advance_sections`

| Column       | Type              | Notes                                      |
| ------------ | ----------------- | ------------------------------------------ |
| `id`         | ULID              | PK                                         |
| `artist_id`  | ULID FK           | → artists                                  |
| `name`       | string            |                                            |
| `type`       | enum              | `guest_list\|contacts\|production\|custom` |
| `is_open`    | bool              |                                            |
| `open_from`  | datetime nullable |                                            |
| `open_to`    | datetime nullable |                                            |
| `sort_order` | int               |                                            |

**Note:** Crescat section model — each section independently submittable.  
**Indexes:** `(artist_id, is_open)`

---

### `advance_submissions`

| Column               | Type               | Notes                          |
| -------------------- | ------------------ | ------------------------------ |
| `id`                 | ULID               | PK                             |
| `advance_section_id` | ULID FK            | → advance_sections             |
| `submitted_by_name`  | string             |                                |
| `submitted_by_email` | string             |                                |
| `submitted_at`       | timestamp          |                                |
| `status`             | enum               | `pending\|accepted\|declined`  |
| `reviewed_by`        | ULID FK nullable   | → users                        |
| `reviewed_at`        | timestamp nullable |                                |
| `data`               | JSON               | Free form data — not queryable |

**Indexes:** `(advance_section_id, status)`

---

### `artist_contacts`

| Column               | Type            | Notes                            |
| -------------------- | --------------- | -------------------------------- |
| `id`                 | ULID            | PK                               |
| `artist_id`          | ULID FK         | → artists                        |
| `name`               | string          |                                  |
| `email`              | string nullable |                                  |
| `phone`              | string nullable |                                  |
| `role`               | string          | e.g. tour manager, agent, booker |
| `receives_briefing`  | bool            |                                  |
| `receives_infosheet` | bool            |                                  |

**Indexes:** `(artist_id)`

---

### `artist_riders`

| Column      | Type    | Notes                                |
| ----------- | ------- | ------------------------------------ |
| `id`        | ULID    | PK                                   |
| `artist_id` | ULID FK | → artists                            |
| `category`  | enum    | `technical\|hospitality`             |
| `items`     | JSON    | Unstructured rider data — OK as JSON |

**Indexes:** `(artist_id, category)`

---

### `itinerary_items`

| Column          | Type            | Notes                                              |
| --------------- | --------------- | -------------------------------------------------- |
| `id`            | ULID            | PK                                                 |
| `artist_id`     | ULID FK         | → artists                                          |
| `type`          | enum            | `transfer\|pickup\|delivery\|checkin\|performance` |
| `datetime`      | datetime        |                                                    |
| `from_location` | string nullable |                                                    |
| `to_location`   | string nullable |                                                    |
| `notes`         | text nullable   |                                                    |

**Note:** Flights/hotels: Out of Scope.  
**Indexes:** `(artist_id, datetime)`

---

## 3.5.8 Communication & Briefings

> **Finding #11:** `broadcast_messages` extended with polymorphic `broadcast_message_targets` for flexible audience definition.

### `briefing_templates`

| Column       | Type    | Notes                                       |
| ------------ | ------- | ------------------------------------------- |
| `id`         | ULID    | PK                                          |
| `event_id`   | ULID FK | → events                                    |
| `name`       | string  |                                             |
| `type`       | enum    | `crowd\|artist\|volunteer\|supplier`        |
| `blocks`     | JSON    | Drag-and-drop block config — never filtered |
| `is_default` | bool    |                                             |

**Indexes:** `(event_id, type)`

---

### `briefings`

| Column                 | Type              | Notes                                  |
| ---------------------- | ----------------- | -------------------------------------- |
| `id`                   | ULID              | PK                                     |
| `event_id`             | ULID FK           | → events                               |
| `briefing_template_id` | ULID FK nullable  | → briefing_templates                   |
| `name`                 | string            |                                        |
| `target_crowd_types`   | JSON              | Array of crowd_type IDs                |
| `send_from`            | datetime nullable |                                        |
| `send_until`           | datetime nullable |                                        |
| `status`               | enum              | `draft\|queued\|sending\|sent\|paused` |

**Indexes:** `(event_id, status)`

---

### `briefing_sends`

| Column        | Type               | Notes                              |
| ------------- | ------------------ | ---------------------------------- |
| `id`          | ULID               | PK                                 |
| `briefing_id` | ULID FK            | → briefings                        |
| `person_id`   | ULID FK            | → persons                          |
| `status`      | enum               | `queued\|sent\|opened\|downloaded` |
| `sent_at`     | timestamp nullable |                                    |
| `opened_at`   | timestamp nullable |                                    |

**Note:** Track per person per briefing. No soft delete — audit record.  
**Indexes:** `(status, briefing_id)` — queue processing, `(person_id)`

---

### `communication_campaigns`

| Column            | Type              | Notes                                        |
| ----------------- | ----------------- | -------------------------------------------- |
| `id`              | ULID              | PK                                           |
| `event_id`        | ULID FK           | → events                                     |
| `type`            | enum              | `email\|sms\|whatsapp`                       |
| `name`            | string            |                                              |
| `body`            | text              |                                              |
| `recipient_group` | JSON              | Target filter description                    |
| `status`          | enum              | `draft\|scheduled\|sending\|sent\|cancelled` |
| `scheduled_at`    | datetime nullable |                                              |
| `sent_at`         | datetime nullable |                                              |
| `sent_count`      | int               |                                              |
| `failed_count`    | int               |                                              |

**Note:** Bulk campaigns. SMS+WhatsApp via Zender.  
**Indexes:** `(event_id, type, status)`

---

### `messages`

| Column                | Type               | Notes                                                |
| --------------------- | ------------------ | ---------------------------------------------------- |
| `id`                  | ULID               | PK                                                   |
| `event_id`            | ULID FK            | → events                                             |
| `sender_user_id`      | ULID FK            | → users                                              |
| `recipient_person_id` | ULID FK            | → persons                                            |
| `body`                | text               |                                                      |
| `urgency`             | enum               | `normal\|urgent\|emergency`                          |
| `channel_used`        | enum               | `email\|sms\|whatsapp` — determined by ZenderService |
| `read_at`             | timestamp nullable |                                                      |
| `replied_at`          | timestamp nullable |                                                      |
| `created_at`          | timestamp          |                                                      |

**Indexes:** `(event_id, recipient_person_id)`, `(recipient_person_id, read_at)`

---

### `message_replies`

| Column          | Type          | Notes                             |
| --------------- | ------------- | --------------------------------- |
| `id`            | ULID          | PK                                |
| `message_id`    | ULID FK       | → messages                        |
| `person_id`     | ULID FK       | → persons                         |
| `body`          | text          |                                   |
| `status_update` | enum nullable | `on_my_way\|arrived\|sick\|other` |
| `created_at`    | timestamp     |                                   |

**Note:** Volunteer replies via portal. No soft delete — audit record.  
**Indexes:** `(message_id)`

---

### `broadcast_messages`

| Column            | Type      | Notes                       |
| ----------------- | --------- | --------------------------- |
| `id`              | ULID      | PK                          |
| `event_id`        | ULID FK   | → events                    |
| `sender_user_id`  | ULID FK   | → users                     |
| `body`            | text      |                             |
| `urgency`         | enum      | `normal\|urgent\|emergency` |
| `channel_used`    | enum      | `email\|sms\|whatsapp`      |
| `sent_at`         | timestamp |                             |
| `recipient_count` | int       |                             |
| `read_count`      | int       |                             |

**Note:** Group message. Audience defined via `broadcast_message_targets`.  
**Indexes:** `(event_id, sent_at)`

---

### `broadcast_message_targets`

> **New polymorphic table (finding #11)**

| Column                 | Type          | Notes                                            |
| ---------------------- | ------------- | ------------------------------------------------ |
| `id`                   | int AI        | PK — integer for join performance                |
| `broadcast_message_id` | ULID FK       | → broadcast_messages                             |
| `target_type`          | enum          | `event\|section\|shift\|crowd_type\|custom_list` |
| `target_id`            | ULID nullable | NULL when `target_type = event` (entire event)   |

**Note:** Multiple targets per message possible.  
**Indexes:** `(broadcast_message_id)`

---

## 3.5.9 Forms, Check-In & Operational

### `public_forms`

| Column                        | Type          | Notes                      |
| ----------------------------- | ------------- | -------------------------- |
| `id`                          | ULID          | PK                         |
| `event_id`                    | ULID FK       | → events                   |
| `name`                        | string        |                            |
| `crowd_type_id`               | ULID FK       | → crowd_types              |
| `fields`                      | JSON          | Form config — not filtered |
| `conditional_logic`           | JSON          | Form config — not filtered |
| `iframe_token`                | ULID unique   |                            |
| `confirmation_email_template` | text nullable |                            |
| `is_active`                   | bool          |                            |

**Indexes:** `(event_id, crowd_type_id, is_active)`

---

### `form_submissions`

| Column           | Type      | Notes                             |
| ---------------- | --------- | --------------------------------- |
| `id`             | ULID      | PK                                |
| `public_form_id` | ULID FK   | → public_forms                    |
| `person_id`      | ULID FK   | → persons                         |
| `data`           | JSON      | Free form results — not queryable |
| `submitted_at`   | timestamp |                                   |

**Indexes:** `(public_form_id, submitted_at)`, `(person_id)`

---

### `check_ins`

| Column               | Type             | Notes       |
| -------------------- | ---------------- | ----------- |
| `id`                 | ULID             | PK          |
| `event_id`           | ULID FK          | → events    |
| `person_id`          | ULID FK          | → persons   |
| `scanned_by_user_id` | ULID FK nullable | → users     |
| `scanner_id`         | ULID FK nullable | → scanners  |
| `scanned_at`         | timestamp        |             |
| `location_id`        | ULID FK nullable | → locations |

**Note:** Immutable audit record — NO soft delete.  
**Indexes:** `(event_id, person_id, scanned_at)`, `(event_id, scanned_at)`

---

### `show_day_absence_alerts`

| Column            | Type               | Notes                                  |
| ----------------- | ------------------ | -------------------------------------- |
| `id`              | ULID               | PK                                     |
| `event_id`        | ULID FK            | → events                               |
| `shift_id`        | ULID FK            | → shifts                               |
| `person_id`       | ULID FK            | → persons                              |
| `alert_sent_at`   | timestamp          |                                        |
| `response_status` | enum               | `no_response\|confirmed\|absent\|late` |
| `resolved_at`     | timestamp nullable |                                        |

**Note:** Immutable audit record — NO soft delete.  
**Indexes:** `(shift_id, response_status)`, `(event_id, alert_sent_at)`

---

### `scanners`

| Column           | Type               | Notes                        |
| ---------------- | ------------------ | ---------------------------- |
| `id`             | ULID               | PK                           |
| `event_id`       | ULID FK            | → events                     |
| `name`           | string             |                              |
| `type`           | enum               | `crowd\|zone\|accreditation` |
| `scope`          | JSON               | Scanner configuration        |
| `pairing_code`   | varchar(8) unique  |                              |
| `last_active_at` | timestamp nullable |                              |

**Indexes:** `(event_id)`, `(pairing_code)`

---

### `inventory_items`

| Column                  | Type               | Notes                         |
| ----------------------- | ------------------ | ----------------------------- |
| `id`                    | ULID               | PK                            |
| `event_id`              | ULID FK            | → events                      |
| `name`                  | string             | e.g. walkie-talkie, vest, key |
| `item_code`             | varchar(50)        |                               |
| `assigned_to_person_id` | ULID FK nullable   | → persons                     |
| `assigned_at`           | timestamp nullable |                               |
| `returned_at`           | timestamp nullable |                               |
| `returned_by_user_id`   | ULID FK nullable   | → users                       |

**Indexes:** `(event_id, assigned_to_person_id)`, `(item_code)`

---

### `event_info_blocks`

| Column         | Type    | Notes                                                      |
| -------------- | ------- | ---------------------------------------------------------- |
| `id`           | ULID    | PK                                                         |
| `event_id`     | ULID FK | → events                                                   |
| `type`         | enum    | `description\|route\|parking\|contacts\|marketing\|custom` |
| `title`        | string  |                                                            |
| `content`      | text    |                                                            |
| `files`        | JSON    | Array of file paths                                        |
| `sort_order`   | int     |                                                            |
| `is_published` | bool    |                                                            |

**Note:** Visibility per crowd_type via `event_info_block_crowd_types`.  
**Indexes:** `(event_id, type, is_published)`

---

### `event_info_block_crowd_types`

> **Finding #8: replaces `visible_to_crowd_types` JSON column**

| Column                | Type    | Notes                             |
| --------------------- | ------- | --------------------------------- |
| `id`                  | int AI  | PK — integer for join performance |
| `event_info_block_id` | ULID FK | → event_info_blocks               |
| `crowd_type_id`       | ULID FK | → crowd_types                     |

**Unique constraint:** `UNIQUE(event_info_block_id, crowd_type_id)`

---

### `production_requests`

> **New table (finding #3: missing table)**

| Column         | Type               | Notes                                                     |
| -------------- | ------------------ | --------------------------------------------------------- |
| `id`           | ULID               | PK                                                        |
| `event_id`     | ULID FK            | → events                                                  |
| `company_id`   | ULID FK            | → companies                                               |
| `title`        | string             |                                                           |
| `status`       | enum               | `draft\|sent\|in_progress\|submitted\|approved\|rejected` |
| `token`        | ULID unique        | Portal access without account                             |
| `sent_at`      | timestamp nullable |                                                           |
| `submitted_at` | timestamp nullable |                                                           |
| `reviewed_by`  | ULID FK nullable   | → users                                                   |
| `reviewed_at`  | timestamp nullable |                                                           |
| `deleted_at`   | timestamp nullable | Soft delete                                               |

**Relations:** `hasMany` material_requests  
**Indexes:** `(event_id, status)`, `(company_id)`  
**Soft delete:** yes

---

### `material_requests`

| Column                  | Type              | Notes                                      |
| ----------------------- | ----------------- | ------------------------------------------ |
| `id`                    | ULID              | PK                                         |
| `production_request_id` | ULID FK           | → production_requests                      |
| `category`              | enum              | `heavy_equipment\|tools\|vehicles\|other`  |
| `name`                  | string            |                                            |
| `description`           | text nullable     |                                            |
| `quantity`              | int               |                                            |
| `period_from`           | datetime nullable |                                            |
| `period_to`             | datetime nullable |                                            |
| `status`                | enum              | `requested\|approved\|rejected\|fulfilled` |
| `notes`                 | text nullable     |                                            |

**Indexes:** `(production_request_id, status)`

---

## 3.5.10 Database Design Rules & Index Strategy

### Rule 1 — ULID as Primary Key

- Business tables: `$table->ulid('id')->primary()` + `HasUlids` trait
- Pure pivot/link tables (no own lifecycle): `$table->id()` (auto-increment integer) for join performance
- Never UUID v4 — avoids InnoDB B-tree fragmentation

---

### Rule 2 — JSON Columns: When Yes, When No

| ✅ Use JSON for                                 | ❌ Never JSON for                     |
| ----------------------------------------------- | ------------------------------------- |
| Opaque config (blocks, fields, settings, items) | Dates/periods                         |
| Toggle-sets (milestone_flags)                   | Status values                         |
| Free-text arrays (top_feedback)                 | Foreign keys                          |
| Unstructured rider data                         | Boolean flags                         |
|                                                 | Anything you filter/sort/aggregate on |

**Replaced in v1.3:**

- `access_zone_days` (was `days` JSON)
- `stage_days` (was `active_days` JSON)
- `broadcast_message_targets` (was `target` JSON)
- `event_info_block_crowd_types` (was `visible_to_crowd_types` JSON)
- `festival_retrospectives` columns (were in `data` JSON blob)

---

### Rule 3 — Soft Delete Strategy

**Soft delete YES:** `organisations`, `events`, `festival_sections`, `shifts`, `shift_assignments`, `persons`, `artists`, `companies`, `production_requests`

**Soft delete NO (immutable audit records):** `check_ins`, `show_day_absence_alerts`, `briefing_sends`, `message_replies`, `audit_log`, `shift_waitlist`, `volunteer_festival_history`

> **Rationale:** Soft deleting audit records creates a false picture of reality.

---

### Rule 4 — Required Indexes (minimum set)

| Table               | Indexes                                                                         |
| ------------------- | ------------------------------------------------------------------------------- |
| `persons`           | `(event_id, crowd_type_id, status)`, `(email, event_id)`, `(user_id, event_id)` |
| `shift_assignments` | `UNIQUE(person_id, time_slot_id)`, `(shift_id, status)`, `(person_id, status)`  |
| `check_ins`         | `(event_id, person_id, scanned_at)`, `(event_id, scanned_at)`                   |
| `briefing_sends`    | `(status, briefing_id)` — queue processing                                      |
| `shift_waitlist`    | `(shift_id, position)`                                                          |
| `performances`      | `(stage_id, date, start_time, end_time)` — B2B overlap detection                |

> Add `EXPLAIN ANALYZE` to queries taking >100ms. Target: all list queries <50ms.

---

### Rule 5 — Multi-Tenancy Scoping

- Every query on event data **MUST** scope on `organisation_id` via Eloquent Global Scope (`OrganisationScope`)
- Use Laravel policies for authorisation: never direct id-checks in controllers
- **Audit log:** Spatie `laravel-activitylog` on: `persons`, `accreditation_assignments`, `shift_assignments`, `check_ins`, `production_requests`