Audit chain and SIEM export

Every audit record is protected twice: the application database links records into a hash chain, and optional sinks copy each event to a system the database administrator does not control. The second part matters, because a chain kept only inside the database can be rewritten by someone who owns the database.

The hash chain

Each record stores prev_hash and hash, where hash = SHA-256(prev_hash || canonical_json) over the record’s fields (team, user, action, request, connection, SQL, metadata with sorted keys, IP and time). Records are linked inside one transaction that also takes a lock row, so the chain cannot fork. Unique indexes on both hashes refuse a second record with the same predecessor.

Database triggers refuse UPDATE and DELETE on the audit table, and TRUNCATE on PostgreSQL (MySQL and MariaDB triggers cannot block it).

Verify it

docker exec queryproxy php artisan audit:verify

The command walks the chain from the first record. When it is intact it prints the number of records and the chain head and exits with 0. When it is not, it prints the id of the first broken record and the reason, and exits with 1. It detects modified records, removed or inserted records, a reordered chain, a chain that does not start at the first record, and a chain that is shorter than the number of records written (a removed tail or an emptied table). queryproxy:doctor runs a quicker check of the newest records and the head; queryproxy:doctor --full-audit walks the whole chain.

Checkpoints

The scheduler runs audit:checkpoint daily. It writes an audit.checkpoint event with the last record id, its hash and the number of records, unless nothing was written since the previous one. A checkpoint is an ordinary audit event, so it also goes to your sinks.

What the chain cannot do

• It cannot catch a database owner who removes the triggers and recomputes the chain. Someone with that power can write a consistent history. Only a copy held outside the database, in your SIEM, shows the difference. That is what the checkpoints and the sinks below are for.
• MySQL and MariaDB: the connection time zone is pinned to UTC because created_at is hashed as UTC wall time. Do not change the server or session time zone behind QueryProxy’s back.
• Long outer transactions that call into the audit writer hold the lock row until they finish, so other writers wait.

Export to a SIEM

Every sink is off by default. QUERYPROXY_SIEM_SINKS turns them on: a comma-separated list of stdout, syslog and webhook, any combination.

Delivery runs on the queue after the audit record is committed, so a sink that is down never blocks or rolls back an audit write. Delivery is at least once: a receiver must deduplicate on the event’s id. A webhook or syslog delivery that keeps failing lands in failed_jobs, which acts as the dead-letter queue (php artisan queue:failed). If the queue backend itself is down, the event is not exported and the problem is logged; the database stays the system of record and its chain stays complete. The gap shows in the SIEM copy, as a missing id in the sequence.

A sink with a bad configuration is not used and the problem is logged once per process. queryproxy:doctor reports it as siem_sinks.

The event

All sinks send the same JSON document, described by docs/audit-event.schema.json (JSON Schema Draft 2020-12, additionalProperties: false, schema_version: 1).

Raw SQL is never exported. The schema’s action list is generated from the event catalog and has 50 events, grouped in the categories user, request, connection, masking_rule, team, auth, chat_integration, chat_identity, execution, audit, masking_key, error_messages and result. The actor.email is read when the event is delivered, so a user who changes their e-mail address between the audit write and the delivery appears with the new one.

stdout

One JSON object per line on the container’s standard output. The same stream also carries the output of nginx, php-fpm and the queue worker (RUNNING and DONE lines), so the collector must skip lines that are not JSON. Container logging drivers and agents that read the Docker log pick it up.

syslog

An RFC 5424 message with the JSON event as its body, over UDP, TCP or TLS. TCP and TLS use octet-counting framing (RFC 6587) unless you choose newline. With tls, QueryProxy verifies the receiver’s certificate and name; set QUERYPROXY_SIEM_SYSLOG_TLS_CA_FILE when the receiver’s CA is not in the system trust store. The standard TLS syslog port is 6514.

Signed webhook

QueryProxy POSTs the JSON event with these headers and does not follow redirects:

A webhook without a secret of at least 16 characters is never sent unsigned. Any HTTP status outside 2xx counts as a failure and is retried with a growing delay (10 s, 1 min, 5 min, 15 min, then 1 h). Verify a delivery before you trust it:

import hashlib
import hmac
import time

def is_valid(secret: str, timestamp: str, raw_body: bytes, signature: str) -> bool:
    # Reject stale deliveries; a retry carries a fresh timestamp.
    if abs(time.time() - int(timestamp)) > 300:
        return False
    message = timestamp.encode() + b"." + raw_body
    expected = "sha256=" + hmac.new(secret.encode(), message, hashlib.sha256).hexdigest()
    return hmac.compare_digest(expected, signature)

Sign over the raw body exactly as received, not over a re-serialised copy.

Product-specific connectors (Splunk, Elastic, Wazuh, Microsoft Sentinel) are not shipped. Any collector that accepts syslog, reads container logs or receives a signed HTTP POST can ingest these events.

Next steps

• Monitoring
• Threat model
• Configuration