schema.sql

-- Schema for the data-center grid demand analysis.
-- SQLite dialect. Built by src/build_db.py, which loads the EIA source
-- files listed in the README into these tables.

PRAGMA foreign_keys = ON;

-- Dimension: US states + DC with Census groupings, loaded from
-- data/reference/states.csv.
CREATE TABLE states (
    state_code      TEXT PRIMARY KEY,          -- two-letter postal code
    state_name      TEXT NOT NULL,
    census_region   TEXT NOT NULL,             -- Northeast / Midwest / South / West
    census_division TEXT NOT NULL
);

-- EIA-861 annual retail electricity sales by state and customer sector,
-- 1990-2024. One row per state-year-sector. The source file also carries
-- a precomputed "total" sector; we drop it and derive totals with SUM so
-- the table stays normalized (build_db.py verifies the sum matches).
CREATE TABLE retail_sales (
    year                 INTEGER NOT NULL,
    state_code           TEXT    NOT NULL REFERENCES states(state_code),
    sector               TEXT    NOT NULL CHECK (sector IN
                             ('residential', 'commercial', 'industrial', 'transportation')),
    revenue_thousand_usd REAL,
    sales_mwh            REAL,
    customers            INTEGER,               -- customer count; sparse before 2008
    avg_price_cents_kwh  REAL,
    PRIMARY KEY (year, state_code, sector)
);

-- EIA-923 annual net generation by state, producer type, and energy source,
-- 1990-2024. Kept in the source's long format. "Total" rows for producer
-- type and energy source are retained because the source publishes them
-- (analysis queries filter explicitly to avoid double counting).
CREATE TABLE net_generation (
    year           INTEGER NOT NULL,
    state_code     TEXT    NOT NULL REFERENCES states(state_code),
    producer_type  TEXT    NOT NULL,
    energy_source  TEXT    NOT NULL,
    generation_mwh REAL,
    PRIMARY KEY (year, state_code, producer_type, energy_source)
);

-- EIA-860 generator inventory (2024 vintage): every utility-scale generator
-- that is operable, proposed, or recently retired/canceled. One row per
-- plant + generator id per status group.
CREATE TABLE generators (
    plant_code          INTEGER NOT NULL,
    generator_id        TEXT    NOT NULL,
    status_group        TEXT    NOT NULL CHECK (status_group IN
                            ('operable', 'proposed', 'retired_canceled')),
    utility_id          INTEGER,
    utility_name        TEXT,
    plant_name          TEXT,
    state_code          TEXT REFERENCES states(state_code),
    county              TEXT,
    sector_name         TEXT,
    technology          TEXT,
    prime_mover         TEXT,
    energy_source       TEXT,
    nameplate_mw        REAL,
    summer_mw           REAL,
    status              TEXT,     -- EIA status code, e.g. OP, SB, P, U, RE
    operating_year      INTEGER,  -- first year online (operable / retired)
    expected_online_year INTEGER, -- current expected in-service year (proposed)
    retirement_year     INTEGER,  -- actual retirement year (retired sheet)
    planned_retirement_year INTEGER,
    PRIMARY KEY (plant_code, generator_id, status_group)
);

CREATE INDEX idx_generators_state  ON generators(state_code, status_group);
CREATE INDEX idx_generators_tech   ON generators(technology);

-- Assumed fleet-average capacity factors used to translate proposed
-- nameplate MW into an annual generation estimate. These are assumptions,
-- not measurements: values approximate recent US fleet averages published
-- in EIA's Electric Power Monthly (Table 6.07). Every number derived from
-- this table is labeled an estimate in the analysis.
CREATE TABLE capacity_factors (
    technology  TEXT PRIMARY KEY,   -- matches generators.technology
    assumed_cf  REAL NOT NULL,
    note        TEXT
);

INSERT INTO capacity_factors (technology, assumed_cf, note) VALUES
    ('Nuclear',                                0.93, 'EPM fleet average'),
    ('Natural Gas Fired Combined Cycle',       0.58, 'EPM fleet average'),
    ('Natural Gas Fired Combustion Turbine',   0.12, 'peaker duty cycle'),
    ('Natural Gas Internal Combustion Engine', 0.20, 'EPM fleet average'),
    ('Natural Gas Steam Turbine',              0.10, 'legacy steam duty cycle'),
    ('Conventional Steam Coal',                0.42, 'EPM fleet average'),
    ('Conventional Hydroelectric',             0.36, 'EPM fleet average'),
    ('Onshore Wind Turbine',                   0.33, 'EPM fleet average'),
    ('Offshore Wind Turbine',                  0.40, 'EPM assumption, small US fleet'),
    ('Solar Photovoltaic',                     0.23, 'EPM fleet average'),
    ('Batteries',                              0.00, 'storage shifts energy; adds none'),
    ('Geothermal',                             0.69, 'EPM fleet average'),
    ('Wood/Wood Waste Biomass',                0.60, 'EPM fleet average'),
    ('Landfill Gas',                           0.68, 'EPM fleet average');