# Canonical Data Dictionary

All domain tables use unsigned integer primary keys, timestamps, and `organization_id` unless noted. Monetary values use `DECIMAL(18,2)` plus an ISO-style three-character currency code. JSON is reserved for non-critical extension metadata; authoritative relationships and reporting fields are relational.

Version 2 uses one base currency per organization so totals are never silently added across currencies. The code is stored on every monetary record for provenance and future explicit multi-currency expansion. Base currency becomes immutable when the first commercial or financial record is created.

| Entity | Meaning | Critical fields / invariants |
|---|---|---|
| `organizations` | Tenant and operating identity | unique slug, timezone, currency, default locale, mission, north star, proof workflow |
| `users` | Human identity | unique email, password hash, timezone, locale; authority comes from membership |
| `organization_user` | Membership and role | organization/user unique, role, title, active flag |
| `actors` | Client, prospect, partner, vendor, or person | type, generated code, relationship stage, owner; unique code inside organization |
| `interactions` | Relationship contact history | actor, optional opportunity/project, user, type, occurrence, next action/date |
| `opportunities` | Governed potential work | actor, stage/status, value, probability, currency, expected close, next step |
| `proposals` | Immutable commercial version | opportunity/version unique, amount/currency, status, validity, scope, terms, accepted timestamp |
| `projects` | Authorized delivery identity | actor, optional source opportunity/cycle, manager, status, health, dates, budget, definition of done |
| `milestones` | Ordered delivery/acceptance point | project, owner, status, due date, amount, acceptance criteria, evidence reference |
| `financial_accounts` | Cash/bank/card/wallet source | name/currency unique per organization, type, opening balance, active flag |
| `invoices` | Receivable claim | actor, optional project/opportunity, unique number, status, dates, subtotal, tax, total, currency |
| `financial_transactions` | Cash evidence | source account, optional destination/actor/project/invoice, type, amount/currency, occurrence, clearing status |
| `work_records` | Shared governance record | type, code, lifecycle, owner/approver, source/confidence, dates, outcome learning, optional actor/opportunity/project |
| `record_relationships` | Directed work-record graph | source/target/type unique; self-links forbidden by controller validation |
| `evidence_files` | Private source artifact | record, private path, MIME/size, SHA-256, source authority, capture/validity dates |
| `comments` | Institutional context | record, user, body, timestamp |
| `operating_cycles` | Bounded evidence window | objective, dates, status, active flag |
| `metrics` | KPI contract | purpose, formula, unit, source, target, guardrail, cadence, owner, confidence |
| `metric_measurements` | KPI observation | metric, period, value, status, notes |
| `audit_logs` | Material action history | user, subject type/id, event, before/after JSON, IP, user agent, timestamp |

## Derived values

- Opportunity weighted value = `value × probability ÷ 100`.
- Invoice collected = sum of linked cleared income transactions.
- Invoice outstanding = `max(0, total − collected)`.
- Account balance = opening + cleared income − cleared expenses − cleared transfers out + cleared transfers in.
- Project cash margin = linked cleared income − linked cleared expenses.

Derived values are not independently editable. If a number is wrong, correct the source transaction or lifecycle state with an audited action.