Funding risk overview
Snapshot of outcomes from the combined CRA, federal, Alberta, and matched-entity dataset. Use the dashboard to filter loops, sort clusters, and inspect public-safe labels.
How clusters are ranked
The table lists loop clusters: groups of charities linked by gift flows where funds can move in a cycle. Ordering helps triage; it is not a finding of wrongdoing.
- Risk score (up to 6). Each cluster gets a numeric score from weighted signals (flow size, network shape, and related factors). The dashboard shows it beside the high / medium / low risk label.
- Default sort. Rows load by risk score, highest first. Use Sort by to switch to public funding exposure, total circular flow on edges, or loop id.
- Public funding. When the public report includes a cluster funding total (sometimes a range), charts and exposure use a consistent midpoint for ranges.
-
Loop depth. Clusters use 2-hop and 3-hop charity gift-flow cycles from
cra.loops(longer materialized paths are excluded in this report). Shorter loops break ties when risk and funding signals are similar.
Screening only. Always verify against primary sources and context before conclusions.
Daniel Demoz · Kenneth Preston · Matthew Rocky · Ayesha Khalil
University of Ottawa
Quick Start Guide
Prerequisites
Node.js 18+ required. All data is already loaded in the shared database — you only need to install dependencies to start querying and running analysis.
Clone and Install
git clone <repo-url> && cd hackathon for dir in CRA FED AB general; do (cd $dir && npm install); done
Verify Database Access
Credentials: the .env.public files for each module are distributed by the hackathon organizers in the info pack provided on event day (April 29, 2026). They are gitignored and not in the clone — drop the info-pack files into CRA/, FED/, AB/, and general/ before running anything that touches the database.
Once in place, test connectivity:
cd CRA && npm run verify cd ../FED && npm run verify cd ../AB && npm run verify
Configure API Keys (optional, for AI features)
To use AI-assisted entity resolution, add credentials to general/.env.public or your personal general/.env:
# Option A: Anthropic Direct API
ANTHROPIC_API_KEY=sk-ant-api03-your-key-here
# Option B: Vertex AI (Google Cloud)
VERTEX_SERVICE_ACCOUNT_JSON={"type":"service_account",...}
VERTEX_PROJECT_ID=your-project-id
VERTEX_CLAUDE_SONNET_MODEL=claude-sonnet-4-6
VERTEX_ENDPOINT=aiplatform.googleapis.com
VERTEX_LOCATION_ID=global
VERTEX_METHOD=rawPredict
The system tries Anthropic first, falls back to Vertex automatically. You can force a provider with --provider vertex.
Run Analysis
# CRA: Detect circular gifting patterns cd CRA && npm run analyze:all # AB: Run all 6 advanced analysis scripts cd ../AB && npm run analyze:all # General: Run the full entity-resolution pipeline (CRA + FED + AB) cd ../general npm run entities:dashboard # browser dashboard at localhost:3800 npm run entities:dossier # per-entity dossier explorer at localhost:3801
Adapt to Your Own LLM
The AI review module (general/lib/llm-review.js) has a clean provider abstraction. To add a new provider:
- Add a
callYourProvider(prompt, options)function returning{ text, usage } - Register it in
availableProviders()by checking your env variable - Add it to the fallback chain in
callLLM()
The prompt and response parsing are provider-agnostic — any LLM that returns JSON works.
Environment Loading Order
All modules load environment variables consistently:
.env.publicloaded first — shared defaults. Gitignored; distributed by the hackathon organizers in the event-day info pack..envloaded second withoverride: true(personal overrides win)
Participants who drop the info-pack .env.public files into each module directory get read-only credentials automatically. Maintainers with a .env containing admin credentials automatically override for write operations (migrations, imports, drops).
CRA T3010 Charity Data
Schema: cra | Records: ~7.3M T3010 + ~1.42M pre-computed analysis | Years: 2020-2024 | Tables: 49 + 3 views (6 lookup + 19 raw-data + 24 analysis created by advanced scripts)
What's in It
Annual filings from all registered Canadian charities: financial statements, board directors, gift flows between charities, compensation, program descriptions, and political activity.
Key Tables
| Table | Description | Rows |
|---|---|---|
cra_identification | Charity name, address, category, designation | 421,866 |
cra_directors | Board members and officers | 2,873,624 |
cra_financial_details | Revenue, expenses, assets (Section D) | 420,849 |
cra_qualified_donees | Gifts to qualified donees | 1,664,343 |
cra_compensation | Employee compensation ranges | 216,380 |
Pipeline Commands
cd CRA && npm install # Full data pipeline (all 5 years from Open Data API) npm run setup # migrate + seed + fetch + import + verify # Analysis npm run analyze:all # Detect loops (2-6 hops) + SCC + partitioned + score (0-30) npm run lookup -- --name "charity name" # Interactive network lookup npm run risk -- --name "charity name" # Risk assessment # Export npm run download # All tables as CSV npm run download -- --year 2024 --format json # Tests npm run test # 54 unit + integration tests
Circular Gifting Detection
The analysis pipeline detects circular flows where money moves in loops between charities (potentially for quota gaming). It scores each charity 0-30 across three dimensions:
| Dimension | Max | What It Measures |
|---|---|---|
| Circular | 6 | Reciprocal partners, multi-cycle, shared directors |
| Financial | 12 | Overhead, pass-through indicators, compensation vs programs |
| Temporal | 12 | Same-year symmetric flows, persistent cross-year patterns |
Federal Grants & Contributions
Schema: fed | Records: ~1.275M | Departments: 51+ | Recipients: 422K+
What's in It
All federal government grants, contributions, and transfer payments. Includes department, program, recipient type, agreement type, and value. Supports amendment tracking and multi-dimensional risk scoring.
Key Fields
| Field | Description |
|---|---|
recipient_legal_name | Grant recipient organization name |
recipient_business_number | 9-digit CRA BN (often NULL pre-2018) |
agreement_type | G=Grant, C=Contribution, O=Other |
agreement_value | Dollar amount (negative = amendment) |
owner_org_title | Funding department name |
Pipeline Commands
cd FED && npm install npm run setup # Full pipeline: migrate + seed + fetch + import + fix-quality + verify npm run dashboard # Summary statistics # Analysis (8 advanced scripts) npm run analyze:all # All: equity, for-profit, amendments, concentration, zombies, export, risk, individuals npm run analyze:risk # 7-dimension risk register (109K+ entities scored) # Tests npm run test
Risk Dimensions (0-35)
| Dimension | Max | What It Measures |
|---|---|---|
| Cessation | 5 | No new grants in recent years |
| Identity | 5 | No business number, weak identity |
| Amendment | 5 | High amendment rate, value creep |
| Concentration | 5 | Dominates program/department |
| Dependency | 5 | Few grants, high total |
| Opacity | 5 | Missing descriptions, results, NAICS |
| Scale | 5 | Outsized single grants |
Alberta Open Data
Schema: ab | Records: ~2.61M | Years: 2014-2026 (12 fiscal years) | Datasets: 4
Datasets
| Dataset | Table | Records | Source |
|---|---|---|---|
| Alberta Grants | ab_grants | 1,986,676 | JSON + CSV disclosure |
| Blue Book Contracts | ab_contracts | 67,079 | Excel |
| Sole-Source Contracts | ab_sole_source | 15,533 | Excel |
| Non-Profit Registry | ab_non_profit | 69,271 | Excel |
Pipeline Commands
cd AB && npm install npm run setup # Full pipeline: migrate + seed + import:all + verify # Individual imports npm run import:grants # Stream 1.1GB JSON (~10 min) npm run import:contracts # Blue Book Excel npm run import:sole-source # Sole-source Excel npm run import:non-profit # Non-profit registry Excel npm run verify # 25 verification checks
Analysis Scripts (6 advanced)
# Run all analysis npm run analyze:all # Individual scripts npm run analyze:quality # Data quality audit (NULLs, duplicates, outliers) npm run analyze:summary # Ministry breakdowns, top recipients, trends npm run analyze:insights # HHI concentration, dissolved NPOs, ministry mapping npm run analyze:sole-source # 5 sole-source scenarios (repeats, splitting, geography) npm run analyze:scenarios # 5 grant/contract scenarios (ratios, volatility, lottery) npm run analyze:non-profit # Lifecycle trends, survival analysis, sector health
5 Sole-Source Scenarios
- Repeat vendors — Same vendor, same ministry, 3+ sole-source contracts
- Sole-source to competitive pipeline — Vendors in both sole-source AND Blue Book
- Contract splitting — Multiple same-FY contracts suggesting threshold avoidance
- Geographic concentration — Out-of-province vendor analysis
- Duration/value outliers — Longest contracts, highest daily rates
5 Grants/Contracts Scenarios
- Grant-to-contract ratio — Which ministries favor grants vs procurement
- Recipient concentration (HHI) — Monopoly programs
- Funding volatility — Most unstable year-over-year programs
- Lottery funding — Lottery vs non-lottery distribution patterns
- Payment timing — End-of-fiscal-year spending spikes
General — Cross-Dataset Entity Resolution
Schema: general | Cross-dataset utilities, reference data, and the entity resolution pipeline.
The Problem
The same legal organization appears under dozens of name variations across CRA, federal, and Alberta sources. A representative mid-sized multi-dataset charity typically has 10+ distinct name variants in the source data (legal names, trade names, typos, bilingual French-English pairs), carries multiple Business Number suffix variants (RR for the charity account, RC for corporate tax, RP for payroll), and contributes 200+ source records across 6 different tables. Without reconciling all of these to one canonical entity, cross-dataset accountability analysis is impossible.
The general module produces one golden record per real-world organization, linked to every source row that contributed to it.
The Three Techniques
The pipeline combines three complementary approaches to entity matching. Each catches cases the others miss.
1. Deterministic matching (rules-based)
Walks the six source tables in trust order — CRA first (every registered charity has a Business Number), then federal grants, then Alberta data — and applies a cascade of matching rules. BN-based matching handles the gold-standard cases; normalized-name matching strips out operational noise like TRADE NAME OF, O/A, DBA, AKA, FORMERLY, and bilingual English-French pipe separators; trade-name extraction catches the common Canadian pattern where an organization operates under a different trading name.
2. Probabilistic matching (Splink)
Integrates Splink, the UK Ministry of Justice's open-source Fellegi-Sunter record linkage library. Splink weights multiple features — name similarity (Jaro-Winkler at thresholds 0.92 and 0.82), city, postal code, entity type, and BN — and learns the relative weights from the data itself through expectation-maximization training. Runs on a DuckDB backend for speed (~3 minutes on ~1 million records), then writes pairwise match probabilities and connected-component cluster IDs back to Postgres.
Splink catches the cases that rules miss: hierarchical organizations where word order differs, truncated federal-registry variants, no-BN cross-dataset pairs that match by address rather than name. It is fully deterministic given a fixed seed — two runs produce bit-identical predictions.
3. LLM verdict and canonical-name authoring
Claude Sonnet 4.6 reviews every candidate pair produced by the deterministic and probabilistic stages. 100 concurrent workers per provider, running against both Anthropic's direct API and Google Vertex AI in parallel (separate rate-limit pools). Each LLM call does two things in one pass:
- Decide whether the two entities are the SAME legal organization, RELATED (parent/subsidiary/regional chapter/affiliate), or DIFFERENT.
- Author the golden record for SAME verdicts: the canonical legal name (the cleanest, most official-looking form chosen from all observed variants), the entity type (charity / non-profit / company / government / grant-recipient / individual / unknown), and an exhaustive deduplicated list of every meaningful name variant across both entities.
LLM review is resumable across crashes. Workers claim candidates via row-level locks; stuck reviews are reset automatically after 10 minutes. 429 rate-limit errors trigger 30-120 second exponential backoff.
The Pipeline: Eight Stages
| # | Stage | What it does | Typical runtime |
|---|---|---|---|
| 1 | Schema migration | Creates the entity tables, indexes, and SQL helper functions (normalized-name, BN validator, BN extractor). | ~10s |
| 2 | Deterministic resolve | Seeds entities from CRA (~91K charities), then enriches with CRA qualified-donees (name variants from T3010s), federal grants (~422K distinct names), and Alberta data. Ends with deduplication by shared BN and same normalized-name. | ~20–25 min |
| 3 | Splink probabilistic match | Exports cleaned source data to Parquet, runs Splink with EM-trained feature weights on a DuckDB backend, writes ~540K pairwise predictions and cluster-alias rows back to Postgres. | ~10 min |
| 4 | Candidate detection | Five tiers: same normalized name, shared BN root, trade-name extraction, trigram similarity, Splink probabilistic. Fills a staging table of ~1.6M candidate pairs. | ~5–10 min |
| 5 | IDF keyword overlap | Inverted-index scan weighted by inverse document frequency. Catches hierarchical organizations (e.g., "Diocese of Hamilton") that trigram misses because of word-order variation. | ~3 min |
| 6 | LLM verdict + authoring | 100×2 concurrent Claude workers decide SAME/RELATED/DIFFERENT and author golden-record fields. Dual-provider throughput (~30 pairs/sec combined). | ~6–8 hours |
| 7 | Final golden-record compile | For every active entity (LLM-touched or not), compiles ranked aliases with provenance, source summary, addresses, CRA financial rollup, federal grants profile, Alberta profile. | ~2–5 min |
| 8 | Donee-name trigram fallback | Reviews unlinked cra_qualified_donees rows via trigram-fuzzy lookup against the authoritative catalog. LLM decides SAME/RELATED/DIFFERENT. Tagged match_method = 'donee_trigram_fallback' for full reversibility. Never creates new entities. | ~30–90 min |
Why CRA Qualified Donees Matter
Every registered Canadian charity declares its gifts to other charities on its annual T3010 filing, writing out the recipient's name as it knows it. This produces an under-appreciated gold mine: the same organization appears under many different names written by many different donors — truncations, program-prefixed variants (e.g. "[PROGRAM NAME] — [ORG NAME]"), and line-item numbers that are often idiosyncratic to the filer's accounting. For any given charity, 5–10 additional name variants typically surface from qualified-donee declarations that don't appear in any other source. The pipeline pulls these in explicitly as a dedicated sub-phase.
Libraries & Tools
| Tool | Role | Why |
|---|---|---|
| PostgreSQL | Primary data store and compute engine | Single source of truth; every stage reads and writes SQL. GIN indexes enable millisecond fuzzy-name joins at 850K-entity scale. |
pg_trgm | Character-trigram similarity | Postgres extension; catches typos and character-level drift in names. |
| Splink (MoJ, MIT license) | Probabilistic matching | Academic-standard Fellegi-Sunter implementation; feature weights learned from data via EM. Battle-tested at government scale. |
| DuckDB | Splink's in-memory compute backend | Fastest option for single-machine Splink. Handles ~1M-record pairwise comparison universe in a few minutes. |
| Claude Sonnet 4.6 (Anthropic + Vertex AI) | Verdict + canonical-name authoring | Handles the long tail — cross-system registration numbers, hierarchical organization semantics, bilingual variants — that rules can't reliably resolve. |
| Node.js + Express | Pipeline orchestration + dashboard | Clean async-await for 100-way concurrent LLM calls; single-file dashboard for end-to-end observability. |
| cleanco (Python) | Legal-suffix stripping | Strips "Inc", "Ltd", "Société", "GmbH" etc. so Splink sees stems rather than boilerplate. |
Dashboard
A browser dashboard at http://localhost:3800 provides control and observability. Click through the pipeline stages; each button spawns the corresponding script server-side and streams its output inline. The dashboard polls Postgres every 2 seconds to show:
- A pipeline-stage strip showing the current state (inferred from the database — no manual coordination needed)
- Entity counts, source-link counts per table, Splink prediction stats with band breakdown, golden-record count
- LLM progress with reviewed-per-second throughput and ETA; per-provider split when both Anthropic and Vertex are running in parallel
- Test-entity sanity cards — a small set of known organizations (configurable in the dashboard source) that exercise different matching paths: a multi-dataset mid-sized charity, a corporate family with distinct BNs, two charities sharing a canonical name, and a large multi-dataset institution. Each card updates every poll so regressions are visible instantly.
- Recent-merges feed showing absorbed → survivor pairs as they happen
- Orphan recovery: phase PIDs persist across dashboard restarts; a "Kill all pipeline processes" button cleans up any stuck jobs
Outcomes
Design Principles
- BN is the primary identifier. Every stage treats the 9-digit Canadian Business Number root as authoritative. Where it's available it overrides name evidence; where it's missing we cascade to weaker signals.
- Every stage is idempotent and resumable. Unique constraints, ON CONFLICT DO NOTHING on inserts, and row-level locks on LLM claims mean re-running any phase is safe and continues from where it left off.
- Observable by default. The dashboard polls the database directly, not a separate event stream. What you see is the actual state of the pipeline, always.
- No destructive operations without explicit consent. The reset script requires
--yesor a dashboard confirmation dialog.
Commands
cd general npm install # One-time setup npm run entities:splink:install # Splink Python dependencies npm run setup # base schema + Alberta ministries # Primary operator interface: the dashboard npm run entities:dashboard # http://localhost:3800 (click through stages) # Headless end-to-end (destructive rebuild) npm run pipeline:rebuild # reset + all stages in order
Full documentation: general/README.md
Schemas & Tables — Complete Reference
Every table and view across the four schemas of the shared PostgreSQL database. Row counts are estimates from pg_class.reltuples (updated by ANALYZE; may lag actual counts by a few percent).
Lookup tables are small reference tables (usually <300 rows) that decode codes used in the primary data. Analysis tables are pre-computed outputs of accountability-analysis scripts, not raw data from publishers. All primary data comes from Open Government portals — see ATTRIBUTIONS.md.
CRA — cra schema (49 tables + 3 views, ~8.76M rows)
Identity & registration
| Table | Rows | Purpose |
|---|---|---|
cra_identification | 421,866 | One row per charity per fiscal year: legal name, account name, designation (A/B/C), category, registered address, contact details, registration date. Primary key (bn, fiscal_year). |
identification_name_history | 92,437 | Consolidated legal/account name history per BN with first and last year seen. Useful for detecting renames. |
cra_web_urls | 169,123 | Contact URLs declared on T3010 by year and sequence. |
T3010 annual return — financial and operational sections
| Table | Rows | Purpose |
|---|---|---|
cra_financial_details | 420,849 | T3010 Section D line items: revenue breakdown (receipted / non-receipted / government / investment / other), expenditures, assets, liabilities. One row per (bn, fpe). |
cra_financial_general | 422,683 | Program-area allocations (% of resources), internal divisions, Y/N program flags. |
cra_foundation_info | 422,569 | Foundation-specific T3010 fields (fields 100-series): controlling / controlled donors, non-qualifying investment income, etc. |
cra_charitable_programs | 478,691 | Free-text descriptions of each charity’s program activities, by program type. |
cra_directors | 2,873,624 | Board members per filing: name, position, arm’s-length flag, start/end dates. |
cra_compensation | 216,380 | Employee compensation ranges (fields 300-390): FTE positions, part-time, total compensation. |
cra_disbursement_quota | 22,151 | Disbursement-quota calculation fields (T3010 Section C). |
cra_gifts_in_kind | 54,575 | In-kind gift details (non-cash donations received). |
Cross-charity gift flows & foreign activity
| Table | Rows | Purpose |
|---|---|---|
cra_qualified_donees | 1,664,343 | Gifts from one registered charity to another. Each row has donor BN, donee BN + name (as the donor wrote it), amount, in-kind portion, and an “associated” flag. This is where the entity-resolution pipeline harvests extra name variants. |
cra_non_qualified_donees | 29,270 | Gifts to non-registered recipients, with stated purpose and cash/non-cash split. |
cra_activities_outside_countries | 44,683 | Countries in which the charity operated during the reporting year. |
cra_activities_outside_details | 25,139 | Additional fields about foreign activities (T3010 Section C5). |
cra_resources_sent_outside | 68,028 | Individual lines itemizing money/resources sent outside Canada: recipient name, amount, country. |
cra_exported_goods | 4,029 | Specific goods exported, with item name, value, destination. |
cra_political_activity_desc | 313 | Free-text descriptions of political activities (when applicable). |
cra_political_activity_funding | 0 | Funding lines for political activities (empty in 2020–2024 extract). |
cra_political_activity_resources | 234 | Staff/volunteer/financial resources allocated to political activities. |
Analysis outputs (pre-computed by accountability scripts)
| Table | Rows | Purpose |
|---|---|---|
overhead_by_charity | 420,021 | Per-charity, per-year overhead ratios: strict (admin + fundraising) and broad (includes support), compared against revenue + program spending. |
overhead_by_year | 5 | Aggregated overhead by fiscal year across all charities. |
overhead_by_year_designation | 16 | Aggregated overhead broken down by designation (A/B/C). |
govt_funding_by_charity | 166,968 | Per-charity, per-year split of federal / provincial / municipal government funding, plus the government share of revenue. |
govt_funding_by_year | 5 | Aggregated government-funding share by year. |
loops | 5,808 | Brute-force ground-truth detected circular-gifting cycles at 2–6 hops (temporal window ±1 year). |
johnson_cycles | 4,601 | Johnson’s algorithm cycle enumeration on the full graph (cross-validation against loops). |
partitioned_cycles | 108 | SCC-partitioned Johnson’s with hub removal (fast, misses cycles routing through mega-hubs). |
loop_participants | 30,003 | BNs that appear in any detected cycle, with position and neighbors. |
loop_edges | 53,771 | Pruned gift-edge graph (after iterative dead-end removal). |
loop_edge_year_flows | 30,003 | Edge-level amounts restricted to each loop’s year window. |
loop_universe | 1,501 | Per-BN summary of cycle participation (2/3/4/5/6-hop counts) and risk inputs. |
loop_charity_financials | 1,501 | Per-BN overhead proxy + loop-related inflow / outflow. |
loop_financials | 5,808 | Financial metadata per cycle: bottleneck amount, total flow, year range, same-year vs cross-year. |
scc_components | 10,177 | Strongly-connected components in the charity gift graph (Tarjan’s algorithm). |
scc_summary | 347 | Per-SCC aggregate statistics. |
matrix_census | 10,177 | Matrix-power walk census for cross-validation (N-hop reachability counts per BN). |
identified_hubs | 20 | Entities flagged as “hubs” in the gift-flow network based on in/out-degree and total flow. |
t3010_impossibilities | 54,010 | Direct violations of T3010 form arithmetic identities, cross-schedule equalities, and Schedule 3 dependencies (10 rules, each traceable to specific form-line or Open Data Dictionary v2.0 text). |
t3010_plausibility_flags | 1,075 | Plausibility flags layered on top of the strict-rule impossibilities. |
t3010_completeness_issues | 0 | Reserved for completeness-rule findings; currently empty. |
donee_name_quality | 439,867 | Scored name-similarity check between donor-written donee names (cra_qualified_donees) and the canonical legal name from cra_identification. Used to detect transcription errors and BN-name mismatches. |
_dnq_canonical | 91,129 | Canonical donee-name lookup used by the donee_name_quality pipeline. |
Lookup tables (code → name decoders)
| Table | Purpose |
|---|---|
cra_category_lookup | Charity category codes → English/French names + descriptions. |
cra_sub_category_lookup | Sub-categories under each category. |
cra_designation_lookup | Designation codes (A = public foundation, B = private foundation, C = charitable organization). |
cra_program_type_lookup | Program-type codes used by cra_charitable_programs. |
cra_country_lookup | Country code → name (used by foreign-activity tables). |
cra_province_state_lookup | Province / state code → name. |
Views
| View | Purpose |
|---|---|
vw_charity_financials_by_year | Joined identification + financial_details, one row per charity-year, with key financial fields exposed by name. |
vw_charity_profiles | Charity directory with identification + computed fields. Useful for search. |
vw_charity_programs | Joined programs + identification for easy program-description search. |
FED — fed schema (6 tables + 3 views, ~1.28M rows)
Primary data
| Table | Rows | Purpose |
|---|---|---|
grants_contributions | 1,275,521 | One row per federal grant or contribution agreement (or amendment). Fields include recipient legal name, business number (when known), department (owner_org), agreement value, program name + purpose (both languages), province/city, agreement start + end dates, federal riding, is_amendment. Amendments share a ref_number with the original. Per TBS spec, agreement_value is a cumulative running total — use vw_agreement_current or vw_agreement_originals to avoid double-counting. |
Lookup tables
| Table | Purpose |
|---|---|
agreement_type_lookup | Agreement-type codes (G = grant, C = contribution, etc.). |
recipient_type_lookup | Recipient-type codes (for-profit / non-profit / individual / government / foreign). |
province_lookup | Province code ↔ bilingual name. |
country_lookup | Country code ↔ bilingual name. |
currency_lookup | Currency code ↔ name (for foreign-currency amounts). |
Views
| View | Purpose |
|---|---|
vw_grants_decoded | grants_contributions joined to all lookup tables with decoded English/French names. |
vw_grants_by_department | Aggregates by owner organization + agreement type: grant count, total value, average, date range. |
vw_grants_by_province | Aggregates by recipient province. |
AB — ab schema (9 tables + 3 views, ~2.61M rows)
Primary data
| Table | Rows | Purpose |
|---|---|---|
ab_grants | 1,986,676 | Alberta government grant payments (fiscal years 2014-2015 through 2025-2026). Fields include ministry, business unit, recipient, program name, amount, payment date, fiscal year (display_fiscal_year format "YYYY - YYYY"), lottery-fund flag. |
ab_contracts | 67,079 | Blue Book (open-procurement) contracts. Recipient, amount, ministry, fiscal year. |
ab_sole_source | 15,533 | Sole-source (non-competitive) contracts. Vendor + department addresses, contract number, start/end dates, amount, permitted-situation justification. |
ab_non_profit | 69,271 | Alberta non-profit societies registry: legal name, type, status, registration date, registered address. |
Pre-computed aggregates
| Table | Rows | Purpose |
|---|---|---|
ab_grants_ministries | 352 | Distinct ministries appearing in ab_grants, with count + total. |
ab_grants_programs | 20,208 | Distinct program names with counts, totals, date ranges. |
ab_grants_recipients | 452,900 | Distinct grant recipients with rollup totals. |
ab_grants_fiscal_years | 12 | Per-fiscal-year rollup, one row per fiscal year (2014-2015 through 2025-2026). |
Lookup + views
| Object | Purpose |
|---|---|
ab_non_profit_status_lookup | Status codes (active / struck / dissolved / under review / etc.) with descriptions. |
vw_non_profit_decoded | ab_non_profit joined to status_lookup for human-readable status. |
vw_grants_by_ministry | ab_grants aggregated by fiscal year + ministry. |
vw_grants_by_recipient | ab_grants aggregated by recipient name. |
general — general schema (14 tables + 2 views, ~10.5M rows)
Reference data — Alberta ministries
| Table | Rows | Purpose |
|---|---|---|
ministries | 27 | Current Alberta government ministries with short codes, ministers, deputy ministers, effective dates. |
ministries_crosswalk | 162 | Mapping of historical ministry names (as they appear in ab_grants.ministry and ab_contracts.ministry) to the current canonical ministry. |
ministries_history | 62 | Name-change timeline: each prior ministry name with first/last year seen and the successor ministry it was renamed to. |
Entity resolution pipeline — state
| Table | Rows | Purpose |
|---|---|---|
entities | 926,670 | One row per resolved entity (including those later absorbed via merge). Canonical name, alternate names, BN root + all 15-char BN variants, entity type, dataset_sources array, merged_into (for soft-deleted absorbed entities), norm_canonical, status (draft/confirmed/merged), JSONB metadata. |
entity_source_links | 5,161,245 | Links each source record (in CRA/FED/AB schemas) to its resolved entity. (source_schema, source_table, source_pk) identifies the source row; source_name preserves the name as originally written; match_method records how it was linked. |
entity_resolution_log | 1,266,141 | Per-name matching progress. UNIQUE (source_schema, source_table, source_name). Status tracks pending/matched/created/error for resumability. |
resolution_batches | variable | Per-run metadata: when the pipeline started/completed, processed/matched/errored counts. |
Entity merging (staging + audit)
| Table | Rows | Purpose |
|---|---|---|
entity_merge_candidates | 1,643,060 | Pairwise candidates from the 5 detection tiers + smart-match. LLM reviews each one and writes verdict (SAME / RELATED / DIFFERENT / UNCERTAIN) + confidence + reasoning. Unique on the unordered pair. |
entity_merges | 66,147 | Audit trail for every merge executed: survivor, absorbed, candidate_id that triggered it, merge_method, names/BNs added, links redirected count, timestamp. |
donee_trigram_candidates | variable | Stage 8 staging table: unlinked cra_qualified_donees names paired with their best trigram neighbour above threshold. Reviewed by the LLM and, on SAME verdicts, appended to the matched entity’s aliases with match_method = 'donee_trigram_fallback' for full reversibility. |
Final golden-record output
| Table | Rows | Purpose |
|---|---|---|
entity_golden_records | 851,300 | The authoritative final table. One row per active real-world organization. Canonical name, every alias with source provenance (JSONB), BN variants, dataset_sources, source_summary per schema.table, deduplicated addresses, CRA / FED / AB profile JSONBs, related entities (LLM RELATED verdicts), merge_history, llm_authored raw response, confidence, status. |
Splink probabilistic matching
| Table | Rows | Purpose |
|---|---|---|
splink_predictions | 540,640 | Pairwise match probabilities from Splink’s Fellegi-Sunter model (run threshold ≥ 0.40). Each row has left record (source_schema.source_table + record_id), right record, match_probability, match_weight, connected-component cluster_id, and feature-level gamma values. 06-detect-candidates Tier 5 uses this. |
splink_aliases | variable | Name variants per cluster with source provenance. Enriches golden-record alias lists with probabilistic matches the deterministic cascade missed. |
splink_build_metadata | 1 per run | One row per Splink run: start/completion, Splink version, threshold, total records + predictions + clusters, config. |
Views
| View | Purpose |
|---|---|
vw_entity_search | Searchable entity directory (active entities only). |
vw_entity_funding | Joined entity + CRA financials + FED grants + AB totals, one row per entity, for cross-dataset funding queries. |
Primary keys & join paths
| From | To | Via |
|---|---|---|
| any CRA table | cra_identification | (bn, fiscal_year) or bn alone for cross-year aggregates |
| CRA | general.entities | LEFT(cra.*.bn, 9) = entities.bn_root |
| FED | general.entities | general.entity_source_links.source_pk->>'_id' = fed.grants_contributions._id |
| AB | general.entities | general.entity_source_links.source_pk->>'id' = ab.*.id (cast to int or uuid depending on table) |
| entity | all funding across CRA+FED+AB | vw_entity_funding (joins everything in one query) |
Local Database Recreation Kit
The .local-db/ directory contains everything needed to recreate the hackathon database in your own PostgreSQL instance: the full DDL (split into pre-data and post-data), a manifest of every table and row count, and export/import scripts that auto-discover tables via information_schema.
Data bundle is separate
The JSONL row data (~13 GB across all four schemas) is not in the repo — it ships out-of-band (GitHub release / file server). Download the bundle and unpack it under .local-db/data/ so the directory structure matches data/{cra,fed,ab,general}/*.jsonl. The small artifacts (DDL, manifest, scripts, README) are committed, so the repository alone documents the full schema.
Why JSONL, not CSV
JSONL was chosen over CSV so that jsonb columns, text[] arrays, NULLs, and strings with embedded newlines, commas, and quotes all round-trip through export/import without escape-layer games. One row per line, keyed by column name.
What ships in .local-db/
| Path | Purpose |
|---|---|
README.md | Quick-start instructions, import options, expected runtime. |
export.js | (Maintainers) Dumps the live DB to schemas/ DDL and data/*.jsonl files. |
import.js | (Participants) Recreates the DB in a local Postgres. Auto-discovers tables; supports --schema, --schema-only, --data-only, --batch-size, --drop. |
manifest.json | Authoritative table inventory with row counts + column metadata. Verified by import after load. |
schemas/{cra,fed,ab,general}.sql | Pre-data DDL: CREATE SCHEMA, sequences, tables (with nextval defaults), indexes, views. |
schemas/{cra,fed,ab,general}_post.sql | Post-data DDL: unique / check / foreign-key constraints, and setval() statements to sync sequences with loaded row counts. |
Dataset totals (from the manifest)
| Schema | Tables + views | Rows | Notes |
|---|---|---|---|
cra | 49 tables + 3 views | ~8.76M | T3010 raw data (2020–2024) + 24 accountability-analysis tables |
fed | 6 tables + 3 views | ~1.28M | Proactive-publication grants & contributions |
ab | 9 tables + 3 views | ~2.61M | Alberta grants, contracts, sole-source, non-profit registry |
general | 14 tables + 2 views | ~10.46M | Cross-dataset entity resolution (entities, source links, golden records, Splink output, ministries reference) |
| Total across schemas | ~23.1M rows | Full load typically ~20–30 min on local Postgres, ~1–3 hours to a remote instance | |
Quick start
createdb hackathon # or via your Postgres admin cd .local-db && npm install DB_CONNECTION_STRING=postgresql://user:pass@localhost/hackathon npm run import
Prerequisites
- PostgreSQL 14+, with permission to
CREATE EXTENSION pg_trgmandCREATE EXTENSION fuzzystrmatch. - Node.js 18+.
- A DB user with
CREATEon the target database andINSERTon the tables being loaded.
Full details: .local-db/README.md
Analysis outputs
Each module ships advanced analysis scripts that write human-readable Markdown / TXT reports and machine-readable JSON to its own reports folder when you run them. Those folders are gitignored — you generate them locally — but every analysis script also persists its findings to a table under its schema, so the same results can be queried directly from SQL without running anything.
AB — six analysis scripts
| Command | What it produces |
|---|---|
npm run analyze:quality | Data-quality audit: NULL rates, empty strings, duplicates, amount outliers, fiscal-year coverage. |
npm run analyze:summary | Ministry-by-fiscal-year breakdowns, top recipients, program distribution, cross-dataset overlap. |
npm run analyze:insights | Vendor HHI, sole-source rates, dissolved-NPO grants, ministry name mapping. |
npm run analyze:sole-source | Five scenarios: repeat vendors, sole-source→Blue Book pipeline, contract splitting, geographic concentration, duration/value outliers. |
npm run analyze:scenarios | Five grant/contract scenarios: grant-to-contract ratio, recipient HHI, funding volatility, lottery funding, payment timing. |
npm run analyze:non-profit | Lifecycle trends, survival analysis, entity-type evolution, sector health. |
npm run analyze:all | Runs all six sequentially. |
FED — eight analysis scripts
| Command | What it produces |
|---|---|
npm run analyze:equity | Per-capita provincial funding analysis (all ten provinces + three territories). |
npm run analyze:forprofit | For-profit recipient deep-dive. |
npm run analyze:amendments | Amendment-creep detection: rate, value growth, duration extensions. |
npm run analyze:concentration | Vendor / recipient concentration (Herfindahl-Hirschman Index, single-department dominance). |
npm run analyze:zombies | Zombie recipients (no new grants recently) and ghost-capacity entities. |
npm run analyze:export | Entity export (CSV) for external adverse-media lookups. |
npm run analyze:risk | Comprehensive 7-dimension risk register (0–35 scale): cessation, identity, amendment, concentration, dependency, opacity, scale. |
npm run analyze:individuals | Individual grant recipient analysis. |
npm run analyze:all | Runs all eight sequentially. |
npm run dashboard | Summary statistics across the whole fed schema. |
CRA — circular-gifting and data-quality pipelines
| Command | What it produces | Persisted table |
|---|---|---|
npm run analyze:loops | Brute-force 2–6-hop cycle detection (ground truth). Temporal window ±1 year. | cra.loops, cra.loop_participants, cra.loop_edges |
npm run analyze:scc | Tarjan SCC decomposition of the charity gift graph. | cra.scc_components, cra.scc_summary |
npm run analyze:partitioned | SCC-partitioned Johnson’s with hub removal (fast complement to brute force). | cra.partitioned_cycles, cra.identified_hubs |
npm run analyze:johnson | Johnson’s algorithm on the full graph (cross-validation). | cra.johnson_cycles |
npm run analyze:matrix | Matrix-power walk census (cross-validates loops / johnson_cycles). | cra.matrix_census |
npm run analyze:financial | Per-loop and per-charity financial rollup restricted to each loop’s year window. | cra.loop_financials, cra.loop_edge_year_flows, cra.loop_charity_financials, cra.loop_universe |
npm run analyze:score | Deterministic 0–30 risk scoring (circular + financial + temporal). | cra.loop_universe (score columns) |
npm run analyze:all | Full pipeline in order. | — |
npm run data-quality | Three data-quality scripts (BN↔name mismatches, T3010 arithmetic impossibilities, identification-backfill check). Every rule traced to an explicit line in the T3010 form or the CRA Open Data Dictionary v2.0. | cra.donee_name_quality, cra.t3010_impossibilities, cra.identification_name_history |
npm run lookup -- --name "..." | Interactive per-charity network lookup (outgoing / incoming gifts, loops, shared directors). | — |
npm run risk -- --bn ... | Interactive per-charity risk report with scored factors and multi-year financials. | — |
General — entity-resolution pipeline
The general pipeline persists everything to tables, not report files. Pipeline state lives in entities, entity_source_links, entity_merge_candidates, entity_merges, entity_golden_records, and the splink_* tables. Operate the pipeline through npm run entities:dashboard (port 3800) and investigate specific organizations through npm run entities:dossier (port 3801). A reference-sample validator is also available:
npm run entities:compare:splink # 500-entity reference sample against the live pipeline