Cycle 3: Database & Data Audit

Audited: 2026-05-09 Auditor: Agent 5 (Database & Data) Scope: Schema, queries, seed scripts, data files, data loading

CRITICAL Findings

DB-CR-01: pool-risks.json downgrade/flag entries have no poolId -- all map lookups fail silently

File: data/pool-risks.json, lines 6-262 (all downgrade and flag entries) Severity: CRITICAL

The pool-risks.json file uses symbol and protocol fields but does NOT include a poolId field on any of its 15 downgrade or 14 flag entries. The loader in lib/pool-risks.ts:69 does downgradeMap.set(d.poolId, d) and at line 74 flagMap.set(f.poolId, f). Since d.poolId is undefined, all 29 entries map to the key undefined, overwriting each other. Only the last entry of each type survives, and no real pool ever matches undefined as its ID.

Result: 29 risk adjustments (downgrades for MIM, Superform, Multipli.fi, and flags for Morpho/Yearn/Lagoon vaults) are completely ignored. Users see these pools with inflated scores and no warnings.

Suggested fix: Either:

1. Add poolId (DefiLlama pool UUID) to every entry in pool-risks.json, or
2. Update lib/pool-risks.ts to also index by symbol+protocol composite key and fall back to that lookup in getPoolRiskAdjustment().

DB-CR-02: API keys generated with Math.random() -- predictable, not cryptographically secure

File: lib/api-keys.ts:84-85 Severity: CRITICAL

const random = Array.from({ length: 16 }, () =>
  "abcdefghijklmnopqrstuvwxyz0123456789"[Math.floor(Math.random() * 36)]
).join("");

Math.random() is not a CSPRNG. API keys generated this way can be predicted by an attacker who observes timing or a few generated keys. These keys gate access to the paid API tier.

Suggested fix:

import { randomBytes } from "crypto";
const random = randomBytes(12).toString("base64url"); // 16 chars, cryptographically random

DB-CR-03: API key storage is a flat JSON file with read-modify-write race condition

File: lib/api-keys.ts:52-64 (loadKeys/saveKeys) Severity: CRITICAL

function loadKeys(): ApiKeyRecord[] {
  const raw = readFileSync(DATA_PATH, "utf-8");
  return JSON.parse(raw);
}
function saveKeys(keys: ApiKeyRecord[]): void {
  writeFileSync(DATA_PATH, JSON.stringify(keys, null, 2) + "\n", "utf-8");
}

Every createKey() call reads the entire file, appends, and writes it back. Under concurrent requests (two POST /api/subscribe at the same time), one key will be lost because both reads happen before either write. No file locking, no atomic write. On a Next.js server with concurrent requests this is a real data loss scenario.

Suggested fix: Store API keys in the SQLite database (already available via lib/db). SQLite handles concurrency with WAL mode. Alternatively, use fs.appendFileSync with a JSONL format, or add file locking via proper-lockfile.

DB-CR-04: Anonymous rate limiting uses single shared key -- all anonymous users share one counter

File: lib/api-keys.ts:141 Severity: CRITICAL

const counterKey = key ?? "__anonymous__";

All unauthenticated requests share the counter key __anonymous__. After 20 total anonymous requests across ALL users, every anonymous user is rate-limited until midnight UTC. This is a denial of service for all anonymous users after a tiny number of requests.

Suggested fix: Use the client IP address as the counter key for anonymous requests:

const counterKey = key ?? `anon:${clientIp}`;

HIGH Findings

DB-HI-01: Schema missing foreign key constraint on token_compositions.component_slug

File: lib/db/schema.ts:25 Severity: HIGH

The componentSlug column in tokenCompositions has no foreign key reference to components.slug. While index.ts:14 enables foreign_keys = ON, there is no FK to enforce. Orphaned rows can exist in token_compositions referencing non-existent components. The seed script (seed-db.ts:285-286) manually checks existence, but batch scripts 1-5 use INSERT OR IGNORE without verifying the component exists first.

Suggested fix:

componentSlug: text("component_slug").notNull().references(() => components.slug),

DB-HI-02: Schema missing indices -- full table scans on every scoring query

File: lib/db/schema.ts:22-27 Severity: HIGH

token_compositions has no index on token or component_slug. Every call to getTokenStack() in scoring.ts:70-73 performs a full table scan. With scoreAll() called for every pool in the API response (potentially hundreds of pools), this means hundreds of full scans per request.

Additionally, components has no index on type or defillama_slug, though these are less critical.

Suggested fix: Add indices in schema:

import { index } from "drizzle-orm/sqlite-core";
// On tokenCompositions table:
// Add (token) index and (component_slug) index

DB-HI-03: N+1 query pattern in getTokenStack -> getComponent

File: lib/scoring.ts:66-97 Severity: HIGH

getTokenStack() queries token_compositions, then for each row calls getComponent() which runs a separate SELECT on components. For a token with 3 layers, this is 1 + 3 = 4 separate queries. Across hundreds of pools in scoreAll(), this compounds.

Suggested fix: Use a single JOIN query:

const rows = db
  .select()
  .from(schema.tokenCompositions)
  .innerJoin(schema.components, eq(schema.tokenCompositions.componentSlug, schema.components.slug))
  .where(eq(schema.tokenCompositions.token, token.toUpperCase()))
  .orderBy(schema.tokenCompositions.layerOrder)
  .all();

DB-HI-04: seed-db.ts DELETE + INSERT is not idempotent -- destroys batch1-5 data

File: scripts/seed-db.ts:113-114 Severity: HIGH

sqlite.exec("DELETE FROM token_compositions");
sqlite.exec("DELETE FROM components");

Running seed-db.ts wipes ALL data including entries inserted by batch1-5. The seed scripts have an implicit ordering dependency: seed-db.ts must run first, then batches. But nothing documents or enforces this. Running seed-db.ts after batches destroys their data. The batch scripts use INSERT OR REPLACE which is fine for re-runs, but seed-db.ts uses plain INSERT which will fail on duplicates.

Suggested fix: Use INSERT OR REPLACE in seed-db.ts (like the batch scripts do), and remove the DELETE statements. Or add a master script that runs them in order.

DB-HI-05: Stale pool-risks data never reloaded -- loaded flag is permanent

File: lib/pool-risks.ts:42-43 Severity: HIGH

let loaded = false;
// ...
function loadRiskFiles() {
  if (loaded) return;
  // ... load once ...
  loaded = true;
}

Unlike profile-loader.ts which has a 1-hour TTL cache, pool-risks data loads once and never refreshes for the lifetime of the process. If JSON files are updated on disk (e.g., after a new audit), the running server continues using stale data until restart. In a Docker deployment this may be acceptable, but for development it causes confusion.

Suggested fix: Add a TTL similar to profile-loader, or add a reloadRiskFiles() export.

DB-HI-06: Duplicate score computation logic between profile-loader.ts and seed-db.ts

File: lib/profile-loader.ts:90-163 vs scripts/seed-db.ts:20-64 Severity: HIGH

computeScore() in seed-db.ts and computeScores() in profile-loader.ts implement the same scoring algorithm but differ in two places:

1. Regulatory score: profile-loader.ts:140-143 computes a real regulatory score from profile data. seed-db.ts:61 hardcodes 5 * 0.05 (always 5).
2. Transparency source: seed-db.ts:28 uses profile.transparency.score ?? 5 (fallback to 5). profile-loader.ts:103 uses profile.transparency.score directly (no fallback -- will be NaN if missing).

This means the score in the DB (from seed) can differ from the score computed at runtime (from profile-loader). The DB score is used for the layered scoring system; the profile-loader score is used elsewhere. Score disagreements will cause inconsistent risk levels.

Suggested fix: Extract scoring to a single shared function used by both the seed script and the runtime.

MEDIUM Findings

DB-ME-01: pool-risks-delta-rwa.json uses completely different schema (protocols object, not actions)

File: data/pool-risks-delta-rwa.json Severity: MEDIUM

This file uses a protocols top-level key with per-protocol objects containing riskMultiplier, effectiveApy, riskRating, maxAllocationPct, etc. The loader in lib/pool-risks.ts only handles two formats: actions (Format 1) and pools (Format 2). This file's protocols format is silently ignored -- none of its data (Ethena, Hyperliquid, etc.) is loaded.

Suggested fix: Either convert the file to the actions format, or add Format 3 handling in loadRiskFiles().

DB-ME-02: Profile alias collision -- getAliases can overwrite unrelated profiles

File: lib/profile-loader.ts:43-48 Severity: MEDIUM

function getAliases(slug: string): string[] {
  const base = slug.replace(/-v\d+$/, "").replace(/-core-pool$/, "").replace(/-lending$/, "");
  if (base !== slug) aliases.push(base);
  return aliases;
}

If both aave-v3 and aave-v2 profiles exist, both generate the alias aave. Whichever loads second (file system order) wins. When someone queries getProfile("aave"), they get an unpredictable result.

