Feature request: Usage dashboard and team leaderboard

[phoenixking25]

Problem

There's no visibility into how the platform is being used across a team. Managers and developers can't answer basic questions:

• Who's using the platform the most? How effectively?
• What's the total cost? Average cost per session?
• Which repos generate the most sessions? The most PRs?
• What's the success rate? Are sessions completing or failing?
• Are we getting value from this — how many PRs are being created?

The session list shows individual sessions, but there's no aggregated view.

Proposed Feature: Usage Dashboard + Team Leaderboard

A dashboard page in the web UI that aggregates session data into team-level metrics, per-user rankings, and time-series trends.

Dashboard Sections

1. Summary Metrics (top cards)

Metric	Source
Total sessions	Count from D1 session index
Total users	Distinct users from session index
Total cost	Sum of total_cost from session state (from #416 / session cost tracking)
Avg cost per session	Total cost / total sessions

2. Sessions Over Time (time-series chart)

Two chart views:

• By user — stacked area chart showing each user's daily session count
• By status — stacked area showing completed/failed/stopped per day

Data source: D1 session index grouped by DATE(created_at) and user/status.

3. Sessions by Repository (donut chart)

Donut chart showing session distribution across repos. Each segment = a repo with session count. Helps identify which codebases benefit most from the platform.

4. Pull Request Metrics

Metric	Source
Total PRs created	Count of PR artifacts across sessions
Sessions with PRs	Sessions that produced at least one PR
PR rate	Percentage of sessions that created a PR
Avg PR session duration	Average duration of sessions that created PRs

5. Team Leaderboard Table

Per-user rankings with sortable columns:

Column	Description
Rank	Position by total sessions
User	GitHub username
Sessions	Total (completed/failed/stopped breakdown)
Success Rate	% of sessions that completed successfully
PRs	Total PRs created
Turns	Total agent turns across sessions
Avg Duration	Average session duration
Cost	Total cost in USD
Streak	Consecutive days with at least one session
Last Active	Relative timestamp of last session

Visual touches:

• Gold/silver/bronze badges for top 3
• Success rate progress bar (green >80%, amber >50%, red <50%)
• Streak fire emojis (1 for 1-2 days, 2 for 3-6 days, 3 for 7+)
• Active session indicator (pulsing blue dot)

Data Requirements

The current D1 session index has most of what's needed:

• created_at, updated_at, status, repo_owner, repo_name, model

Missing columns that would need to be added to the D1 session index:

• user_id / scm_login — who created the session (currently only in DO SQLite, not D1)
• total_cost — session cost (depends on session cost tracking feature)
• pr_count — number of PRs created (currently only in DO artifacts table)
• total_turns — number of agent turns
• duration_seconds — session duration

Alternatively, the leaderboard API could query each session's DO for this data and cache the aggregates, but that's O(n) DOs per request. The better path is denormalizing key metrics into the D1 index on session completion.

Time Range Selector

All metrics filterable by time range: 7d / 14d / 30d / 90d. Default 30d.

API Endpoints

GET /leaderboard?days=30           → LeaderboardResponse (summary + per-user entries)
GET /leaderboard/timeseries?days=30 → daily session counts (for charts)
GET /leaderboard/timeseries/by-user?days=30 → daily per-user session counts
GET /leaderboard/repos?days=30      → repo breakdown (for donut chart)
GET /leaderboard/prs?days=30        → PR metrics

Implementation Approach

Phase 1: D1 denormalization — on session completion, write key metrics (user, cost, PR count, duration, turns) to the D1 session index. This makes aggregation queries fast without touching DOs.

Phase 2: API endpoints — SQL aggregation queries against D1. No complex joins — just GROUP BY user/repo/date on the session index table.

Phase 3: Web UI — new /leaderboard page with the dashboard sections above. Charts can use a lightweight library (e.g., recharts or custom SVG).

Why this matters

• Adoption visibility — see who's using the platform and how much
• Cost governance — track spend per user and per repo
• Team motivation — leaderboard gamification drives adoption
• ROI measurement — PR rate and cost per PR help justify the platform investment
• Operational health — success/failure rates surface systemic issues

Happy to contribute the implementation.

@ColeMurray

[ColeMurray]

@phoenixking25 onboard with the majority of this. Adding analytics has been something i've wanted to add for a bit.

I'm not totally sold on the gamification aspect, but i do like the idea of general analytics usage tracking.

Some nuances that I'm aware of that add some challenges:

• if using a ChatGPT subscription, OpenCode forwards all the costs as $0.00, so this may not be a useful metric depending on which provider is being used.
• The agent will usually use the create-pull-request-tool, which is what tracks the artifacts, however it can create PRs directly via the gh cli / api, which would not be directly tracked within the system. A mitigation can probably be added to regex a PR creation from the tool result
  • Related, a useful metric to track is the result of the PR, whether open/closed/merged and similar. this requires some plumbing into the webhooks to track behavior
• because the system is multi-player, tracking by user isn't total clear on how attribution should work. for example, one user can start the session, but another the one who actually opens the pull request. it's not totally clear how usage should work in this case at the per user level
• duration is a bit nuanced, as there is per-turn duration, but the session may sit idle for some period of time before the next turn.
• many sessions are created from slack which does not have a mapping to the actual users within the system, just slackIDs

I had claude draft up a more generic "analytics" proposal.

Proposed Feature: Usage Analytics Dashboard

A new /analytics page that aggregates session data into usage metrics, per-user breakdowns, and time-series trends. Forward-looking only
(no backfill of existing sessions).

---

Dashboard Sections

1. Summary Cards

Metric	Source
Total sessions	COUNT(*) from D1 sessions
Active users	COUNT(DISTINCT scm_login) from D1 sessions (new column)
Total cost	SUM(total_cost) from D1 sessions (new column)
Avg cost/session	Computed from above
PRs created	SUM(pr_count) from D1 sessions (new column)

2. Sessions Over Time (time-series chart)

Two toggleable views:

• By user — stacked area chart, daily session count per user
• By status — stacked area chart, daily completed/failed/cancelled counts

Source: D1 GROUP BY date(created_at/1000, 'unixepoch') grouped by scm_login or status.

3. Sessions by Repository (donut or bar chart)

Session distribution across repo_owner/repo_name. Identifies which codebases drive the most platform usage.

4. Per-User Table

Sortable table with no ranking or gamification:

Column	Source
User	scm_login
Sessions	Count (with completed/failed/cancelled breakdown)
Completion Rate	completed / (completed + failed + cancelled)
PRs	SUM(pr_count)
Messages	SUM(message_count)
Total Cost	SUM(total_cost)
Avg Duration	AVG(completed_at - created_at)
Last Active	MAX(updated_at)

5. Time Range Selector

All sections filterable by: 7d / 14d / 30d / 90d. Default 30d.

---

Data Requirements

Current D1 sessions columns (migrations 0002–0013):

id, title, repo_owner, repo_name, model, reasoning_effort, base_branch,
status, parent_session_id, spawn_source, spawn_depth, automation_id,
automation_run_id, created_at, updated_at

New D1 columns needed:

Column	Type	When Written
scm_login	TEXT	At session creation — router already has user context from the auth flow; add to SessionIndexStore.create()
total_cost	REAL DEFAULT 0	Synced on session completion via a new syncSessionMetrics() in the DO
active_duration_ms	INTEGER	Active execution time when status transitions to a terminal state (completed, failed, cancelled). Needed for accurate

|
| message_count | INTEGER DEFAULT 0 | Synced on session completion from repository.getMessageCount() |
| pr_count | INTEGER DEFAULT 0 | Synced on session completion from repository.listArtifacts() filtered to type='pr' |

New D1 indexes:

CREATE INDEX idx_sessions_scm_login ON sessions(scm_login, created_at DESC);
CREATE INDEX idx_sessions_created_at ON sessions(created_at DESC);

Sync approach:

• scm_login is written once at creation. The router's handleCreateSession already has the user identity (it passes it to the DO for the
  participants row). Pass the same value to SessionIndexStore.create().
• Cost, message count, PR count, and active_duration_ms are synced on session completion. Add a syncSessionMetrics() method in the DO, called
  from transitionSessionStatus when status is terminal. Follows the existing fire-and-forget ctx.waitUntil pattern used by
  syncSessionIndexStatus.
• No backfill — existing sessions will have NULL for new columns. Queries use COALESCE to handle gracefully.

---

PR Tracking Gap

PR metrics will only capture PRs created through SessionPullRequestService.createPullRequest() — the UI-triggered flow. PRs created by the
agent inside the sandbox via gh pr create or gh api are not tracked as artifacts. sandbox-events.ts does not parse tool_result events for
PR URLs.

This means pr_count will undercount when the agent uses gh directly.

Suggested follow-up (separate issue): Add PR URL detection in sandbox-events.ts when processing tool_result events — a regex match on
GitHub PR URLs in tool output could auto-create artifact rows. Alternatively, a GitHub webhook matching PRs to sessions by branch name
would be more reliable.

For the initial implementation, document that PR metrics reflect PRs created through the platform's built-in tool.

---

API Design

Three control plane endpoints:

GET /analytics/summary?days=30
→ { totalSessions, activeUsers, totalCost, avgCost, totalPrs, statusBreakdown }

GET /analytics/timeseries?days=30&groupBy=user|status
→ { series: [{ date, groups: { [key]: count } }] }

GET /analytics/breakdown?days=30&by=user|repo
→ { entries: [{ key, sessions, completed, failed, cost, prs, messageCount, avgDuration, lastActive }] }

Backed by D1 queries in a new AnalyticsStore class (pattern matches SessionIndexStore). Web app proxies through /api/analytics/* with
existing controlPlaneFetch() + HMAC auth. Client fetches via SWR.

---

Implementation Phases

Phase 1: D1 Schema + Sync Plumbing

• D1 migration 0014_add_analytics_columns.sql
• Modify SessionIndexStore.create() to accept and write scm_login
• Modify router's handleCreateSession to pass scm_login
• Add SessionIndexStore.updateMetrics() method
• Add syncSessionMetrics() in the DO, called on terminal status transitions

Phase 2: Analytics API

• New AnalyticsStore class in packages/control-plane/src/db/
• Three route handlers in the control plane router
• Next.js API proxy routes at packages/web/src/app/api/analytics/

Phase 3: Web UI

• New /analytics page under the (app) route group (inherits sidebar layout)
• recharts (~45KB gzipped) for charts — no chart library exists in the project today; recharts pairs well with shadcn/ui + Tailwind
• Summary cards using existing shadcn Card components
• Per-user table following existing patterns from sessions list / automations pages
• SWR hooks with appropriate refreshInterval

---

[ColeMurray]

phase 1:
#474

[ColeMurray]

this is now implemented and generally available. there's some iterations to be done on attribution, specifically for non-web pathways, which currently show as unknown. Going to resolve this one and follow-up with attribution fixes separately