---
title: "Build a fund screener with Next.js and Postgres, step by step"
description: "Data model, a server-side route handler, a filter table for asset class, risk, TER, AUM and region, sorting, and a cache aligned with the 24-hour refresh."
canonical: https://fundfactsapi.com/blog/build-a-fund-screener-with-nextjs
published: 2026-09-06
updated: 2026-10-06
publisher: FundFacts API
---

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

> Data model, a server-side route handler, a filter table for asset class, risk, TER, AUM and region, sorting, and a cache aligned with the 24-hour refresh.

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](https://fundfactsapi.com/) as the source of the per-fund JSON. The pattern is the one described on the [fund screener use case](https://fundfactsapi.com/use-cases/fund-screener) 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:

| Filter | Source in `data` | Type as published |
| --- | --- | --- |
| Asset class | `keyFacts.assetClass` | string, e.g. `"Equities"` |
| Risk indicator | `riskRating` | integer 1–7 |
| Fees | `headlineMetrics.ter` | string, e.g. `"0.20%"` |
| Size | `headlineMetrics.aum` | string, e.g. `"USD 151.7bn"` |
| Region | `profile.regionFocus` | string, e.g. `"Global"` |

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. The Free plan's 5 lookups a month are enough to try it on a few funds; Pro's 1,000 requests a month refresh about 30 funds a day and Scale's 10,000 about 300, and overage (or Enterprise) takes a mid-sized platform range further. 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](https://fundfactsapi.com/blog/srri-risk-indicator-explained), 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](https://fundfactsapi.com/blog/ter-ongoing-charges-fund-fees) 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. Every paid plan includes an [AI companion kit](https://fundfactsapi.com/#ai) 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](https://fundfactsapi.com/blog/build-fund-app-with-ai-coding-agent) explains what the kit contains and the prompts that work.

Start with the [free plan](https://fundfactsapi.com/signup): 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](https://fundfactsapi.com/docs).

## About FundFacts API

FundFacts turns any fund or ETF ISIN into one dated factsheet (fees and KID costs, the 1–7 risk indicator, holdings, sector and country exposure, returns) read from the fund house's own documents: as a client-ready PDF under your firm's name for advisers and wealth managers, as JSON through its API for developers, or inside ChatGPT and Claude through its MCP server.

FundFacts works inside ChatGPT and Claude as a connector (a remote MCP server): ask about any fund or ETF by ISIN, ticker or name and the assistant answers from FundFacts' dated figures, fees, risk, holdings, exposure and returns, with fund cards, side-by-side comparisons, holdings overlap and portfolio look-through.

- Free API key, no card: https://fundfactsapi.com/signup
- Documentation: https://fundfactsapi.com/docs (OpenAPI: https://fundfactsapi.com/openapi.json)
- MCP server: https://fundfactsapi.com/api/mcp (set-up for every client: https://fundfactsapi.com/mcp-server)
- Pricing: https://fundfactsapi.com/pricing
- Site index for AI assistants: https://fundfactsapi.com/llms.txt
- Informational data, not investment advice.