Chapter 86. Asset Database and Project Directory#

A file called "final_really_final" on a shared drive cannot carry a season. An asset system solves three problems at once: where the binary lives, where the facts about it live, and how dependencies between assets are queried. Directory structure serves people; the database serves state and relationships. Neither substitutes for the other.

86.1 Asset identity, core tables, and the relationship graph#

Stable IDs and display names.

Characters, locations, props, shots, takes, audio, graphics and release packages all carry stable IDs. Display names may change; IDs do not. Filenames contain project, entity, purpose, version and an optional state — never an approval status written as a vague word.

projects/backlit_takeover/
  canonical/
    story/
    identities/
    locations/
    rights/
  episodes/ep001/
    pack/
    shots/sh015/
      inputs/
      candidates/
      approved/
    audio/
    edit/
    qc/
  releases/ep001/rc004/

A directory is only a view. Real paths can be generated from object storage keys and the database, and users must not change a lifecycle by moving files by hand.

Core tables.

The minimum relational model includes entities, assets, asset_versions, relationships, approvals, rights_profiles, tasks, events and release_manifests. Large files live in object storage; the database holds the URI, checksum, dimensions, media properties and state.

asset_version:
  asset_id: asset_char_lin_front
  version: 3
  blob_uri: object://canonical/characters/lin/front/v03.png
  checksum: sha256:bbb
  lifecycle: locked
  entity_id: char_lin_xia
  source_task: img_char_lin_v03
  rights_profile: rights_synth_character_01
  supersedes: asset_char_lin_front@2

The relationship graph.

Relationships express at minimum: a shot uses an identity; a take derives from inputs; an edit references takes; a release package contains an edit; an asset is constrained by rights; a new version supersedes an old one. Each carries an effective time and a source — not merely two IDs.

When the lead's primary identity changes, query every unreleased shot depending on the old version. When a music licence expires, query every platform package containing that cue. When a wrong contract amount is found, query the graphic asset and its derived campaign material. Those queries are the point of an asset library — not thumbnail browsing.

86.2 Lifecycle, concurrency, and retrieval#

Lifecycle and immutability.

draft, candidate, approved, locked, retired and quarantined each permit different actions. An approved version is read-only; modification produces a new version. retired remains traceable and cannot be selected by new tasks. quarantined is isolated for safety, rights or quality reasons.

Deletion divides into logical deletion, rights deletion and physical purge. Audit records may need retention while personal data may need lawful deletion, so the system layers binaries, identifiable metadata and irreversible audit proofs separately.

Concurrency and leases.

Episode packs, subtitles and edit projects are all edited by several people. Use version numbers, short leases and merge policies. Binary project files are never auto-merged as text; on conflict, keep both branches and let an owner decide. Every overwrite records the checksums before and after.

A lock is not indefinite possession that makes everyone wait. After a lease expires the asset can be taken over, and the original editor must discover on submission that the version changed rather than overwriting newer work.

Search and materialized views.

Search supports entity, episode, shot function, state, rights, character, location, error tag and provenance. Useful views include: every blocker in this episode; candidates using retired assets; rights expiring within thirty days; items waiting for human approval beyond the SLO; and shots usable for campaigns with no derivative produced.

Semantic search helps surface similar actions and past failures, and its results are candidates only. Lifecycle, rights and factual filtering still use structured fields.

86.3 Recovery capability and migrating legacy assets#

Backup, verification and restoration.

Snapshot the database regularly, replicate the event log off-site, and enable versioning and checksums in object storage. A successful backup is not a demonstrated restore, so quarterly drills rebuild one episode from an empty environment: entities, relationships, approvals, rights and release packages, with sampled media checksum verification.

Large media can be tiered, with proxies and masters carrying different recovery priorities. Release masters, licensing evidence and project state come before regenerable low-resolution previsualization.

Migrating an old shared drive.

Scan read-only and deduplicate by hash first, then build a mapping table — do not rename everything immediately. Have humans confirm key assets, rights and versions; anything whose provenance cannot be confirmed goes to quarantine. Keep redirects or an index for old paths and freeze them in batches once the new system is stable.

The migration report must list unidentified files, duplicate content, missing rights, naming conflicts and orphaned references. Moving every file into a new directory is not a completed migration.

86.4 Acceptance and the four questions the database must answer#

Fault tree.

Files are findable and nobody dares use them: rights and approvals are missing. The database has a record and the media will not open: no checksums and no backups. Shots are missed after an identity change: dependencies exist only in someone's memory. People overwrite each other: no versions and no leases. Search returns many useless results: natural language tags were treated as the core schema.

SOP, checklist and deliverables.

Define entities and IDs. Design the lifecycle. Build object storage and metadata tables. Record relationships. Enforce checksums. Implement approvals and rights. Create the common views. Define backups. Rehearse restoration. Migrate legacy assets in batches.

• Moving a file does not change approval state.
• Locked versions cannot be overwritten.
• Every released asset can be traced to provenance and rights.
• Retired and quarantined assets cannot be selected by new tasks.
• Backups have been validated by an actual restore.

Exercise: build a minimum asset library for a three-episode pilot, delivering database_schema.sql, object_key_policy.md, lifecycle_rules.yaml, dependency_queries.sql, migration_report.csv and restore_drill.md.

Four questions it must answer.

Before the asset library goes live, a non-developer must be able to answer four questions: which take and inputs produced a given finished shot; which unreleased shots an identity upgrade will affect; which platform versions contain a music licence that is expiring; and whether a quarantined file is still referenced by any task. If an engineer has to write a script each time, the system has not yet become a production interface.

Deliberately retire the lead's identity v02 and leave references in a cached pack, a backup take and one campaign creative. The impact query should find all three and route them to recompilation, quarantine and creative replacement respectively. Re-running the query after repair must return zero while the historical references remain as evidence. That closed loop demonstrates asset governance far better than a successful backup.

Acceptance also takes one released shot and asks a new team member to find its inputs, approvals, rights and cost within five minutes. Anything they cannot find is recorded as a break in the information chain — and blocks the asset library from being declared live.

A note on sources#

This chapter describes a data model rather than a product. What transfers is separating stable identity from directory views, storing relationships as queryable facts, and proving recovery through drills rather than backup success messages.