crewli/dev-docs/SCHEMA.md

Crewli — Core Database Schema

Source: Design Document v1.3 — Section 3.5
Version: 2.9 — Updated April 2026

Changelog:

• v2.9: WS-6 session 3c — form_submission_action_failure_retry_attempts table added. Per-attempt retry history (timestamp, user, outcome, exception details if failed) replaces the counter-only retry_count tracking on the parent. Parent's retry_count stays as denormalised cache; service layer (FormFailureRetryService) keeps both in sync. canBeRetried() now correctly checks both resolved_at AND dismissed_at (sessie 2 Q2 closure). Also adds exception_trace (longtext nullable) on form_submission_action_failures for the admin-UI triage view. RFC-WS-6.md §3 Q5 addendum.

• v2.8: WS-6 session 3a.5 — companies.kvk_number column added (nullable, indexed). Aligns with the binding-target registry's B2B identity-key candidate. Registry entries renamed/removed in the same session to match real model columns; consistency test extended with model-existence + column-existence assertions. RFC-WS-6.md v1.2 §3 Q9 addendum.

• v2.7: WS-6 session 2.5 — form_schemas.default_crowd_type_id column added (nullable; required at publish time for event_registration via RequiresDefaultCrowdType guard). Replaces the silent oldest() CrowdType heuristic from session 2's PersonProvisioner. RFC-WS-6.md v1.1 §3 Q9 addendum.

• v2.6: WS-5d — form_fields.options and form_field_library.options JSON columns dropped; replaced by a single polymorphic relational table form_field_options (rows owned via owner_type / owner_id, reusing the form_field / form_field_library morph aliases from WS-5a). Row carries value + label + sort_order + translations JSON (per-locale BCP-47 string map). UNIQUE index on (owner_type, owner_id, value) enforces no duplicate values per owner. Submission + template snapshots rewritten in-place to the rich-shape [{value, label, sort_order, translations?}, ...]; the parallel pre-WS-5d translations.{locale}.options[] arrays stripped from both source rows and field-snapshot translation bags — option translations live on the option row now. WS-5 family complete; the follow-up base morph-scope class extraction across the four siblings (FormFieldBindingScope, FormFieldValidationRuleScope, FormFieldConfigScope, FormFieldOptionScope) landed post-WS-5d as FormFieldChildTableMorphScope (abstract base; the four concrete classes are now marker subclasses preserving identity). See ARCH-FORM-BUILDER.md §17.6 and ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §"Uitvoering — base scope-class extractie".

• v2.5: WS-5c — form_fields.conditional_logic JSON column dropped; replaced by a two-table relational tree: form_field_conditional_logic_groups (AND/OR nodes with optional parent_group_id for nesting) + form_field_conditional_logic_conditions (leaves: field_slug + comparison_operator + value JSON). Simple FK to form_fields — per addendum Q3 the library is explicitly out of scope for conditional_logic (no library mirror, no polymorphic morph on these tables). Snapshot + API resource JSON shape preserved byte-for-byte via FormFieldConditionalLogicService::toJsonShape. OrganisationScope FK-chain cap raised from 3 to 5 hops to accommodate the 4-hop conditions chain (condition → group → field → schema → organisation_id column) without denormalising form_field_id onto conditions. See ARCH-FORM-BUILDER.md §8 and ARCH-CONSOLIDATION-ADDENDUM-2026-04-24 §Q3 WS-5c Uitvoering.

• v2.4: WS-5b completion — form_field_configs relational table lands alongside form_field_validation_rules (both from WS-5b). Holds non-validation per-field configuration (tag_categories, storage_disk) that would have polluted the validation-rules table had it stayed there. Same polymorphic-morph pattern (owner aliases form_field / form_field_library, reused from WS-5a). The validation_rules JSON columns on form_fields and form_field_library are dropped by this migration pair — the entire pre-WS-5b bag now lives relationally across two tables. Schema snapshots gain a parallel top-level configs key on each field entry; historical snapshots pre-WS-5b remain immutable with the legacy merged shape. Breaking frontend contract: portal + organizer SPAs switched from reading field.validation_rules.min etc. to the canonical post-WS-5b keys (min_value, max_length, max_selected, etc.) per ARCH v1.6 §17.5 and addendum Q3 WS-5b Uitvoering.

• v2.3: WS-5b (partial) — form_field_validation_rules relational table replaces the validation_rules JSON on form_fields and form_field_library. Typed rule_type column + per-rule parameters JSON; polymorphic morph owner reuses the WS-5a aliases (form_field, form_field_library). Canonicalised legacy keys at backfill: ambiguous min/max → min_value/max_value/min_length/max_length/ date_min/date_max by field type; max_priorities → max_selected. Skipped keys: required (is_required column), unique (is_unique column). Non-validation keys (tag_categories, storage_disk) deferred to WS-5b commit 5 — they relocate to a separate form_field_configs table (ARCH §17.5) rather than polluting the validation-rules table. See ARCH-FORM-BUILDER.md §17.4 and ARCH-CONSOLIDATION-ADDENDUM-2026-04-24 §Q3 WS-5b Uitvoering.

• v2.2: WS-5a — form_field_bindings relational table replaces form_fields.binding and form_field_library.default_binding JSON. Polymorphic morph owner (form_field / form_field_library) per ARCH-CONSOLIDATION-ADDENDUM-2026-04-24 §Q3. See ARCH-FORM-BUILDER.md §6.7 for the relational table and §6.3 for the binding-row specification.

• 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.

• v1.8: Registration Form Fields module — EAV system for dynamic event-specific registration fields, replacing queryable use of persons.custom_fields JSON. Added tables: registration_form_fields, person_field_values, person_section_preferences. Added columns: persons.remarks, persons.date_of_birth, events.registration_show_section_preferences, events.registration_show_availability. Added TAG_PICKER field type for tag selection during registration with deferred sync via TagSyncService. Removed minimum volunteer hours threshold concept. Removed hardcoded motivation form step. Moved payment status from fixed admin field to dynamic registration field.

• v2.1: Public Form Builder API completion (S2c). Added columns on form_submissions: identity_match_status (null|pending|matched|none, populated by TriggerPersonIdentityMatchOnFormSubmit per ARCH §31.1) and schema_version_at_open (stamped at draft-create for drift detection). Replaced the composite index on (form_schema_id, idempotency_key) with a UNIQUE constraint so the DB is the race-safe backstop behind application-level idempotency replay. Full public API contract: /dev-docs/ARCH-FORM-BUILDER.md §10.4.

• v2.0: Universal Form Builder replaces event-scoped registration EAV (S1 + S2a + S2b landed). Full architecture: /dev-docs/ARCH-FORM-BUILDER.md v1.2. Added tables (13): user_profiles, form_schemas, form_schema_sections, form_field_library, form_fields, form_submissions, form_submission_section_statuses, form_submission_delegations, form_values, form_value_options, form_templates, form_schema_webhooks, form_webhook_deliveries. Dropped tables (3): registration_form_fields, person_field_values, registration_field_templates. Preserved: person_section_preferences — still the target for the Form Builder's SECTION_PRIORITY field type (ARCH §31.3). Added column: organisations.default_locale (default 'nl') — last link in the FormLocaleResolver fallback chain (ARCH §16.2). Dropped columns on events: registration_show_section_preferences, registration_show_availability (now expressed as form_fields.is_portal_visible / conditional_logic). Renamed concept: volunteer_profiles (planning placeholder, never physically created) is retired; user-universal columns live on the new user_profiles table and event-variable / skill columns moved to form_fields + person_tags. The §3.5.9 form_submissions stub (tied to the never-created public_forms concept) is retired in favour of the Form Builder form_submissions table described in §3.5.12.

---

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
2. 3.5.1a Multi-Factor Authentication
3. 3.5.2 Locations
4. 3.5.3 Festival Sections, Time Slots & Shifts
5. 3.5.4 Volunteer Profile & History
6. 3.5.5 Crowd Types, Persons & Crowd Lists
7. 3.5.5a Person Tags & Skills
8. 3.5.5b Section Preferences
9. 3.5.5c Person Identity Matching
10. 3.5.6 Accreditation Engine
11. 3.5.7 Artists & Advancing
12. 3.5.8 Communication & Briefings
13. 3.5.9 Check-In & Operational
14. 3.5.10 Email Infrastructure
15. 3.5.11 Database Design Rules & Index Strategy
16. 3.5.12 Form Builder

---

3.5.1 Foundation

users

Column	Type	Notes
id	ULID	PK, HasUlids trait
first_name	string
last_name	string
email	string	unique
mfa_enabled	boolean	default: false
mfa_method	string(20) nullable	totp or email
mfa_secret	text nullable	encrypted TOTP secret
mfa_confirmed_at	timestamp nullable	null = setup not yet verified
mfa_enforced	boolean	default: false — forced by policy or admin
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), hasMany mfa_backup_codes, hasMany mfa_email_codes, hasMany trusted_devices
Soft delete: yes

---

organisations

