# BR-STORAGE-031-03: Schema Migration for ADR-033 Multi-Dimensional Tracking

**Business Requirement ID**: BR-STORAGE-031-03
**Category**: Data Storage Service
**Priority**: P0
**Target Version**: V1
**Status**: ❌ **CANCELLED** — ADR-033 removed per #1048 readiness audit
**Date**: November 5, 2025
**Implementation Date**: December 2025

---

## 📋 **Business Need**

### **Problem Statement**

ADR-033 introduces **Multi-Dimensional Success Tracking** (incident_type + workflow + action) for remediation effectiveness. The current `resource_action_traces` schema lacks the columns required to track:
- **Incident Type** (PRIMARY dimension): Which type of incident was remediated
- **Workflow** (SECONDARY dimension): Which workflow was executed
- **AI Execution Mode** (Hybrid Model): How AI selected the remediation strategy

**Current Limitations**:
- ❌ No `incident_type` column → Cannot track success rates by incident type
- ❌ No `workflow_id`/`workflow_version` → Cannot track workflow effectiveness
- ❌ No AI execution mode flags → Cannot validate ADR-033 Hybrid Model (90-9-1 distribution)
- ❌ Only `workflow_id` exists → Meaningless for AI-generated unique workflows

**Impact**:
- Cannot implement BR-STORAGE-031-01 (incident-type API)
- Cannot implement BR-STORAGE-031-02 (workflow API)
- AI cannot learn from historical remediation effectiveness
- No data foundation for ADR-033 Remediation Workflow Catalog

---

## 🎯 **Business Objective**

**Add 7 new columns to `resource_action_traces` table to enable multi-dimensional success tracking as defined in ADR-033.**

### **Success Criteria**
1. ✅ Schema migration adds 7 new columns (incident_type, workflow_id, workflow_version, etc.)
2. ✅ All new columns use native Go types (string, int, bool) - NO `sql.Null*` types (pre-release)
3. ✅ 7 indexes created for efficient aggregation queries
4. ✅ Migration is backward-compatible (nullable columns, no data loss)
5. ✅ Migration script is idempotent (can run multiple times safely)
6. ✅ Rollback script provided for emergency reversion
7. ✅ Zero downtime migration (additive changes only)

---

## 📊 **Use Cases**

### **Use Case 1: Enable Incident-Type Success Rate Tracking**

**Scenario**: AI needs to query success rates by incident type (e.g., `pod-oom-killer`).

**Current Flow** (Without BR-STORAGE-031-03):
```
1. AI queries Data Storage for incident-type success rate
2. Database lacks `incident_type` column
3. ❌ Query fails or returns meaningless data
4. ❌ AI cannot learn from historical effectiveness
```

**Desired Flow with BR-STORAGE-031-03**:
```
1. Migration adds `incident_type` column to schema
2. RemediationExecutor populates `incident_type` on audit creation
3. AI queries Data Storage: SELECT ... WHERE incident_type = 'pod-oom-killer'
4. ✅ Query returns accurate success rate for that incident type
5. ✅ AI can make data-driven workflow selections
```

---

### **Use Case 2: Track Workflow Effectiveness Across Versions**

**Scenario**: Team wants to compare `pod-oom-recovery v1.2` vs `v1.1` effectiveness.

**Current Flow**:
```
1. Team deploys new workflow version v1.2
2. Database lacks `workflow_id` and `workflow_version` columns
3. ❌ Cannot differentiate between v1.1 and v1.2 execution records
4. ❌ Cannot measure workflow version improvement
```

**Desired Flow with BR-STORAGE-031-03**:
```
1. Migration adds `workflow_id` and `workflow_version` columns
2. RemediationExecutor populates these fields on each execution
3. Query success rates: WHERE workflow_id = 'pod-oom-recovery' AND workflow_version = 'v1.2'
4. ✅ Measurable comparison: v1.2 (89% success) vs v1.1 (50% success)
5. ✅ Data-driven validation of new workflow versions
```

---

### **Use Case 3: Validate ADR-033 Hybrid Model Distribution**

**Scenario**: Architecture team wants to validate the ADR-033 Hybrid Model (90% catalog + 9% chaining + 1% manual).

**Current Flow**:
```
1. Architecture team wants to measure AI execution mode distribution
2. Database lacks AI execution mode flags
3. ❌ Cannot validate ADR-033 Hybrid Model assumptions
4. ❌ Cannot detect if AI is over-chaining or under-utilizing catalog
```

