# Storage & Data Management Architecture

**Version**: 1.1
**Date**: November 15, 2025
**Status**: Updated - Service Naming Corrections

## Changelog

### Version 1.1 (2025-11-15)

**Service Naming Corrections**: Corrected "Workflow Engine" → "Remediation Execution Engine" per ADR-035.

**Changes**:
- Updated all references to use correct service naming
- Aligned terminology with authoritative ADR-035
- Maintained consistency with NAMING_CONVENTION_REMEDIATION_EXECUTION.md

---


**Version**: 1.1  
**Date**: 2025-11-15  
**Status**: Updated  

## Changelog

### Version 1.1 (2025-11-15)
- **Service Naming Correction**: Replaced all instances of "Workflow Engine" with "Remediation Execution Engine" per ADR-035
- **Terminology Alignment**: Updated to match authoritative naming convention (RemediationExecution CRD, Remediation Execution Engine architectural concept)
- **Documentation Consistency**: Aligned with NAMING_CONVENTION_REMEDIATION_EXECUTION.md reference document

### Version 1.0 (Original)
- Initial document creation

---


## Overview

This document describes the comprehensive storage and data management architecture for the Kubernaut system, enabling efficient data persistence, intelligent caching, vector similarity operations, and scalable data processing for autonomous operations.

## Business Requirements Addressed

- **BR-STORAGE-001 to BR-STORAGE-040**: Core storage infrastructure and management
- **BR-DATA-001 to BR-DATA-035**: Data processing, transformation, and lifecycle management
- **BR-VECTOR-001 to BR-VECTOR-025**: Vector database operations and similarity search
- **BR-CACHE-001 to BR-CACHE-020**: Intelligent caching and performance optimization
- **BR-PERSISTENCE-001 to BR-PERSISTENCE-015**: Data durability and backup strategies

## Architecture Principles

### Design Philosophy
- **Multi-Modal Storage**: Support for relational, vector, cache, and time-series data
- **Intelligent Caching**: Context-aware caching with 80%+ hit rate requirements
- **Scalable Architecture**: Horizontal scaling for high-throughput data operations
- **Data Consistency**: ACID compliance for critical data with eventual consistency for analytics
- **Performance Optimization**: Sub-100ms response times for cached data access

## System Architecture Overview

### High-Level Storage Architecture

