app/security/migrations/0004_generation_provider_runtime_postgres.sql

-- Provider-runtime safety constraints for the generation foundation.
--
-- Apply after 0003_generation_domain_postgres.sql. This migration does not
-- register a generation provider, add a worker URL, or introduce credentials.

begin;

-- An opaque remote worker job may be bound to exactly one logical
-- MediaRouter job for a provider. PostgreSQL permits multiple NULL values, so
-- queued local jobs remain unaffected until a trusted dispatcher binds them.
do $$
begin
  if exists (
    select 1
    from generation_jobs
    where external_job_id is not null
    group by provider, external_job_id
    having count(*) > 1
  ) then
    raise exception
      'cannot add provider worker-job uniqueness: duplicate generation_jobs external IDs exist'
      using errcode = '23505';
  end if;
end $$;

create unique index if not exists uq_generation_job_provider_external
  on generation_jobs(provider, external_job_id)
  where external_job_id is not null;

commit;