public_actors

Data license: Public Domain (U.S. Government data) · Data source: Federal Register API & Regulations.gov API

13,479,510 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: role_start_date (date), role_end_date (date), created_at (date), updated_at (date)

Next page

CREATE TABLE public_actors (
    public_actor_id INTEGER PRIMARY KEY,
    role_context TEXT NOT NULL,          -- 'congress_member' | 'fara_agent' | 'fec_candidate'
                                         -- | 'oge_pas_filer' | 'sec_form4_reporter'
                                         -- | 'nonprofit_officer' | 'lobbyist_lda'
    source_id TEXT NOT NULL,             -- '{id_part}:{normalized_name}' or bare id — see doc
    name_full TEXT NOT NULL,
    name_normalized TEXT NOT NULL,
    name_first TEXT,
    name_last TEXT,

    -- Canonical IDs (populated where applicable)
    bioguide_id TEXT,
    fec_cand_id TEXT,
    fara_registration_number TEXT,
    oge_filer_slug TEXT,
    sec_reporter_cik INTEGER,

    -- Employer/organization
    employer_entity_id INTEGER,
    employer_entity_name TEXT,
    employer_ein TEXT,
    employer_cik INTEGER,
    employer_lda_registrant_id INTEGER,

    -- Role details
    role_title TEXT,
    role_start_date TEXT,
    role_end_date TEXT,
    is_current INTEGER,

    -- Materiality (two-pass: Pass 1 sets defaults; Pass 2 refines)
    materiality_tier INTEGER NOT NULL DEFAULT 2,
    materiality_reason TEXT,

    -- Merge tracking (Model 2 — deterministic evidence only)
    merged_into_public_actor_id INTEGER,
    merge_method TEXT,                   -- 'bioguide_match' | 'fec_crosswalk' | 'cik_match' | 'manual'
    merge_confidence REAL,

    -- Provenance
    first_seen_date TEXT,
    last_seen_date TEXT,
    source_table TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    notes TEXT, dd_id TEXT GENERATED ALWAYS AS ('DD-P-' || public_actor_id) VIRTUAL,

    UNIQUE(role_context, source_id),
    FOREIGN KEY (employer_entity_id) REFERENCES entities(entity_id),
    FOREIGN KEY (merged_into_public_actor_id) REFERENCES public_actors(public_actor_id),
    CHECK (merged_into_public_actor_id IS NULL OR merged_into_public_actor_id != public_actor_id)
);
CREATE INDEX idx_pa_name_norm ON public_actors(name_normalized);
CREATE INDEX idx_pa_role_context ON public_actors(role_context);
CREATE INDEX idx_pa_bioguide ON public_actors(bioguide_id) WHERE bioguide_id IS NOT NULL;
CREATE INDEX idx_pa_fec_cand ON public_actors(fec_cand_id) WHERE fec_cand_id IS NOT NULL;
CREATE INDEX idx_pa_fara ON public_actors(fara_registration_number) WHERE fara_registration_number IS NOT NULL;
CREATE INDEX idx_pa_employer ON public_actors(employer_entity_id) WHERE employer_entity_id IS NOT NULL;
CREATE INDEX idx_pa_merged ON public_actors(merged_into_public_actor_id) WHERE merged_into_public_actor_id IS NOT NULL;
CREATE INDEX idx_pa_tier ON public_actors(materiality_tier);