0013 — Dual retention for KPI telemetry: aggregate long, detail short

Renamed KPI → Metric on 2026-04-30 (PR-followup to PR #525). This ADR's body preserves the original wording from when KPI was the canonical term. Code, data, AWS resources, and active docs all use "metric" now; this file is kept verbatim as historical record. See docs/planning/kpi-to-metric-rename.md.

• Status: Accepted
• Date: 2026-04-23
• Deciders: Fede, Jose
• Related: ADR-0012

Context

Two kinds of telemetry feed the dashboard:

1. Daily aggregate: one row per property per day, KPI values at that moment. Used for year-over-year sparklines.
2. Per-event detail: e.g. one row per AppFolio sync run (every 15 min), browser-agent heartbeat. Used for operator debugging ("why did property X fail to sync last Tuesday?").

Naive approach — store everything forever — has a volume problem. At 1000 properties × 96 sync runs/day × 400 days = 38M rows of sync log alone. That's not a cost problem (DynamoDB handles it), it's a scan cost and query clarity problem.

Naive alternative — store nothing longer than a week — kills the year-over -year sparklines that are the whole point of the snapshot system.

Decision

Two tiers. Different TTLs per tier.

The rule: if a metric is a count, rate, or average — it belongs in the daily aggregate only. The detail log exists for the "which specific incident caused the spike" question, not for computing the spike.

The daily cron reads the detail logs (where applicable) and rolls them up into the aggregate. After that, the detail is expendable.

Worked example

Sync errors.

• SyncLog rows (detail): PK=SYNC_LOG#<propertyId>, SK=RUN#<isoTs>, attrs { status, errorCode, durationMs }. TTL 90 days.
• KpiSnapshot.appfolioSyncErrors (aggregate): scalar count per day. TTL 400 days.

The dashboard sparkline for "sync errors" reads only KpiSnapshot. The "show me the failures from last Tuesday" operator view reads SyncLog (as long as Tuesday was within 90 days). After 90 days, you still see "on 2026-01-15 there were 4 sync errors" (from KpiSnapshot) but you can no longer see which properties or which error codes.

That's the accepted trade.

Consequences

Easier

• Aggregate table stays small and queryable. 1000 properties × 400 days = ~400k rows, ever.
• Detail tables stay bounded. At 60–90 day TTL, high-frequency writers can't grow without limit.

Harder

• Two writes per event (one detail row + one counter bump in the daily aggregate). Accepted — the aggregate write is idempotent-upsert so re-running the cron doesn't double-count.
• Operator debugging after 90 days falls back to CloudWatch logs or nothing. Acceptable for post-mortems; not acceptable for live incidents.

Follow-up

• When adding a new high-frequency writer (anything > once per hour per property), check whether a detail log is actually needed. If the aggregate alone answers every operator question, skip the detail.
• Revisit if the 60–90 day detail window turns out to be too short for real debugging patterns. Cheap to extend; free to leave alone.

Alternatives considered

Store detail forever at matched 400-day TTL. Rejected. 38M rows of sync log per year per 1000 properties. Not a cost issue, a clarity issue — most queries would have to filter by recency anyway.

Store aggregate at matched 90-day TTL. Rejected. Kills the year-over -year sparkline, which is the whole product feature that justified the snapshot system.

Single tier, aggregate only, no detail log. Rejected. Operator debugging needs specifics ("which property? which error code?") that an aggregate scalar can't answer.

CloudWatch logs as the detail tier. Rejected for anything the dashboard needs to render. CloudWatch is fine for post-mortems but has no structured query, and we'd need a parallel story for JSON-backend local dev anyway.