Audit Trails

// Audit log database schema
CREATE TABLE hitl_audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Request identification
  workflow_id VARCHAR(255) NOT NULL,
  execution_id UUID NOT NULL,
  hitl_step_id VARCHAR(255) NOT NULL,
  
  -- Event details
  event_type VARCHAR(50) NOT NULL,
  -- Types: request_created, notification_sent, viewed, 
  --        approved, rejected, edited, escalated, 
  --        auto_approved, timeout, delegated
  
  -- Actor information
  actor_type VARCHAR(20) NOT NULL, -- user, system, auto
  actor_id VARCHAR(255),
  actor_email VARCHAR(255),
  actor_role VARCHAR(100),
  actor_ip VARCHAR(45),
  actor_user_agent TEXT,
  
  -- Request snapshot (immutable)
  request_data JSONB NOT NULL,
  risk_score INT,
  risk_factors JSONB,
  
  -- Decision details
  decision VARCHAR(50), -- approve, reject, edit, escalate
  decision_reason TEXT,
  edits_made JSONB,
  
  -- Delegation/escalation
  delegated_from VARCHAR(255),
  escalated_from VARCHAR(255),
  escalation_reason TEXT,
  
  -- Auto-approval details
  auto_approval_rule VARCHAR(255),
  auto_approval_confidence DECIMAL(5,4),
  
  -- Timing
  created_at TIMESTAMPTZ DEFAULT NOW(),
  response_time_ms INT,
  
  -- Compliance
  retention_until TIMESTAMPTZ,
  compliance_tags TEXT[],
  
  -- Indexes
  INDEX idx_workflow (workflow_id, execution_id),
  INDEX idx_actor (actor_id, created_at),
  INDEX idx_event_type (event_type, created_at),
  INDEX idx_compliance (compliance_tags, retention_until)
);

-- Separate table for request snapshots (for data integrity)
CREATE TABLE hitl_request_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  audit_log_id UUID REFERENCES hitl_audit_logs(id),
  request_hash VARCHAR(64) NOT NULL, -- SHA-256 hash
  request_data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

// Configure audit logging for HITL
hitl({
  id: "financial-approval",
  
  audit: {
    enabled: true,
    
    // What to log
    events: {
      requestCreated: true,
      notificationSent: true,
      viewed: true,
      approved: true,
      rejected: true,
      edited: true,
      escalated: true,
      autoApproved: true,
      timeout: true,
      delegated: true
    },
    
    // Data retention
    retention: {
      default: "7y", // 7 years
      byComplianceTag: {
        "sox": "7y",
        "gdpr": "3y",
        "hipaa": "6y",
        "pci": "1y"
      }
    },
    
    // Data handling
    pii: {
      // Mask PII in logs
      mask: ["email", "phone", "ssn", "credit_card"],
      // Or encrypt
      encrypt: ["full_name", "address"]
    },
    
    // Integrity
    integrity: {
      // Hash request data for tamper detection
      hashRequests: true,
      // Digital signatures for critical decisions
      signDecisions: true,
      signatureKey: process.env.AUDIT_SIGNING_KEY
    },
    
    // Real-time streaming
    streaming: {
      enabled: true,
      destinations: [
        { type: "kafka", topic: "audit-logs" },
        { type: "s3", bucket: "audit-archive" }
      ]
    }
  }
})

// Query audit logs
import { auditLogs } from "@/lib/hitl/audit"

// Find all approvals by a specific user
const userApprovals = await auditLogs.query({
  actorId: "user_123",
  eventType: ["approved", "rejected"],
  dateRange: {
    from: "2024-01-01",
    to: "2024-03-31"
  },
  orderBy: "created_at",
  order: "desc",
  limit: 100
})

// Find all auto-approvals for audit review
const autoApprovals = await auditLogs.query({
  eventType: "auto_approved",
  workflowId: "expense-approval",
  dateRange: { from: "2024-03-01" }
})

// Find escalated requests
const escalations = await auditLogs.query({
  eventType: "escalated",
  riskScoreMin: 70
})

// Search by request content
const largePayments = await auditLogs.search({
  query: "request_data.amount > 10000",
  eventType: "approved"
})

// Compliance report
const complianceReport = await auditLogs.generateReport({
  type: "sox-compliance",
  period: "Q1-2024",
  includeAutoApprovals: true,
  includeEscalations: true,
  groupBy: ["workflow_id", "decision"],
  format: "pdf"
})

// Generate compliance reports
import { complianceReporter } from "@/lib/hitl/compliance"

// SOX compliance report
const soxReport = await complianceReporter.generate({
  standard: "SOX",
  period: "2024-Q1",
  
  sections: [
    {
      name: "Segregation of Duties",
      query: {
        // Find cases where same person requested and approved
        check: "requester !== approver"
      }
    },
    {
      name: "Approval Authority",
      query: {
        // Verify approvers had authority for amount
        check: "approver.authority >= request.amount"
      }
    },
    {
      name: "Timeliness",
      query: {
        // Check response times
        check: "response_time < sla_target"
      }
    }
  ],
  
  output: {
    format: "pdf",
    includeEvidence: true,
    signReport: true
  }
})

// Export for external audit
await complianceReporter.export({
  format: "csv",
  dateRange: { from: "2024-01-01", to: "2024-03-31" },
  fields: [
    "created_at",
    "workflow_id",
    "actor_email",
    "decision",
    "risk_score",
    "response_time_ms"
  ],
  destination: "s3://audit-exports/q1-2024.csv"
})

// Verify audit log integrity
import { auditIntegrity } from "@/lib/hitl/audit"

// Verify a specific log entry
const verification = await auditIntegrity.verify(logId)
// Returns: { valid: true, hash: "abc...", signature: "def..." }

// Batch verification
const batchResult = await auditIntegrity.verifyBatch({
  dateRange: { from: "2024-03-01", to: "2024-03-31" },
  stopOnError: false
})
// Returns: { total: 10000, valid: 9998, invalid: 2, errors: [...] }

// Generate integrity report
const integrityReport = await auditIntegrity.generateReport({
  period: "2024-Q1",
  includeHashes: true,
  format: "pdf"
})