# VSR RDS Schema — Normalized Layout vs. the Domino Backend

This document explains how the Virtual Sales Rep (VSR) **relational** schema in this
directory (`infra/sql/001–005`) relates to the original **IBM Domino** (Notes/NSF)
backend it replaces. The goal is to make one thing explicit for reviewers:

> The RDS layout deliberately **diverges** from Domino's document model, but it
> **preserves — and actually strengthens — the integrity of the relationships**
> that the legacy system only maintained by convention.

The legacy model is described here in its interpreted form
(`VIRTUALSALESREP.DEV.CART_HEADER` / `CART_DETAIL` / `ORDER_HEADER` / `ORDER_DETAIL`
in Snowflake), which is the Fivetran-synced projection of the Domino documents. A
field-level, provenance-tagged divergence register lives in
[`docs/cart-detail-divergences-INT-2851.html`](../../docs/cart-detail-divergences-INT-2851.html).

> **Preferred viewing / companion document.** This README is the narrative entry
> point (the *why* — the entity model and relationship integrity). The interactive
> [`cart-detail-divergences-INT-2851.html`](../../docs/cart-detail-divergences-INT-2851.html)
> report is the field-by-field register (the *what*). The two are kept in agreement:
> shared facts — the provenance tags (misunderstanding / unfinished / backend-changed
> / intentional), the `bulk_factor_qt` and loose-charge resolution under
> `DECISION_LOG` **D011**, and the **INT-2793** (`FUP-20260703-1348`) reconciliation
> backlog — match across both documents.

---

## 1. Two fundamentally different storage models

| | Domino (legacy) | RDS PostgreSQL (this schema) |
| --- | --- | --- |
| Paradigm | Document store (NSF) | Relational / normalized |
| "Record" | A **document** on a form (`CART`, `ORDER`, `PRODUCT`) | A **row** in a typed table |
| Schema | None enforced — forms are a UI convention | Enforced: types, `NOT NULL`, `CHECK`, `UNIQUE`, FKs |
| Relationships | **By convention** — documents share a field value (e.g. `CART_ID`) that agents/formulas join on | **By constraint** — `FOREIGN KEY` references with `ON DELETE` rules |
| Referential integrity | Application-enforced (LotusScript agents); nothing stops an orphan detail | Database-enforced; orphans are impossible |
| Access control | Per-document `READERS` / `AUTHORS` reader fields | External auth layer (`account_user_access` + app) |
| Multi-value data | Native multi-value fields (`SHIPTO_NO_LIST`) | Normalized: `TEXT[]` or a child/junction table |
| Flags | `"Y"` / `"N"` text | `BOOLEAN` |
| Rollups | Stored computed fields (`@formula`) on the header | Recomputed live server-side (source of truth) |
| Naming | `UPPER_SNAKE` DXL field names | `lower_snake` columns |

The divergence is intentional: Domino guarantees nothing about structure, so the
integrity of a cart-and-its-lines relationship lived entirely in agent code. Moving
to Postgres lets us encode those same relationships as constraints the engine
enforces on every write.

---

## 2. Entity mapping — Domino form/document → RDS table

| Domino form / document | RDS table (migration) | Relationship role |
| --- | --- | --- |
| `CART` header document | `carts` (002) | Cart aggregate root |
| `CART` detail documents | `cart_items` (002) | Line items of a cart |
| `ORDER` header document | `orders` (003) | Order aggregate root |
| `ORDER` detail documents | `order_items` (003) | Immutable order line snapshot |
| Account / customer records | `accounts` (001) | Billing/ship-to account |
| User / login records | `users` (001) | Authenticated buyer |
| Account↔user↔ship-to authorization | `account_user_access` (001) | Who may order for which ship-to |
| Restock watch flags | `notifications_subscriptions` (004) | OOS alert subscriptions |
| Excel / RecordTrak uploads | `order_form_uploads` (004) | Bulk-order upload audit |
| `PRODUCT` documents | *(not an RDS table)* — catalog stays in Snowflake `PRODUCT` / `V_PRODUCTS` | Referenced by UPC snapshot |

