app/security/migrations/0002_authoritative_tenancy_postgres.sql

-- Authoritative MediaRouter tenancy and canonical media ownership.
-- Apply after 0001 with the normal production migration process.
-- API keys remain credentials; api_key_principals binds them to a persisted
-- active membership. Do not grant these tables directly to browser clients.

begin;
create extension if not exists pgcrypto;

create table if not exists users (
  id text primary key default gen_random_uuid()::text,
  subject text not null unique,
  display_name text,
  status text not null default 'active' check (status in ('active', 'disabled')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists workspaces (
  id text primary key default gen_random_uuid()::text,
  slug text not null unique,
  name text not null,
  status text not null default 'active' check (status in ('active', 'suspended', 'disabled')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists workspace_memberships (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete cascade,
  user_id text not null references users(id) on delete cascade,
  role text not null default 'member' check (role in ('owner', 'admin', 'member', 'viewer', 'service')),
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint uq_workspace_membership unique (workspace_id, user_id)
);
create index if not exists ix_workspace_memberships_user on workspace_memberships(user_id);
create index if not exists ix_workspace_memberships_workspace on workspace_memberships(workspace_id);

create table if not exists api_key_principals (
  id text primary key default gen_random_uuid()::text,
  api_key_id text not null unique references api_keys(id) on delete cascade,
  workspace_id text not null references workspaces(id) on delete cascade,
  user_id text not null references users(id) on delete cascade,
  membership_id text not null references workspace_memberships(id) on delete restrict,
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists ix_api_key_principals_workspace on api_key_principals(workspace_id);
create index if not exists ix_api_key_principals_user on api_key_principals(user_id);

create table if not exists media_assets (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  request_id text not null,
  filename text not null,
  mime_type text not null,
  file_size bigint not null check (file_size >= 0),
  sha256 char(64) not null check (sha256 ~ '^[0-9a-f]{64}$'),
  metadata jsonb not null default '{}'::jsonb,
  created_by_user_id text references users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint uq_media_asset_output unique (request_id, filename)
);
create index if not exists ix_media_assets_workspace_created on media_assets(workspace_id, created_at desc);
create index if not exists ix_media_assets_workspace_request on media_assets(workspace_id, request_id);

create table if not exists media_asset_variants (
  id text primary key default gen_random_uuid()::text,
  workspace_id text not null references workspaces(id) on delete restrict,
  asset_id text not null references media_assets(id) on delete cascade,
  request_id text not null,
  filename text not null,
  mime_type text not null,
  file_size bigint not null check (file_size >= 0),
  sha256 char(64) not null check (sha256 ~ '^[0-9a-f]{64}$'),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  constraint uq_media_asset_variant_output unique (asset_id, request_id, filename)
);
create index if not exists ix_media_asset_variants_workspace_asset on media_asset_variants(workspace_id, asset_id);

create or replace function mediarouter_tenant_touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

do $$
declare table_name text;
begin
  foreach table_name in array array['users','workspaces','workspace_memberships','api_key_principals','media_assets'] 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 $$;

-- A tenant API connection must set both values transaction-locally. Backend
-- authentication/administration and service workers use separate credentials,
-- never an exposed browser or SDK connection.
alter table users enable row level security;
alter table users force row level security;
drop policy if exists users_self on users;
create policy users_self on users
  using (id = current_setting('app.user_id', true))
  with check (id = current_setting('app.user_id', true));

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

alter table workspace_memberships enable row level security;
alter table workspace_memberships force row level security;
drop policy if exists workspace_membership_self on workspace_memberships;
create policy workspace_membership_self on workspace_memberships
  using (workspace_id = current_setting('app.workspace_id', true)
    and user_id = current_setting('app.user_id', true))
  with check (false);

alter table api_key_principals enable row level security;
alter table api_key_principals force row level security;
drop policy if exists api_key_principal_self on api_key_principals;
create policy api_key_principal_self on api_key_principals
  using (workspace_id = current_setting('app.workspace_id', true)
    and user_id = current_setting('app.user_id', true))
  with check (false);

do $$
declare table_name text;
begin
  foreach table_name in array array['media_assets','media_asset_variants'] loop
    execute format('alter table %I enable row level security', table_name);
    execute format('alter table %I force row level security', table_name);
    execute format('drop policy if exists canonical_asset_workspace_isolation on %I', table_name);
    execute format(
      'create policy canonical_asset_workspace_isolation on %I using (workspace_id = current_setting(''app.workspace_id'', true)) with check (workspace_id = current_setting(''app.workspace_id'', true))',
      table_name
    );
  end loop;
end $$;

create or replace function mediarouter_assert_canonical_variant_workspace()
returns trigger language plpgsql as $$
declare asset_workspace text;
begin
  select workspace_id into asset_workspace from media_assets where id = new.asset_id;
  if asset_workspace is null or asset_workspace is distinct from new.workspace_id then
    raise exception 'media variant must belong to its source asset workspace' using errcode = '23503';
  end if;
  return new;
end;
$$;
drop trigger if exists mediarouter_canonical_variant_workspace on media_asset_variants;
create trigger mediarouter_canonical_variant_workspace
before insert or update of workspace_id, asset_id on media_asset_variants
for each row execute function mediarouter_assert_canonical_variant_workspace();

commit;