-- ============================================================================= -- HeatWaves v2 — filter-axis unification, building_age axis, breakdown-aware RPC -- Source of truth: canonical migration contract for v2 filter axes and filter FK semantics. -- Runtime get_fire_data SQL body: prefer docs/fire-data-schema/postgres-contract-rls-and-keys.md + -- docs/fire-data-schema/schema-history/incremental-patches-and-rpc-changelog.md over embedded §6 below (UNION-era fragment kept as history). -- Canonical contract: postgres-contract-rls-and-keys.md and incremental-patches-and-rpc-changelog.md. -- Executable DDL: timestamp-prefixed files under supabase/migrations/; this document mirrors the narrative contract. -- Runtime drift warning (2026-05+): embedded §6 get_fire_data below shows an older -- UNION ALL over fact_daily + fact_yearly. The linked database replaces this with -- branching (p_granularity): year → fact_yearly only; sub-year → fact_daily + supports_subyear. -- Do not treat the commentary/SQL fragment here as current Postgres behaviour — see -- docs/fire-data-schema/schema-history/incremental-patches-and-rpc-changelog.md and docs/fire-data-schema/postgres-contract-rls-and-keys.md for the live RPC summary. -- Run order: AFTER v2 initial schema migration (see v2-001-initial-migration-narrative.md), BEFORE the first adapter writes. -- Applied: 2026-04-29 (migration v2_filters_and_breakdown_axes) -- 2026-05-01: RPC renamed get_chart_data → get_fire_data -- (migration name: rename_get_chart_data_to_get_fire_data) for parity with TS getFireData() -- ============================================================================= -- This migration finalizes the v2 fire_data schema. After it, the schema's -- contract with adapters is: -- * fact rows store FILTER FK ids (INTEGER NULL → filters.id) on each -- filter axis column (fire_type_code, building_type_code, building_age_code, -- region_code). Native + standard vocabulary lives on filters rows. -- * native -> standard mapping remains on fire_data.filters (native_code, -- standard_code TEXT nullable). -- * cross-source comparability is reconstructed at query time via standard_code -- resolution inside get_fire_data (TEXT[] params → ids). -- * get_fire_data accepts native or standard codes interchangeably for filters; -- p_sources / p_dim_codes use INTEGER[] ids; breakdown columns return ids. -- * Surrogate filters.id PK + UNIQUE(source_id, axis, native_code), FK + trigger -- enforce_fact_filter_code_axes — applied after native-TEXT-on-facts iteration -- (repo migration scripts / linked DB).

-- Legacy bullets in this file describing "native TEXT on fact columns" without FK ids -- describe an intermediate step — superseded by the linked database + postgres-contract-rls-and-keys.md.

-- See v2-001-initial-migration-narrative.md, brask-ingest-loop-and-invariants.md, -- crosstab-and-bucket-modeling.md, and docs/semantics-and-sources/taxonomy-and-eu-firestat-alignment.md -- for domain semantics.

-- ARCHITECTURAL DECISIONS encoded here: -- 1. Filter axes store NATIVE codes, with cross-source standardization in -- filters — same preserve-native-plus-standardize pattern that -- categories already uses for dimensions. -- 2. building_age_code is a filter axis (not just a dimension). Required -- for cross-tab matrices over BRASK characteristics × age bracket. -- 3. Facts FK-filter_columns → filters.id; axis enforced by trigger (not optional). -- (Earlier doc revisions hypothesised "no FK" — superseded.) -- 4. building_age_code FK semantics match other axes; vocabulary evolves via -- filters inserts (no brittle CHECK on facts). -- 5. ingest.extraction_state also gains building_age_code so the cron -- scheduler tracks per-slice state at the same granularity fact_daily -- uses (forced by the cross-product loop in brask-ingest-loop-and-invariants.md §4 and slice bookkeeping §5.4). -- 6. fact_buildings_yearly's standard-only CHECK is INTENTIONALLY KEPT. -- That table is SSB-only building-stock totals where SSB defines the -- building taxonomy — no cross-source vocabulary tension to relax for. -- 7. BRASK naering / næring is source-native sector vocabulary; canonical -- ids are in lib/data-model/adapters/_braskWebForms.constants.ts -- (BRASK_EXPLORER_DIMENSIONS_JSON.dimensionValues.naering, '0'..'21', '99'). It -- maps into filters (axis='building_type') + fact building_type_code, -- not a separate physical axis/column. -- =============================================================================

