TwoMore

Appearance

B6 — Venues + Weather: Adversarial Backend Audit ​

Status: Archived Date: 2026-07-11 Scope: venue provenance pipeline (venue_source_observations + field_confidence + resolve_venue_facts, migration 00347 and every subsequent redefinition through 00370), fee parser (00348), user corrections (00355/00356/00357/00358/00382/00383), ingest/enrich edge functions (ingest-venues, enrich-venues), the scrape pipeline (scripts/scrape-naver-place.mjs, scripts/lib/emit-observations.mjs, scripts/enrich-venues.mjs), the venue directory read path (packages/app/src/adapters/supabase/venue-directory.supabase.ts), venue_media/courts bridge rows, and the weather pipeline (fetch-weather, fetch-weather-warnings, fetch-medium-forecast, fetch-living-weather edge functions + weather_cache/medium_forecast_cache/weather_warning_cache + signal emission migrations 00111/00113/00287/00316). Method: direct migration-by-migration read of the full resolver lineage (00347→00355→00356→00357→00358→00370) to trace exactly how each redefinition changed behavior, live-incident cross-referencing (00378/00380 naver_place_id collision, 00382 owner-QA corrections) against the CURRENT code to check whether root causes were fixed or only patched, grep sweeps for RLS/GRANT tightening that never happened, and a batch-size trace through the actual operational scrape pipeline (scripts/lib/emit-observations.mjs) to size real blast radii rather than assume worst-case. No code modified.

Executive-severity ranking ​

