---
title: "Audit Logging System"
---

# 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

```rust
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

```rust
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

```toml
[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

```rust
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

```rust
// 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

```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

```rust
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

```rust
// 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:

```rust
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

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

### Selective Logging

Disable verbose logging for performance:

```rust
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:

```rust
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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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