```ascii
┌─────────────────────────────────────────────────────────────────┐
│                  STORAGE & DATA MANAGEMENT                     │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Application Layer                                               │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Context API     │  │ Remediation Execution Engine │  │ Intelligence    │ │
│ │ Data Access     │  │ State Storage   │  │ Analytics       │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Data Access Layer                                               │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Cache Manager   │  │ Database        │  │ Vector Store    │ │
│ │ (Redis/Memory)  │  │ Abstraction     │  │ Manager         │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Storage Infrastructure                                          │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ PostgreSQL      │  │ Redis Cache     │  │ Vector Database │ │
│ │ (Primary DB)    │  │ (Performance)   │  │ (Similarity)    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Infrastructure Layer                                            │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Persistent      │  │ Network         │  │ Backup &        │ │
│ │ Volumes         │  │ Storage         │  │ Recovery        │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

## Core Storage Components

### 1. Primary Database (PostgreSQL)

**Purpose**: ACID-compliant storage for critical system data including workflow state, action history, and configuration.

**Database Schema Architecture**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                    POSTGRESQL SCHEMA DESIGN                   │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Core System Tables                                              │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ workflow_       │  │ action_history  │  │ alert_context   │ │
│ │ executions      │  │                 │  │                 │ │
│ │ • id            │  │ • id            │  │ • id            │ │
│ │ • workflow_id   │  │ • alert_id      │  │ • alert_id      │ │
│ │ • status        │  │ • action_type   │  │ • context_data  │ │
│ │ • started_at    │  │ • success       │  │ • context_hash  │ │
│ │ • completed_at  │  │ • executed_at   │  │ • created_at    │ │
│ │ • execution_log │  │ • duration      │  │ • expires_at    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Intelligence & Analytics Tables                                 │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ pattern_        │  │ effectiveness_  │  │ ai_model_       │ │
│ │ signatures      │  │ assessments     │  │ training        │ │
│ │ • id            │  │ • id            │  │ • id            │ │
│ │ • signature     │  │ • action_id     │  │ • model_type    │ │
│ │ • alert_type    │  │ • success_score │  │ • training_data │ │
│ │ • confidence    │  │ • measured_at   │  │ • accuracy      │ │
│ │ • created_at    │  │ • context_hash  │  │ • deployed_at   │ │
│ │ • updated_at    │  │ • feedback      │  │ • version       │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Configuration & Security Tables                                 │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ system_config   │  │ audit_logs      │  │ health_metrics  │ │
│ │ • key           │  │ • id            │  │ • id            │ │
│ │ • value         │  │ • user_id       │  │ • component     │ │
│ │ • category      │  │ • action        │  │ • status        │ │
│ │ • updated_at    │  │ • resource      │  │ • metrics_data  │ │
│ │ • created_by    │  │ • timestamp     │  │ • measured_at   │ │
│ │ • is_encrypted  │  │ • ip_address    │  │ • alert_level   │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

**Performance Optimization Features**:
```go
// Database configuration optimized for OLTP workloads
type DatabaseConfig struct {
    MaxConnections    int           `json:"max_connections"`
    ConnectionTimeout time.Duration `json:"connection_timeout"`
    IdleTimeout      time.Duration `json:"idle_timeout"`
    QueryTimeout     time.Duration `json:"query_timeout"`

    // Performance tuning
    SharedBuffers    string `json:"shared_buffers"`     // 25% of RAM
    EffectiveCacheSize string `json:"effective_cache_size"` // 75% of RAM
    WorkMem          string `json:"work_mem"`           // RAM/(max_connections*3)
    MaintenanceWorkMem string `json:"maintenance_work_mem"` // RAM/16

    // Write-ahead logging
    WALBuffers       string `json:"wal_buffers"`        // 16MB
    CheckpointTimeout string `json:"checkpoint_timeout"` // 5min

    // Monitoring
    LogStatement     string `json:"log_statement"`      // "all" for audit
    LogDuration      bool   `json:"log_duration"`       // true
    LogSlowQueries   bool   `json:"log_slow_queries"`   // true
}
```

### 2. Redis Cache Infrastructure

**Purpose**: High-performance caching for context data, session management, and real-time state synchronization.

**Cache Strategy Architecture**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                     REDIS CACHE ARCHITECTURE                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Cache Tiers & TTL Strategy                                      │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Hot Cache       │  │ Warm Cache      │  │ Cold Cache      │ │
│ │ (L1)            │  │ (L2)            │  │ (L3)            │ │
│ │ • TTL: 30s-5m   │  │ • TTL: 5m-30m   │  │ • TTL: 30m-24h  │ │
│ │ • Context API   │  │ • Workflow      │  │ • Historical    │ │
│ │ • Session data  │  │ • Pattern data  │  │ • Analytics     │ │
│ │ • Real-time     │  │ • ML models     │  │ • Aggregations  │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Cache Patterns                                                  │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ Write-Through: Critical data (workflow state, config)      │ │
│ │ Write-Behind:  Analytics data (metrics, patterns)          │ │
│ │ Cache-Aside:   Context data (investigation results)        │ │
│ │ Refresh-Ahead: Predictive caching for hot data            │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
│ Key Namespace Design                                            │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ kubernaut:context:{type}:{namespace}:{resource}             │ │
│ │ kubernaut:workflow:{id}:{step}                              │ │
│ │ kubernaut:session:{user_id}:{session_id}                   │ │
│ │ kubernaut:pattern:{signature}:{alert_type}                 │ │
│ │ kubernaut:health:{component}:{metric}                      │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
│ Performance Features                                            │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Pipeline        │  │ Cluster Mode    │  │ Persistence     │ │
│ │ Operations      │  │ (Sharding)      │  │ (RDB + AOF)     │ │
│ │ • Batch ops     │  │ • 3+ nodes      │  │ • Durability    │ │
│ │ • Atomic trans  │  │ • Auto failover │  │ • Fast recovery │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

**Implementation** (`pkg/storage/cache/redis_manager.go`):
```go
type RedisManager struct {
    client          redis.ClusterClient
    defaultTTL      time.Duration
    hotCacheTTL     time.Duration
    warmCacheTTL    time.Duration
    coldCacheTTL    time.Duration
    compressionEnabled bool
    log             *logrus.Logger
}

