Merge template_usage_stats *_mins into app_usage_mins JSONB
- 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
Assessment
This issue has not been assessed yet.