code/cl8y-dex-terraclassic

Fork 0

Security: no runbook queries or SQL snippets for identifying suspicious wallets, pairs, or tokens [SEC-G04] #437

New issue

Closed

opened 2026-06-29 16:11:16 +00:00 by totdking · 12 comments

totdking commented

2026-06-29 16:11:16 +00:00

(Migrated from gitlab.com)

No description provided.

totdking commented

2026-06-29 16:12:03 +00:00

(Migrated from gitlab.com)

Summary

No runbook includes indexer API calls or SQL snippets that help an operator identify a suspicious wallet, pair, or token by transaction pattern. The emergency-commands.md has blacklist_check commands that confirm whether a specific address is already blacklisted (a post-identification step), but provides no queries for discovering suspicious activity in the first place. The incident triage template has a "Tx samples" checklist item but no query to surface them.

What Was Checked

docs/runbooks/emergency-commands.md sections 3, 5, 7: blacklist_check queries confirm an address is blacklisted. These are confirmation queries, not discovery queries.
docs/templates/incident-dex-indexer.md triage section "Tx samples": prompts operator to look for "Failed tx codes, out-of-gas, hook reverts" but gives no query or command to find them.
docs/runbooks/blacklist-decision.md: classification criteria require "on-chain evidence" but give no query for finding it.
Indexer API endpoints documented in docs/integrators.md: reviewed by grep -- no runbook links a specific API call for suspicious-activity discovery.

Expected (per checklist)

A runbook must include indexer API calls or SQL snippets for at minimum: top-volume traders in a recent window; wallets with unusually many failed txs; pairs with abnormal swap count or reserve change; tokens with recent blacklist check hits. These allow an operator to surface suspicious activity without needing to construct ad hoc queries under pressure.

Actual

The only wallet/pair/token queries documented are post-identification confirmation checks (is this address already blacklisted?). No discovery queries exist in any runbook.

Evidence

docs/runbooks/emergency-commands.md: blacklist_check queries confirm known addresses, no discovery step
docs/templates/incident-dex-indexer.md: "Tx samples" bullet has no associated query or command
docs/runbooks/blacklist-decision.md: requires pre-existing evidence, provides no discovery query

Suggested Fix

Add a "Suspicious activity queries" section to docs/templates/incident-dex-indexer.md or docs/runbooks/emergency-commands.md with: (1) indexer API call to list top traders by volume in a short recent window; (2) SQL or API call for wallets with above-threshold failed tx count in a rolling window; (3) indexer API call for pairs with above-average swap count or reserve delta; (4) SQL for blacklist check hits logged by the compliance endpoint. Include example output and escalation steps.

Verification Checklist

A runbook includes queries for suspicious wallet discovery (high failed tx count, anomalous volume)
A runbook includes queries for suspicious pair or token discovery (reserve delta, swap count spike)
Queries are linked from the incident template triage section

Labels

security, pre-launch

Cc: @PlasticDigits

