Add database indexes to commonly queried entity fields

[sameh-farouk]

Split from #134. The processor DB has no indexes beyond auto-generated FK indexes from the Init migration. Adding @index directives in schema.graphql for commonly filtered/ordered fields would improve query performance with minimal effort.

Candidate fields

type Node @entity {
  nodeID: Int! @index
  farmID: Int! @index
  twinID: Int! @index
  # ...
}

type Farm @entity {
  farmID: Int! @index
  twinID: Int! @index
  # ...
}

type Twin @entity {
  twinID: Int! @index
  accountID: String! @index
  # ...
}

type NodeContract @entity {
  contractID: BigInt! @index
  twinID: Int! @index
  nodeID: Int! @index
  state: ContractState! @index
  # ...
}

type NameContract @entity {
  contractID: BigInt! @index
  twinID: Int! @index
  state: ContractState! @index
  # ...
}

type RentContract @entity {
  contractID: BigInt! @index
  twinID: Int! @index
  nodeID: Int! @index
  state: ContractState! @index
  # ...
}

type PublicIp @entity {
  ip: String! @index
  contractId: BigInt! @index
  # ...
}

type ContractBillReport @entity {
  contractID: BigInt! @index
  # ...
}

type NruConsumption @entity {
  contractID: BigInt! @index
  # ...
}

type UptimeEvent @entity {
  nodeID: Int! @index
  # ...
}

Steps

1. Add @index directives to schema.graphql
2. yarn codegen — regenerates src/model/ with @Index_() TypeORM decorators
3. yarn build
4. yarn db:create-migration — generates a migration with CREATE INDEX statements
5. No processor reindex needed — indexes are added to existing data

Notes

