PBS - Entities Reference for Product Managers

Document Version: 2.0 Last Updated: 2025-10-22 Purpose: Non-technical explanation of all database entities, their properties, and relationships

System Overview
Entity Domains
Detailed Entity Reference
Relationship Summary
Data Flow Examples

System Overview

PBS organizes data into 7 main domains:

Company & Access ────┐
People (Staff)       ├──> Core System
Projects & Teams     │
Assignments          ┘
Logistics
Staff Pool (Intake)
Documents & Exports

Hierarchy:

PBS Platform
 └─ Company (Your customer, e.g., NCP)
     ├─ Users (Login accounts - Admins and Staff)
     ├─ Profiles (Staff directory - all crew members)
     ├─ Function Catalog (Roles: Camera Op, Sound Mixer, etc.)
     ├─ Projects
     │   ├─ Teams (Optional groups within project)
     │   ├─ Project Functions (Required roles for project)
     │   │   └─ Assignment Offers (PM sends to multiple people)
     │   │       └─ Assignment (Confirmed booking - one winner)
     │   │           ├─ Travel Bookings
     │   │           ├─ Hotel Bookings
     │   │           └─ Documents (Contracts, NDAs, Road Books)
     │   └─ Exports (Reports: crew lists, meal lists, etc.)
     └─ Staff Pool Links (Public intake forms)
         └─ Profile Submissions (Pending approvals)

Entity Domains

Domain 1: Company & Access Control

Purpose: Manage customer accounts and user permissions

Entity	Purpose	Key Concept
Companies	Your customers (e.g., NCP)	Top-level tenant - all data isolated per company
Users	Login accounts	Can be Admin (company user) or Staff (crew member self-service)
Project ID Settings	Project code generation rules	How to auto-generate project codes (e.g., ESC-001, ESC-002)

Domain 2: People (Staff & Freelancers)

Purpose: Manage your crew directory and their skills

Entity	Purpose	Key Concept
Profiles	Staff directory	All crew members - employees, freelancers, contractors
Functions Catalog	Available roles/positions	Camera Operator, Sound Mixer, Producer, etc.
Profile Functions	Who can do what	Links profiles to functions (many-to-many relationship)

Domain 3: Projects & Teams

Purpose: Define your events and required roles

Entity	Purpose	Key Concept
Projects	Your events/productions	Eurovision 2025, Sports Broadcast, etc.
Project Teams	Sub-groups within project	Optional - e.g., "OB Team 1", "Audio Crew"
Project Functions	Required roles for project	"Need 3x Camera Operators for 10 work days + 2 travel days"

Domain 4: Assignments (The Booking Process)

Purpose: Assign people to projects (offer → negotiation → confirmation)

Entity	Purpose	Key Concept
Assignment Offers	Proposals to staff	PM sends to multiple people for same slot, with individual rates
Assignments	Confirmed bookings	One person locked to one project function (the winner)
Assignment Status Logs	Lifecycle tracking	Timeline: confirmed → contract → travel → completed

Domain 5: Logistics

Purpose: Track travel and accommodation

Entity	Purpose	Key Concept
Travel Bookings	Flight/train/car details	Per assignment - manually entered for MVP
Hotel Bookings	Hotel stays	Per assignment - check-in/out dates, costs

Domain 6: Staff Pool & Intake

Purpose: Collect freelancer data via public forms

Entity	Purpose	Key Concept
Staff Pool Links	Public intake forms	Shareable URLs (e.g., /pool/esc2025) for freelancers to submit info
Profile Submissions	Incoming applications	Pending review - approved submissions become Profiles

Domain 7: Documents & Exports

Purpose: File management and reporting

Entity	Purpose	Key Concept
Documents	File attachments	Can attach to Profiles (photos, passports) or Assignments (contracts, NDAs)
Exports	Generated reports	Crew lists, meal lists, hotel bookings, clothing sizes

Detailed Entity Reference

1. Companies

What it is: Your customers - the organizations that use PBS (e.g., NCP, SVT Sports, etc.)

Why it exists: Multi-tenant isolation - each company's data is completely separate

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	`550e8400-e29b-41d4-a716-446655440000`
`name`	Text	Yes	Company name	"New Century Production"
`org_number`	Text	No	Business registration number	"556677-8899"
`default_currency`	Code (3 chars)	No	ISO currency code	"SEK", "EUR"
`settings`	JSON	No	Company-wide settings	`{"timezone": "Europe/Stockholm", "locale": "sv_SE"}`
`created_at`	Timestamp	Auto	When company was created	2025-01-15 10:30:00
`updated_at`	Timestamp	Auto	Last modified	2025-01-20 14:22:00

Relationships:

Has many: Users, Profiles, Projects, Function Catalog, Staff Pool Links
Has one: Project ID Settings

Business Rules:

✓ One company per customer
✓ All data scoped to company (profiles, projects, etc.)
✓ MVP: Only one company, but schema supports multiple

2. Users

What it is: Login accounts for accessing the system

