Skip to main content

Architecture Design Document: Replacing Cube.js with Custom Moleculer Analytics & Server-Side Reporting

RFC Number: RFC-013
Feature: Cube.js Decommissioning, Native Moleculer Analytics Service, Global BI Semantic AST, Real-time WebSocket Subscriptions, Server-Side Scheduled Reporting & External BI ACL Gateway
Status: Proposed
Author: UniConnect Core, Web & Platform Engineering
Date: 2026-10-27


1. Executive Summary & Goals​

This RFC details the comprehensive architectural plan to completely eliminate Cube.js and CubeStore from the UniConnect platform and replace them with a native, lightweight Moleculer microservice (analytics) in uc-enterprise-core.

In addition to replacing the engine, this design moves away from Cube's rigid OLAP cube format to an open, globally recognized BI Semantic Query Specification (the standard JSON AST popularized by Metabase MBQL and Apache Superset).

Key Architectural Objectives​

  1. Reclaim 1.5 GB – 4 GB+ RAM: Decommission the uc-enterprise-cube Node.js container and CubeStore Rust daemon per deployment.
  2. Global Standard Semantic Query Specification: Eliminate Cube's query limitations by supporting native, nested boolean logic (AND/OR trees), ad-hoc relational joins (LEFT, INNER, FULL OUTER), and aggregate FILTER (WHERE ...) clauses.
  3. 100% Backward Compatibility: An automated legacy query adapter (convertLegacyCubeQuery) guarantees all existing saved report templates and dashboard widgets continue functioning without database schema migrations or manual edits.
  4. Move Custom Fields & Blending to Server: Migrate client-side joining (dataJoinEngine.ts) and formula calculations (formulaEngine.ts) from the browser to PostgreSQL (CTEs) and Node.js.
  5. Server-Side Scheduled Reporting & Emailing: Enable automated, headless report generation (Excel, PDF, CSV) and scheduled email delivery using schedule.service.js (BullMQ) and email.service.js.
  6. External BI Connector with Service Account ACL Enforcement: Provide a lightweight PostgreSQL wire-protocol gateway (analytics-bi-gateway on port 15432) that authenticates Power BI and Tableau service accounts against the users table via bcrypt and dynamically enforces Row-Level Security (RLS) and group ACLs.

2. Global Standard Query Specification vs. Proprietary Language​

Why an Open BI Standard (Metabase MBQL / Apache Superset AST)?​

Inventing a custom, proprietary query language creates technical debt, requires custom documentation, and makes future integrations harder.

Instead, modern BI platforms use an open Semantic JSON AST (Abstract Syntax Tree):

  • Metabase MBQL (Metabase Query Language): The most widely adopted open-source JSON query format in the BI industry.
  • Apache Superset QueryContext: The open Apache foundation standard for defining dimensions, ad-hoc metrics, and complex filter trees.

Both formats follow the same foundational schema: a declarative, JSON-serializable representation of analytical queries that maps 1:1 with UI drag-and-drop builders while compiling cleanly to SQL on any relational database.

The Open Semantic Query Spec Schema​

{
"source": "calls",
"joins": [
{
"strategy": "left-join",
"target": "users",
"condition": {
"operator": "=",
"lhs": "calls.userId",
"rhs": "users.id"
}
},
{
"strategy": "left-join",
"target": "statusLog",
"condition": {
"operator": "and",
"clauses": [
{ "operator": "=", "lhs": "calls.userId", "rhs": "statusLog.userId" },
{ "operator": "=", "lhs": "DATE(calls.startTime)", "rhs": "DATE(statusLog.timestamp)" }
]
}
}
],
"dimensions": [
{ "field": "users.name", "alias": "agent_name" },
{ "field": "calls.startTime", "temporalUnit": "day", "alias": "date" }
],
"metrics": [
{
"name": "total_calls",
"aggregate": "count",
"field": "calls.id"
},
{
"name": "answered_calls",
"aggregate": "count",
"field": "calls.id",
"filter": {
"operator": "=",
"field": "calls.status",
"value": "Answered"
}
},
{
"name": "answer_rate",
"expression": "([answered_calls] * 100.0) / NULLIF([total_calls], 0)",
"format": "percentage"
},
{
"name": "total_online_duration",
"aggregate": "sum",
"field": "statusLog.duration"
}
],
"where": {
"operator": "or",
"clauses": [
{
"operator": "and",
"clauses": [
{ "operator": "=", "field": "calls.status", "value": "Missed" },
{ "operator": ">=", "field": "calls.queueWaitTime", "value": 30 }
]
},
{
"operator": "and",
"clauses": [
{ "operator": "=", "field": "calls.status", "value": "Answered" },
{ "operator": "in", "field": "users.department", "value": ["Support", "Escalations"] }
]
}
]
},
"orderBy": [
{ "field": "total_calls", "direction": "desc" }
],
"limit": 50,
"offset": 0
}

