LinkDesk/backend/SCHEMA_UPDATE_SUMMARY.md

Project Schema Update Summary

New Fields Added to Project Model

The project database schema has been updated to include three new required fields:

1. Code Name (code_name)

• Type: String (VARCHAR)
• Constraints: NOT NULL, UNIQUE, Indexed
• Purpose: Unique project identifier/code (e.g., "PROJ_001", "MARVEL_THOR_2024")
• Validation: 1-50 characters, must be unique across all projects

2. Client Name (client_name)

• Type: String (VARCHAR)
• Constraints: NOT NULL, Indexed
• Purpose: Name of the client or studio commissioning the project
• Validation: 1-255 characters

3. Project Type (project_type)

• Type: Enum
• Values:
  • tv - TV Series/Shows
  • cinema - Cinema/Film projects
  • game - Game development projects
• Constraints: NOT NULL
• Purpose: Categorize projects by production type for better organization

Database Migration

A migration script (migrate_project_fields.py) was created and executed to:

• Add the new columns to existing projects table
• Set default values for existing projects:
  • code_name: Generated from project name + ID (e.g., "PROJECT_NAME_001")
  • client_name: Set to "Default Client"
  • project_type: Set to "tv"
• Create unique index on code_name field

Backend Changes

Models (models/project.py)

• Added ProjectType enum with TV, Cinema, Game values
• Updated Project model with new fields
• Added proper column constraints and indexing

Schemas (schemas/project.py)

• Updated ProjectBase, ProjectCreate, ProjectUpdate schemas
• Added field validation and descriptions
• Updated response schemas to include new fields

API Endpoints (routers/projects.py)

• Added validation for unique code_name constraint
• Enhanced error handling for duplicate code names
• Updated create/update endpoints to handle new fields

Frontend Changes

Services (services/project.ts)

• Updated Project, ProjectCreate, ProjectUpdate interfaces
• Added new fields with proper TypeScript types

Stores (stores/projects.ts)

• Enhanced icon assignment logic to use project_type field
• Updated project filtering and organization

UI (views/ProjectsView.vue)

• Added form fields for code name, client name, and project type
• Enhanced project cards to display new information
• Updated search functionality to include new fields
• Added project type formatting helper

Benefits

1. Better Organization: Projects can now be categorized by type (TV, Cinema, Game)
2. Unique Identification: Code names provide consistent project references
3. Client Tracking: Clear client/studio association for each project
4. Enhanced Search: Users can search by code name, client name, or project type
5. Visual Indicators: Project cards show type badges and client information
6. Industry Standards: Aligns with common VFX production workflows

Usage Examples

Creating a New Project

{
  "name": "Marvel Thor: Love and Thunder",
  "code_name": "MARVEL_THOR_2024",
  "client_name": "Marvel Studios",
  "project_type": "cinema",
  "description": "VFX work for Thor sequel",
  "status": "planning"
}

Project Types

• TV: Series, shows, streaming content
• Cinema: Feature films, movies
• Game: Video game cinematics, in-game VFX

The schema update maintains backward compatibility while adding essential production management features commonly used in the VFX industry.