Catalog Import & Export — Admin

HTTP surface for the universal product import pipeline (CSV/TSV/XLSX in, with S3 image migration) and the async product export, which emits the same format the importer reads. The vendor-scoped mirror of this surface lives in vendor/catalog-io.md.

Source: api-modules/catalog-io/src/controllers/admin-product-import.controller.ts, api-modules/catalog-io/src/controllers/admin-product-export.controller.ts, api-modules/catalog-io/src/controllers/admin-import-profile.controller.ts.

The export is the import template. Both directions are driven by one column definition (api-modules/catalog-io/src/format/columns.ts), so an exported file is a valid import of itself. Export → edit in a spreadsheet → re-import round-trips exactly.

The shape

Every source travels one road:

source file → mapping profile → canonical record → ProductService

The native format is a built-in profile whose mapping is the identity function; OpenCart, WooCommerce, Shopify and Magento are profiles with real transforms. There is one parser, one validator and one applier — imported products go through the same validation, events and audit as one created by hand in the admin.

Authentication

All endpoints require a Better-Auth admin session and a role granting the matching permission.

apply is deliberately separate from create: uploading and previewing a file is far lower-stakes than writing tens of thousands of products, so the two can be granted independently.

Pipeline stages

Seven stages, two of which are operator gates. Nothing large happens inside an HTTP request — profiling, validation, image fetching and applying all run on BullMQ (product-import, product-image-fetch, product-export), gated to worker roles by APP_ROLE.

Asset resolution sits deliberately before apply: by the time a product row is written, every storage key is known, so there are no half-written image arrays and no post-hoc patching of jsonb columns.

A failing row is recorded and skipped — one malformed product never rolls back the 499 good ones sharing its chunk.

Imports

POST /admin/catalog/imports/upload-url

Presigned target for uploading the source file straight to storage. A 128MB catalog never travels through the API process.

