PostgreSQL schema — CX Orbit (T2.1)
Physical model for PostgreSQL 16 via Drizzle ORM + postgres.js (ADR 0001). Conceptual entities: DATA-MODEL.md. Envelope + quarantine fields: SDK-AND-DATA-INGESTION.md §11.7. Idempotency: T0.4 (project_id + event_id transport; project_id + source + source_event_id canonical).
Code: packages/db (@cx-orbit/db). Migrations: packages/db/drizzle/.
Write ingress (non-negotiable)
| Writer | Allowed |
|---|---|
apps/collector |
Sole writer of event_ingestion_request, raw_event, event_quarantine, event_dedup_*, canonical_event |
apps/web (later) |
Read all; write operational action / insight / staff auth (T2.10) |
@cx-orbit/node / browser SDKs |
Never import @cx-orbit/db or DATABASE_URL. HTTPS POST to collector only |
Replay of quarantine is an Orbit ops action through the collector ingest path, not an SDK API and not a direct table write from Simiaroom.
Table catalog vs DATA-MODEL
| DATA-MODEL entity / §10 table | Physical table | Layer |
|---|---|---|
RawEvent / raw_event |
raw_event |
Raw (immutable) |
event_ingestion_request |
event_ingestion_request |
Raw (request audit) |
EventQuarantine / event_quarantine |
event_quarantine |
Quarantine (restricted) |
event_deduplication |
event_dedup_transport + event_dedup_canonical |
Dedup (T0.4 two keys) |
| Event | canonical_event |
Canonical log (append-only) |
| Client (Customer) + derived profile §4 | client |
Current state (rebuildable) |
| Therapist (Provider) | therapist |
Current state (rebuildable) |
| Session + analytics facts §5 | session |
Current state (rebuildable) |
| Booking | booking |
Current state (rebuildable) |
| Payment | payment |
Current state (rebuildable) |
| Assessment | assessment |
Current state (rebuildable) |
| FeedbackResponse | feedback_response |
Current state (import/events) |
| IdentityLink | identity_link |
Canonical / T2.3 job |
| Insight | insight |
Operational (semi-manual) |
| Action §7 | action |
Operational (human) |
| RiskSnapshot | risk_snapshot |
Operational audit (append-only) |
import_batch / import_row §8 |
import_batch, import_row |
Operational (T2.8) |
source_reconciliation_run |
source_reconciliation_run |
Operational (T2.2c / T2.11) |
Generic TypeScript aliases: customer → client, provider → therapist, interaction → session. SQL names match DATA-MODEL for Test Agent diffs.
Staff users/sessions are T2.10 (Auth.js), not this migration.
Raw ingest
raw_event stores the full accepted envelope (envelope jsonb) plus collector metadata: received_at, write_key_id (identifier, never the secret), collector_version, validation_result, denormalized identity/entity keys for timeline indexes.
Transport uniqueness: unique (project_id, event_id) on raw_event and event_dedup_transport — one raw row per SDK retry of the same envelope.
Quarantine (§11.7)
event_quarantine columns: quarantine_id, request_id, project_id, environment, received_at, code, retry_eligible, replay_status, sanitized_payload, source, event_id, canonical_type, source_event_id, sdk_name, sdk_version, path, message, collector_version, write_key_id, plus replay audit (replayed_at, replay_request_id, replay_event_id, discarded_*).
Invalid events never enter canonical_event. Retention default 90 days (VOLUME-AND-RETENTION.md §4). Rows are not deleted on successful replay.
Canonical P0
canonical_event is the append-only fact log. Unique (project_id, source, source_event_id). First accepted envelope wins; do not overwrite — correct with a compensating event (new source_event_id).
Current-state tables (client, therapist, session, booking, payment, assessment) are projections for Client 360 / actions. Mapper upserts land in T2.2 / T2.9; T2.1 provides the tables.
Indexes (T0.7 §7.1 + T3.11)
B-tree on event time and timeline lookups:
raw_event/canonical_event:(occurred_at),(user_id, occurred_at),(canonical_type, occurred_at)canonical_event:(entity_type, entity_id, occurred_at)
T3.11 serving indexes (migration 0001_wild_machine_man):
canonical_event_project_user_occurred_idx(project_id, user_id, occurred_at)— Client 360 timelinecanonical_event_project_type_occurred_idx(project_id, canonical_type, occurred_at)— funnel type filtersclient_risk_gap_idx(project_id, risk_level, days_since_last_session)— at-risk listsession_completed_number_idx(project_id, session_number)WHEREstatus = 'completed'— session-number retention
Serving materialized views (rebuildable; refresh off the request path): mv_client_journey_reach, mv_retention_continue. SQL: packages/db/sql/serving/.
Monthly RANGE partitions on occurred_at remain deferred after T3.11. Scenario A proxy (~150k canonical events / 5k clients / 30-day window) met VOLUME-AND-RETENTION.md §6 p95 without partitioning. Uniqueness for T0.4 keys lives on dedicated dedup tables so partitions can be added later. Revisit at T4.1 live volume / Scenario B.
Rebuild boundary
What can be recomputed from raw_event (plus documented jobs) vs what is not a projection of the event log.
| Object | Rebuildable from raw? | Notes |
|---|---|---|
raw_event, event_ingestion_request |
No — they are the collection record | Immutable; archive after 36 months (T0.7) |
event_quarantine |
No | Restricted debug store; not an analytical source |
canonical_event |
Yes | Replay accepted raw through collector mapping; first-wins per canonical key |
event_dedup_transport / event_dedup_canonical |
Yes | Rebuilt with canonical mapping |
client derived profile (§4) |
Yes | T2.9 SYS-01 recompute from canonical history |
session numbers / gaps |
Yes | T2.4 from session events |
booking, payment, assessment, therapist current state |
Yes | Upsert from canonical P0 types; compensating events, not silent overwrite |
identity_link |
Yes (job) | T2.3 rules + window; not guessed at ingest |
risk_snapshot |
Partial | A new snapshot can be recomputed (T3.4); historical rows stay as explainability audit |
action, insight |
No | Human/ops artifacts; not event projections |
import_batch / import_row |
No | External file audit; replay is re-import (T2.8). Job: apps/import-job. Events (if any) go through collector, not these tables. |
source_reconciliation_run |
No | Run history; can be re-executed but old runs are audit |
| Serving MVs / funnel aggregates | Yes | mv_client_journey_reach, mv_retention_continue; refresh from canonical_event / session (T3.11) |
Rule: derived and serving models are rebuildable; raw + quarantine + human actions are not. Invalid payloads never silently enter canonical metrics.
Map path (stub vs T2.2)
packages/db/src/mapping.ts:
planCanonicalMapping— extracts T0.4 keys + entity projection from an envelope (implemented).applyCanonicalMapping— insertscanonical_event+ both dedup rows for an existingraw_event(schema path for tests).
TODO(T2.2): collector HTTP persist after validation.
TODO(T2.2c): duplicate/conflict matrix.
TODO(T2.9): upsert current-state entity tables.
Apply migrations
docker compose up -d postgres
pnpm --filter @cx-orbit/db migrate
Empty database: docker compose down -v && docker compose up -d postgres then migrate.
Regenerate SQL after schema edits: pnpm --filter @cx-orbit/db generate.