app/projects/migrations/0003_editor_persistence_rendering.sql

-- Content Studio Phase 2: authoritative editor state and project render jobs.
-- Apply after projects/0001_projects_foundation.sql, projects/0002_project_resources.sql,
-- and security generation migrations.  Production migration remains explicit.

begin;

create table if not exists project_editor_states (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  project_id text not null references projects(id) on delete restrict,
  revision integer not null default 1 check (revision > 0),
  schema_version integer not null check (schema_version = 1),
  state jsonb not null check (jsonb_typeof(state) = 'object'),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  updated_by text not null references users(id) on delete restrict,
  constraint uq_project_editor_state_project unique (project_id)
);
create index if not exists ix_project_editor_states_workspace_project
  on project_editor_states(workspace_id, project_id);

create table if not exists project_render_jobs (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  project_id text not null references projects(id) on delete restrict,
  editor_revision integer not null check (editor_revision > 0),
  editor_schema_version integer not null check (editor_schema_version = 1),
  editor_state jsonb not null check (jsonb_typeof(editor_state) = 'object'),
  render_settings jsonb not null check (jsonb_typeof(render_settings) = 'object'),
  request_fingerprint text not null check (char_length(request_fingerprint) = 64),
  idempotency_key text not null check (char_length(idempotency_key) between 1 and 255),
  requested_by text not null references users(id) on delete restrict,
  status text not null default 'queued'
    check (status in ('queued', 'processing', 'completed', 'failed', 'cancelling', 'cancelled')),
  attempt_count integer not null default 0 check (attempt_count >= 0),
  max_attempts integer not null default 3 check (max_attempts between 1 and 10),
  next_attempt_at timestamptz,
  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,
  cancelled_at timestamptz,
  updated_at timestamptz not null default now(),
  constraint uq_project_render_idempotency unique (project_id, editor_revision, idempotency_key)
);
create index if not exists ix_project_render_jobs_workspace_status
  on project_render_jobs(workspace_id, status);
create index if not exists ix_project_render_jobs_project_created
  on project_render_jobs(project_id, created_at desc);
create index if not exists ix_project_render_jobs_dispatch
  on project_render_jobs(status, next_attempt_at);

drop trigger if exists mediarouter_project_editor_touch_updated_at on project_editor_states;
create trigger mediarouter_project_editor_touch_updated_at
before update on project_editor_states
for each row execute function mediarouter_tenant_touch_updated_at();
drop trigger if exists mediarouter_project_render_touch_updated_at on project_render_jobs;
create trigger mediarouter_project_render_touch_updated_at
before update on project_render_jobs
for each row execute function mediarouter_tenant_touch_updated_at();

create or replace function mediarouter_assert_project_editor_ownership()
returns trigger language plpgsql as $$
declare project_workspace text;
declare project_status text;
begin
  if tg_op = 'INSERT' and new.revision <> 1 then
    raise exception 'initial editor revision must be one' using errcode = '23514';
  end if;
  if tg_op = 'UPDATE' and (
    new.workspace_id is distinct from old.workspace_id
    or new.project_id is distinct from old.project_id
    or new.created_at is distinct from old.created_at
  ) then
    raise exception 'project editor ownership fields are immutable' using errcode = '23514';
  end if;
  if tg_op = 'UPDATE' and new.revision <> old.revision + 1 then
    raise exception 'editor revision must increment exactly once' using errcode = '23514';
  end if;
  if new.state->>'projectId' is distinct from new.project_id
     or new.state->>'schemaVersion' is distinct from new.schema_version::text then
    raise exception 'editor state identity does not match its row' using errcode = '23514';
  end if;
  select workspace_id, status into project_workspace, project_status from projects where id = new.project_id;
  if project_workspace is null or project_workspace is distinct from new.workspace_id then
    raise exception 'editor state must belong to its project workspace' using errcode = '23503';
  end if;
  if project_status is distinct from 'active' then
    raise exception 'archived projects cannot change editor state' using errcode = '23514';
  end if;
  if not exists (
    select 1 from workspace_memberships m
    where m.workspace_id = new.workspace_id and m.user_id = new.updated_by and m.status = 'active'
  ) then
    raise exception 'editor state update requires active workspace membership' using errcode = '23503';
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_project_editor_ownership on project_editor_states;
create trigger mediarouter_project_editor_ownership
before insert or update on project_editor_states
for each row execute function mediarouter_assert_project_editor_ownership();

