Data & queries

Every SQL query in the entire backend lives in one file: src/db.rs. That makes the database layer easy to review — and easy to explain. This page shows the patterns.

The single storage layer

db.rs contains the row structs (with sqlx::FromRow) and every query function. Handlers and the block producer call these functions — they never write raw SQL inline. This is a clean separation: if a query is wrong, you know exactly where to look.

src/db.rs — what's inside

1// Row structs (mirror the tables)
2BlockRow · TxRow · TradeRow · OrderRow · AdminMetrics
3
4// Queries
5chain_height() · latest_block() · insert_block()
6list_blocks(after, limit) · block_by_index() · block_by_hash()
7txs_of_block() · tx_by_hash() · txs_by_address() · search_txs()
8admin_metrics() · ...

The query patterns

Simple lookups with query_as

Fetch one balance (0 if absent)

1pub async fn get_balance(pool: &PgPool, address: &str, asset: &str) -> Result<i64, String> {
2    Ok(sqlx::query_scalar("SELECT balance FROM balances WHERE address=$1 AND asset=$2")
3        .bind(address).bind(asset)
4        .fetch_optional(pool).await
5        .map_err(|e| e.to_string())?
6        .unwrap_or(0))
7}

Upserts — the "insert or update" pattern

Used everywhere (balances, validators) so the code doesn't have to check whether a row exists first:

upsert_balance

1INSERT INTO balances (address, asset, balance)
2VALUES ($1, $2, $3)
3ON CONFLICT (address, asset) DO UPDATE SET balance = EXCLUDED.balance

Atomic multi-step ops with transactions

Staking, order placement, unstaking and multisig execution all wrap several writes in pool.begin() … commit() so a failure rolls back everything:

A taste — staking moves 4 things atomically

1let mut tx = pool.begin().await?;
2balances::upsert_balance(&mut *tx, address, &native, bal - amount).await?; // 1. debit staker
3balances::upsert_balance(&mut *tx, validator, &native, escrow + amount).await?; // 2. credit escrow
4sqlx::query("INSERT INTO staking_positions ...").execute(&mut *tx).await?;   // 3. record stake
5bump_validator_stake(&mut tx, validator, amount).await?;                    // 4. raise validator stake
6tx.commit().await?;

Block production — the "whole view" snapshot

Producing a block needs the entire balances + nonces state. The producer loads it into memory with load_view, validates/applies in memory, then writes it all back with persist_view:

load_view → validate/apply in memory → persist_view

1let (balances_view, nonces) = balances::load_view(&db).await?;
2// ... validate_block / apply_block on the in-memory HashMap ...
3balances::persist_view(&db, &balances_view, &nonces).await?;

Honest limitation to know

Block insert, mempool confirmation and balance persistence are separate DB writes, not one transaction. A crash mid-sequence could leave the chain inconsistent. The docs even flag this. In a production node, block production would be one atomic transaction.

Admin metrics — 11 aggregates in one go

admin_metrics — a handful of the queries

1-- TVL in XCH (excluding the burn address 0x00..00)
2SELECT COALESCE(SUM(balance),0)::bigint FROM balances
3WHERE asset='XCH' AND address != '0x0000000000000000000000000000000000000000';
4-- 24h tx volume
5SELECT COALESCE(SUM(amount),0)::bigint FROM transactions WHERE timestamp >= $1;
6-- total staked
7SELECT COALESCE(SUM(stake),0)::bigint FROM validators WHERE status='active';

The mempool is a WHERE clause

The classic blockchain mempool is a list of pending transactions in memory. Here it's just a database status — pending transactions are rows with status='pending'. That means the "mempool" survives restarts for free. The producer pulls them with:

mempool::pending — richest txs first

1SELECT ... FROM transactions
2WHERE status='pending'
3ORDER BY fee + priority_fee DESC
4LIMIT $1

Indexes — why they exist

Table	Index	Serves the query…
balances	(asset)	TVL by asset
transactions	(from_addr), (status), (block_index)	wallet history, mempool, block's txs
staking_positions	(address), (validator)	a user's stakes / a validator's stakes
orders	(trader), (pair, status)	my orders, open orders for book rebuild
trades	(pair, id DESC)	recent trades, newest first
auth_challenges	(address, used)	find an unused challenge

Good defense answer if asked "why indexes?": the queries that run on every request (mempool, recent trades, wallet history) would otherwise scan whole tables.

Cross-references

Backend → Block producer — where persist_view runs.
Backend → Exchange — the atomic settlement transaction.
Database → Table definitions — what each table is for.

PreviousSchema & Tables NextExplain It Simply