frs_wsg_drainage: replace single-ltree closure with measure-aware fwa_downstream; drop public.wsg_outlet dependency

[NewGraphEnvironment]

Problem

frs_wsg_drainage() computes drainage closure with a single-column ltree ancestry test:

WHERE f.outlet <@ w.outlet

against public.wsg_outlet — a table that no repo builds. It was materialized ad-hoc in a psql session on 2026-05-24 (link#175); the CREATE statement was never committed and survives only in a session transcript. Consumers that connect to a database without it fail hard (relation "public.wsg_outlet" does not exist), which is NewGraphEnvironment/link#227.

Worse, the predicate is wrong, and the missing table was hiding it.

Why the predicate is wrong

A single ltree per group cannot express where along a stem a group sits. Its wscode_ltree names the stream; it does not say whether you are above or below a given confluence. Consequences, measured against the local FWA load (246 groups):

• 25% of groups (62/246) resolve to an outlet shallower than their own polygon's wscode_ltree, because the ad-hoc derivation took nlevel(wscode_ltree) ASC — the shallowest code appearing anywhere in the group. A group that merely clips a larger river inherits that river's code and becomes a false ancestor of everything upstream on it.
• Only 167 distinct outlets for 246 groups. The largest false-ancestor cluster is 14 Fraser groups all sharing outlet 100 (BBAR CHWK COTR DOGC FRCN HARR LFRA MFRA MORK NARC SETN TABR TWAC UFRA). Because <@ is inclusive, each is an "ancestor" of every other, so any single Fraser focal pulls in all 14.
• Ordering hazard. Equal outlets give equal nlevel, so the query falls through to its w.wsg ASC tiebreak — alphabetical. In that Fraser cluster, LFRA (the most downstream group) sorts seventh, so six groups upstream of it order first. Downstream-first ordering is load-bearing for cross-WSG ;DAM propagation in link, so this is a correctness hazard, not just wasted compute.

Concrete example — Morice and Bulkley. The Morice (Wedzin Kwa) is hydrologically the mainstem, but FWA carried the "Bulkley" name down through the confluence, so the Morice is filed as a tributary of the thing it feeds. Under the current predicate MORR resolves to outlet 400.431358 — identical to BULK — so the Bulkley appears to drain through the Morice. Backwards.

Fix

Use FWA's own topology function and the polygon table that ships with fwapg:

WHERE whse_basemapping.fwa_downstream(a.wscode_ltree, a.localcode_ltree,
                                      b.wscode_ltree, b.localcode_ltree)

localcode_ltree supplies the along-stem position the single code lacks, and fwa_downstream is the same primitive family as the fwa_upstream already used across fresh/link.

This also removes the public.wsg_outlet dependency entirely — whse_basemapping.fwa_watershed_groups_poly is standard FWA infrastructure, verified present with 246 rows and fwa_downstream available in both the local docker fwapg and the tunnelled bcfishpass database. Nothing to build, nothing to ship, no reproducibility hole.

Province-wide run: 0.3 s, 1032 edges, 208 of 246 groups with at least one downstream group (the remaining 38 are coastal outlets).

Behaviour changes

• Default table becomes whse_basemapping.fwa_watershed_groups_poly; the expected columns change from (wsg, outlet) to (watershed_group_code, wscode_ltree, localcode_ltree). Breaking for any caller passing a custom table.
• Closures get tighter. frs_wsg_drainage(conn, c("PARS","BULK")) goes from 15 groups to 10, correctly dropping LKEL (separate tributary), MSKE / USKE (upstream of the confluence), LBTN (sibling), MORR (upstream).
• fwa_downstream excludes self, so the focal group must be unioned in explicitly — inclusive <@ gave that for free.
• The roxygen @examples block and the acceptance criterion in frs_wsg_drainage: FWA WSG drainage-closure primitive #211 both record the old 15-group answer. Both need updating; that 15-group result is not a correct oracle.

Acceptance

Hydrology fixtures, all verified passing against the proposed predicate:

• MORR → BULK TRUE, BULK → MORR FALSE
• BULK → LSKE TRUE, BULK → KISP TRUE (the Skeena does flow through the Kispiox group)
• BULK → MSKE FALSE, BULK → USKE FALSE (upstream of the confluence)
• BULK → LKEL FALSE, PARS → LBTN FALSE (siblings)
• Focal group present in its own closure
• DS-first ordering derived from topology, not an alphabetical tiebreak — LFRA first in any Fraser closure
• Closure invariant to focal ordering
• Runnable @example

Refs

• NewGraphEnvironment/link#227 — the missing-table symptom; this removes its first half entirely
• Supersedes the closure predicate introduced in frs_wsg_drainage: FWA WSG drainage-closure primitive #211

[NewGraphEnvironment]

Implementation attempt — two blockers found, parking the predicate change

Tried the proposed fix and it does not work as specified. Recording both blockers so the next pass doesn't repeat them.

Blocker 1 — the 4-arg fwa_downstream can't resolve same-stem ordering

The ltree-only overload fwa_downstream(wscode_a, localcode_a, wscode_b, localcode_b) returns FALSE for pairs that are genuinely downstream of one another when they share a stem:

UFRA (ws 100, lc 100.843983)  ->  LFRA (ws 100, lc 100)   =>  FALSE

Upper Fraser water demonstrably reaches the sea through the Lower Fraser. Skeena and Peace fixtures pass only because those pairs differ in wscode; every Fraser group carries 100, which exposes it. Measured result: frs_wsg_drainage(conn, "UFRA") returns 12 groups with LFRA absent entirely.

Resolving same-stem order needs the measure-aware overload:

fwa_downstream(blue_line_key_a, downstream_route_measure_a, wscode_a, localcode_a,
               blue_line_key_b, downstream_route_measure_b, wscode_b, localcode_b, ...)

which requires a genuine outlet point (blue_line_key + downstream_route_measure) per watershed group. fwa_watershed_groups_poly carries no such point, so the clean "just read the polygons" fix in the issue body is not available. Defining that outlet point from fwa_stream_networks_sp is the actual open problem — a naive "most-downstream point of the group's highest-order stream" picks up slivers of neighbouring systems (for BULK it selects a 62 m piece of the Morice line that clips the polygon).

Blocker 2 — the local docker fwapg poly table is unpopulated

SELECT count(*), count(wscode_ltree), count(localcode_ltree)
FROM whse_basemapping.fwa_watershed_groups_poly;
-- docker fwapg      : 246 | 0 | 0
-- tunnelled bcfishpass: 246 | 246 | 246

All 246 rows in the local docker fwapg have NULL wscode_ltree, localcode_ltree, fwa_watershed_code and local_watershed_code — geometry and names only. The tunnelled bcfishpass database has them fully populated, which is why the derivation validated there and errored locally.

That is a second, independent infrastructure gap of the same family as NewGraphEnvironment/link#227: consumers connect to different databases with materially different load states. data-raw/study_area_wsgs.R in link targets docker fwapg; the failing test goes through lnk_db_conn() to the tunnel. Any fix has to state which database it targets and verify the source is populated there.

Still valid from the issue body

The diagnosis stands — the current single-ltree predicate is wrong, 62/246 groups get an over-shallow outlet, 14 Fraser groups collapse onto 100, and the alphabetical tiebreak is an ordering hazard. MORR/BULK remains the clearest illustration. Only the proposed remedy is blocked.

Suggested next step

Split the outlet-point definition out as its own piece of work — deriving a defensible per-group (blue_line_key, downstream_route_measure, wscode, localcode) from the stream network, validated against hydrology fixtures. Until that exists there is no correct predicate to install here.

[NewGraphEnvironment]

Correction to the comment above — blocker 2 was overstated

Blocker 2 as written ("the local docker fwapg poly table is unpopulated") is not a blocker. Correcting the record.

The NULL code columns only mattered for the poly-reading approach, which blocker 1 already rules out. The source the real derivation needs — whse_basemapping.fwa_stream_networks_sp — is fully populated in the local docker fwapg: 32,472 BULK segments, all with wscode_ltree, 30,046 with localcode_ltree (segments without a local code are ordinary FWA, not a gap). The legacy wsg_outlet SQL read that same table and ran fine locally.

No FWA reload is needed. What survives is a documentation note, not an action item: the two databases differ in load state, so code reading the poly code columns silently works against the tunnelled bcfishpass and returns empty against docker fwapg. That is a reason to source from the stream network — which is where the outlet derivation was already headed.

One real blocker stands: no per-group outlet point (blue_line_key + downstream_route_measure) exists, and the measure-aware fwa_downstream overload requires one.