Recording and Querying CLI Run History in SQLite

"What did that deploy on Tuesday actually do?" "When did the sync last succeed?" "Which of yesterday's runs failed, and why?" Shell history answers none of these — it records what was typed, not what happened, and it is per-terminal and easily lost. A CLI that changes things — deploys, syncs, migrations, bulk edits — benefits enormously from its own run history: one row per invocation with the command, the outcome, the duration and a short summary written by the command itself. Users get mytool history and mytool last; support gets a way to ask "send me the output of mytool history --failed --since 7d"; and you get a record that survives terminal sessions and reboots. This guide builds it on SQLite, with privacy-conscious recording, flexible queries, export and retention. It belongs to the SQLite state topic.

Prerequisites

• Python 3.10+, Typer, platformdirs; the SQLite practices from storing CLI state in SQLite.
• An entry-point function you control, as in best practices for Python CLI entry points.

What to record — and what not to

A history row should let someone reconstruct what happened without becoming a second copy of everything the user typed. That rules out storing the raw command line: arguments routinely contain tokens, passwords passed by careless scripts, customer names and file paths the user may not want kept.

Record the command path (deploy, db migrate), the start time in UTC, the duration, the exit code, the tool version, and a summary that the command writes deliberately — "deployed api 2.3.1 to production (4 hosts)". The summary is the key design choice: the command knows which details are meaningful and safe, so it writes them explicitly, and nothing else is captured implicitly.

The recipe

# src/mytool/history.py
from __future__ import annotations

import os
import re
import sqlite3
import time
from contextvars import ContextVar
from datetime import datetime, timedelta, timezone
from pathlib import Path

from platformdirs import user_state_path

SCHEMA = [
    """CREATE TABLE runs (
           id INTEGER PRIMARY KEY,
           command TEXT NOT NULL,
           started_at TEXT NOT NULL,
           duration_ms INTEGER,
           exit_code INTEGER,
           version TEXT NOT NULL,
           summary TEXT
       )""",
    "CREATE INDEX runs_started ON runs (started_at)",
]
_current: ContextVar[int | None] = ContextVar("current_run", default=None)


def db_path() -> Path:
    return Path(os.environ.get("MYTOOL_HISTORY_DB") or user_state_path("mytool") / "history.db")


def connect(path: Path | None = None) -> sqlite3.Connection:
    path = path or db_path()
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path, timeout=5.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode = WAL")
    version = conn.execute("PRAGMA user_version").fetchone()[0]
    for number, sql in enumerate(SCHEMA[version:], start=version + 1):
        conn.execute("BEGIN IMMEDIATE")
        try:
            conn.execute(sql)
            conn.execute(f"PRAGMA user_version = {number}")
            conn.execute("COMMIT")
        except BaseException:
            conn.execute("ROLLBACK")
            raise
    return conn


def start(conn: sqlite3.Connection, command: str, version: str) -> int:
    with conn:
        cur = conn.execute(
            "INSERT INTO runs (command, started_at, version) VALUES (?, ?, ?)",
            (command, datetime.now(timezone.utc).isoformat(timespec="seconds"), version),
        )
    _current.set(cur.lastrowid)
    return cur.lastrowid


def note(conn: sqlite3.Connection, summary: str) -> None:
    """Called by commands to describe what they did."""
    run_id = _current.get()
    if run_id is not None:
        with conn:
            conn.execute("UPDATE runs SET summary = ? WHERE id = ?", (summary[:500], run_id))


def finish(conn: sqlite3.Connection, run_id: int, exit_code: int, started: float) -> None:
    with conn:
        conn.execute("UPDATE runs SET exit_code = ?, duration_ms = ? WHERE id = ?",
                     (exit_code, int((time.monotonic() - started) * 1000), run_id))


_SPAN = re.compile(r"^(\d+)([mhdw])$")
_UNITS = {"m": "minutes", "h": "hours", "d": "days", "w": "weeks"}


