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

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