WEBUSAGE-2a read-view schema: the stable query surface over usageEvents + usageDaily
Read-view schema over webusage.usageEvents + usageDaily. Today every read is an ad-hoc manual SQL pull against the raw append-only table (PLUTO-40's route ranking was run by hand), and raw rows are purged at 90d while usageDaily keeps the day-grain aggregate - so any read written against usageEvents silently changes answer at the 90-day boundary. Scope: name the stable read surface (views or a read schema) that callers are allowed to query, decide per-view whether it reads raw, rolled-up, or a union with an explicit cutover at the purge horizon, and state the day-boundary semantics in the view itself rather than in each caller - WEBUSAGE-4 already showed that a purge keyed on an instant and a rollup keyed on a day disagree by ~21h. No UI, no access control, no client code: just the contract the other three children build on.
Activity
-
parent=#2064
-
Pushed 9c271f5 (telemetry-ingest v0.4.0): views.sql + README 'Read surface' section. Three views in schema webusage, CREATE OR REPLACE only, applied live as webusage_rw. vUsageDaily = stable day-grain surface (rolled rows for completed UTC days UNION ALL today from raw, arms non-overlapping by construction because the rolled arm is filtered day < today with the same (ts AT TIME ZONE 'UTC')::date expression the rollup uses - WEBUSAGE-4's purge-by-instant vs rollup-by-day bug cannot recur through it). vUsageDailyApp = per (app, day) totals, and it exists so nobody writes SUM(uniqueUsers): that column is a count(DISTINCT userIdHash) at the full grain, so summing across routes double-counts anyone who visited two, measured live as mars 64 vs a true 44, pluto 16 vs 13, venus 15 vs 8. It recomputes the distinct count from raw while raw covers the day and reports NULL otherwise, uniqueUsersBasis naming which; NULL means not knowable from what is retained, never 0. vUsageRawCoverage publishes the purge horizon as data so a NULL is interpretable. VERIFICATION (read-only): 0 duplicate grain rows in vUsageDaily; hits reconcile 25661 = 25661 against an independent computation; coverage reads 2026-07-12 .. 2026-10-05, cutoff 2026-07-07, 25661 raw rows. Both arms forced red: a simulated 30-day horizon produced 165 day-rows unknowable-rawPurged with NULL uniqueUsers and 0 null hits (2026-07-12..2026-09-04) alongside 92 recomputed-fromRaw rows all populated. COVERAGE LIMIT, stated: the unknowable-rawPurged arm is exercised ONLY by that simulation - nothing has ever been purged, so the live view returns all 257 rows recomputed-fromRaw, and the first real purge is the ~2026-10-10 day-aligned 90d cut. No UI, no access control (WEBUSAGE-6), no client code.