Custom Dashboard API2 Migration

2026-05-23

Phase 1: source-backed statistics in API2

• Added a native API2 statistics engine for source-backed custom dashboard widgets in rulebase-api2/src/lib/dashboard-statistics.ts.
• Wired /dashboard_widgets/preview to compute custom source previews in API2 instead of proxying Rails.
• Wired /dashboard_widgets/{id}/statistics to compute source-backed saved widget data in API2 when dashboard_widgets.source is present and no legacy dashboard_widget_type_id is set.
• Kept legacy dashboard widget type statistics proxied to Rails for now:
  • saved widgets with only dashboard_widget_type_id
  • /dashboard_widget_types/{id}/statistics
• The new API2 engine still reads Rails-owned public.* tables. No dashboard metadata tables moved to api2.* in this phase.
• Added prefix-id support for labels emitted by custom breakdown dimensions: groups, connections, partners, tags, customer issues, complaint categories, scorecards, criteria, checks, and justification options.
• Added focused DB route coverage for native source-backed saved statistics and grouped preview statistics.
• Added deeper pglite coverage for the native statistics engine:
  • generated date buckets and zero-filled count buckets
  • active/completed/non-overridden QA evaluation scoping
  • grouped model labels with Rails-compatible prefix ids
  • dashboard query filters for groups and channel types
  • saved date-range configs
  • conversation handling-time averages and Rails-style rounding
  • conversation tag config filters
  • malformed/stale prefix-id filters
  • legacy dashboard_widget_type_id fallback behavior

Compatibility scope

• Ported the generic Rails DashboardWidget::Statistics source/metric/dimension contract for these sources:
  • qa_agent_evaluation
  • criterion_result
  • check_result
  • dispute
  • complaint
  • sla_breach
  • conversation
  • conversation_tag
• Preserved Rails response shape: { data, labels }, generated date buckets, grouped combo keys, model prefix ids, and count/average value transforms.
• Preserved the current split between generic source-backed widgets and legacy widget-type classes. The 86 DashboardWidgetType::Statistics::* classes are not ported in this phase.

Verification

• Installed the API2 workspace dependencies with pnpm install --filter rulebase-api2 --frozen-lockfile from rulebase-web.
  • node-liblzma failed to build under Python 3.13 because distutils is missing, but pnpm treated it as non-fatal and Vitest was available.
• Ran node-only pglite coverage rather than booting Rails. The SQL/raw-query risks in this phase are covered by real Postgres-compatible pglite fixtures, so a Rails comparison harness was not needed for this pass.
• Passing targeted tests:
  • pnpm --filter rulebase-api2 test src/lib/dashboard-statistics.db.test.ts src/routes/dashboards.db.test.ts src/lib/prefix-id.test.ts
  • 28 tests passed across 3 files.
• Passing landing check:
  • pnpm --filter rulebase-api2 lint
• The new tests caught and fixed two implementation bugs:
  • parenthesized AT TIME ZONE bounds inside BETWEEN
  • explicit SQL array construction for raw ANY(...) / && filters, compatible with pglite and node-postgres
• Also hardened decodePrefixId so malformed hashids return null instead of throwing.
• No broad lint/typecheck was run, per repo guidance to avoid overchecking before PR landing.

Next phase

• Port high-traffic legacy widget-type statistics from Rails to API2 incrementally, or replace them with source-backed widget configs where the generic engine now covers the behavior.
• Add parity fixtures against Rails output once local dependencies are installed.
• Move dashboard metadata tables from public.* to api2.* only after the data/statistics surface no longer depends on Rails controller/model execution.

Phase 2: active legacy widget-type statistics in API2

• Ported native API2 coverage for every active dashboard widget type reference in rulebase-api/lib/tasks/stubs/dashboard_widget_types.json.
  • The active fixture currently contains 85 widget-type references.
  • canComputeDashboardWidgetTypeStatistics is now aligned to that fixture and exposes the native registry for tests.
  • The TypeScript switch is exhaustive for the native SQL handlers, so adding a fixture reference without a handler fails typecheck.
• Wired both API surfaces to use the native path for active widget-type references:
  • saved legacy widgets through /dashboard_widgets/{id}/statistics
  • direct widget-type requests through /dashboard_widget_types/{id}/statistics
