> For the complete documentation index, see [llms.txt](https://docs.trover.tech/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.trover.tech/engineering/verify-2026-09-04-db-guardrails.md). # Verify — database guardrails, caching, and retention ## No migration this round Schema is unchanged. Services that changed: **api + worker**. `docker-compose.production.yml` changed, so the recreate must pick up the new environment. ```bash cd ~/trover git pull --ff-only origin main docker compose -f docker-compose.yml -f docker-compose.production.yml build api worker docker compose -f docker-compose.yml -f docker-compose.production.yml up -d --force-recreate api worker ``` ## What happened `dex.trover.tech/nfts/robinhood/argonauts` would not load. The page rendered in 0.97s; the API behind it took 17–20s and `/trending` timed out at 40s. The cause was not the NFT code. Postgres had **27 connections wedged for over seventeen minutes** on `stock_market_snapshots` — 2.9GB, of which \~2GB is TOASTed JSON — because a `findFirst` with no `select` was detoasting three JSON columns to read a price. The pool was starved, so the API returned 530 and the host would not complete an SSH handshake, while the Vercel frontends stayed at 200. That split — frontend fine, origin gone — is the signature of a saturated database, not a broken app. ## The guardrail that was missing **Nothing bounded query runtime.** A 846-second query should be killed by the database, not discovered by a person. Now, in `docker-compose.production.yml`: | service | statement\_timeout | connection\_limit | | ------- | --------------------------------------------------------------- | ----------------- | | api | **15s** — an HTTP request has no business outliving that | 15 | | worker | **120s** — scans and rebuilds legitimately run longer | 10 | | migrate | **none, deliberately** — a `CREATE INDEX` here took \~6 minutes | — | Confirm both took effect: ```bash docker compose -f docker-compose.yml -f docker-compose.production.yml exec -T api \ sh -c 'node -e "const{PrismaClient}=require(\"@prisma/client\");const p=new PrismaClient();p.\$queryRaw\`SHOW statement_timeout\`.then(r=>console.log(r)).finally(()=>p.\$disconnect())"' ``` Expect `15s` for api, `2min` for worker. Then confirm nothing long-lived survives: ```sql SELECT max(EXTRACT(EPOCH FROM (now()-query_start)))::int AS max_age_s FROM pg_stat_activity WHERE datname = current_database() AND state = 'active'; ``` Was **1027**. Expect comfortably under 120. ## Our own regressions **The ownership walk was the worst query in the database.** Its candidate selection joined and grouped `nft_market_events` (2M rows, 5.3GB) **every 30 seconds** and had reached 846 seconds. The candidate pool is now built at most once every 5 minutes and cached in Redis at `trover:nft-owners:candidates:`; each tick takes a rotating slice. 120 aggregations an hour became 12. **`/v1/nft-market/trending`** ran two aggregations over the same table per call with only a `cache-control` header, so every miss re-aggregated. Now Redis cached for 15s, same pattern as `/collections/:slug`. ## Read amplification Three queries were fetching JSON they never read: * `/collections/:slug/items` fetched **every** active listing with its full Seaport `providerPayload`, unbounded, then kept the cheapest per token and read seven fields. Now `take: 2000` and column-selected. * `nft-realtime.ts` refreshed 50 collections **every 10 seconds forever**, detoasting `providerPayload` to read an id and an address. * `syncTrackedNftMarkets` did the same every 60s. ## Retention Before: the job ran **once a day at 04:00 UTC** and 7 of its 9 statements only nulled a payload — rows were never deleted. Measured: `stock_market_snapshots` had **607,124 rows since 2026-07-26, 479,286 still holding `rawPayload`**. Now: * `RAW_PAYLOAD_RETENTION_DAYS` **30 → 7**. * Cleanup runs **hourly**. * `nft_market_events.providerPayload` is scrubbed by age. That table is the largest we have and nothing had ever scrubbed it, because the job keys on a `rawPayloadExpiresAt` column this model does not have. The event itself — price, parties, block — is normalised into real columns and untouched. * Rows older than **90 days** are deleted from `stock_market_snapshots`, `price_health_snapshots`, `nft_source_observations`, and **published** `nft_market_outbox` rows. **Never deleted:** `nft_market_events` (the tape every NFT figure derives from), and anything under `trade_*`, `claim_*` or `treasury_*` — a ledger with a retention policy is not a ledger. Deletes run in batches of 5,000, at most 10 batches per table per run, and **outside** the job's transaction. One transactional delete over hundreds of thousands of rows holds locks for its whole duration, which is the exact failure this round exists to prevent; a backlog drains over several hourly runs instead. Watch it work: ```sql SELECT pg_size_pretty(pg_total_relation_size('stock_market_snapshots')) AS size, count(*) AS rows, count(*) FILTER (WHERE "rawPayload" IS NOT NULL) AS with_raw FROM stock_market_snapshots; ``` Baseline: **2895 MB / 607,124 rows / 479,286 with\_raw.** Both counts should fall on each hourly run. The first few runs will not clear the whole backlog — that is the batching working as intended. ```bash docker compose -f docker-compose.yml -f docker-compose.production.yml logs --tail 4000 worker \ | grep -E "Retention (scrubbed|deleted)" ``` ## The reported bug ```bash curl -s -o /dev/null -w "%{http_code} %{time_total}s\n" \ 'https://api.trover.tech/v1/nft-market/trending?chain=robinhood&window=24h&limit=5' curl -s -o /dev/null -w "%{http_code} %{time_total}s\n" \ 'https://api.trover.tech/v1/nft-market/collections/argonauts?chain=robinhood' ``` Targets: `/trending` **200 under 2s** (was timing out at 40s); `/collections` \~0.8s warm. `curl -sI` on a second call to each should show `x-trover-cache: hit`. Then load `https://dex.trover.tech/nfts/robinhood/argonauts` and confirm the terminal populates in a couple of seconds. ## The ownership walk must not stall The candidate cache is the risk: if it were wrong the walk would spin on the same collections. ```sql SELECT count(*) FILTER (WHERE "nextBlock" > 53000000) AS at_head, count(*) AS cursors FROM stabledex_indexer_cursors WHERE source LIKE 'nft-owners:%'; ``` `at_head` was 7 this morning and 54 by evening; it must keep climbing. **Trust this number, not the `caughtUp` field in the walk's log line** — that read healthy through three separate bugs today while nothing was finishing. ## Known gaps * \~90 more unbounded queries and \~35 more detoasting reads are catalogued but untouched. They are real and none is currently causing harm; sweeping them is a large diff with proportionate regression risk. * `nft_market_events` should eventually be time-partitioned. That is the right answer for a 5.3GB append-only tape and the wrong thing to attempt during an incident. * Three unbounded in-process `Map` caches with no eviction: `portfolio-routes.ts:49`, `stabledex-holders.ts:26`, `stabledex-routes.ts:25`. *** ## Addendum — `/trending` covering index (there IS a migration after all) The guardrails worked exactly as intended and, in doing so, exposed the next problem honestly: `/trending` stopped hanging and started returning **500 at 17s** — the API's new 15s `statement_timeout` killing a query that genuinely could not finish. Profiled on production: ``` Bitmap Index Scan on nft_market_events_eventType_occurredAt_idx (actual rows=114996) Heap Blocks: exact=12544 lossy=11450 Buffers: shared hit=850 read=71332 (~557 MB from disk) Execution Time: 64249 ms ``` The index finds the rows; the aggregate then needs `collectionId` and `priceNative`, which are not in it, so all 115k matches required a heap fetch. The `lossy` heap blocks are the tell — the bitmap outgrew `work_mem` and degraded to re-checking whole pages. `packages/db/migrations/20260904200000_nft_event_trending_covering` adds: ```sql CREATE INDEX IF NOT EXISTS "nft_market_events_trending_covering_idx" ON "nft_market_events" ("eventType", "occurredAt") INCLUDE ("collectionId", "priceNative", "accepted"); ``` **This takes a write lock on a 5.3GB table for roughly 6 minutes** — the previous index on it took that long. `CONCURRENTLY` is unavailable because Prisma runs migrations in a transaction. Both chain scanners are cursor-driven and resume on their own. Run it when a short ingest pause is acceptable. **Prisma cannot express `INCLUDE`**, so this index lives only in the migration and is deliberately absent from `schema.prisma`; there is a comment on the model saying so. Do not resolve the drift by dropping it. ```bash docker compose -f docker-compose.yml -f docker-compose.production.yml build api worker migrate docker compose -f docker-compose.yml -f docker-compose.production.yml run --rm migrate docker compose -f docker-compose.yml -f docker-compose.production.yml up -d --force-recreate api worker ``` Confirm it is used, not merely present: ```sql EXPLAIN SELECT e."collectionId", count(*) FROM nft_market_events e WHERE e."eventType"='sale' AND e."occurredAt" >= now() - interval '48 hours' GROUP BY 1; ``` Expect **`Index Only Scan using nft_market_events_trending_covering_idx`**, not a Bitmap Heap Scan. Then: ```bash curl -s -o /dev/null -w "%{http_code} %{time_total}s\n" \ 'https://api.trover.tech/v1/nft-market/trending?chain=robinhood&window=24h&limit=5' ``` Target: **200 under 2s.** It was 500 at 17s, and 64s unbounded before that. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.trover.tech/engineering/verify-2026-09-04-db-guardrails.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.