def parse_since(text: str, *, now: datetime | None = None) -> datetime:
    """'30m', '12h', '7d', '2w' or an ISO date -> an aware datetime."""
    now = now or datetime.now(timezone.utc)
    if m := _SPAN.match(text.strip()):
        return now - timedelta(**{_UNITS[m.group(2)]: int(m.group(1))})
    value = datetime.fromisoformat(text)
    return value if value.tzinfo else value.replace(tzinfo=timezone.utc)


def query(conn: sqlite3.Connection, *, since: datetime | None = None, failed: bool = False,
          command: str | None = None, limit: int = 20) -> list[dict]:
    sql = ["SELECT * FROM runs WHERE 1=1"]
    params: list = []
    if since:
        sql.append("AND started_at >= ?")
        params.append(since.isoformat(timespec="seconds"))
    if failed:
        sql.append("AND (exit_code IS NULL OR exit_code != 0)")
    if command:
        sql.append("AND command = ?")
        params.append(command)
    sql.append("ORDER BY id DESC LIMIT ?")
    params.append(limit)
    return [dict(r) for r in conn.execute(" ".join(sql), params)]


def prune(conn: sqlite3.Connection, *, keep_days: int = 90) -> int:
    cutoff = datetime.now(timezone.utc) - timedelta(days=keep_days)
    with conn:
        return conn.execute("DELETE FROM runs WHERE started_at < ?",
                            (cutoff.isoformat(timespec="seconds"),)).rowcount

Storing timestamps as ISO 8601 strings in UTC with a fixed precision means they sort and compare correctly as text, so started_at >= ? works with the index and needs no date functions. Filters are built from fixed SQL fragments with ? parameters, never by formatting user input into the query. A row whose exit_code is still NULL was interrupted — killed, or the machine lost power — and the --failed filter counts it as a failure, which is what a user investigating problems wants to see.

Recording every run

The entry point wraps the whole application, so every run is recorded, including usage errors and crashes:

# src/mytool/main.py
import sys
import time
from importlib.metadata import version

from mytool import history
from mytool.cli import app


def main() -> None:
    conn = history.connect()
    command = next((a for a in sys.argv[1:] if not a.startswith("-")), "(none)")
    started = time.monotonic()
    run_id = history.start(conn, command, version("mytool"))
    code = 1
    try:
        app()
    except SystemExit as exc:
        code = exc.code if isinstance(exc.code, int) else (0 if exc.code is None else 1)
        raise
    except KeyboardInterrupt:
        code = 130
        raise
    finally:
        history.finish(conn, run_id, code, started)
        history.prune(conn)
        conn.close()

Typer and Click always end with SystemExit in their default mode, carrying the exit code — 0 for success, 2 for usage errors, whatever a command passed to typer.Exit. An unexpected exception skips that branch and is recorded as 1. Recording only the first non-option word as the command keeps arguments out of the database; commands that matter add a summary with history.note().

The history commands

# src/mytool/cli.py
import json
from typing import Optional

import typer

from mytool import history

app = typer.Typer()


@app.callback()
def main() -> None:
    """My tool."""


@app.command()
def deploy(service: str, env: str = "staging") -> None:
    """Deploy a service."""
    conn = history.connect()
    history.note(conn, f"deployed {service} to {env}")
    typer.echo(f"deployed {service} to {env}")


@app.command("history")
def show_history(
    since: Optional[str] = typer.Option(None, help="e.g. 30m, 12h, 7d, 2w or 2026-09-01"),
    failed: bool = typer.Option(False, "--failed", help="Only failed or interrupted runs."),
    command: Optional[str] = typer.Option(None, help="Only runs of this command."),
    limit: int = typer.Option(20, min=1),
    as_json: bool = typer.Option(False, "--json", help="JSON Lines output."),
) -> None:
    """Show previous runs, newest first."""
    try:
        when = history.parse_since(since) if since else None
    except ValueError:
        raise typer.BadParameter(f"cannot understand {since!r}", param_hint="--since")
    rows = history.query(history.connect(), since=when, failed=failed,
                         command=command, limit=limit)
    for r in rows:
        if as_json:
            typer.echo(json.dumps(r))
        else:
            status = "ok" if r["exit_code"] == 0 else f"exit {r['exit_code'] or '?'}"
            typer.echo(f"{r['started_at']}  {r['command']:<10} {status:<8} {r['summary'] or ''}")

