WhatsApp deduction-history usage API (BIF-8884 / M2 · BIF-8885 / M3)

The read model for Deduction V2 usage. Returns WhatsApp usage grouped by day × WhatsApp Business × category, reading the pre-aggregated daily rollup wa_reconciliation_batches written by EOD settlement (eod-settlement-settle-daily.md) — not an aggregation over the 200M-row per-message wa_conversation_logs. Two surfaces share one query:

• M2 (BIF-8884) — GET usage API: paginated JSON, DED-S07 backend, consumed by the hub client usage page (M4).
• M3 (BIF-8885) — download API: the same rows streamed as CSV, DED-S08 backend, consumed by the moderator-be modpanel (M5).

Single source of query truth

Both surfaces call Repositories::Billings::WaDeductionHistoryQuery (hub_core/app/core/domains/repositories/billings/wa_deduction_history_query.rb), a mixin holding the grouping/sum query (wa_deduction_history_rows), the states sanitizer (sanitize_wa_deduction_states), and the row builder (build_wa_deduction_history_row). This is what guarantees DED-S08/AC-3 — the CSV rows match the GET rows exactly for the same org/range — and is backed by an explicit parity spec in the download repo spec.

Models::Billing::WaReconciliationBatch
  .where(organization_id:, meta_date: start..end, state: @states)
  .group(:meta_date, :waba_id, :category)
  .order(meta_date: :desc, waba_id: :asc, category: :asc)
  .pluck(:meta_date, :waba_id, :category,
         Arel.sql('SUM(meta_volume)'), Arel.sql('SUM(settled_charged_amount)'))

Presented row → { usage_date, waba_id, category, message_count (SUM meta_volume), deducted_balance (SUM settled_charged_amount) }, ordered by usage day descending.

Request paths

M2 — GET usage (paginated JSON)

GET /api/core/v1/reports/billing/deduction_history

hub_service/app/services/api/core/v1/reports/resources/billing.rb:193 — inside resource 'reports' → resource 'billing'. oauth2 :admin, :owner, :supervisor, :agent, :member, :bot (mirrors the actual GET /mcc_logs route scope — no :modpanel; the client app is the consumer). The route injects organization_id/actor_id from the authenticated principal, calls Interactors::Billings::UserGetsWaDeductionHistory, and presents { data: response, meta: { pagination } } — the same success idiom as /mcc_logs.

M3 — download (CSV)

GET /api/core/v1/reports/billing/download/deduction_history

