Schema Update — May 2026 (55 Net-New Columns)

Overview

This page documents 55 net-new columns added to the defect_report table. These columns were present in the schema but had not been documented in the data dictionary. Definitions have been added to the Vendor data dictionary in Airtable as of May 2026.

Column List

Querying Financial Impact by Issue Type Group

For certain chargeback types, Amazon creates two rows for the same event — a Chargeback row (CB) and a Record row (REC). Summing financial_charge across all rows without accounting for this will double-count charges.

CB row = the billing record (carries the invoice number and finalization status)

REC row = the operational record (what Amazon's compliance system generated when the defect was identified)

Which row holds the dollar amount depends on the issue type — that's what determines the query approach for each group below.

Group 1 — Single source, use as-is

Issue types: Everything except Receiving Accuracy and PO on-time accuracy. No dedup needed — take financial_charge directly.

One wrinkle for In Full Delivery: Amazon may assign the same VAMP to two rows on the same PO+ASIN — one CHARGED and one WAIVED. These are not duplicates; they are separate line items. Keep both and sum them, or filter by status depending on the use case.

-- Group 1: All issue types except those requiring dedup
SELECT
    issue_type,
    status,
    COUNT(*)              AS chargeback_count,
    SUM(financial_charge) AS total_charge
FROM defect_report
WHERE _partneruuid = '<uuid>'
  AND issue_type NOT IN ('Receiving Accuracy', 'PO on-time accuracy')
  AND financial_charge > 0
GROUP BY issue_type, status
ORDER BY issue_type, total_charge DESC;

Group 2 — Paired via VAMP, CB carries the charge

Issue types: Receiving Accuracy, PO on-time accuracy

VAMP links a CB row to a REC row (or the CB row is a singleton with no REC counterpart). The financial truth lives on the CB side — financial_charge is non-zero there, and charge_invoice_# and finalization_status are on CB as well. Deduplicate by keeping one row per VAMP, preferring CB.

Note: Filtering on finalization_status = 'FINALIZED' cleanly isolates CB rows and excludes REC rows for these issue types, without relying on issue_id ranges.

Group 3 — Paired via VAMP, REC carries the charge

Issue types: shipment (lowercase)

CB rows exist but carry financial_charge = 0 — they are ghost records (FINALIZED, no invoice). The actual dollar amount lives on the REC row. CB-only singletons are also $0 and can be ignored entirely. Always pull from REC.

-- Group 3: REC rows only (excludes FINALIZED CB ghost rows)
SELECT
    issue_type,
    infraction_subtype_code,
    COUNT(*)                AS chargeback_count,
    SUM(financial_charge)   AS total_charge
FROM defect_report
WHERE _partneruuid = '<uuid>'
  AND issue_type = 'shipment'
  AND finalization_status != 'FINALIZED'
GROUP BY issue_type, infraction_subtype_code
ORDER BY total_charge DESC;

Combined query across all groups

Use this when you want a single deduplicated view of total financial impact across all issue types.

Important: The casing of issue_type is significant. 'Shipment' (capital S, Group 1) and 'shipment' (lowercase, Group 3) are different issue types with different dedup behavior. Run SELECT DISTINCT issue_type FROM defect_report to confirm exact values in your data before using these queries.