# Crewli — Core Database Schema > Source: Design Document v1.3 — Section 3.5 > **Version: 1.7** — Updated April 2026 > > **Changelog:** > > - v1.3: Original — 12 database review findings incorporated > - v1.4: Competitor analysis amendments (Crescat, WeezCrew, In2Event) > - v1.5: Concept Event Structure review + final decisions > - v1.6: Removed `festival_sections.shift_follows_events` > - v1.7: Festival/Event architecture — universal event model supporting > single events, multi-day festivals, multi-location events, event series > and periodic operations (recurrence). Added `parent_event_id`, > `event_type`, `sub_event_label`, `is_recurring`, `recurrence_rule`, > `recurrence_exceptions` to `events`. Added `event_person_activations` > pivot. Changed `persons.event_id` to reference festival-level event. > Added `event_type_label` for UI terminology customisation. --- ## Primary Key Convention: ULID > **All tables use ULID as primary key — NO UUID v4.** > > - Laravel: `HasUlids` trait > - Migrations: `$table->ulid('id')->primary()` > - External IDs (URLs, barcodes, API): ULID > - Pure pivot tables: 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 | default: Europe/Amsterdam | | `locale` | string | default: nl | | `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` | enum | `trial\|active\|suspended\|cancelled` | | `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 **Unique constraint:** `UNIQUE(user_id, organisation_id)` --- ### `user_invitations` | Column | Type | Notes | | -------------------- | ---------------- | --------------------------------- | | `id` | ULID | PK | | `email` | string | | | `invited_by_user_id` | ULID FK nullable | → users (nullOnDelete) | | `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)` --- ### `events` > **v1.7:** Universal event model supporting all event types: > single events, multi-day festivals, multi-location events, > event series, and periodic operations (schaatsbaan use case). > > **Architecture:** > > - A **flat event** has no parent and no children → behaves as a normal single event > - A **festival/series** has no parent but has children → container level > - A **sub-event** has a `parent_event_id` → operational unit within a festival > - A **single event** = flat event where festival and operational unit are the same > > **UI behaviour:** If an event has no children, all tabs are shown at the > event level (flat mode). Once children are added, the event becomes a > festival container and children get the operational tabs. | Column | Type | Notes | | ----------------------- | ------------------ | ------------------------------------------------------------------------------------------ | | `id` | ULID | PK | | `organisation_id` | ULID FK | → organisations | | `parent_event_id` | ULID FK nullable | **v1.7** → events (nullOnDelete). NULL = top-level event or festival | | `name` | string | | | `slug` | string | | | `start_date` | date | | | `end_date` | date | | | `timezone` | string | default: Europe/Amsterdam | | `status` | enum | `draft\|published\|registration_open\|buildup\|showday\|teardown\|closed` | | `event_type` | enum | **v1.7** `event\|festival\|series` — default: event | | `event_type_label` | string nullable | **v1.7** UI label chosen by organiser: "Festival", "Evenement", "Serie" | | `sub_event_label` | string nullable | **v1.7** How to call children: "Dag", "Programmaonderdeel", "Editie" | | `is_recurring` | bool | **v1.7** default: false. True = generated from recurrence rule | | `recurrence_rule` | string nullable | **v1.7** RRULE (RFC 5545): "FREQ=WEEKLY;BYDAY=SA,SU;UNTIL=20270126" | | `recurrence_exceptions` | JSON nullable | **v1.7** Array of {date, type: cancelled\|modified, overrides: {}}. JSON OK: opaque config | | `deleted_at` | timestamp nullable | Soft delete | **Relations:** - `belongsTo` Organisation - `belongsTo` Event as parent (`parent_event_id`) - `hasMany` Event as children (`parent_event_id`) - `hasMany` FestivalSection, TimeSlot, Artist, Briefing (on sub-event or flat event) - `hasMany` Person (on festival/top-level event) **Indexes:** `(organisation_id, status)`, `(parent_event_id)`, `UNIQUE(organisation_id, slug)` **Soft delete:** yes **Helper scopes (Laravel):** ```php scopeTopLevel() // WHERE parent_event_id IS NULL scopeChildren() // WHERE parent_event_id IS NOT NULL scopeWithChildren() // includes self + all children scopeFestivals() // WHERE event_type IN ('festival', 'series') ``` **Event type behaviour:** | event_type | Has parent? | Description | |---|---|---| | `event` | No | Flat single event — all modules at this level | | `event` | Yes | Sub-event (operational unit within festival) | | `festival` | No | Multi-day festival — children are the days | | `series` | No | Recurring series — children are the editions | > **Recurrence note (BACKLOG ARCH-01):** `recurrence_rule` and > `recurrence_exceptions` are reserved for the future recurrence generator. > For now, sub-events are created manually. The generator will auto-create > sub-events from the RRULE when built. --- ### `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 **Unique constraint:** `UNIQUE(user_id, event_id, role)` --- ## 3.5.2 Locations > Locations are event-scoped and reusable across sections within an event. > Maps/route integration is out of scope for Crewli. ### `locations` | Column | Type | Notes | | --------------------- | ---------------------- | --------------------------------------------------- | | `id` | ULID | PK | | `event_id` | ULID FK | → events | | `name` | string | e.g. "Bar Hardstyle District", "Stage Silent Disco" | | `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` > **v1.5 note:** `route_geojson` removed — maps/routes out of scope. --- ## 3.5.3 Festival Sections, Time Slots & Shifts > **Architecture overview (based on Concept Event Structure review):** > > The planning model has 4 levels: > > 1. **Section** (e.g. Horeca, Backstage, Entertainment) — operational area > 2. **Location** (e.g. Bar Hardstyle District) — physical spot within a section > 3. **Shift** (e.g. "Tapper" at Bar Hardstyle District) — specific role/task at a location in a time window > 4. **Time Slot** — the event-wide time framework that shifts reference > > Each row in the shift planning document = one Shift record. > Multiple shifts at the same location = multiple records with the same `location_id` but different `title`, `actual_start_time`, and `slots_total`. > > **Generic shifts across sections:** Use a shared Time Slot. All sections reference the same Time Slot, ensuring the same time framework. Exceptions use `actual_start_time` override. > > **Cross-event sections** (EHBO, verkeersregelaars): use `type = cross_event`. Shifts in these sections can set `allow_overlap = true`. --- ### `festival_sections` > **v1.5:** Added `type`, 7 Crescat-derived section settings (excl. `shift_follows_events` — removed in v1.6), `crew_need`, and accreditation level columns. | Column | Type | Notes | | --------------------------------- | ------------------ | ----------------------------------------------------------------- | | `id` | ULID | PK | | `event_id` | ULID FK | → events | | `name` | string | e.g. Horeca, Backstage, Overig, Entertainment | | `type` | enum | `standard\|cross_event` — cross_event for EHBO, verkeersregelaars | | `sort_order` | int | default: 0 | | `crew_need` | int nullable | **v1.5** Total crew needed for this section (Crescat: Crew need) | | `crew_auto_accepts` | bool | **v1.5** Crew assignments auto-approved without explicit approval | | `crew_invited_to_events` | bool | **v1.5** Crew automatically gets event invitations | | `added_to_timeline` | bool | **v1.5** Section visible in event timeline overview | | `responder_self_checkin` | bool | **v1.5** Volunteers can self check-in via QR in portal | | `crew_accreditation_level` | string nullable | **v1.5** Default accreditation level for crew (e.g. AAA, AA, A) | | `public_form_accreditation_level` | string nullable | **v1.5** Accreditation level for public form registrants | | `timed_accreditations` | bool | **v1.5** Accreditations are time-limited for this section | | `deleted_at` | timestamp nullable | Soft delete | **Relations:** `hasMany` shifts **Indexes:** `(event_id, sort_order)` **Soft delete:** yes **Default values:** - `type`: standard - `crew_auto_accepts`: false - `crew_invited_to_events`: false - `added_to_timeline`: false - `responder_self_checkin`: true - `timed_accreditations`: false --- ### `time_slots` > Time Slots are defined centrally at event level. All sections reference the same Time Slots. > This naturally handles "generic shifts" — multiple sections referencing one Time Slot share the same time framework. > Per-shift time overrides are handled by `shifts.actual_start_time` / `actual_end_time`. | Column | Type | Notes | | ---------------- | --------------------- | --------------------------------------------------------------------- | | `id` | ULID | PK | | `event_id` | ULID FK | → events | | `name` | string | Descriptive, e.g. "DAY 1 - AVOND - VRIJWILLIGER", "KIDS - OCHTEND" | | `person_type` | enum | `CREW\|VOLUNTEER\|PRESS\|PHOTO\|PARTNER` — controls portal visibility | | `date` | date | | | `start_time` | time | | | `end_time` | time | | | `duration_hours` | decimal(4,2) nullable | | **Relations:** `hasMany` shifts **Indexes:** `(event_id, person_type, date)` --- ### `shifts` > **Architecture note:** > One shift = one role at one location in one time window. > Example from Concept Event Structure — Bar Hardstyle District has 5 shifts: > > - "Barhoofd" (1 slot, 18:30–03:00, report 18:00, is_lead_role = true) > - "Tapper" (2 slots, 19:00–02:30, report 18:30) > - "Frisdrank" (2 slots, 19:00–02:30, report 18:30) > - "Tussenbuffet" (8 slots, 19:00–02:30, report 18:30) > - "Runner" (1 slot, 20:30–02:30, report 20:00) > > v1.4: added title, description, instructions, coordinator_notes, actual_start_time, actual_end_time, end_date, explicit status enum > v1.5: added report_time (aanwezig-tijd), allow_overlap (Overlap Toegestaan), is_lead_role | 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 | | `title` | string nullable | Role/task name, e.g. "Tapper", "Barhoofd", "Stage Manager" | | `description` | text nullable | Brief description of the task | | `instructions` | text nullable | Shown to volunteer after assignment: what to bring, where to report | | `coordinator_notes` | text nullable | Internal only — never visible to volunteers | | `slots_total` | int | | | `slots_open_for_claiming` | int | Slots visible & claimable in volunteer portal | | `is_lead_role` | bool | **v1.5** Marks this as the lead/head role at a location (Barhoofd, Stage Manager, etc.) | | `report_time` | time nullable | **v1.5** "Aanwezig" time — when to arrive. Displayed in briefing and portal. | | `actual_start_time` | time nullable | Overrides `time_slot.start_time` for this shift. NULL = use time_slot time | | `actual_end_time` | time nullable | Overrides `time_slot.end_time` for this shift. NULL = use time_slot time | | `end_date` | date nullable | For multi-day assignments. NULL = single day (via time_slot.date) | | `allow_overlap` | bool | **v1.5** When true: skip UNIQUE(person_id, time_slot_id) conflict check. For Stage Managers covering multiple stages, cross-event sections. | | `events_during_shift` | JSON | Array of performance_ids — opaque reference, no filtering needed | | `status` | enum | `draft\|open\|full\|in_progress\|completed\|cancelled` | | `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 **Status lifecycle:** - `draft` — created but not yet published for claiming - `open` — visible and claimable in portal (respects `slots_open_for_claiming`) - `full` — capacity reached, waitlist only - `in_progress` — shift has started (show day) - `completed` — shift completed - `cancelled` — shift cancelled **Time resolution:** ```php $effectiveReportTime = $shift->report_time; // shown in briefing $effectiveStart = $shift->actual_start_time ?? $shift->timeSlot->start_time; $effectiveEnd = $shift->actual_end_time ?? $shift->timeSlot->end_time; $effectiveDate = $shift->end_date ?? $shift->timeSlot->date; ``` --- ### `shift_assignments` > v1.4: added hours_expected, hours_completed, checked_in_at, checked_out_at > > Conflict detection: UNIQUE(person_id, time_slot_id) is enforced at DB level. > Exception: when `shifts.allow_overlap = true`, the application skips this check before inserting. > The DB constraint remains — use a conditional unique index or handle in application layer. | Column | Type | Notes | | ------------------ | --------------------- | ------------------------------------------------------------ | | `id` | ULID | PK | | `shift_id` | ULID FK | → shifts | | `person_id` | ULID FK | → persons | | `time_slot_id` | ULID FK | Denormalised from shifts — 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 | | | `hours_expected` | decimal(4,2) nullable | Planned hours for this assignment | | `hours_completed` | decimal(4,2) nullable | Actual hours worked — set after shift completion | | `checked_in_at` | timestamp nullable | Shift-level check-in (when reported at section) | | `checked_out_at` | timestamp nullable | When volunteer completed the shift | | `deleted_at` | timestamp nullable | Soft delete | **Unique constraint:** `UNIQUE(person_id, time_slot_id)` — bypassed in application when `shift.allow_overlap = true` **Indexes:** `(shift_id, status)`, `(person_id, status)`, `(person_id, time_slot_id)` **Soft delete:** yes --- ### `shift_check_ins` > Separate from terrain `check_ins`. Records when a volunteer physically reported at their section for duty. > Enables per-shift no-show detection independent of gate access. > When `festival_sections.responder_self_checkin = true`, volunteers trigger this via QR in portal. | Column | Type | Notes | | ----------------------- | ------------------ | ---------------------------------------------- | | `id` | ULID | PK | | `shift_assignment_id` | ULID FK | → shift_assignments | | `person_id` | ULID FK | → persons (denormalised for query performance) | | `shift_id` | ULID FK | → shifts (denormalised for query performance) | | `checked_in_at` | timestamp | | | `checked_out_at` | timestamp nullable | | | `checked_in_by_user_id` | ULID FK nullable | → users — coordinator who confirmed check-in | | `method` | enum | `qr\|manual` | **Note:** Immutable audit record — NO soft delete. **Indexes:** `(shift_assignment_id)`, `(shift_id, checked_in_at)`, `(person_id, checked_in_at)` --- ### `volunteer_availabilities` > v1.4: added preference_level for future auto-matching algorithm. | Column | Type | Notes | | ------------------ | --------- | ---------------------------------------------------------------- | | `id` | ULID | PK | | `person_id` | ULID FK | → persons | | `time_slot_id` | ULID FK | → time_slots | | `preference_level` | tinyint | 1 (low) – 5 (high). Default: 3. Used for auto-matching priority. | | `submitted_at` | timestamp | | **Unique constraint:** `UNIQUE(person_id, time_slot_id)` **Indexes:** `(time_slot_id)` --- ### `shift_absences` | 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` | 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 | | **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)` --- ### `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` | 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 ### `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` > **v1.7:** `event_id` now always references the top-level event (festival or > flat event). For sub-events, persons register at the festival level. > Activation per sub-event is tracked via `event_person_activations` pivot > and/or derived from shift assignments. | 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 — **v1.7** always references top-level event (festival or flat event) | | `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 | **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` | 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)` --- ### `event_person_activations` > **v1.7 New table.** Tracks which sub-events a person is active on, > independent of shift assignments. Used for: > > - Suppliers/crew present at all sub-events without shifts > - Festival-wide crew who need accreditation per day > - Persons manually activated on specific sub-events by coordinator > > For volunteers: activation is derived from shift assignments (no manual entry needed). > For fixed crew and suppliers: use this pivot for explicit activation. | Column | Type | Notes | | ----------- | ------- | --------------------------------- | | `id` | int AI | PK — integer for join performance | | `event_id` | ULID FK | → events (the sub-event) | | `person_id` | ULID FK | → persons | **Unique constraint:** `UNIQUE(event_id, person_id)` **Indexes:** `(person_id)`, `(event_id)` --- ## 3.5.6 Accreditation Engine ### `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 | | --- ### `event_accreditation_items` | 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 | | `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 | | **Indexes:** `(event_id)` --- ### `access_zone_days` | Column | Type | Notes | | ---------------- | ------- | --------------------------------- | | `id` | int AI | PK — integer for join performance | | `access_zone_id` | ULID FK | → access_zones | | `day_date` | date | | **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 ### `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_offer_in` | bool | Default: false | | `milestone_offer_agreed` | bool | Default: false | | `milestone_confirmed` | bool | Default: false | | `milestone_announced` | bool | Default: false | | `milestone_schedule_confirmed` | bool | Default: false | | `milestone_itinerary_sent` | bool | Default: false | | `milestone_advance_sent` | bool | Default: false | | `milestone_advance_received` | bool | Default: false | | `advance_open_from` | datetime nullable | | | `advance_open_to` | datetime nullable | | | `show_advance_share_page` | bool | Default: true | | `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` | **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 | | **Relations:** `hasMany` performances **Indexes:** `(event_id)` --- ### `stage_days` | 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 | | | `submission_status` | enum | `open\|pending\|submitted\|approved\|declined` | | `last_submitted_at` | timestamp nullable | | | `last_submitted_by` | string nullable | | | `submission_diff` | JSON nullable | `{created, updated, untouched, deleted}` counts per submission | **Indexes:** `(artist_id, is_open)`, `(artist_id, submission_status)` --- ### `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 | | | `is_travel_party` | 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 | **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 | | **Indexes:** `(artist_id, datetime)` --- ## 3.5.8 Communication & Briefings ### `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:** No soft delete — audit record. **Indexes:** `(status, briefing_id)`, `(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 | | **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` | | `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:** 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 | | **Indexes:** `(event_id, sent_at)` --- ### `broadcast_message_targets` | 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` | **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 | | `submitted_at` | timestamp | | **Indexes:** `(public_form_id, submitted_at)`, `(person_id)` --- ### `check_ins` > Terrain check-in at access gates. Separate from `shift_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 | | **Indexes:** `(event_id, type, is_published)` --- ### `event_info_block_crowd_types` | 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` | 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: `$table->id()` (auto-increment integer) - Never UUID v4 --- ### Rule 2 — JSON Columns: When Yes, When No | ✅ Use JSON for | ❌ Never JSON for | | ----------------------------------------------- | ------------------------------------- | | Opaque config (blocks, fields, settings, items) | Dates/periods | | Free-text arrays (top_feedback) | Status values | | Unstructured rider data | Foreign keys | | Submission diff snapshots | Boolean flags | | events_during_shift (opaque reference list) | Anything you filter/sort/aggregate on | --- ### 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`, `shift_check_ins`, `show_day_absence_alerts`, `briefing_sends`, `message_replies`, `shift_waitlist`, `volunteer_festival_history` --- ### 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)` | | `shift_check_ins` | `(shift_assignment_id)`, `(shift_id, checked_in_at)`, `(person_id, checked_in_at)` | | `check_ins` | `(event_id, person_id, scanned_at)`, `(event_id, scanned_at)` | | `briefing_sends` | `(status, briefing_id)` | | `shift_waitlist` | `(shift_id, position)` | | `performances` | `(stage_id, date, start_time, end_time)` | | `advance_sections` | `(artist_id, is_open)`, `(artist_id, submission_status)` | --- ### Rule 5 — Multi-Tenancy Scoping - Every query on event data **MUST** scope on `organisation_id` via `OrganisationScope` Eloquent Global Scope - Use Laravel policies — never direct id-checks in controllers - **v1.7:** For festival queries, use `scopeWithChildren()` to include parent + all sub-events - **Audit log:** Spatie `laravel-activitylog` on: `persons`, `accreditation_assignments`, `shift_assignments`, `check_ins`, `production_requests` --- ### Rule 8 — Festival/Event Model (v1.7) ``` Registration level → top-level event (festival or flat event) Operational level → sub-event (child event) Planning level → festival_section + shift A person: - Registers once at festival/top-level event - Is active on 1 or more sub-events - Has shifts within those sub-events Determined by: - Volunteer: via shift assignments (automatic) - Fixed crew: via event_person_activations (manual) - Supplier crew: via event_person_activations (manual) - Artist: always linked to one sub-event Flat event (no children): - All modules at event level - persons.event_id = the event itself - No sub-event navigation shown in UI Festival/series (has children): - persons.event_id = the parent (festival) event - festival_sections, shifts, artists = on child events - UI shows festival overview + child event tabs ``` --- ### Rule 6 — Shift Time Resolution ```php // Effective times — shift overrides take precedence over time slot $reportTime = $shift->report_time; // arrival time (aanwezig) $effectiveStart = $shift->actual_start_time ?? $shift->timeSlot->start_time; $effectiveEnd = $shift->actual_end_time ?? $shift->timeSlot->end_time; $effectiveDate = $shift->end_date ?? $shift->timeSlot->date; ``` --- ### Rule 7 — Overlap / Conflict Detection Default: `UNIQUE(person_id, time_slot_id)` on `shift_assignments` prevents double-booking. Exception: when `shifts.allow_overlap = true`, the **application layer** skips this constraint check before inserting. Use cases: - `festival_sections.type = cross_event` (EHBO, verkeersregelaars) - Stage Managers covering multiple stages simultaneously - Any role explicitly marked as overlap-allowed in the planning document The DB constraint remains as a safety net for all other cases.