wh_visits

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

7,656,968 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: appt_start (date), appt_end (date), appt_start_iso (date)

Next page

CREATE TABLE wh_visits (
    visit_id           INTEGER PRIMARY KEY AUTOINCREMENT,
    admin              TEXT NOT NULL,                 -- 'obama' | 'biden'
    appt_start         TEXT,                          -- ISO datetime
    appt_end           TEXT,
    appt_made          TEXT,
    -- Visitor (conditional publication)
    visitor_name_full  TEXT,                          -- NULL for tour visitors (PII-aggregate)
    visitor_name_hash  TEXT NOT NULL,                 -- Always present; for distinct-count queries
    visitor_is_tour    INTEGER NOT NULL,              -- 1 if classified as tour → name redacted
    -- Visitee (always published)
    visitee_name_last  TEXT,
    visitee_name_first TEXT,
    visitee_full       TEXT,                          -- derived: "First Last" for display
    visitee_public_actor_id INTEGER,                  -- resolved via public_actors match
    -- Context
    meeting_loc        TEXT,
    meeting_room       TEXT,
    description        TEXT,
    total_people       INTEGER,
    access_type        TEXT,
    post               TEXT,                          -- Secret Service post that processed
    source_file        TEXT,                          -- original file origin
    uin                TEXT
, visitor_public_actor_id INTEGER, visitor_is_lda_lobbyist INTEGER DEFAULT 0, visitor_match_confidence REAL, appt_start_iso TEXT, visitor_is_plum_book INTEGER DEFAULT 0);
CREATE INDEX idx_wv_admin ON wh_visits(admin);
CREATE INDEX idx_wv_appt ON wh_visits(appt_start);
CREATE INDEX idx_wv_visitee ON wh_visits(visitee_name_last, visitee_name_first);
CREATE INDEX idx_wv_visitor_hash ON wh_visits(visitor_name_hash);
CREATE INDEX idx_wv_visitee_actor ON wh_visits(visitee_public_actor_id);
CREATE INDEX idx_wv_tour ON wh_visits(visitor_is_tour);