### Summary No runbook includes indexer API calls or SQL snippets that help an operator identify a suspicious wallet, pair, or token by transaction pattern. The `emergency-commands.md` has `blacklist_check` commands that confirm whether a specific address is already blacklisted (a post-identification step), but provides no queries for discovering suspicious activity in the first place. The incident triage template has a "Tx samples" checklist item but no query to surface them. --- ### What Was Checked - `docs/runbooks/emergency-commands.md` sections 3, 5, 7: `blacklist_check` queries confirm an address is blacklisted. These are confirmation queries, not discovery queries. - `docs/templates/incident-dex-indexer.md` triage section "Tx samples": prompts operator to look for "Failed tx codes, out-of-gas, hook reverts" but gives no query or command to find them. - `docs/runbooks/blacklist-decision.md`: classification criteria require "on-chain evidence" but give no query for finding it. - Indexer API endpoints documented in `docs/integrators.md`: reviewed by grep -- no runbook links a specific API call for suspicious-activity discovery. --- ### Expected (per checklist) A runbook must include indexer API calls or SQL snippets for at minimum: top-volume traders in a recent window; wallets with unusually many failed txs; pairs with abnormal swap count or reserve change; tokens with recent blacklist check hits. These allow an operator to surface suspicious activity without needing to construct ad hoc queries under pressure. --- ### Actual The only wallet/pair/token queries documented are post-identification confirmation checks (is this address already blacklisted?). No discovery queries exist in any runbook. --- ### Evidence - `docs/runbooks/emergency-commands.md`: blacklist_check queries confirm known addresses, no discovery step - `docs/templates/incident-dex-indexer.md`: "Tx samples" bullet has no associated query or command - `docs/runbooks/blacklist-decision.md`: requires pre-existing evidence, provides no discovery query --- ### Suggested Fix Add a "Suspicious activity queries" section to `docs/templates/incident-dex-indexer.md` or `docs/runbooks/emergency-commands.md` with: (1) indexer API call to list top traders by volume in a short recent window; (2) SQL or API call for wallets with above-threshold failed tx count in a rolling window; (3) indexer API call for pairs with above-average swap count or reserve delta; (4) SQL for blacklist check hits logged by the compliance endpoint. Include example output and escalation steps. --- ### Verification Checklist - [ ] A runbook includes queries for suspicious wallet discovery (high failed tx count, anomalous volume) - [ ] A runbook includes queries for suspicious pair or token discovery (reserve delta, swap count spike) - [ ] Queries are linked from the incident template triage section --- ### Labels `security`, `pre-launch` Cc: @PlasticDigits

totdking commented

2026-06-29 16:15:51 +00:00

(Migrated from gitlab.com)

mentioned in issue #381

PlasticDigits commented

2026-06-30 02:22:55 +00:00

(Migrated from gitlab.com)

mentioned in commit efc63a7b6b

mentioned in commit efc63a7b6b3f7da6666a0ad0afa4b2c8ac59b168

PlasticDigits commented

2026-06-30 02:23:08 +00:00

(Migrated from gitlab.com)

mentioned in merge request !969

Brouie commented

2026-06-30 02:59:06 +00:00

(Migrated from gitlab.com)

Verified #437 docs + ran every documented query live (issue-437-suspicious-activity-queries).

Guard: check_suspicious_activity_queries_docs.py -> OK; verify-issue-437.sh -> PASS 5 / FAIL 0.

Ran the runbook's queries against the live indexer + LCD:

wallet discovery: §1 top-volume traders SQL + /traders/leaderboard (top wallet 9981 trades / 1.02T vol, desc), §2a LCD failed-wasm aggregate (100 txs scanned, 0 failed = clean idle chain), §2b hook-warnings SQL (runs, 0 rows)
pair/token discovery: §3b swap-count spike 1h-vs-24h (runs), §4a liquidity adds/removes (desc, 24 adds top), §4b stale-reserve-vs-swaps (runs), §5c tokens-active (OPAL 3,265 rows desc)
§5a blacklist-check on a real wallet -> 200; §3a per-pair stats -> trade_count/volume_quote/price_change real

Every snippet executes and all tables/columns resolve against the live schema. The empty results (hook warnings, 1h spike, stale-reserve) are correct for an idle chain, not query errors; volume_usd is null (no USD oracle on localnet) which the runbook already calls out.

All three checklist items covered, queries linked from the incident-template triage. Docs + indexer/LCD only. Good to close from my side — @PlasticDigits merge !969 + close.

