breakpilot-compliance/ai-compliance-sdk/migrations/001_rbac_schema.sql

-- AI Compliance SDK - RBAC Schema
-- Migration 001: Multi-Tenant RBAC with Namespace Isolation

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================================
-- Tenants (Mandanten)
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    settings JSONB DEFAULT '{}',
    max_users INT DEFAULT 100,
    llm_quota_monthly INT DEFAULT 10000,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_compliance_tenants_slug ON compliance_tenants(slug);
CREATE INDEX idx_compliance_tenants_status ON compliance_tenants(status);

-- ============================================================================
-- Namespaces (Abteilungen - z.B. CFO Use-Case)
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_namespaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    parent_namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    isolation_level VARCHAR(50) DEFAULT 'strict', -- 'strict', 'shared', 'public'
    data_classification VARCHAR(50) DEFAULT 'internal', -- 'public', 'internal', 'confidential', 'restricted'
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, slug)
);

CREATE INDEX idx_compliance_namespaces_tenant ON compliance_namespaces(tenant_id);
CREATE INDEX idx_compliance_namespaces_parent ON compliance_namespaces(parent_namespace_id);

-- ============================================================================
-- Roles with Permissions
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    permissions TEXT[] NOT NULL DEFAULT '{}',
    is_system_role BOOLEAN DEFAULT FALSE,
    hierarchy_level INT DEFAULT 100,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, name)
);

CREATE INDEX idx_compliance_roles_tenant ON compliance_roles(tenant_id);
CREATE INDEX idx_compliance_roles_system ON compliance_roles(is_system_role);

-- ============================================================================
-- System Roles (Pre-defined)
-- ============================================================================
INSERT INTO compliance_roles (name, description, permissions, is_system_role, hierarchy_level) VALUES
('compliance_executive', 'Executive mit Lesezugriff auf Compliance-Daten und LLM-Queries',
    ARRAY['compliance:*:read', 'llm:query:execute', 'audit:own:read'], TRUE, 10),
('compliance_officer', 'Compliance-Verantwortlicher mit vollem Zugriff',
    ARRAY['compliance:*', 'audit:*', 'llm:*', 'namespace:read'], TRUE, 20),
('data_protection_officer', 'Datenschutzbeauftragter',
    ARRAY['compliance:privacy:*', 'consent:*', 'dsr:*', 'audit:read', 'llm:query:execute'], TRUE, 25),
('namespace_admin', 'Administrator fuer einen Namespace',
    ARRAY['namespace:own:admin', 'compliance:own:*', 'llm:own:query', 'audit:own:read'], TRUE, 50),
('auditor', 'Auditor mit Lesezugriff',
    ARRAY['compliance:read', 'audit:log:read', 'evidence:read'], TRUE, 60),
('compliance_user', 'Standardbenutzer mit eingeschraenktem Zugriff',
    ARRAY['compliance:own:read', 'llm:own:query'], TRUE, 100)
ON CONFLICT (tenant_id, name) DO NOTHING;

-- ============================================================================
-- User-Role Assignments with Namespace Scope
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    role_id UUID NOT NULL REFERENCES compliance_roles(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE CASCADE,
    granted_by UUID NOT NULL,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, role_id, tenant_id, namespace_id)
);

CREATE INDEX idx_compliance_user_roles_user ON compliance_user_roles(user_id);
CREATE INDEX idx_compliance_user_roles_tenant ON compliance_user_roles(tenant_id);
CREATE INDEX idx_compliance_user_roles_namespace ON compliance_user_roles(namespace_id);
CREATE INDEX idx_compliance_user_roles_expires ON compliance_user_roles(expires_at) WHERE expires_at IS NOT NULL;

-- ============================================================================
-- LLM Access Policies
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_llm_policies (
    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 CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    allowed_data_categories TEXT[] DEFAULT '{}', -- 'salary', 'health', 'personal', 'financial'
    blocked_data_categories TEXT[] DEFAULT '{}',
    require_pii_redaction BOOLEAN DEFAULT TRUE,
    pii_redaction_level VARCHAR(50) DEFAULT 'strict', -- 'strict', 'moderate', 'minimal', 'none'
    allowed_models TEXT[] DEFAULT '{}', -- 'qwen2.5:7b', 'claude-3-sonnet'
    max_tokens_per_request INT DEFAULT 4000,
    max_requests_per_day INT DEFAULT 1000,
    max_requests_per_hour INT DEFAULT 100,
    is_active BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 100, -- Lower = higher priority
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_compliance_llm_policies_tenant ON compliance_llm_policies(tenant_id);
CREATE INDEX idx_compliance_llm_policies_namespace ON compliance_llm_policies(namespace_id);
CREATE INDEX idx_compliance_llm_policies_active ON compliance_llm_policies(is_active, priority);

-- ============================================================================
-- LLM Audit Log (Immutable)
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_llm_audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id),
    namespace_id UUID REFERENCES compliance_namespaces(id),
    user_id UUID NOT NULL,
    session_id VARCHAR(100),
    operation VARCHAR(100) NOT NULL, -- 'query', 'completion', 'embedding', 'analysis'
    model_used VARCHAR(100) NOT NULL,
    provider VARCHAR(50) NOT NULL, -- 'ollama', 'anthropic', 'openai'
    prompt_hash VARCHAR(64) NOT NULL, -- SHA-256 of prompt (no raw PII stored)
    prompt_length INT NOT NULL,
    response_length INT,
    tokens_used INT NOT NULL,
    duration_ms INT NOT NULL,
    pii_detected BOOLEAN DEFAULT FALSE,
    pii_types_detected TEXT[] DEFAULT '{}',
    pii_redacted BOOLEAN DEFAULT FALSE,
    policy_id UUID REFERENCES compliance_llm_policies(id),
    policy_violations TEXT[] DEFAULT '{}',
    data_categories_accessed TEXT[] DEFAULT '{}',
    error_message TEXT,
    request_metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Partitioning-ready indexes for large audit tables
CREATE INDEX idx_llm_audit_tenant_date ON compliance_llm_audit_log(tenant_id, created_at DESC);
CREATE INDEX idx_llm_audit_user ON compliance_llm_audit_log(user_id, created_at DESC);
CREATE INDEX idx_llm_audit_namespace ON compliance_llm_audit_log(namespace_id, created_at DESC);
CREATE INDEX idx_llm_audit_operation ON compliance_llm_audit_log(operation, created_at DESC);
CREATE INDEX idx_llm_audit_pii ON compliance_llm_audit_log(pii_detected, created_at DESC) WHERE pii_detected = TRUE;
CREATE INDEX idx_llm_audit_violations ON compliance_llm_audit_log(created_at DESC) WHERE array_length(policy_violations, 1) > 0;

-- ============================================================================
-- General Audit Trail
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_audit_trail (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id),
    namespace_id UUID REFERENCES compliance_namespaces(id),
    user_id UUID NOT NULL,
    action VARCHAR(100) NOT NULL, -- 'create', 'update', 'delete', 'access', 'export'
    resource_type VARCHAR(100) NOT NULL, -- 'role', 'namespace', 'policy', 'evidence'
    resource_id UUID,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_trail_tenant_date ON compliance_audit_trail(tenant_id, created_at DESC);
CREATE INDEX idx_audit_trail_user ON compliance_audit_trail(user_id, created_at DESC);
CREATE INDEX idx_audit_trail_resource ON compliance_audit_trail(resource_type, resource_id);

-- ============================================================================
-- API Keys for SDK Access
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    key_hash VARCHAR(64) NOT NULL UNIQUE, -- SHA-256 of API key
    key_prefix VARCHAR(8) NOT NULL, -- First 8 chars for identification
    permissions TEXT[] DEFAULT '{}',
    namespace_restrictions UUID[] DEFAULT '{}', -- Empty = all namespaces
    rate_limit_per_hour INT DEFAULT 1000,
    expires_at TIMESTAMPTZ,
    last_used_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT TRUE,
    created_by UUID NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_api_keys_tenant ON compliance_api_keys(tenant_id);
