INDEXER_DATABASE_TABLES.md

Akash Console Indexer - PostgreSQL Database Tables

This document describes all PostgreSQL tables created and maintained by the Akash Console Indexer (apps/indexer). The indexer syncs blocks from the Akash blockchain via RPC, decodes transactions, and writes structured data into these tables.

---

Table of Contents

• Data Flow Overview
• All Tables
• Base Chain Tables
• Akash Domain Tables
• BME Tables
• Monitoring Tables
• Writer Reference
• Key Relationships

---

Data Flow Overview

Cosmos RPC Nodes
       |
       v
 chainSync.ts ──> block, transaction, message, transactionEvent, transactionEventAttribute, day
       |
       v
 StatsProcessor (routes decoded messages to indexers)
       |
       ├── AkashStatsIndexer ──> deployment, deploymentGroup, deploymentGroupResource,
       |                          lease, bid, provider, providerAttribute,
       |                          providerAttributeSignature, block (Akash columns)
       |
       ├── BmeIndexer ──> bme_raw_event, bme_ledger_record, bme_status_change, block (BME columns)
       |
       ├── ValidatorIndexer ──> validator
       |
       └── MessageAddressesIndexer ──> addressReference
       
 Scheduled Tasks ──> provider (uptime/IP), providerSnapshot hierarchy, day (prices), monitoredValue

---

All Tables

#	Table Name	Category	Written By	Description
1	block	Base	chainSync + AkashStatsIndexer + BmeIndexer	Block headers + per-block aggregate stats
2	transaction	Base	chainSync	Decoded transactions with gas/fee data
3	message	Base	chainSync + AkashStatsIndexer	Individual messages within transactions
4	transaction_event	Base	chainSync	Events emitted by transactions
5	transaction_event_attribute	Base	chainSync	Key-value attributes on transaction events
6	day	Base	chainSync + Price History task	Daily aggregates and AKT price
7	validator	Base	ValidatorIndexer	Cosmos validator metadata
8	addressReference	Base	MessageAddressesIndexer	Links wallet addresses to messages/txs
9	deployment	Akash	AkashStatsIndexer	Akash deployments with escrow balances
10	deploymentGroup	Akash	AkashStatsIndexer	Resource groups within a deployment
11	deploymentGroupResource	Akash	AkashStatsIndexer	CPU/GPU/memory/storage specs per group
12	lease	Akash	AkashStatsIndexer	Active/closed leases linking deployments to providers
13	bid	Akash	AkashStatsIndexer	Provider bids on deployment orders
14	provider	Akash	AkashStatsIndexer + Scheduled Tasks	Provider registry with uptime and geo data
15	providerAttribute	Akash	AkashStatsIndexer	Key-value attributes on providers
16	providerAttributeSignature	Akash	AkashStatsIndexer	Auditor signatures on provider attributes
17	providerSnapshot	Akash	Provider Status task	Point-in-time provider capacity snapshots
18	providerSnapshotNode	Akash	Provider Status task	Per-node details within a snapshot
19	providerSnapshotNodeCPU	Akash	Provider Status task	CPU details per snapshot node
20	providerSnapshotNodeGPU	Akash	Provider Status task	GPU details per snapshot node
21	providerSnapshotStorage	Akash	Provider Status task	Storage class details per snapshot
22	bme_raw_event	BME	BmeIndexer	Raw BME events (staging)
23	bme_ledger_record	BME	BmeIndexer	Processed ledger settlement records
24	bme_status_change	BME	BmeIndexer	Minting status transitions
25	monitoredValue	Monitoring	Balance monitors	Tracked values for notification triggers

---

Base Chain Tables (Cosmos-Generic)

These tables store raw blockchain data and are populated by the chainSync pipeline before any domain-specific indexer runs.

block

The primary time-series anchor. Every other table references block height. On Akash chains, this table is extended with per-block aggregate columns updated by AkashStatsIndexer and BmeIndexer.

