Workspace architecture

Single-table multi-tenant Postgres with RLS + a sparse EAV override layer. One pattern, three substrates: configuration (what users edit), audit + lineage (what happened), computed (what's been derived). All three sit on a shared reference foundation that public + tenant rows co-occupy via workspace_id + Postgres RLS per DD-016. Adding a new analytical dimension is schema-additive, not architecturally novel.

live · 5s

Schema rows are queried live from information_schema.columns + pg_indexes — always honest. For the full prose, read tenant_workspace_v2 README, architecture overview, schema reference, governance, and decisions.md.

Portfolios

Assets

Variants

Overrides

Audit events

109

Sessions

Stale outputs

configuration · audit + lineage · modeling · Your session has performed 0 actions · fa2c9fb8-e42…

Reference foundation

DD-016 unified storage

The shared data layer. Public EIA-derived rows (workspace_id IS NULL) live alongside tenant-private rows in the same physical table; Postgres RLS draws the boundary at read time per DD-016. Legacy `engineering_plant` + `engineering_generator` were dropped in 04m.12 — substrate is the only engineering grain.

Read the canonical doc

plants
Public reference — one row per US plant, JSONB blob of EIA-derived facts. Workspaces never modify; they layer overrides via `tenant_overrides`.
data jsonb NOT NULL
data_hash text
data_provenance jsonb NOT NULL
id uuid NOT NULL
eia_plant_id integer
slug text NOT NULL
source_type text NOT NULL
workspace_id uuid
creation_method text NOT NULL
Indexes
- plants_pkeyPK
- plants_eia_plant_id_uniqueUNIQUE
- plants_id_uniqueUNIQUE
- plants_slug_uniqueUNIQUE
- plants_workspace_id_idx

generators

Public reference — one row per generator. Carries denormalized `tech_class` (DD-053) so engineering lookups don't need the substrate.

id	uuid	NOT NULL
plant_id	uuid	NOT NULL
eia_generator_code	text
source_type	text	NOT NULL
workspace_id	uuid
creation_method	text	NOT NULL
prime_mover_code	text
fuel_code	text
technology	text
nameplate_mw	numeric
net_summer_capacity_mw	numeric
net_winter_capacity_mw	numeric
status	text
status_code	text
in_service_date	date
planned_operation_date	date
retirement_date	date
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
data_provenance	jsonb	NOT NULL
template_id	uuid
tech_class	text

Indexes

generators_pkeyPK
generators_eia_natural_keyUNIQUE
generators_plant_id_idx
generators_tech_class_idx
generators_template_id_idx
generators_workspace_id_idx

plant_index

Typed projection of plant + gen rollups (`total_capacity_mw`, fuel mix, gen count). Refreshed by the pipeline; joined at read time.

plant_id	integer	NOT NULL
plant_name	text
state	text
county	text
lat	double precision
lon	double precision
primary_fuel	text
fuel_types	ARRAY
technologies	ARRAY
is_hybrid	boolean
total_capacity_mw	double precision
n_generators	integer
utility_eia_name	text
gem_entity_name	text
gem_parent_name	text
operating_year	integer
status	text
wikidata_qid	text
rank_state	integer
rank_national	integer
total_in_state	integer
total_in_nation	integer
iso	text
has_financial	boolean
has_pricing_node	boolean
annual_mwh	double precision
capacity_factor	double precision
has_wikipedia	boolean
has_news	boolean
status_detail	text
has_interconnection_queue	boolean
slug	text
canonical_owner	text
canonical_owner_all	ARRAY
grid_region	text
is_thermal	boolean
has_combined_cycle	boolean
has_dual_fuel	boolean
has_repower_planned	boolean
has_emissions	boolean
egrid_subregion_code	text
co2_rate_lb_per_mwh	double precision
ba_code	text
iso_rto_code	text
nerc_region_code	text
rc_code	text
status_code	text
has_corporate_ppa	boolean
offtaker_ids	ARRAY
offtaker_names	ARRAY
fts	tsvector

Indexes

plant_index_pkeyPK
idx_plant_index_ba_code
idx_plant_index_combined_cycle
idx_plant_index_dual_fuel
idx_plant_index_egrid_subregion
idx_plant_index_fts
idx_plant_index_fuel
idx_plant_index_has_corporate_ppa
idx_plant_index_has_emissions
idx_plant_index_is_thermal
idx_plant_index_iso_rto_code
idx_plant_index_nerc_region_code
idx_plant_index_offtaker_ids
idx_plant_index_offtaker_names
idx_plant_index_rc_code
idx_plant_index_repower_planned
idx_plant_index_state
plant_index_canonical_owner_trgm
plant_index_gem_parent_trgm
plant_index_name_trgm
plant_index_utility_trgm

engineering_subsystem

Substrate — one row per (plant or gen) × subsystem (PV_ARRAY, MOUNTING, ROTOR_ASSEMBLY, …). `data` JSONB carries subsystem-level facts.

subsystem_id	uuid	NOT NULL
workspace_id	uuid
plant_id	uuid	NOT NULL
generator_id	uuid
subsystem_code	text	NOT NULL
instance_no	integer	NOT NULL
data	jsonb	NOT NULL
data_provenance	jsonb	NOT NULL
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
variant_id	uuid
tombstone_of	uuid
geometry	jsonb
lat	numeric
lon	numeric
grid_substation_id	text

Indexes

engineering_subsystem_pkeyPK
engineering_subsystem_code_idx
engineering_subsystem_generator_idx
engineering_subsystem_plant_idx
engineering_subsystem_singleton_public_idxUNIQUE
engineering_subsystem_singleton_workspace_idxUNIQUE
engineering_subsystem_tombstone_idxUNIQUE
engineering_subsystem_variant_idx
engineering_subsystem_workspace_idx

engineering_component

Substrate — one row per component installed inside a subsystem (PV_MODULE, TRACKER, GEARBOX, …). `specs` JSONB carries component-level facts.

component_id	uuid	NOT NULL
workspace_id	uuid
subsystem_id	uuid	NOT NULL
component_code	text	NOT NULL
manufacturer	text
model	text
quantity	integer	NOT NULL
year_installed	integer
specs	jsonb	NOT NULL
data	jsonb	NOT NULL
data_provenance	jsonb	NOT NULL
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
variant_id	uuid
tombstone_of	uuid

Indexes

engineering_component_pkeyPK
engineering_component_code_idx
engineering_component_manufacturer_idx
engineering_component_subsystem_idx
engineering_component_tombstone_idxUNIQUE
engineering_component_variant_idx
engineering_component_workspace_idx

subsystem_code_lookup

Code → display-name + tech-class map for subsystems. Read-only seed.

code	text	NOT NULL
display_name	text	NOT NULL
description	text
applicable_tech_classes	ARRAY	NOT NULL
default_parent_scope	text	NOT NULL
damage_behavior	text
is_active	boolean	NOT NULL
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
requirement	text	NOT NULL

Indexes

subsystem_code_lookup_pkeyPK
subsystem_code_lookup_active_idx

component_code_lookup

Code → display-name + tech-class map for components. Read-only seed.

code	text	NOT NULL
display_name	text	NOT NULL
description	text
parent_subsystem_code	text	NOT NULL
specs_schema	jsonb	NOT NULL
is_active	boolean	NOT NULL
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
requirement	text	NOT NULL
exclusive_group	text
is_default	boolean	NOT NULL

Indexes

component_code_lookup_pkeyPK
component_code_lookup_active_idx
component_code_lookup_parent_idx

Configuration substrate

what users edit

Where the workspace expresses its view of the world. `tenant_overrides` is a row-per-field EAV cell store; `tenant_variants` groups override sets into named what-if scenarios; `tenant_templates` are reusable blueprints. The resolver primitives in `provenance.ts` merge baseline + variant overlays at read time — the rendered page is always derived, never persisted.

Read the canonical doc

workspace
One row per tenant. The RLS boundary. `workspace_id IS NULL` rows = public reference; non-NULL = tenant-private.
id uuid NOT NULL
name text NOT NULL
slug text NOT NULL
created_at timestamp with time zone NOT NULL
updated_at timestamp with time zone NOT NULL
deleted_at timestamp with time zone
Indexes
- workspace_pkeyPK
- workspace_slug_keyUNIQUE

portfolio

Named bundle of assets inside a workspace. Soft-deleted (deleted_at) per DD-007.

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
name	text	NOT NULL
description	text
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
deleted_at	timestamp with time zone
kind	text	NOT NULL

Indexes

portfolio_pkeyPK
idx_portfolio_name_unique_in_workspaceUNIQUE
idx_portfolio_workspace_active
idx_portfolio_workspace_kind_active

portfolio_asset

Junction — (portfolio × plant) anchored row + variant enumeration. `is_baseline=TRUE` is the workspace's truth row; non-baseline rows enumerate scenario variants.

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
portfolio_id	uuid	NOT NULL
reference_generator_id	text
variant_name	text	NOT NULL
is_baseline	boolean	NOT NULL
override_capacity_mw	numeric
notes	text
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
deleted_at	timestamp with time zone
reference_plant_id	uuid	NOT NULL

Indexes

portfolio_asset_pkeyPK
idx_portfolio_asset_baseline_oneUNIQUE
idx_portfolio_asset_reference_plant
idx_portfolio_asset_workspace_portfolio
uq_portfolio_asset_variantUNIQUE

tenant_variants

Canonical scoped identity for a variant. The override data targets it via `variant_id`. Plant-wide today (DD-028).

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
plant_id	uuid	NOT NULL
generator_id	uuid
name	text	NOT NULL
description	text
created_by	uuid
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL

Indexes

tenant_variants_pkeyPK
tenant_variants_name_uniqUNIQUE
tenant_variants_workspace_plant_id_uniqUNIQUE
tenant_variants_workspace_plant_idx

tenant_overrides

Row-per-field EAV cell store. Scoped by `(workspace, variant, plant, gen?, subsystem?, component?, dimension, field_path)` with partial unique indexes per scope shape.

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
variant_id	uuid
plant_id	uuid	NOT NULL
generator_id	uuid
dimension	text	NOT NULL
field_path	text	NOT NULL
value	jsonb	NOT NULL
rationale	text
source_event_id	uuid
author_id	uuid
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
subsystem_id	uuid
component_id	uuid

Indexes

tenant_overrides_pkeyPK
tenant_overrides_component_baseline_uniqUNIQUE
tenant_overrides_component_id_idx
tenant_overrides_component_variant_uniqUNIQUE
tenant_overrides_gen_baseline_uniqUNIQUE
tenant_overrides_gen_variant_uniqUNIQUE
tenant_overrides_plant_baseline_uniqUNIQUE
tenant_overrides_plant_variant_uniqUNIQUE
tenant_overrides_subsystem_baseline_uniqUNIQUE
tenant_overrides_subsystem_id_idx
tenant_overrides_subsystem_variant_uniqUNIQUE
tenant_overrides_variant_idx
tenant_overrides_workspace_plant_idx

tenant_templates

Reusable blueprints for instantiating new assets. Body shape evolved to substrate-aware in 04m.8 (DD-054).

id	uuid	NOT NULL
workspace_id	uuid
name	text	NOT NULL
description	text
fuel_code	text
tech_code	text
default_capacity_mw	numeric
body	jsonb	NOT NULL
created_by	text
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
is_deprecated	boolean	NOT NULL

Indexes

tenant_templates_pkeyPK
tenant_templates_fuel_picker_idx
tenant_templates_name_uniqUNIQUE
tenant_templates_workspace_idx

saved_scenario

Schema-only today; UI in a future phase. Will capture share-friendly snapshots of a variant + view state.

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
portfolio_id	uuid	NOT NULL
name	text	NOT NULL
description	text
reference_data_version	text	NOT NULL
synthesis_function_version	text
model_version	text
computed_at	timestamp with time zone	NOT NULL
created_at	timestamp with time zone	NOT NULL

Indexes

saved_scenario_pkeyPK
idx_saved_scenario_workspace

Audit + lineage substrate

what happened

Append-only history. Every mutation handler wraps the data write + audit row in one transaction (DD-013); `event_session_id` groups multi-write actions like Promote-variant into one user-readable line. `model_input_snapshot` extends the same idea for modeling — every output row carries a snapshot ID, so the inputs that produced a number stay reproducible after the workspace moves on.

Read the canonical doc

audit_event

Append-only. Every mutation writes one row in the same transaction as the data write. `event_session_id` groups multi-write actions.

id	uuid	NOT NULL
workspace_id	uuid	NOT NULL
actor_type	text	NOT NULL
actor_id	text
action	text	NOT NULL
resource_type	text	NOT NULL
resource_id	text
occurred_at	timestamp with time zone	NOT NULL
before_state	jsonb
after_state	jsonb
event_session_id	uuid

Indexes

audit_event_pkeyPK
audit_event_session_idx
audit_event_workspace_timeline_idx
idx_audit_event_workspace_resource
idx_audit_event_workspace_time

model_input_snapshot
Frozen `(workspace × plant × variant)` model input bundle (DD-035). Every modeling output row links to one; lineage stays reproducible.
id uuid NOT NULL
workspace_id uuid
variant_id uuid
plant_id uuid NOT NULL
bundle jsonb NOT NULL
model_version text NOT NULL
override_count integer NOT NULL
computed_at timestamp with time zone NOT NULL
Indexes
- model_input_snapshot_pkeyPK
- model_input_snapshot_computed_at
- model_input_snapshot_plant
- model_input_snapshot_workspace_variant

Computed / modeling substrate

what's been derived

Modeled outputs (DD-033/DD-034) at monthly grain. Production is staleness-driven: a config edit fires a `modeling_invalidation` row per affected output dim (DD-038), the UI surfaces an amber badge, and Re-run clears it. Currently a mock — real model orchestration lands later without changing this surface. 04m.13.9: per-field `modelDependencies` declarations are preliminary; the runtime fires all dims on every edit until the modeling team validates.

Read the canonical doc

modeling_metadata_plant

Per-plant {tech, grain, parameters} per DD-039/DD-040. Section-render gate on the asset page (e.g., CCGT hides gen-level generation).

plant_id	uuid	NOT NULL
tech	text	NOT NULL
grain	text	NOT NULL
parameters	jsonb	NOT NULL
source	text	NOT NULL
confidence	text	NOT NULL
data	jsonb	NOT NULL
data_provenance	jsonb	NOT NULL
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL

Indexes

modeling_metadata_plant_pkeyPK
modeling_metadata_plant_low_confidence
modeling_metadata_plant_tech

modeling_generation_plant

Plant-grain monthly generation output. One row per (workspace × plant × variant × month).

id	uuid	NOT NULL
workspace_id	uuid
variant_id	uuid
plant_id	uuid	NOT NULL
period_kind	text	NOT NULL
period_start	date	NOT NULL
generation_mwh	numeric	NOT NULL
capacity_factor	numeric
peak_hour_mw	numeric
availability_pct	numeric
curtailment_mwh	numeric
data	jsonb	NOT NULL
model_version	text	NOT NULL
source	text	NOT NULL
computed_at	timestamp with time zone	NOT NULL
input_snapshot_id	uuid	NOT NULL

Indexes

modeling_generation_plant_pkeyPK
modeling_generation_plant_period
modeling_generation_plant_plant_period
modeling_generation_plant_scope_uniqUNIQUE
modeling_generation_plant_snapshot
modeling_generation_plant_workspace_variant

modeling_generation_generator

Gen-grain monthly generation output. Populated when `topology.grain != 'plant_only'`.

id	uuid	NOT NULL
workspace_id	uuid
variant_id	uuid
plant_id	uuid	NOT NULL
generator_id	uuid	NOT NULL
period_kind	text	NOT NULL
period_start	date	NOT NULL
generation_mwh	numeric	NOT NULL
capacity_factor	numeric
peak_hour_mw	numeric
availability_pct	numeric
curtailment_mwh	numeric
data	jsonb	NOT NULL
model_version	text	NOT NULL
source	text	NOT NULL
computed_at	timestamp with time zone	NOT NULL
input_snapshot_id	uuid	NOT NULL

Indexes

modeling_generation_generator_pkeyPK
modeling_generation_generator_period
modeling_generation_generator_plant
modeling_generation_generator_scope_uniqUNIQUE
modeling_generation_generator_snapshot
modeling_generation_generator_workspace_variant

modeling_hazard_plant

Plant-grain hazard exposure scores (flood, drought, heat, wildfire, freeze, hurricane).

id	uuid	NOT NULL
workspace_id	uuid
variant_id	uuid
plant_id	uuid	NOT NULL
hazard_type	text	NOT NULL
period_kind	text	NOT NULL
period_start	date	NOT NULL
exposure_score	numeric	NOT NULL
exposure_band	text
contributing_factors	jsonb	NOT NULL
model_version	text	NOT NULL
source	text	NOT NULL
computed_at	timestamp with time zone	NOT NULL
input_snapshot_id	uuid	NOT NULL

Indexes

modeling_hazard_plant_pkeyPK
modeling_hazard_plant_hazard_type
modeling_hazard_plant_period
modeling_hazard_plant_plant_period
modeling_hazard_plant_scope_uniqUNIQUE
modeling_hazard_plant_snapshot
modeling_hazard_plant_workspace_variant

modeling_hazard_generator

Gen-grain hazard scores. Same dim taxonomy as the plant-grain table.

id	uuid	NOT NULL
workspace_id	uuid
variant_id	uuid
plant_id	uuid	NOT NULL
generator_id	uuid	NOT NULL
hazard_type	text	NOT NULL
period_kind	text	NOT NULL
period_start	date	NOT NULL
exposure_score	numeric	NOT NULL
exposure_band	text
contributing_factors	jsonb	NOT NULL
model_version	text	NOT NULL
source	text	NOT NULL
computed_at	timestamp with time zone	NOT NULL
input_snapshot_id	uuid	NOT NULL

Indexes

modeling_hazard_generator_pkeyPK
modeling_hazard_generator_hazard_type
modeling_hazard_generator_period
modeling_hazard_generator_plant
modeling_hazard_generator_scope_uniqUNIQUE
modeling_hazard_generator_snapshot
modeling_hazard_generator_workspace_variant

modeling_invalidation
Staleness state. One row per (workspace × plant × variant × output_dim) when the inputs have drifted from the last model run. Cleared by `/rerun-models`.
id uuid NOT NULL
workspace_id uuid NOT NULL
plant_id uuid NOT NULL
variant_id uuid
output_dim text NOT NULL
invalidated_at timestamp with time zone NOT NULL
invalidated_by_override_id uuid
context jsonb
Indexes
- modeling_invalidation_pkeyPK
- modeling_invalidation_target_uniqUNIQUE
- modeling_invalidation_variant_idx
- modeling_invalidation_workspace_plant_idx

Recent portfolio_asset (last 10)

paid	portfolio	plant	variant	cap (eff / ref)	updated
65c55339…	Test	63688Topaz Generating	★baseline	605	2026-06-29
2a52af7d…	Test	63910Bluegrass Solar	★baseline	79.6	2026-06-29
0885d5f5…	Test	64430American Beech Solar LLC	★baseline	140	2026-06-29
175e55ba…	Test	68998Long Point Solar	★baseline	120	2026-06-29
11fbb06d…	Test	62406City of Gallup Solar	★baseline	8	2026-06-29
a8bb6274…	Test	60299HMV Minster Energy Storage System	★baseline	7	2026-06-29
4fb17c28…	Test	69096Jericho	★baseline	5	2026-06-29
c2c33578…	Test	69787AC IL - Hillsboro	★baseline	5	2026-06-29
e3663fc0…	Test	67747Nachtigall 1836-WD	★baseline	4.6	2026-06-29
ff8e24c5…	Test	63727Slate Hybrid	★baseline	440.3	2026-06-18

Recent audit_event (last 10)

time	actor	action	resource_id	diff
2026-06-29	617e3094…	portfolio_asset.add	65c55339…	plant_uuid: 18d9876d-35f9-43f7-808b-bffa428f348c is_baseline: true eia_plant_id: 63,688 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	2a52af7d…	plant_uuid: cae5f8c0-6964-4762-87e8-3c6073e6ac24 is_baseline: true eia_plant_id: 63,910 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	0885d5f5…	plant_uuid: a26d10ca-5557-41d3-aa27-8f7d941634d6 is_baseline: true eia_plant_id: 64,430 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	175e55ba…	plant_uuid: e5d50566-1790-48fe-86dc-4d0853dcce40 is_baseline: true eia_plant_id: 68,998 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	11fbb06d…	plant_uuid: f855ad02-d332-4b22-994b-38ddef0d29c1 is_baseline: true eia_plant_id: 62,406 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	a8bb6274…	plant_uuid: 27e48b48-628f-4229-9e99-be7581076783 is_baseline: true eia_plant_id: 60,299 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	4fb17c28…	plant_uuid: dbb97718-7bad-42e7-947e-e4ff34f17c46 is_baseline: true eia_plant_id: 69,096 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	c2c33578…	plant_uuid: 3a9eba63-b4eb-427d-97b7-d9a44ee2c9c5 is_baseline: true eia_plant_id: 69,787 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-29	617e3094…	portfolio_asset.add	e3663fc0…	plant_uuid: 2fc2cb57-656f-48a1-919a-4c8390f63ab3 is_baseline: true eia_plant_id: 67,747 portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline portfolio_kind: portfolio workspace_asset_state: portfolio
2026-06-18	617e3094…	portfolio_asset.add	ff8e24c5…	bulk: true is_baseline: true portfolio_id: ef5bd170-618b-461c-925b-fa82de117523 variant_name: baseline reference_plant_id: 63,727