Stellar Index status

# [SEV-3] Account cohort graph 500s after v0.91.0 deploy ## What happened Reconstructed after the fact: this incident was never opened live — the only record of it was a CHANGELOG paragraph and the commit message of `bdaecccc5`. This file backfills the SEV runbook procedure that should have run on 2026-09-18. The 10:31Z deploy cleared v0.91.0 to r1 even though the deploy's own ClickHouse evidence step published a `::warning::` naming `stellar.asset_month_usd_prices` and its staging twin as absent on the target host. The config-apply gate at the time could not distinguish "nobody asked whether this surface exists" from "asked, and the host said no" — an operator's `config_acknowledged=true` cleared both alike, so the missing-table warning did not block the release. v0.91.0's cohort flows read (`internal/storage/clickhouse/ account_cohort_rollup.go`, `readCohortFlows`) `LEFT JOIN`s `asset_month_usd_prices` to attach each month's then-price. ClickHouse raises rather than returning nulls when the joined table does not exist, so every `GET /v1/accounts/{g}/graph/cohort` request — the whole route, not just the priced fields — answered 500 in ~0.33s (down from a normal ~1.5s 200) until the missing DDL was applied by hand. ## Impact - **Endpoints affected:** `GET /v1/accounts/{g}/graph/cohort` (both `relation=created` and `relation=sponsored`) — 100% of requests to this one route. - **Customers affected:** anyone reading the explorer's account cohort panel or calling the endpoint directly. No other `/v1/accounts/*` routes were affected. - **Data correctness:** no data served — 500, not stale or wrong data. - **Workaround:** none; the route was unusable until remedy. ## Timeline All times UTC. | Time (UTC) | Update | |-----------|--------| | 10:31 | Deploy clears v0.91.0 to r1. The ClickHouse evidence step's own `::warning::` names `stellar.asset_month_usd_prices` and `…_staging` as absent on the host; the config-apply gate's acknowledgement path clears the release anyway. | | 10:31–~11:00 | Every `GET /v1/accounts/{g}/graph/cohort` request answers 500. No paged alert or status-page update was raised live; the gap was found only in this reverification sweep. | | ~11:00 | `stellar.asset_month_usd_prices` (+ staging twin) DDL applied by hand on the host, matching the operator runbook (`account_cohort_rollup.sql`). Route recovers. | | 11:07 | `bdaecccc5` lands: the evidence step now publishes a `refuted` output beside `applied`, and the config-apply gate refuses an acknowledgement for any surface the host proved unapplied — closing the class of gap that let this deploy through. | ## What we did Applied the missing `asset_month_usd_prices` DDL by hand to restore the route, then landed `bdaecccc5` so a future deploy cannot clear a surface the evidence step already proved absent on the host. Separately (GH-1078), `internal/storage/clickhouse/account_cohort_rollup.go`'s cohort-flows read no longer fails the whole route when that table is absent: it falls back to serving flows without then-prices instead of LEFT JOIN-erroring the request closed, so a future gap in this optional enrichment table degrades rather than 500s. ## Postmortem Not written at the time; this file is the first record. A full postmortem was not produced for this SEV-3 and none is scheduled retroactively — the two concrete remediations (gate fix + fail-open read) are recorded above and in `bdaecccc5` / GH-1078. ## Lessons learned - A deploy gate that can be cleared by an acknowledgement needs to distinguish "no evidence was gathered" from "evidence says no" — conflating them let a known-missing table ship. - An optional enrichment table joined into a base read should degrade, not fail the request: the base cohort-flows rows do not depend on `asset_month_usd_prices` existing. - This incident surfaced only via a documentation/backlog reverification sweep, not the SEV runbook. `/v1/incidents` and the postmortem index are only as complete as the humans who remember to write to them — the deploy gate refusing the unsafe path (`bdaecccc5`) is the higher-leverage fix.