Column	Type	Notes
id	ULID	PK
name	string
slug	string	unique
billing_status	enum	trial|active|suspended|cancelled
default_locale	string(10)	v2.0 default: 'nl'. Fallback for FormLocaleResolver chain (ARCH §16.2) when users.locale and form_schemas.locale are absent.
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	ULID	PK (addendum Q1). App\Models\Pivots\OrganisationUser generates via HasUlids.
user_id	ULID FK	→ users
organisation_id	ULID FK	→ organisations
role	string	Spatie role via pivot

Type: Pure pivot, ULID 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

v2.0 — removed: registration_show_section_preferences and registration_show_availability are gone; the equivalent behaviour now lives on form_fields.is_portal_visible plus per-field conditional_logic in the Form Builder (§3.5.12).

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

Status state machine:

draft → published → registration_open → buildup → showday → teardown → closed
             ↑              ↓                ↑
             └── (back) ────┘                │
                                  (back) ────┘

Allowed transitions:

From	To
draft	published
published	registration_open, draft
registration_open	buildup, published
buildup	showday
showday	teardown
teardown	closed
closed	(terminal — no transitions)

Prerequisites:

• → published: name, start_date, and end_date must be set
• → registration_open: at least one time slot and one section must exist

Festival cascade: When a festival parent transitions to showday, teardown, or closed, all children in an earlier status are automatically updated to the same status. Earlier statuses (draft → published) do NOT cascade.

Helper scopes (Laravel):

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	ULID	PK (addendum Q1). App\Models\Pivots\EventUserRole.
user_id	ULID FK	→ users
event_id	ULID FK	→ events
role	string

Type: Pure pivot, ULID PK
Unique constraint: UNIQUE(user_id, event_id, role)

---

email_change_requests

Column	Type	Notes
id	ULID PK
user_id	ULID FK	→ users (cascade delete)
current_email	string	Email at time of request
new_email	string	Requested new email
token	string	SHA-256 hashed verification token
requested_by_user_id	ULID FK null	→ users (null on delete) — self or admin
status	string	pending / verified / expired / cancelled
expires_at	timestamp	24h from request
verified_at	timestamp?	When verification completed
created_at	timestamp
updated_at	timestamp

Indexes: (user_id, status), (token)

---

3.5.1a Multi-Factor Authentication

MFA tables supporting TOTP, email codes, backup codes, and trusted devices. See /dev-docs/AUTH_ARCHITECTURE.md section 9 for full architecture.

mfa_backup_codes

Column	Type	Notes
id	ULID	PK (addendum Q1)
user_id	ULID	FK → users
code_hash	string(64)	bcrypt hash of code
used	boolean	default: false
used_at	timestamp nullable
created_at	timestamp
updated_at	timestamp

Relations: belongsTo User
Indexes: (user_id, used)
Soft delete: no (audit record)

---

mfa_email_codes

Column	Type	Notes
id	ULID	PK (addendum Q1)
user_id	ULID	FK → users
code	string(6)	6-digit numeric code
expires_at	timestamp	10 min from creation
used	boolean	default: false
created_at	timestamp
updated_at	timestamp

Relations: belongsTo User
Indexes: (user_id, code, used, expires_at)
Soft delete: no (audit record)

---

trusted_devices

Column	Type	Notes
id	ULID	PK
user_id	ULID	FK → users
device_hash	string(64)	SHA-256 of fingerprint+user_id
device_name	string nullable	e.g. "Chrome on macOS"
ip_address	string(45)	IPv4 or IPv6
trusted_until	timestamp	30 days from creation
last_used_at	timestamp nullable
created_at	timestamp
updated_at	timestamp

Relations: belongsTo User
Indexes: (user_id, device_hash, trusted_until)
Soft delete: no

---

impersonation_sessions

Column	Type	Notes
id	ULID	PK
admin_id	ULID FK	→ users
target_user_id	ULID FK	→ users
reason	string	Admin-provided reason
mfa_method	string(20)	totp, email, or backup_code
ip_address	string(45)	Admin's IP at start
user_agent	text nullable	Admin's user agent
started_at	timestamp
ended_at	timestamp nullable	NULL = still active
expires_at	timestamp	Sliding 60-min TTL
end_reason	string(50) nullable	manual, expired, ip_changed, admin_kill_all
actions_count	unsigned int	API requests made during session

Relations: belongsTo User (admin), belongsTo User (target)
Indexes: (admin_id, ended_at), (target_user_id, ended_at), (started_at)
Soft delete: no — immutable audit table

---

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
category	string nullable	v1.8 Free-text grouping label (e.g. Bar, Podium, Operationeel)
icon	string nullable	v1.8 Tabler icon name (e.g. tabler-beer, tabler-microphone-2)
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
show_in_registration	bool	v1.8 Show this section in the volunteer registration form
registration_description	text nullable	v1.8 Description shown to volunteers in the registration form
deleted_at	timestamp nullable	Soft delete

Relations: hasMany shifts
Indexes: (event_id, sort_order), (event_id, category)
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
• show_in_registration: false

Note: "Overkoepelende" sections (shared across all sub-events of a festival) are identified by type = 'cross_event'. There is no separate is_shared boolean column — the type enum distinguishes standard sections from cross-event sections.

Festival context: On a festival parent event, standard type sections are festival-only operational sections (e.g. Terreinploeg, Bouwploeg). cross_event sections appear in all sub-events (e.g. EHBO, Security, Backstage). On sub-events, all sections are standard (program-specific). When querying sections for a sub-event, the API automatically includes cross_event sections from the parent festival.

---

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.

Festival context: Time slots on a festival parent event are for operational scheduling (build-up, teardown, transitions). Time slots on sub-events are for program-specific scheduling. When querying time slots for a sub-event with ?include_parent=true, the parent festival's time slots are included (marked with source='festival') so that local sections can create build-up, teardown, and transition shifts. Without this parameter, only the sub-event's own time slots are returned. Festival-level queries never include sub-event time slots. The Event::getAllRelevantTimeSlots() method can retrieve time slots across levels for planning purposes.

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:

$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
cancelled_by	ULID FK nullable	→ users (who performed the cancellation)
cancellation_source	enum nullable	organiser|volunteer|system
cancelled_at	timestamp 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

v2.0 — volunteer_profiles retired. The old table was a planning placeholder that was never physically created. User-universal columns (bio, photo, emergency contact, reliability_score, is_ambassador) live on the new user_profiles table (§3.5.12). Event-variable / skill-like columns moved to form_fields (per-event, via the Form Builder) and person_tags. See §3.5.12 for the new table + the column-by-column crosswalk.

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
first_name	string
last_name	string
date_of_birth	date nullable
email	string	Indexed deduplication key
phone	string nullable
status	enum	invited|applied|pending|approved|rejected|no_show
is_blacklisted	bool
admin_notes	text nullable	Organiser-only notes
remarks	text nullable	v1.8 Volunteer-editable notes (distinct from admin_notes which is organiser-only)
custom_fields	JSON	Backward compat + truly opaque event-specific data. For queryable registration data, use the Form Builder (form_values via form_fields — see §3.5.12).
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
kvk_number	string nullable	KvK registration number (Dutch Chamber of Commerce). Indexed. v2.8 — WS-6 sessie 3a.5
contact_first_name	string nullable
contact_last_name	string nullable
contact_email	string nullable
contact_phone	string nullable
deleted_at	timestamp nullable	Soft delete

Indexes: (organisation_id), (kvk_number)
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	ULID	PK (addendum Q1). App\Models\Pivots\CrowdListPerson.
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	ULID	PK (addendum Q1). App\Models\Pivots\EventPersonActivation.
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.5c Person Identity Matching

v1.8+: Enterprise-grade identity resolution with three steps: detect → suggest → confirm. No silent auto-linking. Supports email matching (HIGH confidence), fuzzy name matching (MEDIUM confidence, upgradable to HIGH with DOB match), manual linking, and revert/unlink. PersonObserver triggers detection automatically on Person create/update.

person_identity_matches

Column	Type	Notes
id	ULID	PK — HasUlids trait. Entity with its own lifecycle, not a pure pivot
person_id	ULID FK	→ persons. constrained()->cascadeOnDelete()
matched_user_id	ULID FK	→ users. Named matched_user_id (not user_id) to avoid confusion with persons.user_id. constrained()->cascadeOnDelete()
matched_on	string	Enum: email|name_fuzzy|manual (IdentityMatchMethod)
confidence	string	Enum: high|medium (IdentityMatchConfidence). high = exact email or fuzzy+DOB, medium = fuzzy name only
status	string	Enum: pending|confirmed|dismissed|reverted (IdentityMatchStatus), default pending
match_details	JSON nullable	Snapshot of matched fields, emails, names, DOB at detection time
confirmed_by_user_id	ULID FK nullable	→ users (who confirmed). constrained()->nullOnDelete()
confirmed_at	timestamp nullable	When the match was confirmed
dismissed_by_user_id	ULID FK nullable	→ users (who dismissed). constrained()->nullOnDelete()
dismissed_at	timestamp nullable	When the match was dismissed
reverted_by_user_id	ULID FK nullable	→ users (who reverted/unlinked). constrained()->nullOnDelete()
reverted_at	timestamp nullable	When a confirmed match was reverted
resolved_by_user_id	ULID FK nullable	→ users (legacy, set on confirm/dismiss). constrained()->nullOnDelete()
resolved_at	timestamp nullable	When the match was resolved (legacy)
created_at	timestamp

