perf(connectors): singleflight ensureWarehouseRunning to prevent cold-start thundering herd

[MarioCadenas]

Problem

SQLWarehouseConnector.ensureWarehouseRunning() runs an independent poll loop per caller. When N analytics queries hit a cold/stopped warehouse at the same time (typical dashboard: one SSE stream per chart), each request:

1. Calls warehouses.get on its own backoff schedule
2. Can call warehouses.start independently (didStart is per-call, not per-warehouse)
3. Only shares work after one caller observes RUNNING, via the 30s _recentlyRunning cache

That cache does not dedupe concurrent cold-start bursts — exactly when control-plane load is worst.

Location: packages/appkit/src/connectors/sql-warehouse/client.ts (ensureWarehouseRunning, ~L370–481), invoked from packages/appkit/src/plugins/analytics/analytics.ts (_handleQueryRoute, ~L283–294).

Impact

Dimension	Effect
Performance	N charts → ~N× warehouses.get polling (+ up to N× warehouses.start) for the same warehouseId
Security / abuse	Authenticated clients can amplify privileged workspace API traffic by opening many concurrent /api/analytics/query/* SSE connections
Cost	With autoStartWarehouse: true (default), redundant start + poll RPCs during cold start

Not introduced by appkit-ui #416 (frontend indicator). #416 improves UX during the wait but does not change backend fan-out.

Current mitigation (insufficient)

• _recentlyRunning + WAREHOUSE_RUNNING_CACHE_TTL_MS (30s): short-circuits subsequent requests after a prior success
• WarehousePollBackoff jitter: spreads poll times across loops, does not merge them
• CacheManager.getOrExecute singleflight: applies to SQL results, not readiness (readiness is intentionally outside query cache in analytics.ts)

Proposed fix

Add per-warehouseId in-flight deduplication on SQLWarehouseConnector, mirroring CacheManager.inFlightRequests:

// Sketch — on SQLWarehouseConnector
private _readinessInFlight = new Map<string, ReadinessInFlightEntry>();

interface ReadinessInFlightEntry {
  promise: Promise<void>;
  subscribers: Set<(update: WarehouseStatusUpdate) => void>;
  refCount: number;
  sharedController: AbortController;
}

Owner (first caller for warehouseId):

• Runs the existing poll loop
• Issues one warehouses.get / one warehouses.start sequence
• Broadcasts each WarehouseStatusUpdate to all subscribers

Joiners (concurrent callers):

• Register onStatus in subscribers
• await shared promise
• Ref-count abort: shared work aborts only when all waiters have abandoned (same pattern as CacheManager.getOrExecute)

On RUNNING: set _recentlyRunning, resolve promise, clear in-flight entry.

On error / timeout: reject all waiters, clear entry so a later retry can start fresh.

Edge cases

• Late joiner after terminal state: resolve immediately from _recentlyRunning or replay last emitted status
• Mixed options (timeoutMs, autoStart, signal): owner wins; document that concurrent callers for the same warehouse share owner options
• Telemetry: one sql.warehouseReady span per warehouse burst, attribute db.warehouse.waiter_count

Test plan

• Unit test: 10 concurrent ensureWarehouseRunning calls → exactly 1 warehouses.start, bounded warehouses.get count (mock SDK)
• Unit test: all subscribers receive the same status sequence (including synthetic STARTING)
• Unit test: ref-count abort — one waiter aborts, shared loop continues; all abort, loop cancels
• Unit test: after first success, _recentlyRunning still short-circuits without in-flight entry
• Integration test (analytics plugin): N parallel query SSE streams on STOPPED warehouse → single start, all streams receive warehouse_status then results

References

• Follow-up from review of appkit-ui feat(appkit-ui): generic ResourceStatusProvider + indicator (with analytics adapter) #416 (resource status indicator)
• Existing singleflight reference: packages/appkit/src/cache/index.ts (getOrExecute, inFlightRequests)