Data model

Postgres is the sole source of truth for pocket agent. There is no authoritative in-process state anywhere — the store and the ledger read and write the database on every operation. That one decision is what makes the whole app stateless and horizontally scalable: any request can land on any replica, and the correctness of money, cards, and agents comes entirely from rows and the transactions that touch them.

This page is the map of those rows: the key tables, how they relate, and the conventions that keep money honest. The schema lives in one place — ensureSchema() in src/db.ts — and the record shapes in src/store.ts and the *ops modules.

Design principles

A few conventions run through every table. Knowing them makes the rest obvious.

• Real columns for money, jsonb for everything else. Anything that must be locked or summed in SQL — balance_minor, held_minor, frozen, status — is a real typed column, so a transaction can take SELECT … FOR UPDATE on it. Rich, evolving records (a user's wallets, plan, attribution; an agent's persona) live in a data jsonb column so the shape can change without a migration.
• Money is integer minor units. USD is stored as balance_minor (cents, bigint); COGS as amount_usd_micro (USD × 1,000,000). No floats touch a balance.
• The ledger is append-only. The transactions table is written once per money movement; a row's status may advance (pending → confirmed/failed) but the row is never rewritten. It is the statement of record behind the activity feed.
• Exactly-once is a table, not a hope. idempotency, processed_events, and the dedup_key unique indexes make retried requests and redelivered webhooks safe at the storage layer, not just in application logic.
• Durable toggles live in kv. Operator switches (the withdrawals kill-switch, per-user suspend, outflow counters, treasury wallets) are rows an operator flips without a redeploy.

The connection pool (pg, max: 16) sets statement_timeout, query_timeout, and idle_in_transaction_session_timeout all to 15s, so a single slow or lock-blocked statement can never pin a connection and starve the pool.

Entity map

The core of the schema is a chain from identity → agent → card ledger, with the money-op ledgers and the unified transaction log hanging off users and agents. (Postgres foreign-key constraints are intentionally light — relationships are enforced in application code and by targeted indexes — but the logical relationships are what matter for understanding the data.)

Identity and agents

