> ## Documentation Index > Fetch the complete documentation index at: https://docs.kaireonai.com/llms.txt > Use this file to discover all available pages before exploring further. # Data Model > Index of all 105 Prisma models in the platform schema — table mappings, key fields, relations, and purpose KaireonAI persists all platform state in 105 Prisma models declared in the platform schema. This page indexes every model by domain, names the underlying PostgreSQL table (via `@@map`), and links each model back to the API or operator surface that owns it. ## What it indexes The platform schema is the single source of truth for every persisted entity. Every API route, cron job, MCP tool, and worker reads or writes one or more of these models. The Tenant model anchors multi-tenancy — most other models carry a `tenantId` column and an index on it. This page exists because operators and contributors need a one-screen overview before they grep for symbols. For per-API request and response shapes, follow the per-domain links in the Reference tables below; this page only documents the persistence layer. ## Quick start ```bash theme={null} # View live models in a browser at localhost:5555 cd platform && npx prisma studio # Format schema in place cd platform && npx prisma format # Sync schema changes to the database (dev only — destructive in production) cd platform && npx prisma db push # Regenerate the Prisma client into the gitignored generated directory cd platform && npx prisma generate ``` The schema file is 2,333 lines long; jump to a model with the editor's go-to-symbol command on the model name. ## How it works ### Prisma 7 lifecycle Every model in the schema maps to one PostgreSQL table via the `@@map("table_name")` directive. The Prisma client generator emits a typed client into a gitignored generated directory; that path is regenerated by `npm run build` (which runs `prisma generate && next build`) and by the `postinstall` hook on `npm install`. The datasource block in the schema declares only the provider — `provider = "postgresql"` (no `url`). The connection URL lives in the Prisma config file and is read from `DATABASE_URL`. This split is required by Prisma 7; setting `url` directly inside the schema errors out with `The datasource property 'url' is no longer supported in schema files`. ### Multi-tenant isolation Every tenant-scoped model carries a `tenantId String` column and at least one composite index that begins with `tenantId`. API routes enforce isolation by adding `where: { tenantId }` on every query through the shared tenant-resolution helper. The Tenant table itself (`tenants`) holds tenant-level settings — `settings` (JSON), `aiAnalyzerSettings` (JSON), `isPlayground` (boolean) — that gate features per tenant. A handful of models — User, audit log rows, Account, Session, the email-verification token table, and the per-tenant region tag — either hold cross-tenant rows or scope by `userId` instead. Each is called out in its row below. ### Soft delete vs hard delete Models with operator-visible "archive" semantics carry a nullable `deletedAt DateTime?` column and an index on `[tenantId, deletedAt]`. API routes filter out soft-deleted rows by default; the audit log captures the soft-delete event. Models with `deletedAt` include **Category**, **SubCategory**, **Channel**, **Placement**, **FlowRoute**, **Offer**, **Creative**, **OutcomeType**, **QualificationRule**, **ContactPolicy**, **DecisionFlow**, **TriggerRule**, **GuardrailRule**, **RankingProfile**, and **SummaryDefinition**. Other tables — the suppression ledger, the transactional outbox, the dead-letter store, pipeline run records, decision traces, and the interaction history fact table — are hard-deleted by retention crons per the per-tenant retention policy data class. ### IR-native pipelines `Pipeline.irVersion` is `String NOT NULL DEFAULT "1.0"` — every pipeline is IR-native after the legacy ETL editor was deleted on 2026-04-28. The full DAG lives in the pipeline IR version row's `ir` JSONB column. The legacy per-node and per-edge tables were removed in the same change; the underlying `pipeline_nodes` and `pipeline_edges` Postgres tables were dropped via the `04_drop_legacy_pipeline_tables.sql` manual SQL script. ## Reference Each subsection groups models by domain. Tables list the model name, the underlying Postgres table (`@@map`), the most operationally relevant fields, primary relations, and a one-line purpose. Schema line numbers below are valid against the schema as of 2026-04-30. ### Decisioning core The studio entities that make up an offer catalog and the four-stage decisioning pipeline (Eligibility / Fit Filters / Match Scoring / Ranking). | Model | `@@map` | Key Fields | Relations | Purpose | | ---------------------- | --------------------------- | --------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Tenant** | `tenants` | `name`, `slug`, `settings`, `aiAnalyzerSettings`, `isPlayground` | Conversation children | Multi-tenant root; per-tenant feature flags live in `settings` JSON (schema line 12). | | **Category** | `categories` | `name`, `customFields`, `ordinal`, `deletedAt` | sub-category and offer children | Top-level offer grouping (e.g. "Cards", "Loans"). Computed-field formulas live in `customFields` (schema line 30). | | **Sub-category** | `sub_categories` | `name`, `categoryId`, `customFields` | parent category, offer children | Child grouping below a category (e.g. "Travel" under "Cards") (schema line 54). | | **Channel** | `channels` | `channelType`, `deliveryMode`, `impressionMode`, `providerConfig`, `fileConfig` | creative and placement children | Delivery channel — email/sms/push/web/whatsapp/etc. `impressionMode` decides whether `/recommend` auto-records impressions (schema line 80). | | **Placement** | `placements` | `slotType`, `maxSlots`, `channelId`, `targeting` | parent channel, creative children | A targeted slot inside a channel (hero, banner, modal). Used by flow-route lookups (schema line 105). | | **Flow route** | `flow_routes` | `channelId`, `placementId`, `decisionFlowId`, `priority` | — | Maps `(channel, placement)` to a decision flow. Cached 120s in the `/recommend` hot path (schema line 127). | | **Offer** | `offers` | `categoryId`, `subCategoryId`, `priority`, `mandatory`, `eligibility`, `budget`, `schedule` | optional category, optional sub-category, creative children, interaction-history rows | Decisioning offer — what the platform recommends. `mandatory=true` bypasses eligibility (schema line 147). | | **Creative** | `creatives` | `offerId`, `channelId`, `placementId`, `templateType`, `content`, `personalization`, `weight` | parent offer, channel, optional placement, optional content item, interaction-history rows | Channel-specific rendering of an offer. Maps to legacy "treatment" table semantically (schema line 194). | | **Outcome type** | `outcome_types` | `key`, `classification`, `category`, `isSystem` | — | Per-tenant taxonomy of outcomes (impression, click, dismiss, convert). System rows are undeletable (schema line 231). | | **Decisioning gate** | `qualification_rules` | `ruleType`, `config`, `priority`, `stage`, `scope` (deprecated) | scope rows | Stages: `eligibility`, `fit`, `match`. The legacy `scope`/`scopeId` columns are kept for back-compat; new code uses `scopes[]` (schema line 360). | | Decisioning gate scope | `qualification_rule_scopes` | `qualificationRuleId`, `scope`, `scopeId` | parent rule | Multi-scope assignment — one rule can apply globally and to specific categories/offers (schema line 387). | | **Contact policy** | `contact_policies` | `ruleType`, `config`, `priority`, `scope` (deprecated) | scope rows | Frequency caps, cooldowns, mutual exclusion. `ruleType` includes `customer_total_cap` (Phase 2B-3) and `offer_category_cap` (schema line 403). | | Contact policy scope | `contact_policy_scopes` | `contactPolicyId`, `scope`, `scopeId` | parent policy | Multi-scope assignment for contact policies (schema line 429). | | **Decision flow** | `decision_flows` | `key`, `draftConfig`, `publishedVersions`, `rankingProfileId`, `isDefault`, `isProtected` | run children | Visual decisioning flow. `draftConfig` holds the working version; `publishedVersions[]` holds immutable snapshots (schema line 765). | | **Ranking profile** | `ranking_profiles` | `key`, `weights`, `status` | — | Multi-objective ranking weights (revenue, margin, propensity, engagement) (schema line 1707). | | Cross-offer constraint | `cross_offer_constraints` | `scope`, `ruleType`, `config`, `status` | — | Multi-offer ranking constraints. Read by the Lagrangian cross-offer ranking helper; threaded into both realtime and batch decisioning paths (schema line 2273). | | **Guardrail rule** | `guardrail_rules` | `key`, `severity`, `expressionAst` | — | Hard/soft filter expressions evaluated alongside decisioning gates (schema line 743). | | Suppression | `suppressions` | `customerId`, `scope`, `policyId`, `expiresAt` | — | Pre-computed contact-policy block per customer × scope. Expired rows cleaned by retention cron (schema line 1883). | | Frequency cap | `frequency_caps` | `scope`, `scopeId`, `maxVolume`, `period`, `currentCount`, `resetAt` | — | System-wide delivery caps on offers, categories, channels. See [Frequency Caps](/decisioning/frequency-caps) (schema line 1842). | | Budget allocation | `budget_allocations` | `scope`, `scopeId`, `periodType`, `maxImpressions`, `maxSpend`, `maxConversions`, `periodKey` | — | Budget tracking with rolling-window counters per scope (global / category / offer) (schema line 1307). | ### Customer & interactions Customer-scoped rows that drive the decisioning loop and the analytics fact tables. | Model | `@@map` | Key Fields | Relations | Purpose | | -------------------------- | ---------------------------- | --------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | | Interaction history | `interaction_history` | `customerId`, `recommendationId`, `offerId`, `creativeId`, `interactionType`, `outcomeTypeKey`, `direction`, `deduplicationId` | optional offer, creative | Append-only fact table for impressions, recommendations, clicks, conversions. Partitioned in production via manual SQL (schema line 255). | | Interaction summary | `interaction_summaries` | `periodType`, `periodKey`, `customerId`, `offerId`, `dimensionKey`, `definitionId`, `impressions`, `positive`, `converts`, `totalValue` | — | Pre-aggregated rollups by `[periodType, periodKey, customerId, offerId, creativeId, channelId]`. Driven by the summary definition table (schema line 304). | | Impression | `impressions` | `customerId`, `offerId`, `creativeId`, `channelId`, `placementId`, `context` | — | Channel-level impression mirror written by `/respond`; the interaction-history table is the canonical record (schema line 1976). | | Customer engagement health | `customer_engagement_health` | `customerId`, `score` (0-1), `inputs`, `computedAt` | — | Nightly-computed engagement score from a 90-day rollup. Read by engagement-aware contact-policy caps (Phase 2C-3) (schema line 344). | | Customer CLV | `customer_clv` | `customerId`, `clvScore`, `predictedRevenue`, `churnProbability`, `rfmRecency`, `rfmFrequency`, `rfmMonetary`, `segment` | — | Per-customer RFM and lifetime-value scores. Segments: `high`, `medium`, `low`, `at_risk` (schema line 2061). | | Identity link | `identity_links` | `canonicalId`, `identifierType`, `identifierValue`, `confidence` | — | Identity resolution graph — maps email/phone/session/device → `canonicalId` per tenant (schema line 1333). | | Segment | `segments` | `baseSchemaId`, `joins`, `filters`, `viewName`, `customerCount` | parent data schema, run children | Definition of an audience over data-schema rows. Materializes to a Postgres view named `seg_{idPrefix}` (schema line 796). | | Consent record | `consent_records` | `subjectId`, `purpose`, `status`, `source`, `grantedAt`, `revokedAt` | — | GDPR/privacy consent ledger per customer × purpose (schema line 1996). | ### Algorithm models ML model registry, versioning, governance, and per-scope adaptive learning state. | Model | `@@map` | Key Fields | Relations | Purpose | | --------------------- | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- | | Algorithm model | `algorithm_models` | `key`, `modelType`, `targetField`, `predictors`, `metrics`, `modelState`, `outcomeWeights`, `registryStatus`, `registryFamily` | model versions, model adaptations, experiments, experiment challengers | The model record. `modelType` is one of `scorecard`, `bayesian`, `logistic_regression`, `gradient_boosted` (schema line 572). | | Model version | `model_versions` | `modelId`, `version`, `config`, `modelState`, `metrics`, `predictors` | parent algorithm model | Immutable historical snapshot of algorithm-model state (schema line 657). | | Model adaptation | `model_adaptations` | `modelId`, `scope`, `scopeId`, `positives`, `negatives`, `positiveRate`, `predictorBins`, `predictorAucs` | parent algorithm model | Per-scope (`global` / `category` / `offer` / `channel`) learned propensity. Updated atomically by online learning loop (schema line 629). | | Model approval | `model_approvals` | `modelId`, `version`, `status`, `requestedBy`, `reviewedBy`, `metrics` | — | Production-promotion governance ticket per model version (schema line 1231). | | Model drift check | `model_drift_checks` | `modelId`, `checkType`, `baseline`, `current`, `driftScore`, `drifted` | — | Periodic drift snapshot. `checkType` is one of `psi`, `ks_test`, `auc_decay`, `feature_drift` (schema line 1249). | | **Experiment** | `experiments` | `key`, `championModelId`, `trafficSplit`, `autoPromote`, `holdoutPercent`, `results` | optional algorithm model, challenger rows | Champion/challenger experiment definition (schema line 676). | | Experiment challenger | `experiment_challengers` | `experimentId`, `modelId`, `trafficPct` | parent experiment, algorithm model | Join row binding a challenger model to an experiment with a traffic percentage (schema line 701). | | Experiment assignment | `experiment_assignments` | `customerId`, `experimentId`, `variant`, `assignedAt` | — | Sticky customer-to-experiment mapping (schema line 1959). | | Variant assignment | `variant_assignments` | `customerId`, `experimentKey`, `variantName`, `assignedAt`, `expiresAt` | — | Variant assignment with optional TTL — used for blueprint/decision-flow A/B tests (schema line 1449). | | ML job result | `ml_job_results` | `jobType`, `status`, `input`, `result`, `error` | — | Async ML job ledger — `policy_analysis`, `segmentation`, `content_analysis` (schema line 1746). | ### Audit & compliance Tamper-evident logs, retention, consent, and DSAR machinery. | Model | `@@map` | Key Fields | Relations | Purpose | | ------------------ | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Audit log | `audit_logs` | `requestId`, `integrityHash`, `prevHash`, `userId`, `action`, `entityType`, `entityId`, `before`, `after`, `entityVersion` | — | Immutable audit chain. `integrityHash = SHA-256(content + prevHash)` builds a tamper-evident chain. API routes block UPDATE/DELETE on this table (schema line 1063). | | Retention config | `retention_configs` | `dataClass`, `retentionDays`, `legalHold` | — | Per-tenant retention policy. `dataClass` includes `interactions`, `decisions`, `metrics`, `audit`. Cleanup cron honors per-tenant overrides (schema line 1690). | | DSAR request | `dsar_requests` | `requestType` (`export`/`delete`/`rectify`), `subjectId`, `subjectType`, `status`, `requestedBy`, `result` | — | Data Subject Access Request workflow. See [DSAR Portability](/governance-security/dsar-portability) (schema line 1286). | | Policy snapshot | `policy_snapshots` | `policyVersionHash`, `interactionId`, `eligibilityPolicies`, `suppressionPolicies`, `guardrailRules` | — | Frozen policy configuration referenced by the decision-trace `policyVersionHash`. One row per unique 16-char hash per tenant (schema line 1269). | | **Decision trace** | `decision_traces` | `requestId`, `customerId`, `candidateCount`, `afterQualification`, `afterContactPolicy`, `qualificationResults`, `scoringResults`, `selectedOffers`, `policyVersionHash`, `degradedScoring` | — | Forensic per-decision trace. Sample rate set by `tenantSettings.decisionTraceSampleRate` (schema line 1604). | ### Governance Approval workflows, four-eyes governance, and operator-driven change control. | Model | `@@map` | Key Fields | Relations | Purpose | | ---------------------- | ------------------------- | --------------------------------------------------------------------------------------------------------------------------------------- | ------------------------ | --------------------------------------------------------------------------------------------------------- | | Approval request | `approval_requests` | `entityType`, `entityId`, `action`, `payload`, `requesterId`, `approverId`, `status` | — | Single-stage approval ticket — gates production changes when four-eyes is enabled (schema line 930). | | Approval request stage | `approval_request_stages` | `approvalRequestId`, `stageName`, `ordinal`, `requiredRole`, `status`, `approverId` | — | Multi-stage four-eyes (W14) — one approval request can require N sequential approvals (schema line 2255). | | Custom role | `custom_roles` | `name`, `permissions` (JSON array of permission strings) | role-assignment children | Tenant-defined role bundles for fine-grained authorization (schema line 1418). | | Custom role assignment | `custom_role_assignments` | `userId`, `roleId` | parent custom role | Join row binding a user to a custom role per tenant (schema line 1434). | | SSO config | `sso_configs` | `provider` (`saml`/`oidc`/`none`), `samlEntityId`, `oidcIssuer`, `defaultRole`, `allowedDomains`, `autoProvision`, `enforceForAllUsers` | — | Per-tenant SSO configuration. One row per tenant (unique on `tenantId`) (schema line 1393). | | WebAuthn credential | `webauthn_credentials` | `userId`, `credentialId`, `publicKey` (CBOR base64), `counter`, `transports`, `deviceLabel` | — | Hardware-backed FIDO2/WebAuthn credentials per user (W18) (schema line 2303). | ### Operator & infra Tables consumed by operators, cron jobs, and out-of-band workers. | Model | `@@map` | Key Fields | Relations | Purpose | | ------------------ | --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | User | `users` | `email`, `password`, `role`, `mfaSecret`, `mfaEnabled`, `mfaBackupCodes`, `lockedUntil`, `failedLoginAttempts`, `onboardingProgress` | account, session, conversation children | NextAuth v5 user. Optional `tenantId` because some users (admins) are cross-tenant (schema line 990). | | Account | `accounts` | `userId`, `provider`, `providerAccountId`, OAuth tokens | parent user | NextAuth v5 OAuth account binding (schema line 1018). | | Session | `sessions` | `sessionToken`, `userId`, `expires` | parent user | NextAuth v5 active sessions (schema line 1039). | | Verification token | `verification_tokens` | `identifier`, `token`, `expires` | — | NextAuth v5 email-verification + magic-link tokens (schema line 1051). | | API key | `api_keys` | `prefix` (first 12 chars displayed), `hashedKey` (SHA-256), `expiresAt`, `lastUsedAt`, `revokedAt` | — | Long-lived API key for `X-API-Key` auth. Raw key starts with `krn_` (schema line 1124). | | OAuth client | `oauth_clients` | `clientId` (`kci_`-prefixed), `hashedSecret`, `scopes` (`read,write,admin`) | — | OAuth2 client credentials grant — server-to-server callers (schema line 1144). | | Tenant settings | `tenant_settings` | `mlWorkerConfig`, `aiAnalyzerSettings`, `flowIrEnabled` (defaults `true`) | — | Per-tenant operational toggles. `aiAnalyzerSettings.ranking` holds EXP3-IX bandit config; `flowIrEnabled` is a kill-switch for pipelines (schema line 1944). | | Platform setting | `platform_settings` | `category`, `key`, `value`, `encrypted` | — | Per-tenant key-value config — SES from-address, Twilio SID, etc. AES-encrypted when `encrypted=true` (schema line 1586). | | Outbox event | `outbox_events` | `topic`, `payload`, `status`, `retryCount`, `maxRetries`, `lastError`, `publishedAt` | — | Transactional outbox for events. Drained by the outbox publisher worker; on max retries the row moves to the dead-letter store (schema line 1162). | | Dead-letter event | `dead_letter_events` | `originalEventId`, `topic`, `payload`, `failedAt`, `errorMessage` | — | Terminal event store for outbox publish failures. Read by the [Operations Runbook](/self-host/architecture/operations) (schema line 1180). | | Channel delivery | `channel_deliveries` | `interactionId`, `channelType`, `providerName`, `providerMessageId`, `idempotencyKey`, `status` (`pending`/`sent`/`delivered`/`bounced`/`failed`/`opened`/`clicked`), `attempts` | — | Per-message delivery tracking with provider IDs and idempotency. Powers retry/bounce handling (schema line 1196). | | Export checkpoint | `export_checkpoints` | `exportType`, `lastExportAt`, `filePath`, `status` | — | Resume point for the Hive interaction-history export job (schema line 1906). | | Alert rule | `alert_rules` | `metric`, `operator`, `threshold`, `windowMinutes`, `cooldownMinutes`, `channels`, `lastFiredAt`, `status` | — | Threshold-based alerts. `channels` is a JSON list of notification destinations (schema line 1466). | | **Run** | `runs` | `decisionFlowId` (mapped to `blueprintId`), `segmentId`, `scheduleType`, `frequencyCaps`, `fileConfig`, `channelIds` | parent decision flow, segment, campaign-run children | Scheduled batch campaign config. Triggers campaign-run rows on each execution (schema line 825). | | Campaign run | `campaign_runs` | `campaignId`, `runNumber`, `status`, `totalCustomers`, `processed`, `recommended`, `summary` | parent run | One execution of a Run (campaign). One row per fired schedule tick (schema line 876). | | Trigger rule | `trigger_rules` | `eventType`, `condition`, `actionType`, `actionConfig`, `priority`, `cooldownMs` | — | Event-driven trigger — when `eventType` matches `condition`, fire `actionType` (schema line 904). | | Simulation run | `simulation_runs` | `segmentId`, `decisionFlowKey` (mapped to `blueprintKey`), `sampleSize`, `config`, `results` | — | What-if simulation snapshot. Re-runs a flow over a segment sample without writing decisions (schema line 955). | | Attribution result | `attribution_results` | `customerId`, `conversionId`, `model`, `touchpoints`, `totalValue` | — | Per-conversion multi-touch attribution result. `model` is the attribution model name (e.g. `linear`, `time_decay`) (schema line 973). | | Geofence | `geofences` | `latitude`, `longitude`, `radiusMeters`, `triggerOn` (`enter`/`exit`/`dwell`), `action` | — | Per-tenant geofence — fires `action.type` (`recommend`/`notify`) when a customer crosses (schema line 2036). | | Tenant region | `tenant_regions` | `tenantId` (PK), `region`, `primary`, `failoverRegion` | — | Per-tenant region tag for active-active routing (W18) (schema line 2292). | ### AI & imports Conversation ledger, AI recommendations, and the AI document import pipeline (V1). | Model | `@@map` | Key Fields | Relations | Purpose | | ----------------------------------- | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | Conversation | `conversations` | `userId`, `title` | tenant, user, message children | One AI assistant conversation per `(tenant, user)` (schema line 1807). | | Conversation message | `conversation_messages` | `conversationId`, `role`, `content`, `toolInvocations` | parent conversation | Single message in a conversation. `toolInvocations` is the tool-call/result trace (schema line 1824). | | AI recommendation | `ai_recommendations` | `type` (`policy`/`rule`/`segment`/`content`), `title`, `payload`, `confidence`, `source` (`llm`/`ml_worker`), `status` | — | Pending AI suggestion that can be applied as an entity (schema line 1726). | | AI customer segment | `customer_segments_ai` | `name`, `filterRules`, `schemaId`, `size`, `percentage`, `characteristics`, `suggestedUse` | — | AI-generated customer segment proposal — separate from the user-defined segment table (schema line 1785). | | Creative performance | `creative_performance` | `creativeId`, `channelId`, `offerId`, `impressions`, `clicks`, `conversions`, `revenue`, `periodStart`, `periodEnd` | — | Pre-aggregated creative-level performance window for AI content analysis (schema line 1764). | | AI attachment (no `@@map`) | (model-name table) | `conversationId`, `originalFilename`, `mimeType`, `sha256`, `storageKey`, `pageCount`, `tokensUsed`, `estimatedTokens`, `status` | — | Uploaded document (PDF/PPTX) for AI document import. Dedup by `[tenantId, sha256]` (schema line 2170). | | AI import proposal (no `@@map`) | (model-name table) | `attachmentId`, `entityType`, `extractedName`, `extractedFields`, `sourcePageNumber`, `sourceQuote`, `dedupeMatchId`, `dedupeStrategy`, `verdict` | — | Typed proposal extracted from an attachment with page citations (schema line 2196). | | AI import apply (no `@@map`) | (model-name table) | `attachmentId`, `conversationId`, `appliedById`, `createdEntityIds`, `mergedEntityIds`, `skippedProposalIds`, `status`, `revertedAt` | — | Atomic apply ledger — records what was created/merged/skipped per import. Soft-revert via `revertedAt` (schema line 2217). | | AI import token ledger (no `@@map`) | (model-name table) | `tenantId`, `yearMonth`, `tokensUsed` | — | Per-tenant per-month LLM token spend for AI document import (schema line 2233). | | AI import skip digest (no `@@map`) | (model-name table) | `digest`, `entityType`, `extractedName` | — | Skip-tally bookkeeping — remembers proposals the operator dismissed so re-imports don't re-surface them (schema line 2243). | | Negotiation session | `negotiation_sessions` | `customerId`, `offerId`, `state`, `status` (`active`/`accepted`/`rejected`/`expired`) | — | Multi-turn negotiation state (W15). The `state` column deserializes to the multi-turn session shape used by the negotiation runtime (schema line 2320). | ### Journeys Multi-step customer journey orchestration — entry conditions, step definitions, and per-customer enrollment state. | Model | `@@map` | Key Fields | Relations | Purpose | | ------------------ | --------------------- | -------------------------------------------------------------------------------------------------------------------------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------- | | Journey | `journeys` | `name`, `definition` (journey definition JSON), `entryCondition`, `maxDurationDays` | enrollment children | Visual journey definition — steps, branches, wait/decision/action nodes. `definition` is the full DAG (schema line 1349). | | Journey enrollment | `journey_enrollments` | `journeyId`, `customerId`, `currentStepId`, `status` (`active`/`completed`/`exited`), `enteredAt`, `lastStepAt`, `history` | parent journey | Per-customer enrollment state. `history` is the chronological list of visited steps (schema line 1368). | ### Flow & pipeline Data ingestion, schema management, and pipeline runtime. | Model | `@@map` | Key Fields | Relations | Purpose | | ------------------- | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Connector** | `connectors` | `type` (80 registered types, 52 ready + 28 coming soon), `config`, `authMethod`, `authConfig`, `status`, `lastTestedAt`, `lastError` | pipeline children | External data source connection (S3, Snowflake, Kafka, Salesforce, etc.). The full connector registry is exposed via the [Connectors API](/api-reference/connectors) (schema line 501). | | **Data schema** | `data_schemas` | `name`, `tableName` (`ds_*`), `entityType` (`customer`/`account`/`custom`) | field children, pipeline children, segment children | Defines an entity. `entityType` is the single "what is this schema" field: the `entityType=customer` schema is loaded as the base `customer.*` in every decision; related entities reach decisions via Schema Joins. Creates a real Postgres table named `ds_{name}` (schema line 445). | | **Schema field** | `schema_fields` | `name`, `dataType`, `length`, `precision`, `scale`, `isNullable`, `isPrimaryKey`, `isUnique`, `isPredictor`, `defaultValue` | parent data schema | Typed column on a data schema. `isPredictor=true` exposes the field to ML model training. **`isPrimaryKey=true` skips the auto-generated `id BIGSERIAL` column at table creation** — use this when your data has its own natural key (e.g. `customer_id`). The schema-create form has an inline "Custom primary key column" pane: filling it makes that column the PK and avoids the auto `id` entirely. Schemas without any user-defined PK still get the auto `id` column. (schema line 476) | | **Schema join** | `schema_joins` | `primarySchemaId`, `primaryKey`, `foreignSchemaId`, `foreignKey`, `joinType` (`left`/`inner`), `autoEnrich` | — | Auto-enrichment join definition between two data schemas. Used by the runtime to widen customer rows (schema line 2016). | | Data source | `data_sources` | `connectorKey`, `sourceConfig`, `fileFormat`, `syncMode` (`full_refresh`/`incremental_append`/`incremental_dedupe`/`cdc`), `schedule`, `schemaKey` | — | A logical source bound to a connector. Schedules + sync mode drive the pipeline runner (schema line 716). | | **Pipeline** | `pipelines` | `connectorId`, `schemaId`, `irVersion` (always `"1.0"`), `executionConfig`, `lastRunStatus` | parent connector, data schema, run children | IR-native pipeline. The DAG is in the pipeline IR version row (JSONB) — there is no per-node or per-edge table anymore (schema line 525). | | Pipeline IR version | `pipeline_ir_versions` | `pipelineId`, `version`, `ir`, `authoredBy` (user id or `"ai"`), `comment` | — | Version-controlled pipeline IR document. `version` is monotonically increasing per pipeline (schema line 550). | | Pipeline run | `pipeline_runs` | `pipelineId`, `status`, `rowsProcessed`, `rowsTotal`, `rowsFailed`, `validationErrors`, `loadStrategy` (`append`/`truncate`/`upsert`/`blue_green`), `partitions`, `lastProcessedRow` | parent pipeline | Single pipeline execution record. `lastProcessedRow` is the resume checkpoint (schema line 1091). | ### Content Reusable content templates, CMS-sync content items, and template inheritance. | Model | `@@map` | Key Fields | Relations | Purpose | | ---------------------------------------------------- | ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------- | | Template | `templates` | `category` (`email`/`push`/`sms`/`blueprint`/`offer`), `type`, `content`, `variables`, `isSystem` | — | Reusable template — including system-shipped seeds when `isSystem=true` (schema line 1489). | | Content item (no `@@map`; inferred model-name table) | (model-name table) | `name`, `channelType`, `status` (`draft`/`in_review`/`approved`/`published`/`archived`), `parentTemplateId`, `sourceType` (`internal`/`wordpress`/`contentful`/`strapi`/`sanity`), `version` | optional parent and child content items, optional content source, version children, creative children | Channel-typed content payload. CMS-synced when `sourceType != "internal"` (schema line 1510). | | Content version (no `@@map`) | (model-name table) | `contentItemId`, `version`, `content`, `blocks`, `personalization`, `changedBy`, `changeNote` | parent content item | Immutable historical snapshot of a content item (schema line 1545). | | Content source (no `@@map`) | (model-name table) | `provider` (`wordpress`/`contentful`/`strapi`/`sanity`), `config`, `syncMode` (`webhook`/`polling`), `autoPublish`, `mappings`, `webhookSecret` | content-item children | External CMS connection — pulls content into content-item rows (schema line 1562). | | Treatment | `treatments` | `name`, `channel`, `subject`, `headline`, `body`, `config`, `impressions`, `clicks`, `conversions` | — | AI-generated content entity — separate table from creatives (schema line 1921). | ### Reports Scheduled report templates, schedules, and execution runs. | Model | `@@map` | Key Fields | Relations | Purpose | | --------------- | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------ | --------- | ------------------------------------------------------------------------------------------------------------------- | | Report template | `report_templates` | `dataSources`, `sections`, `formats` (default `["pdf"]`), `narrative` (`{enabled, prompt?, modelOverride?}`), `filters`, `enabled` | — | Composable report definition. Reusable across schedules (schema line 2103). | | Report schedule | `report_schedules` | `templateId`, `cronExpression`, `timezone`, `destinations`, `lastRunAt`, `nextRunAt`, `lastStatus` | — | Cron schedule binding a template to delivery destinations (schema line 2123). | | Report run | `report_runs` | `templateId`, `scheduleId`, `triggeredBy` (default `cron`), `sectionsData`, `narrativeOutput`, `artifactPayloads`, `deliveryResults` | — | Per-execution audit row. Artifact payloads are inlined; the runner documents an S3 upgrade path (schema line 2143). | ### Behavioral metrics & summaries User-defined metrics evaluated against the interaction stream. | Model | `@@map` | Key Fields | Relations | Purpose | | ------------------ | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------ | --------------------------------------------------------------------------------------------------------------------------- | | Metric definition | `metric_definitions` | `aggregateFunction` (`count`/`sum`/`avg`/`min`/`max`/`ratio`), `sourceField`, `windowDays` (max 365), `groupByDimensions` (max 2), `filterConditions`, `sqlFilter` (max 500 chars), `computeMode` (`realtime`/`batch`), `batchIntervalMin` | metric-value children | User-defined behavioral metric. Compiled into a query against the interaction-summary table (schema line 1641). | | Metric value | `metric_values` | `metricId`, `customerId`, `dimensionKey`, `value`, `computedAt` | parent metric definition | Materialized per-customer × dimension value. Unique on `[tenantId, metricId, customerId, dimensionKey]` (schema line 1670). | | Summary definition | `summary_definitions` | `dimensions`, `aggregates` (default `["count"]`), `windows` (default `["7d","30d"]`), `isSystem` | — | Pluggable shape for interaction-summary aggregation. `isSystem=true` rows are undeletable (schema line 1860). | ## Configuration ### Prisma 7 datasource split ```prisma theme={null} // platform/prisma/schema.prisma — datasource block datasource db { provider = "postgresql" } ``` ```ts theme={null} // platform/prisma.config.ts — connection URL import "dotenv/config"; import { defineConfig } from "prisma/config"; export default defineConfig({ schema: "prisma/schema.prisma", migrations: { path: "prisma/migrations" }, datasource: { url: process.env["DATABASE_URL"]! }, }); ``` The `url` field is illegal inside the schema file under Prisma 7. The connection URL is read from `DATABASE_URL` by the Prisma config and passed to the generated client. ### `ds_*` tables created outside the schema Data-schema rows do **not** declare their column shape inside the Prisma schema. Each row holds metadata; the actual `ds_{name}` table is created at runtime by the platform's DDL helper via `CREATE TABLE` statements when the schema is published. Adding a schema field issues `ALTER TABLE ds_{name} ADD COLUMN`. This split exists because the column shape is per-tenant and dynamic — it cannot be declared statically in a global schema. Operators viewing `\d ds_*` in psql will see real Postgres tables that are not represented in the Prisma schema. **Primary key & `id` column.** When no schema field is marked as the primary key, the DDL helper adds an auto `id BIGSERIAL PRIMARY KEY`. If exactly one field is marked primary, that column becomes the PK and the auto `id` is skipped. If **two or more** fields are marked primary, they form a composite key emitted as a single table-level `PRIMARY KEY (col_a, col_b, …)` constraint (Postgres rejects multiple per-column `PRIMARY KEY` clauses). Every `ds_*` table also carries `created_at` / `updated_at` timestamp columns. **Row-level security.** If a `ds_*` table has a `tenantId` (or `tenant_id`) column, the DDL helper enables Postgres RLS on it at creation time — an additive isolation layer on top of the application's `where: { tenantId }` scoping. RLS enablement is best-effort: a failure is logged but does not fail table creation. **Type mapping.** Each schema field's abstract `dataType` maps to a concrete Postgres type: `varchar` → `VARCHAR(n)` (default 255), `text` → `TEXT`, `integer`/`bigint`/`smallint`, `numeric`/`decimal` → `NUMERIC(p,s)`, `float`/`real` → `REAL`, `double` → `DOUBLE PRECISION`, `boolean`, `date`, `timestamp`, `timestamptz`, `json` → `JSONB`, `uuid`, and anything unrecognized falls back to `TEXT`. **Table naming.** Schemas created through the Schemas API/UI land at `ds_{name}` (the name lowercased, non-alphanumerics replaced with `_`). The built-in seed/default schemas instead use a tenant-short prefix — `ds_{tenantShort}_{name}`, where `tenantShort` is the tenant id with hyphens stripped, first 8 chars — so both forms can coexist depending on how the schema was created. ### Manual SQL for partitioning and migrations Schema changes that Prisma cannot express are stored in the `manual-sql` directory under the Prisma folder: | File | Purpose | | ------------------------------------ | ----------------------------------------------------------------------------- | | `01_registry_status_check.sql` | Adds CHECK constraint to `algorithm_models.registryStatus` | | `02_flow_ir_phase1.sql` | Initial flow IR table grants | | `04_drop_legacy_pipeline_tables.sql` | Drops legacy `pipeline_nodes` + `pipeline_edges` (2026-04-28) | | `05_campaign_id_additive.sql` | Adds nullable `campaignId` to `interaction_history` + `interaction_summaries` | | `06_customer_engagement_health.sql` | Bootstrap for the customer-engagement-health table | | `07_flow_run_checkpoints.sql` | Per-tenant flow checkpoint storage | | `08_ai_import.sql` | AI document import bootstrap | | `09_parity_w11_to_w19.sql` | W11–W19 parity-sprint additions | Run these in numerical order against any database that pre-dates them. Production databases run them through CI; local databases need a manual `psql -f` or `prisma db execute --file`. ## Honest limits * **`ds_*` tables are not in the Prisma schema.** The dynamic per-tenant entity tables created from data-schema rows live outside the schema file. There is no way to type-check them through Prisma — runtime queries against `ds_{name}` go through raw SQL or generated query builders. * **Interaction history is partitioned in production.** The model declares one logical table; production deployments range-partition `interaction_history` by month, which is why `/recommend` writes use `prisma.$executeRaw` instead of `prisma.createMany({ skipDuplicates })`. The Prisma model does not declare partitioning. * **Soft-delete is not Prisma-enforced.** Models with `deletedAt` rely on every API route to filter `where: { deletedAt: null }`. There is no global Prisma middleware enforcing this — a raw query or a route that forgets the filter will return soft-deleted rows. * **Cross-offer constraints have no UI surface yet.** The model is wired into Lagrangian ranking in both realtime and batch decisioning paths via the cross-offer ranking helper, but rows must be inserted directly via SQL or a future admin API — there is no Studio UI for editing them today. * **Some operator-internal tables have no public API surface.** The transactional outbox, dead-letter store, channel-delivery ledger, export checkpoints, ML job results, AI-import token ledger, AI-import skip digest, policy snapshots, approval-request stages, custom-role assignments, NextAuth verification tokens, WebAuthn credentials, the per-tenant region tag, model-adaptation rows, and customer-engagement-health rollups are read/written by internal code paths only — there is no `/api/v1/{model}` REST surface for them. * **Several alternate storage backends are referenced but not declared here.** ScyllaDB / DynamoDB / OpenSearch backends mentioned in [Scaling](/self-host/architecture/scaling) are write paths inside platform adapters; their row shapes are not declared in the Prisma schema. Treat the Prisma schema as the authoritative shape for the canonical Postgres path only. * **AI import models and content models lack `@@map` directives.** Their Postgres table names default to the model name as written rather than snake\_case — verify via `\dt` in psql. ## Related * [Schemas API](/api-reference/schemas) — the data-schema + schema-field REST surface that creates the dynamic `ds_*` tables. * [Audit Logs API](/api-reference/audit-logs) — query the tamper-evident audit chain. * [Decision Traces API](/api-reference/decision-traces) — query decision-trace rows. * [Decisioning Gates](/decisioning/qualification-rules) — the rule-engine surface backed by qualification rules. * [Contact Policies API](/api-reference/contact-policies) — the contact-policy surface (root rule + scope rows). * [Frequency Caps](/decisioning/frequency-caps) — operator semantics for the frequency-cap model. * [Operations Runbook](/self-host/architecture/operations) — operator view of the outbox, dead-letter store, and channel-delivery ledger. * [Architecture Overview](/self-host/architecture/overview) — system-level view above this index. * [Prisma 7 Schema Reference](https://www.prisma.io/docs/orm/prisma-schema) — upstream Prisma docs.