supabase-brand-studio-auth

-- Brand Studio / Brand MD auth-backed run history.
-- Run this in the Supabase SQL editor for the project used by brand.mirrorfactory.ai.

create extension if not exists pgcrypto;

create table if not exists public.brand_studio_runs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  run_id text not null,
  source_url text not null default '',
  domain text not null default '',
  title text not null default '',
  status text not null default 'running',
  elapsed_ms integer not null default 0,
  summary jsonb not null default '{}'::jsonb,
  result_path text not null default '',
  trace_path text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, run_id)
);

alter table public.brand_studio_runs
  add column if not exists brand_key text not null default '';

alter table public.brand_studio_runs
  add column if not exists signals jsonb not null default '{}'::jsonb;

create index if not exists brand_studio_runs_user_updated_idx
  on public.brand_studio_runs (user_id, updated_at desc);

create index if not exists brand_studio_runs_user_status_idx
  on public.brand_studio_runs (user_id, status);

create index if not exists brand_studio_runs_user_brand_updated_idx
  on public.brand_studio_runs (user_id, brand_key, updated_at desc);

alter table public.brand_studio_runs enable row level security;

drop policy if exists brand_studio_runs_select_own on public.brand_studio_runs;
create policy brand_studio_runs_select_own
  on public.brand_studio_runs
  for select
  using (auth.uid() = user_id);

drop policy if exists brand_studio_runs_insert_own on public.brand_studio_runs;
create policy brand_studio_runs_insert_own
  on public.brand_studio_runs
  for insert
  with check (auth.uid() = user_id);

drop policy if exists brand_studio_runs_update_own on public.brand_studio_runs;
create policy brand_studio_runs_update_own
  on public.brand_studio_runs
  for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists brand_studio_runs_delete_own on public.brand_studio_runs;
create policy brand_studio_runs_delete_own
  on public.brand_studio_runs
  for delete
  using (auth.uid() = user_id);

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

drop trigger if exists set_brand_studio_runs_updated_at on public.brand_studio_runs;
create trigger set_brand_studio_runs_updated_at
  before update on public.brand_studio_runs
  for each row
  execute function public.set_brand_studio_runs_updated_at();