Move from SQLite to Postgres

The agent has outgrown one host. Reservation on SQLite is a write lock on a local file; two hosts need a store they share. The change is the store constructor, and nothing else: same policy, same decorator, same receipts, same guarantees, graded on Postgres by the same suite that grades SQLite.

The policy

runnable

schema: ctrlrun.policy/v2

actions:
  stripe.refund:
    effect: "refund:{payment_id}"
    decision: allow

The code

This script runs on SQLite offline and on Postgres when CTRLRUN_STORE_URL names one; the part that changes is open_store.

runnable file=main.py

import os
from pathlib import Path

import ctrlrun
from ctrlrun import Control, Policy, SQLiteStateStore, StateStore

HERE = Path(__file__).resolve().parent
STATE = HERE / ".ctrlrun"
STATE.mkdir(exist_ok=True)
for name in ("state.db", "state.db-wal", "state.db-shm"):
    (STATE / name).unlink(missing_ok=True)


def open_store() -> StateStore:
    url = os.environ.get("CTRLRUN_STORE_URL")
    if url and url.startswith(("postgresql://", "postgres://")):
        from ctrlrun.postgres import PostgresStateStore  # pip install "ctrlrun[postgres]"

        return PostgresStateStore(url, schema="ctrlrun")  # migrates at open, forward only
    return SQLiteStateStore(STATE / "state.db")


store = open_store()
control = Control(Policy.from_file(HERE / "ctrlrun.yaml"), store)
refunds: list[str] = []


@ctrlrun.protect("stripe.refund", effect="refund:{payment_id}", control=control)
def refund(payment_id: str, amount: int) -> str:
    refunds.append(payment_id)
    return "refunded"


with ctrlrun.context(agent="refund-agent"):
    print("store:", type(store).__name__)
    print("refund txn_1:", refund(payment_id="txn_1", amount=12000))
    try:
        refund(payment_id="txn_1", amount=12000)  # a second host, same effect
    except ctrlrun.DuplicateEffect:
        print("refund txn_1 from another host: refused")
    else:
        raise SystemExit("one effect committed twice")

print("remote refund calls:", len(refunds))
store.close()

What the agent sees

store: SQLiteStateStore
refund txn_1: refunded
refund txn_1 from another host: refused
remote refund calls: 1

With CTRLRUN_STORE_URL=postgresql://ctrlrun@db.internal/ctrlrun the first line reads PostgresStateStore and the rest is identical, and now it holds across hosts: the refusal comes from a unique index on the effect key and compare-and-set updates whose row counts are checked, instead of SQLite’s file lock.

The receipt

runnable

ctrlrun receipts --last 2

On Postgres the same command reads the shared store when CTRLRUN_STORE_URL is set, or with --store-url 'postgresql://db.internal/ctrlrun?ctrlrun_schema=ctrlrun'. A read command migrates nothing and creates nothing.

When an AMBIGUOUS appears

One new case: a connection lost during COMMIT. Postgres very often did commit, so the store treats it as unknown and re-reads the row to find out which; only if the re-read fails does it refuse to proceed. Your executor’s lost replies are handled as before. Resolve them with ctrlrun resolve --store-url … from any host.

Run on Postgres: the schema, the grants, failover.
Effect keys · Get started · Why.

Start

Get started

Production

MCP

Concepts

Guides

Cookbook

Compare

FAQ

More

Move from SQLite to Postgres

The policy

The code

What the agent sees

The receipt

When an AMBIGUOUS appears

Next

​The policy

​The code

​What the agent sees

​The receipt

​When an AMBIGUOUS appears

​Next

The policy

The code

What the agent sees

The receipt

When an AMBIGUOUS appears

Next