Database Schema Documentation

Database Schema Documentation

This document provides comprehensive documentation of the database schema, migration system, and data management for the MCP Metrics Collection Service.

Table of Contents

• Overview
• Schema Architecture
• Table Definitions
• Migration System
• Data Retention
• Performance Considerations
• Backup and Recovery

Overview

The metrics service uses SQLite as its primary data store with a carefully designed schema optimized for:

• High-volume writes: Optimized for metric ingestion
• Time-series data: Efficient timestamp-based queries
• Aggregation support: Pre-computed summaries for performance
• Data retention: Automatic cleanup of old data
• Schema evolution: Version-controlled migrations

Design Principles

1. Write Optimization: Tables designed for fast inserts
2. Query Performance: Strategic indexing for common queries
3. Data Integrity: Foreign key constraints and validation
4. Storage Efficiency: Normalized structure with JSON for flexible data
5. Horizontal Scaling: Partitionable by time and service

Schema Architecture

Entity Relationship Diagram

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   api_keys      │     │     metrics     │     │ schema_migrations│
│                 │     │                 │     │                 │
│ • id (PK)       │     │ • id (PK)       │     │ • version (PK)  │
│ • key_hash      │────▶│ • request_id    │     │ • name          │
│ • service_name  │     │ • service       │     │ • applied_at    │
│ • rate_limit    │     │ • metric_type   │     └─────────────────┘
│ • usage_count   │     │ • timestamp     │              
│ • created_at    │     │ • value         │     ┌─────────────────┐
└─────────────────┘     │ • duration_ms   │     │retention_policies│
                        │ • dimensions    │     │                 │
┌─────────────────┐     │ • metadata      │     │ • id (PK)       │
│ api_key_usage   │     └─────────────────┘     │ • table_name    │
│                 │              │              │ • retention_days│
│ • id (PK)       │              │              │ • is_active     │
│ • key_hash      │──────────────┘              └─────────────────┘
│ • timestamp     │              │
│ • endpoint      │              │
│ • request_count │              ▼
│ • status_code   │     ┌─────────────────┐
└─────────────────┘     │ Specialized     │
                        │ Metric Tables   │
┌─────────────────┐     │                 │
│ metrics_hourly  │     │ • auth_metrics  │
│                 │     │ • discovery_    │
│ • id (PK)       │◀────│   metrics       │
│ • service       │     │ • tool_metrics  │
│ • metric_type   │     └─────────────────┘
│ • hour_timestamp│
│ • count         │     ┌─────────────────┐
│ • sum_value     │     │ metrics_daily   │
│ • avg_value     │     │                 │
│ • min_value     │     │ • id (PK)       │
│ • max_value     │     │ • service       │
└─────────────────┘     │ • metric_type   │
                        │ • date          │
                        │ • count         │
                        │ • aggregates... │
                        └─────────────────┘

Table Definitions

Core Tables

api_keys

Stores API key information and configuration.

CREATE TABLE api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key_hash TEXT UNIQUE NOT NULL,           -- SHA256 hash of API key
    service_name TEXT NOT NULL,              -- Associated service name
    created_at TEXT NOT NULL,                -- ISO timestamp
    last_used_at TEXT,                       -- Last request timestamp
    is_active BOOLEAN DEFAULT 1,             -- Key status
    rate_limit INTEGER DEFAULT 1000,         -- Requests per minute
    usage_count INTEGER DEFAULT 0,           -- Total requests made
    daily_usage_limit INTEGER DEFAULT NULL,  -- Optional daily limit
    monthly_usage_limit INTEGER DEFAULT NULL,-- Optional monthly limit
    description TEXT DEFAULT NULL            -- Optional description
);

-- Indexes
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_service ON api_keys(service_name);

Sample Data:

INSERT INTO api_keys VALUES (
    1,
    'a1b2c3d4e5f6...', 
    'auth-server',
    '2024-01-01T00:00:00Z',
    '2024-01-01T12:30:15Z',
    1,
    1000,
    15842,
    NULL,
    NULL,
    'Production auth server API key'
);

metrics

Primary table for all metrics data.

