UmbraAudio/specs/20-enterprise-database-integration.md

Spec 20 — Umbra Enterprise Database Integration: Continuity, Access, and Schema Contract

• Status: DISCOVERY AND DESIGN ONLY. No production migration SQL. No connection to the production database. No DDL/DML executed.
• Date: 2026-09-25
• Scope: Align UmbraAudio (the English audiobook engine) and the future UmbraTrans translation/review UI onto one shared enterprise PostgreSQL database without interrupting audiobook production.
• Authoritative inputs read: infra/postgres/001_control_plane.sql (584 lines, verified byte-identical to /Volumes/Dock/worstauthor/oldbox/postgres/001_control_plane.sql), infra/bootstrap_umbracontrol_database.sh, specs/18-UmbraTrans.md, specs/19-enterprise-control-plane.md, and the full current UmbraAudio source tree.
• No secrets, credentials, connection strings, tokens, or key material appear in this document.

---

A. Executive summary

A.1 What was actually verified

The shared control-plane schema at version 001_control_plane exists as a file and has a bootstrap script. It contains 13 tables, all tenancy/identity/audit:

schema_migrations, organizations, users, projects, roles, permissions, role_permissions, user_roles, invitations, sessions, mfa_methods, bootstrap_tokens, audit_events.

Verified absent (I am not claiming these exist): there is no source book, edition, work, locale, analysis, speaker, voice, license, translation, audio run, artifact, job, lease, or idempotency table. There is no project_members table (membership is encoded in user_roles). There is no password column on users. There is no service-identity table. Every ebook, voice, translation and audiobook domain concept must be created.

A.2 The three findings that decide the plan

1. There is no local account system to migrate. The prompt asks me to "map any existing local accounts to canonical enterprise user IDs." Verified: the audio system has zero authentication, zero users, zero sessions, and no user model anywhere. The only identity traces are the literal string 'user' in voice_review_history.reviewed_by and exception_reviews.resolved_by. There is exactly one human operator. Mapping therefore means: create one canonical enterprise user, and attribute those legacy 'user' strings to that user id during backfill. No per-project users may be invented.

2. The audio system has no durable utterance identity, and it names its artifacts by a mutable index. On-disk audio is chapter_audio/ch{N}/utt_{utterance_index:04d}.wav (generation/chapter_builder.py:512). utterance_index is renumbered by re-segmentation and by DELETE /api/utterance/{id}, which reindexes the whole chapter and renames files on disk (web/routes.py:3405-3524). specs/18-UmbraTrans.md §3.3 explicitly forbids mutable indices as durable cross-language IDs. The enterprise model must introduce a content-addressed unit key and record it at generation time, while leaving the on-disk filename unchanged so that continuity and the existing staleness markers keep working.

3. The umbracontrol_worker role cannot read any project and cannot attribute an audit event. RLS is enabled on projects and every policy requires app.current_user_id(). The worker sets no user GUC, so it matches zero rows. audit_events_insert requires actor_user_id = app.current_user_id(), which is NULL for the worker, so worker actions are anonymous. This is a BLOCKER (see §M). Spec 19 §5.5 anticipated it ("Worker service accounts receive only the permissions required for their queue and artifact scope") but 001_control_plane never implemented a mechanism.

A.3 Direction change that needs ratification

specs/19-enterprise-control-plane.md §2.3 and §16 M4 specify that the control plane imports a read-only snapshot of UmbraAudio and that the English path keeps running in UmbraAudio unchanged. This document reverses that: the enterprise database becomes authoritative for project state, source analysis, speakers, voices, translation references, audio runs, artifacts, lineage and audit, and UmbraAudio reads and writes it as a client. See §M DECISION-1. Spec 19 §1's narrower prohibition — never put control-plane tables inside the local per-project SQLite database — remains in force and is honoured.

A.4 Production continuity verdict

Audiobook production can and must continue throughout. The design keeps the existing filesystem + SQLite engine running untouched and introduces a repository/adapter boundary so that "where state lives" becomes a configuration decision rather than a code rewrite. The enterprise database is authoritative for identity, tenancy, metadata and release; the audio worker holds a bounded signed lease and can keep rendering audio if the enterprise database is briefly unreachable, which is the only way to satisfy "production remains available" and "the enterprise database is authoritative" simultaneously.

---

B. Audio-production continuity assessment

B.1 How an ebook enters the system today

1. POST /api/projects (web/routes.py:533) or POST /api/upload-manuscript (web/routes.py:620) receives a .docx/.epub/.txt/.md/.pdf upload.
2. permanent_path = permanent_dir / filename (routes.py:647) — the raw multipart filename is used, unsanitised. See §J.6.
3. project_bootstrap.create_project_from_manuscript_async → load_config(name) → config.py:165 ensure_dirs() creates five directories as a side effect of resolving the project.
4. detect_format + parse_manuscript populate chapters (source_text holds the full copyrighted text) and utterances.
5. UPDATE project SET source_path (routes.py:574, 665).
6. With mode=new, the previous run is copied to archives/<timestamp>/ and then shutil.rmtree is applied to voices/, voice_tests/, chapter_audio/, analysis/ (routes.py:712-742). This is the most destructive operation in the system.

B.2 How analysis creates speakers and voices

• Discovery (pass 1-3, LLM via LM Studio, analysis/character_discovery.py) writes discovery/discovery_pass{1,1b,2,3}.json, ending with speakers.json.
• Profiling writes voice_profiles.json (voices/voice_profile_manager.py:753).
• Casting is a fifth parallel store: voice_assignments.json ({character_name: "en-US-JennyNeural"}, scripts/assign_voices.py:161) plus the global ~/.umbra/voice_registry.db (voice_registry, registry_aliases) and library_speakers (library.py:21).
• Voice reference WAVs are written to .umbra/voices/{slug}_ref.wav (voices/voice_designer.py:248), with ~/.umbra/library/voices/ as a global set, voice_embeddings/{slug}.npy, and server-side voice_prompts/{slug}.pt.
• Attribution (analysis/llm_attributor.py) writes utterances.speaker_name — a free-text denormalised label, not a foreign key to characters.

Verified consequence: one speaker exists in five stores under four different keys (characters.id integer, speakers.json.primary_name, voice_profiles.json name key, voice_registry.character_name, library_speakers.primary_name). This is the single largest identity defect in the current system and is the reason §E proposes a canonical speakers table with speaker_aliases.

B.3 How audiobook jobs are queued, retried and completed

There is no queue and no job table. Verified across umbraaudio/ and scripts/: zero occurrences of lease, heartbeat, backoff, or exponential.

• Orchestration primitive: threading.Thread(target=..., daemon=True), at 15 call sites (routes.py:827, 1925, 3139, 3372, 3474, 3657, 3893, 3950, 4039, 4179, 4253, 4565, 5019, 5109, 5380), plus PipelineManager.start() as an asyncio task (pipeline_manager.py:121).
• Work is a for loop over SELECT ... FROM utterances WHERE chapter_id = ? ORDER BY utterance_index.
• The only mutual exclusion is GenerationManager._lock (voices/generation_manager.py:25), which is re-instantiated per HTTP request (routes.py:475-479), so the real guard is the on-disk active boolean in generation_status.json — a TOCTOU check, not a mutex. ChapterBuilder has no lock at all (routes.py:5097-5110).
• Retry: linear time.sleep(5 * (attempt + 1)) on QwenTTSUnavailableError (chapter_builder.py:615); the batch driver scripts/generate_all_audio.py has no retry loop at all (:202-241).
• umbraaudio/generation/retry.py defines a RetryHandler that nothing imports — dead code.
• Job identity is the output filename, derived from (chapter_id, utterance_index).

B.4 How artifacts are named, stored and retrieved

Artifact	Path pattern	Versioned?	Checksum?
Per-utterance WAV	.umbra/chapter_audio/ch{N}/utt_{idx:04d}.wav	No — overwritten in place	No
Staleness marker	.../utt_{idx:04d}.wav.txt	n/a	16-hex truncated SHA-256 of the voice ref only
Chapter master	.umbra/chapter_audio/ch{N}_full.wav	No	No
Chapter MP3	.umbra/chapter_audio/ch{N}_full.mp3	No	No
Full book	.umbra/export/{m4b_meta.output_name} e.g. Deal_Hunter.m4b	No	No
AR package	.umbra/export/ar_package/{Base}_{NN}_{Title}.mp3	No	No (only size_bytes)
AR manifest	.umbra/export/ar_package.json	Overwritten wholesale	No

Verified: no SHA-256 or MD5 of any produced audio file is computed or stored anywhere. Every hash in the repository is a cache key, a voice fingerprint, a watermark bit-vector seed, or a deterministic TTS parameter seed. Retrieval is via a StaticFiles mount plus 12 FileResponse endpoints, of which zero perform a user authorization check.

B.5 How the API and UI identify a project

A single browser cookie, active_project, set at routes.py:529 and routes.py:616 with only max_age=86400*365 — no httponly, no samesite, no secure. The complete gate for all 129 project-scoped routes is:

# web/routes.py:482-491
def _get_active_config(request: Request):
    active_slug = request.cookies.get("active_project")
    if not active_slug:
        raise NoActiveProjectError("No active project selected.")
    config = load_config(active_slug)
    if not (config.data_dir / "project.db").exists():
        raise NoActiveProjectError(f"Project '{active_slug}' no longer exists.")
    return config

The cookie is never checked against list_projects() or any allow-list. load_config accepts absolute paths verbatim (config.py:156-158) with no containment check. And the if not cfg.data_dir.exists(): return 404 guard at routes.py:525 is unreachable dead code, because load_config already created the directory at config.py:165.

list_projects() (config.py:20-47) is a directory scan of the entire repository root — the same directory holding umbraaudio/, scripts/, tests/, models/, .venv/ — and it opens a SQLite connection to every candidate on every page render.

B.6 Operations that must continue during migration

Uninterrupted: upload/parse, discovery, profiling, voice-reference generation, LLM attribution, speaker/alias/merge editing, name-token resolution, pronunciation editing, utterance audio generation, chapter build + stitch, full-book generation, m4b and AR MP3 export, QA checks.

Explicitly not a prerequisite: the translation/review UI. The audio worker must reach a usable state with the UI absent or offline.

B.7 Authority classification of local data