-- ============================================================================= -- 1. filters — native<->standard mapping for filter-axis values -- ============================================================================= -- Mirrors fire_data.categories. categories handles per-row dimension values -- (one row per category per source); filters handles per-column filter -- axis values (one row per native code per source per axis).

-- standard_code is nullable: not every native value participates in a cross- -- source standardised query (BRASK 'ALLE' is the canonical example — it -- overlaps with 'BOLIGER' and intentionally has no standard counterpart). -- NULL standard means "this native exists for this source on this axis but -- has no heatwaves cross-source bridge". Such rows still serve native -- drill-down (Utforsk-style) without participating in cross-source SUMs.

-- The composite PK (source_id, axis, native_code) keeps natives source- -- scoped. BRASK's BOLIGER and SSB's BOLIGBRANNER do not collide even if they -- one day map to the same standard. -- =============================================================================

CREATE TABLE IF NOT EXISTS fire_data.filters ( source_id TEXT NOT NULL REFERENCES fire_data.sources(source_id) ON DELETE RESTRICT, axis TEXT NOT NULL, native_code TEXT NOT NULL, standard_code TEXT NULL, display_name TEXT NOT NULL, description TEXT NULL, sort_order SMALLINT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- Controlled vocabulary for axes. Adding a new axis (e.g. 'severity') is -- one CHECK update + one set of fact-table column adds. Existing rows are -- unaffected — they get NULL on the new axis column, which the WHERE-clause -- semantics already handle (graceful degradation by construction). CONSTRAINT filters_axis_check CHECK (axis IN ('fire_type', 'building_type', 'building_age', 'region')), PRIMARY KEY (source_id, axis, native_code) );

-- Reverse-lookup index for cross-source resolution: "give me every native -- across all sources whose standard_code = X". Partial — rows without a -- standard mapping are useless for cross-source queries and don't index. CREATE INDEX IF NOT EXISTS filters_axis_standard_idx ON fire_data.filters (axis, standard_code) WHERE standard_code IS NOT NULL;

-- Per-source, per-axis sort-order lookup for filter pickers in the UI. CREATE INDEX IF NOT EXISTS filters_source_axis_sort_idx ON fire_data.filters (source_id, axis, sort_order);

-- RLS: match every other fire_data table (public read, service-role-only write). ALTER TABLE fire_data.filters ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "fire_data read-anon" ON fire_data.filters; CREATE POLICY "fire_data read-anon" ON fire_data.filters FOR SELECT TO anon, authenticated USING (true);

COMMENT ON TABLE fire_data.filters IS 'Native<->standard mapping for filter-axis values. One row per (source, axis, native value). ' 'standard_code carries the heatwaves cross-source identifier (e.g. CONFINED, RESIDENTIAL) and is ' 'NULL when the native has no standard counterpart. Sibling of fire_data.categories: same ' 'preserve-native-plus-standardize pattern, applied to filter columns rather than dimension rows.';

COMMENT ON COLUMN fire_data.filters.axis IS 'Filter axis name. Locked by CHECK to: fire_type, building_type, building_age, region.'; COMMENT ON COLUMN fire_data.filters.native_code IS 'Source-native code as written into fact_daily/fact_yearly columns of the same name ' '(fire_type_code, building_type_code, building_age_code, region_code).'; COMMENT ON COLUMN fire_data.filters.standard_code IS 'HeatWaves cross-source identifier. NULL = source-specific, no cross-source bridge.';

