schema.sql

-- Augur — Supabase schema (for the bundled SupabaseStore). Run once against your
-- project. No foreign keys into your tables; user_id is a plain uuid. To remove
-- Augur entirely: drop these tables and the functions, delete the augur/ folder.
--
-- Not using Supabase? Ignore this file and write a store to the AugurStore
-- interface (see augur/store.ts) against whatever backend you have.

-- ── tables ───────────────────────────────────────────────────────────────────
create table if not exists augur_events (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null,          -- plain, NO fk
  trigger_id    text not null,
  trigger_ver   int  not null,
  outcome       text not null check (outcome in ('shown', 'ignored', 'answered')),
  answer        text,                   -- choice3 key, custom choice key, or null
  shown_at      timestamptz not null default now(),
  answered_at   timestamptz,
  category      text,                   -- button submissions: the chosen category, or null
  context       jsonb                   -- button submissions: diagnostic context (places, never words)
);
create index if not exists augur_events_user_idx on augur_events (user_id, shown_at desc);
create index if not exists augur_events_trigger_idx on augur_events (trigger_id, shown_at desc);
create index if not exists augur_events_shown_idx on augur_events (shown_at);   -- admin's date-range counts

create table if not exists augur_notes (
  id         uuid primary key default gen_random_uuid(),
  event_id   uuid not null references augur_events(id) on delete cascade,
  body       text not null,
  read_at    timestamptz,
  created_at timestamptz not null default now()
);
create index if not exists augur_notes_created_idx on augur_notes (created_at desc);

create table if not exists augur_config (
  id         int primary key default 1,
  overrides  jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now()
);
insert into augur_config (id, overrides) values (1, '{}'::jsonb) on conflict (id) do nothing;

-- An emit() with no config entry: recorded, shown to nobody, surfaced in admin.
create table if not exists augur_unconfigured (
  id         uuid primary key default gen_random_uuid(),
  trigger_id text not null,
  user_id    uuid not null,
  seen_at    timestamptz not null default now()
);
create index if not exists augur_unconfigured_trigger_idx on augur_unconfigured (trigger_id, seen_at desc);

-- Friction (0.4): repeated presses on an element the host labelled data-augur="…".
-- Label and page path only; never element text or anything typed.
create table if not exists augur_friction (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid not null,
  label      text not null,
  route      text not null default '',
  clicks     int  not null,
  created_at timestamptz not null default now()
);
create index if not exists augur_friction_label_idx on augur_friction (label, created_at desc);

-- ── admin identity ───────────────────────────────────────────────────────────
-- The allowlist. Add the auth uuid(s) of whoever may read results and write config.
-- Empty = nobody is admin (collection still works; admin reads return null/[]).
create or replace function augur_is_admin() returns boolean
  language sql stable security definer set search_path = public, pg_temp as $$
  select auth.uid() = any (array[
    -- '00000000-0000-0000-0000-000000000000'   -- ← your admin account's auth uuid
  ]::uuid[]);
$$;

-- ── RLS ──────────────────────────────────────────────────────────────────────
alter table augur_events       enable row level security;
alter table augur_notes        enable row level security;
alter table augur_config       enable row level security;
alter table augur_unconfigured enable row level security;
alter table augur_friction     enable row level security;   -- no client policies: writes and reads go through the functions below

-- events: write only your own rows; no select (analytics go through the admin fns).
create policy augur_events_insert on augur_events
  for insert to authenticated with check (user_id = auth.uid());
create policy augur_events_update on augur_events
  for update to authenticated using (user_id = auth.uid()) with check (user_id = auth.uid());

-- notes: writable only against your own event; no select.
create policy augur_notes_insert on augur_notes
  for insert to authenticated
  with check (exists (select 1 from augur_events e where e.id = event_id and e.user_id = auth.uid()));

-- unconfigured: write your own; no select.
create policy augur_unconfigured_insert on augur_unconfigured
  for insert to authenticated with check (user_id = auth.uid());

-- config: any authenticated user reads (boot reads overrides); only admin writes.
create policy augur_config_select on augur_config for select to authenticated using (true);
create policy augur_config_update on augur_config
  for update to authenticated using (augur_is_admin()) with check (augur_is_admin());

