June

Promptfree, for everyone, and the only version there is

Copy the prompt for Claude CodeCopy the prompt for CodexCopy the prompt for Cursor

Copy

Build me a product analytics service that replaces June: the handful of numbers that say whether the product is being used, computed from my own event data.

STACK
- Node 20+ with Fastify for ingestion and the interface
- SQLite through better-sqlite3, WAL mode. It handles tens of millions of events on one disk; when it genuinely stops, the same schema moves to Postgres
- A small browser SDK and a server SDK, both mine
- Caddy in front

THE DATA MODEL
- events: id, name, user_key, company_key, properties_json, occurred_at, received_at, session_id, source
- users: key, first_seen, last_seen, traits_json
- companies: key, first_seen, last_seen, traits_json, plan, seats
- daily_user_activity: derived at query time, never stored — computing it beats keeping it correct
- funnels: id, name, steps_json, window_hours
- reports: id, name, kind, definition_json, created_by
- Index events on (name, occurred_at) and (user_key, occurred_at). Nothing else until a query is slow
- occurred_at is when it happened, received_at is when it arrived. They differ on mobile and the difference matters

INGESTION
- POST /track with name, user key, optional company key, properties and a timestamp
- Batch endpoint that takes an array, because a mobile client sends thirty at once when it comes back online
- Idempotency: a client-supplied event id is unique, so a retry does not double count
- Reject an event with no name or no user key; store nothing half-formed
- Accept events up to 30 days old and never in the future by more than a minute; clock skew is real
- Rate limit per token, and answer 202 quickly — ingestion must never wait on anything

THE NUMBERS THAT MATTER
- Active users: daily, weekly, monthly, each defined on the page. WAU is distinct user keys with at least one event in the last 7 days, and the definition is written where the number is
- Stickiness: DAU over MAU, as a percentage
- Retention: a cohort table by week, where week 0 is the week a user was first seen and each cell is the share still active. Say whether it is calendar weeks or rolling, and mean it
- Feature adoption per event name: users who did it, how often, and the share of all users
- Funnels between named steps with a completion window, showing drop-off per step and the median time between them
- Company-level everything: the same numbers grouped by company key, because a B2B product is used by accounts, not by people

WHAT NOT TO BUILD
- No session replay, no heatmaps, no autocapture. Autocapture produces a lake nobody can query and a privacy problem nobody signed off
- Named events only, defined deliberately. Fewer than fifty for a real product
- No dashboards builder in version one: five fixed reports that answer real questions beat a query builder nobody opens

DATA HYGIENE
- An event schema registry: the first time a name arrives it is recorded with its property types, and a later event with a different type for the same property is flagged rather than silently coerced
- A page listing event names by volume with their last-seen date, so dead events can be removed from the code
- Renaming an event creates an alias so history is not lost

PRIVACY
- No IP stored, no cookie required: the user key comes from the host application
- Properties are whatever the app sends, and the README says plainly not to send names, emails or anything else that identifies a person
- Deletion by user key, and it deletes rather than anonymises

THE INTERFACE
- One page per report, each one number and one chart and the definition beneath it
- A date range and one grouping control, nothing more
- Charts hand-drawn as inline SVG. No charting library
- Dark and light, one accent

OPERATIONS
- .env: DATABASE_PATH, INGEST_TOKENS, BASE_URL, ADMIN_PASSWORD_HASH
- Migrations on boot, each once
- Ingestion and reading are separate routes with separate rate limits; a slow report must not slow a write
- Nightly backup off the machine, restore script, and a documented retention policy
- Health endpoint that writes and reads one row

WHAT MATTERS MOST
Ingestion correctness first: idempotent, fast, and honest about time. Then active users and retention, with their definitions printed beside them. Every analytics product that gets abandoned was abandoned because somebody could not reproduce a number — make every number reproducible from the events table with a query the page will show you.

Give me the repository, both SDKs, migrations, .env.example, a seed with a month of synthetic events, and a README with deploy steps behind Caddy.