CREATE TABLE metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL,                -- Request correlation ID
    service TEXT NOT NULL,                   -- Source service
    service_version TEXT,                    -- Service version
    instance_id TEXT,                        -- Service instance ID
    metric_type TEXT NOT NULL,               -- Type of metric
    timestamp TEXT NOT NULL,                 -- Metric timestamp (ISO)
    value REAL NOT NULL,                     -- Metric value
    duration_ms REAL,                        -- Optional duration
    dimensions TEXT,                         -- JSON key-value pairs
    metadata TEXT,                           -- JSON additional data
    created_at TEXT DEFAULT (datetime('now')) -- Row creation time
);

-- Indexes
CREATE INDEX idx_metrics_timestamp ON metrics(timestamp);
CREATE INDEX idx_metrics_service_type ON metrics(service, metric_type);
CREATE INDEX idx_metrics_type_timestamp ON metrics(metric_type, timestamp);

Sample Data:

INSERT INTO metrics VALUES (
    1,
    'req_abc123',
    'auth-server',
    '1.2.0',
    'auth-pod-01',
    'auth_request',
    '2024-01-01T12:30:15.123Z',
    1.0,
    45.2,
    '{"method":"jwt","success":true,"server":"auth-01"}',
    '{"error_code":null,"request_size":1024}',
    '2024-01-01T12:30:15.500Z'
);

Specialized Metric Tables

auth_metrics

Authentication-specific metrics with optimized schema.

CREATE TABLE auth_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    service TEXT NOT NULL,
    duration_ms REAL,
    success BOOLEAN,                         -- Authentication result
    method TEXT,                             -- Auth method (jwt, oauth, etc)
    server TEXT,                             -- Handling server
    user_hash TEXT,                          -- Hashed user identifier
    error_code TEXT,                         -- Error code if failed
    created_at TEXT DEFAULT (datetime('now'))
);

-- Indexes
CREATE INDEX idx_auth_timestamp ON auth_metrics(timestamp);
CREATE INDEX idx_auth_success ON auth_metrics(success, timestamp);
CREATE INDEX idx_auth_user ON auth_metrics(user_hash, timestamp);

discovery_metrics

Tool discovery operation metrics.

CREATE TABLE discovery_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    service TEXT NOT NULL,
    duration_ms REAL,
    query TEXT,                              -- Search query
    results_count INTEGER,                   -- Number of results
    top_k_services INTEGER,                  -- Services considered
    top_n_tools INTEGER,                     -- Tools returned
    embedding_time_ms REAL,                  -- Vector generation time
    faiss_search_time_ms REAL,              -- Search engine time
    created_at TEXT DEFAULT (datetime('now'))
);

-- Indexes
CREATE INDEX idx_discovery_timestamp ON discovery_metrics(timestamp);
CREATE INDEX idx_discovery_results ON discovery_metrics(results_count, timestamp);

tool_metrics

Tool execution metrics.

CREATE TABLE tool_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    service TEXT NOT NULL,
    duration_ms REAL,
    tool_name TEXT,                          -- Executed tool name
    server_path TEXT,                        -- Server endpoint
    server_name TEXT,                        -- Server identifier
    success BOOLEAN,                         -- Execution result
    error_code TEXT,                         -- Error code if failed
    input_size_bytes INTEGER,                -- Input payload size
    output_size_bytes INTEGER,               -- Output payload size
    created_at TEXT DEFAULT (datetime('now'))
);

-- Indexes
CREATE INDEX idx_tool_timestamp ON tool_metrics(timestamp);
CREATE INDEX idx_tool_name ON tool_metrics(tool_name, timestamp);
CREATE INDEX idx_tool_success ON tool_metrics(success, timestamp);

Aggregation Tables

metrics_hourly

Pre-computed hourly aggregates for performance.

CREATE TABLE metrics_hourly (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    hour_timestamp TEXT NOT NULL,            -- Truncated to hour
    count INTEGER DEFAULT 0,                 -- Number of metrics
    sum_value REAL DEFAULT 0.0,              -- Sum of values
    avg_value REAL DEFAULT 0.0,              -- Average value
    min_value REAL,                          -- Minimum value
    max_value REAL,                          -- Maximum value
    sum_duration_ms REAL DEFAULT 0.0,        -- Sum of durations
    avg_duration_ms REAL DEFAULT 0.0,        -- Average duration
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(service, metric_type, hour_timestamp)
);

