Build a fund screener with Next.js and Postgres, step by step

A fund screener is a table of funds with filters on the side: asset class, risk indicator, fees, size, region. Every fund platform has one, and every one of them is built the same way underneath: a store of per-fund documents, a narrow table of the fields you filter on, and a query layer that sorts and paginates. This tutorial builds that in Next.js with Postgres, using FundFacts API as the source of the per-fund JSON. The pattern is the one described on the fund screener use case page; here we write the code.

What a screener needs from the data

Screeners do not filter on everything. Five fields cover most of the UI:

The third column is the whole design problem. The API returns figures the way the fund house publishes them, which is right for display and wrong for WHERE ter < 0.3. So the screener needs two representations: the full JSON for the detail page, and parsed numeric columns for filtering. Keep both.

Step 1: the data model

Two tables. The first stores the raw payload per ISIN with its freshness metadata; the second holds the columns you filter and sort on.

sql
CREATE TABLE fund_payloads (
  isin          char(12) PRIMARY KEY,
  payload       jsonb NOT NULL,
  generated_at  timestamptz NOT NULL,
  expires_at    timestamptz NOT NULL,
  fetched_at    timestamptz NOT NULL DEFAULT now()
);
​
CREATE TABLE fund_facts (
  isin          char(12) PRIMARY KEY REFERENCES fund_payloads(isin),
  name          text NOT NULL,
  asset_class   text,
  region        text,
  risk_rating   smallint CHECK (risk_rating BETWEEN 1 AND 7),
  ter_pct       numeric(6,3),
  aum_millions  numeric(14,2),
  aum_currency  char(3),
  data_as_of    date
);
​
CREATE INDEX ON fund_facts (asset_class, risk_rating);
CREATE INDEX ON fund_facts (ter_pct);
CREATE INDEX ON fund_facts (aum_millions);

generated_at and expires_at come straight from the response envelope. They are not decoration: expires_at is the only thing that should trigger a refresh, which is what makes the cache strategy in step 4 trivial.

Step 2: a route handler that keeps the key on the server

Never call the API from the browser. A route handler under app/api/ reads the key from the environment, forwards the request, and passes the quota headers back so your own tooling can see them.

typescript
// app/api/funds/[isin]/route.ts
import { NextResponse } from "next/server";
​
const ISIN_RE = /^[A-Z]{2}[A-Z0-9]{9}\d$/;
​
export async function GET(_req: Request, ctx: { params: Promise<{ isin: string }> }) {
  const { isin } = await ctx.params;
  const code = isin.trim().toUpperCase();
  if (!ISIN_RE.test(code)) {
    return NextResponse.json({ error: "invalid_isin" }, { status: 400 });
  }
​
  const upstream = await fetch(`https://fundfactsapi.com/api/v1/funds/${code}`, {
    headers: { Authorization: `Bearer ${process.env.FUNDFACTS_API_KEY}` },
    signal: AbortSignal.timeout(300_000), // a cold ISIN can take 1–3 minutes
  });
​
  const body = await upstream.json();
  const res = NextResponse.json(body, { status: upstream.status });
  for (const h of ["X-RateLimit-Limit", "X-RateLimit-Remaining", "X-RateLimit-Reset"]) {
    const v = upstream.headers.get(h);
    if (v) res.headers.set(h, v);
  }
  return res;
}

The 300-second timeout matters. The first request for an ISIN nobody has asked for before is built from the documents the fund house publishes and takes one to three minutes; subsequent requests return in milliseconds. A default 10-second timeout turns a working integration into a flaky one.

Step 3: load the universe into the tables

A screener's universe is a list of ISINs you own: your platform's product range, a client's holdings, the funds a compliance team monitors. Loading is a script, not a request handler, because it runs for minutes and respects the daily quota.

The parsing step is where published strings become numbers. Be conservative: if a field is missing or unparseable, store NULL and let the filter exclude it rather than inventing a zero.

typescript
// scripts/sync-funds.ts
import { sql } from "./db";
​
function pct(s?: string) {
  const m = s?.match(/-?\d+(\.\d+)?/);
  return m ? Number(m[0]) : null;
}
​
function aum(s?: string): { millions: number | null; ccy: string | null } {
  const m = s?.match(/^([A-Z]{3})\s([\d.,]+)\s?(bn|m|k)?$/i);
  if (!m) return { millions: null, ccy: null };
  const n = Number(m[2].replace(/,/g, ""));
  const unit = (m[3] ?? "m").toLowerCase();
  const millions = unit === "bn" ? n * 1000 : unit === "k" ? n / 1000 : n;
  return { millions, ccy: m[1].toUpperCase() };
}
​
export async function syncIsin(isin: string) {
  const [row] = await sql`SELECT expires_at FROM fund_payloads WHERE isin = ${isin}`;
  if (row && new Date(row.expires_at) > new Date()) return "fresh";
​
  const r = await fetch(`https://fundfactsapi.com/api/v1/funds/${isin}`, {
    headers: { Authorization: `Bearer ${process.env.FUNDFACTS_API_KEY}` },
    signal: AbortSignal.timeout(300_000),
  });
  if (r.status === 404) return "not_found";
  if (r.status === 429) throw new Error(`rate_limited until ${r.headers.get("X-RateLimit-Reset")}`);
  if (!r.ok) throw new Error(`upstream ${r.status}`);
​
  const env = await r.json();
  const d = env.data;
  const size = aum(d.headlineMetrics?.aum ?? d.keyFacts?.aum);
​
  await sql`INSERT INTO fund_payloads (isin, payload, generated_at, expires_at)
    VALUES (${isin}, ${env}, ${env.generatedAt}, ${env.expiresAt})
    ON CONFLICT (isin) DO UPDATE SET payload = EXCLUDED.payload,
      generated_at = EXCLUDED.generated_at, expires_at = EXCLUDED.expires_at, fetched_at = now()`;
​
  const facts = {
    isin, name: env.name, asset_class: d.keyFacts?.assetClass ?? null, region: d.profile?.regionFocus ?? null,
    risk_rating: d.riskRating ?? null, ter_pct: pct(d.headlineMetrics?.ter), aum_millions: size.millions,
    aum_currency: size.ccy, data_as_of: d.dataAsOf ?? null,
  };
  await sql`INSERT INTO fund_facts ${sql(facts)}
    ON CONFLICT (isin) DO UPDATE SET ${sql(facts, ...Object.keys(facts).filter((k) => k !== "isin"))}`;
​
  return Number(r.headers.get("X-RateLimit-Remaining"));
}

