Query the raw log

The dashboard answers the questions we anticipated. For everything else, every statement that crossed the Gateway is one row in a columnar view called proxy_logs, and you can ask it anything in read-only SQL — no export, no parsing project, no warehouse credit spent.

This page is the workflow: getting a credential, what the executor accepts, the handful of things that reject a query or quietly answer the wrong question, and six worked questions. What each column means lives in the proxy_logs column reference — read it before your first query, because several columns do not mean what their name suggests.

The endpoint

POST /tenants/{tenant}/query

The body is a single field:

{ "sql": "SELECT * FROM proxy_logs LIMIT 10" }

The response is columns, rows, a count, and a truncation flag. Full shapes are in the query log reference.

{
  "tenantId":  "...",
  "columns":   [ { "name": "cacheStatus", "type": "VARCHAR" } ],
  "rows":      [ { "cacheStatus": "HIT", "executions": 411 } ],
  "rowCount":  2,
  "truncated": false
}

Failures come back as { "error": "..." } with a status that tells you which layer said no:

The tenant comes from the URL, and the storage location is resolved server-side from that tenant’s own configuration. Your SQL never names a bucket, a path or a tenant, and cannot reach another tenant’s data.

Scope a token first

This runs outside the App, so it needs a non-interactive credential. Mint a personal access token and scope it tightly — the log is the most sensitive thing the API will hand back, because it contains the SQL your people and your models actually wrote.

Scope, in plain language for this use case:

• Method: POST, for this one path.
• Path: /tenants/{tenant}/query.
• Tenant: the single tenant you are investigating.
• Account: the account that owns it.

The caller must be a member of the tenant regardless of scope, and reads against the raw log are audit-logged.

curl -X POST \
  -H "Authorization: Bearer $AIRBRX_PAT" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM proxy_logs LIMIT 1"}' \
  "https://api.airbrx.ai/tenants/your-slug/query"

That first call is worth making for its own sake: the columns array on the response is the authoritative list of what your tenant’s table holds, which is how you confirm a column exists before writing a query around it. DESCRIBE proxy_logs does not work.

What the executor allows

Queries run on a locked-down DuckDB service — read-only filesystem, no extension loading, configuration frozen. The dialect is DuckDB, so window functions, QUALIFY, GROUP BY ALL, FILTER and quantile_cont are all available. What is not:

• SELECT or WITH only. The statement must begin with SELECT, WITH, or a parenthesized SELECT. A leading DESCRIBE, SHOW, EXPLAIN or PIVOT comes back 400.
• One statement, and no trailing semicolon. ;-stacked SQL is rejected rather than silently truncated to the first statement. A single trailing ; is rejected too — the service wraps your SQL in a subquery to enforce the row cap, and a semicolon inside that will not parse.
• One table. proxy_logs, scoped to the tenant in the path. A parser allowlist rejects any other table or file, so there is no cross-tenant query.
• No file or extension functions. read_csv, read_parquet, delta_scan, glob, INSTALL, LOAD, ATTACH, COPY, PRAGMA, SET, getenv and the rest of the write and escape verbs are denied outright.
• 15 seconds, and 1,000 rows. Both are service defaults, and both are covered below.

The denied verbs bite on aliases. They are matched as whole words against the structure of the statement, so a column alias named after one is rejected even though nothing is being executed:

SELECT count(*) AS set   FROM proxy_logs   -- 400, rejected
SELECT count(*) AS load  FROM proxy_logs   -- 400, rejected
SELECT count(*) AS copy  FROM proxy_logs   -- 400, rejected

Pick another alias. String literals and comments are stripped before the check, so searching the logged SQL for one of those words is fine — WHERE standardizedSql LIKE '%COPY%' runs.

Always filter on day

day is the partition column, and the only thing that lets the engine skip files. It is a string of the form 'YYYY-MM-DD' — compare it as a string, not as a DATE:

WHERE day >= '2026-06-01' AND day < '2026-07-01'   -- prunes
WHERE timestamp > 1780272000000                     -- scans everything

Without a day predicate, a query reads the tenant’s entire history. The row cap below does not save you: it bounds what comes back, not what gets scanned, so even SELECT count(*) FROM proxy_logs reads everything and is the most common way to hit the 15-second timeout.

Results are capped

Every result set carries an enforced LIMIT of 1,000 rows. The response tells you whether you hit it:

"rowCount": 1000,
"truncated": true

