Data Model and Migrations (Selected Highlights) – Program & Staff Operations
This section dives into the core database tables and migrations that power program proposals, communication workflows, staffing templates, staff assignments, and venue management. These migrations, paired with corresponding routes and controllers, enable directors and managers to operate at scale.
Proposals 📝
Proposals allow programs to negotiate event details before confirmation. They link programs, contract templates, and events.
| Table | Purpose |
|---|---|
proposals |
Stores program proposals with travel, teams, age info. |
event_proposal |
Pivots proposals to events with team assignments. |
Key Migration: Create Proposals
Schema::create('proposals', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->softDeletes();
$table->foreignId('program_id')->constrained()->onDelete('cascade');
$table->json('teams')->nullable();
$table->json('age_groups')->nullable();
$table->integer('max_travel')->nullable();
$table->json('unavailable_weekends')->nullable();
$table->foreignId('contract_template_id')->nullable()->constrained()->onDelete('set null');
$table->foreignId('contract_id')->nullable()->constrained()->onDelete('set null');
$table->unique(['program_id', 'deleted_at']);
});
Schema::create('event_proposal', function (Blueprint $table) {
$table->id();
$table->foreignId('proposal_id')->constrained()->onDelete('cascade');
$table->foreignId('event_id')->constrained()->onDelete('cascade');
$table->json('teams')->nullable();
$table->unique(['proposal_id', 'event_id']);
});
– Adds core proposal data and links to events
Enhancement: Notes Field
Schema::table('proposals', function (Blueprint $table) {
$table->longText('notes')->nullable();
});
– Allows freeform notes on proposals
Routes & Controllers
Route::resources(['proposals' => ProposalController::class])GET /proposals/{proposal}/clone→ProposalController@showClonePOST /proposals/{proposal}/clone→ProposalController@clone
These reside underrole:Admin|Event Manager|Sales|Event Director
Communication Questions & Answers 💬
Communication workflows use customizable question sets to collect status updates.
| Table | Purpose |
|---|---|
communication_statuses |
Defines a status (e.g., “Venue Confirmed”). |
communication_status_questions |
Holds questions per status. |
communications (answers column) |
Stores user answers as JSON. |
Key Migration: Create Communication Status Questions
Schema::create('communication_status_questions', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->foreignId('communication_status_id')->constrained()->onDelete('cascade');
$table->string('title');
$table->json('options');
$table->string('type');
$table->boolean('required')->default(false);
});
– Structures dynamic questions per communication status
Enhancement: Answers JSON on Communications
Schema::table('communications', function (Blueprint $table) {
$table->json('answers')->nullable();
});
– Captures responses to status questions
Staffing Templates 📋
Staffing templates define default positions and schedules for events.
| Table | Purpose |
|---|---|
staffing_templates |
Stores named templates, positions, schedules. |
Migration: Create Staffing Templates
Schema::create('staffing_templates', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->string('name');
$table->text('positions');
$table->tinyInteger('staffing_grid_enabled');
$table->text('default_schedule')->nullable();
$table->unsignedSmallInteger('game_slot_length')->nullable();
});
– Defines reusable staffing patterns
Staff & Assignments 👥
Staff management spans master staff records, event-specific assignments, and detailed schedules.
| Table | Purpose |
|---|---|
staff |
Master list of staff with profiles. |
event_staff |
Assigns staff to events/venues with rates. |
staff_event_schedule |
Tracks time-bound schedules per staff/event. |
Migration: Create Staff
Schema::create('staff', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('headshot')->default(config('constants.default_user_image'));
$table->text('bio')->nullable();
$table->timestamps();
$table->string('phone')->nullable();
$table->string('email')->nullable();
// ... other profile fields ...
$table->json('days')->nullable();
$table->json('venues')->nullable();
});
– Captures staff personal and availability data
Migration: Create Event Staff
Schema::create('event_staff', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('event_id');
$table->string('role');
$table->unsignedBigInteger('venue_id')->nullable();
$table->double('rate', 8, 2)->default(0.00);
$table->enum('rate_type', ['Hourly', 'Fixed'])->default('Hourly');
$table->double('hours_worked', 4, 2)->default(0.00);
$table->boolean('is_paid')->default(0);
$table->unsignedBigInteger('staff_id')->nullable();
$table->longText('notes')->nullable();
// ... foreign keys ...
});
– Associates staff to event roles and venues
Migration: Create Staff Event Schedule
Schema::create('staff_event_schedule', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->unsignedBigInteger('event_id');
$table->unsignedBigInteger('staff_id');
$table->dateTime('start');
$table->dateTime('end');
$table->longText('notes')->nullable();
$table->string('role')->nullable();
// ... foreign keys ...
});
– Logs exact time blocks for staff assignments
Routes & Controllers
- Staff Management:
Route::resource('staff', StaffController::class)- Bulk destroy:
POST /staff/destroy-bulk→StaffController@destroyBulk
- Staffing Editor:
GET /staffing/editor/{event}/{venue?}→StaffController@staffingEditorPOST /staffing/update-staffing-row/{event}→StaffController@updateStaffingPositionsRow- Create modal:
GET /staffing/editor-create-staff→StaffController@editorCreate
These routes empower managers to onboard, assign, and adjust staff dynamically
Venues & Venue Management 📍
Venues store location, capacity, and facility details. They integrate tightly with event staffing.
| Table | Purpose |
|---|---|
venues |
Stores venue metadata and amenities. |
venue_images |
Holds images linked to venues. |
Migration: Create Venues
Schema::create('venues', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->timestamp('deleted_at')->nullable();
$table->string('name');
$table->string('address');
$table->string('type');
$table->string('court_count')->nullable();
$table->longText('notes')->nullable();
// ... amenities fields ...
});
– Centralizes venue configuration
Migration: Venue Images
Schema::create('venue_images', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->string('url');
$table->unsignedBigInteger('venue_id');
$table->foreign('venue_id')->references('id')->on('venues')->onDelete('cascade');
});
– Supports multiple photos per venue
Routes & Controllers
- Event Venues
GET /events/{event}/venues→VenueController@showEventVenuesGET /events/{event}/getvenues→VenueController@getEventVenuesPOST /events/{event}/venues→VenueController@postEventVenuesPOST /update-venue-data→VenueController@updateData
These endpoints let directors review and modify venue details per event
ER Diagram: Core Relationships
erDiagram
PROGRAMS ||--o{ PROPOSALS : has
PROPOSALS ||--o{ EVENT_PROPOSAL : maps
EVENTS ||--o{ EVENT_PROPOSAL : receives
COMMUNICATION_STATUSES ||--o{ COMMUNICATION_STATUS_QUESTIONS : defines
COMMUNICATION_STATUS_QUESTIONS ||--o{ COMMUNICATIONS : answered
STAFF ||--o{ EVENT_STAFF : assigned
EVENTS ||--o{ EVENT_STAFF : hosts
STAFF ||--o{ STAFF_EVENT_SCHEDULE : scheduled
EVENTS ||--o{ STAFF_EVENT_SCHEDULE : schedules
STAFFING_TEMPLATES ||--o{ EVENT_STAFF : templates
VENUES ||--o{ EVENT_STAFF : location
VENUES ||--o{ VENUE_IMAGES : pictures
This diagram illustrates how programs, events, communications, staff, templates, and venues interconnect to support director/manager workflows at scale.
Key Takeaway
These migrations and routes form the backbone of program proposals, communication handling, staffing logistics, and venue management—ensuring a robust, scalable platform for Prep Network operations.