How do I build and maintain my own database of funds from the API?

Schema

sql
create table funds (
  isin          text primary key,
  name          text,
  kind          text,          -- data.profile.kind
  category      text,          -- data.profile.category
  risk_rating   int,           -- data.riskRating
  risk_band     text,          -- data.profile.riskBand
  ter_pct       numeric,       -- parsed from data.headlineMetrics.ter
  aum_text      text,          -- data.keyFacts.aum as printed
  currency      text,
  distribution  text,
  inception     date,
  sfdr_article  int,
  data_as_of    date,
  expires_at    timestamptz not null,
  payload       jsonb not null,
  status        text not null  -- ok | not_found
);
create index on funds (expires_at);
create index on funds (category, risk_band);

Keep the full payload in jsonb; you will want a field you did not extract.

Loader

python
import os, requests, time, psycopg
H = {"Authorization": f"Bearer {os.environ['FUNDFACTS_API_KEY']}"}
​
def upsert(cur, x):
    d = x.get("data") or {}
    ter = d.get("headlineMetrics", {}).get("ter") or ""
    cur.execute("""insert into funds values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        on conflict (isin) do update set name=excluded.name, kind=excluded.kind, category=excluded.category,
        risk_rating=excluded.risk_rating, risk_band=excluded.risk_band, ter_pct=excluded.ter_pct, aum_text=excluded.aum_text,
        currency=excluded.currency, distribution=excluded.distribution, inception=excluded.inception, sfdr_article=excluded.sfdr_article,
        data_as_of=excluded.data_as_of, expires_at=excluded.expires_at, payload=excluded.payload, status=excluded.status""",
        (x["isin"], x.get("name"), d.get("profile", {}).get("kind"), d.get("profile", {}).get("category"), d.get("riskRating"),
         d.get("profile", {}).get("riskBand"), float(ter.rstrip("%")) if ter.endswith("%") else None, d.get("keyFacts", {}).get("aum"),
         d.get("keyFacts", {}).get("currency"), d.get("keyFacts", {}).get("distribution"), d.get("keyFacts", {}).get("inception") or None,
         d.get("sfdrArticle"), d.get("dataAsOf") or None, x.get("expiresAt") or "now()", psycopg.types.json.Jsonb(x), x["status"]))
​
def refresh(isins, batch=50):
    with psycopg.connect(os.environ["DATABASE_URL"]) as conn, conn.cursor() as cur:
        for i in range(0, len(isins), batch):
            r = requests.post("https://fundfactsapi.com/api/v1/funds", json={"isins": isins[i:i+batch], "wait": True}, headers=H, timeout=300)
            if int(r.headers.get("X-RateLimit-Remaining", "1")) < 20:
                break  # keep headroom for interactive use
            for x in r.json()["results"]:
                if x["status"] in ("ok", "not_found"):
                    upsert(cur, x)
                # pending: picked up on the next run
            conn.commit()
            time.sleep(1)

Run it against select isin from funds where expires_at < now() on a schedule, plus new ISINs from your intake.

Rules of thumb

• Never refresh before `expiresAt`: the payload cannot have changed.
• Prioritise: refresh the funds users open first; the long tail can lag a day.
• Keep 404s: a not_found row stops you from re-spending on a stock ISIN; review monthly.
• Show `data_as_of`, not the refresh time.

The fund screener use case and the Next.js screener guide build the UI on top of this table; on Scale the change feed tells you which rows actually changed.