-- Indexes
CREATE INDEX idx_hourly_service_type_hour ON metrics_hourly(service, metric_type, hour_timestamp);
CREATE INDEX idx_hourly_hour ON metrics_hourly(hour_timestamp);

metrics_daily

Pre-computed daily aggregates for long-term analysis.

CREATE TABLE metrics_daily (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    date TEXT NOT NULL,                      -- YYYY-MM-DD format
    count INTEGER DEFAULT 0,
    sum_value REAL DEFAULT 0.0,
    avg_value REAL DEFAULT 0.0,
    min_value REAL,
    max_value REAL,
    sum_duration_ms REAL DEFAULT 0.0,
    avg_duration_ms REAL DEFAULT 0.0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(service, metric_type, date)
);

-- Indexes
CREATE INDEX idx_daily_service_type_date ON metrics_daily(service, metric_type, date);
CREATE INDEX idx_daily_date ON metrics_daily(date);

System Tables

schema_migrations

Tracks applied database migrations.

CREATE TABLE schema_migrations (
    version INTEGER PRIMARY KEY,             -- Migration version number
    name TEXT NOT NULL,                      -- Migration name
    applied_at TEXT NOT NULL                 -- Application timestamp
);

retention_policies

Configures data retention for different tables.

CREATE TABLE retention_policies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,                -- Target table
    retention_days INTEGER NOT NULL,         -- Retention period
    is_active BOOLEAN DEFAULT 1,             -- Policy status
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    UNIQUE(table_name)
);

-- Default policies
INSERT INTO retention_policies (table_name, retention_days) VALUES 
    ('metrics', 90),           -- Raw metrics: 90 days
    ('auth_metrics', 90),      -- Auth metrics: 90 days  
    ('discovery_metrics', 90), -- Discovery metrics: 90 days
    ('tool_metrics', 90),      -- Tool metrics: 90 days
    ('metrics_hourly', 365),   -- Hourly aggregates: 1 year
    ('metrics_daily', 1095);   -- Daily aggregates: 3 years

api_key_usage_log

Detailed API key usage tracking.

CREATE TABLE api_key_usage_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key_hash TEXT NOT NULL,                  -- API key hash
    service_name TEXT NOT NULL,              -- Service making request
    timestamp TEXT NOT NULL,                 -- Request timestamp
    endpoint TEXT NOT NULL,                  -- API endpoint called
    request_count INTEGER DEFAULT 1,         -- Number of requests
    bytes_processed INTEGER DEFAULT 0,       -- Data processed
    duration_ms REAL DEFAULT 0,              -- Request duration
    status_code INTEGER DEFAULT 200,         -- HTTP status code
    FOREIGN KEY (key_hash) REFERENCES api_keys(key_hash)
);

-- Indexes
CREATE INDEX idx_usage_key_timestamp ON api_key_usage_log(key_hash, timestamp);
CREATE INDEX idx_usage_timestamp ON api_key_usage_log(timestamp);
CREATE INDEX idx_usage_endpoint ON api_key_usage_log(endpoint, timestamp);

Migration System

Migration Architecture

The migration system provides:

• Version Control: Sequential migration numbering
• Rollback Support: Down migrations for reverting changes
• Transaction Safety: Atomic migration application
• Python Integration: Support for data migrations alongside DDL

Migration CLI

# Check migration status
python migrate.py status

# Apply all pending migrations
python migrate.py up

# Apply up to specific version
python migrate.py up --to 3

# Rollback to specific version
python migrate.py down 2

# List all available migrations
python migrate.py list

# Create new migration template
python migrate.py create "add_user_preferences"

Migration Versions

Migration 0001: Initial Schema

• Creates all core tables
• Establishes indexes
• Sets up initial constraints

Migration 0002: Aggregation Tables

• Adds metrics_hourly table
• Adds metrics_daily table
• Creates aggregation indexes

Migration 0003: Retention Policies

• Creates retention_policies table
• Inserts default retention settings
• Enables automated cleanup

Migration 0004: API Key Usage Tracking

• Extends api_keys table with usage fields
• Creates api_key_usage_log table
• Adds usage tracking indexes

