[feature] Web Vitals by device — "is my site slow on mobile"

web_vitals (#41) stores the metric, the path, the value and the time. It carries no device column, so the vitals report answers "is my site slow" but not "is my site slow on mobile" — which is the question people actually have, because it is usually the answer.

The route comment says as much today:

Adding a device breakdown later is additive (a column plus a GROUP BY) and is the obvious next step [...] It is out of scope here because a first cut that answers "is my site slow" correctly beats one that answers more, later.

This is that step.

Scope

• device_type on web_vitals, populated in the vitals branch of /collect from the same parseUserAgent the pageview path already uses, with the same lowercase desktop/mobile/tablet values as page_views.device_type.
• A per-device p75 in GET /api/sites/{siteId}/vitals, and an optional ?device= scope on the report and on /vitals-trends.
• A device row in the dashboard panel, showing all devices side by side rather than behind a selector — the comparison is the point.

Privacy

Device class is three buckets, already recorded per visitor on page_views for the same visit. It adds no linkage the database does not already hold, and it is not a device-persistent identifier — the thing #28 rules out. Nothing about the visitor hash, the rotation, or erasure changes.

The disclosure floor matters more here than in the site-wide report, not less: splitting by device divides the sample count, so more buckets fall under it. A p75 over one person's phone is that person's phone and network reported to the site owner as a device characteristic — exactly the case the floor exists for. Each bucket is suppressed independently, and shows "too few visits" rather than vanishing.

Existing rows

The column is nullable with no backfill, because we do not know the device for measurements taken before it existed and inventing one would be worse than admitting it. Those rows read as an unknown bucket, shown only when it actually has samples, and it ages out on its own.

Shipped in 1999154.

Built as a breakdown rather than a filter: the table now has a device column, but country and browser still do not, so honouring device=mobile from the filter bar would give a panel filtered along an axis nothing on screen identifies. Showing all three side by side is also the better answer — "slow on mobile" is a question asked against desktop, and a filter hides what is being compared to. ?device= still scopes the API report and the trend series; the breakdown itself is never scoped.

Two things turned up on the way:

• ./buddy migrate proposed dropping web_vitals. It diffs a stored model snapshot against app/Models, and the table was still in the snapshot from the WebVital model that briefly existed in #41 before being deleted. The snapshot is gitignored local state so this is not a repo change, but --force would have taken the table. The existing "there is no WebVital model" test is what stops it recurring.
• Migration 47's style note ("no semicolons in comments") is stricter than the runner. Measured: idempotentSql preserves the leading -- block verbatim and splits everything after it on ; with no comment handling, so a semicolon up top is harmless while one further down re-emits the rest of the sentence as SQL. A comment block after the last statement is unsafe too — the rewrite welds a ; onto it. Migration 48 states the real rule; a test now sweeps every migration for it.

Not fixed here: an iPad classifies as mobile, because the shared parser tests /mobile/ before /ipad/ and every iPad Safari UA contains Mobile/15E148. That is upstream in ts-analytics/src/utils/user-agent.ts and already true of page_views. Fixing it only in the vitals path would make two reports on one dashboard disagree about what a device is, so the tests pin that both paths classify identically rather than pinning the classification.

Verified: 30 new unit tests (474 total, tsc + lint clean), 27 checks against real Postgres, 16 against a live server, 19 against the dashboard rendered as a real owner, and 20 mutations each turning a test red.