Why it exists: Authentication and role-based permissions

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`company_id`	UUID	Yes	Which company they belong to	Links to Companies
`email`	Email	Yes	Login email (unique)	"anna@ncp.se"
`password`	Encrypted	Yes	Hashed password	-
`name`	Text	Yes	Display name	"Anna Andersson"
`role`	Enum	Yes	User role	"ADMIN", "CREW", "PROJECT_MANAGER", etc.
`profile_id`	UUID	No	Link to Profile (if CREW role)	For crew self-service portal
`email_verified_at`	Timestamp	No	Email verification	-
`last_login_at`	Timestamp	No	Last login time	2025-10-22 09:15:00

User Roles:

Role	MVP?	Description	Typical User
`ADMIN`	✓ Yes	Full access to company settings and all projects	NCP Project Manager
`CREW`	✓ Yes	Self-service portal - view/edit own profile, see assignments	Freelancer/crew member
`PROJECT_MANAGER`	Future	Can create/manage projects, assign staff	-
`TEAM_LEAD`	Future	Limited to their team(s)	-
`VIEWER`	Future	Read-only access	Client stakeholder

Relationships:

Belongs to: Company
Optionally belongs to: Profile (if role = CREW)

Business Rules:

✓ Email must be unique across entire system
✓ CREW role users must have a profile_id (links to their crew profile)
✓ ADMIN role users typically don't have a profile_id

3. Project ID Settings

What it is: Rules for generating project codes

Why it exists: Auto-generate project codes like "ESC-001", "ESC-002" or allow manual entry

Properties:

Property	Type	Required?	Description	Example
`company_id`	UUID	Yes (PK)	One-to-one with company	-
`mode`	Enum	Yes	Auto or manual generation	"auto", "manual"
`prefix`	Text	No	Code prefix	"ESC", "SPORTS", "PROD"
`sequence_number`	Integer	No	Auto-increment counter	1, 2, 3...

Examples:

Mode	Prefix	Sequence	Result
auto	"ESC"	1	"ESC-001"
auto	"ESC"	2	"ESC-002"
auto	null	42	"PROJECT-042"
manual	-	-	User types any code

Relationships:

Belongs to: Company (one-to-one)

4. Profiles

What it is: Staff directory - all crew members (employees, freelancers, contractors)

Why it exists: Centralized database of everyone who can be assigned to projects

Properties:

Property	Type	Required?	Description	Example
Identity
`id`	UUID	Yes	Unique identifier	-
`company_id`	UUID	Yes	Which company they belong to	-
`type`	Enum	Yes	Staff type (MVP: use 'staff' for all)	"staff", "employee", "freelancer"
`first_name`	Text	Yes	First name	"Anna"
`last_name`	Text	Yes	Last name	"Andersson"
`email`	Email	No	Contact email	"anna.andersson@email.com"
`phone`	Text	No	Phone number	"+46 70 123 4567"

Address & Identity
`address`	Text	No	Full postal address	"Kungsgatan 12, 111 43 Stockholm"
`personal_id_number`	Text	No	Personnummer or national ID	"19850315-1234"
`nationality`	Code	No	ISO country code	"SE", "NO", "DK"
`passport_number`	Text	No	Passport number	"AB1234567"
`home_base_city`	Text	No	Departure city for travel	"Stockholm", "Oslo"

Emergency Contact
`emergency_contact_name`	Text	No	Emergency contact name	"Erik Andersson"
`emergency_contact_phone`	Text	No	Emergency phone	"+46 70 999 8888"
`emergency_contact_relation`	Text	No	Relationship	"Spouse", "Parent", "Sibling"

Freelancer-Specific (only if type='freelancer')
`org_number`	Text	No	Company/VAT number	"556688-9900"
`invoicing_email`	Email	No	Separate email for invoices	"faktura@company.se"
`invoicing_address`	Text	No	Billing address	"Box 123, 100 00 Stockholm"

Visual
`photo_url`	URL	No	Profile photo for crew passes	"/storage/profiles/123/photo.jpg"

Flexible Attributes (JSON)
`languages`	JSON	No	Languages spoken	`[{"code":"en","proficiency":"fluent"},{"code":"sv","proficiency":"native"}]`
`food_preferences`	JSON	No	Dietary restrictions, allergies	`{"allergies":["peanuts","shellfish"],"diet":"vegetarian"}`
`clothing_sizes`	JSON	No	Clothing sizes	`{"shirt":"M","pants":"32","shoes":"42"}`

Metadata
`notes`	Text	No	Internal notes (not visible to staff)	"Preferred for Eurovision projects"
`availability_status`	Enum	No	Availability	"available", "busy", "unavailable"
`created_at`	Timestamp	Auto	When created	-
`updated_at`	Timestamp	Auto	Last modified	-

Staff Self-Service Portal (what staff can view/edit):

✓ Can edit: Basic info (name, email, phone), photo, emergency contact, languages, food preferences, clothing sizes
✗ Cannot edit: Type, company assignment, notes (internal), availability_status (PM manages)
✓ Future: Upload passport, view their assignments

Relationships:

Belongs to: Company
Has many: Profile Functions (skills), Assignments, Documents (photos, passports)
Can have: User account (for self-service portal login)

Business Rules:

✓ MVP: Use type='staff' for everyone (no employee/freelancer distinction yet)
✓ Future: Type 'freelancer' requires org_number and invoicing details
✓ Photo required for crew passes/accreditation
✓ Languages stored as array of objects with proficiency levels

Example Profile JSON:

{
  "first_name": "Anna",
  "last_name": "Andersson",
  "email": "anna@email.com",
  "phone": "+46 70 123 4567",
  "address": "Kungsgatan 12, Stockholm",
  "personal_id_number": "19850315-1234",
  "nationality": "SE",
  "home_base_city": "Stockholm",
  "languages": [
    {"code": "sv", "proficiency": "native"},
    {"code": "en", "proficiency": "fluent"},
    {"code": "no", "proficiency": "basic"}
  ],
  "food_preferences": {
    "allergies": ["peanuts"],
    "diet": "vegetarian",
    "notes": "Prefers gluten-free options"
  },
  "clothing_sizes": {
    "shirt": "M",
    "pants": "32",
    "shoes": "42",
    "jacket": "L"
  }
}

5. Functions Catalog

What it is: Company's catalog of available roles/positions

Why it exists: Define what job functions exist and their typical rates

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`company_id`	UUID	Yes	Which company this belongs to	-
`department`	Text	No	Grouping/category	"Camera", "Audio", "Production"
`name`	Text	Yes	Function name	"Camera Operator", "Sound Mixer"
`description`	Text	No	Job description	"Operates broadcast cameras during live events"
`avg_work_rate_cents`	Integer	No	Average rate for work days (in cents)	50000 (= €500.00)
`avg_travel_rate_cents`	Integer	No	Average rate for travel days (in cents)	20000 (= €200.00)
`avg_rate_currency`	Code	No	Currency for rates	"EUR", "SEK"
`is_custom`	Boolean	No	Standard or custom function	true/false

Why work vs travel rates?

Work days = actual event days (full rate)
Travel days = just traveling to/from event (lower rate, often 40-50% of work rate)

Standard vs Custom Functions:

Standard (is_custom=false): Pre-loaded catalog (Camera Op, Sound Mixer, Gaffer, etc.) - same for all companies
Custom (is_custom=true): Company-created roles for their specific needs

Relationships:

Belongs to: Company
Used by: Project Functions (what roles projects need)
Linked to profiles via: Profile Functions (who can do this role)

Business Rules:

✓ SaaS: Standard catalog provided, companies can add custom functions
✓ Average rates used for budget estimation only (actual rates set per assignment offer)
✓ Travel rate typically 40-50% of work rate

Example Functions:

Department	Name	Avg Work Rate	Avg Travel Rate	Is Custom
Camera	Camera Operator	€500/day	€200/day	false
Audio	Sound Mixer	€450/day	€180/day	false
Production	Line Producer	€600/day	€250/day	false
Custom	Eurovision Stage Manager	€550/day	€220/day	true

6. Profile Functions

What it is: Junction table - which profiles can perform which functions

Why it exists: Many-to-many relationship (one profile can do multiple functions, one function can be done by many profiles)

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`profile_id`	UUID	Yes	Which person	-
`function_id`	UUID	Yes	Which role	-
`seniority`	Enum	No	Experience level	"junior", "mid", "senior", "expert"

Example:

Anna Andersson can do:
- Camera Operator (seniority: senior)
- Drone Pilot (seniority: mid)

Björn Svensson can do:
- Camera Operator (seniority: junior)
- Sound Assistant (seniority: mid)

Relationships:

Links: Profile ↔ Function Catalog

Business Rules:

✓ One profile can have multiple functions
✓ Seniority affects rate negotiation (senior = higher rates)
✓ Used for searching: "Find all senior Camera Operators"

7. Projects

What it is: Your events/productions (Eurovision, sports broadcasts, conferences, etc.)

Why it exists: The top-level container for all staffing work

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`company_id`	UUID	Yes	Which company owns this project	-
`project_code`	Text	Yes	Unique code (auto or manual)	"ESC-2025-001"
`name`	Text	Yes	Project name	"Eurovision Song Contest 2025"
`client_name`	Text	No	External client (if different from company)	"EBU"
`status`	Enum	Yes	Project lifecycle	"planning", "confirmed", "completed"
`start_date`	Date	No	Project start date	2025-05-10
`end_date`	Date	No	Project end date	2025-05-15
`budget_cents`	Integer	No	Total budget in cents	5000000 (= €50,000)
`budget_currency`	Code	No	Budget currency	"EUR"
`location_city`	Text	No	Event city	"Malmö"
`location_country`	Code	No	Country code	"SE"
`notes`	Text	No	Internal notes	"Largest production of the year"

Project Status Lifecycle:

Status	Meaning	Typical Duration
`planning`	Planning phase - defining requirements	Weeks/months
`confirmed`	Project confirmed - crew assigned, event happening	Days/weeks
`completed`	Event finished, ready for financial review	-

Relationships:

Belongs to: Company
Has many: Project Teams, Project Functions, Exports
Has many (indirect): Assignments (via project functions)

Business Rules:

✓ Project code must be unique across company
✓ Dates optional (planning phase projects may not have dates yet)
✓ Budget optional (used for cost tracking comparison)