Run it over your ISIN list sequentially and stop when X-RateLimit-Remaining reaches zero; the X-RateLimit-Reset header tells you when to resume. On the Free plan (10 requests a day) that means a universe of ten new funds per day; Pro covers 100 and Scale 1,000, which is enough to refresh a mid-sized platform range daily. Because the sync skips anything whose expires_at is in the future, re-running the script is free, and a cron job can call it every hour without spending a request until payloads actually expire.

The two-table split also keeps the parsing code in one place. If a fund house changes how it formats fund size, only aum() changes and a re-run repairs the facts table from the stored payloads; nothing in the UI or the query layer is aware that the source was ever a string.

Two details worth copying. AUM comparisons across currencies are only approximate unless you convert; store the currency and either filter within one currency or convert at load time with a rate you control. And riskRating is the regulatory indicator described in the SRRI and SRI explainer, so a filter of "3 to 5" means something a European user recognises.

Step 4: filters, sorting and pagination

With the facts table in place, the screener is a parameterised query. Whitelist the sortable columns; never interpolate a column name from the request.

typescript
// lib/screen.ts
const SORTABLE = { name: "name", ter: "ter_pct", aum: "aum_millions", risk: "risk_rating" } as const;
​
export async function screen(f: {
  assetClass?: string; region?: string; riskMin?: number; riskMax?: number;
  terMax?: number; aumMin?: number; sort?: keyof typeof SORTABLE; desc?: boolean; page?: number;
}) {
  const col = SORTABLE[f.sort ?? "name"];
  const dir = f.desc ? sql`DESC` : sql`ASC`;
  const limit = 50, offset = ((f.page ?? 1) - 1) * limit;
​
  return sql`
    SELECT isin, name, asset_class, region, risk_rating, ter_pct, aum_millions, aum_currency, data_as_of
    FROM fund_facts
    WHERE (${f.assetClass ?? null}::text IS NULL OR asset_class = ${f.assetClass ?? null})
      AND (${f.region ?? null}::text IS NULL OR region = ${f.region ?? null})
      AND (${f.riskMin ?? null}::int IS NULL OR risk_rating >= ${f.riskMin ?? null})
      AND (${f.riskMax ?? null}::int IS NULL OR risk_rating <= ${f.riskMax ?? null})
      AND (${f.terMax ?? null}::numeric IS NULL OR ter_pct <= ${f.terMax ?? null})
      AND (${f.aumMin ?? null}::numeric IS NULL OR aum_millions >= ${f.aumMin ?? null})
    ORDER BY ${sql(col)} ${dir} NULLS LAST
    LIMIT ${limit} OFFSET ${offset}`;
}

Call this from a server component that reads searchParams, render the rows, and link each ISIN to a detail page that reads fund_payloads.payload for the full factsheet: holdings, sector and country exposure, performance. Show data_as_of in the table footer. Fees deserve a one-line explanation somewhere near the filter; the TER and ongoing charges post covers what the number includes.

Step 5: a cache that follows the 24-hour cycle

You have already built the cache. The API refreshes each fund's JSON every 24 hours and tells you when with expiresAt; fund_payloads mirrors that. The rules that follow:

• Serve every page from Postgres. The API is only called by the sync script.
• Refresh a fund only when expires_at < now(). Never refresh on a timer of your own.
• If a refresh fails with 502 upstream_error, keep the previous payload and try again on the next run. Stale by a day beats empty.
• Treat 404 fund_not_found as a permanent answer for that ISIN and remove it from the sync list.
• Read X-RateLimit-Remaining on every response and stop early rather than hitting 429.

If you also cache rendered pages, set their revalidation to something shorter than a day so a refreshed payload shows up within hours, not tomorrow.

Scaffolding this with an AI agent

Everything above fits in a single prompt if the agent knows the field names and the operational rules. Pro and Scale plans include an AI companion kit with an AGENTS.md, a Cursor rule, a Claude Code skill and an OpenAPI spec; drop it into the project and the "screener" recipe produces the two tables, the sync script and the query layer with the right timeouts and parsing. The companion post on coding agents explains what the kit contains and the prompts that work.

Start with the free plan: ten ISINs a day is enough to load a sample universe, see the real field values in your database, and shape the filters before you decide on a plan. The full field reference is in the documentation.