Azure AD & Keycloak
Federation with RBAC & SSO

-- Comprehensive audit event log (append-only)
CREATE TABLE audit_events (
  id            BIGSERIAL PRIMARY KEY,
  event_id      UUID DEFAULT gen_random_uuid() UNIQUE,
  event_type    VARCHAR(50) NOT NULL,
  category      VARCHAR(30) NOT NULL,
  severity      VARCHAR(10) DEFAULT 'INFO',
  timestamp     TIMESTAMPTZ DEFAULT NOW(),

  -- Actor
  user_id       VARCHAR(255) NOT NULL,
  user_email    VARCHAR(255),
  user_role     VARCHAR(50),
  ip_address    INET,
  user_agent    TEXT,
  session_id    VARCHAR(255),

  -- Target resource
  resource_type VARCHAR(50),
  resource_id   VARCHAR(255),
  resource_name VARCHAR(255),

  -- Security gates
  classification_level  INTEGER,
  classification_result VARCHAR(10),
  ntk_project_code      VARCHAR(50),
  ntk_result            VARCHAR(10),
  role_gate_result      VARCHAR(10),

  -- Payload
  action_detail JSONB,
  old_values    JSONB,
  new_values    JSONB,
  metadata      JSONB
);

-- Performance indexes
CREATE INDEX idx_audit_type     ON audit_events(event_type);
CREATE INDEX idx_audit_user     ON audit_events(user_id);
CREATE INDEX idx_audit_ts       ON audit_events(timestamp);
CREATE INDEX idx_audit_resource ON audit_events(resource_type, resource_id);
CREATE INDEX idx_audit_severity ON audit_events(severity);
CREATE INDEX idx_audit_category ON audit_events(category);
CREATE INDEX idx_audit_session  ON audit_events(session_id);
CREATE INDEX idx_audit_user_ts  ON audit_events(user_id, timestamp DESC);

-- Prevent tampering: revoke UPDATE/DELETE on audit table
REVOKE UPDATE, DELETE ON audit_events FROM app_user;
REVOKE UPDATE, DELETE ON audit_events FROM app_role;

-- Session tracking table
CREATE TABLE audit_sessions (
  session_id    VARCHAR(255) PRIMARY KEY,
  user_id       VARCHAR(255) NOT NULL,
  user_email    VARCHAR(255),
  idp_source    VARCHAR(50),
  login_at      TIMESTAMPTZ DEFAULT NOW(),
  logout_at     TIMESTAMPTZ,
  duration_sec  INTEGER,
  mfa_used      BOOLEAN DEFAULT false,
  ip_address    INET,
  user_agent    TEXT
);

// audit-logger.js — Centralized audit service
const { Pool } = require('pg');
const pool = new Pool();

const SEVERITY = {
  CREATE: 'INFO',  READ: 'INFO',  UPDATE: 'WARN',
  DELETE: 'WARN',  PERMISSION_DENIED: 'ALERT',
  ROLE_ASSIGNED: 'WARN',  ROLE_REVOKED: 'WARN',
  LOGIN_FAILED: 'ALERT',  NTK_DENIED: 'ALERT',
  BULK_EXPORT: 'ALERT',   AUDIT_LOG_ACCESSED: 'WARN'
};

async function logAuditEvent({
  eventType, category, userId, userEmail,
  userRole, ipAddress, userAgent, sessionId,
  resourceType, resourceId, resourceName,
  classificationLevel, classificationResult,
  ntkProjectCode, ntkResult, roleGateResult,
  actionDetail, oldValues, newValues, metadata
}) {
  const severity = SEVERITY[eventType] || 'INFO';

  await pool.query(`
    INSERT INTO audit_events (
      event_type, category, severity,
      user_id, user_email, user_role,
      ip_address, user_agent, session_id,
      resource_type, resource_id, resource_name,
      classification_level, classification_result,
      ntk_project_code, ntk_result, role_gate_result,
      action_detail, old_values, new_values, metadata
    ) VALUES (
      $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
      $11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21
    )`, [ eventType, category, severity,
      userId, userEmail, userRole,
      ipAddress, userAgent, sessionId,
      resourceType, resourceId, resourceName,
      classificationLevel, classificationResult,
      ntkProjectCode, ntkResult, roleGateResult,
      JSON.stringify(actionDetail),
      JSON.stringify(oldValues),
      JSON.stringify(newValues),
      JSON.stringify(metadata)
    ]);

  // Alert on high-severity events
  if (severity === 'ALERT') {
    await triggerSecurityAlert({ eventType, userId, resourceId });
  }
}