8. Project Teams

What it is: Sub-groups within a project (e.g., "Camera Crew", "Audio Team", "OB Team 1")

Why it exists: Organize large crews into manageable teams (OPTIONAL - not all projects use teams)

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`project_id`	UUID	Yes	Which project	-
`name`	Text	Yes	Team name	"OB Team 1", "Audio Crew"
`color`	Hex code	No	UI color for visual grouping	"#FF5733"
`sort_order`	Integer	No	Display order	1, 2, 3...

When to use teams:

✓ Large projects with 50+ people
✓ Multiple parallel crews (e.g., OB Team 1 works stadium, OB Team 2 works arena)
✗ Small projects can skip teams (flat list of roles)

Relationships:

Belongs to: Project
Has many: Project Functions (roles assigned to this team)

Business Rules:

✓ Teams are completely optional
✓ If used, project functions can be grouped by team
✓ Visual: Different colors help distinguish teams in UI

9. Project Functions

What it is: Required roles for a project (e.g., "Need 3 Camera Operators for 10 work days + 2 travel days")

Why it exists: Define staffing requirements before assigning specific people

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`project_id`	UUID	Yes	Which project	-
`team_id`	UUID	No	Which team (optional)	-
`function_id`	UUID	Yes	Which role from catalog	"Camera Operator"
`work_days`	Integer	Yes	Number of work days	10
`travel_days`	Integer	Yes	Number of travel days	2
`quantity`	Integer	Yes	How many people needed	3 (need 3 Camera Ops)
`target_rate_cents`	Integer	No	Budget target rate (work day)	50000 (= €500)
`target_rate_currency`	Code	No	Currency	"EUR"
`notes`	Text	No	Requirements	"Must speak Swedish, own drone license preferred"

Example:

Project: Eurovision 2025
Function: Camera Operator
- Quantity: 3 people
- Work days: 10
- Travel days: 2
- Target rate: €500/work day
- Team: OB Team 1

This means: "We need 3 Camera Operators, each working 10 days + 2 travel days,
budget is €500/day per person"

Relationships:

Belongs to: Project
Optionally belongs to: Project Team
References: Function Catalog (which role)
Has many: Assignment Offers (PM sends offers for this slot)
Has one: Assignment (the confirmed booking)

Business Rules:

✓ If quantity > 1, PM creates multiple assignment offers/assignments
✓ Target rate is for budgeting only (actual rates negotiated per person)
✓ Work + travel days define project duration per person

10. Assignment Offers

What it is: Proposals from PM to specific people for specific roles

Why it exists: PM can send offers to multiple candidates for same slot, with individual rates, and support negotiation

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`project_function_id`	UUID	Yes	Which slot/role	-
`profile_id`	UUID	Yes	Who this offer is for	-
`work_day_rate_cents`	Integer	Yes	Offered rate for work days (cents)	50000 (= €500)
`travel_day_rate_cents`	Integer	Yes	Offered rate for travel days (cents)	20000 (= €200)
`rate_currency`	Code	Yes	Currency	"EUR", "SEK"
`departure_city`	Text	No	Where they'll depart from	"Stockholm"
`status`	Enum	Yes	Offer status	"pending", "accepted", "declined", "negotiating", "withdrawn", "converted"
`pm_notes`	Text	No	Internal PM notes	"First choice candidate"
`staff_response`	Text	No	Message from staff	"Can do it but rate too low"
`counter_work_rate_cents`	Integer	No	Staff counter-offer (work days)	55000 (= €550)
`counter_travel_rate_cents`	Integer	No	Staff counter-offer (travel days)	22000 (= €220)
`sent_at`	Timestamp	No	When offer was sent	2025-05-01 10:00
`responded_at`	Timestamp	No	When staff responded	2025-05-01 15:30
`expires_at`	Timestamp	No	Response deadline	2025-05-03 23:59
`assignment_id`	UUID	No	Link to final assignment (if converted)	-
`created_by_user_id`	UUID	Yes	Which PM created this	-

Offer Status Workflow:

pending ──────────┐
                  ├──> accepted ────┐
                  ├──> declined     │
                  └──> negotiating ─┤──> converted (PM confirms) → Creates Assignment
                                     │
withdrawn ◄────────────────────────┘

Example Scenario:

Project Function: Camera Operator for Eurovision (1 person needed)

PM creates 3 offers:

Offer #1 to Anna:
- Work rate: €500/day
- Travel rate: €200/day
- Status: accepted

Offer #2 to Björn:
- Work rate: €450/day
- Travel rate: €180/day
- Status: declined (unavailable)

Offer #3 to Clara:
- Work rate: €550/day
- Travel rate: €220/day
- Status: negotiating
- Counter-offer: €600/day work, €250/day travel

PM Decision: Accept Anna's offer at original rate
Result: Offer #1 converted to Assignment, others withdrawn

Relationships:

Belongs to: Project Function (which slot)
Belongs to: Profile (who)
Can create: Assignment (when PM confirms)
Created by: User (PM)

Business Rules:

✓ Multiple offers allowed for same project_function (competing candidates)
✓ Only one offer can be converted to assignment per slot
✓ Rates can differ per person (PM sets individually)
✓ Staff can negotiate (counter-offer)
✓ PM manually picks winner

