/docs/logging.md

(logging)=

Logging to SQLite

llm defaults to logging all prompts and responses to a SQLite database.

You can find the location of that database using the llm logs path command:

llm logs path

On my Mac that outputs:

/Users/simon/Library/Application Support/io.datasette.llm/logs.db

This will differ for other operating systems.

To avoid logging an individual prompt, pass --no-log or -n to the command:

llm 'Ten names for cheesecakes' -n

To turn logging by default off:

llm logs off

If you've turned off logging you can still log an individual prompt and response by adding --log:

llm 'Five ambitious names for a pet pterodactyl' --log

To turn logging by default back on again:

llm logs on

To see the status of the logs database, run this:

llm logs status

Example output:

Logging is ON for all prompts
Found log database at /Users/simon/Library/Application Support/io.datasette.llm/logs.db
Number of conversations logged: 33
Number of responses logged:     48
Database file size:             19.96MB

(logging-view)=

Viewing the logs

You can view the logs using the llm logs command:

llm logs

This will output the three most recent logged items in Markdown format, showing both the prompt and the response formatted using Markdown.

To get back just the most recent prompt response as plain text, add -r/--response:

llm logs -r

Use -x/--extract to extract and return the first fenced code block from the selected log entries:

llm logs --extract

Or --xl/--extract-last for the last fenced code block:

llm logs --extract-last

Add --json to get the log messages in JSON instead:

llm logs --json

Add -n 10 to see the ten most recent items:

llm logs -n 10

Or -n 0 to see everything that has ever been logged:

llm logs -n 0

You can truncate the display of the prompts and responses using the -t/--truncate option. This can help make the JSON output more readable - though the --short option is usually better.

llm logs -n 1 -t --json

Example output:

[
  {
    "id": "01jm8ec74wxsdatyn5pq1fp0s5",
    "model": "anthropic/claude-3-haiku-20240307",
    "prompt": "hi",
    "system": null,
    "prompt_json": null,
    "response": "Hello! How can I assist you today?",
    "conversation_id": "01jm8ec74taftdgj2t4zra9z0j",
    "duration_ms": 560,
    "datetime_utc": "2025-02-16T22:34:30.374882+00:00",
    "input_tokens": 8,
    "output_tokens": 12,
    "token_details": null,
    "conversation_name": "hi",
    "conversation_model": "anthropic/claude-3-haiku-20240307",
    "attachments": []
  }
]

(logging-short)=

-s/--short mode

Use -s/--short to see a shortened YAML log with truncated prompts and no responses:

llm logs -n 2 --short

Example output:

- model: deepseek-reasoner
  datetime: '2025-02-02T06:39:53'
  conversation: 01jk2pk05xq3d0vgk0202zrsg1
  prompt:  H01 There are five huts. H02 The Scotsman lives in the purple hut. H03 The Welshman owns the parrot. H04 Kombucha is...
- model: o3-mini
  datetime: '2025-02-02T19:03:05'
  conversation: 01jk40qkxetedzpf1zd8k9bgww
  system: Formatting re-enabled. Write a detailed README with extensive usage examples.
  prompt: <documents> <document index="1"> <source>./Cargo.toml</source> <document_content> [package] name = "py-limbo" version...

Include -u/--usage to include token usage information:

llm logs -n 1 --short --usage

Example output:

- model: o3-mini
  datetime: '2025-02-16T23:00:56'
  conversation: 01jm8fxxnef92n1663c6ays8xt
  system: Produce Python code that demonstrates every possible usage of yaml.dump
    with all of the arguments it can take, especi...
  prompt: <documents> <document index="1"> <source>./setup.py</source> <document_content>
    NAME = 'PyYAML' VERSION = '7.0.0.dev0...
  usage:
    input: 74793
    output: 3550
    details:
      completion_tokens_details:
        reasoning_tokens: 2240

(logging-conversation)=

Logs for a conversation

To view the logs for the most recent {ref}conversation <usage-conversation> you have had with a model, use -c:

llm logs -c

To see logs for a specific conversation based on its ID, use --cid ID or --conversation ID:

llm logs --cid 01h82n0q9crqtnzmf13gkyxawg

(logging-search)=

Searching the logs

You can search the logs for a search term in the prompt or the response columns.

llm logs -q 'cheesecake'

The most relevant results will be shown first.

To switch to sorting with most recent first, add -l/--latest. This can be combined with -n to limit the number of results shown:

llm logs -q 'cheesecake' -l -n 3

(logging-filter-id)=

Filtering past a specific ID

If you want to retrieve all of the logs that were recorded since a specific response ID you can do so using these options:

• --id-gt $ID - every record with an ID greater than $ID
• --id-gte $ID - every record with an ID greater than or equal to $ID

IDs are always issued in ascending order by time, so this provides a useful way to see everything that has happened since a particular record.

