# The FedEx rating 5xx conditions miss `status_code = '5XX'`

- **Found:** 2026-08-13, while writing lesson 0002.
- **Re-checked:** 2026-08-20. Still live. The conditions were rewritten in the
  meantime and the bug was carried through the rewrite.
- **Severity:** ongoing under-count in three enabled production conditions.
- **Owner:** CCAP.
- **Status:** not ticketed.

## Affected conditions

All four sit in policy 4525023, Carrier Capabilities (CCAP). Ids are given second
because they are not stable: these four were deleted and recreated between 13 and
20 August, and every id changed while the names carried over.

| Condition | id (2026-08-20) | id (2026-08-13) | Uses `status_code >= 500` |
|---|---|---|---|
| `[CCAP] FedEx REST Rating - 5xx errors (carrier upstream)` | 66242857 | 61106123 | yes |
| `[CCAP] FedEx REST Rating - 5xx burst (Shippo origin)` | 66242858 | 61061369 | yes |
| `[CCAP] FedEx REST Rating - 5xx errors (Shippo origin)` | 66242859 | 57969138 | yes |
| `[CCAP] FedEx REST Rating - 4xx errors` | 66242860 | 57968861 | no, uses 400 to 499 |

## The defect

`status_code` on `CARRIER_INTEGRATION_RESPONSE` is a mixed-type attribute. New
Relic reports it as both `string` and `numeric`:

```sql
SELECT keyset() FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE' AND environment = 'prod'
SINCE 30 minutes ago
-- returns status_code twice: {"type":"string"} and {"type":"numeric"}
```

Some rows carry a literal string sentinel `'5XX'` instead of a number, and a
numeric comparison matches none of them.

```sql
SELECT count(*) AS total,
       filter(count(*), WHERE status_code >= 500)    AS ge500,
       filter(count(*), WHERE status_code LIKE '5%') AS like5
FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE' AND environment = 'prod'
  AND status_code = '5XX'
SINCE 60 minutes ago
-- ge500: 0
```

## Scale of the sentinel

Twenty-four hours to 2026-08-20, all environments filtered to prod:

| Integration | `'5XX'` rows |
|---|---|
| ups | 33,519 |
| fedex | 4,629 |
| parcel_perform | 1,991 |
| dhl_ecommerce | 1,179 |
| royal_mail_sf | 1,021 |
| canada_post | 713 |
| globegistics | 637 |
| usps | 492 |
| twenty or so others | under 400 each |

Only `5XX` appears. There is no `4XX` sentinel in the top results, which is why
`[CCAP] FedEx REST Rating - 4xx errors` is unaffected.

UPS carries seven times FedEx's volume of the sentinel. No UPS condition uses the
numeric comparison today, so that exposure is unrealised rather than absent.

## Measured impact

```sql
SELECT count(*) AS all_5xx_string,
       filter(count(*), WHERE url LIKE '%apis.fedex.com/rate/v1/comprehensiverates/quotes%'
                          AND service = 'shipment'
                          AND caller NOT LIKE '%grpc_service%'
                          AND status != 'success'
                          AND test != true) AS inside_condition_filter
FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE' AND environment = 'prod'
  AND status_code = '5XX' AND integration = 'fedex'
SINCE 24 hours ago
-- all_5xx_string: 4620     inside_condition_filter: 1393
```

**1,393 FedEx rating failures a day sit inside all three conditions' filters and
are counted as zero.** All three alert on a percentage. Those events leave the
numerator and remain in the denominator, so the reported failure rate is pushed
down twice over.

## The fix

```sql
-- before
AND status_code >= 500

-- after
AND (status_code >= 500 OR status_code LIKE '5%')
```

`LIKE '5%'` may match numeric 500 to 599 on its own, because NRQL can coerce the
numeric value to a string for the comparison. Confirm that against live data
before simplifying to the single predicate.

## Sweep for the same pattern

```sql
SELECT count(*) FROM Log
WHERE event = 'CARRIER_INTEGRATION_RESPONSE' AND environment = 'prod'
  AND status_code IN ('5XX', '4XX', '3XX', '2XX')
FACET status_code, integration SINCE 1 day ago
```

```bash
grep -rn 'status_code >= ' ~/code/shippo-tf-services --include='*.tf'
```

For live conditions rather than Terraform, pull the policy and filter:

```
jq -r '.conditions[]|select(.enabled)
  |select(.nrql.query|test("status_code *>="))|.name' conditions.json
```

## What makes this worth writing down

The rewrite on 20 August was careful work. It added paired-condition
descriptions, runbook links, `{{threshold}}` substitution, and a documented
50-sample volume floor. It reproduced the comparison bug in all three 5xx
queries anyway, and it left two descriptions contradicting their own settings.
Reviewing the prose is not reviewing the config.