Gaps in referential integrity validation

[nick-gorman]

Posting this to stimulate discussion rather than as a finished proposal — the framing,
gap categories, and examples below are rough cuts and might not be exactly right.

Our per-column allowed_values_from seems to cover basic foreign-key checks well, but
a quick audit across src/ispypsa/validation/schemas suggests there are a few classes
of cross-table check it can't easily express today. Tentative grouping below, with one
real example each — happy to be told these aren't really the right categories, or that
some of them aren't worth supporting.

Gaps

1. Filtered targets — "value must be in T where T.col = v".

emissions_reduction models biomethane blending for gas-fired generators, so the
name column should reference a generator with fuel_type='Gas' — not just any
generator. Today we can express "must be a known generator name", but not the
fuel-type restriction on the target.

2. Conjunctions — "must be in A and in B".

network_expansion_options.expansion_id already uses disjunction: it must be a
path_id or a constraint_id. We'd also like to require that every expansion option
has a row in network_transmission_path_expansion_costs — i.e. and in the costs
table. No way to combine the two today.

3. Composite-key (tuple) checks — "(col_a, col_b) tuple must match same tuple in
another table".

generators_existing_planned.(fuel_type, fuel_price_mapping) should match a tuple
in costs_fuel_prices. Checking each column independently misses the case where
both values exist in the price table but never together.

This one feels messier than the others. The wildcard / conditional semantics
seem to differ per case. In costs_connection, a row with technology=NULL looks
like an explicit wildcard meaning "applies to any new entrant tech in this geo_id".
In generators_new_entrant, a null resource_type probably means the FK to
resource_limits simply shouldn't fire. If those readings are right, a declarative
tuple-FK might only handle the strict case and leave the rest to code anyway — so
leaning toward custom_validation for these, but worth pushing back on if
people see a cleaner cut.

4. Intra-table multi-column row predicates —

For network_geography rows where geo_type='subregion', subregion_id must
equal geo_id. Or: storage soc_min ≤ soc_max. Already what
custom_validation is for.

A possible direction

One way to address gaps 1 and 2 would be to extend allowed_values_from with a
where filter and explicit any_of / all_of keywords for boolean composition.
Coverage / reverse checks would also fall out of this for free — they're just FK
declarations sitting on the referenced column. Sketches below to give a flavour, not
to nail down syntax.

Single target (today, no change):

# network_transmission_paths.geo_from
geo_from:
  allowed_values_from:
    table: network_geography
    column: geo_id

Filtered target:

# emissions_reduction.name — must be a gas generator
name:
  allowed_values_from:
    table: generators_existing_planned
    column: name
    where: {column: fuel_type, equals: Gas}

Disjunction with explicit any_of (replaces the current bare-list form):

# network_expansion_options.expansion_id
expansion_id:
  allowed_values_from:
    any_of:
      - {table: network_transmission_paths, column: path_id}
      - {table: constraints_rhs, column: constraint_id}

Conjunction with nesting:

# network_expansion_options.expansion_id, requiring a cost row as well
expansion_id:
  allowed_values_from:
    all_of:
      - any_of:
          - {table: network_transmission_paths, column: path_id}
          - {table: constraints_rhs, column: constraint_id}
      - {table: network_transmission_path_expansion_costs, column: expansion_id}

where operators: {column: c, equals: v}, {column: c, in: [...]},
{column: c, not_null: true}.

For gaps 3 and 4 — tuple FKs and intra-table row predicates — leaning toward
custom_validation, for the reasons above. If we landed somewhere like this, every
check would live next to the table it constrains and a central referential-integrity
file probably isn't needed. But happy to be talked out of any of this.

Another possible direction

The approach above keeps every constraint declared on the column it applies to. The
flip side is that some constraints get repeated a lot. geo_id → network_geography is
declared in ~8 columns across 7 tables; the fuel_type allowed-values list is copy-
pasted across 5 schemas (with some drift between copies); technology → costs_new_entrant_build appears in 3 places. Compound constraints would amplify this
if they ever applied to multiple columns.

A dataset-level layer could help. Two pieces, roughly:

Named reference types — define an FK once, reference it by name:

# central, dataset-level
reference_types:
  geo_id: {table: network_geography, column: geo_id}
  buildable_technology: {table: costs_new_entrant_build, column: technology}
  fuel_price_mapping: {table: costs_fuel_prices, column: fuel_price_mapping}

# in any schema
columns:
  geo_id:
    type: string
    references: geo_id

Shared enum lists — pull recurring allowed-values lists out once:

# central
enums:
  fuel_type: [Black Coal, Brown Coal, Liquid Fuel, Gas, Water, Solar, Wind, Biomass]

# in any schema
columns:
  fuel_type:
    type: string
    enum: fuel_type

Both feel like wins independent of the syntax-extension discussion above — they'd
tidy the existing schemas and remove the drift risk regardless of whether we add
where / any_of / all_of. Open question whether to do them together or
sequence them.

[EllieKallmier]

Some rambling thoughts in response to these prompts -

Re: Gap #3

• The fuel_type, fuel_price_mapping tuple match is interesting/maybe a bit strange - mainly because it’s not strictly true that the generators_* tuples must be present and match those in costs_fuel_prices (they map together by fuel_cost_mapping column - fuel_type used for other things by each).
  • That said - those columns in both costs_fuel_prices and generators_* tables are currently required and I do think checking the tuple match up is just the least confusing thing to do at the moment!
• The connection_costs example could be simplified - I think it would be fine to make the technology column required and create rows for each technology not explicitly defined (VRE technologies) - we do equivalent expansions for other tables (e.g. adding rows for subregions). I’d be happy to adopt this approach across the board as a step to reduce the amount of intra-columnar validation options.
  • This would move toward allowing a simpler tuple-match validation that could be standardised across tables
• The resource_limits/resource_type one is interesting I think? As far as I can tell, it’s maybe kinda both ways check - (geo_id, resource_type) tuples that exist in resource_limits don’t strictly need to exist in generators_new_entrant (but should be flagged - ie. build limit defined for generators that don’t exist), and vice versa (a tuple that exists in generator table but doesn’t in resource_limits just means no build limit exists for that generator; again, flag-worthy). So yeah I feel like custom_validation will be needed for some cases for sure.
  • This has also made me realise we might want to tweak/think about the resource_limits table's use of resource_type vs. how it's used by generator tables - for limit_type = "generation" it maps to the resource_type for a generator, but for limit_type = "land_use" it maps to the fuel_type (carrier). Sorry I didn’t catch this in my initial review..

I guess this also relates a bit to what we chatted about re: filtering for model years earlier - it’s not necessarily cross-table but something that checks that at least a tuple of unique column values exists for each model year. E.g. for connection_costs, each model year must also have a unique (geo_id, technology, connection_cost) tuple defined. I haven’t yet thought about how we could implement this, just another puzzle piece...

Re: “A possible direction”

• Yep I am pro something like this, having the any_of, all_of and where features makes sense.
• This feels like another spot where maybe the actual syntax might be dictated a bit by the validation method/setup
• Starting to remind me of JSON Schema vibes!
• Also makes me think maybe we should define super clearly what allowed_values_from means when a column has required: false - I’ve been taking it as a declaration like “if there are non-NaN values in this column, they must be in [allowed]”

Re: “Another possible direction”

• I am not against this! I had an initial kind of hesitant reaction about adding “more clicks” to find the base truth (i.e., not just having all the information in place), but I can get over that for the centralisation bonuses
• In some ways it feels like this relates to defining the “standard” or “canon” columns - though not exactly the same it kind of sits in a similar world? (I think this not a code point just a conceptual linking point for myself)
• I have updated some of the examples of using enums in gen/storage tables now to be column references, which feels like it’ll probably be the best practice going forward. I was thinking about the almost separation between the templater and validation and that it probably makes more sense to have (where it makes sense) enums defined in the templater to apply to IASR tables, but then just use inter-table column refs at the validation stage to flexibly handle either templater output or simplified/modified user input.
  • Basically - becomes more about internal consistency across tables, with the sets of allowed/consistent values either defined by the templater OR the user, not both

Ok also very keen to keep discussing all of this and will continue to collect my thoughts and reflect on this!