-- ============================================================================= -- 2. Drop the standard-only CHECK constraints on fact tables -- ============================================================================= -- Previously fact rows were required to carry standard codes -- (CONFINED/DEVELOPED, RESIDENTIAL/COMMERCIAL/OTHER). Now they carry NATIVE -- source codes — KALD, BRANN_I_SKORSTEIN, BOLIGER, ALLE — so the enumerated -- CHECKs no longer apply. Domain integrity moves to filters membership, -- enforced at adapter write time inside the same transaction as the upsert -- (per brask-ingest-loop-and-invariants.md §5.2.2).

-- Rationale for not replacing them with FKs: -- filters' (source_id, axis, native_code) target columns aren't a -- usable FK target without an axis column on fact rows. Adding generated -- _axis columns just for FK semantics is ugly clutter for marginal value.

-- fact_buildings_yearly's CHECK is INTENTIONALLY KEPT — that table is SSB- -- only building stock totals, where SSB defines the building taxonomy. No -- cross-source vocabulary tension to relax for. -- =============================================================================

ALTER TABLE fire_data.fact_daily DROP CONSTRAINT IF EXISTS fact_daily_fire_type_check, DROP CONSTRAINT IF EXISTS fact_daily_building_type_check;

ALTER TABLE fire_data.fact_yearly DROP CONSTRAINT IF EXISTS fact_yearly_fire_type_check, DROP CONSTRAINT IF EXISTS fact_yearly_building_type_check;

-- ============================================================================= -- 3. Add building_age_code column to both fact tables -- ============================================================================= -- New filter axis. Enables cross-tab matrices (per crosstab-and-bucket-modeling.md §5) by carrying -- the BRASK Bygningsalder bracket on every fact row that BRASK's API allows -- to be cross-tabulated against age. Other sources don't carry it — their -- rows will have building_age_code = NULL, which a WHERE-clause filter on -- this axis automatically excludes (graceful degradation by construction).

-- Unconstrained TEXT — value set is enforced by filters membership -- rather than a static CHECK, matching the post-migration treatment of -- fire_type_code and building_type_code. -- =============================================================================

ALTER TABLE fire_data.fact_daily ADD COLUMN IF NOT EXISTS building_age_code TEXT NULL;

ALTER TABLE fire_data.fact_yearly ADD COLUMN IF NOT EXISTS building_age_code TEXT NULL;

COMMENT ON COLUMN fire_data.fact_daily.building_age_code IS 'Native BRASK Bygningsalder band (Ny, 1-5, 6-10, …, Over 100, Ukjent). NULL on ' 'rows from sources that do not carry building age. Membership enforced by ' 'fire_data.filters (axis = ''building_age'').'; COMMENT ON COLUMN fire_data.fact_yearly.building_age_code IS 'Native BRASK Bygningsalder band (Ny, 1-5, 6-10, …, Over 100, Ukjent). NULL when source does not carry building age. ' 'Membership enforced by fire_data.filters (axis = ''building_age'').'; COMMENT ON COLUMN fire_data.fact_daily.fire_type_code IS 'Native source fire-type code (BRASK: KALD/VARM; BRIS: BRANN_I_BYGNING/...). NULL when ' 'the source does not carry a fire-type filter. Cross-source standardization via filters.'; COMMENT ON COLUMN fire_data.fact_daily.building_type_code IS 'Native source building-type code (BRASK: BOLIGER/ALLE; BRIS Sub-A: BOLIGER/ALLE). NULL when ' 'the source does not carry a building-type filter. Cross-source standardization via filters.'; COMMENT ON COLUMN fire_data.fact_daily.region_code IS 'Native source region code. NULL when the source does not carry region info. ' 'Cross-source standardization via filters (axis = ''region'').';

