Add seat link series and coverage for the billable usage export

Vijay Hawoldarrequested to merge
18552-export-coverage into master

What does this MR do and why?

Adds the two period-scoped building blocks of the air-gapped usage export that do not depend on how the period is resolved:

  • HistoricalData.daily_maximums_on_utc_dates(range) - one row per UTC calendar day with that day's MAX(active_user_count), for an inclusive Date range.
  • Utilization::BillableUsage::Exports::SeatLinkSeries - the seat link block's daily series, [{ date: '2026-08-01', billable_user_count: 412 }, ...].
  • Utilization::BillableUsage::Exports::Coverage - the coverage block: for seat link, expected_days, days_with_data, complete and no_data_dates; for billable events, days_with_data only, counted from the dates of the export's billable events records.

Part of the backend for &23205; the payload assembly, period resolution and admin endpoint follow in a later MR. Nothing here is reachable from a request yet.

Why bucket by UTC date explicitly

billable_usage_daily_aggregates.usage_date is a date written from Time.zone.parse(...).utc.to_date. historical_data.recorded_at is a timestamptz. For the two datasets to agree on which day a row belongs to, the seat link side casts with AT TIME ZONE 'UTC', so bucketing does not depend on the Postgres session zone. Rails sets that session zone to UTC unless database.yml overrides it, so the cast guards a configuration rather than something a default instance hits. The instance's time_zone does affect Ruby, so the range bounds are built with Date#to_time(:utc); the Asia/Tokyo spec pins that.

The existing HistoricalData.during is a timestamp range, so a Date..Date argument stops at midnight on the last day and drops it. The new scope takes an inclusive date range and converts it to a half-open UTC time range.

MAX per day matches License#historical_max, which the networked seat link already uses; nothing guarantees one historical_data row per day.

Why coverage exists

The block states only what each dataset can prove.

Seat link is written by a daily cron whether or not anything was used, so a day missing from the series means nothing was recorded, most likely because the instance was down. Only the instance can say which days those were, so the block lists them. No-data days travel with the export rather than blocking it.

Billable usage cannot make the same claim. A usage row exists only once an event happens, so a day without one is usually just a day without usage, and the instance cannot tell that apart from a day capture was not running. The block therefore reports only how many days had usage. What the fields mean belongs in the export contract (#18558) and its documentation, not in a note inside the file.

"coverage": {
  "period_days": 91,
  "seat_link":       { "included": true, "expected_days": 91, "days_with_data": 91, "complete": true,  "no_data_dates": [] },
  "billable_events": { "included": true, "days_with_data": 88 }
}

Coverage takes the SeatLinkSeries and the dates of the exported billable events records, so the caller computes each once and the block describes exactly what the export holds: coverage.seat_link.days_with_data equals seat_link.records.size by construction, and billable events days are counted from records the payload already carries rather than by a query of their own.

Database

No migration. One new read query, bounded by the export period.

HistoricalData.daily_maximums_on_utc_dates(range)

For a full year, since the export covers the whole licence term:

SELECT (recorded_at AT TIME ZONE 'UTC')::date AS recorded_on, MAX(active_user_count) AS active_user_count FROM "historical_data" WHERE "historical_data"."recorded_at" >= '2025-10-01 00:00:00' AND "historical_data"."recorded_at" < '2026-10-01 00:00:00' GROUP BY (recorded_at AT TIME ZONE 'UTC')::date ORDER BY (recorded_at AT TIME ZONE 'UTC')::date

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59326/commands/164504

Against GitLab.com's own historical_data, with no seeding: HistoricalDataWorker writes one row a day, so the year holds 364 rows. They are read by index range scan and sorted and grouped in memory, in 3.45 ms with half the buffers read from disk. A multi-year term grows by about 365 rows a year.

GroupAggregate  (cost=54.36..61.81 rows=331 width=8) (actual time=3.283..3.371 rows=364 loops=1)
  Group Key: (((historical_data.recorded_at AT TIME ZONE 'UTC'::text))::date)
  Buffers: shared hit=5 read=5
  I/O Timings: read=3.111 write=0.000
  ->  Sort  (cost=54.36..55.19 rows=331 width=8) (actual time=3.277..3.294 rows=364 loops=1)
        Sort Key: (((historical_data.recorded_at AT TIME ZONE 'UTC'::text))::date)
        Sort Method: quicksort  Memory: 33kB
        Buffers: shared hit=5 read=5
        I/O Timings: read=3.111 write=0.000
        ->  Index Scan using index_historical_data_on_recorded_at on public.historical_data  (cost=0.28..40.51 rows=331 width=8) (actual time=1.818..3.219 rows=364 loops=1)
              Index Cond: ((historical_data.recorded_at >= '2025-10-01 00:00:00+00'::timestamp with time zone) AND (historical_data.recorded_at < '2026-10-01 00:00:00+00'::timestamp with time zone))
              Buffers: shared hit=2 read=5
              I/O Timings: read=3.111 write=0.000

References

How to set up and validate locally

# Two historical rows on one UTC day, the later one larger, plus one on the next day
HistoricalData.create!(recorded_at: Time.utc(2026, 8, 30, 12), active_user_count: 400)
HistoricalData.create!(recorded_at: Time.utc(2026, 8, 30, 23, 30), active_user_count: 410)
HistoricalData.create!(recorded_at: Time.utc(2026, 8, 31, 12), active_user_count: 412)

series = Utilization::BillableUsage::Exports::SeatLinkSeries.new(period_start: Date.new(2026, 8, 29), period_end: Date.new(2026, 8, 31))
series.records
# => [{ date: "2026-08-30", billable_user_count: 410 }, { date: "2026-08-31", billable_user_count: 412 }]

Utilization::BillableUsage::Exports::Coverage.new(seat_link_series: series, billable_events_dates: [Date.new(2026, 8, 30)]).to_h
# => seat_link: days_with_data 2, complete false, no_data_dates ["2026-08-29"]; billable_events: days_with_data 1

Then repeat inside Time.use_zone('Asia/Tokyo') { ... }. The result is identical: the range bounds are built with Date#to_time(:utc), and the Postgres session stays in UTC.

bundle exec rspec ee/spec/models/historical_data_spec.rb \
  ee/spec/models/utilization/billable_usage/exports/seat_link_series_spec.rb \
  ee/spec/models/utilization/billable_usage/exports/coverage_spec.rb

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Vijay Hawoldar

Merge request reports