20260801000000_initial_growthloop.sql

-- GrowthLoop core schema for PostgreSQL 15+ / Supabase.
-- Apply with `supabase db reset` locally or `supabase db push` remotely.

create extension if not exists pgcrypto;

create table public.campaigns (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid references auth.users(id) on delete cascade,
  slug text not null unique check (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
  name text not null check (char_length(name) between 1 and 120),
  description text,
  headline text,
  status text not null default 'draft' check (status in ('draft', 'active', 'paused', 'ended')),
  destination_url text not null,
  reward_config jsonb not null default '{}'::jsonb check (jsonb_typeof(reward_config) = 'object'),
  starts_at timestamptz,
  ends_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (ends_at is null or starts_at is null or ends_at > starts_at)
);

create table public.visitors (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  anonymous_id text not null check (char_length(anonymous_id) between 1 and 200),
  external_user_id text,
  email text,
  first_seen_at timestamptz not null default now(),
  last_seen_at timestamptz not null default now(),
  first_touch jsonb not null default '{}'::jsonb check (jsonb_typeof(first_touch) = 'object'),
  last_touch jsonb not null default '{}'::jsonb check (jsonb_typeof(last_touch) = 'object'),
  properties jsonb not null default '{}'::jsonb check (jsonb_typeof(properties) = 'object'),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (campaign_id, anonymous_id),
  unique (id, campaign_id)
);

create table public.participants (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  visitor_id uuid not null,
  referral_code text not null check (referral_code ~ '^[A-Za-z0-9_-]{4,64}$'),
  display_name text,
  email text,
  status text not null default 'active' check (status in ('pending', 'active', 'blocked')),
  referral_count integer not null default 0 check (referral_count >= 0),
  conversion_count integer not null default 0 check (conversion_count >= 0),
  reward_total numeric(14, 2) not null default 0 check (reward_total >= 0),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  foreign key (visitor_id, campaign_id) references public.visitors(id, campaign_id) on delete cascade,
  unique (campaign_id, visitor_id),
  unique (campaign_id, referral_code),
  unique (id, campaign_id)
);

create unique index participants_campaign_email_unique
  on public.participants (campaign_id, lower(email)) where email is not null;

create table public.tracking_links (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  participant_id uuid,
  code text not null unique check (code ~ '^[A-Za-z0-9_-]{4,96}$'),
  destination_url text not null,
  source text,
  medium text,
  campaign_term text,
  content text,
  click_count integer not null default 0 check (click_count >= 0),
  is_active boolean not null default true,
  expires_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  foreign key (participant_id, campaign_id) references public.participants(id, campaign_id) on delete cascade,
  unique (id, campaign_id)
);

create table public.events (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  visitor_id uuid not null,
  tracking_link_id uuid,
  event_name text not null check (char_length(event_name) between 1 and 80),
  session_id text,
  idempotency_key text,
  page_url text,
  referrer_url text,
  properties jsonb not null default '{}'::jsonb check (jsonb_typeof(properties) = 'object'),
  occurred_at timestamptz not null default now(),
  received_at timestamptz not null default now(),
  foreign key (visitor_id, campaign_id) references public.visitors(id, campaign_id) on delete cascade,
  foreign key (tracking_link_id, campaign_id) references public.tracking_links(id, campaign_id),
  unique (id, campaign_id)
);

create unique index events_campaign_idempotency_unique
  on public.events (campaign_id, idempotency_key) where idempotency_key is not null;

create table public.conversions (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  visitor_id uuid not null,
  event_id uuid,
  external_id text,
  conversion_type text not null default 'signup',
  status text not null default 'confirmed' check (status in ('pending', 'confirmed', 'rejected', 'refunded')),
  value numeric(14, 2) not null default 0 check (value >= 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  first_touch_link_id uuid,
  last_touch_link_id uuid,
  properties jsonb not null default '{}'::jsonb check (jsonb_typeof(properties) = 'object'),
  converted_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  foreign key (visitor_id, campaign_id) references public.visitors(id, campaign_id) on delete cascade,
  foreign key (event_id, campaign_id) references public.events(id, campaign_id),
  foreign key (first_touch_link_id, campaign_id) references public.tracking_links(id, campaign_id),
  foreign key (last_touch_link_id, campaign_id) references public.tracking_links(id, campaign_id),
  unique (id, campaign_id)
);

create unique index conversions_campaign_external_unique
  on public.conversions (campaign_id, external_id) where external_id is not null;

create table public.referrals (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  referrer_participant_id uuid not null,
  referred_visitor_id uuid not null,
  referred_participant_id uuid,
  tracking_link_id uuid,
  conversion_id uuid,
  status text not null default 'invited' check (status in ('invited', 'qualified', 'rewarded', 'rejected')),
  reward_amount numeric(14, 2) not null default 0 check (reward_amount >= 0),
  rewarded_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  foreign key (referrer_participant_id, campaign_id) references public.participants(id, campaign_id) on delete cascade,
  foreign key (referred_visitor_id, campaign_id) references public.visitors(id, campaign_id) on delete cascade,
  foreign key (referred_participant_id, campaign_id) references public.participants(id, campaign_id),
  foreign key (tracking_link_id, campaign_id) references public.tracking_links(id, campaign_id),
  foreign key (conversion_id, campaign_id) references public.conversions(id, campaign_id),
  unique (campaign_id, referred_visitor_id),
  check ((status = 'rewarded') = (rewarded_at is not null))
);

create table public.creative_assets (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  name text not null,
  asset_type text not null check (asset_type in ('image', 'video', 'copy', 'email', 'social', 'other')),
  status text not null default 'ready' check (status in ('draft', 'generating', 'ready', 'failed', 'archived')),
  public_url text,
  storage_path text,
  mime_type text,
  prompt text,
  content text,
  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(),
  check (public_url is not null or storage_path is not null or content is not null)
);

create index campaigns_owner_status_idx on public.campaigns (owner_id, status);
create index visitors_campaign_last_seen_idx on public.visitors (campaign_id, last_seen_at desc);
create index visitors_campaign_external_idx on public.visitors (campaign_id, external_user_id) where external_user_id is not null;
create index participants_campaign_created_idx on public.participants (campaign_id, created_at desc);
create index tracking_links_campaign_participant_idx on public.tracking_links (campaign_id, participant_id);
create index events_campaign_occurred_idx on public.events (campaign_id, occurred_at desc);
create index events_visitor_occurred_idx on public.events (visitor_id, occurred_at desc);
create index events_campaign_name_idx on public.events (campaign_id, event_name, occurred_at desc);
create index conversions_campaign_converted_idx on public.conversions (campaign_id, converted_at desc);
create index conversions_visitor_idx on public.conversions (visitor_id);
create index referrals_referrer_status_idx on public.referrals (referrer_participant_id, status);
create index creative_assets_campaign_created_idx on public.creative_assets (campaign_id, created_at desc);

create or replace function public.set_updated_at()
returns trigger
language plpgsql
set search_path = public, pg_temp
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create or replace function public.validate_referral()
returns trigger
language plpgsql
set search_path = public, pg_temp
as $$
declare
  referrer_visitor uuid;
begin
  select visitor_id into referrer_visitor
  from public.participants
  where id = new.referrer_participant_id and campaign_id = new.campaign_id;

  if referrer_visitor is null then
    raise exception 'referrer does not belong to campaign';
  end if;
  if referrer_visitor = new.referred_visitor_id then
    raise exception 'self-referrals are not allowed';
  end if;
  if new.referred_participant_id = new.referrer_participant_id then
    raise exception 'a participant cannot refer themselves';
  end if;
  return new;
end;
$$;

create or replace function public.award_referral_reward(p_referral_id uuid, p_amount numeric)
returns public.referrals
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  awarded public.referrals;
begin
  if p_amount < 0 then
    raise exception 'reward amount cannot be negative';
  end if;

  update public.referrals
  set status = 'rewarded', reward_amount = p_amount, rewarded_at = now()
  where id = p_referral_id and status = 'qualified' and rewarded_at is null
  returning * into awarded;

  if awarded.id is null then
    raise exception 'referral is missing, already rewarded, or not qualified';
  end if;

  update public.participants
  set reward_total = reward_total + p_amount,
      referral_count = referral_count + 1
  where id = awarded.referrer_participant_id;

  return awarded;
end;
$$;

create or replace function public.track_event(
  p_campaign_slug text,
  p_anonymous_id text,
  p_event_name text,
  p_properties jsonb default '{}'::jsonb,
  p_tracking_code text default null,
  p_session_id text default null,
  p_page_url text default null,
  p_referrer_url text default null,
  p_idempotency_key text default null,
  p_occurred_at timestamptz default now()
)
returns uuid
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  target_campaign uuid;
  target_visitor uuid;
  target_link uuid;
  event_id uuid;
  touch jsonb;
begin
  if char_length(p_anonymous_id) not between 1 and 200 or char_length(p_event_name) not between 1 and 80 then
    raise exception 'invalid anonymous id or event name';
  end if;

  select id into target_campaign
  from public.campaigns
  where slug = p_campaign_slug
    and status = 'active'
    and (starts_at is null or starts_at <= now())
    and (ends_at is null or ends_at > now());
  if target_campaign is null then
    raise exception 'campaign is not active';
  end if;

  if p_idempotency_key is not null then
    select id into event_id from public.events
    where campaign_id = target_campaign and idempotency_key = p_idempotency_key;
    if event_id is not null then
      return event_id;
    end if;
  end if;

  if p_tracking_code is not null then
    select id into target_link
    from public.tracking_links
    where campaign_id = target_campaign and code = p_tracking_code and is_active
      and (expires_at is null or expires_at > now());
  end if;

  touch := jsonb_strip_nulls(jsonb_build_object(
    'tracking_link_id', target_link,
    'tracking_code', p_tracking_code,
    'page_url', p_page_url,
    'referrer_url', p_referrer_url,
    'captured_at', p_occurred_at
  ));

  insert into public.visitors (
    campaign_id, anonymous_id, first_seen_at, last_seen_at, first_touch, last_touch
  ) values (
    target_campaign, p_anonymous_id, p_occurred_at, p_occurred_at, touch, touch
  )
  on conflict (campaign_id, anonymous_id) do update
    set first_seen_at = least(public.visitors.first_seen_at, excluded.first_seen_at),
        first_touch = case when excluded.first_seen_at < public.visitors.first_seen_at
          then excluded.first_touch else public.visitors.first_touch end,
        last_seen_at = greatest(public.visitors.last_seen_at, excluded.last_seen_at),
        last_touch = case when excluded.last_seen_at >= public.visitors.last_seen_at
          then excluded.last_touch else public.visitors.last_touch end
  returning id into target_visitor;

  insert into public.events (
    campaign_id, visitor_id, tracking_link_id, event_name, session_id,
    idempotency_key, page_url, referrer_url, properties, occurred_at
  ) values (
    target_campaign, target_visitor, target_link, p_event_name, p_session_id,
    p_idempotency_key, p_page_url, p_referrer_url, coalesce(p_properties, '{}'::jsonb), p_occurred_at
  )
  on conflict (campaign_id, idempotency_key) where idempotency_key is not null do nothing
  returning id into event_id;

  -- A concurrent request may have won the idempotency race.
  if event_id is null then
    select id into event_id from public.events
    where campaign_id = target_campaign and idempotency_key = p_idempotency_key;
    return event_id;
  end if;

  if target_link is not null and p_event_name = 'link_click' then
    update public.tracking_links set click_count = click_count + 1 where id = target_link;
  end if;

  return event_id;
end;
$$;

create trigger campaigns_set_updated_at before update on public.campaigns
  for each row execute function public.set_updated_at();
create trigger visitors_set_updated_at before update on public.visitors
  for each row execute function public.set_updated_at();
create trigger participants_set_updated_at before update on public.participants
  for each row execute function public.set_updated_at();
create trigger tracking_links_set_updated_at before update on public.tracking_links
  for each row execute function public.set_updated_at();
create trigger conversions_set_updated_at before update on public.conversions
  for each row execute function public.set_updated_at();
create trigger referrals_set_updated_at before update on public.referrals
  for each row execute function public.set_updated_at();
create trigger creative_assets_set_updated_at before update on public.creative_assets
  for each row execute function public.set_updated_at();
create trigger referrals_validate before insert or update on public.referrals
  for each row execute function public.validate_referral();

alter table public.campaigns enable row level security;
alter table public.visitors enable row level security;
alter table public.participants enable row level security;
alter table public.tracking_links enable row level security;
alter table public.events enable row level security;
alter table public.conversions enable row level security;
alter table public.referrals enable row level security;
alter table public.creative_assets enable row level security;

create policy campaigns_public_active_read on public.campaigns for select
  using (status = 'active' and (starts_at is null or starts_at <= now()) and (ends_at is null or ends_at > now()));
create policy campaigns_owner_all on public.campaigns for all to authenticated
  using (owner_id = (select auth.uid())) with check (owner_id = (select auth.uid()));

create policy visitors_owner_all on public.visitors for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy participants_owner_all on public.participants for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy tracking_links_owner_all on public.tracking_links for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy events_owner_all on public.events for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy conversions_owner_all on public.conversions for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy referrals_owner_all on public.referrals for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));
create policy creative_assets_owner_all on public.creative_assets for all to authenticated
  using (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())))
  with check (exists (select 1 from public.campaigns c where c.id = campaign_id and c.owner_id = (select auth.uid())));

revoke all on all tables in schema public from anon;
revoke all on function public.award_referral_reward(uuid, numeric) from public, anon, authenticated;
grant execute on function public.award_referral_reward(uuid, numeric) to service_role;
revoke all on function public.track_event(text, text, text, jsonb, text, text, text, text, text, timestamptz) from public;
grant execute on function public.track_event(text, text, text, jsonb, text, text, text, text, text, timestamptz) to anon, authenticated, service_role;
grant select on public.campaigns to anon, authenticated;
grant select, insert, update, delete on all tables in schema public to authenticated;

comment on function public.track_event is 'Public ingestion boundary. Resolves an active campaign, preserves first touch, updates last touch, and deduplicates by campaign/idempotency key.';
comment on function public.award_referral_reward is 'Atomically awards a qualified referral once and increments the referrer totals.';