Skip to main content

PBS Database Schema

The PBS database schema is defined in DBML (Database Markup Language) format in the file schema.dbml at the project root.

📍 Schema Source File​

Location: /schema.dbml (project root) Format: DBML (Database Markup Language) Lines: 1,300+ lines Last Updated: November 19, 2025 Status: ✅ Planning phase complete

Single Source of Truth

The schema.dbml file is the single source of truth for the database structure. All documentation references this file.

🎨 Visualize the Schema​

  1. Open the schema.dbml file from project root
  2. Copy all contents
  3. Go to dbdiagram.io
  4. Paste into the editor
  5. See instant ER diagram with relationships

Export Options from dbdiagram.io​

  • PDF - For presentations
  • PNG/SVG - For documentation
  • SQL - For direct database creation
  • Share Link - Collaborate with team

📊 Schema Overview​

Quick Stats​

  • Total Tables: 27
  • Primary Keys: UUID-based
  • Multi-tenant: Company-level isolation via company_id
  • Multi-company Crew: Crew can work for multiple companies
  • Polymorphic Relations: Documents table supports attachments to any entity
  • Database: PostgreSQL

Schema Structure​

PBS (Platform)
 ├─── Global Functions (System-wide)
 │
 └─── Companies (Customers: NCP, etc.)
       ├─── Users (Admin login accounts)
       ├─── Exchange Rates (Multi-currency)
       ├─── Functions Catalog (Global + Custom)
       ├─── Company Equipment (OB-vans, servers, etc.)
       │
       ├─── Crew Management (Multi-Company Support)
       │     ├─── Crew Users (One email = one account)
       │     ├─── Crew Profiles (Can exist without account)
       │     ├─── Company-Crew Junction (Private notes, relationships)
       │     ├─── Crew Availability (Staff only)
       │     └─── Staff Invitations (Invite-only registration)
       │
       ├─── Projects (Events, broadcasts)
       │     ├─── Project Functions (Required positions)
       │     ├─── Project Equipment (Equipment bookings)
       │     ├─── Project Budget Items (Generic costs)
       │     │
       │     └─── Assignments (Person → Role bookings)
       │           ├─── Assignment Offers (Rate, dates, negotiation)
       │           ├─── Travel Bookings
       │           ├─── Hotel Bookings
       │           └─── Documents (Contracts, road books)
       │
       ├─── Communication Logs (Audit trail)
       └─── Documents (Polymorphic attachments)

🗄️ Core Tables​

Multi-Tenancy & Global​

  • companies: Production companies (NCP, etc.) - includes company website URL for logo scraping
  • users: Admin login accounts with roles (includes crew_profile_id for dual-role support)
  • global_functions: System-wide function library (Camera Operator, Sound Engineer, etc.)
  • exchange_rates: Manual currency conversion rates per company

Multi-Company Crew System​

  • crew_users: Crew member accounts (one email = one account across all companies)
  • crew_profiles: Person records (can exist without account, claimed later)
  • company_crew: Many-to-many junction (companies ↔ crew, includes private notes)
  • crew_availability: Availability tracking (employed staff only)
  • profile_functions: Crew member skills/roles

Functions & Equipment​

  • functions_catalog: Company function catalog (based on global or custom)
  • company_equipment: Company-owned equipment inventory (OB-vans, GFX servers, etc.)

Projects & Assignments​

  • projects: Champions League Final 2025, etc.
  • project_functions: Required roles for project
  • project_equipment: Equipment bookings with availability tracking
  • project_budget_items: Generic budget line items (rentals, venue costs, etc.)
  • project_teams: Team groups (optional for MVP)
  • assignments: Links crew profiles to project roles
  • assignment_offers: Offers sent to crew with rates/dates

Operations​

  • travel_bookings: Flight/train reservations
  • hotel_bookings: Accommodation arrangements
  • documents: Polymorphic attachments (contracts, travel docs, road books)
  • communication_logs: Full audit trail of all emails/requests sent
  • project_activity_logs: Complete event log for all project activities (NEW in v4.6)

Staff Management​

  • staff_invitations: Invite-only registration system
  • profile_submissions: Registration form submissions
  • exports: Generated reports and export files

🔑 Key Design Decisions​

1. Multi-Company Crew Support [NEW in v4.0]​

Problem Solved: Crew members can work for multiple production companies

Architecture:

  • crew_users: One email = one account across ALL companies
  • crew_profiles: Profile data (no company_id, can exist without account)
  • company_crew: Many-to-many relationship with private notes per company