-- ============================================================================= -- 4. Rebuild natural-key unique indexes to include building_age_code -- ============================================================================= -- The natural-key index is the upsert target. Every column that distinguishes -- one fetched slice from another belongs in it, including building_age_code. -- COALESCE-with-empty-string treats NULL as a real key value because plain -- Postgres unique indexes do not enforce uniqueness across NULLs.

-- Locking note: DROP+CREATE briefly takes ACCESS EXCLUSIVE. Tables are empty, -- so this is instant. If re-run on populated tables, use CREATE INDEX -- CONCURRENTLY in a separate transaction first, then DROP the old. -- =============================================================================

DROP INDEX IF EXISTS fire_data.fact_daily_natural_key;

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

DROP INDEX IF EXISTS fire_data.fact_yearly_natural_key;

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

-- ============================================================================= -- 5. Extend ingest.extraction_state with building_age_code -- ============================================================================= -- Forced by step 3: the scheduler must record per-slice fetch state at the -- same granularity fact_daily uses, otherwise it can't tell whether a given -- (source, dim, period, fire_type, building_type, region, building_age) -- slice has been fetched before.

-- Not a fact table, so no SLA hit on adding the column. Rebuild the unique -- index for the same reason as step 4. -- =============================================================================

ALTER TABLE ingest.extraction_state ADD COLUMN IF NOT EXISTS building_age_code TEXT NULL;

DROP INDEX IF EXISTS ingest.extraction_state_natural_key;

CREATE UNIQUE INDEX extraction_state_natural_key ON ingest.extraction_state ( source_id, dim_code, period_start, period_end, (COALESCE(fire_type_code, '')), (COALESCE(building_type_code, '')), (COALESCE(region_code, '')), (COALESCE(building_age_code, '')) );

COMMENT ON COLUMN ingest.extraction_state.building_age_code IS 'Native BRASK Bygningsalder band for this fetched slice. NULL when the slice does not ' 'iterate building age. Scheduling metadata only — not used for content correctness.';

-- ============================================================================= -- 6. Replace legacy read RPC with flexible, breakdown-aware get_fire_data -- (PostgreSQL identifier; pairs with TS getFireData()). Formerly named get_chart_data. -- ============================================================================= -- Signature changes from v1: -- * Filter parameters are TEXT[] (multi-select) and accept either native or -- standard codes — the RPC resolves via filters. -- * p_cat_standard: filter categories by standard_code (cross-source -- equivalence) instead of per-source cat_code. -- * p_breakdown_axes: TEXT[] of axis names ('fire_type', 'building_type', -- 'building_age', 'region') to add to the GROUP BY at runtime. Default -- '{}' means standard breakdown only (period, source, dim, cat).

-- Implementation properties retained: -- * LANGUAGE sql STABLE PARALLEL SAFE -- * SET search_path = '' (security hardening; all refs fully qualified) -- * SECURITY INVOKER (default) — RLS applies to the caller -- * GRANT EXECUTE to anon, authenticated -- * Historical note: this embedded migration used UNION ALL across fact_daily + fact_yearly. -- Replaced in later migrations by granular branching (see header drift warning above).

-- Signature change is incompatible with CREATE OR REPLACE — drop first. -- =============================================================================

DROP FUNCTION IF EXISTS fire_data.get_chart_data( date, date, text[], text[], integer[], text, text, text, text, text ); DROP FUNCTION IF EXISTS fire_data.get_fire_data( date, date, text[], text[], text[], text[], text[], text[], text[], text[], text, text );

