app/projects/migrations/0014_approval_workspace_integrity.sql

-- Additive approval workspace integrity migration.
--
-- This migration brings the existing approval domain in line with the
-- authoritative ORM models and closes the confirmed cross-workspace
-- authorization gap by backfilling approval requests with their owning
-- workflow workspace, enforcing the relationship, and adding an index
-- used by authorization checks.
--
-- Apply after 0013_notification_preferences.sql.

begin;

alter table approval_requests
  add column if not exists workspace_id text;

do $$
begin
  if exists (
    select 1
    from approval_requests
    where workspace_id is null
  ) then
    update approval_requests
    set workspace_id = approval_workflows.workspace_id
    from approval_workflows
    where approval_workflows.id = approval_requests.workflow_id
      and approval_requests.workspace_id is null;
  end if;
end $$;

alter table approval_requests
  alter column workspace_id set not null;

alter table approval_requests
  drop constraint if exists approval_requests_workflow_id_fkey,
  add constraint approval_requests_workflow_id_fkey
    foreign key (workflow_id) references approval_workflows(id) on delete cascade;

create index if not exists ix_approval_requests_workspace
  on approval_requests(workspace_id);

commit;