Data automation

-- The file is a full snapshot, so what is missing from it no longer exists.-- An upsert alone keeps those rows alive forever.with bounds as (    select min(reported_at) lo, max(reported_at) hi from incoming), gone as (    select r.* from reports r, bounds b    where r.reported_at between b.lo and b.hi      and not exists (select 1 from incoming i where i.id = r.id))select count(*) as gone_rows,       (select count(*) from reports r, bounds b         where r.reported_at between b.lo and b.hi) as in_rangefrom gone;-- Guard: more than 3% missing means a bad file, not a clean-up. Stop.-- Otherwise move the rows to reports_archive with their full copy. Never drop them.

-- A later file arrives with blank columns. Blank must never erase work already done.create trigger keep_enrichment before update on reportsfor each row execute function keep_non_blank('reason', 'station', 'feeder');-- A database guard, not an app guard: there are several write paths today,-- and there will be one more tomorrow.