How to Log Shell Commands to SQLite in Python for Audit Trails

Mar 14, 2026

It was 2am. The deployment script had failed. I was staring at a terminal full of output, trying to figure out which command had actually broken things.

I had log files scattered everywhere—deploy.log, build.log, error.log—but none of them told me the full story. What commands ran? In what order? What did they actually output?

That’s when I decided: every shell command my scripts run should be logged to a database I can query.

The Problem with Traditional Logging

I used to do this:

import subprocess
import logging

logging.basicConfig(filename='deploy.log', level=logging.INFO)

def run_command(cmd):
    logging.info(f"Running: {cmd}")
    result = subprocess.run(cmd, capture_output=True, text=True)
    logging.info(f"Exit code: {result.returncode}")
    return result

This works fine until something goes wrong at 2am. Then you’re grepping through log files, trying to piece together what happened:

$ grep "Error" deploy.log
$ grep "failed" deploy.log
$ grep -A5 "npm run build" deploy.log

The problem? Log files are linear. They’re hard to query. And if you want to know “what failed between 2am and 3am?”, you’re doing date math in your head while stressed.

A Better Approach: SQLite as Command History

What if every command was a row in a database?

┌─────────────────────────────────────────────────────────────┐
│                      command_log                            │
├──────────────┬─────────────┬───────────┬─────────┬─────────┤
│ timestamp    │ command     │ exit_code │ stdout  │ stderr  │
├──────────────┼─────────────┼───────────┼─────────┼─────────┤
│ 2026-03-14   │ npm build   │ 0         │ Done... │         │
│ 02:15:00     │             │           │         │         │
├──────────────┼─────────────┼───────────┼─────────┼─────────┤
│ 2026-03-14   │ git push    │ 1         │         │ auth... │
│ 02:17:30     │             │           │         │         │
└──────────────┴─────────────┴───────────┴─────────┴─────────┘

Query: SELECT * FROM command_log WHERE exit_code != 0;
Result: Instant list of everything that failed.

Now I can ask real questions:

“What failed in the last hour?”
“What commands touched the production directory?”
“Show me all git operations from yesterday”

Building the Command Logger

I started with a simple wrapper class:

import subprocess
import sqlite3
from datetime import datetime
from pathlib import Path

class CommandLogger:
    def __init__(self, db_path: str = "commands.db"):
        self.db_path = db_path
        self._init_db()

    def _init_db(self):
        conn = sqlite3.connect(self.db_path)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS command_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                command TEXT NOT NULL,
                exit_code INTEGER,
                stdout TEXT,
                stderr TEXT,
                working_dir TEXT
            )
        """)
        conn.commit()
        conn.close()

    def run(self, cmd: list, cwd: str = None) -> subprocess.CompletedProcess:
        start_time = datetime.now().isoformat()

        result = subprocess.run(
            cmd,
            capture_output=True,
            text=True,
            cwd=cwd
        )

        conn = sqlite3.connect(self.db_path)
        conn.execute("""
            INSERT INTO command_log
            (timestamp, command, exit_code, stdout, stderr, working_dir)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (
            start_time,
            " ".join(cmd),
            result.returncode,
            result.stdout,
            result.stderr,
            cwd or str(Path.cwd())
        ))
        conn.commit()
        conn.close()

        return result

Usage is straightforward:

logger = CommandLogger("/var/log/audit/commands.db")

# Instead of subprocess.run(["npm", "run", "build"])
result = logger.run(["npm", "run", "build"])

if result.returncode != 0:
    print(f"Build failed: {result.stderr}")

Querying the Audit Trail

The real power comes when things break. Here’s a helper I use:

import sqlite3

def get_failed_commands(db_path: str, hours: int = 24):
    """Get all failed commands in the last N hours."""
    conn = sqlite3.connect(db_path)
    cursor = conn.execute("""
        SELECT timestamp, command, stderr, working_dir
        FROM command_log
        WHERE exit_code != 0
        AND timestamp >= datetime('now', ?)
        ORDER BY timestamp DESC
    """, (f'-{hours} hours',))

    for row in cursor.fetchall():
        print(f"[{row[0]}] {row[1]}")
        print(f"  Directory: {row[3]}")
        print(f"  Error: {row[2][:200] if row[2] else 'No stderr'}")
        print()

    conn.close()

