# ERD — Full Schema

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

All **42 tables** — every `DbSet` on `RemplaDbContext`. Generated by reading
`Rempla.Core/Entities/` and `RemplaModelConfiguration.cs`, not the prose docs.

It is a large picture (roughly 7300 × 5100). It is an SVG, so zoom and pan rather than trying to
read it whole. For the catalog and vendor tables at full detail, use
**[ERD — Catalog & Vendor](erd-catalog-vendor.md)** instead; this one is the map, that one is the
zoom.

---

## What is shown, and what is not

**Every table, every foreign key, every unique index.** Columns are filtered so the map stays
readable — each table shows its primary key, all foreign keys, and the columns that discriminate or
carry money or meaning.

**Deliberately omitted:** `DateCreated` / `DateUpdated` (on nearly every table), and on the two
transaction tables the long tail of address, refund-recipient and per-charge columns.
`ScheduledGiftTransaction` alone has ~50 columns; drawing them all would make the other 41 tables
unreadable. Nothing structural is hidden — no FK, key or constraint is left out. For the full column
list, read the entity class.

## 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)`. There is no `HasConversion` anywhere in `Rempla.Core`,
so every enum takes EF's default int storage: the database holds `0`, `1`, `2` and **there is no
table to join to**. If it is a box on the diagram it is a real table; if it is only named in a
comment it exists solely in code.

There is no house rule about which to use. `AdminRole` is an enum on `Account`; `UserType` is a table
with a join. `VaultBoxes` is an enum that only seeds the `VaultBox` table's primary keys, with
nothing keeping the two in step. `DateType` is a lookup table with an enum column inside it. Full
discussion in the [catalog ERD notes](erd-catalog-vendor.md#reading-the-diagram-enums-are-not-lookup-tables).

## The five contexts

Mermaid ER diagrams have no grouping construct, so the contexts are not boxed on the picture. They
cluster by connectivity instead:

| Context | Tables | Anchor |
|---|---|---|
| **Identity** | 19 — `Account`, `Person`, `Relationship`, `RemembrancePlanRole`, `EstateDesignation`, `Document`, contacts, and their type tables | `Person` |
| **Catalog & vendor** | 11 — `Product`, `Vendor`, `VendorOffer`, `VendorProduct`, `VendorOrder`, merchandising joins | `Product` / `Vendor` |
| **Scheduled gifts** | 4 — `CustomerScheduledGift`, `ScheduledGiftTransaction`, `GiftInterest`, `ScheduledGiftInterest` | `CustomerScheduledGift` |
| **Vault** | 7 — `VaultBox`, `CustomerVaultBox`, items, `VaultTransaction`, its line items and vault items | `CustomerVaultBox` |
| **Platform** | 1 — `ApplicationSetting` (unconnected; it appears with no edges) | — |

**`Person` and `Account` are the joins between contexts.** Gifts and vault boxes both hang off
`Account` for the buyer and `Person` for the recipient. Nothing else crosses.

## Patterns the whole schema repeats

- **Cart, then receipt.** `CustomerScheduledGift` → `ScheduledGiftTransaction` and
  `CustomerVaultBox` → `VaultTransaction` are the same shape twice: a mutable in-progress row
  (including `WizardLastStep` / `WizardFurthestStep` wizard position) and an immutable purchased one.
  Both transactions carry `TransactionNumber` unique, `IdempotencyKey` unique-when-not-null, and a
  **filtered unique index on the parent** (`WHERE Status <> Voided`) — so one live transaction per
  cart, with voided ones allowed to accumulate.
- **Snapshot rather than reference.** Purchase and placement freeze what they bought:
  `ProductNameSnapshot` and `PromisedUnitPrice` on the gift transaction, `VaultBoxName` /
  `VaultBoxServiceFee` on `CustomerVaultBox` and `VaultTransaction`, `VendorSkuSnapshot` /
  `VendorCostSnapshot` on `VendorOrder`. `VaultTransactionVaultItem` copies the box's contents
  outright rather than pointing at them. Re-pricing tomorrow must not rewrite yesterday.
- **Directional person-to-person edges.** `Relationship`, `RemembrancePlanRole` and
  `EstateDesignation` each carry `PersonId` + `RelatedPersonId` — three separate, independent edge
  types between the same two people. Structure, plan role, and estate designation are deliberately
  not the same edge.
- **Nullable FKs almost everywhere.** Most relationships are optional at the database level, so very
  little is enforced by the schema. Fail-closed routing (ADR-013) is the exception, not the rule.

## Known drift, drawn as it is

The diagram shows the schema as built, including the parts that are wrong:

- **`CustomerVaultBox.DeliveryPersonDateId` and `VaultTransaction.DeliveryPersonDateId` are not
  foreign keys.** They are plain nullable ints — no navigation property, no configuration, no index,
  no constraint in the model snapshot. The gift side does this correctly:
  `CustomerScheduledGift.AnchorPersonDateId` is a real FK with an index. Same concept, two levels of
  enforcement. Drawn with no edge, because there is no edge.
- **Nothing in the codebase can set a transaction to `Paid`.** `PaymentIntentId` and
  `IdempotencyKey` exist; no payment execution does.
- `GiftBillingModes` names a choice that no longer exists after ADR-010's upfront-payment amendment.
- `VendorOffer.Availability` duplicates a subject `VendorProduct.Availability` already covers.
- `VendorProduct.DateLastImported` goes stale — catalog sync never notices a product leaving a feed.
- `Product.DefaultLeadTimeDays` and `VendorOffer.LeadTimeDaysOverride` are read by nothing.
- `RelationshipType` names its display string `Label` while every other lookup table uses `Name`.

## Regenerating

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

Renders every `.mmd` in this directory. Requires Node; uses `npx @mermaid-js/mermaid-cli`, no
install. Edit the `.mmd`, run the script, commit both files together.

## When to refresh

Tie it to schema change, not the calendar:

1. **Every migration.** Same commit — the migration is the trigger. A migration that adds a table
   must add it here.
2. **Every accepted ADR that changes structure.**
3. **A standing read at each monthly rollup** to check "Known drift" against what is still true.