Column	Type	Notes
height	integer	Primary Key
datetime	timestamptz	Block timestamp
hash	varchar(255)	Block hash
proposer	varchar(255)	Proposer validator address
dayId	UUID	FK -> day.id
txCount	integer	Transactions in this block
isProcessed	boolean	false until all indexers have processed it
totalTxCount	bigint	Running total of all transactions
Akash extensions:
activeLeaseCount	integer	Active leases at this block height
totalLeaseCount	integer	Cumulative lease count
activeCPU	integer	Total active CPU units (millicores)
activeGPU	integer	Total active GPU units
activeMemory	bigint	Total active memory (bytes)
activeEphemeralStorage	bigint	Total active ephemeral storage (bytes)
activePersistentStorage	bigint	Total active persistent storage (bytes)
activeProviderCount	integer	Providers with active leases
totalUAktSpent	double	Cumulative uAKT spent on leases
totalUUsdcSpent	double	Cumulative uUSDC spent on leases
totalUActSpent	double	Cumulative uACT spent on leases
totalUUsdSpent	double	Cumulative USD-equivalent spent
BME extensions:
totalUaktBurnedForUact	double	Cumulative uAKT burned for ACT minting
totalUactMinted	double	Cumulative uACT minted
totalUactBurnedForUakt	double	Cumulative uACT burned for AKT
totalUaktReminted	double	Cumulative uAKT re-minted
vaultUakt	double	Current vault balance in uAKT
outstandingUact	double	Outstanding uACT supply

Schema files: packages/database/dbSchemas/base/block.ts, packages/database/dbSchemas/akash/akashBlock.ts

---

transaction

Column	Type	Notes
id	UUID	Primary Key
hash	varchar(255)	Transaction hash (SHA256)
index	integer	Position within the block
height	integer	FK -> block.height
msgCount	integer	Number of messages
multisigThreshold	integer	Multisig threshold (if applicable)
gasUsed	integer	Gas consumed
gasWanted	integer	Gas requested
fee	numeric(30,0)	Transaction fee
memo	text	User-provided memo
isProcessed	boolean	Processing flag
hasProcessingError	boolean	Error during processing
log	text	Raw log output

Indexes: height, hash, height + isProcessed + hasProcessingError
Schema file: packages/database/dbSchemas/base/transaction.ts

---

message

Individual messages within transactions. Extended on Akash chains with a relatedDeploymentId FK.

Column	Type	Notes
id	UUID	Primary Key
txId	UUID	FK -> transaction.id
height	integer	FK -> block.height
type	varchar(255)	Protobuf message type URL
typeCategory	varchar(255)	Grouped category (e.g., "deployment")
index	integer	Position within the transaction
indexInBlock	integer	Global position within the block
isProcessed	boolean	Processing flag
isNotificationProcessed	boolean	Notification processing flag
amount	numeric(30,0)	Transfer amount (if applicable)
data	bytea	Raw protobuf-encoded message body
relatedDeploymentId	UUID	Akash only - FK -> deployment.id

Schema files: packages/database/dbSchemas/base/message.ts, packages/database/dbSchemas/akash/akashMessage.ts

---

transaction_event

Column	Type	Notes
id	UUID	Primary Key
height	integer	Block height
tx_id	UUID	FK -> transaction.id
index	integer	Event index within the transaction
type	varchar(255)	Event type string

Unique index: tx_id + index
Schema file: packages/database/dbSchemas/base/transactionEvent.ts

---

transaction_event_attribute

Column	Type	Notes
id	UUID	Primary Key
transaction_event_id	UUID	FK -> transaction_event.id
index	integer	Attribute index within the event
key	varchar(255)	Attribute key
value	text	Attribute value

Unique index: transaction_event_id + index
Schema file: packages/database/dbSchemas/base/transactionEventAttribute.ts

---

day

Daily aggregate records. One row per calendar day.

Column	Type	Notes
id	UUID	Primary Key
date	timestamptz	Calendar date (unique)
aktPrice	double	AKT/USD price for the day
firstBlockHeight	integer	First block of the day
lastBlockHeight	integer	Last block of the day
lastBlockHeightYet	integer	Latest known block (during the day)
aktPriceChanged	boolean	Whether price was updated

Schema file: packages/database/dbSchemas/base/day.ts

---

validator

Cosmos validator metadata. Only populated when indexing from genesis (no startHeight).

Column	Type	Notes
id	UUID	Primary Key
operatorAddress	varchar(255)	Unique operator address
accountAddress	varchar(255)	Unique account address
hexAddress	varchar(255)	Unique hex address
createdMsgId	UUID	Message that created this validator
moniker	varchar(255)	Human-readable name
identity	varchar(255)	Keybase identity hash
website	varchar(255)	Validator website
description	text	Validator description
securityContact	varchar(255)	Security contact email
rate	double	Commission rate
maxRate	double	Maximum commission rate
maxChangeRate	double	Maximum commission change rate
minSelfDelegation	bigint	Minimum self-delegation
keybaseUsername	varchar(255)	Resolved Keybase username
keybaseAvatarUrl	varchar(255)	Keybase avatar URL

