← back to Norma Platform
fix(api)+test: close Cody-gate gaps — all 14 uuid=text casts + org-scoped smoke + deterministic fixture
39264f38457d013d55deb6448d30cacf40a9698a · 2026-08-06 10:41:49 -0700 · Steve
Cody (contrarian gate) VERIFIED my first pass fixed only 1 of 14 identical
$N::text-forces-uuid=text casts, and that the 500-only suite MISSED analytics/charts
(safeQuery swallows the error → HTTP 200 + _queryErrors:5 → blank dashboard for any
org-scoped admin, live on :7400).
- Fixed the remaining 13 casts: analytics/charts (5), pipeline approve(2)/dispatch(4)/
schedule(1), library PATCH+DELETE (2). Now $N::uuid IS NULL OR org_id = $N::uuid.
- api-smoke: admin requests now send x-org-id (exercise the tenant-scoped path where the
casts actually fire — null-org fixtures left them dormant); a 200 body with
_queryErrors>0 is now a hard failure (catches swallowed errors the 500-check missed).
- test-instance.sh: fixture preflight ensures idempotent migrations 024+025 on boot and
FAILS FAST + LOUD if required schema is still absent, so a recreated sdcc_test can't
silently regress the suite (Cody risk #3).
Verified on :7411: analytics/charts org-scoped _queryErrors 5→0; api-smoke 0 crashes +
0 swallowed across 195 GET routes; security-regression 20/20.
Files touched
M app/api/analytics/charts/route.tsM app/api/library/[id]/route.tsM app/api/pipeline/[id]/approve/route.tsM app/api/pipeline/[id]/dispatch/route.tsM app/api/pipeline/[id]/schedule/route.tsM scripts/test-instance.shM tests/api-smoke.mjs
Diff
commit 39264f38457d013d55deb6448d30cacf40a9698a
Author: Steve <steve@designerwallcoverings.com>
Date: Thu Aug 6 10:41:49 2026 -0700
fix(api)+test: close Cody-gate gaps — all 14 uuid=text casts + org-scoped smoke + deterministic fixture
Cody (contrarian gate) VERIFIED my first pass fixed only 1 of 14 identical
$N::text-forces-uuid=text casts, and that the 500-only suite MISSED analytics/charts
(safeQuery swallows the error → HTTP 200 + _queryErrors:5 → blank dashboard for any
org-scoped admin, live on :7400).
- Fixed the remaining 13 casts: analytics/charts (5), pipeline approve(2)/dispatch(4)/
schedule(1), library PATCH+DELETE (2). Now $N::uuid IS NULL OR org_id = $N::uuid.
- api-smoke: admin requests now send x-org-id (exercise the tenant-scoped path where the
casts actually fire — null-org fixtures left them dormant); a 200 body with
_queryErrors>0 is now a hard failure (catches swallowed errors the 500-check missed).
- test-instance.sh: fixture preflight ensures idempotent migrations 024+025 on boot and
FAILS FAST + LOUD if required schema is still absent, so a recreated sdcc_test can't
silently regress the suite (Cody risk #3).
Verified on :7411: analytics/charts org-scoped _queryErrors 5→0; api-smoke 0 crashes +
0 swallowed across 195 GET routes; security-regression 20/20.
---
app/api/analytics/charts/route.ts | 10 ++++----
app/api/library/[id]/route.ts | 4 +--
app/api/pipeline/[id]/approve/route.ts | 4 +--
app/api/pipeline/[id]/dispatch/route.ts | 8 +++---
app/api/pipeline/[id]/schedule/route.ts | 2 +-
scripts/test-instance.sh | 31 +++++++++++++++++++++++
tests/api-smoke.mjs | 45 +++++++++++++++++++++++----------
7 files changed, 76 insertions(+), 28 deletions(-)
diff --git a/app/api/analytics/charts/route.ts b/app/api/analytics/charts/route.ts
index e250304..4b251c1 100644
--- a/app/api/analytics/charts/route.ts
+++ b/app/api/analytics/charts/route.ts
@@ -83,7 +83,7 @@ export async function GET(request: NextRequest) {
SELECT platform, COUNT(*)::int as count,
COALESCE(SUM(signature_count), 0)::int as total_signatures
FROM petitions
- WHERE ($1::text IS NULL OR org_id = $1)
+ WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
GROUP BY platform ORDER BY count DESC
`, [orgId]),
@@ -92,7 +92,7 @@ export async function GET(request: NextRequest) {
SELECT category, COUNT(*)::int as count,
COALESCE(SUM(signature_count), 0)::int as total_signatures
FROM petitions
- WHERE category IS NOT NULL AND ($1::text IS NULL OR org_id = $1)
+ WHERE category IS NOT NULL AND ($1::uuid IS NULL OR org_id = $1::uuid)
GROUP BY category ORDER BY count DESC LIMIT 10
`, [orgId]),
@@ -100,7 +100,7 @@ export async function GET(request: NextRequest) {
safeQuery('topPetitions', `
SELECT title, platform, COALESCE(signature_count, 0)::int as signature_count
FROM petitions
- WHERE ($1::text IS NULL OR org_id = $1)
+ WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
ORDER BY signature_count DESC NULLS LAST
LIMIT 8
`, [orgId]),
@@ -110,7 +110,7 @@ export async function GET(request: NextRequest) {
SELECT source, COUNT(*)::int as count,
COALESCE(SUM(amount), 0)::numeric as total_amount
FROM donations
- WHERE ($1::text IS NULL OR org_id = $1)
+ WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
GROUP BY source ORDER BY total_amount DESC
`, [orgId]),
@@ -122,7 +122,7 @@ export async function GET(request: NextRequest) {
COALESCE(SUM(amount), 0)::numeric as total_amount
FROM donations
WHERE donated_at >= NOW() - INTERVAL '12 months'
- AND ($1::text IS NULL OR org_id = $1)
+ AND ($1::uuid IS NULL OR org_id = $1::uuid)
GROUP BY TO_CHAR(donated_at, 'YYYY-MM')
ORDER BY month ASC
`, [orgId]),
diff --git a/app/api/library/[id]/route.ts b/app/api/library/[id]/route.ts
index 100edbd..4220b79 100644
--- a/app/api/library/[id]/route.ts
+++ b/app/api/library/[id]/route.ts
@@ -71,7 +71,7 @@ export async function PATCH(request: NextRequest, context: RouteContext) {
values.push(id);
values.push(orgId);
const result = await query(
- `UPDATE library_items SET ${setClauses.join(', ')} WHERE id = $${paramIndex} AND ($${paramIndex + 1}::text IS NULL OR org_id = $${paramIndex + 1}) RETURNING *`,
+ `UPDATE library_items SET ${setClauses.join(', ')} WHERE id = $${paramIndex} AND ($${paramIndex + 1}::uuid IS NULL OR org_id = $${paramIndex + 1}::uuid) RETURNING *`,
values
);
@@ -108,7 +108,7 @@ export async function DELETE(request: NextRequest, context: RouteContext) {
try {
const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
const result = await query(
- `DELETE FROM library_items WHERE id = $1 AND ($2::text IS NULL OR org_id = $2) RETURNING id, title, item_type`,
+ `DELETE FROM library_items WHERE id = $1 AND ($2::uuid IS NULL OR org_id = $2::uuid) RETURNING id, title, item_type`,
[id, orgId]
);
diff --git a/app/api/pipeline/[id]/approve/route.ts b/app/api/pipeline/[id]/approve/route.ts
index da2397d..2d7a7b0 100644
--- a/app/api/pipeline/[id]/approve/route.ts
+++ b/app/api/pipeline/[id]/approve/route.ts
@@ -29,7 +29,7 @@ export async function POST(
// Check current status
const existing = await query(
- `SELECT id, status FROM petition_pipeline WHERE id = $1 AND ($2::text IS NULL OR org_id = $2)`,
+ `SELECT id, status FROM petition_pipeline WHERE id = $1 AND ($2::uuid IS NULL OR org_id = $2::uuid)`,
[id, orgId]
);
@@ -63,7 +63,7 @@ export async function POST(
reviewed_at = NOW(),
review_notes = COALESCE($2, review_notes),
updated_at = NOW()
- WHERE id = $3 AND ($4::text IS NULL OR org_id = $4)
+ WHERE id = $3 AND ($4::uuid IS NULL OR org_id = $4::uuid)
RETURNING *`,
[auth.username, reviewNotes, id, orgId]
);
diff --git a/app/api/pipeline/[id]/dispatch/route.ts b/app/api/pipeline/[id]/dispatch/route.ts
index 0bfc743..c28492b 100644
--- a/app/api/pipeline/[id]/dispatch/route.ts
+++ b/app/api/pipeline/[id]/dispatch/route.ts
@@ -44,7 +44,7 @@ export async function POST(
// Check current status — must be approved or posting (re-dispatch)
const existing = await query(
- `SELECT id, status, social_dispatched, title FROM petition_pipeline WHERE id = $1 AND ($2::text IS NULL OR org_id = $2)`,
+ `SELECT id, status, social_dispatched, title FROM petition_pipeline WHERE id = $1 AND ($2::uuid IS NULL OR org_id = $2::uuid)`,
[id, orgId]
);
@@ -77,7 +77,7 @@ export async function POST(
SET status = 'posting',
social_dispatched = $1,
updated_at = NOW()
- WHERE id = $2 AND ($3::text IS NULL OR org_id = $3)
+ WHERE id = $2 AND ($3::uuid IS NULL OR org_id = $3::uuid)
RETURNING *`,
[JSON.stringify(existingDispatched), id, orgId]
);
@@ -124,14 +124,14 @@ export async function POST(
existingDispatched[body.platform].pulse_result = pulseResult;
await query(
- `UPDATE petition_pipeline SET social_dispatched = $1, updated_at = NOW() WHERE id = $2 AND ($3::text IS NULL OR org_id = $3)`,
+ `UPDATE petition_pipeline SET social_dispatched = $1, updated_at = NOW() WHERE id = $2 AND ($3::uuid IS NULL OR org_id = $3::uuid)`,
[JSON.stringify(existingDispatched), id, orgId],
);
} catch (pulseErr) {
console.error('[dispatch] Pulse agent call failed:', (pulseErr as Error).message);
existingDispatched[body.platform].status = 'pulse_unreachable';
await query(
- `UPDATE petition_pipeline SET social_dispatched = $1, updated_at = NOW() WHERE id = $2 AND ($3::text IS NULL OR org_id = $3)`,
+ `UPDATE petition_pipeline SET social_dispatched = $1, updated_at = NOW() WHERE id = $2 AND ($3::uuid IS NULL OR org_id = $3::uuid)`,
[JSON.stringify(existingDispatched), id, orgId],
);
}
diff --git a/app/api/pipeline/[id]/schedule/route.ts b/app/api/pipeline/[id]/schedule/route.ts
index 3bdecf6..8c91e7c 100644
--- a/app/api/pipeline/[id]/schedule/route.ts
+++ b/app/api/pipeline/[id]/schedule/route.ts
@@ -56,7 +56,7 @@ export async function POST(
// Fetch pipeline item
const existing = await query(
- `SELECT id, status, title, body FROM petition_pipeline WHERE id = $1 AND ($2::text IS NULL OR org_id = $2)`,
+ `SELECT id, status, title, body FROM petition_pipeline WHERE id = $1 AND ($2::uuid IS NULL OR org_id = $2::uuid)`,
[id, orgId],
);
if (existing.rows.length === 0) {
diff --git a/scripts/test-instance.sh b/scripts/test-instance.sh
index b0f3e5f..7573231 100755
--- a/scripts/test-instance.sh
+++ b/scripts/test-instance.sh
@@ -22,5 +22,36 @@ export GEMINI_API_KEY="" # AI routes must fail gracefully, not burn t
export GMAIL_CLIENT_ID=""; export GMAIL_CLIENT_SECRET=""
export CRON_SECRET="test-cron-secret"
+# ── Fixture preflight (deterministic scratch DB) ──────────────────────────────
+# sdcc_test must carry the same schema as prod or routes 500 on missing objects.
+# Apply the idempotent migrations that provide the objects the suite depends on
+# (024 role_permissions — ON CONFLICT DO NOTHING; 025 gmail assign cols — ADD
+# COLUMN IF NOT EXISTS), then FAIL FAST + LOUD if a required object is still
+# absent — so a recreated/torn-down sdcc_test can never silently regress the
+# suite back to red with no explanation. (Do NOT blanket-apply db/*.sql: seeds
+# would duplicate fixture rows and alpha-order would run migrations before
+# schema.sql. Add specific idempotent migrations here as the suite grows.)
+for m in db/024_user_management.sql db/025_email_assign_read.sql; do
+ if psql "$DATABASE_URL" -v ON_ERROR_STOP=0 -f "$m" >/dev/null 2>&1; then
+ echo "[test-instance] ensured $m"
+ else
+ echo "[test-instance] WARN: could not apply $m"
+ fi
+done
+missing=$(psql "$DATABASE_URL" -Atc "
+ SELECT string_agg(x, ', ') FROM (
+ SELECT 'role_permissions (missing migration 024)' AS x
+ WHERE to_regclass('public.role_permissions') IS NULL
+ UNION ALL
+ SELECT 'gmail_messages.assigned_user_id (missing migration 025)'
+ WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
+ WHERE table_name='gmail_messages' AND column_name='assigned_user_id')
+ ) t" 2>/dev/null || echo "PSQL_UNREACHABLE")
+if [ -n "$missing" ]; then
+ echo "[test-instance] FATAL: sdcc_test is missing required schema: $missing"
+ echo "[test-instance] Rebuild the scratch DB from db/*.sql (schema.sql first, then numbered migrations) and retry."
+ exit 1
+fi
+
echo "[test-instance] starting next on :$PORT against sdcc_test (sinked integrations)"
exec node_modules/.bin/next start -p "$PORT"
diff --git a/tests/api-smoke.mjs b/tests/api-smoke.mjs
index fae4173..b3243d8 100644
--- a/tests/api-smoke.mjs
+++ b/tests/api-smoke.mjs
@@ -74,11 +74,21 @@ async function login(username, password = PW) {
return { status: res.status, cookie, role: (await res.json().catch(() => ({}))).role };
}
const jar = (c) => (c ? { Cookie: `norma-auth=${c}` } : {});
-async function hit(path, cookie) {
+// Admin requests carry an org scope so the tenant-scoped (non-null org_id) query
+// paths are actually exercised — a null-org fixture leaves org-scoping bugs
+// dormant (e.g. `$N::text` casts that only fail when org_id is compared).
+async function hit(path, cookie, extra = {}) {
try {
- const res = await fetch(`${BASE}${path}`, { headers: jar(cookie), redirect: 'manual' });
- return res.status;
- } catch (e) { return `ERR:${e.code || e.message}`; }
+ const res = await fetch(`${BASE}${path}`, { headers: { ...jar(cookie), ...extra }, redirect: 'manual' });
+ const body = res.status === 200 ? await res.text().catch(() => '') : '';
+ return { status: res.status, body };
+ } catch (e) { return { status: `ERR:${e.code || e.message}`, body: '' }; }
+}
+// A 200 that embeds a swallowed query-error marker (e.g. safeQuery → _queryErrors)
+// is a silent failure the status code hides — count it as a real defect.
+function swallowedErrors(body) {
+ const m = /"_queryErrors"\s*:\s*(\d+)/.exec(body);
+ return m ? Number(m[1]) : 0;
}
// Run tasks with bounded concurrency so the sweep is quick but not a thundering herd.
@@ -111,17 +121,21 @@ async function main() {
const targets = SMOKE ? gettable.filter((r) => critical.has(r.url)) : gettable;
const crashes = []; // any 500 under any auth = hard fail
+ const swallowed = []; // 200 that hides _queryErrors > 0 = silent fail
const publicGets = []; // 200 while unauthenticated = visibility audit surface
const errored = []; // network errors (instance flapping)
+ const ADMIN_ORG = { 'x-org-id': NIL_UUID }; // scope admin to an org → exercise tenant path
await pool(targets, async (r) => {
const anon = await hit(r.url, null);
- const asAdmin = await hit(r.url, admin.cookie);
- for (const [who, code] of [['anon', anon], ['admin', asAdmin]]) {
- if (code === 500) crashes.push(`${r.url} → 500 (${who})`);
- if (typeof code === 'string' && code.startsWith('ERR')) errored.push(`${r.url} (${who}) ${code}`);
+ const asAdmin = await hit(r.url, admin.cookie, ADMIN_ORG);
+ for (const [who, res] of [['anon', anon], ['admin', asAdmin]]) {
+ if (res.status === 500) crashes.push(`${r.url} → 500 (${who})`);
+ if (typeof res.status === 'string' && res.status.startsWith('ERR')) errored.push(`${r.url} (${who}) ${res.status}`);
}
- if (anon === 200) publicGets.push(r.url);
+ const qe = swallowedErrors(asAdmin.body);
+ if (qe > 0) swallowed.push(`${r.url} → 200 but _queryErrors=${qe} (admin, org-scoped)`);
+ if (anon.status === 200) publicGets.push(r.url);
});
// ── Report ──────────────────────────────────────────────────────────────────
@@ -137,14 +151,17 @@ async function main() {
console.log('');
}
- const passed = crashes.length === 0;
- if (passed) {
- console.log(`\x1b[32mPASS\x1b[0m — 0 unexpected 500s across ${targets.length} GET routes.`);
- } else {
+ const passed = crashes.length === 0 && swallowed.length === 0;
+ if (crashes.length) {
console.log(`\x1b[31mFAIL\x1b[0m — ${crashes.length} route(s) returned 500 (crash):`);
crashes.forEach((c) => console.log(` ✗ ${c}`));
}
- console.log(`\n${passed ? '\x1b[32m✔ api-smoke green' : '\x1b[31m✘ api-smoke red'}\x1b[0m (${targets.length} swept, ${publicGets.length} public, ${crashes.length} crashes)\n`);
+ if (swallowed.length) {
+ console.log(`\x1b[31mFAIL\x1b[0m — ${swallowed.length} route(s) returned 200 hiding a query error:`);
+ swallowed.forEach((s) => console.log(` ✗ ${s}`));
+ }
+ if (passed) console.log(`\x1b[32mPASS\x1b[0m — 0 crashes + 0 swallowed query-errors across ${targets.length} GET routes.`);
+ console.log(`\n${passed ? '\x1b[32m✔ api-smoke green' : '\x1b[31m✘ api-smoke red'}\x1b[0m (${targets.length} swept, ${publicGets.length} public, ${crashes.length} crashes, ${swallowed.length} swallowed)\n`);
process.exit(passed ? 0 : 1);
}
← eb853c3 fix(api): 4 GET-500s surfaced by api-smoke (onboard/library
·
back to Norma Platform
·
auto-data-snapshot: 2026-08-06T10:52:46 (2 data files) — pac 9ba7940 →