Activation as a number you can move, not a feeling you have

Most analytics integrations scatter event calls across the client side, hope for the best, and produce a dashboard nobody trusts. Production activation tracking looks like the four files below: an event registry typed by Zod, a track() helper called from Server Actions, a nightly aggregation writing to tenant_metrics, and the funnel query the dashboard reads. Server-side from end to end; the source of truth in Postgres; the vendor (PostHog or Plausible) downstream.

1. The typed event registry

Every event has a name, a properties schema, and an owner. Adding an event is a PR that updates this file; missing it leaves the helper untypable at the call site.

// src/lib/analytics/events.ts
import { z } from 'zod'

export const EVENT_NAMES = [
  'user.signed_up',
  'user.verified_email',
  'tenant.created',
  'project.created',
  'invite.sent',
  'invite.accepted',
  'subscription.started',
  'first_value.reached',
] as const

export type EventName = (typeof EVENT_NAMES)[number]

export const EVENT_PROPS = {
  'user.signed_up': z.object({
    source: z.enum(['organic', 'paid', 'referral', 'direct']).optional(),
    plan: z.string().optional(),
  }),
  'user.verified_email': z.object({
    verification_age_minutes: z.number().int().nonnegative(),
  }),
  'tenant.created': z.object({
    plan: z.string(),
    seat_count: z.number().int().positive().default(1),
  }),
  'project.created': z.object({
    project_id: z.string().uuid(),
    template: z.string().optional(),
  }),
  'invite.sent': z.object({
    invite_id: z.string().uuid(),
    role: z.enum(['owner', 'admin', 'member', 'billing']),
  }),
  'invite.accepted': z.object({
    invite_id: z.string().uuid(),
    accept_age_hours: z.number().int().nonnegative(),
  }),
  'subscription.started': z.object({
    stripe_subscription_id: z.string(),
    plan: z.string(),
  }),
  'first_value.reached': z.object({
    flow: z.string(),
    elapsed_minutes: z.number().int().nonnegative(),
  }),
} as const satisfies Record<EventName, z.ZodTypeAny>

export type EventProps<T extends EventName> = z.infer<(typeof EVENT_PROPS)[T]>

2. The track() helper

A typed function called from any Server Action. It validates the properties at runtime, writes to Postgres, and forwards to the analytics vendor when configured. If validation fails it throws; we would rather find the bug than write a malformed event.

// src/lib/analytics/track.ts
import { adminClient } from '@/lib/supabase/admin'
import { EVENT_PROPS, type EventName, type EventProps } from './events'

interface TrackInput<T extends EventName> {
  tenantId: string
  userId: string | null
  event: T
  properties: EventProps<T>
}

export async function track<T extends EventName>(
  input: TrackInput<T>,
): Promise<void> {
  const schema = EVENT_PROPS[input.event]
  const validated = schema.parse(input.properties)

  // Write to our own events table first; this is the source of truth.
  const { error } = await adminClient.from('events').insert({
    tenant_id: input.tenantId,
    user_id: input.userId,
    event_name: input.event,
    properties: validated,
  })
  if (error) throw error

  // Forward to PostHog if configured. Failure here is logged but never
  // throws; the customer-facing dashboard depends on the Postgres write,
  // not on the third-party reach.
  if (process.env.POSTHOG_API_KEY) {
    void fetch(`${process.env.POSTHOG_HOST}/i/v0/e/`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        api_key: process.env.POSTHOG_API_KEY,
        event: input.event,
        properties: {
          ...validated,
          $groups: { tenant: input.tenantId },
          distinct_id: input.userId ?? input.tenantId,
        },
      }),
    }).catch(() => {})
  }
}

3. A Server Action that fires an event

The event fires AFTER the database write succeeds. The two operations are wrapped in a try / catch so a partial state cannot leave the event written without the corresponding row, or vice versa.

// app/[lang]/(app)/projects/actions.ts
'use server'

import { z } from 'zod'
import { revalidatePath } from 'next/cache'
import { createServerClient } from '@/lib/supabase/server'
import { getServerSession, getActiveTenantId } from '@/lib/auth/server'
import { track } from '@/lib/analytics/track'

const Input = z.object({
  name: z.string().min(1).max(200),
  template: z.string().optional(),
})

export async function createProject(
  raw: unknown,
): Promise<{ ok: true; projectId: string } | { ok: false; error: string }> {
  const session = await getServerSession()
  if (!session) return { ok: false, error: 'unauthorised' }

  const tenantId = await getActiveTenantId()
  if (!tenantId) return { ok: false, error: 'no active tenant' }

  const parsed = Input.safeParse(raw)
  if (!parsed.success) return { ok: false, error: 'invalid input' }

  const supabase = await createServerClient()
  const { data: project, error } = await supabase
    .from('projects')
    .insert({
      tenant_id: tenantId,
      name: parsed.data.name,
      template: parsed.data.template ?? null,
      created_by: session.userId,
    })
    .select('id')
    .single()

  if (error) return { ok: false, error: error.message }

  // Fire the activation event only after the DB write succeeded.
  await track({
    tenantId,
    userId: session.userId,
    event: 'project.created',
    properties: {
      project_id: project.id,
      template: parsed.data.template,
    },
  })

  revalidatePath('/projects')
  return { ok: true, projectId: project.id }
}

