Skip to content

Runbook: historical import (Phase 7)

This brings the old workbook (June 2023 → July 2026) into the Drive Tracker once. It runs on your PC, never on Cloudflare, and never talks to ConnectWise or SharePoint. It reads Excel files and writes reports plus one SQL file, which you then apply with Wrangler.

Design and rules: BUILD-PLAN.md §12 and Module 13.

Customer data. Everything under import\ is git-ignored. Never commit it, and delete it when you're done (step 12).

What you need

  • The repo on your PC with npm ci done.
  • Phase 1 live, with the ConnectWise customer sync run at least once in production.
  • A clean (unencrypted) copy of Customer Drive Destruction worksheet.xlsx (§12.1): Excel → Home → Sensitivity → a label without encryption (or File → Info → Protect Workbook → Restrict Access → Unrestricted Access) → File → Save As a new .xlsx. The dry run tells you if the copy is still encrypted.

Steps (PowerShell, from the repo folder)

1. Copy the inputs into the git-ignored folder:

New-Item -ItemType Directory -Force .\import\input\recycler | Out-Null
Copy-Item ".\<your clean copy>.xlsx" .\import\input\workbook.xlsx
Copy-Item ".\Recycleing info\*.xlsx" .\import\input\recycler\

The recycler sheets are only a cross-check. The ones used, and the pickup each one should match, are listed in scripts/import/config.ts (RECYCLER_SHEETS). April 30th.xlsx is empty and skipped.

2. Export the customers from production (read-only):

npx wrangler d1 execute drives_db --env production --remote --json `
  --command "SELECT id, kind, cw_company_id, name FROM customers" > .\import\input\customers.json

3. Dry run. This writes nothing to any database.

npm run import:dry

It writes these files to import\out\:

  • anomalies.html: open it in a browser. BLOCKING findings come first, then REVIEW. Click a heading to sort.
  • anomalies.csv
  • customer-mapping.csv
  • summary.txt: rows and drives per tab, status counts, pickups and legacy certificates.

4. Fill in the customer mapping. Open import\out\customer-mapping.csv in Excel. There is one row per workbook customer name that isn't handled automatically. (?, blank and N/A become Unknown customer; Totlcom… and TCV… become TOTLCOM (internal).) In the action column, put:

action Meaning
map Use the suggested ConnectWise company
map:<cw id> Use another company (IDs of the next-best matches are in other_suggestions)
unknown Import as Unknown customer (fix later on the Customers screen)
internal TOTLCOM (internal)

Save it as CSV UTF-8 to import\input\customer-mapping.csv. Later dry runs keep the actions you've already filled in.

5. Re-run npm run import:dry until summary.txt says Ready: 0 BLOCKING findings. Then read the REVIEW list.

BLOCKING code What to do
UNKNOWN_TAB The workbook has a tab that config.ts doesn't list. Remove it from the clean copy, or add it to the config.
MISSING_COLUMNS A tab's header row isn't where the config says, or a header was renamed.
UNPARSEABLE_DATE / MISSING_DATE Fix the date in the clean copy (accepted forms: a real Excel date, M/D/YY, M/D/YYYY, M-D-YY).
EMPTY_SERIAL / BAD_SERIAL Fix or delete the row in the clean copy.
PRECISION_LOST Excel rounded a 15+ digit number. Type the real serial from the COR or the drive, as text (start it with ').
UNMAPPED_CUSTOMER Give that name an action in the mapping CSV.

REVIEW findings don't block, but read them. They cover: numeric serials (check for lost leading zeros), split cells, duplicates, inherited (ditto or merged) values, pickups with no certificate, rows with no pickup date (imported as on the shelf), recycler-sheet differences, probable typo dates, and estimated received dates.

6. Build the SQL:

npm run import:build

This writes import\out\import-<date>.sql. Pickups and legacy certificates come first, then drives in chunks of 50.

7. Rehearse locally:

npm run db:migrate:local
npx wrangler d1 execute DB --local --file .\import\out\import-<date>.sql
npm run dev

Check the dashboard, search a few serials you know, and open a legacy certificate (Certificates → All).

8. Note the restore point, then apply to production:

npx wrangler d1 time-travel info drives_db --env production
npx wrangler d1 execute drives_db --env production --remote --file .\import\out\import-<date>.sql

Write down the bookmark it prints. npx wrangler d1 time-travel restore drives_db --env production --bookmark <bookmark> undoes the import if needed.

9. Apply the same file again to prove it is idempotent. Wrangler should report 0 rows written.

10. Link the 6 real CORs through the app (Certificates → Upload), one per pickup:

COR PDF Pickup
12-5-25 PU-2025-12-05
3-27-26 PU-2026-03-27
04-23-2026 PU-2026-04-23
4-30-26 PU-2026-04-30
6-5-26 PU-2026-06-05
9-19-25 PU-2025-09-19, by Manual link (the COR has no serial list)

Each approval files the PDF in SharePoint.

11. Fix Unknown customers over time: Customers → Move drives to another customer (Manager+). Pick Unknown customer, tick the drives, pick the right customer, give a reason.

12. Clean up:

Remove-Item -Recurse -Force .\import\input, .\import\out

Also delete the clean workbook copy, or put the sensitivity label back on it (§12.1).

How it behaves (for review)

  • Traceable: each drive's notes say Imported from <tab> row <n>. Each imported drive, pickup and legacy certificate has an audit row (source = import, actor import@local) holding the original cell values.
  • Idempotent: each drive has an import_key = sha256(serial | received date | tab | occurrence). Every insert is ON CONFLICT DO NOTHING or guarded with NOT EXISTS.
  • Blank cells take the value from the row above only in the 2023 merged-cell layouts (B, C, D). In the other layouts, only ditto marks (", '') do.
  • Legacy pickups:
  • Each pickup date becomes a closed pickup PU-<date> with a legacy certificate LEGACY-<date>.
  • certified_at is the certificate-received date, or the pickup date when none was received ("no certificate received", Q19).
  • Rows with no pickup date are imported as on the shelf, and flagged.