Summary
Windowed analytics lose history when raw packets expire: REFRESH MATERIALIZED VIEW replaces each summary from the surviving packet/observation/message rows. A 3-day packet-retention setting therefore prevents the existing 7/30-day graphs from retaining a full 30 days of summaries. The hourly IATA view also has a hard-coded seven-day source window.
Proposed focused storage change
Retain compact aggregates for expired packet cohorts in the same transaction that deletes those packets. Existing hourly materialized views then combine live rows with archived aggregates, so the API does not count the same rows twice during refresh and retries do not duplicate archived counts. Expire aggregate history independently after 30 days. Keep the current population/census views separate.
Cover hourly IATA, payload, observer, talker, advertiser, observer-activity, Signal and Paths summaries. Preserve the existing region/count/grain/units/availability contracts and default 30-day API maximum. Archive no packet bodies, ciphertext or raw paths; avoid extra per-ingest writes. Document that already-expired source history cannot be reconstructed.
Validation will use real PostgreSQL to prove 3-day raw expiry with 30-day aggregate preservation, transactional failure/rollback and retry behavior, distinct counts across observer/IATA groups, late current observations, independent expiry, and bounded cleanup plans/storage. The Pi preview will use the user-approved 3-day packet / 30-day analytics policy. This extends the retention batching accepted in #162 and requires an append-only migration.
Summary
Windowed analytics lose history when raw packets expire: REFRESH MATERIALIZED VIEW replaces each summary from the surviving packet/observation/message rows. A 3-day packet-retention setting therefore prevents the existing 7/30-day graphs from retaining a full 30 days of summaries. The hourly IATA view also has a hard-coded seven-day source window.
Proposed focused storage change
Retain compact aggregates for expired packet cohorts in the same transaction that deletes those packets. Existing hourly materialized views then combine live rows with archived aggregates, so the API does not count the same rows twice during refresh and retries do not duplicate archived counts. Expire aggregate history independently after 30 days. Keep the current population/census views separate.
Cover hourly IATA, payload, observer, talker, advertiser, observer-activity, Signal and Paths summaries. Preserve the existing region/count/grain/units/availability contracts and default 30-day API maximum. Archive no packet bodies, ciphertext or raw paths; avoid extra per-ingest writes. Document that already-expired source history cannot be reconstructed.
Validation will use real PostgreSQL to prove 3-day raw expiry with 30-day aggregate preservation, transactional failure/rollback and retry behavior, distinct counts across observer/IATA groups, late current observations, independent expiry, and bounded cleanup plans/storage. The Pi preview will use the user-approved 3-day packet / 30-day analytics policy. This extends the retention batching accepted in #162 and requires an append-only migration.