---
phase: dataModel
kind: level
level: relationships
---

# Aspect — RELATIONSHIPS

Connect the entities with foreign keys and cardinalities. **Every column that
references another table is a foreign-key constraint — non-negotiable, whatever
module the target lives in.** For each relationship, define: cardinality
(`1:1`/`1:N`/`N:1`/`N:M`), FK name (PascalCase, usually `{Target}Id`), required
(nullable?), cascade (`restrict`/`cascade`/`set-null`/`no-action`), and the
**target scope** (same-module / cross-module / Core — see § below, which decides
how the FK is realized). You auto-deduce relationships from entity attributes,
use cases and rules — never from domain knowledge — and present the complete map
for review.

## How to deduce (closed sources only)

- **Names/attributes** — `OrderLine` belongs to `Order` → N:1, FK `OrderId`; a
  "department" concept on Employee → N:1 to Department.
- **Use cases** — "the manager approves the leave request" → LeaveRequest N:1
  Employee (approver); "the order contains multiple lines" → Order 1:N OrderLine.
- **Business rules** — "cannot delete a department with employees" → `restrict`
  on Department→Employee; "when an order is cancelled, lines are soft-deleted" →
  `cascade` on Order→OrderLine; "if a ticket has no assignee, route to pool" →
  nullable assignee, `set-null`.

**NEVER from domain knowledge.** Hierarchical-tree, "manager-of", "category-of",
"type-of" reflexes are not sources: if no module UC/BR names the relationship,
defer to the upstream phase. Pick `restrict` only when a BR says deletion is
blocked; otherwise surface the gap.

## Cardinality

| Cardinality | Meaning | FK lives on |
|-------------|---------|-------------|
| `1:1` | each A ↔ exactly one B | the dependent side |
| `1:N` | each A has many B; each B one A | on B (`AId`) |
| `N:1` | each A has one B; each B many A | on A (`BId`) |
| `N:M` | each A many B and vice-versa | junction table (composite key) |

Declare the **owning side** (the one with the FK column); the inverse navigation
is auto-created. An `N:M` without payload (a pure tag cloud, e.g. Employee↔Skill)
is declared on one side; the junction table is generated automatically. If the
junction carries business meaning (e.g. `Enrollment` between Student and Course
with a grade and a completedAt), model it as a **separate entity** with two `N:1`
relationships instead of an `N:M`.

## Required & cascade

- **required** — `true` when a BR says "must exist" / "cannot be null"; else
  `false`. Self-references are usually `false` (root nodes exist).
- **cascade** — `cascade` when children can't exist without the parent
  (`Order`→`OrderLine`); `restrict` when a BR blocks deletion
  (`Department`→`Employee`); `set-null` to detach (requires nullable FK,
  `Ticket`→`Assignee`); `no-action` for self-references (SQL can't cascade a
  cycle). **Default `restrict`.** A self-referencing hierarchy (org chart,
  category tree) uses `N:1` self-ref, FK `{Role}Id` nullable, cascade
  `no-action`.

## Cross-table references — DESIGN RULE (load-bearing)

**A reference to another table is ALWAYS a real foreign-key constraint.** A bare
`Guid` with no FK violates referential integrity and is forbidden. What changes
across scopes is only **how** the constraint is realized (and whether a navigation
property exists) — never **whether** it exists. The generated app is ONE physical
database with two schemas (`core` from the SmartStack NuGet, `extensions` for
client modules), so a cross-schema FK is always possible.

| Target lives in… | `targetScope` | Realization |
|------------------|---------------|-------------|
| Same module | `same-module` | FK + **reference navigation** property. |
| Another module / another app (schema `extensions`) | `cross-module` | FK to the real target type, **NO navigation** (decoupled across modules; same DbContext). |
| SmartStack Core, **V1 whitelist** (User, Role, Tenant, TenantOrganisation, Department, JobTitle, Office, Language, Group) | `core` | FK + **reference navigation** property resolved against `SmartStackExtensionDbContext` (the base class your `ExtensionsDbContext` inherits). Real cross-schema FK without owning or recreating the Core table. |
| SmartStack Core, **outside the V1 whitelist** (sessions, tokens, navigation, permissions, AI/Workflow/Support internals, audit logs, …) | n/a | **Rejected** by `scaffold-entity`. Use `ICoreDataService` lookup helpers, or propose adding the entity to the whitelist per `docs/extensions/whitelist-evolution.md`. |

The historical rule ("model cross-module/Core as a plain `Guid`, no relationship")
was WRONG — it confused "no navigation property" (true for `cross-module`: two
modules don't share entity classes) with "no FK constraint" (false: same database
→ the constraint is emitted in the migration). Since v3.55 the **V1 whitelist
Core entities ALSO carry a navigation property** (`SmartStackExtensionDbContext`
maps them via `ExcludeFromMigrations`), enabling `.Include(e => e.User)` on
extensions. `scaffold-entity` emits the appropriate shape from the `relations[]`
spec (`targetScope`).

**In `entité.md`, EVERY such FK is a `Relations` line entry** carrying its scope —
no longer a plain attribute row. Record the scope and the cascade, e.g.:
- `Opportunity *→1 Contact — FK ContactId, scope same-module, onDelete restrict`
- `Invoice *→1 Client — FK ClientId, scope cross-module (CRM/CLIENTS), onDelete restrict`
- `Order *→1 Tenant — FK TenantId, scope core, onDelete restrict`
- `Employee *→1 User — FK UserId, scope core, onDelete restrict`
- `Employee *→1 TenantOrganisation — FK OrganisationId, scope core, onDelete restrict`

### The ONLY exception — identity/audit columns (allowlist)

Mirroring SmartStack.app, a small set of identity/audit columns stay a **plain
`Guid` (indexed, NO FK)**: `CreatedByUserId`, `UpdatedByUserId`,
`ModifiedByUserId`, `DeletedByUserId`, `ChangedByUserId`, `ApprovedByUserId`,
`AssignedToUserId`, and the bare join `UserId`. Reason: the referenced user may be
soft-deleted (audit rows must survive) or synced from an external identity
provider, so a hard FK would break deletes/inserts. These are the documented
allowlist (`lib/fk-allowlist.ts`) — **`TenantId` is NOT among them: it MUST be a
real FK.** Such an allowlisted column appears as a normal attribute row (type
`Guid`), NOT in the `Relations` line, with a description noting it is an
audit/identity id.

## Quality checks (per entity)

- **Every `*Id` attribute that names another table is a `Relations` entry** with a
  `targetScope` — NOT a bare `Guid` (the only exception is the identity/audit
  allowlist above).
- Each relationship carries a scope: `same-module` (default), `cross-module`, or
  `core` (with the physical Core table named, e.g. `tenant_Tenants`).
- Cross-module / core relationships are owning (`N:1` / `1:1`) — a collection
  cannot point outside the module.
- No duplicate relationship name (or duplicate FK column) within an entity.
- `required: true` is consistent with the FK being non-nullable (cannot pair
  `required: true` with `set-null`).
- Self-references use `no-action`.
- `N:M` is either payload-free or already promoted to a separate junction entity.

After relationships, Write `entité.md`.