func (rm *RedisManager) SetWithTier(key string, value interface{}, tier CacheTier) error {
    var ttl time.Duration

    switch tier {
    case HotTier:
        ttl = rm.hotCacheTTL      // 30s-5m
    case WarmTier:
        ttl = rm.warmCacheTTL     // 5m-30m
    case ColdTier:
        ttl = rm.coldCacheTTL     // 30m-24h
    default:
        ttl = rm.defaultTTL
    }

    // Serialize and optionally compress
    data, err := rm.serialize(value)
    if err != nil {
        return fmt.Errorf("serialization failed: %w", err)
    }

    if rm.compressionEnabled && len(data) > 1024 {
        data = rm.compress(data)
    }

    return rm.client.Set(context.Background(), key, data, ttl).Err()
}

func (rm *RedisManager) GetWithMetrics(key string) (interface{}, bool, error) {
    start := time.Now()

    data, err := rm.client.Get(context.Background(), key).Bytes()
    if err == redis.Nil {
        rm.recordCacheMiss(key)
        return nil, false, nil
    }
    if err != nil {
        return nil, false, err
    }

    // Decompress if needed
    if rm.isCompressed(data) {
        data = rm.decompress(data)
    }

    value, err := rm.deserialize(data)
    if err != nil {
        return nil, false, fmt.Errorf("deserialization failed: %w", err)
    }

    rm.recordCacheHit(key, time.Since(start))
    return value, true, nil
}
```

### 3. Vector Database Integration

**Purpose**: Store and search high-dimensional vectors for pattern similarity, anomaly detection, and intelligent correlation.

**Vector Storage Architecture**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                    VECTOR DATABASE ARCHITECTURE               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Vector Types & Embeddings                                       │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Alert           │  │ Pattern         │  │ Context         │ │
│ │ Embeddings      │  │ Signatures      │  │ Vectors         │ │
│ │ • 512-1024 dim  │  │ • 256-512 dim   │  │ • 768-1536 dim  │ │
│ │ • Semantic      │  │ • Behavioral    │  │ • Multi-modal   │ │
│ │ • Classification│  │ • Correlation   │  │ • Contextual    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Indexing Strategy                                               │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ HNSW (Hierarchical Navigable Small World) Graphs           │ │
│ │ • M = 16 (max bidirectional links per node)                │ │
│ │ • efConstruction = 200 (search width during construction)  │ │
│ │ • efQuery = 100 (search width during query)                │ │
│ │ • Metric: Cosine similarity + Euclidean distance           │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
│ Collection Organization                                         │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ alerts_prod     │  │ patterns_hist   │  │ contexts_cache  │ │
│ │ • Real-time     │  │ • Historical    │  │ • Contextual    │ │
│ │ • High freq     │  │ • Pattern lib   │  │ • Investigation │ │
│ │ • 7 day TTL     │  │ • Permanent     │  │ • 30 day TTL    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Query Operations                                                │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Similarity      │  │ Clustering      │  │ Anomaly         │ │
│ │ Search (k-NN)   │  │ Analysis        │  │ Detection       │ │
│ │ • Top-k results │  │ • Group similar │  │ • Outlier ID    │ │
│ │ • Score thresh  │  │ • Centroid calc │  │ • Distance calc │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

**Implementation** (`pkg/storage/vector/weaviate_database.go`):
```go
type WeaviateDatabase struct {
    client          *weaviate.Client
    alertCollection string
    patternCollection string
    contextCollection string
    log             *logrus.Logger
}

