DB Schema

Complete database schema documentation for the Hawkeye system.

Overview

Hawkeye uses a relational database to store incidents, predictions, model metadata, and system configuration.

Core Tables

1. Incidents Table

Stores incident information and metadata.

CREATE TABLE incidents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    incident_type VARCHAR(50) NOT NULL,
    severity VARCHAR(20) NOT NULL,
    description TEXT,
    source VARCHAR(50),
    category VARCHAR(50),
    region VARCHAR(50),
    affected_users INTEGER DEFAULT 0,
    system_impact_score DECIMAL(5,2),
    status VARCHAR(20) DEFAULT 'created',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP,
    metadata JSONB,
    INDEX idx_incident_type (incident_type),
    INDEX idx_severity (severity),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

Fields:

• id: Unique identifier (UUID)
• incident_type: Type of incident (security, system, network, etc.)
• severity: Initial severity (low, medium, high, critical)
• description: Incident description
• source: Source of incident (api, manual, automated)
• category: Incident category
• region: Geographic region
• affected_users: Number of affected users
• system_impact_score: Calculated impact score (0-100)
• status: Current status (created, validated, processing, predicted, classified, resolved)
• created_at: Creation timestamp
• updated_at: Last update timestamp
• resolved_at: Resolution timestamp
• metadata: Additional JSON metadata

2. Predictions Table

Stores ML model predictions for incidents.

CREATE TABLE predictions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE CASCADE,
    prediction VARCHAR(20) NOT NULL,
    confidence DECIMAL(5,4) NOT NULL,
    probability_low DECIMAL(5,4),
    probability_medium DECIMAL(5,4),
    probability_high DECIMAL(5,4),
    probability_critical DECIMAL(5,4),
    model_version VARCHAR(50) NOT NULL,
    feature_values JSONB,
    feature_importance JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_incident_id (incident_id),
    INDEX idx_prediction (prediction),
    INDEX idx_confidence (confidence),
    INDEX idx_created_at (created_at)
);

Fields:

• id: Unique identifier
• incident_id: Foreign key to incidents table
• prediction: Predicted severity (low, medium, high, critical)
• confidence: Prediction confidence (0-1)
• probability_*: Probability for each class
• model_version: Version of model used
• feature_values: JSON of input features
• feature_importance: JSON of feature importance scores
• created_at: Prediction timestamp

3. Model Metadata Table

Stores information about ML models.

CREATE TABLE model_metadata (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version VARCHAR(50) UNIQUE NOT NULL,
    model_type VARCHAR(50) NOT NULL,
    model_path VARCHAR(255) NOT NULL,
    feature_count INTEGER NOT NULL,
    classes TEXT[] NOT NULL,
    training_date TIMESTAMP NOT NULL,
    accuracy DECIMAL(5,4),
    precision DECIMAL(5,4),
    recall DECIMAL(5,4),
    f1_score DECIMAL(5,4),
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_version (version),
    INDEX idx_is_active (is_active)
);

Fields:

• id: Unique identifier
• version: Model version identifier
• model_type: Type of model (XGBoost, etc.)
• model_path: Path to model file
• feature_count: Number of features
• classes: Array of prediction classes
• training_date: Date model was trained
• accuracy: Model accuracy metric
• precision: Precision metric
• recall: Recall metric
• f1_score: F1 score metric
• is_active: Whether model is currently active
• created_at: Creation timestamp

4. Performance Metrics Table

Stores model performance metrics over time.

CREATE TABLE performance_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_version VARCHAR(50) NOT NULL,
    metric_date DATE NOT NULL,
    accuracy DECIMAL(5,4),
    precision DECIMAL(5,4),
    recall DECIMAL(5,4),
    f1_score DECIMAL(5,4),
    prediction_count INTEGER DEFAULT 0,
    average_confidence DECIMAL(5,4),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(model_version, metric_date),
    INDEX idx_model_version (model_version),
    INDEX idx_metric_date (metric_date)
);

5. Feature Statistics Table

Stores statistics about features used in predictions.

