Audit Logging System¶

Overview¶

HeliosDB Lite includes a comprehensive audit logging system that tracks all DDL and DML operations for compliance, security, and debugging purposes. The audit system is designed to be:

Tamper-proof: Append-only log with cryptographic checksums
High-performance: Asynchronous logging that doesn't block operations
Configurable: Fine-grained control over what gets logged
Queryable: SQL interface to query audit logs
Compliant: Supports SOC2, HIPAA, GDPR requirements

Features¶

Operation Tracking¶

The audit system tracks:

DDL Operations: CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX
DML Operations: INSERT, UPDATE, DELETE
SELECT Queries: Optional (disabled by default due to verbosity)
Transaction Operations: BEGIN, COMMIT, ROLLBACK
Authentication: LOGIN, LOGOUT, GRANT, REVOKE
System Operations: BACKUP, RESTORE, VACUUM

Metadata Captured¶

For each operation, the audit log captures:

Timestamp: When the operation occurred
Session ID: Unique identifier for the session
User: Who performed the operation
Operation Type: Type of SQL operation
Target: Table or object affected
Query: Full SQL query text
Affected Rows: Number of rows changed
Success/Failure: Whether the operation succeeded
Error Message: If the operation failed
Checksum: Cryptographic hash for tamper detection

Optional metadata (configurable): - Client IP address - Application name - Database name - Query execution time

Configuration¶

Basic Configuration¶

use heliosdb_lite::audit::AuditConfig;

// Default configuration
let config = AuditConfig::default();
// Logs: DDL, DML (not SELECT), Auth
// Retention: 90 days
// Checksums: Enabled

// Minimal configuration (DDL only)
let config = AuditConfig::minimal();
// Logs: DDL only
// Retention: 30 days
// Checksums: Disabled (for performance)

// Verbose configuration (everything)
let config = AuditConfig::verbose();
// Logs: DDL, DML, SELECT, Transactions, Auth
// Retention: 365 days
// Checksums: Enabled

// Compliance configuration (SOC2, HIPAA, GDPR)
let config = AuditConfig::compliance();
// Logs: DDL, DML, Transactions, Auth
// Retention: 2555 days (7 years)
// Checksums: Enabled (tamper detection)

Custom Configuration¶

use heliosdb_lite::audit::{AuditConfig, MetadataCapture};

let config = AuditConfig {
    enabled: true,
    log_ddl: true,
    log_dml: true,
    log_select: false,
    log_transactions: true,
    log_auth: true,
    retention_days: 180,
    async_buffer_size: 100,
    enable_checksums: true,
    max_query_length: 10000,
    capture_metadata: MetadataCapture {
        capture_client_ip: true,
        capture_application_name: true,
        capture_database_name: true,
        capture_execution_time: true,
        capture_custom_fields: false,
    },
};

TOML Configuration¶

[audit]
enabled = true
log_ddl = true
log_dml = true
log_select = false
log_transactions = false
log_auth = true
retention_days = 90
async_buffer_size = 100
enable_checksums = true
max_query_length = 10000

[audit.capture_metadata]
capture_client_ip = true
capture_application_name = true
capture_database_name = true
capture_execution_time = true
capture_custom_fields = false

Usage¶

Initializing Audit Logger¶

use heliosdb_lite::{EmbeddedDatabase, Config};
use heliosdb_lite::audit::{AuditLogger, AuditConfig};
use std::sync::Arc;

// Create database
let config = Config::in_memory();
let db = EmbeddedDatabase::new_in_memory()?;

// Get storage reference
let storage = Arc::new(
    heliosdb_lite::storage::StorageEngine::open_in_memory(&config)?
);

// Create audit logger
let audit_config = AuditConfig::default();
let logger = AuditLogger::new(storage, audit_config)?;

Logging Operations¶

// Log DDL operation
logger.log_ddl(
    "CREATE TABLE",
    "users",
    "CREATE TABLE users (id INT, name TEXT)",
    true,  // success
    None,  // no error
)?;

// Log DML operation
logger.log_dml(
    "INSERT",
    "users",
    "INSERT INTO users VALUES (1, 'Alice')",
    1,     // affected rows
    true,  // success
    None,  // no error
)?;

// Log failed operation
logger.log_dml(
    "UPDATE",
    "nonexistent_table",
    "UPDATE nonexistent_table SET x=1",
    0,     // no rows affected
    false, // failed
    Some("Table does not exist"),
)?;

// Log SELECT query (if enabled)
logger.log_select(
    "users",
    "SELECT * FROM users WHERE id = 1",
    1,         // row count
    Some(50),  // execution time in ms
)?;

Querying Audit Logs¶

Using SQL¶

-- Get all audit events
SELECT * FROM __audit_log ORDER BY id DESC LIMIT 100;

-- Filter by operation type
SELECT * FROM __audit_log WHERE operation = 'INSERT';

-- Filter by user
SELECT * FROM __audit_log WHERE user = 'alice';

-- Filter by time range
SELECT * FROM __audit_log
WHERE timestamp >= '2024-01-01T00:00:00Z'
  AND timestamp <= '2024-12-31T23:59:59Z';

-- Filter by success/failure
SELECT * FROM __audit_log WHERE success = false;

-- Filter by target table
SELECT * FROM __audit_log WHERE target = 'users';

Using Query Builder¶

use heliosdb_lite::audit::{AuditQuery, OperationType};
use chrono::Utc;

// Build query
let query = AuditQuery::new()
    .with_operation(OperationType::Insert)
    .with_target("users".to_string())
    .with_user("alice".to_string())
    .with_success(true)
    .limit(100)
    .offset(0);

// Execute query
let sql = query.build_sql();
let tuples = logger.query_audit_log(&sql)?;

