supabase/migrations/0011_analytics.sql

-- 0011_analytics.sql
-- Product analytics: engagement events, dwell sessions, and reporting views.
--
-- Why: public.runs only logs /api/run (the DE pipeline) and — until the engine
-- change that ships with this migration — only ever wrote status='started'
-- (finished_at / elapsed_seconds / n_variants stayed NULL, and successful_runs
-- in user_summary was always 0). profiles.last_seen_at was also never written.
-- This migration adds the missing pieces so activation, retention, engagement
-- (across ALL tools), and real dwell time become queryable.
--
-- Privacy: mirrors public.runs — no sequence content; anonymous visitors are
-- keyed only by a salted /24-IP+UA fingerprint, never a raw IP. Retention is
-- bounded by prune_old_analytics() (companion to 0010's prune_old_runs).
--
-- Run once in the Supabase SQL editor. Re-runnable: tables/functions/views use
-- IF NOT EXISTS / OR REPLACE, and policies are dropped before (re)create.

create extension if not exists "pgcrypto";

-- ═══════════════════════════════════════════════════════════════════════
-- EVENTS — one row per meaningful tool action (engagement)
-- ───────────────────────────────────────────────────────────────────────
-- Written by the Flask backend via the service role (auth.log_event), fired
-- from the after_request hook for a curated set of endpoints (plasmid map,
-- CRISPR design, saves, alignment, DE round-2, …). /api/run is NOT logged here
-- — public.runs is authoritative for it, so logging it twice would double-count.
create table if not exists public.events (
    id                uuid primary key default gen_random_uuid(),
    user_id           uuid references auth.users(id) on delete cascade,
    anon_fingerprint  text,
    kind              text not null,
    meta              jsonb not null default '{}'::jsonb,
    created_at        timestamptz not null default now()
);

comment on table public.events is
    'Engagement events — one row per meaningful tool action. No sequence content.';

create index if not exists events_user_idx    on public.events(user_id, created_at desc);
create index if not exists events_kind_idx     on public.events(kind, created_at desc);
create index if not exists events_created_idx  on public.events(created_at desc);


-- ═══════════════════════════════════════════════════════════════════════
-- APP_SESSIONS — per-session dwell time
-- ───────────────────────────────────────────────────────────────────────
-- One row per browser session, upserted by the /api/ping heartbeat via the
-- record_ping() RPC below. visible_ms is CUMULATIVE foreground time; the RPC
-- keeps the max so duplicate / out-of-order beats are harmless.
create table if not exists public.app_sessions (
    session_id        text primary key,
    user_id           uuid references auth.users(id) on delete set null,
    anon_fingerprint  text,
    first_seen        timestamptz not null default now(),
    last_seen         timestamptz not null default now(),
    visible_ms        bigint not null default 0,
    ping_count        integer not null default 0
);

comment on table public.app_sessions is
    'Per-session dwell tracking. Upserted by record_ping() from /api/ping.';

create index if not exists app_sessions_user_idx  on public.app_sessions(user_id, first_seen desc);
create index if not exists app_sessions_first_idx on public.app_sessions(first_seen desc);


-- ═══════════════════════════════════════════════════════════════════════
-- record_ping() — atomic dwell upsert
-- ───────────────────────────────────────────────────────────────────────
-- Insert a new session, or bump last_seen / ping_count and keep visible_ms
-- monotonic. Crucially, it PRESERVES an already-known user_id when a later beat
-- arrives without one — the on-hide sendBeacon is unauthenticated, so a naive
-- upsert would null out the user_id captured by the earlier authenticated beats.
create or replace function public.record_ping(
    p_session_id text,
    p_user_id    uuid,
    p_anon       text,
    p_visible_ms bigint
) returns void
language plpgsql
security definer
set search_path = public
as $$
begin
    insert into public.app_sessions
        (session_id, user_id, anon_fingerprint, first_seen, last_seen, visible_ms, ping_count)
    values
        (p_session_id, p_user_id, p_anon, now(), now(), greatest(coalesce(p_visible_ms, 0), 0), 1)
    on conflict (session_id) do update set
        last_seen        = now(),
        visible_ms       = greatest(public.app_sessions.visible_ms, excluded.visible_ms),
        ping_count       = public.app_sessions.ping_count + 1,
        user_id          = coalesce(public.app_sessions.user_id, excluded.user_id),
        anon_fingerprint = coalesce(public.app_sessions.anon_fingerprint, excluded.anon_fingerprint);
end;
$$;

-- Only the service role (Flask backend) may call it. Revoke the default public
-- EXECUTE, then grant it back to service_role explicitly — the engine invokes
-- this RPC with the service key at runtime, so without this grant every ping
-- would fail "permission denied for function record_ping".
revoke all on function public.record_ping(text, uuid, text, bigint) from public;
revoke all on function public.record_ping(text, uuid, text, bigint) from anon;
revoke all on function public.record_ping(text, uuid, text, bigint) from authenticated;
grant execute on function public.record_ping(text, uuid, text, bigint) to service_role;

comment on function public.record_ping(text, uuid, text, bigint) is
    'Atomic upsert of a dwell session from /api/ping. Service-role only.';


-- ═══════════════════════════════════════════════════════════════════════
-- ROW-LEVEL SECURITY — deny by default; reads of own rows; writes via service
-- role (which bypasses RLS, like public.runs).
-- ═══════════════════════════════════════════════════════════════════════
alter table public.events       enable row level security;
alter table public.app_sessions enable row level security;

drop policy if exists "events_select_own"       on public.events;
drop policy if exists "app_sessions_select_own"  on public.app_sessions;

create policy "events_select_own"
    on public.events for select using (auth.uid() = user_id);
create policy "app_sessions_select_own"
    on public.app_sessions for select using (auth.uid() = user_id);


-- ═══════════════════════════════════════════════════════════════════════
-- RETENTION SWEEP — companion to 0010's prune_old_runs. Free tier has no
-- pg_cron, so this is a callable maintenance routine; safe to run repeatedly.
-- ═══════════════════════════════════════════════════════════════════════
create or replace function public.prune_old_analytics(retain_days integer default 90)
returns integer
language plpgsql
security definer
set search_path = public
as $$
declare
    d_events integer;
    d_sess   integer;
begin
    delete from public.events
        where created_at < now() - make_interval(days => greatest(retain_days, 1));
    get diagnostics d_events = row_count;
    delete from public.app_sessions
        where last_seen < now() - make_interval(days => greatest(retain_days, 1));
    get diagnostics d_sess = row_count;
    return d_events + d_sess;
end;
$$;

revoke all on function public.prune_old_analytics(integer) from public;
revoke all on function public.prune_old_analytics(integer) from anon;
revoke all on function public.prune_old_analytics(integer) from authenticated;

comment on function public.prune_old_analytics(integer) is
    'Maintenance: delete events + app_sessions older than retain_days (default 90).';


-- ═══════════════════════════════════════════════════════════════════════
-- REPORTING VIEWS (service-role only — RLS on the base tables blocks anon).
-- Build dashboards against these instead of re-writing the joins.
-- ═══════════════════════════════════════════════════════════════════════

-- Per-user activation: first activity = earliest run OR event.
create or replace view public.user_activation as
with first_act as (
    select user_id, min(ts) as first_activity_at from (
        select user_id, started_at as ts from public.runs   where user_id is not null
        union all
        select user_id, created_at as ts from public.events where user_id is not null
    ) a group by user_id
)
select
    p.id,
    p.email,
    p.created_at                                                 as signed_up_at,
    p.signup_source,
    fa.first_activity_at,
    (fa.first_activity_at is not null)                           as ever_active,
    (fa.first_activity_at <= p.created_at + interval '24 hours') as activated_24h,
    (fa.first_activity_at <= p.created_at + interval '7 days')   as activated_7d
from public.profiles p
left join first_act fa on fa.user_id = p.id;

comment on view public.user_activation is
    'Per-user activation: did they act, and how soon after signup. Service-role only.';

-- Weekly signup cohorts × still-active in later windows.
create or replace view public.retention_cohorts as
with act as (
    select user_id, started_at::date as d from public.runs   where user_id is not null
    union all
    select user_id, created_at::date  as d from public.events where user_id is not null
)
select
    date_trunc('week', p.created_at)::date as cohort_week,
    count(distinct p.id)                   as cohort_size,
    count(distinct a.user_id) filter (
        where a.d >= (p.created_at + interval '1 day')::date
          and a.d <  (p.created_at + interval '8 days')::date)  as wk1,
    count(distinct a.user_id) filter (
        where a.d >= (p.created_at + interval '8 days')::date
          and a.d <  (p.created_at + interval '29 days')::date) as wk2_4,
    count(distinct a.user_id) filter (
        where a.d >= (p.created_at + interval '29 days')::date) as wk4plus
from public.profiles p
left join act a on a.user_id = p.id
group by 1 order by 1 desc;

comment on view public.retention_cohorts is
    'Weekly signup cohorts × later activity (runs+events). Service-role only.';

-- Daily action volume + active users (runs + events unioned).
create or replace view public.engagement_daily as
with act as (
    select user_id, anon_fingerprint, started_at as ts, 'run'::text as kind from public.runs
    union all
    select user_id, anon_fingerprint, created_at as ts, kind            from public.events
)
select
    date_trunc('day', ts)::date                                as day,
    count(*)                                                   as actions,
    count(distinct user_id) filter (where user_id is not null) as signed_in_users,
    count(distinct coalesce(user_id::text, anon_fingerprint))  as total_actors
from act
group by 1 order by 1 desc;

comment on view public.engagement_daily is
    'Daily action volume + active users (runs+events). Service-role only.';

-- Per-session dwell.
create or replace view public.dwell_sessions as
select
    s.session_id,
    s.user_id,
    p.email,
    s.first_seen,
    s.last_seen,
    s.ping_count,
    round(s.visible_ms / 1000.0)  as visible_seconds
from public.app_sessions s
left join public.profiles p on p.id = s.user_id
order by s.first_seen desc;

comment on view public.dwell_sessions is
    'Per-session foreground dwell time. Service-role only.';

-- Daily dwell summary.
create or replace view public.dwell_daily as
select
    date_trunc('day', first_seen)::date                       as day,
    count(*)                                                  as sessions,
    count(distinct coalesce(user_id::text, anon_fingerprint)) as visitors,
    round(avg(visible_ms) / 1000.0)                           as avg_session_seconds,
    round((percentile_cont(0.5) within group (order by visible_ms)) / 1000.0)
                                                              as median_session_seconds
from public.app_sessions
group by 1 order by 1 desc;

comment on view public.dwell_daily is
    'Daily dwell: sessions, visitors, avg/median foreground seconds. Service-role only.';