Verified #437 docs + ran every documented query live (issue-437-suspicious-activity-queries). Guard: check_suspicious_activity_queries_docs.py -> OK; verify-issue-437.sh -> PASS 5 / FAIL 0. Ran the runbook's queries against the live indexer + LCD: - wallet discovery: §1 top-volume traders SQL + /traders/leaderboard (top wallet 9981 trades / 1.02T vol, desc), §2a LCD failed-wasm aggregate (100 txs scanned, 0 failed = clean idle chain), §2b hook-warnings SQL (runs, 0 rows) - pair/token discovery: §3b swap-count spike 1h-vs-24h (runs), §4a liquidity adds/removes (desc, 24 adds top), §4b stale-reserve-vs-swaps (runs), §5c tokens-active (OPAL 3,265 rows desc) - §5a blacklist-check on a real wallet -> 200; §3a per-pair stats -> trade_count/volume_quote/price_change real Every snippet executes and all tables/columns resolve against the live schema. The empty results (hook warnings, 1h spike, stale-reserve) are correct for an idle chain, not query errors; volume_usd is null (no USD oracle on localnet) which the runbook already calls out. All three checklist items covered, queries linked from the incident-template triage. Docs + indexer/LCD only. Good to close from my side — @PlasticDigits merge !969 + close.

PlasticDigits commented

2026-06-30 05:39:54 +00:00

(Migrated from gitlab.com)

mentioned in commit 8de9d708e1

mentioned in commit 8de9d708e1e81796eeaf799d4ef81517d858e72b

PlasticDigits commented

2026-06-30 06:13:44 +00:00

(Migrated from gitlab.com)

mentioned in commit 2f966c048f

mentioned in commit 2f966c048f914abbc74c56c66634a3babd8ca6b6

PlasticDigits commented

2026-06-30 06:17:21 +00:00

(Migrated from gitlab.com)

mentioned in merge request !970

PlasticDigits commented

2026-06-30 07:24:13 +00:00

(Migrated from gitlab.com)

mentioned in commit 255e3630eb

mentioned in commit 255e3630eb62e713604396fc2ec7d074fdb69651

PlasticDigits commented

2026-06-30 07:25:07 +00:00

(Migrated from gitlab.com)

mentioned in merge request !972

PlasticDigits commented

2026-06-30 07:59:36 +00:00

(Migrated from gitlab.com)

mentioned in commit 4757f1a2de

mentioned in commit 4757f1a2de040e36d30f0d281d71f864a0ef33b0

PlasticDigits commented

2026-06-30 08:16:23 +00:00

(Migrated from gitlab.com)

Verification complete — PASS (no repo changes)

Verified GitLab #437 / SEC-G04 on main @ 4757f1a2 (includes issue-437-suspicious-activity-queries + LCD query= param fix).

Acceptance checklist

Item	Result	How verified
Runbook includes suspicious wallet discovery (failed tx + anomalous volume)	PASS	`docs/runbooks/suspicious-activity-queries.md` §1 (`/api/v1/traders/leaderboard`, `traders` SQL), §2a (LCD `query=message.module='wasm'` + `code != 0` aggregate), §2b (`hook_events` SQL)
Runbook includes suspicious pair/token discovery (reserve delta, swap spike)	PASS	Same runbook §3 (`/api/v1/pairs`, `.../stats`, `swap_events` spike SQL), §4 (`liquidity_events`, `pair_reserves` stale mirror SQL), §5c (token swap rollup SQL)
Queries linked from incident template Triage	PASS	`docs/templates/incident-dex-indexer.md` links to runbook on Tx samples and Suspicious activity discovery rows; `docs/runbooks/blacklist-decision.md` links discovery runbook

Automated guards

make check-suspicious-activity-queries-docs  → OK
make verify-issue-437                        → PASS 5 / FAIL 0

Live query smoke (LocalTerra + indexer after `make setup-cloud-localterra --fresh --skip-build`)

Endpoints: INDEXER_URL=http://127.0.0.1:3001, LCD=http://127.0.0.1:1317, deploy seed 60 swaps.

Section	Result	Notes
§1 leaderboard API + SQL	PASS	Top wallet `terra1x46…` — 60 trades; `volume_24h` 0 (no USD oracle on localnet; runbook documents this)
§2a LCD failed wasm	PASS	100 txs scanned, 0 failed
§2b hook warnings SQL	PASS	Query runs, 0 rows (expected on clean seed)
§3a pairs API + per-pair stats	PASS	`trade_count`/`volume_quote`/`price_change_pct` populated; `volume_usd` null (localnet)
§3b swap-count spike SQL	PASS	Rows returned (pairs with 3 swaps in 1h window)
§4a liquidity events SQL	PASS	`add` events listed (top pair 2 adds)
§4b stale-reserve SQL	PASS	Query runs, 0 rows (no stale mirror on fresh deploy)
§5a blacklist-check	PASS	HTTP 200, `blocked: false` for top trader
§5c tokens-active SQL	PASS	ONYX/JADE/RUBY etc. with swap row counts

