supabase-brand-studio-retention

-- Brand Studio — per-account retention settings.
--
-- ADDITIVE. Run AFTER docs/supabase-brand-studio-pulls.sql (needs
-- brand_studio_pulls / brand_studio_brands to exist for the feature to mean
-- anything, though this table has no FK into either — see below). Nothing
-- here alters an existing table.
--
-- The gap this closes: src/local/retention.mjs's resolveRetention() has
-- always accepted a per-account override, but no column ever stored one, so
-- every account silently ran DEFAULT_RETENTION forever. An owner decision
-- already settled that retention is CUSTOMER-CONTROLLED — this table is the
-- missing storage half of that decision. The live server reads it in
-- retentionPreviewForUser/retentionEnforceForUser (scripts/brand-studio-live-server.mjs).
--
-- One row per user, upserted by the account's own retention-settings
-- endpoint. ABSENCE of a row is a meaningful, distinct state from a row that
-- happens to hold default-shaped values: "this account never set a policy" —
-- the server falls back to DEFAULT_RETENTION from src/local/retention.mjs
-- and reports that honestly via policy_source, rather than a migration
-- inventing a row (and therefore a policy_source lie: "stored" when nobody
-- ever stored anything) for every existing account.
--
-- `null` on scores_months/content_months means "keep indefinitely" — the
-- same convention retention.mjs already uses — so both must stay nullable.
-- content_months defaults to 12 (matching DEFAULT_RETENTION) so a fresh row
-- inserted with only e.g. scores_months set does not silently start the
-- customer at "keep content forever" by column-default accident.

create extension if not exists pgcrypto;

create table if not exists public.brand_studio_retention_settings (
  user_id uuid primary key references auth.users(id) on delete cascade,

  -- Months until scores/diffs/metadata (the trend) are purged along with the
  -- whole pull. Null = kept indefinitely, matching DEFAULT_RETENTION.
  scores_months integer,
  -- Months until captured third-party content (screenshots, page text) is
  -- stripped while the pull's scores survive. Null = kept indefinitely.
  content_months integer default 12,
  -- A pinned anchor baseline is exempt from both clocks above while true —
  -- see retention.mjs's retentionActionFor(). Defaults on: an account has to
  -- explicitly opt OUT of protecting its own comparison baseline.
  anchor_exempt boolean not null default true,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),

  constraint brand_studio_retention_settings_scores_months_check
    check (scores_months is null or scores_months >= 0),
  constraint brand_studio_retention_settings_content_months_check
    check (content_months is null or content_months >= 0)
);

-- ------------------------------------------------------------------ RLS ----
alter table public.brand_studio_retention_settings enable row level security;

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

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

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

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

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

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

-- ------------------------------------------------------------ reference ----
-- Deliberately NOT a foreign key into brand_studio_brands or
-- brand_studio_pulls: a retention policy is a property of the ACCOUNT
-- (auth.users), not of any one brand or pull, and it must remain settable
-- (and readable, so a customer can be told what happens to their data) even
-- for an account that has not created a brand yet. auth.users(id) on delete
-- cascade is the only ownership tie this table needs.