First-party analytics with SQLite

I didn’t want Google Analytics on StackPlan.

I wanted simple funnel numbers: how many reports get created, how many people click the paywall, how many pay.

So I built first-party analytics on top of D1. One events table. A whitelist of event names. Done.

One table, append-only

The schema is intentionally flat:

export const events = sqliteTable('events', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  src: text('src'),
  anonToken: text('anon_token'),
  userId: text('user_id'),
  reportId: text('report_id'),
  meta: text('meta', { mode: 'json' }),
  createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
})

Every row is one thing that happened. name is the event type. meta holds small extras — product id, tool name, share slug.

Add an index on (name, created_at). Funnel queries filter by event name and time range constantly.

Whitelist event names

Don’t accept arbitrary strings from the client. You’ll end up with garbage rows and storage bills.

Define allowed names in TypeScript:

export const EVENT_NAMES = [
  'report_created',
  'report_viewed',
  'checkout_started',
  'checkout_completed',
  'signup',
] as const

Server-side trackEvent() only inserts known names. The type system enforces it at compile time.

For client beacons, use a smaller allowlist. Only events that truly need the browser:

export const BEACON_EVENT_NAMES = [
  'blur_click',
  'brief_cta_click',
  'agent_prompt_copy',
] as const

Everything else is recorded on the server where you already trust the code path.

Track on the server during renders

The cleanest signals fire where the action already happens.

Report created? POST /api/reports inserts the row, then calls trackEvent.

Report viewed? The report page coordinator loads data, then tracks before rendering.

Checkout started? The billing route tracks right before redirecting to Polar.

await trackEvent(db, {
  name: 'report_viewed',
  src: getSrc(cookies),
  anonToken,
  userId: user?.id ?? null,
  reportId,
})

trackEvent never throws. Analytics must not break user flows. Failures log to the console and get swallowed.

Client beacons for click signals

Some events only exist in the browser. A blur panel click. A CTA tap. A copy button.

Use navigator.sendBeacon so the request survives page navigation:

export function sendBeacon(name: BeaconEventName, reportId?: string) {
  const body = JSON.stringify({ name, reportId })
  const url = '/api/events'

  if (navigator.sendBeacon) {
    navigator.sendBeacon(url, new Blob([body], { type: 'application/json' }))
    return
  }

  fetch(url, { method: 'POST', body, keepalive: true })
}

The POST /api/events route checks the name against BEACON_EVENT_NAMES. Wrong name → 400. Rate limit → 429.

The server attaches anonToken, userId, and campaign src from cookies. The client only sends what it knows.

Campaign attribution with ?src=

Newsletter links carry ?src=nl-2026-07-launch. Middleware sanitizes the tag and sets an httpOnly cookie.

Every trackEvent call reads that cookie. Later you can group conversions by campaign without UTM parsing in SQL.

Funnel queries on top

No fancy pipeline needed. Count rows.

SELECT name, COUNT(*) AS total
FROM events
WHERE created_at >= unixepoch('now', '-30 days') * 1000
GROUP BY name
ORDER BY total DESC

Unlock conversion is two counts:

SELECT
  (SELECT COUNT(*) FROM events WHERE name = 'report_viewed' ...) AS viewed,
  (SELECT COUNT(*) FROM events WHERE name = 'checkout_completed'
    AND json_extract(meta, '$.product') = 'unlock' ...) AS unlocks

I built a small /admin/funnel page that runs these counts for the last 7, 30, or 90 days. Conversion percentages are just to / from * 100 in TypeScript.

What you trade away

You don’t get session replay, geo maps, or real-time cohort dashboards.

You get ownership. No third-party script on every page. No cookie banner drama for basic product metrics. Data stays in your SQLite database.

My advice: start with ten event names, not a hundred. Add one when you have a real question to answer.

If the question is “did this ship work?”, a single events table is enough.

Related posts about database: