breakpilot-compliance/ai-compliance-sdk/migrations/007_portfolio_schema.sql

-- ============================================================================
-- Migration 007: Portfolio Schema
-- AI Use Case Portfolio Management with Merge Support
-- ============================================================================

-- Portfolios table
CREATE TABLE IF NOT EXISTS portfolios (
    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,

    -- Info
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'DRAFT', -- DRAFT, ACTIVE, REVIEW, APPROVED, ARCHIVED

    -- Organization
    department VARCHAR(255),
    business_unit VARCHAR(255),
    owner VARCHAR(255),
    owner_email VARCHAR(255),

    -- Aggregated metrics (computed)
    total_assessments INT DEFAULT 0,
    total_roadmaps INT DEFAULT 0,
    total_workshops INT DEFAULT 0,
    avg_risk_score DECIMAL(5,2) DEFAULT 0,
    high_risk_count INT DEFAULT 0,
    conditional_count INT DEFAULT 0,
    approved_count INT DEFAULT 0,
    compliance_score DECIMAL(5,2) DEFAULT 0, -- 0-100

    -- Settings (JSONB)
    settings JSONB DEFAULT '{}',

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

-- Portfolio items table (links portfolios to assessments, roadmaps, workshops)
CREATE TABLE IF NOT EXISTS portfolio_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID NOT NULL REFERENCES portfolios(id) ON DELETE CASCADE,
    item_type VARCHAR(50) NOT NULL, -- ASSESSMENT, ROADMAP, WORKSHOP, DOCUMENT
    item_id UUID NOT NULL,

    -- Cached info from the linked item
    title VARCHAR(500),
    status VARCHAR(50),
    risk_level VARCHAR(20),
    risk_score INT DEFAULT 0,
    feasibility VARCHAR(20),

    -- Ordering and categorization
    sort_order INT DEFAULT 0,
    tags JSONB DEFAULT '[]',
    notes TEXT,

    -- Audit
    added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    added_by UUID NOT NULL,

    -- Unique constraint: item can only be in portfolio once
    UNIQUE(portfolio_id, item_id)
);

-- Portfolio activity log table
CREATE TABLE IF NOT EXISTS portfolio_activity (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID NOT NULL REFERENCES portfolios(id) ON DELETE CASCADE,

    -- Activity info
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    action VARCHAR(50) NOT NULL, -- added, removed, updated, merged, approved, submitted
    item_type VARCHAR(50),
    item_id UUID,
    item_title VARCHAR(500),
    user_id UUID NOT NULL,

    -- Additional details
    details JSONB
);

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

-- Portfolio indexes
CREATE INDEX IF NOT EXISTS idx_portfolios_tenant ON portfolios(tenant_id);
CREATE INDEX IF NOT EXISTS idx_portfolios_tenant_status ON portfolios(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_portfolios_department ON portfolios(tenant_id, department) WHERE department IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_portfolios_business_unit ON portfolios(tenant_id, business_unit) WHERE business_unit IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_portfolios_owner ON portfolios(owner) WHERE owner IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_portfolios_created_by ON portfolios(created_by);
CREATE INDEX IF NOT EXISTS idx_portfolios_risk_score ON portfolios(avg_risk_score);

-- Portfolio item indexes
CREATE INDEX IF NOT EXISTS idx_portfolio_items_portfolio ON portfolio_items(portfolio_id);
CREATE INDEX IF NOT EXISTS idx_portfolio_items_type ON portfolio_items(portfolio_id, item_type);
CREATE INDEX IF NOT EXISTS idx_portfolio_items_item ON portfolio_items(item_id);
CREATE INDEX IF NOT EXISTS idx_portfolio_items_risk ON portfolio_items(portfolio_id, risk_level);
CREATE INDEX IF NOT EXISTS idx_portfolio_items_feasibility ON portfolio_items(portfolio_id, feasibility);
CREATE INDEX IF NOT EXISTS idx_portfolio_items_sort ON portfolio_items(portfolio_id, sort_order);

-- Activity indexes
CREATE INDEX IF NOT EXISTS idx_portfolio_activity_portfolio ON portfolio_activity(portfolio_id);
CREATE INDEX IF NOT EXISTS idx_portfolio_activity_timestamp ON portfolio_activity(portfolio_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_portfolio_activity_user ON portfolio_activity(user_id);

-- ============================================================================
-- Triggers
-- ============================================================================

-- Reuse existing update_updated_at_column function

-- Portfolios trigger
DROP TRIGGER IF EXISTS update_portfolios_updated_at ON portfolios;
CREATE TRIGGER update_portfolios_updated_at
    BEFORE UPDATE ON portfolios
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- Functions for Metrics Calculation
-- ============================================================================

-- Function to recalculate portfolio metrics
CREATE OR REPLACE FUNCTION recalculate_portfolio_metrics(p_portfolio_id UUID)
RETURNS VOID AS $$
DECLARE
    v_total_assessments INT;
    v_total_roadmaps INT;
    v_total_workshops INT;
    v_avg_risk DECIMAL(5,2);
    v_high_risk INT;
    v_conditional INT;
    v_approved INT;
    v_compliance DECIMAL(5,2);
BEGIN
    -- Count by type
    SELECT COUNT(*) INTO v_total_assessments
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id AND item_type = 'ASSESSMENT';

    SELECT COUNT(*) INTO v_total_roadmaps
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id AND item_type = 'ROADMAP';

    SELECT COUNT(*) INTO v_total_workshops
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id AND item_type = 'WORKSHOP';

    -- Calculate risk metrics
    SELECT COALESCE(AVG(risk_score), 0) INTO v_avg_risk
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id AND item_type = 'ASSESSMENT';

    SELECT COUNT(*) INTO v_high_risk
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id
      AND item_type = 'ASSESSMENT'
      AND risk_level IN ('HIGH', 'UNACCEPTABLE');

    SELECT COUNT(*) INTO v_conditional
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id
      AND item_type = 'ASSESSMENT'
      AND feasibility = 'CONDITIONAL';

    SELECT COUNT(*) INTO v_approved
    FROM portfolio_items
    WHERE portfolio_id = p_portfolio_id
      AND item_type = 'ASSESSMENT'
      AND feasibility = 'YES';

    -- Calculate compliance score
    IF v_total_assessments > 0 THEN
        v_compliance := (v_approved::DECIMAL / v_total_assessments) * 100;
    ELSE
        v_compliance := 0;
    END IF;

    -- Update portfolio
    UPDATE portfolios SET
        total_assessments = v_total_assessments,
        total_roadmaps = v_total_roadmaps,
        total_workshops = v_total_workshops,
        avg_risk_score = v_avg_risk,
        high_risk_count = v_high_risk,
        conditional_count = v_conditional,
        approved_count = v_approved,
        compliance_score = v_compliance,
        updated_at = NOW()
    WHERE id = p_portfolio_id;
END;
$$ LANGUAGE plpgsql;

-- Trigger function to auto-update metrics on item changes
CREATE OR REPLACE FUNCTION portfolio_items_metrics_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        PERFORM recalculate_portfolio_metrics(OLD.portfolio_id);
        RETURN OLD;
    ELSE
        PERFORM recalculate_portfolio_metrics(NEW.portfolio_id);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Trigger for auto metrics update
DROP TRIGGER IF EXISTS trg_portfolio_items_metrics ON portfolio_items;
CREATE TRIGGER trg_portfolio_items_metrics
    AFTER INSERT OR UPDATE OR DELETE ON portfolio_items
    FOR EACH ROW EXECUTE FUNCTION portfolio_items_metrics_trigger();

-- ============================================================================
-- Views
-- ============================================================================

-- View for portfolio summary with counts
CREATE OR REPLACE VIEW portfolio_summary_view AS
SELECT
    p.id,
    p.tenant_id,
    p.name,
    p.description,
    p.status,
    p.department,
    p.business_unit,
    p.owner,
    p.total_assessments,
    p.total_roadmaps,
    p.total_workshops,
    p.avg_risk_score,
    p.high_risk_count,
    p.conditional_count,
    p.approved_count,
    p.compliance_score,
    p.created_at,
    p.updated_at,
    (p.total_assessments + p.total_roadmaps + p.total_workshops) as total_items,
    CASE
        WHEN p.high_risk_count > 0 THEN 'CRITICAL'
        WHEN p.conditional_count > p.approved_count THEN 'WARNING'
        ELSE 'GOOD'
    END as health_status
FROM portfolios p;

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

COMMENT ON TABLE portfolios IS 'AI use case portfolios for grouping and managing multiple assessments';
COMMENT ON TABLE portfolio_items IS 'Items linked to portfolios (assessments, roadmaps, workshops)';
COMMENT ON TABLE portfolio_activity IS 'Activity log for portfolio changes';

COMMENT ON COLUMN portfolios.compliance_score IS 'Percentage of assessments with YES feasibility (0-100)';
COMMENT ON COLUMN portfolios.avg_risk_score IS 'Average risk score across all assessments in portfolio';
COMMENT ON COLUMN portfolio_items.item_type IS 'Type of linked item: ASSESSMENT, ROADMAP, WORKSHOP, DOCUMENT';
COMMENT ON COLUMN portfolio_items.sort_order IS 'Custom ordering within the portfolio';

COMMENT ON FUNCTION recalculate_portfolio_metrics(UUID) IS 'Recalculates aggregated metrics for a portfolio';