-- ── admin reads (security definer, gated on the allowlist) ───────────────────
create or replace function augur_admin_summary(p_from timestamptz, p_to timestamptz) returns jsonb
  language sql stable security definer set search_path = public, pg_temp as $$
  select case when augur_is_admin() then jsonb_build_object(
    'shown',     (select count(*) from augur_events where outcome = 'shown'    and shown_at >= p_from and shown_at < p_to),
    'answered',  (select count(*) from augur_events where outcome = 'answered' and shown_at >= p_from and shown_at < p_to),
    'ignored',   (select count(*) from augur_events where outcome = 'ignored'  and shown_at >= p_from and shown_at < p_to),
    'notes',     (select count(*) from augur_notes  where created_at >= p_from and created_at < p_to),
    'unread',    (select count(*) from augur_notes  where read_at is null)
  ) else null end;
$$;

create or replace function augur_admin_trigger_stats(p_from timestamptz, p_to timestamptz)
  returns table (trigger_id text, shown bigint, answered bigint, ignored bigint, yes bigint, not_really bigint, unclear bigint)
  language sql stable security definer set search_path = public, pg_temp as $$
  select e.trigger_id, count(*),
    count(*) filter (where e.outcome = 'answered'),
    count(*) filter (where e.outcome = 'ignored'),
    count(*) filter (where e.answer = 'yes'),
    count(*) filter (where e.answer = 'not_really'),
    count(*) filter (where e.answer = 'unclear')
  from augur_events e
  where augur_is_admin() and e.shown_at >= p_from and e.shown_at < p_to
  group by e.trigger_id order by e.trigger_id;
$$;

-- Answer counts per trigger and question version, so Results can count only answers
-- to the current wording of a reworded question.
create or replace function augur_admin_trigger_version_stats(p_from timestamptz, p_to timestamptz)
  returns table (trigger_id text, trigger_ver int, shown bigint, answered bigint, ignored bigint, yes bigint, not_really bigint, unclear bigint)
  language sql stable security definer set search_path = public, pg_temp as $$
  select e.trigger_id, e.trigger_ver, count(*),
    count(*) filter (where e.outcome = 'answered'),
    count(*) filter (where e.outcome = 'ignored'),
    count(*) filter (where e.answer = 'yes'),
    count(*) filter (where e.answer = 'not_really'),
    count(*) filter (where e.answer = 'unclear')
  from augur_events e
  where augur_is_admin() and e.shown_at >= p_from and e.shown_at < p_to
  group by e.trigger_id, e.trigger_ver order by e.trigger_id, e.trigger_ver;
$$;

create or replace function augur_admin_unconfigured()
  returns table (trigger_id text, seen bigint, last_seen timestamptz)
  language sql stable security definer set search_path = public, pg_temp as $$
  select u.trigger_id, count(*), max(u.seen_at)
  from augur_unconfigured u where augur_is_admin()
  group by u.trigger_id order by max(u.seen_at) desc;
$$;

-- Notes, newest first, a page at a time; the admin passes p_limit and p_before.
create or replace function augur_admin_notes(p_unread_only boolean default false, p_category text default null,
                                             p_before timestamptz default null, p_before_id uuid default null,
                                             p_limit int default null)
  returns table (id uuid, trigger_id text, answer text, body text, created_at timestamptz, read boolean, category text, context jsonb)
  language sql stable security definer set search_path = public, pg_temp as $$
  select n.id, e.trigger_id, e.answer, n.body, n.created_at, (n.read_at is not null), e.category, e.context
  from augur_notes n join augur_events e on e.id = n.event_id
  where augur_is_admin() and (not p_unread_only or n.read_at is null)
    and (p_category is null or e.category = p_category)
    and (p_before is null or (n.created_at, n.id) < (p_before, coalesce(p_before_id, 'ffffffff-ffff-ffff-ffff-ffffffffffff')))
  order by n.created_at desc, n.id desc   -- (created_at, id) is the page cursor, so ties are never skipped
  limit case when p_limit is null then null else least(greatest(p_limit, 1), 200) end;   -- null = all (older callers)
$$;

-- Button-panel submissions in a range, per entry point and category ("manual" is the
-- pre-0.3 name for the built-in button).
create or replace function augur_admin_submissions(p_from timestamptz, p_to timestamptz)
  returns table (source text, category text, n bigint)
  language sql stable security definer set search_path = public, pg_temp as $$
  select case when e.trigger_id = 'manual' then 'button' else e.trigger_id end, e.category, count(*)
  from augur_events e
  where augur_is_admin() and (e.category is not null or e.context is not null)
    and e.shown_at >= p_from and e.shown_at < p_to
  group by 1, 2;