PostgreSQL Generated by the Compiler​

SELECT
u."name" AS "agent_name",
DATE_TRUNC('day', c."startTime" AT TIME ZONE 'UTC') AS "date",
COUNT(c."id") AS "total_calls",
COUNT(c."id") FILTER (WHERE c."status" = 'Answered') AS "answered_calls",
ROUND((COUNT(c."id") FILTER (WHERE c."status" = 'Answered') * 100.0 / NULLIF(COUNT(c."id"), 0)), 2) AS "answer_rate",
COALESCE(SUM(s."duration"), 0) AS "total_online_duration"
FROM "calls" c
LEFT JOIN "users" u ON c."userId" = u."id"
LEFT JOIN "statusLog" s ON c."userId" = s."userId" AND DATE(c."startTime") = DATE(s."timestamp")
WHERE
/* RLS injected by Moleculer */
(c."userId" = $1 OR c."groupId" = ANY($2))
/* User-defined filter tree */
AND (
(c."status" = 'Missed' AND c."queueWaitTime" >= 30)
OR
(c."status" = 'Answered' AND u."department" IN ('Support', 'Escalations'))
)
GROUP BY 1, 2
ORDER BY "total_calls" DESC
LIMIT 50;

3. High-Level Architecture Diagrams​

3.1 Current vs. Target Architecture​

3.2 Query Processing Pipeline & Legacy Adapter​

3.3 Real-Time WebSocket Subscription Flow​

3.4 Server-Side Scheduled Reporting & Emailing Pipeline​

3.5 External BI Connector (Power BI / Tableau) with Service Account ACL Enforcement​


4. Phase-by-Phase Implementation Plan​

Phase 1: Semantic Layer & SQL AST Compiler​

Target Directory: uc-enterprise-core/services/analytics/

  1. Semantic Model Registry (methods/semantic-registry.js):

    • Centralize field mappings for static tables (calls, summarizecalls, statusLog, users, groups, campaign, leads, messages).
    • Dynamic CRM table auto-discovery via PostgreSQL/Prisma introspection.
    • Maintain the entity join graph (e.g. calls.userId ➔ users.id, calls.id ➔ callSegments.callId).
  2. AST-to-SQL Compiler (methods/sql-compiler.js):

    • Built on Knex.js.
    • Boolean Logic Tree Compiler: Recursively compile { operator: "and"|"or", clauses: [...] } into SQL (c1 AND c2) OR (c3 AND c4).
    • Join Generator: Translate explicit joins or auto-resolve shortest-path joins when fields from multiple tables are selected.
    • Filtered Aggregate Compiler: Translate conditional metrics into PostgreSQL FILTER (WHERE ...) clauses.
    • Time Bucket Compiler: Generate timezone-aware DATE_TRUNC(granularity, col AT TIME ZONE tz).
  3. Row-Level Security (RLS) Filter Injector (methods/rls-filter.js):

    • Read ctx.meta.accessLevel, ctx.meta.ownerId, and ctx.meta.accessGroups.
    • Inject security constraints directly into the root WHERE clause:
      • own: (t."createdById" = $ownerId OR t."assignedToUserId" = $ownerId OR t."userId" = $ownerId)
      • group: (t."groupId" = ANY($groupIds) OR t."assignedToGroupId" = ANY($groupIds))
  4. Analytics Endpoints (analytics.service.js):

    • Action query: POST /api/analytics/query (with legacy fallback alias POST /cubejs-api/v1/load).
    • Action meta: GET /api/analytics/meta (returns entity/field dictionary for builder palettes).
    • Action sql: POST /api/analytics/sql (returns generated SQL for inspection/debugging).

Phase 2: Server-Side Blending & In-Database Calculations​

