UmbraDatastore/migrations/0008_audio_source_import.sql

-- 0008: the audio source import — the controller's copy of what the audio node
-- analyzed, and the tables the file-based importer writes.
--
-- Status: APPLIED (applied by the audio-controller to `umbratrans` as
-- described in the migration procedure; verified against scratch and then
-- applied). Proposed alongside `umbratrans_web/audio_import.py` and
-- `docs/audio-system-mapping-v1.md`.
--
-- Additive and idempotent: four new tables, one guard function, their policies.
-- Nothing existing is altered, and no existing table gains or loses a column.
--
-- Why these tables exist at all. The audio node owns the audiobooks and exports
-- a metadata bundle; the controller is the authoritative repository for the
-- translation pipeline and must hold a copy of that analysis rather than reach
-- across to the audio database whenever it needs a chapter or a speaker. The
-- copy is a snapshot identified by `content_hash`, and the fields are the ones
-- `audio_import.py` maps — the mapping document is the contract, this file is
-- where it lands.
--
-- Three decisions here are load-bearing and are commented where they are made:
--
--   1. `project_id` carries no foreign key. `projects` lives in the per-tenant
--      schema (0005) and is created by `app.provision_tenant_schema`, so there is
--      no stable relation name to reference. `public.artifacts` reached the same
--      conclusion in 0003 and this follows it: `organization_id` is the FK that
--      matters for tenancy, and `project_id` is an opaque key scoped by it.
--   2. Identity is the source row id, never the order. `source_row_id` and
--      `external_id` come from the audio database; `reading_order` is a snapshot
--      of one export and is deliberately not unique or indexed as an identity.
--   3. The four tables are append-only on UPDATE. A source fact whose text is
--      rewritten underneath its `source_hash` is a row that no longer describes
--      anything; a changed source is a new import, not an edit. DELETE is
--      permitted, because re-import and retention both need it and the same
--      reasoning as `artifacts` applies.
--
-- `integrity` for an artifact is `unverified` on import by design: the importer
-- records a render's path and checksum as a claim. Reading the bytes back is a
-- different operation with a different name.

BEGIN;

-- Fail fast rather than half-create if the prerequisites are missing. Both are
-- phase-1 objects; if either is absent this is the wrong database.
DO $preflight$
BEGIN
    IF to_regclass('public.organizations') IS NULL THEN
        RAISE EXCEPTION 'public.organizations is missing; 0008 belongs on the control-plane schema';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                    WHERE n.nspname = 'app' AND p.proname = 'is_organization_member') THEN
        RAISE EXCEPTION 'app.is_organization_member(uuid) is missing; the RLS policies this file writes would be inert';
    END IF;
END
$preflight$;

-- ---------------------------------------------------------------------
-- 1. The source reference: one row per project, one source system
-- ---------------------------------------------------------------------

-- The book-level record: what this project came from, and the content hash of
-- the whole export. The hash is what makes an import idempotent — the same bytes
-- are the same import, and a different hash is a different one.
CREATE TABLE IF NOT EXISTS public.project_source_refs (
    project_id uuid PRIMARY KEY,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    -- The exporting system and the node that ran it. Text, not a FK to
    -- compute_nodes: the source system is a fact about the data (it is
    -- `umbraaudio` on `umbraaudio-m4`), and a node row can be removed (0006)
    -- without the provenance of an already-imported book becoming unknown.
    system text NOT NULL CHECK (length(btrim(system)) BETWEEN 1 AND 64),
    node_id text NOT NULL,
    external_id text NOT NULL,
    source_title text,
    source_status text,
    -- Languages the audio side designated, if any. Empty is accepted as an
    -- answer: target languages are a translation-side decision.
    languages jsonb NOT NULL DEFAULT '[]'::jsonb,
    -- The numbering schemes and the hazard that accompanies them, stored
    -- verbatim because the note ("three numbers identify one chapter and none
    -- derives from another") is the useful part.
    numbering jsonb NOT NULL DEFAULT '{}'::jsonb,
    content_hash text NOT NULL CHECK (content_hash ~ '^[0-9a-f]{64}$'),
    product_version text,
    source_meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    imported_at timestamptz NOT NULL DEFAULT now(),
    -- One source system may only describe one project once. A second system (a
    -- future cover pipeline, say) may add its own row about the same project, so
    -- the uniqueness is on the pair and not on either alone.
    CONSTRAINT project_source_refs_system_external_key UNIQUE (system, external_id)
);

CREATE INDEX IF NOT EXISTS project_source_refs_org_idx ON public.project_source_refs (organization_id);

-- ---------------------------------------------------------------------
-- 2. Speakers
-- ---------------------------------------------------------------------

-- One row per speaker *as the source recorded it*. The narrator is the one
-- merge the importer performs, and it is performed on the identity the pipeline
-- uses, not here: both narrator source rows are kept, and `merged_into` records
-- which spelling they resolve to. Nothing else is merged — a casefold collision
-- group is stored and surfaced to a human, never collapsed.
CREATE TABLE IF NOT EXISTS public.translation_speakers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    external_id text NOT NULL,
    display_name text NOT NULL,
    role text NOT NULL CHECK (role IN ('narrator', 'character')),
    aliases jsonb NOT NULL DEFAULT '[]'::jsonb,
    collision_group text,
    has_profile boolean NOT NULL DEFAULT false,
    is_group boolean NOT NULL DEFAULT false,
    unidentified boolean NOT NULL DEFAULT false,
    edge_voice text,
    tts_engine text,
    voice_review_status text,
    -- The canonical narrator this row resolves to, when it is not itself the
    -- canonical one. NULL for characters and for the canonical narrator.
    merged_into_external_id text,
    source_meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT translation_speakers_identity_key UNIQUE (project_id, external_id)
);

CREATE INDEX IF NOT EXISTS translation_speakers_org_idx ON public.translation_speakers (organization_id);
CREATE INDEX IF NOT EXISTS translation_speakers_project_idx ON public.translation_speakers (project_id);

-- ---------------------------------------------------------------------
-- 3. Chapters
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.translation_chapters (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    external_id text NOT NULL,
    -- A snapshot of the export's order. Useful, not an identity.
    reading_order integer NOT NULL,
    matter text NOT NULL DEFAULT 'novel' CHECK (matter IN ('novel', 'matter')),
    title text,
    source_hash text NOT NULL CHECK (source_hash ~ '^[0-9a-f]{64}$'),
    source_row_id bigint NOT NULL,
    numbering_basis jsonb NOT NULL DEFAULT '{}'::jsonb,
    utterance_count integer NOT NULL DEFAULT 0 CHECK (utterance_count >= 0),
    source_status text,
    source_meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT translation_chapters_identity_key UNIQUE (project_id, external_id)
);

CREATE INDEX IF NOT EXISTS translation_chapters_org_idx ON public.translation_chapters (organization_id);
CREATE INDEX IF NOT EXISTS translation_chapters_project_idx ON public.translation_chapters (project_id, reading_order);

-- ---------------------------------------------------------------------
-- 4. Utterances
-- ---------------------------------------------------------------------

-- `chapter_external_id` is text and not a FK to `translation_chapters`, for the
-- same reason `project_id` has no FK: these rows arrive together in one import
-- and the importer has already resolved that every reference resolves. A FK
-- would add a constraint the import order has to satisfy without adding a
-- guarantee the importer does not already hold.
CREATE TABLE IF NOT EXISTS public.translation_utterances (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    external_id text NOT NULL,
    chapter_external_id text NOT NULL,
    utterance_index integer NOT NULL,
    kind text NOT NULL CHECK (kind IN ('narration', 'dialogue', 'scene_break')),
    text text NOT NULL,
    source_hash text NOT NULL CHECK (source_hash ~ '^[0-9a-f]{64}$'),
    speaker_external_id text NOT NULL,
    -- NULL is the implicit narrator, and that is the whole meaning of NULL here.
    -- An explicit attribution always carries the name the source wrote.
    speaker_name_raw text,
    speaker_name_resolved text NOT NULL DEFAULT '',
    attribution text NOT NULL CHECK (attribution IN ('implicit_narrator', 'explicit')),
    needs_review boolean NOT NULL DEFAULT false,
    render_external_id text,
    source_row_id bigint,
    source_meta jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT translation_utterances_identity_key UNIQUE (project_id, external_id)
);

CREATE INDEX IF NOT EXISTS translation_utterances_org_idx ON public.translation_utterances (organization_id);
CREATE INDEX IF NOT EXISTS translation_utterances_chapter_idx
    ON public.translation_utterances (project_id, chapter_external_id, utterance_index);
CREATE INDEX IF NOT EXISTS translation_utterances_speaker_idx
    ON public.translation_utterances (project_id, speaker_external_id);

-- ---------------------------------------------------------------------
-- 5. Append-only on UPDATE
-- ---------------------------------------------------------------------

-- One guard for the four tables. UPDATE is refused because a source fact is a
-- statement about a document at a moment, and rewriting it in place breaks the
-- link between the row and its `source_hash`. DELETE is permitted, exactly as it
-- is for `artifacts`: re-importing a changed bundle and reclaiming storage both
-- need it, and the guarantee that matters (no silent edit) is not weakened by
-- allowing a row to be removed and written again.
DO $guard$
BEGIN
    EXECUTE $func$
        CREATE OR REPLACE FUNCTION app.prevent_translation_source_mutation()
        RETURNS trigger LANGUAGE plpgsql AS $body$
        BEGIN
            RAISE EXCEPTION
                'translation source rows are a snapshot and are append-only; a changed source is a new import, not an edit';
        END;
        $body$;
    $func$;

    EXECUTE 'DROP TRIGGER IF EXISTS project_source_refs_append_only ON public.project_source_refs';
    EXECUTE 'CREATE TRIGGER project_source_refs_append_only BEFORE UPDATE ON public.project_source_refs FOR EACH ROW EXECUTE FUNCTION app.prevent_translation_source_mutation()';
    EXECUTE 'DROP TRIGGER IF EXISTS translation_speakers_append_only ON public.translation_speakers';
    EXECUTE 'CREATE TRIGGER translation_speakers_append_only BEFORE UPDATE ON public.translation_speakers FOR EACH ROW EXECUTE FUNCTION app.prevent_translation_source_mutation()';
    EXECUTE 'DROP TRIGGER IF EXISTS translation_chapters_append_only ON public.translation_chapters';
    EXECUTE 'CREATE TRIGGER translation_chapters_append_only BEFORE UPDATE ON public.translation_chapters FOR EACH ROW EXECUTE FUNCTION app.prevent_translation_source_mutation()';
    EXECUTE 'DROP TRIGGER IF EXISTS translation_utterances_append_only ON public.translation_utterances';
    EXECUTE 'CREATE TRIGGER translation_utterances_append_only BEFORE UPDATE ON public.translation_utterances FOR EACH ROW EXECUTE FUNCTION app.prevent_translation_source_mutation()';

    EXECUTE 'REVOKE ALL ON FUNCTION app.prevent_translation_source_mutation() FROM PUBLIC';
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_api') THEN
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_translation_source_mutation() TO umbracontrol_api';
    END IF;
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_worker') THEN
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_translation_source_mutation() TO umbracontrol_worker';
    END IF;