CREATE OR REPLACE FUNCTION fire_data.get_fire_data( p_start_date DATE, p_end_date DATE, p_sources TEXT[] DEFAULT NULL, p_dim_codes TEXT[] DEFAULT NULL, p_cat_standard TEXT[] DEFAULT NULL, p_fire_type TEXT[] DEFAULT NULL, p_building_type TEXT[] DEFAULT NULL, p_building_age TEXT[] DEFAULT NULL, p_region TEXT[] DEFAULT NULL, p_breakdown_axes TEXT[] DEFAULT '{}', p_granularity TEXT DEFAULT 'month', p_metric TEXT DEFAULT 'count' ) RETURNS TABLE ( period DATE, source_id TEXT, dim_code TEXT, cat_code INT, fire_type_code TEXT, -- NULL unless 'fire_type' is in p_breakdown_axes building_type_code TEXT, -- NULL unless 'building_type' is in p_breakdown_axes building_age_code TEXT, -- NULL unless 'building_age' is in p_breakdown_axes region_code TEXT, -- NULL unless 'region' is in p_breakdown_axes value NUMERIC ) LANGUAGE sql STABLE PARALLEL SAFE SET search_path = '' AS $$

---

-- Resolve filter inputs to (source_id, native_code) pairs. -- Accepts either a native_code or a standard_code as input — the OR clause -- in the WHERE handles both. Result is the WHERE-clause filter set per axis.

---

WITH resolved_fire_type AS ( SELECT DISTINCT fc.source_id, fc.native_code FROM fire_data.filters fc WHERE p_fire_type IS NOT NULL AND fc.axis = 'fire_type' AND (fc.native_code = ANY(p_fire_type) OR fc.standard_code = ANY(p_fire_type)) ), resolved_building_type AS ( SELECT DISTINCT fc.source_id, fc.native_code FROM fire_data.filters fc WHERE p_building_type IS NOT NULL AND fc.axis = 'building_type' AND (fc.native_code = ANY(p_building_type) OR fc.standard_code = ANY(p_building_type)) ), resolved_building_age AS ( SELECT DISTINCT fc.source_id, fc.native_code FROM fire_data.filters fc WHERE p_building_age IS NOT NULL AND fc.axis = 'building_age' AND (fc.native_code = ANY(p_building_age) OR fc.standard_code = ANY(p_building_age)) ), resolved_region AS ( SELECT DISTINCT fc.source_id, fc.native_code FROM fire_data.filters fc WHERE p_region IS NOT NULL AND fc.axis = 'region' AND (fc.native_code = ANY(p_region) OR fc.standard_code = ANY(p_region)) ),

---

-- Resolve cat_standard filter: which (source, dim, cat) tuples match. -- categories is small enough that a CTE without index hint is fine.

---

resolved_categories AS ( SELECT c.source_id, c.dim_code, c.cat_code FROM fire_data.categories c WHERE p_cat_standard IS NOT NULL AND c.standard_code = ANY(p_cat_standard) ),

---

-- Historical UNION ALL body (superseded on linked DB by branched get_fire_data). -- Kept only as migration archaeology — see docs/fire-data-schema/schema-history/incremental-patches-and-rpc-changelog.md.

---