Schema file: packages/database/dbSchemas/base/validator.ts

---

addressReference

Links wallet addresses to the transactions/messages they appear in.

Column	Type	Notes
id	serial	Primary Key
transactionId	UUID	FK -> transaction.id
messageId	UUID	FK -> message.id (nullable)
address	varchar(255)	Wallet address
type	varchar(255)	Role: Signer, Sender, Receiver
height	integer	Block height

Indexes: address, address + transactionId
Schema file: packages/database/dbSchemas/base/addressReference.ts

---

Akash Domain Tables

These tables are populated by the AkashStatsIndexer and scheduled tasks. They represent Akash-specific concepts: deployments, leases, providers, and their capacity.

deployment

Column	Type	Notes
id	UUID	Primary Key
owner	varchar(255)	Deployer wallet address
dseq	varchar(255)	Deployment sequence number
createdHeight	integer	Block height of creation
balance	double	Current escrow balance
deposit	bigint	Initial deposit amount
denom	varchar(255)	Payment denomination (uakt, uusdc)
lastWithdrawHeight	integer	Last withdrawal block
withdrawnAmount	double	Total withdrawn
closedHeight	integer	Block height of closure (null if active)

Unique index: owner + dseq
Schema file: packages/database/dbSchemas/akash/deployment.ts

---

deploymentGroup

Column	Type	Notes
id	UUID	Primary Key
deploymentId	UUID	FK -> deployment.id
owner	varchar(255)	Deployer wallet address
dseq	varchar(255)	Deployment sequence
gseq	integer	Group sequence number

Unique index: owner + dseq + gseq
Schema file: packages/database/dbSchemas/akash/deploymentGroup.ts

---

deploymentGroupResource

Column	Type	Notes
id	serial	Primary Key
deploymentGroupId	UUID	FK -> deploymentGroup.id
cpuUnits	integer	CPU in millicores
gpuUnits	integer	GPU count
gpuVendor	varchar(255)	GPU vendor (nvidia, amd)
gpuModel	varchar(255)	GPU model name
memoryQuantity	bigint	Memory in bytes
ephemeralStorageQuantity	bigint	Ephemeral storage in bytes
persistentStorageQuantity	bigint	Persistent storage in bytes
count	integer	Number of instances
price	double	Bid price

Schema file: packages/database/dbSchemas/akash/deploymentGroupResource.ts

---

lease

Column	Type	Notes
id	UUID	Primary Key
deploymentId	UUID	FK -> deployment.id
deploymentGroupId	UUID	FK -> deploymentGroup.id
owner	varchar(255)	Deployer wallet address
dseq	varchar(255)	Deployment sequence
oseq	integer	Order sequence
gseq	integer	Group sequence
bseq	integer	Bid sequence (default 0)
providerAddress	varchar(255)	FK -> provider.owner
createdHeight	integer	Block height of creation
closedHeight	integer	Block height of closure (null if active)
predictedClosedHeight	bigint	Estimated block when funds exhaust
price	double	Price per block
withdrawnAmount	double	Total withdrawn by provider
denom	varchar(255)	Payment denomination
cpuUnits	integer	Leased CPU
gpuUnits	integer	Leased GPU
memoryQuantity	bigint	Leased memory
ephemeralStorageQuantity	bigint	Leased ephemeral storage
persistentStorageQuantity	bigint	Leased persistent storage

Unique index: owner + dseq + gseq + oseq + providerAddress + bseq
Schema file: packages/database/dbSchemas/akash/lease.ts

---

bid

Column	Type	Notes
id	serial	Primary Key
owner	varchar(255)	Deployer wallet address
dseq	varchar(255)	Deployment sequence
gseq	integer	Group sequence
oseq	integer	Order sequence
bseq	integer	Bid sequence (default 0)
provider	varchar(255)	Provider address
price	double	Bid price per block
createdHeight	integer	Block height of creation

Schema file: packages/database/dbSchemas/akash/bid.ts

---

provider

Column	Type	Notes
owner	varchar(255)	Primary Key - Provider wallet address
hostUri	varchar(255)	Provider endpoint URI
createdHeight	integer	Block height of registration
deletedHeight	integer	Block height of deletion (null if active)
updatedHeight	integer	Block height of last update
email	varchar(255)	Contact email
website	varchar(255)	Provider website
akashVersion	varchar(255)	Akash software version
cosmosSdkVersion	varchar(255)	Cosmos SDK version
isOnline	boolean	Current online status
lastCheckDate	timestamptz	Last status check time
error	text	Last error message
ip	varchar(255)	Resolved IP address
ipRegion / ipRegionCode	varchar(255)	Geographic region
ipCountry / ipCountryCode	varchar(255)	Geographic country
ipLat / ipLon	varchar(255)	Geographic coordinates
uptime1d / uptime7d / uptime30d	double	Uptime percentages
lastSnapshotId	UUID	Latest snapshot
lastSuccessfulSnapshotId	UUID	Latest successful snapshot
downtimeFirstSnapshotId	UUID	First snapshot of current downtime
nextCheckDate	timestamptz	When to check next
failedCheckCount	integer	Consecutive failed checks

