.studio schema

crowmaki commented

2026-10-06 03:55:54 +00:00

Owner

Part of #1
Blocked by: #2

Question

What is the SQLite schema for a Project? It must cover Scenes and Lines (stable IDs), Entities, Looks, Wardrobe items, Reference sets, Tiles, Reference versions, Lock snapshots, Atlases (identified by content), Setups, Shots, Boards, Keyframes, Performances, Takes, Renditions, provenance, and queue jobs. Small assets are stored as blobs and Renditions sit in the sibling media folder (ADR-0001). Provenance must be able to grow into a chain for future Take extension.

Part of #1 Blocked by: #2 ## Question What is the SQLite schema for a Project? It must cover Scenes and Lines (stable IDs), Entities, Looks, Wardrobe items, Reference sets, Tiles, Reference versions, Lock snapshots, Atlases (identified by content), Setups, Shots, Boards, Keyframes, Performances, Takes, Renditions, provenance, and queue jobs. Small assets are stored as blobs and Renditions sit in the sibling media folder (ADR-0001). Provenance must be able to grow into a chain for future Take extension.

crowmaki added the

wayfinder:grilling

label

2026-10-06 03:55:54 +00:00

crowmaki referenced this issue

2026-10-06 03:56:04 +00:00

Staleness rules #12

crowmaki referenced this issue

2026-10-06 03:56:04 +00:00

Render worker scheduling #18

crowmaki referenced this issue

2026-10-06 03:56:08 +00:00

Wayfinder: AI Studio end-to-end slice spec #1

crowmaki referenced this issue

2026-10-06 05:23:35 +00:00

Default atlas layout #4

crowmaki referenced this issue

2026-10-06 05:41:54 +00:00

External media view and cleanup #22

crowmaki commented

2026-10-06 06:28:23 +00:00

Author

Owner

From "Dailies review and circling" (#7): the schema needs on Take a stored seen flag, an NG flag, a list of notes (each with optional timecode), rerolled_from (Take id), and circled-at time; a per-Shot Pinned take reference (must be circled; cleared on uncircle). Take numbers are never reused, even when a failed Take is deleted.

From "Dailies review and circling" (#7): the schema needs on Take a stored `seen` flag, an NG flag, a list of notes (each with optional timecode), `rerolled_from` (Take id), and circled-at time; a per-Shot Pinned take reference (must be circled; cleared on uncircle). Take numbers are never reused, even when a failed Take is deleted.

crowmaki commented

2026-10-06 07:32:36 +00:00

Author

Owner

From "Animatic duration rules" (#8), the schema must also hold: per-Line duration overrides; per-Project speaking/action rates; Shot head/tail handles and a length override; user-orderable Setup order; Cut overrides (Line or simultaneous group → Shot); and per-Take Line marks.

crowmaki commented

2026-10-07 03:42:51 +00:00

Author

Owner

From "Prompt composer phrasing" (#9), fields the schema must hold:

Character: default identity fields (age and build, face, hair, eyes, distinctive features). Look: nullable overrides of each, plus default-wardrobe text. Wardrobe item: wardrobe text.
Location: description and Ambience. Location Reference set per Lighting: lighting note (nullable, defaults from a project-wide phrase per Lighting).
Prop: description. Scene: optional Ambience addition. Setup: framing note.
Shot: action text with embedded mentions (Characters/Props by id) and dialogue chips (Line ids, order fixed); summary text; optional prompt override with its fork-base composed text.
Line: parentheticals with their position in the speech (they render inline as tags).
Take provenance: the exact prompt text sent, and whether it was overridden.

From "Prompt composer phrasing" (#9), fields the schema must hold: - Character: default identity fields (age and build, face, hair, eyes, distinctive features). Look: nullable overrides of each, plus default-wardrobe text. Wardrobe item: wardrobe text. - Location: description and Ambience. Location Reference set per Lighting: lighting note (nullable, defaults from a project-wide phrase per Lighting). - Prop: description. Scene: optional Ambience addition. Setup: framing note. - Shot: action text with embedded mentions (Characters/Props by id) and dialogue chips (Line ids, order fixed); summary text; optional prompt override with its fork-base composed text. - Line: parentheticals with their position in the speech (they render inline as tags). - Take provenance: the exact prompt text sent, and whether it was overridden.

crowmaki commented

2026-10-07 04:06:04 +00:00

Author

Owner

From "Reference slot budget" (#10), things the schema must hold: a per-Project ordered list of reference categories (Keyframes, Atlases, Wardrobe, Location, Board, Props), plus an app-wide default outside the Project that seeds new ones. Per Shot: an optional stored Slot list, which is a full ordered list of concrete references plus the sent/not-sent line position, with "excluded" vs "over budget" per item and a "new" flag. An absent list means the Shot follows the Project order live. Per Take: the exact slot assignment it was sent with (which reference went in which numbered slot).

From "Reference slot budget" (#10), things the schema must hold: a per-Project ordered list of reference categories (Keyframes, Atlases, Wardrobe, Location, Board, Props), plus an app-wide default outside the Project that seeds new ones. Per Shot: an optional stored **Slot list**, which is a full ordered list of concrete references plus the sent/not-sent line position, with "excluded" vs "over budget" per item and a "new" flag. An absent list means the Shot follows the Project order live. Per Take: the exact slot assignment it was sent with (which reference went in which numbered slot).

crowmaki self-assigned this

2026-10-07 04:26:02 +00:00

crowmaki commented

2026-10-07 04:45:43 +00:00

Author

Owner

Resolution

A table-level model. The full column-by-column DDL gets written in the build issue that creates each table. Field lists from the comments above (Dailies, Animatic, Prompt composer, Slot budget) go in as written there.

Cross-cutting

IDs: a ULID primary key on every domain row. Human labels (frozen Scene number, Setup letter, Take number) are plain columns, never keys. Scenes also keep d:id as a unique column.
Blobs: one content-addressed blob table (BLAKE3 hash, media type, size, pixel dimensions). Every other table points at a hash. Unused blobs are removed by a sweep on Project open and on demand, with no reference counts.
Deletion: a row that any Take's provenance points at (take_input, take_link) cannot be hard-deleted. Deleting it sets deleted_at and hides it everywhere except provenance and the Dailies inputs comparison. Rows nothing has consumed are hard-deleted. Exceptions:
- Failed Takes can be hard-deleted (their numbers are still never reused).
- A merged Character or Location is kept as a tombstone with merged_into.
- Deleting a Setup that has Takes hides it, its Shots and their Takes. Their Renditions are freed only through media cleanup. See ADR-0004.
File lifecycle:
- Versioning: PRAGMA user_version with ordered migrations embedded in studio-core, applied in one transaction on open. Before migrating, the file is copied to <name>.studio.bak-v<N>. A file whose version is newer than the app is refused. PRAGMA application_id is set to a fixed value.
- Journal mode: WAL while open, then a checkpoint and journal_mode=DELETE on a clean close, so the file at rest is a single file.
- Single opener: a <name>.studio.lock file holds the PID and host.
Settings: a singleton project row with typed columns. Settings that are lists get their own tables: lighting, slug_lighting, reference_category_order, notation_entry (Notation entries are retired, never deleted). App-wide defaults are a JSON file in the app config directory, copied into a Project when it's created and never read again by that Project.
Generation settings: a Project default profile (checkpoint, quantization, LoRAs with weights, draft/final resolution choices). A new Take copies it, and the queue dialog lets you change it for that Take. "Same take, new seed" copies the source Take's settings. Provenance records the resolved values. Changing the default does not make Takes stale.

Entities and references

Entities: separate character, location and prop tables with no shared parent. "Entity" stays a glossary word, not a table.
Owners of Reference sets: each owner holds a unique, not-null FK to its set: look.reference_set_id, wardrobe_item.reference_set_id, prop.reference_set_id, character.voice_set_id. A location_lighting(location, lighting, reference_set, lighting_note) row holds each per-Lighting set. It exists only when a Scene at that Location uses that Lighting, or when the user adds it. reference_set has an id and a kind (turnaround, mannequin, location, prop or voice).
Tiles: each tile has a role and an ordinal.
- Turnaround sets have fixed roles: face, front, side, back.
- Mannequin sets have fixed roles: front, side, back.
- Location, Prop and Voice sets are open, user-labelled lists.
- A Tile that any Lock snapshot includes is retired, never deleted.
Reference versions: append-only, each with its own blob, plus the Source sheet and crop box for imported ones. A generated version exists as a row with no pixels from the moment it's queued.
Lock snapshots: lock_snapshot(set, number) with snapshot_tile(snapshot, tile, reference_version), immutable. The current snapshot is always the highest number. Rolling back makes a new snapshot that copies the old pick, and locking an identical pick is a no-op.
Atlases: a cache, atlas(input_key, blob, built_at). input_key is a hash of the ordered snapshot IDs, the layout version and the size settings. Setups store only their grouping and never point at an Atlas. Takes record the Atlases they used. Atlases nothing needs can be dropped and rebuilt (byte-stable). See ADR-0005.

Script, Setups, Shots

Lines: an immutable integer position within their Scene. The script is frozen after import.
Shots: a contiguous range, stored as first_line_id and last_line_id, within one Scene. Transition and shot-direction Lines can fall inside the range.
Setups: setup(scene, letter, setup_order). The letter is unique within the Scene and never reused. setup_order is separate and the user can reorder it.
Shot labels and Take numbers: shot(setup, index, next_take_number). The label is 12A when the Setup has one Shot, otherwise 12B·1. Take numbers come from the counter. When a Shot is split, its existing Takes stay on the first part.
Looks: scene_cast(scene, character, look, wardrobe_item?) says how each Character appears in the Scene. Speaking Characters are added automatically, with their default Look. setup_subject(setup, character, position) says who is in frame and the <Subject N> order. A speaking Character need not be a subject (off-screen dialogue). Props are not cast; they come from Shot mentions.
Action text: a JSON node document (text, mention{kind,id}, dialogue{line_id}), plus a derived shot_mention(shot, character|prop) table that the core rewrites on every save. A merge rewrites both in one transaction.
Prompt override: plain text, stored together with the composed text it was forked from.
Slot list: shot_slot_item(shot, position, candidate_key, state = sent/excluded/over_budget, is_new) plus shot.slot_line. Keys are logical: keyframe:<id>, atlas:<group>, wardrobe:<id>, location_angle:<tile>, board, prop:<id>. With no rows, the Shot follows the Project order live.

Boards, Keyframes, Performances

Panels: panel(board, position, canvas_w, canvas_h, sketch_blob, annotations JSON). Annotation positions are normalized. Boards are edited in place, with no history. A Take records the composited blob that was sent.
Aspect ratio: set on the project row and changeable at any time, from a fixed list with no custom values. Shots can override it, so a Shot's effective ratio is its override, else the Project's. Changing the effective ratio makes the Shot's Takes stale. A Panel keeps the canvas it was drawn on and is barred at send time; its first new stroke re-canvases it to the current ratio. Keyframes are fitted the same way, with a warning badge. The Animatic and FCPXML timeline stay at the Project ratio, with other-ratio Shots letterboxed into it.
Keyframes: keyframe(shot, anchor = start/end/time, at_seconds?, image_blob, origin = imported/generated/take_frame). A take_frame Keyframe records its source Take, Rendition and frame time, and its pixels are extracted into a blob. A Take made with it gets a take_link(frame_from) edge. In the slice: a Dailies action, "Use first / last frame as Keyframe for…".
Performances: performance(scene, number, audio_blob, created_at), immutable. The current one is the highest number. performance_source(performance, take, …) is an edge list, and the Scene has a performance_off flag. How Lines are marked in a Performance is left to "Marking Lines in a Performance".

Takes and production

Provenance:
- take_input has one row per thing the Take consumed, each with a real FK and the slot number it filled. Shot and Setup text fields are recorded by value or hash.
- The exact job as sent (filled-in workflow JSON) and the prompt text are stored as blobs, along with whether the prompt was overridden.
- take_link(take, other_take, kind) holds Take-to-Take edges, with kinds rerolled_from and frame_from now and extends later.
Renditions: rendition(take, kind = draft/final, via = draft/promotion/single-pass, state, path?), created when queued. Media folder: renditions/<rendition-id>.mp4 and latents/<rendition-id>.{video,audio}.latent, with paths stored relative to the folder. Human names exist only in the edit package and the UI.
Queue: a job table with one row per ComfyUI submission:
- kind: draft, promotion, single-pass final, t2i reference or t2i Board
- target: an FK to the Rendition, Reference version or Panel, with exactly one set
- state: queued, submitted, done, failed or cancelled
- queue_position, attempt, our own prompt_id, error, and timestamps
A job left submitted is re-sent on open. The scheduling policy is left to "Render worker scheduling".

Knock-ons

This clears the map's fog item "Project aspect ratio".
"Staleness rules" gains these triggers: an effective aspect ratio change, a Performance version change, and a Lock snapshot no longer being the latest.
Glossary: Setup and Shot are added. Panel and Keyframe are updated.
ADR-0004: provenance-preserving deletion.
ADR-0005: Atlases are a cache.

## Resolution A table-level model. The full column-by-column DDL gets written in the build issue that creates each table. Field lists from the comments above (Dailies, Animatic, Prompt composer, Slot budget) go in as written there. ### Cross-cutting - **IDs:** a ULID primary key on every domain row. Human labels (frozen Scene number, Setup letter, Take number) are plain columns, never keys. Scenes also keep `d:id` as a unique column. - **Blobs:** one content-addressed `blob` table (BLAKE3 hash, media type, size, pixel dimensions). Every other table points at a hash. Unused blobs are removed by a sweep on Project open and on demand, with no reference counts. - **Deletion:** a row that any Take's provenance points at (`take_input`, `take_link`) cannot be hard-deleted. Deleting it sets `deleted_at` and hides it everywhere except provenance and the Dailies inputs comparison. Rows nothing has consumed are hard-deleted. Exceptions: - Failed Takes can be hard-deleted (their numbers are still never reused). - A merged Character or Location is kept as a tombstone with `merged_into`. - Deleting a Setup that has Takes hides it, its Shots and their Takes. Their Renditions are freed only through media cleanup. See ADR-0004. - **File lifecycle:** - Versioning: `PRAGMA user_version` with ordered migrations embedded in `studio-core`, applied in one transaction on open. Before migrating, the file is copied to `<name>.studio.bak-v<N>`. A file whose version is newer than the app is refused. `PRAGMA application_id` is set to a fixed value. - Journal mode: WAL while open, then a checkpoint and `journal_mode=DELETE` on a clean close, so the file at rest is a single file. - Single opener: a `<name>.studio.lock` file holds the PID and host. - **Settings:** a singleton `project` row with typed columns. Settings that are lists get their own tables: `lighting`, `slug_lighting`, `reference_category_order`, `notation_entry` (Notation entries are retired, never deleted). App-wide defaults are a JSON file in the app config directory, **copied** into a Project when it's created and never read again by that Project. - **Generation settings:** a Project default profile (checkpoint, quantization, LoRAs with weights, draft/final resolution choices). A new Take copies it, and the queue dialog lets you change it for that Take. "Same take, new seed" copies the source Take's settings. Provenance records the resolved values. Changing the default does not make Takes stale. ### Entities and references - **Entities:** separate `character`, `location` and `prop` tables with no shared parent. "Entity" stays a glossary word, not a table. - **Owners of Reference sets:** each owner holds a unique, not-null FK to its set: `look.reference_set_id`, `wardrobe_item.reference_set_id`, `prop.reference_set_id`, `character.voice_set_id`. A `location_lighting(location, lighting, reference_set, lighting_note)` row holds each per-Lighting set. It exists only when a Scene at that Location uses that Lighting, or when the user adds it. `reference_set` has an id and a `kind` (turnaround, mannequin, location, prop or voice). - **Tiles:** each `tile` has a `role` and an `ordinal`. - Turnaround sets have fixed roles: face, front, side, back. - Mannequin sets have fixed roles: front, side, back. - Location, Prop and Voice sets are open, user-labelled lists. - A Tile that any Lock snapshot includes is **retired**, never deleted. - **Reference versions:** append-only, each with its own blob, plus the Source sheet and crop box for imported ones. A generated version exists as a row with no pixels from the moment it's queued. - **Lock snapshots:** `lock_snapshot(set, number)` with `snapshot_tile(snapshot, tile, reference_version)`, immutable. The current snapshot is always the highest number. Rolling back makes a new snapshot that copies the old pick, and locking an identical pick is a no-op. - **Atlases:** a cache, `atlas(input_key, blob, built_at)`. `input_key` is a hash of the ordered snapshot IDs, the **layout version** and the size settings. Setups store only their grouping and never point at an Atlas. Takes record the Atlases they used. Atlases nothing needs can be dropped and rebuilt (byte-stable). See ADR-0005. ### Script, Setups, Shots - **Lines:** an immutable integer `position` within their Scene. The script is frozen after import. - **Shots:** a contiguous range, stored as `first_line_id` and `last_line_id`, within one Scene. Transition and shot-direction Lines can fall inside the range. - **Setups:** `setup(scene, letter, setup_order)`. The letter is unique within the Scene and never reused. `setup_order` is separate and the user can reorder it. - **Shot labels and Take numbers:** `shot(setup, index, next_take_number)`. The label is `12A` when the Setup has one Shot, otherwise `12B·1`. Take numbers come from the counter. When a Shot is split, its existing Takes stay on the first part. - **Looks:** `scene_cast(scene, character, look, wardrobe_item?)` says how each Character appears in the Scene. Speaking Characters are added automatically, with their default Look. `setup_subject(setup, character, position)` says who is in frame and the `<Subject N>` order. A speaking Character need not be a subject (off-screen dialogue). Props are not cast; they come from Shot mentions. - **Action text:** a JSON node document (`text`, `mention{kind,id}`, `dialogue{line_id}`), plus a derived `shot_mention(shot, character|prop)` table that the core rewrites on every save. A merge rewrites both in one transaction. - **Prompt override:** plain text, stored together with the composed text it was forked from. - **Slot list:** `shot_slot_item(shot, position, candidate_key, state = sent/excluded/over_budget, is_new)` plus `shot.slot_line`. Keys are logical: `keyframe:<id>`, `atlas:<group>`, `wardrobe:<id>`, `location_angle:<tile>`, `board`, `prop:<id>`. With no rows, the Shot follows the Project order live. ### Boards, Keyframes, Performances - **Panels:** `panel(board, position, canvas_w, canvas_h, sketch_blob, annotations JSON)`. Annotation positions are normalized. Boards are edited in place, with no history. A Take records the composited blob that was sent. - **Aspect ratio:** set on the `project` row and changeable at any time, from a fixed list with no custom values. Shots can override it, so a Shot's effective ratio is its override, else the Project's. Changing the effective ratio makes the Shot's Takes stale. A Panel keeps the canvas it was drawn on and is barred at send time; its first new stroke re-canvases it to the current ratio. Keyframes are fitted the same way, with a warning badge. The Animatic and FCPXML timeline stay at the Project ratio, with other-ratio Shots letterboxed into it. - **Keyframes:** `keyframe(shot, anchor = start/end/time, at_seconds?, image_blob, origin = imported/generated/take_frame)`. A take_frame Keyframe records its source Take, Rendition and frame time, and its pixels are extracted into a blob. A Take made with it gets a `take_link(frame_from)` edge. **In the slice:** a Dailies action, "Use first / last frame as Keyframe for…". - **Performances:** `performance(scene, number, audio_blob, created_at)`, immutable. The current one is the highest number. `performance_source(performance, take, …)` is an edge list, and the Scene has a `performance_off` flag. How Lines are marked in a Performance is left to "Marking Lines in a Performance". ### Takes and production - **Provenance:** - `take_input` has one row per thing the Take consumed, each with a real FK and the slot number it filled. Shot and Setup text fields are recorded by value or hash. - The exact job as sent (filled-in workflow JSON) and the prompt text are stored as blobs, along with whether the prompt was overridden. - `take_link(take, other_take, kind)` holds Take-to-Take edges, with kinds `rerolled_from` and `frame_from` now and `extends` later. - **Renditions:** `rendition(take, kind = draft/final, via = draft/promotion/single-pass, state, path?)`, created when queued. Media folder: `renditions/<rendition-id>.mp4` and `latents/<rendition-id>.{video,audio}.latent`, with paths stored relative to the folder. Human names exist only in the edit package and the UI. - **Queue:** a `job` table with one row per ComfyUI submission: - kind: draft, promotion, single-pass final, t2i reference or t2i Board - target: an FK to the Rendition, Reference version or Panel, with exactly one set - state: queued, submitted, done, failed or cancelled - `queue_position`, `attempt`, our own `prompt_id`, `error`, and timestamps A job left `submitted` is re-sent on open. The scheduling policy is left to "Render worker scheduling". ### Knock-ons - This clears the map's fog item "Project aspect ratio". - "Staleness rules" gains these triggers: an effective aspect ratio change, a Performance version change, and a Lock snapshot no longer being the latest. - Glossary: Setup and Shot are added. Panel and Keyframe are updated. - ADR-0004: provenance-preserving deletion. - ADR-0005: Atlases are a cache.

crowmaki closed this issue

2026-10-07 04:45:44 +00:00

crowmaki referenced this issue

2026-10-07 04:45:44 +00:00

Staleness rules #12

crowmaki referenced this issue

2026-10-07 07:14:03 +00:00

Marking Lines in a Performance #23

crowmaki referenced this issue

2026-10-07 07:24:15 +00:00

Spec: AI Studio end-to-end slice #24