-- Migration 001: Raw ingestion schema -- -- Design principles: -- - Every row traces back to: which ingest run, which source system, -- which document, and which filing. -- - raw.filing is the dedup anchor: one row per unique filing per source system. -- - Per-form tables (raw.form_990, raw.form_990pf, raw.form_990ez) hold -- filing-level summary fields, 1:1 with raw.filing. -- - Grant tables stay split by form type (different column sets). -- - raw.filing_source tracks every place a filing was seen (which archive, which path). -- - IRS manifest data lives in its own table for completeness auditing. -- - Nothing here touches canonicalization — that's a later layer. -- -- Parser conventions (EIN normalization, source_document_id derivation, -- line_number stability, amount parsing, placeholder detection) are -- enforced in code at scripts/common/, not in documentation. BEGIN; CREATE SCHEMA IF NOT EXISTS raw; -- ============================================================ -- 1. Reference tables -- ============================================================ -- Data sources. Add rows as new sources come online — no schema changes needed. CREATE TABLE IF NOT EXISTS raw.source_system ( code TEXT PRIMARY KEY, -- e.g. 'irs_xml', 'ny_ag' display_name TEXT NOT NULL, url TEXT, -- home page or API docs notes TEXT ); -- IRS form types. Includes amended and other variants. -- New form types from future sources get a row here, not a schema change. CREATE TABLE IF NOT EXISTS raw.form_type ( code TEXT PRIMARY KEY, -- e.g. '990', '990PF' display_name TEXT NOT NULL, notes TEXT ); -- Grant detail completeness status. -- Tracks whether parsed grant rows fully represent the filing's grants. CREATE TABLE IF NOT EXISTS raw.grant_detail_status ( code TEXT PRIMARY KEY, display_name TEXT NOT NULL, notes TEXT ); -- Reference tables are seeded by scripts/seed.py, not by this migration. -- ============================================================ -- 2. Ingest tracking -- ============================================================ -- One row per invocation of a parser/loader script. CREATE TABLE IF NOT EXISTS raw.ingest_run ( id SERIAL PRIMARY KEY, parser TEXT NOT NULL, -- e.g. 'parse_990pf', 'parse_990_schedule_i' source_system TEXT NOT NULL REFERENCES raw.source_system(code), started_at TIMESTAMPTZ NOT NULL DEFAULT now(), finished_at TIMESTAMPTZ, status TEXT NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'done', 'error')), files_scanned INT, files_matched INT, rows_inserted INT, notes TEXT -- free-form (CLI args, etc.) ); -- Per-file errors during ingestion. CREATE TABLE IF NOT EXISTS raw.ingest_error ( id SERIAL PRIMARY KEY, ingest_run_id INT REFERENCES raw.ingest_run(id), source_archive TEXT, -- zip filename or equivalent container source_path TEXT, -- file within archive, or standalone file path source_document_id TEXT, -- parsed document id, if available at error time stage TEXT, -- processing stage: 'read', 'parse_xml', 'extract', 'db_write', etc. error TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- ============================================================ -- 3. IRS filing manifest (from IRS index CSVs) -- ============================================================ -- One row per (object_id, index_year) pair. The same filing can appear in -- multiple annual index releases, so object_id alone is not unique here. CREATE TABLE IF NOT EXISTS raw.irs_manifest ( id SERIAL PRIMARY KEY, return_id TEXT, filing_type TEXT, -- 'EFILE' ein TEXT NOT NULL, tax_period TEXT, -- YYYYMM sub_date TEXT, -- submission date as published taxpayer_name TEXT, return_type TEXT NOT NULL, -- '990', '990EZ', '990PF', amended variants dln TEXT, object_id TEXT NOT NULL, index_year INT NOT NULL, -- which year's index CSV this came from UNIQUE (object_id, index_year) ); CREATE INDEX IF NOT EXISTS idx_raw_irs_manifest_ein ON raw.irs_manifest (ein); CREATE INDEX IF NOT EXISTS idx_raw_irs_manifest_return_type ON raw.irs_manifest (return_type); CREATE INDEX IF NOT EXISTS idx_raw_irs_manifest_object_id ON raw.irs_manifest (object_id); -- ============================================================ -- 4. Raw filing table (dedup anchor) -- ============================================================ -- One row per unique filing we have actually ingested. -- Unique on (source_system, source_document_id): object_id works for IRS XML, -- but other sources will have their own document identifiers. -- -- For source_system = 'irs_xml', source_document_id is the object_id, -- which can be joined to irs_manifest.object_id for reconciliation. CREATE TABLE IF NOT EXISTS raw.filing ( id SERIAL PRIMARY KEY, source_system TEXT NOT NULL REFERENCES raw.source_system(code), source_document_id TEXT NOT NULL, -- IRS: object_id; NY AG: filing_id; etc. ein TEXT NOT NULL CHECK (ein ~ '^\d{9}$'), filer_name TEXT, form_type TEXT NOT NULL REFERENCES raw.form_type(code), tax_year INT, tax_period_begin DATE, -- from XML TaxPeriodBeginDt tax_period_end DATE, -- from XML TaxPeriodEndDt return_version TEXT, return_timestamp TIMESTAMPTZ, -- from XML ReturnTs source_url TEXT, -- canonical lookup URL where available ingest_run_id INT REFERENCES raw.ingest_run(id), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (source_system, source_document_id) ); CREATE INDEX IF NOT EXISTS idx_raw_filing_ein ON raw.filing (ein); CREATE INDEX IF NOT EXISTS idx_raw_filing_form_type ON raw.filing (form_type); CREATE INDEX IF NOT EXISTS idx_raw_filing_source_document_id ON raw.filing (source_document_id); CREATE INDEX IF NOT EXISTS idx_raw_filing_tax_year ON raw.filing (tax_year); -- ============================================================ -- 5. Filing provenance (where each filing was seen) -- ============================================================ -- A filing can appear in multiple archives (overlapping ZIPs, re-releases). -- This table records every sighting so we can debug path inconsistencies -- and understand archive overlap without losing information. -- -- The UNIQUE on (source_archive, source_path) prevents the same archive/path -- from mapping to different filings — a given file in a given ZIP is one filing. CREATE TABLE IF NOT EXISTS raw.filing_source ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL REFERENCES raw.filing(id), ingest_run_id INT REFERENCES raw.ingest_run(id), source_archive TEXT NOT NULL, -- e.g. 'download990xml_2020_1.zip' source_path TEXT NOT NULL, -- e.g. 'Cycles_202042_202052/202013089349101246_public.xml' seen_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (source_archive, source_path) ); CREATE INDEX IF NOT EXISTS idx_raw_filing_source_archive ON raw.filing_source (source_archive); CREATE INDEX IF NOT EXISTS idx_raw_filing_source_filing ON raw.filing_source (raw_filing_id); -- ============================================================ -- 6. Filing summary tables (per form type, 1:1 with raw.filing) -- ============================================================ -- Form 990: public charity / large exempt org filing summary. -- Holds Part I summary, balance sheet totals, governance counts, -- and Schedule I metadata (since Schedule I is 1:1 with the 990 filing). CREATE TABLE IF NOT EXISTS raw.form_990 ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL UNIQUE REFERENCES raw.filing(id), -- filer identity (as reported on this specific return) filer_name2 TEXT, -- BusinessNameLine2Txt filer_address_line1 TEXT, filer_address_line2 TEXT, filer_city TEXT, filer_state TEXT, filer_zip TEXT, filer_country TEXT, -- only for foreign filers filer_foreign_postal_code TEXT, phone TEXT, website TEXT, -- classification is_501c3 BOOLEAN, section_501c_type TEXT, -- '4', '6', '7', etc. if not 501c3 org_type TEXT, -- 'corp', 'trust', 'assoc', 'other' group_return BOOLEAN, group_exemption_num TEXT, formation_year TEXT, legal_domicile_state TEXT, mission TEXT, accounting_method TEXT, -- 'cash', 'accrual', 'other' -- filing status flags is_amended BOOLEAN DEFAULT false, is_final BOOLEAN DEFAULT false, is_initial BOOLEAN DEFAULT false, is_terminated BOOLEAN DEFAULT false, -- Part I: current year summary gross_receipts NUMERIC, cy_contributions_grants NUMERIC, cy_program_service_revenue NUMERIC, cy_investment_income NUMERIC, cy_other_revenue NUMERIC, cy_total_revenue NUMERIC, cy_grants_paid NUMERIC, cy_benefits_to_members NUMERIC, cy_salaries_benefits NUMERIC, cy_fundraising_expense NUMERIC, cy_other_expenses NUMERIC, cy_total_expenses NUMERIC, cy_revenue_less_expenses NUMERIC, -- Part I: prior year summary (NULL for first-year filers) py_total_revenue NUMERIC, py_total_expenses NUMERIC, -- balance sheet (Part I summary / Part X) total_assets_boy NUMERIC, total_assets_eoy NUMERIC, total_liabilities_boy NUMERIC, total_liabilities_eoy NUMERIC, net_assets_boy NUMERIC, net_assets_eoy NUMERIC, -- governance / workforce total_employees INT, total_volunteers INT, voting_members INT, independent_voting_members INT, -- Part IX: functional expense breakdown program_services_expense NUMERIC, management_general_expense NUMERIC, fundraising_expense_ix NUMERIC, -- Part IX col D total -- Part VIII: revenue detail government_grants NUMERIC, total_contributions NUMERIC, total_program_service_rev NUMERIC, investment_income NUMERIC, -- UBI gross_ubi NUMERIC, net_ubi NUMERIC, -- Schedule I metadata (1:1 with filing) sched_i_grant_records_maintained BOOLEAN, sched_i_501c3_org_count INT, sched_i_other_org_count INT, sched_i_total_grants_amt NUMERIC, -- for reconciliation vs grant rows -- grant detail completeness grant_detail_status TEXT NOT NULL DEFAULT 'unresolved' REFERENCES raw.grant_detail_status(code), -- officer / signer principal_officer TEXT, officer_name TEXT, officer_title TEXT, signature_date DATE, preparer_firm TEXT ); -- Form 990-PF: private foundation filing summary. CREATE TABLE IF NOT EXISTS raw.form_990pf ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL UNIQUE REFERENCES raw.filing(id), -- filer identity (as reported on this specific return) filer_name2 TEXT, filer_address_line1 TEXT, filer_address_line2 TEXT, filer_city TEXT, filer_state TEXT, filer_zip TEXT, filer_country TEXT, filer_foreign_postal_code TEXT, phone TEXT, website TEXT, -- classification is_501c3_pf BOOLEAN, is_4947a1_trust BOOLEAN, is_private_operating BOOLEAN, accounting_method TEXT, -- 'cash', 'accrual' -- filing status flags is_amended BOOLEAN DEFAULT false, is_final BOOLEAN DEFAULT false, is_initial BOOLEAN DEFAULT false, -- Part I: revenue and expenses contributions_received NUMERIC, interest_revenue NUMERIC, dividends_revenue NUMERIC, net_gain_sale_assets NUMERIC, total_revenue NUMERIC, total_net_investment_income NUMERIC, compensation_officers NUMERIC, total_operating_expenses NUMERIC, contributions_paid NUMERIC, total_expenses NUMERIC, total_charitable_disbursements NUMERIC, excess_revenue_over_expenses NUMERIC, net_investment_income NUMERIC, adjusted_net_income NUMERIC, -- Part II: balance sheets total_assets_boy NUMERIC, total_assets_eoy NUMERIC, total_assets_eoy_fmv NUMERIC, total_liabilities_boy NUMERIC, total_liabilities_eoy NUMERIC, net_assets_boy NUMERIC, net_assets_eoy NUMERIC, fmv_assets_eoy NUMERIC, -- cover-page FMV figure -- Part X-XII: distribution calculations minimum_investment_return NUMERIC, distributable_amount NUMERIC, qualifying_distributions NUMERIC, -- excise tax excise_tax_amount NUMERIC, -- Part XV: supplementary information totals (for reconciliation) total_grants_paid NUMERIC, total_grants_approved_future NUMERIC, -- state registration state_of_registration TEXT, -- grant detail completeness grant_detail_status TEXT NOT NULL DEFAULT 'unresolved' REFERENCES raw.grant_detail_status(code), -- officer / signer officer_name TEXT, officer_title TEXT, signature_date DATE, preparer_firm TEXT ); -- Form 990-EZ: smaller exempt org filing summary. -- Parser not yet built, but schema should exist so the table set is complete. CREATE TABLE IF NOT EXISTS raw.form_990ez ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL UNIQUE REFERENCES raw.filing(id), -- filer identity filer_name2 TEXT, filer_address_line1 TEXT, filer_city TEXT, filer_state TEXT, filer_zip TEXT, filer_country TEXT, phone TEXT, website TEXT, -- classification is_501c3 BOOLEAN, section_501c_type TEXT, group_exemption_num TEXT, -- filing status flags is_amended BOOLEAN DEFAULT false, is_final BOOLEAN DEFAULT false, is_initial BOOLEAN DEFAULT false, -- Part I: revenue, expenses, assets gross_receipts NUMERIC, contributions_gifts_grants NUMERIC, program_service_revenue NUMERIC, investment_income NUMERIC, total_revenue NUMERIC, grants_paid NUMERIC, salaries_compensation NUMERIC, total_expenses NUMERIC, revenue_less_expenses NUMERIC, total_assets_boy NUMERIC, total_assets_eoy NUMERIC, total_liabilities_boy NUMERIC, total_liabilities_eoy NUMERIC, net_assets_boy NUMERIC, net_assets_eoy NUMERIC, -- grant detail completeness -- 990-EZ grant detail often lives in Schedule O or attachments, -- not in a structured repeated table like Schedule I. grant_detail_status TEXT NOT NULL DEFAULT 'unresolved' REFERENCES raw.grant_detail_status(code), has_schedule_o BOOLEAN, -- officer / signer officer_name TEXT, officer_title TEXT, signature_date DATE, preparer_firm TEXT ); -- ============================================================ -- 7. Raw grant tables (per form type) -- ============================================================ -- 990-PF Part XV grants (private foundation → recipient) -- -- line_number is the ordinal position of this grant within the filing's -- grant list (1-based). Together with raw_filing_id it forms a natural -- dedup key: re-ingesting the same filing cannot create duplicate rows. CREATE TABLE IF NOT EXISTS raw.grant_990pf ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL REFERENCES raw.filing(id), line_number INT NOT NULL, -- recipient fields recipient_name TEXT, recipient_name2 TEXT, recipient_person_name TEXT, -- non-null = individual grant address_line1 TEXT, address_line2 TEXT, city TEXT, state TEXT, zip TEXT, country TEXT, foreign_postal_code TEXT, -- grant fields (raw text preserved, typed column alongside) amount_raw TEXT, amount NUMERIC, purpose TEXT, foundation_status TEXT, relationship TEXT, UNIQUE (raw_filing_id, line_number) ); CREATE INDEX IF NOT EXISTS idx_raw_grant_990pf_filing ON raw.grant_990pf (raw_filing_id); -- 990 Schedule I grants (public charity → recipient) CREATE TABLE IF NOT EXISTS raw.grant_990 ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL REFERENCES raw.filing(id), line_number INT NOT NULL, -- recipient fields recipient_name TEXT, recipient_name2 TEXT, recipient_ein TEXT CHECK (recipient_ein IS NULL OR recipient_ein ~ '^\d{9}$'), address_line1 TEXT, address_line2 TEXT, city TEXT, state TEXT, zip TEXT, country TEXT, foreign_postal_code TEXT, -- grant fields (raw text preserved, typed columns alongside) cash_grant_amt_raw TEXT, cash_grant_amt NUMERIC, non_cash_amt_raw TEXT, non_cash_amt NUMERIC, non_cash_desc TEXT, valuation_method TEXT, purpose TEXT, irc_section TEXT, UNIQUE (raw_filing_id, line_number) ); CREATE INDEX IF NOT EXISTS idx_raw_grant_990_filing ON raw.grant_990 (raw_filing_id); -- ============================================================ -- 8. Schedule O / narrative text -- ============================================================ -- Preserves narrative text from Schedule O and supplemental information. -- Both 990 and 990-EZ push grant detail and explanatory text here. -- Not fully structured — stores raw text blocks with whatever section -- reference the filing provides. CREATE TABLE IF NOT EXISTS raw.schedule_o ( id SERIAL PRIMARY KEY, raw_filing_id INT NOT NULL REFERENCES raw.filing(id), line_number INT NOT NULL, -- ordinal within the schedule form_line_ref TEXT, -- e.g. 'SCHEDULE I, PART I, LINE 2' explanation TEXT, UNIQUE (raw_filing_id, line_number) ); CREATE INDEX IF NOT EXISTS idx_raw_schedule_o_filing ON raw.schedule_o (raw_filing_id); -- ============================================================ -- 10. BMF (organization registry reference source) -- ============================================================ -- Current-snapshot IRS EO BMF rows, minimally transformed from the published -- CSVs. This is a registry/reference source, not a filing source. CREATE TABLE IF NOT EXISTS raw.bmf ( ein TEXT PRIMARY KEY CHECK (ein ~ '^\d{9}$'), name TEXT, ico TEXT, street TEXT, city TEXT, state TEXT, zip TEXT, grp TEXT, subsection TEXT, affiliation TEXT, classification TEXT, ruling TEXT, deductibility TEXT, foundation TEXT, activity TEXT, organization TEXT, status TEXT, tax_period TEXT, asset_cd TEXT, income_cd TEXT, filing_req_cd TEXT, pf_filing_req_cd TEXT, acct_pd TEXT, asset_amt BIGINT, income_amt BIGINT, revenue_amt BIGINT, ntee_cd TEXT, sort_name TEXT, source_file TEXT, ingest_run_id INT REFERENCES raw.ingest_run(id), created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_raw_bmf_name ON raw.bmf (name); CREATE INDEX IF NOT EXISTS idx_raw_bmf_state ON raw.bmf (state); CREATE INDEX IF NOT EXISTS idx_raw_bmf_ntee_cd ON raw.bmf (ntee_cd); COMMIT;