Production & Schedule Tables¶
Schedules (schedules)¶
Purpose: Manages project schedules with version control support
Use Case Example: Create "v1.0" as Draft while planning, then create "v2.0" as Active once approved. Previous versions remain for historical reference.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier |
| project_id | UUID | Reference to the project |
| version_number | NUMERIC | Version number (e.g., 1, 2, 3) |
| title | TEXT | Title for this schedule |
| status | TEXT | Draft/Proposed/Approved/Active/Superseded/Archived |
| created_at | TIMESTAMPTZ | Creation timestamp |
| updated_at | TIMESTAMPTZ | Last update timestamp |
| created_by_user_id | UUID | User who created this schedule |
| updated_by_user_id | UUID | User who last updated this schedule |
Key Features:
- Unique constraint on (project_id, version_number)
- Supports draft and approved workflows
- Maintains version history
Production Phases (production_phases)¶
Purpose: Defines production phases within a project with optional automatic day generation
Use Case Example: Add a "Shooting" phase to a project with a start date of Jan 1st for 30 days. The system automatically generates the individual production days. Phases can also be created without dates for planning purposes.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier |
| project_id | UUID | Reference to project (ON DELETE CASCADE) |
| name | TEXT | Phase name (e.g., Development, Pre-Production) |
| description | TEXT | Optional description of the phase |
| phase_type | production_phase_type | Type of phase: Pre-Production, Shooting, or Post-Production |
| start_date | DATE | Planned start date (nullable - can be set later) |
| number_of_days | INTEGER | Duration in days (nullable, CHECK > 0 - can be set later) |
| end_date | DATE | End date (computed as start_date + number_of_days - 1, nullable) |
| country_code | TEXT | Country where this phase takes place (FK to countries, NOT NULL) |
| production_base | TEXT | Base location for this phase (nullable) |
| created_at | TIMESTAMPTZ | Creation timestamp |
| updated_at | TIMESTAMPTZ | Last update timestamp |
| created_by_user_id | UUID | User who created this phase |
| updated_by_user_id | UUID | User who last updated this phase |
Key Features:
- Now linked directly to projects (replacing schedule_id FK)
- Day generation is skipped when start_date or number_of_days is NULL
- Uses soft-delete approach (is_removed) for day management instead of hard DELETE
end_dateis computed automatically by a BEFORE trigger fromstart_date + number_of_days - 1- Phases are ordered by phase_type (enum order), then start_date, then created_at
- Triggers automatically manage production days
Indexes:
idx_production_phases_project_idonproject_ididx_production_phases_country_codeoncountry_code
Production Days (production_days)¶
Purpose: Individual days within production phases with customizable work schedules
Use Case Example: "Shooting" phase has 30 days. Mark day 15 as a Travel day, and soft-delete days when a phase is shortened so they can be reactivated if the phase is later extended again.
| Column | Type | Description |
|---|---|---|
| id | UUID | Unique identifier |
| production_phase_id | UUID | Reference to production phase |
| day_number | INTEGER | Sequential day number (nullable - only Working days get a number) |
| calendar_date | DATE | Actual calendar date |
| day_of_week | TEXT | Day of week name (Monday-Sunday) - generated from calendar_date |
| day_type | day_type | Type of day: Working, Travel, or Rest (default: Working) |
| notes | TEXT | Notes about the day |
| is_removed | BOOLEAN | Whether this day has been soft-deleted (default: false) |
| removed_at | TIMESTAMPTZ | Timestamp when the day was soft-deleted (nullable) |
| removed_by_user_id | UUID | User who soft-deleted this day (FK to users, nullable) |
| created_at | TIMESTAMPTZ | Creation timestamp |
| updated_at | TIMESTAMPTZ | Last update timestamp |
Key Features:
- Automatically created/updated by phase triggers
- Uses soft-delete (is_removed) instead of hard DELETE when phases are shortened
- Previously removed days are reactivated before creating new ones when phases are extended
day_numberis nullable: only Working days get sequential numbers; Travel/Rest days have NULL- Renumbering is done via the
renumber_working_days(p_phase_id)RPC function - Days are ordered by
calendar_date(notday_number) since day_number is nullable - Unique constraint on
(production_phase_id, day_number)— NULLs allowed - Unique constraint on
(production_phase_id, calendar_date)— DEFERRABLE INITIALLY IMMEDIATE, allowing the trigger to shift dates without intermediate violations
Indexes:
idx_production_days_is_removedonis_removedidx_production_days_removed_by_user_idonremoved_by_user_id