Storage and catalog schema

Logical contract and authority

The SQLite and PostgreSQL catalog adapters implement this logical schema. Application functions operate on typed records through the catalog adapter; SQL syntax, connection management and migrations stay inside that adapter. Both official backends implement every catalog record and operation. The selected LadybugDB or ArcadeDB graph is a projection of this authority, not the only repository of media, speaker or trained-model lineage. JSON contracts define the versioned interchange payloads around these records.

Every workspace-scoped row carries workspace_id and a stable application-generated ID. Foreign keys and uniqueness include workspace scope so a relationship cannot accidentally cross workspaces. IDs use one documented UUID text representation at the application boundary; adapters may use compatible native storage. Never use SQLite row IDs, PostgreSQL sequences or graph record IDs as public identity. Mutable selections/settings have increasing revisions and expected-revision checks. Original source assets remain immutable. Computed current evidence is atomically replaced; accepted receipts retain identities and hashes without preserving obsolete documents or assignments.

Byte identity, media identity and speaker identity are separate. A SHA-256 digest identifies immutable content; a media entry is a user-facing library item; a speaker is a catalog identity with correction history. Equal bytes can serve several entries, and a speaker can have many model families/versions without being equated with a model file.

flowchart TB
  Workspace[Workspace and backend profiles] --> Media[Media entries and assets]
  Media --> Metadata[Current raw metadata and typed observations]
  Metadata --> Dates[Origination observations and selection revisions]
  Media --> Transcripts[One current embedded Cue JSON and source map]
  Media --> Segments[Current segment references]
  Transcripts --> Attribution[Cue-contained UUID assignments and external mappings]
  Segments --> Attribution
  Attribution --> Speaker[Stable speaker identity and lineage]
  Segments --> Dataset[Current corpus references or invalidated state]
  Speaker --> Dataset
  Dataset --> Training[Preparation manifest and training runs]
  Training --> Models[Speaker-model families and immutable versions]
  Media --> Artifacts[Immutable artifact IDs and locations]
  Metadata --> Artifacts
  Training --> Artifacts
  Models --> Artifacts
  Workspace --> Jobs[Jobs, leases, catalog revisions and graph outbox]

Portable values and invariants

Catalog schema 5 adds revisioned saved pipelines, canonical speaker state and aliases, scoped terminology and context-selection provenance to the current embedded recordings, external mappings and reference-only corpus evidence. The current library's nullable duration_us is authoritative: null means unknown and zero means a measured zero. Existing numeric source-asset duration fields remain for snapshot compatibility and never supply a fallback for unknown current media duration. Historical schema-1/schema-2/schema-3/schema-4 migration definitions remain frozen. Legacy speakers retain their stable IDs and names; aliases and terms are not guessed from old assignment data.

Current facts, metadata and dates are replaced atomically. Operational receipts retain identities and record hashes, rather than an archive of superseded computed values. Physical cleanup removes only report publications authorized by the accepted replacement; active readers delay success until their leases end. Original and supplied subtitle source assets remain retained. Exports include current data and expire imported running authority on restore.

Absolute timestamps use the Cueson timestamp format: iso contains RFC 3339 text and unix_ns contains the exact Unix-epoch nanosecond count for the same instant. Audit output uses UTC. The application validates agreement between both values; structural schema validation alone cannot prove it. Import wall-time literals retain their separate timezone and uncertainty rules, and media-relative intervals use the units defined above.

Catalog adapters preserve the exact nanosecond value; a PostgreSQL timestamp column alone cannot preserve nanosecond precision. Native runtimes and JSON consumers use lossless integer parsing and serialization. The desktop wrapper displays iso and passes exact timestamp payloads through the shared runtime rather than converting unix_ns through a JavaScript floating-point number.

