app/projects/migrations/0001_projects_foundation.sql

-- Authoritative workspace-owned Project foundation.
-- Apply after app/security/migrations/0002_authoritative_tenancy_postgres.sql.
-- IDs remain UUID values stored as text to match MediaRouter's existing
-- tenancy and canonical-asset foreign-key types.

begin;
create extension if not exists pgcrypto;

create table if not exists projects (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  created_by text not null references users(id) on delete restrict,
  name text not null check (char_length(name) between 1 and 200),
  description text check (description is null or char_length(description) <= 4000),
  status text not null default 'active' check (status in ('active', 'archived')),
  thumbnail_asset_id text references media_assets(id) on delete set null,
  metadata jsonb not null default '{}'::jsonb
    check (jsonb_typeof(metadata) = 'object'),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  archived_at timestamptz,
  constraint ck_projects_archive_timestamp check (
    (status = 'active' and archived_at is null)
    or (status = 'archived' and archived_at is not null)
  )
);
create index if not exists ix_projects_workspace on projects(workspace_id);
create index if not exists ix_projects_workspace_status on projects(workspace_id, status);
create index if not exists ix_projects_workspace_updated on projects(workspace_id, updated_at desc);
create index if not exists ix_projects_created_by on projects(created_by);

-- The shared AuditService owns this generic event table. It is deliberately
-- not project-specific so future domains can reuse the same safe boundary.
create table if not exists audit_events (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  actor_user_id text references users(id) on delete set null,
  api_key_id text references api_keys(id) on delete set null,
  event_type text not null check (char_length(event_type) between 1 and 100),
  entity_type text not null check (char_length(entity_type) between 1 and 64),
  entity_id text not null,
  request_id text,
  metadata jsonb not null default '{}'::jsonb check (jsonb_typeof(metadata) = 'object'),
  created_at timestamptz not null default now()
);
create index if not exists ix_audit_events_workspace_created
  on audit_events(workspace_id, created_at desc);
create index if not exists ix_audit_events_type on audit_events(event_type);
create index if not exists ix_audit_events_entity on audit_events(entity_type, entity_id);

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

create or replace function mediarouter_assert_project_ownership()
returns trigger language plpgsql as $$
declare asset_workspace text;
begin
  if tg_op = 'UPDATE' then
    if new.workspace_id is distinct from old.workspace_id
       or new.created_by is distinct from old.created_by then
      raise exception 'project ownership fields are immutable' using errcode = '23514';
    end if;
  end if;
  if new.thumbnail_asset_id is not null then
    select workspace_id into asset_workspace from media_assets
      where id = new.thumbnail_asset_id;
    if asset_workspace is null or asset_workspace is distinct from new.workspace_id then
      raise exception 'project thumbnail must belong to its workspace' using errcode = '23503';
    end if;
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_project_ownership on projects;
create trigger mediarouter_project_ownership
before insert or update of workspace_id, created_by, thumbnail_asset_id on projects
for each row execute function mediarouter_assert_project_ownership();

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

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

drop policy if exists projects_insert on projects;
create policy projects_insert on projects for insert with check (
  workspace_id = current_setting('app.workspace_id', true)
  and created_by = current_setting('app.user_id', true)
  and exists (
    select 1 from workspace_memberships m
    where m.workspace_id = projects.workspace_id
      and m.user_id = current_setting('app.user_id', true)
      and m.status = 'active'
  )
);

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

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

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

commit;