entities

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

0 rows where merged_into_entity_id = 1630499

✎ View and edit SQL

This data as json

0 records

CREATE TABLE entities (
    entity_id INTEGER PRIMARY KEY,
    canonical_name TEXT NOT NULL,
    name_normalized TEXT NOT NULL,
    display_name TEXT,                   -- lazy; NULL falls back to canonical_name
    entity_type TEXT,                    -- 'public_company' | 'private_company' | 'nonprofit'
                                         -- | 'foreign_entity' | 'pac' | 'trade_association'
                                         -- | 'union' | 'political_committee' | 'unknown'
    ein TEXT,                            -- IRS (9 digits)
    cik INTEGER,                         -- SEC Central Index Key
    uei TEXT,                            -- SAM.gov Unique Entity Identifier (12 chars)
    lei TEXT,                            -- GLEIF Legal Entity Identifier (20 chars)
    ticker TEXT,                         -- SEC primary ticker, UPPERCASE
    fec_committee_id TEXT,               -- FEC 9-char C00XXXXXX (for PACs/parties)
    duns TEXT,                           -- legacy D&B; federal-adopted subset only
    sic_code TEXT,
    naics_code TEXT,
    ntee_code TEXT,
    state_of_incorporation TEXT,
    primary_state TEXT,                  -- 2-letter USPS (country='US') or ISO-3166-2 subdivision code
                                         -- without country prefix; country lives in country col
    country TEXT,                        -- 2-letter ISO code
    status TEXT,                         -- 'active' | 'merged' | 'defunct' | 'unknown'
    merged_into_entity_id INTEGER,
    source_provenance TEXT,              -- comma-separated set from {'sec', 'bmf', 'gleif',
                                         -- 'fec', 'sam', 'manual'}; tracks which sources
                                         -- contributed to this entity. Unordered set semantics.
    first_seen_date TEXT,
    last_seen_date TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    notes TEXT, dd_id TEXT GENERATED ALWAYS AS ('DD-E-' || entity_id) VIRTUAL, predecessor_names TEXT, primary_industry TEXT, industry_codes TEXT, naics_description TEXT, psc_code TEXT,
    FOREIGN KEY (merged_into_entity_id) REFERENCES entities(entity_id),
    CHECK (merged_into_entity_id IS NULL OR merged_into_entity_id != entity_id)
);
CREATE UNIQUE INDEX uq_entity_ein ON entities(ein) WHERE ein IS NOT NULL;
CREATE UNIQUE INDEX uq_entity_cik ON entities(cik) WHERE cik IS NOT NULL;
CREATE UNIQUE INDEX uq_entity_uei ON entities(uei) WHERE uei IS NOT NULL;
CREATE UNIQUE INDEX uq_entity_lei ON entities(lei) WHERE lei IS NOT NULL;
CREATE UNIQUE INDEX uq_entity_fec_cmte ON entities(fec_committee_id) WHERE fec_committee_id IS NOT NULL;
CREATE INDEX idx_entity_name_norm ON entities(name_normalized);
CREATE INDEX idx_entity_name_state ON entities(name_normalized, primary_state);
CREATE INDEX idx_entity_ticker ON entities(ticker) WHERE ticker IS NOT NULL;
CREATE INDEX idx_entity_type ON entities(entity_type);
CREATE INDEX idx_entity_provenance ON entities(source_provenance) WHERE source_provenance IS NOT NULL;