breakpilot-compliance/ai-compliance-sdk/migrations/005_roadmap_schema.sql

-- ============================================================================
-- Migration 005: Roadmap Schema
-- Compliance Roadmap Management with Import Support
-- ============================================================================

-- Roadmaps table
CREATE TABLE IF NOT EXISTS roadmaps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,

    title VARCHAR(255) NOT NULL,
    description TEXT,
    version VARCHAR(50) DEFAULT '1.0',

    -- Links to other entities
    assessment_id UUID REFERENCES ucca_assessments(id) ON DELETE SET NULL,
    portfolio_id UUID, -- Will reference portfolio table when created

    -- Status tracking
    status VARCHAR(50) DEFAULT 'draft', -- draft, active, completed, archived
    total_items INT DEFAULT 0,
    completed_items INT DEFAULT 0,
    progress INT DEFAULT 0, -- Percentage 0-100

    -- Timeline
    start_date DATE,
    target_date DATE,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Roadmap items table
CREATE TABLE IF NOT EXISTS roadmap_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    roadmap_id UUID NOT NULL REFERENCES roadmaps(id) ON DELETE CASCADE,

    -- Core fields
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category VARCHAR(50) DEFAULT 'TECHNICAL', -- TECHNICAL, ORGANIZATIONAL, PROCESSUAL, DOCUMENTATION, TRAINING
    priority VARCHAR(50) DEFAULT 'MEDIUM', -- CRITICAL, HIGH, MEDIUM, LOW
    status VARCHAR(50) DEFAULT 'PLANNED', -- PLANNED, IN_PROGRESS, BLOCKED, COMPLETED, DEFERRED

    -- Compliance mapping
    control_id VARCHAR(100), -- e.g., "CTRL-AVV"
    regulation_ref VARCHAR(255), -- e.g., "DSGVO Art. 28"
    gap_id VARCHAR(100), -- e.g., "GAP_AVV_MISSING"

    -- Effort estimation
    effort_days INT,
    effort_hours INT,
    estimated_cost INT, -- EUR

    -- Assignment
    assignee_id UUID,
    assignee_name VARCHAR(255),
    department VARCHAR(255),

    -- Timeline
    planned_start DATE,
    planned_end DATE,
    actual_start DATE,
    actual_end DATE,

    -- Dependencies (JSONB arrays of UUIDs)
    depends_on JSONB DEFAULT '[]',
    blocked_by JSONB DEFAULT '[]',

    -- Evidence
    evidence_required JSONB DEFAULT '[]', -- Array of strings
    evidence_provided JSONB DEFAULT '[]', -- Array of strings

    -- Notes
    notes TEXT,
    risk_notes TEXT,

    -- Import metadata
    source_row INT,
    source_file VARCHAR(500),

    -- Ordering
    sort_order INT DEFAULT 0,

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Import jobs table
CREATE TABLE IF NOT EXISTS roadmap_import_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    roadmap_id UUID REFERENCES roadmaps(id) ON DELETE SET NULL,

    -- File info
    filename VARCHAR(500) NOT NULL,
    format VARCHAR(50) NOT NULL, -- EXCEL, CSV, JSON
    file_size BIGINT,
    content_type VARCHAR(255),

    -- Status
    status VARCHAR(50) DEFAULT 'pending', -- pending, parsing, parsed, validating, completed, failed
    error_message TEXT,

    -- Parsing results
    total_rows INT DEFAULT 0,
    valid_rows INT DEFAULT 0,
    invalid_rows INT DEFAULT 0,
    imported_items INT DEFAULT 0,

    -- Parsed items (before confirmation)
    parsed_items JSONB DEFAULT '[]',

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    created_by UUID NOT NULL
);

-- ============================================================================
-- Indexes
-- ============================================================================

-- Roadmaps indexes
CREATE INDEX IF NOT EXISTS idx_roadmaps_tenant ON roadmaps(tenant_id);
CREATE INDEX IF NOT EXISTS idx_roadmaps_status ON roadmaps(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_roadmaps_assessment ON roadmaps(assessment_id) WHERE assessment_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_roadmaps_portfolio ON roadmaps(portfolio_id) WHERE portfolio_id IS NOT NULL;

-- Roadmap items indexes
CREATE INDEX IF NOT EXISTS idx_roadmap_items_roadmap ON roadmap_items(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_items_status ON roadmap_items(roadmap_id, status);
CREATE INDEX IF NOT EXISTS idx_roadmap_items_priority ON roadmap_items(roadmap_id, priority);
CREATE INDEX IF NOT EXISTS idx_roadmap_items_category ON roadmap_items(roadmap_id, category);
CREATE INDEX IF NOT EXISTS idx_roadmap_items_assignee ON roadmap_items(assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_roadmap_items_control ON roadmap_items(control_id) WHERE control_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_roadmap_items_deadline ON roadmap_items(planned_end) WHERE planned_end IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_roadmap_items_sort ON roadmap_items(roadmap_id, sort_order);

-- Import jobs indexes
CREATE INDEX IF NOT EXISTS idx_import_jobs_tenant ON roadmap_import_jobs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_import_jobs_status ON roadmap_import_jobs(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_import_jobs_roadmap ON roadmap_import_jobs(roadmap_id) WHERE roadmap_id IS NOT NULL;

-- ============================================================================
-- Triggers for updated_at
-- ============================================================================

-- Trigger function (reuse if exists)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Roadmaps trigger
DROP TRIGGER IF EXISTS update_roadmaps_updated_at ON roadmaps;
CREATE TRIGGER update_roadmaps_updated_at
    BEFORE UPDATE ON roadmaps
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Roadmap items trigger
DROP TRIGGER IF EXISTS update_roadmap_items_updated_at ON roadmap_items;
CREATE TRIGGER update_roadmap_items_updated_at
    BEFORE UPDATE ON roadmap_items
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Import jobs trigger
DROP TRIGGER IF EXISTS update_roadmap_import_jobs_updated_at ON roadmap_import_jobs;
CREATE TRIGGER update_roadmap_import_jobs_updated_at
    BEFORE UPDATE ON roadmap_import_jobs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- Comments
-- ============================================================================

COMMENT ON TABLE roadmaps IS 'Compliance implementation roadmaps';
COMMENT ON TABLE roadmap_items IS 'Individual items/tasks in a compliance roadmap';
COMMENT ON TABLE roadmap_import_jobs IS 'Track file imports for roadmap items';

COMMENT ON COLUMN roadmap_items.control_id IS 'Reference to controls catalog (e.g., CTRL-AVV)';
COMMENT ON COLUMN roadmap_items.regulation_ref IS 'Reference to regulation article (e.g., DSGVO Art. 28)';
COMMENT ON COLUMN roadmap_items.gap_id IS 'Reference to gap mapping (e.g., GAP_AVV_MISSING)';
COMMENT ON COLUMN roadmap_items.depends_on IS 'Array of item IDs this item depends on';
COMMENT ON COLUMN roadmap_items.blocked_by IS 'Array of item IDs currently blocking this item';