11. Assignments

What it is: Confirmed bookings - one person locked to one project role

Why it exists: The final, confirmed staffing decision (created when PM accepts an offer)

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`project_function_id`	UUID	Yes	Which role	-
`profile_id`	UUID	Yes	Who is assigned	-
`work_day_rate_cents`	Integer	Yes	Agreed work rate (cents)	50000 (= €500)
`travel_day_rate_cents`	Integer	Yes	Agreed travel rate (cents)	20000 (= €200)
`rate_currency`	Code	Yes	Currency	"EUR"
`departure_city`	Text	No	Departure city	"Stockholm"
`current_status`	Enum	Yes	Lifecycle status	"confirmed", "contract_sent", "active", etc.
`start_date`	Date	No	Assignment start (for conflict detection)	2025-05-10
`end_date`	Date	No	Assignment end (for conflict detection)	2025-05-15
`notes`	Text	No	Internal notes	-

Assignment Status Lifecycle (Fixed workflow):

confirmed → contract_sent → contract_signed → travel_booked →
hotel_booked → active → completed

(Can jump to 'cancelled' at any point)

Status Descriptions:

Status	Meaning	Typical Actions
`confirmed`	PM has accepted offer, booking confirmed	Send confirmation email
`contract_sent`	Contract/NDA sent to staff	Wait for signature
`contract_signed`	Contract returned, signed	File document
`travel_booked`	Travel arrangements made	Record booking details
`hotel_booked`	Hotel reservation made	Record booking details
`active`	Project is happening now, person is on-site	-
`completed`	Project finished, person returned	Prepare invoicing
`cancelled`	Assignment cancelled (any reason)	-

Relationships:

Belongs to: Project Function (which role)
Belongs to: Profile (who)
Has many: Status Logs (timeline), Travel Bookings, Hotel Bookings, Documents (contracts, NDAs, road books)
Created from: Assignment Offer (when PM converts)

Business Rules:

✓ One assignment per project_function slot (quantity > 1 = multiple assignments)
✓ Start/end dates used for conflict detection
✓ Status workflow is fixed (not customizable per company)
✓ Rates locked in (no more negotiation after confirmation)

Conflict Detection Example:

Anna is assigned to:
- Eurovision (May 10-15)
- Sports Broadcast (May 14-18)  ← CONFLICT WARNING!

System warns PM: "Anna already assigned to Eurovision during overlapping dates"

12. Assignment Status Logs

What it is: Audit trail for assignment lifecycle

Why it exists: Track timeline and changes (who did what, when)

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`assignment_id`	UUID	Yes	Which assignment	-
`status`	Enum	Yes	Status at this point	"contract_sent"
`note`	Text	No	Context for change	"Sent via DocuSign"
`changed_by_user_id`	UUID	No	Who made the change	-
`created_at`	Timestamp	Yes	When change happened	2025-05-02 14:30:00

Example Timeline:

Assignment for Anna / Camera Operator:

2025-05-01 10:00  confirmed          (by PM Maria)     "Accepted at €500/day"
2025-05-01 15:00  contract_sent      (by PM Maria)     "Contract sent via email"
2025-05-02 09:00  contract_signed    (by PM Maria)     "Received signed PDF"
2025-05-03 11:00  travel_booked      (by Admin Karin)  "SAS flight SK1234"
2025-05-03 14:00  hotel_booked       (by Admin Karin)  "Scandic Hotel, 3 nights"
2025-05-10 08:00  active             (by PM Maria)     "Project started"
2025-05-15 18:00  completed          (by PM Maria)     "All good!"

Relationships:

Belongs to: Assignment
Changed by: User (optional)

Business Rules:

✓ Append-only log (never delete/edit entries)
✓ Automatic: System records when status changes
✓ Timeline view: Show full history to PM

13. Travel Bookings

What it is: Travel details per assignment (flights, trains, cars)

Why it exists: Track and manage crew transportation

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`assignment_id`	UUID	Yes	Which assignment	-
`type`	Enum	Yes	Travel mode	"flight", "train", "car", "other"
`from_city`	Text	No	Departure city	"Stockholm"
`to_city`	Text	No	Arrival city	"Malmö"
`departure_at`	Timestamp	No	Departure time	2025-05-10 07:30
`arrival_at`	Timestamp	No	Arrival time	2025-05-10 08:45
`booking_reference`	Text	No	Booking/ticket number	"SAS-ABC123"
`cost_cents`	Integer	No	Cost in cents	89900 (= €899)
`cost_currency`	Code	No	Currency	"SEK"
`notes`	Text	No	Additional info	"Window seat requested"

MVP Note: Manually entered by PM/admin (no flight API integration yet)

Relationships:

Belongs to: Assignment (one assignment can have multiple travel bookings, e.g., outbound + return)

Business Rules:

✓ MVP: Manual entry only
✓ One assignment can have multiple travel bookings (outbound, return, mid-project transfers)
✓ Used for reports: Travel overview per project

14. Hotel Bookings

What it is: Hotel accommodation details per assignment