Body: fileName (required), contentLength (required — the file's exact size in bytes; it is signed into the presigned PUT, so S3 rejects a body of any other size), contentType.

Returns: { key, uploadUrl, method, headers, expiresIn }. PUT the file to uploadUrl with the returned headers, then pass key to the next call.

POST /admin/catalog/imports

Registers an uploaded file and enqueues profiling.

Returns: 201 with the batch.

GET /admin/catalog/imports

Paginated batch list (buildPaginatedResponse → { data, metadata }).

GET /admin/catalog/imports/fields

The canonical field catalogue a mapping may target — served from the same COLUMNS definition the parser, exporter and template read, so the mapping UI can never offer a field the pipeline doesn't understand.

GET /admin/catalog/imports/:id

Batch status and counters (totalRows, validRows, invalidRows, appliedRows, failedRows, assetsTotal, assetsResolved, assetsFailed). Poll while the status is a working one.

GET /admin/catalog/imports/:id/profiling

Detected sheets, headers, ~50 sample rows, the auto-suggested mapping, and any required fields left unmapped. 409 until profiling has finished.

Suggestions carry a confidence: exact (our own header), alias (a known spelling from another platform, e.g. Model → sku), fuzzy, or none.

PUT /admin/catalog/imports/:id/mapping

Confirms or overrides the mapping. Body is { mapping, saveAsProfile? }; passing saveAsProfile also persists it as a reusable profile.

The mapping is validated against importProfileSchema — a fixed, enumerated transform registry. Profiles are operator-editable and stored in the database, so an executable mapping would be a remote-code-execution hole; there is no eval.

POST /admin/catalog/imports/:id/validate

Enqueues validation. Every row is normalised, grouped into products, resolved (names → ids, dry), and then the exact payload apply will send is built and checked against createProductSchema / syncProductSchema. Both stages call one builder (services/payload.ts), so the preview cannot promise something apply then rejects — a bad slug pattern or an out-of-range quantity reads as a fixable cell rather than an apply-time crash.

Per-row issues are persisted so the operator sees every problem in the file at once.

Two behaviours keep an update from failing on data it isn't changing:

• An unchanged slug is not re-sent. Legacy rows can hold slugs the current pattern rejects; re-asserting one would fail an update that never touched it.
• Unspecified media falls back to what the product already has. media.images is required by the sync schema, so omitting it isn't possible and sending [] would wipe the gallery — the existing images are passed through instead, which is what "a blank cell leaves it alone" has to mean against a full-replace endpoint.

GET /admin/catalog/imports/:id/preview

The confirm-before-apply payload: counts, an error histogram by code, the first 100 invalid rows, what will be created (products, variants, and the actual brand/category/tag names), what will be updated, and the image breakdown (total / already in storage / to download).

Nothing has been written at this point.

GET /admin/catalog/imports/:id/rows

Paginated rows, filterable by status (pending, valid, invalid, applied, failed, skipped).

GET /admin/catalog/imports/:id/errors.csv

The invalid rows and their errors as a file: row_number,handle,sku,field,code,message.

POST /admin/catalog/imports/:id/apply

Applies from validated or failed_validation: the valid rows are written and the invalid ones stay skipped. One malformed product out of a thousand must not block the whole file, and the preview already shows exactly what will be left out. Only a file where every row has an error is refused.

Transitions are guarded on the expected current status, so a duplicate confirm cannot start two apply runs.

POST /admin/catalog/imports/:id/cancel

Cancels a batch that has not been applied.

Exports

POST /admin/catalog/exports

Queues an export. Filters: status, visibility, vendorId, categoryId, brandId, updatedSince, productIds. format is csv or xlsx; amountUnit picks the money column spelling.

Rendering is keyset-paginated on product.id rather than OFFSET — a 100k-product export with a growing offset degrades badly. Beyond the row cap the file is marked truncated rather than silently cut.

GET /admin/catalog/exports

Paginated export history with status.

GET /admin/catalog/exports/template

The native-format template: header row plus a few illustrative rows showing the conventions (path separators, list separators, the explicit-clear sentinel). Query: format, amountUnit. Built from the same column definitions the exporter and importer share, so it cannot drift.

GET /admin/catalog/exports/:id · GET /admin/catalog/exports/:id/download

Status (poll until ready), then the file. Files expire after seven days; the row stays as an audit record.

Mapping profiles

GET|POST /admin/catalog/import-profiles, GET|PUT|DELETE /admin/catalog/import-profiles/:id.

Built-ins (native, opencart, woocommerce, shopify, magento) are re-seeded from code on every worker boot (APP_ROLE=worker or all) so a shipped fix reaches existing deployments; the seed runs detached with a bounded retry, so an unreachable database delays it rather than failing boot. They cannot be edited in place — a PUT or DELETE against one returns 409; clone it and edit the copy. Operator clones carry isBuiltIn: false and are never touched by seeding.

A profile declares four things:

• sheets — which worksheet holds products, and where gallery images live in a multi-sheet workbook.
• grouping — how rows collapse into products. row_per_variant (Shopify, grouped by handle), row_per_product (OpenCart), parent_child (WooCommerce's type/parent columns), or joined_sheet.
• fields — canonical field → { column, transforms }.
• images — which columns carry image references, their delimiter, and a baseUrl prefix for legacy shops that store catalog/product/foo.jpg relative to an image root.

Plus onImageFailure (skip_image default, or fail_row) and optionStrategy (flatten option combinations into variant SKUs, or keep them as metadata — OpenCart has no variant entity, so it defaults to metadata).

The native format

One row per variant, grouped by handle. Single-variant products are one row. Product columns repeat on every row of a group; import takes the first non-empty value and warns on a conflicting repeat.

Conventions

• | separates multi-values; categories are paths (Home > Men > Shirts).
• Relationships are by name, not id — resolved case-insensitively, created per createMissing. Category paths are created top-down so a hierarchy comes out as a hierarchy, not three orphans.
• Units are in the header (weight_grams, length_cm) so nobody guesses. Legacy "width" maps to breadth_cm.
• At most 3 options and 3 tabs. A fourth is a clear row error rather than a widening file — the fixed column set is what makes the export usable as a template.

Money

The column name declares the unit, so an export and a later re-import of that same file can never disagree:

Both present is an error. The amountUnit field on the batch is only a fallback for a mapped column that declares neither.

Blank versus clear

A blank cell means leave unchanged. syncProduct is full-replace, so if blank meant "clear", deleting a column from a spreadsheet would wipe that field across the whole catalog. The literal __NULL__ is the explicit clear.

Column reference

product_id and variant_id are written by export and authoritative on import; blank means match by handle/slug and SKU, or create.

Seller resolution runs most-specific first: the session seller (vendor surface) → vendor_slug → the vendor display name → a MISSING_VENDOR row error. vendor_slug is preferred because vendor.slug is uniquely indexed, stays readable in a spreadsheet, and is portable between environments — unlike an internal id, and unlike a display name that can be renamed or duplicated. Both seller columns are admin-only: the vendor surface ignores them and locks every row to the session seller. stock_quantity is routed to @sc/inventory as a delta against what is on hand, so re-importing the same file converges rather than doubling stock.

Image migration

product.images and product_variant.images store relative keys, and StorageService.resolveUrl() builds the public URL at read time. Each image cell is classified individually:

• Starts with http:// or https:// — downloaded, subject to a byte cap, timeout and an image-only content-type allowlist. image/svg+xml is excluded for the same reason it is excluded from presigned uploads: it is script-executable and would stage stored XSS served from the asset domain.
• Anything else — treated as a key already in the bucket and stored verbatim. This is the path for "we mirrored the old bucket ourselves".

Downloads are content-addressed: the sha256 of the bytes is the dedupe key (unique index on product_import_asset.content_hash), so identical images upload exactly once across every batch. Re-running a 40,000-image migration re-uploads nothing.

Failures are per-image and retried with backoff. When retries are exhausted, onImageFailure decides: drop that one image and warn (default), or fail the row.

Re-import idempotency

source_platform + source_id populate product_external_ref ((platform, entity, sourceId) → targetId, unique). A second run of the same source file updates rather than duplicating, and a later pull of orders or reviews from that shop can resolve to the products this pipeline created.

Downstream effects

Applying emits one product.import.applied carrying the distinct product ids — never per-row product.created. Per-row emission during a 50,000-product migration would storm the search, Klaviyo, Meta and Google Merchant listeners. @sc/search chunks the ids into bulk reindex jobs, collated per batch by jobId so a retried import doesn't fan out duplicate work.