fact_union AS ( SELECT d.date AS event_date, d.source_id, d.dim_code, d.cat_code, d.fire_type_code, d.building_type_code, d.building_age_code, d.region_code, d.value, d.value_kr FROM fire_data.fact_daily d WHERE d.date BETWEEN p_start_date AND p_end_date

UNION ALL

SELECT
  make_date(y.year, 1, 1) AS event_date,
  y.source_id, y.dim_code, y.cat_code,
  y.fire_type_code, y.building_type_code, y.building_age_code, y.region_code,
  y.value, y.value_kr
FROM fire_data.fact_yearly y
WHERE make_date(y.year, 1, 1) BETWEEN p_start_date AND p_end_date

) SELECT -- Granularity bucket. date_trunc returns TIMESTAMP; cast back to DATE. CASE p_granularity WHEN 'day' THEN fu.event_date WHEN 'week' THEN date_trunc('week', fu.event_date)::date WHEN 'month' THEN date_trunc('month', fu.event_date)::date WHEN 'quarter' THEN date_trunc('quarter', fu.event_date)::date WHEN 'year' THEN date_trunc('year', fu.event_date)::date ELSE date_trunc('month', fu.event_date)::date END AS period, fu.source_id, fu.dim_code, fu.cat_code, -- Breakdown projection: surface a column only when its axis is in -- p_breakdown_axes; otherwise NULL. The matching CASE in GROUP BY -- collapses unselected axes so they don't split rows. CASE WHEN 'fire_type' = ANY(p_breakdown_axes) THEN fu.fire_type_code END AS fire_type_code, CASE WHEN 'building_type' = ANY(p_breakdown_axes) THEN fu.building_type_code END AS building_type_code, CASE WHEN 'building_age' = ANY(p_breakdown_axes) THEN fu.building_age_code END AS building_age_code, CASE WHEN 'region' = ANY(p_breakdown_axes) THEN fu.region_code END AS region_code, -- Metric dispatch. SUM ignores NULLs, so rows where the requested metric -- is absent (e.g. value_kr on POLICE) contribute nothing — graceful -- degradation across heterogeneous sources. CASE p_metric WHEN 'kr' THEN SUM(fu.value_kr) ELSE SUM(fu.value) END AS value FROM fact_union fu WHERE (p_sources IS NULL OR fu.source_id = ANY(p_sources)) AND (p_dim_codes IS NULL OR fu.dim_code = ANY(p_dim_codes)) -- Cross-source category filter via standard_code AND (p_cat_standard IS NULL OR EXISTS ( SELECT 1 FROM resolved_categories rc WHERE rc.source_id = fu.source_id AND rc.dim_code = fu.dim_code AND rc.cat_code = fu.cat_code )) -- Filter-axis predicates. NULL input = no filter on this axis. -- Non-NULL = row must match (source_id, native_code) in the resolved set. AND (p_fire_type IS NULL OR EXISTS ( SELECT 1 FROM resolved_fire_type rft WHERE rft.source_id = fu.source_id AND rft.native_code = fu.fire_type_code )) AND (p_building_type IS NULL OR EXISTS ( SELECT 1 FROM resolved_building_type rbt WHERE rbt.source_id = fu.source_id AND rbt.native_code = fu.building_type_code )) AND (p_building_age IS NULL OR EXISTS ( SELECT 1 FROM resolved_building_age rba WHERE rba.source_id = fu.source_id AND rba.native_code = fu.building_age_code )) AND (p_region IS NULL OR EXISTS ( SELECT 1 FROM resolved_region rr WHERE rr.source_id = fu.source_id AND rr.native_code = fu.region_code )) GROUP BY period, fu.source_id, fu.dim_code, fu.cat_code, -- Conditional grouping: identical CASE expressions to the SELECT list. -- Postgres matches them syntactically — axes not in p_breakdown_axes -- evaluate to a constant NULL and collapse into a single bucket. CASE WHEN 'fire_type' = ANY(p_breakdown_axes) THEN fu.fire_type_code END, CASE WHEN 'building_type' = ANY(p_breakdown_axes) THEN fu.building_type_code END, CASE WHEN 'building_age' = ANY(p_breakdown_axes) THEN fu.building_age_code END, CASE WHEN 'region' = ANY(p_breakdown_axes) THEN fu.region_code END ORDER BY period, fu.source_id, fu.dim_code, fu.cat_code; $$;

GRANT EXECUTE ON FUNCTION fire_data.get_fire_data( date, date, text[], text[], text[], text[], text[], text[], text[], text[], text, text ) TO anon, authenticated;

COMMENT ON FUNCTION fire_data.get_fire_data( date, date, text[], text[], text[], text[], text[], text[], text[], text[], text, text ) IS 'Universal fire-data read RPC. All filter parameters accept arrays of native or standard codes; ' 'p_breakdown_axes composes GROUP BY at runtime. Replaces the v1 scalar-parameter RPC; ' 'renamed from get_chart_data.';