func (wd *WeaviateDatabase) SearchSimilarPatterns(ctx context.Context, query *PatternQuery) (*SimilarityResult, error) {
    // Create GraphQL query for vector similarity search
    gqlQuery := fmt.Sprintf(`
        {
            Get {
                %s (
                    nearVector: {
                        vector: %v
                        certainty: %f
                    }
                    limit: %d
                ) {
                    signature
                    alertType
                    confidence
                    createdAt
                    _additional {
                        certainty
                        distance
                    }
                }
            }
        }
    `, wd.patternCollection, query.Vector, query.MinSimilarity, query.Limit)

    result, err := wd.client.GraphQL().Raw().WithQuery(gqlQuery).Do(ctx)
    if err != nil {
        return nil, fmt.Errorf("vector search failed: %w", err)
    }

    return wd.parseSearchResults(result), nil
}

func (wd *WeaviateDatabase) StorePatternVector(ctx context.Context, pattern *PatternVector) error {
    // Create object with vector and metadata
    object := &models.Object{
        Class: wd.patternCollection,
        Properties: map[string]interface{}{
            "signature":  pattern.Signature,
            "alertType":  pattern.AlertType,
            "confidence": pattern.Confidence,
            "createdAt":  pattern.CreatedAt.Format(time.RFC3339),
            "metadata":   pattern.Metadata,
        },
        Vector: pattern.Vector,
    }

    _, err := wd.client.Data().Creator().WithClassName(wd.patternCollection).WithObject(object).Do(ctx)
    if err != nil {
        return fmt.Errorf("vector storage failed: %w", err)
    }

    wd.log.WithFields(logrus.Fields{
        "signature":   pattern.Signature,
        "alert_type":  pattern.AlertType,
        "vector_size": len(pattern.Vector),
    }).Debug("Pattern vector stored successfully")

    return nil
}
```

### 4. Data Processing Pipeline

**Purpose**: Transform, validate, and enrich data flowing through the system with real-time and batch processing capabilities.

**Processing Pipeline Architecture**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                    DATA PROCESSING PIPELINE                   │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Data Ingestion                                                  │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Real-time       │  │ Batch           │  │ Event           │ │
│ │ Streams         │  │ Processing      │  │ Sourcing        │ │
│ │ • Alerts        │  │ • Log analysis  │  │ • State changes │ │
│ │ • Metrics       │  │ • Aggregations  │  │ • Audit trail   │ │
│ │ • Events        │  │ • ML training   │  │ • Recovery      │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Data Transformation                                             │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ • Schema validation and normalization                      │ │
│ │ • Data cleansing and quality checks                        │ │
│ │ • Format conversion (JSON, Protobuf, Avro)                │ │
│ │ • Enrichment with contextual metadata                      │ │
│ │ • Compression and optimization                             │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                              │                                  │
│                              ▼                                  │
│ Data Routing & Storage                                          │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Primary DB      │  │ Cache Layer     │  │ Vector Store    │ │
│ │ (Operational)   │  │ (Performance)   │  │ (Analytics)     │ │
│ │ • ACID writes   │  │ • Hot data      │  │ • Embeddings    │ │
│ │ • Consistency   │  │ • Sub-100ms     │  │ • Similarity    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Data Quality & Monitoring                                       │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ • Processing latency monitoring                             │ │
│ │ • Data quality metrics and alerting                        │ │
│ │ • Throughput and error rate tracking                       │ │
│ │ • Dead letter queue for failed processing                  │ │
│ └─────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

### 5. Backup and Recovery System

**Purpose**: Ensure data durability, disaster recovery, and business continuity with automated backup strategies.

**Backup Strategy Architecture**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                   BACKUP & RECOVERY SYSTEM                    │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Backup Tiers & Scheduling                                       │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Hot Backup      │  │ Warm Backup     │  │ Cold Storage    │ │
│ │ (Real-time)     │  │ (Daily)         │  │ (Archive)       │ │
│ │ • WAL shipping  │  │ • Full dumps    │  │ • Monthly       │ │
│ │ • Streaming     │  │ • Incremental   │  │ • Compressed    │ │
│ │ • <1s RPO       │  │ • <24h RPO      │  │ • Long-term     │ │
│ │ • <5min RTO     │  │ • <2h RTO       │  │ • Compliance    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Multi-Storage Backend                                           │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ PostgreSQL: pg_basebackup + WAL-E streaming                │ │
│ │ Redis: RDB snapshots + AOF replication                     │ │
│ │ Vector DB: Collection exports + metadata backup            │ │
│ │ File System: Incremental snapshots + versioning           │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
│ Recovery Procedures                                             │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Point-in-Time   │  │ Full System     │  │ Selective       │ │
│ │ Recovery        │  │ Recovery        │  │ Recovery        │ │
│ │ • PITR to sec   │  │ • Complete      │  │ • Table-level   │ │
│ │ • WAL replay    │  │ • Automated     │  │ • Custom data   │ │
│ │ • Validation    │  │ • Health checks │  │ • Testing       │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Disaster Recovery                                               │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ • Multi-region replication for critical data               │ │
│ │ • Automated failover with health monitoring                │ │
│ │ • Cross-site backup verification and testing               │ │
│ │ • Recovery runbooks and automated procedures               │ │
│ └─────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

## Data Models and Schemas

### Core Data Structures

**Workflow Execution Schema**:
```sql
CREATE TABLE workflow_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id VARCHAR(255) NOT NULL,
    alert_id VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL CHECK (status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
    started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    execution_log JSONB,
    metadata JSONB,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_by VARCHAR(255),

    -- Indexing for performance
    INDEX idx_workflow_executions_status (status),
    INDEX idx_workflow_executions_alert_id (alert_id),
    INDEX idx_workflow_executions_started_at (started_at),
    INDEX gin_workflow_executions_metadata USING gin(metadata),
    INDEX gin_workflow_executions_execution_log USING gin(execution_log)
);

