HeatWaves v2 schema — constraint and convention reference

HeatWaves v2 schema — constraint and convention reference

Loaded on demand. Read this when you need exact shapes, constraint behavior, or index/RPC templates. For why these choices exist, see conceptual-tables-and-grain.md.

Authority: Generated types (types/fire_data.database.types.ts from npm run types:gen:fire-data) match the linked database; if this file disagrees with types, fix this file.

Filter axes and RPC token rules

Filter axes (fire_type, building_type, building_age, region) resolve through fire_data.filters. get_fire_data accepts rollup standard_code tokens on the read path; ingestion still posts native ids per filter-axes-native-to-filter-id.md. The enforce_fact_filter_code_axes trigger (axis trigger) validates each non-null fact FK against filters.axis and applicable_dim_codes.

---

Naming and typing conventions

Fact filter columns — hard rule

Fact rows store fire_data.filters.id in fire_type_code, building_type_code, building_age_code, and region_code. They never store BRASK form values ('0', '2'), BRIS Matrikkel strings, or standard_code strings inline.

Adapters resolve upstream natives → filters.id at ingest; get_fire_data resolves RPC tokens → filters.id at read time, then compares to the integer FK columns on facts.

Separation of concerns (catalog vs facts vs RPC)

• filters.id / cat_code on facts — which catalog row this slice used. Distinct næring values (e.g. Beboelse vs Industri) are different filters.id values under the building_type axis; "Alle" is NULL, not an id.
• filters.native_code — upstream extract identifier on the catalog row (BRASK lbNæring=0, not the fact column value).
• standard_code — cross-source bridge on filters / categories only. RPC/chart tokens such as RESIDENTIAL resolve to many filters.id values (one per matching catalog row per source), then match fact FKs.
• get_fire_data filter parameters (p_fire_type, p_building_type, …) — breakdown-aware (migration 20260601120000_get_fire_data_null_as_all_breakdown_aware.sql). Per axis:
  • NULL and not in p_breakdown_axes — return only facts where that axis FK IS NULL (the all-level total slice; never the sum of children).
  • NULL but in p_breakdown_axes — return the leaf rows where that FK IS NOT NULL, grouped (the per-value breakdown; the NULL total is excluded so leaves never double-count with it).
  • Non-null token array — resolve tokens to filters.id (via native_code or standard_code on filters); keep facts whose integer FK is any of those ids (OR within axis; summed when not broken down, grouped when broken down).

---

Fact table column layout (fact_daily / fact_yearly)

id                   BIGSERIAL PRIMARY KEY
date | year          DATE | SMALLINT NOT NULL     -- grain
source_id            INTEGER NOT NULL            -- FK → sources.source_id
dim_code             INTEGER NOT NULL            -- FK → dimensions.dim_code
cat_code             INTEGER NOT NULL            -- FK → categories (compound)
value                NUMERIC NOT NULL DEFAULT 0
value_kr             NUMERIC NULL                -- BRASK kr; NULL elsewhere
fire_type_code       INTEGER NULL                -- FK → filters.id (axis = fire_type)
building_type_code   INTEGER NULL                -- FK → filters.id (axis = building_type)
building_age_code    INTEGER NULL                -- FK → filters.id (axis = building_age)
region_code          INTEGER NULL                -- FK → filters.id (axis = region)
ingested_at          TIMESTAMPTZ NOT NULL DEFAULT now()
```text

Axis correctness on non-null filter values is enforced **before insert/update**
by triggers **`fact_yearly_enforce_filter_axes`** and
**`fact_daily_enforce_filter_axes`**, both calling the same function
**`fire_data.enforce_fact_filter_code_axes()`** (definition in
[`supabase/migrations/20260503190000_filters_dim_scoping_repair_and_undo_ledger.sql`](../../supabase/migrations/20260503190000_filters_dim_scoping_repair_and_undo_ledger.sql)).

That function, per non-null filter column:

1. Requires a **`filters`** row with matching **`id`** and **`axis`** (otherwise raises).
2. If **`filters.applicable_dim_codes`** is non-null, requires **`NEW.dim_code`** to appear in that array (otherwise raises — prevents e.g. **`building_type_code`** on BRIS Police Årsak rows where the restricted endpoint ignores building type).

There are **no** `CHECK (... IN ('CONFINED', …))` clauses on fact filter columns;
vocabulary lives in **`filters`**.

**FK vs trigger:** **`REFERENCES fire_data.filters(id)`** rejects ids that do not
exist. Wrong **`axis`** or wrong **`dim_code`** for **`applicable_dim_codes`** are
rejected by the trigger, not by FK alone.

**Not enforced in Postgres:** the trigger does **not** compare **`filters.source_id`**
to **`fact.source_id`**. Adapters must resolve ids from the registry for the active
source so rows never reference another source’s filter row by mistake.

---

## CHECK constraints (facts)

```sql
-- Both fact_daily and fact_yearly
CHECK (value    >= 0)
CHECK (value_kr IS NULL OR value_kr >= 0)