-- ============================================================================= -- 7. Seed filters — fire_type axis only -- ============================================================================= -- Source: taxonomy-and-eu-firestat-alignment.md, "Fire Type Encoding Per Source" section. -- Keep these rows in sync if the taxonomy evolves.

-- BRASK uses native parameter Type (2 values, 1:1 mapping to standard). -- BRIS uses native parameter Oppdragstype (3 or 4 values depending on -- dataset family — 4 in "Alle branner og ulykker", 3 elsewhere). Multiple -- BRIS native values collapse to CONFINED — preserving the natives at row -- level enables drill-down without losing cross-source comparability.

-- building_type / building_age / region rows are NOT seeded here. They get -- inserted as their respective adapters come online so the seed evolves -- alongside ingest logic. SSB has no fire_type axis at all (cannot separate -- confined / DEVELOPED) and is intentionally absent. -- =============================================================================

INSERT INTO fire_data.filters (source_id, axis, native_code, standard_code, display_name, description, sort_order) VALUES -- BRASK: Type parameter — 2 values, 1:1 mapping (no information loss) ('BRASK', 'fire_type', 'KALD', 'CONFINED', 'Kald (Begrenset)', 'BRASK Type=Kald — fire confined to object of origin', 10), ('BRASK', 'fire_type', 'VARM', 'DEVELOPED', 'Varm (Utviklet)', 'BRASK Type=Varm — fire spread beyond object of origin', 20),

-- POLICE: Oppdragstype on "Alle branner og ulykker" — 4 values, lossy mapping -- (3 of 4 collapse to CONFINED; native preserved on fact rows for drill-down) ('POLICE', 'fire_type', 'BRANN_I_BYGNING', 'DEVELOPED', 'Brann i bygning', 'BRIS Oppdragstype — building fire (DEVELOPED)', 10), ('POLICE', 'fire_type', 'BRANNTILLOP_KOMFYR', 'CONFINED', 'Branntilløp komfyr', 'BRIS Oppdragstype — stove incipient fire (confined)', 20), ('POLICE', 'fire_type', 'BRANNTILLOP_I_BYGG_ANNET', 'CONFINED', 'Branntilløp i bygg annet', 'BRIS Oppdragstype — other incipient building fire (confined)', 30), ('POLICE', 'fire_type', 'BRANN_I_SKORSTEIN', 'CONFINED', 'Brann i skorstein', 'BRIS Oppdragstype — chimney fire (confined). Sub-A only.', 40),

-- BRIGADE: Oppdragstype on "Hvordan bygningsbranner starter" — 3 values -- (no Brann i skorstein on this dataset family) ('BRIGADE', 'fire_type', 'BRANN_I_BYGNING', 'DEVELOPED', 'Brann i bygning', 'BRIS Oppdragstype — building fire (DEVELOPED)', 10), ('BRIGADE', 'fire_type', 'BRANNTILLOP_KOMFYR', 'CONFINED', 'Branntilløp komfyr', 'BRIS Oppdragstype — stove incipient fire (confined)', 20), ('BRIGADE', 'fire_type', 'BRANNTILLOP_I_BYGG_ANNET', 'CONFINED', 'Branntilløp i bygg annet', 'BRIS Oppdragstype — other incipient building fire (confined)', 30),

-- DSB: same Oppdragstype set as BRIGADE (3 values, no skorstein) ('DSB', 'fire_type', 'BRANN_I_BYGNING', 'DEVELOPED', 'Brann i bygning', 'BRIS Oppdragstype — building fire (DEVELOPED)', 10), ('DSB', 'fire_type', 'BRANNTILLOP_KOMFYR', 'CONFINED', 'Branntilløp komfyr', 'BRIS Oppdragstype — stove incipient fire (confined)', 20), ('DSB', 'fire_type', 'BRANNTILLOP_I_BYGG_ANNET', 'CONFINED', 'Branntilløp i bygg annet', 'BRIS Oppdragstype — other incipient building fire (confined)', 30) ON CONFLICT (source_id, axis, native_code) DO NOTHING;

