HeatWaves Data Patches

Source of truth: This file tracks small, targeted data-layer patches and clarifications. For major contracts, use v2-001-initial-migration-narrative.md and v2-002-filter-codes-migration-narrative.md.

---

2026-05-10 — ingest_runs analytics columns + prefetch + conditional batch upsert

Purpose:

• Extend fire_data.ingest_runs with granular write counters (rows_added, rows_updated, rows_skipped_identical), scalar Utforsk totals (api_total, transformed_row_count), and per_slice_metrics for multi-slice batch runs.
• Add fire_data.fact_yearly_slice_ingest_prefetch — cheap grand total, per-year JSON, and row count for tier-1/tier-2 gates without loading full slices.
• Add fire_data.upsert_fact_yearly_batch — batch upsert with ON CONFLICT DO UPDATE … WHERE … IS DISTINCT FROM to avoid rewriting identical rows.

Migration file: supabase/migrations/20260510140000_ingest_runs_metrics_fact_prefetch_upsert.sql.

Narrative: pipeline-slices-idempotency-and-triggers.md §5.5–5.6, catalog-governance-and-reconciliation.md §1 / §5.

Constraints / RLS on existing tables are unchanged — ALTER TABLE … ADD COLUMN only plus new functions.

---

2026-04-29 — ARNESTED rename patch

Purpose:

• Rename POLITI_ARNESTED dimension code to ARNESTED while preserving referential integrity.

SQL:

BEGIN;

ALTER TABLE fire_data.categories
  DROP CONSTRAINT categories_dim_code_fkey,
  ADD CONSTRAINT categories_dim_code_fkey
    FOREIGN KEY (dim_code) REFERENCES fire_data.dimensions(dim_code)
    ON UPDATE CASCADE ON DELETE RESTRICT;

UPDATE fire_data.dimensions
SET dim_code     = 'ARNESTED',
    name         = 'Arnested',
    display_name = 'Area of Origin',
    description  = 'Where in the building the fire originated. Source-scoped categories — POLICE has its 21; future sources can add their own.'
WHERE dim_code = 'POLITI_ARNESTED';

COMMIT;

---

RPC note — get_fire_data argument model (reference)

Current shape summary (Postgres name; TS wrapper is getFireData()). Formerly get_chart_data.

get_fire_data(
  p_start_date       DATE,
  p_end_date         DATE,
  p_sources          INTEGER[] DEFAULT NULL,
  p_dim_codes        INTEGER[] 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'
)

Behavior summary:

• p_sources / p_dim_codes use numeric ids (sources.source_id, dimensions.dim_code).
• sources.supports_subyear gates whether a source appears in sub-year get_fire_data reads (fact_daily branch); taxonomy yearly-only rows keep false.
• fact_yearly for p_granularity = year is filled only by ingest (never by summarizing fact_daily in SQL helpers).
• One argument per filter axis (p_fire_type, p_building_type, p_building_age, p_region) — each TEXT[] element may be native_code or standard_code; RPC resolves to filters.id.
  • Within one axis: multiple array elements expand to distinct matching filter ids; facts match if their FK is any of those ids (logical OR). Example: two leaf fire-type codes select both series at once; overlapping totals are not double-counted because each fact row has one FK per axis.
  • Across axes: predicates for p_fire_type, p_building_type, etc. are ANDed. Omitted / NULL axis args — breakdown-aware (IS NULL total slice when axis not in p_breakdown_axes); live semantics in postgres-contract-rls-and-keys.md (migration 20260601120000).
  • p_cat_standard behaves the same way against categories.standard_code (OR within the set, combined with other predicates via AND).
• p_breakdown_axes selects optional breakdown dimensions; result columns for those axes return filters.id (nullable when inactive). Join filters for labels.
• Contrast with ETL: adapters write atomic rows (one filters.id per axis per stored row, or NULL). Chart standard_code expansion is read-time only. See docs/ingestion/pipeline-slices-idempotency-and-triggers.md §5.2–5.3.