Batch consumer lookups in EnrichmentService to eliminate N+1 find_or_create queries

Problem

Billing::Usage::EnrichmentService calls load_consumer per event inside process_saas_events (line 112) and process_sm_events (line 155). Each call delegates to Consumers.find_or_create, which runs Consumer.find_or_create_by! inside an ActiveRecord::Base.transaction:

EnrichmentService#process_saas_events / process_sm_events
  → load_consumer(source, event)  ← called per event
    → Consumers.find_or_create(entity_id:, source:, ...)
      → FindOrCreateWithSubscriptionService#execute
        → source.eligible_usage_billing_base_charges(date)  ← re-filters charges
        → ActiveRecord::Base.transaction { Consumer.find_or_create_by!(...) }

This results in one SELECT consumers.* query per event, even when many events share the same consumer (same entity_id + source combination).

Additionally, load_eligible_base_charge! calls source.eligible_usage_billing_base_charges(effective_date) which re-iterates rate plan charges and calls effective_on? per charge — duplicating work already done in the preload phase.

Evidence from production span

  • 47 SELECT consumers.* queries in a single job execution
  • 330 ms total duration
  • All queries are identical-pattern single-row lookups: SELECT "consumers".* FROM "consumers" WHERE "consumers"."entity_id" = $1 AND ...
  • Zero INSERTs observed — all consumers already existed, meaning all 47 queries were pure lookups that could have been batched

Proposed solution

  1. Batch prefetch existing consumers: After preload_event_mapping resolves sources, collect all unique (entity_id, source) pairs and batch-load existing consumers in a single query. Store them in a lookup hash.

  2. Only call find_or_create for missing consumers: During the per-event loop, check the prefetched hash first. Only fall through to find_or_create for genuinely new consumers.

  3. Cache eligible_usage_billing_base_charges result per source: Since multiple events share the same source, the base charge lookup should be computed once per source, not per event.

Relevant files

  • app/services/billing/usage/enrichment_service.rb:38-59 — load_consumer called per event
  • app/services/billing/usage/consumers.rb:6-12 — delegates to find_or_create services
  • app/services/billing/usage/consumers/base_find_or_create_service.rb:83-109 — find_or_create_consumer! in transaction
  • app/services/billing/usage/consumers/find_or_create_with_subscription_service.rb:17-21 — load_eligible_base_charge! re-filters charges per event
  • app/models/zuora/local/subscription.rb:193 — eligible_usage_billing_base_charges