Complete database schema reference. Every table, its purpose, key columns, indexes, and relationships across marketplace, agent, governance, rewards, and realtime domains.
Every node runs its own isolated PostgreSQL 16 instance. There is no shared database anywhere in the system. Tables are organized into six domains.
listings -- Core asset records for sale
| Column | Type | Purpose |
|---|---|---|
id | TEXT PK | Unique listing identifier |
chain | ENUM (BTC, ETH, SOL) | Which blockchain the asset lives on |
assetOutpoint | TEXT UNIQUE | On-chain asset identifier |
sellerAddress | TEXT | Seller's wallet address |
priceSats | BIGINT | Price in base units (satoshis for BTC, wei for ETH) |
status | TEXT | OPEN, FILLED, or CANCELLED |
btcSale / evmSale / solanaSale | JSONB | Chain-specific sale parameters |
collectionId | TEXT FK | Parent collection (optional) |
royaltyBpsOverride | INT (0-10000) | Override creator royalty percentage |
revealNonce | INT | Incrementing counter for PSBT share reveals |
expiresAt | TIMESTAMP | When listing automatically cancels |
Indexed by: chain, status, assetOutpoint, expiresAt, updatedAt.
orderIntents -- Purchase initialization
| Column | Type | Purpose |
|---|---|---|
id | TEXT PK | Order identifier |
listingOutpoint | TEXT FK | Which listing is being purchased |
buyerPubkey | TEXT | Buyer's Ed25519 public key |
chain | ENUM | Target chain |
sessionId | TEXT | Buyer's active session |
btcActiveOrders -- Bitcoin-specific order state tracking
| Column | Type | Purpose |
|---|---|---|
listingOutpoint | TEXT PK/FK | The listing being purchased |
orderId | TEXT UNIQUE FK | Links to orderIntents |
status | TEXT | Signing/confirmation progress |
txid | TEXT | Final transaction hash (once broadcast) |
lockedAt | TIMESTAMP | When this order locked the listing |
expiresAt | TIMESTAMP | Order timeout |
sessionQuotes -- Pricing snapshots bound to orders
| Column | Type | Purpose |
|---|---|---|
orderId + sessionId | Composite PK | Links quote to specific order and session |
sessionCommitHash | TEXT | Hash of quote commitment |
body | JSONB | Full quote details (price, fees, witnesses) |
signature | TEXT | Serving node's Ed25519 signature on the quote |
expiresAt | TIMESTAMP | Quote validity window |
txSummaries -- On-chain transaction records
| Column | Type | Purpose |
|---|---|---|
txid | TEXT PK | On-chain transaction ID |
orderId | TEXT FK | Which order this settles |
chain | ENUM | Which blockchain |
blockHeight | INT | Block where transaction landed |
decodedSummary | JSONB | Parsed outputs and fee breakdown |
txAttestations -- Witness signatures on transactions
| Column | Type | Purpose |
|---|---|---|
txid | TEXT FK | Which transaction |
agentPubkey | TEXT | Which witness node signed |
verdict | ENUM (VALID, INVALID) | Attestation result |
reasons / breakdown | JSONB | Validation details and fee verification |
validatorVersion | TEXT | Protocol version used for validation |
UNIQUE constraint on (txid, orderId, agentPubkey) -- one attestation per node per transaction.
listingPsbtShards -- Threshold encryption metadata per listing
| Column | Type | Purpose |
|---|---|---|
listingOutpoint | TEXT PK/FK | Links to listing |
threshold / totalShares | INT | M-of-N scheme (e.g. 3-of-4) |
encryptedPsbtVaultBlobId | TEXT FK | Secret-vault reference to threshold-encrypted PSBT data |
participants | JSONB | Array of shard-holder node pubkeys |
shareCommitments | JSONB | Verification hashes for each share |
listingPsbtShares -- Individual encrypted shares held by nodes
| Column | Type | Purpose |
|---|---|---|
listingOutpoint | TEXT FK | Which listing |
holderAgentPubkey | TEXT | Which node holds this share |
shareIndex | INT | Position in threshold scheme |
shareValueVaultBlobId | TEXT FK | Secret-vault reference to encrypted share data |
UNIQUE on (listingOutpoint, holderAgentPubkey) -- one share per node per listing.
listingPsbtRevealChallenges -- Authorized share reveal requests
| Column | Type | Purpose |
|---|---|---|
challengeId | TEXT PK | Unique challenge identifier |
listingOutpoint | TEXT FK | Which listing |
orderId | TEXT FK | Which order is requesting |
listingRevealNonce | INT | Must match listing's current revealNonce |
nonce / signature | TEXT | Challenge authentication |
expiresAt | TIMESTAMP | Challenge timeout |
collectionMetadata -- Collection identity and display info
| Column | Type | Purpose |
|---|---|---|
collectionId | TEXT PK | Unique collection ID |
chain | ENUM | BTC, ETH, or SOL |
collectionType | ENUM | EVM_CONTRACT, PARENT_CHILD, GALLERY, CURATED |
name / description | TEXT | Display information |
verified | ENUM | UNVERIFIED, PENDING, VERIFIED, REJECTED |
contractAddress | TEXT | EVM contract (if applicable) |
parentInscriptionId | TEXT | BTC parent inscription (if applicable) |
creatorAddress | TEXT | Collection creator wallet |
collectionRegistry -- Royalty recipient configuration
| Column | Type | Purpose |
|---|---|---|
collectionId | TEXT FK | Which collection |
recipientAddress | TEXT | Where royalties are sent |
bps | INT (0-10000) | Royalty percentage in basis points |
Multiple recipients per collection are supported (e.g. co-creators splitting royalties).
collectionSubmissions -- Community proposal and voting system
| Column | Type | Purpose |
|---|---|---|
collectionId | TEXT | Proposed collection identifier |
collectionType | ENUM | Type of collection being proposed |
status | ENUM | UNVERIFIED, PENDING, VERIFIED, REJECTED |
verifyVotes / rejectVotes | INT | Community vote tallies |
softApproved | BOOL | True if no conflicts were detected, so the collection is shown |
hasConflict | BOOL | True if inscription range overlaps existing collection |
votingDeadline | TIMESTAMP | 7-day deadline for conflicted proposals |
assetOwnershipCache -- Who owns what (lazy-loaded, cursor-paginated)
| Column | Type | Purpose |
|---|---|---|
assetKey | TEXT PK | Composite identifier (chain:contract:tokenId) |
chain | ENUM | Which blockchain |
ownerAddress | TEXT | Current owner |
verifiedAtBlock | BIGINT | Last on-chain verification block |
inscriptionId / contractAddress / mintAddress | TEXT | Denormalized for fast queries |
Indexed by (chain, ownerAddress, assetKey) for cursor pagination.
assetActivity -- Historical event log with keyset pagination
| Column | Type | Purpose |
|---|---|---|
id | TEXT PK | Sortable "{epochMs}_{random}" format (IS the cursor) |
activityType | ENUM | MINT, TRANSFER, LIST, DELIST, BUY, SELL, BURN, INSCRIBE |
priceRaw | TEXT | Sale price in base units |
txHash | TEXT | On-chain transaction |
occurredAt | TIMESTAMP | When the activity happened on-chain |
UNIQUE on (txHash, assetKey) -- prevents double-logging the same event. All queries use keyset pagination: WHERE id < ?cursor ORDER BY id DESC (no OFFSET).
rewardClaims -- Token entitlements (claims, not transfers)
| Column | Type | Purpose |
|---|---|---|
id | TEXT PK | Claim identifier |
reason | ENUM | NODE_FEE_REIMBURSEMENT, NODE_INCENTIVE, USER_PURCHASE_REWARD, MODERATION_REWARD, BURN_ALLOCATION, MEDIA_OPTIMIZATION |
tokenAmountRaw | TEXT | Full precision token amount |
status | ENUM | PENDING, SIGNED, CLAIMED, EXPIRED |
stateEpoch | INT | Which weekly epoch this belongs to |
signature | TEXT | MPC threshold signature for claiming |
claimEpochs -- Weekly reward batching windows
| Column | Type | Purpose |
|---|---|---|
epochNumber | INT UNIQUE | Sequential epoch counter |
status | ENUM | OPEN, CLOSING, FUNDING, SIGNING, BROADCASTING, CONFIRMED, FAILED |
totalTokenAmountRaw | TEXT | Sum of all claims in this epoch |
signingCeremonyId | TEXT FK | MPC ceremony for batch payout |
payoutTxHash | TEXT | Final on-chain transaction |
chatThreads / chatMessages -- Sharded via rendezvous hashing. Each thread is assigned to specific nodes based on the shard ring. Messages include sender, content, and thread reference.
protocolEvents -- The complete signed event log. Every Ed25519-signed event gossiped through the network is stored here for full auditability. This is the canonical record of all protocol activity.