This can be particularly useful when {ref}working with schema data <schemas-logs>, where you might want to access every record that you have created using a specific --schema but exclude records you have previously processed.

(logging-filter-model)=

Filtering by model

You can filter to logs just for a specific model (or model alias) using -m/--model:

llm logs -m chatgpt

(logging-filter-fragments)=

Filtering by prompts that used specific fragments

The -f/--fragment X option will filter for just responses that were created using the specified {ref}fragment <usage-fragments> hash or alias or URL or filename.

Fragments are displayed in the logs as their hash ID. Add -e/--expand to display fragments as their full content - this option works for both the default Markdown and the --json mode:

llm logs -f https://llm.datasette.io/robots.txt --expand

You can display just the content for a specific fragment hash ID (or alias) using the llm fragments show command:

llm fragments show 993fd38d898d2b59fd2d16c811da5bdac658faa34f0f4d411edde7c17ebb0680

If you provide multiple fragments you will get back responses that used all of those fragments.

(logging-filter-tools)=

Filtering by prompts that used specific tools

You can filter for responses that used tools from specific fragments with the --tool/-T option:

llm logs -T simple_eval

This will match responses that involved a result from that tool. If the tool was not executed it will not be included in the filtered responses.

Pass --tool/-T multiple times for responses that used all of the specified tools.

Use the llm logs --tools flag to see all responses that involved at least one tool result, including from --functions:

llm logs --tools

(logging-filter-schemas)=

Browsing data collected using schemas

The --schema X option can be used to view responses that used the specified schema, using any of the {ref}ways to specify a schema <schemas-specify>:

llm logs --schema 'name, age int, bio'

This can be combined with --data and --data-array and --data-key to extract just the returned JSON data - consult the {ref}schemas documentation <schemas-logs> for details.

(logging-datasette)=

Browsing logs using Datasette

You can also use Datasette to browse your logs like this:

datasette "$(llm logs path)"

(logging-backup)=

Backing up your database

You can backup your logs to another file using the llm logs backup command:

llm logs backup /tmp/backup.db

This uses SQLite VACUUM INTO under the hood.

(logging-message-store)=

Structured message storage

Responses are logged as a lean turns row plus a set of content-addressed tables that capture the full structured form of the conversation — every part of every message, including reasoning parts and provider metadata such as encrypted reasoning signatures. Logged responses can be re-inflated from the database with no loss of detail, using the {ref}llm.message_store Python API <python-api-message-store>.

The message content lives in two tables:

• messages stores one row per unique message. Its id is a SHA-256 hash of the message content, so the same message is stored exactly once no matter how many conversations include it. The parts column contains the JSON list of parts; attachments are referenced by their id in the attachments table rather than embedded.
• message_nodes stores chain nodes. A node is a (parent node, message) pair and its id is a hash of both, which means a node id uniquely identifies an entire message chain — that message plus everything before it. Conversations that share a prefix share nodes: re-logging a conversation that grew by one turn only inserts the new tail. Chains that diverge after a common prefix form a tree. This means clients that re-send the full conversation history with every request — an OpenAI-compatible API endpoint, for example — can be logged without storing any message more than once.

Each turns row records the node heads for its input chain (input_node_id, exactly what was sent to the model) and its output chain (output_node_id, the input plus the messages the model produced), alongside the per-occurrence facts: when the response was generated, token usage, options, the model used, and small single-turn text columns (prompt, system, response, reasoning) that power full-text search and display. The response_json column preserves the raw provider response payload, which can carry data (such as logprobs) not captured anywhere else.