Design notes:

• No updated_at: status transitions tracked via specific *_at columns. Model sets const UPDATED_AT = null;.
• Specific confirmed_by/dismissed_by/reverted_by columns track each action separately, enabling a match lifecycle of: pending → confirmed → reverted.
• resolved_by/resolved_at retained for backward compatibility (set on confirm/dismiss).
• Detection strategies: (1) Exact email within org → HIGH, (2) Fuzzy name (Levenshtein ≤2/3) → MEDIUM, (3) Fuzzy name + DOB match → HIGH.

Unique constraint: UNIQUE(person_id, matched_user_id) — prevent duplicate match records
Indexes: (person_id, status), (matched_user_id, status), (status)
Foreign keys: person_id → persons (cascade delete), matched_user_id → users (cascade delete), all *_user_id → users (null on delete)

users.date_of_birth

Column	Type	Notes
date_of_birth	date nullable	Added after last_name. Used as DOB tiebreaker for fuzzy name matching

---

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

Authoritative spec: RFC-TIMETABLE v0.2 §5.3. The pre-v0.2 schema (event-scoped artists, milestone bool flags, artist_riders, itinerary_items) was replaced in 2026-05 — see dev-docs/RFC-TIMETABLE-Artist-Timetable-Module.md for the design decisions (D9, D10, D13, D14, D17, D23, D24, D26).

The artist domain splits into a master record (artists, org-scoped) and a per-event booking (artist_engagements). The split allows one artist to have multiple engagements across events, each with its own deal, advance trajectory, and timetable performances.

genres

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations
name	string(40)
color	string(7) nullable	hex
sort_order	int default 0
is_active	bool default true	use instead of soft delete
created_at, updated_at	timestamps

Unique: UNIQUE(organisation_id, name) Soft delete: no — is_active=false is the retire mechanism (D24) Scope: OrganisationScope (direct organisation_id)

---

artists (master)

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations
name	string(120)
slug	string(120)	unique per organisation, generated from name
default_genre_id	ULID FK nullable	→ genres (D24)
default_draw	int nullable	for capacity-warn defaults
star_rating	tinyint nullable	1–5
home_base_country	string(2) nullable	ISO 3166-1 alpha-2
agent_company_id	ULID FK nullable	→ companies (typically type=agency)
notes	text nullable
created_at, updated_at, deleted_at		soft delete

Unique: UNIQUE(organisation_id, slug) Indexes: (organisation_id, name), (default_genre_id), (agent_company_id) Soft delete: yes (RFC §5.4) Scope: OrganisationScope (direct organisation_id)

---

companies.handles_buma (added column)

The existing companies table (§3.5.5) gains a single bool column:

Column	Type	Notes
handles_buma	bool default false	Whether an agency reports BUMA on the artist's behalf (RFC D26)

The existing type enum is unchanged — the agency value is reused for booking agencies in the artist domain.

---

artist_contacts (master-scoped)

Column	Type	Notes
id	ULID	PK
artist_id	ULID FK	→ artists (master)
name	string(120)
email	string nullable
phone	string nullable
role	string(60)	tour_manager, agent, manager…
is_primary	bool default false
receives_briefing	bool default false
receives_infosheet	bool default false
created_at, updated_at	timestamps

Indexes: (artist_id, role) Soft delete: no Scope: OrganisationScope (FK-chain via artist_id → artists.organisation_id)

---

stages

Column	Type	Notes
id	ULID	PK
event_id	ULID FK	→ events (festival or flat event)
name	string(120)
color	string(7)	hex
capacity	int nullable	drives capacity-warn (RFC D23)
sort_order	int default 0	(RFC D23)
created_at, updated_at	timestamps

Unique: UNIQUE(event_id, name) Indexes: (event_id, sort_order) Soft delete: no — destructive deletion is rare; query complexity isn't worth the safety win Scope: OrganisationScope (FK-chain via event_id → events.organisation_id)

---

stage_days

Column	Type	Notes
id	int AI	PK — integer for join performance
stage_id	ULID FK	→ stages
event_id	ULID FK	→ events (sub-event or flat event = "show host")

Unique: UNIQUE(stage_id, event_id) Indexes: (event_id) Soft delete: no (pure pivot) Scope: OrganisationScope (FK-chain via stage)

For a festival, each stage has multiple stage_days rows (one per active sub-event). For a flat event, each stage has exactly one row referencing the event itself.

---

artist_engagements (per-event booking)

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations (denormalised — observer-maintained)
artist_id	ULID FK	→ artists (master)
event_id	ULID FK	→ events (festival or flat event)
booking_status	string	RFC D9 — see App\Enums\Artist\ArtistEngagementStatus
project_leader_id	ULID FK nullable	→ users
fee_amount	decimal(10,2) nullable
fee_currency	string(3) default 'EUR'
fee_type	string nullable	App\Enums\Artist\FeeType
buma_applicable	bool default true
buma_percentage	decimal(5,2) default 7.00
buma_handled_by	string default 'organisation'	App\Enums\Artist\BumaHandledBy (D26)
vat_applicable	bool default true
vat_percentage	decimal(5,2) default 21.00
deal_breakdown	JSON nullable	opaque line-items
deposit_percentage	decimal(5,2) nullable
deposit_due_date	date nullable
balance_due_date	date nullable
payment_status	string default 'none'	App\Enums\Artist\PaymentStatus
crew_count	int default 0
guests_count	int default 0
requested_at	datetime nullable	set when status → Requested
option_expires_at	datetime nullable	required when status=Option; demote-job uses this
advance_open_from	datetime nullable
advance_open_to	datetime nullable
portal_token	ULID unique nullable	tour-manager portal access
advancing_completed_count	int default 0	observer-maintained (Session 3)
advancing_total_count	int default 0	observer-maintained (Session 3)
notes	text nullable
created_at, updated_at, deleted_at		soft delete

Unique: UNIQUE(artist_id, event_id), UNIQUE(portal_token) Indexes: (organisation_id), (event_id, booking_status), (option_expires_at) Soft delete: yes (cascades to performances, advance_sections via ArtistEngagementObserver) Scope: OrganisationScope (direct organisation_id) Observers:

• creating: auto-fills organisation_id from artist, asserts artist.organisation_id === event.organisation_id (cross-tenant guard — CrossTenantEngagementException)
• deleted: cascade soft-deletes performances, hard-deletes advance_sections. advance_submissions rows are immutable audit records and remain attached.

---

performances

Column	Type	Notes
id	ULID	PK
engagement_id	ULID FK	→ artist_engagements
event_id	ULID FK	→ events (sub-event or flat event = "show host")
stage_id	ULID FK nullable	→ stages; NULL = parked / wachtrij (D13)
lane	unsigned tinyint default 0	(D13)
start_at	datetime	⩾ event.start, < event.end
end_at	datetime	> start_at, ⩽ event.end
version	int default 0	optimistic-lock counter — observer-maintained (D14)
notes	text nullable
created_at, updated_at, deleted_at		soft delete

Indexes: (event_id, stage_id, start_at, end_at), (engagement_id), (stage_id, start_at) (lane resolver) Soft delete: yes (cascade with engagement via observer) Scope: OrganisationScope (FK-chain via engagement_id → artist_engagements.organisation_id) Observer (PerformanceObserver): increments version by 1 on every UPDATE (D14 optimistic lock — MoveTimetablePerformanceRequest in Session 2 compares against client-supplied version).

---

advance_sections (engagement-scoped)

Column	Type	Notes
id	ULID	PK
engagement_id	ULID FK	→ artist_engagements (was artist_id in the pre-v0.2 plan)
name	string(80)
type	string	App\Enums\Artist\AdvanceSectionType
is_open	bool default false
open_from	datetime nullable
open_to	datetime nullable
sort_order	int default 0
submission_status	string default 'open'	App\Enums\Artist\AdvanceSectionSubmissionStatus
last_submitted_at	timestamp nullable
last_submitted_by	string nullable	tour-manager name from form
submission_diff	JSON nullable	{created, updated, untouched, deleted} counts per submission
created_at, updated_at	timestamps

Indexes: (engagement_id, is_open), (engagement_id, submission_status) Soft delete: no — hard-deleted with the parent engagement (RFC §5.4) Scope: OrganisationScope (FK-chain via engagement)

The shift from artist_id (master) to engagement_id (per-event) is the schema correction that allows multi-event artists to advance each engagement independently. The form_submission subject remains the master Artist (per PurposeRegistry §17.3) — see ARCH-FORM-BUILDER.md §3.2.5 for the resolver wiring.

---

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	string default 'pending'	App\Enums\Artist\AdvanceSubmissionStatus
reviewed_by	ULID FK nullable	→ users
reviewed_at	timestamp nullable
data	JSON	free-form payload — not queryable
created_at, updated_at	timestamps

