telemetry-worker

jcode Telemetry Worker

Cloudflare Worker that receives anonymous telemetry events from jcode.

The headline number is Total users: distinct, non-CI telemetry_ids that ever installed jcode OR did meaningful work in it. Run it with:

wrangler d1 execute jcode-telemetry --remote --file=users.sql

Storage architecture

Events are dual-written to two stores with different jobs:

1. Workers Analytics Engine firehose (jcode_telemetry_firehose dataset): every event, written first. Time-series store with no database size cap and ~90-day retention (adaptive sampling on reads; index1 is the telemetry_id, so per-user sampling stays accurate). This is the primary store for high-volume raw analysis (turn_end, session_start, onboarding_step volume) and the safety net: telemetry keeps recording even when D1 is full. Column mapping lives in FIREHOSE_SCHEMA in src/worker.js and is append-only (never reorder or repurpose a position). Query it via the Analytics Engine SQL API:

   # Requires an API token with Account Analytics read. Example: auth failure
   # reasons over the last 7 days (blob9=auth_provider, blob11=auth_failure_reason).
   curl -s "https://api.cloudflare.com/client/v4/accounts/<ACCOUNT_ID>/analytics_engine/sql" \
     -H "Authorization: Bearer $CF_ANALYTICS_TOKEN" \
     -d "SELECT blob9 AS provider, blob11 AS reason, SUM(_sample_interval) AS n
         FROM jcode_telemetry_firehose
         WHERE blob1 = 'onboarding_step' AND blob8 = 'auth_failed'
           AND timestamp > NOW() - INTERVAL '7' DAY
         GROUP BY provider, reason ORDER BY n DESC"

2. D1 (jcode-telemetry database): the durable relational store for identity anchors (install, feedback), auth/lifecycle events, the daily_active_users rollup, and a retention-pruned raw tail of the high-volume events (see RETENTION_DAYS). All the dashboard SQL in this repo (users.sql, dau.sql, health.sql) reads D1.

D1 size self-defense

D1 hard-caps databases at 500 MB on the free plan; at the cap every insert 500s and telemetry silently stops (June 2026: ~3 days lost). Defenses, in order:

