| name | update-funds |
| description | Ingest EU-funds beneficiary data (ИСУН) from the 2020.eufunds.bg public register into data/funds/. Use when the daily watch report flags "ИСУН EU funds" as changed, when the user asks to refresh EU-funds / еврофондове / beneficiary data, or after a fresh git clone if data/funds/ is empty. |
| allowed-tools | ["Read","Bash","Edit","Write"] |
Update Funds skill (ИСУН EU-funds beneficiaries)
Pulls the public "Бенефициенти" register of ИСУН 2020 — Bulgaria's Management & Monitoring Information System for EU funds — and writes canonical JSON to data/funds/. One row per organisation that has signed at least one EU-funds contract, with all-time rollup totals: contracts signed, funds contracted, funds actually paid (all EUR).
This covers the 2014-2020 cohesion operational programmes, the 2021-2027 period, and the National Recovery Plan — all funnelled through ИСУН. It is a major public-money channel the project otherwise does not track.
When to run
| Trigger | Action |
|---|
Daily watcher reports ИСУН EU funds (beneficiaries) changed | Full re-ingest (npm run funds:ingest) |
| User asks to "refresh EU funds" / "update еврофондове" / "refresh ИСУН" | Same — full re-ingest |
data/funds/ empty (fresh clone) | Same — the ingest is a full rebuild every run |
/update-connections refreshed companies-index.json | Re-run — the ingest re-joins the MP cross-reference automatically |
| Ingest aborts with "header row not found" | The eufunds.bg export schema changed — investigate scripts/funds/parse.ts BEFORE re-running |
| Ingest aborts with "export looks truncated" | The download was partial or date-filtered — see "Why a full export" below |
Step 1 — Ingest
npm run funds:ingest
This downloads the full XLSX export — fresh every run — from
https://2020.eufunds.bg/bg/0/0/Beneficiary/ExportToExcel
(a snapshot is kept at data/_cache/funds/beneficiaries.xlsx, gitignored, for
offline --file re-runs), parses the ~52k beneficiary rows, rebuilds
data/funds/ from scratch — index.json plus
the sharded beneficiaries/<0-9>.json + beneficiaries/_x.json files — and,
when data/parliament/companies-index.json is present, cross-references the
beneficiaries against the MP-companies graph into derived/mp_connected.json.
Expected output on a normal run:
→ fetching https://2020.eufunds.bg/bg/0/0/Beneficiary/ExportToExcel
2.4 MB
parsed 52779 beneficiary row(s)
⚠ 4 beneficiary row(s) with a negative EUR rollup (net clawback / rounding residue — kept as-is):
...
→ wrote 11 beneficiary shard(s)
→ wrote 45887 per-EIK beneficiary file(s)
→ cross-referencing beneficiaries against the MP-companies graph
EIK linkage map: 938 EIK(s) from 938/1110 TR-enriched companies
100 MP↔beneficiary pair(s) → derived/mp_connected.json (86 MP(s), 98 company(ies), €168,527,162 contracted)
✓ index.json written
52779 beneficiaries · 80705 contracts · €43,500,972,226 contracted · €16,494,577,249 paid · 45887 with EIK (86.9%)
Flags:
npm run funds:ingest -- --dry-run
npm run funds:ingest -- --file PATH.xlsx
--file ingests a local XLSX instead of fetching — use it when the operator
has already exported the report by hand (the export endpoint also accepts the
page's filter query string). The same row-count floor applies, so the file
must be a full export.
Step 2 — Verify
node -e "
const idx = require('./data/funds/index.json');
console.log('totals:', idx.totals);
console.log('byOrgForm:', idx.byOrgForm.map(b => b.key + '=' + Math.round(b.contractedEur)));
console.log('top beneficiary:', idx.topByContracted[0].name);
console.log('cross-reference:', idx.crossReference);
"
git diff --stat data/funds/
You should see index.json, up to 11 beneficiaries/*.json shards, and
derived/mp_connected.json changed. withEik should stay near ~87% — a sharp
drop means EIK parsing regressed. byOrgForm carries the public-law vs
private-law split; crossReference.pairCount (the MP-tied payload) should sit
in the low hundreds.
Step 3 — Contract-level ingest (Проекти)
npm run funds:ingest-projects
This pulls the sibling Проекти export from https://2020.eufunds.bg/bg/0/0/Project/ExportToExcel (one row per signed contract, ~80k rows, ~10 MB XLSX). Unlike the beneficiary rollup it carries a per-contract implementation location (Местонахождение) which is resolved against data/settlements.json + data/municipalities.json into:
- single-settlement EKATTE (~85% of rows) →
data/funds/projects/by-ekatte/{ekatte}.json
- single- or multi-муни label (~10%) →
data/funds/projects/by-muni/{muni}.json
- NUTS-region label (~1%) → folded into
multi_location.json
- national / foreign / TA (~3%) → folded into
multi_location.json
- unresolved (~0.1%, mostly settlements missing from settlements.json plus genuinely ambiguous bare names) → folded into
multi_location.json
Also writes per-beneficiary contract lists to data/funds/projects/by-eik/{eik}.json (gitignored, same convention as beneficiaries-by-eik) and per-programme lists to by-program/{code}.json. The top-level index.json carries corpus totals, the location-kind histogram, per-programme rollups, and per-status rollups.
The ingest also emits two slim derivatives for the frontend:
- Per-place summaries —
by-ekatte/{ekatte}-summary.json and by-muni/{обshtina}-summary.json (~3-5 KB each), carrying rollup + top-3 contracts + top-3 programmes + per-capita €. The муни summary backs the My-Area EU-funds tile and the AI placeEuProjects tool (avoids loading the full 18 MB Sofia shard). Money for a contract naming several общини is split evenly between them, so a topContracts row can exceed the place rollup — it carries muniCount so the reader can be told why. (The EKATTE summary currently has no live reader.)
- Choropleth map data —
muni-map.json (~65 KB) with one denormalised row per муни. Backs the FundsMuniMapTile on /funds. Includes a synthetic SOF00 row aggregating S22 + S23xx/S24xx/S25xx so the Sofia districts on the map render as a single Стoлична value.
Per-capita uses Census 2021 population (data/census_2021_settlements.json) — not ГРАО — because the census carries the Sofia city EKATTE (68134 = 1.18M) which ГРАО does not. Re-run this step after update-census if NSI ever re-releases the corpus.
Flags mirror Step 1 — --dry-run, --file PATH.
Step 3b — My-Area projects map (geo pins)
npx tsx scripts/funds/build_geo_pins.ts
Distils each município's heavy by-muni/{обshtina}.json corpus into a slim by-muni-geo/{обshtina}.json — the top-200 contracts by money, geocoded and non-geocoded together. Each contract that resolves to a location.ekatte carries lat/lon (joined against data/settlements.json); the rest carry none. Per-file schema: sourceContractCount (município total — the honest headline count), geocodedCount (how many resolved to a location), and contracts[] (the capped list).
This one slim file backs the My-Area "Проекти от еврофондовете" tile: it renders the full contracts list (scrollable) and, on demand, a Leaflet map of just the subset carrying lat/lon. Always re-run after Step 3 rewrites by-muni/ — the isun_eu_funds_projects watcher flips both together. Output is idempotent except the generatedAt stamp.
New / modified contract detection (runs inside funds:ingest-projects)
scripts/funds/projects_ingest.ts calls scripts/funds/projects_diff.ts automatically (step 7c). ИСУН carries no native new-vs-amendment field — one status per contract — so the only way to surface a "new project" / "value or status changed" signal is to diff successive ingests on the stable contractNumber. The diff:
- loads the prior snapshot from
state/funds/projects_snapshot.json (per-machine host state, gitignored, ~80k entries, fully rebuildable);
- emits per-município
data/funds/projects/changes/<obshtina>.json + a national changes/index.json (committed — small, capped at 50/obshtina);
- writes the new snapshot.
The changes/ directory is reset each run, so each file reflects only the most-recent ingest's diff (the "what changed in the last update" the My-Area alert feed renders). First run seeds the snapshot silently (no prior baseline ⇒ no changes emitted, else all ~80k contracts read as "new"). These change files feed scripts/myarea/build_alerts.ts (EU "Нов проект" / "Промяна" events) and the AI placeEuProjects tool. Commit data/funds/projects/changes/ alongside the rest of data/funds/.
Two-ingest warm-up: the feature is visibly silent until the second post-baseline ingest. The first run only seeds state/funds/projects_snapshot.json, so changes/ stays empty, no EU "Нов проект"/"Промяна" events appear in the alert feed, and placeEuProjects's new/modified counts read zero. The first run that has a prior snapshot to diff against is the first one that emits changes.
Step 4 — Commit + publish to Postgres (Cloud SQL, not the bucket)
The whole /funds surface is served from Cloud SQL (/api/db/fund-*), so
publishing means reloading the DB tables from the fresh on-disk shards — NOT an
rsync to GCS.
git add data/funds/
git commit -m "funds: refresh ИСУН EU-funds beneficiaries + projects"
npm run db:load:funds:pg
npm run db:load:funds:pg:cloud
Deployment (READ THIS before syncing): funds is served from Cloud SQL
(Firebase fn /api/db/*), not GCS — same architecture as procurement.
bucket:sync excludes all of funds/ (the ^funds/.* term in the -x
regex in package.json), and bucket_gzip.ts ships no funds file. The
ingest's JSON is the local PG-load source db:load:funds:pg reads: every
precomputed page payload (index, projects-index, muni-map, taxonomy,
absorption, sankey, integrity + per-programme, mp_connected + per-mp/by-eik,
political_links + per-eik, confirmed, rrf_context, themes + per-slug, per-place
- per-programme summaries, geo pins) is loaded verbatim into the
fund_payloads(kind, key) table; per-beneficiary rollups → fund_beneficiaries,
per-contract detail → fund_projects. So the prod-deploy path for funds is
db:load:funds:pg:cloud, NOT bucket:sync. The small curated globals
(index.json, derived/political_links.json, derived/integrity.json,
rrf_context.json, themes.json, …) stay committed because the deploy
build (prerender + sitemap) reads them from the git tree — they are load
sources, not bucket-served. Verify parity after any loader/serving change:
npx tsx scripts/db/gen_funds/parity.ts --full (asserts PG payloads ==
on-disk JSON). Cloud SQL is production — never auto-run
db:load:funds:pg:cloud unprompted; emit it for the operator.
Why a full export (no incremental path)
The eufunds.bg report is an all-time rollup per organisation, not a feed
of dated contract events. A date-filtered export returns period-scoped totals
that would corrupt the all-time figures if merged. So the canonical refresh is
always a full re-export — it's only ~2.5 MB and the ingest rebuilds the whole
tree idempotently. The MIN_ROWS floor (40,000) deliberately rejects a
small date-filtered export from overwriting data/funds/. A date-filtered
slice is fine to inspect with --file ... --dry-run, but never write one as
canonical.
Political-economy join layer
After the MP cross-reference runs (Step 1 above), the ingest folds in two more
sources into a single derived shard set keyed by beneficiary EIK:
data/officials/derived/company_links.json (from /update-officials) —
non-MP officials with declared stakes or TR roles: cabinet, deputy ministers,
state-agency heads, regional governors, mayors, deputy mayors, council chairs,
councillors, chief architects. Only the high-confidence slice is used —
declarations and namesakeCount == 1 TR roles.data/procurement/derived/top_contractors.json + per-EIK
data/procurement/contractors/{eik}.json (from /update-procurement) — the
АОП award overlap per flagged EIK.data/procurement/debarred.json — name-matched debarred-suppliers flag.
Outputs:
| Path | Shape | Size |
|---|
data/funds/derived/political_links.json | { totals, top: top-50, flaggedEiks: [...] } | ~50 KB committed |
data/funds/derived/political-by-eik/{eik}.json | One PoliticalEntry per flagged EIK | 1–4 KB × ~286 files committed |
data/funds/derived/political-by-eik/index.json | Manifest of flagged EIKs | ~5 KB committed |
The build runs as part of funds:ingest (Step 1 — no separate command). For
dev iteration on just this step, run it standalone:
npx tsx scripts/funds/political_links.ts
No new external fetch — purely a join over already-ingested data. Re-run after
/update-officials or /update-procurement flips, even when ИСУН itself
hasn't moved.
MP cross-reference
When data/parliament/companies-index.json is present, the ingest joins every
beneficiary's EIK against the MP-companies graph (built by /update-connections
from Court-of-Audit declarations + Commerce Registry filings) and writes
data/funds/derived/mp_connected.json — one entry per (MP, beneficiary) pair:
the declared relations (a management role or an ownership stake) plus that
beneficiary's contracts / contracted / paid totals. index.json also gains a
crossReference summary and an mpTied flag on the top-beneficiary lists.
The join key is the 9-digit canonical EIK (companies[].tr.uic on the
companies side). Beneficiary rows that share an EIK — a parent organisation
and its sub-units (райони, териториални поделения, клонове), which the register
lists separately — are aggregated before the join, so a connected beneficiary
is counted once with summed totals. Editorial guardrail: a connection is
flagged only when it is recorded in the official declarations or the
Commerce Registry — no name-match guessing. The cross-reference hard-fails if companies-index.json
is present but TR-enrichment is missing on >90% of entries (the silent
"/update-connections TR refresh wasn't run" failure mode).
If companies-index.json is absent (fresh clone before /update-connections),
the ingest still completes — the raw beneficiary data lands; only the MP-tied
payload is skipped, with a logged hint.
Ordering dependency. When the orchestrator queues both /update-connections
and /update-funds, /update-connections must run first — it produces
companies-index.json. The watcher source list already places
cacbg_declarations and egov_commerce (→ update-connections) before
isun_eu_funds, so the natural source-order traversal handles this.
Data-integrity contract
Fails loud rather than write partial / corrupt data. Surfaces that halt before any write:
| Surface | Trigger | Action |
|---|
| HTTP error on eufunds.bg | non-200 on the export download | Throws |
| Header row not found | The 7 expected column headers don't match — export schema drift | Throws — investigate parse.ts |
| Row-count floor | Fewer than 40,000 beneficiary rows parsed (truncated / filtered download) | Throws |
| Non-finite amount | A contracted/paid value is NaN or Infinity | Throws naming the beneficiary |
| Negative / fractional count | contractCount is negative or not an integer | Throws naming the beneficiary |
Surfaces that are intentionally non-fatal:
| Surface | Behaviour | Why not a hard fail |
|---|
| Negative EUR rollup | Listed as a warning, kept as-is | Net clawbacks and cent-level reconciliation residue are legitimate in an upstream rollup |
| 10-digit leading token | eik set to null, token still stripped from the name | Can't tell legacy BULSTAT from a personal ЕГН — not persisted, avoids storing PII |
| Beneficiary with no EIK | eik: null, row still ingested | ~13% of rows (individuals, some public bodies); they just won't cross-reference |
What this skill does NOT do
- Does not write frontend UI. The
/funds dashboard is a later phase; it consumes data/funds/ via React Query once that screen exists.
- Does not auto-fire. The watcher reports when the register moves; the orchestrator or the user decides when to run.
- Does not ingest project-level detail. The "Бенефициенти" report is organisation-grain. Per-project / per-programme breakdowns would need a different ИСУН report.
File map
| Path | Purpose |
|---|
scripts/funds/ingest.ts | CLI entry — fetch, parse, validate, write data/funds/ |
scripts/funds/fetch.ts | XLSX export download (always fresh) + snapshot writer |
scripts/funds/parse.ts | XLSX → FundsBeneficiary[] (header-schema guard, EIK extraction) |
scripts/funds/cross_reference.ts | EIK-keyed join against companies-index.json → mp_connected.json |
scripts/funds/political_links.ts | Political-economy join: MP + officials + АОП overlap + debarred → political_links.json + per-EIK shards |
scripts/funds/taxonomy.ts | Programme-code → period + fund-family inference (CCI pattern). Used by both ingest scripts. |
scripts/funds/build_taxonomy_derivatives.ts | Builds data/funds/taxonomy.json, derived/absorption.json, derived/sankey.json from the projects ingest. Runs at the end of funds:ingest-projects. |
scripts/funds/integrity.ts | Builds derived/integrity.json (slim leaderboard) + per-programme shards (HHI, serial winners, debarred matches). Runs at the end of funds:ingest-projects. |
scripts/funds/themes.ts | Builds derived/themes/{slug}.json editorial-focus shards from the data/funds/themes.json definition file. Runs at the end of funds:ingest-projects. |
scripts/funds/eik.ts | EIK/BULSTAT canonicalization (9-digit) |
scripts/funds/types.ts | Shared type definitions |
scripts/watch/sources/isun_eu_funds.ts | Watcher source — fingerprints the export corpus shape |
data/funds/index.json | Totals, by-org-type / by-org-form breakdowns, top beneficiaries, crossReference summary — committed |
data/funds/beneficiaries/<0-9>.json, _x.json | Beneficiary rows sharded by EIK last digit — committed |
data/funds/beneficiaries-by-eik/<EIK>.json | One small file per beneficiary for O(1) /company/{EIK} lookup — bulky (~46k files), gitignored local PG-load source → fund_beneficiaries (served via /api/db/fund-beneficiary) |
data/funds/derived/mp_connected.json | One entry per (MP, beneficiary) pair — the MP-tied journalism payload — committed; PG-load source → fund_payloads('mp-connected'). The aggregate fallback only; the candidate page reads the per-MP shard below first. |
data/funds/derived/per-mp/<mpId>.json + per-mp/index.json; by-eik/<EIK>.json + index.json | Data-diet shards + manifest the /candidate/:id EU-funds tile and /company/:eik read. Regenerated every ingest by cross_reference.ts (write-if-changed). Gitignored PG-load sources → fund_payloads('per-mp'/'per-mp-index'/'by-eik'/'by-eik-index'), served via /api/db — NOT bucket-synced. See "Per-MP shard invariant" in process-watch-report. |
data/funds/derived/political_links.json | Slim leaderboard of politically-tied beneficiaries (MP + non-MP officials + АОП overlap + debarred) — committed; PG-load source → fund_payloads('political-links') |
data/funds/derived/political-by-eik/{EIK}.json | Per-EIK political-economy shard for the /company panel — gitignored PG-load source → fund_payloads('political-by-eik') |
data/funds/taxonomy.json | Per-programme period + fund-family lookup (~10 KB) — committed; PG-load source → fund_payloads('taxonomy') |
data/funds/derived/absorption.json | Per-period / per-fund-type / per-programme absorption% rollup (~10 KB) — committed; PG-load source → fund_payloads('absorption') |
data/funds/derived/sankey.json | Precomputed Fund → top-OP Sankey for the /funds tile (~5 KB) — committed; PG-load source → fund_payloads('sankey') |
data/funds/derived/integrity.json | Slim concentration / serial-winner / debarred leaderboard (~50 KB) — committed; PG-load source → fund_payloads('integrity') |
data/funds/derived/integrity-by-program/{code}.json | Per-programme HHI + top-10 beneficiaries + debarred matches (~3-5 KB) — gitignored PG-load source → fund_payloads('integrity-program') |
data/funds/themes.json | Editorial focus-theme definitions (slug, label, keywords, programme codes, investigative cards) — hand-maintained, committed (also read at build by prerender + sitemap) |
data/funds/derived/themes/{slug}.json | Per-theme derived shard (totals, top beneficiaries, top contracts, top munis, programmes, sources) — gitignored PG-load source → fund_payloads('theme') |
data/funds/derived/themes/index.json | Slim themes index for the /funds tile and /funds/focus/{slug} router — gitignored PG-load source → fund_payloads('themes-index') |
data/_cache/funds/beneficiaries.xlsx | Snapshot of the last downloaded export — gitignored |
Quick command reference
npm run funds:ingest
npm run funds:ingest
git add data/funds/
git commit -m "funds: refresh ИСУН EU-funds beneficiaries"
npm run db:load:funds:pg
npm run db:load:funds:pg:cloud
npm run funds:ingest -- --dry-run
