Ingest CABD dams as parallel reporting dimension

[NewGraphEnvironment]

Problem

link does not ingest dam locations from CABD. bcfp pulls them from cabd.dams + applies the same four edit CSVs that the falls path uses (cabd_exclusions, cabd_blkey_xref, cabd_passability_status_updates, cabd_additions filtered to feature_type='dams').

Important framing: bcfp's per-species access models AND habitat_linear models are dam-blind — verified across all 5 model_access_*.sql and 8 load_habitat_linear_*.sql files: zero references to barriers_dams, barriers_anthropogenic, or barriers_pscis. Dams in bcfp live as a parallel reporting dimension (the bcfishpass.dams table) that downstream consumers compose with habitat output for reports, WCRP tracking, and dam-impact analyses.

So this issue is not a habitat-parity gap — fixing it will not close any rollup deltas. It's a reporting-data gap. We need dams in the system because:

• Real-world habitat above Stave / Alouette / Campbell / Strathcona dams is materially blocked even though bcfp's habitat output shows it as accessible. Reports composed off the habitat output need to factor dams in at the reporting layer (separate from rule-based classification).
• link is producing an SRED-relevant per-segment habitat layer; consumers (memo authors, project teams, fish-passage planners) will ask "what's above each dam?" and we need data to answer.
• Doing it now while we have the falls-CABD wiring fresh in mind is cheaper than coming back to it later.

The per-species methodology question — "should default-bundle make some dam classes block which species?" — is separate and tracked at link#83. This issue is purely about getting the data in. link#83 is the consumer-side question.

bcfp's load_dams.sql, mapped

bcfp/model/01_access/sql/load_dams.sql — same shape as load_falls.sql, four CABD-edit CSVs:

-- 1. Pull CABD dams, exclude false positives, snap blkey via xref
with cabd as (
  select d.cabd_id as dam_id, blk.blue_line_key, st_transform(d.geom, 3005) as geom
  from cabd.dams d
  left outer join bcfishpass.cabd_exclusions x on d.cabd_id = x.cabd_id
  left outer join bcfishpass.cabd_blkey_xref blk on d.cabd_id = blk.cabd_id
  where x.cabd_id is null
),

-- 2. Snap to nearest stream segment within 65 m
matched as ( ...lateral join on fwa_stream_networks_sp... ),

-- 3. Apply passability override + carry CABD attributes (height_m, owner, dam_use, operating_status)
cabd_pts as (
  select n.*, cabd.dam_name_en, cabd.height_m, cabd.owner, cabd.dam_use, cabd.operating_status,
         coalesce(u.passability_status_code, cabd.passability_status_code) as passability_status_code
  from matched n
  inner join cabd.dams cabd on n.dam_id = cabd.cabd_id
  left outer join bcfishpass.cabd_passability_status_updates u on n.dam_id = u.cabd_id
),

-- 4. US dam placeholders (Grand Coulee, Ross) — additions where feature_type='dams'
usa as ( ... select from bcfishpass.cabd_additions where feature_type = 'dams' ... )

insert into bcfishpass.dams (...) select * from cabd_pts union all select ... from usa;

What's in cabd_additions.csv for dams (currently 4 rows)

All four are US-side dam placeholders for trans-border flows — Grand Coulee Dam x4 entries on different blkeys (different streams flowing into the Columbia → Grand Coulee impoundment), and Ross Dam x1. No domestic-BC dam additions in the current CSV. The CABD source itself covers all the BC dams; additions exists to handle CABD's BC-only geographic scope.

Proposed approach

Same shape as the falls issue (link#? CABD waterfalls):

1. Source pull from cabd.dams (DB join over the tunnel, or a download — same path decision as falls).
2. Edit application using the four shared CSVs (already loaded for the falls path).
3. Storage: <schema>.dams table with the bcfp column set (dam_name_en, height_m, owner, dam_use, operating_status, passability_status_code, geom).
4. NOT used as an access barrier in habitat classification (matches bcfp design). Available as a parallel data layer for lnk_aggregate / report consumers / future per-species gating per link#83.

Bundle config declares cabd.dams ingestion (or an extracted CSV mirror, depending on path decision) under files: like cabd_dams.csv. Both bcfp and default bundles ingest — the data is methodology-agnostic.

Verification — prove the data is wired correctly before closing

1. Pre-fix: count rows in any link <schema>.dams (zero — the table doesn't exist).
2. Post-fix: count rows; spot-check named dams appear with correct (blue_line_key, downstream_route_measure) against bcfp tunnel — Stave / Alouette / Ruskin / Coquitlam / Elk Falls / John Hart / Strathcona / Hugh Keenleyside / Mica / Revelstoke.
3. Confirm zero impact on habitat rollup: run the 4-WSG regression (HARR/HORS/LFRA/BABL); both bcfp and default bundle rollups must be byte-identical to pre-fix baseline. If they shift, the dams data is leaking into habitat classification incorrectly — fix that before closing.

The third check is the load-bearing one: dams data in, habitat output unchanged, parallel data layer ready for the consumer side.

Out of scope

• Per-species access gating on dams (link#83 — methodology decision, separate issue)
• WCRP / dam-impact report composition (downstream consumer work)
• CABD release version pin / refresh cadence — same follow-up as the falls issue

Reference

• bcfp model/01_access/sql/load_dams.sql — implementation reference
• bcfp model/01_access/sql/barriers_dams.sql, barriers_dams_hydro.sql, barriers_anthropogenic.sql — downstream consumers of bcfishpass.dams (we don't replicate these; reporting-layer concerns)
• research/bcfishpass_comparison.md § "Dams design — much smaller than expected" — bcfp's dam-blindness in habitat models
• link#83 — per-species dam-class handling (consumer methodology — depends on this issue)
• link#? CABD waterfalls + edit CSVs (sibling — the 4 edit CSVs and source-pull mechanism overlap)

Relates to NewGraphEnvironment/sred-2025-2026#24