• Rails fallback now remains only for unknown/non-active widget-type references, not for the active seeded dashboard widget catalog.
• Added a PostHog kill switch for the dashboard statistics engine:
  • flag key: custom_dashboard_api2_statistics
  • false or variant rails forces Rails proxy behavior for preview stats, saved widget stats, and direct widget-type stats
  • missing, true, variant api2, or an evaluation error keeps API2 as the default
  • API2 caches the evaluated flag per account/organization for 5 minutes
• Kept the two QA agent evaluation references on top of the generic source-backed API2 engine:
  • qa_agent_evaluation_average_score
  • qa_agent_evaluation_average_score_by_team
• Added native SQL handlers for the rest of the active legacy families:
  • QA score, handling time, customer sentiment, issue and complaint trends
  • SLA compliance, compliance violations, scorecard adherence/performance
  • evaluation volume/reviewer/contest trends
  • assignment review, response-time, connection-volume, and conversation-volume widgets
• Added prefix-id support needed by legacy outputs:
  • assignment_queue
  • compliance_criterion_group
  • conversation_field
  • qa_agent_evaluation
• Added pglite route coverage for native direct widget-type stats, saved legacy widget stats, manager-filtered widget-type stats, multi-agent saved-widget semantics, score flooring, org/active/effective scoping, and by-team labels.
• Added route coverage that proves flag-off preview, saved widget, and direct widget-type statistics proxy to Rails.
• Added registry coverage that compares the API2 native registry against the active Rails fixture.
• Added exhaustive pglite SQL smoke coverage that executes all 85 active widget-type references against Rails-shaped public.* tables.

Phase 2 verification

• Ran node-only pglite coverage rather than booting Rails. The new per-widget smoke suite executes the native API2 SQL for every active widget type, which caught and fixed a real GROUP BY/timezone-parameter bug in date-bucketed raw SQL.
• Passing targeted tests:
  • CENTRAL_LICENSE_KEY=dummy TIPTAP_PRO_TOKEN=dummy pnpm --filter rulebase-api2 test src/lib/dashboard-statistics-feature-flags.test.ts src/lib/dashboard-widget-type-statistics.test.ts src/lib/dashboard-widget-type-statistics.db.test.ts src/routes/dashboards.db.test.ts src/lib/dashboard-statistics.db.test.ts src/lib/prefix-id.test.ts
  • 124 tests passed across 6 files.
• Passing landing check:
  • pnpm --filter rulebase-api2 lint

Phase 3: removed Rails statistics proxy

• Removed the custom_dashboard_api2_statistics PostHog kill switch.
• Removed API2’s Rails proxy path for dashboard preview, saved-widget statistics, and direct widget-type statistics.
• Unsupported saved widget statistics now return 422 from API2 instead of falling through to Rails.
• Unknown direct widget-type statistics now return 404 from API2 instead of falling through to Rails.
• Deleted flag/proxy route coverage and the feature-flag unit test.
• Passing targeted test: CENTRAL_LICENSE_KEY=dummy TIPTAP_PRO_TOKEN=dummy pnpm --filter rulebase-api2 test src/routes/dashboards.db.test.ts

Phase 4: dashboard shadow tables in API2

• Added Drizzle declarations for the Rails dashboard metadata tables under api2.*.
• Generated rulebase-api2/drizzle/0060_mature_famine.sql to create the API2 shadow tables.
• Added same-migration SQL to copy existing public.dashboard* rows into the new api2.dashboard* tables while preserving numeric IDs.
• Reset the new API2 dashboard table sequences after the copy so future inserts continue above the copied IDs.
• Kept dashboards.dashboard_group_id as a post-copy NOT VALID FK to mirror Rails’ structure.sql and let verification report any legacy orphan rows.
• Added the dashboard API2 shadow-table verification SQL with row-count, row-diff, orphan, and sequence checks to run after applying the migration.
• Kept application reads and writes on the current public tables in this phase.

Remaining migration work

• Run Rails parity fixtures against representative real dashboard data before deleting Rails widget statistics classes.
• Switch dashboard application reads and writes from public.* to api2.* after the shadow copy has been verified.
• Remove Rails dashboard statistics controller/model paths once API2 owns the metadata tables and alert evaluation no longer calls Rails dashboard widget data.