MVP Data Model

---

🎬 Core Production & Financial Data Model (Slimmed Down with Tax)

1. PROJECTS (Core MVP)

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
  title: string
  status: enum('Development', 'PreProduction', 'Production', 'PostProduction', 'Archived')
  // logline?: string
  // genre?: string
  // production_company_id?: UUID // FK to Company.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
  default_currency_code: string // FK to Currency.code
  fixed_exchange_rates_json?: JSONB // { "USD": 0.012, "EUR": 0.011 }
  default_tax_scheme_id?: UUID // FK to TaxScheme.id (Optional: for defaulting tax selection)
}

Questions:

Should we build to a limit of levels or should we do infinite recursion with functions?

2. TRANSACTIONS (Unified Financials) (Core MVP)

Transaction {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  transaction_number: string // System-generated
  type: enum('Expense', 'APInvoice')
  status: enum('Draft', 'Submitted', 'Reviewed', 'AutoLinked', 'NeedsReview', 'Void')
  transaction_date: Date
  // due_date?: Date
  posting_date?: Date
  supplier_company_id?: UUID // FK to Company.id
  // person_id?: UUID // FK to Person.id
  total_amount: number // Gross amount (subtotal_amount + tax_amount)
  tax_amount?: number // Sum of all TransactionLineItem.line_item_tax_amount for this transaction
  subtotal_amount?: number // Sum of all TransactionLineItem.amount (pre-tax) for this transaction
  currency_code: string // FK to Currency.code
  vendor_invoice_number?: string
  notes?: string
  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 (pre-tax)
  budget_item_version_id?: UUID // FK to BudgetItemVersion.id
  tax_rate_id?: UUID // FK to TaxRate.id << MODIFIED/ADDED
  line_item_tax_amount?: number // Calculated: amount * (TaxRate.rate_percent / 100). Can be stored or calculated on the fly. << MODIFIED/ADDED
  // production_item_id?: UUID // FK to ProductionItem.id
}

3. BUDGETS & FORECASTING (Core MVP)

(No direct changes here for tax, as budgets are typically pre-tax costs, and tax is applied at the transaction/expense stage. However, reports might show "budgeted cost + estimated tax" vs "actual cost + actual tax" in the future.)

Budget {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  title: string
  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
  title: string
  status: enum('Draft', 'Active', 'Superseded', 'Archived')
  total_budgeted_cost: number
  notes?: string
  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 {
  id: UUID // PK
  budget_version_id: UUID // FK to BudgetVersion.id
  parent_header_id?: UUID // FK to BudgetHeaderVersion.id
  account_code_prefix?: string
  title: string
  total_estimated_cost: number
}

BudgetItemVersion {
  id: UUID // PK
  header_id: UUID // FK to BudgetHeaderVersion.id
  account_code_suffix?: string
  description: string
  quantity?: number
  rate?: number
  days_weeks_count?: number
  estimated_cost: number
  notes?: string
  linked_script_element_id?: UUID // FK to ScriptElement.id
  linked_production_item_id?: UUID // FK to ProductionItem.id
}

4. PEOPLE, COMPANIES, ROLES & USERS (Core MVP for structure, phased functionality)

(No changes in this section for tax integration at MVP level)

// --- GLOBAL PLATFORM ENTITIES ---
Person {
  id: UUID // PK
  first_name: string
  last_name: string
  email_primary: string
  phone_primary?: string
  created_at: DateTime
  updated_at: DateTime
}

Company {
  id: UUID // PK
  name: string
  created_at: DateTime
  updated_at: DateTime
}

User {
  id: UUID // PK
  person_id?: UUID // FK to Person.id
  email: string
  hashed_password?: string
  is_active: boolean
  last_login_at?: DateTime
  created_at: DateTime
  updated_at: DateTime
}

UserProfileSetting {
  user_id: UUID // PK, FK to User.id
  language_code: string // FK to Language.code
  preferred_theme: enum('Light', 'Dark', 'SystemDefault')
  timezone_id: string
  updated_at: DateTime
}

// --- PROJECT-SPECIFIC PARTICIPATION & DATA SNAPSHOTS ---
ProjectRelationship {
  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
  role_title_override?: string
  status: enum('Invited', 'Active', 'Wrapped', 'DeclinedInvite')
}

ProjectParticipantData {
  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 (UNIQUE)
  first_name_snapshot: string
  last_name_snapshot: string
  email_primary_project_snapshot: string
  phone_primary_project_snapshot?: string
  data_captured_at: DateTime
  created_at: DateTime
  updated_at: DateTime
}

// --- ROLES (Global Definitions) ---
RoleDefinition {
  id: UUID // PK
  title: string
  is_active: boolean
  created_at: DateTime
  updated_at: DateTime
}

5. PROJECT ACCESS CONTROL (Core MVP)

(No changes in this section for tax integration at MVP level)

ProjectRoleDefinition {
  id: UUID // PK
  name: string
  description?: string
  created_at: DateTime
  updated_at: DateTime
}

ProjectTeamMember {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  user_id: UUID // FK to User.id
  project_relationship_id: UUID // FK to ProjectRelationship.id (NEWLY ADDED LINK)
  project_role_definition_id: UUID // FK to ProjectRoleDefinition.id
  status: enum('Active', 'Invited', 'Revoked')
  created_at: DateTime
  updated_at: DateTime
}

Permission {
  id: UUID // PK
  permission_key: string
  description?: string
  module: string
}

ProjectRolePermissionLink {
  project_role_definition_id: UUID // FK to ProjectRoleDefinition.id
  permission_id: UUID // FK to Permission.id
}

6. CHART OF ACCOUNTS & TAXES (NEW / MODIFIED FOR MVP)

// ChartOfAccount { // Deferred for MVP as no GL needed for Phase 1
//   id: UUID // PK
//   account_number: string
//   name: string
//   type: enum('Asset', 'Liability', 'Equity', 'Income', 'Expense')
//   project_id?: UUID // FK to Project.id (if project-specific)
//   is_active: boolean
// }

TaxScheme { // (Core MVP) << NEW TABLE
  id: UUID // PK
  name: string // e.g., "India GST", "Default Sales Tax"
  country_code?: string // FK to Country.code (Important for future scalability)
  is_active: boolean
  created_at: DateTime
  updated_at: DateTime
}

TaxRate { // (Core MVP) << NEW TABLE
  id: UUID // PK
  tax_scheme_id: UUID // FK to TaxScheme.id
  name: string // e.g., "Standard Rate (18%)", "Reduced Rate (5%)", "Zero Rate"
  rate_percent: number // Store as a numeric value, e.g., 18.00 for 18%
  // effective_date: Date // Defer for MVP if rates are considered static initially
  // expiration_date?: Date // Defer for MVP
  is_default_for_scheme?: boolean // Could be useful for pre-selection
  created_at: DateTime
  updated_at: DateTime
}

7. MISCELLANEOUS SUPPORTING TABLES (Core MVP / Structural)

(No changes in this section for tax integration at MVP level)

Attachment {
  id: UUID // PK
  parent_entity_type: string
  parent_entity_id: UUID
  file_name_original: string
  file_name_stored: string
  storage_path_or_url: string
  mime_type: string
  file_size_bytes: number
  description?: string
  uploaded_by_user_id: UUID // FK to User.id
  uploaded_at: DateTime
}

// UnitOfMeasure { ... } // Structural Foundation

---

🎬 Scheduling & Production Data Model (Core MVP - for AI-driven budget)

(No changes in this section for tax integration at MVP level)

1. PRODUCTION ITEMS & ASSETS (Core MVP)

ProductionItem {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  item_type_name?: string
  name: string
  description?: string
  quantity_needed?: number
  // ai_generated_image_url?: string
}

2. SCRIPT & SCENES (Core MVP)

Script {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  title: string
  created_at: DateTime
  updated_at: DateTime
}

ScriptVersion {
  id: UUID // PK
  script_id: UUID // FK to Script.id
  version_number: string
  revision_date: Date
  attachment_id?: UUID // FK to Attachment.id
  created_at: DateTime
  uploaded_by_user_id: UUID // FK to User.id
}

Scene {
  id: UUID // PK
  script_version_id: UUID // FK to ScriptVersion.id
  scene_number: string
  int_ext?: enum('INT', 'EXT', 'INT_EXT', 'OTHER')
  location_setting?: string
  time_of_day?: enum('DAY', 'NIGHT', 'DAWN', 'DUSK', 'MORNING', 'AFTERNOON', 'EVENING', 'OTHER')
  synopsis?: string
  planned_location_id?: UUID // FK to Location.id
}

ScriptElement {
  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
}

// Character { ... } // Structural Foundation

3. LOCATIONS (Structural Foundation)

Location {
  id: UUID // PK
  project_id?: UUID // FK to Project.id
  name: string
  created_at: DateTime
  updated_at: DateTime
}

4. SCHEDULING (Core MVP)

Schedule {
  id: UUID // PK
  project_id: UUID // FK to Project.id
  title: string
  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
  title: string
  status: enum('Draft', 'Active', 'Superseded')
  created_at: DateTime
  updated_at: DateTime
  created_by_user_id: UUID // FK to User.id
}

ProductionDay {
  id: UUID // PK
  schedule_version_id: UUID // FK to ScheduleVersion.id
  day_number: number
  date_of_production: Date
}

SceneDayAssignment {
  id: UUID // PK
  production_day_id: UUID // FK to ProductionDay.id
  scene_id: UUID // FK to Scene.id
}

---

🌍 Internationalization & Regionalization Model (Core MVP)

(No changes in this section for tax integration at MVP level, but Country in TaxScheme is relevant)

Country {
  code: string // PK
  name: string
}

Currency {
  code: string // PK
  name: string
  symbol: string
}

// CurrencyExchangeRate { ... } // Structural Foundation

Language {
  code: string // PK
  name: string
}

---