Indexes: (advance_section_id, status) Soft delete: no — audit-immutable (RFC §5.4). Survive engagement deletion via FK; no application code mutates these rows after creation. Scope: OrganisationScope (FK-chain via section → engagement)

---

Enums (under App\Enums\Artist\)

Enum	Values	Notes
ArtistEngagementStatus	draft, requested, option, offered, confirmed, contracted, cancelled, rejected, declined	RFC D9 — Dutch labels via label()
BumaHandledBy	organisation, booking_agency, not_applicable	RFC D26
FeeType	flat, door_split, guarantee_plus_split
PaymentStatus	none, deposit_paid, paid_in_full
AdvanceSectionType	guest_list, contacts, production, custom
AdvanceSectionSubmissionStatus	open, pending, submitted, approved, declined
AdvanceSubmissionStatus	pending, accepted, declined

Riders + itineraries: the previous §3.5.7 plan included artist_riders and itinerary_items tables. RFC v0.2 §5.3 does NOT create them; rider data lives in advance-section submissions (free-form JSON), and itineraries are deferred to a future RFC.

---

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 Check-In & Operational

v2.0 — moved: the former public_forms + stub form_submissions entries here described a never-created iframe-embed feature (FORM-01 in BACKLOG). The Form Builder (§3.5.12) now owns every form concept: form_schemas with public_token handle public-facing forms, form_submissions stores results. If the iframe-embed requirement returns it will layer on top of those tables rather than ship a new public_forms table.

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.5a Person Tags & Skills

Tag-based skills/competencies system for volunteers and crew. Tags are defined per organisation, assigned to users at the organisation level (persistent across events), and come from two sources: self-reported by volunteers during registration, or assigned by organisers based on experience.

person_tags

Column	Type	Notes
id	ULID	PK, HasUlids trait
organisation_id	ULID FK	→ organisations
name	string(50)	e.g. "Tapper", "EHBO", "Duits"
category	string(50) null	e.g. "Vaardigheid", "Taal", "Certificaat"
icon	string(50) null	Tabler icon name
color	string(7) null	Hex color
is_active	bool	default: true
sort_order	int	default: 0
created_at	timestamp
updated_at	timestamp

Relations: belongsTo Organisation Indexes: (organisation_id, is_active, sort_order) Unique constraint: UNIQUE(organisation_id, name) No soft deletes — tags are deactivated via is_active = false

---

user_organisation_tags

Tag assignments linking a user to a tag within an organisation. Persistent across events — tags live on user+org level, not event level. Users without a user_id (external guests, artists) cannot have tags.

Column	Type	Notes
id	ULID	PK (addendum Q1)
user_id	ULID FK	→ users
organisation_id	ULID FK	→ organisations
person_tag_id	ULID FK	→ person_tags
source	enum	self_reported|organiser_assigned
assigned_by_user_id	ULID FK null	→ users (who assigned, for organiser_assigned)
proficiency	enum null	beginner|experienced|expert
notes	text null	Organiser-only notes, never shown to volunteer
assigned_at	timestamp

Relations: belongsTo User, Organisation, PersonTag, AssignedBy (→ User) Unique constraint: UNIQUE(user_id, organisation_id, person_tag_id, source) — allows both self_reported AND organiser_assigned for the same tag Indexes: (user_id, organisation_id), (person_tag_id), (organisation_id, person_tag_id, proficiency)

Design notes:

• Tags are scoped to organisation_id — organisation A's "Tapper" tag is independent of organisation B's.
• Tags link to user_id, NOT person_id — this makes them persistent across events.
• The unique constraint includes source — a volunteer can self-report "Tapper" AND the organiser can independently assign "Tapper expert". Both coexist.
• Sync behaviour: The PUT .../tags/sync endpoint replaces tags of the specified source only. Syncing self_reported tags removes self_reported tags not in the new list and adds new ones, while leaving all organiser_assigned tags untouched.

---

3.5.5b Section Preferences

Dynamic registration fields moved to Form Builder — see §3.5.10 (renumbered to §3.5.12 in v2.0). The legacy registration_form_fields, person_field_values, and registration_field_templates tables were dropped in S2a.

person_section_preferences is retained — it remains the integration target for the Form Builder's SECTION_PRIORITY field type (ARCH §31.3).

person_section_preferences

Volunteer's preferred sections for shift assignment. Soft hints for the organiser, NOT promises. The organiser retains full flexibility to assign anyone anywhere. Captured by the Form Builder's SECTION_PRIORITY field type on an event_registration schema; the listener per ARCH §31.3 upserts rows here on submit.

Column	Type	Notes
id	ULID	PK (addendum Q1)
person_id	ULID FK	→ persons
festival_section_id	ULID FK	→ festival_sections
priority	tinyint	1 (first choice) – 5

Unique constraint: UNIQUE(person_id, festival_section_id) Indexes: (festival_section_id, priority), (person_id)

Design notes:

• Priority is a ranking, not a score. 1 = first choice.
• Accompanying text in form: "We proberen hier zoveel mogelijk rekening mee te houden, maar de uiteindelijke indeling wordt bepaald door de organisatie."
• For festivals: shown sections = sub-event sections + parent's cross_event sections.
• Whether the step renders is driven by the form schema — form_fields.is_portal_visible
  • conditional_logic — not by a column on events.

---

3.5.11 Database Design Rules & Index Strategy

Rule 1 — ULID as Primary Key

Business tables AND pure pivot tables: $table->ulid('id')->primary() + (on modelled tables) the HasUlids trait. Never UUID v4.

Legacy note: before 2026-04-24 pure pivots used auto-increment integer PKs; this exception was retired by ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md Q1. For pivots consumed via Eloquent belongsToMany (organisation_user, event_user_roles, crowd_list_persons, event_person_activations), a minimal Pivot subclass under App\Models\Pivots\* carries HasUlids so attach() auto-generates the key — the addendum's "no model required" phrasing refers to domain modelling, not Eloquent plumbing.

---

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)
person_section_preferences	UNIQUE(person_id, festival_section_id), (festival_section_id, priority)

---

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
• v2.2 (addendum Q2): OrganisationScope supports a declarative tenantScopeStrategy() convention on the model for FK-chain scoping:

  public static function tenantScopeStrategy(): array
  {
      return ['column' => 'organisation_id'];            // direct
      // OR
      return ['via' => FormSchema::class, 'fk' => 'form_schema_id']; // chain
  }
  

  The resolver walks parents recursively (max 3 hops — TenantScopeResolutionException beyond that) and accepts parents using either tenantScopeStrategy() or the legacy $organisationScopeColumn property. Link: /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q2.
• v2.2 (D-03 + D-04 subset): fourteen new registrations — nine form-builder child models (FormSchemaSection, FormField, FormValue, FormValueOption, FormSubmission, FormSubmissionSectionStatus, FormSubmissionDelegation, FormSchemaWebhook, FormWebhookDelivery) + five event-data models (ShiftAssignment, ShiftWaitlist, VolunteerAvailability, PersonSectionPreference, PersonIdentityMatch). See ARCH-FORM-BUILDER.md §4.14 for the chain shapes. The D-04 user/admin subset (MFA / TrustedDevice / UserProfile / EmailLog / OrganisationEmailSettings) is per-model backlog, not WS-4 scope.

---

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

// 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.

---

3.5.10 Email Infrastructure

organisation_email_settings

Per-organisation email branding configuration. One-to-one with organisations.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations, UNIQUE, CASCADE DELETE
logo_url	string(500) nullable	Logo URL for email header
primary_color	string(7)	Hex color, default #6366F1
secondary_color	string(7)	Hex color, default #4F46E5
footer_text	string(200) nullable	Custom footer text
reply_to_email	string nullable	Override reply-to per org
reply_to_name	string(100) nullable	Reply-to display name

Relations: belongsTo organisation
Soft delete: no

---

organisation_email_templates

Per-organisation, per-type email text overrides. When no override exists, system defaults from EmailTemplateType enum are used.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations, CASCADE DELETE
type	string(50)	EmailTemplateType enum value
subject	string(200)	Custom subject line
heading	string(200) nullable	Custom heading in email body
body_text	text	Custom body text (supports {variable} placeholders)
button_text	string(100) nullable	Custom CTA button label

Unique constraint: UNIQUE(organisation_id, type)
Relations: belongsTo organisation
Soft delete: no

Template types: invitation, password_reset, email_verification, registration_approved, registration_rejected, shift_assignment

---

email_logs

Immutable audit record of every email sent. No soft deletes.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK nullable	→ organisations, NULL ON DELETE
event_id	ULID FK nullable	→ events, NULL ON DELETE
person_id	ULID nullable	Person context if applicable
user_id	ULID nullable	User context if applicable
recipient_email	string
recipient_name	string nullable
mailable_class	string	e.g. App\Mail\TransactionalMail
template_type	string(50)	EmailTemplateType enum value
subject	string	Resolved subject (after variable substitution)
status	string(20)	queued|sent|failed
error_message	text nullable	Failure reason
queued_at	timestamp
sent_at	timestamp nullable
failed_at	timestamp nullable
triggered_by_user_id	ULID nullable	Who triggered the email