-- Partitioning by month for performance
CREATE TABLE workflow_executions_y2024m01 PARTITION OF workflow_executions
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
```

**Action History Schema**:
```sql
CREATE TABLE action_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_id VARCHAR(255) NOT NULL,
    workflow_execution_id UUID REFERENCES workflow_executions(id),
    action_type VARCHAR(100) NOT NULL,
    action_parameters JSONB,
    success BOOLEAN NOT NULL,
    executed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    duration_ms INTEGER,
    error_message TEXT,
    context_hash VARCHAR(64),
    namespace VARCHAR(255),
    resource_type VARCHAR(100),
    resource_name VARCHAR(255),

    -- Performance indexes
    INDEX idx_action_history_alert_id (alert_id),
    INDEX idx_action_history_action_type (action_type),
    INDEX idx_action_history_executed_at (executed_at),
    INDEX idx_action_history_context_hash (context_hash),
    INDEX idx_action_history_success (success),
    INDEX gin_action_history_parameters USING gin(action_parameters)
);
```

**Pattern Signatures Schema**:
```sql
CREATE TABLE pattern_signatures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    signature VARCHAR(128) UNIQUE NOT NULL,
    alert_type VARCHAR(100) NOT NULL,
    pattern_data JSONB NOT NULL,
    confidence DECIMAL(5,4) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    occurrence_count INTEGER NOT NULL DEFAULT 1,
    success_rate DECIMAL(5,4),
    last_seen TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    -- Pattern matching optimization
    INDEX idx_pattern_signatures_alert_type (alert_type),
    INDEX idx_pattern_signatures_confidence (confidence),
    INDEX idx_pattern_signatures_last_seen (last_seen),
    INDEX gin_pattern_signatures_pattern_data USING gin(pattern_data)
);
```

## Performance Optimization

### Query Optimization Strategies

**Database Performance Features**:
```go
type DatabaseOptimizer struct {
    connectionPool   *sql.DB
    queryCache      map[string]*sql.Stmt
    indexAnalyzer   *IndexAnalyzer
    statisticsUpdater *StatisticsUpdater
    log             *logrus.Logger
}