truncated: true means you are looking at an arbitrary subset, not the top of a ranking — a fact worth being blunt about, because a truncated result still looks like an answer. The fix is almost never to page through raw rows; it is to make the database do the work. Aggregate, filter to a narrower window, or rank and take the top N, so the rows you get back are the rows you meant to see.

When you genuinely do need the rows, page with a boundary filter rather than OFFSET. Your own LIMIT sits inside the wrapper the service adds, so it cannot raise the cap — and an OFFSET walk re-reads every row it skips. Order deterministically and carry the last value forward instead:

SELECT timestamp, logId, userName, standardizedSql
FROM proxy_logs
WHERE day = '2026-06-14'
  AND timestamp < 1781740800000   -- oldest timestamp from the previous page
ORDER BY timestamp DESC
LIMIT 500

Worked questions

Every example carries a day predicate — copy that habit before you copy anything else. The column reference carries a shorter set covering the cache-status mix, repeated uncached statements and per-rule hit rate; these are the ones that take a little more assembling.

What did the cache actually save?

A HIT has no warehouse time of its own — the warehouse never saw it, so warehouseExecutionTimeMs is null. To price one, use what the same statement costs when it does reach the warehouse:

WITH priced AS (
  SELECT queryHash,
         avg(warehouseExecutionTimeMs) AS avg_wh_ms
  FROM proxy_logs
  WHERE day >= '2026-06-01'
    AND warehouseExecutionTimeMs IS NOT NULL
  GROUP BY queryHash
)
SELECT p.day,
       count(*) FILTER (WHERE p.cacheStatus = 'HIT') AS hits,
       round(sum(CASE WHEN p.cacheStatus = 'HIT' THEN c.avg_wh_ms ELSE 0 END) / 1000.0, 1)
         AS warehouse_seconds_avoided
FROM proxy_logs p
LEFT JOIN priced c USING (queryHash)
WHERE p.day >= '2026-06-01'
GROUP BY p.day
ORDER BY p.day

The shortcut — averaging warehouseExecutionTimeMs across all rows — gives you miss-only warehouse time wearing the label “average”, because the nulls it skips are exactly the population you were trying to measure. For tying a specific HIT to the cost it avoided, queryId is the join key into your warehouse’s own query history.

How much faster is a hit, really?

SELECT cacheStatus,
       count(*)                                   AS executions,
       round(quantile_cont(responseTimeMs, 0.50)) AS p50_ms,
       round(quantile_cont(responseTimeMs, 0.95)) AS p95_ms,
       round(quantile_cont(responseTimeMs, 0.99)) AS p99_ms
FROM proxy_logs
WHERE day >= '2026-06-01'
  AND responseTimeMs > 0
GROUP BY ALL
ORDER BY p95_ms

responseTimeMs > 0 is not optional. responseTimeMs reports “unmeasured” as 0 rather than null, so aggregates do not skip it on their own and the unmeasured rows drag every percentile down. This is the one number to quote at a data consumer: it is what they feel, end to end, through the Gateway.

Why is this traffic uncacheable?

cacheKey IS NULL selects the requests that never touched the cache. Sorting them by why separates “we chose not to” from “we could not”, which are very different backlogs:

SELECT CASE
         WHEN cacheOverride = 'nocache'    THEN 'explicit nocache directive'
         WHEN hasNonDeterministicFunctions THEN 'non-deterministic function'
         WHEN NOT isReadOnly               THEN 'not read-only'
         WHEN hasParameters                THEN 'parameterized'
         ELSE 'no rule matched'
       END                                  AS reason,
       count(*)                             AS executions,
       count(DISTINCT queryHash)            AS distinct_statements,
       round(avg(warehouseExecutionTimeMs)) AS avg_wh_ms
FROM proxy_logs
WHERE day >= '2026-06-01'
  AND cacheKey IS NULL
GROUP BY reason
ORDER BY executions DESC

“No rule matched” is addressable today with a cache rule. “Non-deterministic function” is a property of the statement, and has to be fixed where the statement is written.

Is a TTL too tight?

SELECT matchedRuleName,
       cacheTtlSeconds,
       count(*) FILTER (WHERE missReason = 'ttl-expired') AS expired_misses,
       count(*) FILTER (WHERE cacheStatus = 'HIT')        AS hits,
       round(avg(cacheAgeSeconds) FILTER (WHERE cacheStatus = 'HIT'))
         AS avg_age_at_hit_s
FROM proxy_logs
WHERE day >= '2026-06-01'
  AND matchedRuleName IS NOT NULL
GROUP BY ALL
ORDER BY expired_misses DESC

