ADR: Multi-ISO schema strategy — universal vs ISO-specific columns

[haydenk]

Decision needed

How should we structure the data model so ERCOT's 24 fields can coexist with future ISOs (CAISO, MISO, NYISO, ISO-NE, PJM, CENACE) that have wildly different field sets?

Options

A. Wide single table with NULLable ISO-specific columns

• One service_points table holds the union of all ISO fields
• Pros: simple queries; one schema
• Cons: column count explodes; semantically unclear (which fields apply to which ISO?); migrations on millions of rows

B. Universal columns + JSONB iso_attributes

• service_points has only universal columns (id, iso, address, city, state, zip, status, is_active, search_vector, timestamps); ERCOT-specific fields live in iso_attributes JSONB
• Pros: schema stable; cheap to add an ISO; queryable via JSONB operators
• Cons: typeless; harder to index specific fields; query verbosity

C. Universal columns + per-ISO sidecar tables

• service_points (universal) + service_points_ercot_attrs (FK + ERCOT columns) + service_points_caiso_attrs, etc.
• Pros: typed; indexable; clear ownership per ISO
• Cons: more migrations; joins for full-record queries

Required output

A short ADR at docs/adr/0001-multi-iso-schema-strategy.md documenting:

• The choice and why
• Migration path from current esiids table
• How Add settlement_load_zone to the esiids schema and ETL #16 (settlement_load_zone), Reconcile esiid_documents table design with CLAUDE.md #20 (esiid_documents), Parse and store publish_date from ERCOT filenames #21 (publish_date) should land in light of the decision

Why now

This decision needs to land before more v0.2.0 schema work continues, otherwise issues like #16 and #20 risk being done one way and redone in v0.4.0.

Blocks

• v0.4.0 multi-ISO schema implementation
• Add settlement_load_zone to the esiids schema and ETL #16 (settlement_load_zone may land in sidecar/JSONB rather than esiids directly)
• Reconcile esiid_documents table design with CLAUDE.md #20 (table naming + structure)

Context

See docs/multi-iso-plan.md for the broader multi-ISO architectural plan.