Skip to main content

Admin User Cases: Operations & Reports

Actors: Susanne (Admin/PM) User Cases: UC-07, UC-08, UC-09, UC-10

Overview​

This document covers operational and reporting workflows:

  • UC-07: Generate Reports (Crew List, Meal List, Budget)
  • UC-08: Invite Staff to System
  • UC-09: Review Staff Registrations
  • UC-10: Handle Scheduling Conflicts

These workflows support day-to-day project operations, from bringing new crew into the system to generating client-ready reports.

UC-07: Generate Reports​

Goal: Susanne generates various reports for a project (crew list, meal list, clothing sizes, budget).

Actors: Susanne (Admin)

Preconditions:

  • Project exists with confirmed assignments
  • Some assignments have travel/hotel booked (for those reports)

System: backend.pbs.com

UI Journey​

Step 1: Navigate to Project Reports

┌─────────────────────────────────────────────────────────────┐
│ backend.pbs.com                           [Susanne â–Œ]       │
├──────────────────────────────────────────────────────────────
│ ← Projects                                                   │
│                                                              │
│ Champions League Final 2025                                 │
│ ESC-2025-042 • Active • May 10-15, 2025 • Madrid           │
│                                                              │
│ [Overview] [Staffing] [Travel] [Documents] [Reports]        │
│                                                              │
│ ═══ REPORTS ═════════════════════════════════════════════    │
│                                                              │
│ Generate project reports for clients and operations          │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 📋 Crew List                                          │   │
│ │    All assignments with names, roles, contact info   │   │
│ │    [Generate PDF] [Generate Excel]                    │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 🍜 Meal List                                          │   │
│ │    Food preferences and dietary requirements          │   │
│ │    [Generate PDF]                                      │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 👕 Clothing Sizes                                     │   │
│ │    Sizes for uniform/merchandise ordering             │   │
│ │    [Generate Excel]                                    │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 🏚 Hotel Bookings                                     │   │
│ │    All hotel reservations and check-in dates          │   │
│ │    [Generate PDF] [Generate Excel]                    │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 💰 Budget Report                                      │   │
│ │    Planned vs actual costs                            │   │
│ │    [Generate PDF]                                      │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ PREVIOUSLY GENERATED (3)                                     │
│                                                              │
│ • Crew List PDF - May 4, 2025 14:30 [Download]             │
│ • Meal List PDF - May 3, 2025 10:15 [Download]             │
│ • Budget Report PDF - May 1, 2025 09:00 [Download]         │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 2: Click "Generate PDF" for Crew List → Preview