$$;

create or replace function augur_admin_mark_note_read(p_note_id uuid) returns void
  language sql volatile security definer set search_path = public, pg_temp as $$
  update augur_notes set read_at = now() where id = p_note_id and augur_is_admin() and read_at is null;
$$;


-- ── collection writes (SECURITY DEFINER; derive the user from the JWT) ────────
-- Required: events/notes have no SELECT policy, so direct client writes fail under
-- RLS (the notes ownership check can't see the event). The SupabaseStore calls these.
create or replace function public.augur_log_shown(p_trigger_id text, p_trigger_ver int)
returns uuid language plpgsql security definer set search_path = public as $$
declare v_id uuid;
begin
  if auth.uid() is null then raise exception 'auth required'; end if;
  insert into augur_events (user_id, trigger_id, trigger_ver, outcome)
    values (auth.uid(), p_trigger_id, p_trigger_ver, 'shown')
  returning id into v_id;
  return v_id;
end $$;

create or replace function public.augur_log_outcome(p_event_id uuid, p_outcome text, p_answer text default null)
returns void language plpgsql security definer set search_path = public as $$
begin
  update augur_events set
    outcome     = p_outcome,
    answer      = case when p_outcome = 'answered' then p_answer else answer end,
    answered_at = case when p_outcome = 'answered' then now() else answered_at end
  where id = p_event_id and user_id = auth.uid();
end $$;

create or replace function public.augur_log_note(p_event_id uuid, p_body text)
returns void language plpgsql security definer set search_path = public as $$
begin
  if not exists (select 1 from augur_events where id = p_event_id and user_id = auth.uid()) then
    raise exception 'not your event';
  end if;
  insert into augur_notes (event_id, body) values (p_event_id, p_body);
end $$;

create or replace function public.augur_log_unconfigured(p_trigger_id text)
returns void language plpgsql security definer set search_path = public as $$
begin
  if auth.uid() is null then return; end if;
  insert into augur_unconfigured (trigger_id, user_id) values (p_trigger_id, auth.uid());
end $$;

-- One submission from the feedback button: an answered event carrying the category
-- and diagnostic context, plus the note. The entry point ("button", or the host's
-- own label) goes in as the trigger id.
create or replace function public.augur_submit(
  p_trigger_id text, p_category text, p_body text, p_context jsonb
) returns uuid language plpgsql security definer set search_path = public as $$
declare v_id uuid;
begin
  if auth.uid() is null then raise exception 'auth required'; end if;
  insert into augur_events (user_id, trigger_id, trigger_ver, outcome, answered_at, category, context)
    values (auth.uid(), coalesce(nullif(btrim(p_trigger_id), ''), 'button'), 0, 'answered', now(), p_category, p_context)
  returning id into v_id;
  if p_body is not null and length(btrim(p_body)) > 0 then
    insert into augur_notes (event_id, body) values (v_id, p_body);
  end if;
  return v_id;
end $$;


-- ── admin maintenance (delete a note; purge old data) ────────────────────────
create or replace function public.augur_admin_delete_note(p_note_id uuid)
returns void language plpgsql security definer set search_path = public as $$
begin
  if not augur_is_admin() then raise exception 'not authorized'; end if;
  delete from augur_notes where id = p_note_id;
end $$;

create or replace function public.augur_admin_purge(p_before_days int default null)
returns integer language plpgsql security definer set search_path = public as $$
declare n int;
begin
  if not augur_is_admin() then raise exception 'not authorized'; end if;
  with del as (
    delete from augur_events
    where p_before_days is null or shown_at < now() - make_interval(days => p_before_days)
    returning 1
  ) select count(*) into n from del;
  delete from augur_unconfigured
    where p_before_days is null or seen_at < now() - make_interval(days => p_before_days);
  delete from augur_friction
    where p_before_days is null or created_at < now() - make_interval(days => p_before_days);
  return coalesce(n, 0);
end $$;

-- Friction write (signed-in users) and admin read. Inputs are bounded so a client
-- can't store long strings or absurd counts.
create or replace function public.augur_log_friction(p_label text, p_route text, p_clicks int)
returns void language plpgsql security definer set search_path = public as $$
begin
  if auth.uid() is null then raise exception 'auth required'; end if;
  if p_label is null or length(btrim(p_label)) = 0 then return; end if;
  insert into augur_friction (user_id, label, route, clicks)
    values (auth.uid(), left(btrim(p_label), 80), left(coalesce(p_route, ''), 300), least(greatest(coalesce(p_clicks, 0), 1), 1000));
end $$;

create or replace function augur_admin_friction(p_from timestamptz, p_to timestamptz)
  returns table (label text, bursts bigint, people bigint, top_route text, last_seen timestamptz)
  language sql stable security definer set search_path = public, pg_temp as $$
  select f.label, count(*), count(distinct f.user_id),
    (select f2.route from augur_friction f2
      where f2.label = f.label and f2.created_at >= p_from and f2.created_at < p_to
      group by f2.route order by count(*) desc, f2.route limit 1),
    max(f.created_at)
  from augur_friction f
  where augur_is_admin() and f.created_at >= p_from and f.created_at < p_to
  group by f.label order by count(*) desc, f.label;
$$;

-- ── permissions ──────────────────────────────────────────────────────────────
-- Postgres lets every role execute a new function by default. Every function above
-- checks auth.uid() or augur_is_admin() itself, but signed-out visitors (anon) should
-- not be able to call them at all, so a future function that forgets its check
-- still isn't open. Revoke from public + anon, then grant to signed-in users.
revoke execute on function augur_is_admin()                                   from public, anon;
revoke execute on function augur_admin_summary(timestamptz, timestamptz)       from public, anon;
revoke execute on function augur_admin_trigger_stats(timestamptz, timestamptz) from public, anon;
revoke execute on function augur_admin_trigger_version_stats(timestamptz, timestamptz) from public, anon;
revoke execute on function augur_admin_unconfigured()                          from public, anon;
revoke execute on function augur_admin_notes(boolean, text, timestamptz, uuid, int) from public, anon;
revoke execute on function augur_admin_submissions(timestamptz, timestamptz)   from public, anon;
revoke execute on function augur_admin_mark_note_read(uuid)                    from public, anon;
revoke execute on function public.augur_log_shown(text, int)                   from public, anon;
revoke execute on function public.augur_log_outcome(uuid, text, text)          from public, anon;
revoke execute on function public.augur_log_note(uuid, text)                   from public, anon;
revoke execute on function public.augur_log_unconfigured(text)                 from public, anon;
revoke execute on function public.augur_submit(text, text, text, jsonb)        from public, anon;
revoke execute on function public.augur_admin_delete_note(uuid)                from public, anon;
revoke execute on function public.augur_admin_purge(int)                       from public, anon;
revoke execute on function public.augur_log_friction(text, text, int)         from public, anon;
revoke execute on function augur_admin_friction(timestamptz, timestamptz)      from public, anon;

grant execute on function augur_is_admin()                                   to authenticated;
grant execute on function augur_admin_summary(timestamptz, timestamptz)       to authenticated;
grant execute on function augur_admin_trigger_stats(timestamptz, timestamptz) to authenticated;
grant execute on function augur_admin_trigger_version_stats(timestamptz, timestamptz) to authenticated;
grant execute on function augur_admin_unconfigured()                          to authenticated;
grant execute on function augur_admin_notes(boolean, text, timestamptz, uuid, int) to authenticated;
grant execute on function augur_admin_submissions(timestamptz, timestamptz)   to authenticated;
grant execute on function augur_admin_mark_note_read(uuid)                    to authenticated;
grant execute on function public.augur_log_shown(text, int)                   to authenticated;
grant execute on function public.augur_log_outcome(uuid, text, text)          to authenticated;
grant execute on function public.augur_log_note(uuid, text)                   to authenticated;
grant execute on function public.augur_log_unconfigured(text)                 to authenticated;
grant execute on function public.augur_submit(text, text, text, jsonb)        to authenticated;
grant execute on function public.augur_admin_delete_note(uuid)                to authenticated;
grant execute on function public.augur_admin_purge(int)                       to authenticated;
grant execute on function public.augur_log_friction(text, text, int)         to authenticated;
grant execute on function augur_admin_friction(timestamptz, timestamptz)      to authenticated;