CREATE INDEX idx_api_keys_prefix ON compliance_api_keys(key_prefix);
CREATE INDEX idx_api_keys_active ON compliance_api_keys(is_active, expires_at);

-- ============================================================================
-- LLM Usage Statistics (Aggregated)
-- ============================================================================
CREATE TABLE IF NOT EXISTS compliance_llm_usage_stats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id),
    namespace_id UUID REFERENCES compliance_namespaces(id),
    user_id UUID,
    period_start DATE NOT NULL,
    period_type VARCHAR(20) NOT NULL, -- 'daily', 'weekly', 'monthly'
    total_requests INT DEFAULT 0,
    total_tokens INT DEFAULT 0,
    total_duration_ms BIGINT DEFAULT 0,
    requests_with_pii INT DEFAULT 0,
    policy_violations INT DEFAULT 0,
    models_used JSONB DEFAULT '{}', -- {"qwen2.5:7b": 100, "claude-3-sonnet": 50}
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, namespace_id, user_id, period_start, period_type)
);

CREATE INDEX idx_llm_usage_tenant_period ON compliance_llm_usage_stats(tenant_id, period_start DESC);

-- ============================================================================
-- Helper Functions
-- ============================================================================

-- Function to check if user has permission in namespace
CREATE OR REPLACE FUNCTION check_namespace_permission(
    p_user_id UUID,
    p_tenant_id UUID,
    p_namespace_id UUID,
    p_permission TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    has_permission BOOLEAN := FALSE;
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM compliance_user_roles ur
        JOIN compliance_roles r ON ur.role_id = r.id
        WHERE ur.user_id = p_user_id
          AND ur.tenant_id = p_tenant_id
          AND (ur.namespace_id = p_namespace_id OR ur.namespace_id IS NULL)
          AND (ur.expires_at IS NULL OR ur.expires_at > NOW())
          AND (
              p_permission = ANY(r.permissions)
              OR EXISTS (
                  SELECT 1 FROM unnest(r.permissions) perm
                  WHERE perm LIKE '%:*' AND p_permission LIKE replace(perm, ':*', '') || ':%'
              )
          )
    ) INTO has_permission;

    RETURN has_permission;
END;
$$ LANGUAGE plpgsql;

-- Function to get effective permissions for user in namespace
CREATE OR REPLACE FUNCTION get_effective_permissions(
    p_user_id UUID,
    p_tenant_id UUID,
    p_namespace_id UUID
) RETURNS TEXT[] AS $$
DECLARE
    permissions TEXT[];
BEGIN
    SELECT array_agg(DISTINCT perm)
    INTO permissions
    FROM (
        SELECT unnest(r.permissions) as perm
        FROM compliance_user_roles ur
        JOIN compliance_roles r ON ur.role_id = r.id
        WHERE ur.user_id = p_user_id
          AND ur.tenant_id = p_tenant_id
          AND (ur.namespace_id = p_namespace_id OR ur.namespace_id IS NULL)
          AND (ur.expires_at IS NULL OR ur.expires_at > NOW())
    ) sub;

    RETURN COALESCE(permissions, '{}');
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- Default Tenant for Breakpilot (Self-Hosting)
-- ============================================================================
INSERT INTO compliance_tenants (name, slug, settings, max_users, llm_quota_monthly)
VALUES (
    'Breakpilot',
    'breakpilot',
    '{"deployment": "self-hosted", "hybrid_mode": true}',
    1000,
    100000
) ON CONFLICT (slug) DO NOTHING;

-- Default namespaces
INSERT INTO compliance_namespaces (tenant_id, name, slug, data_classification)
SELECT
    t.id,
    ns.name,
    ns.slug,
    ns.classification
FROM compliance_tenants t
CROSS JOIN (VALUES
    ('Allgemein', 'general', 'internal'),
    ('Finanzen', 'finance', 'restricted'),
    ('Personal', 'hr', 'confidential'),
    ('IT', 'it', 'internal'),
    ('Compliance', 'compliance', 'confidential')
) AS ns(name, slug, classification)
WHERE t.slug = 'breakpilot'
ON CONFLICT (tenant_id, slug) DO NOTHING;