Written by: AkashStatsIndexer (on-chain data), Provider Status task (capacity), IP Lookup task (geo), Uptime Tracker (uptime)
Schema file: packages/database/dbSchemas/akash/provider.ts

---

providerAttribute

Column	Type	Notes
id	serial	Primary Key
provider	varchar(255)	FK -> provider.owner
key	varchar(255)	Attribute key
value	varchar(255)	Attribute value

Schema file: packages/database/dbSchemas/akash/providerAttribute.ts

---

providerAttributeSignature

Column	Type	Notes
id	serial	Primary Key
provider	varchar(255)	FK -> provider.owner
auditor	varchar(255)	Auditor wallet address
key	varchar(255)	Attribute key
value	varchar(255)	Attribute value

Schema file: packages/database/dbSchemas/akash/providerAttributeSignature.ts

---

providerSnapshot

Point-in-time capacity snapshot from polling a provider's gRPC/REST endpoint.

Column	Type	Notes
id	UUID	Primary Key
owner	varchar(255)	FK -> provider.owner
isOnline	boolean	Was provider reachable
checkDate	timestamptz	When the check occurred
error	text	Error message if offline
deploymentCount	integer	Active deployments
leaseCount	integer	Active leases
activeCPU / pendingCPU / availableCPU	bigint	CPU allocation states
activeGPU / pendingGPU / availableGPU	bigint	GPU allocation states
activeMemory / pendingMemory / availableMemory	bigint	Memory allocation states
activeEphemeralStorage / pendingEphemeralStorage / availableEphemeralStorage	bigint	Ephemeral storage states
activePersistentStorage / pendingPersistentStorage / availablePersistentStorage	bigint	Persistent storage states
isLastOfDay	boolean	Last snapshot of the calendar day
isLastSuccessOfDay	boolean	Last successful snapshot of the day

Schema file: packages/database/dbSchemas/akash/providerSnapshot.ts

---

providerSnapshotNode

Per-node (Kubernetes node) resource data within a provider snapshot.

Column	Type	Notes
id	UUID	Primary Key
snapshotId	UUID	FK -> providerSnapshot.id
name	varchar(255)	Node name
cpuAllocatable / cpuAllocated	bigint	CPU capacity vs usage
memoryAllocatable / memoryAllocated	bigint	Memory capacity vs usage
ephemeralStorageAllocatable / ephemeralStorageAllocated	bigint	Storage capacity vs usage
gpuAllocatable / gpuAllocated	bigint	GPU capacity vs usage
capabilitiesStorageHDD / SSD / NVME	boolean	Storage type capabilities

Schema file: packages/database/dbSchemas/akash/providerSnapshotNode.ts

---

providerSnapshotNodeCPU

Column	Type	Notes
id	UUID	Primary Key
snapshotNodeId	UUID	FK -> providerSnapshotNode.id
vendor	varchar(255)	CPU vendor
model	varchar(255)	CPU model
vcores	smallint	Virtual core count

Schema file: packages/database/dbSchemas/akash/providerSnapshotNodeCPU.ts

---

providerSnapshotNodeGPU

Column	Type	Notes
id	UUID	Primary Key
snapshotNodeId	UUID	FK -> providerSnapshotNode.id
vendor	varchar(255)	GPU vendor
name	varchar(255)	GPU name
modelId	varchar(255)	GPU model identifier
interface	varchar(255)	Bus interface (PCIe, etc.)
memorySize	varchar(255)	VRAM size

Schema file: packages/database/dbSchemas/akash/providerSnapshotNodeGPU.ts

---

providerSnapshotStorage

Column	Type	Notes
id	UUID	Primary Key
snapshotId	UUID	FK -> providerSnapshot.id
class	varchar(255)	Storage class: hdd, ssd, nvme
allocatable	bigint	Total capacity
allocated	bigint	Currently allocated

Schema file: packages/database/dbSchemas/akash/providerSnapshotStorage.ts

---

