MariaDB Integration

This document describes how MIRADOR-CORE integrates with MariaDB for reading tenant data sources and KPI definitions.

Overview

MIRADOR-CORE can connect to a MariaDB database (shared with mirador-ui) to read:

• Data Sources: VictoriaMetrics, VictoriaLogs, VictoriaTraces, Prometheus, and other telemetry endpoints

• KPI Definitions: KPI metadata, queries, thresholds, and configuration

This enables a unified configuration experience where data sources and KPIs configured in mirador-ui are automatically available in mirador-core.

Architecture

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   mirador-ui    │────▶│    MariaDB      │◀────│  mirador-core   │
│  (read/write)   │     │ (tenant_slug)   │     │  (read-only)    │
└─────────────────┘     └─────────────────┘     └─────────────────┘
                                                        │
                                                        ▼
                                                ┌─────────────────┐
                                                │    Weaviate     │
                                                │  (KPI sync)     │
                                                └─────────────────┘

Key Design Decisions:

1. One deployment = One tenant: Each mirador-core deployment connects to a single tenant database

2. Read-only access: mirador-core never writes to MariaDB

3. Graceful degradation: If MariaDB is unavailable, mirador-core falls back to config.yaml static endpoints

4. Background sync: KPIs are synced from MariaDB to Weaviate via a background worker

Configuration

Basic Configuration

Add the mariadb section to your config.yaml:

mariadb:
  enabled: true
  host: "mariadb.example.com"
  port: 3306
  database: "tenant_acme"      # Tenant-specific database name
  username: "mirador_core_ro"  # Read-only user
  password: ""                 # Set via environment variable
  
  # Connection pool settings (optional)
  max_open_conns: 10
  max_idle_conns: 5
  conn_max_lifetime: 5m
  
  # KPI sync to Weaviate (optional)
  sync:
    enabled: true
    interval: 5m              # Sync interval
    batch_size: 100           # Batch size for sync operations
  
  # Bootstrap configuration for backward compatibility
  # When enabled, mirador-core creates tables and syncs data sources
  # from config.yaml to MariaDB on startup
  bootstrap:
    enabled: true
    create_tables_if_missing: true      # Create data_sources and kpis tables
    sync_datasources_from_config: true  # Sync config.yaml endpoints to MariaDB

Bootstrap Feature (Backward Compatibility)

The bootstrap feature ensures seamless migration for existing users:

1. Table Creation: If the data_sources and kpis tables don’t exist, mirador-core creates them automatically

2. Data Source Sync: Data sources defined in config.yaml are synced to MariaDB:

   • If a URL already exists in MariaDB → validated and skipped

   • If a URL doesn’t exist in MariaDB → created from config

This enables existing users to continue using config.yaml while gradually migrating to MariaDB-backed configuration.

Bootstrap Configuration Options:

Option	Description	Default
bootstrap.enabled	Enable bootstrap on startup	true
bootstrap.create_tables_if_missing	Create tables if not present	true
bootstrap.sync_datasources_from_config	Sync config.yaml to MariaDB	true

Environment Variables

Variable	Description	Default
MARIADB_ENABLED	Enable MariaDB integration	false
MARIADB_HOST	MariaDB host	localhost
MARIADB_PORT	MariaDB port	3306
MARIADB_DATABASE	Database name	tenant_default
MARIADB_USERNAME	Username	mirador_core_ro
MARIADB_PASSWORD	Password	-
MARIADB_MAX_OPEN_CONNS	Max open connections	10
MARIADB_MAX_IDLE_CONNS	Max idle connections	5
MARIADB_CONN_MAX_LIFETIME	Connection max lifetime	5m
MARIADB_SYNC_ENABLED	Enable KPI sync	true
MARIADB_SYNC_INTERVAL	Sync interval	5m
MARIADB_SYNC_BATCH_SIZE	Batch size	100
MARIADB_BOOTSTRAP_ENABLED	Enable bootstrap	true
MARIADB_BOOTSTRAP_CREATE_TABLES	Create tables if missing	true
MARIADB_BOOTSTRAP_SYNC_DATASOURCES	Sync datasources from config	true

Kubernetes Secrets

For production deployments, use Kubernetes secrets for credentials:

apiVersion: v1
kind: Secret
metadata:
  name: mirador-core-mariadb
type: Opaque
stringData:
  password: "your-secure-password"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mirador-core
spec:
  template:
    spec:
      containers:
        - name: mirador-core
          env:
            - name: MARIADB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mirador-core-mariadb
                  key: password

Database Schema

data_sources Table

mirador-core reads from the data_sources table:

CREATE TABLE data_sources (
    id                          VARCHAR(36) PRIMARY KEY,
    name                        VARCHAR(255) NOT NULL,
    type                        VARCHAR(50) NOT NULL,
    project_identifier          VARCHAR(255),
    url                         VARCHAR(500) NOT NULL,
    api_key                     VARCHAR(255),
    username                    VARCHAR(255),
    password                    VARCHAR(255),
    
    -- Health check configuration
    health_url                  VARCHAR(500),
    health_expected_status      INT DEFAULT 200,
    health_body_type            VARCHAR(50),
    health_body_match_mode      VARCHAR(50),
    health_body_text_pattern    VARCHAR(255),
    health_body_json_key        VARCHAR(255),
    health_body_json_expected_value VARCHAR(255),
    health_check_interval_ms    INT DEFAULT 60000,
    
    is_active                   BOOLEAN DEFAULT TRUE,
    ai_config                   JSON,
    created_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Supported data source types:

Type	Description
prometheus	Prometheus/VictoriaMetrics metrics endpoint
victorialogs	VictoriaLogs endpoint
victoriatraces	VictoriaTraces endpoint
jaeger	Jaeger tracing endpoint
loki	Grafana Loki logs endpoint
miradorcore	Another mirador-core instance
aiengine	AI/ML engine endpoint

kpis Table

mirador-core reads from the kpis table:

CREATE TABLE kpis (
    id                  VARCHAR(36) PRIMARY KEY,
    name                VARCHAR(255) NOT NULL,
    description         TEXT,
    data_type           VARCHAR(50) NOT NULL,
    definition          TEXT,
    formula             TEXT,
    data_source_id      VARCHAR(36),
    kpi_datastore_id    VARCHAR(36),
    unit                VARCHAR(50),
    thresholds          JSON,
    refresh_interval    INT DEFAULT 60,
    is_shared           BOOLEAN DEFAULT FALSE,
    user_id             VARCHAR(36) NOT NULL,
    
    -- Classification fields
    namespace           VARCHAR(100) NOT NULL,
    kind                VARCHAR(50) NOT NULL,
    layer               VARCHAR(50) NOT NULL,
    classifier          VARCHAR(100),
    signal_type         VARCHAR(50) NOT NULL,
    sentiment           VARCHAR(50),
    component_type      VARCHAR(100),
    
    -- Query configuration
    query               JSON,
    examples            TEXT,
    dimensions_hint     JSON,
    query_type          VARCHAR(50),
    datastore           VARCHAR(100),
    service_family      VARCHAR(100),
    
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (data_source_id) REFERENCES data_sources(id)
);

Data Source Discovery

When MariaDB is enabled, mirador-core dynamically reads data source endpoints:

Metrics Endpoints

// GetMetricsEndpoints returns VictoriaMetrics/Prometheus URLs
endpoints, err := dataSourceRepo.GetMetricsEndpoints(ctx)
// Returns: ["http://vm1:8428", "http://vm2:8428"]

Logs Endpoints

// GetLogsEndpoints returns VictoriaLogs URLs
endpoints, err := dataSourceRepo.GetLogsEndpoints(ctx)
// Returns: ["http://vmlogs:9428"]

Traces Endpoints

// GetTracesEndpoints returns VictoriaTraces URLs
endpoints, err := dataSourceRepo.GetTracesEndpoints(ctx)
// Returns: ["http://vmtraces:9429"]

Endpoint Priority

When both MariaDB and config.yaml define endpoints:

1. MariaDB takes precedence when enabled and connected

2. config.yaml is fallback when MariaDB is unavailable or disabled

3. Dynamic refresh occurs when MariaDB reconnects

KPI Synchronization

Background Sync Worker

When sync.enabled is true, a background worker syncs KPIs from MariaDB to Weaviate:

MariaDB (kpis table) ──sync──▶ Weaviate (KPIDefinition class)

Sync behavior:

1. Incremental sync: Only KPIs modified since last sync are processed

2. Upsert logic: Existing KPIs are updated, new KPIs are created

3. Batch processing: KPIs are processed in configurable batches

4. Retry on failure: Transient failures are retried with backoff

Monitoring Sync Status

Check sync status via the health endpoint:

curl http://localhost:8010/api/v1/health/deep | jq '.components.kpi_sync'

Response:

{
  "status": "healthy",
  "last_sync": "2026-03-02T15:30:00Z",
  "kpis_synced": 125,
  "next_sync": "2026-03-02T15:35:00Z"
}

Manual Sync Trigger

Trigger an immediate sync via API:

curl -X POST http://localhost:8010/api/v1/admin/sync/kpis

Health Checks

MariaDB Health Status

The /api/v1/health endpoint includes MariaDB status:

curl http://localhost:8010/api/v1/health | jq '.components.mariadb'

Response when connected:

{
  "enabled": true,
  "connected": true,
  "host": "mariadb.example.com",
  "database": "tenant_acme"
}

Response when disconnected:

{
  "enabled": true,
  "connected": false,
  "host": "mariadb.example.com",
  "database": "tenant_acme",
  "error": "mariadb: connection refused"
}

Deep Health Check

The /api/v1/health/deep endpoint performs an active ping:

curl http://localhost:8010/api/v1/health/deep

Error Handling

Graceful Degradation

mirador-core implements graceful degradation for MariaDB failures:

Scenario	Behavior
MariaDB unavailable at startup	Log warning, use config.yaml endpoints
MariaDB becomes unavailable	Continue with cached endpoints, attempt reconnect
MariaDB query fails	Return 503, log error, don’t crash
MariaDB disabled	Use config.yaml endpoints only

Error Responses

When MariaDB is unavailable, API calls that require it return 503:

{
  "error": "config_database_unavailable",
  "code": "MARIADB_UNAVAILABLE",
  "message": "The configuration database is temporarily unavailable. Operation 'list_data_sources' cannot be completed. Please try again later."
}

When MariaDB is disabled but endpoint requires it:

{
  "error": "config_database_disabled",
  "code": "MARIADB_DISABLED",
  "message": "The configuration database is not enabled for this deployment. Operation 'list_data_sources' is not available."
}

Auto-Reconnect

mirador-core automatically attempts to reconnect to MariaDB:

1. On first use: If initial connection failed, retry on first query

2. On ping failure: If connection is lost, attempt reconnect

3. Exponential backoff: Retries use exponential backoff to avoid thundering herd

Database User Setup

Create Read-Only User

Create a dedicated read-only user for mirador-core:

-- Create the read-only user
CREATE USER 'mirador_core_ro'@'%' IDENTIFIED BY 'secure_password';

-- Grant SELECT on tenant database
GRANT SELECT ON tenant_acme.* TO 'mirador_core_ro'@'%';

-- (Optional) Grant SELECT on specific tables only
GRANT SELECT ON tenant_acme.data_sources TO 'mirador_core_ro'@'%';
GRANT SELECT ON tenant_acme.kpis TO 'mirador_core_ro'@'%';

-- Apply privileges
FLUSH PRIVILEGES;

Connection Limits

Configure connection limits to prevent resource exhaustion:

-- Limit connections for the read-only user
ALTER USER 'mirador_core_ro'@'%' WITH MAX_USER_CONNECTIONS 20;

Troubleshooting

Connection Issues

Problem: MariaDB connection refused

mariadb: dial tcp 127.0.0.1:3306: connect: connection refused

Solutions:

1. Verify MariaDB is running: systemctl status mariadb

2. Check host/port configuration

3. Verify network connectivity: nc -zv mariadb.example.com 3306

4. Check firewall rules

Problem: Access denied

mariadb: Error 1045 (28000): Access denied for user 'mirador_core_ro'@'...'

Solutions:

1. Verify credentials

2. Check user grants: SHOW GRANTS FOR 'mirador_core_ro'@'%';

3. Ensure password is correctly set via environment variable

Sync Issues

Problem: KPIs not syncing to Weaviate

Diagnostic steps:

1. Check sync status: curl http://localhost:8010/api/v1/health/deep

2. Check logs for sync errors: grep "kpi_sync" /var/log/mirador-core.log

3. Verify Weaviate connectivity

4. Check that sync.enabled is true

Problem: Stale KPI data

Solutions:

1. Trigger manual sync: POST /api/v1/admin/sync/kpis

2. Check updated_at column in MariaDB

3. Verify sync interval is appropriate

Performance Issues

Problem: Slow queries

Solutions:

1. Add indexes on frequently queried columns:

   CREATE INDEX idx_kpis_namespace ON kpis(namespace);
   CREATE INDEX idx_kpis_updated_at ON kpis(updated_at);
   CREATE INDEX idx_data_sources_type ON data_sources(type, is_active);
   

2. Increase connection pool size

3. Enable query caching in MariaDB

Migration from config.yaml

Migrating from static config.yaml endpoints to MariaDB is automatic when using the bootstrap feature.

Automatic Migration (Recommended)

With bootstrap enabled (default), mirador-core automatically:

1. Creates tables if missing: On first startup, creates data_sources and kpis tables

2. Syncs config.yaml data sources: Checks each endpoint in config.yaml against MariaDB

3. Creates missing entries: If an endpoint URL doesn’t exist in MariaDB, creates it

4. Validates existing entries: If an endpoint URL already exists, logs validation and moves on

Simply enable MariaDB with bootstrap in your config.yaml:

# config.yaml
mariadb:
  enabled: true
  host: "mariadb.example.com"
  database: "tenant_acme"
  username: "mirador_core_rw"  # Requires write access for bootstrap
  password: "your_password"
  bootstrap:
    enabled: true
    create_tables_if_missing: true
    sync_datasources_from_config: true

On startup, mirador-core will:

INFO Starting MariaDB bootstrap...
INFO Created data_sources table
INFO Created kpis table
INFO Syncing data sources from config.yaml...
INFO Data source already exists in MariaDB: http://vm1:8428
INFO Created data source in MariaDB: http://vm2:8428 (victoria_metrics)
INFO Bootstrap completed successfully

Manual Migration (Advanced)

For more control over the migration process, disable bootstrap and follow these steps:

Step 1: Create Data Sources in mirador-ui

Create the data sources in mirador-ui that match your config.yaml:

# Before (config.yaml)
database:
  victoria_metrics:
    endpoints:
      - "http://vm1:8428"
      - "http://vm2:8428"

Create equivalent entries in mirador-ui’s Data Sources UI.

Step 2: Enable MariaDB in mirador-core

# config.yaml
mariadb:
  enabled: true
  host: "mariadb.example.com"
  database: "tenant_acme"
  username: "mirador_core_ro"
  bootstrap:
    enabled: false  # Disable automatic bootstrap
  # ... other settings

Step 3: Verify Migration

# Check discovered endpoints
curl http://localhost:8010/api/v1/health/deep | jq '.components.victoria_metrics'

# Verify KPI sync
curl http://localhost:8010/api/v1/health/deep | jq '.components.kpi_sync'

Post-Migration: Read-Only Access

After bootstrap completes, you can switch to read-only credentials:

mariadb:
  enabled: true
  username: "mirador_core_ro"  # Switch to read-only user
  bootstrap:
    enabled: false  # Disable bootstrap after initial sync

Remove Static Endpoints (Optional)

Once verified, you can remove static endpoints from config.yaml. MariaDB will be the source of truth.

Best Practices

Security

1. Use read-only credentials: Never grant write access to mirador-core

2. Use TLS connections: Enable SSL/TLS for MariaDB connections in production

3. Rotate credentials: Regularly rotate the MariaDB password

4. Network isolation: Restrict MariaDB access to mirador-core’s network segment

Performance

1. Connection pooling: Configure appropriate pool sizes for your load

2. Index optimization: Ensure proper indexes on data_sources and kpis tables

3. Sync interval tuning: Balance freshness vs. database load

Monitoring

1. Monitor connection health: Alert on MariaDB connection failures

2. Track sync latency: Monitor time between KPI updates and sync

3. Watch for errors: Set up alerts for repeated query failures

High Availability

1. Use MariaDB replication: Connect to a read replica for HA

2. Handle failover: mirador-core auto-reconnects on failover

3. Multiple mirador-core instances: Each instance maintains its own connection pool