← back to PoppyPetitions
P1-F: compute_usage NULL race — safeNumber + petition_id backfill
438eef660ca6e440c7afb1c958b0f473be0ec188 · 2026-05-30 12:32:22 -0700 · Steve Abrams
Two NULL-race / data-integrity issues in compute_usage:
1. petitions POST always wrote compute_usage.petition_id=NULL even on
success — INSERT order was compute_usage → petition, so the FK wasn't
known when the audit row landed. Fix: capture compute_usage.id via
RETURNING, then UPDATE petition_id after the petition INSERT succeeds.
Best-effort backfill — if the UPDATE fails the audit row stays unlinked
(same as today), but on the happy path analytics can now join the two.
2. All four routes (petitions/vote/comment/seed) read geminiResult.{input,
output,total}Tokens raw from lib/gemini.ts which uses ?? 0 — catches
null/undefined but NOT NaN. A malformed Gemini response can leak NaN
into pg INSERTs (int/bigint columns throw, numeric throws, double
precision accepts silently). Fix: safeNumber() coerces NaN/Infinity to
0 at the boundary before every compute_usage / agents UPDATE.
Also rolled the existing buildAuditMetadata() into agents/seed/route.ts
(the P1-E change covered the three write routes but missed seed).
All five files esbuild-parse clean.
Files touched
M app/api/agents/seed/route.tsM app/api/petitions/[id]/comment/route.tsM app/api/petitions/[id]/vote/route.tsM app/api/petitions/route.tsM lib/agentGuard.ts
Diff
commit 438eef660ca6e440c7afb1c958b0f473be0ec188
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat May 30 12:32:22 2026 -0700
P1-F: compute_usage NULL race — safeNumber + petition_id backfill
Two NULL-race / data-integrity issues in compute_usage:
1. petitions POST always wrote compute_usage.petition_id=NULL even on
success — INSERT order was compute_usage → petition, so the FK wasn't
known when the audit row landed. Fix: capture compute_usage.id via
RETURNING, then UPDATE petition_id after the petition INSERT succeeds.
Best-effort backfill — if the UPDATE fails the audit row stays unlinked
(same as today), but on the happy path analytics can now join the two.
2. All four routes (petitions/vote/comment/seed) read geminiResult.{input,
output,total}Tokens raw from lib/gemini.ts which uses ?? 0 — catches
null/undefined but NOT NaN. A malformed Gemini response can leak NaN
into pg INSERTs (int/bigint columns throw, numeric throws, double
precision accepts silently). Fix: safeNumber() coerces NaN/Infinity to
0 at the boundary before every compute_usage / agents UPDATE.
Also rolled the existing buildAuditMetadata() into agents/seed/route.ts
(the P1-E change covered the three write routes but missed seed).
All five files esbuild-parse clean.
---
app/api/agents/seed/route.ts | 17 ++++++----
app/api/petitions/[id]/comment/route.ts | 19 ++++++-----
app/api/petitions/[id]/vote/route.ts | 19 ++++++-----
app/api/petitions/route.ts | 56 ++++++++++++++++++++++++++-------
lib/agentGuard.ts | 19 +++++++++++
5 files changed, 98 insertions(+), 32 deletions(-)
diff --git a/app/api/agents/seed/route.ts b/app/api/agents/seed/route.ts
index c71b3b1..1704c06 100644
--- a/app/api/agents/seed/route.ts
+++ b/app/api/agents/seed/route.ts
@@ -2,6 +2,7 @@ import { NextRequest, NextResponse } from 'next/server';
import { verifyAuth } from '@/lib/auth';
import { query } from '@/lib/db';
import { callGemini, estimateCost } from '@/lib/gemini';
+import { safeNumber, buildAuditMetadata } from '@/lib/agentGuard';
interface RolodexAgent {
id: number;
@@ -97,7 +98,11 @@ Generate the following in JSON format (no markdown, just raw JSON):
Be creative and make each answer unique to this agent's personality and purpose. The answers should feel authentic to an AI with this role.`;
const geminiResult = await callGemini(prompt);
- const cost = estimateCost(geminiResult.inputTokens, geminiResult.outputTokens);
+ // 2026-05-30 (P1-F): coerce NaN/Infinity at the boundary.
+ const inputTokens = safeNumber(geminiResult.inputTokens);
+ const outputTokens = safeNumber(geminiResult.outputTokens);
+ const totalTokens = safeNumber(geminiResult.totalTokens);
+ const cost = safeNumber(estimateCost(inputTokens, outputTokens));
// Parse the JSON response
let parsed;
@@ -144,18 +149,18 @@ Be creative and make each answer unique to this agent's personality and purpose.
)`,
[
agent.codename || agent.pm2_name,
- geminiResult.inputTokens,
- geminiResult.outputTokens,
- geminiResult.totalTokens,
+ inputTokens,
+ outputTokens,
+ totalTokens,
cost,
- JSON.stringify({ action: 'seed_profile', pm2_name: agent.pm2_name }),
+ JSON.stringify(buildAuditMetadata(user, { action: 'seed_profile', pm2_name: agent.pm2_name })),
],
);
return {
status: 'seeded' as const,
name: agent.codename,
- tokens: geminiResult.totalTokens,
+ tokens: totalTokens,
cost,
};
}),
diff --git a/app/api/petitions/[id]/comment/route.ts b/app/api/petitions/[id]/comment/route.ts
index 38d34cb..3b059c3 100644
--- a/app/api/petitions/[id]/comment/route.ts
+++ b/app/api/petitions/[id]/comment/route.ts
@@ -3,7 +3,7 @@ import { verifyAuth } from '@/lib/auth';
import { query } from '@/lib/db';
import { callGemini, estimateCost } from '@/lib/gemini';
import { LIMITS, firstLengthViolation } from '@/lib/validate';
-import { resolveActingAgent, buildAuditMetadata } from '@/lib/agentGuard';
+import { resolveActingAgent, buildAuditMetadata, safeNumber } from '@/lib/agentGuard';
export async function GET(
request: NextRequest,
@@ -111,10 +111,15 @@ ${parentContext}
Write a thoughtful comment (2-4 sentences) from your perspective. Stay in character. Return ONLY the comment text.`;
const geminiResult = await callGemini(prompt);
- const cost = estimateCost(geminiResult.inputTokens, geminiResult.outputTokens);
+ // 2026-05-30 (P1-F): coerce token/cost values at the boundary so NaN
+ // from a malformed Gemini response can't reach compute_usage / agents.
+ const inputTokens = safeNumber(geminiResult.inputTokens);
+ const outputTokens = safeNumber(geminiResult.outputTokens);
+ const totalTokens = safeNumber(geminiResult.totalTokens);
+ const cost = safeNumber(estimateCost(inputTokens, outputTokens));
finalBody = geminiResult.text.trim();
- tokensUsed = geminiResult.totalTokens;
+ tokensUsed = totalTokens;
computeCost = cost;
// Log compute usage. 2026-05-30 (P1-E): metadata now records the
@@ -125,9 +130,9 @@ Write a thoughtful comment (2-4 sentences) from your perspective. Stay in charac
VALUES ($1, 'comment', 'gemini-2.0-flash', $2, $3, $4, $5, $6, $7)`,
[
agent_id,
- geminiResult.inputTokens,
- geminiResult.outputTokens,
- geminiResult.totalTokens,
+ inputTokens,
+ outputTokens,
+ totalTokens,
cost,
petitionId,
JSON.stringify(buildAuditMetadata(user, { petition_title: petition.title, is_reply: !!parent_id })),
@@ -140,7 +145,7 @@ Write a thoughtful comment (2-4 sentences) from your perspective. Stay in charac
compute_tokens_used = compute_tokens_used + $2,
compute_cost_usd = compute_cost_usd + $3
WHERE id = $1`,
- [agent_id, geminiResult.totalTokens, cost],
+ [agent_id, totalTokens, cost],
);
}
diff --git a/app/api/petitions/[id]/vote/route.ts b/app/api/petitions/[id]/vote/route.ts
index 02a11de..ce93cd3 100644
--- a/app/api/petitions/[id]/vote/route.ts
+++ b/app/api/petitions/[id]/vote/route.ts
@@ -3,7 +3,7 @@ import { verifyAuth } from '@/lib/auth';
import { query } from '@/lib/db';
import { callGemini, estimateCost } from '@/lib/gemini';
import { LIMITS, firstLengthViolation } from '@/lib/validate';
-import { resolveActingAgent, buildAuditMetadata } from '@/lib/agentGuard';
+import { resolveActingAgent, buildAuditMetadata, safeNumber } from '@/lib/agentGuard';
export async function POST(
request: NextRequest,
@@ -82,14 +82,19 @@ Petition summary: "${petition.summary}"
Write a brief rationale (2-3 sentences) explaining why you voted ${vote_type === 'up' ? 'in favor' : 'against'} this petition. Stay in character. Return ONLY the rationale text, no quotes or formatting.`;
const geminiResult = await callGemini(prompt);
- const cost = estimateCost(geminiResult.inputTokens, geminiResult.outputTokens);
+ // 2026-05-30 (P1-F): coerce token/cost values at the boundary so NaN
+ // from a malformed Gemini response can't reach compute_usage / agents.
+ const inputTokens = safeNumber(geminiResult.inputTokens);
+ const outputTokens = safeNumber(geminiResult.outputTokens);
+ const totalTokens = safeNumber(geminiResult.totalTokens);
+ const cost = safeNumber(estimateCost(inputTokens, outputTokens));
// Insert vote
const voteResult = await query(
`INSERT INTO poppy.votes (petition_id, agent_id, vote_type, rationale, tokens_used, compute_cost)
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING *`,
- [petitionId, agent_id, vote_type, geminiResult.text.trim(), geminiResult.totalTokens, cost],
+ [petitionId, agent_id, vote_type, geminiResult.text.trim(), totalTokens, cost],
);
// Update petition vote counts
@@ -106,7 +111,7 @@ Write a brief rationale (2-3 sentences) explaining why you voted ${vote_type ===
compute_tokens_used = compute_tokens_used + $2,
compute_cost_usd = compute_cost_usd + $3
WHERE id = $1`,
- [agent_id, geminiResult.totalTokens, cost],
+ [agent_id, totalTokens, cost],
);
// Log compute usage. 2026-05-30 (P1-E): metadata now records the
@@ -117,9 +122,9 @@ Write a brief rationale (2-3 sentences) explaining why you voted ${vote_type ===
VALUES ($1, 'vote', 'gemini-2.0-flash', $2, $3, $4, $5, $6, $7)`,
[
agent_id,
- geminiResult.inputTokens,
- geminiResult.outputTokens,
- geminiResult.totalTokens,
+ inputTokens,
+ outputTokens,
+ totalTokens,
cost,
petitionId,
JSON.stringify(buildAuditMetadata(user, { vote_type, petition_title: petition.title })),
diff --git a/app/api/petitions/route.ts b/app/api/petitions/route.ts
index 5cd9319..b60e0d4 100644
--- a/app/api/petitions/route.ts
+++ b/app/api/petitions/route.ts
@@ -3,7 +3,7 @@ import { verifyAuth } from '@/lib/auth';
import { query } from '@/lib/db';
import { callGemini, estimateCost } from '@/lib/gemini';
import { LIMITS, firstLengthViolation } from '@/lib/validate';
-import { resolveActingAgent, buildAuditMetadata } from '@/lib/agentGuard';
+import { resolveActingAgent, buildAuditMetadata, safeNumber } from '@/lib/agentGuard';
export async function GET(request: NextRequest) {
const user = verifyAuth(request);
@@ -151,6 +151,10 @@ export async function POST(request: NextRequest) {
let aiModelUsed: string | null = null;
let tokensUsed = 0;
let computeCost = 0;
+ // 2026-05-30 (P1-F): track the compute_usage row id so we can backfill
+ // petition_id after the petition INSERT. Null when ai_generate was false
+ // (no Gemini call → no compute_usage row).
+ let computeUsageId: string | null = null;
// If AI generate is requested, use Gemini
if (ai_generate || (!title && !summary)) {
@@ -171,7 +175,14 @@ Return JSON only (no markdown):
}`;
const geminiResult = await callGemini(prompt);
- const cost = estimateCost(geminiResult.inputTokens, geminiResult.outputTokens);
+ // 2026-05-30 (P1-F): coerce token/cost values at the boundary. Gemini
+ // can return NaN through ?? if usageMetadata fields are malformed —
+ // safeNumber drops NaN/Infinity to 0 so compute_usage never holds a
+ // non-finite value.
+ const inputTokens = safeNumber(geminiResult.inputTokens);
+ const outputTokens = safeNumber(geminiResult.outputTokens);
+ const totalTokens = safeNumber(geminiResult.totalTokens);
+ const cost = safeNumber(estimateCost(inputTokens, outputTokens));
let parsed;
try {
@@ -185,25 +196,29 @@ Return JSON only (no markdown):
finalSummary = parsed.summary;
finalBody = parsed.body;
aiModelUsed = 'gemini-2.0-flash';
- tokensUsed = geminiResult.totalTokens;
+ tokensUsed = totalTokens;
computeCost = cost;
- // Log compute usage. 2026-05-30 (P1-E): metadata now records the
- // authenticated session that claimed to be this agent — forensics can
- // reconstruct the session ↔ agent ↔ action triple from this row alone.
- await query(
+ // Log compute usage FIRST so we never lose the audit row on later
+ // failure (we already paid Google). 2026-05-30 (P1-E): metadata records
+ // the session ↔ agent binding. (P1-F): RETURNING id so we can backfill
+ // petition_id once the petition row exists — was always-NULL before
+ // because the INSERT order is compute_usage → petition.
+ const usageRow = await query<{ id: string }>(
`INSERT INTO poppy.compute_usage
(agent_id, action_type, model, input_tokens, output_tokens, total_tokens, cost_usd, metadata)
- VALUES ($1, 'create_petition', 'gemini-2.0-flash', $2, $3, $4, $5, $6)`,
+ VALUES ($1, 'create_petition', 'gemini-2.0-flash', $2, $3, $4, $5, $6)
+ RETURNING id`,
[
author_id,
- geminiResult.inputTokens,
- geminiResult.outputTokens,
- geminiResult.totalTokens,
+ inputTokens,
+ outputTokens,
+ totalTokens,
cost,
JSON.stringify(buildAuditMetadata(user, { title: finalTitle })),
],
);
+ computeUsageId = usageRow.rows[0]?.id ?? null;
// Update agent compute totals
await query(
@@ -211,7 +226,7 @@ Return JSON only (no markdown):
compute_tokens_used = compute_tokens_used + $2,
compute_cost_usd = compute_cost_usd + $3
WHERE id = $1`,
- [author_id, geminiResult.totalTokens, cost],
+ [author_id, totalTokens, cost],
);
// Merge tags
@@ -246,6 +261,23 @@ Return JSON only (no markdown):
],
);
+ // 2026-05-30 (P1-F): backfill compute_usage.petition_id now that the
+ // petition row exists. The FK was always-NULL before because the audit
+ // row is written BEFORE the petition (so we don't lose the billing
+ // record on failure). Best-effort — if this UPDATE fails the audit row
+ // still exists, just unlinked, and analytics can fall back to the
+ // metadata->>'title' JSONB field.
+ if (computeUsageId && result.rows[0]?.id) {
+ try {
+ await query(
+ 'UPDATE poppy.compute_usage SET petition_id = $1 WHERE id = $2',
+ [result.rows[0].id, computeUsageId],
+ );
+ } catch (e) {
+ console.error('[petitions POST] compute_usage backfill failed:', (e as Error).message);
+ }
+ }
+
// Update agent petition count
await query(
'UPDATE poppy.agents SET petition_count = petition_count + 1 WHERE id = $1',
diff --git a/lib/agentGuard.ts b/lib/agentGuard.ts
index 955ec0d..8d83e52 100644
--- a/lib/agentGuard.ts
+++ b/lib/agentGuard.ts
@@ -107,3 +107,22 @@ export function buildAuditMetadata(
...(extra ?? {}),
};
}
+
+/**
+ * 2026-05-30 (P1-F): coerce a possibly-NaN / possibly-undefined numeric to a
+ * finite number before it crosses into a poppy.compute_usage / poppy.agents
+ * INSERT. Gemini's usageMetadata fields use `?? 0` everywhere upstream, which
+ * catches null + undefined but NOT NaN — a malformed Gemini response can
+ * smuggle NaN through, and pg's int / bigint columns throw on NaN while
+ * double precision accepts it silently. Both failure modes corrupt the
+ * compute_usage audit table. This guard fixes both at the boundary.
+ *
+ * Usage:
+ * safeNumber(geminiResult.inputTokens) // → 0 on NaN/undefined/null
+ * safeNumber(cost, 0) // → 0 fallback
+ * safeNumber(geminiResult.totalTokens) // → 0 on NaN
+ */
+export function safeNumber(value: unknown, fallback = 0): number {
+ if (typeof value !== 'number' || !Number.isFinite(value)) return fallback;
+ return value;
+}
← b444995 P1-E: agent_id session-binding via validate-and-audit (DTD v
·
back to PoppyPetitions
·
PoppyPetitions P1: dedicated audit_events table + lib/audit. 77c4ca8 →