# SPECIFICATIONS/04_DATABASE/04_RELATIONSHIPS_AND_CONSTRAINTS.md

**PURPOSE:** Defines the database-level relationship rules (ON DELETE behavior) and describes the conceptual relationships between entities. This file answers "how are tables connected at the database level?" — it contains zero application-level relationship code.

> **Table schemas and column types:** `01_TABLES.md`
> **Application-level relationship and scope code:** `../../CONSTITUTIONS/04_DATABASE_COMMUNICATION.md`
> **Entity inference logic (how roles are determined):** `../05_ALGORITHMS/01_ENTITY_INFERENCE.md`

---

## ON DELETE Rules

### Complete Matrix

| Source Table | Source Column | Target Table | ON DELETE | Rationale |
|:---|:---|:---|:---|:---|
| `contracts` | `customer_id` | `clients` | `RESTRICT` | Clients are immutable — see BR-001-1 |
| `contracts` | `agent_id` | `clients` | `RESTRICT` | Clients are immutable — see BR-001-1 |
| `installments` | `contract_id` | `contracts` | `CASCADE` | Draft deletion removes installments — see BR-002-6 |
| `payments` | `contract_id` | `contracts` | `CASCADE` | Draft deletion removes payments — see BR-002-6 |
| `payment_audit_logs` | `contract_id` | `contracts` | `CASCADE` | Draft deletion removes audit logs — no history for cancelled drafts |
| `payment_audit_logs` | `payment_id` | `payments` | `SET NULL` | Payment deletion preserves audit narrative — `payment_id` becomes null but `narrative_text` survives |
| `payments` | `installment_id` | `installments` | `SET NULL` | Installment deletion (draft cascade) nullifies the link without breaking payment records |
| `client_shares_logs` | `client_id` | `clients` | `RESTRICT` | Clients are immutable — see BR-001-1 |

### Key Design Decisions

**Why CASCADE on `payment_audit_logs.contract_id` but SET NULL on `payment_audit_logs.payment_id`?**

- **Contract CASCADE:** Cancelled drafts leave no trace — the entire contract and everything in it is removed. There is no business value in keeping audit logs for a draft that never became real.
- **Payment SET NULL:** When a payment is deleted (LIFO), the audit log must survive because it records what happened. The `narrative_text` is self-contained (see BR-004-9), so it does not need the payment link after deletion. The link becomes null rather than causing the audit log to be deleted.

> **Decision record:** `../03_ARCHITECTURE/04_DECISION_RECORDS.md` → ADR-029

---

## Conceptual Relationships

### Client → Contracts
A client can appear as **customer** in many contracts and as **agent** in many contracts. These are two separate relationship paths from the same source table, distinguished by the foreign key column (`customer_id` vs `agent_id`).

### Contract → Installments
A contract has many installments. Installments are created in bulk at contract creation and are immutable after that (only their status changes).

### Contract → Payments
A contract has many payments of all types (`company_payout`, `initial_payment`, `installment_payment`).

### Installment ← Payments
An installment may be linked to at most one payment (via `payments.installment_id`). Only `installment_payment` type payments have this link. The link is the **sole source of truth** for which payment settled which installment (see BR-004-2).

### Payment → Audit Logs
A payment may have many audit log entries (create, update, delete actions). When a payment is deleted, the `payment_id` in audit logs becomes null — the logs survive with their self-contained narrative text.

### Client → Shares Logs
A client (who has investor status) has many share log entries. These track the full history of share additions, withdrawals, modifications, and logical deletions.

### Notification Templates
Standalone entity — no foreign key relationships to any other table.