-- fact_yearly only
CHECK (year BETWEEN 1900 AND 2100)

---

Foreign keys (representative)

-- categories
FOREIGN KEY (source_id) REFERENCES fire_data.sources(source_id)     ON DELETE RESTRICT
FOREIGN KEY (dim_code)  REFERENCES fire_data.dimensions(dim_code)   ON DELETE RESTRICT

-- facts → categories (source-scoped categories)
FOREIGN KEY (source_id, dim_code, cat_code)
  REFERENCES fire_data.categories (source_id, dim_code, cat_code)
  ON DELETE RESTRICT

-- facts → filters (per axis column)
FOREIGN KEY (fire_type_code)     REFERENCES fire_data.filters(id) ON DELETE RESTRICT
-- … same pattern for building_type_code, building_age_code, region_code

dimensions.dim_code renames use the same ON UPDATE CASCADE pattern as in legacy TEXT-key migrations when applied to the FK from categories.

---

Natural-key unique indexes (upsert targets)

Facts use an eight-column natural key (also written 8-column natural key): source_id, dim_code, cat_code, the time grain column (date or year), and the four nullable filter FK columns (fire_type_code, building_type_code, region_code, building_age_code).

Nullable filter columns participate in uniqueness as plain columns using NULLS NOT DISTINCT so two all-NULL filter slices still conflict correctly.

CREATE UNIQUE INDEX fact_daily_natural_key ON fire_data.fact_daily (
  source_id,
  dim_code,
  cat_code,
  date,
  fire_type_code,
  building_type_code,
  region_code,
  building_age_code
) NULLS NOT DISTINCT;

CREATE UNIQUE INDEX fact_yearly_natural_key ON fire_data.fact_yearly (
  source_id,
  dim_code,
  cat_code,
  year,
  fire_type_code,
  building_type_code,
  region_code,
  building_age_code
) NULLS NOT DISTINCT;

Upsert template (fact_daily)

Column list must match the unique index exactly (same columns, same order):

