Skip to main content

​
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:
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.