Analyze Agent Logs with fs9

AI agents produce structured logs — tool invocations, model calls, lifecycle events, errors. The fs9 extension lets you write those logs to DB9’s built-in filesystem and then query them with SQL, without loading them into a separate observability tool.

This guide walks through a practical flow: write agent logs, query them as structured data, and build useful aggregations.

Prerequisites

A DB9 database (see Quick Start)
The fs9 extension enabled

CREATE EXTENSION IF NOT EXISTS fs9;

How fs9 Works

fs9 exposes a per-database filesystem backed by TiKV. Files are isolated per tenant — each database has its own / root. You interact with it through:

Scalar functions like fs9_write(), fs9_read(), fs9_append() for file operations
A table function extensions.fs9() that reads files as SQL result sets with automatic format detection

The table function auto-detects .jsonl, .ndjson, .csv, .tsv, and .parquet by file extension, and falls back to plain text for anything else. See format detection for the row schema each one produces.

1. Write Agent Logs to the Filesystem

Agents typically emit logs as JSONL (one JSON object per line). Use fs9_write() or fs9_append() to store them.

Create the directory first — fs9_write() and fs9_append() do not create missing parent directories, and fail with fs9 resource was not found (58P01) if the parent does not exist. The second argument, true, creates parent directories as needed:

SELECT fs9_mkdir('/logs/agent', true);

Then write a batch of agent events as JSONL:

SELECT fs9_write('/logs/agent/2026-03-12.jsonl',
  '{"ts":"2026-03-12T10:00:01Z","event":"tool_call","tool":"sql","args":{"query":"SELECT count(*) FROM users"},"duration_ms":42}
{"ts":"2026-03-12T10:00:02Z","event":"tool_call","tool":"http_get","args":{"url":"https://api.example.com/status"},"duration_ms":310}
{"ts":"2026-03-12T10:00:03Z","event":"tool_call","tool":"fs_write","args":{"path":"/data/report.csv"},"duration_ms":15}
{"ts":"2026-03-12T10:00:05Z","event":"model_call","model":"claude-sonnet-4-20250514","tokens_in":1200,"tokens_out":350,"duration_ms":890}
{"ts":"2026-03-12T10:00:06Z","event":"tool_call","tool":"sql","args":{"query":"INSERT INTO reports VALUES (...)"},"duration_ms":28}
{"ts":"2026-03-12T10:00:08Z","event":"lifecycle","phase":"complete","status":"success","total_duration_ms":7200}
');

For streaming logs, use fs9_append() to add lines without overwriting:

SELECT fs9_append('/logs/agent/2026-03-12.jsonl',
  '{"ts":"2026-03-12T10:01:00Z","event":"tool_call","tool":"sql","args":{"query":"SELECT * FROM metrics"},"duration_ms":55}
');

2. Query Logs as Structured Data

The extensions.fs9() table function reads .jsonl files and returns each line as a JSONB row:

SELECT _line_number, line
FROM extensions.fs9('/logs/agent/2026-03-12.jsonl')
LIMIT 5;

Each row has:

Column	Type	Description
`_line_number`	INT64	1-based line index
`line`	JSONB	The parsed JSON object
`_path`	TEXT	Source file path (useful with globs)

Since line is JSONB, you can use standard PostgreSQL JSON operators to extract fields:

SELECT
  line->>'event' AS event_type,
  line->>'tool' AS tool,
  (line->>'duration_ms')::int AS duration_ms
FROM extensions.fs9('/logs/agent/2026-03-12.jsonl')
WHERE line->>'event' = 'tool_call';

3. Aggregate Tool Call Patterns

Count tool calls by tool name:

SELECT
  line->>'tool' AS tool,
  count(*) AS calls,
  round(avg((line->>'duration_ms')::numeric)) AS avg_ms,
  max((line->>'duration_ms')::int) AS max_ms
FROM extensions.fs9('/logs/agent/2026-03-12.jsonl')
WHERE line->>'event' = 'tool_call'
GROUP BY 1
ORDER BY calls DESC, tool;

Example output:

tool	calls	avg_ms	max_ms
sql	3	42	55
fs_write	1	15	15
http_get	1	310	310

4. Track Token Usage

Aggregate model calls to see token consumption:

SELECT
  line->>'model' AS model,
  count(*) AS calls,
  sum((line->>'tokens_in')::int) AS total_tokens_in,
  sum((line->>'tokens_out')::int) AS total_tokens_out,
  round(avg((line->>'duration_ms')::numeric)) AS avg_latency_ms
FROM extensions.fs9('/logs/agent/2026-03-12.jsonl')
WHERE line->>'event' = 'model_call'
GROUP BY 1;

5. Query Across Multiple Log Files

Use glob patterns to query all logs in a directory or across dates. All JSONL files in the agent logs directory:

SELECT
  _path,
  line->>'event' AS event,
  line->>'tool' AS tool
FROM extensions.fs9('/logs/agent/*.jsonl')
LIMIT 20;

A recursive search across all subdirectories:

SELECT
  _path,
  count(*) AS events
FROM extensions.fs9('/logs/**/*.jsonl')
GROUP BY 1
ORDER BY events DESC;

Glob limits: a single glob reads at most 10,000 files and 100 MB in total. Past either limit fs9 stops reading and returns what it already has, with no error or warning, so the query just covers fewer files. Compare count(DISTINCT _path) with the file count from a directory listing, and for larger log volumes query specific date ranges instead of using **.

6. Query CSV Logs

If your agent writes CSV logs, fs9 auto-detects the format and maps headers to column names. Write a CSV log:

SELECT fs9_write('/logs/requests.csv',
  'timestamp,method,path,status,duration_ms
2026-03-12T10:00:01Z,GET,/api/users,200,42
2026-03-12T10:00:02Z,POST,/api/tasks,201,85
2026-03-12T10:00:03Z,GET,/api/users/1,404,12
');

Then query it — the column names come from the header row:

SELECT method, path, status, duration_ms
FROM extensions.fs9('/logs/requests.csv')
WHERE status != '200';

7. File Management

Check what logs exist and how large they are. List the log files:

SELECT path, size, mtime
FROM extensions.fs9('/logs/agent/')
ORDER BY mtime DESC;

Check a specific file’s size, or read a partial range (here the first 1024 bytes):

SELECT fs9_size('/logs/agent/2026-03-12.jsonl');

SELECT fs9_read_at('/logs/agent/2026-03-12.jsonl', 0, 1024);

Delete old logs — this is the file created in step 1, so run it last — and delete a directory recursively:

SELECT fs9_remove('/logs/agent/2026-03-12.jsonl');

SELECT fs9_remove('/logs/old/', true);

A missing path is an error only when recursive is left at its default

The two calls above disagree about what a nonexistent path means, and the switch is the second argument rather than whether you named a file or a directory:

Call	Path does not exist
`fs9_remove(path)` — `recursive` defaults to `false`	`ERROR: fs9 resource was not found` (`58P01`)
`fs9_remove(path, true)`	returns `0`, no error
`fs9_remove('/logs/2026-01-*.jsonl')` — a glob matching nothing	returns `0`, no error

So a cleanup script that deletes yesterday’s file crashes the first time it runs twice, while the recursive line next to it quietly no-ops. Pass true when “already gone” is an acceptable outcome, and keep the default when a missing path should be loud.

Limits and Caveats

Superuser required — all fs9 operations require the database admin role.
Max file size — 100 MB, but enforced per call rather than on the stored file. A single fs9_write() over the limit is rejected with 54000; repeated fs9_append() calls are not checked and will carry a log file past it without error. Once a file is over, fs9_read() and the table function can no longer load it in one call, so split by date or category before you get there rather than waiting for a write to fail.
Max glob result — a single glob query reads at most 10,000 files and 100 MB in total; files past either limit are dropped silently, with no error.
Concurrent read budget — 128 MB across all concurrent fs9_read() calls globally.
UTF-8 only — there is no lossy fallback. fs9_read() on bytes that are not valid UTF-8 fails with 22021 and tells you to use fs9_read_bytea(); the table function raises nothing at all — it returns the complete lines before the first invalid byte and silently stops, which is zero rows only when the bad byte precedes any newline. Keep binary payloads out of the log files you intend to query, and read them with fs9_read_bytea() when you do store them.
No streaming — the table function loads the entire file into memory. For very large files, use fs9_read_at() to read in ranges.

fs9 Extension Reference — full function reference, format detection rules, and named parameters
RAG with Built-in Embeddings — vector search and retrieval pipelines
HTTP from SQL — call external APIs from SQL
Extensions Overview — all built-in extensions
CLI Reference — db9 db sql for running queries from the terminal