tapir/docs/specs/stage0-usage-measurement.md

Spec — Stage 0 usage measurement (login events)

Date: 2026-06-03 Status: Ready to build · Repo: tapir · Size: small (one migration + middleware + query) Why: The Stage 0 gate (VISION, ADR-016) is return usage in ≥2 separate weeks. summary_actions captures acts (watch/skip/save) but not reads — a friend who logs in weekly and reads summaries without clicking anything is invisible. For a reading product that is the most important signal. This adds the missing data so the gate is measurable as written. Solo session, not a swarm.

Read CLAUDE.md + ADR-016 first. TBD, conventional commits, task check green before each commit.

Scope (resist sprawl — this is NOT analytics)

A lightweight, append-only record of when each user was active, enough to answer "returned/read in ≥N distinct weeks". Not page-level events, not click tracking, not a funnel.

1. Migration — login_events (append-only)

login_events (
  id        UUID PK default gen_random_uuid(),
  user_id   UUID NOT NULL,          -- per-user; RLS like every user-owned table
  seen_at   TIMESTAMPTZ NOT NULL default NOW()
)
INDEX (user_id, seen_at)

• RLS: FORCE ROW LEVEL SECURITY, same policy/pattern as the other user-owned tables (the tapir.current_user_id GUC via the withUser seam — match migration 003). A reporting query that needs cross-user counts runs as the owner/maintainer outside the per-user scope, or via a dedicated read — decide consistently with how existing admin-ish reads are done.
• Append-only: no updates, no deletes except the user-delete cascade. Add to the delete-account cascade (ADR-013) — login_events has no FK (mirrors summary_actions), so DeleteUser needs an explicit delete for it, and the delete test must assert it's covered. Do not forget this — it's the exact footgun the last delete work caught.

2. Middleware — throttled stamp

• In the authenticated request path (after CurrentUserID resolves, inside the registration-gated app — NOT on /welcome//healthz//auth), record one login_events row per user per day (throttle: skip if a row exists for this user with seen_at ≥ start-of-today). One insert per active day, not per request — keeps the table small and the signal clean.
• Throttle check must itself be RLS-scoped (withUser). Keep it cheap (indexed lookup).

3. Query — the gate report

Provide a query (and optionally a tiny tapir report CLI subcommand or an admin page — your call, CLI is fine) answering, per user:

-- distinct active weeks from reads (login_events) AND acts (summary_actions), unioned
WITH weeks AS (
  SELECT user_id, date_trunc('week', seen_at) AS wk FROM login_events
  UNION
  SELECT user_id, date_trunc('week', acted_at) FROM summary_actions
)
SELECT user_id, COUNT(DISTINCT wk) AS active_weeks
FROM weeks GROUP BY user_id
ORDER BY active_weeks DESC;

Gate passes when any user_id (maintainer or friend) reaches active_weeks >= 2 within the window.

Honesty caveats to carry (from VISION/ADR-016)

• "Unprompted" is not measurable here. login_events records that a user returned, not why. A nudged return looks identical to an organic one. This build does not close that gap and must not claim to — the VISION measurement note stands: count returns, read a nudged return as weaker signal. (If prompt-tracking is ever wanted, that's a separate decision, not this build.)
• Data accrues from deploy onward. The gate window's read-data starts when this ships — so ship soon (maintainer's call) rather than batching with the infra tooling session.
• date_trunc('week') is ISO/timezone-sensitive and noisy at low volume (N=3). Two visits days apart can fall in the same or different weeks. Acceptable, but don't over-read a single-week-margin pass/fail.

Out of scope

Page/event analytics; prompt-vs-organic tracking; dashboards beyond the one gate query; anything touching the engine or sinks (this is web/store only — ADR-003 holds).

Tests

• Migration up/down; RLS on login_events (extend the two-user isolation test to cover it).
• Throttle: N requests same day → 1 row; next day → 2nd row.
• DeleteUser removes the user's login_events and leaves others' intact (extend the delete test).
• The gate query returns correct distinct-week counts across a seeded reads+acts fixture.