---

## 3. Relationship integrity — preserved and enforced

Every relationship the Domino system relied on is reproduced, and where Domino only
had a shared field value, the RDS schema promotes it to an enforced foreign key.

### 3.1 Cart header ⟷ cart detail  → `carts` 1—N `cart_items`

- **Domino:** a `CART_HEADER` document and its `CART_DETAIL` documents are tied
  together only because they carry the same `CART_ID` field. Nothing prevents a
  detail whose header was deleted, or two headers claiming the same `CART_ID`.
- **RDS:** `cart_items.cart_id` is a `FOREIGN KEY REFERENCES carts(cart_id) ON DELETE CASCADE`.
  A line can only exist under a real cart; deleting the cart removes its lines
  atomically. The header's identity is a surrogate `SERIAL` PK, not a hand-managed
  string.

### 3.2 Order header ⟷ order detail  → `orders` 1—N `order_items`

- **Domino:** `ORDER_HEADER` / `ORDER_DETAIL` documents linked by the web order
  number, denormalized point-in-time copies of the cart.
- **RDS:** `order_items.order_id` → `orders(order_id) ON DELETE CASCADE`. The
  denormalized product snapshot on `order_items` is **kept on purpose** — it mirrors
  Domino's point-in-time capture so historical orders render correctly even after the
  catalog changes. Immutability of history is a relationship we deliberately preserve.

### 3.3 Cart → Order lifecycle

- **Domino:** placing an order copied cart documents into order documents; the link
  back to the originating cart was informal.
- **RDS:** `orders.cart_id` → `carts(cart_id)` records provenance, and `carts.status`
  moves `open → placed` under a `CHECK` constraint so the state machine can't hold an
  invalid value. The cart's lines are snapshotted into `order_items` inside a single
  transaction (`placeOrder`), so an order is never half-created.

### 3.4 Account / user / ship-to authorization

- **Domino:** a user's right to order for a set of ship-to accounts was expressed via
  multi-value fields and reader/author ACLs on documents.
- **RDS:** the many-to-many is normalized into `account_user_access`
  (`user_id` → `users`, `account_id` → `accounts`, both `ON DELETE CASCADE`,
  `UNIQUE(user_id, account_id)`), with the authorized ship-tos in `ship_to_accounts TEXT[]`
  and an `access_level` `CHECK (read|write|admin)`. Cart/order mutations verify
  ship-to authorization against this table (`requiresAccountAccess`), replacing the
  implicit reader-field gate.

### 3.5 Product references (intentionally *not* a foreign key)

- The catalog is **not** in RDS — it lives in Snowflake (`PRODUCT` / `V_PRODUCTS`),
  refreshed from CDS/Domino. `cart_items` / `order_items` therefore reference a
  product by a **snapshotted `upc_cd`** plus copied descriptive/pricing columns,
  **not** an FK. This is a deliberate divergence: it decouples the transactional store
  from the analytical catalog and lets a placed line survive a product being retired —
  exactly the durability Domino gave by copying fields into each detail document.

---

## 4. How each Domino integrity convention is now enforced

| Legacy convention (Domino) | RDS mechanism |
| --- | --- |
| Header/detail joined by shared `CART_ID` field | `FOREIGN KEY … ON DELETE CASCADE` |
| "One open cart per user" (agent logic) | App-enforced + `carts.status` `CHECK` + `idx_carts_user_id` |
| No duplicate line for a UPC in a cart (agent logic) | `UNIQUE (cart_id, upc_cd)` (drives the add-to-cart upsert) |
| Positive quantities (form validation) | `CHECK (quantity > 0)` / `CHECK (order_quantity > 0)` |
| Valid status values (keyword field) | `CHECK (status IN (...))`, `CHECK (account_type IN (...))`, etc. |
| Composite key `(CART_ID, LINE_NO)` | Surrogate `SERIAL` PK + `UNIQUE (cart_id, upc_cd)`; `line_no` reconstructed at order time |
| `"Y"`/`"N"` flags | `BOOLEAN NOT NULL DEFAULT` |
| Multi-value `SHIPTO_NO_LIST` | `ship_to_accounts TEXT[]` (or the `account_user_access` junction) |
| Stored `@formula` rollups (`TOTAL_*`) | Recomputed live in `pricing.ts` on every read (single source of truth); `orders.*_total` persisted only as an immutable order snapshot |
| Carton/loose-charge factor on each detail | `bulk_factor_qt` snapshot on `cart_items` / `order_items` (INT-2851) drives `loose = quantity % bulk_factor_qt` |

---

## 5. What is intentionally *not* carried over (distilled subset)

The RDS tables are a **purpose-built distilled subset** of the legacy documents, not a
1:1 clone. Domino-only artifacts are dropped, and legacy columns that no current
feature needs are deferred rather than ported:

- **Dropped (correct):** `READERS` / `AUTHORS` reader-field ACLs (access control now
  lives in the auth layer, not per row); Domino sync bookkeeping columns.
- **Computed live instead of stored:** `CART_HEADER` `TOTAL_*` rollups and per-category
  quantity breakdowns.
- **Deferred (backlog, not defects):** soft-delete (`LOGICAL_DELETE_IN`), shadowbox
  fields, per-line status lifecycle, and other descriptive columns.

The full field-by-field accounting — tagged as *misunderstanding*, *unfinished*,
*backend-changed*, or *intentional* — is in
[`docs/cart-detail-divergences-INT-2851.html`](../../docs/cart-detail-divergences-INT-2851.html),
and full RDS ↔ legacy reconciliation is tracked under **INT-2793**
(`FUP-20260703-1348`).

---

## 6. Entity–relationship diagram (RDS)

```mermaid
erDiagram
    accounts ||--o{ account_user_access : "authorizes"
    users    ||--o{ account_user_access : "granted to"
    users    ||--o{ carts               : "owns"
    accounts ||--o{ carts               : "billed to"
    carts    ||--o{ cart_items          : "contains (CASCADE)"
    users    ||--o{ orders              : "places"
    accounts ||--o{ orders              : "billed to"
    carts    ||--o{ orders              : "originates"
    orders   ||--o{ order_items         : "snapshots (CASCADE)"
    users    ||--o{ notifications_subscriptions : "watches"
    users    ||--o{ order_form_uploads  : "uploads"

    accounts {
        int     account_id PK
        string  account_no UK
        string  account_type "retail|label|internal"
    }
    users {
        int     user_id PK
        string  email UK
        string  account_no
    }
    account_user_access {
        int      access_id PK
        int      user_id FK
        int      account_id FK
        string[] ship_to_accounts
        string   access_level "read|write|admin"
    }
    carts {
        int     cart_id PK
        int     user_id FK
        int     account_id FK
        string  status "open|placed|expired"
    }
    cart_items {
        int     cart_item_id PK
        int     cart_id FK
        string  upc_cd "snapshot (no FK; catalog in Snowflake)"
        int     quantity "CHECK > 0"
        int     bulk_factor_qt "carton factor snapshot"
    }
    orders {
        int     order_id PK
        int     user_id FK
        int     account_id FK
        int     cart_id FK "provenance"
        string  status "pending|submitted|shipped|invoiced|cancelled"
    }
    order_items {
        int     order_item_id PK
        int     order_id FK
        int     line_no
        string  upc_cd "immutable snapshot"
    }
```

---

## 7. Bottom line

The migration trades Domino's **implicit, agent-maintained** relationships for
**explicit, engine-enforced** ones. Every legacy header→detail and
account→user→ship-to relationship survives; each is now backed by a foreign key,
a check, or a unique constraint that the database guarantees on every write.
Where we deliberately diverge — surrogate keys, booleans, live-computed rollups,
UPC snapshots instead of catalog FKs, dropped reader fields — the change either
strengthens integrity or reflects that the catalog now lives in Snowflake, not
Domino. Nothing that mattered to correctness was loosened; several things Domino
left to convention are now impossible to get wrong.