# [SEV-2] Customer emails and API keys written to the log store unredacted ## Identification Two of our edge logging paths wrote sensitive values in full to journald/Loki instead of redacting them: - `GET /v1/account/admin/lookup?email=...` — the staff email look-up route logged the customer's email address in the request URL. - Any request carrying `X-API-Key` — our name for a customer's live API key — was logged complete and replayable to anyone with Grafana read access, because Caddy's built-in credential redaction has no knowledge of that header name. No customer action was required; this was an internal exposure into our own log store, not a public leak. ## Impact - **Endpoints affected:** `GET /v1/account/admin/lookup`, and any authenticated request that used `X-API-Key` rather than the `Authorization: Bearer` form. - **Customers affected:** any customer whose email was looked up by staff, or who authenticated with `X-API-Key`, while the unredacted filter was live. - **Data correctness:** no data was lost or corrupted; the exposure was read-access to log lines by staff with existing Grafana access, not a public disclosure. - **Workaround:** none needed — see remediation below. ## Timeline All times UTC. | Time (UTC) | Update | |-----------|--------| | 2026-09-02 00:55 | **Identified.** Commit `7843f129` fixes the edge redaction filter (rebuilt allow-list-shaped instead of enumerated) and deletes `X-API-Key` from logged headers. | | 2026-09-02 | **Monitoring.** Caddy filter and the ansible template regenerated; boot-time credential formatting in `internal/config/validate.go` fixed alongside it (same class of defect). | | 2026-09-03 | **Resolved.** Loki's 30-day retention window scanned in full (1,069,477 lines): zero unredacted `email=` lines remained, so the exposure window had already rolled off naturally. No targeted purge was necessary. | ## What we did Commit `7843f129` replaced the enumerated Caddy query-redaction filter with an allow-list-shaped one (`?<redacted>` on the whole query string), and added `X-API-Key`, `X-Reason` and `Referer` to the set of headers stripped before logging. We then verified the existing 30-day Loki retention window against the fix: zero unredacted `email=` lines across the full scanned corpus, so nothing required manual purging. ## Post-exposure remediation The redaction fix and the retention-window verification closed the **forward-looking** and **retroactive-scan** legs of this incident. Operational follow-ups: - [x] File the customer-facing incident record — this file, filed retroactively per [`docs/operations/sev-playbook.md` §6.5](../../../docs/operations/sev-playbook.md#65-credentialpii-exposure-incidents). - [x] **Targeted API-key rotation / customer notification — no customer in scope.** Measured on the production database 2026-10-02: of the keys created before `7843f129`, exactly one authenticated a request in the exposure window. It belongs to the internal `launch-verify-agent` account (free tier, created 2026-08-13 by the launch verification run through self-serve registration, no billing email, no users) and was used once, at creation. No `X-API-Key` customer was active in the window, so there is no one to notify. This checkbox is a structural closure gate: `scripts/ci/lint-docs.sh` §15 fails CI once this incident is 30 days old if it is still unchecked. ## Postmortem Draft: [`docs/operations/postmortems/2026-09-02-log-store-credential-pii-exposure.md`](../../../docs/operations/postmortems/2026-09-02-log-store-credential-pii-exposure.md). The front-matter `postmortem:` field is set once it is ratified (`docs/operations/sev-playbook.md` §6.1).

