# First-party analytics with SQLite

> Build product analytics with a SQLite events table in D1 instead of Google Analytics: whitelisted events, server tracking, beacons, funnel SQL.

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2026-07-19 | Topics: [Database](https://flaviocopes.com/tags/database/) | Canonical: https://flaviocopes.com/first-party-analytics-sqlite/

I didn't want Google Analytics on [StackPlan](https://stackplan.dev).

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:

```ts
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:

```ts
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:

```ts
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.

```ts
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:

```ts
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.

```sql
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:

```sql
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.