module.exports = { logAuditEvent };

// audit-middleware.js
const { logAuditEvent } = require('./audit-logger');

const HTTP_TO_CRUD = {
  GET: 'READ', POST: 'CREATE',
  PUT: 'UPDATE', PATCH: 'UPDATE', DELETE: 'DELETE'
};

function auditMiddleware(resourceType) {
  return async (req, res, next) => {
    const startTime = Date.now();
    const originalJson = res.json.bind(res);

    res.json = async (body) => {
      const duration = Date.now() - startTime;
      const eventType = HTTP_TO_CRUD[req.method] || req.method;

      await logAuditEvent({
        eventType,
        category: 'DATA_ACCESS',
        userId: req.user?.sub,
        userEmail: req.user?.email,
        userRole: req.user?.realm_role,
        ipAddress: req.ip,
        userAgent: req.get('User-Agent'),
        sessionId: req.user?.session_state,
        resourceType,
        resourceId: req.params.id || body?.id,
        resourceName: body?.title,
        classificationLevel: req.classificationLevel,
        classificationResult: req.classificationPassed
          ? 'PASS' : 'FAIL',
        ntkProjectCode: req.ntkProjectCode,
        ntkResult: req.ntkPassed ? 'PASS' : 'FAIL',
        roleGateResult: req.rolePassed ? 'PASS' : 'FAIL',
        actionDetail: {
          method: req.method,
          path: req.originalUrl,
          statusCode: res.statusCode,
          durationMs: duration
        },
        oldValues: req.previousValues || null,
        newValues: req.method !== 'GET' ? req.body : null,
        metadata: { correlationId: req.headers['x-request-id'] }
      });

      return originalJson(body);
    };

    next();
  };
}

// Usage in routes
app.get('/api/documents/:id',
  authenticate, requireRole('employee'),
  requireClassifiedAccess('read'),
  auditMiddleware('document'),
  getDocumentHandler
);

app.put('/api/documents/:id',
  authenticate, requireRole('manager'),
  requireClassifiedAccess('update'),
  captureOldValues('documents'),
  auditMiddleware('document'),
  updateDocumentHandler
);