Indexes: (organisation_id, created_at), (recipient_email, created_at), (template_type, status), (event_id), (person_id)
Relations: belongsTo organisation (nullable), event (nullable), person (nullable), user (nullable), triggeredBy → user
Soft delete: no — immutable audit table

---

3.5.12 Form Builder

Universal form builder — one schema/field/submission/value stack serving the seven v1.0 FormPurpose variants (event_registration, artist_advance, supplier_intake, post_event_evaluation, incident_report, signature_contract, user_profile) registered in config/form_builder/purposes.php via PurposeRegistry. See /dev-docs/ARCH-FORM-BUILDER.md v1.3 for the authoritative behaviour spec; this section documents the physical tables as landed through S1 + S2a + S2b + WS-2. Where ARCH §4 and the migrations disagree, the migrations win (document code-as-built).

Legacy tables dropped (S2a): registration_form_fields, person_field_values, registration_field_templates removed by 2026_04_20_100000_drop_remaining_legacy_registration_tables. That migration's down() is a hard failure — restoration requires migrate:fresh or a pre-S2a backup; environments with real legacy data must run forms:migrate-legacy-data BEFORE applying the drop.

Crosswalk: legacy volunteer_profiles (never physically created) → new locations

Legacy column	New location
bio	user_profiles.bio
photo_url	user_profiles.photo_url
tshirt_size	form_fields (Pattern B, per event)
first_aid	person_tags (system-seeded, S3)
driving_licence	person_tags (system-seeded, S3)
allergies	form_fields (Pattern B, per event)
access_requirements	form_fields (Pattern B, per event)
emergency_contact_name	user_profiles.emergency_contact_name
emergency_contact_phone	user_profiles.emergency_contact_phone
reliability_score	user_profiles.reliability_score
is_ambassador	user_profiles.is_ambassador

---

user_profiles

User-universal profile — replaces the never-created volunteer_profiles placeholder. Holds bio, photo, emergency contact details, reliability score, ambassador flag, and an opaque UI/ notification settings JSON. 1:1 with users; auto-created by UserObserver on user creation and backfilled for existing users via 2026_04_19_100001_populate_user_profiles_from_existing_users. Cascade-deleted with the user — no independent soft delete. ARCH §4.13.

Column	Type	Notes
id	ULID	PK, HasUlids trait
user_id	ULID FK	→ users, cascade delete, UNIQUE (1:1)
bio	text nullable
photo_url	string nullable
emergency_contact_name	string nullable	PII
emergency_contact_phone	string nullable	PII
reliability_score	decimal(3,2)	default: 0.00. System-computed; not mass-assignable
is_ambassador	bool	default: false. System-awarded; not mass-assignable
settings	JSON nullable	Whitelisted keys only (ui.*, notifications.*); enforced by UserProfileSettingsValidator (ARCH §4.13.1)
created_at	timestamp
updated_at	timestamp

Relations: belongsTo User. Exposes a last_submitted_at accessor that aggregates the user's submitted, non-test form_submissions.
Indexes: (reliability_score)
Unique constraint: UNIQUE(user_id)
Soft delete: no — cascades on user delete

---

form_schemas

Form definition. Polymorphic owner (event / user_profile / artist / company / organisation / null). Carries purpose, mode, publication flag, public-token rotation state, snapshot policy, freeze-on-submit lock, retention/consent, and collaborative edit-lock columns. OrganisationScope applied as a global scope. ARCH §4.1.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations, cascade delete
owner_type	string(50) nullable	polymorph: event / user_profile / artist / company / organisation / null
owner_id	ULID nullable	polymorph target
name	string
slug	string	canonical within organisation
purpose	string(50)	Slug matching a key in PurposeRegistry::all(); FormPurpose enum mirrors the seven v1.0 values
default_crowd_type_id	ULID nullable	Default CrowdType for event_registration Person provisioning. NULL allowed for non-event_registration purposes; required at publish time for event_registration via RequiresDefaultCrowdType guard. v2.7 — RFC-WS-6 v1.1 §3 Q9 addendum
description	text nullable
is_published	bool	default: false
submission_mode	string(20)	FormSubmissionMode enum value
public_token	ULID nullable	public-facing form URL token
public_token_previous	ULID nullable	previous token — accepted during the rotation grace window
public_token_rotated_at	timestamp nullable	rotation stamp; grace expires 7 days later
submission_deadline	timestamp nullable
locale	string(10)	default: 'nl'; primary locale (translations in form_fields.translations)
settings	JSON nullable	Opaque UI config
version	int unsigned	default: 1; bumped on structural edits
snapshot_mode	string(20)	never / on_submit / always (default never)
freeze_on_submit	bool	default: false. After first submitted submission, schema-structural edits are blocked
retention_days	int unsigned null	After submitted_at + retention_days, PII anonymised via FormSubmissionAnonymisationService
consent_version	string nullable	e.g. "privacy-v2"
section_level_submit	bool	default: false. Enables sections-with-own-submit flow
auto_save_enabled	bool	default: false
max_submissions	int unsigned null	Optional cap (public schemas)
created_by_user_id	ULID FK nullable	→ users, null on delete
last_updated_by_user_id	ULID FK nullable	→ users, null on delete
edit_lock_user_id	ULID FK nullable	→ users, null on delete. Pessimistic editor lock (ARCH §14.5)
edit_lock_expires_at	timestamp nullable	Lock expires here; auto-released by acquire attempts after expiry
created_at, updated_at	timestamps
deleted_at	timestamp nullable	Soft delete

Relations: belongsTo organisation, createdBy/lastUpdatedBy/editLockUser (User); morphsTo owner; hasMany fields, sections, submissions, webhooks
Indexes: (organisation_id, purpose), (owner_type, owner_id), (public_token), (public_token_previous)
Unique constraint: UNIQUE(organisation_id, slug)
Global scope: OrganisationScope
Soft delete: yes

---

form_schema_sections

Optional sections within a schema, active when form_schemas.section_level_submit = true. Supports dependencies: a section is gated until its depends_on_section_id chain reaches approved. Cycle detection applied on save. ARCH §4.8 + §4.8.1.

Column	Type	Notes
id	ULID	PK
form_schema_id	ULID FK	→ form_schemas, cascade delete
slug	string	Snapshot-portable section reference
name	string
description	text nullable
sort_order	int unsigned	default: 0
submit_independent	bool	default: true
depends_on_section_id	ULID FK nullable	→ form_schema_sections (self), null on delete
required_for_schema_submit	bool	default: true
created_at, updated_at	timestamps
deleted_at	timestamp nullable	Soft delete

Relations: belongsTo schema; belongsTo parent section; hasMany form_fields
Indexes: (form_schema_id, sort_order)
Unique constraint: UNIQUE(form_schema_id, slug)
Soft delete: yes

---

form_field_library

Cross-schema reusable field definitions scoped per organisation. Inserting a library field into a schema creates an independent form_fields row linked back via library_field_id for analytics. OrganisationScope applied. ARCH §4.7.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations, cascade delete
name	string
slug	string
field_type	string(50)	One of FormFieldType or a registered custom type
label	string
help_text	text nullable
default_is_required	bool	default: false
default_is_filterable	bool	default: false
translations	JSON nullable	Per-locale overrides
description	text nullable	Admin-only description of intended use
usage_count	int unsigned	default: 0; incremented by FormFieldService::insertFromLibrary
is_system	bool	default: false; system-seeded rows flip true
is_active	bool	default: true
created_at, updated_at	timestamps

Relations: belongsTo organisation; hasMany form_fields via library_field_id; morphMany form_field_bindings as owner; morphMany form_field_validation_rules as owner; morphMany form_field_configs as owner; morphMany form_field_options as owner
Indexes: (organisation_id, field_type), (organisation_id, is_active)
Unique constraint: UNIQUE(organisation_id, slug)
Global scope: OrganisationScope
Soft delete: no

Bindings moved to the relational form_field_bindings table (ARCH-FORM-BUILDER.md §6.7). See WS-5a in /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3.

Validation rules moved to the relational form_field_validation_rules table (ARCH-FORM-BUILDER.md §17.4). The column was dropped in WS-5b; see /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3 WS-5b Uitvoering for the full catalogue and migration notes.

Options moved to the relational form_field_options table (ARCH-FORM-BUILDER.md §17.6). The column was dropped in WS-5d; see /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3 WS-5d Uitvoering.

---

form_fields

Field within a schema. field_type stored as string (not DB enum) so CustomFieldTypeRegistry can extend the catalogue at runtime. Carries the binding (Pattern A/B/C per ARCH §6), filterability, PII flag, conditional_logic rules, per-field role restrictions, translations, and the storage hint that guides FormValueObserver. Uniqueness of slug within a schema is application-enforced (soft deletes prevent a DB-level partial unique). ARCH §4.2.

