entity_relationships

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

405,304 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: relationship_type, source, created_at (date)

Next page

CREATE TABLE entity_relationships (
    relationship_id INTEGER PRIMARY KEY,
    parent_entity_id INTEGER,            -- NULL when parent is unknown (e.g., BMF GEN with no resolved central)
    child_entity_id INTEGER NOT NULL,
    relationship_type TEXT NOT NULL,     -- 'subsidiary_of' | 'parent_of' | 'affiliated_pac'
                                         -- | 'founder_family' | 'foreign_controlled' | 'joint_venture'
                                         -- | 'predecessor_to' | 'successor_to'
                                         -- | 'bmf_group_exemption' | 'lobbying_affiliate'
    source TEXT NOT NULL,                -- 'bmf' | 'sec_exhibit_21' | 'lobbying_affiliated_orgs'
                                         -- | 'gleif_successor' | 'fec_connected_org' | 'manual'
    source_ref TEXT,                     -- filing_uuid / accession_number / GEN / etc.
    gen TEXT,                            -- IRS Group Exemption Number when source='bmf'
    effective_start TEXT,
    effective_end TEXT,
    confidence_score REAL NOT NULL DEFAULT 1.0,
    notes TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY (parent_entity_id) REFERENCES entities(entity_id),
    FOREIGN KEY (child_entity_id) REFERENCES entities(entity_id),
    CHECK (parent_entity_id IS NULL OR parent_entity_id != child_entity_id)
);
CREATE INDEX idx_er_parent ON entity_relationships(parent_entity_id) WHERE parent_entity_id IS NOT NULL;
CREATE INDEX idx_er_child ON entity_relationships(child_entity_id);
CREATE INDEX idx_er_type ON entity_relationships(relationship_type);
CREATE INDEX idx_er_gen ON entity_relationships(gen) WHERE gen IS NOT NULL;
CREATE INDEX idx_er_source ON entity_relationships(source);
CREATE INDEX idx_er_gen_child ON entity_relationships(gen, child_entity_id);