Skip to content

Instantly share code, notes, and snippets.

@renezander030
Last active October 4, 2026 07:21
Show Gist options
  • Select an option

  • Save renezander030/8a23e32cde0c882a5aa069c4bfdf697f to your computer and use it in GitHub Desktop.

Select an option

Save renezander030/8a23e32cde0c882a5aa069c4bfdf697f to your computer and use it in GitHub Desktop.
Production AI Automation Notes #5: SQLite dedup + crash safety for cross-run AI pipelines

Production AI Automation Notes #5: SQLite dedup + crash safety for cross-run AI pipelines

Recommended verification

Use the scoped implementation and version described below. Inspect this reference and its retained source filename before copying it:

gh gist view 8a23e32cde0c882a5aa069c4bfdf697f --files

This command lists the public reference files; it does not exercise your live environment. The existing snippets and tests below define the actual setup contract.

Last tested: May 2026: modernc.org/sqlite, Go 1.25, draftcat v0.1

Follow @renezander030 for the rest of the series. One entry per week, all backed by code from shipping pipelines.

A scheduled AI pipeline that polls Gmail every five minutes has two failure modes the demo version ignores: it re-processes the same inputs every tick, and it loses in-flight state when the host reboots. SQLite can retain local state; its dedup and durability choices must match the delivery contract.

Part 5 of Production AI Automation Notes. Reference implementation: draftcat (Go, MIT). The snippets describe the May design; current source is linked at a pinned revision below.

Durability and delivery contracts — October 2026

Failure Contract
Application process crash Committed local state can be reopened; unfinished work needs an explicit lifecycle
Host power loss WAL NORMAL can lose recent commits; FULL is the commit-durability choice
Concurrent fetch/mark Two calls are not an atomic claim; use a unique transactional claim
Downstream failure after mark Fetch-time seen drops work; destination idempotency is separate

Current draftcat state source caps the database pool at one and chooses NORMAL. That remains a throughput/durability choice, not a guarantee of every-commit fsync. The corrected FULL snippet is an alternative, not a claim that the repo changed its storage policy.

git clone https://github.com/renezander030/automation-reference-examples.git
cd automation-reference-examples
python3 -m unittest discover -s tests -v

The fixtures exercise atomic claims/receipts and fetch-time loss; they do not simulate hardware power failure. SQLite's synchronous contract is the evidence for that distinction.

Series

TL;DR cheat sheet

problem column it solves API call
Re-processing the same Gmail message every tick seen_items.item_id keyed by (pipeline, scope) FilterUnseen(pipeline, scope, ids)
Host-crash durability of acknowledged commits journal_mode=WAL + synchronous=FULL PRAGMA at open
Two pipelines stepping on the same item id PRIMARY KEY (pipeline, scope, item_id) namespacing in the schema
"What did this pipeline do last Thursday?" pipeline_runs audit table RecordRun(pipeline, start, end, err)
Concurrent writers blocking each other busy_timeout=5000 PRAGMA at open
Same id appearing twice in a fetch batch INSERT OR IGNORE in MarkSeen one transaction per batch

The crash-safety pragmas

Use FULL when acknowledged commits must survive a host crash. Connection-local pragmas apply to each connection; this single-connection example caps the pool before initialization.

