path: root/migrations/001_raw_schema.sql

-- 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;