**Desired Flow with BR-STORAGE-031-03**:
```
1. Migration adds `ai_selected_workflow`, `ai_chained_workflows`, `ai_manual_escalation` flags
2. RemediationExecutor sets appropriate flag on each execution
3. Query execution mode distribution:
   SELECT
     SUM(CASE WHEN ai_selected_workflow THEN 1 ELSE 0 END) AS catalog_selected,
     SUM(CASE WHEN ai_chained_workflows THEN 1 ELSE 0 END) AS chained,
     SUM(CASE WHEN ai_manual_escalation THEN 1 ELSE 0 END) AS manual
   FROM resource_action_traces
   WHERE action_timestamp >= NOW() - INTERVAL '7 days'
4. ✅ Results: catalog=1800 (90%), chained=180 (9%), manual=20 (1%)
5. ✅ Validated ADR-033 Hybrid Model distribution
```

---

## 🔧 **Functional Requirements**

### **FR-STORAGE-031-03-01: Schema Migration Script**

**Requirement**: Data Storage Service SHALL provide a goose migration script to add 7 new columns to `resource_action_traces` table.

**Migration Script**: `migrations/012_adr033_multidimensional_tracking.sql`

**SQL Implementation** (Actual):
```sql
-- +goose Up
-- Add columns for multi-dimensional success tracking (ADR-033)

-- DIMENSION 1: INCIDENT TYPE (PRIMARY)
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS incident_type VARCHAR(100);
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS alert_name VARCHAR(255);
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS incident_severity VARCHAR(20);

-- DIMENSION 2: WORKFLOW (SECONDARY)
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS workflow_id VARCHAR(64);
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS workflow_version VARCHAR(20);
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS workflow_step_number INT;
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS workflow_execution_id VARCHAR(64);

-- AI EXECUTION MODE (HYBRID MODEL)
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS ai_selected_workflow BOOLEAN DEFAULT false;
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS ai_chained_workflows BOOLEAN DEFAULT false;
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS ai_manual_escalation BOOLEAN DEFAULT false;
ALTER TABLE resource_action_traces ADD COLUMN IF NOT EXISTS ai_workflow_customization JSONB;

-- Indexes for multi-dimensional queries
CREATE INDEX IF NOT EXISTS idx_incident_type_success
ON resource_action_traces(incident_type, execution_status, action_timestamp DESC)
WHERE incident_type IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_workflow_success
ON resource_action_traces(workflow_id, workflow_version, execution_status, action_timestamp DESC)
WHERE workflow_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_multidimensional_success
ON resource_action_traces(incident_type, workflow_id, action_type, execution_status, action_timestamp DESC)
WHERE incident_type IS NOT NULL AND workflow_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_workflow_execution
ON resource_action_traces(workflow_execution_id, workflow_step_number, action_timestamp DESC)
WHERE workflow_execution_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_ai_execution_mode
ON resource_action_traces(incident_type, ai_selected_workflow, ai_chained_workflows, ai_manual_escalation, action_timestamp DESC)
WHERE incident_type IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_alert_name_lookup
ON resource_action_traces(alert_name, execution_status, action_timestamp DESC)
WHERE alert_name IS NOT NULL;

-- +goose Down
-- Rollback: Remove ADR-033 columns and indexes
DROP INDEX IF EXISTS idx_alert_name_lookup;
DROP INDEX IF EXISTS idx_ai_execution_mode;
DROP INDEX IF EXISTS idx_workflow_execution;
DROP INDEX IF EXISTS idx_multidimensional_success;
DROP INDEX IF EXISTS idx_workflow_success;
DROP INDEX IF EXISTS idx_incident_type_success;

ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS ai_workflow_customization;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS ai_manual_escalation;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS ai_chained_workflows;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS ai_selected_workflow;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS workflow_execution_id;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS workflow_step_number;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS workflow_version;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS workflow_id;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS incident_severity;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS alert_name;
ALTER TABLE resource_action_traces DROP COLUMN IF EXISTS incident_type;
```

**Acceptance Criteria**:
- ✅ Migration script is syntactically valid PostgreSQL
- ✅ Migration adds exactly 11 new columns (3 for incident, 4 for workflow, 4 for AI mode)
- ✅ All new columns are nullable (backward compatible)
- ✅ Migration creates exactly 6 new indexes
- ✅ Rollback script removes all added columns and indexes cleanly
- ✅ Migration is idempotent (re-running does not cause errors)