func (do *DatabaseOptimizer) OptimizeQueries() error {
    // Analyze query patterns and suggest optimizations
    slowQueries := do.analyzeSlowQueries()

    for _, query := range slowQueries {
        // Suggest index improvements
        indexSuggestions := do.indexAnalyzer.SuggestIndexes(query)

        // Apply automatic optimizations
        if query.ExecutionTime > 1*time.Second {
            do.optimizeQuery(query, indexSuggestions)
        }
    }

    // Update table statistics for query planner
    return do.statisticsUpdater.UpdateStatistics()
}

func (do *DatabaseOptimizer) PrepareCommonQueries() error {
    commonQueries := map[string]string{
        "workflow_by_alert": `
            SELECT id, status, started_at, execution_log
            FROM workflow_executions
            WHERE alert_id = $1
            ORDER BY started_at DESC
            LIMIT 10
        `,
        "action_history_by_type": `
            SELECT action_type, success, AVG(duration_ms) as avg_duration
            FROM action_history
            WHERE action_type = $1 AND executed_at > $2
            GROUP BY action_type, success
        `,
        "pattern_by_signature": `
            SELECT signature, confidence, success_rate, pattern_data
            FROM pattern_signatures
            WHERE signature = $1
        `,
    }

    for name, query := range commonQueries {
        stmt, err := do.connectionPool.Prepare(query)
        if err != nil {
            return fmt.Errorf("failed to prepare query %s: %w", name, err)
        }
        do.queryCache[name] = stmt
    }

    return nil
}
```

### Cache Performance Optimization

**Multi-Level Cache Strategy**:
```go
type CachePerformanceOptimizer struct {
    l1Cache         *sync.Map           // In-memory for hot data
    l2Cache         *RedisManager       // Redis for warm data
    l3Cache         *DatabaseManager    // Database for cold data
    hitRateTarget   float64            // 80%+ requirement
    evictionPolicy  EvictionPolicy     // LRU with TTL
    compressionEnabled bool
    log             *logrus.Logger
}

func (cpo *CachePerformanceOptimizer) Get(key string) (interface{}, bool, error) {
    // L1 Cache check (fastest)
    if value, exists := cpo.l1Cache.Load(key); exists {
        cpo.recordHit(L1Cache, key)
        return value, true, nil
    }

    // L2 Cache check (Redis)
    if value, exists, err := cpo.l2Cache.Get(key); err == nil && exists {
        cpo.recordHit(L2Cache, key)
        // Promote to L1 if hot enough
        if cpo.isHotData(key) {
            cpo.l1Cache.Store(key, value)
        }
        return value, true, nil
    }

    // L3 Cache check (Database)
    if value, exists, err := cpo.l3Cache.Get(key); err == nil && exists {
        cpo.recordHit(L3Cache, key)
        // Promote to L2 and optionally L1
        cpo.l2Cache.SetWithTier(key, value, WarmTier)
        if cpo.isHotData(key) {
            cpo.l1Cache.Store(key, value)
        }
        return value, true, nil
    }

    cpo.recordMiss(key)
    return nil, false, nil
}

