# ERD — Catalog & Vendor

**[Open the diagram →](erd-catalog-vendor.svg)**

Generated from the EF Core model in `Rempla.Core` (entities plus `RemplaModelConfiguration.cs`), not
hand-drawn. The `.mmd` beside this file is the source; the `.svg` is the rendered artifact that
`docs.rempla.com` serves.

---

## What this diagram covers

The catalog and vendor slice: how a vendor's published feed becomes something a customer can buy, and
how a purchase becomes an order placed with a vendor. It stops at the boundary — `CustomerScheduledGift`
and `ScheduledGiftTransaction` appear with only the columns that touch the catalog, because their full
shape belongs to the scheduled-gift model (ADR-010).

Not covered here: identity and the relationship tree, the vault, pricing internals. For all 42 tables
at map scale, see **[ERD — Full Schema](erd-full.md)**; this file is the zoom on that map.

## Reading the diagram: enums are not lookup tables

The type column is the **storage type**, so every enum-backed column reads `int` and names its enum
in the comment as `ENUM <Name> (code-only)`. That distinction matters more than it looks:

| | Backed by | In the database | Joinable |
|---|---|---|---|
| `ENUM X (code-only)` | a C# enum | a bare `int` — `0`, `1`, `2` | **No.** There is no table. |
| A box on the diagram | a real table | an `int` FK | Yes |

There is no `HasConversion` anywhere in `Rempla.Core`, so every enum takes EF's default int storage.
The names exist only in code — a `SELECT` against the shared dev DB gives you integers and nothing
to join them to.

In this slice: `ProductCategory`, `ProductTag` and `Occasion` are real tables. Everything else you
see named — `VendorStatus`, `VendorIntegrationType`, `VendorAuthScheme`, `ProductStatus`,
`VendorOfferAvailability`, `VendorProductSelectionState`, `VendorOrderStatuses` — is code-only.

**Do not generalise this to a house rule; there isn't one.** Across the wider model the same kind of
concept is modelled both ways:

- `AdminRole` {Support, Ops, Admin} is an **enum** on `Account.AdminRole`, while `UserType`
  {Benefactor, GiftRecipient, …} is a **table** with an `AccountUserType` join. Both answer "what
  kind of user is this".
- `VaultBoxes` {Small=1, Medium=2, Large=3} is a third pattern — an enum shadowing a real table,
  used only to seed it (`Id = (int)VaultBoxes.Small`). It lets code name seeded rows, but nothing
  keeps the enum and the table's primary keys in step.
- `DateType` is a lookup table that itself has an enum column (`RepeatType`).

Nor are the lookup tables a uniform `Id` + `Name` shape. `UserType`, `RemembrancePlanRoleType` and
`EstateDesignationType` are just that; `ProductTag` and `DocumentType` add `SortIndex`; `Occasion`
and `VaultBoxItemCategory` add `IsActive`; `ProductCategory` adds `Slug` and `ImagePath`; and
`RelationshipType` calls its display string **`Label`**, not `Name`.

## The spine

```
Vendor ──publishes──> VendorProduct ──bound to──> VendorOffer <──sourced by── Product
                                                       ▲                        │
                                                       └────────selects─────────┘
```

Four tables, and each one exists because a different party owns the facts in it:

| Table | Who writes it | What it means |
|---|---|---|
| `VendorProduct` | **Catalog sync** | The vendor's assortment as published, mirrored by SKU. Overwritten on every sync. |
| `Product` | **Operator** | Something we have decided to sell. Vendor-agnostic (ADR-008). |
| `VendorOffer` | **Both** — see below | A route: this product, from this vendor, at this cost. |
| `Product.ActiveVendorOfferId` | **Operator** | Which route actually serves customers (ADR-013). |

`VendorOffer` is the only row with two writers, which is exactly why ADR-013 split its old `Status`
column. `Availability` and `VendorCost` are the sync's facts and are read-only everywhere else,
including admin. `ListPrice` and `LeadTimeDaysOverride` are ours.

## Rules the diagram encodes

- **A product with no selection is not sellable**, however many offers it has. `ActiveVendorOfferId`
  is nullable and routing **fails closed** — no silent substitution of another vendor, or another
  vendor's price (ADR-013).
- **Selection is structural, not policed.** One nullable FK on `Product` cannot express two
  selections, so there is no invariant for a sync to violate.
- **`Product` ↔ `VendorOffer` is a cycle.** `Product.ActiveVendorOfferId` is therefore `NO ACTION`;
  `DeleteOfferAsync` clears it explicitly. This also means `HasData` cannot seed a selection — it
  throws at model build.
- **`VendorOffer.VendorSku` is derived on save, never typed.** The sync refreshes stock and cost by
  looking this value up, so a SKU disagreeing with `VendorProductId` points the sync at the wrong
  product.
- **`VendorProduct` is keyed by `(VendorId, Sku)`**, unique — that pair is what sync upserts on.
- **One `VendorOrder` per `ScheduledGiftTransaction`**, enforced by a unique index. Placing twice
  sends two gifts and pays twice, so it is a database error rather than a rule the screen remembers.
- **Orders and offers snapshot rather than reference.** `VendorSkuSnapshot` / `VendorCostSnapshot` on
  the order and `ProductNameSnapshot` / `PromisedUnitPrice` on the transaction are historical facts.
  Re-selecting a vendor tomorrow must not rewrite what was sent yesterday.
- **Provenance FKs `SET NULL` rather than cascade.** An offer outlives the loss of its vendor product;
  an order outlives the loss of its offer (ADR-012).

## Known drift, drawn as it is

The diagram shows the schema as built, including the parts that are wrong. These are open threads,
not diagram errors:

- `VendorOffer.Availability` sits on the offer, while `VendorProduct.Availability` already describes
  the same subject one level up.
- `VendorProduct.DateLastImported` goes stale and nothing reads it — catalog sync never notices a
  product leaving the feed.
- `VendorProduct.Category` is a lossy remnant, still shown and still filtered on.
- `Product.DefaultLeadTimeDays` and `VendorOffer.LeadTimeDaysOverride` are collected in admin and
  read by nothing — `LeadTime` appears zero times in `Rempla.Web` and `Rempla.Api`.
- Nothing in the codebase can set a transaction to `Paid`.

## Regenerating

```bash
./render-erd.sh
```

Requires Node (uses `npx @mermaid-js/mermaid-cli`, no install needed). Edit the `.mmd`, run the
script, commit both files together — a `.svg` whose `.mmd` says something else is worse than no
diagram.

## When to refresh

Tie it to schema change, not the calendar:

1. **Every migration that adds, drops, or repoints a column in this slice.** Same commit. The
   migration is the trigger.
2. **Every accepted ADR that changes catalog or vendor structure.** ADR-013 moved a column between
   two owners; a diagram a week behind that would have been actively misleading.
3. **A standing read at each monthly rollup** — not to redraw, but to check the "Known drift" list
   above against what is still true.