INSERT INTO fire_data.fact_daily (
  source_id, dim_code, cat_code, date,
  value, value_kr,
  fire_type_code, building_type_code, region_code, building_age_code
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT (
  source_id, dim_code, cat_code, date,
  fire_type_code, building_type_code, region_code, building_age_code
)
DO UPDATE SET
  value       = EXCLUDED.value,
  value_kr    = EXCLUDED.value_kr,
  ingested_at = now();

Adapters must resolve native upstream values to filters.id (lookup by source_id, axis, native_code) before insert.

---

fact_buildings_yearly (exception)

SSB building-stock totals keep building_type_code TEXT and STRING region_code per SSB’s own taxonomy — not the incident filters spine. Natural key and CHECKs for that table follow its dedicated migration; do not assume parity with fact_daily filter FKs.

---

Performance indexes (representative)

CREATE INDEX idx_fact_daily_date_source_dim
  ON fire_data.fact_daily (date, source_id, dim_code);

CREATE INDEX idx_fact_yearly_year_source_dim
  ON fire_data.fact_yearly (year, source_id, dim_code);

CREATE INDEX idx_categories_standard_code
  ON fire_data.categories (standard_code)
  WHERE standard_code IS NOT NULL;

CREATE INDEX idx_categories_source_dim
  ON fire_data.categories (source_id, dim_code, sort_order);

CREATE INDEX filters_axis_standard_idx
  ON fire_data.filters (axis, standard_code)
  WHERE standard_code IS NOT NULL;

---

RLS policies

ALTER TABLE fire_data.<table> ENABLE ROW LEVEL SECURITY;
CREATE POLICY "fire_data read-anon"
  ON fire_data.<table>
  FOR SELECT
  TO anon, authenticated
  USING (true);

-- ingest.* — RLS on, no policies (default-deny). Service role bypasses for writes.

---

RPC template

CREATE OR REPLACE FUNCTION fire_data.<function_name>(
  p_<param1>     <type>,
  p_<param2>     <type> DEFAULT NULL,
  ...
)
RETURNS TABLE (...)
LANGUAGE sql
STABLE
PARALLEL SAFE
SET search_path = ''
AS $$
  -- Fully qualify: fire_data.fact_daily
  -- NULL params = restrict to facts where that axis FK IS NULL
$$;

GRANT EXECUTE ON FUNCTION fire_data.<function_name>(...) TO anon, authenticated;

get_fire_data (summary)

• p_sources, p_dim_codes: integer[] (ids).
• p_fire_type, p_building_type, p_building_age, p_region: text[] — each element may be a native_code or standard_code; RPC resolves to filters.id.
• p_granularity: year reads fact_yearly only (must already contain yearly aggregates from ingest); day · week · month · quarter read fact_daily only, intersected with sources.supports_subyear so taxonomy yearly-only sources are excluded from sub-year reads.
• ISO weeks use Postgres date_trunc('week', …) (Monday-based buckets).
• Breakdown columns in the result set are filters.id (nullable when the axis is not in p_breakdown_axes). Join filters for labels.
• No helper aggregates fact_daily → fact_yearly — yearly charts rely solely on rows written into fact_yearly during ingest.

---

TypeScript data-layer file structure

types/
├── database.types.ts           ← canonical entrypoint (re-exports)
├── public.database.types.ts    ← legacy public schema
└── fire_data.database.types.ts ← generated from Supabase (fire_data schema)

lib/data-model/
├── index.ts      ← public barrel
├── types.ts      ← aliases + GetFireDataRow nullability fixes + UI literals
├── client.ts     ← INTERNAL
├── queries.ts    ← INTERNAL
└── adapters/     ← service-role writes; resolve filters.id at ingest

UI literals such as FireTypeCode describe standard_code vocabulary for filters and URLs, not physical fact column types (facts store ids).

---

Live database (reference)

• Project: xulwgjinkizflfzdobkn (“Heat Waves”)
• Schemas: fire_data (exposed), ingest (hidden), public (legacy v1)

---

Common advisor warnings

• Anonymous read on fire_data.* — intentional (public catalog/charts).
• rls_enabled_no_policy on ingest.* — intentional (default-deny).
• function_search_path_mutable — use SET search_path = '' on functions.
• Legacy public.* warnings — out of scope until v1 retirement.

---

Maintenance checklist

Update this file whenever one of these changes lands:

1. Any migration that changes constraints, FKs, triggers, or indexes in fire_data.
2. Any filters axis/routing change that affects how fact filter columns are validated.
3. Any get_fire_data signature, parameter semantics, or grain-routing behavior change.
4. Any type-generation flow change for types/fire_data.database.types.ts.