> ## Documentation Index
> Fetch the complete documentation index at: https://internal.softcrum.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Standard — Data (v2, major update for Engage/CRM)

> Scope: every table, migration, and query in the monorepo. References: DEC-A, DEC-B.

## 1. Schema organization

* One Postgres schema per bounded context: `identity`, `core`, `loyalty`, `messaging`, `crm` (plus existing suite schemas). Drizzle: `pgSchema('<name>')`.
* Every tenant-scoped table carries `tenant_id` + `cell_id`; tenant guards + RLS mandatory; DB access ONLY via `getDb(tenantCtx)` (unchanged rules).
* Cross-schema FKs allowed only toward `core`. `loyalty`/`messaging`/`crm` NEVER reference each other's tables. `identity` has **no** cross-schema FKs at all: it is a foundation nothing else reads directly (ADR-022).

## 2. Parametric tables (business enums)

* NEVER Postgres enums for business values. Every closed set lives in `{schema}.{entity}_types` (or `core.*` if truly cross-cutting: currencies, notification\_channels, national\_id\_types).
* Standard columns: `code` TEXT PK (stable business key), `label`, `is_system` BOOL, `is_active` BOOL, `sort_order`, `metadata` JSONB.
* FKs reference `code` (text). Catalogs are cached (process memory + Redis); hot paths MUST NOT join catalogs.
* `is_system = true` rows are seeded in migrations and generate the Zod/TS union types in packages/core (single source).
* Adding a row does NOT add behavior: APIs reject codes not yet supported by business logic with a typed error (`UNSUPPORTED_CODE`).
* Tenant-extensible catalogs: ONLY where the value is tenant business taxonomy (`crm.activity_types`, `loyalty.reward_types`). NEVER where the value drives a state machine or system logic (ledger transaction types, send statuses, redemption states).
* Anti-example: adding `'bonus'` to `loyalty.ledger_transaction_types` via SQL and expecting the ledger to handle it. WRONG — requires code + spec update; the API must reject it until then.

## 2b. Uniqueness is per tenant, never global

**Every uniqueness constraint on tenant-scoped data includes `tenant_id`.** There is no natural key
in this platform that is unique across the database.

The case that fixes the rule: doña María is a customer of company A and of company B. Both run a
Softcrum program, both create her as a contact and both may give her a member login. Those are
**two entirely separate people as far as the system is concerned** — separate rows, separate
credentials, separate consent, separate points, and neither tenant may ever learn that the other
one exists. A globally unique email would break that on the first collision, and worse, it would
turn "invite this email" into an oracle that reveals whether that person is a customer of someone
else.

Applies to: `core.contacts.email`, `core.contacts.national_id`, member credentials,
`identity.users.email`, and any future natural key. The index is partial and tenant-scoped:

```sql theme={null}
UNIQUE (tenant_id, email)          -- correct
UNIQUE (email)                     -- FORBIDDEN on tenant-scoped data
```

Consequence accepted deliberately: the same person holding accounts with two tenants maintains two
sets of credentials. Linking them would require a cross-tenant identity, which is precisely the
thing this rule exists to prevent.

## 3. Money

* Monetary amounts: `amount` BIGINT in minor units + `currency_code` CHAR(3) FK `core.currencies` (ISO 4217). NEVER floats. NEVER an amount column without its currency column.
* F1: single currency per tenant. `tenant.base_currency` is IMMUTABLE after creation (change only via assisted migration process). Multi-currency + FX normalization deferred to F2 (future ADR).
* Points are NOT money: `points_amount` INTEGER + FK `loyalty.point_currencies`. Mixing points and money in one column is forbidden.

## 4. Partitioning (by criterion, not by default)

* Partition (RANGE, monthly, on the time column) ONLY tables that are append-only AND high-volume or retention-managed. Designated v1: `core.tracked_events`, `messaging.sends`, `messaging.send_status_history`, `core.audit_log`, `core.usage_snapshots`. Candidates pending real data: outbox, `loyalty.ledger_transactions`.
* Implementation: DDL in custom SQL migrations (Drizzle does not manage partitions declaratively). A cron job pre-creates partitions N+2 months ahead; a DEFAULT partition exists and alerts if it ever receives rows. pg\_partman: pending spike; adopt if available on Supabase.
* Every query against a partitioned table MUST include the partition key predicate (pruning). Anti-example: `SELECT * FROM core.tracked_events WHERE contact_id = $1` — WRONG; add `AND occurred_at >= …`.

## 5. Retention & export

* Detail retention by plan: 13 months (starter) / 25 months (pro) / 37+ months (enterprise) for `tracked_events` and `sends`.
* Before dropping a partition: export to Supabase Storage as compressed NDJSON under `exports/{tenant}/{table}/{yyyymm}.ndjson.gz`; aggregates are kept forever.
* Deletion rights (Ley 21.719): profile + events are deleted; ledger rows are ANONYMIZED (contact reference nulled to a tombstone), never destroyed (accounting integrity).

## 6. Data classification & national\_id

* Levels: public / internal / personal / sensitive. Controls per level defined here; the STRICTEST applicable law across supported countries governs (floor: Ley 21.719 + LGPD).
* `contacts.national_id` (+ `national_id_type` FK `core.national_id_types`): normalized before persist (per-type normalizer), format regex enforced, check digit validated where an algorithm exists (`dv_validated` flag).
* Uniqueness: partial unique index `(tenant_id, national_id_type, national_id) WHERE national_id IS NOT NULL`. Same id across different tenants is always allowed.
* Sensitive handling: masked by default in every UI/API response; full value requires dedicated permission `core.contacts.read_national_id`; every full-value access writes to `core.audit_log`.
* `contact_kind` = `person | company`. Company contacts: `legal_name`, no `birth_date`; date-property triggers apply per kind.

## 7. Audit (CQRS-lite)

* Every command handler: ONE transaction = state change + outbox event(s) + `core.audit_log` row (actor typed user/member/api\_key/system, entity, action, full old→new diff, correlation\_id, occurred\_at).
* Read models are projections; each projection documents its rebuild procedure.
* Event sourcing as system of record: FORBIDDEN as a general pattern (ADR-17). Append-only domains (ledger, metering, audit) already provide it where it pays.