--json emits JSON Lines so the history composes with jq and with the export patterns from the output formats topic.

UX considerations

• Summaries are the product. A history of bare command names is barely useful. Make note() part of every command that changes something, and write summaries a colleague could read.
• Never store secrets. Recording only the command name plus deliberate summaries is the simplest way to guarantee it; if you must store arguments, run them through the same redaction as redacting secrets from CLI output and logs.
• Retention by default. Ninety days, configurable, pruned automatically. Unbounded history is a slow leak of disk and of data users may not want kept.
• Make it easy to turn off. Some users and environments must not keep records at all; honour MYTOOL_NO_HISTORY=1 by skipping start() and finish().
• Human time formats in the table, ISO in JSON. The table can say "2 hours ago"; JSON keeps the precise timestamp.
• History must never break a command. Wrap recording in try/except sqlite3.Error in production and continue without it, logging a warning at debug level.

Testing the behaviour

Point the database at tmp_path with the environment variable and drive the real entry point:

# tests/test_history.py
import json
import sys
from datetime import datetime, timezone

import pytest

from mytool import history
from mytool.main import main


@pytest.fixture(autouse=True)
def isolated_db(tmp_path, monkeypatch):
    monkeypatch.setenv("MYTOOL_HISTORY_DB", str(tmp_path / "h.db"))
    monkeypatch.setattr("mytool.main.version", lambda name: "1.0.0")


def run(monkeypatch, *args):
    monkeypatch.setattr(sys, "argv", ["mytool", *args])
    with pytest.raises(SystemExit) as exc:
        main()
    return exc.value.code


def test_success_and_summary_are_recorded(monkeypatch):
    assert run(monkeypatch, "deploy", "api", "--env", "prod") == 0
    [row] = history.query(history.connect())
    assert (row["command"], row["exit_code"]) == ("deploy", 0)
    assert row["summary"] == "deployed api to prod"
    assert "prod" not in row["command"]                  # arguments are not stored


def test_usage_errors_are_failures(monkeypatch):
    assert run(monkeypatch, "deploy") == 2
    [row] = history.query(history.connect(), failed=True)
    assert row["exit_code"] == 2


def test_history_json_output(monkeypatch, capsys):
    run(monkeypatch, "deploy", "web")
    capsys.readouterr()
    run(monkeypatch, "history", "--json", "--command", "deploy")
    line = capsys.readouterr().out.splitlines()[0]
    assert json.loads(line)["summary"] == "deployed web to staging"


@pytest.mark.parametrize("text,hours", [("30m", 0.5), ("12h", 12), ("7d", 168), ("2w", 336)])
def test_parse_since_spans(text, hours):
    now = datetime(2026, 10, 2, tzinfo=timezone.utc)
    assert (now - history.parse_since(text, now=now)).total_seconds() == hours * 3600


def test_bad_since_is_a_usage_error(monkeypatch):
    assert run(monkeypatch, "history", "--since", "last tuesday") == 2

Running the real main() means each test exercises the wrapper, the exit-code capture and the database together — the places where a history feature usually breaks.

Conclusion

A run history turns a CLI's past into something users can query: which commands ran, when, how they ended and what they did. Record a row from the entry point for every run, let commands add deliberate summaries instead of storing arguments, store UTC ISO timestamps so queries are simple, offer --since, --failed and --json, and prune automatically. The result is a small feature that answers big questions.

Frequently asked questions

Related

• Up: Local state and SQLite in Python CLIs
• Storing CLI state in SQLite
• Migrating a CLI's SQLite schema
• Choosing exit codes for CLI tools
• Adding trace IDs and context to CLI logs