← back to Ca Donations
ca-donations: wire server-side sort (mandatory sort rule) across all 3 families
9e7508bd1543bb110734e73ba84afc2c5382e618 · 2026-08-22 11:02:16 -0700 · Steve
- whitelisted ORDER BY per endpoint (orgs: name/status/ntee; grants: amount/year/grantor; political: date/amount/donor/recipient)
- frontend passes st.sort to /api/orgs, /api/grants, /api/political
- verified: sort=amount surfaces Uber $45M / Congressional Leadership Fund $41.7M; sort=name alphabetizes orgs
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M public/index.htmlM server.js
Diff
commit 9e7508bd1543bb110734e73ba84afc2c5382e618
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Aug 22 11:02:16 2026 -0700
ca-donations: wire server-side sort (mandatory sort rule) across all 3 families
- whitelisted ORDER BY per endpoint (orgs: name/status/ntee; grants: amount/year/grantor; political: date/amount/donor/recipient)
- frontend passes st.sort to /api/orgs, /api/grants, /api/political
- verified: sort=amount surfaces Uber $45M / Congressional Leadership Fund $41.7M; sort=name alphabetizes orgs
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/index.html | 6 +++---
server.js | 19 +++++++++++++------
2 files changed, 16 insertions(+), 9 deletions(-)
diff --git a/public/index.html b/public/index.html
index 5bb08bd..913596c 100644
--- a/public/index.html
+++ b/public/index.html
@@ -108,7 +108,7 @@ async function render() {
const st = state[tab];
let url, cards;
if (tab === 'orgs') {
- url = `/api/orgs?q=${encodeURIComponent(st.q)}&status=${st.filter}&limit=200`;
+ url = `/api/orgs?q=${encodeURIComponent(st.q)}&status=${st.filter}&sort=${st.sort}&limit=200`;
const { rows } = await (await fetch(url)).json();
cards = rows.map(r => `<div class="card"><div class="name">${(r.name||'').replace(/</g,'<')}</div>
<div class="kv">
@@ -121,7 +121,7 @@ async function render() {
view.innerHTML = rows.length ? `<div class="grid">${cards}</div>` : `<div class="empty">No orgs match.</div>`;
} else if (tab === 'grants') {
url = `/api/grants?grantor=${encodeURIComponent(st.q)}&grantee=${encodeURIComponent(st.q)}&limit=200`;
- const { rows } = await (await fetch(`/api/grants?grantor=${encodeURIComponent(st.q)}&limit=200`)).json();
+ const { rows } = await (await fetch(`/api/grants?grantor=${encodeURIComponent(st.q)}&sort=${st.sort}&limit=200`)).json();
if (!rows.length) { view.innerHTML = `<div class="pending"><b>Foundation-grant ingest pending.</b> Grant records (990-PF Part XV, 990 Schedule I/F) load from IRS 990 XML in the charitable-grants node. The schema, API, and grid are live and will populate on that run.</div>`; return; }
cards = rows.map(r => `<div class="card"><div class="name">${(r.grantee_name||'').replace(/</g,'<')} <span class="amt">${money(r.amount)}</span></div>
<div class="kv">
@@ -132,7 +132,7 @@ async function render() {
</div></div>`).join('');
view.innerHTML = `<div class="grid">${cards}</div>`;
} else {
- const { rows } = await (await fetch(`/api/political?donor=${encodeURIComponent(st.q)}&jurisdiction=${st.filter}&limit=200`)).json();
+ const { rows } = await (await fetch(`/api/political?donor=${encodeURIComponent(st.q)}&jurisdiction=${st.filter}&sort=${st.sort}&limit=200`)).json();
if (!rows.length) { view.innerHTML = `<div class="pending"><b>Political-contribution ingest pending.</b> Donor-level rows load from the CAL-ACCESS daily dump (state, incl. Forms 461/496/497), FEC bulk <code>itcont</code> filtered to CA (federal), and local NetFile/Socrata. The schema, API, and grid are live and will populate on that run.</div>`; return; }
cards = rows.map(r => `<div class="card"><div class="name">${(r.donor_name||'').replace(/</g,'<')} <span class="amt">${money(r.amount)}</span></div>
<div class="kv">
diff --git a/server.js b/server.js
index 6f4b037..0d125b1 100644
--- a/server.js
+++ b/server.js
@@ -22,6 +22,13 @@ app.use((req, res, next) => {
});
const like = (s) => `%${String(s).trim()}%`;
+// Whitelisted ORDER BY per endpoint — the UI's sort dropdown must actually sort.
+const ORDER = {
+ orgs: { name: 'name ASC', status: 'ca_ag_status ASC, name ASC', ntee: 'ntee_code ASC NULLS LAST, name ASC' },
+ grants: { amount: 'amount DESC NULLS LAST', year: 'tax_year DESC NULLS LAST, amount DESC NULLS LAST', grantor: 'grantor_name ASC' },
+ political: { date: 'contribution_date DESC NULLS LAST', amount: 'amount DESC NULLS LAST', donor: 'donor_name ASC', recipient: 'recipient_name ASC' },
+};
+const orderBy = (which, sort, fallbackKey) => ORDER[which][sort] || ORDER[which][fallbackKey];
app.get('/healthz', (_req, res) => res.json({ ok: true, service: 'ca-donations' }));
@@ -41,7 +48,7 @@ app.get('/api/stats', async (_req, res) => {
// Charitable orgs search — drillable: each org links to /api/org/:ein.
app.get('/api/orgs', async (req, res) => {
try {
- const { q: term = '', status = '', ntee = '', limit = 100 } = req.query;
+ const { q: term = '', status = '', ntee = '', sort = 'name', limit = 100 } = req.query;
const where = [], params = [];
if (term) { params.push(like(term)); where.push(`name ILIKE $${params.length}`); }
if (status) { params.push(status); where.push(`ca_ag_status = $${params.length}`); }
@@ -50,7 +57,7 @@ app.get('/api/orgs', async (req, res) => {
const rows = await q(
`SELECT ein,name,city,state,ntee_code,subsection,ca_ag_status
FROM charitable_orgs ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
- ORDER BY name LIMIT $${params.length}`, params);
+ ORDER BY ${orderBy('orgs', sort, 'name')} LIMIT $${params.length}`, params);
res.json({ rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
@@ -67,7 +74,7 @@ app.get('/api/org/:ein', async (req, res) => {
// Charitable grants search (donor->recipient records from 990-PF/Sched I/F).
app.get('/api/grants', async (req, res) => {
try {
- const { grantor = '', grantee = '', year = '', limit = 100 } = req.query;
+ const { grantor = '', grantee = '', year = '', sort = 'amount', limit = 100 } = req.query;
const where = [], params = [];
if (grantor) { params.push(like(grantor)); where.push(`grantor_name ILIKE $${params.length}`); }
if (grantee) { params.push(like(grantee)); where.push(`grantee_name ILIKE $${params.length}`); }
@@ -76,7 +83,7 @@ app.get('/api/grants', async (req, res) => {
const rows = await q(
`SELECT grantor_ein,grantor_name,grantee_name,grantee_city,amount,tax_year,grant_type
FROM charitable_grants ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
- ORDER BY amount DESC NULLS LAST LIMIT $${params.length}`, params);
+ ORDER BY ${orderBy('grants', sort, 'amount')} LIMIT $${params.length}`, params);
res.json({ rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
@@ -84,7 +91,7 @@ app.get('/api/grants', async (req, res) => {
// Political donor-level contributions (above CA $100 itemization threshold).
app.get('/api/political', async (req, res) => {
try {
- const { donor = '', recipient = '', jurisdiction = '', employer = '', limit = 100 } = req.query;
+ const { donor = '', recipient = '', jurisdiction = '', employer = '', sort = 'date', limit = 100 } = req.query;
const where = [], params = [];
if (donor) { params.push(like(donor)); where.push(`donor_name ILIKE $${params.length}`); }
if (recipient) { params.push(like(recipient)); where.push(`recipient_name ILIKE $${params.length}`); }
@@ -94,7 +101,7 @@ app.get('/api/political', async (req, res) => {
const rows = await q(
`SELECT donor_name,donor_employer,donor_city,amount,contribution_date,recipient_name,office,jurisdiction,form
FROM political_contributions ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
- ORDER BY contribution_date DESC NULLS LAST LIMIT $${params.length}`, params);
+ ORDER BY ${orderBy('political', sort, 'date')} LIMIT $${params.length}`, params);
res.json({ rows });
} catch (e) { res.status(500).json({ error: e.message }); }
});
← 37ac678 ca-donations: political + foundation-grant ingest landed (bo
·
back to Ca Donations
·
ca-donations: FIX-FIRST hardening (Cody gate) — rate limit + 8340af8 →