Target Directory: uc-enterprise-core/services/analytics/engine/

  1. SQL Common Table Expression (CTE) Pushdown:
    • When a widget references multiple distinct datasets (e.g., call volume from calls and agent occupancy from statusLog), compile them into a unified query with FULL OUTER JOIN or LEFT JOIN.
  2. Formula Evaluator (formula-evaluator.js):
    • Mathematical expressions evaluate directly inside the SQL SELECT statement (NULLIF, arithmetic, percentage).
    • Non-linear custom formulas evaluate in Node.js on the server before serializing rows.
  3. Decommission Client-Side Join Engine:
    • dataJoinEngine.ts in the browser is completely bypassed; widgets receive fully joined, calculated datasets.

Phase 3: Native Real-Time Subscriptions via WebSocket​

Target Directory: uc-enterprise-core/services/websocket/ & services/analytics/

  1. Subscription Protocol on Existing Gateway:
    • Client sends over existing WebSocket connection:
      {
      "action": "analytics.subscribe",
      "params": {
      "subscriptionId": "sub_wallboard_kpis",
      "query": { ... },
      "intervalSeconds": 10
      }
      }
  2. Subscription Registry & Hash Diffing:
    • Tracks active subscriptions in memory: { connectionId, subscriptionId, query, hash, rlsContext, intervalTimer }.
    • On tick, run query, compute MD5(JSON.stringify(rows)), and push updates only when the hash changes.
  3. NATS Event Invalidation (Instant Wallboard Updates):
    • Listen to domain events: call.created, call.ended, agent.statusChanged.
    • Immediately flag matching subscriptions as dirty and push fresh data without waiting for the timer.
  4. Auto Unsubscribe:
    • Clean up subscriptions and clear timers on socket disconnect.

Phase 4: Frontend Migration & Client Adapter​

Target Directory: uc-enterprise-web/src/

  1. Lightweight Drop-in Client Adapter (cubeClient.ts):
    • Remove @cubejs-client/core and @cubejs-client/ws-transport dependencies.
    • Implement analyticsClient matching the existing interface:
      • .load(query) ➔ axiosInstance.post('/api/analytics/query', { query })
      • .meta() ➔ axiosInstance.get('/api/analytics/meta')
      • .subscribe(query, options, callback) ➔ routes over existing Moleculer WebSocket
    • Expose lightweight ResultSet wrapper (.rawData(), .tablePivot(), .series(), .total()).
  2. Update Visual Filter & Join Builders:
    • Update FilterRow.tsx to support nested AND / OR group toggles.
    • Simplify DataJoinConfigurator.tsx to emit declarative join definitions directly into the query AST.
  3. Transparent Hook Migration:
    • Hooks like useWidgetQueryData.ts, useCubeSubscriptions.ts, and useCubeSubscription.ts seamlessly operate through the adapter.

Phase 5: Server-Side Scheduled Reporting & Emailing​

Target Directory: uc-enterprise-core/services/report-scheduler/

  1. Database Schema (prisma/schema.prisma):
    model ReportSchedule {
    id String @id @default(uuid())
    reportId String
    report Report @relation(fields: [reportId], references: [id], onDelete: Cascade)
    title String
    cronExpression String // e.g. "0 8 * * 1" (Every Monday at 8 AM)
    timezone String @default("UTC")
    format String // "xlsx" | "pdf" | "csv"
    recipients String[] // Recipient email addresses
    emailSubject String?
    emailBody String?
    filterOverrides Json? // e.g. { "relativeTime": "last_week" }
    isActive Boolean @default(true)
    ownerId String
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    }
  2. Scheduler Service (report-scheduler.service.js):
    • Backed by BullMQ via schedule.service.js.
    • On cron trigger:
      1. Loads Report layout and ReportSchedule configuration.
      2. Resolves dynamic relative dates ("yesterday", "last_week", "last_month").
      3. Calls analytics.query under the author's RLS permissions.
      4. Renders output document:
        • Excel (.xlsx): Multi-sheet workbook with formatting via exceljs.
        • CSV (.csv): Clean tabular export via json2csv.
        • PDF (.pdf): Headless styled document via pdfmake or HTML template.
      5. Dispatches email with attachment via email.service.js.
      6. Records execution in audit log.
  3. Frontend Schedule Manager UI:
    • Add a "Schedule & Email" modal in the Report Viewer.
    • Configure frequency, time of day, recipient list, and format.

