KaireonAI persists all platform state in 104 Prisma models declared in the platform schema. This page indexes every model by domain, names the underlying PostgreSQL table (viaDocumentation Index
Fetch the complete documentation index at: https://docs.kaireonai.com/llms.txt
Use this file to discover all available pages before exploring further.
@@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 atenantId 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
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 atenantId 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 nullabledeletedAt 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, ArbitrationProfile, 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). |
| Qualification rule | 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). |
| Qualification rule 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, arbitrationProfileId, isDefault, isProtected | run children | Visual decisioning flow. draftConfig holds the working version; publishedVersions[] holds immutable snapshots (schema line 765). |
| Arbitration profile | arbitration_profiles | key, weights, status | — | Multi-objective ranking weights (revenue, margin, propensity, engagement) (schema line 1705). |
| Cross-offer constraint | cross_offer_constraints | scope, ruleType, config, status | — | Multi-offer arbitration constraints. Read by the Lagrangian cross-offer arbitration 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 qualification rules (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). |
| Volume constraint | volume_constraints | scope, scopeId, maxVolume, period, currentCount, resetAt | — | System-wide delivery caps on offers, categories, channels. See Volume Constraints (schema line 1840). |
| 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 (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.arbitration 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 (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, volumeConstraints, fileConfig, channelIds | parent decision flow, segment, campaign-run children | Scheduled batch campaign config. Triggers campaign-run rows on each execution (schema line 823). |
| 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 (54 registered types), 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 (schema line 501). |
| Data schema | data_schemas | name, tableName (ds_*), entityType, schemaType, linkedSchemaId, joinMapping, summaryColumns, autoEnrich | field children, pipeline children, segment children | Defines an entity (customer, account, custom). 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
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.
Manual SQL for partitioning and migrations
Schema changes that Prisma cannot express are stored in themanual-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 |
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 againstds_{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_historyby month, which is why/recommendwrites useprisma.$executeRawinstead ofprisma.createMany({ skipDuplicates }). The Prisma model does not declare partitioning. - Soft-delete is not Prisma-enforced. Models with
deletedAtrely on every API route to filterwhere: { 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 arbitration in both realtime and batch decisioning paths via the cross-offer arbitration 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 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
@@mapdirectives. Their Postgres table names default to the model name as written rather than snake_case — verify via\dtin psql.
Related
- Schemas API — the data-schema + schema-field REST surface that creates the dynamic
ds_*tables. - Audit Logs API — query the tamper-evident audit chain.
- Decision Traces API — query decision-trace rows.
- Decisioning Gates — the rule-engine surface backed by qualification rules.
- Contact Policies API — the contact-policy surface (root rule + scope rows).
- Volume Constraints — operator semantics for the volume-constraint model.
- Operations Runbook — operator view of the outbox, dead-letter store, and channel-delivery ledger.
- Architecture Overview — system-level view above this index.
- Prisma 7 Schema Reference — upstream Prisma docs.