# [SEV-2] ClickHouse log-channel wedge fills root → Postgres crashes ## What happened During heavy SDEX completeness recovery + re-verify work (sustained ClickHouse query load), r1's 49 GB root filesystem filled to 100%. That tipped ClickHouse into a **self-sustaining log-spam loop**: CH's server log lived on the root FS (`<log>/var/log/clickhouse-server/ clickhouse-server.log`, 1000M × 10 rotation). On a full root the size-based rotation throws, and CH's async logging channel wedges — emitting `Cannot log message in OwnAsyncSplitChannel channel` + `Poco::RotateBySizeStrategy::mustRotate` stack traces at **~80,000 lines/minute** to journald → rsyslog → `/var/log/syslog`. That re-filled root faster than it could be cleared (~3.8 GB/min), and a full root crashed **Postgres twice** (it couldn't write its log; `postgresql@15-main` failed to restart until root was freed). The loop was driven by the *live indexer's* ongoing CH activity, not just the operator's recovery load — so it persisted after the heavy jobs were killed and survived three rounds of freeing root (the wedged channel does not reset just because space reappears). ## Why this surface PG's data is on the 9 TB ZFS pool (`data/postgres`), which was never short of space. But PG writes its **log** to `/var/log/postgresql` on the 49 GB root, and 17 GB of that root is a swap file (`/swap_f1209`), leaving little headroom. When root hit 0, PG's log write failed and the cluster went down — a ZFS-tier health check would never have caught it. ## Timeline (UTC) - **2026-06-10 22:09** — root hits 100% under recovery load; PG log stops; cluster crashes. - **2026-06-11 ~00:11–09:26** — diagnosis: root 100% (not the ZFS data vol); 3.7 GB PG log + 9 GB syslog; PG won't restart on full root. - **09:26** — operator-authorized truncation of `syslog` + PG log frees root to 9.8 GB; PG restarted; `log_min_duration_statement=-1`, `log_statement=none`, `log_connections=off` applied (PG-side spam). - **09:29** — root re-fills to 2.6 GB in minutes; CH still at ~20k lines/min — identified `OwnAsyncSplitChannel` wedge; freeing root alone does **not** reset it. - **09:31** — CH `<log>` moved to the ZFS pool (`config.d/zzz-logpath.xml`) + `systemctl restart clickhouse-server`. Spam → 0; root stable at 9.8 GB free. ## Root cause + remediation Root cause: ClickHouse's server log on the chronically-tight root FS, combined with a full root, wedges CH's async log channel into a spam loop. Applied + codified (`configs/ansible/roles/archival-node/tasks/ 15-log-discipline.yml`): - **CH logs moved to the ZFS pool** (`/var/lib/clickhouse/logs/`) via `config.d/zzz-logpath.xml` — the pool never fills, so the channel can't wedge. This is the source fix. - **rsyslog filter** `if $programname == "clickhouse-server" then stop` — keeps any future CH journald flood off `/var/log/syslog` (belt-and-braces; `journalctl -u clickhouse-server` still has it). - **PG logging quieted** — `log_min_duration_statement=-1` + `log_statement=none` + `log_connections=off` (slow-query + connection logging was the PG-side contributor under load). ## Follow-ups - [~] **Root FS is chronically too small (49 GB, 17 GB of it swap).** The real durable fix is moving `/var/log` (and ideally the swap) onto the ZFS pool, or resizing root. This is the third root-fill incident in the class (2026-05-10, 2026-05-13, 2026-06-11). *Accepted debt 2026-07-12*: live filesystem surgery on the production host is operator-gated — tracked in the r1 operator queue (notes/ROADMAP.md). Interim mitigations shipped instead: CH logs on ZFS (above), rsyslog filter, and the wrapper watchdog below. - [x] **Heavy ClickHouse jobs must run with a root-disk watchdog.** Done 2026-07-12: `/usr/local/sbin/run-heavy-job.sh` (mandatory for ALL heavy one-shots since 2026-07-05, not just CH jobs) now runs a background watchdog that stops the job's systemd scope when root free space drops below 2 GiB — codified in the archival-node ansible role. - [x] CH log path + rsyslog filter codified in the archival-node role. ## Lessons learned - A full root is not just "logs pile up" — it can wedge a *service's* logging into a feedback loop that fills root faster than you can clear it. Freeing space does not reset a wedged async channel; the service must be restarted. - The disk-fill alert class (`stellarindex_node_root_disk_*`, shipped after 2026-05-10) is correct but has no pager on the dev host, so it was discovered via downstream symptoms (PG down) again. - Operator recovery load on shared infra (CH) can trigger service-level failure modes; heavy jobs need disk guardrails.