• The worker observes meta.size_after on every D1 write. Past the soft limit (D1_SOFT_LIMIT_BYTES, just above the file's high-water mark) it triggers an emergency prune (halved retention windows, rate-limited to one per 10 minutes per isolate) instead of waiting for the nightly cron.
• If an insert fails with a SQLITE_FULL-class error, the emergency prune runs immediately, bounding a June-style outage to minutes instead of days.
• The nightly cron re-checks size after the normal prune and escalates to the emergency prune if still over the soft limit.
• If a D1 insert still fails, the request returns {ok, durable:false, firehose:true} instead of a 500, because the event was captured in the firehose.
• GET /v1/health reports db_size_bytes vs the soft limit for external monitoring.

Note: D1 has no VACUUM, so the file never shrinks; deletes only free pages internally for reuse. If bloat itself becomes the problem, rotate to a fresh database (create new D1 DB, copy live rows, repoint wrangler.toml).

Setup

1. Install wrangler: npm install

2. Create D1 database:

   wrangler d1 create jcode-telemetry

3. Update wrangler.toml with the database ID from step 2

4. Initialize schema:

   wrangler d1 execute jcode-telemetry --file=schema.sql

Migrating an existing database

If your production database was created before the latest telemetry fields were added, apply all remote migrations:

wrangler d1 execute jcode-telemetry --remote --file=migrations/0001_expand_events.sql
wrangler d1 execute jcode-telemetry --remote --file=migrations/0002_transport_metrics.sql
wrangler d1 execute jcode-telemetry --remote --file=migrations/0003_usage_expansion.sql
wrangler d1 execute jcode-telemetry --remote --file=migrations/0004_telemetry_phase123.sql
wrangler d1 execute jcode-telemetry --remote --file=migrations/0005_workflow_turn_telemetry.sql

(...and so on through the latest numbered migration; each also has an npm run migrate:<name> alias, see Ops helpers below. The newest is migrations/0016_web_subscription_analytics.sql / npm run migrate:web-subscription.)

Then redeploy the worker:

npm run deploy

5. Deploy:

   npm run deploy

6. Set up custom domain (optional): point telemetry.jcode.dev to the worker in Cloudflare dashboard

Ops helpers

# Apply schema catch-up migrations
npm run migrate:expand
npm run migrate:transport
npm run migrate:usage
npm run migrate:phase123
npm run migrate:workflow
npm run migrate:tokens
npm run migrate:dashboard-indexes
npm run migrate:feedback-text
npm run migrate:daily-active
npm run migrate:daily-active-backfill
npm run migrate:daily-active-ci
npm run migrate:detail-fields
npm run migrate:dau-full-backfill
npm run migrate:auth-failure-reason
npm run migrate:web-subscription

# Run the health dashboard query
npm run health

Event types

CLI events (sent by jcode itself): install, upgrade, auth_success, onboarding_step, feedback, session_start, turn_end, session_end, session_crash.

Website analytics events (migration 0016)

Sent by the beacon on https://solosystems.dev (and the https://solosystems.pages.dev preview). The browser mints an anonymous visitor_id UUID in localStorage; the worker uses it as the telemetry id and fills in version/os/arch defaults, so the beacon payload can stay tiny. Web-only fields are stored in the web_details table (keyed by event_id, like session_details/turn_details) because events is near D1's 100-column cap.

• web_pageview: path, referrer, visitor_id, utm_source, utm_medium, utm_campaign
• web_cta_click: path, cta (e.g. plus_early_access, flagship_early_access, install), visitor_id

Token subscription plan events (migration 0016)

All require account_id; tier and model are attached where relevant (model is stored in the existing generic model_start column).

• subscription_login: account_id, tier
• subscription_activated: account_id, tier
• subscription_budget_exhausted: account_id, tier, model
• subscription_router_error: account_id, tier, model
• account_linked: telemetry_id (the standard id field) + account_id. This is the analytics<->account join anchor: it ties an anonymous CLI telemetry_id to a subscription account_id, and is never pruned.

Web + subscription events are firehosed to the separate jcode_web_firehose dataset (FIREHOSE_WEB_SCHEMA in src/worker.js, also append-only): the main FIREHOSE_SCHEMA is at Analytics Engine's 20-blob/20-double capacity. For web events index1 is the visitor_id.

Querying Data

# Total installs (raw, and excluding CI runners which mint a fresh id per job)
wrangler d1 execute jcode-telemetry --command "SELECT COUNT(DISTINCT telemetry_id) AS raw_installs, COUNT(DISTINCT CASE WHEN is_ci = 0 THEN telemetry_id END) AS installs_noci FROM events WHERE event = 'install'"

# Weekly / monthly active users (canonical: use the rollup so every window
# shares one "meaningful" definition and includes session_crash + turn_end days).
# meaningful_release_*_noci is the headline product metric: real users on the
# release channel, excluding automated CI traffic (ephemeral runners that mint a
# fresh telemetry_id per job and otherwise inflate users/installs and tank retention).
# WAU (last 7 UTC days):
wrangler d1 execute jcode-telemetry --command "SELECT COUNT(DISTINCT telemetry_id) AS raw_wau, COUNT(DISTINCT CASE WHEN meaningful_active > 0 THEN telemetry_id END) AS meaningful_wau, COUNT(DISTINCT CASE WHEN meaningful_release_active > 0 THEN telemetry_id END) AS meaningful_release_wau, COUNT(DISTINCT CASE WHEN meaningful_release_active > 0 AND last_is_ci = 0 THEN telemetry_id END) AS meaningful_release_wau_noci FROM daily_active_users WHERE activity_date > date('now', '-7 days')"

# MAU (last 30 UTC days):
wrangler d1 execute jcode-telemetry --command "SELECT COUNT(DISTINCT telemetry_id) AS raw_mau, COUNT(DISTINCT CASE WHEN meaningful_active > 0 THEN telemetry_id END) AS meaningful_mau, COUNT(DISTINCT CASE WHEN meaningful_release_active > 0 THEN telemetry_id END) AS meaningful_release_mau, COUNT(DISTINCT CASE WHEN meaningful_release_active > 0 AND last_is_ci = 0 THEN telemetry_id END) AS meaningful_release_mau_noci FROM daily_active_users WHERE activity_date > date('now', '-30 days')"

# Raw vs meaningful active users this week, directly from raw events (matches the
# rollup definition: counts session_end/session_crash AND turn_end activity).
wrangler d1 execute jcode-telemetry --command "SELECT COUNT(DISTINCT telemetry_id) AS raw_wau, COUNT(DISTINCT CASE WHEN (event IN ('session_end','session_crash') AND (turns > 0 OR had_user_prompt > 0 OR had_assistant_response > 0 OR assistant_responses > 0 OR tool_calls > 0 OR executed_tool_calls > 0 OR duration_secs > 0 OR error_provider_timeout > 0 OR error_auth_failed > 0 OR error_tool_error > 0 OR error_mcp_error > 0 OR error_rate_limited > 0 OR provider_switches > 0 OR model_switches > 0)) OR (event = 'turn_end' AND (assistant_responses > 0 OR tool_calls > 0 OR executed_tool_calls > 0 OR file_write_calls > 0 OR tests_run > 0 OR turn_success > 0)) THEN telemetry_id END) AS meaningful_wau FROM events WHERE event IN ('session_end','session_crash','turn_end') AND created_at > datetime('now', '-7 days')"

# Provider distribution for meaningful sessions
wrangler d1 execute jcode-telemetry --command "SELECT provider_end, COUNT(*) as sessions FROM events WHERE event = 'session_end' AND (turns > 0 OR duration_mins > 0 OR error_provider_timeout > 0 OR error_auth_failed > 0 OR error_tool_error > 0 OR error_mcp_error > 0 OR error_rate_limited > 0 OR provider_switches > 0 OR model_switches > 0) GROUP BY provider_end ORDER BY sessions DESC"

# Average meaningful session duration
wrangler d1 execute jcode-telemetry --command "SELECT AVG(duration_mins) as avg_mins, AVG(turns) as avg_turns FROM events WHERE event = 'session_end' AND (turns > 0 OR duration_mins > 0 OR error_provider_timeout > 0 OR error_auth_failed > 0 OR error_tool_error > 0 OR error_mcp_error > 0 OR error_rate_limited > 0 OR provider_switches > 0 OR model_switches > 0)"

# Error rates. Count affected sessions/users, not raw sums: raw sums are
# dominated by runaway retry loops (one pre-breaker session logged 18k+ auth
# failures), which makes one broken install look like a fleet-wide outage.
wrangler d1 execute jcode-telemetry --command "SELECT COUNT(CASE WHEN error_provider_timeout > 0 THEN 1 END) as timeout_sessions, COUNT(CASE WHEN error_rate_limited > 0 THEN 1 END) as rate_limited_sessions, COUNT(CASE WHEN error_auth_failed > 0 THEN 1 END) as auth_failed_sessions, COUNT(DISTINCT CASE WHEN error_auth_failed > 0 THEN telemetry_id END) as auth_failed_users FROM events WHERE event = 'session_end'"

# Auth failure reasons (requires 0015; reasons recorded from explicit auth_failed onboarding steps)
wrangler d1 execute jcode-telemetry --command "SELECT auth_provider, auth_failure_reason, COUNT(*) AS n, COUNT(DISTINCT telemetry_id) AS users FROM events WHERE event = 'onboarding_step' AND step = 'auth_failed' AND created_at > datetime('now', '-30 days') GROUP BY 1, 2 ORDER BY n DESC"

# Version adoption
wrangler d1 execute jcode-telemetry --command "SELECT version, COUNT(DISTINCT telemetry_id) as users FROM events GROUP BY version ORDER BY version DESC"

# Heavy telemetry IDs (useful for spotting dev/test noise)
wrangler d1 execute jcode-telemetry --command "SELECT telemetry_id, COUNT(*) AS session_ends FROM events WHERE event = 'session_end' GROUP BY telemetry_id ORDER BY session_ends DESC LIMIT 20"

# OS/arch breakdown
wrangler d1 execute jcode-telemetry --command "SELECT os, arch, COUNT(DISTINCT telemetry_id) as users FROM events GROUP BY os, arch ORDER BY users DESC"

# Transport breakdown (requires 0002 transport migration)
wrangler d1 execute jcode-telemetry --command "SELECT SUM(transport_https) AS https, SUM(transport_persistent_ws_fresh) AS ws_fresh, SUM(transport_persistent_ws_reuse) AS ws_reuse, SUM(transport_cli_subprocess) AS cli, SUM(transport_native_http2) AS native_http2, SUM(transport_other) AS other FROM events WHERE event IN ('session_end', 'session_crash')"

# Telemetry health dashboard
wrangler d1 execute jcode-telemetry --file=health.sql

# Daily active users. Prefer meaningful_release_* as the headline product metric.
npm run dau

# Fast UTC-day DAU from the ingest-time rollup table
wrangler d1 execute jcode-telemetry --remote --command "SELECT COUNT(*) AS raw_today, SUM(CASE WHEN meaningful_active > 0 THEN 1 ELSE 0 END) AS meaningful_today, SUM(CASE WHEN release_active > 0 THEN 1 ELSE 0 END) AS raw_release_today, SUM(CASE WHEN meaningful_release_active > 0 THEN 1 ELSE 0 END) AS meaningful_release_today FROM daily_active_users WHERE activity_date = date('now')"

# Auth activation funnel by provider
wrangler d1 execute jcode-telemetry --command "SELECT auth_provider, COUNT(DISTINCT telemetry_id) AS users FROM events WHERE event = 'auth_success' GROUP BY auth_provider ORDER BY users DESC"

# Onboarding funnel steps
wrangler d1 execute jcode-telemetry --command "SELECT step, COUNT(DISTINCT telemetry_id) AS users FROM events WHERE event = 'onboarding_step' GROUP BY step ORDER BY users DESC"

# Recent explicit feedback
wrangler d1 execute jcode-telemetry --command "SELECT created_at, feedback_text, feedback_rating, feedback_reason, version, build_channel FROM events WHERE event = 'feedback' ORDER BY created_at DESC LIMIT 50"

# Session starts by UTC hour (workflow timing)
wrangler d1 execute jcode-telemetry --command "SELECT session_start_hour_utc, COUNT(*) AS sessions FROM events WHERE event = 'session_start' GROUP BY session_start_hour_utc ORDER BY session_start_hour_utc"

# Multi-sessioning rate
wrangler d1 execute jcode-telemetry --command "SELECT AVG(CASE WHEN multi_sessioned > 0 THEN 1.0 ELSE 0.0 END) AS multi_session_rate FROM events WHERE event IN ('session_end', 'session_crash') AND created_at > datetime('now', '-30 days')"

# Per-turn latency and success
wrangler d1 execute jcode-telemetry --command "SELECT AVG(turn_active_duration_ms) AS avg_turn_ms, AVG(CASE WHEN turn_success > 0 THEN 1.0 ELSE 0.0 END) AS turn_success_rate FROM events WHERE event = 'turn_end' AND created_at > datetime('now', '-30 days')"

# Build-channel cleanup for active users
wrangler d1 execute jcode-telemetry --command "SELECT build_channel, COUNT(DISTINCT telemetry_id) AS users FROM events WHERE event IN ('session_end', 'session_crash') AND created_at > datetime('now', '-30 days') GROUP BY build_channel ORDER BY users DESC"

# D7 retention for users who installed 8-14 days ago
wrangler d1 execute jcode-telemetry --command "WITH cohort AS (SELECT DISTINCT telemetry_id FROM events WHERE event = 'install' AND created_at >= datetime('now', '-14 days') AND created_at < datetime('now', '-7 days')), retained AS (SELECT DISTINCT telemetry_id FROM events WHERE event IN ('session_end', 'session_crash') AND created_at >= datetime('now', '-7 days')) SELECT COUNT(*) AS cohort_users, (SELECT COUNT(*) FROM cohort WHERE telemetry_id IN retained) AS retained_users FROM cohort"

# Feature adoption (last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT SUM(feature_memory_used) AS memory_sessions, SUM(feature_swarm_used) AS swarm_sessions, SUM(feature_web_used) AS web_sessions, SUM(feature_email_used) AS email_sessions, SUM(feature_mcp_used) AS mcp_sessions, SUM(feature_side_panel_used) AS side_panel_sessions, SUM(feature_goal_used) AS goal_sessions, SUM(feature_selfdev_used) AS selfdev_sessions, SUM(feature_background_used) AS background_sessions, SUM(feature_subagent_used) AS subagent_sessions FROM events WHERE event IN ('session_end', 'session_crash') AND created_at > datetime('now', '-30 days')"

# Session success rate + abandonment rate (last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT AVG(CASE WHEN session_success > 0 THEN 1.0 ELSE 0.0 END) AS success_rate, AVG(CASE WHEN abandoned_before_response > 0 THEN 1.0 ELSE 0.0 END) AS abandoned_before_response_rate FROM events WHERE event IN ('session_end', 'session_crash') AND created_at > datetime('now', '-30 days')"

# Tool and response latency (last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT AVG(first_assistant_response_ms) AS avg_first_response_ms, AVG(first_tool_success_ms) AS avg_first_tool_success_ms, AVG(CASE WHEN executed_tool_calls > 0 THEN CAST(tool_latency_total_ms AS REAL) / executed_tool_calls END) AS avg_tool_latency_ms FROM events WHERE event IN ('session_end', 'session_crash') AND created_at > datetime('now', '-30 days')"

# --- Website + subscription analytics (requires 0016) ---

# Daily web visitors (distinct anonymous visitor_ids per UTC day, last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT date(e.created_at) AS day, COUNT(DISTINCT w.visitor_id) AS visitors, COUNT(*) AS pageviews FROM events e JOIN web_details w ON w.event_id = e.event_id WHERE e.event = 'web_pageview' AND e.created_at > datetime('now', '-30 days') GROUP BY day ORDER BY day"

# Pricing-page funnel: pageview -> CTA click by tier (last 30d).
# cta encodes the tier (plus_early_access / flagship_early_access / install).
wrangler d1 execute jcode-telemetry --command "WITH viewers AS (SELECT COUNT(DISTINCT w.visitor_id) AS n FROM events e JOIN web_details w ON w.event_id = e.event_id WHERE e.event = 'web_pageview' AND w.path = '/pricing' AND e.created_at > datetime('now', '-30 days')) SELECT w.cta, COUNT(DISTINCT w.visitor_id) AS clickers, (SELECT n FROM viewers) AS pricing_viewers, ROUND(1.0 * COUNT(DISTINCT w.visitor_id) / MAX(1, (SELECT n FROM viewers)), 4) AS click_through FROM events e JOIN web_details w ON w.event_id = e.event_id WHERE e.event = 'web_cta_click' AND w.path = '/pricing' AND e.created_at > datetime('now', '-30 days') GROUP BY w.cta ORDER BY clickers DESC"

# Traffic sources for pricing pageviews (last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT w.utm_source, w.utm_medium, w.utm_campaign, COUNT(DISTINCT w.visitor_id) AS visitors FROM events e JOIN web_details w ON w.event_id = e.event_id WHERE e.event = 'web_pageview' AND e.created_at > datetime('now', '-30 days') GROUP BY 1, 2, 3 ORDER BY visitors DESC"

# Subscription activations by tier (last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT tier, COUNT(DISTINCT account_id) AS accounts, COUNT(*) AS activations FROM events WHERE event = 'subscription_activated' AND created_at > datetime('now', '-30 days') GROUP BY tier ORDER BY accounts DESC"

# Budget exhaustion count (accounts hitting their token budget, by tier, last 30d)
wrangler d1 execute jcode-telemetry --command "SELECT tier, COUNT(*) AS exhaustion_events, COUNT(DISTINCT account_id) AS accounts FROM events WHERE event = 'subscription_budget_exhausted' AND created_at > datetime('now', '-30 days') GROUP BY tier ORDER BY exhaustion_events DESC"

# Subscription router errors by tier/model (last 7d)
wrangler d1 execute jcode-telemetry --command "SELECT tier, model_start AS model, COUNT(*) AS errors, COUNT(DISTINCT account_id) AS accounts FROM events WHERE event = 'subscription_router_error' AND created_at > datetime('now', '-7 days') GROUP BY 1, 2 ORDER BY errors DESC"

# account_linked join example: CLI usage (meaningful active days, last 30d)
# per subscribed account, via the telemetry_id <-> account_id anchor.
wrangler d1 execute jcode-telemetry --command "WITH links AS (SELECT DISTINCT telemetry_id, account_id FROM events WHERE event = 'account_linked') SELECT l.account_id, COUNT(DISTINCT d.activity_date) AS active_days_30d, SUM(d.turn_end_count) AS turns_30d FROM links l JOIN daily_active_users d ON d.telemetry_id = l.telemetry_id WHERE d.activity_date > date('now', '-30 days') AND d.meaningful_active > 0 GROUP BY l.account_id ORDER BY active_days_30d DESC LIMIT 50"

What to watch for

• session_start far exceeding session_end + session_crash for multiple days
• session_crash = 0 for long periods despite known crashes
• large lifecycle_ids_without_install counts
• a single telemetry ID dominating session totals (dev/test skew)
• zeroed transport totals after transport-aware releases (missing migration)
• daily_active_users row counts diverging from raw distinct-user checks
• headline DAU including build_channel != 'release' or raw event counts instead of distinct users
• headline DAU/installs including CI traffic (is_ci = 1); prefer the *_noci columns. A spike in ci_ids_30d / ci_install_ids from health.sql means CI runners are inflating user and install counts.

Accuracy notes

• DAU/WAU/MAU should be distinct telemetry_id counts, never event counts. Heavy users and long-running agents can emit thousands of turn_end events in a day.
• Use meaningful_release_active for headline product usage. It excludes local/dev/git-checkout traffic and open/close sessions with no meaningful lifecycle activity.
• For the cleanest headline numbers, prefer the *_noci columns, which additionally exclude is_ci = 1 traffic. Ephemeral CI runners mint a fresh telemetry_id per job, so unfiltered they look like brand-new users and installs, inflating active-user/install counts and depressing retention. The client also skips the install event under CI, so historical CI installs (before that ships) are the main residual source; the rollup's last_is_ci flag lets dashboards filter the rest. Raw events stay tagged (not dropped) so CI crash/error signal is still queryable.
• Meaningful activity is derived from session_end/session_crash and turn_end events. A turn_end only fires after a real user turn completes, so counting it keeps the metric accurate for users whose session_end is lost (process killed, machine shutdown, dropped final flush, or a session still open at UTC midnight).
• Retention pruning: D1 hard-caps databases at 500 MB. When the cap is hit, every insert fails with HTTP 500 and telemetry silently stops being recorded (this happened in June 2026; ~3 days of events were lost). The worker now runs a nightly cron (scheduled handler, see RETENTION_DAYS in src/worker.js) that prunes high-volume raw rows: turn_end/session_start/onboarding_step after 30 days, upgrade after 60, auth_success after 180, session_end/session_crash after 365, web_pageview/subscription_router_error after 90, web_cta_click/subscription_budget_exhausted after 365, subscription_login after 180. install, feedback, subscription_activated, and account_linked rows are never pruned. Because of this, historical user/DAU queries must read daily_active_users, not raw events - the rollup is backfilled across full history (migration 0014) and maintained at insert time.
• D1 100-column cap: production events has 98 columns after migration 0016 and D1 refuses ALTER TABLE ADD COLUMN past 100 (too many columns). Migration 0005's per-turn/session-cadence columns never applied to production events; migration 0013 moved those fields into turn_details/session_details, and migration 0016 put the web beacon fields in web_details for the same reason. Do not add new columns to events; add them to the detail tables.
• Raw events remain the source of truth within their retention windows. The daily_active_users table is an ingest-time rollup for cheap dashboard queries and is the durable record beyond those windows.
• The worker uses INSERT OR IGNORE keyed by event_id; rollups and detail rows are updated only when the canonical raw event insert succeeds, so client retries do not inflate counts.
• Telemetry still undercounts users who opt out (JCODE_NO_TELEMETRY, DO_NOT_TRACK, ~/.jcode/no_telemetry) or whose network blocks telemetry, and may overcount one person using multiple machines.