A handful of small link and index tables complete the picture: turn_fragments (fragment provenance for -f filtering), turn_tools (which tool definitions were available), turn_attachments (this turn's prompt attachments), tool_uses (one row per tool result, powering llm logs -T — the payloads live in the message parts) and toolbox_instances (configured Toolbox arguments).

Content hashes are calculated as follows. Canonical JSON means json.dumps(value, sort_keys=True, separators=(",", ":"), ensure_ascii=False) encoded as UTF-8. A message is hashed as its Message.to_dict() dictionary with attachments replaced by references — an attachment part becomes {"type": "attachment", "attachment_id": ...} and a tool result part's attachments list becomes attachment_ids, where the attachment id is the existing SHA-256-based Attachment.id(). Then:

message id = sha256(canonical JSON of the stored message dictionary)
node id = sha256("{parent node id}:{message id}")

with the empty string in place of the parent node id for the first message in a chain. Hex digests are lowercase. Anything writing to these tables must hash identically or deduplication will break — treat this scheme as part of the schema.

(logging-upgrading)=

Logs from older versions of LLM

Earlier versions of LLM logged to a different set of tables: responses, tool_calls, tool_results, prompt_attachments, prompt_fragments, system_fragments, tool_responses and tool_instances. If your database contains rows in those tables they are left exactly as they were — LLM no longer writes to them, and llm logs no longer reads from them, but nothing ever modifies or deletes them.

To carry that history forward into the current format, install the llm-upgrade-logs plugin and run llm upgrade-logs. The port is additive and repeatable: it reads the old tables, writes equivalent rows into the current tables, and preserves the original response and conversation IDs so llm logs --cid and llm -c work seamlessly across the upgrade boundary. llm logs status will remind you if a database contains old-format rows.

The shared catalog tables — conversations, schemas, attachments, fragments, fragment_aliases and tools — are used by both formats and continue to work unchanged.

(logging-sql-schema)=

SQL schema

Here's the SQL schema used by the logs.db database:

CREATE TABLE [conversations] (
  [id] TEXT PRIMARY KEY,
  [name] TEXT,
  [model] TEXT
);
CREATE TABLE [schemas] (
  [id] TEXT PRIMARY KEY,
  [content] TEXT
);
CREATE TABLE [attachments] (
  [id] TEXT PRIMARY KEY,
  [type] TEXT,
  [path] TEXT,
  [url] TEXT,
  [content] BLOB
);
CREATE TABLE [fragments] (
  [id] INTEGER PRIMARY KEY,
  [hash] TEXT,
  [content] TEXT,
  [datetime_utc] TEXT,
  [source] TEXT
);
CREATE TABLE [fragment_aliases] (
  [alias] TEXT PRIMARY KEY,
  [fragment_id] INTEGER REFERENCES [fragments]([id])
);
CREATE TABLE [tools] (
  [id] INTEGER PRIMARY KEY,
  [hash] TEXT,
  [name] TEXT,
  [description] TEXT,
  [input_schema] TEXT,
  [plugin] TEXT
);
CREATE TABLE [messages] (
  [id] TEXT PRIMARY KEY,
  [role] TEXT,
  [parts] TEXT,
  [provider_metadata] TEXT,
  [first_seen_utc] TEXT
);
CREATE TABLE [message_nodes] (
  [id] TEXT PRIMARY KEY,
  [parent_id] TEXT REFERENCES [message_nodes]([id]),
  [message_id] TEXT REFERENCES [messages]([id]),
  [depth] INTEGER,
  [first_seen_utc] TEXT
);
CREATE TABLE [turns] (
  [id] TEXT PRIMARY KEY,
  [model] TEXT,
  [resolved_model] TEXT,
  [prompt] TEXT,
  [system] TEXT,
  [options_json] TEXT,
  [response] TEXT,
  [reasoning] TEXT,
  [response_json] TEXT,
  [conversation_id] TEXT REFERENCES [conversations]([id]),
  [duration_ms] INTEGER,
  [datetime_utc] TEXT,
  [input_tokens] INTEGER,
  [output_tokens] INTEGER,
  [token_details] TEXT,
  [schema_id] TEXT REFERENCES [schemas]([id]),
  [input_node_id] TEXT REFERENCES [message_nodes]([id]),
  [output_node_id] TEXT REFERENCES [message_nodes]([id])
);
CREATE VIRTUAL TABLE [turns_fts] USING FTS5 (
  [prompt],
  [response],
  content=[turns]
);
CREATE TABLE [turn_fragments] (
  [turn_id] TEXT REFERENCES [turns]([id]),
  [fragment_id] INTEGER REFERENCES [fragments]([id]),
  [fragment_type] TEXT,
  [order] INTEGER,
  PRIMARY KEY ([turn_id],
  [fragment_id],
  [fragment_type],
  [order])
);
CREATE TABLE [turn_tools] (
  [turn_id] TEXT REFERENCES [turns]([id]),
  [tool_id] INTEGER REFERENCES [tools]([id]),
  PRIMARY KEY ([turn_id],
  [tool_id])
);
CREATE TABLE [turn_attachments] (
  [turn_id] TEXT REFERENCES [turns]([id]),
  [attachment_id] TEXT REFERENCES [attachments]([id]),
  [order] INTEGER,
  PRIMARY KEY ([turn_id],
  [attachment_id])
);
CREATE TABLE [tool_uses] (
  [id] INTEGER PRIMARY KEY,
  [turn_id] TEXT REFERENCES [turns]([id]),
  [tool_id] INTEGER REFERENCES [tools]([id]),
  [name] TEXT,
  [tool_call_id] TEXT,
  [instance_id] INTEGER REFERENCES [toolbox_instances]([id])
);
CREATE TABLE [toolbox_instances] (
  [id] INTEGER PRIMARY KEY,
  [plugin] TEXT,
  [name] TEXT,
  [arguments] TEXT
);

responses_fts configures SQLite full-text search against the prompt and response columns in the responses table.