• Subsquid codegen v0.3.3 supports @index (single field) and @index(fields: [...], unique: true) (composite/unique) on OBJECT | FIELD_DEFINITION
• Compatible with the typegen automation (PR feat: automate type generation with append-only version log #207) — codegen touches src/model/, typegen touches src/types/, no overlap
• The candidate list above should be validated against actual query patterns from grid-proxy and dashboard before finalizing

[sameh-farouk]

Research: How downstream components query tfchain_graphql

Consumer	Method	Key queries
tfgrid-sdk-ts (grid_client)	Direct GraphQL	Contracts by twinID+state, node by nodeID, farm count, bill reports by contractID
tfgrid-sdk-ts (playground/dashboard)	Direct GraphQL	Node contracts by deploymentData+state, uptime events by nodeID+timestamp, publicIps by ip, interfaces by node.nodeID
tfgrid-sdk-go (grid-client)	Direct GraphQL	Contracts by twinID+state (same pattern as TS SDK)
tfgrid-sdk-go (relay-cache-warmer)	Direct GraphQL	Twins paginated by id
grid-proxy	Does NOT query GraphQL — reads chain via Substrate RPC into its own DB
tfchain_client (Rust)	Does NOT query GraphQL — uses grid-proxy REST API
zos, rmb-rs, tf-kyc-verifier	No GraphQL usage

All observed filter/orderBy patterns from real code

Entity	Field	Operator	Source
Node	nodeID	_eq	grid_client nodes.ts
Node	farmID	_eq	grid_client nodes.ts
NodeContract	twinID	_eq	grid_client (TS+Go), grid-cli, user-contracts-mon, gridify, tfrobot
NodeContract	state	_in, _eq	same consumers + playground
NodeContract	contractID	_eq	grid_client consumption queries
NodeContract	nodeID	_eq	grid_client optional filter
NodeContract	deploymentData	_contains	playground (text search — B-tree index won't help)
NodeContract	numberOfPublicIPs	_eq	playground (very low cardinality — not worth indexing)
NameContract	twinID	_eq	grid_client (TS+Go)
NameContract	state	_in	grid_client (TS+Go)
NameContract	contractID	_eq	grid_client consumption
RentContract	twinID	_eq	grid_client (TS+Go)
RentContract	state	_in	grid_client (TS+Go)
RentContract	contractID	_eq	grid_client consumption
ContractBillReport	contractID	_eq	grid_client
ContractBillReport	timestamp	orderBy DESC	grid_client
UptimeEvent	nodeID	_eq	playground
UptimeEvent	timestamp	_gt + orderBy ASC	playground
PublicIp	ip	_eq	playground

Updated recommendation: 17 indexes across 6 entities

Only fields with confirmed query patterns — no speculative indexes.

NodeContract.twinID          @index
NodeContract.state           @index
NodeContract.contractID      @index
NodeContract.nodeID          @index

NameContract.twinID          @index
NameContract.state           @index
NameContract.contractID      @index

RentContract.twinID          @index
RentContract.state           @index
RentContract.contractID      @index

Node.nodeID                  @index
Node.farmID                  @index

ContractBillReport.contractID  @index
ContractBillReport.timestamp   @index

UptimeEvent.nodeID           @index
UptimeEvent.timestamp        @index

PublicIp.ip                  @index

Skipped (with reasoning)

• Twin.twinID / accountID — relay-cache-warmer uses cursor pagination on id (primary key), no filter on twinID
• Farm.farmID / twinID — only used in orderBy for count queries, not filtered
• NodeContract.deploymentData — _contains needs pg_trgm GIN index, not supported by @index
• NodeContract.numberOfPublicIPs — very low cardinality (0/1/2), index overhead not worth it
• NruConsumption, ServiceContract, Power reports, Mint/Burn/Refund — no queries observed from any consumer

[sameh-farouk]

Additional finding: Grid-proxy reads the processor DB directly

Grid-proxy (tfgrid-sdk-go/grid-proxy) does NOT query the GraphQL API. It connects directly to the processor's PostgreSQL database and runs raw SQL queries via GORM. It reads from processor tables (node, farm, twin, node_contract, name_contract, rent_contract, contract_bill_report, public_ip, etc.) and maintains its own cache tables (resources_cache, public_ips_cache) alongside them.

Grid-proxy already creates its own indexes via setup.sql:

CREATE INDEX IF NOT EXISTS idx_node_id ON public.node(node_id);
CREATE INDEX IF NOT EXISTS idx_twin_id ON public.twin(twin_id);
CREATE INDEX IF NOT EXISTS idx_farm_id ON public.farm(farm_id);

However, these are applied at grid-proxy startup — they get lost on processor DB reset/reindex and must be re-applied. Moving these into the processor's schema via @index makes them part of the migration and survive resets.

Grid-proxy SQL filter patterns (from postgres.go)

Table	Field	Query pattern
node	node_id	= ?, IN ?, NOT IN ?
node	twin_id	= ?
node	farm_id	IN ?
node	certification	= 'Certified'
farm	farm_id	= ?
farm	twin_id	= ?
farm	name	LOWER() = LOWER(), ILIKE
farm	dedicated_farm	= true
twin	twin_id	= ?
contracts (union)	twin_id	= ?
contracts (union)	contract_id	= ?
contracts (union)	state	LOWER() IN ?
contracts (union)	node_id	= ?
contracts (union)	name	= ?
contract_bill_report	contract_id	IN (?), = ?
contract_bill_report	timestamp	ORDER BY DESC

Updated recommendation: 25 indexes across 9 entities

Original 17 + 8 new from grid-proxy direct DB access. All indexes are on processor tables only — grid-proxy's own tables (resources_cache, public_ips_cache, node_gpu, etc.) are not touched.

# Contracts (original + 1 new)
NodeContract.twinID          @index
NodeContract.state           @index
NodeContract.contractID      @index
NodeContract.nodeID          @index
NameContract.twinID          @index
NameContract.state           @index
NameContract.contractID      @index
RentContract.twinID          @index
RentContract.state           @index
RentContract.contractID      @index
RentContract.nodeID          @index      # NEW — grid-proxy filters contracts by node_id

# Nodes (original + 2 new)
Node.nodeID                  @index
Node.farmID                  @index
Node.twinID                  @index      # NEW — grid-proxy filters node.twin_id
Node.certification           @index      # NEW — grid-proxy filters node.certification

# Farms (NEW — grid-proxy reads DB directly)
Farm.farmID                  @index      # NEW
Farm.twinID                  @index      # NEW
Farm.name                    @index      # NEW — grid-proxy does LOWER(name) and ILIKE
Farm.dedicatedFarm           @index      # NEW — grid-proxy filters dedicated_farm

# Twins (NEW)
Twin.twinID                  @index      # NEW — grid-proxy filters twin_id

# Billing/Uptime (unchanged)
ContractBillReport.contractID  @index
ContractBillReport.timestamp   @index
UptimeEvent.nodeID           @index
UptimeEvent.timestamp        @index

# Public IPs (unchanged)
PublicIp.ip                  @index

Backward compatibility

Adding @index only generates CREATE INDEX migration statements — no table/column changes. Grid-proxy's setup.sql indexes use CREATE INDEX IF NOT EXISTS, so they won't conflict with our migration-created indexes (different names, same columns). Both sets can coexist.