Column	Type	Notes
id	ULID	PK
form_schema_id	ULID FK	→ form_schemas, cascade delete
form_schema_section_id	ULID FK nullable	→ form_schema_sections, null on delete
library_field_id	ULID FK nullable	→ form_field_library, null on delete
field_type	string(50)	FormFieldType enum value OR a key from config('form_builder.custom_field_types')
slug	string(100)	Unique per schema (application-enforced)
label	string	Default-locale label
help_text	text nullable
section	string(100) null	Visual grouping header (independent of form_schema_section_id)
is_required	bool	default: false
is_filterable	bool	default: false — populates form_values.value_indexed / pivot
is_portal_visible	bool	default: true
is_admin_only	bool	default: false — convenience for common role restriction
is_unique	bool	default: false — uniqueness enforced in FormValueService (ARCH §4.2.1)
is_pii	bool	default: false — drives retention + anonymisation
display_width	string(10)	default: full; FormFieldDisplayWidth enum
role_restrictions	JSON nullable	Per-field RBAC driving FieldAccessService
translations	JSON nullable	{ <locale>: { label, help_text } } (per-option translations live on form_field_options.translations post-WS-5d)
value_storage_hint	string(10)	default: json. FormValueStorageHint enum — guides typed-column population
review_required	bool	default: false
sort_order	int unsigned	default: 0
created_at, updated_at	timestamps
deleted_at	timestamp nullable	Soft delete preserves history

Relations: belongsTo schema, section (nullable), libraryField; hasMany form_values, conditionalLogicGroups; morphMany form_field_bindings as owner; morphMany form_field_validation_rules as owner; morphMany form_field_configs as owner; morphMany form_field_options as owner
Indexes: (form_schema_id, sort_order), (form_schema_id, is_filterable), (library_field_id), (form_schema_id, slug)
Soft delete: yes

Bindings moved to the relational form_field_bindings table (ARCH-FORM-BUILDER.md §6.7). See WS-5a in /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3.

Validation rules moved to the relational form_field_validation_rules table (ARCH-FORM-BUILDER.md §17.4). The column was dropped in WS-5b; see /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3 WS-5b Uitvoering for the full catalogue and migration notes.

Conditional logic moved to the relational form_field_conditional_logic_groups + form_field_conditional_logic_conditions tables (ARCH-FORM-BUILDER.md §8). The column was dropped in WS-5c; see /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3 WS-5c Uitvoering. No library mirror — addendum Q3 excludes library from conditional_logic scope.

Options moved to the relational form_field_options table (ARCH-FORM-BUILDER.md §17.6). The column was dropped in WS-5d; see /dev-docs/ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md §Q3 WS-5d Uitvoering.

---

form_field_bindings

Relational home for field and library-field bindings to entity columns. Polymorphic owner — morph-map aliases form_field and form_field_library. Pattern B (no binding) is represented by the absence of a row; only Pattern A (entity_owned) and Pattern C (mirrored) create rows. Bindings are physical state (not historical intent) — they cascade on owner delete (soft or hard) via FormFieldBindingsCascadeObserver. ARCH §6.7.

Column	Type	Notes
id	ULID	PK
owner_type	string(40)	morph alias: form_field or form_field_library
owner_id	ULID	parent row (a form_fields.id or form_field_library.id)
target_entity	string(50)	e.g. person, user_profile, company, organisation, artist
target_attribute	string(100)	e.g. email, first_name, emergency_contact_phone
mode	string(20)	FormFieldBindingMode enum: entity_owned or mirrored
sync_direction	string(30) null	Pattern C only (e.g. write_on_submit); null for Pattern A
merge_strategy	string(20)	FormFieldBindingMergeStrategy enum; default overwrite
trust_level	tinyint unsigned	0–100, default 50; higher = dominant at conflict
is_identity_key	bool	default false; used by person-matching (WS-6)
created_at, updated_at	timestamps

Relations: morphTo owner (form_field or form_field_library)
Indexes: (target_entity, target_attribute), (owner_type, owner_id)
Unique constraint: UNIQUE(owner_type, owner_id, target_entity, target_attribute)
Global scope: FormFieldBindingScope — resolves tenant via UNION over both owner chains (form_field → form_schema → organisation_id, form_field_library → organisation_id). The standard OrganisationScope can't walk morph parents, so this scope exists as a sibling. Escape hatch: withoutGlobalScope(FormFieldBindingScope::class).
Soft delete: no — bindings are current state, not audit

---

form_field_validation_rules

Relational home for field and library-field validation rules. One row per (owner, rule_type). rule_type is an app-enforced enum (FormFieldValidationRuleType) — database column is string(40) so the enum can extend without migration. parameters JSON carries per- rule-type configuration (value, pattern, mime_types, etc.); shape is enforced at the service layer, not the DB.

Polymorphic owner — morph-map aliases form_field and form_field_library (reused from WS-5a). Rules are physical state (not audit) — they cascade on owner delete (soft or hard) via the shared FormFieldChildTablesCascadeObserver. ARCH §17.4.

Column	Type	Notes
id	ULID	PK
owner_type	string(40)	morph alias: form_field or form_field_library
owner_id	ULID	parent row (a form_fields.id or form_field_library.id)
rule_type	string(40)	FormFieldValidationRuleType case (min_length, max_value, regex, allowed_mime_types, callback, etc.)
parameters	JSON	Per-rule-type bag (e.g. {"value": 3} for min_length, {"mime_types":[...]} for allowed_mime_types)
error_message_key	string(100) null	Optional i18n key for custom rejection message
created_at, updated_at	timestamps

Relations: morphTo owner (form_field or form_field_library)
Indexes: (rule_type), (owner_type, owner_id)
Unique constraint: UNIQUE(owner_type, owner_id, rule_type)
Global scope: FormFieldValidationRuleScope — marker subclass of FormFieldChildTableMorphScope (post-WS-5d extraction); shared UNION-over-two-owner-chains logic lives in the abstract base. Escape hatch: withoutGlobalScope(FormFieldValidationRuleScope::class).
Soft delete: no — rules are current state, not audit

---

form_field_configs

Parallel sibling to form_field_validation_rules — holds non-validation per-field configuration (tag-picker category filters, upload disk selection). Keeps form_field_validation_rules semantically pure (ARCH-FORM-BUILDER.md §17.5; addendum Q3 strict- enterprise decision). Same polymorphic-morph pattern as the binding and validation-rules tables.

Column	Type	Notes
id	ULID	PK
owner_type	string(40)	morph alias: form_field or form_field_library
owner_id	ULID	parent row
config_type	string(40)	FormFieldConfigType case (tag_categories, storage_disk)
parameters	JSON	Per-config-type bag ({"categories":[string]}, {"disk":string})
created_at, updated_at	timestamps

Relations: morphTo owner (form_field or form_field_library)
Indexes: (config_type), (owner_type, owner_id)
Unique constraint: UNIQUE(owner_type, owner_id, config_type)
Global scope: FormFieldConfigScope — third sibling in the scope family (after FormFieldBindingScope and FormFieldValidationRuleScope), same UNION shape. Escape hatch: withoutGlobalScope(FormFieldConfigScope::class).
Soft delete: no — configs are current state, not audit

---

form_field_options

Relational home for option rows on RADIO / SELECT / MULTISELECT / CHECKBOX_LIST fields, replacing the pre-WS-5d form_fields.options and form_field_library.options JSON columns. Same polymorphic-morph pattern as the binding / validation-rules / configs siblings. Each row carries the option's storage value, the default-locale display label, a stable sort_order within owner, and an optional per-locale translations bag. ARCH-FORM-BUILDER.md §17.6; addendum §Q3 WS-5d Uitvoering.

Column	Type	Notes
id	ULID	PK
owner_type	string(40)	morph alias: form_field or form_field_library
owner_id	ULID	parent row (a form_fields.id or form_field_library.id)
value	string(255)	canonical storage value (used by in:options validator)
label	string(255)	default-locale display label
sort_order	int unsigned	default: 0; stable ordering within owner
translations	JSON nullable	{ <locale>: <translated label> } (BCP-47 short form keys)
created_at, updated_at	timestamps

Relations: morphTo owner (form_field or form_field_library)
Indexes: (owner_type, owner_id, sort_order) as ffo_owner_sort_idx
Unique constraint: UNIQUE(owner_type, owner_id, value) as ffo_owner_value_unique — seed-bug guard
Global scope: FormFieldOptionScope — marker subclass of FormFieldChildTableMorphScope (post-WS-5d extraction); shared UNION-over-two-owner-chains logic lives in the abstract base alongside the three sibling marker subclasses (FormFieldBindingScope, FormFieldValidationRuleScope, FormFieldConfigScope). Escape hatch: withoutGlobalScope(FormFieldOptionScope::class).
Soft delete: no — options are current state, not audit. Submission snapshots carry the historical shape.

---

form_field_conditional_logic_groups

Tree nodes (root + branches) of the relational conditional-logic structure that replaced form_fields.conditional_logic JSON in WS-5c. Per addendum Q3, only FormField is in scope — no polymorphic morph, just a simple FK to form_fields. Nesting is adjacency-list via parent_group_id; leaves live in form_field_conditional_logic_conditions. ARCH-FORM-BUILDER.md §8.