Use non-null references for required provenance. An unknown value differs from an empty string, zero or a guessed default. Portable JSON is not a substitute for relational foreign keys on core associations. Store commonly queried properties in typed columns; preserve complete vendor reports/options in versioned JSON or artifact manifests. Backend-specific JSON, full-text and index facilities may optimize operations only if the normalized results stay equivalent.

Primary uniqueness includes artifact digest/size within the deduplication scope, entity/revision pairs, job idempotency keys, dataset/member ordinal, outbox event ID and graph target checkpoint. Duplicate cue text is not a unique identity. Deletion must obey explicit retention rules; a speaker correction invalidates dependent current preparation while retaining canonical sources and captured provenance and noncontent model lineage. Hard deletion of referenced durable artifacts fails until the user-requested retention operation has reconciled those references.

Workspace, artifacts and media

Artifact locations are separate from byte identity so provider migration does not rewrite current document identities, valid corpus references or model versions. An artifact may have several verified locations; a local materialization is not automatically a durable replica. Unavailable external bytes retain the admitted identity and an explicit changed/missing state. A newly changed external source requires a new asset; it does not redefine the original digest.

The time map can represent extraction, resampling, trimming and concatenation. Each piece names its original source clock; a concatenated training input cannot claim a single offset if it contains disjoint intervals. Stream selection and channel provenance remain explicit. Supplied subtitle bytes use artifact records with a media/track attachment, not filename-based implicit ownership.

Early metadata and origination dates

MediaMetadataSnapshot, MetadataObservation and OriginationDateObservation are the conceptual names; the records below use consistent snake_case. Ingestion defines capture and precedence before any media transformation.

Capture states are captured, no-embedded-metadata, partial, unsupported and failed. Only successful extraction with no embedded fields establishes no-embedded-metadata. An extraction failure can have no report artifact while still retaining its attempt and original source. Partial reports describe omissions and retain original bytes for retry. Raw reports and normalized observations describe the current capture. Refresh atomically replaces computed metadata and queues physical removal of superseded reports. Original media and supplied subtitle assets remain immutable. Owner edits add date observations or selection revisions without rewriting source tags.

Store the input's wall-time literal and its resolved interpretation. Local time is resolved once at import and retains the actual zone/offset and timezone rules version. A date-only value has date precision and calendar bounds where resolvable, not an invented midnight recording instant. Ambiguous or invalid daylight-saving values retain an unresolved interpretation until the affected observation is disambiguated. Another machine never reinterprets old records using its current local timezone.

Keep recording/origination, publication, retrieval, import, filesystem modification and processing observations distinguishable. The selected recording date can be unknown even when other timestamps exist. Date corrections advance only the selection revision and affected calendar projection; they do not rewrite original tags or media-relative cue/segment times.

Pipelines, current recordings and evidence

Only the embedded Cue JSON stores recording-local assignments. Native source labels remain separate upstream observations. External known-speaker corrections preserve document text and bytes. Segment/query/playback/training consumers resolve current cue/token references rather than freezing another assignment list. Audio replacement removes stale acoustic segment/dataset membership references and invalidates dependent preparation/evidence even when document bytes are kept. Declared roster membership has its own revision and is retained or cleared only by its explicit election. Historical receipts retain IDs/digests/diagnostics without copied documents or turn arrays.

Unknown duration is null, known zero remains zero, and full media duration is measured independently of cue coverage. Preserve exact original-clock integer/rational mapping outside Cue JSON; deliberately project the consumer millisecond representation under subtitles.

Speaker corpus and trained models

Dataset and model provenance can retain source identities and hashes. It cannot preserve obsolete assignments or frozen subtitle text. Document replacement removes affected segment memberships; mapping correction invalidates affected selection while preserving the document. Invalidated dataset/run/model records clear obsolete derived manifest/preparation references and copied options, then queue their removed managed publications for physical retirement. Current corpus reuse requires rebuilding valid preparation. Original sources and durable completed model weights/checkpoints retain their independent provenance.

Downloaded base models, trained models, embeddings and provider handles are separate kinds. A provider-only version names its sanitized stable handle and actual retrieval capability, without inventing a digest for unavailable weights. Credentials remain local references. Fetching an exact supported output never substitutes a default model silently. Elected training and roster-constrained matching use the shared speaker-model runtime.

Catalog schema 10 supports synthetic job durations from 1 through 600000 milliseconds and retains schema-9 immutable speaker outputs, revisioned exact current profiles and durable checkpoint records. A training run links to actual durable work. Mapping origin distinguishes manual corrections from automatic results; legacy independently confirmed mappings become manual. Schema-8 and schema-9 migration definitions remain frozen. SQLite and PostgreSQL migrate the duration constraint without changing accepted jobs, attempts or receipts; schema-9 snapshots remain restorable. Portable restore validates receipt proofs, output/publication relationships and current evidence, while source corrections retire obsolete matching payloads and assignment-bearing inputs.

Durable jobs, outbox and queries

Lease generations fence long-running work; expected revisions fence user edits. Outbox events are immutable by operation/revision, with claim/outcome state tracked separately. Allocate gap-free event sequences per projection target in the accepting catalog transaction. Apply only the next sequence using a predecessor/generation compare-and-set with facts and exact event receipt in one graph transaction; event 11 cannot discard unapplied event 10 merely because its catalog revision is newer. A new owner installs its higher generation atomically in the graph checkpoint before publishing, preserves the committed sequence and reconciles already committed receipts. Catalog acknowledgements use the current lease generation separately. A graph rebuild selects a recorded catalog revision and replays its accepted evidence. Query/view revisions identify dialect compatibility after backend migration; incompatible native text remains stored with a reason.

Artifact retirement is a catalog operation with an expected lifecycle generation. Atomically claim retirement, verify retention references/active leases and prevent new durable-reference admission or materialization claims before deleting physical locations. A cleanup worker deletes only the claimed key/version/generation, reconciles an unknown result, then records completion. New writers wait, cancel retirement under the current generation or publish a new verified location rather than race a checked-but-not-yet-deleted object. Do not reuse an S3 key while an earlier deletion can still complete; use immutable unique keys or exact object versions. Filesystem publication/deletion shares the runtime's lifecycle lock. Historical identity/provenance survives deliberate byte removal with explicit missing/retired availability.

Adapter acceptance and migration

SQLite/PostgreSQL migrations preserve foreign keys, unknown/precision values, metadata capture states, workspace isolation, current-result replacement receipts, speaker lineage, valid or explicitly invalidated dataset/model references, job claims, stale-worker fencing, idempotent operation reconciliation, outbox replay and consistent export/restore. Shared fixtures compare application results, including JSON/nulls and deterministic ordering. Backend-specific indexes and queue locks optimize operations without changing the contract.

Artifact-store migration copies and verifies bytes/locations before switching selected profiles. Catalog migration transfers typed records and validates all constraints at a paused revision. Graph migration rebuilds from catalog evidence and revalidates saved queries. Cross-store operations use the recovery contracts above. Interruptions retain a recoverable old authority and explicit pending state rather than activating a partly copied workspace. Release packages declare their supported database/server versions.

Catalog schema 7 adds declared roster domains and the truthful untranscribed state. Startup migration widens current-state representation and retains normal logical export/restore integrity; it does not convert media bytes. Portable roster proofs contain IDs and digests, while current membership remains in typed catalog records.

Catalog schema 8 adds revisioned model aliases and configured sources. Alias tombstones preserve remove/recreate concurrency fences; targets distinguish exact base installations, speaker versions and hosted handles. Processing/import work records preserve frozen model selections and shared acquisition dependencies. Schema-7 catalogs and snapshots retain their frozen migration/proof compatibility. Export preserves target relationships, source and alias revisions and opaque credential IDs, while excluding secret values and machine-specific materialization paths. Restored work must resolve its configured credentials in the destination workspace; it cannot silently change the selected model or inference route.