Data	Class	Note
chapters.source_text, utterances.text	Authoritative	Full copyrighted manuscript text
speakers.json, voice_profiles.json, voice_assignments.json, characters	Authoritative, 4-way conflict	Must be reconciled in Phase P3
voices/*_ref.wav, ~/.umbra/library/voices/, voice_embeddings/*.npy, voice_prompts/*.pt	Authoritative + biometric	See §C.2
*.wav, *.mp3, *.m4b	Authoritative artifacts, unversioned	No checksums
*.wav.txt markers	Authoritative idempotency state	Must be preserved verbatim across cutover
manifests/ch{N}.json	Derived from SQLite
pipeline_status.json, pipeline_checkpoint.json, analysis_status.json, ar_build_status.json, scan_results.json	Cache/progress	4 of 5 written non-atomically
generation_status.json	Cache/progress, atomic	The one correct pattern (generation_manager.py:52-55)
ch{N}_build_status.json	Cache/progress, non-atomic	Rewritten ~2× per utterance (chapter_builder.py:503)
defects.json (1.3 MB), debug_*.json	Derived/debug	Contains LLM reasoning
discovery_pass*.json	Temporary	Inputs to speakers.json
cache/cache_index.json, voice_analysis.json	Cache

---

C. Current-state persistence and identity inventory

C.1 Legacy objects (full field detail in §F rationale; summary here)

#	Logical entity	Storage	Owner	Primary identifier	Lifecycle	Class	Sensitivity
1	Project	<base>/<slug>/ + SQLite project	audio app	filesystem dir name (slug); project.id INTEGER	initialized	Authoritative	Copyrighted
2	Source manuscript	uploads/, last_upload/, project.source_path	audio app	filesystem path	replaced in place	Authoritative	Copyrighted + licensed
3	Chapter	SQLite chapters	audio app	chapters.id INTEGER (per-DB)	parsed	Authoritative	Copyrighted
4	Utterance	SQLite utterances (26 cols)	audio app	utterances.id INTEGER; no UNIQUE on (chapter_id, utterance_index)	pending	Authoritative	Copyrighted derivative
5	Speaker	5 stores (see §B.2)	audio app	4 different keys	identified / unidentified	Authoritative, conflicting	Personal (voice)
6	Voice review	SQLite voice_review_history (no project_id)	audio app	id INTEGER	append	Authoritative	Personal
7	Pronunciation term	3 stores (pronunciation_guide, phonetic_names.json, library_names)	audio app	term TEXT; UNIQUE (project_id, term) on one only	confirmed	Authoritative	Low
8	Generated audio	SQLite generated_audio + filesystem	audio app	id INTEGER	generated	Table not written by the pipeline; filesystem is truth	—
9	Quality report	SQLite quality_reports	audio app	id INTEGER	—	Table exists, no row is ever written (database.py:549 is the only caller)	—
10	Review decision	SQLite review_decisions (polymorphic entity_type/entity_id, no FK)	audio app	id INTEGER	—	Authoritative	Low
11	Attribution exception	SQLite exception_reviews (web-only table, absent from database.py)	audio web	id INTEGER	pending/resolved	Authoritative	Low
12	Orphan name token	SQLite name_tokens; UNIQUE (project_id, token)	audio app	token TEXT	unresolved	Authoritative	Low
13	Voice reference	voices/{slug}_ref.wav, ~/.umbra/library/voices/	audio app	filename slug	exists/absent	Authoritative	Biometric + consent
14	Voice embedding	voice_embeddings/{slug}.npy	audio app	filename slug	—	Cache	Biometric
15	Qwen voice prompt	voice_prompts/{slug}.pt (server-side)	Qwen TTS server	slug	—	Cache	Biometric
16	Pipeline checkpoint	pipeline_status.json, pipeline_checkpoint.json	audio app	none	step state	Cache	Low
17	Generation run	generation_status.json, ch{N}_build_status.json	audio app	none	active bool / phase	Cache	Low
18	Artifact manifest	export/ar_package.json	audio app	—	rebuilt wholesale	Authoritative for shipped package	Low
19	Job	DOES NOT EXIST	—	—	—	—	—
20	User / session / role	DOES NOT EXIST	—	—	—	—	—

C.2 Data classification — the two sensitive classes

Class R (Restricted — biometric + consent). voices/*_ref.wav, voice_embeddings/*.npy, voice_prompts/*.pt, ~/.umbra/library/voices/. These encode a human or cloned vocal identity. Where a real person's voice was cloned, EU/UK AI and biometric rules and the provider's own consent terms apply. No retention period, no consent-evidence record, no territory or media restriction, and no takedown mechanism exists today. These must never appear in PostgreSQL; only keys, checksums and rights metadata do.

Class C (Confidential — copyrighted). chapters.source_text, utterances.text, utterances.context_before/after, uploads/, all produced audio and exports, defects.json. Underlying the author's licence. Translation derivatives inherit this class and must not outlive the source entitlement.

Class P (Personal). users.email, sessions.ip_address, sessions.user_agent, audit_events.ip_address, plus the legacy 'user' actor strings.

C.3 Verified schema divergence (a migration hazard in its own right)

There are four independent DDL authorities for the same project database:

1. umbraaudio/database.py:9-166 — 10 tables (SCHEMA).
2. umbraaudio/web/routes.py:102-275 — 11 tables (SCHEMA_SQL); adds exception_reviews + 3 indexes + 3 qa_* columns on utterances.
3. umbraaudio/analysis/name_tokens.py:504-520 — name_tokens only, no indexes.
4. scripts/reattribute_all.py:30-42 — ALTER TABLE only.

Both live project databases have been built by ALTER TABLE under older schemas, so column ordering differs from both schema files; DealHunterShip lacks name_tokens entirely; and neither database has PRAGMA foreign_keys=ON, so every REFERENCES clause in the legacy schema is declarative only. There are zero CHECK constraints in either legacy schema file.

---

D. Enterprise identity, session, and project-access contract

D.1 Canonical model

The enterprise users, organizations, projects, roles, permissions and user_roles records are the canonical account and project model. No second password store is created (verified: users has no password column, and users_auth_subject_idx is a partial unique index on (auth_provider, auth_subject)). No second project namespace is created.

Membership is not a new table. user_roles with scope='project' is the membership record. "Active membership" ≡ user_roles.scope='project' AND user_roles.status='active' AND users.status='active'. This is deliberate: a separate project_members table would create two sources of truth for the same fact. I reject the project_members and project_access_grants models from spec 19 §8.1 for phase one.

D.2 Login flow

1. Unauthenticated request → 302 to the enterprise IdP (OIDC authorization-code + PKCE).
2. IdP callback → the API verifies the ID token signature, issuer, audience, nonce, exp and azp server-side, and maps iss + sub to users.auth_provider + users.auth_subject. A users row is created on first login only if a matching invitations row exists and is unexpired, unrevoked and unaccepted.
3. If users.mfa_required is true, the IdP must assert an MFAacr/amr value satisfying the policy; the API records sessions.mfa_verified_at. Today no such column exists (§M B4).
4. The API issues a session: 32 random bytes, stored only as SHA-256 in sessions.token_hash; the plaintext goes to the browser in an HttpOnly; Secure; SameSite=Lax cookie.
5. sessions.ip_address and user_agent recorded.
6. Bootstrap path (first run only): bootstrap_tokens.token_hash → set password/MFA at the IdP → consumed_at. The bootstrap token is single-use and 24 h (bootstrap script:301-302).

The single authentication entry point is one SECURITY DEFINER function, app.resolve_session(p_token_hash text), returning the user id only if all of: hash match, revoked_at IS NULL, expires_at > now(), users.status='active', organizations.status is active for the requested org, and MFA satisfied when required. Every other code path authorises from the returned principal, never from a GUC or a request value.

D.3 Session lifetime and revocation

• Idle timeout 8 h, absolute 7 days, both stored in sessions.expires_at (absolute) plus an application-side idle clock.
• Logout: UPDATE sessions SET revoked_at = now() for the presented token.
• "Sign out everywhere": requires new capability — sessions_self is the only policy (001:526-529) and is USING (user_id = app.current_user_id()), so a user cannot revoke their own other sessions and an admin cannot revoke anyone's. Fix: app.revoke_user_sessions(uuid) as SECURITY DEFINER, executable by the owning user and by an org admin, writing audit_events.
• Suspension is enforced in app.resolve_session, not by RLS, because no RLS policy reads users.status. A suspended user's existing session stops resolving on the next request; the row remains for forensics. This is the accepted design, with the caveat recorded in §M B5.

D.4 Project selection

Selection is server-derived, never client-declared. The request path carries an opaque project_id (uuid). The API resolves organization_id from projects and validates app.can_access_project(project_id) on every request before any domain read. The browser may hold a selected project hint; it is a signed, server-issued value and is advisory only — a stale or forged value cannot widen access, because access is recomputed from the authorised-project set each time. The existing unsigned active_project cookie is retired.

D.5 Authorisation checks by action

Every action maps to a permissions.key. Existing keys are listed in 001:378-391; the keys missing and required by the prompt are enumerated in §H as ADD TABLE (data) / BACKFILL.

Action	Required permission	Role(s) granted	Exists in 001?
View project metadata	project.access.view (via membership)	any project role	No — implicit only
Read manuscripts/utterances	project.source.read	all project roles	No
Edit speakers/aliases/merges	project.speakers.edit	project_manager, audio_director	No — destructive today, unpermissioned
Run analysis / attribution	project.analysis.run	project_manager, audio_director	No
Review translations	project.translation.review	proofreader	Yes
Edit translations	project.translation.edit	translator, project_manager	Yes
Generate audio	project.audio.generate	project_manager, audio_director	No — no generate permission exists
Approve audio	project.audio.approve	audio_director, project_manager	Yes
Cast voices	project.voice.cast	audio_director	No
Manage voice library/licences	voice.catalog.manage	voice_manager	Yes (but see §J.3 — cannot see a project)
Download approved artifact	artifact.download	member, viewer	Yes
Download unapproved/unreleased	artifact.download.unreleased	audio_director, release_manager	No — and today all downloads are ungated
Approve release	project.release.approve	release_manager, project_owner	Yes
Manage project access	project.access.manage	project_owner, organization_admin	Yes
Delete/reset project state	project.state.destroy	project_owner	No — rmtree is unpermissioned today
Manage own sessions/MFA	user.self.manage	self	No

D.6 Suspended / deactivated behaviour

users.status ∈ invited | active | suspended | deactivated | locked. In every non-active state: existing sessions stop resolving, new logins are refused, background jobs owned by that user are cancelled, and historical attribution and audit rows are preserved — suspension never rewrites history. organizations.status ∈ active | suspended | archived gates all project access for members of that org.

D.7 Service-to-service authentication for the audio worker

The worker is never a users row and never impersonates one. It authenticates as the umbracontrol_worker PostgreSQL role, and presents a service identity (service_identities, §E) plus a lease (job_leases). RLS policy branches for the worker are keyed on app.current_service_identity_id() and the existence of a live lease joining that service identity to the row's project_id. A worker can therefore read and write exactly the projects it is currently engaged on, and nothing else. A worker with no lease sees zero rows. This satisfies "a worker maps to a project without impersonating a user" and "a worker cannot read a project outside its lease" (spec 19 §5.5).

D.8 Account and project segmentation rules

1. organization_id is the tenant boundary; present on every tenant-owned row.
2. project_id scopes work; a user reaches a project only through an active user_roles project-scope row, or org-admin, or platform-admin.
3. A user in multiple organizations holds separate user_roles rows per org; there is no cross-org inheritance and no "default org" that grants implicit access.
4. Sessions are user-global, not org-scoped. A session token is therefore valid for every org that user belongs to — correct for a human, and the reason project/org context must be re-derived and re-authorised on every request rather than cached in the session.
5. Voice assets, licences and entitlements are org-scoped and reusable across that org's projects only, and only when language, accent, consent, territory, media and term allow.

---

E. Proposed canonical entity model

Accept / reject decisions on the models the prompt asked me to evaluate.

#	Proposed model	Decision	Rationale
1	Source books / editions / source documents → works + source_documents	ACCEPT	A public-domain edition and an author manuscript are different assets of one bibliographic work (spec 19 §6.1). UmbraAudio's project.source_path becomes a source_documents row.
2	Immutable analysis runs + snapshots → analysis_runs + analysis_snapshots	ACCEPT	Mandatory for invariant "analysis has immutable versions". Snapshots are content-addressed, never updated.
3	Speakers / characters → speakers + speaker_aliases	ACCEPT	Resolves the 5-store/4-key defect (§B.2). One row per logical speaking entity per project.
4	Canonical voice profiles → voice_profiles + voice_profile_revisions	ACCEPT	Spec 19 §11.1: logical identity ≠ provider asset ≠ profile.
5	Voice casting and reuse → voice_casts	ACCEPT	Casting is a fact about (project, locale, speaker, revision) and must be snapshot-able.
6	Voice licensing / consent / usage → voice_licenses	ACCEPT (phase 1: 1 table)	Rights are the gating fact for reuse. voice_entitlements, voice_reservations, voice_rate_cards, voice_invoices are DEFERRED to a later phase — no commercial billing is in scope for this transition.
7	Translation runs + language targets → project_locales + translation_runs	ACCEPT	project_locales is the locale lane; no translations_de-style tables.
8	Translation segments / units → translation_units	ACCEPT	Grain = one ordered target render span.
9	Audio generation runs + voice-cast snapshot → audio_runs + audio_run_casts	ACCEPT	The cast snapshot must be frozen per run so a release can be reproduced after casting changes.
10	Generated ebook / audiobook artifacts → artifacts	ACCEPT	Metadata only; bytes in object storage.
11	Pipeline jobs, retries, idempotency → jobs + job_attempts + job_leases	ACCEPT	Closes the "no queue, filename is identity" defect.
12	Provenance, lineage, checksums, audit → artifact_links + reuse audit_events	ACCEPT	audit_events already exists and is append-only by trigger (001:356-359).
13	project_members (separate table)	REJECT	Membership is user_roles(scope='project'). A second table is a second source of truth.
14	project_access_grants (spec 19 §8.1)	REJECT for phase 1	No non-user principals exist yet. Revisit when a translation-vendor principal type is needed.
15	works ↔ projects 1:1	REJECT — use 1:N	A work may be produced in several projects (re-translation, new edition). projects.work_id FK.
16	semantic_packages, identity_registries, glossary_revisions, memory_revisions, alignment_links, target_render_spans (spec 19 §8.2)	DEFER	These are UmbraTrans translation-design objects. They are required for the translation UI, not for the audio system to keep producing books. Adding them now would exceed the continuity objective. Listed in §M as a phase-2 scope item.
17	Storing WAV/MP3/M4B/EPUB bytes in PostgreSQL	REJECT — hard	Object storage only (§G invariant 8).
18	Storing voice reference audio or embeddings in PostgreSQL	REJECT — hard	Class R biometric data; object storage with rights metadata.

E.1 The stable utterance identity (the load-bearing new concept)

Because artifacts are named by a mutable index, the enterprise model introduces a content-addressed unit key and records it at generation time, without changing any on-disk filename:

source_unit_key = sha256(
      project_id            ‖ source_revision_id ‖
      chapter_ordinal       ‖ occurrence_index  ‖
      text_sha256
)

source_units is keyed by source_unit_key. artifacts reference (source_revision_id, unit_ordinal, text_sha256) and record the legacy path in legacy_object_key. Consequences:

• A re-segmentation is detectable: the same ordinal now has a different text_sha256, so the enterprise record and the on-disk file diverge visibly instead of silently.
• The existing *.wav.txt marker logic (generation/staleness.py) keeps working unchanged, so continuity is preserved and the audio path is not blocked on this migration.
• UmbraTrans can reference English units with a durable key, satisfying spec 18 §3.3, which forbids using a mutable utterance_index or a database row id as a cross-language ID.

---

F. Table-by-table contract

Common conventions for every table below:

• PK: id uuid PRIMARY KEY DEFAULT gen_random_uuid() (v4, matching 001 exactly — no version dependency on the server's PostgreSQL version).
• Tenancy: organization_id uuid NOT NULL, project_id uuid NOT NULL (nullable only for org-scoped tables), enforced by FOREIGN KEY (project_id, organization_id) REFERENCES projects(id, organization_id). This composite FK is possible because 001:51 already declares UNIQUE (id, organization_id) on projects — the schema anticipated this pattern.
• Concurrency: row_version integer NOT NULL DEFAULT 1, incremented by trigger, with optimistic UPDATE ... WHERE id = ? AND row_version = ?. Job claiming additionally uses FOR UPDATE SKIP LOCKED.
• RLS: ENABLE ROW LEVEL SECURITY (never FORCE on the mutually-referencing tables — see §J.2), plus a tenant_read / tenant_write policy pair using the composite FK columns.
• Idempotency: natural unique constraints stated per table; all writes are INSERT … ON CONFLICT or guarded by a uniqueness violation, never blind overwrites.
• New / extension / not-in-PostgreSQL stated per table.

---

F.1 works

• Purpose: bibliographic identity of a book, independent of any production project.
• Grain: one row per distinct work.
• Authoritative writer: ingestion service (human via API).
• Columns: id uuid PK; organization_id uuid NOT NULL; title text NOT NULL; author text; series text; isbn13 text; source_language text; rights_status text NOT NULL DEFAULT 'unknown'; rights_evidence_uri text; created_at, updated_at timestamptz NOT NULL DEFAULT now().
• FKs: organization_id → organizations(id) ON DELETE RESTRICT.
• Unique: UNIQUE (organization_id, isbn13) where isbn13 IS NOT NULL.
• Indexes: works_org_idx (organization_id); works_title_trgm (pg_trgm) later.
• State machine: active → archived. No separate status column needed in phase 1; reuse archived_at timestamptz NULL.
• RLS: org-scoped read for members; write requires organization.projects.manage.
• Classification: C (title/author are bibliographic, not licensed text).
• Idempotency/concurrency: UNIQUE (organization_id, isbn13); row_version.
• Verdict: NEW.

F.2 source_documents

• Purpose: an immutable ingested file (DOCX/EPUB/TXT/PDF) of a work.
• Grain: one row per distinct ingested file version.
• Authoritative writer: ingestion service.
• Columns: id uuid PK; organization_id, project_id NOT NULL; work_id uuid NOT NULL → works(id) ON DELETE RESTRICT; media_type text NOT NULL; byte_size bigint NOT NULL CHECK (byte_size > 0); sha256 char(64) NOT NULL; object_key text NOT NULL; original _filename text; ingested_by_user_id uuid → users(id) ON DELETE SET NULL; ingested_at timestamptz NOT NULL DEFAULT now(); superseded_at timestamptz; row_version integer NOT NULL DEFAULT 1.
• Unique: UNIQUE (project_id, sha256) — re-ingesting the same bytes is a no-op.
• Indexes: source_documents_project_idx (project_id, ingested_at DESC); source_documents_work_idx (work_id).
• State machine: active → superseded → withdrawn.
• RLS: project tenant policy; WITH CHECK also requires the actor to hold project.source.edit.
• Classification: C.
• Idempotency: content-addressed by sha256; re-upload returns the existing row.
• Verdict: NEW. Backfilled from project.source_path and uploads/.

F.3 source_revisions

• Purpose: a frozen, numbered revision of the source text — the anchor for all analysis, translation and audio lineage.
• Grain: one row per frozen source-text revision.
• Authoritative writer: source-editor workflow.
• Columns: id uuid PK; organization_id, project_id NOT NULL; work_id uuid NOT NULL; source_document_id uuid NOT NULL → source_documents(id) ON DELETE RESTRICT; revision_number integer NOT NULL; label text; sha256 char(64) NOT NULL; object_key text NOT NULL; word_count integer; locked boolean NOT NULL DEFAULT false; locked_by_user_id uuid; locked_at timestamptz; approved_by_user_id uuid; approved_at timestamptz; created_at timestamptz NOT NULL DEFAULT now(); row_version integer NOT NULL DEFAULT 1.
• FKs: locked_by_user_id/approved_by_user_id → users(id) ON DELETE SET NULL (history must survive account deletion).
• Unique: UNIQUE (project_id, revision_number); UNIQUE (id, project_id, organization_id) to enable child composite FKs.
• Indexes: source_revisions_project_idx (project_id, revision_number DESC).
• State machine: draft → locked → approved (immutable after locked; enforced by a BEFORE UPDATE trigger raising when OLD.locked), or draft → abandoned.
• RLS: tenant policy; write requires project.source.edit.
• Classification: C.
• Idempotency: UNIQUE (project_id, revision_number); content sha256 recorded.
• Verdict: NEW.

F.4 chapters (enterprise)

• Purpose: chapter boundaries of a source revision.
• Grain: one row per chapter per source revision.
• Columns: id uuid PK; organization_id, project_id NOT NULL; source_revision_id uuid NOT NULL; ordinal integer NOT NULL; title text; text_sha256 char(64) NOT NULL; word_count integer NOT NULL DEFAULT 0; created_at; row_version.
• Unique: UNIQUE (source_revision_id, ordinal).
• Indexes: chapters_project_idx (project_id, ordinal).
• State machine: none; immutable with its source revision.
• RLS: tenant read; write requires project.source.edit.
• Idempotency: ON CONFLICT (source_revision_id, ordinal) DO UPDATE.
• Verdict: NEW (distinct from legacy SQLite chapters; name is the same by intent, but the enterprise table is uuid-keyed and revision-scoped). To avoid confusion the enterprise table is named source_chapters.

F.5 source_units

• Purpose: the atomic addressable source element — the durable unit that audio clips, translation units and review items all reference.
• Grain: one row per addressable source text unit within a source revision.
• Authoritative writer: analysis/segmentation pipeline.
• Columns: id uuid PK; organization_id, project_id NOT NULL; source_revision_id uuid NOT NULL; source_chapter_id uuid NOT NULL; ordinal integer NOT NULL; occurrence integer NOT NULL DEFAULT 0; text text NOT NULL; text_sha256 char(64) NOT NULL; unit_kind text NOT NULL DEFAULT 'utterance' CHECK (unit_kind IN ('narration','utterance', 'heading','scene_break','footnote')); speaker_name text; speaker_id uuid → speakers(id) ON DELETE SET NULL; attribution_confidence numeric(4,3); attribution_evidence_kind text; attribution_tag_name text; attribution_uncertain boolean NOT NULL DEFAULT false; needs_review boolean NOT NULL DEFAULT false; review_reason text; user_edited boolean NOT NULL DEFAULT false; created_at; row_version.
• Unique: UNIQUE (source_revision_id, ordinal, occurrence); plus a generated source_unit_key char(64) = sha256(source_revision_id || ordinal || occurrence || text_sha256), UNIQUE. See §E.1.
• Indexes: source_units_chapter_idx (source_chapter_id, ordinal); source_units_speaker_idx (project_id, speaker_id); source_units_review_idx (project_id, needs_review) WHERE needs_review; source_units_sha_idx (source_revision_id, text_sha256).
• State machine: proposed → attributed → reviewed; needs_review is an overlay flag, not a state.
• RLS: tenant read; write requires project.analysis.run or project.speakers.edit.
• Classification: C.
• Idempotency: upsert on (source_revision_id, ordinal, occurrence); text change bumps row_version and flips needs_review.
• Verdict: NEW. This is the enterprise replacement for legacy SQLite utterances.

F.6 speakers

• Purpose: the logical speaking entity — the stable cross-language character identity.
• Grain: one row per logical speaker per project.
• Columns: id uuid PK; organization_id, project_id NOT NULL; canonical_name text NOT NULL; kind text NOT NULL DEFAULT 'character' CHECK (kind IN ('character','narrator', 'group','system','unknown')); gender text; age_range text; description text; communication_style text; is_group boolean NOT NULL DEFAULT false; is_unidentified boolean NOT NULL DEFAULT false; unidentified_reason text; designation text NOT NULL DEFAULT 'needs_review' CHECK (designation IN ('clear','needs_review')); sample _dialogue text; source text NOT NULL DEFAULT 'analysis' CHECK (source IN ('analysis', 'manual','imported','legacy')); created_at; updated_at; row_version integer NOT NULL DEFAULT 1.
• Unique: UNIQUE (project_id, canonical_name).
• Indexes: speakers_project_idx (project_id, designation); speakers_name_trgm (project_id, canonical_name gin_trgm_ops) (pg_trgm, phase 2).
• State machine: discovered → profiled → confirmed; merge is a superseded_by pointer, never a delete.
• RLS: tenant read; write requires project.speakers.edit.
• Classification: P (name + gender + description).
• Idempotency: ON CONFLICT (project_id, canonical_name) DO UPDATE; merges are UPDATE speakers SET superseded_by = target + alias fold, both in one transaction.
• Verdict: NEW. Replaces characters + speakers.json + voice_profiles.json as the single speaker authority.

F.7 speaker_aliases

• Purpose: alternate surface forms and the evidence for each, preserving today's aliases / alias_evidence model.
• Grain: one row per (speaker, alias form).
• Columns: id uuid PK; organization_id, project_id NOT NULL; speaker_id uuid NOT NULL → speakers(id) ON DELETE CASCADE; alias citext NOT NULL; evidence jsonb NOT NULL DEFAULT '{}'; confidence numeric(4,3); is_confirmed boolean NOT NULL DEFAULT false; created_at; updated_at.
• Unique: UNIQUE (project_id, alias).
• Indexes: speaker_aliases_alias_idx (project_id, alias); speaker_aliases_speaker_idx (speaker_id).
• State machine: none; is_confirmed is a flag.
• RLS: tenant; write requires project.speakers.edit.
• Idempotency: ON CONFLICT (project_id, alias) DO NOTHING then merge evidence.
• Verdict: NEW.

F.8 analysis_runs

• Purpose: an immutable record of one analysis execution (which model, which prompt version, which inputs, which parameters).
• Grain: one row per analysis execution.
• Columns: id uuid PK; organization_id, project_id NOT NULL; source_revision_id uuid NOT NULL; run_kind text NOT NULL CHECK (discovery, segmentation, attribution, emotion, voice_analysis); engine text NOT NULL; model_id text NOT NULL; prompt_version text NOT NULL; input_sha256 char(64) NOT NULL; params jsonb NOT NULL DEFAULT '{}'; status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'succeeded','failed','cancelled','superseded')); started_by_service_id uuid; started_at timestamptz NOT NULL DEFAULT now(); finished_at timestamptz; metrics jsonb NOT NULL DEFAULT '{}'; error text; superseded_by_run_id uuid → analysis_runs(id) ON DELETE SET NULL.
• Unique: UNIQUE (project_id, run_kind, source_revision_id, input_sha256, prompt_version, model_id) — re-running an identical analysis is a no-op, which is the idempotency requirement.
• Indexes: analysis_runs_project_idx (project_id, run_kind, started_at DESC).
• State machine: running → succeeded | failed | cancelled; succeeded → superseded.
• RLS: tenant read; write: worker lease-scoped, human project.analysis.run.
• Classification: C (params may embed text).
• Idempotency/concurrency: the wide UNIQUE; FOR UPDATE on transition.
• Verdict: NEW.

F.9 analysis_snapshots

• Purpose: the immutable result payload of an analysis run, content-addressed.
• Grain: one row per produced snapshot.
• Columns: id uuid PK; organization_id, project_id NOT NULL; analysis_run_id uuid NOT NULL → analysis_runs(id) ON DELETE RESTRICT; snapshot_kind text NOT NULL; sha256 char(64) NOT NULL; object_key text; inline_payload jsonb; byte_size bigint; created_at.
• Unique: UNIQUE (project_id, snapshot_kind, sha256); UNIQUE (analysis_run_id, snapshot_kind).
• Indexes: analysis_snapshots_run_idx (analysis_run_id).
• State machine: immutable, no updates.
• RLS: tenant read; write only by the producing worker under lease.
• Classification: C.
• Idempotency: content-addressed.
• Verdict: NEW. Satisfies "analysis has immutable versions".

F.10 voice_profiles

• Purpose: a reusable voice identity profile (spec 19 §11.1) — the logical reusable character/performance identity, not a provider asset.
• Grain: one row per voice profile per organization.
• Columns: id uuid PK; organization_id uuid NOT NULL; project_id uuid NULL (null = org-shared); name text NOT NULL; locale_tag text; gender text; accent text; pitch_register, pace_baseline, energy, timbre, texture, formality, emotional _baseline, accent_marker, distinguishing_trait (all text); voice_description text; reference_object_key text; reference_sha256 char(64); engine text NOT NULL DEFAULT 'qwen3-tts'; engine_voice_id text; is_narrator boolean NOT NULL DEFAULT false; status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','in_review','approved', 'rejected','retired')); created_at; updated_at; row_version integer NOT NULL DEFAULT 1.
• Unique: UNIQUE (organization_id, name).
• Indexes: voice_profiles_org_idx (organization_id, status).
• State machine: draft → in_review → approved | rejected; approved → retired.
• RLS: org policy — any org member may read metadata; reference_object_key access requires voice.catalog.manage or an active cast.
• Classification: P; the reference audio is Class R and never in PostgreSQL.
• Idempotency: upsert on (organization_id, name); profile changes create voice_profile_revisions.
• Verdict: NEW. Replaces voice_registry + library_speakers + voice_profiles.json.

F.11 voice_profile_revisions

• Purpose: immutable history of a voice profile so a past render stays reproducible.
• Grain: one row per profile change.
• Columns: id uuid PK; organization_id uuid NOT NULL; voice_profile_id uuid NOT NULL → voice_profiles(id) ON DELETE CASCADE; revision_number integer NOT NULL; payload jsonb NOT NULL; sha256 char(64) NOT NULL; changed_by_user_id uuid; change_reason text; created_at timestamptz NOT NULL DEFAULT now().
• Unique: UNIQUE (voice_profile_id, revision_number).
• Indexes: voice_profile_revisions_profile_idx (voice_profile_id, revision_number DESC).
• State machine: immutable.
• RLS: org read; write requires voice.catalog.manage.
• Idempotency: UNIQUE (voice_profile_id, revision_number); UNIQUE (voice_profile_id, sha256).
• Verdict: NEW.

F.12 voice_licenses

• Purpose: the rights record that gates reuse of a voice asset across projects, locales, media and time.
• Grain: one row per licence grant for a voice profile.
• Columns: id uuid PK; organization_id uuid NOT NULL; voice_profile_id uuid NOT NULL → voice_profiles(id) ON DELETE RESTRICT; license_type text NOT NULL; licensor text; territories text[] NOT NULL DEFAULT '{}'; media text[] NOT NULL DEFAULT '{}'; languages text[] NOT NULL DEFAULT '{}'; exclusive boolean NOT NULL DEFAULT false; term_start date NOT NULL; term_end date; max_concurrency integer; derivative_rights boolean NOT NULL DEFAULT false; consent_evidence_uri text; consent_recorded_at timestamptz; status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','active','expired','revoked', 'takedown')); revoked_at, revoked_reason text; created_at, updated_at; row_version.
• Unique: UNIQUE (voice_profile_id, term_start).
• Indexes: voice_licenses_profile_idx (voice_profile_id, status); voice_licenses_expiry_idx (organization_id, term_end) WHERE status = 'active' — supports the "assets expiring before release" dashboard.
• State machine: pending → active → expired | revoked | takedown.
• RLS: org read for voice_manager; write requires voice.catalog.manage. A cast may read only status='active' rows covering the requested locale/territory/media/date — enforced by app.voice_licence_covers(voice_profile_id, locale, territory, media, at_date).
• Classification: P + legal. consent_evidence_uri points to object storage, never inline.
• Idempotency: upsert on (voice_profile_id, term_start).
• Verdict: NEW. Backfilled as status='pending' with rights_status='unknown' — UmbraAudio holds no licence evidence today, so every legacy voice is non-reusable for paid/public release until a voice manager approves it. This is a deliberate, conservative default.

F.13 voice_casts

• Purpose: assignment of a speaker to a voice profile for a project/locale/revision, and the snapshot input to an audio run.
• Grain: one row per (project, locale, speaker, source revision, revision_number).
• Columns: id uuid PK; organization_id, project_id NOT NULL; project_locale_id uuid; speaker_id uuid NOT NULL → speakers(id) ON DELETE RESTRICT; voice_profile_id uuid NOT NULL → voice_profiles(id) ON DELETE RESTRICT; source_revision_id uuid NOT NULL; revision_number integer NOT NULL DEFAULT 1; voice_licence_id uuid → voice_licenses(id) ON DELETE RESTRICT; cast_by_user_id uuid; cast_at timestamptz NOT NULL DEFAULT now(); row_version.
• Unique: UNIQUE (project_id, speaker_id, source_revision_id, revision_number).
• Indexes: voice_casts_locale_idx (project_locale_id); voice_casts_profile_idx (voice_profile_id) — reverse-lookup "where is this voice used".
• State machine: proposed → approved → superseded.
• RLS: tenant read; write requires project.voice.cast. Deny-by-default trigger: a cast whose voice_licence_id is not active and covering the locale/date is rejected.
• Classification: P.
• Idempotency: upsert on the 4-column unique; a change increments revision_number and supersedes the prior row (never updates in place).
• Verdict: NEW. Replaces voice_assignments.json and characters.voice_description.

F.14 project_locales

• Purpose: the target-language lane for a project. No translations_de tables.
• Grain: one row per (project, BCP-47 locale).
• Columns: id uuid PK; organization_id, project_id NOT NULL; locale_tag text NOT NULL; is_source boolean NOT NULL DEFAULT false; translation_status text NOT NULL DEFAULT 'not_started'; proofread_status text NOT NULL DEFAULT 'not_started'; cast_status text NOT NULL DEFAULT 'not_started'; audio_status text NOT NULL DEFAULT 'not_started'; qa_status text NOT NULL DEFAULT 'pending'; release_state text NOT NULL DEFAULT 'draft'; current_ translation_run_id uuid; target_voice_profile_set_hash char(64); row_version integer NOT NULL DEFAULT 1; created_at, updated_at.
• Unique: UNIQUE (project_id, locale_tag).
• Indexes: project_locales_project_idx (project_id).
• State machine: per-column monotone ladders (not_started → in_progress → in_review → approved), release_state: draft → approved → released | withdrawn.
• RLS: tenant read; write requires the matching role for the column (project.translation.edit, project.translation.review, project.voice.cast, project.audio.generate, project.release.approve).
• Idempotency: upsert on (project_id, locale_tag).
• Verdict: NEW. This is the one row per language that replaces all per-language tables.

F.15 translation_runs

• Purpose: an immutable execution record for translating a source revision into a locale.
• Grain: one row per translation execution.
• Columns: id uuid PK; organization_id, project_id NOT NULL; project_locale_id uuid NOT NULL; source_revision_id uuid NOT NULL; run_kind text NOT NULL DEFAULT 'draft' (draft, revise, verify); engine text NOT NULL; model_id text NOT NULL; prompt_version text NOT NULL; input_sha256 char(64) NOT NULL; status text NOT NULL DEFAULT 'running' (same enum as analysis_runs); started_by_service_id uuid; started_at, finished_at; metrics jsonb NOT NULL DEFAULT '{}'; error text; superseded_by_run_id uuid.
• Unique: UNIQUE (project_locale_id, run_kind, source_revision_id, input_sha256, prompt_version, model_id).
• Indexes: translation_runs_locale_idx (project_locale_id, started_at DESC).
• State machine: running → succeeded | failed | cancelled; succeeded → superseded.
• RLS: tenant read; write: project.translation.edit (human) or worker under lease.
• Idempotency: the wide UNIQUE; a retry with identical inputs returns the existing row.
• Verdict: NEW.

F.16 translation_units

• Purpose: an ordered target render span for one source unit in one locale.
• Grain: one row per (translation run, source unit, ordinal).
• Columns: id uuid PK; organization_id, project_id NOT NULL; translation_run_id uuid NOT NULL → translation_runs(id) ON DELETE CASCADE; source_unit_id uuid NOT NULL → source_units(id) ON DELETE RESTRICT; source_unit_key char(64) NOT NULL (denormalised so cross-language joins never need a chain); ordinal integer NOT NULL; target_text text NOT NULL; target_sha256 char(64) NOT NULL; status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','submitted','proofread','approved','rejected')); proofreader_user_id uuid; reviewed_at timestamptz; review_notes text; row_version integer NOT NULL DEFAULT 1; created_at, updated_at.
• Unique: UNIQUE (translation_run_id, ordinal); UNIQUE (translation_run_id, source_unit_id).
• Indexes: translation_units_run_idx (translation_run_id, ordinal); translation_units_source_idx (project_id, source_unit_key) — the cross-language join path.
• State machine: draft → submitted → proofread → approved; → rejected from submitted/proofread. Enforced by a trigger on status transitions.
• RLS: tenant read; draft→submitted requires project.translation.edit; → approved requires project.translation.review and reviewer ≠ author (four-eyes, implemented as a trigger comparing row_version-tracked author field).
• Classification: C.
• Idempotency: upsert; row_version gives two reviewers a conflict instead of a silent overwrite.
• Verdict: NEW.

F.17 audio_runs

• Purpose: one generation run over a project (or a bounded chapter range within it).
• Grain: one row per generation execution.
• Columns: id uuid PK; organization_id, project_id NOT NULL; project_locale_id uuid NULL (null = source language); source_revision_id uuid NOT NULL; translation_run_id uuid NULL; run_scope text NOT NULL DEFAULT 'full' CHECK (run_scope IN ('full','chapter','unit', 'range')); scope_from integer, scope_to integer; cast_snapshot_sha256 char(64) NOT NULL; engine text NOT NULL; model_id text NOT NULL; params jsonb NOT NULL DEFAULT '{}'; status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','leased','running', 'succeeded','partial','failed','cancelled')); units_total integer NOT NULL DEFAULT 0; units_generated integer NOT NULL DEFAULT 0; units_skipped integer NOT NULL DEFAULT 0; units_failed integer NOT NULL DEFAULT 0; started_by_service_id uuid; started_at, finished_at; error text; row_version integer NOT NULL DEFAULT 1.
• Unique: UNIQUE (project_id, source_revision_id, cast_snapshot_sha256, input_sha256, engine, model_id, run_scope, scope_from, scope_to) where input_sha256 is a new column holding the sha256 of the ordered (unit_key, text_sha256, voice_profile_id, instruct, fx_version) tuple list. This is the enterprise form of the marker-based idempotency.
• Indexes: audio_runs_project_idx (project_id, started_at DESC); audio_runs_status_idx (status) WHERE status IN ('queued','leased','running').
• State machine: queued → leased → running → succeeded | partial | failed | cancelled; leased → queued on lease expiry (reaper).
• RLS: tenant read; write: worker under lease, or human with project.audio.generate.
• Classification: C.
• Idempotency: the wide UNIQUE — a retried identical run is a no-op, matching today's marker-skip behaviour exactly. Concurrency: FOR UPDATE SKIP LOCKED on claim.
• Verdict: NEW.

F.18 audio_run_casts

• Purpose: the frozen cast snapshot an audio run actually used, so a release is reproducible after casting changes.
• Grain: one row per (audio run, speaker).
• Columns: id uuid PK; organization_id, project_id NOT NULL; audio_run_id uuid NOT NULL → audio_runs(id) ON DELETE CASCADE; speaker_id uuid NOT NULL; voice_profile_id uuid NOT NULL; voice_licence_id uuid; voice_reference_sha256 char(64); cast_revision_number integer NOT NULL; created_at.
• Unique: UNIQUE (audio_run_id, speaker_id).
• Indexes: audio_run_casts_run_idx (audio_run_id); audio_run_casts_profile_idx (organization_id, voice_profile_id).
• State machine: immutable.
• RLS: tenant read; write only by the run's lease holder.
• Idempotency: ON CONFLICT DO NOTHING; the run-level cast_snapshot_sha256 is the digest of the ordered rows.
• Verdict: NEW.

F.19 artifacts

• Purpose: the registry of every produced file. Metadata only.
• Grain: one row per immutable produced artifact version.
• Columns: id uuid PK; organization_id, project_id NOT NULL; project_locale_id uuid; artifact_kind text NOT NULL (source_clip, chapter_master, book_master, m4b, mp3_track, wav_master, cover, epub, pdf, qa_report, manifest, voice_reference, pronunciation_clip); logical_key text NOT NULL (e.g. ch11/utt_0042, ar_package/Deal_Hunter_01_Salvage.mp3); object_key text NOT NULL; sha256 char(64) NOT NULL; byte_size bigint NOT NULL CHECK (byte_size >= 0); media_type text NOT NULL; duration_ms integer; sample_rate integer; channels integer; loudness_lufs numeric(6,2); peak_dbfs numeric(6,2); source_revision_id uuid; translation_run_id uuid; audio_run_id uuid; supersedes_artifact_id uuid → artifacts(id) ON DELETE SET NULL; superseded_by_ artifact_id uuid; retention_class text NOT NULL DEFAULT 'standard'; legal_hold boolean NOT NULL DEFAULT false; availability text NOT NULL DEFAULT 'available' CHECK (availability IN ('pending','quarantined','available','withdrawn')); produced_by_service_id uuid; created_at; row_version integer NOT NULL DEFAULT 1; legacy_object_key text; legacy_marker jsonb.
• Unique: UNIQUE (project_id, object_key); UNIQUE (project_id, sha256, artifact_kind).
• Indexes: artifacts_project_idx (project_id, artifact_kind, created_at DESC); artifacts_run_idx (audio_run_id); artifacts_source_unit_lookups via artifact_links.
• State machine: pending → quarantined | available; available → withdrawn. Never deleted while legal_hold or while referenced by a release manifest.
• RLS: tenant read; availability='available' additionally requires membership; withdrawn requires project.release.approve; write via the artifact service only, and download via short-lived signed URLs audited in audit_events.
• Classification: metadata C/P; bytes never in PostgreSQL.
• Idempotency: content-addressed by (project_id, sha256, artifact_kind); re-producing identical bytes returns the existing row and increments a reproduced_count.
• Verdict: NEW. legacy_object_key and legacy_marker are how the existing on-disk files and their *.wav.txt markers are registered without moving a byte (§I).

F.20 artifact_links

• Purpose: the lineage DAG edge table.
• Grain: one row per (parent artifact, child artifact, relation).
• Columns: id bigint GENERATED ALWAYS AS IDENTITY PK; organization_id uuid NOT NULL; project_id uuid NOT NULL; parent_artifact_id uuid NOT NULL ON DELETE RESTRICT; child_ artifact_id uuid NOT NULL ON DELETE RESTRICT; relation text NOT NULL (derived_from, stitched_from, encoded_from, supersedes, voice_reference_for, translation_of); created_at.
• Unique: UNIQUE (parent_artifact_id, child_artifact_id, relation); CHECK (parent_artifact_id <> child_artifact_id).
• Indexes: artifact_links_child_idx (child_artifact_id); artifact_links_parent_idx (parent_artifact_id).
• State machine: append-only.
• RLS: tenant read; write by the producing service.
• Idempotency: the 3-column UNIQUE.
• Verdict: NEW.

F.21 jobs

• Purpose: the durable work queue that does not exist today.
• Grain: one row per unit of schedulable work.
• Columns: id uuid PK; organization_id, project_id NOT NULL; project_locale_id uuid; job_type text NOT NULL (source_import, parse, semantic_analysis, identity_review, translation_draft, translation_verify, pronunciation_resolve, voice_cast, voice_sample_generate, audio_render, audio_qa, ebook_export, pdf_export, audio_assembly, release_package, artifact_derivative); payload jsonb NOT NULL; idempotency_key text NOT NULL; priority smallint NOT NULL DEFAULT 100; deadline_at timestamptz; required_pool text; status text NOT NULL DEFAULT 'queued' (queued,leased,running,succeeded,failed,cancelled,dead_letter); attempt_count integer NOT NULL DEFAULT 0; max_attempts integer NOT NULL DEFAULT 3; next_attempt_at timestamptz NOT NULL DEFAULT now(); last_error text; progress jsonb NOT NULL DEFAULT '{}'; output_job_id uuid; created_at, updated_at; row_version integer NOT NULL DEFAULT 1; created_by_user_id uuid; created_by_service_id uuid.
• Unique: UNIQUE (project_id, job_type, idempotency_key). The idempotency key is content-derived, never a filename — this is the direct answer to spec 19 §12.2.
• Indexes: jobs_claim_idx (status, next_attempt_at, priority) WHERE status IN ('queued', 'leased'); jobs_project_idx (project_id, created_at DESC).
• State machine: queued → leased → running → succeeded | failed; failed → queued while attempt_count < max_attempts; failed → dead_letter when exhausted; any → cancelled.
• RLS: tenant read; human write requires the permission mapped to job_type; worker write/update permitted only for a row whose job_leases entry names the caller's service identity and is unexpired.
• Classification: payload may contain text → C.
• Idempotency/concurrency: claim with UPDATE jobs SET status='leased' WHERE id = (SELECT id FROM jobs WHERE ... FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING *.
• Verdict: NEW.

F.22 job_attempts

• Purpose: per-attempt history and the structured log pointer.
• Grain: one row per attempt.
• Columns: id bigint GENERATED ALWAYS AS IDENTITY PK; organization_id, project_id NOT NULL; job_id uuid NOT NULL ON DELETE CASCADE; attempt_number integer NOT NULL; service_identity_id uuid NOT NULL; lease_id uuid; started_at, finished_at; outcome text NOT NULL CHECK (outcome IN ('succeeded','failed','cancelled','expired')); error_class text; error_message text; log_uri text; metrics jsonb NOT NULL DEFAULT '{}'.
• Unique: UNIQUE (job_id, attempt_number).
• Indexes: job_attempts_job_idx (job_id, attempt_number DESC).
• State machine: append-only.
• RLS: tenant read; insert only by the lease holder.
• Idempotency: the UNIQUE; a retried attempt increments the number.
• Verdict: NEW.

F.23 job_leases

• Purpose: worker ownership with heartbeat and expiry — the mechanism that lets RLS scope a worker to a project without impersonation.
• Grain: one row per active lease.
• Columns: id uuid PK; organization_id, project_id NOT NULL; job_id uuid NOT NULL ON DELETE CASCADE; service_identity_id uuid NOT NULL; lease_token_hash char(64) NOT NULL; acquired_at timestamptz NOT NULL DEFAULT now(); heartbeat_at timestamptz NOT NULL DEFAULT now(); expires_at timestamptz NOT NULL; released_at timestamptz; worker_fingerprint text.
• Unique: UNIQUE (job_id) WHERE released_at IS NULL — one live lease per job; UNIQUE (lease_token_hash).
• Indexes: job_leases_expiry_idx (expires_at) WHERE released_at IS NULL (reaper); job_leases_service_idx (service_identity_id, expires_at).
• State machine: active → released | expired. A reaper moves expired leases back to queued and increments attempt_count.
• RLS: insert/renew by the worker for its own service identity; a worker may read only its own rows.
• Idempotency: ON CONFLICT (job_id) WHERE released_at IS NULL DO UPDATE SET heartbeat_at, expires_at — heartbeat is idempotent and repeatable.
• Verdict: NEW. This table plus service_identities is the fix for BLOCKER B1.

F.24 service_identities

• Purpose: a non-human principal. A worker or service is never a users row.
• Grain: one row per service principal.
• Columns: id uuid PK; organization_id uuid NULL (null = platform-wide); name citext NOT NULL; kind text NOT NULL CHECK (kind IN ('audio_worker','analysis_worker', 'export_worker','migration_tool','readonly_report')); status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','disabled')); description text; created_at, updated_at.
• Unique: UNIQUE (name).
• Indexes: service_identities_org_idx (organization_id, status).
• State machine: active ↔ disabled.
• RLS: readable by platform_admin and organization_admin; not readable by ordinary users. Writes by platform.manage only.
• Classification: P (operational identity).
• Idempotency: ON CONFLICT (name) DO NOTHING.
• Verdict: NEW. Required by BLOCKER B1.

F.25 legacy_id_map

• Purpose: the permanent, auditable bridge from every legacy identifier to its canonical enterprise id. This is the mechanism that makes rollback and re-sync safe forever.
• Grain: one row per (legacy system, entity kind, legacy key).
• Columns: id uuid PK; organization_id uuid NOT NULL; project_id uuid NULL; legacy_system text NOT NULL (umbra_sqlite, umbra_json, umbra_registry, umbra_file); legacy_source_ref text NOT NULL (e.g. project.db:utterances:4711, ch11/utt_0042); entity_kind text NOT NULL (project, chapter, source_unit, speaker, voice_profile, artifact, job); entity_id uuid NULL; legacy_key text NOT NULL; source_sha256 char(64); mapping_state text NOT NULL DEFAULT 'mapped' CHECK (mapping_state IN ('mapped','unmapped','ambiguous','rejected')); confidence numeric(4,3); note text; mapped_at timestamptz NOT NULL DEFAULT now(); mapped_by_service_id uuid`.
• Unique: UNIQUE (legacy_system, legacy_source_ref, legacy_key).
• Indexes: legacy_id_map_entity_idx (entity_kind, entity_id); legacy_id_map_unmapped_idx (project_id) WHERE mapping_state <> 'mapped'.
• State machine: unmapped → mapped | ambiguous | rejected.
• RLS: tenant read; write by migration tooling only.
• Classification: C.
• Idempotency: the UNIQUE; re-running the mapper is a no-op.
• Verdict: NEW. This is where unmappable historical records go — mapping_state = unmapped/ambiguous/rejected is the answer to "historical local records that cannot be mapped" (§I.7). Nothing is dropped.

F.26 Extensions to existing 001_control_plane tables

Table	Change	Why
projects	ADD work_id uuid → works(id) ON DELETE RESTRICT	§E item 1; a project produces one work
projects	ADD source_locale text NOT NULL DEFAULT 'en-US'	project_locales.is_source needs an anchor
projects	ADD row_version integer NOT NULL DEFAULT 1	optimistic concurrency
projects	ADD migration_state text NOT NULL DEFAULT 'legacy' CHECK (migration_state IN ('legacy','mirroring','dual_write','enterprise_authoritative','retired'))	the per-project cutover flag (§I.5)
projects	ADD legacy_project_path text	the old /Volumes/.../<slug> directory, so both systems can find the same book
users	ADD password_disabled_at timestamptz	defence-in-depth: makes "no second password store" explicit and auditable
sessions	ADD mfa_verified_at timestamptz, ADD idle_deadline_at timestamptz	B4 fix; idle timeout
audit_events	ADD actor_service_identity_id uuid → service_identities(id) ON DELETE SET NULL	B1 fix — worker attribution
audit_events	ADD principal_kind text NOT NULL DEFAULT 'user' CHECK (principal_kind IN ('user','service','system'))	explicit principal type
audit_events	ADD actor_subject_snapshot text	immutability of identity behind an audit row when users.email later changes (§J.5)
audit_events	ADD session_id uuid → sessions(id) ON DELETE SET NULL	ties an authorization decision to a session
permissions	ADD rows (§D.5)	B3 fix
roles	ADD audio_engineer (project), producer (project) if required	optional; decide in M

Deliberately NOT changed: audit_events.project_id and audit_events.organization_id get no foreign keys. Audit rows must survive the deletion of the project and organization they describe; an FK would either block deletion or cascade the audit trail away. The absence of those FKs is a feature and should be documented as intentional so a future reviewer does not "fix" it.

---

G. Shared-record and cross-language invariants

Each is stated as a testable invariant with its enforcement point.

1. One source ebook has one stable source identity. Invariant: for a given (organization_id, sha256) there is at most one source_documents row. Enforced: UNIQUE (project_id, sha256) + content-addressed ingest.
2. One canonical project contains the audiobook and the translation work. Invariant: every source_units, speakers, artifacts, jobs row for a book resolves to exactly one projects.id, and that id is shared by UmbraAudio and UmbraTrans. Enforced: NOT NULL project_id + composite FK + composite RLS.
3. Analysis has immutable versions. Invariant: no UPDATE on a source_revisions row with locked = true, and no UPDATE/DELETE on analysis_runs/analysis_snapshots in state succeeded. Enforced: BEFORE UPDATE triggers; append-only trigger on audit_events already exists as a precedent (001:356-359).
4. Speaker identity is stable across languages. Invariant: speakers.canonical_name and its uuid are the only cross-language speaker reference; translation_units.source_unit_key and speaker_id carry that identity, never a translated display name. Enforced: no translated name is ever written to speakers.
5. A voice profile is reusable across translations only when language, accent, consent and licensing allow. Invariant: a row in voice_casts may only be created when voice_licences.status='active' and the licence covers the target locale_tag, the requested territory, the media, and the release date, and the profile's locale_tag/accent are compatible. Enforced: deny-by-default trigger on voice_casts + app.voice_licence_covers(...).
6. Every translation references a source book, a project and a specific analysis version. Invariant: translation_runs.source_revision_id IS NOT NULL and its input_sha256 matches the analysis_runs input for the same revision. Enforced: NOT NULL FK + a reconciliation check in the nightly validator.
7. Every audiobook references a project, a translation when applicable, a voice-cast snapshot and a generation run. Invariant: every artifacts row with artifact_kind IN ('m4b','mp3_track','book_master','chapter_master') has audio_run_id IS NOT NULL and cast_snapshot_sha256 resolvable in audio_run_casts. Enforced: NOT NULL FK + CHECK + the nightly lineage validator.
8. Translated ebooks and audiobooks never become independent duplicate source records. Invariant: a translated artifact's lineage path to a source_documents row always exists and is acyclic; no translated artifact is ever registered as a source_documents row. Enforced: artifact_links acyclicity check (recursive CTE, nightly) + application rule.
9. The audio system keeps creating audiobooks before, during and after cutover. Invariant: the audio worker's critical path never requires a successful enterprise write. Enforced: degraded-mode lease (§I.6); PG write failure is logged and reconciled, never fatal. This is the invariant that makes the whole plan safe, and it must be tested by stopping PostgreSQL mid-run (§L, test 14).
10. Large media live in object storage; PostgreSQL holds only object keys, checksums, media metadata and lifecycle state. Invariant: no table in this design has a bytea/blob column. Enforced: schema review + a test asserting no bytea column exists in schema public.
11. Every write is idempotent and safe to retry. Invariant: every table has a natural unique constraint; re-executing any write with identical inputs changes nothing. Enforced: the UNIQUE constraints in §F; replay tests in §L.
12. Concurrent workers cannot silently overwrite one another. Invariant: a job has at most one live lease; metadata edits use row_version. Enforced: partial UNIQUE on job_leases, FOR UPDATE SKIP LOCKED claims, row_version on every mutable table.
13. Service identities are not represented by arbitrary client-supplied user ids. Invariant: audit_events.principal_kind='service' rows have actor_service_identity_id IS NOT NULL and actor_user_id IS NULL. Enforced: CHECK constraint + the GUC-derivation rule in §J.1 (the service identity comes from the connection, never from a payload).
14. The future translation UI can discover authorized projects and audiobooks without a separate account system. Invariant: the UI's project list is exactly SELECT id FROM projects WHERE app.can_access_project(id) — the same predicate the audio system uses. Enforced: both clients use one app.authorized_projects() function.
15. The existing staleness rule survives. Invariant: an artifact whose legacy_marker.fx_version is set is stale for a run that requests no fx_version (generation/staleness.py:98). Any enterprise re-implementation of staleness must preserve this, because the two current generation drivers differ on it (ChapterBuilder does not pass instruct/fx_version; scripts/generate_all_audio.py does). Enforced: documented and covered by a regression test.

---

H. Schema delta and migration order

No SQL is emitted. Each row is a reviewed change with its own rollback and verification.

#	Class	Change	Reason	Depends on	Rollout	Rollback	Volume	Lock risk	Verification	Human approval
H1	NEW SERVICE ROLE	service_identities principal + app.current_service_identity_id()	B1 blocker	—	additive	drop function	0	none	§L-6	Yes
H2	ADD TABLE	service_identities	B1	H1	additive	drop table	~5 rows	none	§L-6	Yes
H3	ADD TABLE	works, source_documents, source_revisions, source_chapters	§E 1	—	additive	drop	10s–100s	none	§L-8	Yes
H4	ADD TABLE	legacy_id_map	§I stable-ID mapping	H3	additive	drop	10⁵ rows	none	§L-8	Yes
H5	ALTER TABLE	projects: work_id, source_locale, row_version, migration_state, legacy_project_path	cutover flag + work link	H3	nullable columns, then backfill, then NOT NULL on source_locale	drop columns	~10 rows	brief ACCESS EXCLUSIVE; use NOT VALID patterns, lock_timeout	§L-5	Yes
H6	ADD TABLE	speakers, speaker_aliases	5-store identity fix	H3	additive	drop	~10²	none	§L-9	Yes
H7	ADD TABLE	source_units (+ generated source_unit_key)	§E.1 stable identity	H3, H6	additive	drop	10⁴–10⁶	none	§L-9	Yes
H8	ADD TABLE	analysis_runs, analysis_snapshots	immutable analysis	H3	additive	drop	10²	none	§L-10	Yes
H9	ADD TABLE	voice_profiles, voice_profile_revisions, voice_licenses, voice_casts	voice reuse gating	H6	additive	drop	10²	none	§L-11	Yes
H10	ADD TABLE	project_locales, translation_runs, translation_units	locale lanes	H3	additive	drop	0 initially	none	§L-12	Yes
H11	ADD TABLE	audio_runs, audio_run_casts	generation runs	H7, H9	additive	drop	10²–10³	none	§L-13	Yes
H12	ADD TABLE	artifacts, artifact_links	artifact registry + lineage	H11	additive	drop	10⁴–10⁶	none	§L-13	Yes
H13	ADD TABLE	jobs, job_attempts, job_leases	durable queue	H1	additive	drop	10³–10⁵	none	§L-14	Yes
H14	ADD CONSTRAINT	artifacts: CHECK requiring audio_run_id for audio kinds; artifact_links self-edge CHECK	G7	H12	NOT VALID then VALIDATE	drop constraint	large	none if NOT VALID	§L-13	Yes
H15	ALTER TABLE	audit_events: actor_service_identity_id, principal_kind, actor_subject_snapshot, session_id	worker attribution	H1, H2	nullable add	drop columns	large table; add nullable only	brief	§L-7	Yes
H16	ENABLE/PROVE RLS	RLS policies on all 20 new tables, tenant read/write, worker-lease branch	§J	H2–H13	create policies, then ENABLE, then prove	drop policies	—	none	§L-1,2,3	Yes
H17	ALTER TABLE	sessions: mfa_verified_at, idle_deadline_at	B4	—	nullable add	drop columns	small	brief	§L-4	Yes
H18	ADD CONSTRAINT	users.password_disabled_at	no second password store, explicit	—	nullable add	drop column	small	brief	§L-4	Yes
H19	BACKFILL	Insert permissions rows + role_permissions grants from §D.5	B3	—	idempotent upserts	delete the added rows	~15 rows	none	§L-5	Yes
H20	ADD CONSTRAINT	users.status='active' + organizations.status='active' enforcement inside app.resolve_session	B5	H17	replace function	revert function	—	none	§L-4	Yes
H21	ADD FUNCTION	app.revoke_user_sessions(uuid), app.authorized_projects(), app.can(action, project_id)	D.3, D.5, G14	H1	create	drop	—	none	§L-2,4	Yes
H22	ENABLE/PROVE RLS	Replace projects_select, organizations_select, user_roles_select policies to (a) admit project-only members to their org, (b) admit voice_manager to project metadata, (c) scope the worker	§J.3, §J.4	H1, H21	CREATE OR REPLACE in one transaction	restore prior policy text	small	brief	§L-2,3	Yes
H23	GRANT/REVOKE	ALTER DEFAULT PRIVILEGES — revoke EXECUTE ON FUNCTIONS from PUBLIC in schema app; grant table DML to umbracontrol_api; grant lease-scoped DML to umbracontrol_worker	B7 — new app functions are world-executable by default today	H1	per-migrator ALTER DEFAULT PRIVILEGES	revert	—	none	§L-15	Yes
H24	GRANT/REVOKE	Re-own app schema + all tables from postgres to umbracontrol_owner (NOLOGIN); migrations connect as umbracontrol_migrator then SET ROLE umbracontrol_owner; never FORCE ROW LEVEL SECURITY	§J.2 superuser dependency	H16	maintenance window; REASSIGN OWNED in one transaction	REASSIGN OWNED ... TO postgres	all	brief; blocks writes	§L-16,17	Yes — highest risk item
H25	BACKFILL	Project → projects (with legacy_project_path); manuscript → source_documents; speakers → speakers/speaker_aliases; utterances → source_units; voice refs → voice_profiles (+voice_licenses pending); artifacts → artifacts (+legacy_object_key, legacy_marker)	§I.4	H4–H12	resumable, batched, idempotent	delete by legacy_id_map batch	10⁵–10⁶	none (batched)	§L-8,9,11,13	Yes
H26	ADD INDEX	All indexes in §F, created CONCURRENTLY	query paths	tables exist	CREATE INDEX CONCURRENTLY, one at a time	DROP INDEX	large	none	EXPLAIN checks	No
H27	ADD CONSTRAINT	Triggers: immutable revisions, status state machines, four-eyes approval, cast licence gate, row_version bump	G3, G5, G12	tables exist	create, then backfill-violation audit	drop triggers	—	none	§L-13,18	Yes
H28	NEW SERVICE ROLE	Optional audio_engineer / producer roles	§M open question	H19	seed upsert	delete	0	none	§L-5	Yes
H29	NO CHANGE	organizations, roles, role_permissions, invitations, mfa_methods, bootstrap_tokens, schema_migrations	no delta required	—	—	—	—	—	—	No
H30	NO CHANGE	Every legacy SQLite table, JSON file, WAV, MP3, M4B, marker	§G invariant 9	—	—	—	—	—	—	No

Dependency chain: H1 → H2 → H3 → H4 → {H5, H6, H7, H8} → {H9, H10, H11} → H12 → H13; H16 after all tables; H22–H24 last (they change the enforcement layer and must be proven against a fully-populated schema).

---

I. Gradual cutover, reconciliation, and rollback plan

I.1 The repository/adapter boundary (Phase P1 — no behaviour change)

Introduce a single seam in the audio codebase, behind which all state access lives:

umbraaudio/persistence/
    ProjectRepository      (protocol)
    SqliteProjectRepository   -> today's project.db + JSON + files  [DEFAULT]
    ControlPlaneProjectRepository -> enterprise PostgreSQL
    ArtifactStore
    LocalArtifactStore        -> today's filesystem tree  [DEFAULT]
    ObjectArtifactStore       -> enterprise object storage

Rule: the default implementation is today's code, byte-for-byte. Phase P1 ships with the enterprise implementation present but unused behind a per-project flag. Nothing about audiobook production changes. This is what makes the rest of the plan reversible.

I.2 Read-only mirror / shadow-read phase (P2)

• H25 backfill populates the enterprise DB from SQLite/files, recording every mapping in legacy_id_map.
• The audio system keeps reading and writing only SQLite/files. Zero risk.
• A nightly shadow-read job runs the same queries through both repositories and records discrepancies in a reconciliation table. Discrepancies are logged, never fatal.

I.3 Stable-ID mapping and reconciliation rules

Legacy identity	Canonical id	Rule
<base>/<slug>/ directory	projects.id	legacy_project_path; slug collisions resolved by suffix, never overwritten
SQLite project.id	projects.id	via legacy_id_map
chapters.id	source_chapters.id	within a source_revision
utterances.id / utterance_index	source_units.source_unit_key	§E.1; utterance_index alone is never used as an id
characters.id, speakers.json primary_name, voice_profiles.json key, voice_registry.character_name, library_speakers.primary_name	speakers.id	five sources merged; conflicts recorded, ambiguous ones left mapping_state='ambiguous'
voices/{slug}_ref.wav	voice_profiles.id	reference_object_key; voice_licenses seeded pending
chapter_audio/ch{N}/utt_{I}.wav	artifacts.id	legacy_object_key; legacy_marker = parsed *.wav.txt
ch{N}_full.wav / .mp3 / export/*.m4b / ar_package/*.mp3	artifacts.id	legacy_object_key
generation_status.json, ch{N}_build_status.json	audio_runs + jobs	seeded as succeeded/partial with legacy_marker
'user' in reviewed_by / resolved_by	the single canonical users.id	legacy_system='umbra_json', note legacy_actor='user'

Reconciliation rules (nightly): row counts per entity per project; sha256 of every registered artifact re-verified against the file; status-transition legality (no succeeded → running); lineage completeness (§G7); mapping_state <> 'mapped' counts; and speaker-name consistency between speakers.canonical_name and speaker_aliases.

I.4 Backfill plan

Order: projects → works/source documents/revisions → speakers + aliases → source units → analysis runs/snapshots → voice profiles + licences + casts → artifacts + links → audio runs → jobs. Batched at 1000 rows, resumable, idempotent, each batch one transaction, and each batch only advances migration_state after its reconciliation passes. peak memory and runtime are bounded by batching; nothing is held in memory.

I.5 Feature flag / per-project cutover

projects.migration_state ∈ legacy | mirroring | dual_write | enterprise_authoritative | retired. The audio worker reads it per request; a per-project override in local config allows an immediate per-project rollback without a database change. Cutover is per project, never global, and is reversible in one flag flip.

I.6 Dual-write / write-through period

For a project in dual_write:

• Primary path: unchanged. Audio generation writes the WAV, then the marker, then the SQLite row — exactly as today. It never waits on PostgreSQL.
• Secondary path: best-effort mirror of the small, high-value records: artifacts (object key + sha256 + legacy_marker), audio_runs progress, jobs, speakers, source_units, audit_events.
• On PostgreSQL failure: log, increment a mirror_deficit counter, and continue. The audio work is never blocked, never rolled back, never retried inline. A reconciler replays the deficit from the local state. This is the concrete meaning of G9.
• Degraded mode: if PostgreSQL is unreachable when a worker starts a run, the worker continues under a previously issued lease with a bounded validity (6 h), after which it must re-check in. Exceeding that window halts new runs, not in-flight rendering, and raises an operator alert. Production is degraded, never stopped, and never silently un-reconciled.
• TTS saturation note: the Mac serves one TTS model and must not co-reside with the LLM (AGENTS.md). The resource_pool column on jobs (tts_local, llm_local, gpu_remote, cpu) is what lets the queue express that constraint, replacing today's in-process lock.

I.7 Historical local records that cannot be mapped

They are never dropped. They are written to legacy_id_map with mapping_state ∈ ('unmapped','ambiguous','rejected'), a note, and the original legacy_source_ref. A monthly report lists them. Resolution is a human decision (merge / confirm / retire). The five-way speaker conflict (§I.3) is the largest expected population. This directly satisfies "a plan for historical local records that cannot be mapped".

I.8 The point at which the enterprise database becomes authoritative

Per project, when all of the following hold for three consecutive nightly reconciliations: counts match; every registered artifact's sha256 matches; zero mapping_state='unmapped' rows for that project; a full generation run has completed with PostgreSQL as the primary store (the enterprise_authoritative dry-run sub-phase); and a restore drill (§K) has passed. Then migration_state = 'enterprise_authoritative': UmbraAudio reads project state, speakers, units and artifacts from PostgreSQL; SQLite becomes a read-only mirror; the filesystem remains the audio working area and the *.wav.txt markers remain the idempotency record, so the TTS path is unchanged. Historical local records are retained read-only indefinitely.

I.9 Rollback plan

1. Flip migration_state back to dual_write (or mirroring). One statement.
2. The audio worker reverts to SQLite + files as primary. The worker is never stopped.
3. Enterprise rows written during the dual-write window are retained, not deleted; they are marked superseded_by_local so reconciliation knows they are not authoritative.
4. No source data is deleted at any point in this plan. Legacy SQLite, JSON, WAV, MP3, M4B and markers are all read-only after cutover and are never pruned until a separate, explicit retention decision (§M).
5. Rollback is safe at any point, because Phase P1's default repository is still the current code.

---

J. Security / RLS review

J.1 Can RLS be bypassed by client-controlled settings? — Yes. Documented residual risk.

app.current_user_id() is:

-- 001_control_plane.sql:218-224
SELECT NULLIF(current_setting('app.user_id', true), '')::uuid

app.user_id is a custom placeholder GUC, and any role may SET it. There is no REVOKE SET ON PARAMETER for a GUC that does not exist in pg_settings, so this cannot be prevented in PostgreSQL. Consequences:

• Any holder of the umbracontrol_api or umbracontrol_worker credential can impersonate any user, including a platform_admin, and read everything that user's RLS permits.
• A non-UUID value raises 22P02, a minor robustness/DoS issue.

This is the standard pattern used by Supabase/PostgREST and it is acceptable only under compensating controls, all of which are mandatory:

1. The API/worker credential lives in a root-owned 0640 file readable only by the umbracontrol service group (the bootstrap script already creates /etc/umbracontrol/db-credentials.env with exactly these permissions — preserve that).
2. app.user_id / app.service_identity_id are set with SET LOCAL inside an explicit transaction, by trusted framework code only, derived from a verified session token.
3. No code path may ever read these GUCs from a request body, query parameter, cookie, header, or file. This is a code-review invariant with a lint rule.
4. The database is never exposed directly; only the API connects.
5. ALTER ROLE umbracontrol_api SET log_statement = 'none' is already applied by the bootstrap script's PGOPTIONS; statement logging of GUC changes is available for incident response.
6. A dedicated non-superuser owner (§H24) means a superuser is not in the data path.

Residual risk, stated plainly: leakage of the umbracontrol_api password is equivalent to full tenant bypass. That is why §L-16 tests credential handling and why the API must never be internet-exposed.

J.2 Superuser ownership of the RLS machinery — HIGH finding

Verified: infra/bootstrap_umbracontrol_database.sh:243 streams the schema through psql_as_postgres, i.e. as the postgres superuser. Therefore all 13 tables and all app.* functions are owned by a superuser, and the database itself is owned by umbracontrol_owner (NOLOGIN, createdb --owner at line 226) which owns nothing else.

Two consequences:

1. Superusers bypass RLS unconditionally. This is load-bearing, not incidental: the policy projects_select USING (app.can_access_project(id)) calls a function that itself does SELECT 1 FROM projects … (001:298-320). That is a self-referencing policy. It does not raise 42P17 infinite recursion only because the SECURITY DEFINER function runs as a superuser and therefore bypasses RLS on the inner query.
2. Therefore ALTER TABLE projects FORCE ROW LEVEL SECURITY would break the entire access model with 42P17. Equally, re-owning the app functions to a role that does not own the tables would break it. The only working configuration is: the app.* SECURITY DEFINER functions and the tables they read must share one owner, and RLS must not be FORCE'd on the mutually-referencing tables (organizations, projects, users, user_roles).

Required (H24): re-own the app schema and all tables to the dedicated NOLOGIN role umbracontrol_owner; run migrations as umbracontrol_migrator then SET ROLE umbracontrol_owner. This preserves the required semantics, removes the superuser from the data path, and makes the invariant explicit. Add the regression assertion in §L-17 so nobody later "improves" it with FORCE.

J.3 voice_manager cannot see any project — authorization gap

voice_manager is seeded at scope='organization' (001:373). projects_select requires can_access_project, which admits only platform admins, organization admins, and holders of a scope='project' role. An org-scoped voice_manager therefore matches zero projects and cannot perform their job. Fix (H22): a policy branch granting org-scoped non-admin roles visibility of project metadata only (id, name, slug, status) — implemented as a column-restricting view or a column_privilege arrangement, not a blanket row grant. This requires a human decision (§M Q3): least privilege vs. usability.

J.4 Project-only members cannot read their own organization — availability gap

organizations_select requires is_organization_member, which admits only org-scope roles named organization_admin or member (001:257-261). A user holding only translator on one project cannot SELECT their organization row, so the UI cannot render an org name. Under- permissive, not a hole. Fix (H22): also admit any active user_roles row for that organization at any scope.

J.5 Human session vs worker identity

• Human: sessions row; app.resolve_session(token_hash) → users.id; all authorisation derives from user_roles. sessions_self (001:526-529) confines a user to their own rows.
• Worker: service_identities row + job_leases row; app.current_service_identity_id(); authorisation derives from an unexpired lease, never from a user. A worker with no lease sees no project rows. audit_events.principal_kind distinguishes the two permanently.
• No worker path may set app.user_id. Enforced by code review + the fact that principal_kind='service' audit rows require actor_service_identity_id IS NOT NULL AND actor_user_id IS NULL.
• Audit integrity note: users.email is mutable and audit_events.actor_user_id has a FK with no ON DELETE action, so (a) a user with audit history cannot be deleted (FK violation) and (b) changing a user's email silently changes the identity behind historical audit rows. Fix: H15 actor_subject_snapshot freezes the subject at write time; and document that account deletion is intentionally blocked once audit history exists.

J.6 Pre-existing application-level defects (preconditions, not migration scope)

These must be fixed before the audio web UI is placed behind the enterprise identity layer, because the identity layer is only as strong as the endpoint it guards. Reported, not fixed here:

1. No authentication at all. Zero Depends, zero middleware, zero CSRF, zero security headers, zero TrustedHost (umbraaudio/web/). 129 routes, 65 mutating, 0 authorization checks. The only gate is the unsigned active_project cookie (routes.py:482-491).
2. The server binds 0.0.0.0:8420 over plain HTTP with the macOS firewall disabled and a LAN Caddy proxy in front. Today this is reachable by anyone on the LAN. Until auth lands, the audio UI must remain LAN-only behind an authenticating reverse proxy.
3. load_config accepts absolute paths verbatim (config.py:156-158), so the cookie is a path-selection primitive. active_project=/any/dir with /any/dir/.umbra/project.db grants full read/write. Verified.
4. The activate existence check is dead code (routes.py:525), because load_config pre-creates the directory (config.py:165).
5. Download traversal: routes.py:5049 uses str(file_path).startswith(str(export_dir)) — a string prefix, not path-component containment. A sibling directory such as export_secret/ satisfies the prefix and is served. Path.is_relative_to() is the correct primitive. The existence check at :5047 also runs before the containment check, giving a 404-vs-403 existence oracle.
6. Upload path traversal: routes.py:633, 640, 647, 652 use upload.filename raw, with no Path(...).name. A multipart part named ../../../evil.txt passes the suffix allow-list (.txt) and writes outside data_dir/uploads. Same pattern at routes.py:558, 572. (routes.py:6627 does it correctly — the fix pattern already exists in the codebase.)
7. Unauthenticated resource control: POST /api/models/lm/unload (routes.py:5814) unloads every LM Studio instance; POST /api/models/switch/{stage} unloads/loads GB-scale models.
8. Unauthenticated destructive operations: POST /api/pipeline/reset?step=discover (routes.py:4690) shutil.rmtrees the voices directory; POST /api/pipeline/run-all (routes.py:4711) wipes speakers.json, all utterances and all generated audio.
9. Zero download authorization. Of 12 file-serving endpoints, none checks a user; the two slug comparisons (routes.py:2604, 2991) are cache-buster guards, not authorization.
10. A second, CWD-relative project-resolution path exists in the speaker_designations template global (web/__init__.py:51): Path(slug) / ".umbra", with no load_config, no containment check and no project.db existence test.
11. Concurrent ChapterBuilder runs are unprotected (routes.py:5097-5110), so two clicks write the same utt_*.wav concurrently.
12. Unauthenticated outbound-request proxy: POST /api/pronunciations/{name}/lookup (routes.py:1847) fetches attacker-supplied strings from dictionaryapi.dev.
13. Destructive manuscript upload: POST /api/upload-manuscript with mode=new rmtree's voice and audio directories (routes.py:712-742) with no confirmation and no permission.

J.7 Service-to-service write authorisation

Writes are authorised by lease, not by role. A worker may UPDATE a jobs row only while it holds the unexpired job_leases row for that job, and the policy derives that from app.current_service_identity_id() — never from a parameter. Renewals are ON CONFLICT (job_id) WHERE released_at IS NULL DO UPDATE, so a heartbeat is idempotent. Audit inserts by a service require actor_service_identity_id to match the current service identity.

J.8 Can login state be forged or reused across organizations?

• Forged: no. A session requires a 256-bit token whose SHA-256 matches sessions.token_hash; there is no forgeable signed cookie.
• Reused across organizations: yes, by design, and this is correct for a human — a session is user-global, so one token is valid for every organization that user belongs to. The protection is that every request re-derives and re-authorises the organization/project context from the session's user id, and RLS admits only organizations for which that user has an active user_roles row. There is no "default organization" and no cross-org inheritance. The practical consequence to enforce: never cache an authorised project/organization in the session payload.

J.9 Secrets

001_control_plane.sql contains no secret material and neither does this document. bootstrap_tokens.token_hash, sessions.token_hash, mfa_methods.secret_reference and invitations.token_hash are all hash-or-reference columns, never plaintext. lease_token_hash char(64) (F.23) follows the same pattern. voice_licenses.consent_evidence_ uri points at object storage. The bootstrap admin's one-time token is root-only at /root/umbra-enterprise-bootstrap.token and, per the infra README, must never be pasted into chat or logs. Add to the contract: the API must never log a connection string, a token, a GUC value carrying a user id, or a query parameter that could contain one.

---

K. Backup and restore requirements

1. PostgreSQL PITR with WAL archiving; target RPO ≤ 5 min, RTO ≤ 60 min. Daily full backup retained 35 days, monthly retained 13 months.
2. Restore drill before cutover (G-K1): restore a full backup to a scratch instance, then verify: row counts per table match the source; every artifacts.sha256 matches its object in object storage; every project is reachable by an authorised user and only by authorised users; audit_events is intact and append-only; a source_revision, a speakers row, a voice_casts row and an artifacts lineage path survive and resolve end-to-end.
3. Object storage versioning enabled, with server-side checksum validation and immutability (or object-lock) on released artifacts. A DELETE must never be a filesystem unlink: deletion is a retention_class/tombstone transition.
4. Class R (voice reference audio, embeddings, voice prompts) backed up separately from project data, with its own access control and its own retention/consent policy, because it is biometric data.
5. Class C (manuscripts, translations, produced audio) retention tied to the underlying licence. When an author's licence expires, the derived audiobook and translation entitlements are withdrawn (artifacts.availability='withdrawn') — not silently deleted, so the audit trail and any accounting survive.
6. Legacy sources protected: the local project tree, ~/.umbra/voice_registry.db and all markers are included in the backup set for the whole transition and are read-only after cutover. A full UmbraAudio project export/re-import must preserve lineage (spec 19 §15.3).
7. The backup role is umbracontrol_backup (LOGIN, SELECT on all tables, no schema CREATE) — note it is granted SELECT ON ALL TABLES at 001:576, which is a point-in-time grant; new tables get no backup access until H23 sets default privileges. Backup coverage of new tables is a release gate.

---

L. Acceptance tests

#	Test	Pass condition
L-1	Two-organization isolation	A user in org A cannot SELECT any row of org B's projects, source_units, speakers, artifacts, jobs via any API route or direct query.
L-2	Authorized project discovery	app.authorized_projects() returns exactly the projects with an active membership; the UI project list equals that set; forging a project id in a path yields 404/403, never data.
L-3	Policy-only membership	A translator on project X can read project X and cannot read project Y in the same org; an org voice_manager sees project metadata but no manuscript text (per the J.3 decision).
L-4	Session lifecycle	Expiry, logout, "sign out everywhere", suspension and deactivated all stop resolution on the next request; a user with mfa_required=true cannot obtain a session without MFA asserted; revoked tokens never resolve.
L-5	Permission enforcement	Each action in §D.5 is denied for a role lacking the permission and allowed for a role holding it — including project.audio.generate and project.speakers.edit, which have no equivalent today.
L-6	Worker lease scoping	A worker holding a lease on project X can read/write X; the same worker on project Y (no lease) sees zero rows and every write raises 42501. A worker cannot read sessions, mfa_methods, or users.password-like columns.
L-7	Worker audit attribution	Every worker-initiated mutation produces an audit_events row with principal_kind='service', actor_service_identity_id set, actor_user_id NULL, and the correct project_id.
L-8	Backfill completeness	For every project: legacy project count = projects count; every legacy chapter/utterance has a legacy_id_map row; re-running the backfill changes zero rows.
L-9	Identity merge correctness	All five legacy speaker stores map to one speakers.id per canonical name; source_units.speaker_name values are all present in speakers.canonical_name ∪ speaker_aliases.alias; no utterance references a missing speaker.
L-10	Analysis immutability	An UPDATE on a locked source_revisions row, or on a succeeded analysis_runs row, raises.
L-11	Voice licence gating	A cast against a pending/expired/takedown licence, or one not covering the target locale/territory/media/date, is rejected; an existing render is not deleted and a project issue is raised instead.
L-12	Locale independence	Two locales on one project have fully independent translation/cast/audio/release state; no table is named for a language.
L-13	Lineage + idempotency	Every audio artifact resolves a complete acyclic path to a source_documents row through artifact_links; re-running an identical audio_runs payload creates no new row; two concurrent claimers produce exactly one lease.
L-14	Continuity under database outage	With PostgreSQL stopped mid-generation, audio production continues to completion; on restart, the mirror reconciler converges with zero lost artifacts; the 6 h lease window then halts new runs while in-flight rendering completes.
L-15	Default-privilege safety	A newly created function in schema app is not executable by PUBLIC; a newly created table is readable/writable by umbracontrol_api and scoped for the worker.
L-16	Credential handling	The database credential file is root-owned 0640; no secret appears in any log line, environment dump, report, or migration file; the API is not reachable without a session.
L-17	RLS invariant	relforcerowsecurity is false for organizations, projects, users, user_roles; app.* functions and the tables they read share one owner; no superuser owns any object in schema public or app.
L-18	Concurrency	Two reviewers editing the same translation_units row: the second receives a conflict (row_version), never a silent overwrite.
L-19	Unmapped history	Records that cannot be mapped appear in legacy_id_map with mapping_state set and are never dropped; the monthly report is non-empty and actionable.
L-20	Application regression	The three verified defects are fixed and tested: (a) GET /api/export/download/../export_secret/x.mp3 returns 403; (b) an upload named ../../../evil.txt cannot escape data_dir/uploads; (c) active_project=/tmp/evil is rejected.

---

M. Open questions and blockers

M.1 BLOCKERS — must be resolved before any implementation

ID	Blocker	Evidence	Resolution
B1	The worker role cannot read any project and cannot attribute an audit event. All RLS policies require app.current_user_id(); the worker sets none, so it matches zero rows. audit_events_insert requires actor_user_id = app.current_user_id(), so worker actions are anonymous.	001:471-473, 548-552	H1/H2: service_identities + app.current_service_identity_id() + lease-scoped policy branch + audit_events.actor_service_identity_id. Do not work around it by letting the worker set app.user_id.
B2	No permission exists for generating audio, editing speakers, casting voices, running analysis, or destroying project state. The prompt requires authorization checks for exactly these.	001:378-391	H19. Without it, the contract is unimplementable as written.
B3	mfa_required is stored but never enforced, and sessions has no mfa_verified_at. A user flagged mfa_required=true can hold a fully valid session.	001:30, 120-129	H17 + app.resolve_session.
B4	New functions in schema app are EXECUTE-able by PUBLIC by default. REVOKE ALL ON ALL FUNCTIONS at 001:580 was a one-time statement with no ALTER DEFAULT PRIVILEGES behind it. Every future migration that adds a SECURITY DEFINER helper silently publishes it.	001:580	H23, applied before any further migration.
B5	users.status and organizations.status are not enforced by any policy. Suspension does not deny access at the database layer.	001:28-29, 15-17	H20: enforce inside app.resolve_session, plus a documented decision that RLS is not the suspension gate.

M.2 HIGH findings (not blockers, must be scheduled)

• H-1: superuser ownership of all RLS machinery (§J.2) — H24.
• H-2: voice_manager cannot see any project (§J.3) — H22.
• H-3: project-only members cannot read their organization (§J.4) — H22.
• H-4: app.user_id impersonation by any credential holder (§J.1) — accepted residual risk with six mandatory compensating controls.
• H-5: no admin session revocation (§D.3) — H21.
• H-6: users.email mutability undermines historical audit identity (§J.5) — H15.
• H-7: umbracontrol_backup gets no SELECT on tables created after 001 until H23.
• H-8: the four divergent legacy DDL authorities (§C.3) mean the legacy schema cannot be trusted as a backfill source without per-database introspection.

M.3 DECISIONS requiring human adjudication

• DECISION-1 (highest). This document reverses specs/19-enterprise-control-plane.md §2.3 and §16 M4, which specify a one-way read-only import with UmbraAudio unchanged. The new instruction makes the enterprise DB authoritative for the audio system. Confirm the reversal; spec 19 should then be amended so the two documents do not contradict each other. Recommendation: adopt the new model, and amend spec 19 §2.3/§16 M4 to "the enterprise database is authoritative; UmbraAudio is a client, with the filesystem retained as the audio working area." Spec 19 §1's prohibition on control-plane tables inside local SQLite stands.
• DECISION-2. specs/18-UmbraTrans.md §4.2 forbids UmbraTrans from modifying UmbraAudio and specifies an online-backup snapshot protocol (§4.2.1). Under the new model, UmbraTrans reads the same authoritative database instead. Confirm that §4.2.1 is superseded — this simplifies spec 18 and removes the snapshot-consistency problem entirely.
• DECISION-3. Org-scoped voice_manager project visibility: metadata-only (recommended) or full membership required?
• DECISION-4. Should audio_engineer and producer roles be added, or is project_manager + audio_director sufficient?
• DECISION-5. Retention: how long are Class C artifacts and legacy SQLite kept after cutover? The plan assumes indefinite read-only retention.
• DECISION-6. Which volume actually hosts object storage, and is it on the same host as PostgreSQL? Object storage is a hard prerequisite for §G invariant 8; until it exists, artifacts stay on the local filesystem and the enterprise DB holds only object_key values that resolve locally.
• DECISION-7. Is the identity provider external OIDC, or is the bootstrap provider promoted to first-class? 001 defaults auth_provider='bootstrap', which is not a production identity mechanism.
• DECISION-8. Does the audio UI get retired in favour of the shared review UI, or do both ship? This determines whether the J.6 fixes are a prerequisite or parallel work.
• DECISION-9. Scope of phase 2 (deferred in §E): the UmbraTrans translation-design tables (semantic_packages, identity_registries, glossary_revisions, memory_revisions, alignment_links, target_render_spans) plus the commercial voice subsystem (voice_entitlements, voice_reservations, voice_rate_cards, voice_invoices).

M.4 Explicitly out of scope for this phase

Connecting to any database; any DDL, DML, migration, grant or role change; moving or rewriting any UmbraAudio file; stopping or pausing the audio worker; producing migration SQL; requesting or using any credential, token, key or connection string.

---

N. Machine-readable contract

contract:
  name: umbra-enterprise-database-integration
  version: 1.0.0-draft
  date: 2026-09-25
  phase: discovery_and_design
  status: awaiting_human_approval
  production_changes_made: false
  database_contacted: false
  migration_sql_emitted: false

baseline:
  schema_reference: infra/postgres/001_control_plane.sql
  schema_version: 001_control_plane
  sha256_verified_identical_copy_at: /Volumes/Dock/worstauthor/oldbox/postgres/001_control_plane.sql
  existing_tables:
    - schema_migrations
    - organizations
    - users
    - projects
    - roles
    - permissions
    - role_permissions
    - user_roles
    - invitations
    - sessions
    - mfa_methods
    - bootstrap_tokens
    - audit_events
  verified_absent:
    - no source/work/edition tables
    - no locale tables
    - no analysis tables
    - no speaker or voice tables
    - no license or entitlement tables
    - no translation tables
    - no audio run or artifact tables
    - no job, lease, attempt or idempotency tables
    - no service identity table
    - no project_members table
    - no password column on users
  existing_roles: [platform_admin, organization_admin, member, project_owner,
                   project_manager, translator, proofreader, audio_director,
                   voice_manager, release_manager, viewer]
  existing_permissions: [platform.manage, organization.manage, organization.users.manage,
                         organization.projects.manage, project.access.manage,
                         project.source.edit, project.translation.edit,
                         project.translation.review, project.audio.approve,
                         project.release.approve, voice.catalog.manage, artifact.download]
  ownership:
    tables_and_app_functions_owner: postgres        # SUPERUSER - see finding H-1
    database_owner: umbracontrol_owner              # NOLOGIN, owns nothing else
    migrations_applied_as: postgres                 # bootstrap_umbracontrol_database.sh:243
  rls:
    enabled_on: [organizations, projects, users, roles, permissions, role_permissions,
                 user_roles, invitations, sessions, mfa_methods, bootstrap_tokens,
                 audit_events]
    forced_on: []
    invariant: >-
      app.* SECURITY DEFINER functions and the tables they read MUST share one owner,
      and RLS MUST NOT be FORCE'd on organizations, projects, users, user_roles.
      Violating either causes 42P17 infinite recursion in policy for relation "projects".

current_state:
  authentication: none
  authorization_checks: 0
  routes_total: 129
  mutating_endpoints: 65
  project_identifier: unsigned browser cookie "active_project", max_age 1 year
  job_queue: none
  job_identity: output filename derived from (chapter_id, utterance_index)
  leases_or_heartbeats: none
  retry: linear sleep, innermost loop only; batch driver has none
  durable_utterance_identity: none
  artifact_checksums: none
  checksummed_content: voice reference fingerprint only (sha256 truncated to 16 hex)
  local_accounts_to_migrate: 0
  legacy_identity_strings: ["'user' in voice_review_history.reviewed_by",
                            "'user' in exception_reviews.resolved_by"]
  legacy_speaker_stores: 5
  legacy_ddl_authorities: 4
  enforced_foreign_keys: false
  check_constraints: 0
  project_identifier_path_pattern: <base>/<slug>/.umbra/project.db
  artifact_path_patterns:
    utterance: .umbra/chapter_audio/ch{N}/utt_{utterance_index:04d}.wav
    marker: .umbra/chapter_audio/ch{N}/utt_{utterance_index:04d}.wav.txt
    chapter_master: .umbra/chapter_audio/ch{N}_full.wav
    chapter_mp3: .umbra/chapter_audio/ch{N}_full.mp3
    book_master: .umbra/export/{m4b_meta.output_name}
    ar_track: .umbra/export/ar_package/{Base}_{NN}_{Title}.mp3
    ar_manifest: .umbra/export/ar_package.json
    voice_reference: .umbra/voices/{name_slug}_ref.wav

proposed_tables:
  - {name: works,                 grain: one bibliographic work,        tenancy: organization, writer: human_api, pk: uuid, delta: ADD_TABLE}
  - {name: source_documents,      grain: one ingested file version,      tenancy: organization+project, writer: ingestion, pk: uuid, delta: ADD_TABLE}
  - {name: source_revisions,      grain: one frozen source revision,    tenancy: organization+project, writer: source_editor, pk: uuid, delta: ADD_TABLE}
  - {name: source_chapters,       grain: one chapter per revision,      tenancy: organization+project, writer: analysis, pk: uuid, delta: ADD_TABLE}
  - {name: source_units,          grain: one addressable source unit,   tenancy: organization+project, writer: analysis, pk: uuid, delta: ADD_TABLE, note: generated source_unit_key = sha256(source_revision_id||ordinal||occurrence||text_sha256)}
  - {name: speakers,              grain: one logical speaker per project, tenancy: organization+project, writer: analysis+human, pk: uuid, delta: ADD_TABLE}
  - {name: speaker_aliases,       grain: one alias per speaker,         tenancy: organization+project, writer: analysis+human, pk: uuid, delta: ADD_TABLE}
  - {name: analysis_runs,         grain: one analysis execution,        tenancy: organization+project, writer: worker, pk: uuid, delta: ADD_TABLE}
  - {name: analysis_snapshots,    grain: one immutable result payload,  tenancy: organization+project, writer: worker, pk: uuid, delta: ADD_TABLE}
  - {name: voice_profiles,        grain: one voice profile per org,     tenancy: organization, writer: voice_manager, pk: uuid, delta: ADD_TABLE}
  - {name: voice_profile_revisions, grain: one profile change,          tenancy: organization, writer: voice_manager, pk: uuid, delta: ADD_TABLE}
  - {name: voice_licenses,        grain: one licence grant,             tenancy: organization, writer: voice_manager, pk: uuid, delta: ADD_TABLE}
  - {name: voice_casts,           grain: one speaker cast per revision, tenancy: organization+project, writer: audio_director, pk: uuid, delta: ADD_TABLE}
  - {name: project_locales,       grain: one locale lane per project,   tenancy: organization+project, writer: translation, pk: uuid, delta: ADD_TABLE}
  - {name: translation_runs,      grain: one translation execution,     tenancy: organization+project, writer: translation_worker, pk: uuid, delta: ADD_TABLE}
  - {name: translation_units,     grain: one target render span,        tenancy: organization+project, writer: translator, pk: uuid, delta: ADD_TABLE}
  - {name: audio_runs,            grain: one generation run,            tenancy: organization+project, writer: audio_worker, pk: uuid, delta: ADD_TABLE}
  - {name: audio_run_casts,       grain: one cast row per run+speaker,  tenancy: organization+project, writer: audio_worker, pk: uuid, delta: ADD_TABLE}
  - {name: artifacts,             grain: one immutable artifact version, tenancy: organization+project, writer: producer, pk: uuid, delta: ADD_TABLE, bytes_in_postgres: false}
  - {name: artifact_links,        grain: one lineage edge,              tenancy: organization+project, writer: producer, pk: bigint_identity, delta: ADD_TABLE}
  - {name: jobs,                  grain: one schedulable work unit,     tenancy: organization+project, writer: api+worker, pk: uuid, delta: ADD_TABLE, idempotency: "UNIQUE(project_id, job_type, idempotency_key)", key_is_never_a_filename: true}
  - {name: job_attempts,          grain: one attempt per job,           tenancy: organization+project, writer: worker, pk: bigint_identity, delta: ADD_TABLE}
  - {name: job_leases,            grain: one live lease per job,        tenancy: organization+project, writer: worker, pk: uuid, delta: ADD_TABLE, unique: "UNIQUE(job_id) WHERE released_at IS NULL"}
  - {name: service_identities,    grain: one non-human principal,       tenancy: organization_or_platform, writer: platform_admin, pk: uuid, delta: ADD_TABLE}
  - {name: legacy_id_map,         grain: one legacy key mapping,        tenancy: organization+project, writer: migration_tool, pk: uuid, delta: ADD_TABLE}

alters_existing:
  - {table: projects,     change: "ADD work_id, source_locale, row_version, migration_state, legacy_project_path", delta: ALTER_TABLE}
  - {table: users,        change: "ADD password_disabled_at", delta: ALTER_TABLE}
  - {table: sessions,     change: "ADD mfa_verified_at, idle_deadline_at", delta: ALTER_TABLE}
  - {table: audit_events, change: "ADD actor_service_identity_id, principal_kind, actor_subject_snapshot, session_id", delta: ALTER_TABLE}
  - {table: permissions,  change: "ADD missing audio/speaker/cast/analysis/destroy/self-manage keys", delta: BACKFILL}
  - {table: audit_events, change: "NO foreign keys on organization_id/project_id - audit must survive deletion", delta: NO_CHANGE}

invariants:
  - id: G1
    rule: one source ebook has one stable source identity
    enforced_by: "UNIQUE (project_id, sha256) on source_documents"
  - id: G2
    rule: one canonical project contains audiobook and translation work
    enforced_by: "NOT NULL project_id + composite FK (project_id, organization_id)"
  - id: G3
    rule: analysis has immutable versions
    enforced_by: "BEFORE UPDATE triggers on source_revisions/analysis_runs/analysis_snapshots"
  - id: G4
    rule: speaker identity is stable across languages
    enforced_by: "speakers.id + source_unit_key; translated names never written to speakers"
  - id: G5
    rule: voice reuse only when language/accent/consent/licence allow
    enforced_by: "deny-by-default trigger on voice_casts + app.voice_licence_covers()"
  - id: G6
    rule: every translation references source book, project and analysis version
    enforced_by: "NOT NULL source_revision_id + nightly reconciliation"
  - id: G7
    rule: every audiobook references project, translation when applicable, cast snapshot, generation run
    enforced_by: "NOT NULL audio_run_id + CHECK + nightly lineage validator"
  - id: G8
    rule: translated ebooks and audiobooks never become duplicate source records
    enforced_by: "artifact_links acyclicity check + application rule"
  - id: G9
    rule: audio production continues before, during and after cutover
    enforced_by: "degraded-mode lease; PG failure logged, never fatal"
  - id: G10
    rule: large media in object storage only
    enforced_by: "no bytea/blob column in any new table; test asserts absence"
  - id: G11
    rule: every write is idempotent and safe to retry
    enforced_by: "natural unique constraints on every table + replay tests"
  - id: G12
    rule: concurrent workers cannot silently overwrite one another
    enforced_by: "partial UNIQUE on job_leases + FOR UPDATE SKIP LOCKED + row_version"
  - id: G13
    rule: service identities are not arbitrary client-supplied user ids
    enforced_by: "CHECK (principal_kind='service' => actor_service_identity_id NOT NULL AND actor_user_id IS NULL)"
  - id: G14
    rule: translation UI discovers authorized projects without a separate account system
    enforced_by: "single app.authorized_projects() used by both clients"
  - id: G15
    rule: existing staleness semantics preserved, including the fx_version rule
    enforced_by: "regression test; generation/staleness.py:98"

identity_contract:
  canonical_users_table: users
  canonical_projects_table: projects
  membership_model: user_roles where scope='project' and status='active'
  project_members_table: REJECTED
  second_password_store: FORBIDDEN
  authentication_entry_point: "app.resolve_session(p_token_hash text) SECURITY DEFINER"
  project_context: "derived server-side per request; app.can_access_project(project_id) recomputed every request; never trusted from request"
  retired_mechanism: "unsigned active_project browser cookie"
  session_absolute_lifetime: 7d
  session_idle_lifetime: 8h
  session_storage: "sha256(token) only"
  session_revoke_all: "app.revoke_user_sessions(uuid) SECURITY DEFINER - NEW"
  suspension_enforcement: app.resolve_session
  mfa_enforcement: app.resolve_session, requires sessions.mfa_verified_at - NEW
  human_principal: users row via sessions
  worker_principal: service_identities row + job_leases row
  worker_impersonation: PROHIBITED
  session_scope: user-global; organisation context re-derived and re-authorised per request

security:
  guc_impersonation:
    guc: app.user_id
    settable_by_any_role: true
    revocable: false
    rationale: "custom placeholder GUCs are not in pg_settings, so REVOKE SET ON PARAMETER is unavailable"
    classification: ACCEPTED_RESIDUAL_RISK
    compensating_controls:
      - credential in root-owned 0640 file, service-group readable only
      - "SET LOCAL inside an explicit transaction by trusted framework code only"
      - never derived from request body, query, cookie, header or file (lint-enforced)
      - database reachable only by the API
      - non-superuser owner role (H24)
      - incident-response logging available
  superuser_in_data_path: true
  force_rls_permitted: false
  new_app_functions_public_executable_by_default: true
  default_privileges_configured: false
  cookie_flags_present: [max_age]
  cookie_flags_missing: [httponly, samesite, secure]
  server_binding: "0.0.0.0:8420 plain HTTP behind LAN reverse proxy"
  application_auth: none
  csrf_protection: none
  security_headers: none
  trusted_host: none
  download_authorization: none
  verified_application_defects:
    - {id: A1, severity: critical, finding: "load_config accepts absolute paths verbatim; cookie is a path-selection primitive", evidence: "umbraaudio/config.py:156-158"}
    - {id: A2, severity: critical, finding: "download traversal via string-prefix containment instead of path components", evidence: "umbraaudio/web/routes.py:5049"}
    - {id: A3, severity: critical, finding: "upload filename used raw, no Path().name; writes outside uploads/", evidence: "umbraaudio/web/routes.py:633,640,647,652"}
    - {id: A4, severity: high, finding: "activate-project existence check is unreachable dead code", evidence: "umbraaudio/web/routes.py:525 with config.py:165"}
    - {id: A5, severity: critical, finding: "65 mutating endpoints with zero authorization, including rmtree of voices/ and DELETE FROM utterances", evidence: "umbraaudio/web/routes.py:4674,4711,5097"}
    - {id: A6, severity: high, finding: "unauthenticated model load/unload of GB-scale models", evidence: "umbraaudio/web/routes.py:5744,5775,5814"}
    - {id: A7, severity: high, finding: "second CWD-relative project resolution path bypasses load_config and its checks", evidence: "umbraaudio/web/__init__.py:51"}
    - {id: A8, severity: medium, finding: "unauthenticated outbound-request proxy to dictionaryapi.dev", evidence: "umbraaudio/web/routes.py:1847"}
    - {id: A9, severity: medium, finding: "404-vs-403 existence oracle: existence checked before containment", evidence: "umbraaudio/web/routes.py:5047"}
    - {id: A10, severity: medium, finding: "ChapterBuilder has no lock; concurrent builds write the same files", evidence: "umbraaudio/web/routes.py:5097-5110"}

cutover:
  strategy: per_project_flagged_dual_write
  default_repository: SqliteProjectRepository
  phase_order: [P0_inventory, P1_adapter_boundary, P2_mirror_backfill_shadow_read,
                P3_enterprise_schema, P4_dual_write, P5_dry_run_primary,
                P6_enterprise_authoritative, P7_retention]
  flag: projects.migration_state
  flag_values: [legacy, mirroring, dual_write, enterprise_authoritative, retired]
  global_switch: FORBIDDEN
  dual_write_failure_policy: log_and_continue
  degraded_mode:
    lease_validity_hours: 6
    on_expiry: halt new runs, complete in-flight rendering, alert operator
  authoritative_when:
    - three consecutive nightly reconciliations pass
    - artifact sha256 all match
    - zero mapping_state=unmapped for the project
    - one full generation run completed with enterprise store primary
    - restore drill passed
  rollback:
    method: single flag flip
    stops_worker: false
    deletes_source_data: false
    retains_enterprise_rows: true
    marks_rows: superseded_by_local
  unmappable_records:
    table: legacy_id_map
    states: [unmapped, ambiguous, rejected]
    dropped: false

schema_delta_counts:
  add_table: 24
  alter_table: 4
  add_column: 12
  add_constraint: 6
  add_index: 30
  enable_prove_rls: 21
  backfill: 3
  grant_revoke: 2
  new_service_role: 2
  no_change: 2

blockers:
  - {id: B1, severity: BLOCKER, title: "worker role can read no project and cannot attribute audit events", evidence: "001:471-473,548-552", fix: [H1, H2, H15, H22], workaround_forbidden: "setting app.user_id for the worker"}
  - {id: B2, severity: BLOCKER, title: "no permission exists for audio generation, speaker editing, voice casting, analysis, or state destruction", evidence: "001:378-391", fix: [H19]}
  - {id: B3, severity: BLOCKER, title: "mfa_required stored but never enforced; sessions lack mfa_verified_at", evidence: "001:30,120-129", fix: [H17, H20]}
  - {id: B4, severity: BLOCKER, title: "new functions in schema app are EXECUTE-able by PUBLIC by default", evidence: "001:580", fix: [H23]}
  - {id: B5, severity: BLOCKER, title: "users.status and organizations.status not enforced by any policy", evidence: "001:28-29,15-17", fix: [H20]}

open_decisions:
  - {id: DECISION-1, priority: highest, question: "confirm reversal of spec 19 section 2.3 and 16 M4 (one-way import) in favour of an authoritative enterprise database", recommendation: "adopt, and amend spec 19"}
  - {id: DECISION-2, question: "confirm spec 18 section 4.2.1 online-backup snapshot protocol is superseded by shared-database reads", recommendation: "adopt; removes snapshot-consistency problem"}
  - {id: DECISION-3, question: "org-scoped voice_manager project visibility: metadata-only or full membership", recommendation: "metadata-only"}
  - {id: DECISION-4, question: "add audio_engineer and producer roles", recommendation: "defer; project_manager plus audio_director is sufficient"}
  - {id: DECISION-5, question: "retention period for artifacts and legacy SQLite after cutover", recommendation: "indefinite read-only"}
  - {id: DECISION-6, question: "object storage host and availability", recommendation: "must exist before artifact registration; until then object_key resolves locally"}
  - {id: DECISION-7, question: "identity provider: external OIDC or promoted bootstrap provider", recommendation: "external OIDC"}
  - {id: DECISION-8, question: "is the audio UI retired in favour of the shared review UI", recommendation: "decide early; determines whether defect fixes are prerequisite or parallel"}
  - {id: DECISION-9, question: "phase 2 scope: translation-design tables and commercial voice subsystem", recommendation: "defer to a separate approved phase"}

acceptance_tests: 20
backup_requirements: 7
production_actions_taken: none
next_step: "human review and approval of this contract; no connection, migration, or application data write until approved"

---

End of contract. No database was contacted, no migration was executed, no production data was written, and no secret was used or requested. Awaiting human approval.