Why it exists: Track crew lodging

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`assignment_id`	UUID	Yes	Which assignment	-
`hotel_name`	Text	No	Hotel name	"Scandic Malmö City"
`check_in_date`	Date	No	Check-in	2025-05-10
`check_out_date`	Date	No	Check-out	2025-05-15
`nights`	Integer	Yes	Number of nights	5
`rate_per_night_cents`	Integer	No	Nightly rate (cents)	120000 (= €1,200)
`total_cost_cents`	Integer	No	Total cost (cents)	600000 (= €6,000)
`cost_currency`	Code	No	Currency	"SEK"
`booking_reference`	Text	No	Confirmation number	"BOOK-12345"
`notes`	Text	No	Special requests	"Breakfast included"

MVP Note: Manually entered by PM/admin

Relationships:

Belongs to: Assignment

Business Rules:

✓ One assignment = one hotel booking (typically)
✓ Used for reports: Hotel booking overview, rooming lists

15. Staff Pool Links

What it is: Public intake forms for collecting freelancer data

Why it exists: Allow external people to submit their info via shareable URL (e.g., /pool/esc2025)

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`company_id`	UUID	Yes	Which company	-
`slug`	Text	Yes	URL slug (unique)	"esc2025", "sports-crew"
`title`	Text	Yes	Form title	"Eurovision 2025 Crew Application"
`welcome_text`	Text (Markdown)	No	Intro message	"We're looking for experienced broadcast crew..."
`enabled_fields`	JSON	Yes	Which fields to show	`["name","email","phone","functions","languages"]`
`allowed_functions`	JSON	No	Which roles can be selected	`["camera-op-id","sound-mixer-id"]`
`is_active`	Boolean	No	Form accepting submissions?	true/false
`expires_at`	Timestamp	No	Optional deadline	2025-04-01 23:59

Example Public Form URL: https://pbs.app/pool/esc2025

Form Configuration:

{
  "enabled_fields": ["name", "email", "phone", "photo", "functions", "languages", "food_preferences", "clothing_sizes"],
  "allowed_functions": ["camera-op-uuid", "sound-mixer-uuid", "lighting-tech-uuid"]
}

Relationships:

Belongs to: Company
Has many: Profile Submissions (incoming applications)

Business Rules:

✓ Slug must be unique across system
✓ Can be enabled/disabled without deleting
✓ Welcome text supports Markdown formatting
✓ Can restrict which functions applicants can choose

16. Profile Submissions

What it is: Incoming applications from public staff pool forms

Why it exists: Pending review before becoming full profiles

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`staff_pool_link_id`	UUID	Yes	Which form	-
`data`	JSON	Yes	All submitted fields	`{"first_name":"Anna","email":"anna@email.com",...}`
`status`	Enum	Yes	Review status	"pending", "approved", "rejected"
`approved_profile_id`	UUID	No	Link to created profile (if approved)	-
`reviewed_by_user_id`	UUID	No	Who reviewed	-
`reviewed_at`	Timestamp	No	When reviewed	2025-05-01 10:00
`submitted_at`	Timestamp	Yes	When submitted	2025-04-28 14:30

Workflow:

Freelancer fills form → submission created (status: pending)
PM reviews submission → approves or rejects
If approved → Profile created, submission linked

Example Submission Data:

{
  "first_name": "Anna",
  "last_name": "Andersson",
  "email": "anna@example.com",
  "phone": "+46 70 123 4567",
  "functions": ["camera-op-uuid"],
  "languages": [{"code":"sv","proficiency":"native"},{"code":"en","proficiency":"fluent"}],
  "food_preferences": {"diet":"vegetarian"},
  "clothing_sizes": {"shirt":"M","pants":"32"}
}

Relationships:

Belongs to: Staff Pool Link
Can create: Profile (when approved)
Reviewed by: User

Business Rules:

✓ Pending submissions await review
✓ Approved → System creates Profile with submitted data
✓ Rejected → Submission kept for record, no profile created

17. Documents

What it is: File attachments (PDFs, images, etc.)

Why it exists: Store documents related to profiles or assignments

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`documentable_type`	Enum	Yes	What it attaches to	"Profile", "Assignment"
`documentable_id`	UUID	Yes	Which profile/assignment	-
`doc_type`	Enum	Yes	Document category	"photo", "passport", "contract", "nda", "road_book", etc.
`file_url`	URL/Path	Yes	File location	"/storage/profiles/123/photo.jpg"
`file_name`	Text	Yes	Original filename	"anna_photo.jpg"
`file_size_bytes`	Integer	No	File size	245678
`mime_type`	Text	No	File type	"image/jpeg", "application/pdf"
`expires_at`	Date	No	Expiration (for passports, IDs)	2030-12-31
`uploaded_by_user_id`	UUID	No	Who uploaded	-
`created_at`	Timestamp	Auto	Upload time	-

Polymorphic Relationship (can attach to different entities):

Profile Documents:

photo - Crew pass photo, accreditation badge
passport - Passport copy (future: for accreditation)
id_card - National ID

Assignment Documents:

contract - Employment contract
nda - Non-disclosure agreement
road_book - Individual travel planning document
accreditation - Event access pass

Example:

Profile: Anna Andersson
- Document #1: type=photo, file=anna_photo.jpg
- Document #2: type=passport, file=anna_passport.pdf, expires=2030-05-15

Assignment: Anna → Eurovision Camera Op
- Document #3: type=contract, file=contract_anna_esc2025.pdf
- Document #4: type=nda, file=nda_signed.pdf
- Document #5: type=road_book, file=roadbook_anna.pdf

Relationships:

Polymorphic belongs to: Profile OR Assignment
Uploaded by: User

Business Rules:

✓ Staff can upload their own photo via self-service portal
✓ PM/Admin uploads contracts, NDAs
✓ Road book can be uploaded or generated
✓ Passport expiration tracked (alert when expiring)

18. Exports

What it is: Generated reports and export files

Why it exists: Create crew lists, meal lists, hotel overviews, etc.

Properties:

Property	Type	Required?	Description	Example
`id`	UUID	Yes	Unique identifier	-
`project_id`	UUID	Yes	Which project	-
`type`	Enum	Yes	Report type	"crew_list", "hotel_bookings", "meal_list", "clothing_sizes"
`filters`	JSON	No	Export parameters	`{"team_id":"team-1-uuid","date_range":"2025-05-10 to 2025-05-15"}`
`file_url`	URL/Path	Yes	Generated file location	"/storage/exports/crew_list_20250501.pdf"
`file_format`	Enum	Yes	File type	"pdf", "excel", "csv"
`generated_by_user_id`	UUID	No	Who generated	-
`generated_at`	Timestamp	Yes	When generated	2025-05-01 10:30

Export Types (MVP - all critical):

Type	Description	Typical Format
`crew_list`	All assigned staff with contact info, functions	PDF, Excel
`hotel_bookings`	Hotel overview with check-in/out dates	PDF, Excel
`meal_list`	Food preferences, allergies for catering	PDF, Excel
`clothing_sizes`	Sizes for ordering crew uniforms	Excel

Example Filters:

{
  "team_id": "ob-team-1-uuid",
  "include_travel": true,
  "date_range": {
    "start": "2025-05-10",
    "end": "2025-05-15"
  }
}

Relationships:

Belongs to: Project
Generated by: User

Business Rules:

✓ SaaS: Export templates are fixed (not customizable)
✓ Can regenerate exports as data changes
✓ Kept for history (can see old exports)

Relationship Summary

Visual Relationship Map

Companies (1) ──────────> (Many) Users
Companies (1) ──────────> (1) Project ID Settings
Companies (1) ──────────> (Many) Profiles
Companies (1) ──────────> (Many) Functions Catalog
Companies (1) ──────────> (Many) Projects
Companies (1) ──────────> (Many) Staff Pool Links

Profiles (Many) ←──────> (Many) Functions Catalog
  (via Profile Functions - junction table)

Projects (1) ──────────> (Many) Project Teams
Projects (1) ──────────> (Many) Project Functions
Projects (1) ──────────> (Many) Exports

Project Functions (1) ──> (Many) Assignment Offers
Project Functions (1) ──> (1) Assignment (the winner)

Assignment Offers (1) ──> (1) Assignment (when converted)

Assignments (1) ────────> (Many) Assignment Status Logs
Assignments (1) ────────> (Many) Travel Bookings
Assignments (1) ────────> (Many) Hotel Bookings
Assignments (1) ────────> (Many) Documents (contracts, etc.)

Profiles (1) ───────────> (Many) Documents (photos, passports)

Staff Pool Links (1) ───> (Many) Profile Submissions
Profile Submissions (1) -> (1) Profile (when approved)

Data Flow Examples

Example 1: Creating a Project and Staffing It

Step-by-step:

Create Project

Company: NCP
Project: "Eurovision 2025"
Code: ESC-2025-001
Dates: May 10-15, 2025
Location: Malmö, Sweden
Budget: €500,000

Define Required Roles (Project Functions)

Role #1: Camera Operator
- Quantity: 3 people
- Work days: 10
- Travel days: 2
- Target rate: €500/work day, €200/travel day
- Team: OB Team 1

Role #2: Sound Mixer
- Quantity: 2 people
- Work days: 10
- Travel days: 2
- Target rate: €450/work day, €180/travel day
- Team: Audio Team