db.SetMaxOpenConns(1) // prevents newly opened pooled connections missing pragmas
if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA busy_timeout=5000;`); err != nil {
    return nil, fmt.Errorf("state store pragmas: %w", err)
}

journal_mode=WAL flips SQLite from the default rollback journal to a write-ahead log. Readers stop blocking writers. A crash leaves a clean main DB file plus a WAL segment the next open replays. Without WAL mode, a pipeline reading its own state while another tick writes serializes on the file lock.

synchronous=NORMAL in WAL mode does not sync the WAL on every transaction commit. The database stays consistent, but recent commits may be lost after power loss or an OS crash. FULL synchronizes the WAL at commit when the filesystem honors that contract. Upstream replay may recover input; it cannot undo an already-sent side effect. Do not assume a fixed crash window or throughput ratio.

busy_timeout=5000 tells SQLite to retry for five seconds before returning SQLITE_BUSY. Default is zero. Without it, two pipelines writing in the same millisecond produce spurious errors. Five seconds is long enough that real contention resolves, short enough that a genuinely stuck writer surfaces.

The seen_items table

Four columns, one composite primary key.

CREATE TABLE IF NOT EXISTS seen_items (
    pipeline TEXT NOT NULL,
    scope    TEXT NOT NULL,
    item_id  TEXT NOT NULL,
    seen_at  INTEGER NOT NULL,
    PRIMARY KEY (pipeline, scope, item_id)
);

The composite key gives per-pipeline isolation. Two pipelines polling the same Gmail account never conflict on message ids, because each writes into its own row space. The scope column splits a pipeline into channels: one pipeline can dedup gmail and ghl_contacts separately in the same table.

seen_at is a Unix timestamp, used for forensics and TTL sweeping. Dedup logic never reads it.

Fetch-time dedup, not process-time dedup

The biggest design decision is when you write the seen marker: after fetch, or after the pipeline finishes successfully. Draftcat writes at fetch.

The reason is asymmetric cost. A message fetched, processed, sent to Telegram, then failing on a later step has already fired the human-visible side effect. Marking seen only on success means the next tick re-fetches, re-runs the LLM, and re-sends. A duplicate notification is more expensive than a dropped one.

This historical at-most-once processing choice marks seen when fetch returns. If the downstream blows up, the item is gone. Replay is an operator command, and the audit table records what was dropped.

The pipeline_runs audit table

Application-append-only by convention, one row per pipeline execution. Database-file access can rewrite it.

CREATE TABLE IF NOT EXISTS pipeline_runs (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    pipeline    TEXT NOT NULL,
    started_at  INTEGER NOT NULL,
    ended_at    INTEGER NOT NULL,
    status      TEXT NOT NULL,
    error_text  TEXT
);
CREATE INDEX IF NOT EXISTS idx_runs_pipeline ON pipeline_runs(pipeline, started_at DESC);

Each row records pipeline name, start and end timestamps, status (ok/error), and error text. The /status pipeline_name operator command reads this table newest-first. When something breaks, scroll back to the last green run, and the gap to the first red run is your investigation window.

Go code: FilterUnseen + MarkSeen

FilterUnseen is one IN query against the composite key. SQLite plans the expanded placeholder list well below a batch size of fifty.

func (s *StateStore) FilterUnseen(pipeline, scope string, ids []string) ([]string, error) {
    if len(ids) == 0 {
        return nil, nil
    }
    placeholders := strings.TrimRight(strings.Repeat("?,", len(ids)), ",")
    q := fmt.Sprintf(`SELECT item_id FROM seen_items WHERE pipeline=? AND scope=? AND item_id IN (%s)`, placeholders)
    args := make([]interface{}, 0, len(ids)+2)
    args = append(args, pipeline, scope)
    for _, id := range ids {
        args = append(args, id)
    }
    rows, err := s.db.Query(q, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    seen := make(map[string]struct{}, len(ids))
    for rows.Next() {
        var id string
        if err := rows.Scan(&id); err != nil {
            return nil, err
        }
        seen[id] = struct{}{}
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    out := make([]string, 0, len(ids))
    for _, id := range ids {
        if _, ok := seen[id]; !ok {
            out = append(out, id)
        }
    }
    return out, nil
}

MarkSeen runs one transaction with a prepared statement reused across the batch. INSERT OR IGNORE swallows duplicates, the right semantics for a set-membership table.

func (s *StateStore) MarkSeen(pipeline, scope string, ids []string) error {
    if len(ids) == 0 {
        return nil
    }
    tx, err := s.db.Begin()
    if err != nil {
        return err
    }
    stmt, err := tx.Prepare(`INSERT OR IGNORE INTO seen_items (pipeline, scope, item_id, seen_at) VALUES (?, ?, ?, ?)`)
    if err != nil {
        _ = tx.Rollback()
        return err
    }
    defer stmt.Close()
    now := time.Now().Unix()
    for _, id := range ids {
        if _, err := stmt.Exec(pipeline, scope, id, now); err != nil {
            _ = tx.Rollback()
            return err
        }
    }
    return tx.Commit()
}

Each method may be called concurrently, but FilterUnseen followed by MarkSeen is not an atomic claim. Two workers can both fetch the same unseen ID before either marks it. Use a transactional unique claim/outbox and destination idempotency for repeat-sensitive effects. Busy timeout only handles lock contention.

Calling it from a deterministic step

Every fetch step follows the same pattern: fetch a batch, dedup, short-circuit if empty, store for downstream steps, mark seen. This is the gmail_unread step from main.go:

case "gmail_unread":
    emails, err := gmail.FetchUnread(10)
    if err != nil {
        return fmt.Errorf("[step:%s] gmail fetch failed: %w", step.Name, err)
    }
    if len(emails) == 0 {
        log.Printf("no unread emails, skipping pipeline")
        return nil
    }
    emails = dedupByID(pipeline.Name, "gmail", emails,
        func(e Email) string { return e.ID })
    if len(emails) == 0 {
        log.Printf("all unread emails already processed, skipping pipeline")
        return nil
    }
    data["emails"] = FormatEmailsForPrompt(emails)

dedupByID is a generic helper wrapping FilterUnseen + MarkSeen with a key function. If the state layer is misconfigured or locked, dedup degrades to "process everything" and logs a warning, on purpose. Silently dropping items on a transient state-store issue is worse than running the LLM twice.

What this doesn't give you

  • Cross-host coordination. A SQLite file lives on one filesystem. For pipelines across two hosts, switch to Postgres with the same schema, use Redis with a TTL set, or accept that the duplicate-process window equals your sync interval.
  • Network-attached storage. WAL mode assumes OS lock primitives work. NFS lies about locks. Keep the SQLite file on a local disk.
  • Long-term growth. seen_items grows forever unless you sweep. A cron deleting rows where seen_at < now - 90 days is enough for most pipelines.
  • Replay semantics. Marking seen at fetch means a failed downstream is a dropped item. If your domain cannot tolerate that, write seen on success and add an explicit dedup window on the side effect itself.

Reference implementation

draftcat ships:

  • state.go: full StateStore, schema init, FilterUnseen, MarkSeen, RecordRun, RecentRuns
  • main.go: dedupByID helper and deterministic steps (gmail_unread, ghl_new_contacts, ghl_new_conversations)
  • pipelines/: YAML configs wiring dedup into real pipelines
  • README.md: operator commands including /status

MIT license. Go 1.25, pure-Go SQLite via modernc.org/sqlite so there is no CGO requirement.

Reader contributions

If you ship something similar with a different storage layer, drop a comment with the rough shape: Postgres advisory locks, Redis Bloom filter, Cloudflare KV with versioned keys, in-memory hash flushed on shutdown. Curious how multi-host setups handle the "two workers fetched the same item in the same tick" race without a queue broker.

Related

Changelog

2026-10-04

  • Durability and delivery contracts — October 2026; source review is separate from historical runtime tests.

2026-05-25: initial publish

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment