UmbraTrans/docs/umbra-audio-database-prompt-1-schema-contract.txt

UMBRA AUDIO ENTERPRISE DATABASE INTEGRATION — PROMPT 1
CONTINUITY, ACCESS, AND SCHEMA CONTRACT

You are the Umbra Audio integration agent.

Objective:
Design a safe transition for Umbra Audio to use the shared enterprise PostgreSQL database while the audio system continues operating and creating audiobooks.

This is not a shutdown-and-rebuild project. Audiobook production must remain available during the transition. The shared enterprise database will become the authoritative source for accounts, organizations, projects, memberships, permissions, project state, source-book analysis, speakers, voices, translation references, audio runs, artifacts, lineage, and audit history.

The same enterprise database will later be used by the translation/review UI. The audio system and that UI must use the same identities, organizations, projects, permissions, and stable record IDs. They must not create independent account or project silos.

This is a DISCOVERY AND DESIGN PHASE ONLY.

Do not:
- connect to the production database;
- run DDL, DML, migrations, grants, or role changes;
- create tables, alter tables, copy data, or change configuration;
- stop or disable the audiobook production pipeline;
- invent existing columns, tables, users, projects, or production data;
- use or request credentials, tokens, private keys, connection strings, or secret values;
- put secrets in prompts, command lines, environment dumps, logs, reports, or files.

Inputs:
- the current Umbra Audio source code and configuration;
- the current ebook ingestion, analysis, voice, and audiobook-generation pipeline;
- the current API and review UI;
- the current local user, project, job, and artifact persistence;
- the approved shared schema reference, currently the control-plane schema at version 001_control_plane;
- any existing local database, JSON, SQLite, or file-based persistence.

Known shared control-plane concepts:
- organizations
- projects
- users
- roles and permissions
- user_roles and project memberships
- sessions, invitations, and MFA-related records
- audit_events
- project-scoped roles including translator, proofreader, audio_director, voice_manager, project_manager, and release_manager

The shared database currently has tenancy and control-plane tables, but you must verify this rather than assume it. Do not claim that ebook, voice, translation, or audiobook domain tables already exist.

Tasks:

1. Continuity and current-state inventory.

Document how the audio system operates today:
- how an ebook enters the system;
- how analysis creates speakers and voices;
- how audiobook jobs are queued, retried, and completed;
- how artifacts are named, stored, and retrieved;
- how the API and UI identify a project;
- how the current system stores users, projects, and job state;
- which operations must continue during migration;
- which local data is authoritative, cached, or temporary.

For every current persistent object, report:
- logical entity;
- current storage location;
- owner/service;
- primary identifier;
- important fields and types;
- relationships;
- lifecycle/status values;
- whether it is authoritative, derived, cached, or temporary;
- whether it contains personal, licensed, copyrighted, or otherwise sensitive data.

2. Enterprise identity and project-access contract.

Design how the existing audio system and the future translation/review UI will use enterprise identities.

The design must:
- use the enterprise users, organizations, projects, roles, permissions, and user_roles records as the canonical account/project model;
- map any existing local accounts to canonical enterprise user IDs;
- avoid creating a second password store or a second project namespace;
- use the approved enterprise authentication provider or session mechanism;
- support login, logout, session expiry, revocation, and account suspension;
- require project membership before a user can see or work on an audiobook;
- restrict users to projects where they have an active membership;
- use the existing project roles where appropriate, including project_manager, audio_director, translator, proofreader, and viewer;
- record authorization decisions and material changes in audit_events;
- keep human user sessions separate from background audio-worker identities.

The UI and API must derive organization and project context from the authenticated server-side session. They must never trust an organization_id, project_id, role, or user_id supplied by an untrusted browser, request body, query parameter, or local file.

Explicitly define:
- login flow;
- session lifetime and revocation;
- project selection;
- authorization checks for viewing, reviewing, editing, generating, downloading, and releasing;
- behavior for suspended or deactivated users;
- service-to-service authentication for the audio worker;
- account and project segmentation rules.

3. Canonical shared domain model.

Evaluate, and explicitly accept or reject, models equivalent to:

- source books / editions / source documents;
- immutable analysis runs and analysis snapshots;
- speakers or characters;
- canonical voice profiles;
- voice casting and voice reuse;
- voice licensing, consent, and usage restrictions;
- translation runs and language targets;
- translation segments or units;
- audio generation runs and voice-cast snapshots;
- generated ebook and audiobook artifacts;
- pipeline jobs, retries, and idempotency;
- provenance, lineage, checksums, and audit events.

For every proposed table, specify:
- table name and purpose;
- row grain: exactly what one row represents;
- authoritative writer;
- primary key;
- columns, types, nullability, and defaults;
- foreign keys and delete behavior;
- unique constraints;
- indexes;
- organization_id and project_id tenancy fields;
- lifecycle/status state machine;
- RLS requirements;
- data classification and retention;
- idempotency and concurrency strategy;
- whether it is new, an extension of an existing table, or should not be stored in PostgreSQL.

4. Shared-record and cross-language invariants.

The design must ensure that:
- one source ebook has one stable source identity;
- one canonical project contains the audiobook and translation work for that project;
- analysis has immutable versions;
- speaker/character identity is stable across languages;
- a voice profile can be reused across translations only when language, accent, consent, and licensing rules allow it;
- every translation references a source book, project, and specific analysis version;
- every audiobook references a project, translation when applicable, a voice-cast snapshot, and a generation run;
- translated ebooks and audiobooks never become independent duplicate source records;
- the audio system continues creating audiobooks before, during, and after the cutover;
- large media files are stored in object storage, with only immutable object keys, checksums, media metadata, and lifecycle state in PostgreSQL;
- every write is idempotent and safe to retry;
- concurrent workers cannot silently overwrite one another;
- service identities are not represented by arbitrary client-supplied user IDs;
- the future translation UI can discover authorized projects and audiobooks without receiving data from a separate account system.

5. Continuity and cutover plan.

Design a gradual migration that does not interrupt audiobook production.

Include:
- a repository/adapter boundary that hides the storage backend;
- a read-only mirror or shadow-read phase;
- a backfill plan for existing local users, projects, books, jobs, and artifact references;
- stable-ID mapping and reconciliation rules;
- a feature flag or per-project cutover;
- a dual-write or write-through period, if safe;
- validation of counts, IDs, checksums, and status transitions;
- retry and recovery behavior for jobs created during migration;
- a rollback plan that does not delete source data or stop the audio worker;
- a point at which the enterprise database becomes authoritative;
- a plan for historical local records that cannot be mapped.

The audio system must be able to continue audiobook creation during the transition. The translation/review UI must not be a prerequisite for the audio worker to produce audiobooks.

6. Schema delta.

Classify every change as:
- ADD TABLE
- ALTER TABLE
- ADD CONSTRAINT
- ADD INDEX
- ENABLE/PROVE RLS
- BACKFILL
- GRANT/REVOKE
- NEW SERVICE ROLE
- NO CHANGE

For each change provide:
- reason;
- dependency order;
- backward-compatible rollout strategy;
- rollback strategy;
- data-volume estimate;
- lock/availability risk;
- verification query or test;
- whether human approval is required.

Do not emit production migration SQL in this phase. A separate reviewed migration process will produce it.

7. Security and tenancy review.

Explicitly evaluate:
- how the audio system authenticates;
- how the future UI authenticates;
- which database role each component uses;
- how organization and project context is established;
- how RLS is enforced;
- how a human session is distinguished from a worker identity;
- how a worker maps to a project without impersonating a user;
- how service-to-service writes are authorized;
- how audit events are recorded;
- how secrets remain outside the database and application logs;
- whether any existing RLS design can be bypassed by client-controlled settings or roles;
- whether login state can be forged or reused across organizations.

If the existing control-plane RLS or identity mechanism is not safe for a service worker or UI session, mark it as a BLOCKER. Do not work around it.

Required output format:

A. Human-readable executive summary
B. Audio-production continuity assessment
C. Current-state persistence and identity inventory
D. Enterprise identity, session, and project-access contract
E. Proposed canonical entity model
F. Table-by-table contract
G. Shared-record and cross-language invariants
H. Schema delta and migration order
I. Gradual cutover, reconciliation, and rollback plan
J. Security/RLS review
K. Backup and restore requirements
L. Acceptance tests
M. Open questions and blockers
N. Machine-readable YAML or JSON contract

Stop after producing the contract. Do not connect, migrate, or write application data until a human approves it.