billing.rb top-level resource 'download', oauth2 :modpanel (the consumer is moderator-be/M5; the client uses M2's GET). Accepts organization_id or moderator_account_id (+ start_date, end_date, timezone, waba_id, states), calls Interactors::Billings::UserDownloadsWaDeductionHistory, and streams a CSV with columns WhatsApp Business, Category, Usage Day, Message Count, Deducted Balance. No batches → a header-only CSV (never an error). The download repo returns all rows (no pagination); the GET repo paginates the same array.

Query params (both surfaces)

Param	Type	Notes
start_date, end_date	Integer (epoch)	usage-day window; converted to a meta_date range in timezone (default +07:00)
timezone	String	e.g. Asia/Jakarta; shifts the day boundaries used to bucket meta_date
waba_id	String	optional filter to one WhatsApp Business
states	Array[String]	queryable; subset of %w[pending settled]. Omitted → both (full usage picture). values: on the route rejects anything else at the edge; the shared module re-sanitizes
offset pagination	—	M2 GET only (use :offset_pagination); the M3 download returns all rows

Layers

Interactors

• Interactors::Billings::UserGetsWaDeductionHistory (M2) — contract mirrors UserGetsMccLog (pagination_schema + organization_id/actor_id, optional date range/timezone/waba_id/states), with the same check_range_date guard.
• Interactors::Billings::UserDownloadsWaDeductionHistory (M3) — contract mirrors UserDownloadsMccLogs (organization_id or moderator_account_id, actor_id, optional date range/timezone/waba_id/states).

Both are thin: validate params, delegate to their repository.

Repositories

Both include the shared WaDeductionHistoryQuery and call wa_deduction_history_rows, which wraps the grouped query in switch_replica_billing_db (reads the chat_billing replica). One row per (usage_date, waba_id, category), collapsing across phone_recipient:

Response field	Source
usage_date	meta_date (the true usage day)
waba_id	waba_id
category	category
message_count	SUM(meta_volume)
deducted_balance	SUM(settled_charged_amount) — the client-charged figure added by M0 (BIF-8959)

• Gets::WaDeductionHistory (M2, extends AbstractRepository) paginates the array and returns { response: [...], pagination: { offset, total, limit } } (Hashie::Mash), matching Gets::MccLog.
• Downloads::WaDeductionHistory (M3, extends Reports::AbstractReporting, Dry::Monads::Do.for(:call)) returns the full array via Success(rows) — Success([]) when empty, so the route emits a header-only CSV rather than an error.

State filtering — the trust boundary

sanitize_wa_deduction_states accepts an array (grape Array[String]) or a comma-separated string, keeps only values in ALLOWED_STATES = %w[pending settled], and falls back to DEFAULT_STATES (both) when nothing valid remains. state is therefore always an IN (...) over a fixed whitelist — a caller can never widen it to an unknown/future state.

Semantics: pending == partially settled (late deliveries still outstanding — settled_count < meta_volume), settled == fully reconciled. A pending row's deducted_balance reflects settlement progress so far, so late deliveries surface naturally as the figure grows on subsequent days. Default shows both; the caller can pass states=settled to show only fully-reconciled days.

Gating

Both repos call wa_hold_settlement_enabled?(organization_package) (from Repositories::Billings::Helpers) — company-scoped Services::Billing::FeatureFlag, the same predicate the settlement engine uses. A V2-off org gets Failure('WhatsApp hold settlement is not enabled') (surfaced as 422). A V2-on org with no batches in range → Success with an empty result (M2: empty response for the FE's DED-S07/ERR-1 empty state; M3: header-only CSV).

Why the rollup, not the log

wa_reconciliation_batches already holds one row per (organization_id, waba_id, phone_recipient, category, meta_date) with meta_volume and settled_charged_amount, indexed on (organization_id, meta_date) (idx_wrb_org_date). Reading it is orders of magnitude lighter than a GROUP BY over per-message logs, and meta_date is the correct usage day — a settled log row's created_at is the settlement-run day (~T+1), which would bucket usage into the wrong day.

Tests

• gets/wa_deduction_history_spec.rb (M2 repo): grouping grain + sum-across-recipient, meta_date ordering, empty state, wrong-org isolation, gate-off failure, waba_id filter, pagination, and the states filter (default both / explicit settled / unknown-value fallback / comma-string).
• downloads/wa_deduction_history_spec.rb (M3 repo): grouping grain, empty → Success([]), org-or-moderator lookup, gate-off failure, states filter, and a parity test asserting the download rows equal the M2 GET rows for the same org/range.
• user_gets_wa_deduction_history_spec.rb / user_downloads_wa_deduction_history_spec.rb (interactors): contract failures, date-range guard (GET), states passthrough, repository success.
• billing_spec.rb (hub_service request specs): M2 GET — 200 admin scope, 401 unauth, modpanel disallowed; M3 download — 200 CSV (header + rows), header-only CSV when empty, 401 unauth, admin scope disallowed (modpanel-only).

Depends on

M0 (BIF-8959, settled_charged_amount on the rollup) and hub_core T5 settlement (writes the rollup, BIF-8744, shipped). M2 consumed by M4 (hub client usage page); M3 consumed by M5 (moderator-be modpanel download). M2 and M3 share the same rollup query (WaDeductionHistoryQuery), so their rows can never diverge.