Account Claiming Workflow:

  1. Admin creates crew_profile without account (claimed_by_user_id = null)
  2. Crew can be booked immediately (no account needed)
  3. Crew registers later → system finds all crew_profiles with matching email
  4. Links all profiles via claimed_by_user_id
  5. Crew can now manage all their data across companies

Data Isolation:

  • Companies cannot see other companies' data
  • Each company has separate private notes about crew (GDPR-sensitive)
  • Crew sees combined view of all their assignments across companies

2. Employment & Availability Tracking [NEW in v4.0]​

Rules:

  • crew_users.employer_id: Only ONE employer (or null for freelancers)
  • Only employer company tracks crew_availability
  • Other companies can still book (staff rental scenario)
  • Freelancers manage their own conflicts (accept/decline)

3. Global + Company Functions [NEW in v4.0]​

Two Types:

  1. Global-based: global_function_id set, company customizes fees
  2. Custom: global_function_id null, company created this function

Usage:

  • default_fee used for initial project budgeting
  • Actual booking fees negotiated per crew member per project
  • Each company sets own fees even for global functions

4. Polymorphic Documents​

The documents table can attach to multiple entity types:

-- Company documents
documentable_type = 'Company'
documentable_id = {company_uuid}

-- Project documents
documentable_type = 'Project'
documentable_id = {project_uuid}

-- Crew profile documents (CV, certifications)
documentable_type = 'CrewProfile'
documentable_id = {crew_profile_uuid}
crew_profile_id = {crew_profile_uuid}  -- Access control

-- Assignment documents (contract, road book)
documentable_type = 'Assignment'
documentable_id = {assignment_uuid}
crew_profile_id = {crew_profile_uuid}  -- Specific crew access

Access Control:

  • Company admins: See all files
  • Crew members: Only see files where crew_profile_id matches their profile
  • Cross-company isolation: Company A cannot see Company B's files about same crew

5. Assignment Offers Flow (Simplified in v4.6)​

Two paths supported:

Standard Offer Flow​

  1. PM creates offer → status: pending
  2. Crew responds:
    • Accept → status: accepted → Assignment created immediately
    • Negotiate → status: negotiating → PM updates rates → Crew accepts → Assignment created
    • Decline → status: declined → Stays declined until PM selects another person
  3. Assignment created → status: confirmed

Key Change: No PM confirmation step - crew acceptance creates assignment automatically.

Direct Booking (Employees)​

  1. PM clicks "Book Directly"
  2. Assignment created immediately with is_direct_booking: true
  3. Confirmation email sent to crew
  4. Assignment status → confirmed

Declined Offers: Remain visible in grid until PM creates new offer for different person.

6. Financial Tracking​

Supports actual vs planned for finalization:

assignments:
  -- Original booking
  planned_work_days: 5
  planned_travel_days: 2
  work_day_rate_cents: 550000  -- 5,500 SEK
  travel_day_rate_cents: 275000  -- 2,750 SEK
  rate_currency: 'SEK'

  -- Actual completion (set at project end)
  actual_work_days: 6
  actual_travel_days: 2
  actual_work_rate_cents: 550000
  actual_travel_rate_cents: 275000

  -- Adjustments
  adjustment_amount_cents: 50000  -- Bonus
  adjustment_reason: 'Arrived 2 days early for prep'

7. Money Storage​

  • Format: Integer cents for precision (e.g., amount_cents INT)
  • Currency: Stored alongside amounts (e.g., currency CHAR(3))
  • Example: 5,500 SEK/day → work_day_rate_cents: 550000, rate_currency: 'SEK'

8. Multi-Currency Support [NEW in v4.0]​

Architecture:

  • Each company has base_currency
  • Manual exchange_rates configured by admin
  • Each booking has own currency
  • Reporting shows original + converted to base currency

Example:

exchange_rates:
  company_id: {company_uuid}
  from_currency: 'SEK'
  to_currency: 'EUR'
  rate: 0.090000

9. Equipment Tracking [NEW in v4.0]​

Company Equipment:

  • company_equipment: Inventory (OB-Van Alpha, GFX Server 1, etc.)
  • Each unit tracked individually (no quantity field)
  • Default daily rate for budgeting

Project Equipment Bookings:

  • project_equipment: Links equipment to projects
  • Start/end dates (availability tracking)
  • Rate adjustable per project
  • Prevents double-booking (like crew)

