# Catalog import mapping

The importer treats the Excel **Product ID** as the stable source key. It never coerces ISBN or SKU values to numbers.

## Data-preservation rules

- `Book.sourceProductId` is the idempotency and resume key.
- `Book.sourceSku` stores the workbook value verbatim. `Book.sku` is the unique operational key; duplicate source SKUs receive a deterministic Product ID suffix.
- `Book.isbn` is text and intentionally not unique because the workbook contains legitimate repeated values.
- `Book.sourceSlug` stores the workbook slug. `Book.slug` is the unique public slug; duplicates receive a deterministic Product ID suffix.
- Author and publisher `sourceKey` values are SHA-256 keys of the exact Unicode source values. Case-, spacing-, and Unicode-distinct source records are preserved.
- Blank author/publisher cells remain null; the importer does not invent an “Unknown” record.
- Product and child-record transactions are isolated. One rejected row is logged without aborting the remaining catalog.

## Worksheets

| Worksheet | Rows | Destination |
|---|---:|---|
| Products | 3,083 | `Book`, `BookAuthor`, `BookCategory`, `BookImage` |
| Inventory | 3,083 | `BookInventory` and mirrored `Book.stock` |
| Categories | 47 | `Category` |
| Sub Categories | 0 | `SubCategory` (no records created) |
| Authors | 1,072 | `Author` |
| Publishers | 300 | `Publisher` |
| Product Text | 6,060 | `BookText` plus sanitized display HTML on `Book` |
| Custom Attributes | 23,166 | `BookAttribute` |
| Homepage Categories | 1 | `HomepageCategory` |
| Header Categories | 0 | no records |
| Currencies | 4 | `StoreCurrency` |
| Display Limits | 5 | `DisplayLimit` |
| Catalogues | 43 | `Catalogue` |
| Reconciliation | 10 | preflight validation only |

## Product columns

| Excel column(s) | Database field / relationship |
|---|---|
| Product ID, Admin Serial | `sourceProductId`, `adminSerial` |
| Book Name | `title` |
| ISBN | `isbn` (text) |
| SKU / ISBN Code | `sourceSku`, derived unique `sku` |
| Barcode | `barcode` |
| SEO Slug | `sourceSlug`, derived unique `slug` |
| Author | `BookAuthor → Author` |
| Publisher | `publisherId → Publisher` |
| Category Raw/Normalized/IDs/Homepage Category fields | raw audit columns plus `BookCategory → Category` |
| Price, Discount Price, GST | decimal price/tax fields |
| Stock, In Stock | `Book.stock`, `BookInventory`, source flag |
| Status, New Release, Display Order | status/display fields |
| Language, Year, Edition, Class, Pages, Binding | bibliographic fields |
| Description Plain, Additional Attributes Plain | plain text fields |
| Description / Additional Attributes HTML parts | `BookText` and sanitized HTML display fields |
| Image URL / all image URL columns | ordered `BookImage` rows; first image is `coverImage` |
| Tags | `tagsJson` |
| Product/Admin/Source URLs | source audit fields |

HTML is retained verbatim in `BookText.rawHtml`; only the copy exposed for storefront rendering is sanitized.

## Catalogue relationship rule

The workbook contains 43 catalogue records but no product-to-catalogue or category-to-catalogue key. The importer therefore creates all `Catalogue` records and does not guess relationships. An approved JSON mapping can be supplied with `--catalogue-map`; its keys are workbook Catalogue IDs and its values are exact category-name arrays. The importer then creates `CategoryCatalogue` and corresponding `BookCatalogue` links transactionally.