-- Database trigger for automatic CRUD auditing
CREATE OR REPLACE FUNCTION fn_audit_documents()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_events (
    event_type, category, severity,
    user_id, resource_type, resource_id,
    old_values, new_values, metadata
  ) VALUES (
    TG_OP, 'DB_TRIGGER',
    CASE WHEN TG_OP = 'DELETE' THEN 'WARN' ELSE 'INFO' END,
    current_setting('app.current_user_id', true),
    'document', COALESCE(NEW.id, OLD.id)::TEXT,
    CASE WHEN TG_OP IN ('UPDATE','DELETE')
         THEN row_to_json(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE')
         THEN row_to_json(NEW) END,
    jsonb_build_object(
      'trigger', TG_NAME, 'table', TG_TABLE_NAME,
      'session_user', session_user, 'timestamp', NOW()
    )
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trg_audit_documents_insert
  AFTER INSERT ON documents
  FOR EACH ROW EXECUTE FUNCTION fn_audit_documents();
CREATE TRIGGER trg_audit_documents_update
  AFTER UPDATE ON documents
  FOR EACH ROW EXECUTE FUNCTION fn_audit_documents();
CREATE TRIGGER trg_audit_documents_delete
  AFTER DELETE ON documents
  FOR EACH ROW EXECUTE FUNCTION fn_audit_documents();

-- Audit trigger for role changes
CREATE OR REPLACE FUNCTION fn_audit_role_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_events (
    event_type, category, severity,
    user_id, resource_type, resource_id,
    old_values, new_values
  ) VALUES (
    CASE WHEN TG_OP = 'INSERT' THEN 'ROLE_ASSIGNED'
         WHEN TG_OP = 'DELETE' THEN 'ROLE_REVOKED'
         ELSE 'ROLE_UPDATED' END,
    'ROLE_MANAGEMENT', 'WARN',
    current_setting('app.current_user_id', true),
    'user_role', COALESCE(NEW.user_id, OLD.user_id),
    CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Audit trigger for NTK approval changes
CREATE OR REPLACE FUNCTION fn_audit_ntk_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_events (
    event_type, category, severity,
    user_id, resource_type, resource_id,
    ntk_project_code, ntk_result,
    old_values, new_values
  ) VALUES (
    CASE WHEN TG_OP = 'INSERT' THEN 'NTK_REQUESTED'
         WHEN NEW.status = 'approved' THEN 'NTK_APPROVED'
         WHEN NEW.status = 'denied' THEN 'NTK_DENIED'
         ELSE 'NTK_UPDATED' END,
    'NTK_MANAGEMENT',
    CASE WHEN NEW.status = 'denied' THEN 'ALERT' ELSE 'WARN' END,
    current_setting('app.current_user_id', true),
    'ntk_approval', COALESCE(NEW.user_id, OLD.user_id),
    COALESCE(NEW.project_code, OLD.project_code), NEW.status,
    CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD) END,
    row_to_json(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 1. All CRUD operations by a specific user (last 30 days)
SELECT event_type, resource_type, resource_id,
       timestamp, action_detail, classification_result
FROM   audit_events
WHERE  user_id = $1
  AND  category = 'DATA_ACCESS'
  AND  timestamp > NOW() - INTERVAL '30 days'
ORDER BY timestamp DESC;

-- 2. All denied access attempts (security review)
SELECT user_id, user_email, user_role, event_type,
       resource_id, ip_address, classification_result,
       ntk_result, role_gate_result, timestamp
FROM   audit_events
WHERE  severity = 'ALERT'
  AND  (classification_result = 'FAIL'
   OR   ntk_result = 'FAIL'
   OR   role_gate_result = 'FAIL')
ORDER BY timestamp DESC LIMIT 100;

-- 3. Role change history for compliance audit
SELECT event_type, user_id,
       old_values->>'role' AS previous_role,
       new_values->>'role' AS new_role,
       metadata->>'changed_by' AS changed_by, timestamp
FROM   audit_events
WHERE  category = 'ROLE_MANAGEMENT'
ORDER BY timestamp DESC;

-- 4. Anomaly: users accessing data outside business hours
SELECT user_id, user_email, event_type,
       resource_id, timestamp, ip_address
FROM   audit_events
WHERE  EXTRACT(HOUR FROM timestamp) NOT BETWEEN 7 AND 19
  AND  event_type IN ('READ','UPDATE','DELETE')
  AND  timestamp > NOW() - INTERVAL '7 days'
ORDER BY timestamp DESC;

-- 5. Executive summary: event counts by category
SELECT category, COUNT(*) AS total_events,
       COUNT(*) FILTER (WHERE severity = 'ALERT') AS alerts,
       COUNT(DISTINCT user_id) AS unique_users
FROM   audit_events
WHERE  timestamp > NOW() - INTERVAL '24 hours'
GROUP BY category ORDER BY alerts DESC;

// Keycloak Realm Settings → Events
// Enable: Login Events + Admin Events
// Event Listeners: jboss-logging, custom-audit-spi

Login Events:
  LOGIN, LOGIN_ERROR, LOGOUT, REGISTER,
  CODE_TO_TOKEN, CODE_TO_TOKEN_ERROR,
  REFRESH_TOKEN, REFRESH_TOKEN_ERROR,
  FEDERATED_IDENTITY_LINK

Admin Events:
  CREATE, UPDATE, DELETE (on Users, Roles,
  Clients, Groups, Identity Providers)

// Custom SPI forwards to audit_events table:
public class AuditEventListener implements
    EventListenerProvider {

  @Override
  public void onEvent(Event event) {
    auditService.log(AuditEvent.builder()
      .eventType(mapEventType(event.getType()))
      .category("AUTHENTICATION")
      .userId(event.getUserId())
      .ipAddress(event.getIpAddress())
      .sessionId(event.getSessionId())
      .metadata(Map.of(
        "realmId", event.getRealmId(),
        "clientId", event.getClientId(),
        "error", event.getError()
      ))
      .build());
  }
}