---

### **FR-STORAGE-031-03-02: Go Model Updates**

**Requirement**: Data Storage Service SHALL update the `ActionTrace` Go struct to reflect new schema columns.

**Go Model**: `pkg/datastorage/models/action_trace.go`

**Implementation** (Actual):
```go
package models

type ActionTrace struct {
    // ========================================
    // EXISTING FIELDS (KEEP ALL - BACKWARD COMPATIBLE)
    // ========================================
    ActionID        string          `json:"action_id" db:"action_id"`
    ActionType      string          `json:"action_type" db:"action_type"`
    Status          string          `json:"status" db:"status"`
    // ... existing fields ...

    // ========================================
    // ADR-033: NEW FIELDS (NATIVE TYPES - PRE-RELEASE)
    // ========================================

    // DIMENSION 1: INCIDENT TYPE (PRIMARY) - REQUIRED for ADR-033
    IncidentType     string `json:"incident_type" db:"incident_type"`           // REQUIRED
    AlertName        string `json:"alert_name,omitempty" db:"alert_name"`       // OPTIONAL
    IncidentSeverity string `json:"incident_severity" db:"incident_severity"`   // REQUIRED

    // DIMENSION 2: WORKFLOW (SECONDARY) - REQUIRED for ADR-033
    WorkflowID          string `json:"workflow_id" db:"workflow_id"`                         // REQUIRED
    WorkflowVersion     string `json:"workflow_version" db:"workflow_version"`               // REQUIRED
    WorkflowStepNumber  int    `json:"workflow_step_number,omitempty" db:"workflow_step_number"` // OPTIONAL
    WorkflowExecutionID string `json:"workflow_execution_id" db:"workflow_execution_id"`     // REQUIRED

    // AI EXECUTION MODE (HYBRID MODEL: 90% catalog + 9% chaining + 1% manual)
    AISelectedWorkflow     bool            `json:"ai_selected_workflow" db:"ai_selected_workflow"`
    AIChainedWorkflows     bool            `json:"ai_chained_workflows" db:"ai_chained_workflows"`
    AIManualEscalation     bool            `json:"ai_manual_escalation" db:"ai_manual_escalation"`
    AIWorkflowCustomization json.RawMessage `json:"ai_workflow_customization,omitempty" db:"ai_workflow_customization"`
}
```

**Acceptance Criteria**:
- ✅ All new fields use native Go types (`string`, `int`, `bool`, `json.RawMessage`)
- ❌ NO `sql.NullString`, `sql.NullInt32`, or `sql.NullBool` types (pre-release simplification)
- ✅ JSON tags use `omitempty` for optional fields
- ✅ Struct compiles without errors
- ✅ All fields are exported (public)

---

### **FR-STORAGE-031-03-03: Zero Downtime Migration**

**Requirement**: Migration SHALL be executed without downtime or data loss.

**Migration Strategy**:
1. **Phase 1**: Add nullable columns (additive change, no downtime)
2. **Phase 2**: Create indexes concurrently (PostgreSQL `CREATE INDEX CONCURRENTLY`)
3. **Phase 3**: Deploy RemediationExecutor update to populate new fields
4. **Phase 4**: Backfill historical data (optional, low priority)

**Acceptance Criteria**:
- ✅ Migration does not lock `resource_action_traces` table for writes
- ✅ Existing queries continue to work during migration
- ✅ New columns default to NULL (no mandatory values during transition)
- ✅ No data loss or corruption during migration

---

## 📈 **Non-Functional Requirements**

### **NFR-STORAGE-031-03-01: Performance**

- ✅ Migration execution time <5 minutes for 1M existing records
- ✅ Index creation does not block writes (use `CONCURRENTLY`)
- ✅ Query performance maintained after migration (indexes on new columns)

### **NFR-STORAGE-031-03-02: Data Integrity**

- ✅ No data loss during migration
- ✅ Rollback script restores original schema exactly
- ✅ Foreign key constraints maintained (if applicable)

### **NFR-STORAGE-031-03-03: Backward Compatibility**

- ✅ Existing API endpoints continue to work (new fields omitted for older clients)
- ✅ Existing queries do not break (new columns are nullable)
- ✅ No breaking changes for Context API or RemediationExecutor

---

## 🔗 **Dependencies**

### **Upstream Dependencies**
- ✅ ADR-033: Remediation Workflow Catalog (architectural decision)
- ✅ PostgreSQL 16+ (per DD-011)
- ✅ goose migration tool installed

