One-Time ID Migration Guide

-- Example: Postgres
COPY (
  SELECT id AS legacy_id, name, entity_type
  FROM legacy_entities
) TO '/tmp/legacy_entities.csv' WITH CSV HEADER;

legacy_id, name,                entity_type
30981,     Lionel Messi,        player
44835,     Erling Haaland,      player
49,        FA Cup,              tournament
131,       FC Barcelona,        team

GET https://api.sportsapipro.com/v1/football/search?query={name}&filter={all|athletes|competitors|competitions}

{
  "competitors": [
    { "id": 131,  "name": "Barcelona",    "countryId": 2,  "popularityRank": 98500000 },
    { "id": 5432, "name": "Barcelona SC", "countryId": 35, "popularityRank": 125000 }
  ]
}

CREATE TABLE entity_id_map (
  legacy_id        TEXT NOT NULL,
  entity_type      TEXT NOT NULL,           -- 'player' | 'team' | 'tournament' | 'country'
  canonical_id     BIGINT NOT NULL,         -- SportsAPI Pro ID
  canonical_name   TEXT NOT NULL,
  confidence       TEXT NOT NULL,           -- 'auto' | 'manual'
  migrated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (legacy_id, entity_type)
);

CREATE INDEX ON entity_id_map (entity_type, canonical_id);

# If this returns 200, the canonical ID is good.
curl -I "https://api.sportsapipro.com/v2/football/images/players/{canonical_id}" \
  -H "x-api-key: YOUR_API_KEY"

import fs from "node:fs";

const API_KEY = process.env.SPORTSAPI_KEY;
const SLEEP   = (ms) => new Promise((r) => setTimeout(r, ms));

const filterFor = { player: "athletes", team: "competitors", tournament: "competitions" };
const arrayFor  = { player: "athletes", team: "competitors", tournament: "competitions" };

const rows = fs.readFileSync("legacy_entities.csv", "utf8")
  .trim().split("\n").slice(1)
  .map((l) => { const [legacy_id, name, entity_type] = l.split(","); return { legacy_id, name, entity_type }; });

const out = [];
for (const row of rows) {
  const url = `https://api.sportsapipro.com/v1/football/search?query=${encodeURIComponent(row.name)}&filter=${filterFor[row.entity_type]}`;
  const res = await fetch(url, { headers: { "x-api-key": API_KEY } });
  const data = await res.json();
  const hit  = (data[arrayFor[row.entity_type]] || []).sort((a, b) => (b.popularityRank || 0) - (a.popularityRank || 0))[0];
  out.push({ ...row, canonical_id: hit?.id ?? null, canonical_name: hit?.name ?? null });
  await SLEEP(220); // ~4.5 rps
}

fs.writeFileSync("entity_id_map.json", JSON.stringify(out, null, 2));
console.log(`Mapped ${out.filter((r) => r.canonical_id).length}/${out.length}`);

import csv, json, os, time, requests

API_KEY = os.environ["SPORTSAPI_KEY"]
FILTER  = {"player": "athletes", "team": "competitors", "tournament": "competitions"}

def lookup(name, entity_type):
    r = requests.get(
        "https://api.sportsapipro.com/v1/football/search",
        params={"query": name, "filter": FILTER[entity_type]},
        headers={"x-api-key": API_KEY},
        timeout=10,
    )
    r.raise_for_status()
    hits = r.json().get(FILTER[entity_type], [])
    return max(hits, key=lambda h: h.get("popularityRank", 0)) if hits else None

out = []
with open("legacy_entities.csv") as f:
    for row in csv.DictReader(f):
        hit = lookup(row["name"], row["entity_type"])
        out.append({**row, "canonical_id": hit and hit["id"], "canonical_name": hit and hit["name"]})
        time.sleep(0.22)  # ~4.5 rps

with open("entity_id_map.json", "w") as f:
    json.dump(out, f, indent=2)

print(f"Mapped {sum(1 for r in out if r['canonical_id'])}/{len(out)}")