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 records an event. name is the event type. meta holds small extras such as the product id, tool name, or share slug.

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

Whitelist event names

Accepting arbitrary strings from the client leads to 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 catches failures and logs them to the console, so analytics never breaks a user flow.

Client beacons for click signals

Some events only exist in the browser, such as a blur panel click, a CTA tap, or 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. A wrong name returns 400, while the rate limit returns 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

The funnel comes from counting 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 own the data, and it stays in your SQLite database. There is no third-party script on every page or cookie banner drama for basic product metrics.

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.

Want me to talk about your product? You can sponsor this site.