Table	Primary key	Holds
users	app_user_id (Privy DID)	one row per person. email, nickname (unique @handle), kyc_status, and a rich UserRecord in data: wallets, reapUserId / reapAccountId / reapSigner / reapDeposit, plan state (plan, planUntil, plusUntil, stripeCustomerId), attribution, refCode, waitlist, telegramId, locale, ipHash, fp.
agents	agent_id	one row per agent, linked by app_user_id. email is its alias on agents.pocketagent.to; data is the AgentMeta (wallets, role, server-owned persona).
enrollments	agent_id	Agentic Commerce enrollment. source is REAP_CARD (our issued card) or EXTERNAL (the user's own linked card).
agent_settings	agent_id	the agent's SpendLimits (per-tx / daily / monthly caps, merchant policy, approvalOver).
chats / inbox / sent	agent_id	capped messages jsonb arrays (chat 100, inbox/sent 200). Appends are serialized per agent with an advisory lock.

users.nickname and agents.email each carry a partial unique index (uq_users_nickname, uq_agents_email) so handles and aliases can't collide — with app-level advisory-lock claims as the primary enforcement, and the index as a DB-level backstop.

The card ledger

ledger_agents is the custodial card balance-of-record under the Program-Funded model. Because money columns are real columns, a card's balance can be locked and mutated transactionally.

The two numbers that matter are balance_minor and held_minor. Available balance is balance_minor − held_minor. A hold is placed when funds are committed but not yet spent — a card purchase reservation, or a cash-out whose payout is in flight — so the same dollars can't be spent twice.

• reservations — one row per card hold (held → consumed → released); the data carries amount, merchant, and expiresAt. matchAuthorization expires stale holds, then matches one against an incoming authorization.
• mock_pans — demo card numbers (4622 9431 XXXX XXXX, crypto-random, unique) used offline when a real PAN reveal isn't configured.

Money-op ledgers

Each money flow has its own ledger so an operation whose on-chain leg hadn't confirmed by the time the request returned can be finished later — without ever double-crediting. See Money movement for the flows these back.

Table	Backs	Status lifecycle	Credit/debit rule
fund_ops	crypto → card top-ups	sending → pending → credited / failed	credit after confirm, exactly-once, from the amount on the row
redeem_ops	card → crypto cash-outs	sending → pending → done / failed	debit after confirm; USD held meanwhile; released on revert
transactions	the unified feed	pending → confirmed / failed	append-only; status advances by ref
idempotency	money-op claims	pending → done	beginIdempotent claims the key; replay returns the stored result
processed_events	settlement guard	insert-once	INSERT … ON CONFLICT DO NOTHING in the same tx as the balance change

The op_key is deterministic — fund:${user}:${card}:${opId} and redeem:${user}:${card}:${opId} — so a retried request with the same opId resolves to the same row and replays its outcome instead of broadcasting again. The settlement functions (creditFundOp, finalizeRedeemOp) flip the status and mutate ledger_agents inside one transaction, using the amount recorded on the row, so a replay can never over-credit.

transactions.ref is the soft join that ties the feed back to its driver: a fund_card row's ref is the fund op's op_key; a card_purchase row's ref is the reservation id. The partial index idx_tx_ref keeps the hot-path lookup (WHERE ref = $1) index-backed as the append-only log grows.

The double-send backstop

One index deserves its own mention. The money lock plus hasPendingTransferOut is the primary serializer for wallet sends, but a Redis outage could, in theory, let two replicas both pass the guard. So the invariant is also written into Postgres:

CREATE UNIQUE INDEX uq_tx_pending_transfer
  ON transactions (user_id, COALESCE(agent_id,''), chain, asset)
  WHERE kind = 'transfer_out' AND status = 'pending';

At most one pending transfer_out per (user, agent, chain, asset) can exist. If a lock ever fails open, the loser's record-intent INSERT fails — before any broadcast — instead of double-sending.

Human-in-the-loop and the agent

Table	Holds
approvals	an agent's proposed money action (card_purchase / wallet_withdraw / defi) with the exact amount, merchant, and summary. The user must approve before it executes — the agent can never move money on the model's say-so.
agent_tasks	autonomous scheduled runs; next_run_at is a bigint epoch the leader-locked scheduler polls.
agent_memory	durable facts an agent stores about the user (surfaced to the model as untrusted notes).
reminders	one-shot reminders fired at due_at.
defi_positions	open DeFi positions per agent (chain, protocol, kind, USD size, status, opening tx).

Growth, cost, and operations

These tables don't move money but measure and govern it.

Table	Purpose
events	the funnel spine — one append-only row per milestone (signup, gate_pass, agent_created, first_task, card_funded, purchase, subscribe) with per-channel attribution (channel, ref_code, utm, click_ids). dedup_key makes a one-time event exactly-once; anon_id stitches a pre-login touch to the user at login.
cost_events	the COGS ledger — amount_usd_micro for card issuing, gas sponsorship, and LLM inference, so a defensible gross margin exists instead of a guess.
kv	durable operator toggles: admin:kill_withdrawals, admin:suspend:<userId>, the rolling outflow counters, treasury:wallets, and cached home/pulse data.
admin_audit	an immutable, append-only record of every operator action in /admin (freeze a card, decide KYC, suspend a user, flip the kill-switch) with operator and IP.
gold_ledger / user_badges / referrals	gamification. GOLD is a non-withdrawable in-app reward — never cash or crypto. uq_gold_dedup makes each one-time award exactly-once; a referral's escrowed join reward is released only when the invitee funds a card.
waitlist	top-of-funnel capture for invite-gated signups that arrive without a code.
signals	lightweight behavior signals that drive smart suggestions.
push_subs / expo_push_tokens	Web Push (VAPID) and Expo push transports; Telegram linkage lives in users.data.

Schema management and migrations

The schema is created the same way on every boot: initDb() runs ensureSchema(), a block of CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS / ALTER TABLE … ADD COLUMN IF NOT EXISTS statements. The database is required — if DATABASE_URL is absent the app throws rather than falling back to in-memory state. TLS is auto-enabled for proxy.rlwy.net / .railway.app / sslmode=require URLs.

Known gap — no migration framework

ensureSchema runs the same boot DDL on every replica. It is idempotent (everything is IF NOT EXISTS), so it is safe to run concurrently, but there is no versioned migration system — no ordered, advisory-locked, run-once migrations, and no down-migrations. Additive column and index changes are handled inline; a destructive or data-reshaping change would need to be run by hand. Moving to advisory-locked, run-once migrations is a tracked gap.

The payoff of this model is worth restating: because Postgres holds all authoritative state and Redis only coordinates (locks, leader election, dedup, caches), the app scales by adding stateless replicas, and every money invariant on the Money movement page ultimately reduces to a row, a status column, and a transaction that touches them exactly once.