Audit Logging

FreeSDN writes an immutable audit trail for every administrative action, authentication event, and resource mutation. Each entry carries full actor context (who, from where, using what credential), the resource that changed, a before/after diff, and an HMAC that chains the row to every row that came before it. This page explains what is recorded, how the chain works, how to query records, and what the limits of the system are.

---

What the audit log records

Audit log fields

Every audit entry maps to a row in audit.audit_logs. The table below lists the full database schema for that table. Only a subset of these fields is returned by the API - see the API response fields section below.

Audit log database schema

Field	Type	Description
id	UUID	Unique row identifier
timestamp	ISO 8601 UTC	When the action occurred
action	string	Verb (see action vocabulary below)
resource_type	string	Category of the affected object
resource_id	UUID / string	Identifier of the affected object
resource_name	string	Human-readable name at the time of the action
actor_id	UUID	User or system that performed the action
actor_type	string	user, system, or api_key
actor_name	string	Display name of the actor
actor_email	string	Email of the actor (stored in DB; not returned by the API)
organization_id	UUID	Organization that owns the record (DB only; scoping enforced server-side)
site_id	UUID	Site context, if applicable (DB only)
ip_address	string	Client IP (up to 45 chars - supports IPv6)
user_agent	string	User-Agent header value (DB only)
session_id	string	Session that was active when the action ran (DB only)
request_id	string	X-Request-ID correlation token (DB only)
request_method	string	HTTP verb (GET, POST, etc.) (DB only)
request_path	string	API path that was called (DB only)
status	string	success, failure, or error
response_code	integer	HTTP status code returned (DB only)
response_time_ms	float	Request duration in milliseconds (DB only)
error_message	string	Error detail when status != success (DB only)
changes	JSONB	Per-field diff: {field: {old, new}}
previous_state	JSONB	Full object state before the action (DB only)
new_state	JSONB	Full object state after the action (DB only)
tags	string[]	Free-form categorization tags (DB only)
extra_metadata	JSONB	Additional context passed by the action handler (DB only)

Chain columns are internal

prev_hash and row_hmac exist as columns in audit.audit_logs and are used by the chain validator, but they are not returned by any query endpoint (GET /api/v1/audit/logs, the resource/user variants, or the export endpoint). The GET /api/v1/audit/validate endpoint recomputes and checks them server-side and returns only valid, checked, unchained, broken_at, and broken_reason. Operators cannot retrieve raw HMAC values from the API.

API response fields

GET /api/v1/audit/logs (and the /logs/resource/… and /logs/user/… variants) serialise each entry through AuditLogResponse, which exposes only the following 12 fields:

Field	Type	Description
id	UUID	Unique row identifier
timestamp	ISO 8601 UTC	When the action occurred
action	string	Verb (see action vocabulary below)
resource_type	string	Category of the affected object
resource_id	UUID / string	Identifier of the affected object
resource_name	string	Human-readable name at the time of the action
actor_id	UUID	User or system that performed the action
actor_name	string	Display name of the actor
actor_type	string	user, system, or api_key
status	string	success, failure, or error
ip_address	string	Client IP (up to 45 chars - supports IPv6)
changes	JSONB	Per-field diff: {field: {old, new}}

All other database columns (including session_id, user_agent, request_id, request_method, request_path, response_code, response_time_ms, error_message, previous_state, new_state, tags, extra_metadata, prev_hash, row_hmac, organization_id, site_id, and actor_email) are stored in the database but are not currently included in the API response.

Change diffs

When a mutation succeeds, the changes field contains a map from field name to {old, new} values. For example, a role change on a user produces:

{
  "changes": {
    "role": { "old": "viewer", "new": "operator" }
  }
}

previous_state and new_state capture the full serialized object when the caller explicitly includes them; changes is the lightweight per-field view.

Caution

Sensitive fields are redacted before the entry is written. Any key matching password, secret, token, api_key, apikey, credential, private_key, access_key, secret_key, keys, tokens, secrets, or passwords (case-insensitive substring match) is replaced with [REDACTED] - including the entire value when the key maps to a list. This scrubbing runs on changes, previous_state, new_state, and extra_metadata.