-- SSB: deliberately not inserted — no fire_type filter capability.

-- ============================================================================= -- 8. Seed snippet (BRASK building_age) — run with adapter rollout -- ============================================================================= -- Keep this snippet ready for the BRASK adapter migration step so building_age -- semantics are explicit from day one: -- * granular native brackets keep one-to-one standard mappings -- * Ukjent is a true semantic bucket and maps to UNKNOWN -- * only overlapping rollups (e.g. building_type='ALLE') stay NULL

-- INSERT INTO fire_data.filters -- (source_id, axis, native_code, standard_code, display_name, description, sort_order) -- VALUES -- ('BRASK', 'building_age', 'Ny', 'AGE_NEW', 'Ny', 'BRASK Bygningsalder', 10), -- ('BRASK', 'building_age', '1-5', 'AGE_1_5', '1-5', 'BRASK Bygningsalder', 20), -- ('BRASK', 'building_age', '6-10', 'AGE_6_10', '6-10', 'BRASK Bygningsalder', 30), -- ('BRASK', 'building_age', '11-15', 'AGE_11_15', '11-15', 'BRASK Bygningsalder', 40), -- ('BRASK', 'building_age', '16-20', 'AGE_16_20', '16-20', 'BRASK Bygningsalder', 50), -- ('BRASK', 'building_age', '21-25', 'AGE_21_25', '21-25', 'BRASK Bygningsalder', 60), -- ('BRASK', 'building_age', '26-30', 'AGE_26_30', '26-30', 'BRASK Bygningsalder', 70), -- ('BRASK', 'building_age', '31-40', 'AGE_31_40', '31-40', 'BRASK Bygningsalder', 80), -- ('BRASK', 'building_age', '41-50', 'AGE_41_50', '41-50', 'BRASK Bygningsalder', 90), -- ('BRASK', 'building_age', '51-75', 'AGE_51_75', '51-75', 'BRASK Bygningsalder', 100), -- ('BRASK', 'building_age', '76-100', 'AGE_76_100', '76-100', 'BRASK Bygningsalder', 110), -- ('BRASK', 'building_age', 'Over 100', 'AGE_OVER_100', 'Over 100', 'BRASK Bygningsalder', 120), -- ('BRASK', 'building_age', 'Ukjent', 'UNKNOWN', 'Ukjent', 'BRASK Bygningsalder', 130) -- ON CONFLICT (source_id, axis, native_code) DO NOTHING;

-- ============================================================================= -- Migration complete.

-- POST-MIGRATION FOLLOW-UPS (NOT in this script):

-- 1. Update lib/data-model/queries.ts to match the new RPC signature: -- - All filter params are TEXT[] (was scalar TEXT or INT[]). -- - p_cat_codes (INT[]) replaced by p_cat_standard (TEXT[]). -- - New params: p_building_age, p_breakdown_axes. -- - Return shape includes building_age_code (was absent).

-- 2. Add the literal union types in lib/data-model/types.ts: -- export type FilterAxis = -- | 'fire_type' | 'building_type' | 'building_age' | 'region' -- export type BreakdownAxis = FilterAxis -- // FactDaily / FactYearly: add building_age_code: string | null.

-- 3. Regenerate Supabase types: -- supabase gen types typescript --project-id xulwgjinkizflfzdobkn
-- --schema fire_data > types/fire_data.database.types.ts

-- 4. Build the BRASK adapter (see brask-ingest-loop-and-invariants.md). The adapter MUST -- upsert into filters (ON CONFLICT DO NOTHING) before writing fact -- rows referencing those native codes — this is the integrity mechanism -- that replaces the dropped CHECK constraints (per brask-ingest-loop-and-invariants.md §5.2.2).

-- 5. Subsequent adapters (BRIS Police/Brigade/DSB, SSB) follow the same -- four-step skeleton (fetch → translate → upsert → mark state). -- =============================================================================