func (cpo *CachePerformanceOptimizer) MonitorPerformance() *CacheMetrics {
    return &CacheMetrics{
        L1HitRate:    cpo.calculateHitRate(L1Cache),
        L2HitRate:    cpo.calculateHitRate(L2Cache),
        L3HitRate:    cpo.calculateHitRate(L3Cache),
        OverallHitRate: cpo.calculateOverallHitRate(),
        AvgResponseTime: cpo.calculateAverageResponseTime(),
        EvictionRate:   cpo.calculateEvictionRate(),
        MemoryUsage:    cpo.calculateMemoryUsage(),
    }
}
```

## Integration Patterns

### Storage Service Integration

**Data Access Layer Pattern**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                    STORAGE SERVICE INTEGRATION                │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Application Services                                            │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Remediation Execution Engine │  │ AI Intelligence │  │ Context API     │ │
│ │                 │  │                 │  │                 │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Storage Abstraction Layer                                       │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ StorageManager Interface                                    │ │
│ │ • Get(key) → value, exists, error                          │ │
│ │ • Set(key, value, options) → error                         │ │
│ │ • Delete(key) → error                                      │ │
│ │ • Query(criteria) → results, error                         │ │
│ │ • Transaction(operations) → results, error                 │ │
│ └─────────────────────────────────────────────────────────────┘ │
│          │                     │                     │         │
│          ▼                     ▼                     ▼         │
│ Storage Implementation                                          │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ PostgreSQL      │  │ Redis Cache     │  │ Vector Store    │ │
│ │ Manager         │  │ Manager         │  │ Manager         │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

**Implementation** (`pkg/storage/manager.go`):
```go
type StorageManager interface {
    // Core operations
    Get(ctx context.Context, key string) (interface{}, bool, error)
    Set(ctx context.Context, key string, value interface{}, options ...SetOption) error
    Delete(ctx context.Context, key string) error

    // Advanced operations
    Query(ctx context.Context, criteria *QueryCriteria) (*QueryResult, error)
    Transaction(ctx context.Context, operations []Operation) (*TransactionResult, error)

    // Vector operations
    SearchSimilar(ctx context.Context, vector []float32, limit int) (*SimilarityResult, error)
    StoreVector(ctx context.Context, id string, vector []float32, metadata map[string]interface{}) error

    // Monitoring
    GetMetrics() *StorageMetrics
    HealthCheck() error
}

type UnifiedStorageManager struct {
    primaryDB    DatabaseManager
    cache        CacheManager
    vectorStore  VectorStoreManager
    config       *StorageConfig
    log          *logrus.Logger
}

