app/security/migrations/0003_generation_domain_postgres.sql

-- Provider-neutral generation foundation.
--
-- Apply after 0002_authoritative_tenancy_postgres.sql.  This migration adds
-- durable generation intent and execution records only; it does not configure
-- WAN, FLUX, external worker endpoints, or any credentials.

begin;

create table if not exists generation_requests (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  created_by_user_id text not null references users(id) on delete restrict,
  provider text not null check (provider ~ '^[a-z][a-z0-9_-]{0,63}$'),
  model_id text not null,
  modality text not null check (modality in ('image', 'video')),
  input_asset_id text references media_assets(id) on delete restrict,
  spec jsonb not null default '{}'::jsonb,
  request_fingerprint char(64) not null check (request_fingerprint ~ '^[0-9a-f]{64}$'),
  idempotency_key text not null,
  status text not null default 'queued' check (status in (
    'queued', 'submitting', 'running', 'retrying', 'succeeded', 'failed',
    'cancel_requested', 'cancelled'
  )),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz,
  constraint uq_generation_request_workspace_idempotency unique (workspace_id, idempotency_key)
);
create index if not exists ix_generation_requests_workspace_created
  on generation_requests(workspace_id, created_at desc);
create index if not exists ix_generation_requests_workspace_status
  on generation_requests(workspace_id, status);

create table if not exists generation_jobs (
  id text primary key default gen_random_uuid()::text,
  generation_request_id text not null unique references generation_requests(id) on delete cascade,
  workspace_id text not null references workspaces(id) on delete restrict,
  provider text not null check (provider ~ '^[a-z][a-z0-9_-]{0,63}$'),
  status text not null default 'queued' check (status in (
    'queued', 'submitting', 'running', 'retrying', 'succeeded', 'failed',
    'cancel_requested', 'cancelled'
  )),
  attempt_count integer not null default 0 check (attempt_count >= 0),
  max_attempts integer not null default 3 check (max_attempts >= 0),
  next_attempt_at timestamptz,
  external_job_id text,
  provider_metadata jsonb not null default '{}'::jsonb,
  output_asset_id text references media_assets(id) on delete restrict,
  error_code text,
  error_message text,
  created_at timestamptz not null default now(),
  started_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz not null default now()
);
create index if not exists ix_generation_jobs_workspace_status
  on generation_jobs(workspace_id, status);
create index if not exists ix_generation_jobs_next_attempt
  on generation_jobs(status, next_attempt_at);
create index if not exists ix_generation_jobs_external
  on generation_jobs(provider, external_job_id) where external_job_id is not null;

create table if not exists generation_job_attempts (
  id text primary key default gen_random_uuid()::text,
  generation_job_id text not null references generation_jobs(id) on delete cascade,
  attempt_number integer not null check (attempt_number > 0),
  status text not null,
  error_code text,
  error_message text,
  provider_request_id text,
  started_at timestamptz not null default now(),
  completed_at timestamptz,
  constraint uq_generation_job_attempt_number unique (generation_job_id, attempt_number)
);
create index if not exists ix_generation_job_attempts_job
  on generation_job_attempts(generation_job_id, attempt_number);

-- Keep denormalised workspace IDs and canonical input/output references
-- internally consistent even when a trusted service role writes the rows.
create or replace function mediarouter_assert_generation_request_asset_workspace()
returns trigger language plpgsql as $$
declare asset_workspace text;
begin
  if new.input_asset_id is not null then
    select workspace_id into asset_workspace from media_assets where id = new.input_asset_id;
    if asset_workspace is null or asset_workspace is distinct from new.workspace_id then
      raise exception 'generation input asset must belong to its request workspace' using errcode = '23503';
    end if;
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_generation_request_asset_workspace on generation_requests;
create trigger mediarouter_generation_request_asset_workspace
before insert or update of workspace_id, input_asset_id on generation_requests
for each row execute function mediarouter_assert_generation_request_asset_workspace();

create or replace function mediarouter_assert_generation_job_workspace()
returns trigger language plpgsql as $$
declare request_workspace text;
declare asset_workspace text;
begin
  select workspace_id into request_workspace from generation_requests where id = new.generation_request_id;
  if request_workspace is null or request_workspace is distinct from new.workspace_id then
    raise exception 'generation job must belong to its request workspace' using errcode = '23503';
  end if;
  if new.output_asset_id is not null then
    select workspace_id into asset_workspace from media_assets where id = new.output_asset_id;
    if asset_workspace is null or asset_workspace is distinct from new.workspace_id then
      raise exception 'generation output asset must belong to its job workspace' using errcode = '23503';
    end if;
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_generation_job_workspace on generation_jobs;
create trigger mediarouter_generation_job_workspace
before insert or update of workspace_id, generation_request_id, output_asset_id on generation_jobs
for each row execute function mediarouter_assert_generation_job_workspace();

do $$
declare table_name text;
begin
  foreach table_name in array array['generation_requests', 'generation_jobs'] loop
    execute format('drop trigger if exists mediarouter_tenant_touch_updated_at on %I', table_name);
    execute format('create trigger mediarouter_tenant_touch_updated_at before update on %I for each row execute function mediarouter_tenant_touch_updated_at()', table_name);
  end loop;
end $$;

-- API paths set app.workspace_id and app.user_id through SecurityDatabase's
-- tenant session.  The security/service role is backend-only; these policies
-- remain defence in depth and independently verifiable with a tenant role.
alter table generation_requests enable row level security;
alter table generation_requests force row level security;
drop policy if exists generation_requests_workspace_isolation on generation_requests;
create policy generation_requests_workspace_isolation on generation_requests
  using (
    workspace_id = current_setting('app.workspace_id', true)
    and exists (
      select 1 from workspace_memberships m
      where m.workspace_id = generation_requests.workspace_id
        and m.user_id = current_setting('app.user_id', true)
        and m.status = 'active'
    )
  )
  with check (
    workspace_id = current_setting('app.workspace_id', true)
    and created_by_user_id = current_setting('app.user_id', true)
    and exists (
      select 1 from workspace_memberships m
      where m.workspace_id = generation_requests.workspace_id
        and m.user_id = current_setting('app.user_id', true)
        and m.status = 'active'
    )
  );

alter table generation_jobs enable row level security;
alter table generation_jobs force row level security;
drop policy if exists generation_jobs_workspace_isolation on generation_jobs;
create policy generation_jobs_workspace_isolation on generation_jobs
  using (
    workspace_id = current_setting('app.workspace_id', true)
    and exists (
      select 1 from workspace_memberships m
      where m.workspace_id = generation_jobs.workspace_id
        and m.user_id = current_setting('app.user_id', true)
        and m.status = 'active'
    )
  )
  with check (
    workspace_id = current_setting('app.workspace_id', true)
    and exists (
      select 1 from workspace_memberships m
      where m.workspace_id = generation_jobs.workspace_id
        and m.user_id = current_setting('app.user_id', true)
        and m.status = 'active'
    )
  );

alter table generation_job_attempts enable row level security;
alter table generation_job_attempts force row level security;
drop policy if exists generation_job_attempts_workspace_isolation on generation_job_attempts;
create policy generation_job_attempts_workspace_isolation on generation_job_attempts
  using (exists (
    select 1 from generation_jobs j
    where j.id = generation_job_attempts.generation_job_id
      and j.workspace_id = current_setting('app.workspace_id', true)
  ))
  with check (exists (
    select 1 from generation_jobs j
    where j.id = generation_job_attempts.generation_job_id
      and j.workspace_id = current_setting('app.workspace_id', true)
  ));

commit;