Audit Logging System

Overview

HeliosDB Nano includes an 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_nano::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_nano::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_nano::{EmbeddedDatabase, Config};
use heliosdb_nano::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_nano::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_nano::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:

1. Append-Only: Events can only be added, never modified or deleted
2. Cryptographic Checksums: Each event has a SHA-256 checksum
3. Separate Storage: Audit data stored in dedicated table
4. 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 Compliance

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

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

Troubleshooting

Audit events not appearing

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

Performance issues

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

Storage growing too fast

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

Examples

See tests/audit_tests.rs for 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