This guide moves money between two accounts that live on different shards of Neki without one transaction across shards. The debit and an outbox row commit together on the source shard, and a relay later applies the credit on the destination shard exactly once, using a table of applied transfer ids.
What you will build
accounts (account_id, balance CHECK >= 0) --+
outbox (account_id, outbox_id, transfer_id, to_account, amount) --+-- shard group by_account, xxhash(account_id)
applied_transfers (account_id, transfer_id) --+
transfer.sh: BEGIN; debit source; INSERT outbox row; COMMIT; (source shard)
relay.sh: for each undelivered outbox row:
credit.sh: BEGIN; INSERT applied_transfers; credit if new; COMMIT; (destination shard)
mark the outbox row delivered only if credit.sh succeeded (source shard)
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, move money between accounts on different Neki shards with an outbox table.
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 accounts, outbox and applied_transfers by account_id with {"type":"xxhash","columns":["account_id"]}, in one shard group. The outbox row carries the source account_id, so the debit and the outbox insert are one single-shard transaction.
- Generate the transfer_id with gen_random_uuid() when the debit commits. Keep CHECK (balance >= 0) on accounts, so an unaffordable transfer fails and queues nothing.
- Apply the credit on the destination shard in one transaction: INSERT INTO applied_transfers ... ON CONFLICT DO NOTHING RETURNING, and add the amount only when that insert returned a row. Use a psql \gset variable and \if for the condition, because a CTE that contains INSERT ... ON CONFLICT fails with [992].
- Mark the outbox row delivered only when credit.sh exits successfully. A failed credit stays queued so the relay retries it. Running the relay again after a failure must not credit twice.
- Run the verification step below before finishing.
Prerequisites
A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
- A Neki database with three shards. Create it with these commands:
pscale database create ledgerdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create ledgerdb 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 ledgerdb 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'")
Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster.
PS-10-AWS-ARM-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete ledgerdb --force --org "$PLANETSCALE_ORG".
1. Create the tables and accounts
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE accounts (account_id bigint PRIMARY KEY, balance bigint NOT NULL CHECK (balance >= 0))" \
-c "CREATE TABLE outbox (account_id bigint NOT NULL, outbox_id bigint GENERATED ALWAYS AS IDENTITY, transfer_id uuid NOT NULL, to_account bigint NOT NULL, amount bigint NOT NULL CHECK (amount > 0), delivered_at timestamptz, PRIMARY KEY (account_id, outbox_id))" \
-c "CREATE TABLE applied_transfers (account_id bigint NOT NULL, transfer_id uuid NOT NULL, PRIMARY KEY (account_id, transfer_id))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "account": { "type": "xxhash", "columns": ["account_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_account", "default_shard_index": "account",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"accounts": { "shard_group": "by_account" },
"outbox": { "shard_group": "by_account" },
"applied_transfers": { "shard_group": "by_account" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update ledgerdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -q -c "INSERT INTO accounts SELECT a, 1000 FROM generate_series(1, 20) a"
for a in 1 2 3 4 5 6 7 8; do
for n in sh2 sh3; do
u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
[ "$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM accounts WHERE account_id = $a" | tail -1)" = 1 ] && echo "account $a is on $n"
done
done
Real output:
applied
account 1 is on sh3
account 2 is on sh2
account 3 is on sh3
account 4 is on sh3
account 5 is on sh3
account 6 is on sh2
account 7 is on sh3
account 8 is on sh2
Twenty accounts hold 20,000 in total. Accounts 1, 3, 4, 5 and 7 are on one data shard, and accounts 2, 6 and 8 are on the other, so a transfer from 1 to 2 crosses shards.
2. Write the transfer, the credit and the relay
cat > transfer.sh <<'EOF'
#!/bin/bash
# transfer.sh <from> <to> <amount>: debit the source and queue the credit in one single-shard transaction
psql -q -At -v ON_ERROR_STOP=1 -v from="$1" -v to="$2" -v amt="$3" <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance - :amt WHERE account_id = :from;
INSERT INTO outbox (account_id, transfer_id, to_account, amount) VALUES (:from, gen_random_uuid(), :to, :amt);
COMMIT;
SQL
EOF
cat > credit.sh <<'EOF'
#!/bin/bash
# credit.sh <to_account> <transfer_id> <amount>: apply a transfer to the destination exactly once
set -o pipefail
psql -q -At -v ON_ERROR_STOP=1 -v to="$1" -v tid="$2" -v amt="$3" <<'SQL' 2>&1 | grep -v 'no rows returned'
BEGIN;
INSERT INTO applied_transfers (account_id, transfer_id) VALUES (:to, :'tid') ON CONFLICT DO NOTHING RETURNING 1 AS fresh \gset
\if :{?fresh}
UPDATE accounts SET balance = balance + :amt WHERE account_id = :to;
\echo credited
\else
\echo already applied
\endif
COMMIT;
SQL
EOF
cat > relay.sh <<'EOF'
#!/bin/bash
# relay.sh: deliver every undelivered outbox row, and mark it delivered only after the credit succeeds
psql -At -F' ' -c "SELECT account_id, outbox_id, transfer_id, to_account, amount FROM outbox WHERE delivered_at IS NULL ORDER BY outbox_id LIMIT 100" |
while read -r src oid tid to amt; do
if result=$(./credit.sh "$to" "$tid" "$amt"); then
echo "outbox $src/$oid to account $to: $result"
psql -q -At -c "UPDATE outbox SET delivered_at = now() WHERE account_id = $src AND outbox_id = $oid"
else
echo "outbox $src/$oid to account $to: credit failed${result:+: $result}" >&2
fi
done
EOF
chmod +x transfer.sh credit.sh relay.sh
totals() { psql -At -c "SELECT sum(balance) || ' in accounts, ' || (SELECT coalesce(sum(amount), 0) FROM outbox WHERE delivered_at IS NULL) || ' queued' FROM accounts"; }
totals
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT account_id FROM outbox WHERE delivered_at IS NULL" | grep -oE 'Route \[[A-Za-z]+\]'
Real output:
20000 in accounts, 0 queued
Route [Scatter]
transfer.sh touches only the source account's shard. credit.sh touches only the destination's. The relay's poll for undelivered rows has no shard key, so it scans every shard.
3. Transfer between shards
./transfer.sh 1 2 100
./transfer.sh 3 8 250
./transfer.sh 6 5 40
totals
psql -At -c "SELECT account_id || ' -> ' || to_account || ': ' || amount FROM outbox ORDER BY account_id"
Real output:
19610 in accounts, 390 queued
1 -> 2: 100
3 -> 8: 250
6 -> 5: 40
Each transfer debited its source and queued a credit in one transaction. The 390 that left the accounts is in the outbox, so accounts plus queue still add up to 20,000.
4. Reject an unaffordable transfer
./transfer.sh 4 5 5000 2>&1 | grep -oE 'violates check constraint "[a-z_]+"'
psql -At -c "SELECT count(*) FROM outbox"
totals
Real output:
violates check constraint "accounts_balance_check"
3
19610 in accounts, 390 queued
The debit failed the balance >= 0 check, so the transaction rolled back and no outbox row was queued.
5. Run the relay
./relay.sh
totals
Real output:
outbox 1/1 to account 2: credited
outbox 3/1001 to account 8: credited
outbox 6/2001 to account 5: credited
20000 in accounts, 0 queued
The relay applied each credit on the destination account's shard and marked the outbox rows delivered. All 20,000 are back in the accounts and nothing is queued.
6. Recover from a relay that stopped halfway
./transfer.sh 5 2 60
read -r src oid tid to amt <<< "$(psql -At -F' ' -c "SELECT account_id, outbox_id, transfer_id, to_account, amount FROM outbox WHERE delivered_at IS NULL")"
echo "credit applied, row not yet marked: $(./credit.sh "$to" "$tid" "$amt")"
totals
echo "--- relay runs again"
./relay.sh
totals
psql -At -c "SELECT count(*) FROM applied_transfers"
Real output:
credit applied, row not yet marked: credited
20000 in accounts, 60 queued
--- relay runs again
outbox 5/1002 to account 2: already applied
20000 in accounts, 0 queued
4
This run applied the credit and stopped before marking the outbox row, which is the failure a crashed relay leaves behind. The queue still lists the transfer, so the relay delivers it again. The second credit finds the transfer id in applied_transfers, does not add the amount a second time, and the row is marked delivered. Four transfers have been applied once each.
7. Run two relays at once
./transfer.sh 1 8 10
./transfer.sh 3 2 20
./transfer.sh 7 6 30
./transfer.sh 6 3 5
./relay.sh > /dev/null &
./relay.sh > /dev/null &
wait
totals
psql -At -c "SELECT count(*) || ' outbox rows, ' || count(*) FILTER (WHERE delivered_at IS NOT NULL) || ' delivered' FROM outbox"
psql -At -c "SELECT count(*) FROM applied_transfers"
Real output:
20000 in accounts, 0 queued
8 outbox rows, 8 delivered
8
Two relays ran at the same time over the same queue. Eight transfers have been applied once each, every outbox row is delivered, and the totals did not change.
Verify the result
psql -At -c "SELECT sum(balance) FROM accounts"
psql -At -c "SELECT count(*) FROM outbox WHERE delivered_at IS NULL"
psql -At -c "SELECT (SELECT count(*) FROM outbox) = (SELECT count(*) FROM applied_transfers)"
Expected: 20000, 0, and t. Money is conserved, nothing is waiting, and every outbox row has exactly one applied transfer.
How it works
The outbox row and the debit share a shard key, so they commit or roll back together, and the credit's destination shard is reached later by a separate single-shard transaction. Delivery is at least once: the relay marks a row delivered only after credit.sh succeeds, and retries any row it left queued. The primary key (account_id, transfer_id) on applied_transfers, written in the same transaction as the credit, turns that into exactly-once effect, because a retry finds the id already present and skips the balance update. While a transfer is in flight, money is in neither account and the outbox holds it, which is why the check adds the two. The Neki data topology guide describes how a shard key is hashed and placed.
Common issues
A CTE with ON CONFLICT is not supported
WITH ins AS (INSERT ... ON CONFLICT DO NOTHING RETURNING 1) UPDATE ... WHERE EXISTS (SELECT 1 FROM ins) failed with [992] CTE containing DML body (INSERT) with ON CONFLICT cannot be executed on the router yet. The \gset and \if form in credit.sh does the same job as separate statements in one transaction.
Cross-shard transactions in the default mode
In testing, a transaction that updated two accounts on different shards committed in the default __neki.tx_mode=multi. This guide did not test what happens if one shard fails during the commit, so it does not rely on cross-shard transactions: every transaction here touches one shard.
Totals look short during a transfer
Between the debit and the credit, the accounts hold less than the starting total by the queued amount, so compare accounts plus undelivered outbox rows with the starting total. The reverse happens after the credit and before the row is marked delivered: the accounts are whole again while the queue still lists the transfer, as step 6 shows with 20000 in accounts and 60 queued. A check that adds the two can read high in that window, so run it after the relay finishes.
The relay polls every shard
The query for undelivered rows has no shard key and runs as a scatter. Poll in batches, as relay.sh does with LIMIT 100, and delete or archive delivered rows so the poll stays small.
Next steps
- Delete the database with
pscale database delete ledgerdb --force --org "$PLANETSCALE_ORG". - Read the guide on keeping ledger transactions on one Neki shard for the case where both sides of a movement can share a shard key.
- Read the Neki data topology guide for placing related tables in one shard group.
verification:
status: verified
tested_at: "2026-10-29"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "total balance; undelivered outbox rows; outbox rows equal applied transfers"
expected_result: "20000, 0, t"