Adding a new IDE session type (Cursor, Windsurf, ...) currently requires changes to 15+ files across migrations, proto, server, SDK, and UI. This PR replaces the fixed session_count_* columns on the ephemeral workspace_agent_stats table with a normalized workspace_agent_session_counts child table keyed by app name, and makes the agent report session counts as a dynamic proto map. New session types then flow end-to-end (agent counters → stats pipeline → template usage rollup) with zero schema, proto, or server changes.

Phase 1 of the Scalable Approach for Adding New IDE Session Types RFC. External surfaces are intentionally unchanged: all read queries keep their row shapes, so the deployment stats API, Prometheus gauge names, and telemetry snapshots are byte-identical. Later phases add the API map field and family-labeled metrics (2), migrate connection_logs off its type enum (3), merge template_usage_stats *_mins into app_usage_mins (4), and version-gate the IDE clients (5).

NOTE: The migration copies the ephemeral stats buffer (~1 day of rows, bounded by dbpurge) into the child table in a single pass, drops the four session_count_* columns, and recreates the insights covering index without them. There is no reporting gap. The down migration restores the four canonical app names.

App names are stored in canonical form (supersedes the RFC's raw-storage rule)

Every app name is folded at stats ingestion: lowercased, hyphens folded to underscores, null bytes stripped, capped at 64 runes, empty becomes unknown. Cursor, cursor, and my-new-ide/my_new_ide are one storage key.

This deliberately supersedes the RFC's "casing preserved, not rewritten at storage" decision, and the RFC needs a matching amendment. Rationale: app_name is an aggregation key (primary key component, rollup JSONB key, GROUP BY target), not a forensics field like audit_logs.user_agent. Once phase 4 rolls arbitrary names into the durable app_usage_mins, raw storage would mean a name cataloged after its first appearance splits into two keys forever, and every read path carries a fold-or-undercount obligation. Canonical storage removes that obligation, keeps SQL exact-match, and costs nothing now: clients only send non-standard names after the phase-5 gate, so no data can exist under the old policy. The fold is deterministic and total (not the rejected regex sanitizer; it never rejects or drops), and display casing for cataloged IDEs returns via the phase-2 metadata map.

Key changes:

• workspace_agent_session_counts (workspace_agent_stats_id, app_name, count) with ON DELETE CASCADE. App names canonicalized at ingestion as described above; no CHECK constraint.
• Proto Stats.session_counts map (field 13) as Agent API v2.11; fields 8–11 are deprecated and converted server-side by workspacestats.SessionCountsFromProto, so old agents keep working through the upgrade window. Agents dial the version they report via the new ConnectRPC211 helpers; the unused ConnectRPC210WithRole is removed.
• Reports are capped at 64 distinct app names; overflow counts are aggregated under the reserved other app name (well-known names always kept, remainder chosen lexicographically for determinism). This bounds child-table fan-out from malicious or buggy agents; legitimate agents report a handful of entries.
• The agent counts sessions per session type via a typed syncmap.Map and omits idle types from reports; JetBrains keeps its channel-watcher special case via family lookup; agent session metrics are labeled by family to keep cardinality bounded.
• New coderd/idemetadata leaf package: one map from canonical app names to families, grouping stored names at read time. Adding an IDE is one map entry, no migration, and applies retroactively to stored data; entries ship for Cursor, Windsurf, Positron, VSCodium, Antigravity, Trae, Kiro, and Devin. The agentssh magic session type constants derive from it, so the vocabularies cannot drift. Display names ship with their phase-2 consumers.
• The batcher passes session counts as a JSONB array (same pattern as connections_by_proto), exploded into the child table within the same insert statement.
• The workspace usage API and coder ssh --usage-app accept arbitrary app names; codersdk.AllowedAppNames is removed.

Behavior changes, invisible until clients start sending non-standard names in phase 5: unknown app names are accepted and stored in canonical form instead of rejected (API) or coerced to ssh (CLI), and unknown session types are counted instead of silently dropped.

Benchmarks

Measured on PostgreSQL 13 with 1.73M stats rows (300 agents × 24h @ 15s) and 1.3M child rows; results byte-identical to the old queries in every case. The rollup and insights queries originally used per-row lateral probes and regressed 7.6×/7.7×; rewriting them as direct hash joins (the inclusion filter becomes the join, DISTINCT/MAX aggregates tolerate the fan-out) recovered most of it:

Since these measurements, the four pivots whose aggregates only touch child counts (deployment stats, per-agent stats, and both usage minute-bucket CTEs) were relaxed from per-row laterals to plain joins with FILTER pivots, letting the planner choose between PK probes and hash joins; the two *AndLabels queries keep the lateral because they also aggregate parent columns.

A covering INCLUDE (count) index was evaluated and rejected: the hash-join plans never touch it, so it would be pure write overhead. Total storage at this scale dropped 697 MB → 666 MB, since the 40% of rows with no session activity now store nothing.

Risks and mitigations

• Write amplification: up to one child row per active session type per stats row (only counts > 0 are stored; idle rows store nothing, previously four zero bigints). Bounded by the ~1 day retention, the 64-name cap, and cascade on purge. Net storage decreased in the benchmark.
• Query plans: benchmarked above; the two window-scan queries pay an inherent second-table join (2.4×/1.7×, ~100/~660 ms absolute at 1.7M rows) and everything else is at parity. The rollup's child-side seq scan scales with total child table size, which the daily purge keeps bounded.
• Clean-cut rollback: the down migration restores only the four canonical names. Exposure window is empty by construction: clients only send non-standard names once the phase-5 gate flips, which targets the phase-3 release.
• Open name space: bounded by auth (workspace owners can only spam their own stats, same as today), the 64-rune name cap, the 64-entry report cap with other aggregation, and the ~1-day purge. Prometheus is unaffected (family labels).

🤖 This PR was generated by Coder Agents on behalf of @EhabY.