#FindingSeverity
1ingest-venues edge function's token check is fail-OPEN: if INGEST_TOKEN is unset, the entire write/delete surface (including a mass-delete action=reset) becomes unauthenticatedCRITICAL
2venue_corrections RLS/GRANT was never tightened after the raw table was created — a client can bypass submit_venue_correction's entire validation stack via a direct INSERT, and a malformed value can abort resolve_venue_facts for an entire enrichment batch (empirically up to 400 venues per real pipeline call)CRITICAL
3Owner/host facts recorded as venue_source_observations(provider='manual') (not through the venue_corrections ledger) are not protected by the "human corrections win" override — they're one ordinary vote and can be silently outvoted by 2 agreeing lower-trust scraped sources on the next resolve passHIGH
4A venue wrongly marked closed_at (reachable via 2 colluding/sockpuppet accounts, cheaper still via #2's bypass) has no reopen/dispute path anywhere in the codebase — permanent removal from the directory short of a manual SQL fixHIGH
5The tennis-fee parser (private.parse_tennis_fee, 00348) that fixed "soccer prices shown for a tennis court" is called only from its one-time backfill migration — never from the live ingest/enrich write path, so any future re-ingest reintroduces the bug it fixedMEDIUM
6adm1_id/adm2_id region linkage has no ingest-time guard — ingest_venues_batch never populates it; population is a standalone script that must be manually remembered and re-runMEDIUM
7signals_on_weather_warning_activated only fires on inactive→active transitions — a warning that stays active but escalates severity (주의보→경보) mid-window never re-notifies already-warned usersMEDIUM
8No per-row exception isolation in enrich_venues_batch/resolve_venue_facts loops — the naver_place_id UNIQUE collision that already broke a live batch (00378/00380) was patched at its one known cause, not structurally; any future single bad row can still abort an entire batch with zero partial-progressMEDIUM
9fetch-weather's batch-mode error logging references grid.name, a field that does not exist on any ALL_GRIDS entry — every failure log/failedGrids entry is undefined, so cron failures are undiagnosable from logs aloneLOW
10x-ingest-token/x-cron-secret comparisons use plain !== (not constant-time) — a theoretical timing side-channel on server-to-server shared secretsLOW
11Confirmed still true, by design: enrich_venues_batch silently drops any JSONB key it doesn't explicitly name in its SET list (the "unnamed keys dropped" behavior flagged in project history)INFO
12Confirmed complete: 00316's weather-warning session_status enum fix is live and correct; the safety-notification path is no longer deadINFO (verified fix)

What works well (evidence):

  • SECDEF hygiene is airtight across the entire venue-correction lineage. Every redefinition of resolve_venue_facts and submit_venue_correction (00347, 00355, 00356, 00357, 00358, 00370) consistently sets SECURITY DEFINER + SET search_path = '' + REVOKE ALL ... FROM PUBLIC + an explicit narrow GRANT EXECUTE (service_role for the resolver, authenticated for the correction RPC). No drift found in 6 redefinitions.
  • 00356's hardening of submit_venue_correction is real, substantive, adversarially-tested work, not just a comment: it independently fixed a CRIT coord-wipe (JSON null propagating past a #>>'{}' check as SQL NULL), a HIGH empty-array data wipe, a HIGH pin-walk exploit (chained ≤500m moves), and a MED rejected/superseded-row vote-counting bug — all four fixes verified still present in the current function body (00356:55-106, 84-136).
  • 00357's reputation-weighted trust gate is correctly wired: a low-tier (sprout/active) user's correction that contradicts a high-confidence, non-host, multi-source fact is held as pending_agreement until a 2nd independent user agrees (00357:206-245); confirmations, void-field fills, and low-confidence fixes still apply instantly for everyone, and trusted/veteran users bypass the gate everywhere — matching the documented OSM-contributor-reputation model exactly.
  • weather_cache's early "any authenticated user can write" RLS hole (00028) was found and closed in 00154 ("00028 granted INSERT/UPDATE to authenticated, allowing any logged-in user to poison the shared weather cache... restricting to service_role causes no client breakage") — verified via the DROP POLICY + re-CREATE POLICY ... TO service_role pair. medium_forecast_cache and weather_warning_cache were SELECT-only for authenticated from their first migration (00081) and never had a write hole.
  • All 4 weather-fetch edge functions implement the identical fail-closed cron gate (cronSecret.length === 0 || provided !== cronSecret → 403), verified by direct read of fetch-weather, fetch-weather-warnings, fetch-medium-forecast, and fetch-living-weather — the "Wave E2" hardening pass was applied consistently across all four, closing the "DoS-via-KMA-quota" vector the code comments name. This makes finding #1 (ingest-venues's fail-OPEN pattern) a genuine outlier, not the house style — the team clearly knows and uses the correct pattern elsewhere in the same codebase.
  • 00316's weather-warning enum fix is confirmed live and correct. The current signals_on_weather_warning_activated body filters s.status IN ('open', 'locked', 'in_progress') — the real non-terminal session_status labels — not the never-existed 'scheduled'/'published' labels that made the entire severe-weather notification path silently dead-on-arrival (every trigger fire raised 22P02 invalid input value for enum, aborting the weather_warning_cache upsert itself). Verified by reading the live function body, not just the fix migration's own claim.
  • 영동/영서 and 경기북부/남부 forecast-region splitting is correctly grid-position-derived, not hardcoded per-sido: gridToForecastRegion (fetch-weather/index.ts:307-317) splits 강원 on nx >= 85 (east coast) and 경기 on ny >= 128 (north of the Han/border districts), with parseRegionGrades normalizing both API endpoints' differing label conventions (예보통보's 영동/영서 vs 주간예보's 강원영동/강원영서) to one canonical key.
  • AQI's D+6 ceiling is a real, well-engineered API-shape limit, not a code bug: fetchForecastAqiByDate merges getMinuDustFrcstDspth (D+0..D+2) with a dual-fetch (yesterday + today searchDate) of getMinuDustWeekFrcstDspth (D+2..D+6) specifically to avoid the pre-17:00-KST gap when today's weekly issuance isn't published yet — the merge-not-replace logic ({...(out[dt] ?? {}), ...grades}) is correct.
  • The just-added Naver-scrape branch-mismatch coord guard is sound. scrape-naver-place.mjs:292-306 rejects a name-containment match when the scraped place's own coordinate sits >600m from the venue's existing coordinate (guards against same-brand different-branch mismatches, e.g. "마마테니스" ⊂ "마마테니스 개포점", 2.4km away) — a real, live-observed failure mode fixed with a conservative, explicitly-justified trade-off ("missing data is recoverable, wrong-facility data is corrosive").
  • The corroboration-gate SQL on the read side is correctly AND/OR (already verified in U6, re-confirmed here): venue-directory.supabase.ts:87 correctly excludes closed venues via .or('closed_at.is.null,closure_reason.eq.temporary'), matching the documented active-venue partial index (00283) verbatim across every place it's referenced (00371/00372/00373/00374/00375/00380 all reproduce the identical filter).

1. CRITICAL — ingest-venues token gate is fail-open; a missing secret exposes writes AND a mass-delete ​

Evidence:

  • supabase/functions/ingest-venues/index.ts:48-51:
    ts
    const token = Deno.env.get('INGEST_TOKEN');
if (token && req.headers.get('x-ingest-token') !== token) {
  return json({ error: 'unauthorized' }, 401);
}
    If INGEST_TOKEN is unset/empty, token is falsy, the && short-circuits, and the if body never runs — the request proceeds fully unauthenticated.
  • Contrast with the sibling enrich-venues/index.ts:25-29, which fails CLOSED on the identical scenario: if (!expected || token !== expected) return 401 — !expected explicitly catches the missing-env-var case.
  • Contrast with all 4 weather-fetch edge functions (fetch-weather:789-796 and identical in the other 3), which also fail closed: if (cronSecret.length === 0 || provided !== cronSecret) return 403.
  • ingest-venues is deployed --no-verify-jwt (per its own header comment, line 12) — i.e. it is a bare public HTTP endpoint with no Supabase auth layer underneath the custom token check. If the token check is bypassed, there is no second gate.
  • What an unauthenticated caller reaches on bypass: upsertVenues() (write.ts:9-63, real writes into public.venues via the service-role-backed ingest_venues_batch RPC when dry_run=false, attacker-controlled) and ?action=reset → resetImported() (write.ts:65-74): DELETE FROM venues WHERE verification_status = 'imported' — an unconditional bulk delete of the entire imported venue corpus (2,284+ venues per the live pilot data referenced in migration 00378/00380), with no confirmation, no soft-delete, no rate limit.
  • This is a live footgun, not a hypothetical: env-var rotation, a redeployed preview/staging function without secrets synced, or a typo in the secret name silently converts this into a fully open write-and-wipe API for the entire venue database. The identical class of bug is demonstrably preventable — the team's own enrich-venues sibling function and all 4 weather crons already use the correct fail-closed idiom.

Fix shape (not implemented): change line 49 to if (!token || req.headers.get('x-ingest-token') !== token), matching enrich-venues's pattern exactly.

2. CRITICAL — venue_corrections RLS/GRANT never tightened; raw INSERT bypasses all validation and can abort a whole resolve batch ​

Evidence:

  • 00339_venue_corrections.sql:41-51 created the table with:
    sql
    CREATE POLICY venue_corrections_insert_own ON public.venue_corrections
  FOR INSERT TO authenticated
  WITH CHECK (submitted_by = (SELECT auth.uid()));
GRANT SELECT, INSERT ON public.venue_corrections TO authenticated;
    A repo-wide grep (grep -rn "venue_corrections" supabase/migrations/*.sql | grep -iE "revoke|policy|grant") across every migration touching the table (00339, 00355, 00356, 00357, 00358, 00370, 00371-00374, 00379, 00380) returns only these three original 00339 lines — the policy and grant were never revisited, and no BEFORE INSERT trigger exists on the table (confirmed via grep -rn "CREATE TRIGGER" ... | grep venue_correction → zero hits).
  • The WITH CHECK only constrains submitted_by = auth.uid(). It does not constrain status, field, or proposed_value. Any authenticated user can therefore INSERT directly into venue_corrections (bypassing the submit_venue_correction RPC entirely) with status = 'applied' and an arbitrary, unvalidated proposed_value for any of the 5 allowed field values — skipping the RPC's rate limit (30/hr), strict jsonb_typeof bounds checks (00356), the reputation trust gate (00357), the location anti-walk guard, and the closed/location 2-agreement threshold.
  • Consequence A — silent fact poisoning: for the 4 "instant apply" fact fields (court_count, has_indoor, telephone, capabilities), a raw-inserted status='applied' row is picked up unconditionally by the "HUMAN CORRECTIONS WIN" step in resolve_venue_facts (every redefinition since 00355, e.g. 00370:250-255: WHERE venue_id = vid AND field = 'telephone' AND status = 'applied' — no other provenance check) the next time anyone else's legitimate correction on that same venue triggers PERFORM public.resolve_venue_facts(ARRAY[p_venue_id]). There is no table-level CHECK constraint bounding proposed_value (only the RPC's in-body validation, which this bypasses) — venues.court_count's own CHECK is only > 0 (00283:78), no upper bound.
  • Consequence B — batch-abort DoS: if the raw-inserted proposed_value is not a clean numeric/boolean string, the resolver's unguarded cast — e.g. r_cc := (hv #>> '{}')::int; (00370:271, present in every redefinition since 00355) — raises a hard Postgres cast exception (invalid input syntax for type integer). There is no BEGIN...EXCEPTION handling inside the FOREACH vid IN ARRAY p_venue_ids LOOP, so one poisoned venue anywhere in the array aborts the entire resolve_venue_facts call, rolling back resolution for every other venue in that batch.
  • This is not a theoretical blast radius: scripts/lib/emit-observations.mjs:96-101 is the real operational scrape-pipeline write path, and it chunks resolve calls at n=400 (chunk(venueIds, 400) → post({ resolve: c }, 'resolve')), which the enrich-venues edge function accepts up to its own 500-row cap (index.ts:65-66). The --sql migration-generation path (buildMigrationSql, lines 46-49) is unchunked — it puts every valid venue ID from a full scrape run into one ARRAY[...] call. Either path means one poisoned venue silently breaks fact resolution for up to hundreds of unrelated venues per real pipeline run, discoverable only by manually finding and deleting the bad row.

Fix shape (not implemented): either (a) REVOKE INSERT ON public.venue_corrections FROM authenticated and drop venue_corrections_insert_own, forcing all writes through the RPC (the RPC is already SECURITY DEFINER and does its own INSERT), or (b) add a BEFORE INSERT trigger that re-validates status/bounds server-side regardless of entry path. Separately, wrap each venue's resolution in resolve_venue_facts's loop with BEGIN ... EXCEPTION WHEN OTHERS THEN CONTINUE so one bad row can never abort a whole batch.

3. HIGH — Owner/host facts recorded via venue_source_observations(provider='manual') are not actually top-trust ​

Evidence:

  • Migration 00382's own description claims: "Three findings, each fixed SURGICALLY as a manual-provider fact (the top-trust source in resolve_venue_facts)" (00382:1-4), and it writes the owner's corrections directly as UPDATE public.venue_source_observations ... WHERE provider = 'manual' (00382:37-48) rather than through submit_venue_correction/the venue_corrections ledger.
  • But reading the actual resolver code: the "HUMAN CORRECTIONS WIN" override block (present in every version since 00355) reads exclusively from the venue_corrections table (SELECT proposed_value, cnt ... FROM public.venue_corrections WHERE ... field = 'telephone' AND status = 'applied', 00370:250-261 and siblings). It never reads venue_source_observations WHERE provider IN ('manual', 'host').
  • Instead, provider='manual' rows are aggregated through the exact same per-fact majority-vote/mode logic as every scraped provider (gov_kspo, kakao, naver, etc.) — e.g. court_count picks ORDER BY g.c DESC (most votes wins), telephone the same. A manual observation is one vote, with zero trust weighting.
  • This is independently corroborated by 00382's own finding #2: "매헌시민의숲 테니스장: its manual observation asserted has_indoor=false, which as top-trust data pinned the venue outdoor-only" — the only reason a single manual row could "pin" a value at all is that it was the sole voter for that fact (via the has_indoor OR-monotonic rule, a lone false observation resolves to false only when no other source has asserted true), not because it carries any special trust tier. If even one other (possibly stale/wrong) scraped source had asserted has_indoor=true for that venue, bool_or would have overridden the manual fact regardless of intent.
  • Consequence: any future re-scrape/re-enrich pass that adds 2 new agreeing (but incorrect) automated observations for court_count/telephone/capabilities on a venue whose owner-verified fact lives only in venue_source_observations(provider='manual') will silently outvote and overwrite it on the next resolve_venue_facts call — with no confidence penalty, no signal, no audit trail, and no code path distinguishing "owner said so" from "one scraper said so." The only way to actually get protected top-trust status is to route through submit_venue_correction (the venue_corrections ledger), which 00382 did not do.

Fix shape (not implemented): either extend the human-override block to also read venue_source_observations WHERE provider IN ('manual', 'host') at top trust, or change the operational convention (and 00382's own doc claim) to always route owner/curator corrections through submit_venue_correction so they land in the ledger that's actually protected.

4. HIGH — A wrongly-closed venue has no reopen path ​

Evidence:

  • submit_venue_correction's closed branch (00357:191-203, unchanged in shape since 00356) sets venues.closed_at = now() once 2 distinct submitted_by users agree (live rows only, per 00356's fix). There is no closure_reason set on this path (stays NULL).
  • A repo-wide grep for any un-close/reopen mechanism (grep -rln "closed_at = NULL\|closed_at=NULL\|reopen" supabase/migrations/*.sql supabase/functions) returns zero venue-related hits — every closed_at-setting statement in the venue migrations is one-directional (close only: 00358, 00371, 00372, 00373, 00374, 00380 — all merge/closure operations, never a reversal).
  • Once closed_at is non-null and closure_reason isn't 'temporary', the venue is permanently excluded from every directory read (venue-directory.supabase.ts:87, .or('closed_at.is.null,closure_reason.eq.temporary')) and from the idx_venues_active partial index (00283).
  • Combined with finding #2 (a raw-insert RLS bypass trivially satisfies "2 distinct submitted_by" with zero validation, and even without that bypass, 2 genuine colluding/sockpuppet accounts suffice — there is no real-identity verification behind submitted_by), a real, still-operating court can be permanently removed from discovery by 2 bad-faith or simply mistaken reports, with no in-app recourse for the operator, an admin, or even the original reporter to reverse it. The only recovery path is a manual service-role SQL statement, i.e. an engineer intervention per incident.

Fix shape (not implemented): add a reopen_venue/dispute RPC (owner-verified or admin-gated) that clears closed_at/closure_reason, mirroring the existing report_venue_data (00357) pattern for the inverse direction.

5. MEDIUM — Tennis-fee parser fix isn't wired into the live ingest pipeline ​

Evidence:

  • private.parse_tennis_fee (00348:23-74) was written specifically to fix a real, cited bug: gov fee blobs mixing every sport at a facility caused soccer prices to display for a tennis court (강남세곡체육공원, per the migration's own worked example).
  • grep -rln "parse_tennis_fee" supabase/migrations/*.sql supabase/functions returns only 00348_reparse_gov_tennis_fees.sql itself — the function is defined, called once in that migration's own backfill UPDATE/re-resolve statements (00348:77-99), and never referenced again anywhere else in the codebase (not in ingest_venues_batch, not in record_venue_observations, not in any ingest-venues/sources/*.ts extractor).
  • This means the fix was applied as a point-in-time data patch, not a durable ingest-time transform. Any future re-run of the gov ingest sources (std-data.ts/kspo.ts/eshare.ts) for an already-covered region, or a first-time ingest of a new region, writes the raw multi-sport fee blob straight into the fee_schedule observation unparsed — reintroducing the exact bug 00348 fixed, silently, for any venue touched by that future run.

Fix shape (not implemented): call private.parse_tennis_fee from inside record_venue_observations (or resolve_venue_facts's fee*schedule block) whenever a gov*%provider's payload carries an unparsednote-only fee entry, so the transform runs on every ingest, not just the one historical backfill.

6. MEDIUM — adm1_id/adm2_id region linkage has no systemic ingest-time guard ​

Evidence:

  • grep -n "adm1_id\|adm2_id" supabase/migrations/00334_ingest_venues_merge_safe_upsert.sql (the live ingest_venues_batch RPC that backs both the ingest edge function and the enrichment scripts) returns zero hits — the RPC never populates region linkage on write.
  • The only place these columns get filled is scripts/backfill-venue-adm.mjs, a standalone one-off script (also referenced defensively in enrich-tmap.mjs, triangulate-coords.mjs, repair-coords.mjs, triangulate-quality.mjs — all scripts, none of them triggers/RPCs).
  • Consequence, directly answering the audit brief's question: this is confirmed to be script-only, not a systemic guard. A newly-ingested venue (new region added, or a re-run that inserts previously-missed venues) sits with adm1_id/adm2_id = NULL until a human remembers to re-run the backfill script. The directory's default (no-region-filter) query correctly still shows these venues (ADM1_EMBED is a LEFT embed unless a region filter is active — verified in venue-directory.supabase.ts:75-81), but the moment a user filters by 시/도 (the inner-embed path), these venues silently vanish from that filtered view with no distinguishing UI signal — this is the backend root cause behind U6's independently-flagged "directory empty state can't distinguish 'no courts' from 'filtered out'" finding.

Fix shape (not implemented): fold adm-lookup into ingest_venues_batch itself (a lat/lng → administrative_divisions polygon/bbox lookup at write time), or at minimum chain the backfill script as a mandatory last step of the ingest pipeline's documented run order.

7. MEDIUM — Weather-warning severity escalation doesn't re-notify ​

Evidence:

  • signals_on_weather_warning_activated (current body, 00316) only proceeds past IF TG_OP = 'UPDATE' AND OLD.is_active = true THEN RETURN NEW; END IF; when a warning transitions from inactive→active. If a weather_warning_cache row stays continuously is_active = true across an UPDATE where only warning_level changes (e.g. a 호우주의보 escalating to 호우경보, a materially more urgent condition, or warning_type broadening), the function returns immediately and emits no new/updated signal.
  • Users who were already notified at the lower severity level have no mechanism to learn the warning escalated — they'd need to independently re-check the weather banner. The dedup key (session_weather_warning:{userId}:{warning_type}:{session_date}) is keyed by warning_type, not warning_level, so even removing the early-return wouldn't naturally re-fire without a level-comparison branch — the dedup key would need warning_level folded in, or an explicit escalation-detection clause (NEW.warning_level <> OLD.warning_level).

Fix shape (not implemented): add an escalation branch: IF TG_OP = 'UPDATE' AND OLD.is_active = true AND NEW.warning_level = OLD.warning_level THEN RETURN NEW; END IF; (only skip when nothing materially changed), and extend the dedup key with warning_level so an escalation is treated as a distinguishable event.

8. MEDIUM — No per-row exception isolation in enrichment RPC loops (same class as #2, different trigger) ​

Evidence:

  • scrape-naver-place.mjs:234-236 documents a REAL live incident from 2026-07-10 (the day before this audit): "a retired 00371 loser was scraped and its naver_place_id marker collided with its merge survivor's (unique index on venues.naver_place_id)" — confirmed independently by migration comments in 00378_naver_place_scrape_full.sql:12 ("8 place_ids were claimed by 2 venues each") and fixed reactively by 00380_placeid_proven_dedup.sql.
  • The CURRENT mitigation is a source-side filter (scrape-naver-place.mjs:236: .is('closed_at', null) when selecting venues to scrape) — this prevents the one specific already-diagnosed cause (re-scraping a merged/retired venue) but is not a structural fix: enrich_venues_batch's per-row FOR r IN ... LOOP UPDATE ... END LOOP (00370:395-431, unchanged in shape since 00336) has no BEGIN...EXCEPTION handling, identical to the resolve_venue_facts issue in finding #2. Any future naver_place_id/kakao_place_id UNIQUE collision from a different cause (a genuine new wrong-match, a race between two concurrent enrichment runs, a manual data-entry error) will still hard-abort the entire batch (write.ts chunks at 200 rows per RPC call) with zero partial progress and no per-row error reporting beyond one opaque Postgres error string bubbled into writeErrors.

Fix shape (not implemented): either wrap each row's UPDATE in enrich_venues_batch with per-row exception handling (log + skip, matching the pattern record_venue_observations already uses defensively at 00347:107-111 — IF NOT EXISTS ... THEN CONTINUE), or catch unique-violation specifically (WHEN unique_violation THEN ...) and either null out the conflicting identity field or skip that row.

9. LOW — fetch-weather's failure logs reference a field that doesn't exist ​

Evidence: fetch-weather/index.ts:989-990: failedGrids.push(grid.name); console.error(Grid ${grid.name} (${grid.nx},${grid.ny}) failed:, e); — but every ALL_GRIDS entry (fetch-weather/index.ts:28-95) is a plain { nx, ny } object with no name property. grid.name is always undefined, so every failure log line and every entry in the JSON response's failedGrids array reads "Grid undefined (61,126) failed" / "undefined" — the (nx,ny) pair is still present inline in the console log (so the underlying grid IS identifiable there), but the dedicated failedGrids array in the JSON response — the field an on-call engineer would actually scan first — is useless for triage.

10. LOW — Shared-secret comparisons aren't constant-time ​

Evidence: every ingest/cron gate in this block (ingest-venues:49, enrich-venues:27, fetch-weather:791 and the 3 other weather crons) uses plain !==/token !== expected string comparison rather than a constant-time comparison. For server-to-server shared secrets over HTTPS this is a low-severity theoretical timing side-channel (network jitter typically swamps the signal), noted for completeness per the audit's SECDEF/hygiene dimension rather than as an actionable priority.

11. INFO — Confirmed still true: enrich_venues_batch silently drops unnamed keys ​

Evidence: 00370:385-434's enrich_venues_batch explicitly enumerates every column it writes in its SET clause (telephone, naver_place_id, kakao_place_id, ..., capabilities, facility_kind, ...) and nothing else — any JSONB key in p_rows not named in that list is silently ignored. The function's own comment (00370:376-379) confirms this was already learned the hard way once: "rating_value / rating_count / review_count ... ride the marker route... Without this the RPC silently DROPS them — it only writes keys it names explicitly." This is a deliberate, documented architectural characteristic (not a regression) — confirmed current via direct read of the live function body, answering the audit brief's specific question definitively.

12. INFO (verified fix) — 00316's weather-warning enum bug is fully resolved ​

Evidence: see "What works well" above. The live function body correctly uses s.status IN ('open', 'locked', 'in_progress'); the previous 'scheduled'/'published' labels (which don't exist in the session_status enum) are gone from every code path. Direct read of the currently-active function definition, not reliance on the fix migration's self-report.

Adversarial probes run ​

  • Traced the FULL resolver lineage across all 6 redefinitions (00347→00355→00356→00357→00358→00370) side-by-side rather than reading only the latest, to catch behavior that changed silently between versions (this is how finding #3's manual-vs-ledger trust gap and finding #7's escalation gap were both found — neither is visible from reading only the newest migration).
  • Grepped every migration touching venue_corrections for RLS/GRANT changes to verify the 00339 policy was actually tightened somewhere downstream, as the correction RPC's own hardening history (00356/00357) would suggest — it was not (finding #2).
  • Cross-referenced two independently-documented LIVE incidents (00378/00380 naver_place_id collision, 00382 owner-QA corrections) against the CURRENT code rather than trusting the fix migrations' own claims, to check whether the root cause was closed or only the symptom — in both cases found the symptom was patched but the structural gap (no per-row exception isolation; no ledger-routing enforcement for manual observations) remains open (findings #3, #8).
  • Sized finding #2's blast radius against the REAL operational pipeline (scripts/lib/emit-observations.mjs's actual chunk size, 400) rather than asserting a hypothetical worst case, and additionally found the --sql migration-generation path is unchunked (larger blast radius than the edge-function path alone).
  • Compared ingest-venues's auth gate byte-for-byte against its sibling enrich-venues and all 4 weather-cron functions to establish that the fail-open pattern is a genuine outlier within this codebase's own established (and correctly fail-closed) house style, not an ambiguous judgment call.
  • Verified the AQI D+6 ceiling and 영동/영서 splitting claims from project memory against the actual grid-position arithmetic and dual-searchDate merge logic in fetch-weather/index.ts, rather than accepting the memory note at face value.
  • Checked for a reopen/dispute RPC for closed venues via repo-wide grep before concluding it doesn't exist (finding #4), rather than inferring absence from a single file read.

Canon violations ​

  • CLAUDE.md "PREVENTIVE — every fix includes prevention" — finding #5 (fee-parser fix has no ingest-time wiring, only a one-time backfill) and finding #8 (naver_place_id collision fixed at its one diagnosed cause, not structurally via exception isolation).
  • AGENTS.md / DATA canon "every SECURITY DEFINER function MUST set search_path + REVOKE + explicit GRANT" — verified fully compliant across this block (no violation; see "What works well").
  • Every new public table needs an explicit data-API GRANT — venue_corrections, venue_source_observations, venue_data_reports all correctly carry explicit GRANT statements (no drift found), but venue_corrections's grant is too wide relative to what the RLS policy actually constrains (finding #2) — a GRANT-hygiene pass in the mechanical sense, but a real authorization gap in the substantive sense.

Markdown remains the source of truth. Run yarn docs:check before handoff.