All snippets execute; tables/columns resolve against live schema. Empty or null fields match idle/localnet expectations documented in the runbook, not query errors.

Cross-links / skills

skills/AGENTS_SUSPICIOUS_ACTIVITY_QUERIES.md present
scripts/check_suspicious_activity_queries_docs.py invariant covers SEC-G04 markers + incident/blacklist cross-links

Closing — criteria satisfied on main.

## Verification complete — PASS (no repo changes) Verified GitLab **#437** / **SEC-G04** on `main` @ `4757f1a2` (includes `issue-437-suspicious-activity-queries` + LCD `query=` param fix). ### Acceptance checklist | Item | Result | How verified | |------|--------|--------------| | Runbook includes suspicious **wallet** discovery (failed tx + anomalous volume) | **PASS** | `docs/runbooks/suspicious-activity-queries.md` §1 (`/api/v1/traders/leaderboard`, `traders` SQL), §2a (LCD `query=message.module='wasm'` + `code != 0` aggregate), §2b (`hook_events` SQL) | | Runbook includes suspicious **pair/token** discovery (reserve delta, swap spike) | **PASS** | Same runbook §3 (`/api/v1/pairs`, `.../stats`, `swap_events` spike SQL), §4 (`liquidity_events`, `pair_reserves` stale mirror SQL), §5c (token swap rollup SQL) | | Queries linked from incident template **Triage** | **PASS** | `docs/templates/incident-dex-indexer.md` links to runbook on **Tx samples** and **Suspicious activity discovery** rows; `docs/runbooks/blacklist-decision.md` links discovery runbook | ### Automated guards ``` make check-suspicious-activity-queries-docs → OK make verify-issue-437 → PASS 5 / FAIL 0 ``` ### Live query smoke (LocalTerra + indexer after `make setup-cloud-localterra --fresh --skip-build`) Endpoints: `INDEXER_URL=http://127.0.0.1:3001`, `LCD=http://127.0.0.1:1317`, deploy seed `60 swaps`. | Section | Result | Notes | |---------|--------|-------| | §1 leaderboard API + SQL | **PASS** | Top wallet `terra1x46…` — 60 trades; `volume_24h` 0 (no USD oracle on localnet; runbook documents this) | | §2a LCD failed wasm | **PASS** | 100 txs scanned, 0 failed | | §2b hook warnings SQL | **PASS** | Query runs, 0 rows (expected on clean seed) | | §3a pairs API + per-pair stats | **PASS** | `trade_count`/`volume_quote`/`price_change_pct` populated; `volume_usd` null (localnet) | | §3b swap-count spike SQL | **PASS** | Rows returned (pairs with 3 swaps in 1h window) | | §4a liquidity events SQL | **PASS** | `add` events listed (top pair 2 adds) | | §4b stale-reserve SQL | **PASS** | Query runs, 0 rows (no stale mirror on fresh deploy) | | §5a blacklist-check | **PASS** | HTTP 200, `blocked: false` for top trader | | §5c tokens-active SQL | **PASS** | ONYX/JADE/RUBY etc. with swap row counts | All snippets execute; tables/columns resolve against live schema. Empty or null fields match idle/localnet expectations documented in the runbook, not query errors. ### Cross-links / skills - `skills/AGENTS_SUSPICIOUS_ACTIVITY_QUERIES.md` present - `scripts/check_suspicious_activity_queries_docs.py` invariant covers SEC-G04 markers + incident/blacklist cross-links Closing — criteria satisfied on `main`.

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-06-30 08:16:24 +00:00