BME Tables (Blockchain Monetary Exchange)

These tables track the AKT/ACT payment settlement layer. Populated by the BmeIndexer, which processes finalize_block_events rather than transaction messages.

bme_raw_event

Staging table for raw BME events before processing.

Column	Type	Notes
id	UUID	Primary Key (gen_random_uuid)
height	integer	FK -> block.height
index	integer	Event index within the block
type	varchar(255)	Event type name
data	jsonb	Full event payload
is_processed	boolean	Processing flag

Unique index: height + index
Schema file: packages/database/dbSchemas/akash/bmeRawEvent.ts

---

bme_ledger_record

Processed ledger settlement records - the core BME accounting data.

Column	Type	Notes
id	UUID	Primary Key (gen_random_uuid)
height	integer	FK -> block.height
sequence	integer	Sequence number within the block
burned_from	varchar(255)	Source address
minted_to	varchar(255)	Destination address
burned_denom	varchar(255)	Token burned (e.g., uakt)
burned_amount	numeric(30,0)	Amount burned
burned_price	numeric(20,10)	Price at burn time
minted_denom	varchar(255)	Token minted (e.g., uact)
minted_amount	numeric(30,0)	Amount minted
minted_price	numeric(20,10)	Price at mint time
remint_credit_issued_amount	numeric(30,0)	Re-mint credit issued
remint_credit_accrued_amount	numeric(30,0)	Re-mint credit accrued

Indexes: height, burned_denom + height, minted_denom + height
Schema file: packages/database/dbSchemas/akash/bmeLedgerRecord.ts

---

bme_status_change

Tracks transitions in the BME minting system status.

Column	Type	Notes
id	UUID	Primary Key (gen_random_uuid)
height	integer	FK -> block.height
previous_status	varchar(255)	Status before change
new_status	varchar(255)	Status after change
collateral_ratio	numeric(20,10)	Collateral ratio at time of change

Schema file: packages/database/dbSchemas/akash/bmeStatusChange.ts

---

Monitoring Tables

monitoredValue

Used by the address balance and deployment balance monitors to track state changes for notification triggers.

Column	Type	Notes
id	UUID	Primary Key
tracker	varchar(255)	Monitor type identifier
target	varchar(255)	Monitored entity (address, deployment ID)
value	varchar(255)	Current tracked value
lastUpdateDate	timestamptz	Last time the value changed

Unique index: tracker + target
Schema file: packages/database/dbSchemas/base/monitoredValue.ts

---

Writer Reference

Which component writes to which tables:

Component	Tables Written
chainSync (block download/insert pipeline)	block (base cols), transaction, message (base cols), transaction_event, transaction_event_attribute, day
AkashStatsIndexer (Akash message handler)	deployment, deploymentGroup, deploymentGroupResource, lease, bid, provider (on-chain), providerAttribute, providerAttributeSignature, block (Akash cols), message (relatedDeploymentId)
BmeIndexer (BME event handler)	bme_raw_event, bme_ledger_record, bme_status_change, block (BME cols)
ValidatorIndexer (validator handler)	validator
MessageAddressesIndexer (address tracker)	addressReference
Sync Providers Info (scheduled, every 10s)	providerSnapshot, providerSnapshotNode, providerSnapshotNodeCPU, providerSnapshotNodeGPU, providerSnapshotStorage, provider (online status)
Provider IP Lookup (scheduled, every 30m)	provider (ip/geo columns)
Update Provider Uptime (scheduled, every 10m)	provider (uptime columns)
Sync Price History (scheduled, every 1h)	day (aktPrice)
USD Spending Tracker (scheduled, every 1m)	block (totalUUsdSpent)
Address/Deployment Balance Monitor (scheduled, every 10m)	monitoredValue
Sync Keybase Info (scheduled, every 6h)	validator (keybase columns)

---

Key Relationships

day 1──n block 1──n transaction 1──n message
                  |                    |
                  └──n transactionEvent 1──n transactionEventAttribute
                  |
                  └──n addressReference ──> message (optional)

deployment 1──n deploymentGroup 1──n deploymentGroupResource
     |                |
     └──n lease ──────┘
     |
     └──n bid

provider 1──n providerAttribute
    |    1──n providerAttributeSignature
    |    1──n providerSnapshot 1──n providerSnapshotNode 1──n providerSnapshotNodeCPU
    |                         |                          1──n providerSnapshotNodeGPU
    |                         1──n providerSnapshotStorage
    └──n lease

block 1──n bme_raw_event
      1──n bme_ledger_record
      1──n bme_status_change