# [SEV-2] /v1/price returning 404 on rewritten pairs — Redis writes blocked by disk-full ## What happened For approximately 9 hours on 2026-05-10, `/v1/price` returned 404 `price-not-found` for any pair whose VWAP is served from the Redis cache (rewritten / triangulated / stablecoin-proxy paths). Pairs that fall through to the `prices_1m` CAGG directly continued to serve normally. Root cause: r1's root filesystem (`/dev/md1`, 49 GB) reached 100% capacity at 03:39 UTC, dominated by 35 GB of stale logs in `/var/log` (8.6 GB rotated syslog, 7.6 GB postgres, 5.5 GB galexie verify history, 3.5 GB systemd journal, 2.2 GB one-time WASM-audit stderr captures). Redis was unable to write its periodic RDB snapshot. With the default `stop-writes-on-bgsave-error yes` configuration, every subsequent `SET` returned `MISCONF Redis is configured to save RDB snapshots, but it's currently unable to persist to disk`. The aggregator's VWAP cache writes failed for every pair, every refresh cycle (~15 WARNs per 30 s observed in the aggregator log). `flags.stale` did NOT fire — the aggregator was running, just unable to publish — so the customer-visible signal was a 404 on specific pairs (most user-noticed: `XLM/USD` via the stablecoin proxy path) rather than a stale flag. ## Why this surface Pairs that landed in `prices_1m` directly (CAGG-served closed-bucket VWAPs) kept serving because the read path doesn't depend on Redis for those. Only pairs computed at aggregator runtime and cached in Redis — anything triangulated, anything proxied through a stablecoin peg, the rolling-window tip surface — went dark. ## Timeline (UTC) - **03:39** — Redis log first records `Write error saving DB on disk(rdbSaveRio): No space left on device`. Aggregator WARN cadence picks up on the next refresh tick. - **2026-05-09 → 2026-05-10 (overnight)** — `/v1/price` 404s noticed during diagnostic audit of the X/fiat:USD coverage family. - **12:50** — Operator on-call ran disk audit; found root FS at 100% with 35 GB of stale logs. - **12:53** — `journalctl --vacuum-size=200M` freed 3.3 GB → root at 93%. - **12:55** — Truncated `/var/log/syslog.1` (8.6 GB) and removed `/var/log/wasm-history-*.stderr` (2.2 GB) → root at 70%, 15 GB free. - **12:57** — `redis-cli BGSAVE` succeeded; cleared `stop-writes-on-bgsave-error` block; aggregator WARN cadence dropped to 0. - **12:57:20** — `/v1/price?asset=native&quote=USDC-classic` returned 200 OK with fresh observation. Customer-visible recovery. ## Customer impact Approximately 9 hours of degraded service on rewritten / triangulated price pairs. Direct DEX-pair queries (e.g. `?asset=native&quote=USDC-classic`) were unaffected once the cache was rebuilt; the flagship `?asset=native&quote=fiat:USD` query that depends on the stablecoin proxy fallback would have stayed degraded until a future binary rebuild deploys (rc.38 ships the proxy fix that was implemented during the incident, fixed in 6505934b5). ## Root cause + remediation Two layered causes: 1. **Logrotate retention was too long (or compress wasn't set)** — the 8.6 GB rotated syslog.1 should have aged off / compressed weeks ago. Suspect `/etc/logrotate.d/rsyslog` is misconfigured. 2. **No disk-usage alert at the warning threshold** — the operator should have been paged at 85% used, not had to discover at 100% via downstream symptoms. Operational follow-ups: - [~] Audit `/etc/logrotate.d/rsyslog` and `postgresql-common` — rsyslog half is codified in `configs/ansible/roles/ archival-node/tasks/15-log-discipline.yml` (cap 100M × 7 rotations, compress, plus journald cap 500M). The `postgresql-common` half hasn't been audited end-to-end yet. Acknowledged 2026-06-10 as deferred low-priority hygiene — the critical remediation (root-disk alert below) is the real fix. - [x] Add Prometheus alert rule on `node_filesystem_avail_bytes / node_filesystem_size_bytes < 0.15` for `/` — shipped in commit 922ebd72431142931250d93dcac4e1967930e3dc as `stellarindex_node_root_disk_warning` (< 20% for 10m, P2) and `stellarindex_node_root_disk_full` (< 10% for 1m, P1). Catches the gap that `stellarindex_timescale_disk_full` only watched `/var/lib/postgresql`. - [~] Move WASM-audit one-time stderr captures to a dedicated dir (acknowledged 2026-06-10 — deferred cosmetic hygiene) - [x] Document the recovery sequence (this incident notes done): `docs/operations/runbooks/cache.md` — runbook landed in commit 7843ec721dfcafa3c510d614d3bb61d97e8023f6. ## Lessons learned - Cache-only Redis being treated like a primary store made a log-volume issue cascade into a customer-visible API outage. Consider `stop-writes-on-bgsave-error no` for our cache-only Redis once the disk-monitoring alert is in place — the trade-off is fast recovery vs. snapshot durability across Redis restart, and our cache is reproducible from `prices_1m` on demand. - Aggregator WARNs fired loudly but the customer-visible signal was 404 on specific pairs. Operator-side monitoring should alert on aggregator WARN rate (not just service-up status).