Action vocabulary

The action field is one of the following string constants:

Category	Actions
CRUD	create, read, update, delete
Authentication	login, logout, login_failed, password_change, password_reset
MFA	mfa_enable, mfa_disable, mfa_verify, mfa_failed
Administrative	enable, disable, approve, reject, assign, unassign, invite
Device operations	adopt, provision, reboot, upgrade, locate
Backup / Restore	backup, restore
Data	export, import, sync
Plugins	install, uninstall

Resource type vocabulary

resource_type is one of: user, role, permission, organization, site, device, controller, network, client, alert, alert_rule, automation, integration, webhook, api_key, session, config, firmware, backup, settings, camera, nvr, recording, camera_event, camera_view, camera_group, plugin.

Use GET /api/v1/audit/actions and GET /api/v1/audit/resource-types to retrieve the live enumerations from a running instance.

---

Security events

Security events are stored separately in audit.security_events and exposed at /api/v1/audit/security (alias: /api/v1/audit/security-events). They record authentication and authorization outcomes with a numerical risk score.

Field	Description
event_type	One of the security event type constants below
user_id / user_email	The user the event concerns
ip_address	Source IP
success	Whether the operation succeeded
risk_score	0-100 integer; events ≥ 70 trigger a real-time security.alert event bus publish
details	Free-form JSONB context

Security event types: login_success, login_failed, logout, password_change, password_reset, password_reset_request, mfa_enabled, mfa_disabled, mfa_failed, api_key_created, api_key_revoked, api_key_used, account_locked, account_unlocked, suspicious_activity, permission_escalation, unauthorized_access, session_created, session_revoked, brute_force_attempt.

Risk score severity buckets (used by the UI and the severity filter parameter):

Label	risk_score range
critical	80-100
high	50-79
medium	20-49
low	0-19

Note

SecurityEventRecord has a severity column in the database (type string, default info), but the severity query parameter on GET /api/v1/audit/security is not filtered against that column - it is translated server-side to a risk_score range. You will not see a severity field in the API response payload; use risk_score instead.

---

HMAC tamper-evidence chain

How it works

Each new audit row is assigned a row_hmac before it is persisted. The HMAC covers two inputs:

1. The prev_hash of the most-recently-written audit row (or the empty string for the first row).
2. A canonical JSON serialization of all non-chain fields in the new row (sorted keys, compact separators, deterministic across runtimes).

The algorithm is HMAC-SHA256. The key bytes are always computed as SHA-256("freesdn.audit.v1::" + key_material) where key_material is AUDIT_HMAC_KEY if set, otherwise SECRET_KEY. The raw value of AUDIT_HMAC_KEY is never used directly as the HMAC key. New rows carry both prev_hash and row_hmac under normal conditions. If HMAC computation fails (for example, due to a missing key attribute or a transient DB error), row_hmac is set to NULL and the write still proceeds - the validator will report those rows as unchained, not broken. Rows written before the chain migration are similarly NULL in both columns; the validator makes no distinction between the two cases.

To prevent two concurrent writers from computing the same prev_hash and branching the chain, the insert acquires a SELECT ... FOR UPDATE lock on the latest existing row before writing.

Tamper-evidence, not tamper-prevention

The chain detects surgical edits to individual rows - the most common audit-log manipulation vector. It does not prevent a database administrator who holds AUDIT_HMAC_KEY and direct table access from deleting the entire table or re-computing a fresh chain end-to-end. For full tamper-prevention you should ship rows to an append-only external store (WORM object bucket, immudb, or similar) in addition to running the validation endpoint on a schedule.

Validating the chain

GET /api/v1/audit/validate

This endpoint walks the chain forward in (timestamp, id) ascending order and recomputes the expected HMAC for each row. It is restricted to super_admin.

Query parameters:

Parameter	Type	Default	Description
start_id	UUID	-	Begin the walk at this row (inclusive)
end_id	UUID	-	End the walk at this row (inclusive)
limit	integer	10000	Maximum rows to examine (1-100000)