// Parse events
let events = AuditQuery::parse_events(tuples)?;

// Process events
for event in events {
    println!("{}: {} on {} by {}",
        event.timestamp,
        event.operation,
        event.target.unwrap_or_default(),
        event.user
    );
}

Audit Log Schema¶

The __audit_log table has the following schema:

Column	Type	Description
id	INT8	Unique event ID (auto-incrementing)
timestamp	TIMESTAMP	When the operation occurred
session_id	TEXT	Session identifier
user	TEXT	User who performed the operation
operation	TEXT	Operation type (INSERT, UPDATE, etc.)
target	TEXT	Target object (table name, etc.)
query	TEXT	Full SQL query
affected_rows	INT8	Number of rows affected
success	BOOLEAN	Whether operation succeeded
error	TEXT	Error message (if failed)
checksum	TEXT	Cryptographic checksum (SHA-256)

Security Features¶

Tamper-Proof Logging¶

The audit log is designed to be tamper-proof:

Append-Only: Events can only be added, never modified or deleted
Cryptographic Checksums: Each event has a SHA-256 checksum
Separate Storage: Audit data stored in dedicated table
Verification: Checksums can be verified to detect tampering

// Verify event integrity
let event = /* ... get event from log ... */;
if !event.verify_checksum() {
    eprintln!("WARNING: Audit event {} has been tampered with!", event.id);
}

Access Control¶

The __audit_log table should be protected:

Read-only access for auditors
No write/delete permissions for users
System-level access only for the audit logger

Sensitive Data Protection¶

Configure the audit system to avoid logging sensitive data:

let config = AuditConfig {
    max_query_length: 1000,  // Truncate long queries
    // Don't log SELECT queries (may contain sensitive data)
    log_select: false,
    ..Default::default()
};

Performance Considerations¶

Async Logging¶

The audit system uses asynchronous logging to minimize performance impact:

Operations are logged to an in-memory buffer
A background task flushes the buffer to storage
Database operations are never blocked by audit logging

let config = AuditConfig {
    async_buffer_size: 500,  // Larger buffer = fewer flushes
    ..Default::default()
};

Selective Logging¶

Disable verbose logging for performance:

let config = AuditConfig {
    log_select: false,       // Don't log SELECT queries
    log_transactions: false, // Don't log BEGIN/COMMIT/ROLLBACK
    ..Default::default()
};

Query Truncation¶

Truncate long queries to save space:

let config = AuditConfig {
    max_query_length: 5000,  // Truncate queries > 5KB
    ..Default::default()
};

Compliance¶

SOC2 Compliance¶

SOC2 requires tracking: - Who accessed the system (user) - What they did (operation) - When they did it (timestamp) - Whether it succeeded (success)

Use AuditConfig::compliance() for SOC2 compliance.

HIPAA Compliance¶

HIPAA requires: - Audit trails for all access to protected health information (PHI) - 7-year retention period - Tamper-proof logging

Use AuditConfig::compliance() with retention_days: 2555 (7 years).

GDPR requires: - Logging of data access and modifications - Ability to produce audit reports - Retention periods (varies by jurisdiction)

Use AuditConfig::compliance() and adjust retention as needed.

Maintenance¶

Log Retention¶

Implement log retention policy:

-- Delete old audit events (run periodically)
DELETE FROM __audit_log
WHERE timestamp < NOW() - INTERVAL '90 days';

Note: This breaks the append-only guarantee. Consider archiving instead:

-- Archive old events to separate table
INSERT INTO __audit_log_archive
SELECT * FROM __audit_log
WHERE timestamp < NOW() - INTERVAL '90 days';

DELETE FROM __audit_log
WHERE timestamp < NOW() - INTERVAL '90 days';

Monitoring¶

Monitor audit log size and growth:

-- Count events by day
SELECT DATE(timestamp) as day, COUNT(*) as events
FROM __audit_log
GROUP BY DATE(timestamp)
ORDER BY day DESC;

-- Count events by operation type
SELECT operation, COUNT(*) as count
FROM __audit_log
GROUP BY operation
ORDER BY count DESC;

-- Find failed operations
SELECT * FROM __audit_log
WHERE success = false
ORDER BY timestamp DESC;

Best Practices¶

Enable audit logging in production: Always enable for compliance and security
Use appropriate configuration: Choose minimal/default/verbose/compliance based on needs
Protect the audit log: Restrict access to the __audit_log table
Monitor regularly: Review audit logs for anomalies and failed operations
Archive old logs: Implement retention policies to manage storage
Verify checksums: Periodically verify event checksums for tampering
Don't log sensitive data: Avoid logging passwords, API keys, or PII in queries
Test audit configuration: Verify that the right events are being logged

Troubleshooting¶

Audit events not appearing¶

Check if audit logging is enabled: config.audit.enabled
Check if the operation type is enabled (e.g., log_dml, log_select)
Give async task time to flush (100-200ms in tests)
Check for errors in audit logger initialization

Performance issues¶

Reduce buffer size to flush more frequently (or increase to flush less)
Disable verbose logging (SELECT queries, transactions)
Increase max_query_length to truncate queries earlier
Monitor buffer flush rate and adjust async_buffer_size

Storage growing too fast¶

Implement retention policy (delete/archive old events)
Disable SELECT query logging
Truncate long queries with max_query_length
Reduce retention period

Examples¶

See tests/audit_tests.rs for comprehensive examples.

API Reference¶

AuditLogger: Main audit logging interface
AuditConfig: Configuration for audit logging
AuditEvent: Represents a single audit event
AuditQuery: Query builder for audit logs
OperationType: Enum of all operation types
AuditMetadata: Additional metadata for events