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
- Reclaim 1.5 GB – 4 GB+ RAM: Decommission the
uc-enterprise-cubeNode.js container and CubeStore Rust daemon per deployment. - Global Standard Semantic Query Specification: Eliminate Cube's query limitations by supporting native, nested boolean logic (
AND/ORtrees), ad-hoc relational joins (LEFT,INNER,FULL OUTER), and aggregateFILTER (WHERE ...)clauses. - 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. - 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. - Server-Side Scheduled Reporting & Emailing: Enable automated, headless report generation (Excel, PDF, CSV) and scheduled email delivery using
schedule.service.js(BullMQ) andemail.service.js. - External BI Connector with Service Account ACL Enforcement: Provide a lightweight PostgreSQL wire-protocol gateway (
analytics-bi-gatewayon port15432) that authenticates Power BI and Tableau service accounts against theuserstable 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/
-
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).
- Centralize field mappings for static tables (
-
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).
-
Row-Level Security (RLS) Filter Injector (
methods/rls-filter.js):- Read
ctx.meta.accessLevel,ctx.meta.ownerId, andctx.meta.accessGroups. - Inject security constraints directly into the root
WHEREclause:own:(t."createdById" = $ownerId OR t."assignedToUserId" = $ownerId OR t."userId" = $ownerId)group:(t."groupId" = ANY($groupIds) OR t."assignedToGroupId" = ANY($groupIds))
- Read
-
Analytics Endpoints (
analytics.service.js):- Action
query:POST /api/analytics/query(with legacy fallback aliasPOST /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).
- Action
Phase 2: Server-Side Blending & In-Database Calculations
Target Directory: uc-enterprise-core/services/analytics/engine/
- SQL Common Table Expression (CTE) Pushdown:
- When a widget references multiple distinct datasets (e.g., call volume from
callsand agent occupancy fromstatusLog), compile them into a unified query withFULL OUTER JOINorLEFT JOIN.
- When a widget references multiple distinct datasets (e.g., call volume from
- Formula Evaluator (
formula-evaluator.js):- Mathematical expressions evaluate directly inside the SQL
SELECTstatement (NULLIF, arithmetic, percentage). - Non-linear custom formulas evaluate in Node.js on the server before serializing rows.
- Mathematical expressions evaluate directly inside the SQL
- Decommission Client-Side Join Engine:
dataJoinEngine.tsin 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/
- Subscription Protocol on Existing Gateway:
- Client sends over existing WebSocket connection:
{"action": "analytics.subscribe","params": {"subscriptionId": "sub_wallboard_kpis","query": { ... },"intervalSeconds": 10}}
- Client sends over existing WebSocket connection:
- 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.
- Tracks active subscriptions in memory:
- 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.
- Listen to domain events:
- Auto Unsubscribe:
- Clean up subscriptions and clear timers on socket disconnect.
Phase 4: Frontend Migration & Client Adapter
Target Directory: uc-enterprise-web/src/
- Lightweight Drop-in Client Adapter (
cubeClient.ts):- Remove
@cubejs-client/coreand@cubejs-client/ws-transportdependencies. - Implement
analyticsClientmatching 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
ResultSetwrapper (.rawData(),.tablePivot(),.series(),.total()).
- Remove
- Update Visual Filter & Join Builders:
- Update
FilterRow.tsxto support nestedAND/ORgroup toggles. - Simplify
DataJoinConfigurator.tsxto emit declarative join definitions directly into the query AST.
- Update
- Transparent Hook Migration:
- Hooks like
useWidgetQueryData.ts,useCubeSubscriptions.ts, anduseCubeSubscription.tsseamlessly operate through the adapter.
- Hooks like
Phase 5: Server-Side Scheduled Reporting & Emailing
Target Directory: uc-enterprise-core/services/report-scheduler/
- Database Schema (
prisma/schema.prisma):model ReportSchedule {id String @id @default(uuid())reportId Stringreport Report @relation(fields: [reportId], references: [id], onDelete: Cascade)title StringcronExpression String // e.g. "0 8 * * 1" (Every Monday at 8 AM)timezone String @default("UTC")format String // "xlsx" | "pdf" | "csv"recipients String[] // Recipient email addressesemailSubject String?emailBody String?filterOverrides Json? // e.g. { "relativeTime": "last_week" }isActive Boolean @default(true)ownerId StringcreatedAt DateTime @default(now())updatedAt DateTime @updatedAt} - Scheduler Service (
report-scheduler.service.js):- Backed by BullMQ via
schedule.service.js. - On cron trigger:
- Loads
Reportlayout andReportScheduleconfiguration. - Resolves dynamic relative dates (
"yesterday","last_week","last_month"). - Calls
analytics.queryunder the author's RLS permissions. - 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
pdfmakeor HTML template.
- Excel (.xlsx): Multi-sheet workbook with formatting via
- Dispatches email with attachment via
email.service.js. - Records execution in audit log.
- Loads
- Backed by BullMQ via
- 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/
- Service Account Authentication Handshake (
checkSqlAuthParity):- Emulate PostgreSQL Frontend/Backend protocol v3 on port
15432usingpg-gateway. - Verify client ID and secret against the
userstable via bcrypt. - Load
permissionsanduserGroupsfrom Redis/Postgres and store on socket session context.
- Emulate PostgreSQL Frontend/Backend protocol v3 on port
- SQL AST ACL Rewriting (
queryRewriteParity):- Intercept SQL queries from Power BI and Tableau.
- Parse AST: pass
information_schemaandpg_catalogqueries untouched for clean metadata browsing. - For business tables (
calls,statusLog,leads), inject ACLWHEREconstraints matching the service account'saccessLevel: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
- Container Cleanup:
- Remove
uniconnect-cubeanduniconnect-cubestoreservices fromdocker-compose.yml, Kubernetes manifests, and Helm charts.
- Remove
- Environment Variables:
- Remove
VITE_REACT_APP_CUBEJS_API_URLandVITE_REACT_APP_CUBEJS_SOCKET_URL.
- Remove
- Repository Archival:
- Archive the
uc-enterprise-cuberepository.
- Archive the
5. Implementation Schedule & Milestones
| Sprint / Week | Focus Area | Deliverables |
|---|---|---|
| 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
- 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.
- 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
FILTERpushdown and avoiding Cube orchestration hops.
- Subscription Reliability:
- Verify active wallboards receive instant updates when call events fire on NATS.
- Confirm socket disconnects cleanly release memory and timers.
- Scheduled Report Delivery:
- Verify recurring cron jobs execute headlessly, resolve relative dates correctly, and dispatch valid attachments via email.
- Power BI ACL Verification:
- Connect Power BI using a service account with
accessLevel: "group"; assert that only rows matching assigned groups are returned.
- Connect Power BI using a service account with