Giving Tuesday Datamart

A data backbone for IRS Form 990 / 990-PF filings. It pulls Giving Tuesday's public Datamart CSVs from S3 and the IRS Exempt Organizations Business Master File from irs.gov, lands them in PostgreSQL with full lineage, materializes canonical entity tables and a Postgres full-text-search surface on top, runs grant-recipient matching, and exposes a read-only Python client. A Next.js explorer (peerlo) sits in frontend/.

S3 (Giving Tuesday public bucket)      irs.gov (EO Business Master File)
        │                                        │
        ▼                                        ▼
   Staging tables          ── public.basic_fields, public.mission_statements, ..., public.eo_bmf
   (all-TEXT, lineage-stamped)
        │
        ▼
   Canonical tables        ── public.nonprofit_canonical, public.nonprofit_text (FTS), ...
   (typed, one row per real entity)
        │
        ▼
   Matched grants          ── public.unioned_grants
   (private-grants ↔ recipient EIN, plus Schedule I; grant inputs read
    through filing-version-deduped *_current relations, issue #33)
        │
        ▼
   GtDatamartClient        ── read-only Python client, used by the frontend
                              and by any downstream consumer

What's in gt_datamart

A dedicated Postgres database, broken into four layers:

• Staging (public.*) — direct ingest of the source CSVs. One table per source (e.g. basic_fields, mission_statements, programs, schedule_o, grants_to_domestic_organizations, privategrants, basic_fields_pf from Giving Tuesday; eo_bmf from the IRS: every currently recognized exempt org, one row per EIN, with the 501(c) subsection, foundation code and NTEE code that no 990 carries — see docs/bmf-assessment.md). Every column is TEXT so zero-padded EINs (012345678), ZIPs (01234), and phones round-trip intact; cast at query time (SUM(totrevcuryea::bigint)). Every row carries four lineage columns: _source_version, _source_url, _ingested_at, _ingest_run_id.
• Canonical (public.*) — typed materializations rebuilt from staging. One row per real entity (nonprofit_canonical, funder_canonical, optionally person_canonical); a per-EIN narrative built from the latest filing only with two GIN-indexed tsvector columns for full-text search (nonprofit_text — text_tsv_compact for stemmed/relevance-ranked matching, text_tsv_compact_simple for exact-term matching); a filtered Schedule O Part III narrative table (schedule_o_part_iii).
• Matched grants — unioned_grants is the consumer-facing grants table: 990-PF grants joined to their matched recipient EINs (via recordlinkage) UNION'd with Schedule I grants. Indexed on both granter and grantee EIN.
• Lineage (datamart_meta.*) — ingest_runs records every staging ingest; canonical_builds records every canonical rebuild together with the ingest_run_id of every staging input it read.

Setup

1. Install

The package is pip-installable. Two profiles:

# Read-only client only — for downstream consumers (notebooks, app
# backends). Dependency-light: just sqlalchemy + psycopg2-binary.
pip install -e .

# Full pipeline — ingestion, canonical build, grant matching.
# Pulls boto3, polars, recordlinkage, pyarrow, etc.
pip install -e '.[ingest]'

Requires Python ≥ 3.10.

2. Postgres

You need an empty Postgres database called gt_datamart. The pipeline deliberately does not create databases for you — having a human do this once means a typo in config can never silently provision a new database.

createdb gt_datamart
# or:
psql -h <host> -U <user> -d postgres -c "CREATE DATABASE gt_datamart;"

Configure connection details in either:

• A config.ini file with a [postgres] section pointing at your host — used by the ingest pipeline. Path resolved via GT_DATAMART_CONFIG_PATH (or legacy VDL_GLOBAL_CONFIG_PATH), falling back to ./config.ini.
• Or the GT_DATAMART_PG_* environment variables, used by the read-only client. See Read-only Python client.

The ingest path uses config.ini; the read-only client uses the environment variables. They are independent — set whichever you need.

3. Source data

Giving Tuesday's source CSVs live in the public-read S3 bucket gt990datalake-analytics-and-datamarts (no AWS credentials required). The pipeline lists the bucket and picks the newest matching file for each registered source automatically. The IRS Business Master File is six plain CSVs under https://www.irs.gov/pub/irs-soi/ (eo1–eo4, eo_xx, eo_pr, about 340 MB together), republished in place roughly monthly; the pipeline versions them by the server's Last-Modified.

Usage

All commands run as Python modules. Output is plain text tables, easy to pipe or eyeball.

Inspect

# What's in S3 right now? (no DB connection)
python -m givingtuesday_datamart.sources status

# What's loaded into gt_datamart, at what version, how long ago?
python -m givingtuesday_datamart.sources loaded

status lists each registered source alongside the newest matching file in S3 — version date, filename, size. loaded queries the run-history table and shows the latest run per source: status (success / failed / skipped / never), version, row count, last-ingested-at, age, and a count of validation warnings. Failed runs and validation warnings are re-printed below the table so they don't disappear into JSONB.

以上為 README 的開頭部分。其餘內容請閱讀原文。 檢視完整 README ↗