Monitoring — What to Watch

~12 min · monitoring, production

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The numbers that tell you something's wrong

SQLite is quiet — no daemon, no built-in metrics endpoint. You build observability from the application side. The signals worth tracking:

Database file size — sudden growth, plateau, or shrinkage all mean something.
WAL file size — large WAL means the checkpointer can't keep up; readers may be holding the write off.
Query latency — wrap the store class to time every query, send to your metrics backend.
SQLITE_BUSY count — if you ever see this, your busy_timeout is too short or your transactions are too long.
Integrity check pass/fail — scheduled, alert on fail.
Disk free — VACUUM and backups need space; running out is bad.

Self-reference: Pippa's /api/health endpoint reports SQLite size, JSONL size, last-write timestamp, and integrity-check status. The WebUI shows a small status dot — green/yellow/red — so Dad can see at a glance whether the data layer is healthy.

Code

Cheap health-check endpoint·python

import os, time
from fastapi import APIRouter, Request

router = APIRouter()

@router.get('/api/health/db')
async def db_health(request: Request):
    conn = request.app.state.store.conn
    db_path = 'myapp.db'
    started = time.perf_counter()
    n = (await (await conn.execute('SELECT count(*) AS n FROM messages')).fetchone())['n']
    elapsed_ms = (time.perf_counter() - started) * 1000
    return {
        'rows': n,
        'count_ms': round(elapsed_ms, 2),
        'db_size_bytes': os.path.getsize(db_path),
        'wal_size_bytes': (
            os.path.getsize(db_path + '-wal') if os.path.exists(db_path + '-wal') else 0
        ),
    }

External links

dbstat virtual table

Exercise

Add a health endpoint to one of your services that reports DB size, WAL size, row count of one key table, and the time it took. Hit it periodically (every minute) and graph the values for a few days. Note any pattern that surprises you and ask whether it indicates a real problem.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousProduction Migrations — Zero-Downtime Patterns Next →Litestream and Cloud-Backed SQLite

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.