Database: schema & tables

PostgreSQL is the source of truth for everything. There are 12 tables, created by a single migration. Each one exists because a specific piece of the app needs it — the cross-links below show exactly where.

The database in one picture

Three groups of tables, roughly: chain (blocks, transactions),ledger (accounts, balances, validators, staking), and app (orders, trades, auth, multisig, faucet). They all live in the same database — the node just uses different tables for different jobs.

apps/node/src/db/migrations/0001_init.sql (single migration, applied at boot)

1CREATE TABLE accounts ( ... );
2CREATE TABLE balances ( ... );
3CREATE TABLE transactions ( ... );
4CREATE TABLE blocks ( ... );
5CREATE TABLE validators ( ... );
6CREATE TABLE staking_positions ( ... );
7CREATE TABLE orders ( ... );
8CREATE TABLE trades ( ... );
9CREATE TABLE auth_challenges ( ... );
10CREATE TABLE multisig_wallets ( ... );
11CREATE TABLE multisig_txs ( ... );
12CREATE TABLE faucet_claims ( ... );
13// applied automatically: sqlx::migrate!("./src/db/migrations").run(&db)

Design note worth mentioning

There are no foreign-key constraints — referential integrity is enforced in Rust. That's unusual but deliberate for a demo: the node logic already guarantees consistency, and it keeps the schema tiny. A production schema would likely add FKs.

Conventions used everywhere

Money = BIGINT minor units. 1 XCH = 1e8 minor units. No floats, ever.
Time = unix epoch milliseconds as BIGINT.
address = VARCHAR(44) ("0x" + 40 hex).
hash = VARCHAR(64) hex (SHA-256/SHA3-256).

All 12 tables

👤accountstable

One row per wallet address. Tracks the nonce (replay counter) and creation time.

Column	Type	Notes
address	VARCHAR(44) PK	0x + 40 hex chars
nonce	BIGINT	next tx counter, starts 0
created_at	BIGINT	epoch ms

Used in:Backend → Nonce check Frontend → Send page

💰balancestable

How much of each asset an address owns. Composite key (address, asset).

Column	Type	Notes
address	VARCHAR(44)
asset	VARCHAR(16)	'XCH' or 'xUSD'
balance	BIGINT	minor units, no floats

Used in:Backend → Ledger Frontend → Dashboard

🧾transactionstable

Every on-chain transaction. Also serves as the MEMPOOL (status='pending').

Column	Type	Notes
hash	VARCHAR(64) PK
from_addr / to_addr	VARCHAR(44)
asset / amount / fee / priority_fee	VARCHAR(16) / BIGINT
nonce / chain_id / timestamp	BIGINT
pubkey / signature	VARCHAR(64) / VARCHAR(128)
status	VARCHAR(10)	pending → confirmed / failed
block_index / block_hash	BIGINT / VARCHAR(64)	NULL until confirmed

Used in:Backend → Mempool Frontend → Explorer

🧱blockstable

The chain itself: each row is a block. index is the height.

Column	Type	Notes
index	BIGINT PK	block height (0 = genesis)
hash	VARCHAR(64) UNIQUE
prev_hash	VARCHAR(64)	the chain link
merkle_root	VARCHAR(64)
proposer	VARCHAR(44)
tx_count	BIGINT
created_at	BIGINT

Used in:Backend → Block producer Frontend → Explorer

⚖️validatorstable

Proof-of-stake participants. stake = their consensus weight.

Column	Type	Notes
address	VARCHAR(44) PK
stake	BIGINT
status	VARCHAR(10)	active / removed
rewards	BIGINT	lifetime rewards
joined_at	BIGINT

Used in:Backend → Staking Backend → Proposer selection

🔒staking_positionstable

Every individual stake. unlock_epoch controls when coins can be withdrawn.

Column	Type	Notes
id	BIGSERIAL PK
address	VARCHAR(44)	the staker
validator	VARCHAR(44)	who it's staked to
amount	BIGINT
unlock_epoch	BIGINT	when it matures

Used in:Backend → Stake/unstake Frontend → Staking page

📋orderstable

Exchange orders. status drives the in-memory order book.

Column	Type	Notes
id	BIGSERIAL PK
trader	VARCHAR(44)
pair / side	VARCHAR(16) / VARCHAR(4)	side = bid \| ask
price / amount / filled	BIGINT
status	VARCHAR(10)	open / closed / cancelled
created_at	BIGINT

Used in:Backend → Matching engine Frontend → Trade page

🤝tradestable

Every executed fill. Price is the maker's price.

Column	Type	Notes
id	BIGSERIAL PK
pair / price / amount	VARCHAR(16) / BIGINT
buy_addr / sell_addr	VARCHAR(44)
created_at	BIGINT

Used in:Backend → Settlement Frontend → Recent trades

🎟️auth_challengestable

Temporary login challenges (10 min, single-use).

Column	Type	Notes
id	BIGSERIAL PK
address	VARCHAR(44)
challenge	VARCHAR(64)	16 random bytes hex
expires_at	BIGINT
used	BOOLEAN	single-use

Used in:Backend → Sign-in-with-wallet

🏦multisig_walletstable

N-of-M wallets. Address is derived from signers + threshold.

Column	Type	Notes
address	VARCHAR(44) PK
signers	TEXT	JSON array of addresses
threshold	INT
created_at	BIGINT

Used in:Backend → Multisig

✍️multisig_txstable

Pending multisig proposals + their approvals.

Column	Type	Notes
id	BIGSERIAL PK
wallet / to_addr / asset / amount / nonce	…
signatures	TEXT	JSON array of approvers
executed	BOOLEAN
created_at	BIGINT

Used in:Backend → Multisig flow

🚰faucet_claimstable

One claim per address — makes the faucet one-time.

Column	Type	Notes
address	VARCHAR(44) PK
claimed_at	BIGINT

Used in:Backend → Faucet

How the schema gets created

There's no "CREATE DATABASE xchange" step to run by hand — the node connects to a database and applies the migration at boot with sqlx::migrate!(). For local dev, docker compose up -d starts a Postgres 16 container with database xchange.

See how the node reads and writes all of this — every query lives in one file — on the Database → data & queries page.

PreviousClient-Side Crypto NextData & Queries