Spaces:
Running
Running
| -- Reconcile durable generation ORM fields with the production PostgreSQL schema. | |
| -- Apply after 0003_generation_domain_postgres.sql and the project foundation migration. | |
| begin; | |
| alter table generation_requests | |
| add column if not exists project_id text, | |
| add column if not exists product_surface text not null default 'generation'; | |
| alter table generation_jobs | |
| add column if not exists brand_kit_version_id text, | |
| add column if not exists progress_percent integer | |
| check (progress_percent is null or progress_percent between 0 and 100); | |
| alter table generation_job_attempts | |
| add column if not exists error_code text; | |
| do $$ | |
| begin | |
| if not exists ( | |
| select 1 from pg_constraint | |
| where conname = 'fk_generation_requests_project' | |
| and conrelid = 'generation_requests'::regclass | |
| ) then | |
| alter table generation_requests | |
| add constraint fk_generation_requests_project | |
| foreign key (project_id) references projects(id) on delete restrict; | |
| end if; | |
| if not exists ( | |
| select 1 from pg_constraint | |
| where conname = 'ck_generation_requests_product_surface' | |
| and conrelid = 'generation_requests'::regclass | |
| ) then | |
| alter table generation_requests | |
| add constraint ck_generation_requests_product_surface | |
| check (product_surface in ('generation', 'ai_studio')); | |
| end if; | |
| end $$; | |
| create index if not exists ix_generation_requests_workspace_project_created | |
| on generation_requests(workspace_id, project_id, created_at desc); | |
| create or replace function mediarouter_assert_generation_request_project_workspace() | |
| returns trigger language plpgsql as $$ | |
| declare project_workspace text; | |
| begin | |
| if new.project_id is not null then | |
| select workspace_id into project_workspace | |
| from projects where id = new.project_id; | |
| if project_workspace is null or project_workspace is distinct from new.workspace_id then | |
| raise exception 'generation project must belong to its request workspace' | |
| using errcode = '23503'; | |
| end if; | |
| end if; | |
| return new; | |
| end; | |
| $$; | |
| drop trigger if exists mediarouter_generation_request_project_workspace on generation_requests; | |
| create trigger mediarouter_generation_request_project_workspace | |
| before insert or update of workspace_id, project_id on generation_requests | |
| for each row execute function mediarouter_assert_generation_request_project_workspace(); | |
| commit; | |