┌─────────────────────────────────────────────────────────────┐
│ Generate Crew List                               [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ Preview (5 crew members):                                    │
│                                                              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ CREW LIST                                              │ │
│ │ Champions League Final 2025                            │ │
│ │ Madrid, Spain • May 10-15, 2025                        │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ EVS OPERATORS (2)                                      │ │
│ │                                                        │ │
│ │ Lisa Berg                                              │ │
│ │ [Photo]                                                │ │
│ │ Phone: +46 70 123 4567                                │ │
│ │ Email: lisa@example.com                               │ │
│ │ Departure: Stockholm                                   │ │
│ │                                                        │ │
│ │ Johan Andersson                                        │ │
│ │ [Photo]                                                │ │
│ │ Phone: +46 70 234 5678                                │ │
│ │ Email: johan@example.com                              │ │
│ │ Departure: Stockholm                                   │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ CAMERA OPERATORS (2)                                   │ │
│ │ [... continues for all roles ...]                     │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ CONTACT:                                               │ │
│ │ Project Manager: Susanne +46 70 123 4567              │ │
│ │ Generated: May 5, 2025 10:30                           │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ [Download PDF] [Send via Email] [Cancel]                   │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 3: Click "Generate PDF" for Meal List

┌─────────────────────────────────────────────────────────────┐
│ Generate Meal List                               [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ Preview:                                                     │
│                                                              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ MEAL LIST                                              │ │
│ │ Champions League Final 2025                            │ │
│ │ May 10-15, 2025 • 5 crew members                       │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ DIETARY REQUIREMENTS SUMMARY                           │ │
│ │                                                        │ │
│ │ • Standard meals: 3 people                            │ │
│ │ • Vegetarian: 1 person (Lisa Berg)                    │ │
│ │   - No shellfish (allergy)                            │ │
│ │ • Gluten-free: 1 person (Anders Svensson)             │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ DETAILED LIST                                          │ │
│ │                                                        │ │
│ │ Lisa Berg                                              │ │
│ │ • Vegetarian                                           │ │
│ │ • No shellfish (allergy)                              │ │
│ │                                                        │ │
│ │ Johan Andersson                                        │ │
│ │ • No special requirements                              │ │
│ │                                                        │ │
│ │ [... continues for all crew ...]                      │ │
│ │                                                        │ │
│ │ For catering contact: Susanne +46 70 123 4567         │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ [Download PDF] [Send to Caterer] [Cancel]                  │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 4: Budget Report (After Project Completion)

┌─────────────────────────────────────────────────────────────┐
│ Generate Budget Report                           [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ ⚠ Note: Project not completed yet                           │
│ This report shows planned costs. Generate again after        │
│ project completion for actual costs.                         │
│                                                              │
│ Preview:                                                     │
│                                                              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ BUDGET REPORT                                          │ │
│ │ Champions League Final 2025                            │ │
│ │                                                        │ │
│ │ PROJECT BUDGET: 150,000 SEK                            │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ CREW COSTS (Planned)                                   │ │
│ │                                                        │ │
│ │ Lisa Berg (EVS Operator)                               │ │
│ │ • 3 work days @ 5,500 SEK = 16,500 SEK               │ │
│ │ • 2 travel days @ 2,750 SEK = 5,500 SEK              │ │
│ │ • Total: 22,000 SEK                                    │ │
│ │                                                        │ │
│ │ Johan Andersson (Multi-Cam Producer)                   │ │
│ │ • 3 work days @ 7,500 SEK = 22,500 SEK               │ │
│ │ • 2 travel days @ 3,750 SEK = 7,500 SEK              │ │
│ │ • Total: 30,000 SEK                                    │ │
│ │                                                        │ │
│ │ [... continues for all assignments ...]               │ │
│ │                                                        │ │
│ │ ────────────────────────────────────────────────────   │ │
│ │                                                        │ │
│ │ TOTAL CREW COSTS: 110,000 SEK                          │ │
│ │                                                        │ │
│ │ TRAVEL & HOTEL: 35,000 SEK                             │ │
│ │ OTHER COSTS: 5,000 SEK                                 │ │
│ │                                                        │ │
│ │ ════════════════════════════════════════════════════   │ │
│ │ TOTAL PROJECT COST: 150,000 SEK                        │ │
│ │ Budget remaining: 0 SEK                                │ │
│ │ Status: On budget ✓                                    │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ [Download PDF] [Cancel]                                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

System Actions​

Crew List Generation:

-- Query assignments with profile details
SELECT
  p.first_name, p.last_name, p.photo_url,
  p.phone, p.email,
  a.departure_city,
  f.name AS function_name, f.department,
  proj.name AS project_name, proj.location_city, proj.location_venue,
  proj.start_date, proj.end_date
FROM assignments a
JOIN profiles p ON a.profile_id = p.id
JOIN project_functions pf ON a.project_function_id = pf.id
JOIN projects proj ON pf.project_id = proj.id
JOIN functions_catalog f ON pf.function_id = f.id
WHERE proj.id = {project_id}
  AND a.current_status NOT IN ('cancelled')
ORDER BY f.department, f.name, p.last_name

Meal List Generation:

-- Query food preferences
SELECT
  p.first_name, p.last_name,
  p.food_preferences
FROM assignments a
JOIN profiles p ON a.profile_id = p.id
JOIN project_functions pf ON a.project_function_id = pf.id
WHERE pf.project_id = {project_id}
  AND a.current_status NOT IN ('cancelled')
ORDER BY p.last_name

Budget Report Generation:

-- For active project (planned costs)
SELECT
  p.first_name || ' ' || p.last_name AS crew_name,
  f.name AS function_name,
  pf.work_days, pf.travel_days,
  a.work_day_rate_cents, a.travel_day_rate_cents,
  a.rate_currency,
  -- Calculate total
  (a.work_day_rate_cents * pf.work_days +
   a.travel_day_rate_cents * pf.travel_days) AS total_cents
FROM assignments a
JOIN profiles p ON a.profile_id = p.id
JOIN project_functions pf ON a.project_function_id = pf.id
JOIN functions_catalog f ON pf.function_id = f.id
WHERE pf.project_id = {project_id}
  AND a.current_status NOT IN ('cancelled')

-- For completed project (actual costs)
SELECT
  p.first_name || ' ' || p.last_name AS crew_name,
  f.name AS function_name,
  -- Planned
  pf.work_days AS planned_work_days,
  pf.travel_days AS planned_travel_days,
  a.work_day_rate_cents AS planned_work_rate,
  a.travel_day_rate_cents AS planned_travel_rate,
  -- Actual
  COALESCE(a.actual_work_days, pf.work_days) AS actual_work_days,
  COALESCE(a.actual_travel_days, pf.travel_days) AS actual_travel_days,
  COALESCE(a.actual_work_rate_cents, a.work_day_rate_cents) AS actual_work_rate,
  COALESCE(a.actual_travel_rate_cents, a.travel_day_rate_cents) AS actual_travel_rate,
  a.adjustment_amount_cents,
  a.adjustment_reason,
  -- Calculate totals
  (a.work_day_rate_cents * pf.work_days +
   a.travel_day_rate_cents * pf.travel_days) AS planned_total,
  (COALESCE(a.actual_work_rate_cents, a.work_day_rate_cents) * COALESCE(a.actual_work_days, pf.work_days) +
   COALESCE(a.actual_travel_rate_cents, a.travel_day_rate_cents) * COALESCE(a.actual_travel_days, pf.travel_days) +
   a.adjustment_amount_cents) AS actual_total
FROM assignments a
JOIN profiles p ON a.profile_id = p.id
JOIN project_functions pf ON a.project_function_id = pf.id
JOIN functions_catalog f ON pf.function_id = f.id
WHERE pf.project_id = {project_id}
  AND a.current_status NOT IN ('cancelled')

Store Export Record:

INSERT INTO exports (
  project_id, type, filters,
  file_url, file_format,
  generated_by_user_id, generated_at
) VALUES (
  {project_id},
  'crew_list',  -- or 'meal_list', 'budget_report', etc.
  '{"include_photos": true, "format": "standard"}',
  's3://exports/crew-list-2025-05-05.pdf',
  'pdf',
  {susanne_user_id},
  NOW()
)

UC-08: Invite Staff to System​

Goal: Susanne invites new crew members to register on the crew portal.

Actors: Susanne (Admin)

Preconditions:

  • Susanne has email addresses of crew to invite

System: backend.pbs.com

UI Journey​

Step 1: Navigate to Staff Directory

┌─────────────────────────────────────────────────────────────┐
│ backend.pbs.com                           [Susanne â–Œ]       │
├──────────────────────────────────────────────────────────────
│ Staff Directory                                              │
│                                                              │
│ [All Staff] [Pending Invitations] [Pending Registrations]   │
│                                                              │
│ ═══ ALL CREW (24) ═══════════════════════════════════════    │
│                                                              │
│ Search: [____________] 🔍                                    │
│ Filter: [All Functions â–Œ] [All Locations â–Œ]                 │
│                                                              │
│ [+ Invite New Crew]                                          │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 📷 Lisa Berg                          🟢 Available   │   │
│ │    EVS Operator (Senior) • Stockholm                  │   │
│ │    [View Profile] [Send Offer]                        │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ [... more staff members ...]                                 │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 2: Click "+ Invite New Crew" → Invitation Form

┌─────────────────────────────────────────────────────────────┐
│ Invite New Crew Members                          [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ Send invitations to crew members to join your portal         │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ EMAIL ADDRESSES (one per line):                              │
│                                                              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ anders@example.com                                     │ │
│ │ maria@example.com                                      │ │
│ │ erik@example.com                                       │ │
│ │                                                        │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ INVITATION SETTINGS                                          │
│                                                              │
│ ☐ Auto-approve registrations                                │
│   (skip manual review, create profile immediately)           │
│                                                              │
│ Invitation expires in: [14 days â–Œ]                          │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ [Send 3 Invitations] [Cancel]                               │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 3: Click "Send 3 Invitations" → Confirmation

┌─────────────────────────────────────────────────────────────┐
│ ✓ Invitations sent!                                         │
│                                                              │
│ 3 invitation emails have been sent:                          │
│ • anders@example.com                                         │
│ • maria@example.com                                          │
│ • erik@example.com                                           │
│                                                              │
│ They'll receive an email with a registration link.           │
│                                                              │
│ Track invitations in: [Pending Invitations]                 │
│                                                              │
│ [Close]                                                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 4: View Pending Invitations

┌─────────────────────────────────────────────────────────────┐
│ Staff Directory - Pending Invitations                        │
├──────────────────────────────────────────────────────────────
│                                                              │
│ [All Staff] [Pending Invitations] [Pending Registrations]   │
│                                                              │
│ ═══ PENDING INVITATIONS (3) ═════════════════════════════    │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ anders@example.com                                    │   │
│ │ Sent: May 5, 2025 10:00                              │   │
│ │ Status: Pending (not opened yet)                      │   │
│ │ Expires: May 19, 2025                                 │   │
│ │                                                        │   │
│ │ [Resend Email] [Cancel Invitation]                    │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ maria@example.com                                     │   │
│ │ Sent: May 5, 2025 10:00                              │   │
│ │ Status: Opened (May 5, 11:30)                         │   │
│ │ Registration: In progress                             │   │
│ │                                                        │   │
│ │ [View Status]                                          │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ erik@example.com                                      │   │
│ │ Sent: May 5, 2025 10:00                              │   │
│ │ Status: Completed!                                     │   │
│ │ Registration submitted: May 5, 14:00                  │   │
│ │                                                        │   │
│ │ [Review Registration →]                                │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
└─────────────────────────────────────────────────────────────┘

System Actions​

Create Invitations:

-- For each email address
INSERT INTO staff_invitations (
  company_id, email, token,
  auto_approve, status,
  sent_at, expires_at,
  invited_by_user_id
) VALUES (
  {company_id},
  'anders@example.com',
  uuid_generate_v4(),  -- Unique token for URL
  false,  -- Manual review required
  'pending',
  NOW(),
  NOW() + INTERVAL '14 days',
  {susanne_user_id}
)

Send Invitation Email (external service):

To: anders@example.com
Subject: Invitation to join NCP crew portal

Hi,

Susanne from NCP has invited you to join their crew portal on PBS.

[Complete Registration]
(Link: crew.pbs.com/register/{token})

This link expires in 14 days.

Track Invitation Status:

-- When recipient clicks link
UPDATE staff_invitations
SET status = 'opened',
    opened_at = NOW()
WHERE token = {token_from_url}

-- When registration submitted
UPDATE staff_invitations
SET status = 'completed',
    completed_at = NOW()
WHERE token = {token_from_url}

UC-09: Review Staff Registrations​

Goal: Susanne reviews completed registrations and approves/rejects them.

Actors: Susanne (Admin)

Preconditions:

  • Staff have completed registration forms (UC-11)
  • Submissions awaiting review

System: backend.pbs.com

UI Journey​

Step 1: Navigate to Pending Registrations

┌─────────────────────────────────────────────────────────────┐
│ Staff Directory - Pending Registrations                      │
├──────────────────────────────────────────────────────────────
│                                                              │
│ [All Staff] [Pending Invitations] [Pending Registrations]   │
│                                                              │
│ ═══ PENDING REGISTRATIONS (2) ═══════════════════════════    │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 📷 Erik Johansson                                     │   │
│ │    Sound Mixer (Senior)                               │   │
│ │    Gothenburg, Sweden                                 │   │
│ │                                                        │   │
│ │    Submitted: May 5, 2025 14:00                       │   │
│ │    From invitation: erik@example.com                  │   │
│ │                                                        │   │
│ │    [Review Registration]                               │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
│ ┌──────────────────────────────────────────────────────┐   │
│ │ 📷 Maria Lindqvist                                    │   │
│ │    Graphics Operator (Mid)                            │   │
│ │    Malmö, Sweden                                      │   │
│ │                                                        │   │
│ │    Submitted: May 4, 2025 16:30                       │   │
│ │    From invitation: maria@example.com                 │   │
│ │                                                        │   │
│ │    [Review Registration]                               │   │
│ └──────────────────────────────────────────────────────┘   │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 2: Click "Review Registration" for Erik → Review Screen

┌─────────────────────────────────────────────────────────────┐
│ Review Registration: Erik Johansson              [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│     ┌───────────┐                                           │
│     │ [Photo]   │      Erik Johansson                       │
│     │           │      erik@example.com                     │
│     └───────────┘      +46 70 234 5678                      │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ BASIC INFORMATION                                            │
│ • Name: Erik Johansson                                       │
│ • Email: erik@example.com                                    │
│ • Phone: +46 70 234 5678                                     │
│ • Home base: Gothenburg                                      │
│ • Address: Kungsgatan 45, 411 15 Gothenburg                 │
│                                                              │
│ IDENTITY                                                     │
│ • Personal ID: 900312-5678                                   │
│ • Nationality: Sweden                                        │
│ • Passport: 87654321                                         │
│                                                              │
│ EMERGENCY CONTACT                                            │
│ • Name: Sara Johansson (Spouse)                             │
│ • Phone: +46 70 345 6789                                     │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ SKILLS & EXPERIENCE                                          │
│ • Sound Mixer (Senior)                                       │
│ • Audio Engineer (Mid)                                       │
│                                                              │
│ LANGUAGES                                                    │
│ • Swedish (Native)                                           │
│ • English (Fluent)                                           │
│                                                              │
│ PREFERENCES                                                  │
│ • Food: No special requirements                              │
│ • Clothing: Shirt L, Pants 34, Shoes 43                     │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ ✓ All required fields completed                             │
│ ✓ Photo uploaded                                             │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ ACTIONS                                                      │
│                                                              │
│ [✓ Approve & Create Profile]                                │
│ [✗ Reject]                                                  │
│ [💬 Request More Info]                                       │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 3: Click "Approve & Create Profile" → Confirmation

┌─────────────────────────────────────────────────────────────┐
│ ✓ Registration approved!                                    │
│                                                              │
│ Profile created for Erik Johansson                           │
│                                                              │
│ Welcome email sent with login link.                          │
│                                                              │
│ Erik can now:                                                │
│ • Set his password                                           │
│ • Log in to crew.pbs.com                                     │
│ • Receive offers from you                                    │
│                                                              │
│ [View Erik's Profile] [Close]                               │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 4: Request More Info Scenario

If something is missing or unclear, Susanne clicks "Request More Info":

┌─────────────────────────────────────────────────────────────┐
│ Request More Information                         [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ What additional information do you need from Erik?           │
│                                                              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ Hi Erik,                                               │ │
│ │                                                        │ │
│ │ Thanks for registering! Could you please provide:     │ │
│ │                                                        │ │
│ │ • Your company/VAT number (if you have one)           │ │
│ │ • References from recent projects                      │ │
│ │                                                        │ │
│ │ Reply to this email with the details.                  │ │
│ │                                                        │ │
│ │ Thanks,                                                │ │
│ │ Susanne                                                │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ [Send Request] [Cancel]                                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Step 5: Reject Scenario

If email was wrong or person not suitable:

┌─────────────────────────────────────────────────────────────┐
│ Reject Registration                              [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ Are you sure you want to reject this registration?           │
│                                                              │
│ Reason (internal note, not sent to applicant):              │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ Wrong email - invited erik@company.com but registered  │ │
│ │ with personal email erik@gmail.com                     │ │
│ └────────────────────────────────────────────────────────┘ │
│                                                              │
│ [Confirm Rejection] [Cancel]                                │
│                                                              │
└─────────────────────────────────────────────────────────────┘

System Actions​

Approve Registration:

-- See UC-12 for complete profile creation SQL

-- Update submission
UPDATE profile_submissions
SET status = 'approved',
    approved_profile_id = {new_profile_id},
    reviewed_by_user_id = {susanne_user_id},
    reviewed_at = NOW()
WHERE id = {submission_id}

-- Update invitation
UPDATE staff_invitations
SET approved_profile_id = {new_profile_id}
WHERE profile_submission_id = {submission_id}

Request More Info:

UPDATE profile_submissions
SET status = 'more_info_requested'
WHERE id = {submission_id}

-- Send email to applicant with Susanne's message

Reject Registration:

UPDATE profile_submissions
SET status = 'rejected',
    rejection_reason = 'Wrong email - invited erik@company.com...',
    reviewed_by_user_id = {susanne_user_id},
    reviewed_at = NOW()
WHERE id = {submission_id}

UC-10: Handle Scheduling Conflicts​

Goal: Susanne identifies and resolves scheduling conflicts when staff are double-booked or offers overlap.

Actors: Susanne (Admin)

Preconditions:

  • Multiple projects with overlapping dates
  • Attempting to send offer or book directly

System: backend.pbs.com

UI Journey​

Scenario A: Conflict Warning When Sending Offer

(Already shown in UC-04, repeated here for completeness)

Susanne tries to send offer to Lisa for May 10-15, but Lisa has assignment May 12-14.

┌─────────────────────────────────────────────────────────────┐
│ Create Offer for Lisa Berg                       [✕ Close] │
├──────────────────────────────────────────────────────────────
│                                                              │
│ ⚠ Conflict Warning                                          │
│ Lisa has an existing assignment May 12-14 (Football Match). │
│ Continue anyway? [Yes, send offer] [Cancel]                 │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Scenario B: View All Conflicts for a Person

Susanne clicks on Lisa's profile to see her schedule:

┌─────────────────────────────────────────────────────────────┐
│ Lisa Berg - Schedule & Availability                          │
├──────────────────────────────────────────────────────────────
│                                                              │
│ ═══ MAY 2025 CALENDAR ═══════════════════════════════════    │
│                                                              │
│  Mon  Tue  Wed  Thu  Fri  Sat  Sun                          │
│               1    2    3    4    5                          │
│   6    7    8    9   [10] [11] [12]                         │
│ [13] [14] [15]  16   17   18   19                           │
│  20   21   22   23   24   25   26                           │
│  27   28   29   30   31                                      │
│                                                              │
│ Legend:                                                      │
│ [XX] Confirmed assignment                                    │
│ (XX) Pending offer                                           │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ CONFIRMED ASSIGNMENTS (2)                                    │
│                                                              │
│ • Football Match - May 12-14 (EVS Operator)                 │
│   Status: Confirmed                                          │
│                                                              │
│ • Champions League Final - May 10-15 (EVS Operator)         │
│   Status: Confirmed                                          │
│   ⚠ OVERLAPS with Football Match                            │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ PENDING OFFERS (1)                                           │
│                                                              │
│ • Ice Hockey Finals - May 8-10 (EVS Operator)               │
│   Status: Awaiting Lisa's response                           │
│   ⚠ OVERLAPS with Champions League (if accepted)            │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ ACTIONS                                                      │
│                                                              │
│ [Resolve Conflicts] [Contact Lisa] [View Projects]          │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Scenario C: Conflict Resolution Options

Susanne clicks "Resolve Conflicts":

┌─────────────────────────────────────────────────────────────┐
│ Resolve Scheduling Conflicts                     [✕ Close]  │
├──────────────────────────────────────────────────────────────
│                                                              │
│ Lisa Berg has overlapping assignments:                       │
│                                                              │
│ CONFLICT 1:                                                  │
│ • Champions League Final: May 10-15                         │
│ • Football Match: May 12-14                                  │
│ • Overlap: May 12-14 (3 days)                               │
│                                                              │
│ Resolution options:                                          │
│ ○ Contact Lisa to choose one project                         │
│ ○ Cancel Football Match assignment                          │
│ ○ Cancel Champions League assignment                         │
│ ○ Leave as-is (Lisa will handle it)                         │
│                                                              │
│ ─────────────────────────────────────────────────────────   │
│                                                              │
│ [Send Message to Lisa] [Cancel Assignment] [Mark Resolved]  │
│                                                              │
└─────────────────────────────────────────────────────────────┘

System Actions​

Conflict Detection Query (run when sending offer or viewing schedule):

SELECT
  a1.*,
  p1.name AS project1_name,
  a2.*,
  p2.name AS project2_name,
  -- Calculate overlap
  GREATEST(a1.start_date, a2.start_date) AS overlap_start,
  LEAST(a1.end_date, a2.end_date) AS overlap_end,
  (LEAST(a1.end_date, a2.end_date) - GREATEST(a1.start_date, a2.start_date) + 1) AS overlap_days
FROM assignments a1
JOIN project_functions pf1 ON a1.project_function_id = pf1.id
JOIN projects p1 ON pf1.project_id = p1.id,
     assignments a2
JOIN project_functions pf2 ON a2.project_function_id = pf2.id
JOIN projects p2 ON pf2.project_id = p2.id
WHERE a1.profile_id = {lisa_profile_id}
  AND a2.profile_id = {lisa_profile_id}
  AND a1.id != a2.id
  AND (a1.start_date, a1.end_date) OVERLAPS (a2.start_date, a2.end_date)
  AND a1.current_status NOT IN ('cancelled')
  AND a2.current_status NOT IN ('cancelled')
ORDER BY a1.start_date

Include Pending Offers in Conflict Check:

-- Check both confirmed assignments AND pending offers
SELECT * FROM assignment_offers
WHERE profile_id = {lisa_profile_id}
  AND status IN ('pending', 'accepted', 'negotiating')
  AND EXISTS (
    SELECT 1 FROM project_functions pf
    JOIN projects p ON pf.project_id = p.id
    WHERE pf.id = assignment_offers.project_function_id
      AND (p.start_date, p.end_date) OVERLAPS ({new_offer_start}, {new_offer_end})
  )

Summary: Operations & Reports​

UC-07: Generate Reports
├─→ Crew List: Names, roles, contact info, photos
├─→ Meal List: Food preferences for catering
├─→ Clothing Sizes: For uniform ordering
├─→ Hotel Bookings: All reservations
└─→ Budget Report: Planned vs actual costs (post-completion)

UC-08: Invite Staff
├─→ Enter email addresses
├─→ Set auto-approve or manual review
└─→ System sends invitation emails with registration links

UC-09: Review Registrations
├─→ View pending submissions
├─→ Review all details (profile, photo, skills)
├─→ Approve → Create profile + send welcome email
├─→ Request More Info → Ask for clarification
└─→ Reject → Mark rejected (for wrong email cases)

UC-10: Handle Conflicts
├─→ Soft warnings when sending offers
├─→ View crew schedule with conflict highlights
└─→ Resolution options: Contact crew, cancel assignment, or leave as-is

Key MVP Features Demonstrated​

  1. Fixed report templates: No customization for MVP, keeps it simple
  2. Company-wide report access: All admins can generate/view reports
  3. Invite-only registration: No public forms, controlled onboarding
  4. Manual review by default: Susanne controls who gets approved
  5. Soft conflict detection: Warnings, not hard blocks
  6. Budget tracking foundation: Tracks planned vs actual for future invoicing

Database Tables Used​

  • exports - Generated reports
  • staff_invitations - Invitation tracking
  • profile_submissions - Registration data
  • profiles - Staff directory
  • users - Account creation
  • assignments - For conflict detection, budget reports
  • projects - Project data, financial finalization
  • travel_bookings - For travel reports
  • hotel_bookings - For hotel reports