Full ERP Data Model
Okay, here is the Finalised Full ERP Data Model (Expanded Version) again, as presented earlier. This is the comprehensive version you can use as the long-term blueprint, from which the slimmed-down MVP version was derived.
🎬 Full ERP Data Model (Final Expanded Version)¶
🎬 Core Production & Financial Data Model¶
1. PROJECTS & WORKFLOW¶
Project {
id: UUID // PK
project_code: string // Human-readable, unique identifier
type: enum('Film', 'Series', 'Season', 'Episode', 'ShortFilm', 'Commercial', 'Documentary', 'Other')
parent_project_id?: UUID // FK to Project.id (for Series > Season > Episode hierarchy)
title: string
status: enum('Development', 'PreProduction', 'Production', 'PostProduction', 'Distribution', 'Archived', 'OnHold', 'Cancelled')
logline?: string
genre?: string
production_company_id?: UUID // FK to Company.id (Primary producing company)
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
// metadata_json: JSONB // Could hold things like target audience, shooting format, etc.
// project_settings_json: JSONB // Could hold project-specific workflow settings, notification preferences
}
WorkflowTemplate {
id: UUID // PK
name: string
entity_type: string // e.g., 'Transaction', 'BudgetVersion', 'Contract'
steps_json: JSONB // [{ step_order: 1, approver_role_id: UUID (FK to RoleDefinition or SystemRole), required: true, step_name: "Initial Review", instructions: "..." }]
is_active: boolean
created_at: DateTime
updated_at: DateTime
}
WorkflowInstance {
id: UUID // PK
workflow_template_id: UUID // FK to WorkflowTemplate.id
entity_id: UUID // ID of the entity this workflow applies to (e.g., Transaction.id)
entity_type: string // Type of the entity
current_step_order: number
status: enum('InProgress', 'Approved', 'Rejected', 'Cancelled', 'PendingAction', 'Escalated')
submitted_by_user_id: UUID // FK to User.id
last_action_by_user_id?: UUID // FK to User.id
last_action_at?: DateTime
notes_json?: JSONB // [{ step_order: 1, user_id: UUID, note: "Looks good", timestamp: DateTime }]
created_at: DateTime
updated_at: DateTime
}
2. TRANSACTIONS (Unified Financials)¶
Transaction {
id: UUID // PK
project_id: UUID // FK to Project.id
transaction_number: string // System-generated, unique or unique per project/type
type: enum('Expense', 'APInvoice', 'ARInvoice', 'PurchaseOrder', 'PaymentSent', 'PaymentReceived', 'PayrollExpense', 'JournalEntry', 'OpeningBalance')
status: enum(
// General
'Draft', 'PendingApproval', 'Approved', 'Rejected', 'Void', 'Cancelled',
// PO Specific
'SentToVendor', 'PartiallyFulfilled', 'Fulfilled',
// Invoice Specific
'Sent', 'Received', // 'Sent' for AR, 'Received' for AP
'PartiallyPaid', 'Paid', 'Overdue',
// Payment Specific
'Pending', 'Cleared', 'Failed', 'Reconciled',
// Payroll Specific
'Processing', 'PaidOut'
)
transaction_date: Date // Date the transaction occurred or was initiated
due_date?: Date // For invoices, POs
posting_date?: Date // Date it hits the general ledger
company_id?: UUID // FK to global Company.id (Vendor/Supplier or Client)
person_id?: UUID // FK to global Person.id (e.g., for employee expense reimbursement, refers to the global person)
total_amount: number // Gross amount including tax
tax_amount?: number
subtotal_amount?: number // Amount before tax
currency_code: string // FK to Currency.code
payment_method_id?: UUID // FK to PaymentMethod.id (for payments)
vendor_invoice_number?: string // Supplier's own invoice number (for APInvoices/Expenses)
notes?: string
related_po_transaction_id?: UUID // FK to Transaction.id (if this is an Invoice related to a PO)
related_invoice_transaction_id?: UUID // FK to Transaction.id (if this is a Payment related to an Invoice)
related_quote_id?: UUID // FK to Quote.id (if this Transaction, e.g. PO, originated from a Quote)
workflow_instance_id?: UUID // FK to WorkflowInstance.id
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
}
TransactionLineItem {
id: UUID // PK
transaction_id: UUID // FK to Transaction.id
description: string
quantity?: number
unit_price?: number
amount: number // Net amount for this line item (exclusive of line-item specific tax if applicable, or could be inclusive depending on model choice)
budget_item_version_id?: UUID // FK to BudgetItemVersion.id (for linking spend to budget)
chart_of_account_id?: UUID // FK to ChartOfAccount.id (for accounting classification)
asset_id?: UUID // FK to Asset.id (if purchase is for a specific asset)
production_item_id?: UUID // FK to ProductionItem.id (if related to a specific production element)
tax_rate_id?: UUID // FK to TaxRate.id (if specific tax rate applies to this line)
// department_id?: UUID // FK to Department.id (for departmental costing)
// location_id?: UUID // FK to Location.id (if spend is tied to a location)
// notes?: string
}
PaymentMethod {
id: UUID // PK
name: string // e.g., "Project Bank Account HSBC", "Credit Card AMEX ****1234"
type: enum('Electronic', 'Card', 'Check', 'Cash', 'Other')
details_json?: JSONB // { accountNumber: "...", bankName: "...", cardType: "Visa" } - sensitive data should be handled carefully/encrypted if stored
is_active: boolean
}
Quote {
id: UUID // PK
project_id: UUID // FK to Project.id
quote_number: string // Unique within project or globally
supplier_company_id: UUID // FK to Company.id (The company providing the quote)
contact_person_id?: UUID // FK to Person.id (Contact at the supplier company, references global Person)
status: enum('Draft', 'SentToSupplier', 'ReceivedFromSupplier', 'UnderReview', 'Accepted', 'Rejected', 'Expired', 'ConvertedToPO', 'Archived')
issue_date: Date
valid_until_date?: Date
total_amount: number // Estimated total from the supplier
currency_code: string // FK to Currency.code
notes?: string
terms_and_conditions?: string // Or link to an Attachment
supplier_reference_number?: string // Supplier's own quote ID
workflow_instance_id?: UUID // FK to WorkflowInstance.id (for quote approval)
associated_purchase_order_transaction_id?: UUID // FK to Transaction.id (once converted)
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id (User who initiated/logged the quote request)
updated_by_user_id: UUID // FK to User.id
}
QuoteLineItem {
id: UUID // PK
quote_id: UUID // FK to Quote.id
description: string
quantity?: number
unit_of_measure_id?: UUID // FK to UnitOfMeasure.id
unit_price?: number
amount: number // quantity * unit_price
notes?: string
linked_production_item_id?: UUID // FK to ProductionItem.id
linked_asset_id?: UUID // FK to Asset.id
// tax_rate_id?: UUID // FK to TaxRate.id (if applicable at quote stage)
}
3. BUDGETS & FORECASTING (Enhanced with Versioning & Change Orders)¶
Budget {
id: UUID // PK
project_id: UUID // FK to Project.id
title: string // e.g., "Initial Production Budget", "Post-Production Budget v2"
currency_code: string // FK to Currency.code (Primary currency for this budget)
// description?: string
// is_master_budget?: boolean
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
}
BudgetVersion {
id: UUID // PK
budget_id: UUID // FK to Budget.id
version_number: string // e.g., "v1.0", "v1.1_Approved", "v2.0_Draft"
title: string // e.g., "Locked Budget - Pre-Shoot", "Revised for Tax Credit"
status: enum('Draft', 'SubmittedForApproval', 'Approved', 'Active', 'Superseded', 'Archived', 'Rejected')
effective_date?: Date
total_budgeted_cost: number // Sum of all BudgetItemVersion.estimated_cost for this version
total_actual_cost?: number // Sum of linked TransactionLineItem.amount, updated periodically
total_variance?: number // total_budgeted_cost - total_actual_cost
notes?: string // General notes about this version
workflow_instance_id?: UUID // FK to WorkflowInstance.id (for approval of this budget version)
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
}
BudgetHeaderVersion { // Represents a category or sub-category in the budget (e.g., "ABOVE THE LINE", "1100 - Story & Script")
id: UUID // PK
budget_version_id: UUID // FK to BudgetVersion.id
parent_header_id?: UUID // FK to BudgetHeaderVersion.id (for hierarchical structure)
account_code_prefix?: string // e.g., "1000", "1100"
title: string
indent_level?: number // For display purposes
display_order?: number // For display purposes
total_estimated_cost: number // Sum of estimated_cost of child BudgetItemVersions and child BudgetHeaderVersions
total_actual_cost?: number // Sum of actual_cost of child BudgetItemVersions and child BudgetHeaderVersions
notes?: string
// is_summary_only?: boolean // If true, this header doesn't directly contain items but summarizes children
}
BudgetItemVersion { // Represents a specific line item in the budget
id: UUID // PK
header_id: UUID // FK to BudgetHeaderVersion.id
account_code_suffix?: string // e.g., "01" to combine with header prefix for full "1100-01"
description: string // e.g., "Writer Fees", "Camera Rental - Main Unit"
quantity?: number
rate?: number // Cost per unit/day/week
days_weeks_count?: number // Number of days or weeks (often used with rate)
unit_of_measure_id?: UUID // FK to UnitOfMeasure.id (e.g., "Day", "Week", "Lump Sum", "Item")
estimated_cost: number // Calculated or manually entered (quantity * rate * days_weeks_count, or fixed amount)
actual_cost?: number // Sum of linked TransactionLineItem.amount, updated periodically
variance?: number // estimated_cost - actual_cost
notes?: string
chart_of_account_id?: UUID // FK to ChartOfAccount.id (default COA for this budget item)
linked_script_element_id?: UUID // FK to ScriptElement.id (for script-to-budget links)
linked_production_item_id?: UUID // FK to ProductionItem.id
// tax_credit_eligible?: boolean
// department_id?: UUID // FK to Department.id
}
BudgetItemFringeVersion { // Fringes applied to a specific budget item
id: UUID // PK
budget_item_version_id: UUID // FK to BudgetItemVersion.id
fringe_id: UUID // FK to Fringe.id
applied_rate_percent?: number // Could be different from Fringe.rate_percent if overridden for this specific item
applied_fixed_amount?: number // Could be different from Fringe.fixed_amount if overridden
calculated_amount: number // The fringe amount for this budget item
// notes?: string
}
BudgetChangeOrder {
id: UUID // PK
budget_version_id: UUID // FK to BudgetVersion.id (The version this change order applies to)
change_order_number: string
title: string
description: string
status: enum('Draft', 'PendingApproval', 'Approved', 'Rejected', 'Implemented', 'Cancelled')
net_financial_impact: number // Positive or negative
proposed_by_user_id: UUID // FK to User.id
approved_by_user_id?: UUID // FK to User.id
approval_date?: Date
workflow_instance_id?: UUID // FK to WorkflowInstance.id
created_at: DateTime
updated_at: DateTime
// reason_for_change?: string
// impact_on_schedule?: string
}
BudgetChangeOrderLineItem {
id: UUID // PK
budget_change_order_id: UUID // FK to BudgetChangeOrder.id
target_budget_item_version_id?: UUID // FK to BudgetItemVersion.id (If modifying an existing item)
// If adding a new item, this might be null and new item details are below,
// or a temporary BudgetItemVersion record is created and linked.
action_type: enum('Modify', 'Add', 'Remove', 'TransferFrom', 'TransferTo')
// Fields for describing the change from the original BudgetItemVersion or defining a new one:
description_change?: string // New/updated description
quantity_change?: number // New/updated quantity (or delta)
rate_change?: number // New/updated rate (or delta)
days_weeks_count_change?: number
unit_of_measure_id_change?: UUID // FK to UnitOfMeasure.id
estimated_cost_change: number // The financial impact of this line item (can be +/-)
justification: string // Reason for this specific line item change
// new_header_id?: UUID // FK to BudgetHeaderVersion.id (if adding a new item to a specific header)
// new_account_code_suffix?: string (if adding a new item)
}
4. PEOPLE, COMPANIES, ROLES & USERS (Revised for Global Entities)¶
// --- GLOBAL PLATFORM ENTITIES ---
Person { // Global master record for an individual
id: UUID // PK
first_name: string
last_name: string
email_primary: string // Globally unique, used for platform login/identification if they become a User
email_secondary?: string
phone_primary?: string // Global contact phone (e.g., mobile)
phone_secondary?: string // Global contact phone (e.g., work, home)
// global_profile_last_updated_at: DateTime // When the person last updated their own global info via User account
// global_profile_managed_by_user_id?: UUID // FK to User.id (if they have direct platform access to their Person/CrewProfile)
created_at: DateTime
updated_at: DateTime // Reflects updates to this global Person record by anyone with permission
}
CrewProfile { // Global master HR/onboarding profile for a Person (sensitive, self-managed or by trusted admin)
person_id: UUID // PK, FK to Person.id
// Global Address (current primary)
address_street?: string
address_city?: string
address_state_province?: string
address_postal_code?: string
address_country_code?: string // FK to Country.code
date_of_birth?: Date // For compliance, insurance (store encrypted or manage access strictly)
nationality_country_code?: string // FK to Country.code
// Global Emergency Contact (primary)
emergency_contact_name?: string
emergency_contact_phone?: string
emergency_contact_relationship?: string
// Global Financial (primary, for platform services or general payment preferences - ALL FINANCIAL DATA HERE SHOULD BE ENCRYPTED AT REST)
bank_account_name_encrypted?: string
bank_account_number_encrypted?: string
bank_routing_number_encrypted?: string // (e.g., Sort Code, ABA)
bank_swift_bic_encrypted?: string
bank_iban_encrypted?: string
bank_name?: string
bank_country_code?: string // FK to Country.code
tax_id_number_encrypted?: string // (e.g., SSN, UTR, NINO, SIN)
tax_id_type?: enum('SSN', 'UTR', 'NINO', 'SIN', 'EIN', 'VAT_ID', 'Other') // Type of the global tax ID
// Global Union & Guild Affiliations
union_memberships_json?: JSONB // [{ union_id: UUID, member_id: "string", status: "Active", local: "..." }]
// Other Global Profile Info
// resume_attachment_id?: UUID // FK to Attachment table for global resume (Attachment.parent_entity_type = 'CrewProfile')
// skills_tags_json?: JSONB // ["Cinematography", "Steadicam", "Drone Pilot", "AVID", "FinalCutPro"]
// portfolio_link?: string
// website_url?: string
// Global Dietary/Medical (for general reference, project-specific consent needed for use) - Store with extreme care for privacy
// global_dietary_restrictions?: string // (e.g. Vegetarian, Gluten-Free)
// global_allergies_medical_conditions?: string // (e.g. Peanut Allergy, Diabetes)
updated_at: DateTime // When this global CrewProfile was last updated
}
Company { // Global master record for a company (Production Co, Supplier, Financier, etc.)
id: UUID // PK
name: string // Common name, unique on platform
legal_name?: string
tax_id?: string // Global company tax ID (e.g., EIN, VAT Registration)
registration_number?: string // Company registration number
// primary_contact_person_id?: UUID // FK to Person.id (a global contact for this company)
website_url?: string
// phone_main?: string
// email_main?: string
// address_street?: string (Global primary address)
// address_city?: string
// address_state_province?: string
// address_postal_code?: string
// address_country_code?: string // FK to Country.code
// global_profile_last_updated_at: DateTime
// global_profile_managed_by_user_id?: UUID // FK to User.id (primary admin User for this Company's platform profile)
created_at: DateTime
updated_at: DateTime // Reflects updates to this global Company record
}
User { // System users, can be a Person or an admin for a Company
id: UUID // PK
person_id?: UUID // FK to Person.id (if this user IS a person in the system and manages their own profile)
username: string // Unique login username
email: string // Unique login email, might be Person.email_primary
hashed_password: string
system_role_id: UUID // FK to SystemRole.id (platform-level role, e.g., "PlatformAdmin", "StandardUser", "CompanyAdmin")
is_active: boolean
last_login_at?: DateTime
// two_factor_secret_encrypted?: string
// email_verified_at?: DateTime
// password_last_changed_at?: DateTime
created_at: DateTime
updated_at: DateTime
}
UserProfileSetting {
user_id: UUID // PK, FK to User.id
language_code: string // FK to Language.code (e.g., "en-US", "fr-FR")
preferred_theme: enum('Light', 'Dark', 'SystemDefault') // For UI theme
timezone_id: string // E.g., "Europe/London", "America/New_York" (IANA timezone names)
notification_preferences_json?: JSONB // { "email_new_assignment": true, "sms_urgent_alert": false, "whatsapp_updates": true }
// other_preferences_json?: JSONB // For future extensibility, e.g., default project view
updated_at: DateTime
}
// --- PLATFORM-LEVEL LINKING & ADMINISTRATION ---
CompanyUserAdministrator { // Who can manage a Company's global profile and its users/services on the platform
company_id: UUID // FK to Company.id
user_id: UUID // FK to User.id
role_type: enum('PrimaryAdmin', 'ProfileEditor', 'FinanceContact', 'ServiceManager', 'UserAdmin')
// PRIMARY KEY (company_id, user_id, role_type)
granted_at: DateTime
granted_by_user_id?: UUID // FK to User.id (platform admin or company primary admin)
}
CompanyType { // e.g., "Production Company", "Post-Production House", "Equipment Supplier", "Caterer", "Financier"
id: UUID // PK
name: string // Unique
description?: string
}
CompanyTypeLink { // A company can have multiple types
company_id: UUID // FK to Company.id
company_type_id: UUID // FK to CompanyType.id
// PRIMARY KEY (company_id, company_type_id)
}
PersonCompanyLink { // Links a global Person to a global Company (e.g., employee, owner, director of)
id: UUID // PK
person_id: UUID // FK to Person.id
company_id: UUID // FK to Company.id
role_in_company: string // e.g., "CEO", "Sales Rep", "Accountant", "Director", "Owner"
is_primary_contact_for_company?: boolean // Is this person the main contact for this company on the platform?
department_in_company?: string
start_date?: Date
end_date?: Date
// This is about their role IN that company globally, not for a specific project.
}
// --- PROJECT-SPECIFIC PARTICIPATION & DATA SNAPSHOTS ---
ProjectRelationship { // Links a global Person to a Project in a specific functional/production Role (the "engagement")
id: UUID // PK
project_id: UUID // FK to Project.id
person_id: UUID // FK to global Person.id
role_definition_id: UUID // FK to RoleDefinition.id (the type of functional role, e.g., "Gaffer", "Actor")
role_title_override?: string // Specific title for this project, e.g., "Gaffer - Main Unit", "Lead Actor - John Doe"
department_id?: UUID // FK to Department.id (assigned department for this project)
employment_type?: enum('EmployeeW2', 'Contractor1099', 'Freelancer', 'Volunteer', 'DayPlayer', 'LoanOut', 'Other')
status: enum('Invited', 'PendingAcceptance', 'OfferSent', 'Accepted', 'Active', 'OnHold', 'Wrapped', 'Terminated', 'DeclinedInvite', 'OfferRescinded')
start_date?: Date // For this project engagement
end_date?: Date // For this project engagement
deal_memo_contract_id?: UUID // FK to Contract.id (contract for THIS engagement)
// pay_rate_info_json?: JSONB // { rate: 500, unit: "day", currency: "USD", overtime_rules_id: UUID } - simple rate for this engagement
notes?: string // Notes specific to this person's role/engagement on this project
// reports_to_project_relationship_id?: UUID // FK to ProjectRelationship.id (for hierarchy within project team)
// is_key_crew?: boolean
// on_call_sheet_default?: boolean
}
ProjectParticipantData { // CRITICAL: Snapshot of a Person's data FOR A SPECIFIC PROJECT ENGAGEMENT
id: UUID // PK
project_id: UUID // FK to Project.id
person_id: UUID // FK to global Person.id (identifies whose data this is)
project_relationship_id: UUID // FK to ProjectRelationship.id (links to the specific engagement this snapshot is for) - UNIQUE
// Snapshot of Identifying Info (copied/verified at project onboarding)
first_name_snapshot: string
last_name_snapshot: string
email_primary_project_snapshot: string // Email to use for THIS project's comms
phone_primary_project_snapshot?: string // Phone to use for THIS project
// Snapshot of Onboarding & HR Info FOR THIS PROJECT (copied/verified from global CrewProfile, with consent)
address_street_project_snapshot?: string
address_city_project_snapshot?: string
address_state_province_project_snapshot?: string
address_postal_code_project_snapshot?: string
address_country_code_project_snapshot?: string // FK to Country.code
date_of_birth_project_snapshot?: Date // Only if legally required for this project (e.g., minors, insurance)
nationality_country_code_project_snapshot?: string // FK to Country.code (relevant for tax credits, visas)
emergency_contact_name_project_snapshot?: string
emergency_contact_phone_project_snapshot?: string
emergency_contact_relationship_project_snapshot?: string
// Financial Snapshot FOR THIS PROJECT (ENCRYPTED, specific consent for THIS project's payroll/payment)
bank_account_name_encrypted_project_snapshot?: string
bank_account_number_encrypted_project_snapshot?: string
bank_routing_number_encrypted_project_snapshot?: string
bank_swift_bic_encrypted_project_snapshot?: string
bank_iban_encrypted_project_snapshot?: string
bank_name_project_snapshot?: string
bank_country_code_project_snapshot?: string // FK to Country.code
tax_id_number_encrypted_project_snapshot?: string // Tax ID used for THIS project's payments
tax_id_type_project_snapshot?: enum('SSN', 'UTR', 'NINO', 'SIN', 'EIN', 'VAT_ID', 'Other') // Type for this project
// payment_method_preference_project_snapshot?: enum ('DirectDeposit', 'Check') // if applicable
// Other Project-Specific Profile Data (with consent for this project)
dietary_restrictions_for_project?: string // For this project's catering
allergies_medical_conditions_for_project?: string // For this project's safety officer/medic
// Metadata for the snapshot
data_captured_at: DateTime // When this project-specific snapshot was created/last verified
// Source Timestamps: To know how "fresh" the global data was when this snapshot was taken
source_global_person_updated_at?: DateTime // Timestamp of Person.updated_at at time of capture
source_global_crew_profile_updated_at?: DateTime // Timestamp of CrewProfile.updated_at at time of capture
// Consent specific to this project's data usage
// project_data_consent_flags_json?: JSONB // { "payroll": true, "communication": true, "emergency_contact": true, "catering_dietary": true, "safety_medical": true }
// project_data_consent_obtained_at?: DateTime
// project_data_consent_document_attachment_id?: UUID // FK to Attachment.id
created_at: DateTime // When this snapshot record was created
updated_at: DateTime // When this snapshot record was last modified (e.g. project admin correction)
updated_by_user_id?: UUID // FK to User.id (Project admin who last touched this project-specific record)
}
// --- ONBOARDING PROCESS (Now tied to ProjectRelationship) ---
OnboardingProcess {
id: UUID // PK
project_relationship_id: UUID // FK to ProjectRelationship.id (Defines person, project, role context)
status: enum('NotStarted', 'InProgress', 'AwaitingReview', 'Completed', 'OnHold', 'RequiresAttention', 'Failed')
overall_due_date?: Date
assigned_to_user_id?: UUID // FK to User.id (HR/Coordinator on project managing this)
notes?: string
created_at: DateTime
updated_at: DateTime
}
OnboardingTaskDefinition { // Global templates for tasks
id: UUID // PK
name: string
description?: string
category?: enum('DocumentSignature', 'FormSubmission', 'InformationVerification', 'TrainingModule', 'SystemAccessSetup', 'EquipmentIssue')
// default_instructions_json?: JSONB
// estimated_duration_minutes?: number
// is_mandatory_globally?: boolean
// requires_document_upload?: boolean
// linked_form_template_id?: UUID // (If using a form builder system)
}
OnboardingTaskInstance { // Instance of a task for a specific OnboardingProcess
id: UUID // PK
onboarding_process_id: UUID // FK to OnboardingProcess.id
onboarding_task_definition_id: UUID // FK to OnboardingTaskDefinition.id
status: enum('Pending', 'InProgress', 'Submitted', 'AwaitingVerification', 'Completed', 'Skipped', 'Failed', 'Expired')
due_date?: Date
completed_date?: Date
// submitted_document_attachment_id is via Attachment table:
// Attachment.parent_entity_type = 'OnboardingTaskInstance', Attachment.parent_entity_id = OnboardingTaskInstance.id
// submitted_data_json?: JSONB // For form data captured that isn't a document
notes?: string // Notes by assignee or reviewer
verified_by_user_id?: UUID // FK to User.id
verified_at?: DateTime
// assigned_to_user_id?: UUID // FK to User.id (If specific task assigned differently from process)
}
// --- ROLES (Global Definitions) ---
SystemRole { // Platform-level roles for Users
id: UUID // PK
name: string // e.g., "PlatformSuperAdmin", "CompanyAdmin", "ProjectCreator", "StandardUser", "FinanceManagerGlobal"
description?: string
// is_default_role?: boolean
}
Permission { // Granular permissions in the system
id: UUID // PK
permission_key: string // e.g., "user:invite", "budget:edit:all", "transaction:approve:department_only", "user:manage:company"
description?: string
module: string // e.g., "Projects", "Budgets", "Users", "Settings"
// category?: string // e.g., "Write", "Read", "Delete", "Approve"
}
SystemRolePermissionLink { // Links SystemRoles to Permissions
system_role_id: UUID // FK to SystemRole.id
permission_id: UUID // FK to Permission.id
// PRIMARY KEY (system_role_id, permission_id)
}
RoleDefinition { // Standard production functional roles (e.g., Gaffer, Director, Actor, Production Accountant)
id: UUID // PK
title: string // e.g., "Director of Photography", "Production Manager", "First Assistant Director"
abbreviation?: string // e.g., "DP", "PM", "1st AD"
description?: string
responsibilities_json?: JSONB // ["...", "...", "..."]
category: enum('Crew', 'Cast', 'KeyCreative', 'ProductionStaff', 'PostProductionStaff', 'DayPlayer', 'Background', 'VendorContact', 'FinancierRep', 'Other')
default_department_id?: UUID // FK to Department.id
is_active: boolean
// is_union_specific?: boolean
// union_id?: UUID // FK to Union.id
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
}
5. PROJECT ACCESS CONTROL¶
ProjectRoleDefinition {
id: UUID // PK
name: string // e.g., "Project Administrator", "Project Accountant", "Department Head Viewer", "Read-Only Guest"
description?: string
// is_template: boolean // If some roles are templates for projects to use
// created_by_system: boolean // If it's a default system-provided project role
created_at: DateTime
updated_at: DateTime
// created_by_user_id: UUID // FK to User.id
// updated_by_user_id: UUID // FK to User.id
}
ProjectTeamMember { // Links a User to a Project with a specific ProjectRoleDefinition for system access
id: UUID // PK
project_id: UUID // FK to Project.id
user_id: UUID // FK to User.id
project_role_definition_id: UUID // FK to ProjectRoleDefinition.id
status: enum('Active', 'Invited', 'Suspended', 'Revoked')
notes?: string
// UNIQUE (project_id, user_id) // Assuming one project access role per user per project.
// If multiple roles allowed, remove this UNIQUE constraint and ensure ProjectRolePermissionLink handles combined perms.
created_at: DateTime
updated_at: DateTime
assigned_by_user_id?: UUID // FK to User.id
}
ProjectRolePermissionLink { // Links ProjectRoleDefinitions to global Permissions
project_role_definition_id: UUID // FK to ProjectRoleDefinition.id
permission_id: UUID // FK to Permission.id (re-using global Permission definitions)
// PRIMARY KEY (project_role_definition_id, permission_id)
created_at: DateTime
// granted_by_user_id?: UUID // FK to User.id
}
6. CONTRACTS¶
Contract { // Contract is for a specific project engagement usually
id: UUID // PK
project_id: UUID // FK to Project.id
// Parties to the contract:
person_id?: UUID // FK to global Person.id (if an individual is a party, e.g., talent)
company_id?: UUID // FK to global Company.id (if a company is a party, e.g., vendor, loan-out)
// For crew/cast engagements, this links to their specific role assignment:
project_relationship_id?: UUID // FK to ProjectRelationship.id (if contract is for a specific crew/cast engagement)
contract_number: string // Unique identifier for the contract
contract_type: enum('Employment', 'VendorServices', 'EquipmentRental', 'LocationAgreement', 'TalentAgreement', 'DealMemo', 'Distribution', 'Financing', 'ClearanceAgreement', 'NDA', 'LicenseAgreement', 'LoanOutAgreement', 'Other')
status: enum('Draft', 'Negotiation', 'PendingSignature', 'Active', 'Expired', 'Terminated', 'Archived', 'Breached', 'Completed')
effective_date: Date
expiration_date?: Date
engagement_start_date?: Date // Often relevant for talent/crew contracts, mirrors ProjectRelationship
engagement_end_date?: Date // Often relevant for talent/crew contracts, mirrors ProjectRelationship
// terms_structured_json: JSONB // Key queryable terms: { payment_terms: "Net 30", territory: "Worldwide", exclusivity: true }
// full_text_terms_json?: JSONB // Full legal text, less structured clauses, or link to an attachment
// signed_document_attachment_id via Attachment table (Attachment.parent_entity_type = 'Contract')
workflow_instance_id?: UUID // FK to WorkflowInstance.id (for contract approval)
// renewal_terms_json?: JSONB
// termination_clause_summary?: string
// governing_law_jurisdiction_id?: UUID // FK to a Jurisdiction table (if needed)
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
updated_by_user_id: UUID // FK to User.id
}
ContractPaymentMilestone {
id: UUID // PK
contract_id: UUID // FK to Contract.id
description: string
due_date?: Date
trigger_event?: string // e.g., "On Signature", "Principal Photography Start", "Delivery of First Cut"
amount: number
currency_code: string // FK to Currency.code
status: enum('Pending', 'Due', 'Paid', 'Overdue', 'Disputed', 'Cancelled')
// related_invoice_transaction_id?: UUID // FK to Transaction.id (when invoiced/paid)
// notes?: string
}
ContractRate { // Defines specific rates within a contract (e.g., day rate, weekly rate, overtime rate)
id: UUID // PK
contract_id: UUID // FK to Contract.id
description: string // e.g., "Standard Day Rate", "Overtime Rate (1.5x)", "Travel Day Rate"
phase_id?: UUID // FK to ProductionPhase.id (if rate is specific to a phase)
rate: number
unit_of_measure_id: UUID // FK to UnitOfMeasure.id (e.g., "Day", "Week", "Hour", "Lump Sum")
// For guarantees:
guarantee_period_value?: number // e.g., 10
guarantee_period_unit_id?: UUID // FK to UnitOfMeasure.id (e.g., "Weeks") -> "10 Week Guarantee"
// condition_json?: JSONB // e.g. { "min_hours_for_ot": 8 }
// is_active: boolean // If a rate can be deactivated within a contract
}
ContractRateFringe { // Links a specific contract rate to applicable fringes
id: UUID // PK
contract_rate_id: UUID // FK to ContractRate.id
fringe_id: UUID // FK to Fringe.id
// applied_rate_percent_override?: number // If fringe rate is different for this specific contract rate
// applied_fixed_amount_override?: number // If fringe amount is different
// is_waived?: boolean
}
7. FRINGES & PAYROLL¶
Fringe { // Global definition of a fringe benefit or payroll tax
id: UUID // PK
name: string // e.g., "Social Security (Employer)", "Pension Contribution", "Holiday Pay Accrual"
description?: string
calculation_type: enum('Percentage', 'FixedAmountPerUnit', 'FixedAmountTotal', 'TieredPercentage', 'RatePerHourWorked')
rate_percent?: number // For Percentage type
fixed_amount?: number // For FixedAmount types
unit_of_measure_id_for_fixed?: UUID // FK to UnitOfMeasure.id (e.g., "Day", "Week" for FixedAmountPerUnit)
applicable_to_categories: enum('Labor', 'Talent', 'Equipment', 'SpecificServices', 'All')[] // Array of enums
// calculation_rules_json: JSONB // { base: 'gross_pay', cap_annual: 160200, tiers: [{ upto: 50000, rate: 0.05}, {over: 50000, rate: 0.03}] }
chart_of_account_id_expense?: UUID // FK to ChartOfAccount.id (for the expense side)
chart_of_account_id_liability?: UUID // FK to ChartOfAccount.id (for the payable/liability side)
is_active: boolean
country_code?: string // FK to Country.code (if specific to a country)
state_province_code?: string // (if specific to a state/province within a country)
// effective_date?: Date
// expiry_date?: Date
}
PayrollPeriod {
id: UUID // PK
project_id: UUID // FK to Project.id
period_start_date: Date
period_end_date: Date
pay_date: Date // Date employees/contractors will be paid for this period
status: enum('Open', 'PendingApproval', 'ApprovedForProcessing', 'Processing', 'Paid', 'Closed', 'Archived', 'Error')
cutoff_datetime: DateTime // Deadline for timecard submissions/approvals for this period
notes?: string
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
// approved_by_user_id?: UUID // FK to User.id
// approval_at?: DateTime
// payment_batch_id?: string // From external payroll processor
}
// Timecard & TimeLogDetail are under LOGGING (Section 8)
PayrollCalculation { // Result of payroll run for one person (via ProjectRelationship) for one payroll period
id: UUID // PK
payroll_period_id: UUID // FK to PayrollPeriod.id
project_relationship_id: UUID // FK to ProjectRelationship.id (identifies the person, project, role)
timecard_id?: UUID // FK to Timecard.id (source of hours, if applicable)
// Gross Pay Components
regular_hours_worked?: number
regular_rate?: number
regular_pay_amount?: number
overtime1_hours_worked?: number
overtime1_rate?: number
overtime1_pay_amount?: number
// ... other overtime types (OT2, OT3 etc.)
// ... other pay types (holiday_pay, sick_pay, day_rate_allowance, per_diem_amount, bonuses etc.)
total_gross_pay: number
// Deductions (Pre-tax & Post-tax)
// pre_tax_deductions_json?: JSONB // [{ name: "Pension 401k", amount: 100.00, type: "percentage", rate: 0.05 }, ...]
// post_tax_deductions_json?: JSONB // [{ name: "Union Dues", amount: 50.00 }, ...]
total_deductions?: number
// Taxes (Employee contributions)
// employee_taxes_json?: JSONB // [{ name: "Federal Income Tax", amount: 200.00 }, { name: "Social Security", amount: 62.00 }, ...]
total_employee_taxes?: number
// Employer Contributions (Fringes)
// employer_fringes_json?: JSONB // [{ fringe_id: UUID, name: "Employer Pension", amount: 100.00, expense_coa_id: UUID, liability_coa_id: UUID }, ...]
total_employer_fringes_cost?: number // Cost to employer, not part of net pay
net_pay: number // total_gross_pay - total_deductions - total_employee_taxes
related_transaction_id?: UUID // FK to Transaction.id (type='PayrollExpense', for the net_pay + employer_taxes)
calculation_run_at: DateTime
// status: enum('Calculated', 'PendingPayment', 'Paid', 'Error', 'RequiresRecalculation')
// IMPORTANT: All personal data (bank, tax ID) for this calculation MUST be sourced from
// the ProjectParticipantData snapshot associated with the project_relationship_id for this project.
// error_message?: string // If calculation failed
}
8. CHART OF ACCOUNTS & TAXES¶
ChartOfAccount {
id: UUID // PK
account_number: string // e.g., "10100", "60210-001" (Often hierarchical)
name: string
type: enum('Asset', 'Liability', 'Equity', 'Income', 'Expense', 'CostOfGoodsSold', 'Revenue')
parent_account_id?: UUID // FK to ChartOfAccount.id (for hierarchical COA)
project_id?: UUID // FK to Project.id (if COA is project-specific, otherwise null for global/template COA)
is_active: boolean
description?: string
// normal_balance: enum('Debit', 'Credit')
// allow_direct_posting?: boolean // Some accounts are summary/control accounts
// reporting_category?: string // For custom reporting groupings
}
TaxScheme { // e.g., "UK VAT", "California Sales Tax", "Canadian GST/HST"
id: UUID // PK
name: string
country_code: string // FK to Country.code
// state_province_code?: string // If specific to a sub-region
// description?: string
// tax_authority_name?: string
is_active: boolean
}
TaxRate {
id: UUID // PK
tax_scheme_id: UUID // FK to TaxScheme.id
name: string // e.g., "Standard Rate", "Reduced Rate", "Zero Rate"
rate_percent: number
effective_date: Date
expiration_date?: Date
is_default_for_scheme?: boolean
// tax_account_id_payable?: UUID // FK to ChartOfAccount.id (for tax collected/payable)
// tax_account_id_recoverable?: UUID // FK to ChartOfAccount.id (for tax paid/recoverable)
// description?: string
}
TaxCreditAssessment {
id: UUID // PK
project_id: UUID // FK to Project.id
tax_scheme_id: UUID // FK to TaxScheme.id (e.g., "UK Film Tax Credit 2025", "Ontario Production Services Tax Credit")
assessment_date: Date
assessed_by_user_id?: UUID // FK to User.id
status: enum('Draft', 'PendingReview', 'ApprovedForApplication', 'ApplicationSubmitted', 'QueriedByAuthority', 'ApprovedByAuthority', 'CreditReceived', 'Rejected')
total_qualifying_expenditure_estimate?: number
estimated_credit_amount?: number
actual_credit_amount_received?: number
cultural_points_achieved?: number // For points-based systems
// specific_criteria_met_json?: JSONB // {"criterion_A_passed": true, "criterion_B_score": 5, "regional_uplift_applied": true }
notes?: string
application_submission_date?: Date
// Supporting documents can be linked via the Attachment table:
// Attachment.parent_entity_type = 'TaxCreditAssessment', Attachment.parent_entity_id = TaxCreditAssessment.id
created_at: DateTime
updated_at: DateTime
}
9. MISCELLANEOUS SUPPORTING TABLES¶
Unit { // Represents a shooting unit, e.g., "Main Unit", "Second Unit", "VFX Unit"
id: UUID // PK
project_id: UUID // FK to Project.id
name: string
description?: string
// unit_manager_project_relationship_id?: UUID // FK to ProjectRelationship.id
}
UnitOfMeasure { // e.g., Day, Week, Hour, Item, Meter, KG, Lump Sum, Roll, Foot
id: UUID // PK
name: string // Unique
abbreviation: string // Unique
category: enum('Time', 'Length', 'Weight', 'Quantity', 'Currency', 'Area', 'Volume', 'Data', 'Other')
// conversion_factor_to_base_unit?: number // If supporting conversions
// base_unit_id?: UUID // FK to UnitOfMeasure.id (if this unit is derived from a base)
}
Department { // Production departments, e.g., "Camera", "Art Department", "Sound", "Post-Production"
id: UUID // PK
project_id?: UUID // FK to Project.id (if department is project-specific, else global template)
name: string
department_code?: string // e.g., "CAM", "ART"
head_of_department_project_relationship_id?: UUID // FK to ProjectRelationship.id (linking to the HoD for a specific project)
// parent_department_id?: UUID // FK to Department.id (for sub-departments)
}
Union { // e.g., DGA, SAG-AFTRA, IATSE, BECTU
id: UUID // PK
name: string // Unique
short_code?: string // e.g., "DGA"
// country_code?: string // FK to Country.code
// website_url?: string
// contact_info_json?: JSONB
}
Attachment { // Generic attachment for any file, linked to a parent entity
id: UUID // PK
parent_entity_type: string // e.g., 'Transaction', 'Contract', 'OnboardingTaskInstance', 'ScriptVersion', 'CrewProfile', 'Project', 'TaxCreditAssessment'
parent_entity_id: UUID // ID of the instance of parent_entity_type
document_category_id?: UUID // FK to DocumentCategory.id (for classification)
file_name_original: string
file_name_stored: string // Could be a hashed name or UUID
storage_path_or_url: string // e.g., S3 bucket key, local file path, CDN URL
mime_type: string
file_size_bytes: number
description?: string
version_identifier?: string // Simple versioning if not using full DocumentVersion table for this type of attachment
uploaded_by_user_id: UUID // FK to User.id
uploaded_at: DateTime
// tags_json?: JSONB // ["receipt", "invoice_123", "set_photo"]
// expires_at?: DateTime // For temporary attachments
// access_control_json?: JSONB // If more granular access is needed than parent entity implies
}
DocumentCategory { // For classifying attachments/documents, e.g., "Invoice", "Contract", "Deal Memo", "Script", "Storyboard", "Insurance Certificate"
id: UUID // PK
name: string // Unique
description?: string
// default_retention_policy_days?: number
}
Document { // For formally managed documents that require versioning, review, and explicit lifecycle management (can be global or project-specific)
id: UUID // PK
project_id?: UUID // FK to Project.id (if project-specific document)
document_category_id: UUID // FK to DocumentCategory.id (more formal than just Attachment's category)
title: string
current_version_id?: UUID // FK to DocumentVersion.id (points to the active/latest approved version)
// status: enum('Draft', 'Active', 'Archived', 'Superseded') // Overall status of the document
// owner_user_id?: UUID // FK to User.id
// review_cycle_template_id?: UUID // FK to a review workflow template
created_at: DateTime
updated_at: DateTime
}
DocumentVersion {
id: UUID // PK
document_id: UUID // FK to Document.id
version_number: string // e.g., "1.0", "2.1", "Draft 3"
status: enum('Draft', 'InReview', 'Approved', 'Active', 'Superseded', 'Archived', 'Rejected')
attachment_id: UUID // FK to Attachment.id (the actual file for this version)
change_notes?: string // Summary of changes in this version
effective_date?: Date
// reviewed_by_user_id?: UUID // FK to User.id
// review_approved_at?: DateTime
created_at: DateTime
created_by_user_id: UUID // FK to User.id
}
AuditLog { // For tracking changes and significant actions in the system
id: UUID // PK
timestamp: DateTime
user_id?: UUID // FK to User.id (who performed the action, null if system action)
impersonator_user_id?: UUID // FK to User.id (if an admin was impersonating another user)
action_type: enum('CREATE', 'UPDATE', 'DELETE', 'LOGIN_SUCCESS', 'LOGIN_FAILURE', 'VIEW', 'DOWNLOAD', 'UPLOAD', 'APPROVE', 'REJECT', 'SEND_MESSAGE', 'SYSTEM_PROCESS_START', 'SYSTEM_PROCESS_END')
entity_type?: string // e.g., "Project", "Transaction", "User"
entity_id?: UUID // ID of the affected entity
project_id?: UUID // FK to Project.id (if action is project-related, for easier scoping of audit trails)
old_values_json?: JSONB // For UPDATE actions, the state before change (selected fields)
new_values_json?: JSONB // For UPDATE/CREATE actions, the state after change (selected fields)
description?: string // Human-readable description of the action, e.g., "User X updated budget Y"
ip_address?: string
user_agent?: string
// status_code?: integer // e.g. HTTP status code for API calls
// request_id?: UUID // For correlating related log entries
}
🎬 Scheduling & Production Data Model¶
1. PRODUCTION ITEMS & ASSETS¶
ProductionItem { // Any item needed for production, e.g., Prop, Costume, Set Piece, Vehicle, Animal
id: UUID // PK
project_id: UUID // FK to Project.id
item_type_id?: UUID // FK to a new ProductionItemType table (e.g. Prop, Wardrobe, SetDressing)
name: string
description?: string
// quantity_needed?: number
// status: enum('Planned', 'Sourced', 'InUse', 'Wrapped', 'Returned')
// source_type: enum('Rental', 'Purchase', 'Loan', 'ExistingStock')
// supplier_company_id?: UUID // FK to Company.id
// linked_asset_id?: UUID // FK to Asset.id (if it's a specific inventoried asset)
// linked_script_element_ids_json?: JSONB // [UUID, UUID] (FKs to ScriptElement.id)
// notes?: string
}
// Optional: ProductionItemType
// ProductionItemType { id: UUID, name: string, description?: string }
Asset { // A specific, uniquely identifiable physical or digital item owned or managed by the production/company
id: UUID // PK
// asset_category_id?: UUID // FK to AssetCategory.id (e.g. Camera, Lens, Vehicle, SoftwareLicense)
asset_tag_id: string // Unique identifier/barcode for the asset
name: string
description?: string
serial_number?: string
model_number?: string
manufacturer_company_id?: UUID // FK to Company.id
purchase_date?: Date
purchase_cost?: number
currency_code?: string // FK to Currency.code
current_value?: number
// current_condition: enum('New', 'Good', 'Fair', 'Poor', 'NeedsRepair', 'Decommissioned')
// owner_company_id?: UUID // FK to Company.id (Which company owns this asset)
// current_custodian_person_id?: UUID // FK to Person.id (Who currently has it)
// current_stock_location_id?: UUID // FK to StockLocation.id (Where it's currently stored)
// warranty_expiration_date?: Date
// notes?: string
// is_consumable: boolean // Differentiates from ConsumableItemType for bulk items
created_at: DateTime
updated_at: DateTime
}
// Optional: AssetCategory
// AssetCategory { id: UUID, name: string, description?: string, parent_category_id?: UUID }
AssetConditionLog {
id: UUID // PK
asset_id: UUID // FK to Asset.id
log_date: DateTime
condition_status: enum('New', 'Good', 'Fair', 'Poor', 'NeedsRepair', 'Repaired', 'Decommissioned', 'Lost', 'Stolen')
description?: string // Details of condition, damage, repair
reported_by_user_id: UUID // FK to User.id
// repair_cost?: number
// related_transaction_id?: UUID // FK to Transaction.id (for repair costs)
}
AssetMaintenanceSchedule {
id: UUID // PK
asset_id: UUID // FK to Asset.id
maintenance_type: string // e.g., "Sensor Cleaning", "Oil Change", "Software Update"
due_date?: Date
frequency_days?: number // e.g., every 30 days
last_performed_date?: Date
next_due_date_calculated?: Date
assigned_to_user_id?: UUID // FK to User.id
status: enum('Scheduled', 'InProgress', 'Completed', 'Overdue', 'Skipped')
notes?: string
}
AssetKitDefinition { // Defines a standard package of assets, e.g., "Standard Camera Package A"
id: UUID // PK
name: string
description?: string
// total_replacement_value?: number
}
AssetKitComponent { // Links an Asset (type) to a Kit Definition, specifying quantity
id: UUID // PK
asset_kit_definition_id: UUID // FK to AssetKitDefinition.id
// asset_category_id?: UUID // FK to AssetCategory.id (e.g., "Lens") OR
// specific_asset_id?: UUID // FK to Asset.id (if a specific serial number is always part of this kit type - less common)
production_item_type_id?: UUID // FK to ProductionItemType.id (e.g. "PL Mount Lens")
quantity: number
description_of_component?: string // e.g. "Prime Lens Set (24,35,50,85mm)"
}
AssetKitInstance { // A specific instance of a kit that has been assembled for use
id: UUID // PK
project_id: UUID // FK to Project.id
asset_kit_definition_id: UUID // FK to AssetKitDefinition.id (what type of kit this is)
kit_instance_identifier: string // e.g., "CameraKit_001_MainUnit"
status: enum('Assembling', 'ReadyForPickup', 'CheckedOut', 'InUse', 'Returned', 'NeedsInspection', 'Decommissioned')
// current_custodian_person_id?: UUID // FK to Person.id
// current_location_description?: string
// notes?: string
}
AssetKitInstanceMember { // Links a specific Asset (instance) to an AssetKitInstance
asset_kit_instance_id: UUID // FK to AssetKitInstance.id
asset_id: UUID // FK to Asset.id (the specific serial-numbered item)
// PRIMARY KEY (asset_kit_instance_id, asset_id)
// checked_out_at?: DateTime
// returned_at?: DateTime
// condition_on_checkout?: string
// condition_on_return?: string
}
ConsumableItemType { // e.g., "Gaffer Tape", "AA Batteries", "Bottled Water", "Lumber 2x4"
id: UUID // PK
name: string
description?: string
unit_of_measure_id: UUID // FK to UnitOfMeasure.id (e.g., "Roll", "Pack", "Bottle", "Piece")
// default_supplier_company_id?: UUID // FK to Company.id
// reorder_threshold_quantity?: number
// typical_cost_per_unit?: number
}
StockLocation { // Where items (assets or consumables) are stored, e.g., "Main Warehouse Shelf A", "Production Office Room 3"
id: UUID // PK
project_id?: UUID // FK to Project.id (if location is project-specific)
name: string
description?: string
// address_id?: UUID // FK to an Address table if more complex locations needed
// is_secure_storage?: boolean
}
ConsumableStock { // Inventory of a specific consumable type at a specific location
id: UUID // PK
consumable_item_type_id: UUID // FK to ConsumableItemType.id
stock_location_id: UUID // FK to StockLocation.id
quantity_on_hand: number
last_updated_at: DateTime // When quantity was last changed
// average_cost_per_unit?: number
// last_purchase_transaction_id?: UUID // FK to Transaction.id
}
ConsumableLog { // Tracks usage or adjustment of consumables
id: UUID // PK
project_id: UUID // FK to Project.id
consumable_item_type_id: UUID // FK to ConsumableItemType.id
stock_location_id?: UUID // FK to StockLocation.id (from where it was taken/added)
log_type: enum('Usage', 'AdjustmentIn', 'AdjustmentOut', 'PurchaseReceipt', 'ReturnToStock', 'Spoilage')
quantity_changed: number // Positive for additions, negative for usage/removals
transaction_date: DateTime
logged_by_user_id: UUID // FK to User.id
// related_scene_id?: UUID // FK to Scene.id (if usage tied to a scene)
// related_department_id?: UUID // FK to Department.id
// notes?: string
// related_transaction_id?: UUID // FK to Transaction.id (if a purchase or costed adjustment)
}
2. SCRIPT & SCENES¶
Script {
id: UUID // PK
project_id: UUID // FK to Project.id
title: string // Usually same as project title, but could be "Shooting Script"
// author_person_id?: UUID // FK to Person.id
// registration_number?: string // e.g., WGA registration
created_at: DateTime
updated_at: DateTime
}
ScriptVersion {
id: UUID // PK
script_id: UUID // FK to Script.id
version_number: string // e.g., "1.0", "Blue Revision", "Final Locked"
revision_date: Date
// description?: string // e.g., "Added scene 12A, dialogue changes in scene 5"
// color_code?: string // For colored script pages, e.g., "Blue", "Pink"
attachment_id?: UUID // FK to Attachment.id (the script file itself, e.g., PDF, Final Draft file)
page_count?: number
// is_shooting_script?: boolean
// is_locked?: boolean
created_at: DateTime
uploaded_by_user_id: UUID // FK to User.id
}
Scene {
id: UUID // PK
script_version_id: UUID // FK to ScriptVersion.id (belongs to a specific version of the script)
scene_number: string // e.g., "1", "10A", "S101" (can be alphanumeric)
int_ext: enum('INT', 'EXT', 'INT_EXT', 'EXT_INT', 'OTHER')
location_setting: string // Textual description from script, e.g., "JOE'S APARTMENT - BEDROOM"
time_of_day: enum('DAY', 'NIGHT', 'DAWN', 'DUSK', 'MORNING', 'AFTERNOON', 'EVENING', 'CONTINUOUS', 'LATER', 'OTHER')
synopsis?: string // Brief description of the scene's action
page_number_start?: string // e.g., "1", "1 1/8"
page_number_end?: string // e.g., "2 3/8"
estimated_duration_on_screen_seconds?: number // Estimated length in the final film
// notes?: string // Production notes for this scene
planned_location_id?: UUID // FK to Location.id (planned or actual shooting location)
// set_id?: UUID // FK to a Set table if sets are managed entities
display_order?: number // For ordering scenes as they appear in the script
}
Storyboard {
id: UUID // PK
scene_id: UUID // FK to Scene.id
shot_number_in_scene: number // e.g., 1, 2, 3 for ordering within the scene
description?: string // Description of the shot
// camera_angle?: string
// shot_type?: string // e.g., "Close Up", "Wide Shot"
// movement?: string // e.g., "Pan Right", "Dolly In"
// dialogue_snippet?: string
// sound_notes?: string
attachment_id?: UUID // FK to Attachment.id (the storyboard image/panel)
// thumbnail_url?: string
created_at: DateTime
// created_by_user_id: UUID // FK to User.id
}
ScriptElement { // Represents a breakdown element within a scene (Character, Prop, Costume, Set Dressing, etc.)
id: UUID // PK
scene_id: UUID // FK to Scene.id
element_type: enum('Character', 'Prop', 'Costume', 'SetDressing', 'MakeupHair', 'Vehicle', 'Animal', 'SpecialEffect', 'SoundEffect', 'MusicCue', 'Stunt', 'LocationNote', 'CameraNote', 'Other')
name_or_description: string // e.g., "John Doe", "Red Scarf", "Vintage Lamp", "Rain Machine"
// quantity?: number
// notes?: string // Specific notes about this element in this scene
// is_key_element?: boolean
// linked_character_id?: UUID // FK to Character.id (if element_type is 'Character')
// linked_production_item_id?: UUID // FK to ProductionItem.id (if this script element corresponds to a managed production item)
}
Character { // Represents a character in the script
id: UUID // PK
script_id: UUID // FK to Script.id (Character exists across versions, but might be first introduced in one)
name: string // Character name, e.g., "John Doe"
description?: string // Brief character bio or notes
// actor_person_id?: UUID // FK to Person.id (the actor cast for this role) - often via ProjectRelationship
// character_type: enum('Lead', 'Supporting', 'Recurring', 'Guest', 'BackgroundSpeaking', 'BackgroundNonSpeaking')
}
3. LOCATIONS¶
Location { // Represents a real-world shooting location or a studio/stage
id: UUID // PK
project_id?: UUID // FK to Project.id (if scouted/used for a specific project, or null for global location library)
name: string // e.g., "Griffith Observatory", "Studio B - Main Street Set"
address_street?: string
address_city?: string
address_state_province?: string
address_postal_code?: string
address_country_code: string // FK to Country.code
latitude?: number
longitude?: number
// contact_person_id?: UUID // FK to Person.id (Location manager or owner contact)
// contact_company_id?: UUID // FK to Company.id
// phone?: string
// email?: string
// description?: string // Notes about the location, look, feel
// status: enum('Scouted', 'Considering', 'Approved', 'Booked', 'Wrapped', 'Rejected')
// type: enum('Exterior', 'Interior', 'Studio', 'Practical', 'Backlot')
// parking_info?: string
// power_availability?: string
// sound_issues?: string
// accessibility_notes?: string
// cost_per_day?: number
// currency_code?: string // FK to Currency.code
// permit_requirements?: string
// photos_attachment_ids_json?: JSONB // [UUID, UUID] (FKs to Attachment.id)
created_at: DateTime
updated_at: DateTime
// created_by_user_id: UUID // FK to User.id
}
4. SCHEDULING¶
Schedule {
id: UUID // PK
project_id: UUID // FK to Project.id
title: string // e.g., "Main Unit Shooting Schedule", "Post-Production Edit Schedule"
// schedule_type: enum('Shooting', 'PostProduction', 'Marketing', 'OverallMaster')
// start_date?: Date
// end_date?: Date
// total_shooting_days?: number
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
}
ScheduleVersion {
id: UUID // PK
schedule_id: UUID // FK to Schedule.id
version_number: string // e.g., "v1.0", "Stripboard Rev 3"
title: string // e.g., "Locked for Pre-Call", "Revised due to Location Change"
status: enum('Draft', 'Proposed', 'Approved', 'Active', 'Superseded', 'Archived')
// effective_date?: Date
// notes?: string
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
}
ProductionDay { // Represents a single day in the schedule (e.g., Day 1 of 30)
id: UUID // PK
schedule_version_id: UUID // FK to ScheduleVersion.id
day_number: number // e.g., 1, 2, 3...
date_of_production: Date
day_of_week?: enum('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')
// day_type: enum('ShootingDay', 'TravelDay', 'RehearsalDay', 'PreLightDay', 'OffDay', 'PostDay')
// unit_id?: UUID // FK to Unit.id (if different units have different schedules)
// general_crew_call_time?: Time
// notes?: string // General notes for the day
// sunrise_time?: Time
// sunset_time?: Time
// weather_forecast_json?: JSONB
}
SceneDayAssignment { // Links a Scene to be shot on a specific ProductionDay
id: UUID // PK
production_day_id: UUID // FK to ProductionDay.id
scene_id: UUID // FK to Scene.id
// estimated_setup_time_minutes?: number
// estimated_shoot_time_minutes?: number
// estimated_wrap_time_minutes?: number
// actual_start_time?: DateTime
// actual_end_time?: DateTime
// pages_to_shoot?: string // e.g., "1 2/8"
// status: enum('Scheduled', 'InProgress', 'Completed', 'PartiallyCompleted', 'Rescheduled', 'Cancelled')
// notes?: string // Notes specific to shooting this scene on this day
// shooting_order_on_day?: number // Sequence of scenes for the day
// location_id_for_day?: UUID // FK to Location.id (if different from Scene's primary planned location)
}
SchedulingConstraint { // Represents constraints like actor availability, location permits, etc.
id: UUID // PK
schedule_version_id: UUID // FK to ScheduleVersion.id
constraint_type: enum('ActorAvailability', 'LocationAvailability', 'EquipmentAvailability', 'CrewAvailability', 'SunriseSunset', 'ChildLaborLaw', 'UnionRule', 'SpecialEvent', 'WeatherDependent', 'Other')
description: string
// severity: enum('Critical', 'High', 'Medium', 'Low')
// affected_entity_type?: string // e.g., 'Person', 'Location', 'Asset'
// affected_entity_id?: UUID // FK to the relevant table
// affected_scene_id?: UUID // FK to Scene.id
// affected_production_day_id?: UUID // FK to ProductionDay.id
start_datetime_constraint?: DateTime
end_datetime_constraint?: DateTime
// is_hard_constraint?: boolean // If true, cannot be violated
// resolution_notes?: string
// status: enum('Active', 'Resolved', 'Waived')
}
5. PRODUCTION PHASES¶
ProductionPhase { // e.g., Development, Pre-Production, Principal Photography, Post-Production, Distribution
id: UUID // PK
project_id: UUID // FK to Project.id
name: string
// phase_code?: string
// description?: string
planned_start_date?: Date
planned_end_date?: Date
actual_start_date?: Date
actual_end_date?: Date
// status: enum('Planned', 'InProgress', 'Completed', 'OnHold', 'Cancelled')
// budget_allocation_amount?: number
// primary_manager_project_relationship_id?: UUID // FK to ProjectRelationship.id
}
6. BOOKINGS (Resource Reservations)¶
Booking {
id: UUID // PK
project_id: UUID // FK to Project.id
resource_type: enum('Asset', 'AssetKitInstance', 'Location', 'Person', 'ConsumableItemType', 'Vehicle', 'StudioStage', 'MeetingRoom', 'Other')
resource_id: UUID // FK to the specific Asset, Location, global Person.id, AssetKitInstance.id etc.
// If booking a 'Person', their availability might be checked from their global calendar,
// but project-specific work hours/rules come from ProjectRelationship or SchedulingConstraints.
// The booking here represents a confirmed allocation for the project.
title_or_purpose: string // e.g., "Camera A for Main Unit", "John Doe - ADR Session"
start_datetime: DateTime
end_datetime: DateTime
status: enum('Tentative', 'Confirmed', 'Cancelled', 'InProgress', 'Completed', 'RequiresAttention')
// booked_by_user_id: UUID // FK to User.id
// notes?: string
// quantity?: number // For bookings of consumable types or multiple identical non-unique items
// related_scene_id?: UUID // FK to Scene.id
// related_department_id?: UUID // FK to Department.id
// confirmation_number?: string // From external supplier if applicable
// cost_estimate?: number
// actual_cost?: number
// related_transaction_id?: UUID // FK to Transaction.id (for booking fees)
created_at: DateTime
updated_at: DateTime
}
7. CALL SHEETS¶
CallSheet {
id: UUID // PK
project_id: UUID // FK to Project.id
production_day_id: UUID // FK to ProductionDay.id (Which day this call sheet is for)
schedule_version_id: UUID // FK to ScheduleVersion.id (Based on which version of the schedule)
call_sheet_version: number // e.g., 1, 2 (for revisions of the call sheet for a single day)
// title?: string // e.g., "Call Sheet - Day 5 - Main Unit"
issue_date: DateTime // When the call sheet was distributed
// status: enum('Draft', 'Issued', 'Superseded')
general_crew_call_time: Time
shooting_call_time?: Time // First shot planned
// breakfast_time?: Time
// lunch_time?: Time
// estimated_wrap_time?: Time
weather_forecast_json?: JSONB // { high_temp: "25C", low_temp: "15C", conditions: "Sunny", wind: "10mph W" }
sunrise_time?: Time
sunset_time?: Time
// nearest_hospital_json?: JSONB // { name: "City General", address: "...", phone: "..." }
// production_office_contact_json?: JSONB // { name: "Jane Doe", phone: "..." }
// security_contact_json?: JSONB // { name: "Security Team", phone: "..." }
// general_notes?: string // Important notes for everyone
// parking_instructions?: string
// walkie_channel_assignments_json?: JSONB // { "Channel 1": "Director/DP/AD", "Channel 2": "Camera" }
// emergency_procedures_summary?: string
created_at: DateTime
updated_at: DateTime
created_by_user_id: UUID // FK to User.id
// approved_by_user_id?: UUID // FK to User.id
}
CallSheetScene { // Scenes scheduled for the day, as they appear on the call sheet
id: UUID // PK
call_sheet_id: UUID // FK to CallSheet.id
scene_id: UUID // FK to Scene.id
scene_number_on_callsheet: string // How it's displayed (e.g., "Sc. 25A")
synopsis_on_callsheet?: string // May be truncated or specific for call sheet
location_on_callsheet?: string // e.g. "INT. JOE'S APARTMENT - DAY (LOCATION: STUDIO A)"
pages_to_shoot_on_callsheet?: string // e.g., "1 3/8 pgs"
estimated_time_on_callsheet?: string // e.g., "2 hrs"
// cast_on_call_for_scene_ids_json?: JSONB // [ProjectRelationship.id, ...]
// elements_needed_for_scene_summary?: string // e.g., "Props: Gun, Briefcase. Wardrobe: Suit."
shooting_order_on_callsheet?: number
}
CallSheetPersonnelCall { // Individual calls for cast, crew, stand-ins, etc.
id: UUID // PK
call_sheet_id: UUID // FK to CallSheet.id
project_relationship_id: UUID // FK to ProjectRelationship.id (identifies the person and their project role)
// Contact details (phone, email) for this call sheet MUST be sourced from the
// ProjectParticipantData snapshot associated with project_relationship_id for this project.
role_title_on_callsheet: string // From ProjectRelationship.role_title_override or RoleDefinition.title
department_name_on_callsheet?: string // From Department.name
call_time: Time
// makeup_hair_call_time?: Time
// on_set_ready_time?: Time
// pickup_time?: Time
// pickup_location?: string
// transport_notes?: string // e.g., "Self-drive, parking at Lot B"
notes?: string // Specific instructions for this person for the day
// status_on_call: enum('Work', 'Travel', 'Hold', 'Finish', 'NonWorkDayCall')
// display_order_in_department?: number
}
8. LOGGING (Time, Assets, Production Reports)¶
LogEntry { // Generic log entry for various activities, can be specialized by other tables
id: UUID // PK
project_id: UUID // FK to Project.id
log_type: enum('TimeLog', 'AssetMovement', 'SafetyIncident', 'ProductionNote', 'Communication', 'SystemEvent', 'Custom')
timestamp: DateTime
// summary?: string
// details_json?: JSONB
// related_person_id?: UUID // FK to global Person.id (Person involved or subject of log)
// related_asset_id?: UUID // FK to Asset.id
// related_location_id?: UUID // FK to Location.id
// related_scene_id?: UUID // FK to Scene.id
// related_department_id?: UUID // FK to Department.id
// created_by_user_id: UUID // FK to User.id
}
Timecard { // Groups TimeLogDetails for a person (via ProjectRelationship) for a payroll period
id: UUID // PK
payroll_period_id: UUID // FK to PayrollPeriod.id
project_relationship_id: UUID // FK to ProjectRelationship.id (identifies person, project, role)
status: enum('Draft', 'Submitted', 'Approved', 'Rejected', 'Processed', 'RequiresAdjustment', 'Archived')
total_regular_hours?: number
total_ot1_hours?: number // Overtime Tier 1
total_ot2_hours?: number // Overtime Tier 2
total_meal_penalty_hours?: number
// ... other hour summaries (e.g., travel hours, holiday hours)
total_gross_pay_estimate?: number // Preliminary estimate based on hours and known rates
submitted_at?: DateTime
approved_by_user_id?: UUID // FK to User.id
approved_at?: DateTime
rejection_reason?: string
// notes_for_payroll?: string
// attachment_id?: UUID // FK to Attachment (e.g., scanned paper timecard if used as backup)
created_at: DateTime
updated_at: DateTime
}
TimeLogDetail { // Individual work segment or break, linked to a Timecard
id: UUID // PK
timecard_id: UUID // FK to Timecard.id
// project_relationship_id is on Timecard, identifying person for project
start_datetime: DateTime
end_datetime: DateTime
duration_minutes?: number // Calculated: end_datetime - start_datetime
activity_type: enum('WorkRegular', 'WorkOvertime1', 'WorkOvertime2', 'Travel', 'MealBreakUnpaid', 'MealBreakPaid', 'OtherBreak', 'Standby', 'Rehearsal', 'Meeting')
activity_description?: string // e.g., "Setup Lights Scene 5", "Travel to Location B", "Lunch"
// linked_scene_id?: UUID // FK to Scene.id (if work was for a specific scene)
// linked_location_id?: UUID // FK to Location.id (physical location of work)
notes?: string
// GPS Coordinates for logging start/end
start_latitude?: number
start_longitude?: number
end_latitude?: number
end_longitude?: number
start_location_description?: string // If GPS not available/used, or for context (e.g., "Studio A")
end_location_description?: string // If GPS not available/used
is_billable?: boolean // Defaults to true for work types
// is_overtime_eligible?: boolean // Based on rules, could be auto-calculated
entry_method: enum('ManualSystemUI', 'ManualWhatsApp', 'AppClockIn', 'SystemGeneratedBreak', 'BulkImport')
created_at: DateTime
updated_at: DateTime
// updated_by_user_id?: UUID // FK to User.id (If edits are allowed post-submission by authorized personnel)
}
AssetActivityDetail { // Specific log for asset movements or usage, linked to LogEntry
log_entry_id: UUID // PK, FK to LogEntry.id (where LogEntry.log_type = 'AssetMovement')
asset_id: UUID // FK to Asset.id
action: enum('Checkout', 'Checkin', 'Transfer', 'UsageReport', 'Maintenance', 'Sighting')
// from_location_id?: UUID // FK to StockLocation.id or Location.id
// to_location_id?: UUID // FK to StockLocation.id or Location.id
from_location_description?: string // Textual description
to_location_description?: string // Textual description
// target_person_id?: UUID // FK to global Person.id (who received/returned the asset)
// condition_notes?: string
// expected_return_date?: Date
}
ProductionReport { // Daily or End-of-Day report summarizing key production activities
id: UUID // PK
project_id: UUID // FK to Project.id
production_day_id: UUID // FK to ProductionDay.id (Report for this specific day)
// report_date: Date // Usually same as ProductionDay.date_of_production
// report_number?: string // e.g., "PR-005"
// unit_id?: UUID // FK to Unit.id
// prepared_by_user_id: UUID // FK to User.id (e.g., AD, Production Coordinator)
// approved_by_user_id?: UUID // FK to User.id (e.g., Production Manager)
// general_summary?: string // Overall summary of the day
// scenes_shot_ids_json?: JSONB // [Scene.id, ...] (Completed scenes)
// scenes_partially_shot_ids_json?: JSONB // [Scene.id, ...]
// scenes_not_shot_ids_json?: JSONB // [Scene.id, ...] (Scheduled but not shot)
// total_pages_shot?: string // e.g., "5 1/8"
// total_setups?: number
// total_takes?: number
// first_shot_time?: Time
// last_shot_time?: Time
// meal_break_1_start_time?: Time
// meal_break_1_end_time?: Time
// meal_break_2_start_time?: Time // (if applicable)
// meal_break_2_end_time?: Time
// cast_wrap_times_json?: JSONB // [{ project_relationship_id: UUID, wrap_time: Time, status: "Wrapped for day/project" }, ...]
// crew_wrap_times_json?: JSONB // Similar for key crew or departments
// delays_encountered_json?: JSONB // [{ reason: "Weather", duration_minutes: 60, notes: "..." }, ...]
// accidents_incidents_summary?: string // Or link to separate SafetyIncidentLog entries
// footage_report_json?: JSONB // { rolls_shot: 10, total_footage_minutes: 120, sound_rolls: 5 }
// camera_reports_summary_json?: JSONB // [{ camera_id: "A Cam", operator_person_id: UUID, rolls: ["A001", "A002"], notes: "..." }, ...]
// script_supervisor_notes?: string
// continuity_photos_attachment_ids_json?: JSONB // [Attachment.id, ...]
// notes_for_next_day?: string
created_at: DateTime
updated_at: DateTime
}
🌍 Internationalization & Regionalization Model¶
Country {
code: string // PK, ISO 3166-1 alpha-2 code, e.g., "US", "GB", "CA"
name: string // e.g., "United States", "United Kingdom", "Canada"
// official_name?: string
// currency_code_default?: string // FK to Currency.code
// language_codes_primary_json?: JSONB // ["en", "es"] (ISO 639-1 codes)
// calling_code?: string // e.g., "+1", "+44"
// national_holidays_json?: JSONB // For scheduling awareness
// tax_region_id?: UUID // Link to a broader tax region if applicable
}
Currency {
code: string // PK, ISO 4217 code, e.g., "USD", "GBP", "EUR"
name: string // e.g., "US Dollar", "British Pound", "Euro"
symbol: string // e.g., "$", "£", "€"
// decimal_places?: integer // Typically 2
// is_active: boolean // If this currency is actively used in the system
}
CurrencyExchangeRate {
id: UUID // PK
from_currency_code: string // FK to Currency.code
to_currency_code: string // FK to Currency.code
rate: number // 1 FromCurrency = rate * ToCurrency
effective_date: DateTime // Rate is valid from this timestamp
// source?: string // e.g., "Bank API", "Manual Entry"
// fetched_at?: DateTime
}
Language {
code: string // PK e.g. "en-US", "en-GB", "fr-FR", "es-ES" (BCP 47 codes preferred)
name: string // e.g. "English (United States)", "English (United Kingdom)", "French (France)"
native_name?: string // e.g. "English (US)", "Français (France)"
is_active: boolean // If this language is supported by the platform UI/Comms
// text_direction: enum('LTR', 'RTL') // Left-to-Right, Right-to-Left
}
VisaTracking { // For tracking visa status for personnel on international productions
id: UUID // PK
project_id: UUID // FK to Project.id
person_id: UUID // FK to global Person.id
// project_relationship_id: UUID // FK to ProjectRelationship.id (links to specific engagement)
target_country_code: string // FK to Country.code (Country requiring the visa)
visa_type: string // e.g., "Work Permit Tier 2", "O-1 Visa", "Schengen Business Visa"
status: enum('NotRequired', 'ApplicationPending', 'Approved', 'Rejected', 'Expired', 'Revoked', 'Active')
application_date?: Date
approval_date?: Date
expiration_date?: Date
visa_number_encrypted?: string
// issuing_authority?: string
// notes?: string
// supporting_documents_attachment_ids_json?: JSONB // [Attachment.id, ...]
// last_checked_at?: DateTime
// checked_by_user_id?: UUID // FK to User.id
}
🎬 Post-Production Model¶
EditorialProject { // Represents an editing project (e.g., in AVID, Premiere Pro)
id: UUID // PK
project_id: UUID // FK to Project.id (the overall film project)
// name: string // e.g., "Feature Film Edit V1", "Trailer Edit"
// software_used?: string // e.g., "Avid Media Composer", "Adobe Premiere Pro"
// primary_editor_person_id?: UUID // FK to Person.id (or ProjectRelationship.id)
// start_date?: Date
// due_date?: Date
// status: enum('Planned', 'InProgress', 'RoughCutComplete', 'FineCutComplete', 'Locked', 'Delivered')
// notes?: string
// current_sequence_name?: string
// total_runtime_estimate_seconds?: number
}
VFXShot {
id: UUID // PK
project_id: UUID // FK to Project.id
shot_code: string // e.g., "PRJ_SC010_001_VFX", unique identifier for the VFX shot
// scene_id?: UUID // FK to Scene.id
// brief_description?: string
// complexity_level: enum('Simple', 'Medium', 'Complex', 'VeryComplex')
// status: enum('PendingAssignment', 'InProgress', 'PendingReview', 'ApprovedInternal', 'PendingClientReview', 'ApprovedClient', 'Omitted', 'OnHold')
// assigned_artist_person_id?: UUID // FK to Person.id (or ProjectRelationship.id)
// assigned_vendor_company_id?: UUID // FK to Company.id
// due_date?: Date
// estimated_man_days?: number
// actual_man_days?: number
// budget_allocated?: number
// cost_actual?: number
// feedback_notes_json?: JSONB // [{ version: 1, reviewer_id: UUID, notes: "...", timestamp: DateTime}, ...]
// latest_version_media_asset_link_id?: UUID // FK to MediaAssetLink.id
}
MediaAssetLink { // Links to media files (dailies, edits, VFX renders, sound files) stored elsewhere
id: UUID // PK
project_id: UUID // FK to Project.id
// related_entity_type?: string // e.g., 'Scene', 'VFXShot', 'EditorialProject', 'ProductionDay' (for dailies)
// related_entity_id?: UUID
asset_name: string // e.g., "SC010_001_VFX_v003.mov", "Day5_Dailies_CamA_Roll01.mxf"
// asset_type: enum('VideoRaw', 'VideoEdit', 'VFXPlate', 'VFXRender', 'AudioStem', 'StillImage', 'Other')
storage_url_or_path: string // Link to MAM, DAM, cloud storage, or network path
// mime_type?: string
// file_size_bytes?: number
// duration_seconds?: number // For time-based media
// resolution?: string // e.g., "1920x1080", "4K DCI"
// frame_rate?: number
// codec?: string
// version_number?: string // e.g., "v001", "Edit_Pass_2"
// description?: string
// uploaded_at?: DateTime
// linked_by_user_id?: UUID // FK to User.id
// access_permissions_json?: JSONB // Who can view/download
}
🛡️ Compliance, Rights & Communication Model¶
Clearance { // Tracking clearances for music, stock footage, locations, persons, brands, etc.
id: UUID // PK
project_id: UUID // FK to Project.id
item_name: string // e.g., "Song Title 'Yesterday'", "Likeness of John Doe", "Coca-Cola Logo"
item_type: enum('Music', 'Footage', 'Image', 'TalentLikeness', 'BrandLogo', 'LocationRelease', 'ScriptContent', 'Other')
// status: enum('Required', 'Researching', 'Negotiating', 'Cleared', 'Uncleared', 'AlternativeFound', 'NotRequired')
// rights_holder_company_id?: UUID // FK to Company.id
// rights_holder_person_id?: UUID // FK to Person.id
// territory_cleared_for_json?: JSONB // ["Worldwide", "USA_Canada"] or list of Country.codes
// media_cleared_for_json?: JSONB // ["Theatrical", "BroadcastTV", "Streaming", "AllMedia"]
// term_duration_years?: number // Null for perpetual
// term_expiration_date?: Date
// cost_amount?: number
// currency_code?: string // FK to Currency.code
// related_contract_id?: UUID // FK to Contract.id
// clearance_document_attachment_id?: UUID // FK to Attachment.id
// notes?: string
// clearance_request_date?: Date
// clearance_obtained_date?: Date
// cleared_by_user_id?: UUID // FK to User.id
}
InsurancePolicy { // Details of insurance policies covering the production
id: UUID // PK
project_id: UUID // FK to Project.id
// insurer_company_id: UUID // FK to Company.id
policy_number: string
policy_type: enum('GeneralLiability', 'WorkersCompensation', 'ErrorsAndOmissions', 'EquipmentFloater', 'VehicleInsurance', 'Umbrella', 'ProductionPackage', 'Other')
// coverage_amount: number
// currency_code: string // FK to Currency.code
// deductible_amount?: number
effective_date: Date
expiration_date: Date
// broker_company_id?: UUID // FK to Company.id
// broker_contact_person_id?: UUID // FK to Person.id
// premium_amount?: number
// policy_document_attachment_id?: UUID // FK to Attachment.id
// notes?: string
}
InsuranceCertificateInstance { // When a Certificate of Insurance (COI) is issued to a third party
id: UUID // PK
insurance_policy_id: UUID // FK to InsurancePolicy.id (The policy this COI is based on)
// issued_to_company_id?: UUID // FK to Company.id (e.g., Location owner, Equipment rental house)
// issued_to_person_id?: UUID // FK to Person.id
// certificate_holder_name: string
// certificate_holder_address?: string
// effective_date_on_certificate: Date
// expiration_date_on_certificate: Date
// coi_document_attachment_id: UUID // FK to Attachment.id (The COI PDF)
// issue_date: Date
// reason_for_issuance?: string // e.g., "Location Rental - Griffith Park", "Camera Package Rental"
// notes?: string
}
ProductionMessage { // For internal or external communication logging related to the project
id: UUID // PK
project_id: UUID // FK to Project.id
// sender_user_id?: UUID // FK to User.id (If sent by a platform user)
// sender_person_id?: UUID // FK to Person.id (If logged for an external person)
// sender_company_id?: UUID // FK to Company.id (If sent on behalf of a company)
// subject?: string
body_text: string // Could be HTML or plain text
// message_type: enum('Email', 'SMS', 'WhatsApp', 'PlatformNotification', 'CallLog', 'MeetingMinutes', 'Other')
// direction: enum('Incoming', 'Outgoing', 'Internal')
// status: enum('Sent', 'Received', 'Read', 'Failed', 'Pending')
// sent_at?: DateTime
// received_at?: DateTime
// external_message_id?: string // e.g., Email Message-ID from header
// related_thread_id?: UUID // FK to ProductionMessage.id (for threading)
// importance_level: enum('High', 'Medium', 'Low')
// attachment_ids_json?: JSONB // [Attachment.id, ...]
}
ProductionMessageRecipient {
production_message_id: UUID // FK to ProductionMessage.id
// recipient_user_id?: UUID // FK to User.id
recipient_person_id?: UUID // FK to Person.id
recipient_company_id?: UUID // FK to Company.id
// recipient_email_address?: string // If sent to an external email not linked to a Person/User
// recipient_phone_number?: string // If sent to an external phone
// recipient_type: enum('To', 'Cc', 'Bcc', 'Group')
// status: enum('Delivered', 'Read', 'Bounced', 'Unsubscribed')
// read_at?: DateTime
// PRIMARY KEY (production_message_id, /* some combination of recipient fields or a sequence */ )
// This might need a UUID PK if a single message goes to many diverse recipient types.
id: UUID // PK
}
✈️ Travel & Logistics Model¶
TravelArrangement {
id: UUID // PK
project_id: UUID // FK to Project.id
person_id: UUID // FK to global Person.id (The traveler)
// project_relationship_id?: UUID // FK to ProjectRelationship.id (Travel for a specific role)
arrangement_type: enum('Flight', 'Train', 'CarRental', 'Accommodation', 'GroundTransport', 'VisaApplicationService', 'Other')
// description?: string // e.g., "Flight to LAX for John Doe", "Hotel for Jane Smith (3 nights)"
// status: enum('Requested', 'Quoted', 'Booked', 'Confirmed', 'Ticketed', 'Cancelled', 'Completed', 'RequiresAttention')
// supplier_company_id?: UUID // FK to Company.id (Travel agency, airline, hotel)
// booking_reference?: string
// start_datetime?: DateTime // Departure time / Check-in time
// end_datetime?: DateTime // Arrival time / Check-out time
// origin_location_description?: string // e.g., "LHR Airport", "Home Address"
// destination_location_description?: string // e.g., "LAX Airport", "Production Office LA"
// cost_estimate?: number
// cost_actual?: number
// currency_code?: string // FK to Currency.code
// related_transaction_id?: UUID // FK to Transaction.id (for payment)
// notes?: string
// booked_by_user_id?: UUID // FK to User.id
// booking_date?: Date
// travel_policy_adherence_status?: enum('Compliant', 'ExceptionApproved', 'NonCompliant')
// seat_preference?: string
// meal_preference?: string // Sourced from ProjectParticipantData if available, else here
// loyalty_program_number?: string
}