← back to Grant
app/api/grants/route.ts
201 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { verifyAuthWithOrg, resolveOrgId } from '@/lib/auth';
import { auditLog } from '@/lib/audit';
/* ─── GET /api/grants ─────────────────────────────────────────────────────── */
export async function GET(request: NextRequest) {
const session = verifyAuthWithOrg(request);
if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
try {
const url = new URL(request.url);
const status = url.searchParams.get('status');
const search = url.searchParams.get('search');
const orgId = await resolveOrgId(session);
if (!orgId) {
return NextResponse.json({ rows: [], statusCounts: {} });
}
let sql = `SELECT id, org_id, title, funder, funder_url, amount_min, amount_max, description, eligibility, focus_areas, application_url, deadline, cycle, ai_fit_score, ai_suggestion, status, priority, notes, applied_at, amount_requested, amount_awarded, tags, is_bookmarked, grants_gov_id, federal_register_id, source_api, created_at, updated_at FROM grants WHERE org_id = $1`;
const params: unknown[] = [orgId];
let paramIdx = 2;
if (status && status !== 'all') {
sql += ` AND status = $${paramIdx}`;
params.push(status);
paramIdx++;
}
if (search) {
sql += ` AND (title ILIKE $${paramIdx} OR funder ILIKE $${paramIdx})`;
params.push(`%${search}%`);
paramIdx++;
}
sql += ` ORDER BY CASE WHEN deadline IS NOT NULL AND deadline > NOW() THEN 0 ELSE 1 END, deadline ASC NULLS LAST, created_at DESC`;
const result = await query(sql, params);
// Status counts (always unfiltered)
const countsRes = await query(
`SELECT status, COUNT(*)::int as count FROM grants WHERE org_id = $1 GROUP BY status`,
[orgId],
);
const statusCounts: Record<string, number> = {};
let total = 0;
for (const row of countsRes.rows) {
statusCounts[row.status] = row.count;
total += row.count;
}
statusCounts['all'] = total;
return NextResponse.json({ rows: result.rows, statusCounts });
} catch (err) {
console.error('[grants GET]', err);
return NextResponse.json({ error: 'Failed to fetch grants' }, { status: 500 });
}
}
/* ─── POST /api/grants ────────────────────────────────────────────────────── */
export async function POST(request: NextRequest) {
const session = verifyAuthWithOrg(request);
if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
try {
const body = await request.json();
if (!body.title || typeof body.title !== 'string' || !body.title.trim()) {
return NextResponse.json({ error: 'title is required' }, { status: 400 });
}
const orgId = await resolveOrgId(session);
if (!orgId) {
return NextResponse.json({ error: 'No organization found' }, { status: 400 });
}
const result = await query(
`INSERT INTO grants (org_id, title, funder, funder_url, amount_min, amount_max, description, eligibility, deadline, focus_areas, priority, application_url, cycle, notes, tags, status)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
RETURNING id, org_id, title, funder, funder_url, amount_min, amount_max, description, eligibility, focus_areas, application_url, deadline, cycle, ai_fit_score, ai_suggestion, status, priority, notes, applied_at, amount_requested, amount_awarded, tags, is_bookmarked, grants_gov_id, federal_register_id, source_api, created_at, updated_at`,
[
orgId,
body.title,
body.funder || 'Unknown Funder',
body.funder_url || null,
body.amount_min || null,
body.amount_max || null,
body.description || null,
body.eligibility || null,
body.deadline || null,
body.focus_areas || [],
body.priority || 'medium',
body.application_url || null,
body.cycle || null,
body.notes || null,
body.tags || [],
body.status || 'discovered',
],
);
return NextResponse.json(result.rows[0], { status: 201 });
} catch (err) {
console.error('[grants POST]', err);
return NextResponse.json({ error: 'Failed to create grant' }, { status: 500 });
}
}
/* ─── PATCH /api/grants ───────────────────────────────────────────────────── */
export async function PATCH(request: NextRequest) {
const session = verifyAuthWithOrg(request);
if (!session) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
try {
const body = await request.json();
const { id, ...fields } = body;
if (!id) {
return NextResponse.json({ error: 'id is required' }, { status: 400 });
}
// 2026-05-30 (audit P1-7): map allowed field → literal SQL identifier
// instead of interpolating the user-supplied key. Removes the "allowlist
// typo/removal = SQL injection" failure mode entirely.
const COLUMN_MAP: Record<string, string> = {
title: '"title"', funder: '"funder"', funder_url: '"funder_url"',
amount_min: '"amount_min"', amount_max: '"amount_max"',
description: '"description"', eligibility: '"eligibility"',
deadline: '"deadline"', focus_areas: '"focus_areas"', priority: '"priority"',
application_url: '"application_url"', cycle: '"cycle"', notes: '"notes"',
tags: '"tags"', status: '"status"', is_bookmarked: '"is_bookmarked"',
applied_at: '"applied_at"', amount_requested: '"amount_requested"',
amount_awarded: '"amount_awarded"', ai_fit_score: '"ai_fit_score"',
ai_suggestion: '"ai_suggestion"',
};
const setClauses: string[] = [];
const params: unknown[] = [];
let paramIdx = 1;
for (const key of Object.keys(fields)) {
const col = COLUMN_MAP[key];
if (col) {
setClauses.push(`${col} = $${paramIdx}`);
params.push(fields[key]);
paramIdx++;
}
}
if (setClauses.length === 0) {
return NextResponse.json({ error: 'No valid fields to update' }, { status: 400 });
}
// 2026-05-04 (code-reviewer P0-3): tenant isolation on PATCH. Resolves
// org from the cookie payload (v0.2.0) or LIMIT 1 fallback (legacy).
const patchOrgId = await resolveOrgId(session);
if (!patchOrgId) {
return NextResponse.json({ error: 'No organization found' }, { status: 400 });
}
// If changing status to submitted, fetch previous status for audit
let previousStatus: string | null = null;
if (fields.status === 'submitted') {
const prev = await query('SELECT status, org_id FROM grants WHERE id = $1 AND org_id = $2', [id, patchOrgId]);
if (prev.rows.length > 0) {
previousStatus = prev.rows[0].status;
}
}
params.push(id);
const idIdx = paramIdx;
paramIdx++;
params.push(patchOrgId);
const orgIdx = paramIdx;
const sql = `UPDATE grants SET ${setClauses.join(', ')} WHERE id = $${idIdx} AND org_id = $${orgIdx} RETURNING id, org_id, title, funder, funder_url, amount_min, amount_max, description, eligibility, focus_areas, application_url, deadline, cycle, ai_fit_score, ai_suggestion, status, priority, notes, applied_at, amount_requested, amount_awarded, tags, is_bookmarked, grants_gov_id, federal_register_id, source_api, created_at, updated_at`;
const result = await query(sql, params);
if (result.rows.length === 0) {
// Same response for "not in your org" and "doesn't exist" — don't leak existence.
return NextResponse.json({ error: 'Grant not found' }, { status: 404 });
}
// Audit log for status changes to 'submitted'
if (fields.status === 'submitted') {
const updatedGrant = result.rows[0];
await auditLog(
'grant.submitted',
'grant',
id,
updatedGrant.org_id || null,
'admin',
{ previous_status: previousStatus, title: updatedGrant.title },
request.headers.get('x-forwarded-for') || undefined,
);
}
return NextResponse.json(result.rows[0]);
} catch (err) {
console.error('[grants PATCH]', err);
return NextResponse.json({ error: 'Failed to update grant' }, { status: 500 });
}
}