A large ttl-expired count against a short cacheTtlSeconds says entries are dying before anyone reuses them. avg_age_at_hit_s is the counter-evidence: if hits are landing well inside the TTL, the TTL is not what is costing you and the cache key probably is.

Which tables is the traffic hitting?

The hotspot list, for deciding where an invalidation rule earns its keep. tables is JSON text holding an array of objects — catalog, schema, table, fullyQualifiedName, operation — so the name has to be pulled out of each element by path:

WITH refs AS (
  SELECT unnest(json_extract(tables, '$[*]')) AS t,
         isDataChange
  FROM proxy_logs
  WHERE day >= '2026-06-01'
    AND tables IS NOT NULL
)
SELECT json_extract_string(t, '$.fullyQualifiedName') AS table_name,
       json_extract_string(t, '$.operation')          AS operation,
       count(*)                                       AS reference_count,
       count(*) FILTER (WHERE isDataChange)           AS writes
FROM refs
GROUP BY ALL
ORDER BY reference_count DESC
LIMIT 50

Two traps in that one. json_extract_string(tables, '$[*]') looks like it should give you table names and gives you JSON blobs instead, because the elements are objects rather than strings. And unnest cannot sit in the select list of an aggregating query — that is a Binder Error: UNNEST not supported here, which is why it is expanded in a CTE first. For single-table statements you can skip both:

SELECT json_extract_string(tables, '$[0].fullyQualifiedName') AS table_name,
       count(*) AS executions
FROM proxy_logs
WHERE day >= '2026-06-01'
  AND tableCount = 1
GROUP BY ALL
ORDER BY executions DESC
LIMIT 50

Is anyone failing to authenticate?

SELECT userTokenHash,
       clientIp,
       userAgent,
       count(*)                            AS failures,
       to_timestamp(min(timestamp) / 1000) AS first_seen,
       to_timestamp(max(timestamp) / 1000) AS last_seen
FROM proxy_logs
WHERE day >= '2026-06-01'
  AND cacheStatus = 'AUTH_ERROR'
GROUP BY ALL
HAVING count(*) > 3
ORDER BY failures DESC

One token hash failing repeatedly from a single address is usually a stale credential in a job someone forgot about. The same hash failing from many addresses is a different conversation. Swap AUTH_ERROR for BLOCKED to see what your deny rules refused, and who tried.

Note what this query does not do: filter to the four hit-rate statuses. cacheStatus IN ('HIT','MISS','BYPASS','PASSTHROUGH') is the correct filter for hit-rate arithmetic and a silent mistake anywhere else — it drops every BLOCKED, ERROR and AUTH_ERROR row, which is to say all of the security-relevant ones.

Before you trust a null

Every column is nullable, and a column that is null across the board usually means the rows predate it rather than that the Gateway never populates it — a tenant whose traffic started before a schema widening shows whole columns empty for the older days. Count the column against the row total per day before concluding anything:

SELECT day,
       count(*)                        AS rows_total,
       count(userAgent)                AS with_user_agent,
       count(warehouseExecutionTimeMs) AS with_warehouse_time
FROM proxy_logs
WHERE day >= '2026-06-01'
GROUP BY ALL
ORDER BY day

A column that fills in partway through the range was added; one that is empty throughout is genuinely not being written for your traffic. warehouseExecutionTimeMs is neither — it is null on every HIT by design, so its count tracks your miss volume rather than your coverage.

When a query comes back 400

And the ones that return a number, just the wrong one:

One more that is not a SQL error at all: everything in standardizedSql, parameterValues and the header columns is caller-supplied. If you are feeding results to an agent or into a report, treat them as data, never as instructions.

When to use something else

• A number for a dashboard. The summaries API already has it, pre-aggregated and cheap. Build custom reports covers wiring those into a BI tool.
• One statement, in detail. missReason is a column here, so a SELECT will tell you why a request missed. What it won’t show you is the cache key broken down into the inputs that produced it, which is what the App’s traffic page is for. Investigate a statement.
• The SQL with its literals still in it. This view carries standardizedSql, which has them removed. The original exists only in the raw objects.
• Everything, into your own pipeline. Read those same raw objects from your bucket rather than paging a capped result set.

Where to go next

• proxy_logs columns — all 51, with the units and null conventions.
• The three logs — when a summary would have been the cheaper answer.
• Query log reference — the record, the storage layout, and the request and response shapes.
• Scope a token — least-privilege scoping for a log-reading PAT.
• Rule cookbook — turning a repeat you found into a cache rule.