app/projects/migrations/0005_ai_copilot.sql

-- AI Copilot: bounded durable plans and action results.
-- Apply after projects/0004_ai_studio.sql. Production migration remains explicit.

begin;

create table if not exists copilot_runs (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  user_id text not null references users(id) on delete restrict,
  project_id text references projects(id) on delete restrict,
  idempotency_key text not null check (char_length(idempotency_key) between 1 and 255),
  request_fingerprint text not null check (char_length(request_fingerprint) = 64),
  request_text text not null check (char_length(request_text) between 1 and 4000),
  context jsonb not null check (jsonb_typeof(context) = 'object'),
  plan jsonb not null check (jsonb_typeof(plan) = 'object'),
  status text not null check (
    status in ('plan_ready','blocked','executing','completed','partial','failed','cancelled')
  ),
  current_action_id text,
  results jsonb not null default '[]'::jsonb check (jsonb_typeof(results) = 'array'),
  summary text,
  error_code text,
  error_message text,
  confirmed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz,
  constraint uq_copilot_run_workspace_idempotency unique (workspace_id, idempotency_key)
);

create index if not exists ix_copilot_runs_workspace_created
  on copilot_runs(workspace_id, created_at desc);
create index if not exists ix_copilot_runs_workspace_status
  on copilot_runs(workspace_id, status);
create index if not exists ix_copilot_runs_project_created
  on copilot_runs(project_id, created_at desc);

drop trigger if exists mediarouter_copilot_touch_updated_at on copilot_runs;
create trigger mediarouter_copilot_touch_updated_at
before update on copilot_runs
for each row execute function mediarouter_tenant_touch_updated_at();

create or replace function mediarouter_assert_copilot_run_ownership()
returns trigger language plpgsql as $$
declare project_workspace text;
begin
  if tg_op = 'UPDATE' and (
    new.workspace_id is distinct from old.workspace_id
    or new.user_id is distinct from old.user_id
    or new.project_id is distinct from old.project_id
    or new.idempotency_key is distinct from old.idempotency_key
    or new.request_fingerprint is distinct from old.request_fingerprint
    or new.request_text is distinct from old.request_text
    or new.context is distinct from old.context
    or new.plan is distinct from old.plan
    or new.created_at is distinct from old.created_at
  ) then
    raise exception 'copilot run identity fields are immutable' using errcode = '23514';
  end if;
  if new.context->>'workspace_id' is distinct from new.workspace_id then
    raise exception 'copilot context workspace does not match its run' using errcode = '23514';
  end if;
  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 'copilot project must belong to its workspace' using errcode = '23503';
    end if;
  end if;
  if not exists (
    select 1 from workspace_memberships m
    where m.workspace_id = new.workspace_id
      and m.user_id = new.user_id
      and m.status = 'active'
  ) then
    raise exception 'copilot run requires active workspace membership' using errcode = '23503';
  end if;
  if new.status in ('completed','partial','failed','cancelled') and new.completed_at is null then
    raise exception 'terminal copilot runs require completed_at' using errcode = '23514';
  end if;
  return new;
end;
$$;

drop trigger if exists mediarouter_copilot_run_ownership on copilot_runs;
create trigger mediarouter_copilot_run_ownership
before insert or update on copilot_runs
for each row execute function mediarouter_assert_copilot_run_ownership();

alter table copilot_runs enable row level security;
alter table copilot_runs force row level security;

drop policy if exists copilot_runs_select on copilot_runs;
create policy copilot_runs_select on copilot_runs for select using (
  workspace_id = current_setting('app.workspace_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = copilot_runs.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
  )
);

drop policy if exists copilot_runs_insert on copilot_runs;
create policy copilot_runs_insert on copilot_runs for insert with check (
  workspace_id = current_setting('app.workspace_id', true)
  and user_id = current_setting('app.user_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = copilot_runs.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
      and m.role <> 'viewer'
  )
);

drop policy if exists copilot_runs_update on copilot_runs;
create policy copilot_runs_update on copilot_runs for update using (
  workspace_id = current_setting('app.workspace_id', true)
  and user_id = current_setting('app.user_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = copilot_runs.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 user_id = current_setting('app.user_id', true)
);

commit;