CREATE TABLE feature_statistics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    feature_name VARCHAR(100) NOT NULL,
    statistic_date DATE NOT NULL,
    mean_value DECIMAL(10,4),
    std_value DECIMAL(10,4),
    min_value DECIMAL(10,4),
    max_value DECIMAL(10,4),
    count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(feature_name, statistic_date),
    INDEX idx_feature_name (feature_name),
    INDEX idx_statistic_date (statistic_date)
);

6. System Logs Table

Stores system and application logs.

CREATE TABLE system_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    log_level VARCHAR(20) NOT NULL,
    log_type VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    context JSONB,
    error_details JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_log_level (log_level),
    INDEX idx_log_type (log_type),
    INDEX idx_created_at (created_at)
);

7. Scheduled Tasks Table

Tracks scheduled task execution.

CREATE TABLE scheduled_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_name VARCHAR(100) NOT NULL,
    task_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    priority INTEGER DEFAULT 3,
    scheduled_at TIMESTAMP NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    duration_ms INTEGER,
    result JSONB,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_task_name (task_name),
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at)
);

Relationships

Entity Relationship Diagram

incidents (1) ──< (many) predictions
model_metadata (1) ──< (many) predictions
model_metadata (1) ──< (many) performance_metrics

Indexes

Performance Indexes

-- Composite indexes for common queries
CREATE INDEX idx_incidents_type_status ON incidents(incident_type, status);
CREATE INDEX idx_predictions_incident_created ON predictions(incident_id, created_at);
CREATE INDEX idx_metrics_version_date ON performance_metrics(model_version, metric_date);

-- Full-text search index
CREATE INDEX idx_incidents_description_fts ON incidents USING gin(to_tsvector('english', description));

Data Types

Custom Types

-- Enum for incident status
CREATE TYPE incident_status AS ENUM (
    'created',
    'validated',
    'processing',
    'predicted',
    'classified',
    'resolved',
    'error'
);

-- Enum for severity levels
CREATE TYPE severity_level AS ENUM (
    'low',
    'medium',
    'high',
    'critical'
);

Views

Useful Views

-- View for recent predictions with incident details
CREATE VIEW recent_predictions_view AS
SELECT 
    p.id,
    p.incident_id,
    i.incident_type,
    i.severity as initial_severity,
    p.prediction,
    p.confidence,
    p.model_version,
    p.created_at
FROM predictions p
JOIN incidents i ON p.incident_id = i.id
WHERE p.created_at >= NOW() - INTERVAL '7 days'
ORDER BY p.created_at DESC;

-- View for model performance summary
CREATE VIEW model_performance_summary AS
SELECT 
    model_version,
    AVG(accuracy) as avg_accuracy,
    AVG(f1_score) as avg_f1_score,
    COUNT(*) as metric_count,
    MAX(metric_date) as last_updated
FROM performance_metrics
GROUP BY model_version;

Migrations

Migration Script Example

-- Migration: Add feature importance to predictions
ALTER TABLE predictions 
ADD COLUMN feature_importance JSONB;

CREATE INDEX idx_predictions_feature_importance 
ON predictions USING gin(feature_importance);

Data Retention

Retention Policies

-- Archive old predictions (older than 90 days)
CREATE OR REPLACE FUNCTION archive_old_predictions()
RETURNS void AS $$
BEGIN
    INSERT INTO predictions_archive
    SELECT * FROM predictions
    WHERE created_at < NOW() - INTERVAL '90 days';
    
    DELETE FROM predictions
    WHERE created_at < NOW() - INTERVAL '90 days';
END;
$$ LANGUAGE plpgsql;

Backup Strategy

Backup Tables

-- Create backup table for incidents
CREATE TABLE incidents_backup (LIKE incidents INCLUDING ALL);

-- Backup procedure
CREATE OR REPLACE FUNCTION backup_incidents()
RETURNS void AS $$
BEGIN
    TRUNCATE incidents_backup;
    INSERT INTO incidents_backup SELECT * FROM incidents;
END;
$$ LANGUAGE plpgsql;

Next Steps

• AI Logic - AI processing details
• Routine Flow - System workflows
• Task Scheduling - Scheduled tasks