coder / coder/coder

Merge template_usage_stats *_mins into app_usage_mins JSONB

Open
#27,413 2 comments 0 reactions 0 assignees View on GitHub
feature vs-code
Dominant language
Go
Stars
14.5k
Forks
1.5k
Avg merge
1d 20h
Merged PRs (30d)
601

Description

Phase 4 of the [Scalable Approach for Adding New IDE Session Types RFC](). The durable `template_usage_stats` table still has dedicated `vscode_mins`/`jetbrains_mins`/`ssh_mins`/`reconnecting_pty_mins` columns, so adding an IDE would still require a schema change there; this phase makes the durable rollup layer dynamic too.

Scope (RFC: "template_usage_stats must also be migrated"):

* `UpsertTemplateUsageStats`: have the `agent_stats_buckets` CTE produce `app_name`/`app_minutes` entries that flow into the existing `app_usage_mins` JSONB, the same way web app usage is stored today
* Key scheme: builtin session names will share `app_usage_mins` with web app slugs; confirm how the insights queries tell them apart before writing the backfill
* One-time migration: backfill the dedicated `*_mins` values into `app_usage_mins` for existing rows, then drop the dedicated columns, including the always-zero `sftp_mins` (dead since its TODOs were never implementable)
* `GetTemplateInsights`: read IDE minutes from `app_usage_mins` instead of dedicated columns; `convertTemplateInsightsApps` builds the builtin apps list dynamically
* `GetTemplateAppInsights` stays separate (it filters via `workspace_apps`, which builtin IDE sessions never join)

After this phase, a new IDE flows agent → normalized table → JSONB rollup with zero schema changes, which is the point of the RFC.

### Done when

- [x] Rollup writes per-app and per-family minutes and the dedicated columns are dropped (coder/coder#29109, child tables `template_usage_stats_session_apps` / `_session_families` instead of `app_usage_mins`, see the comment below on slug collisions)
- [x] Backfilled historical data produces identical `GetTemplateInsights` results (coder/coder#29109)
- [ ] Per-app read query over `template_usage_stats_session_apps` and dynamic `convertTemplateInsightsApps`
- [ ] A new app name reported through phase 1 shows up in template insights with no schema change

Depends on coder/coder#27410. Storage landed in coder/coder#29109; per-app history starts at migration 000591.

---

🤖 Created by Coder Agents on behalf of @EhabY.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.