END
$guard$;

-- ---------------------------------------------------------------------
-- 6. Row-level security
-- ---------------------------------------------------------------------

-- Enabled but not forced, matching every other table in this schema: the
-- application owns these tables, so the policy is defence in depth for a
-- non-owner role and the adapter's own scoping is today's control. `FORCE` and a
-- non-owner application role arrive together at gate G2 and nowhere sooner.
--
-- `CREATE POLICY` has no `IF NOT EXISTS`, so a re-run would fail on the second
-- apply. Each is dropped first, which is what makes this file safe to apply
-- twice.
DO $rls$
DECLARE
    target text;
BEGIN
    FOREACH target IN ARRAY ARRAY[
        'project_source_refs', 'translation_speakers', 'translation_chapters', 'translation_utterances'
    ] LOOP
        EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', target);
        EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', target || '_tenant', target);
    END LOOP;

    EXECUTE 'CREATE POLICY project_source_refs_tenant ON public.project_source_refs USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
    EXECUTE 'CREATE POLICY translation_speakers_tenant ON public.translation_speakers USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
    EXECUTE 'CREATE POLICY translation_chapters_tenant ON public.translation_chapters USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
    EXECUTE 'CREATE POLICY translation_utterances_tenant ON public.translation_utterances USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
END
$rls$;

-- ---------------------------------------------------------------------
-- 7. Documentation for the schema itself
-- ---------------------------------------------------------------------

COMMENT ON TABLE public.project_source_refs IS
    'One row per project per source system: what the project was imported from and the content hash of that import.';
COMMENT ON COLUMN public.project_source_refs.content_hash IS
    'sha256 of the exported manifest bytes. The same bytes are the same import; a different hash is a different import.';
COMMENT ON TABLE public.translation_speakers IS
    'Speakers as the source recorded them. The narrator merge is recorded in merged_into_external_id; no other row is merged.';
COMMENT ON TABLE public.translation_chapters IS
    'Chapters. Identity is external_id (the source row id); reading_order is a snapshot and not an identity.';
COMMENT ON TABLE public.translation_utterances IS
    'Utterances. speaker_name_raw NULL means the implicit narrator, which is the entire meaning of NULL in that column.';
COMMENT ON FUNCTION app.prevent_translation_source_mutation() IS
    'Refuses UPDATE on the translation source tables. A changed source is a new import, not an edit.';

COMMIT;

-- ---------------------------------------------------------------------
-- Rollback, in reverse dependency order. Not applied automatically.
--
-- DROP POLICY IF EXISTS translation_utterances_tenant ON public.translation_utterances;
-- DROP POLICY IF EXISTS translation_chapters_tenant ON public.translation_chapters;
-- DROP POLICY IF EXISTS translation_speakers_tenant ON public.translation_speakers;
-- DROP POLICY IF EXISTS project_source_refs_tenant ON public.project_source_refs;
-- DROP TRIGGER IF EXISTS translation_utterances_append_only ON public.translation_utterances;
-- DROP TRIGGER IF EXISTS translation_chapters_append_only ON public.translation_chapters;
-- DROP TRIGGER IF EXISTS translation_speakers_append_only ON public.translation_speakers;
-- DROP TRIGGER IF EXISTS project_source_refs_append_only ON public.project_source_refs;
-- DROP FUNCTION IF EXISTS app.prevent_translation_source_mutation();
-- DROP TABLE IF EXISTS public.translation_utterances;
-- DROP TABLE IF EXISTS public.translation_chapters;
-- DROP TABLE IF EXISTS public.translation_speakers;
-- DROP TABLE IF EXISTS public.project_source_refs;
--
-- The rollback is destructive and drops the imported data with the tables. It is
-- safe only while nothing the translation pipeline produced points at a
-- translation row, which is true today because the pipeline has not run. Once a
-- translated chapter exists, the rollback must be replaced by a deprecation, not
-- run as written.