SQLite Operations Layer

Day 13 covered why markdown breaks for state. Day 14 covers the replacement.

The database

data/tito.db. Three tables. Schema stays human-managed. The agent gets DML only.

sqlCREATE TABLE IF NOT EXISTS actions (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    title        TEXT NOT NULL,
    context      TEXT,
    skill        TEXT,
    priority     INTEGER DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
    status       TEXT DEFAULT 'open'
                 CHECK (status IN ('open','doing','done','dropped')),
    due_date     TEXT,
    source       TEXT DEFAULT 'manual',
    created_at   TEXT DEFAULT (datetime('now')),
    updated_at   TEXT DEFAULT (datetime('now')),
    completed_at TEXT,
    artifact_path TEXT
);

CREATE TABLE IF NOT EXISTS work_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    action_id    INTEGER REFERENCES actions(id),
    body         TEXT NOT NULL,
    session_date TEXT DEFAULT (date('now')),
    created_at   TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS alerts (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    check_name   TEXT NOT NULL,
    severity     TEXT NOT NULL DEFAULT 'info',
    message      TEXT NOT NULL,
    source       TEXT DEFAULT 'manual',
    fired_at     TEXT NOT NULL,
    acked_at     TEXT,
    resolved_at  TEXT
);

alerts is written by the Sentinel monitoring cron and auto-resolves when checks pass. work_log is a running record of what happened each session, optionally linked to an action. actions is the task queue.

The Actions class

tools/actions.py is a thin DML interface that works both as a library and a CLI.

pythonfrom actions import Actions
a = Actions()

a.add("Write day 14 article", skill="analyst", priority=2)
a.doing(14)
a.done(14, artifact_path="docs/series/day_14/")

open_items = a.list_actions()  # ordered by priority, due_date, created_at
a.log("Wrote article, committed", action_id=14)
a.summary()  # "1 doing, 4 open, 9 done"

CLI:

bashpython3 tools/actions.py list
python3 tools/actions.py add "Research Wylie prospects" --skill crm-operator --priority 2
python3 tools/actions.py done 5 --artifact-path docs/series/day_14/
python3 tools/actions.py logs

Boot query

sqlSELECT id, title, priority, skill, due_date
FROM actions
WHERE status IN ('open', 'doing')
ORDER BY priority ASC, due_date ASC NULLS LAST, created_at ASC;

DDL/DML boundary

The agent cannot modify the schema. If the table doesn't exist, the class raises:

pythontry:
    self.conn.execute("SELECT 1 FROM actions LIMIT 1")
except sqlite3.OperationalError:
    raise RuntimeError(
        "actions table does not exist. "
        "Run: sqlite3 data/tito.db < tools/actions_schema.sql"
    )

Schema changes still require the human to run the migration. WAL mode is enabled so interactive sessions and cron jobs can read concurrently without tripping over each other.

What goes where