Pulse Architecture Documentation

High-Level Overview
Developer Architecture
Client Architecture
Server Architecture

High-Level Overview

What is Pulse?

Pulse is an open source real-time visual analysis, email reporting and alerting tool that enables data analysts to build interactive data applications. It supports 40+ databases including kdb+, PostgreSQL, MySQL, MS SQL Server, ClickHouse, and wire-compatible databases like TimescaleDB, QuestDB, and CockroachDB.

Data Flow

Starting from the front-end, when a user connects:

User Interface
- The user downloads a modern single-page React-based application that uses a fast charting library (ECharts) to provide animated, interactive charts
- When a dashboard is opened, it opens a WebSocket connection to allow real-time fast updates from the server
Server Security & Control
- The server controls security, authentication and data access controls
- All data queries are validated and sanitized before execution
- User permissions determine which dashboards and data sources can be accessed
Data Source Connectivity
The server can connect to either real-time or database data sources depending on the dashboard configuration:
Databases (Polling Mode)
- You have existing data in a MySQL/PostgreSQL/kdb+ or any JDBC compatible database
- The server will poll the data on a schedule the author has set, only taking action on changes
- Results are cached and shared across users viewing the same dashboard
- Connection pooling ensures efficient resource usage
Real-Time (Streaming Mode)
- The server will create a new thread per client that subscribes to that external source
- The real-time QDB/KDB will push updates to the server and onwards to the client
- Supported streaming sources include: KDB+, Binance, KuCoin cryptocurrency feeds

Security Architecture

Authentication Endpoint

The server can be configured to check HTTP endpoints for authentication:

http://qdb-master.com/?.z.pw[((username));((password))]

This endpoint is queried each time a new user logs in. The .z.pw function can either:

Return true/false to indicate authentication success/failure
Return the name of a role, which will be assigned to the user

Assuming positive authentication, the server will issue a JWT token that allows continued access for a time period without needing to call the REST API every time.

Authorization Endpoint

http://qdb-master.com/?isAuthorized[((username));((dashboardName))]

This endpoint is queried each time the user requests to open a dashboard. If it returns true, access is granted; anything else is assumed to be false.

Built-in Security

JWT token-based authentication
Role-based access control (RBAC)
Team-based permissions
Dashboard ownership and sharing controls
SQL injection prevention through parameterized queries
HTML sanitization for user-generated content

Developer Architecture

Technology Stack

Frontend

React 18 with TypeScript
Blueprint.js UI framework
ECharts and ECharts-GL for visualization
FlexLayout for dashboard layouts
WebSockets for real-time communication

Backend

Java 11+
Micronaut 3.8.7 framework
H2 embedded database for configuration
Apache Commons DBCP2 for connection pooling
WebSocket support via Micronaut

Build System

Gradle 7.6.4 multi-module build
npm/webpack for client bundling
Shadow JAR for server packaging

Project Structure

sqldash3/
├── client/                 # React/TypeScript frontend
│   ├── src/
│   │   ├── components/    # React UI components
│   │   ├── engine/        # Query engine & result processing
│   │   ├── pro/           # Premium features
│   │   └── App.tsx        # Main application entry point
│   └── build/             # Compiled client assets
├── server/                # Java/Micronaut backend
│   ├── src/main/java/com/sqldashboards/
│   │   ├── webby/        # Web controllers & WebSocket
│   │   ├── dashy/        # Query engine core
│   │   ├── shared/       # Connection management
│   │   └── pro/          # Premium server features
│   └── lib/              # Bundled database drivers
└── build.gradle          # Root build configuration

Key Design Patterns

Repository Pattern: Database entities (Dashboard, User, Team) use Micronaut Data repositories
Connection Pooling: Apache DBCP2 manages JDBC connection pools per data source
Query Engine Pattern: Centralized query execution with caching and result sharing
WebSocket Communication: Real-time bidirectional communication between client and server
Observer Pattern: QueryEngine uses listeners to notify clients of data changes
Strategy Pattern: Different SubEngine implementations for streaming vs polling

Client Architecture

Core Technologies

React 18: Component-based UI framework
TypeScript: Type-safe JavaScript
Blueprint.js: Enterprise-grade UI components
ECharts 5: Advanced charting library with WebGL support for 3D charts
FlexLayout: Flexible, dockable panel layouts
React Router: Client-side routing
Axios: HTTP client for REST API calls
WebSocket-TS: WebSocket client wrapper

Major Modules

Engine (`client/src/engine/`)

The engine is responsible for query processing and result transformation:

queryEngine.tsx: Manages query execution lifecycle, caching, and WebSocket communication
queryModel.tsx: Defines data models for queries, results, and configurations
queryTranslator.tsx: Translates user variables and parameters into SQL queries
chartResultSet.tsx: Transforms database results into chart-compatible formats
ViewStrategy.tsx: Determines how to render results (chart, table, metric, etc.)

Key Features:

Query result caching to reduce server load
Automatic reconnection on WebSocket failure
Support for parameterized queries with variable substitution
Real-time data updates via WebSocket subscriptions

Client Data Flow

User Action → React Component → Query Engine → WebSocket/HTTP
                                      ↓
                             Query Translation
                                      ↓
                             Server Communication
                                      ↓
                             Result Processing
                                      ↓
                        Chart/Table Rendering

Server Architecture

Core Framework

Pulse server is built on Micronaut 3.8.7, a modern, JVM-based framework optimized for:

Fast startup times
Low memory footprint
Compile-time dependency injection
Native cloud support

2. Query Engine (`com.sqldashboards.dashy`)

The query engine is the heart of Pulse's data processing:

QueryEngine2: Main query orchestration engine

Manages query execution scheduling
Maintains query result cache
Handles variable substitution
Coordinates multiple Queryables (dashboard panels)
Implements smart caching - multiple users viewing same data = single query

Query Lifecycle:

User opens dashboard → QueryEngine2 created for user session
Dashboard panels added as Queryables
QueryTranslator substitutes variables in SQL
Query scheduled for execution
Results cached and sent to client via WebSocket
On data change or schedule, query re-executed
Only changed data sent to client (delta updates)

Queryable: Represents a single dashboard panel query

Query SQL text
Refresh rate configuration
Server connection reference
Result format specification

QueryTranslator: SQL query transformation

Variable substitution: {{variable}} → actual value
Parameter validation and escaping
Special variable support: {{USER}}, {{ALL}}, etc.

SubEngine (Interface): Streaming data handler

KdbSubEngine: KDB+ streaming subscriptions
BinanceSubEngine: Binance cryptocurrency feeds
KucoinSubEngine: KuCoin cryptocurrency feeds

Each SubEngine runs in a separate thread, maintaining a persistent connection to the streaming source and pushing updates to the query engine.

3. Connection Management (`com.sqldashboards.shared`)

ConnectionManager: Central connection management

Thread-safe with concurrent collections
Manages JDBC connection pools per data source
Query result caching

Connection Pooling: Uses Apache Commons DBCP2 with GenericObjectPool:

Connection validation on borrow
Idle connection eviction
Maximum connections per pool
Connection timeout configuration

ServerConfig: Database connection configuration

JDBC URL, username, password
Database type (PostgreSQL, MySQL, KDB+, etc.)
Polling vs streaming mode
SSL/TLS settings

4. JDBC Integration

Query Execution Flow:

ConnectionManager.executeQuery(ServerConfig, SQL)
        ↓
Get Connection from Pool
        ↓
Create PreparedStatement
        ↓
Execute Query (with timeout)
        ↓
Process ResultSet
        ↓
Convert to Internal Format
        ↓
Return Connection to Pool

Supported Databases (via bundled JDBC drivers in server/lib/):

PostgreSQL 42.7.4
MySQL 9.1.0
MS SQL Server 11.2.0
H2 2.3.232 (embedded)
DuckDB 1.4.1.0
Redis JDBC 1.4
KDB+ (custom driver)

5. Query Rotation & Scheduling

How Query Rotation Works:

Scheduler Thread: QueryEngine2 runs a scheduled executor

scheduler = Executors.newSingleThreadScheduledExecutor();
scheduler.scheduleAtFixedRate(this, 0, milliseconds, TimeUnit.MILLISECONDS);

Query Queue: Queryables are rotated through in round-robin fashion
- Priority queue for immediate execution requests
- Fair scheduling ensures all panels get updated
Change Detection:
- Results are compared with cached previous results
- Only changed results trigger WebSocket updates
- Reduces network traffic and client processing
Smart Caching:
- Same query from multiple users executes once
- Results shared across sessions
- Cache key: serverName + SQL + variables
- 60-minute expiration, 100 entry maximum
Rate Limiting:
- Per-query refresh rate (30 seconds default)
- Throttling for slow clients
- Prevents overwhelming the server or databases

6. Data Serialization

ResultSetSerializer: Converts JDBC ResultSets to JSON

Type detection and conversion
Date/time/timestamp handling with timezone support
Binary data encoding
NULL handling
Special column naming conventions:
- _SD_BG: Background color
- _SD_FG: Foreground color
- SD_LINK: Clickable links

7. Security Implementation

Authentication Flow:

User submits credentials to /login
UserService.authenticate() validates credentials
JWT token generated with user claims
Token returned to client
Subsequent requests include Bearer token
Micronaut Security validates token on each request

Authorization:

Dashboard ownership and team membership checked
Role-based permissions (ADMIN, EDITOR, VIEWER)
Database connection permissions per user/team
Query result row-level security (via database views)

HTML Sanitization:

// OWASP Java HTML Sanitizer
import org.owasp.html.PolicyFactory;
// User HTML content sanitized before rendering

8. Database Schema

H2 Embedded Database (pulsedb3.mv.db):

Stores user accounts, teams, roles
Dashboard configurations and versions
Database connection configs
Report schedules
Audit logs

Flyway Migrations: Version-controlled schema updates

Server Build & Deployment

Shadow JAR (pulse.jar):

All dependencies bundled
Embedded Netty HTTP server
Client assets included in /public
Bundled JDBC drivers
~50-80 MB single executable JAR

Startup Sequence:

Initialize Micronaut context
Load configuration from application.yml and environment
Initialize H2 database, run Flyway migrations
Register JDBC drivers (PostgreSQL, MySQL, custom drivers)
Start ConnectionManager
Start Netty HTTP server on configured port (default 8080)
Serve client assets from /public
Initialize WebSocket endpoints
Log startup URL and admin credentials

Configuration Sources (in order of precedence):

Environment variables
System properties
application.yml
External config files in pl-config/ directory
Built-in defaults

Component Interactions

Dashboard Load Sequence

1. Client: HTTP GET /dash/{dashId}
          ↓
2. Server: DashboardController.getDashboard()
          ↓
3. Check user authorization
          ↓
4. Return dashboard JSON config
          ↓
5. Client: Render dashboard layout
          ↓
6. Client: HTTP GET /api/getKey
          ↓
7. Server: Generate subscription key
          ↓
8. Client: WebSocket connect /api/subscribe/{key}
          ↓
9. Server: WebSocketServer.onOpen()
          ↓
10. Server: Create QueryEngine2 for session
          ↓
11. Client: Send panel queries via WebSocket
          ↓
12. Server: Add Queryables to QueryEngine2
          ↓
13. Server: Query rotation begins
          ↓
14. Server: Send results via WebSocket
          ↓
15. Client: Render charts/tables
          ↓
16. [Real-time updates continue...]

Real-Time Streaming Flow

1. Dashboard configured for streaming source (KDB+/Binance)
          ↓
2. QueryEngine2 detects streaming server
          ↓
3. Create SubEngine for streaming source
          ↓
4. SubEngine.run() in separate thread
          ↓
5. SubEngine connects to external source
          ↓
6. Subscribe to data updates
          ↓
7. On data arrival:
   - SubEngine receives update
   - Notifies QueryEngineListener
   - QueryEngine2 formats result
   - Sends to client via WebSocket
          ↓
8. Client receives update, renders immediately

Query Caching Strategy

User A opens Dashboard X with Query Q1
          ↓
Execute Q1, cache result with key K1 = hash(server, query, variables)
          ↓
User B opens Dashboard X with same Query Q1
          ↓
Check cache for K1 → HIT
          ↓
Return cached result (no database query)
          ↓
Both users receive same data updates when Q1 re-executed

Performance Optimizations

Client Side

Virtual scrolling for large tables (react-window)
Chart data point sampling for performance
Debounced user input
Lazy loading of dashboard components
WebSocket message batching

Server Side

JDBC connection pooling (reduces connection overhead)
Query result caching (prevents duplicate queries)
Smart scheduling (queries execute only when needed)
Result change detection (only send deltas)
Parallel query execution (ExecutorService thread pool)
Connection validation and eviction

Network

WebSocket for efficient real-time updates
JSON compression
Delta updates (only changed data)
Heartbeat for connection health

Deployment Considerations

System Requirements

Java 11+ JRE (bundled in packaged versions)
4GB RAM minimum (8GB recommended for production)
Modern web browser with WebSocket support

Scaling

Multiple instances can run behind a load balancer
Sticky sessions required (for WebSocket affinity)
Shared database file system or external database recommended
Connection pools prevent database overload

Conclusion

Pulse's architecture is designed for:

Real-time Performance: WebSocket communication and efficient query execution
Scalability: Connection pooling, caching, and smart query rotation
Flexibility: Support for 40+ databases and streaming sources
Security: JWT authentication, RBAC, and SQL injection prevention
Developer Experience: Modern stack (React, Micronaut) with TypeScript type safety

The separation between client and server, combined with the powerful query engine and flexible connection management, allows Pulse to serve both traditional database dashboards and real-time streaming data visualizations efficiently.

Pulse Real-Time Dashboards Architecture