func (usm *UnifiedStorageManager) Get(ctx context.Context, key string) (interface{}, bool, error) {
    // Try cache first
    if value, exists, err := usm.cache.Get(ctx, key); err == nil && exists {
        return value, true, nil
    }

    // Fallback to primary database
    value, exists, err := usm.primaryDB.Get(ctx, key)
    if err != nil {
        return nil, false, err
    }

    if exists {
        // Populate cache for future requests
        go usm.cache.Set(ctx, key, value, WithTTL(5*time.Minute))
    }

    return value, exists, nil
}
```

## Security and Compliance

### Data Security Framework

**Security Implementation**:
```ascii
┌─────────────────────────────────────────────────────────────────┐
│                      DATA SECURITY FRAMEWORK                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Encryption at Rest                                              │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Database        │  │ Cache           │  │ Vector Store    │ │
│ │ Encryption      │  │ Encryption      │  │ Encryption      │ │
│ │ • AES-256       │  │ • TLS + AES     │  │ • Field-level   │ │
│ │ • TDE enabled   │  │ • AUTH enabled  │  │ • Key rotation  │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                                                                 │
│ Access Control                                                  │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ • RBAC with principle of least privilege                   │ │
│ │ • Database-level row security policies                     │ │
│ │ • API authentication with JWT tokens                       │ │
│ │ • Network security with VPCs and security groups          │ │
│ └─────────────────────────────────────────────────────────────┘ │
│                                                                 │
│ Audit and Compliance                                            │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│ │ Access Logging  │  │ Data Lineage    │  │ Retention       │ │
│ │ • All queries   │  │ • Change        │  │ • Policies      │ │
│ │ • User actions  │  │ • Tracking      │  │ • Auto-purge    │ │
│ │ • System events │  │ • Audit trail   │  │ • Compliance    │ │
│ └─────────────────┘  └─────────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
```

## Monitoring and Observability

### Storage Metrics and Alerting

**Comprehensive Monitoring**:
```go
type StorageMetrics struct {
    // Performance metrics
    QueryLatency     map[string]time.Duration `json:"query_latency"`
    Throughput       map[string]int64         `json:"throughput"`
    ErrorRate        map[string]float64       `json:"error_rate"`

    // Resource metrics
    ConnectionPoolUtilization float64 `json:"connection_pool_utilization"`
    CacheHitRate             float64 `json:"cache_hit_rate"`
    StorageUtilization       float64 `json:"storage_utilization"`

    // Health metrics
    DatabaseHealth   HealthStatus `json:"database_health"`
    CacheHealth      HealthStatus `json:"cache_health"`
    VectorStoreHealth HealthStatus `json:"vector_store_health"`

    // Business metrics
    DataGrowthRate   float64 `json:"data_growth_rate"`
    BackupStatus     string  `json:"backup_status"`
    LastSuccessfulBackup time.Time `json:"last_successful_backup"`
}

func (sm *StorageManager) CollectMetrics() *StorageMetrics {
    return &StorageMetrics{
        QueryLatency: sm.measureQueryLatencies(),
        Throughput:   sm.measureThroughput(),
        ErrorRate:    sm.calculateErrorRates(),

        ConnectionPoolUtilization: sm.getConnectionPoolUtilization(),
        CacheHitRate:             sm.getCacheHitRate(),
        StorageUtilization:       sm.getStorageUtilization(),

        DatabaseHealth:    sm.checkDatabaseHealth(),
        CacheHealth:       sm.checkCacheHealth(),
        VectorStoreHealth: sm.checkVectorStoreHealth(),

        DataGrowthRate:       sm.calculateDataGrowthRate(),
        BackupStatus:         sm.getBackupStatus(),
        LastSuccessfulBackup: sm.getLastSuccessfulBackup(),
    }
}
```

## Future Enhancements

### Planned Improvements
- **Multi-Region Storage**: Global data distribution and synchronization
- **Advanced Vector Operations**: Hybrid search combining vector and traditional queries
- **Real-time Analytics**: Stream processing with Apache Kafka/Pulsar
- **ML-Driven Optimization**: Automated cache and query optimization

### Research Areas
- **Quantum-Safe Encryption**: Post-quantum cryptography for long-term security
- **Edge Storage**: Distributed storage for edge computing scenarios
- **Blockchain Integration**: Immutable audit trails and data integrity
- **Neuromorphic Computing**: Brain-inspired computing for pattern storage

---

## Related Documentation

- [AI Context Orchestration Architecture](AI_CONTEXT_ORCHESTRATION_ARCHITECTURE.md)
- [Intelligence & Pattern Discovery Architecture](INTELLIGENCE_PATTERN_DISCOVERY_ARCHITECTURE.md)
- [Remediation Execution Engine & Orchestration Architecture](WORKFLOW_ENGINE_ORCHESTRATION_ARCHITECTURE.md)
- [Production Monitoring](PRODUCTION_MONITORING.md)
- [Resilience Patterns](RESILIENCE_PATTERNS.md)

---

*This document describes the Storage & Data Management architecture for Kubernaut, enabling efficient data persistence, intelligent caching, vector operations, and scalable data processing for autonomous system operations. The architecture supports high performance, reliability, and security requirements for production environments.*