#Data Model — CX Orbit MVP

Status: T1.2 implement lock 2026-08-12 (in-repo review; named eng+data sign-off residual — §13).
Physical DDL: SCHEMA.md / packages/db. This doc is the conceptual contract. SQL names match this file so Test Agent can diff.
Field class (evented / import / manual): LIVE-VS-PERIODIC-FIELDS.md (T1.9).
Porsline form catalog / join keys: PORSLINE-FORMS.md (T1.5).
Role × screen × privacy class: PRIVACY-ACCESS-MATRIX.md (T1.11).
Browser consent / ID storage / send-minimization: BROWSER-CONSENT-POLICY.md (T1.14; SDK is T2.13).

Generic naming in parentheses supports future Pedida reuse. Every P0 decision field has a locked name and type. Capacity: this is not a CRM — see §12.

#0. Generic aliases (customer / provider / interaction)

Simiaroom product nouns are the SQL and serving names. Generic aliases exist for Pedida reuse and TypeScript imports. They are the same tables, not extra entities.

Rules:

• Docs, APIs, and fixtures use Simiaroom names (client_id, therapist_id, session_id).
• Code may import customer / provider / interaction; they must not create parallel tables.
• interaction always means session (recurring delivered work), never booking, payment, or a support ticket.
• Identity is anonymous_id ↔ user_id on events + identity_link. There is no generic contact / account table.

#1. Core entities

Key fields below are the locked P0 set. Serving metadata project_id, created_at, updated_at apply to every current-state / operational table unless noted. Enums are closed.

Not core entities (deliberate): support ticket, CRM contact/company, opportunity, full case-management thread. Ops extras land as import_row on feed ops_support_ticket (T2.8) or as Orbit action rows (T3.8).

Closed enums used by P0 serving:

#2. Relationship sketch

