Customers

Customer is the unified contact list behind /admin/customers. It is keyed on a lowercased email address and holds everyone the business has a record of — registered accounts, guest checkouts, imported mailing-list contacts, fundraiser organizers, and wholesale accounts — whether or not they ever placed an order.

A Customer is deliberately not a User. Most rows are contacts with no login; userId links the ones that do have an account.

Account type

Every customer carries an accountType (CustomerAccountType), shown as the Account column on the customer list and included in the CSV export:

accountType answers "what kind of relationship is this?" and is distinct from source (CustomerSource), which records how the record arrived — IMPORT, GUEST_ORDER, REGISTERED, or MANUAL.

When a customer has evidence of more than one relationship, the strongest commercial one wins: wholesale > fundraising > standard. Every designation the evidence supported is written into notes alongside the source files, so the choice can be audited or re-derived without re-running the import.

Order rollups

totalOrders, totalSpent, and lastOrderAt are denormalized rollups. POST /api/admin/customers/sync recomputes them from real Order rows — both account orders (via userId) and guest orders (via guestEmail) — and also creates any missing customer rows for registered users and guest checkouts.

Rollups reconstructed from imported spreadsheets are only ever raised, never lowered, so a sync against live order data always takes precedence.

Browsing the list

/admin/customers is built for a list in the tens of thousands rather than a handful of records.

Sorting — every column heading is a button. The first click sorts ascending, clicking the active column flips the direction, and the choice lives in the sortBy / sortDir query parameters so a sorted view can be linked or bookmarked. Sortable columns: customer, phone, account type, organization, source, email status, orders, spent, and last order.

Two rules make long-list sorting behave. Blank values always sort last in both directions — over 5,000 contacts have no name, and they shouldn't fill the first page whenever you sort by one. And every sort ends with a hidden tie-break on email, so paging through 22,000 rows doesn't reshuffle ties between pages.

Sorting is whitelisted in lib/customers/customer-list.ts; an unrecognized sortBy falls back to the default rather than reaching the database.

Filtering — free-text search covers name, email, phone, and organization. Account type and source have their own dropdowns. Filters reset paging but preserve the active sort.

Page size — 100, 250, 500 (default), or 1,000 rows per page. Any other value in the query string falls back to the default, so the whole table can't be requested at once.

Export — the CSV download shares the list's where and orderBy, so it contains exactly the rows you're looking at, in the order shown, across every page — not just the visible one.

The export response is streamed in batches rather than assembled as one string. A Vercel Function caps a buffered response body at 4.5 MB, and a full export of the current book is around 11 MB, which would fail with FUNCTION_PAYLOAD_TOO_LARGE. Streamed responses have no such cap. Batches are paged by offset because the export honours the user's chosen sort — there is no single cursor column — which is why every orderBy ends with a unique tie-break on email.

Editing a customer

Clicking a row opens an editor for firstName, lastName, phone, accountType, sourceName, emailStatus, emailPermissionStatus, and notes. Saving issues a PATCH /api/admin/customers/[id], which requires users:write and writes an audit log entry.

Three things are shown but not editable, by design:

• email is the unique key that order sync, import batches, and every mailing-list row join on. Changing it in place would detach the customer from its own history.
• totalOrders / totalSpent / lastOrderAt are recomputed by the order sync from real Order rows, so a hand edit would silently disappear.

notes is pre-filled rather than blank — for imported customers it holds the archive audit trail, and an empty field would wipe it on the first save.

Building a mailing list

The Create mailing list button turns the current view into a MailingList plus its MailingListSubscriber rows, via POST /api/admin/customers/mailing-list. It requires both users:read and content:write, since it reads the customer book and writes to email marketing.

Scope is either every customer matching the current filters — the whole result set, not just the page on screen — or only the rows ticked with checkboxes, when any are ticked.

Each subscriber is tagged with its account type and organization, so the list can be segmented after it lands in email marketing. Rows are inserted in batches, and @@unique([listId, email]) with skipDuplicates makes the whole operation safe to repeat.

CSV import and export

/admin/customers supports round-tripping the list as CSV.

• POST /api/admin/customers/import parses an upload and returns a create/update preview. It is a dry run unless commit: true. Column detection is alias-based (built around the Constant Contact export header) and can be overridden with an explicit mapping. Email is the only required column and is the upsert key.
• GET /api/admin/customers/export writes the list back out with column names the importer recognizes, honoring the current search and source filters.

Re-importing enriches rather than erases: a blank cell leaves the stored value untouched.

Both routes require the users:write / users:export permissions.

Document-archive importer

A two-stage operational importer builds the customer list from the local Documents/ business archive (a gitignored, multi-GB folder of scans and exports that is never committed). It exists because the archive holds a decade of contact data across BigCommerce exports, Constant Contact lists, QuickBooks dumps, PayPal statements, retail-store lists, and hand-built fundraiser order forms.

Stage 1 — extract

python3 scripts/extract-archive-customers.py \
  --root ../../Documents --out ../../data/customer-extract

Walks every .csv, .xlsx, .xls, .docx, and .pdf and writes one JSONL record per (email, source file) pair with full provenance. Three modes are tried per file:

• tabular — a header row maps columns to fields. Every email-shaped cell in a row is accepted, not just the mapped column, because several hand-edited sheets have shifted columns.
• labelled — the Organization: / Submitted by: / E-Mail: layout used by the fundraiser order forms, matched per cell. An Organization: match is required, which keeps bank statements and vendor agreements out.
• freeform — any email-shaped string in the text. Restricted to the archive categories that actually hold customers.

Each file also gets a designation signal from its path and name. Files that mix retail individuals with business accounts (QuickBooks dumps, campaign-response reports) are marked mixed and decided per row by whether a company name is present.

Stage 2 — merge and load

tsx scripts/import-archive-customers.ts --in ../../data/customer-extract
tsx scripts/import-archive-customers.ts --in ../../data/customer-extract --commit

lib/customers/archive-merge.ts collapses the records into one customer per email. Every rule is order-independent — feeding the same records in a different sequence must produce the same customer:

• Designation by the wholesale > fundraising > standard precedence above.
• Email status takes the most restrictive value across all sources, so an unsubscribe is never overwritten by an older export marking the address Active.
• Field values take whatever the most sources agree on, breaking ties toward the fuller value.
• Dates resolve their DD/MM vs MM/DD convention per file — the archive's BigCommerce exports disagree with each other.
• Order rollups dedupe by the order itself, so the same export filed under two names counts once.

The script prints its target database before writing and is a dry run unless --commit is passed. It writes customers.merged.csv and merge-report.json for review, tags every row with an importBatchId, and preserves hand-written notes by appending rather than replacing.

Related

• Email Marketing — campaigns, lists, and suppression
• Lead Generation — B2B prospecting, which feeds wholesale contacts
• Orders — the order data behind the rollups