# At 2am, I can now run:
# get_failed_commands("/var/log/audit/commands.db", hours=1)

The Monkey-Patch Approach

I have legacy scripts that call subprocess.run directly. Rewriting them would take forever. Instead, I use a monkey-patch:

import subprocess
import sqlite3
from datetime import datetime

# Save the original
_original_run = subprocess.run
_db_path = "audit.db"

# Initialize database
def _init_db():
    conn = sqlite3.connect(_db_path)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS command_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            command TEXT NOT NULL,
            exit_code INTEGER,
            stdout TEXT,
            stderr TEXT
        )
    """)
    conn.commit()
    conn.close()

_init_db()

def logged_run(*args, **kwargs):
    # Ensure we capture output
    if 'capture_output' not in kwargs:
        kwargs['capture_output'] = True
    if 'text' not in kwargs:
        kwargs['text'] = True

    result = _original_run(*args, **kwargs)

    # Log to SQLite
    conn = sqlite3.connect(_db_path)
    conn.execute("""
        INSERT INTO command_log (timestamp, command, exit_code, stdout, stderr)
        VALUES (?, ?, ?, ?, ?)
    """, (
        datetime.now().isoformat(),
        str(args[0]) if args else str(kwargs.get('args', '')),
        result.returncode,
        result.stdout,
        result.stderr
    ))
    conn.commit()
    conn.close()

    return result

# Replace globally
subprocess.run = logged_run

Now I just add one import at the top of my existing scripts:

import audit_patch  # Must be first!
import subprocess

# All subprocess.run calls are now logged automatically
subprocess.run(["npm", "run", "build"])
subprocess.run(["git", "push", "origin", "main"])

Mistakes I Made

1. Not Capturing stderr Separately

Initially, I combined stdout and stderr. Big mistake. When a command fails, the error is usually in stderr. Mixing them makes debugging harder.

2. Forgetting Working Directory

Commands behave differently depending on where they run. I added working_dir to the schema after debugging why the same command worked in one script but failed in another.

3. Storing Commands Without Parameters

I first stored just the command name ("npm"), not the full command line ("npm run build --production"). Useless for debugging.

4. No Log Rotation

After a few months, my database was 500MB. Now I have a cron job that archives old entries:

import sqlite3
from datetime import datetime, timedelta

def rotate_old_logs(db_path: str, days: int = 30):
    """Archive logs older than N days."""
    conn = sqlite3.connect(db_path)

    # Count what we'll delete
    cursor = conn.execute("""
        SELECT COUNT(*) FROM command_log
        WHERE timestamp < datetime('now', ?)
    """, (f'-{days} days',))
    count = cursor.fetchone()[0]

    # Delete old entries
    conn.execute("""
        DELETE FROM command_log
        WHERE timestamp < datetime('now', ?)
    """, (f'-{days} days',))

    conn.commit()
    conn.close()

    print(f"Archived {count} old command logs")

Why This Matters

Beyond debugging at 2am, this approach has helped with:

Compliance: Some industries require audit trails of all system changes
Learning: Seeing exactly what my scripts executed vs what I thought they did
Incident Response: “What changed between 2pm and 3pm yesterday?”

The database becomes a source of truth. Log files can be deleted, rotated, or corrupted. SQLite is durable and queryable.

When Not to Use This

This isn’t for everything:

High-volume commands: If you’re running thousands of commands per minute, SQLite will bottleneck
Sensitive data: Commands with passwords or API keys will be logged in plain text
Short-lived scripts: For quick one-off scripts, this is overkill

For those cases, stick with regular logging or structured logging to files.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 Python subprocess documentation
👨‍💻 SQLite in Python tutorial
👨‍💻 Reddit discussion on useful Python scripts

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!