run_594cc500bc261d78

input

{
  "request": "Triage Jira ticket MZCLD-3772: Inventory v2 Fix service to app_code mapping ",
  "references": [
    "https://mozilla-hub.atlassian.net/browse/MZCLD-3772"
  ],
  "sourceContext": {
    "issue": "MZCLD-3772"
  }
}

summary

MZCLD-3772 is a Backlog engineering task to remove the hardcoded, manually-maintained service_id→app_code mapping (billingetl/src/fastly-api-v2/app_codes.py) that feeds billing.fastly_usage_v1.sql, billing.unblended_cost_estimated_v2.sql, and bandwidth_cost_by_app.sql. Newly provisioned Fastly services currently show up as unattributed (NULL app_code) in the LEFT JOIN reports or are silently dropped entirely from the INNER JOIN report. James Francis's comment provides a concrete heuristic: for Terraform-managed services, the service name follows app_code-realm-env_code (or app_code-differentiator-realm-env_code), so the app_code can be inferred as the substring before the first '-' when there are 2+ hyphen-separated segments; results should be written to a BQ services table and then used to update service_app_codes. The ticket also calls for migrating app_codes.py to a BQ table as the source of truth, and notes a separate ticket will handle alerting on unmapped services. This is a scoped, actionable backlog item with a reasonable implementation approach already sketched in comments — it just needs sizing/prioritization and clarification on fallback behavior for non-Terraform-named services.

findings

• Core problem: app_codes.py is a hardcoded, manually maintained mapping with no auto-update on new Fastly service provisioning, causing unattributed or missing billing data.
• Downstream impact differs by join type: LEFT JOIN queries surface NULL app_code (visible but unattributed), while the INNER JOIN table function silently drops the service with no error or null row.
• Ticket scope includes two asks: (1) implement a best-effort auto-guess of app_code (falling back to 'none') for unmapped services, and (2) migrate the source of truth from an inline Python dict to a BigQuery table, retiring app_codes.py.
• A concrete heuristic for guessing app_code already exists from a subject-matter expert, based on Terraform-managed service naming conventions.
• The proposed workflow already pushes the full services table (from Fastly's get_services() API) to BQ; the plan is to derive app_code during that same processing step and update service_app_codes accordingly.
• Alerting for unmapped/guessed services is explicitly out of scope here and tracked separately, so this ticket should not be blocked waiting on that work.

proposed actions

tool calls

[
  {
    "capability": "jira.issue.read",
    "args": {
      "key": "MZCLD-3772"
    },
    "result": {
      "key": "MZCLD-3772",
      "summary": "Inventory v2 Fix service to app_code mapping ",
      "status": "Backlog",
      "description_text": "Problem:  service_app_codes (billingetl/src/fastly-api-v2/app_codes.py) is a hardcoded  service_id -> app_code mapping, manually maintained and reloaded into  BigQuery on each daily run. When Fastly provisions a new service, it has no  entry in this mapping until someone manually adds one. Impact: billing.fastly_usage_v1.sql and billing.unblended_cost_estimated_v2.sql  LEFT JOIN to service_app_codes, so the new service's usage/cost shows up  with app_code = NULL (unattributed). bandwidth_cost_by_app.sql (table function) INNER JOINs to  service_app_codes, so the new service's bandwidth/cost is silently  excluded from that report entirely \u2014 no error, no null row, just missing. a separate ticket is being created for alerting Ask:  try to guess the app_code for the service (see below) else update the service_app_code map table with a \u201cnone\u201d value. Also move app_code from an inline python map to a BQ table as the source of truth removing app_codes.py.",
      "comment_count": 1,
      "comments": [
        {
          "author": "James Francis",
          "body": "We get the services as an API call - that returns what we see in get_services() function. The format of that includes an id and a name. The name is what we\u2019re interested in: | It is arbitrary, BUT a service that uses terraform (the comment in line 29 will be \u201cManaged by Terraform\u201d)will have this in the format: app_code-realm-env_code OR as we see below app_code-<differentiator>-realm-env_code For example app_code odin has the names: odin-nonprod-dev  odin-nonprod-stage  odin-prod-prod  odin-scan-prod-prod  odin-scan-nonprod-dev  odin-scan-nonprod-stage We want to use just the string prior to the first \u2018-' character as the app_code. There are exceptions of course, but this will give us some baseline for an otherwise arbitrary string. I think the match should be 2 or more - characters. After we do our processing, at    , we push the entire table up to BQ into the services table. What we\u2019d like to be able to do is update the table service_app_codes with that value."
        }
      ]
    }
  }
]

log