Nate Swenson

Make the agent's only write a draft, and let the database enforce the rest

Local Fitness · No. 010 · 9 min read

Shipped

On 2026-06-15 I shipped goal-driven training plans end to end: a schema, an agent that drafts a periodized plan from Garmin history, a UI to riff on the draft and commit it, and a fold into the daily brief so the active plan shows up without asking. The feature went through a design pass and an adversarial red-team/siege review before any code existed. The same day, a separate pass on the AI loop shipped two real latency wins (compact tool JSON, a three-tier chat model toggle) and reverted a third after a live before/after measurement showed no win: brief generation ran a median 211 seconds tool-driven versus 255 seconds after pre-fetching the same data in-process, because the brief is bound by how long the model spends writing, not by the round-trips that gather the data for it.

The part worth teaching is the training-plan write path, because it was the first time this app let an agent write to its own database. The risky decision wasn’t the schema or the UI, it was deciding exactly what the agent is capable of doing once it has a write tool at all. Here’s how to build a write boundary an agent can’t talk its way around, whatever your agent happens to be writing.

Setup: a status column and an index that only watches one value

A plan (or any record with a lifecycle) starts as a draft, becomes active when a human approves it, and is archived when it’s replaced. Whatever you’re building, the moment you need “at most one X is active right now,” don’t write that rule into application code and hope every caller remembers it. Put it in the schema.

SQLite (and Postgres) support a partial index, an index built over only the rows matching a WHERE clause. “A partial index is over a subset of the rows of a table,” and when it’s also UNIQUE, uniqueness is enforced only within that subset (SQLite: Partial Indexes). That’s exactly the shape of “one active row, unlimited drafts and archives”:

# plan_boundary.py: save this schema string, plus the functions from the
# next two sections, in one file; the verification script at the end
# imports all four names from it.
SCHEMA = """
CREATE TABLE training_plans (
    plan_id     INTEGER PRIMARY KEY AUTOINCREMENT,
    status      TEXT NOT NULL,          -- 'draft' | 'active' | 'archived'
    goal_type   TEXT NOT NULL,
    race_date   TEXT NOT NULL
);

-- Only rows WHERE status='active' are indexed, so the UNIQUE constraint
-- only applies to that subset: many drafts and many archived rows are
-- fine, a second active row is rejected at write time.
CREATE UNIQUE INDEX idx_one_active_plan
    ON training_plans(status) WHERE status = 'active';
"""

A plain UNIQUE constraint on status would be wrong here: it would allow at most one draft and one archived row too, which isn’t the rule. The WHERE clause is what narrows the uniqueness down to just the value that actually matters.

Build: give the agent a tool that cannot do the dangerous thing

With the schema holding the invariant, the next question is what the agent’s write tool is allowed to say. OWASP’s guidance on prompt injection is specific here: “restrict the model’s access privileges to the minimum necessary for its intended operations,” and handle high-impact functions in code rather than handing them to the model outright (OWASP: LLM01 Prompt Injection). Concretely, that means the agent’s only capability is a function that can never produce anything but a draft, because status never appears as one of its parameters:

def insert_draft(conn, goal_type: str, race_date: str) -> int:
    """The only way a row is created. status is a literal in the SQL, never
    a parameter, so no caller-supplied value can override it."""
    cur = conn.execute(
        "INSERT INTO training_plans (status, goal_type, race_date) "
        "VALUES ('draft', ?, ?)",
        (goal_type, race_date),
    )
    return cur.lastrowid

# The tool schema handed to the agent has no "status" property at all:
PROPOSE_PLAN_SCHEMA = {
    "type": "object",
    "properties": {
        "goal_type": {"type": "string"},
        "race_date": {"type": "string", "description": "ISO YYYY-MM-DD"},
    },
    "required": ["goal_type", "race_date"],
}

This works whatever tool-calling framework you’re on, because the enforcement isn’t a runtime check, it’s an absence. There is no code path, anywhere, that takes a status value from outside and writes it. A defaulted parameter isn’t the same guarantee: a default can still be overridden by whoever’s calling the tool. Leaving the parameter out of the schema entirely is what makes it unreachable.

Build: whitelist what a draft can edit

The agent still needs to revise a draft while you riff on it in chat, and that’s a second place the same mistake can creep back in. If a “revise” function takes an arbitrary dict of fields and forwards it into an UPDATE, status is just another key that dict could contain. The fix is the same principle applied to editing instead of creating: give the function an explicit allowlist of what’s editable, not a blocklist of what isn’t (OWASP: Least Privilege Principle).

EDITABLE_COLS = frozenset({"goal_type", "race_date"})  # "status" is not here

def revise_draft(conn, plan_id: int, fields: dict) -> None:
    bad = set(fields) - EDITABLE_COLS
    if bad:
        raise ValueError(f"non-editable field(s): {sorted(bad)}")
    row = conn.execute(
        "SELECT status FROM training_plans WHERE plan_id=?", (plan_id,)
    ).fetchone()
    if row is None:
        raise LookupError(f"no plan {plan_id}")
    if row[0] != "draft":
        raise ValueError(f"plan {plan_id} is '{row[0]}', not draft")
    sets = ", ".join(f"{c}=?" for c in fields)
    conn.execute(
        f"UPDATE training_plans SET {sets} WHERE plan_id=?",
        (*fields.values(), plan_id),
    )

