Audit Logging

FraiseQL provides comprehensive audit logging for compliance and security monitoring, with multiple backend options.

Overview

Audit logging tracks:

• GraphQL queries and mutations
• Authentication events (login, logout, token refresh)
• Authorization decisions (access granted/denied)
• Administrative operations
• Data access patterns

Backends

PostgreSQL

Store audit logs in the database for querying and retention. Enable via [security.enterprise]:

[security.enterprise]
audit_logging_enabled = true
audit_log_level       = "info"   # "debug" | "info" | "warn"

FraiseQL writes to the ta_audit_log table you create in your database (see schema below). Retention and batching are managed by your PostgreSQL maintenance jobs.

Schema (auto-created):

PostgreSQL

CREATE TABLE tb_audit_log (
    pk_audit_log BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    level TEXT NOT NULL,
    event_type TEXT NOT NULL,
    user_id UUID,
    tenant_id UUID,
    operation TEXT,
    resource TEXT,
    details JSONB,
    ip_address INET,
    user_agent TEXT,
    request_id UUID,
    duration_ms INTEGER
);

CREATE INDEX idx_audit_log_timestamp ON tb_audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_user ON tb_audit_log(user_id, timestamp DESC);
CREATE INDEX idx_audit_log_event ON tb_audit_log(event_type, timestamp DESC);

MySQL

CREATE TABLE tb_audit_log (
    pk_audit_log BIGINT PRIMARY KEY AUTO_INCREMENT,
    id CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    level VARCHAR(20) NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    user_id CHAR(36),
    tenant_id CHAR(36),
    operation VARCHAR(255),
    resource VARCHAR(255),
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_id CHAR(36),
    duration_ms INT
);

CREATE INDEX idx_audit_log_timestamp ON tb_audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_user ON tb_audit_log(user_id, timestamp DESC);
CREATE INDEX idx_audit_log_event ON tb_audit_log(event_type, timestamp DESC);

SQLite

CREATE TABLE tb_audit_log (
    pk_audit_log INTEGER PRIMARY KEY AUTOINCREMENT,
    id TEXT NOT NULL UNIQUE,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    level TEXT NOT NULL,
    event_type TEXT NOT NULL,
    user_id TEXT,
    tenant_id TEXT,
    operation TEXT,
    resource TEXT,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT,
    request_id TEXT,
    duration_ms INTEGER
);

CREATE INDEX idx_audit_log_timestamp ON tb_audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_user ON tb_audit_log(user_id, timestamp DESC);
CREATE INDEX idx_audit_log_event ON tb_audit_log(event_type, timestamp DESC);

SQL Server

CREATE TABLE dbo.tb_audit_log (
    pk_audit_log BIGINT PRIMARY KEY IDENTITY(1,1),
    id UNIQUEIDENTIFIER NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    timestamp DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET() NOT NULL,
    level NVARCHAR(20) NOT NULL,
    event_type NVARCHAR(255) NOT NULL,
    user_id UNIQUEIDENTIFIER,
    tenant_id UNIQUEIDENTIFIER,
    operation NVARCHAR(255),
    resource NVARCHAR(255),
    details NVARCHAR(MAX),
    ip_address VARCHAR(45),
    user_agent NVARCHAR(MAX),
    request_id UNIQUEIDENTIFIER,
    duration_ms INT
);

CREATE INDEX idx_audit_log_timestamp ON dbo.tb_audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_user ON dbo.tb_audit_log(user_id, timestamp DESC);
CREATE INDEX idx_audit_log_event ON dbo.tb_audit_log(event_type, timestamp DESC);

Syslog / File / Multiple Backends

Caution

The [audit.syslog], [audit.file], and multi-backend configuration below are unverified TOML namespaces and will cause a parse error. The current confirmed configuration is [security.enterprise] only (see top of page).

To forward audit events to syslog or files, write a PostgreSQL trigger or use your log aggregator (Fluent Bit, Logstash) to tail the ta_audit_log table and forward entries.

Event Types

Authentication Events

Event	Description
auth.login	User login attempt
auth.login.success	Successful login
auth.login.failure	Failed login
auth.logout	User logout
auth.token.refresh	Token refreshed
auth.token.expired	Token expired
auth.session.created	Session created
auth.session.destroyed	Session ended

Authorization Events

Event	Description
authz.access.granted	Access allowed
authz.access.denied	Access denied
authz.role.required	Role check performed
authz.scope.required	Scope check performed

Operation Events

Event	Description
graphql.query	Query executed
graphql.mutation	Mutation executed
graphql.subscription	Subscription started
graphql.error	Operation error

Administrative Events

Event	Description
admin.schema.reload	Schema reloaded
admin.cache.clear	Cache cleared
admin.key.rotate	Encryption key rotated

Configuration Options

The confirmed TOML configuration for audit logging is:

[security.enterprise]
audit_logging_enabled = true
audit_log_level       = "info"   # "debug" | "info" | "warn"

Log level filtering, event-type filtering, and field redaction are handled in your PostgreSQL ta_audit_log table and views — for example, exclude sensitive fields from the audit view, or use PostgreSQL column-level permissions to protect PII fields.

Field Redaction

Protect sensitive data by not inserting it into the audit table in the first place. The ta_audit_log schema below has a details JSONB column — write your PostgreSQL audit trigger to omit or hash sensitive fields before inserting:

-- Hash email for correlation without storing PII
INSERT INTO ta_audit_log (event_type, user_id, details)
VALUES (
    'auth.login.success',
    p_user_id,
    jsonb_build_object(
        'email_hash', encode(sha256(p_email::bytea), 'hex')
        -- never store p_email directly
    )
);

Querying Audit Logs

PostgreSQL Queries

-- Recent failed logins
SELECT * FROM tb_audit_log
WHERE event_type = 'auth.login.failure'
  AND timestamp > NOW() - INTERVAL '1 hour'
ORDER BY timestamp DESC;

-- User activity
SELECT event_type, COUNT(*), MAX(timestamp)
FROM tb_audit_log
WHERE user_id = 'user-uuid-here'
  AND timestamp > NOW() - INTERVAL '7 days'
GROUP BY event_type;

-- Access denied events
SELECT * FROM tb_audit_log
WHERE event_type = 'authz.access.denied'
ORDER BY timestamp DESC
LIMIT 100;

-- Mutations by resource
SELECT
    resource,
    COUNT(*) as mutation_count,
    COUNT(DISTINCT user_id) as unique_users
FROM tb_audit_log
WHERE event_type = 'graphql.mutation'
  AND timestamp > NOW() - INTERVAL '24 hours'
GROUP BY resource
ORDER BY mutation_count DESC;

GraphQL Query

Expose audit logs via GraphQL (admin only):

@fraiseql.query(
    sql_source="v_audit_log",
    requires_role="admin"
)
def audit_logs(
    event_type: str | None = None,
    user_id: ID | None = None,
    since: DateTime | None = None,
    limit: int = 100
) -> list[AuditLog]:
    pass

Retention and Cleanup

Automatic Cleanup

Use a PostgreSQL cron job (via pg_cron) or an external scheduler:

-- Delete audit logs older than 365 days (run as a scheduled job)
DELETE FROM ta_audit_log
WHERE timestamp < NOW() - INTERVAL '365 days';

Manual Cleanup

-- Manual cleanup
DELETE FROM ta_audit_log
WHERE timestamp < NOW() - INTERVAL '365 days';

Archive Before Delete

Archive to S3 or cold storage using pg_dump or your data pipeline before deleting:

# Export audit logs older than 365 days to JSONB
psql $DATABASE_URL -c "COPY (
    SELECT row_to_json(ta_audit_log)
    FROM ta_audit_log
    WHERE timestamp < NOW() - INTERVAL '365 days'
) TO '/tmp/audit-archive.json'"

# Upload to S3, then delete from DB
aws s3 cp /tmp/audit-archive.json s3://your-bucket/audit/$(date +%Y%m%d).json
psql $DATABASE_URL -c "DELETE FROM ta_audit_log WHERE timestamp < NOW() - INTERVAL '365 days'"

Compliance

Enable audit logging via [security.enterprise], then implement compliance-specific controls at the PostgreSQL layer.

HIPAA

Log all PHI access events. FraiseQL writes every query and mutation to the audit table when audit_logging_enabled = true. Add retention enforcement via pg_cron:

-- Retain PHI access logs for 6 years (HIPAA minimum)
SELECT cron.schedule('hipaa-retention', '0 2 * * *', $$
    DELETE FROM ta_audit_log
    WHERE timestamp < NOW() - INTERVAL '6 years'
      AND event_type LIKE 'graphql.%'
$$);

Redact sensitive fields before inserting (see Field Redaction above).

SOC 2

Enable audit_logging_enabled = true. The audit table captures all access control decisions (authz.access.granted, authz.access.denied) and mutations automatically. No additional configuration required.

GDPR

For right-to-erasure support, implement a PostgreSQL function that anonymises the user_id column in audit rows when a deletion request is processed:

-- Anonymise audit log entries for deleted users
CREATE FUNCTION fn_anonymise_user_audit(p_user_id UUID) RETURNS VOID AS $$
BEGIN
    UPDATE ta_audit_log
    SET user_id = NULL,
        details = details - 'email' - 'ip_address'
    WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Metrics

Metric	Description
fraiseql_audit_events_total	Total audit events
fraiseql_audit_events_by_type	Events by type
fraiseql_audit_write_latency_ms	Write latency
fraiseql_audit_queue_size	Pending events
fraiseql_audit_errors_total	Write errors

Best Practices

Log Mutations, Sample Queries

Enable audit_logging_enabled = true. FraiseQL logs all mutations, auth events, and access denials. For high-volume query logging, insert a sampling condition in your PostgreSQL audit trigger or view.

Use Structured Details

# Good - structured, queryable
audit.log("graphql.mutation", details={
    "operation": "createUser",
    "input": {"email": "user@example.com"},
    "result": {"id": "user-123"}
})

# Bad - unstructured
audit.log("graphql.mutation", details="Created user user@example.com")

Monitor Audit System Health

# Alert on audit write failures
rate(fraiseql_audit_errors_total[5m]) > 0

# Alert on queue backup
fraiseql_audit_queue_size > 1000

Troubleshooting

Missing Audit Events

1. Check enabled = true
2. Verify log level isn’t filtering
3. Check filter rules
4. Review backend connectivity

High Latency

1. Increase batch_size
2. Increase flush_interval_ms
3. Check database/syslog performance
4. Consider async writes

Disk Space Issues

1. Enable retention cleanup
2. Reduce retention period
3. Archive to cheaper storage
4. Increase sampling for high-volume events

Next Steps

• Security - Authorization configuration
• Encryption - Protect audit data
• Deployment - Production audit setup