Genace
Sign inGet started

SQLite generation job store: persist a single-machine collector

A single-machine automation does not need to keep its entire generation history in memory. SQLite can hold a small application ledger containing operation identity, accepted job IDs, latest snapshots, and delivery progress. This is your client-side database; Genace’s own job service uses PostgreSQL and is not changed by this design.

Store recoverable milestones

Create a database on persistent local storage rather than a temporary container filesystem. Keep request intent separate from remote rendering and local delivery phases. A successful render whose file copy failed should remain a succeeded render with incomplete delivery.

import sqlite3

connection = sqlite3.connect("generation-ledger.sqlite", timeout=5)
connection.execute("""
CREATE TABLE IF NOT EXISTS operations (
  operation_id TEXT PRIMARY KEY,
  phase TEXT NOT NULL,
  remote_job_id TEXT,
  snapshot_json TEXT,
  delivery_state TEXT NOT NULL DEFAULT 'not_started'
)
""")
connection.commit()

def save_acceptance(operation_id: str, job_id: str):
    with connection:
        cursor = connection.execute("""
          UPDATE operations SET phase = 'accepted', remote_job_id = ?
          WHERE operation_id = ? AND phase = 'submitting'
        """, (job_id, operation_id))
        if cursor.rowcount != 1:
            raise RuntimeError("Operation could not accept this transition")

Create the operation row before calling this helper. The code illustrates a conditional milestone update and does not submit or observe a generation. Parameterized values avoid turning identifiers or prompt content into SQL syntax.

Keep network waits outside transactions

Use short transactions for state changes. Do not hold a SQLite write transaction open while a long poll waits for thirty seconds. SQLite supports concurrent readers but only one simultaneous writer, so unnecessary write locks can block unrelated local progress.

After a GET response arrives, parse and validate it, then update the saved snapshot in a short transaction. The remote snapshot uses id and can contain null outputs. Store the original state and error fields needed for recovery without copying Authorization headers or unrestricted sensitive logs into the ledger.

Resume from the ledger at startup

On restart, select accepted operations with known remote IDs and schedule observation. Select succeeded operations with incomplete delivery for download recovery. Do not submit again merely because the previous process ended before it printed a final URL.

An operation left submitting without an ID needs a separate uncertainty branch. SQLite durability cannot prove whether an external POST was accepted before the process stopped. A new remote generation remains an explicit decision after reconciliation because the creation route has no published idempotency contract.

Operate within the single-host scope

Choose a backup strategy and test restoration. Avoid treating a copied database file during active writes as automatically consistent; use SQLite-supported backup mechanisms for your chosen configuration. Restrict filesystem permissions because the ledger can contain prompts and asset references even if it contains no API key.

Verification checklist

Stop the process after saving acceptance and confirm startup resumes GET observation. Fail a snapshot update and verify no new POST occurs. Test a completed render with a missing download and confirm only delivery resumes. Finally, contend two local writers and verify lock failures produce bounded retries rather than silent lost state.

See durable acceptance, output storage, and PostgreSQL storage when the deployment grows beyond one machine.

Continue with production operations

Where these facts come from