Phase 6: External BI Connector Gateway with Service Account ACL Enforcement​

Target Directory: uc-enterprise-core/services/analytics/bi-gateway/

  1. Service Account Authentication Handshake (checkSqlAuth Parity):
    • Emulate PostgreSQL Frontend/Backend protocol v3 on port 15432 using pg-gateway.
    • Verify client ID and secret against the users table via bcrypt.
    • Load permissions and userGroups from Redis/Postgres and store on socket session context.
  2. SQL AST ACL Rewriting (queryRewrite Parity):
    • Intercept SQL queries from Power BI and Tableau.
    • Parse AST: pass information_schema and pg_catalog queries untouched for clean metadata browsing.
    • For business tables (calls, statusLog, leads), inject ACL WHERE constraints matching the service account's accessLevel:
      • group: AND ("calls"."groupId" = ANY($groupIds) OR "calls"."userId" = $userId)
      • own: AND ("calls"."userId" = $userId)
    • Execute rewritten SQL on the PostgreSQL pool and stream standard row packets to Power BI.

Phase 7: Decommissioning uc-enterprise-cube​

  1. Container Cleanup:
    • Remove uniconnect-cube and uniconnect-cubestore services from docker-compose.yml, Kubernetes manifests, and Helm charts.
  2. Environment Variables:
    • Remove VITE_REACT_APP_CUBEJS_API_URL and VITE_REACT_APP_CUBEJS_SOCKET_URL.
  3. Repository Archival:
    • Archive the uc-enterprise-cube repository.

5. Implementation Schedule & Milestones​

Sprint / WeekFocus AreaDeliverables
Sprint 1• Semantic Model Registry & Join Graph
• Knex-based SQL Compiler (Metrics, Dimensions, FILTER)
• RLS Security Injector & Legacy Query Adapter
• analytics.query and analytics.meta working in uc-enterprise-core.
• Legacy Cube queries successfully translated and executed.
Sprint 2• Boolean Logic Tree (AND/OR clauses)
• Multi-source CTE Join Engine (Server-side Blending)
• Dynamic CRM table auto-introspection
• Complex queries with nested OR conditions and multi-table LEFT JOINs executing in single PostgreSQL queries.
Sprint 3• WebSocket Subscription Manager in Moleculer
• Hash-diffing & NATS invalidation triggers
• Drop-in client adapter in uc-enterprise-web
• uc-enterprise-web reports, wallboards, and dashboards running on native Moleculer services.
• Cube client libraries removed.
Sprint 4• ReportSchedule Prisma schema & BullMQ worker
• Headless Exporters (ExcelJS, CSV, PDF)
• Email dispatch integration
• Schedule UI in frontend
• Automated, scheduled reports delivered to email inboxes on cron schedule.
Sprint 5• External BI Connector Gateway (analytics-bi-gateway on port 15432)
• Power BI service account authentication & SQL AST ACL rewriting
• Power BI & Tableau successfully querying port 15432 with strict ACL enforcement.
Sprint 6• Performance benchmarking & Redis query caching
• Final cleanup & Cube container teardown
• Cube.js and CubeStore completely removed from infrastructure.
• Server RAM usage decreased by >70%.

6. Verification & Quality Gates​

  1. Query Parity & AST Verification:
    • Run a suite of 50 existing production reports through the legacy adapter; verify identical datasets and data types.
    • Run test suite for complex boolean trees (A AND B) OR (C AND D) and multi-table joins.
  2. Performance & Memory Benchmark:
    • Compare memory consumption under 100 concurrent dashboard queries (Before: Cube + CubeStore vs. After: Moleculer).
    • Ensure latency is equal or lower due to aggregate FILTER pushdown and avoiding Cube orchestration hops.
  3. Subscription Reliability:
    • Verify active wallboards receive instant updates when call events fire on NATS.
    • Confirm socket disconnects cleanly release memory and timers.
  4. Scheduled Report Delivery:
    • Verify recurring cron jobs execute headlessly, resolve relative dates correctly, and dispatch valid attachments via email.
  5. Power BI ACL Verification:
    • Connect Power BI using a service account with accessLevel: "group"; assert that only rows matching assigned groups are returned.