erDiagram
  CLIENT ||--o{ SESSION : has
  THERAPIST ||--o{ SESSION : delivers
  CLIENT ||--o{ BOOKING : makes
  CLIENT ||--o{ PAYMENT : pays
  CLIENT ||--o{ ASSESSMENT : completes
  CLIENT ||--o{ FEEDBACK_RESPONSE : submits
  CLIENT ||--o{ ACTION : targeted_by
  CLIENT ||--o{ RISK_SNAPSHOT : scored_as
  CLIENT ||--o{ IDENTITY_LINK : resolves
  CLIENT ||--o{ CANONICAL_EVENT : emits
  SESSION ||--o| FEEDBACK_RESPONSE : triggers
  INSIGHT ||--o{ ACTION : spawns
  RAW_EVENT ||--o| CANONICAL_EVENT : maps_to
  IMPORT_BATCH ||--o{ IMPORT_ROW : contains

Timeline (T2.10) is a read of canonical_event (+ identity overlay), not a separate table. Import rows never become a ticket entity.

#3. Journey stages (MVP)

Canonical machine (funnel display + client.current_stage): JOURNEY-STAGE-MACHINE.md (T1.4). Aligns with T3.2 /journey and T2.9 profile replay.

Linear spine (conversion table, first-reach — not current-stage buckets):

identified → assessed → matched → booked → paid → in_treatment

Non-linear branches (not conversion denominators): paused, returned_from_pause (resume, not a stage id), completed, churned.

Initial serving value: unknown (not a funnel column). Product refs J-01…J-12 collapse onto this machine; they are not extra funnel stages. Gap analysis (exists/partial/missing): JOURNEY-GAP-ANALYSIS.md (T1.1).

#4. Derived client profile (must recompute)

T2.9 SYS-01 writes the serving row on client. T2.10 Client 360 and T3.4 risk_v1 read these fields (plus event history for signals). Rebuildable from canonical_event.

Rules, thresholds, and suppressions: RISK-RULES-V1.md. Engine: packages/profile evaluateRisk.

Risk / continuum inputs that are not client columns (locked locations, not unspecified):

Global flags that suppress naive churn:

• Declared pause should suppress At-Risk/Churn unless the pause is expired or a non-gap signal remains
• Completion must not be counted as churn

#4.1 Client 360 timeline serving (T2.10)

Not an entity. Chronological read of canonical events for one client_id, sorted by occurred_at then source_event_id.

Payments / feedback / actions on 360 are joins to payment, feedback_response, and action. Empty arrays are valid (empty.payments / empty.feedback / empty.actions). Missing feedback must not block timeline or at-risk.

#5. Session analytics facts

T2.4 packages/session-continuum projects these onto session (rebuildable). Store per completed / cancelled / no-show spine row:

CON-S01 unplanned_gap_detected is an Orbit-derived event (not a session column): cadence or pause-return breach, suppressed by active pause and by treatment_completed. Idempotency unplanned_gap:{user_id}:{gap_period_start}.

#6. Feedback schema (MVP)

Form list, triggers, and join keys: PORSLINE-FORMS.md (T1.5). form_type values: baseline, session_feedback, session_feedback_deep, therapist_change_reason, cancel_reason, stop_reason, gap_reason, payment_fail_reason.

Numeric (example dimensions):

• Session satisfaction
• Comfort with therapist
• Feeling understood
• Relationship quality
• Booking / payment / support quality (where asked)
• Intent to continue

Text:

• Free-text comment
• Imported category (rule/keyword/LLM-light): matching, fit, payment, scheduling, price, support, understanding, pathway clarity

P0: scores may feed SIG-FDB-* when present; missing feedback does not fire signals and does not block Client 360.

#7. Action + insight schemas

#7.1 Action (P0 — T3.8)

Light operational follow-up. Human-authored; not rebuildable from the event log. API: apps/web /api/actions + /api/at-risk/assign|close. Physical table action.

At-risk loop: one open follow-up per client (open or in_progress); assign reuses it; close requires an open row. Same row appears on Client 360 actions[]. Close also requires a resolution-domain reason code from the SET-06 library (/settings/reason-codes). Pause / churn / change / cancellation codes are categorized in the same library; they are not substitutes for the T3.8 four outcomes. Retire is soft-archive (no delete). This is P2 SET-06 — it does not claim the P0 decision loop (T3.4+T3.8).

Canonical INT-01 / INT-03 / INT-09 remain evented (Orbit → collector). T3.8 does not yet emit them; that is a residual, not a missing field.

Field ingest class: LIVE-VS-PERIODIC-FIELDS.md (T1.9).

#7.2 Insight (P1 — T3.7 shipped)

Semi-manual analysis output. API/UI: apps/web /insights + /api/insights. Physical table insight exists. T3.10 one-click sets action.source=insight + linked_insight_id. Not a P0 decision-loop entity. Do not treat empty insights as a Client 360 blocker. Serving store is in-memory (same residual class as T3.8 actions).

Templates (METRICS-AND-RISK §12): drop-off spike, churn peak, therapist change-request, recurring negative theme (T3.6 counts as evidence), unplanned gaps.

Out of P0 / later: standalone Actions page, Postgres persist of insight/action rows.

#8. Import batch audit entities

Keep raw import batches auditable. T2.8 job (packages/import, apps/import-job) fills these shapes for the P0 supplemental feed ops_support_ticket. Postgres INSERT of audit rows is deferred; persist shapes already match T2.1 columns.

#8.1 `import_batch`

Report-only (not table columns): batch_status (succeeded | partial | failed), on_error (continue | fail), collector-plan counts. Replay is a new batch, not an in-place edit.

#8.2 `import_row`

Failed rows are quarantined with error_reason. If a row maps to an event (resolved ticket with case fields → internal_case_resolved), the job POSTs the collector — it does not write canonical_event. Operator contract: ops/CSV-IMPORT.md.

Open / in-progress tickets stay audit-only. They are not a ticket entity and not an action unless staff create one in Orbit.

#9. MVP storage principles

1. Append-only event log + mutable derived profile
2. Two idempotency keys (T0.4): transport project_id + event_id (SDK retry); canonical project_id + source + source_event_id (business fact). First accepted envelope wins; do not overwrite — correct with a compensating event
3. Never overwrite historical session/payment facts silently — correct via compensating events
4. Version risk rules; store version on each risk snapshot and on client.risk_rule_version
5. Preserve occurred_at, SDK sent_at, and collector received_at for late-event analysis
6. Separate raw, canonical, derived, and serving models; derived state is rebuildable from history
7. PostgreSQL owns operational state, actions, permissions, and configuration
8. Use an analytical store for event-heavy funnels/cohorts; adopt ClickHouse only from measured volume/latency needs
9. Invalid events go to restricted quarantine and never silently enter canonical metrics. Pilot visibility and go-live rules: DATA-QUALITY-BAR.md (T1.10).
10. Direct identifiers and sensitive ORS/SRS/free-text data require restricted storage and minimal analytical exposure. Role × screen grants: PRIVACY-ACCESS-MATRIX.md (T1.11). restricted and financial are deny for support except operational payment status; enforcement is T5.6.
11. Collector is the only event write ingress; SDKs never write storage. Import jobs and Actions APIs must not INSERT canonical_event

#10. Event delivery support tables

• event_ingestion_request — request_id, project_id, source, received_at, accepted_count / duplicate_count / rejected_count / quarantined_count, write_key_id, collector_version
• raw_event — immutable accepted envelope and collector metadata; unique (project_id, event_id)
• event_quarantine — sanitized invalid payload, error codes, retry eligibility, replay audit; field catalog and replay path in SDK-AND-DATA-INGESTION.md §11.7
• event_deduplication — implemented as event_dedup_transport (project_id + event_id) + event_dedup_canonical (project_id + source + source_event_id)
• source_reconciliation_run — source, window_start / window_end, expected_count / observed_count / mismatch_count, mismatches, ran_at
• risk_snapshot — append-only explainable risk decisions by rule version (T3.4). New row on recompute; historical rows stay

If the authoritative Simiaroom backend cannot guarantee reliable post-commit publication, use the transactional outbox in the source system (ops/OUTBOX-RELAY.md, T2.14) rather than making the business transaction depend on the CX Orbit collector.

Physical PostgreSQL tables, T0.4 unique keys, and the rebuild boundary (what can be recomputed from raw_event) are in SCHEMA.md (T2.1).

#11. Rebuild boundary (T2.1)

• Not rebuildable: raw_event, event_ingestion_request, event_quarantine, human action / insight, import audit, reconciliation run history.
• Rebuildable from accepted raw: canonical_event, dedup keys, current-state client / session / booking / payment / assessment / therapist, identity_link (T2.3 job), serving aggregates.
• Risk snapshots: append-only audit; a new snapshot can be recomputed, historical rows are not rewritten.
• Write ingress: collector only for event tables; SDKs never write storage.

Full table: SCHEMA.md.

#12. Capacity — what this model is not

P0 decision loop needs timeline, sessions, risk, and actions. Do not grow CRM objects:

#13. In-repo review (T1.2)

This is the eng+data review evidence, not a fabricated stakeholder sign-off.

Cross-checked in this pass (pointers, not a Test Agent result):

Approve Agent may still require named humans. This pack must not be treated as that sign-off.