7.5 KiB

Raw Permalink Blame History

Design Document

Overview

This design implements a database schema enhancement to add a project_id column to the shots table, establishing a direct relationship between shots and projects. This change improves data integrity, enables project-scoped shot name uniqueness, and provides better query performance for project-based shot operations.

Architecture

The enhancement follows a layered approach:

Database Layer: Add project_id column with foreign key constraint and index
Model Layer: Update SQLAlchemy Shot model with project relationship
Schema Layer: Update Pydantic schemas to include project_id
API Layer: Modify endpoints to handle project_id in requests/responses
Service Layer: Update business logic for project-scoped validation
Frontend Layer: Update TypeScript interfaces and components

Components and Interfaces

Database Schema Changes

-- Add project_id column to shots table
ALTER TABLE shots ADD COLUMN project_id INTEGER;

-- Create foreign key constraint
ALTER TABLE shots ADD CONSTRAINT fk_shots_project_id 
    FOREIGN KEY (project_id) REFERENCES projects(id);

-- Create index for performance
CREATE INDEX idx_shots_project_id ON shots(project_id);

-- Create composite index for project-scoped name uniqueness
CREATE UNIQUE INDEX idx_shots_project_name_unique 
    ON shots(project_id, name) WHERE deleted_at IS NULL;

Model Updates

Shot Model (backend/models/shot.py):

Add project_id column as non-nullable foreign key
Add project relationship to Project model
Update uniqueness constraints to be project-scoped
Maintain backward compatibility with episode relationship

Project Model (backend/models/project.py):

Add shots relationship back-reference

Schema Updates

ShotBase Schema:

Add optional project_id field for API flexibility
Maintain episode_id as primary relationship identifier

ShotResponse Schema:

Include project_id in all response payloads
Add computed project_name field for frontend convenience

API Endpoint Changes

Shot Creation Endpoints:

Automatically derive project_id from episode_id
Validate project consistency between episode and provided project_id
Update uniqueness validation to be project-scoped

Shot Query Endpoints:

Add optional project_id filter parameter
Include project information in response payloads
Maintain existing episode-based filtering

Data Models

Updated Shot Model Structure

class Shot(Base):
    __tablename__ = "shots"
    
    id = Column(Integer, primary_key=True, index=True)
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False, index=True)
    episode_id = Column(Integer, ForeignKey("episodes.id"), nullable=False)
    name = Column(String, nullable=False, index=True)
    # ... other existing fields
    
    # Relationships
    project = relationship("Project", back_populates="shots")
    episode = relationship("Episode", back_populates="shots")
    # ... other existing relationships
    
    # Constraints
    __table_args__ = (
        UniqueConstraint('project_id', 'name', name='uq_shot_project_name'),
    )

Frontend Interface Updates

interface Shot {
  id: number
  project_id: number
  episode_id: number
  name: string
  // ... other existing fields
  
  // Optional computed fields
  project_name?: string
}

interface ShotCreate {
  name: string
  project_id?: number  // Optional, derived from episode if not provided
  // ... other existing fields
}

Correctness Properties

A property is a characteristic or behavior that should hold true across all valid executions of a system-essentially, a formal statement about what the system should do. Properties serve as the bridge between human-readable specifications and machine-verifiable correctness guarantees.

Property 1: Project-Episode Consistency

For any shot in the system, the project_id must match the project_id of its associated episode Validates: Requirements 1.2, 3.3, 4.2

Property 2: Project-Scoped Name Uniqueness

For any project, shot names must be unique within that project scope (excluding soft-deleted shots) Validates: Requirements 1.1, 1.4

Property 3: API Response Completeness

For any shot API response, the response must include both the project_id field and all previously existing fields Validates: Requirements 1.3, 3.1, 4.1

Property 4: Migration Data Preservation

For any existing shot before migration, the shot data after migration should be identical except for the addition of the correct project_id derived from the episode relationship Validates: Requirements 2.4, 2.5

Property 5: Project Filtering Accuracy

For any project_id filter parameter, the API should return only shots that belong to that specific project Validates: Requirements 3.4

Property 6: Bulk Operation Consistency

For any bulk shot creation operation, all created shots must have the same project_id as their target episode Validates: Requirements 3.5

Property 7: Soft Deletion Project Preservation

For any shot that undergoes soft deletion, the project_id must be preserved for recovery operations Validates: Requirements 4.5

Property 8: Permission System Continuity

For any shot operation that was previously authorized, the same operation should remain authorized after the schema change Validates: Requirements 4.4

Error Handling

Migration Errors

Orphaned Episodes: Handle episodes without valid project references
Data Inconsistency: Detect and report shots with mismatched episode-project relationships
Constraint Violations: Handle existing duplicate shot names within projects

Runtime Errors

Invalid Project ID: Return 400 Bad Request for non-existent project references
Project-Episode Mismatch: Return 400 Bad Request when provided project_id doesn't match episode's project
Duplicate Shot Names: Return 409 Conflict for project-scoped name collisions

Frontend Error Handling

Migration Status: Display migration progress and handle temporary unavailability
Validation Errors: Show clear messages for project-scoped naming conflicts
Fallback Behavior: Gracefully handle missing project information during transition

Testing Strategy

Unit Tests

Test Shot model creation with project_id
Test project-scoped uniqueness validation
Test API endpoint parameter handling
Test schema serialization/deserialization

Property-Based Tests

Property 1 Test: Generate random shots and verify project-episode consistency
Property 2 Test: Generate random shot names within projects and verify uniqueness enforcement
Property 3 Test: Create test data, run migration simulation, verify data preservation
Property 4 Test: Generate shots with various project_id values and verify foreign key constraints
Property 5 Test: Generate API requests in old format and verify response compatibility

Integration Tests

Test complete shot creation workflow with project validation
Test shot querying with project filtering
Test bulk shot creation with project consistency
Test soft deletion with project_id preservation

Migration Tests

Test migration script with various data scenarios
Test rollback procedures
Test performance with large datasets
Test constraint creation and validation

The testing approach uses Pytest for the Python backend with Hypothesis for property-based testing. Each property-based test will run a minimum of 100 iterations to ensure comprehensive coverage of the input space.

7.5 KiB Raw Permalink Blame History