Semantic Cube Deployment

The CTMS Semantic Cube is the analytics layer powered by Cube.dev. It provides a semantic layer over the DBT gold layer, enabling real-time dashboards and analytics queries.

Monorepo Available

Cube.dev is now part of the ctms-data-pipeline-ai-analytics monorepo as ctms-cube/. See AI Analytics Pipeline for the unified deployment approach.

Architecture Overview

Frappe API → DLT Ingester → PostgreSQL (Bronze)
                              ↓
                           DBT (Gold Layer)
                              ↓
                           Cube.dev (Semantic Layer)
                              ↓
                           Web App Dashboards

Quick Deploy

Option A: Standalone Repository

cd ctms-semantic-cube
make deploy ENV=production

This command:

1. Loads environment variables from .env.production
2. Sets Fly.io secrets from those variables
3. Deploys to Fly.io

Option B: Monorepo Approach (Recommended)

cd ctms-data-pipeline-ai-analytics
make env-copy
make cube-deploy

This uses the unified .env.production and manages all services together.

Prerequisites

Install Fly.io CLI

# macOS
brew install flyctl

# or using install script
curl -L https://fly.io/install.sh | sh

Authenticate

flyctl auth login

Environment Configuration

Copy the example environment file and configure:

cp .env.example .env.production

Required Variables

Variable	Description	Example
CUBEJS_DB_TYPE	Database type	postgres
CUBEJS_DB_HOST	Neon/PostgreSQL host	ep-xxx.neon.tech
CUBEJS_DB_PORT	Database port	5432
CUBEJS_DB_NAME	Database name	neondb
CUBEJS_DB_USER	Database user	neondb_owner
CUBEJS_DB_PASS	Database password	your-password
CUBEJS_DB_SSL	Enable SSL	true
CUBEJS_API_SECRET	API authentication secret	your-secret-key
CUBEJS_DEV_MODE	Enable dev mode	true
CUBEJS_CACHE_AND_QUEUE_DRIVER	Cache driver	memory

Dev Mode Required

CUBEJS_DEV_MODE=true is required when not using Cube Store for pre-aggregations.

Makefile Commands

The Makefile provides convenient commands for managing the Cube deployment:

# Show all available commands
make help

# View current configuration
make status ENV=production

# Deploy to Fly.io
make deploy ENV=production

# Restart the Fly.io app
make restart

# Check health
make health ENV=production

# View schema metadata (list of cubes)
make meta ENV=production

# Flush cache after DBT refresh
make flush-cache ENV=production

# Validate cube definitions
make validate

After DBT Refresh

When the DBT pipeline refreshes the gold layer, you should flush the Cube cache to ensure fresh data:

make flush-cache ENV=production

This restarts the Fly.io app and clears the in-memory cache.

Available Cubes

The semantic layer includes the following cubes:

Cube	Source Table	Description
Studies	gold.dim_study	Clinical study master data
Subjects	gold.dim_subject	Study subjects/participants
Sites	gold.dim_site	Clinical trial sites
Enrollments	gold.fct_enrollment	Subject enrollment metrics
AdverseEvents	gold.fct_adverse_events	Safety data
Visits	gold.fct_visits	Visit compliance data
StudyPersonnel	gold.dim_study_personnel	User-study role mappings

API Endpoints

Endpoint	Description
/readyz	Health check
/cubejs-api/v1/meta	Schema metadata
/cubejs-api/v1/load	Query data

Production URLs

• App: https://ctms-semantic-cube.fly.dev
• Health: https://ctms-semantic-cube.fly.dev/readyz
• API: https://ctms-semantic-cube.fly.dev/cubejs-api/v1

Troubleshooting

Check App Status

flyctl status

View Logs

flyctl logs

SSH into Container

flyctl ssh console

List Secrets

flyctl secrets list

Common Issues

1. "Cannot find module" errors: Ensure npm install was run and node_modules is included in the Docker image.

2. Database connection errors: Verify the database credentials and that the Neon database allows connections from Fly.io IPs.

3. Cache stale data: Run make flush-cache ENV=production to clear the cache.

4. Pre-aggregation errors: Ensure CUBEJS_DEV_MODE=true is set if not using Cube Store.