create or replace function mediarouter_assert_project_render_ownership()
returns trigger language plpgsql as $$
declare project_workspace text;
declare project_status text;
declare output_workspace text;
declare output_project text;
begin
  if tg_op = 'UPDATE' and (
    new.workspace_id is distinct from old.workspace_id
    or new.project_id is distinct from old.project_id
    or new.editor_revision is distinct from old.editor_revision
    or new.editor_schema_version is distinct from old.editor_schema_version
    or new.editor_state is distinct from old.editor_state
    or new.render_settings is distinct from old.render_settings
    or new.request_fingerprint is distinct from old.request_fingerprint
    or new.idempotency_key is distinct from old.idempotency_key
    or new.requested_by is distinct from old.requested_by
    or new.max_attempts is distinct from old.max_attempts
    or new.created_at is distinct from old.created_at
  ) then
    raise exception 'render identity fields are immutable' using errcode = '23514';
  end if;
  if new.editor_state->>'projectId' is distinct from new.project_id
     or new.editor_state->>'schemaVersion' is distinct from new.editor_schema_version::text then
    raise exception 'render editor snapshot identity does not match its row' using errcode = '23514';
  end if;
  if tg_op = 'UPDATE' and new.status is distinct from old.status and not (
    (old.status = 'queued' and new.status in ('processing', 'cancelled', 'failed'))
    or (old.status = 'processing' and new.status in ('queued', 'completed', 'failed', 'cancelling', 'cancelled'))
    or (old.status = 'cancelling' and new.status in ('completed', 'failed', 'cancelled'))
  ) then
    raise exception 'invalid render status transition' using errcode = '23514';
  end if;
  select workspace_id, status into project_workspace, project_status from projects where id = new.project_id;
  if project_workspace is null or project_workspace is distinct from new.workspace_id then
    raise exception 'render must belong to its project workspace' using errcode = '23503';
  end if;
  if tg_op = 'INSERT' and project_status is distinct from 'active' then
    raise exception 'archived projects cannot be rendered' using errcode = '23514';
  end if;
  if new.output_asset_id is not null then
    select workspace_id, project_id into output_workspace, output_project
      from media_assets where id = new.output_asset_id;
    if output_workspace is null
       or output_workspace is distinct from new.workspace_id
       or output_project is distinct from new.project_id then
      raise exception 'render output must belong to its project workspace' using errcode = '23503';
    end if;
  end if;
  if new.status = 'completed' and (new.output_asset_id is null or new.completed_at is null) then
    raise exception 'completed render requires an output asset and timestamp' using errcode = '23514';
  end if;
  if new.status = 'cancelled' and (new.cancelled_at is null or new.completed_at is null) then
    raise exception 'cancelled render requires cancellation timestamps' using errcode = '23514';
  end if;
  if new.status = 'failed' and new.completed_at is null then
    raise exception 'failed render requires a completion timestamp' using errcode = '23514';
  end if;
  if new.status in ('queued', 'processing', 'cancelling') and new.output_asset_id is not null then
    raise exception 'active render cannot expose an output asset' using errcode = '23514';
  end if;
  if tg_op = 'INSERT' and not exists (
    select 1 from workspace_memberships m
    where m.workspace_id = new.workspace_id and m.user_id = new.requested_by and m.status = 'active'
  ) then
    raise exception 'render requires active workspace membership' using errcode = '23503';
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_project_render_ownership on project_render_jobs;
create trigger mediarouter_project_render_ownership
before insert or update on project_render_jobs
for each row execute function mediarouter_assert_project_render_ownership();

alter table project_editor_states enable row level security;
alter table project_editor_states force row level security;
drop policy if exists project_editor_states_select on project_editor_states;
create policy project_editor_states_select on project_editor_states for select using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_editor_states.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active')
);
drop policy if exists project_editor_states_insert on project_editor_states;
create policy project_editor_states_insert on project_editor_states for insert with check (
  workspace_id = current_setting('app.workspace_id', true)
  and updated_by = current_setting('app.user_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_editor_states.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
);
drop policy if exists project_editor_states_update on project_editor_states;
create policy project_editor_states_update on project_editor_states for update using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_editor_states.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
) with check (
  workspace_id = current_setting('app.workspace_id', true)
  and updated_by = current_setting('app.user_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_editor_states.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
);

alter table project_render_jobs enable row level security;
alter table project_render_jobs force row level security;
drop policy if exists project_render_jobs_select on project_render_jobs;
create policy project_render_jobs_select on project_render_jobs for select using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_render_jobs.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active')
);
drop policy if exists project_render_jobs_insert on project_render_jobs;
create policy project_render_jobs_insert on project_render_jobs for insert with check (
  workspace_id = current_setting('app.workspace_id', true)
  and requested_by = current_setting('app.user_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_render_jobs.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
);
drop policy if exists project_render_jobs_update on project_render_jobs;
create policy project_render_jobs_update on project_render_jobs for update using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_render_jobs.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
) with check (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (select 1 from workspace_memberships m where m.workspace_id = project_render_jobs.workspace_id
    and m.user_id = current_setting('app.user_id', true) and m.status = 'active' and m.role <> 'viewer')
);

commit;