PM Creates Offers (for Camera Operator slot #1)

Offer A → Anna Andersson
- Work rate: €500/day
- Travel rate: €200/day
- Sent: May 1, 10:00

Offer B → Björn Svensson
- Work rate: €450/day
- Travel rate: €180/day
- Sent: May 1, 10:05

Offer C → Clara Nilsson
- Work rate: €550/day
- Travel rate: €220/day
- Sent: May 1, 10:10

Staff Respond

Anna: ACCEPTED (May 1, 15:00)
Björn: DECLINED (unavailable)
Clara: NEGOTIATING - Counter: €600/day work, €250/day travel

PM Picks Winner

PM accepts Anna's offer (original terms)
→ Assignment created
→ Offers B and C marked as "withdrawn"

Assignment Lifecycle

May 1: confirmed
May 2: contract_sent (contract uploaded)
May 3: contract_signed
May 4: travel_booked (SAS flight recorded)
May 5: hotel_booked (Scandic Malmö, 5 nights)
May 10: active (project started)
May 15: completed

Generate Reports

PM exports:
- Crew list (PDF) - all assigned staff
- Hotel bookings (Excel) - rooming list
- Meal list (PDF) - dietary requirements

Example 2: Staff Self-Service Portal

Freelancer Perspective (Anna's experience):

Receive Public Form Link

Email from NCP: "Join our crew pool for upcoming events"
Link: https://pbs.app/pool/ncp-crew-2025

Fill Out Form

- Name: Anna Andersson
- Email: anna@example.com
- Phone: +46 70 123 4567
- Functions: Camera Operator, Drone Pilot
- Languages: Swedish (native), English (fluent)
- Food: Vegetarian, no peanuts
- Clothing: Shirt M, Pants 32
- Upload photo

Submission Reviewed

PM reviews → Approves
→ Profile created in system
→ Anna receives email: "Welcome! Create your login"

Anna Creates Account

Sets password, logs in
Role: CREW (self-service portal)

Anna Uses Portal

Can view/edit:
- Basic info (email, phone, address)
- Profile photo
- Emergency contact
- Languages
- Food preferences
- Clothing sizes

Can view:
- Her assignments (past and upcoming)
- Pending offers from PMs

Future:
- Upload passport
- Accept/decline offers directly
- View travel/hotel details

Anna Receives Offer

Notification: "You have a new offer for Eurovision 2025"
Offer details:
- Role: Camera Operator
- Dates: May 10-15
- Work rate: €500/day (10 days)
- Travel rate: €200/day (2 days)
- Total: €5,400

Options:
- Accept
- Decline (with reason)
- Negotiate (enter counter-offer)

Anna Accepts

Status: accepted
PM receives notification
PM converts offer → Assignment
Anna can now see:
- Contract document (download/sign)
- Travel details (when booked)
- Hotel info (when booked)

Example 3: Conflict Detection

Scenario: PM tries to assign Anna to overlapping projects

Existing Assignment:
- Project: Eurovision 2025
- Dates: May 10-15

New Assignment Attempt:
- Project: Sports Broadcast
- Dates: May 14-18

System Alert:
⚠️ "Anna Andersson is already assigned to Eurovision 2025 (May 10-15).
   This overlaps with Sports Broadcast (May 14-18).

   Continue anyway? [Yes] [No]"

PM Options:
1. Cancel new assignment (find someone else)
2. Continue anyway (maybe Anna can do both if schedules work out)
3. Adjust dates

Technical: Query checks assignment.start_date and assignment.end_date for overlaps:

WHERE profile_id = anna_id
  AND start_date <= '2025-05-18'
  AND end_date >= '2025-05-14'

Conclusion

This document provides a complete reference for all 18 entities in the PBS system, their properties, relationships, and business rules. Use this alongside:

schema.dbml - Technical database definition
USER_CASES.md - Detailed user stories and workflows (see separate document)
Visual ER Diagram - Generated from DBML at dbdiagram.io

Next Steps:

Review with PM team
Validate all properties and relationships
Confirm business rules match real-world processes
Identify any missing entities or fields
Proceed to user case scenarios document

Table of Contents​

System Overview​

Entity Domains​

Domain 1: Company & Access Control​

Domain 2: People (Staff & Freelancers)​

Domain 3: Projects & Teams​

Domain 4: Assignments (The Booking Process)​

Domain 5: Logistics​

Domain 6: Staff Pool & Intake​

Domain 7: Documents & Exports​

Detailed Entity Reference​

1. Companies​

2. Users​

3. Project ID Settings​

4. Profiles​

5. Functions Catalog​

6. Profile Functions​

7. Projects​

8. Project Teams​

9. Project Functions​

10. Assignment Offers​

11. Assignments​

12. Assignment Status Logs​

13. Travel Bookings​

14. Hotel Bookings​

15. Staff Pool Links​

16. Profile Submissions​

17. Documents​

18. Exports​

Relationship Summary​

Visual Relationship Map​

Data Flow Examples​

Example 1: Creating a Project and Staffing It​

Example 2: Staff Self-Service Portal​

Example 3: Conflict Detection​

Conclusion​

Table of Contents

System Overview

Entity Domains

Domain 1: Company & Access Control

Domain 2: People (Staff & Freelancers)

Domain 3: Projects & Teams

Domain 4: Assignments (The Booking Process)

Domain 5: Logistics

Domain 6: Staff Pool & Intake

Domain 7: Documents & Exports

Detailed Entity Reference

1. Companies

2. Users

3. Project ID Settings

4. Profiles

5. Functions Catalog

6. Profile Functions

7. Projects

8. Project Teams

9. Project Functions

10. Assignment Offers

11. Assignments

12. Assignment Status Logs

13. Travel Bookings

14. Hotel Bookings

15. Staff Pool Links

16. Profile Submissions

17. Documents

18. Exports

Relationship Summary

Visual Relationship Map

Data Flow Examples

Example 1: Creating a Project and Staffing It

Example 2: Staff Self-Service Portal

Example 3: Conflict Detection

Conclusion