<!-- Customer-facing incident post. Tone, detail level, and cadence per docs/operations/sev-playbook.md §5. --> # [SEV-3] Indexer dropping ~1% of trades — Postgres lock-table-full ## Identification Some trades arriving on `coinbase`, `binance`, `kraken`, and `sdex` were not landing in `prices_1m` or `trades` for a window earlier today. The error rate was small (~1% of trades) and bursty — clusters of `pq: out of shared memory (53200)` rejections from the storage layer. No customer-visible API errors; price endpoints remained available throughout. ## Cause TimescaleDB's chunk-based storage takes one shared-memory lock per chunk that an INSERT touches. Postgres's lock table is sized as `max_locks_per_transaction × max_connections`. We were running on the Postgres default `max_locks_per_transaction = 64`, with `max_connections = 200` — a 12,800-entry lock table. Under concurrent ingest from 11 exchange-class sources, the indexer's INSERTs occasionally needed to take more locks than the table could hold, and Postgres returned SQLSTATE 53200 ("out of shared memory"). The affected INSERTs were dropped (logged + retried at the source layer; some events past their freshness window were abandoned). ## Resolution Bumped `max_locks_per_transaction` from 64 → 256 in `/etc/postgresql/15/main/postgresql.conf` and restarted Postgres. The lock table is now 51,200 entries (4× headroom). Brief ~5s downtime during the restart; indexer/aggregator/api all reconnected on their own without intervention. ## Verification Monitored for 5 minutes post-restart: - 53200 errors: 0 (was ~10/hour) - `prices_1m` write rate: 887 rows in 5 min — backfill flowing - All three services back to `active` within 6 seconds of restart ## What customers need to do Nothing. The window of dropped trades was small and the affected data is already being re-ingested by the live + backfill paths; prices_1m buckets converge as the rolling-window fills. ## What we changed - `postgresql.conf`: `max_locks_per_transaction = 256` - Backup of pre-change conf at `postgresql.conf.bak.20260507-003904` on r1 for rollback. ## What we'll do next - Track lock-table headroom in Prometheus (`pg_locks_count / (max_locks_per_transaction * max_connections)`) and page when above 70% saturation. - Apply the same `max_locks_per_transaction` change to R2 + R3 ahead of their cutover so the same incident doesn't recur.