Response:

{
  "valid": true,
  "checked": 4821,
  "unchained": 3,
  "broken_at": null,
  "broken_reason": null
}

Field	Meaning
valid	true only if no broken links were found among chained rows
checked	Total rows examined
unchained	Rows with NULL row_hmac (pre-migration rows); these do not count as broken
broken_at	UUID of the first row where the chain breaks; null when valid
broken_reason	Human-readable explanation - one of "prev_hash mismatch: expected '...' got '...'" (includes the actual hash values) or "row_hmac mismatch (row body modified)"

Run this endpoint on a schedule - weekly for most deployments, daily if you need tighter evidence for compliance workflows - and alert on any response where valid is false.

Example: validate the last 1000 rows only

curl -s -H "Authorization: Bearer $TOKEN" \
  "https://your-instance/api/v1/audit/validate?limit=1000"

Example: paginate through a large table

1. Run the initial call with the default limit.
2. Note broken_at or, if valid, record the last row ID from a preceding log query.
3. Pass that ID as start_id in the next call.

---

Querying audit logs

Endpoint reference

Method	Path	Purpose	Min role
GET	/api/v1/audit/logs	Paginated log list with filters	org_admin
GET	/api/v1/audit/logs/resource/{resource_type}/{resource_id}	Logs for a specific resource	org_admin
GET	/api/v1/audit/logs/user/{user_id}	Logs for a specific user	org_admin (or self)
GET	/api/v1/audit/security	Security events	org_admin
GET	/api/v1/audit/security-events	Security events (alias)	org_admin
GET	/api/v1/audit/summary/activity	Activity breakdown by action / resource / status	org_admin
GET	/api/v1/audit/summary/security	Security event counters	org_admin
POST	/api/v1/audit/export	Bulk export as JSON or CSV (capped at 10,000 rows)	org_admin
GET	/api/v1/audit/validate	Walk and verify the HMAC chain	super_admin
GET	/api/v1/audit/actions	Enumeration of valid action values	authenticated
GET	/api/v1/audit/resource-types	Enumeration of resource types	authenticated
GET	/api/v1/audit/security-event-types	Enumeration of security event types	authenticated

All read endpoints are scoped to the caller’s organization. An org_admin cannot read another org’s logs by supplying a foreign UUID - the query is filtered server-side by organization_id. A super_admin sees platform-wide records.

Users can read their own log history via GET /api/v1/audit/logs/user/{user_id} without needing org_admin. Cross-org user UUID probes return 404 (not 403) to avoid leaking UUID existence.

Filtering the log list

GET /api/v1/audit/logs accepts the following query parameters:

Parameter	Type	Description
start_date / end_date	ISO 8601 datetime	Time window
action	string	Single action (legacy, back-compat)
actions	repeated string	Multiple actions in one request (?actions=login&actions=logout)
resource_type	string	Single resource type (legacy)
resource_types	repeated string	Multiple resource types
resource_id	UUID	Specific resource
actor_id	UUID	Specific actor
site_id	UUID	Site filter
status	string	success, failure, or error
search	string	Case-insensitive substring across action, resource_type, resource_name, actor_name, actor_email, ip_address (max 128 chars)
page / per_page	integer	Pagination; per_page 1-200, default 50

Example: fetch all failed logins in the last 24 hours

START=$(date -u -d '24 hours ago' +%Y-%m-%dT%H:%M:%SZ)
NOW=$(date -u +%Y-%m-%dT%H:%M:%SZ)

curl -s -H "Authorization: Bearer $TOKEN" \
  "https://your-instance/api/v1/audit/logs?actions=login_failed&start_date=${START}&end_date=${NOW}&per_page=200"

Example: fetch all changes to a specific device

DEVICE_UUID="..."
curl -s -H "Authorization: Bearer $TOKEN" \
  "https://your-instance/api/v1/audit/logs/resource/device/${DEVICE_UUID}"

Exporting

POST /api/v1/audit/export streams a JSON or CSV file. Exports are capped at 10,000 rows. When the matching row count exceeds this limit:

• The response includes X-Result-Truncated: true, X-Result-Total, and X-Result-Limit headers.
• For JSON exports, the payload is wrapped in an envelope with truncated, total, limit, returned, and items keys so programmatic consumers can detect partial results without parsing headers.
• CSV exports retain their raw shape (no injected envelope rows) and rely on the headers only.

curl -s -X POST \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"start_date":"2026-01-01T00:00:00Z","end_date":"2026-06-01T00:00:00Z","format":"csv"}' \
  "https://your-instance/api/v1/audit/export" \
  -o audit.csv

Caution

CSV cells that begin with =, +, -, @, a tab, or a carriage return are prefixed with a single quote (') to prevent formula injection when files are opened in spreadsheet applications. This is transparent to CSV parsers but may appear as a leading apostrophe in certain viewers.

---

Retention

The default retention period is 90 days. The Celery scheduled task cleanup_audit_data (registered as security.cleanup_audit_data) deletes rows from both audit.audit_logs and audit.security_events where timestamp < now() - retention_days.

There is no per-organization retention override in the current release. If your compliance requirements mandate longer retention, configure a PostgreSQL dump or replicate rows to external storage before the cleanup task runs.

Caution

Entries older than the retention window are deleted permanently. The HMAC chain is not preserved across deletions - rows preceding the cleanup boundary are gone, not archived. For compliance workflows that require an unbroken multi-year chain, export to an external append-only store before the retention cutoff.

---

Write safety and audit write failures

Audit entries share the caller’s database session, so on success they commit together with the action that produced them. The audit write runs inside its own nested savepoint (SAVEPOINT), so if the audit write fails the savepoint is rolled back without affecting the outer transaction - the user-facing action is preserved. If the audit write itself fails (schema drift, transient DB error, constraint violation), the failure is:

• logged at ERROR level with CRITICAL: audit log write failed - audit trail incomplete
• incremented in the audit_write_failures_total Prometheus metric
• not propagated to the caller - the user-facing action already executed and the correct trade-off is to keep the action alive rather than roll it back because an audit row failed

Monitor audit_write_failures_total in your alerting stack. A non-zero counter means the audit trail has gaps.

---

Access control summary

Action	Required role
Read own audit-log history	any authenticated user
Read any user’s audit-log history	org_admin or higher
Read resource/entity audit-log history	org_admin or higher
Read security events	org_admin or higher
Export logs	org_admin or higher
Validate HMAC chain	super_admin only

No PostgreSQL row-level security

Multi-tenant isolation on the audit tables is enforced at the application layer - queries are filtered by organization_id in Python before they reach the database. PostgreSQL RLS is not used. A misconfigured query in application code could expose cross-tenant records. Cross-tenant isolation on these tables is covered by the automated test suite, but you should not rely on database-layer enforcement.

---

AUDIT_HMAC_KEY configuration

Set AUDIT_HMAC_KEY in your environment to a strong random string:

# Generate a suitable key
python -c "import secrets; print(secrets.token_hex(32))"

Add to your .env file:

AUDIT_HMAC_KEY=<64-char-hex>

If AUDIT_HMAC_KEY is absent, the system falls back to a domain-separated derivative of SECRET_KEY. The chain will work but any rotation of SECRET_KEY will invalidate all previously computed HMACs - the validator will report row_hmac mismatch for every row written under the old key. Set AUDIT_HMAC_KEY separately so you can rotate SECRET_KEY independently.

Danger

If both AUDIT_HMAC_KEY and SECRET_KEY are empty (which the production boot validation prevents), the chain uses a hard-coded fallback constant. Any attacker with DB access could recompute valid HMACs for tampered rows. The production startup check refuses to boot with an empty SECRET_KEY, so this state is not reachable in a correctly deployed instance.

---

Next steps

• Security Model - authentication, authorization, and write-safety architecture
• Roles and Permissions - role levels, default permission sets, and per-user site grants
• Multi-Tenancy - how org and site scoping is enforced at the application layer
• Hardening Checklist - production deployment security settings including AUDIT_HMAC_KEY