10. Project Activity Logging [NEW in v4.6]​

Purpose: Complete audit trail of all project-related events

Table: project_activity_logs

  • Tracks every action: project changes, offers, status updates, function additions
  • Flexible JSON changes field for before/after values
  • Human-readable description for timeline display
  • Links to entity type and ID for drill-down

Example Events:

{
  "event_type": "offer_declined",
  "entity_type": "assignment_offer",
  "description": "Lisa Berg declined offer for EVS Operator",
  "user_name": "Lisa Berg (crew response)",
  "changes": {"status": {"old": "pending", "new": "declined"}}
}

{
  "event_type": "project_updated",
  "description": "Project dates changed",
  "user_name": "Susanne",
  "changes": {
    "start_date": {"old": "2025-05-10", "new": "2025-05-12"},
    "end_date": {"old": "2025-05-15", "new": "2025-05-17"}
  }
}

{
  "event_type": "function_added",
  "description": "Added Camera Operator role (2 positions needed)",
  "user_name": "Susanne"
}

UI Display: Project timeline view showing complete history of all activities.

11. Project Date Flexibility [NEW in v4.0]​

Project Dates:

  • start_date/end_date: Main event reference (informational)
  • Used for availability warnings (soft, not blocking)
  • Default dates when sending crew requests (editable)

Assignment Dates:

  • start_date/end_date: Actual booking dates
  • Can be OUTSIDE project dates (pre-visits, load-in, post-production)

11. Crew Capabilities & Notes [NEW in v4.0]​

Three Types:

  1. Crew capabilities (public, crew edits):

    • crew_profiles.capabilities (text)
    • Example: "Handheld, Steadicam, Sony cameras"

  2. Company private notes (private, company edits):

    • company_crew.private_notes (text)
    • Example: "Has back problems, needs sugar"
    • GDPR-sensitive, not visible to crew

  3. Project role note (per booking):

    • assignments.role_note (text)
    • Example: "Main graphic operator", "Backup"

  4. Team label (per booking):

    • assignments.team_label (varchar)
    • Example: "Camera A", "Audio Main"
    • Simple visual organization (MVP approach)

12. Favorite Crew [NEW in v4.1]​

Feature: Star/favorite crew members for easier filtering

Implementation:

  • company_crew.is_favorite (boolean, default: false)
  • Company-specific (each company has own favorites)
  • Visible in crew directory and everywhere crew appears
  • Simple toggle on/off

Use Case: Quickly filter to "A-Team" or preferred crew when staffing projects

13. Administrative Task Tracking [NEW in v4.1]​

Feature: Manual checkboxes for admin workflow tracking

Implementation on assignments table:

  • travel_status: 'not_booked' | 'booked' (all crew)
  • hotel_status: 'not_booked' | 'booked' (all crew)
  • roadbook_status: 'not_sent' | 'sent' (all crew)
  • invoice_status: 'not_handled' | 'handled' (freelancers only)

Key Points:

  • All are manual (no auto-detection)
  • Visible in project grid view
  • Helps admins track completion of all tasks per crew member

14. Contract Acceptance [NEW in v4.1]​

Feature: Required contract acceptance for freelancers

Implementation:

  • assignment_offers.contract_accepted_at (timestamp)
  • Set when crew accepts offer
  • Freelancers must check "I accept the contract" checkbox
  • Staff use direct booking (no contract workflow)

Workflow:

  1. Crew views offer with contract text
  2. Must check contract acceptance to accept offer
  3. Timestamp recorded
  4. PM confirms → assignment created

15. Custom Data Storage [NEW in v4.1]​

Feature: Flexible JSON storage for future features

Implementation:

  • projects.custom_data (json, nullable)
  • Future-proof for extensions

Potential Uses:

  • Useful links (client portals, resources)
  • Custom project fields
  • Integration data (external systems)
  • Any unstructured data needs

📈 Entity Relationships​

ParentRelationshipChildCardinality
Companieshas manyUsers (admins)1:N
Companieshas manyCrew Profiles (via company_crew)N:N
Companieshas manyProjects1:N
Companieshas manyFunctions Catalog1:N
Companieshas manyCompany Equipment1:N
Companieshas manyExchange Rates1:N
Crew Usershas manyCrew Profiles (via claimed_by)1:N
Crew Usershas oneEmployer (company)N:1 (optional)
Global Functionsreferenced byFunctions Catalog1:N (optional)
Projectshas manyProject Functions1:N
Projectshas manyProject Equipment1:N
Projectshas manyProject Budget Items1:N
Projectshas manyAssignments1:N
Assignmentshas oneAssignment Offer1:1 (optional)
Assignmentshas manyTravel Bookings1:N
Assignmentshas manyHotel Bookings1:N
Any Entityhas manyDocumentsPolymorphic
Companieshas manyCommunication Logs1:N

