Timezone handling is correct in report date bucketing

SQL date bucketing with DATE(occurred_at) uses PostgreSQL's session timezone — typically UTC — without advertising that fact to users. A campaign sent at 11pm EST fires tracking events that land on the following UTC day, making the performance report look like the campaign ran a day later than it did. For teams optimizing by day-of-week or comparing week-over-week performance, these bucketing artifacts corrupt trend analysis. The data-integrity taxon names the failure: the data is accurate at the row level but the aggregation step introduces systematic misclassification that is invisible without timezone awareness.

Use an explicit AT TIME ZONE clause in every date-bucketing query. Never rely on the session timezone default:

-- Parameterized with account timezone
SELECT
  (occurred_at AT TIME ZONE $2)::DATE AS date,
  COUNT(*) AS event_count
FROM email_events
WHERE campaign_id = $1
GROUP BY 1
ORDER BY 1;

In your ORM layer:

// src/lib/reporting/queries.ts
const rows = await db.$queryRaw<{ date: string; count: number }[]>`
  SELECT
    (occurred_at AT TIME ZONE ${accountTimezone})::DATE AS date,
    COUNT(*) AS count
  FROM email_events
  WHERE campaign_id = ${campaignId}
  GROUP BY 1
  ORDER BY 1
`

Store a per-account reporting_timezone field (e.g., 'America/New_York') and pass it to all reporting queries. Document in the UI which timezone reports are displayed in.