Write a Checkout Saga with Compensating Steps Across Neki Shards

orders, wallets, charges   -> shard group by_customer, xxhash(customer_id)
inventory, reservations    -> shard group by_sku,      xxhash(sku_id)

checkout:    create order (pending) -> reserve stock -> charge wallet -> confirm order
compensate:  release stock (delete reservation, add stock back) + refund (delete charge, add balance back) + cancel order
recover:     compensate every order that is still pending after MIN_AGE

Using the PlanetScale CLI (pscale 0.344.0) and psql, write a checkout saga with compensating steps for orders, stock and wallets that live on different Neki shards.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Shard orders, wallets and charges by customer_id, and inventory and reservations by sku_id, each in its own shard group with an xxhash shard index. Keep CHECK (on_hand >= 0) and CHECK (balance_cents >= 0).
- Make every step one single-shard transaction. Reserve stock with UPDATE ... WHERE on_hand >= qty RETURNING and write the reservation row in the same transaction. Charge with UPDATE ... WHERE balance_cents >= amount RETURNING and write the charge row in the same transaction. Use a psql \gset variable and \if for the condition, because a CTE that contains DML with ON CONFLICT is not supported.
- Make every compensating step idempotent by deleting the reservation or charge row with RETURNING and undoing the change only when a row came back.
- Add a recovery command that compensates orders that are still pending after an age limit.
- Run an order that succeeds, one that is out of stock, one with insufficient funds, and one whose process dies after the charge.
- Run the verification step below before finishing.

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version

pscale database create saga --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create saga main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
       PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

pscale branch shard create saga main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
  until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done

SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE inventory (sku_id bigint PRIMARY KEY, on_hand int NOT NULL CHECK (on_hand >= 0))" \
  -c "CREATE TABLE reservations (sku_id bigint NOT NULL, order_id bigint NOT NULL, qty int NOT NULL, PRIMARY KEY (sku_id, order_id))" \
  -c "CREATE TABLE wallets (customer_id bigint PRIMARY KEY, balance_cents bigint NOT NULL CHECK (balance_cents >= 0))" \
  -c "CREATE TABLE charges (customer_id bigint NOT NULL, order_id bigint NOT NULL, amount_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
  -c "CREATE TABLE orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, sku_id bigint NOT NULL, qty int NOT NULL, amount_cents bigint NOT NULL, status text NOT NULL DEFAULT 'pending', created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (customer_id, order_id))" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": {
    "customer": { "type": "xxhash", "columns": ["customer_id"] },
    "sku":      { "type": "xxhash", "columns": ["sku_id"] }
  },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_customer", "default_shard_index": "customer",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
    { "uid": "by_sku", "default_shard_index": "sku",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "orders":       { "shard_group": "by_customer" },
    "wallets":      { "shard_group": "by_customer" },
    "charges":      { "shard_group": "by_customer" },
    "inventory":    { "shard_group": "by_sku" },
    "reservations": { "shard_group": "by_sku" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update saga main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

psql -q -c "INSERT INTO inventory VALUES (1, 5), (2, 0)" -c "INSERT INTO wallets VALUES (1, 10000), (2, 500)"
psql -At -c "SELECT 'sku ' || sku_id || ': ' || on_hand FROM inventory ORDER BY sku_id" -c "SELECT 'customer ' || customer_id || ': ' || balance_cents FROM wallets ORDER BY customer_id"

applied
sku 1: 5
sku 2: 0
customer 1: 10000
customer 2: 500

cat > saga.sh <<'EOF'
#!/bin/bash
# saga.sh checkout <customer> <order> <sku> <qty> <amount> | recover
# Every step is one single-shard transaction. CRASH_AFTER=charge simulates a process that dies after that step.
P="psql -X -q -At -v"

create_order() { $P c=$1 -v o=$2 -v s=$3 -v n=$4 -v a=$5 <<'SQL'
INSERT INTO orders (customer_id, order_id, sku_id, qty, amount_cents) VALUES (:c, :o, :s, :n, :a);
SQL
}

reserve() { $P s=$1 -v o=$2 -v n=$3 <<'SQL' 2>&1 | grep -v 'no rows returned'
BEGIN;
UPDATE inventory SET on_hand = on_hand - :n WHERE sku_id = :s AND on_hand >= :n RETURNING 1 AS ok \gset
\if :{?ok}
  INSERT INTO reservations (sku_id, order_id, qty) VALUES (:s, :o, :n);
  \echo reserved
\else
  \echo out_of_stock
\endif
COMMIT;
SQL
}

charge() { $P c=$1 -v o=$2 -v a=$3 <<'SQL' 2>&1 | grep -v 'no rows returned'
BEGIN;
UPDATE wallets SET balance_cents = balance_cents - :a WHERE customer_id = :c AND balance_cents >= :a RETURNING 1 AS ok \gset
\if :{?ok}
  INSERT INTO charges (customer_id, order_id, amount_cents) VALUES (:c, :o, :a);
  \echo charged
\else
  \echo insufficient_funds
\endif
COMMIT;
SQL
}

confirm() { $P c=$1 -v o=$2 <<'SQL'
UPDATE orders SET status = 'confirmed' WHERE customer_id = :c AND order_id = :o AND status = 'pending';
SQL
}

release_stock() { $P s=$1 -v o=$2 <<'SQL' 2>&1 | grep -v 'no rows returned'
BEGIN;
DELETE FROM reservations WHERE sku_id = :s AND order_id = :o RETURNING qty AS q \gset
\if :{?q}
  UPDATE inventory SET on_hand = on_hand + :q WHERE sku_id = :s;
  \echo released
\else
  \echo no_reservation
\endif
COMMIT;
SQL
}

refund() { $P c=$1 -v o=$2 <<'SQL' 2>&1 | grep -v 'no rows returned'
BEGIN;
DELETE FROM charges WHERE customer_id = :c AND order_id = :o RETURNING amount_cents AS a \gset
\if :{?a}
  UPDATE wallets SET balance_cents = balance_cents + :a WHERE customer_id = :c;
  \echo refunded
\else
  \echo no_charge
\endif
COMMIT;
SQL
}

cancel() { $P c=$1 -v o=$2 <<'SQL'
UPDATE orders SET status = 'cancelled' WHERE customer_id = :c AND order_id = :o AND status = 'pending';
SQL
}

compensate() {  # compensate <customer> <order> <sku>: undo whatever was done, in reverse order
  echo "  compensate order $2: $(refund $1 $2), $(release_stock $3 $2)"
  cancel $1 $2
}

checkout() {  # checkout <customer> <order> <sku> <qty> <amount>
  create_order "$@"
  r=$(reserve $3 $2 $4); [ "$r" = reserved ] || { echo "order $2: $r"; compensate $1 $2 $3; return 2; }
  r=$(charge $1 $2 $5);  [ "$r" = charged ]  || { echo "order $2: $r"; compensate $1 $2 $3; return 3; }
  [ "${CRASH_AFTER:-}" = charge ] && { echo "order $2: process died after the charge"; return 99; }
  confirm $1 $2; echo "order $2: confirmed"
}

recover() {  # compensate every order that has stayed pending longer than MIN_AGE
  $P age="${MIN_AGE:-5 minutes}" -At -F' ' <<'SQL' | while read -r c o s; do echo "recover order $o"; compensate $c $o $s; done
SELECT customer_id, order_id, sku_id FROM orders WHERE status = 'pending' AND created_at < now() - :'age'::interval ORDER BY order_id
SQL
}

"$@"
EOF
chmod +x saga.sh
./saga.sh checkout 1 1001 1 2 2400

order 1001: confirmed

./saga.sh checkout 1 1002 2 1 100; echo "exit=$?"
psql -At -c "SELECT order_id || ' ' || status FROM orders WHERE order_id = 1002"

order 1002: out_of_stock
  compensate order 1002: no_charge, no_reservation
exit=2
1002 cancelled

./saga.sh checkout 2 1003 1 1 1500; echo "exit=$?"
psql -At -c "SELECT 'sku 1: ' || on_hand FROM inventory WHERE sku_id = 1" -c "SELECT order_id || ' ' || status FROM orders WHERE order_id = 1003"

order 1003: insufficient_funds
  compensate order 1003: no_charge, released
exit=3
sku 1: 3
1003 cancelled

CRASH_AFTER=charge ./saga.sh checkout 1 1004 1 1 800; echo "exit=$?"
psql -At -c "SELECT order_id || ' ' || status FROM orders WHERE order_id = 1004" \
        -c "SELECT 'reserved: ' || count(*) FROM reservations WHERE order_id = 1004" \
        -c "SELECT 'charged:  ' || count(*) FROM charges WHERE order_id = 1004"

order 1004: process died after the charge
exit=99
1004 pending
reserved: 1
charged:  1

MIN_AGE='0 seconds' ./saga.sh recover
MIN_AGE='0 seconds' ./saga.sh recover; echo "second run printed: $(MIN_AGE='0 seconds' ./saga.sh recover | wc -l | tr -d ' ') lines"
psql -At -c "SELECT order_id || ' ' || status FROM orders ORDER BY order_id"

recover order 1004
  compensate order 1004: refunded, released
second run printed: 0 lines
1001 confirmed
1002 cancelled
1003 cancelled
1004 cancelled

psql -At -c "SELECT status || ' ' || count(*) FROM orders GROUP BY status ORDER BY status" \
        -c "SELECT 'sku 1: ' || on_hand || ' on hand, ' || (SELECT coalesce(sum(qty), 0) FROM reservations WHERE sku_id = 1) || ' reserved' FROM inventory WHERE sku_id = 1" \
        -c "SELECT 'wallet 1: ' || balance_cents || ', charges ' || (SELECT coalesce(sum(amount_cents), 0) FROM charges WHERE customer_id = 1) FROM wallets WHERE customer_id = 1"

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "order counts by status, stock on hand plus reserved, wallet balance plus charges"
  expected_result: "cancelled 3, confirmed 1, sku 1: 3 on hand, 2 reserved, wallet 1: 7600, charges 2400"