Migration Example

# Example migration structure
from app.storage.migrations import Migration

migration = Migration(
    version=5,
    name="add_metric_labels",
    up_sql="""
        -- Add labels column to metrics table
        ALTER TABLE metrics ADD COLUMN labels TEXT;
        
        -- Create index on labels
        CREATE INDEX idx_metrics_labels ON metrics(labels);
        
        -- Update existing metrics with empty labels
        UPDATE metrics SET labels = '{}' WHERE labels IS NULL;
    """,
    down_sql="""
        -- Remove labels functionality
        DROP INDEX idx_metrics_labels;
        
        -- Note: Cannot drop column in SQLite easily
        -- Would require table recreation in production
    """,
    python_up=async_migrate_labels,      # Optional Python function
    python_down=async_rollback_labels    # Optional Python rollback
)

Data Retention

Retention Strategy

The service implements a multi-tiered retention strategy:

1. Raw Data: 90 days for detailed analysis
2. Hourly Aggregates: 365 days for trend analysis
3. Daily Aggregates: 3 years for historical reporting
4. Audit Logs: 90 days for compliance

Automated Cleanup

-- Example cleanup procedures (run via cron)

-- Clean old raw metrics
DELETE FROM metrics 
WHERE created_at < datetime('now', '-90 days');

-- Clean old hourly aggregates
DELETE FROM metrics_hourly 
WHERE hour_timestamp < datetime('now', '-365 days');

-- Clean old daily aggregates
DELETE FROM metrics_daily 
WHERE date < date('now', '-1095 days');

-- Clean old API usage logs
DELETE FROM api_key_usage_log 
WHERE timestamp < datetime('now', '-90 days');

Retention Policy Management

# Python API for managing retention
from app.storage.database import MetricsStorage

storage = MetricsStorage()

# Update retention policy
await storage.update_retention_policy('metrics', 120)  # 120 days

# Get all policies
policies = await storage.get_retention_policies()

# Apply cleanup based on policies
await storage.apply_retention_cleanup()

Performance Considerations

Query Optimization

Common Query Patterns

-- Time-range queries (most common)
SELECT * FROM metrics 
WHERE timestamp BETWEEN ? AND ?
  AND service = ?;

-- Aggregation queries
SELECT service, metric_type, COUNT(*), AVG(value)
FROM metrics 
WHERE timestamp > datetime('now', '-1 hour')
GROUP BY service, metric_type;

-- Recent metrics by type
SELECT * FROM auth_metrics
WHERE timestamp > datetime('now', '-5 minutes')
  AND success = 1
ORDER BY timestamp DESC
LIMIT 100;

Index Strategy

-- Primary indexes for time-series queries
CREATE INDEX idx_metrics_timestamp ON metrics(timestamp);
CREATE INDEX idx_metrics_service_timestamp ON metrics(service, timestamp);
CREATE INDEX idx_metrics_type_timestamp ON metrics(metric_type, timestamp);

-- Composite indexes for common filters
CREATE INDEX idx_auth_success_timestamp ON auth_metrics(success, timestamp);
CREATE INDEX idx_tool_name_timestamp ON tool_metrics(tool_name, timestamp);

-- Covering indexes for aggregations
CREATE INDEX idx_metrics_service_type_time_value ON metrics(service, metric_type, timestamp, value);

SQLite Optimizations

PRAGMA Settings

-- WAL mode for better concurrency
PRAGMA journal_mode = WAL;

-- Optimize for write performance
PRAGMA synchronous = NORMAL;

-- Large cache for read performance
PRAGMA cache_size = 10000;

-- Memory temp store
PRAGMA temp_store = MEMORY;

-- Auto-vacuum for space management
PRAGMA auto_vacuum = INCREMENTAL;

Connection Pool Settings

# Connection configuration
SQLITE_CONFIG = {
    'database': '/var/lib/sqlite/metrics.db',
    'timeout': 30.0,
    'isolation_level': None,  # Autocommit mode
    'check_same_thread': False,
    'factory': sqlite3.Row,   # Row factory for dict-like access
}

# Connection pool
connection_pool = aiosqlite.connect(**SQLITE_CONFIG)

Write Performance

Batch Processing

# Efficient batch inserts
async def batch_insert_metrics(metrics_batch):
    async with aiosqlite.connect(db_path) as db:
        await db.execute('BEGIN IMMEDIATE')
        try:
            await db.executemany(
                'INSERT INTO metrics (...) VALUES (...)',
                metrics_batch
            )
            await db.commit()
        except Exception:
            await db.rollback()
            raise

Prepared Statements

# Reuse prepared statements
INSERT_METRIC = """
    INSERT INTO metrics (
        request_id, service, metric_type, timestamp, 
        value, duration_ms, dimensions, metadata
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
"""

async def insert_metrics(db, metrics):
    await db.executemany(INSERT_METRIC, metrics)

Read Performance

Aggregation Queries

-- Use pre-computed aggregates when possible
SELECT sum_value, avg_value, count
FROM metrics_hourly
WHERE service = 'auth-server'
  AND metric_type = 'auth_request'
  AND hour_timestamp >= '2024-01-01T00:00:00Z';

-- Fallback to raw data for recent metrics
SELECT SUM(value), AVG(value), COUNT(*)
FROM metrics
WHERE service = 'auth-server'
  AND metric_type = 'auth_request'
  AND timestamp >= '2024-01-01T23:00:00Z';

Query Plan Analysis

-- Analyze query performance
EXPLAIN QUERY PLAN 
SELECT COUNT(*) FROM metrics 
WHERE timestamp > '2024-01-01T00:00:00Z' 
  AND service = 'auth-server';

-- Expected plan should use index
-- SEARCH TABLE metrics USING INDEX idx_metrics_service_timestamp

Backup and Recovery

Backup Strategy

Hot Backup (Online)

#!/bin/bash
# hot-backup.sh - SQLite online backup

DB_PATH="/var/lib/sqlite/metrics.db"
BACKUP_PATH="/backups/metrics/$(date +%Y%m%d_%H%M%S).db"

# Use SQLite's online backup API
sqlite3 "$DB_PATH" ".backup $BACKUP_PATH"

# Verify backup integrity
if sqlite3 "$BACKUP_PATH" "PRAGMA integrity_check;" | grep -q "ok"; then
    echo "Backup completed successfully: $BACKUP_PATH"
    gzip "$BACKUP_PATH"
else
    echo "Backup verification failed!"
    rm "$BACKUP_PATH"
    exit 1
fi

Cold Backup (Offline)

#!/bin/bash
# cold-backup.sh - File system level backup

systemctl stop metrics-service

# Copy database files
cp /var/lib/sqlite/metrics.db /backups/
cp /var/lib/sqlite/metrics.db-wal /backups/
cp /var/lib/sqlite/metrics.db-shm /backups/

systemctl start metrics-service

Recovery Procedures

Point-in-Time Recovery

# Restore from backup
systemctl stop metrics-service

# Restore database
gunzip -c /backups/metrics/20240101_120000.db.gz > /var/lib/sqlite/metrics.db

# Verify integrity
sqlite3 /var/lib/sqlite/metrics.db "PRAGMA integrity_check;"

# Restart service
systemctl start metrics-service

Corruption Recovery

-- Check for corruption
PRAGMA integrity_check;
PRAGMA foreign_key_check;

-- Recover from corruption
.output /tmp/recovery.sql
.dump
.quit

-- Create new database
rm metrics.db
sqlite3 metrics.db < /tmp/recovery.sql

Disaster Recovery

Full System Recovery

1. Provision new server
2. Install application
3. Restore latest backup
4. Apply any missing migrations
5. Verify data integrity
6. Update DNS/load balancers

# Recovery script
#!/bin/bash
set -e

# Download latest backup
aws s3 cp s3://backups/metrics/latest.db.gz ./

# Restore database
gunzip latest.db.gz
mv latest.db /var/lib/sqlite/metrics.db

# Apply migrations
uv run python migrate.py up

# Verify
sqlite3 /var/lib/sqlite/metrics.db "PRAGMA integrity_check;"

# Start service
systemctl start metrics-service

This documentation provides a complete reference for understanding and managing the database schema, migrations, and data lifecycle of the MCP Metrics Collection Service.