db-debug

name

db-debug

description

Read-only debugging of the production database via the scripts/db-debug.sh CLI (the structured /gs/debug endpoint). Use to inspect financial anomalies, total-balance history (FinancialDataLog), liquidity balances, an asset's balance history, referral chains or trees, compare two balance-log snapshots, inspect one asset's balance structure, or run an ad-hoc read-only query against any allowlisted table with a structured JSON DTO. No raw SQL — read-only by construction, never writes.

Database debug (read-only)

Read-only forensics against the production database through the scripts/db-debug.sh CLI. The script authenticates itself (DEBUG address + signature from the local .env) and posts a structured JSON query to the /gs/debug endpoint. There is no raw SQL: the request body is a DebugQueryDto describing table, select items, an optional where-tree, group/order/limit, which the service compiles to a parameter-bound SELECT via TypeORM. Writes and DDL are not expressible, and every identifier must appear in the per-table allowlist (DebugAllowedColumns in src/subdomains/generic/gs/dto/gs.dto.ts) — the source of truth, which drifts per migration.

By default this targets production — the local .env sets DEBUG_API_URL (defaults to https://api.dfx.swiss/v1); point it at another environment by setting DEBUG_API_URL in .env.

How to run

Run the script from the repository root (it is executable and resolves its own .env):

scripts/db-debug.sh <mode> [args]

Run exactly this script. It is read-only by construction — the endpoint only accepts the structured query DTO, so writes and DDL cannot be expressed. Never modify the script to attempt otherwise; if a request implies a write, refuse and explain why.

Modes

Command	Purpose
(no args)	default query (recent assets)
--anomalies [N]	FinancialDataLog rows with valid = false (default 20)
--balance [N]	recent total-balance history (default 20)
--stats	log statistics by system / subsystem / severity
--asset-history <id|Blockchain/Name> [N]	balance history for one asset (default 10)
--referral-chain <userDataId>	referral chain upward
--referral-tree <userDataId>	full referral tree with status
--get <table> [col1,col2,...] [limit]	ad-hoc: fetch columns (default id,created,updated) from any allowlisted table (default limit 100)
--query <json|@file|->	ad-hoc: POST an arbitrary structured DTO (inline JSON, @file, or - to read the DTO from stdin)
--help	full usage

Ad-hoc queries

Use --get for the common case (columns from one table) and --query for anything that needs a where-tree, aggregate, jsonb path, group by, or order by. --query validates that its payload is well-formed JSON (via jq) and fails loudly before contacting the endpoint.

# Latest 100 rows of a table, default columns id,created,updated
scripts/db-debug.sh --get user_data

# Specific columns and a limit
scripts/db-debug.sh --get buy_crypto id,created,amountInEur 50

# Arbitrary DTO inline
scripts/db-debug.sh --query '{"table":"asset","select":[{"kind":"column","column":"id"},{"kind":"column","column":"name"}],"where":{"kind":"leaf","column":"blockchain","op":"=","value":"Ethereum"},"orderBy":[{"column":"id","direction":"DESC"}],"limit":20}'

# Arbitrary DTO from a file, or from stdin
scripts/db-debug.sh --query @/tmp/query.json
cat query.json | scripts/db-debug.sh --query -

Liquidity balances

liquidity_balance has no dedicated mode, but --get reads it directly. It is a snapshot table (one row per asset, updated in place; updated is the last refresh). The endpoint has no JOINs, so resolve asset names with a separate asset query. Current snapshot:

scripts/db-debug.sh --get liquidity_balance id,assetId,amount,availableAmount,isDfxOwned,updated 100

Balance forensics (companion scripts)

Three companion scripts share the same .env auth and the same log / FinancialDataLog source, for investigating total-balance steps (see the valid / suspicious-step heuristic in reference.md). A <log_id> is the log.id of a FinancialDataLog entry — find candidates with --balance or --anomalies above. All three are read-only — they only issue structured /gs/debug queries (no writes); sum-asset-balances.sh also reads the asset table to resolve IDs.

Command	Purpose
scripts/compare-balance-logs.sh <log_id_1> <log_id_2>	diff two snapshots: plus / minus / total deltas plus the top asset changes ranked by CHF impact
scripts/inspect-asset-balance.sh <log_id> <asset_id>	full plus / minus breakdown for one asset in a log entry (incl. the liquidity sub-structure)
scripts/sum-asset-balances.sh <log_id> <financial_type>	sum plus / minus / net CHF across assets of one financialType (per-asset plusBalance.total shown in the breakdown)

Composing a query

Table names are snake_case; column names are camelCase and case-sensitive. Both must appear in the table's DebugAllowedColumns entry — anything not listed there is unreachable. id, created and updated exist on every allowlisted table. select needs at least one item; each is a column, a jsonb path (only on columns in jsonbColumns, currently just log.message), or an aggregate. where is an optional tree of leaf / and / or / not nodes; limit is required.

Before composing a non-trivial query, read reference.md in this skill folder for the full DTO shape, the where-tree operators and caps, the FinancialDataLog / balancesTotal structure, and the liquidity_balance schema. It is not loaded until read.