### **Downstream Impacts**
- ✅ BR-STORAGE-031-01: Incident-type API requires these columns
- ✅ BR-STORAGE-031-02: Workflow API requires these columns
- ✅ BR-REMEDIATION-015: RemediationExecutor must populate `incident_type`
- ✅ BR-REMEDIATION-016: RemediationExecutor must populate workflow fields

---

## 🚀 **Implementation Status**

### **✅ IMPLEMENTED** (December 2025)

| Phase | Status | Evidence |
|-------|--------|----------|
| Migration Script | ✅ Complete | `migrations/012_adr033_multidimensional_tracking.sql` |
| Go Model Updates | ✅ Complete | `pkg/datastorage/models/action_trace.go` |
| Success Rate APIs | ✅ Complete | `GetSuccessRateByIncidentType()`, `GetSuccessRateByWorkflow()` |
| Integration Tests | ✅ Complete | `test/unit/datastorage/repository_adr033_test.go` |

---

## 📊 **Success Metrics**

### **Migration Success**
- **Target**: 100% success rate on first migration attempt
- **Measure**: Zero failed migrations in dev/staging/production

### **Performance Impact**
- **Target**: <5% query performance degradation post-migration
- **Measure**: Compare P95 query times before/after migration

### **Data Integrity**
- **Target**: 100% data integrity maintained
- **Measure**: Row count before migration = row count after migration

---

## 🔄 **Alternatives Considered**

### **Alternative 1: Create New Table for ADR-033 Data**

**Approach**: Create `incident_workflow_tracking` table with foreign key to `resource_action_traces`

**Rejected Because**:
- ❌ Complexity: Requires JOIN queries for aggregation
- ❌ Performance: JOINs are slower than single-table queries
- ❌ Maintenance: Two tables to manage instead of one

---

### **Alternative 2: Use JSONB for All ADR-033 Data**

**Approach**: Add single `adr033_metadata JSONB` column

**Rejected Because**:
- ❌ Performance: JSONB queries are slower than native column queries
- ❌ Indexing: Harder to create efficient indexes on JSONB fields
- ❌ Type Safety: No compile-time type checking for Go structs

---

### **Alternative 3: Require Non-Nullable Columns**

**Approach**: Add columns as `NOT NULL` with default values

**Rejected Because**:
- ❌ Backward Compatibility: Breaks existing RemediationExecutor deployments
- ❌ Data Quality: Default values would be meaningless for historical records
- ❌ Risk: Higher migration failure risk (requires backfill strategy)

---

## ✅ **Approval**

**Status**: ✅ **IMPLEMENTED**
**Approved Date**: November 5, 2025
**Implemented Date**: December 2025
**Decision**: Implement as P0 priority (foundation for all ADR-033 features)
**Rationale**: Required for all other BR-STORAGE-031-XX requirements
**Approved By**: Architecture Team
**Related ADR**: [ADR-033: Remediation Workflow Catalog](../architecture/decisions/ADR-033-remediation-playbook-catalog.md)

---

## 📚 **References**

### **Related Business Requirements**
- BR-STORAGE-031-01: Incident-Type Success Rate API
- BR-STORAGE-031-02: Workflow Success Rate API
- BR-REMEDIATION-015: Populate incident_type on audit creation
- BR-REMEDIATION-016: Populate workflow metadata on audit creation

### **Related Documents**
- [ADR-033: Remediation Workflow Catalog](../architecture/decisions/ADR-033-remediation-playbook-catalog.md)
- [DD-011: PostgreSQL 16+ Version Requirements](../architecture/decisions/DD-011-postgresql-version-requirements.md)
- [Data Storage Implementation Plan V5.7](../services/stateless/data-storage/implementation/IMPLEMENTATION_PLAN_V5.7.md)
- [Migration Script: 012_adr033_multidimensional_tracking.sql](../../migrations/012_adr033_multidimensional_tracking.sql)

---

## 📜 **Changelog**

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0 | Nov 5, 2025 | Architecture Team | Initial BR creation, approved for V1 |
| 2.0 | Dec 10, 2025 | Data Storage Team | Updated terminology: "playbook" → "workflow"; Updated migration file reference (002 → 012); Updated status to IMPLEMENTED; Corrected SQL/Go examples to match actual implementation |

---

**Document Version**: 2.0
**Last Updated**: December 10, 2025
**Status**: ✅ **IMPLEMENTED**