🔄 Status Flows​

Project Status​

draft → active → completed

Assignment Offer Status (Simplified in v4.6)​

pending → accepted (→ assignment created)
       → negotiating → accepted (→ assignment created)
       → declined (stays declined)

Assignment Status (from status logs)​

confirmed → contract_sent → contract_signed → travel_booked →
hotel_booked → active → completed
(can jump to cancelled at any point)

Crew Employment​

employer_id set: Employed by that company (staff)
employer_id null: Freelancer

🛠️ How to Use This Schema​

During Planning (Now)​

  1. Discuss with PM: Open schema.dbml side-by-side with dbdiagram.io
  2. Make changes: Edit schema.dbml directly
  3. Version control: Commit changes to Git
  4. Review diffs: See what changed between versions
  5. Iterate: Add notes, adjust relationships, refine fields

During Development (Later)​

Option A: Manual Migration Creation​

  1. Read schema.dbml
  2. Write migrations based on structure
  3. Keep schema.dbml in sync as documentation

Option B: SQL Export​

  1. Use dbdiagram.io "Export to SQL"
  2. Adapt SQL to your migration format

Option C: DBML Parser​

  • dbml-parser (official Node.js parser)
  • Custom scripts to generate migrations

Recommended: Start with Option A for full control

📝 Making Schema Changes​

Workflow​

  1. Edit schema.dbml in project root
  2. Refresh dbdiagram.io to see visual impact
  3. Discuss with team using the diagram
  4. Commit to Git with descriptive message
  5. Update this documentation
  6. During development: Create corresponding migration

Example Change​

// Before
Table crew_profiles {
  ...
  phone varchar
}

// After
Table crew_profiles {
  ...
  phone varchar
  capabilities text [null, note: 'Free-text skills (e.g., "Handheld, Steadicam")']
}

🔗 Related Documentation​

📚 Tools & Resources​

⚠️ Important Notes​

Version 4.0 Changes (Stakeholder Meeting 2025-11-11) [MAJOR UPDATE]​

Multi-Company Crew Support:

  • ✅ Crew can work for multiple companies
  • ✅ One email = one account across all companies
  • ✅ Account claiming workflow (admin creates, crew claims later)
  • ✅ Employment tracking (one employer or none)
  • ✅ Availability tracking (employed staff only)

New Features:

  • ✅ Global function library + company custom functions
  • ✅ Company-specific fees even for global functions
  • ✅ Crew capabilities (free-text, crew editable)
  • ✅ Company private notes (GDPR-sensitive, hidden from crew)
  • ✅ Project role notes & team labels
  • ✅ Equipment tracking with availability
  • ✅ Generic budget line items
  • ✅ Communication log (full audit trail)
  • ✅ Multi-currency support with manual exchange rates
  • ✅ Enhanced file storage with access control
  • ✅ Economic reporting (budget vs actual)

Project Date Flexibility:

  • ✅ Project dates are informational (main event)
  • ✅ Assignments can be outside project dates (pre-visits, etc.)
  • ✅ Soft availability warnings (not blocking)

Version 3.0 Changes (Q&A Session 2025-10-23)​

  • ✅ Simplified project status: draft → active → completed
  • ✅ Direct booking support (skip offer/negotiation flow)
  • ✅ Assignment completion tracking (actual vs planned)
  • ✅ Financial finalization mechanism
  • ✅ Invite-only staff registration (no public forms)
  • ✅ Road book generation from structured data
  • ✅ Personal message field on offers
  • ✅ Separate subdomains: backend.pbs.com + crew.pbs.com

Not in MVP​

  • ❌ Automated email notifications (manual workflow)
  • ❌ Real-time collaboration with locking (warning system only)
  • ❌ Timezone support (dates only)
  • ❌ Automatic currency conversion (manual exchange rates)
  • ❌ Equipment quantity tracking (each unit individual)
  • ❌ Public registration forms (invite-only)
  • ❌ Advanced analytics/dashboards

Schema Version: 4.0 Last Updated: 2025-11-11 Source File: /schema.dbml Breaking Changes: Yes (multi-company crew restructure)