How to Share One SQLite File Between a Python Worker and a Next.js Dashboard

An agent that waits for a human is not a request/response program. It produces something, then stops, and the thing it produced has to survive the process exiting, the container restarting, and the reviewer going to bed. The moment you put a person in the loop, the interesting part of your agent stops being the model call and becomes the state store.

A pattern that works well for this on a single host: a scheduled Python worker that writes drafts, a Next.js dashboard where a human approves or rejects them, and one SQLite file as the entire contract between them. No queue broker, no API between your own services, no second database to operate. Docker is the only dependency on the host.

Here is how to make that hold up, and where it breaks.

The file is the interface

Resist the urge to have the dashboard call the worker over HTTP. The worker's job is to write rows. The dashboard's job is to read rows and change a status column. If both speak only to the database, each can be restarted, rebuilt, or rewritten in another language without touching the other.

A minimal schema for human-in-the-loop work:

CREATE TABLE drafts (
 id INTEGER PRIMARY KEY,
 product_slug TEXT NOT NULL,
 channel TEXT NOT NULL,
 run_key TEXT NOT NULL,
 title TEXT NOT NULL,
 body TEXT NOT NULL,
 status TEXT NOT NULL DEFAULT 'pending',
 reject_reason TEXT,
 created_at TEXT NOT NULL,
 published_at TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS draft_run_unique
 ON drafts (product_slug, channel, run_key);

status carries the workflow: pending, approved, scheduled, rejected, published. The reviewer's whole job is moving rows between those values, and every tab in the UI is one filtered query.

The unique index is there because I decided the database, not the job, should be the thing that refuses a duplicate. A scheduled job that retries after a timeout will happily generate the same draft again. Make the job compute a deterministic run_key (the product, the channel, and the period it is drafting for) and let the database refuse the duplicate instead of writing dedupe logic in two places.

Open the connection correctly

SQLite's defaults are tuned for a single embedded process. You are running a worker and a web app against the same file, so change them:

import os
import sqlite3

BUSY_TIMEOUT_MS = int(os.environ["SQLITE_BUSY_TIMEOUT_MS"])

def connect(path: str) -> sqlite3.Connection:
 conn = sqlite3.connect(path, isolation_level=None)
 conn.execute("PRAGMA journal_mode = WAL")
 conn.execute("PRAGMA synchronous = NORMAL")
 conn.execute("PRAGMA foreign_keys = ON")
 conn.execute(f"PRAGMA busy_timeout = {BUSY_TIMEOUT_MS}")
 conn.row_factory = sqlite3.Row
 return conn

WAL mode is what makes concurrent access tolerable: readers do not block the writer and the writer does not block readers. It is stored in the database file itself, so setting it once sticks across processes. busy_timeout is per connection, so both services must set it. Pick a value longer than your longest write transaction; the point is that a blocked writer waits for the lock instead of raising immediately.

isolation_level=None turns off Python's implicit transaction handling so you control transactions yourself. That matters for the next part.

One writer, short transactions, explicit locks

SQLite allows one writer at a time. That is fine, because your writes are small. What is not fine is holding the write lock while you wait on the network.

The ordering rule: do the slow work first, outside any transaction, then open a transaction only to write the result.

draft = generate_draft(product, channel) # model call, seconds of latency
findings = validate(draft) # pure functions, fast

conn.execute("BEGIN IMMEDIATE")
try:
 conn.execute(
 "INSERT OR IGNORE INTO drafts "
 "(product_slug, channel, run_key, title, body, status, created_at) "
 "VALUES (?, ?, ?, ?, ?, 'pending', ?)",
 (product.slug, channel, run_key, draft.title, draft.body, now_iso()),
 )
 conn.execute("COMMIT")
except Exception:
 conn.execute("ROLLBACK")
 raise

BEGIN IMMEDIATE takes the write lock up front. The alternative, a deferred transaction that starts as a reader and tries to upgrade to a writer mid-flight, can fail with SQLITE_BUSY after you have already done work inside it, and busy_timeout does not save you from that case. If a transaction is going to write, say so when it begins.

State transitions should be conditional rather than absolute, so a retry or a double click cannot move a row twice:

conn.execute("BEGIN IMMEDIATE")
cur = conn.execute(
 "UPDATE drafts SET status = 'published', published_at = ? "
 "WHERE id = ? AND status = 'scheduled'",
 (now_iso(), draft_id),
)
conn.execute("COMMIT")

if cur.rowcount == 0:
 return # already handled, or a human pulled it back

That rowcount == 0 branch is your idempotency guarantee for anything that touches the outside world. Check it before you call a publishing API, not after.

Let exactly one side own the schema

With an ORM on the Next.js side, the tempting move is to let each service manage its own migrations against the same file. Do not. Pick one side as the schema owner, run its migrations at startup before the other service touches the database, and have the other side treat the tables as a fixed interface it reads and writes but never alters.

In Compose that means ordering and a shared volume:

services:
 dashboard:
 build: ./dashboard
 volumes: [data:/data]
 environment:
 DATABASE_URL: "file:/data/app.db"

 agent:
 build: ./agent
 depends_on: [dashboard]
 volumes: [data:/data]
 environment:
 DATABASE_PATH: "/data/app.db"
 SQLITE_BUSY_TIMEOUT_MS: "5000"

volumes:
 data:

Note the file lives on a named volume, not inside a container's writable layer. Rebuild an image with the database inside it and you throw away every row the reviewer ever approved.

Backups

Do not copy the .db file while the services are running. In WAL mode there are -wal and -shm files next to it holding committed data that has not been checkpointed, and a plain cp of the main file alone can give you a torn snapshot. Use the online backup API, which takes a consistent copy of a live database:

sqlite3 /data/app.db ".backup '/backups/app-$(date +%F).db'"

Python's sqlite3 exposes the same thing as source.backup(dest) if you would rather run it from the worker on a schedule.

Where this breaks

This design is for one host. It is worth being direct about what that rules out.

• More than one machine. The file is local. SQLite on NFS or any network filesystem is where locking goes wrong quietly, and you will find out during a write.

• Many concurrent writers. One writer at a time is plenty for a worker drafting on a schedule and a human clicking approve. It is not a fan-in buffer for high-frequency event ingestion.

• Long-running write transactions. Anything slow inside a write lock stalls the other service. If you cannot keep writes short, you want a server database.

• Multi-tenant or hosted products. The moment you need per-customer isolation, replicas, or a connection pool across instances, move to Postgres. The schema above ports over without drama, which is a good reason to keep your queries boring.

If your agent is a scheduled worker plus a review UI that one team runs on one box, none of those limits bind, and you get something genuinely nice out of it: the whole application state is a single file you can copy, inspect with the sqlite3 CLI, and open in any language.

Content Agent Pro is the packaged version of this setup for promotion drafting, a Python agent and a Next.js review dashboard on one SQLite file, which I built, maintain and run myself: https://fulcrumenterprises.tech/go/content-agent-kit-pro/?c=hashnode&v=635ef5