Database Schema

Key Tables

raw_clips

Source clips from Annotate mode. Identity: end_time (not ID).

raw_clips (
    id INTEGER PRIMARY KEY,
    filename TEXT,
    start_time REAL,
    end_time REAL,           -- IDENTITY KEY
    boundaries_version INT,   -- Increments when times change
    boundaries_updated_at TIMESTAMP,
    game_id INT,
    auto_project_id INT,
    ...
)

working_clips

Clips in projects with framing edits. Identity: raw_clip.end_time OR uploaded_filename.

working_clips (
    id INTEGER PRIMARY KEY,
    project_id INT,
    raw_clip_id INT,          -- FK to raw_clips (nullable)
    uploaded_filename TEXT,    -- For direct uploads (no raw_clip)
    version INT DEFAULT 1,     -- Increments on re-export
    raw_clip_version INT,      -- Snapshot of boundaries_version at import
    crop_data TEXT,            -- JSON
    timing_data TEXT,          -- JSON
    segments_data TEXT,        -- JSON
    ...
)

working_videos / final_videos

Output videos. Identity: project_id + version.

working_videos (
    id INTEGER PRIMARY KEY,
    project_id INT,
    filename TEXT,
    version INT DEFAULT 1,     -- Increments on re-export
    highlights_data TEXT,      -- JSON
    ...
)

Latest Query Pattern

To get only the latest version of each item:

-- Latest working clips for a project
SELECT * FROM working_clips wc
WHERE wc.id IN (
    SELECT id FROM (
        SELECT wc2.id,
               ROW_NUMBER() OVER (
                   PARTITION BY COALESCE(rc.end_time, wc2.uploaded_filename)
                   ORDER BY wc2.version DESC
               ) as rn
        FROM working_clips wc2
        LEFT JOIN raw_clips rc ON wc2.raw_clip_id = rc.id
        WHERE wc2.project_id = ?
    ) WHERE rn = 1
)
ORDER BY wc.sort_order

Helper Function

Use app/queries.py for common patterns:

from app.queries import latest_working_clips_subquery

cursor.execute(
    f"SELECT * FROM working_clips WHERE id IN ({latest_working_clips_subquery()}) AND project_id = ?",
    (project_id,)
)

Version Identity Rules

FK Constraints

Use ON DELETE CASCADE for child tables:

CREATE TABLE annotations (
    id INTEGER PRIMARY KEY,
    game_id INTEGER NOT NULL,
    ...
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
);

This ensures:

• Deleting a game deletes all its annotations
• Deleting a project deletes all its clips and videos
• No orphaned records

Common Pitfalls

Pitfall 1: Querying by ID instead of identity

-- BAD: Gets a specific row, may not be latest
SELECT * FROM working_clips WHERE id = 123;

-- GOOD: Gets latest version of the logical clip
SELECT * FROM working_clips WHERE id IN (
    SELECT id FROM (...latest query...)
) AND raw_clip_id = 456;

Pitfall 2: Forgetting to increment version

# BAD: Overwrites without versioning
cursor.execute("UPDATE working_clips SET crop_data = ? WHERE id = ?", ...)

# GOOD: Insert new version
cursor.execute("""
    INSERT INTO working_clips (project_id, raw_clip_id, version, crop_data, ...)
    SELECT project_id, raw_clip_id, version + 1, ?, ...
    FROM working_clips WHERE id = ?
""", ...)

Complete Rules

See individual rule files in rules/ directory.