be04e19d04
- Add migration 0010: utterance_translations table with UNIQUE(utterance_id, language_code)
- Add schemas: OrganizationCreateRequest/Response, ProgramCreateRequest/Response,
UtteranceTranslationItem/Request/Response/ListResponse, SpeakerSyncRequest/Response
- Add service methods: create_organization, create_program, sync_speakers,
upsert_utterance_translations, get_utterance_translations
- Add routes_controller.py: POST /api/orgs, POST /api/programs,
POST /api/speakers/sync, POST /api/projects/{id}/translations,
GET /api/projects/{id}/translations/{language_code}
- Mount controller_router at /api in app.py
- Create bdumbra account (member, BDMurphy org) for integration testing
60 lines
2.6 KiB
PL/PgSQL
60 lines
2.6 KiB
PL/PgSQL
-- 0010: utterance translations — per-language translated text for controller-audio integration.
|
|
-- Status: PROPOSED
|
|
-- Additive: one new table, no existing tables altered.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS public.utterance_translations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL REFERENCES public.projects(id),
|
|
utterance_id uuid NOT NULL REFERENCES public.translation_utterances(id),
|
|
language_code text NOT NULL,
|
|
language_name text NOT NULL,
|
|
text text NOT NULL,
|
|
status text NOT NULL DEFAULT 'draft',
|
|
origin text NOT NULL DEFAULT 'umbratrans',
|
|
source_ref text,
|
|
delivered_at timestamptz,
|
|
confirmed_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
voiced boolean NOT NULL DEFAULT true,
|
|
review_reason text,
|
|
UNIQUE(utterance_id, language_code)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS utterance_translations_project_idx
|
|
ON public.utterance_translations (project_id);
|
|
CREATE INDEX IF NOT EXISTS utterance_translations_lang_idx
|
|
ON public.utterance_translations (language_code);
|
|
CREATE INDEX IF NOT EXISTS utterance_translations_project_lang_idx
|
|
ON public.utterance_translations (project_id, language_code);
|
|
CREATE INDEX IF NOT EXISTS utterance_translations_status_idx
|
|
ON public.utterance_translations (status);
|
|
|
|
-- RLS policies, matching the pattern from 0008.
|
|
DO $rls$
|
|
BEGIN
|
|
EXECUTE 'ALTER TABLE public.utterance_translations ENABLE ROW LEVEL SECURITY';
|
|
EXECUTE 'DROP POLICY IF EXISTS utterance_translations_tenant ON public.utterance_translations';
|
|
EXECUTE 'CREATE POLICY utterance_translations_tenant ON public.utterance_translations USING (app.is_platform_admin() OR app.is_organization_member(project_id))';
|
|
END
|
|
$rls$;
|
|
|
|
COMMENT ON TABLE public.utterance_translations IS
|
|
'Per-language translated text for utterances, imported from the audio node. One row per utterance per language.';
|
|
|
|
COMMENT ON COLUMN public.utterance_translations.language_code IS
|
|
'BCP-47 language tag (e.g. en, zh-Hans).';
|
|
COMMENT ON COLUMN public.utterance_translations.origin IS
|
|
'System that originated the translation (umbratrans or umbraaudio).';
|
|
COMMENT ON COLUMN public.utterance_translations.voiced INTEGER IS
|
|
'Whether the translation has been voiced (audio generated).';
|
|
|
|
COMMIT;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Rollback, in reverse dependency order. Not applied automatically.
|
|
-- DROP POLICY IF EXISTS utterance_translations_tenant ON public.utterance_translations;
|
|
-- DROP TABLE IF EXISTS public.utterance_translations;
|