Column	Type	Notes
id	ULID	PK
form_field_id	ULID FK	→ form_fields, cascade delete
parent_group_id	ULID FK nullable	→ form_field_conditional_logic_groups, cascade delete; null at the tree root
operator	string(10)	FormFieldConditionalLogicGroupOperator enum: all (AND) | any (OR)
sort_order	int unsigned	default: 0; ordering within parent_group_id
created_at, updated_at	timestamps

Relations: belongsTo form_field, parentGroup; hasMany childGroups, conditions
Indexes: (form_field_id), (parent_group_id, sort_order)
Global scope: OrganisationScope via tenantScopeStrategy() — FK-chain group → field → schema → organisation_id (3 hops, inside the cap). Escape hatch: withoutGlobalScope(OrganisationScope::class).
Soft delete: no — conditional logic is current state, not audit

---

form_field_conditional_logic_conditions

Leaves of the relational conditional-logic tree. Each row holds one (field_slug, comparison_operator, value) comparison attached to a parent group. value is JSON nullable — scalar for most operators, array for in/not_in, null for empty/not_empty. ARCH-FORM-BUILDER.md §8.

Column	Type	Notes
id	ULID	PK
group_id	ULID FK	→ form_field_conditional_logic_groups, cascade delete
field_slug	string(100)	references form_fields.slug within the same schema (not a FK — the service layer resolves it and raises on unknown slugs)
comparison_operator	string(20)	FormFieldConditionalLogicConditionOperator enum: equals, not_equals, contains, not_contains, in, not_in, greater_than, less_than, empty, not_empty
value	JSON nullable	per-operator; null for empty/not_empty
sort_order	int unsigned	default: 0; ordering within the parent group alongside sub-groups
created_at, updated_at	timestamps

Relations: belongsTo group
Indexes: (group_id, sort_order), (field_slug)
Global scope: OrganisationScope via tenantScopeStrategy() — FK-chain condition → group → field → schema → organisation_id (4 hops; fits within the WS-5c-raised cap of 5). Escape hatch: withoutGlobalScope(OrganisationScope::class).
Soft delete: no — conditional logic is current state, not audit

---

form_submissions

One submission per (schema, subject) in single / draft_single modes; unbounded in multiple mode. Polymorphic subject_type / subject_id (allowed types derived from PurposeRegistry::allSubjectTypes(); WS-2 Q6 consolidation). Carries the lifecycle timestamps, review status, optional schema snapshot (when form_schemas.snapshot_mode != 'never'), locale used, idempotency key, anonymisation marker, and a search_index text column fed by FULLTEXT on MySQL and by LIKE fallback elsewhere. ARCH §4.3.

Column	Type	Notes
id	ULID	PK
form_schema_id	ULID FK	→ form_schemas, cascade delete
organisation_id	ULID FK	→ organisations, cascade delete. Denormalized per addendum Q2 — this is the only form-builder child that carries a direct tenant column; every other child resolves tenant via FK-chain through its parent. Populated by FormSubmissionObserver from the schema parent.
event_id	ULID FK nullable	→ events, null on delete. Denormalized per addendum Q2. Observer resolves from form_schemas.owner_id when owner_type = event; otherwise from the active route's {event} parameter. Null for purposes like user_profile / signature_contract.
subject_type	string(50) null	polymorph
subject_id	ULID nullable	polymorph target
submitted_by_user_id	ULID FK nullable	→ users, null on delete
public_submitter_name	string nullable	Public submitters
public_submitter_email	string nullable	Public submitters
public_submitter_ip	string(45) null	IPv4/v6
public_submitter_ip_anonymised_at	timestamp nullable	Set by retention job (ARCH §10.3)
status	string(20)	FormSubmissionStatus enum
review_status	string(30) null	FormSubmissionReviewStatus enum
reviewed_by_user_id	ULID FK nullable	→ users, null on delete
reviewed_at	timestamp nullable
review_notes	text nullable
submitted_at	timestamp nullable
schema_version_at_open	int unsigned null	v2.1 form_schemas.version at draft-create; compared against schema_version_at_submit for drift detection (ARCH §10.4)
schema_version_at_submit	int unsigned null	form_schemas.version at submit time
schema_snapshot	JSON nullable	Full snapshot when policy dictates (ARCH §4.6.1 shape)
is_test	bool	default: false — excluded from reporting & retention
submitted_in_locale	string(10) null	Locale the submitter used while filling in
opened_at	timestamp nullable	First GET
first_interacted_at	timestamp nullable	First field focus
submission_duration_seconds	int unsigned null	opened_at → submitted_at
auto_save_count	int unsigned	default: 0
idempotency_key	ULID nullable	Duplicate-submit guard — UNIQUE (form_schema_id, idempotency_key) since v2.1
anonymised_at	timestamp nullable
identity_match_status	string(20) null	v2.1 null|pending|matched|none — written by TriggerPersonIdentityMatchOnFormSubmit (ARCH §31.1)
apply_status	string(20) null	v2.3 — RFC-WS-6 §3 (Q4) + §5 (O1) null|pending|completed|partial|failed — written by FormBindingApplicator. NULL for legacy rows by design (no DB default)
apply_completed_at	timestamp nullable	v2.3 — RFC-WS-6 §3 (Q4) Set when applicator finishes (success or fail)
search_index	mediumText null	Concatenated text of text-type values; FULLTEXT-indexed on MySQL when supported
created_at, updated_at	timestamps
deleted_at	timestamp nullable	Soft delete

Relations: belongsTo schema, organisation, event, submittedBy / reviewedBy (User); morphsTo subject; hasMany values, section statuses, delegations
Indexes: (form_schema_id, status), (organisation_id, status) (v2.2 — addendum Q2), (event_id, status) (v2.2 — addendum Q2), (subject_type, subject_id), (submitted_by_user_id), (form_schema_id, review_status), UNIQUE (form_schema_id, idempotency_key) (v2.1; replaced the non-unique composite index from v2.0), (form_schema_id, identity_match_status) (v2.1), (form_schema_id, apply_status) (v2.3 — RFC-WS-6), (organisation_id, apply_status) (v2.3 — RFC-WS-6), FULLTEXT(search_index) (MySQL/InnoDB — best-effort, skipped gracefully on SQLite)
Events fired: FormSubmissionCreated, FormSubmissionDraftUpdated, FormSubmissionSubmitted, FormSubmissionReviewed, FormSubmissionSectionSubmitted, FormSubmissionSectionReviewed, FormSubmissionAnonymised, FormSubmissionArchived, FormSubmissionDeleted
Soft delete: yes

Denormalization rationale (addendum Q2): form_submissions is the single rapportage-hot table that earns a denormalized organisation_id (and event_id). Aggregerende queries over duizenden rijen — dashboards, CSV-exports, counts — run directly against these columns instead of joining through form_schemas. Every other form-builder child table (form_schema_sections, form_fields, form_values, form_value_options, form_submission_section_statuses, form_submission_delegations, form_schema_webhooks, form_webhook_deliveries) resolves its tenant via the FK-chain strategy added to OrganisationScope in the same addendum.

---

form_submission_section_statuses

Per-section lifecycle state when form_schemas.section_level_submit = true. ULID PK per addendum Q1 (integer-AI exception retired 2026-04-24). ARCH §4.9.

Column	Type	Notes
id	ULID	PK
form_submission_id	ULID FK	→ form_submissions, cascade delete
form_schema_section_id	ULID FK	→ form_schema_sections, cascade delete
status	string(30)	draft / submitted / approved / rejected / changes_requested
submitted_at	timestamp nullable
reviewed_by_user_id	ULID FK nullable	→ users, null on delete
reviewed_at	timestamp nullable
review_notes	text nullable
created_at, updated_at	timestamps

Unique constraint: UNIQUE(form_submission_id, form_schema_section_id)
Soft delete: no

---

form_submission_delegations

"X fills in this submission on behalf of Y." Subject-self grants the delegation; the delegatee can view + edit the draft. ARCH §4.10.

Column	Type	Notes
id	ULID	PK
form_submission_id	ULID FK	→ form_submissions, cascade delete
delegated_to_user_id	ULID FK	→ users, cascade delete
delegated_by_user_id	ULID FK	→ users, cascade delete
granted_at	timestamp
revoked_at	timestamp nullable	Null = delegation active
message	text nullable	Optional context from delegator to delegatee
created_at, updated_at	timestamps

Indexes: (delegated_to_user_id, revoked_at), (form_submission_id)
Soft delete: no

---

form_values

EAV row per (submission, field). ULID PK per ARCH-CONSOLIDATION-ADDENDUM-2026-04-24.md Q1 — the earlier "int joins beat ULID joins" rationale was retired on 2026-04-24 in favour of uniform ULID consistency across every business and pivot table. Canonical payload lives in the value JSON column; the typed columns are derived.

Observer behaviour (FormValueObserver, ARCH §7.2):

• When form_fields.is_filterable = true on a scalar field, value_indexed is populated (truncated to 255 chars) from the JSON value.
• When form_fields.value_storage_hint = number, value_number is populated as decimal(15,4).
• When value_storage_hint = date, value_date is populated.
• When value_storage_hint = bool, value_bool is populated.
• When is_filterable = false, all typed columns are reset to NULL and every matching form_value_options row is deleted.
• On filterable multi-value fields (MULTISELECT, CHECKBOX_LIST, TAG_PICKER), the form_value_options pivot is rebuilt (delete all, insert current) on each save.