With 96 profiles, similar collisions likely occur for other versioned protocols (e.g., uniswap-v2 and uniswap-v3 both alias to uniswap).

Suggested fix: Only alias to base if no exact slug match exists, or don't overwrite existing entries.

DB-ME-03: incidentHistory access in profile-loader.ts can throw on non-string values

File: lib/profile-loader.ts:115 Severity: MEDIUM

if (profile.security.incidentHistory === "None known" || profile.security.incidentHistory.toLowerCase().includes("none")) security += 1;

If incidentHistory is undefined or not a string, .toLowerCase() will throw. The seed script (seed-db.ts:37) uses (profile.security.incidentHistory ?? "").toLowerCase() which is defensive, but the runtime code in profile-loader.ts:115 does not have this guard.

Suggested fix: Add nullish coalescing: (profile.security.incidentHistory ?? "").toLowerCase().

DB-ME-04: Stablecoin token whitelist has duplicates

File: lib/pool-risks.ts:163-178 Severity: MEDIUM

SUSDE, SUSDS, SDAI, SGHO, SFRAX, SFRXUSD each appear twice in the STABLECOIN_TOKENS Set (lines 166 and 176-177). While Set deduplicates them, this suggests copy-paste maintenance issues and makes the list harder to maintain.

Suggested fix: Remove the duplicate entries from lines 176-177.

DB-ME-05: isStablecoinToken fuzzy matching is too aggressive

File: lib/pool-risks.ts:185 Severity: MEDIUM

if (t.startsWith("USD") || t.startsWith("DAI") || t.startsWith("USDT") || t.startsWith("USDC")) return true;

This matches tokens like USDOGE, DAIQUIRI, or any token starting with these prefixes. Since the purpose is to filter out volatile LP pairs, false positives here mean volatile pairs are NOT flagged for removal -- users could see risky non-stablecoin LPs in their results.

Suggested fix: Use the explicit whitelist only, or limit fuzzy matching to known wrapper patterns (e.g., aUSDC, cUSDT).

DB-ME-06: DB singleton not safe in serverless/edge contexts

File: lib/db/index.ts:8-18 Severity: MEDIUM

let _db: ReturnType<typeof drizzle> | null = null;
export function getDb() {
  if (!_db) { ... }
  return _db;
}

Module-level singleton works in long-lived Node.js processes but in serverless environments (Vercel, etc.), each invocation may get a fresh module context, creating a new SQLite connection per request without closing the old one. Also, the SQLite connection is never explicitly closed -- no cleanup on process exit.

Suggested fix: For production Docker deployment (as documented), this is acceptable. But add connection cleanup:

process.on("beforeExit", () => { /* close sqlite */ });

LOW Findings

DB-LO-01: seed-db.ts log message is misleading

File: scripts/seed-db.ts:233 Severity: LOW

console.log(`Inserted ${baseAssets.length} base asset/LST/restaking components`);

This logs the total array length, not the actual number inserted (some are skipped due to existing slugs). Should track and log actual insert count.

DB-LO-02: Empty catch blocks in scoring.ts swallow errors silently

File: lib/scoring.ts:57-63, 67-96 Severity: LOW

try { ... } catch { return null; }
try { ... } catch { return []; }

Database errors (corrupt DB, schema migration issues) are silently swallowed, causing all pools to fall back to heuristic scoring with no indication that the DB is broken.

Suggested fix: Log the error in catch blocks:

catch (e) { console.error("[scoring] DB lookup failed:", e); return null; }

DB-LO-03: process.cwd() for DB path is fragile

File: lib/db/index.ts:6, lib/pool-risks.ts:47, lib/profile-loader.ts:9, lib/api-keys.ts:43 Severity: LOW

All data file paths use process.cwd(). If the Next.js process is started from a different working directory (e.g., in Docker with a different WORKDIR, or via a process manager), all file lookups will fail silently. The Dockerfile should be checked to ensure WORKDIR matches.

DB-LO-04: seed-db.ts computeScore uses any type pervasively

File: scripts/seed-db.ts:20-96 Severity: LOW

All helper functions (computeScore, generatePros, generateCons, generateSummary) accept any parameter type. This eliminates TypeScript's ability to catch property access errors at build time. Since these are scripts run manually, the blast radius is limited, but type safety would prevent future regressions.

Summary

Top 3 actions by impact:

1. Fix pool-risks.json missing poolId fields (CR-01) -- 29 risk adjustments silently ignored
2. Move API keys to SQLite and use crypto.randomBytes (CR-02, CR-03)
3. Fix anonymous rate limiting to be per-IP (CR-04)