4. The nightly aggregation and the funnel query

The aggregation computes per-tenant metrics every night and writes them to tenant_metrics. The dashboard reads from tenant_metrics (cheap, indexed) instead of replaying the event stream every page load.

-- supabase/migrations/0020_tenant_metrics.sql
create table tenant_metrics (
  tenant_id uuid primary key references tenants(id) on delete cascade,
  activation_score numeric(5,2) not null default 0,
  funnel_step_pct jsonb not null default '{}',
  time_to_activation_hours integer,
  drop_off_step text,
  events_total integer not null default 0,
  computed_at timestamptz not null default now()
);

create or replace function refresh_tenant_metrics()
returns void
language plpgsql
security definer
as $$
declare
  rec record;
begin
  for rec in
    select t.id as tenant_id, t.created_at as tenant_created_at
    from tenants t
  loop
    with funnel as (
      select
        event_name,
        min(created_at) as first_seen
      from events
      where tenant_id = rec.tenant_id
      group by event_name
    ),
    steps as (
      select
        (select first_seen from funnel where event_name = 'user.signed_up') as signed_up,
        (select first_seen from funnel where event_name = 'project.created') as project_created,
        (select first_seen from funnel where event_name = 'invite.accepted') as invite_accepted,
        (select first_seen from funnel where event_name = 'first_value.reached') as first_value
    )
    insert into tenant_metrics (tenant_id, activation_score, funnel_step_pct, time_to_activation_hours, drop_off_step, events_total, computed_at)
    select
      rec.tenant_id,
      case
        when first_value is not null then 100
        when invite_accepted is not null then 75
        when project_created is not null then 50
        when signed_up is not null then 25
        else 0
      end as activation_score,
      jsonb_build_object(
        'signed_up', signed_up is not null,
        'project_created', project_created is not null,
        'invite_accepted', invite_accepted is not null,
        'first_value', first_value is not null
      ) as funnel_step_pct,
      case
        when first_value is not null
          then extract(epoch from (first_value - signed_up))::int / 3600
      end as time_to_activation_hours,
      case
        when first_value is null and invite_accepted is not null then 'first_value'
        when invite_accepted is null and project_created is not null then 'invite_accepted'
        when project_created is null and signed_up is not null then 'project_created'
      end as drop_off_step,
      (select count(*) from events where tenant_id = rec.tenant_id),
      now()
    from steps
    on conflict (tenant_id) do update set
      activation_score = excluded.activation_score,
      funnel_step_pct = excluded.funnel_step_pct,
      time_to_activation_hours = excluded.time_to_activation_hours,
      drop_off_step = excluded.drop_off_step,
      events_total = excluded.events_total,
      computed_at = now();
  end loop;
end;
$$;

// app/admin/funnel/page.tsx
import { Suspense } from 'react'
import { adminClient } from '@/lib/supabase/admin'

async function FunnelData() {
  const { data: rows } = await adminClient
    .from('tenant_metrics')
    .select('tenant_id, activation_score, drop_off_step, time_to_activation_hours, computed_at')
    .order('computed_at', { ascending: false })
    .limit(200)

  if (!rows) return <p>No metrics yet.</p>

  return (
    <table className="ds-table">
      <thead>
        <tr>
          <th>Tenant</th>
          <th>Score</th>
          <th>Drop-off</th>
          <th>Time to activation</th>
          <th>Computed</th>
        </tr>
      </thead>
      <tbody>
        {rows.map((r) => (
          <tr key={r.tenant_id}>
            <td>{r.tenant_id}</td>
            <td>{r.activation_score}</td>
            <td>{r.drop_off_step ?? '—'}</td>
            <td>{r.time_to_activation_hours ?? '—'}</td>
            <td>{new Date(r.computed_at).toLocaleString()}</td>
          </tr>
        ))}
      </tbody>
    </table>
  )
}

export default function FunnelPage() {
  return (
    <Suspense fallback={<p>Loading funnel…</p>}>
      <FunnelData />
    </Suspense>
  )
}

5. What this composes

Server Actions fire events as they succeed. The events table is the source of truth; row-level security scopes reads to tenants automatically. The nightly aggregation derives per-tenant metrics that the dashboard reads in one query. PostHog or Plausible receive a copy for exploration; the internal table is what the team optimises against.

Activation stops being a feeling the founder has and becomes a number on a dashboard the team opens Monday morning. The funnel becomes the artefact every product decision argues against; the next ten experiments have a target instead of a vibe.