API usage billing not operating correctly

[zachaysan]

Currently some customers are not being billed for API usage overages compared to their actual usage.

Part of this seems tied up with the current issue where current API usage notifications are missing alerts at lower thresholds, see #5069

Some users are being billed at repeated thresholds at their current maximum, for example see this organisation in Metabase:

select *
from organisations_organisationapiusagenotification
where organisation_id = 7201
order by notified_at asc;

The code involved is likely at issue. See the task charge_for_api_call_count_overages in organisations/tasks.py. The code roughly does the following:

It processes API overage charges for monthly subscribers nearing the end of their billing cycle. It first identifies organizations that have been flagged for exceeding 100% of their API usage in the past 30 days and filters for those whose billing terms are about to close (roughly a 25–35 day window). For each eligible organization with the proper feature flag enabled, it calculates the current API usage and checks whether it exceeds twice the allowed limit—if it does, and after accounting for any previous overage adjustments, it determines the number of 100,000-call increments by which the usage has surpassed the limit. Depending on whether the organization is on a Scale Up or Start Up plan, it applies the appropriate overage charge and logs a billing record to avoid duplicate charges, while also handling any errors that might occur during the billing process.

Looking into the billing failure, it seems like both startup and scaleup plans are involved in the API usage notifications:

select plan, count(*)
from organisations_organisationapiusagenotification ooaun
join organisations_subscription os on os.organisation_id = ooaun.organisation_id
group by plan;

But the problem seems like not enough of the organisations are being billed properly. For example look at the following SQL query:

SELECT
  "public"."organisations_organisationapibilling"."id" AS "id",
  "public"."organisations_organisationapibilling"."api_overage" AS "api_overage",
  "public"."organisations_organisationapibilling"."immediate_invoice" AS "immediate_invoice",
  "public"."organisations_organisationapibilling"."billed_at" AS "billed_at",
  "public"."organisations_organisationapibilling"."created_at" AS "created_at",
  "public"."organisations_organisationapibilling"."updated_at" AS "updated_at",
  "public"."organisations_organisationapibilling"."organisation_id" AS "organisation_id"
FROM
  "public"."organisations_organisationapibilling"

Only 34 ever existing records are present, with many records deleted from the table.