studio-db - SKILL.md Agent Skill

name: studio-db description: Query Studio deployment PostgreSQL databases for transaction debugging and analytics

Studio Database Debugging

Query and debug the genlayer_state database for transaction issues.

Connection

For cluster access setup and connection workflow, see: devexp-apps-workload/.claude/skills/studio-db/SKILL.md

Quick connect (after setup):

export PATH="/opt/homebrew/share/google-cloud-sdk/bin:/opt/homebrew/bin:$PATH"
DBHOST=$(kubectl get secret database-config -n <namespace> -o jsonpath='{.data.DBHOST}' | base64 -d)
DBPASSWORD=$(kubectl get secret database-password -n <namespace> -o jsonpath='{.data.DBPASSWORD}' | base64 -d)

echo "<SQL>" | kubectl run pg-query --rm -i --image=postgres:16 --restart=Never \
  --namespace=<namespace> --env="PGPASSWORD=$DBPASSWORD" -- \
  psql -h "$DBHOST" -p 5432 -U postgres -d genlayer_state -t

Transaction Table Schema

Primary table for debugging (transactions):

Column	Type	Description
hash	VARCHAR(66)	Primary key, tx identifier
status	ENUM	Transaction lifecycle status
from_address	VARCHAR	Sender address
to_address	VARCHAR	Recipient/contract address
nonce	INTEGER	Tx sequence number
value	INTEGER	Transaction value
type	INTEGER	Tx type (0-3)
created_at	TIMESTAMP	When tx was created
input_data	JSONB	Contract call input
data	JSONB	Transaction data payload
consensus_data	JSONB	Final consensus results
consensus_history	JSONB	Full voting history
contract_snapshot	JSONB	Contract state at execution
appealed	BOOLEAN	Was tx appealed
appeal_failed	INTEGER	Appeal failure count
appeal_undetermined	BOOLEAN	Appeal resulted in undetermined
appeal_leader_timeout	BOOLEAN	Leader timeout during appeal
appeal_validators_timeout	BOOLEAN	Validators timeout during appeal
leader_timeout_validators	JSONB	Validators that timed out
worker_id	VARCHAR	Which worker processed tx
triggered_by_hash	VARCHAR(66)	Parent tx hash (for tx chains)
blocked_at	TIMESTAMP	When/if tx got blocked

Transaction Statuses

PENDING → ACTIVATED → PROPOSING → COMMITTING → REVEALING → ACCEPTED → FINALIZED
                                                        ↘ UNDETERMINED
                                                        ↘ LEADER_TIMEOUT
                                                        ↘ VALIDATORS_TIMEOUT
                                                        ↘ CANCELED

Other Tables

current_state: Global chain state (id, data JSONB, balance)
validators: Validator configs (id, address, stake, provider, model, plugin)
llm_providers: LLM provider configurations
snapshot: Compressed state snapshots (snapshot_id, state_data, transaction_data)

Common Debugging Queries

Find transaction by hash

SELECT hash, status, from_address, to_address, created_at,
       consensus_data, worker_id
FROM transactions WHERE hash = '0x...';

List stuck/problematic transactions

SELECT hash, status, created_at, worker_id
FROM transactions
WHERE status IN ('UNDETERMINED', 'LEADER_TIMEOUT', 'VALIDATORS_TIMEOUT')
ORDER BY created_at DESC LIMIT 20;

Transaction status distribution

SELECT status, COUNT(*) as count
FROM transactions
GROUP BY status ORDER BY count DESC;

Recent transactions

SELECT hash, status, from_address, created_at
FROM transactions ORDER BY created_at DESC LIMIT 10;

Transactions with appeals

SELECT hash, status, appealed, appeal_undetermined, appeal_failed,
       appeal_leader_timeout, appeal_validators_timeout
FROM transactions WHERE appealed = true
ORDER BY created_at DESC LIMIT 20;

Find triggered transactions (tx chains)

SELECT t1.hash as parent, t2.hash as child, t2.status
FROM transactions t1
JOIN transactions t2 ON t2.triggered_by_hash = t1.hash
WHERE t1.hash = '0x...';

Transactions by worker

SELECT worker_id, status, COUNT(*)
FROM transactions
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY worker_id, status
ORDER BY worker_id, count DESC;

Check consensus history for a tx

SELECT hash, status,
       jsonb_pretty(consensus_history) as history
FROM transactions WHERE hash = '0x...';

Write Operations

CAUTION: Before any UPDATE/DELETE:

First SELECT to verify affected rows
Ask user for explicit confirmation
Prefer dev/stg for testing

Reset stuck transaction

-- Verify first
SELECT hash, status FROM transactions
WHERE hash = '0x...' AND status = 'UNDETERMINED';

-- Then update (after confirmation)
UPDATE transactions SET status = 'PENDING' WHERE hash = '0x...';

Full Schema

Models: backend/database_handler/models.py Migrations: backend/database_handler/migration/versions/ Tx processor: backend/database_handler/transactions_processor.py