Backfill Scripts

Standalone TypeScript scripts that repair historical store, zone, and customer-state data outside the worker.

The backfill-scripts/ directory holds one-off maintenance scripts that run independently of the worker. They exist to repair historical data that the worker missed, usually because of a bug or an edge case that has since been fixed. Each is a plain TypeScript file run with tsx.

How they run

Backfill scripts read a local .env file (they do not use Cloudflare secrets) and share the worker's helpers, such as getStateFromZipCode:

SUPABASE_URL=...
SUPABASE_SERVICE_ROLE_KEY=...   # elevated service-role key
ZONE_CALC_API_KEY=...

Every script honors a DRY_RUN flag. Always dry-run first to preview the changes, then run live:

# Preview (makes no changes)
DRY_RUN=true  npx tsx --env-file=.env backfill-scripts/<path-to-script>.ts

# Apply
DRY_RUN=false npx tsx --env-file=.env backfill-scripts/<path-to-script>.ts

All scripts are idempotent, process rows in batches to avoid timeouts, and print a statistics summary at the end.

The scripts

Scripts are grouped by what they repair and organized into dated folders by when they were run.

Customer state backfill

customer-state-backfill/2025-10-08/ populates blank State values on End Customer (Amazon buyer) rows by deriving the state from the ZIP code (the same logic the worker uses on new customers). This clears the "Unknown" states that otherwise appear on the zone-analysis dashboard.

backfill-customer-states.ts: fills NULL/empty State where a Zip_Code exists, in batches of 100.
backfill-missing-order-customers.ts links orders to customers where the relationship is missing.

Store & zone backfill (October 2, 2025)

shipments-orders-zone-store-backfill/2025-10-07/ is a one-time repair of 120 shipments from October 2, 2025 (brand = 1, Vitamin Well LLC) that were missing store and zone data. Run with npm run backfill:oct2.

Why this backfill existed

A worker bug skipped zone data whenever a shipment was already linked to an order. The fix, an independent zone-write block, is described in Webhook Processing. This script repaired the rows written before that fix; the folder's EXECUTION_SUMMARY.md records the results (120/120 stores and zones, zero errors).

Zone backfill (monthly, 2025)

zone-backfill/2025-10-10/ is a set of per-month scripts (backfill-missing-zones-<month>-2025.ts for January through September) that find shipments with a NULL zone_calculation_status but valid ZIP codes, then calculate and populate the missing zone data. These cover a period when the zone calculation system was changed and some shipments were left unstamped. A helper shell script, complete-warehouse-updates.sh, was used while developing the later scripts to sync warehouse-fetching logic across months.

Writing a new backfill

The repository's convention (from shipments-orders-zone-store-backfill/README.md) is:

Create a dated folder: YYYY-MM-DD (the execution date).
Add the script and a README.md describing purpose and usage.
Dry-run and save the log.
Run live and save the log.
Write an EXECUTION_SUMMARY.md with the results.
Update the folder's README with the new entry.

Every script should include dry-run mode, idempotent operations, error handling, progress tracking, and a statistics summary.