# Audit chain and SIEM export

> How QueryProxy chains and verifies audit records, and how to send every audit event to stdout, syslog or a signed webhook for your SIEM.

- Documentation: Guides
- Updated: 2026-10-25
- Source: https://queryproxy.com/docs/audit-export/
- Language: en-US
- Author: Muhammet Şafak

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

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

| Variable | Default | Meaning |
| :-- | :-- | :-- |
| `QUERYPROXY_SIEM_SINKS` | none | Sinks to turn on: `stdout`, `syslog`, `webhook` |
| `QUERYPROXY_SIEM_QUEUE` | `default` | Queue the deliveries run on. The stock worker serves `default`; a different name needs a worker for it |
| `QUERYPROXY_SIEM_STDOUT_STREAM` | `php://stdout` | Stream the `stdout` sink writes to |
| `QUERYPROXY_SIEM_SYSLOG_HOST` | none | Syslog receiver host |
| `QUERYPROXY_SIEM_SYSLOG_PORT` | `514` (`6514` for `tls`) | Receiver port |
| `QUERYPROXY_SIEM_SYSLOG_TRANSPORT` | `udp` | `udp`, `tcp` or `tls` |
| `QUERYPROXY_SIEM_SYSLOG_FRAMING` | `octet-counting` | For `tcp` and `tls`: `octet-counting` or `newline` |
| `QUERYPROXY_SIEM_SYSLOG_FACILITY` | `13` | Syslog facility (13 is "log audit") |
| `QUERYPROXY_SIEM_SYSLOG_APP_NAME` | `queryproxy` | APP-NAME field |
| `QUERYPROXY_SIEM_SYSLOG_HOSTNAME` | none | HOSTNAME field |
| `QUERYPROXY_SIEM_SYSLOG_TLS_CA_FILE` | none | CA file for verifying a `tls` receiver; empty uses the system trust store |
| `QUERYPROXY_SIEM_SYSLOG_TIMEOUT` | `5` | Seconds |
| `QUERYPROXY_SIEM_WEBHOOK_URL` | none | `http://` or `https://` URL that receives each event |
| `QUERYPROXY_SIEM_WEBHOOK_SECRET` | none | Signing secret, at least 16 characters. `QUERYPROXY_SIEM_WEBHOOK_SECRET_FILE` reads it from a file |
| `QUERYPROXY_SIEM_WEBHOOK_TIMEOUT` | `5` | Seconds |
| `QUERYPROXY_SIEM_WEBHOOK_TRIES` | `6` | Delivery attempts before the job fails |

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`](https://github.com/QueryProxy/QueryProxy/blob/main/docs/audit-event.schema.json)
(JSON Schema Draft 2020-12, `additionalProperties: false`, `schema_version: 1`).

| Field | Meaning |
| :-- | :-- |
| `id` | Audit record id; unique per installation. Deduplicate on it |
| `timestamp` | UTC, second precision |
| `instance` | Host of the installation's `APP_URL`, or `null` when it is not set |
| `action` | Event name, for example `request.approved` |
| `category` | The part of `action` before the dot |
| `actor` | `{ "id", "email" }`, or `null` for system events |
| `team_id`, `connection_id`, `query_request_id` | Related objects, or `null` |
| `ip` | Client address, or `null` for console and queue events |
| `sql_redacted` | The SQL with literals replaced by placeholders, or `null` |
| `sql_hidden` | `true` when SQL exists but could not be redacted safely |
| `metadata` | Event-specific details, or `null` |

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:

| Header | Content |
| :-- | :-- |
| `X-QueryProxy-Timestamp` | Unix seconds when the delivery was made |
| `X-QueryProxy-Signature` | `sha256=` followed by the hex HMAC-SHA256 of `"{timestamp}.{body}"` with the secret |
| `X-QueryProxy-Event` | The `action` |
| `X-QueryProxy-Event-Id` | The audit record id |
| `User-Agent` | `QueryProxy-Audit/1` |

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:

```python
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](/docs/monitoring/)
- [Threat model](/docs/threat-model/)
- [Configuration](/docs/configuration/)