Rejecting non-whitelisted fields outright, rather than silently dropping them, matters too: silently dropping status would hide a bug (or an injection attempt) instead of surfacing it.

Use it: draft, revise, commit

Activating a plan is the one action the agent is never given at all, not draft-only, not whitelisted, just absent from anything the agent can call:

def commit_plan(conn, plan_id: int) -> None:
    """The only place status is ever set to 'active'. Called from the UI's
    commit button, never from the agent."""
    row = conn.execute(
        "SELECT status FROM training_plans WHERE plan_id=?", (plan_id,)
    ).fetchone()
    if row is None:
        raise LookupError(f"no plan {plan_id}")
    if row[0] != "draft":
        raise ValueError(f"plan {plan_id} is '{row[0]}', not draft")
    conn.execute("UPDATE training_plans SET status='archived' WHERE status='active'")
    conn.execute("UPDATE training_plans SET status='active' WHERE plan_id=?", (plan_id,))

Notice commit_plan still checks for an existing active row before switching, but that check and the write that follows it are two separate statements, and another request could land between them. That gap, checking a condition and then acting on it as if nothing changed in between, is a textbook time-of-check-to-time-of-use race (Wikipedia: TOCTOU). The application-level check is a nice error message for the common case; the partial index is what actually makes the race impossible, because the database rejects the second write no matter how it got there.

Verify the boundary

Save the two functions above plus the schema as plan_boundary.py, then run this against them:

import sqlite3
from plan_boundary import SCHEMA, insert_draft, revise_draft, commit_plan

conn = sqlite3.connect(":memory:")
conn.executescript(SCHEMA)

a = insert_draft(conn, "10k", "2026-09-14")
commit_plan(conn, a)

b = insert_draft(conn, "half", "2026-11-01")
commit_plan(conn, b)
print("plan", a, "is now", conn.execute(
    "SELECT status FROM training_plans WHERE plan_id=?", (a,)
).fetchone()[0])

try:
    revise_draft(conn, b, {"status": "active"})
except ValueError as e:
    print("revise_draft rejected status field:", e)

c = insert_draft(conn, "5k", "2026-08-01")
try:
    # Bypass the app layer entirely and try to force a second active row
    # straight through SQL, the way a bug or a rogue script might.
    conn.execute("UPDATE training_plans SET status='active' WHERE plan_id=?", (c,))
except sqlite3.IntegrityError as e:
    print("raw UPDATE blocked by the partial index:", e)

Running it prints:

plan 1 is now archived
revise_draft rejected status field: non-editable field(s): ['status']
raw UPDATE blocked by the partial index: UNIQUE constraint failed: training_plans.status

The third line is the one that matters most: it didn’t go through commit_plan at all, it’s a raw UPDATE, and the database still refused it. That’s the difference between a rule your code follows and a rule your data enforces.

Gotchas

A plain UNIQUE(status) looks right and isn’t. Without the WHERE clause, the constraint applies to every row, which means at most one draft and one archived plan total, not the “unlimited drafts, one active” rule you actually want. The WHERE status = 'active' clause is load-bearing, not decoration.

A default value on the tool parameter is not the same as omitting it. status: str = "draft" still lets a caller pass status="active"; only removing the parameter from the schema entirely removes the path. If your framework insists on always listing every field, at minimum validate that any status-like field is rejected outright rather than silently coerced.

An allowlist that’s easy to bypass is worse than none, because it looks safe in review. Build the whitelist as the literal set of columns a write function will touch, not as a list you promise to keep in sync with the schema; when the schema changes, the two sets drifting apart is exactly how a forgotten field becomes writable again.

The database check is what catches the case your code’s check doesn’t, and that’s worth actually testing, not just reasoning about. Write the regression test as a raw statement that skips your application function entirely, the way the verification script above does. If that test can’t fail, your “protection” is just documentation.

Sources

Changelog

  • docs: design + contract for training plans feature (9ec9834)
  • feat: add training_plans + plan_workouts schema with single-active index (58f96c6)
  • feat: training-plan persistence + draft-only agent tools (9d3037c)
  • feat: fold training plan into the brief + add plan-quality scorer (29188dd)
  • test+chore: plan regression tests, v0.2.0 release, devlog (ef623b1)
  • perf: Phase A AI-efficiency — compact tool JSON, 3-way chat tier, caching guard (8bf583b)
  • perf: Phase B — pre-fetch the brief’s data to collapse tool round-trips (0754a86)
  • revert: drop brief pre-fetch (#1) — failed its latency gate (d4fc389)
  • fix: brief now always surfaces an active plan (handles no-session-today) (0ef435f)
  • harden: suppress Server header + add HSTS (75f0783)