Column	Type	Notes
id	ULID	PK
form_submission_id	ULID FK	→ form_submissions, cascade delete
form_field_id	ULID FK	→ form_fields, cascade delete
value	JSON	Canonical payload
value_indexed	string(255) nullable	Populated by observer for filterable scalar fields
value_number	decimal(15,4) null	Populated when value_storage_hint = number
value_date	date nullable	Populated when value_storage_hint = date
value_bool	bool nullable	Populated when value_storage_hint = bool
value_anonymised	bool	default: false — set by anonymisation
created_at, updated_at	timestamps

Unique constraint: UNIQUE(form_submission_id, form_field_id)
Indexes: (form_field_id, value_indexed), (form_field_id, value_number), (form_field_id, value_date)
Soft delete: no — history preserved via submission soft delete

---

form_value_options

Filter pivot for multi-value field types (MULTISELECT, CHECKBOX_LIST, TAG_PICKER). Denormalises form_field_id and form_submission_id for fast filtering joins. Rebuilt by the observer on every save — rows have no long-term identity beyond the row they belong to. ARCH §4.5.

Column	Type	Notes
id	ULID	PK
form_value_id	ULID FK	→ form_values, cascade delete
form_field_id	ULID FK	→ form_fields, cascade delete (denormalised)
form_submission_id	ULID FK	→ form_submissions, cascade delete (denormalised)
option_value	string(255)	Single selected option

Indexes: (form_field_id, option_value), (form_submission_id), (form_value_id)
Soft delete: no

---

form_templates

Org-scoped reusable schema snapshots. Applying a template creates a new form_schemas row with fields copied from the snapshot. System templates (is_system = true) ship with Crewli and cannot be deleted — only deactivated. OrganisationScope applied. ARCH §4.6.

Column	Type	Notes
id	ULID	PK
organisation_id	ULID FK	→ organisations, cascade delete
name	string
slug	string
purpose	string(50)	FormPurpose enum value — constrains which schemas this template can seed
description	text nullable
schema_snapshot	JSON	Canonical snapshot shape (ARCH §4.6.1) — schema metadata + sections[] + fields[] block
is_system	bool	default: false
is_active	bool	default: true
created_at, updated_at	timestamps

Indexes: (organisation_id, purpose, is_active)
Unique constraint: UNIQUE(organisation_id, slug)
Global scope: OrganisationScope
Soft delete: no

---

form_schema_webhooks

Webhook subscriptions per schema. url and secret are stored as encrypted TEXT (Eloquent cast on the model). API resources never echo these back — only url_host + has_secret. ARCH §4.11. OrganisationScope NOT applied directly — enforced through the parent schema.

Column	Type	Notes
id	ULID	PK
form_schema_id	ULID FK	→ form_schemas, cascade delete
name	string
trigger_event	string(40)	submission_created / submission_submitted / submission_reviewed / section_submitted / section_approved / section_rejected
url	text	Encrypted via Eloquent cast
secret	text nullable	Encrypted via Eloquent cast; used for HMAC-SHA256 signing
is_active	bool	default: true
created_at, updated_at	timestamps

Indexes: (form_schema_id, is_active)
Soft delete: no

---

form_webhook_deliveries

Delivery audit + retry queue populated by FormWebhookDispatcher and driven through by DeliverFormWebhookJob on the dedicated webhooks queue. Retries: {1m, 5m, 30m, 2h, 8h}, max 5 attempts, status flips to dead_letter on exhaustion. SSRF-protected. ARCH §4.12 + §17.5.

Column	Type	Notes
id	ULID	PK
form_schema_webhook_id	ULID FK	→ form_schema_webhooks, cascade delete
form_submission_id	ULID FK	→ form_submissions, cascade delete
trigger_event	string(40)	Mirrors the webhook's trigger
status	string(20)	FormWebhookDeliveryStatus enum: pending / delivered / failed / dead_letter
attempts	int unsigned	default: 0
last_attempt_at	timestamp nullable
response_status	smallint unsigned nullable	HTTP status code
response_body_excerpt	text nullable	First ~1000 chars
next_retry_at	timestamp nullable
delivered_at	timestamp nullable
failed_permanently_at	timestamp nullable
payload_snapshot	JSON	Exactly what was (or will be) sent — for replay/audit

Indexes: (status, next_retry_at), (form_schema_webhook_id, status), (form_submission_id)
Soft delete: no — no created_at / updated_at either; last_attempt_at is the effective timestamp

---

form_submission_action_failures

v2.3 — RFC-WS-6 Q5 Audit table for binding-pipeline failures. Populated by ApplyBindingsOnFormSubmit (and any future listener that wraps a FormBindingApplicator invocation) when the inner apply transaction rolls back. Retry / Resolve / Dismiss workflows consume this table from session 2 onward.

No organisation_id column — tenant scope flows via form_submission_id → form_submissions.organisation_id. Enforced at access time by FormSubmissionActionFailurePolicy (RFC V3, IDOR-class FK-chain pattern). Do NOT register OrganisationScope directly on this table.

Column	Type	Notes
id	ULID	PK
form_submission_id	ULID FK	→ form_submissions, cascade delete
listener_class	string(255)	e.g. App\Listeners\FormBuilder\ApplyBindingsOnFormSubmit
binding_id	ULID FK nullable	→ form_field_bindings, null on delete (binding may be edited later)
failed_at	timestamp
exception_class	string(255)
exception_message	text
exception_trace	longtext nullable	Full PHP stack trace for triage (admin-UI). v2.9 — WS-6 sessie 3c
context	json	Free-form: {target_entity, target_attribute, value_excerpt, merge_strategy}
retry_count	tinyint unsigned	default: 0
resolved_at	timestamp nullable	Set when retry succeeds OR organiser marks resolved
resolved_by_user_id	ULID FK nullable	→ users, null on delete
resolved_note	text nullable	Optional human note on resolution
dismissed_at	timestamp nullable	Mutex with resolved_at
dismissed_by_user_id	ULID FK nullable	→ users, null on delete
dismissed_reason_type	string(40) nullable	DismissalReasonType enum (RFC V2). Constrained at request layer
dismissed_reason_note	string(500) nullable	Required at request layer when reason_type = other
created_at, updated_at	timestamps

Relations: belongsTo submission, binding (nullable), resolvedBy / dismissedBy (User)
Indexes: (form_submission_id), (listener_class, failed_at), (resolved_at), (dismissed_at), (binding_id), (dismissed_reason_type) (analytics)
Soft delete: no — audit table; retention via parent submission cascade-delete

---

form_submission_action_failure_retry_attempts

v2.9 — WS-6 sessie 3c (RFC-WS-6.md §3 Q5 addendum) Per-attempt retry history. Sessie 1's form_submission_action_failures.retry_count is a counter only; this table adds per-attempt records (timestamp, user, outcome, exception details if failed) so the admin UI can show retry history with full context. Parent's retry_count stays as denormalised cache for index-view performance; the service layer (FormFailureRetryService) keeps both in sync per retry.

Column	Type	Notes
id	ULID	PK
form_submission_action_failure_id	ULID FK	→ form_submission_action_failures, cascade delete (FK name fsafra_failure_fk to fit MySQL's 64-char identifier limit)
attempted_at	timestamp	When the retry was invoked
attempted_by_user_id	ULID FK nullable	→ users, null on delete (FK name fsafra_user_fk)
outcome	enum	succeeded | failed
exception_class	string(255) nullable	Captured per-attempt — parent's exception_class stays audit-immutable (represents FIRST failure)
exception_message	text nullable	Captured per-attempt
created_at, updated_at	timestamps

Relations: belongsTo failure, attemptedBy (User)
Indexes: fsafra_failure_attempt_idx on (form_submission_action_failure_id, attempted_at)
Soft delete: no — audit table; retention via parent failure cascade-delete

---

Activity log strategy: explicit calls via FormSchema::logSchemaChange() and FormField::logFieldChange() — no LogsActivity trait (would produce noise). Only impactful events logged (publish toggle, purpose change, binding change, is_pii toggle, etc.). Bulk-fixture suppression via App\Support\ActivityLog::suppressed(fn () => …) which flips config('activitylog.enabled') for the callback.

Tag sync integration (FORM-02, ARCH §31.10): the SyncTagPickerSelectionsOnSubmit listener fires on FormSubmissionSubmitted for event_registration schemas with a person subject. It rebuilds user_organisation_tags rows where source = self_reported through FormTagSyncService::rebuildForPerson — no-op when person.user_id IS NULL (the deferred-sync path runs via PersonIdentityService::confirmMatch once the person is linked).

Multi-tenancy: OrganisationScope applied on FormSchema, FormTemplate, FormFieldLibrary. Other form-builder tables inherit isolation through their parent schema; FormSchemaWebhook documents this discipline explicitly via a docblock warning to never query directly without an eager constraint.