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@showClone
  • POST /proposals/{proposal}/clone → ProposalController@clone
    These reside under role: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@staffingEditor
    • POST /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@showEventVenues
    • GET /events/{event}/getvenues → VenueController@getEventVenues
    • POST /events/{event}/venues → VenueController@postEventVenues
    • POST /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.