Data Model: Mongo to Postgres

This page maps every collection the Python backend reads or writes to the Prisma models of the TypeScript app, field by field, and gives the draft Prisma schema. It is the input to the migration script described in the Cutover Runbook and to the tenant isolation work in Framework Copy. The overview is the migration plan.

Sources read: backend/app/models/*.py, every insert_one and update_one under backend/app/api, backend/app/services, backend/app/utils, backend/app/core/database.py, backend/app/core/migrations.py, backend/app/main.py and scripts/migrate_happiness_index.py in CatalyzeUp/sondely (branch develop, commit 06debbf). The collections that actually exist were listed read-only from Azure (az cosmosdb mongodb collection list, account cosmos-catalyzeup) on 5 November 2026. The older Database Schema page is stale (it lists maslow_scores, which no code uses, and embeds questions in surveys, while questions are their own collection); this page replaces it for the migration.

What exists in Cosmos today

Both databases, impactpulse (dev) and impactpulse_prod (prod), hold the same 14 collections. Mongo creates a collection on its first write, so a collection the code names but never writes in that environment does not exist.

Redis (redis-catalyzeup, DB 0 dev and DB 1 prod) holds one thing: the session token minted at sign-in (session:<token>, 24 hour TTL, core/redis.py, auth/routes.py _create_tokens_and_session). It is not migrated; see Session.

Conventions for every model

JSON columns and why

A column is JSON only when its shape is a document the product reads whole and never filters on, or when it is keyed by ids that live elsewhere. Each one has a Zod schema at the write boundary; rows migrated from Mongo are stored verbatim and validated as a report (see the runbook).

Points are columns, not JSON: score, overallPercentage, geoCountry, geoRegion, contextEvent, contextDescription, so the impact screens, the map and later insight tools can filter and aggregate on them.

Collection by collection

User

From users. Insert sites: auth/routes.py register (133), Google callback (565), Apple callback (715), auto-enroll (776), utils/auth.py create_user, the dev-only invite branch of organizations/routes.py (436), core/seed.py, the WeHappers import.

Organization

From organizations. Model: models/organization_enhanced.py (the only organization model imported by routes). Insert: organizations/routes.py 88.

OrganizationMember

From organization_members. Inserts in five places (organizations/routes.py 100, 422, 458; invitations/routes.py 106; auth/routes.py 798; services/enrollment.py 27; main.py WeHappers branch).

Unique (organizationId, userId). Python has no such index, and five code paths check then insert, so duplicates are possible. The script keeps one row per pair (highest role, then earliest joinedAt) and quarantines the others with the kept id in detail.

Invitation

From organization_invitations (absent in both databases today). Insert: organizations/routes.py 479: id, organization_id, email, role, token, invited_by_id, created_at, expires_at, status. Accept and decline set accepted_at, declined_at.

token is a raw cuid today; the new row stores tokenHash (sha-256), so a migrated invitation link keeps working. The stored status: "PENDING" is never updated (acceptance writes accepted_at only, invitations/routes.py), so it is dropped and the status is derived, as OrganizationInvitation.compute_status does.

Survey

From surveys. Inserts: surveys/routes.py 76, templates/routes.py 101, seed, WeHappers import. Updates: survey update, translation routes, migrations 0001 and 0002, the WeHappers branch (status).

QuestionGroup

Embedded in surveys.question_groups as {id, name, order, color, tint, description} (models/survey.py QuestionGroup). The id is not unique across surveys: template groups use fixed slugs (vital, physiological, safety_development, ... in data/survey_templates.py), copied into every survey built from the template. So:

• QuestionGroup.id is new, derived from (surveyId, legacy group id) by a hash (for example qg_ plus the first 24 hex characters of a sha-256), so a re-run produces the same id and the upsert stays idempotent.
• QuestionGroup.key holds the legacy group id; unique (surveyId, key).
• The API keeps exposing key as group_id, and the JSON keyed by group (groupResults[].group_id, SurveyTranslation.content.groups) keeps the key. Nothing a client holds changes.
• color is stored as written (a CSS name or hex, normalize_group_color); tint, description, order as is.

A survey whose groups contain the same id twice, or a question whose group_id names no group of its survey, is quarantined as a warning; the question is migrated with groupId null.

Question

From questions (their own collection, indexed on survey_id and (survey_id, order)). Inserts: surveys/routes.py 98 and 446, templates/routes.py 121.

SurveyTranslation

Built from three places for each language L: surveys.translations[L] ({title, description, groups}), surveys.translation_metadata[L] ({status, translated_at, approved_at, approved_by}) and every questions.translations[L] ({text, options, rating_labels}) of the survey. One row per (surveyId, language), unique. A question translation for a language the survey has no entry for still creates the row (status from metadata, else DRAFT) and is reported.

SurveySession

From survey_sessions. Model: models/survey_session.py (the live one). Insert: sessions/routes.py 111, which writes both id and _id with the same cuid.

Python treats one in_progress session per (survey, user) as the rule (sessions/routes.py 64) without an index. The partial unique index enforcing it is created only if the rehearsal finds no duplicates; otherwise the duplicates are listed for a decision before cutover.

SurveyResponse

From survey_responses. Two document shapes exist in the code:

Shape B documents are not readable by the Python API as responses (SafeCollection strips _id and SurveyResponse requires id; inferred, confirmed in rehearsal 1). The script migrates them with id = _id, scores null, sessionId and attemptNumber set, and records each in quarantine with severity NOTE, so their count is visible before anyone decides whether they are kept.

SurveyAssignment

From survey_assignments. Inserts: assignments/routes.py 92, services/enrollment.py 79, seed.

member_id is a user id, not a membership id (assignments/routes.py checks organization_members with user_id: member_id), so it becomes userId. assigned_by is a user id or the literal "system" (enrollment); it becomes assignedById (null for system), and the serializer writes "system" back. Status values: PENDING, COMPLETED (surveys/routes.py 622), OVERDUE (reminder_engine.py 92). Unique (surveyId, userId), which Python checks before insert but does not index; duplicates handled as for members.

EmailReminder

The email_reminders collection was designed (models/reminder.py, core/database_enhanced.py) but never written; both are dead code and the collection does not exist. Reminder state today lives on assignments, responses and sessions (counters and last-sent timestamps). The EmailReminder table is kept with a new, narrower job: one row per reminder email sent or attempted, unique on (kind, targetId, number). The reminder job, which replaces the 60 second reminder_loop that runs on every replica, inserts the row before sending, so two replicas or a retried tick cannot send the same reminder twice. That is the record of intent the idempotency rule asks for, and it gives support a history. The counters on assignments, responses and sessions stay, because the API returns them.

NotificationPreference

From notification_preferences (absent in both databases: created on first GET /notifications/preferences, never called in production so far). Fields: user_id, unsubscribe_token, email_reminders_enabled, reminder_frequency, survey_reminders, organization_updates, quiet_hours_enabled, quiet_hours_start, quiet_hours_end, timezone, muted_survey_ids, created_at, updated_at (api/notifications/routes.py). No id field: the row gets a new cuid and is unique on userId. Per person, not per organization. unsubscribeToken becomes unsubscribeTokenHash so links already emailed keep working. mutedSurveyIds String[].

ApiKey

From api_keys. Insert: api_keys/routes.py 74, seed, the WeHappers startup branch. Fields: id, organization_id, name, key_prefix (first 12 characters, ip_live_ plus 4), key_hash (bcrypt of the full key), last_four, created_by_id, created_at, last_used_at, request_count, expires_at, revoked_at.

One key matters at cutover: the WeHappers site (wehappers.org/src/app/api/happiness-map/route.ts) calls https://impactpulse.catalyzeup.ai/api/v1/surveys and /surveys/{id}/responses with IMPACTPULSE_API_KEY, falling back to the hard-coded ip_live_wehappers_dev_key_00001. The wehappers container app sets no environment variable (read from Azure), so the hard-coded key is the one in use, and it authenticates against production today (HTTP 200 on 5 November 2026). See the WeHappers section of the Cutover Runbook.

Session, OtpCode, VerificationToken, OAuthState

Collections that are not migrated

Dead and duplicate models

Found by grepping every import of app.models.<name> and app.core.<name> outside the module itself.

Draft Prisma schema

The framework models (Session, IdempotencyKey, Audit) follow TourCockpit at f952d0eea with companyId renamed organizationId and User moved out of the tenant set. Row level security policies (organizationId = current_setting('app.tenant_id'), as TourCockpit's setTenantGuc in src/lib/db.ts) and the email CHECK live in the migration SQL, not in the schema file. This is a draft: API Routes and Auth may add fields, and the rehearsal findings may change uniqueness rules. The schema as built, copied from TourCockpit at 74e9a2a53 on 7 November 2026, is prisma/schema.prisma in CatalyzeUp/sondely-platform, and where it differs from this draft it is the one the migration script and the app follow.

// Sondely: draft schema for the move from Cosmos (Mongo API) to Postgres.
// Docs: /sondely/migration/data-model
// Framework models copied from ideaplaces-tour-platform@f952d0eea, companyId -> organizationId.

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ---------------------------------------------------------------------------
// Enums. Upper-case here; the API serializer emits today's casing.
// ---------------------------------------------------------------------------

enum MemberRole {
  OWNER
  ADMIN
  RESPONDENT
}

enum SurveyStatus {
  DRAFT
  PUBLISHED
  CLOSED
}

enum ResultType {
  FLAT
  PYRAMID
  RADAR
  BREAKDOWN
}

enum QuestionType {
  BOOLEAN
  TEXT
  SINGLE_CHOICE
  MULTIPLE_CHOICE
  RATING
  COUNTRY // legacy WeHappers geo question, removed from surveys by Python migration 0001
}

enum TranslationStatus {
  PENDING
  DRAFT
  APPROVED
}

enum SessionStatus {
  IN_PROGRESS
  COMPLETED
  ABANDONED
}

enum SessionChannel {
  WEB
  API
  WHATSAPP
  VOICE
}

enum AssignmentStatus {
  PENDING
  COMPLETED
  OVERDUE
}

enum ReminderKind {
  ASSIGNMENT
  RECURRENCE
  INCOMPLETE_SESSION
}

enum ReminderState {
  SENDING
  SENT
  FAILED
}

enum ApiKeyScope {
  READ
  DRAFT
  FULL
}

enum TokenKind {
  EMAIL_VERIFY
  PASSWORD_RESET
}

enum AuditSource {
  SEMANTIC
  ROW
}

enum QuarantineSeverity {
  ERROR // row NOT migrated
  WARNING // row migrated, something about it needs a decision
  NOTE // row migrated, recorded for visibility
}

// ---------------------------------------------------------------------------
// Tenant root and people
// ---------------------------------------------------------------------------

model Organization {
  id            String   @id
  name          String
  description   String?
  website       String?
  industry      String?
  size          String?
  /// Unique when set (Mongo: sparse unique). Resolved by Next middleware.
  customDomain  String?  @unique
  settings      Json     @default("{}")
  profileConfig Json
  createdById   String?
  createdAt     DateTime @default(now()) @db.Timestamptz(3)
  updatedAt     DateTime @updatedAt @db.Timestamptz(3)

  members       OrganizationMember[]
  invitations   Invitation[]
  surveys       Survey[]
  questionGroups QuestionGroup[]
  questions     Question[]
  translations  SurveyTranslation[]
  sessions      SurveySession[]
  responses     SurveyResponse[]
  assignments   SurveyAssignment[]
  reminders     EmailReminder[]
  apiKeys       ApiKey[]
  idempotency   IdempotencyKey[]
  audits        Audit[]

  @@index([name])
  @@map("organizations")
}

/// Global: one row per person across every organization. NOT in TENANT_MODELS.
model User {
  id              String    @id
  /// Lower-cased and trimmed; CHECK (email = lower(btrim(email))) in the migration SQL.
  email           String    @unique
  passwordHash    String?
  fullName        String?
  locale          String?
  googleId        String?   @unique
  appleId         String?   @unique
  emailOnly       Boolean   @default(false)
  emailVerifiedAt DateTime? @db.Timestamptz(3)
  image           String?
  /// _migration_source_id, _migration_source, _migration_metadata (WeHappers import).
  legacy          Json?
  createdAt       DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt       DateTime  @updatedAt @db.Timestamptz(3)

  memberships        OrganizationMember[]
  sessions           Session[]
  verificationTokens VerificationToken[]
  surveySessions     SurveySession[]
  responses          SurveyResponse[]
  assignments        SurveyAssignment[]
  notificationPrefs  NotificationPreference?

  @@map("users")
}

model OrganizationMember {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  userId         String
  user           User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  role           MemberRole
  invitedById    String?
  joinedAt       DateTime     @default(now()) @db.Timestamptz(3)
  updatedAt      DateTime     @updatedAt @db.Timestamptz(3)

  @@unique([organizationId, userId])
  @@index([userId])
  @@map("organization_members")
}

model Invitation {
  id             String       @id @default(cuid())
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  email          String
  role           MemberRole
  message        String?
  /// sha-256 of the token in the emailed link.
  tokenHash      String       @unique
  invitedById    String
  createdAt      DateTime     @default(now()) @db.Timestamptz(3)
  expiresAt      DateTime     @db.Timestamptz(3)
  acceptedAt     DateTime?    @db.Timestamptz(3)
  declinedAt     DateTime?    @db.Timestamptz(3)

  @@index([organizationId, email])
  @@map("invitations")
}

// ---------------------------------------------------------------------------
// Surveys
// ---------------------------------------------------------------------------

model Survey {
  id                     String       @id
  organizationId         String
  organization           Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  publicId               String       @unique
  title                  String
  description            String?
  status                 SurveyStatus @default(DRAFT)
  isActive               Boolean      @default(true)
  settings               Json         @default("{}")
  minDaysBetweenAttempts Int          @default(0)
  autoRemind             Boolean      @default(true)
  requireEmail           Boolean      @default(true)
  resultType             ResultType   @default(FLAT)
  /// A template slug such as "maslow-needs-v1"; not a foreign key.
  templateId             String?
  recurrenceEnabled      Boolean      @default(false)
  recurrenceIntervalDays Int?
  reminderIntervals      Int[]        @default([0, 3, 7])
  availableLanguages     String[]     @default(["en"])
  defaultLanguage        String       @default("en")
  createdById            String
  createdAt              DateTime     @default(now()) @db.Timestamptz(3)
  updatedAt              DateTime     @updatedAt @db.Timestamptz(3)

  groups       QuestionGroup[]
  questions    Question[]
  translations SurveyTranslation[]
  sessions     SurveySession[]
  responses    SurveyResponse[]
  assignments  SurveyAssignment[]

  @@index([organizationId, createdAt])
  @@index([createdById])
  @@map("surveys")
}

/// Was embedded in surveys.question_groups. `key` is the legacy group id
/// (a slug such as "vital", reused across surveys), unique per survey.
model QuestionGroup {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId       String
  survey         Survey       @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  key            String
  name           String
  order          Int
  color          String
  tint           String?
  description    String?

  questions Question[]

  @@unique([surveyId, key])
  @@index([organizationId, surveyId, order])
  @@map("question_groups")
}

model Question {
  id             String         @id
  organizationId String
  organization   Organization   @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId       String
  survey         Survey         @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  groupId        String?
  group          QuestionGroup? @relation(fields: [groupId], references: [id], onDelete: SetNull)
  text           String
  type           QuestionType
  required       Boolean        @default(false)
  order          Int
  weight         Float          @default(1.0)
  options        String[]       @default([])
  ratingLabels   Json?
  scoringConfig  Json?
  createdAt      DateTime       @default(now()) @db.Timestamptz(3)
  updatedAt      DateTime       @updatedAt @db.Timestamptz(3)

  @@index([organizationId, surveyId, order])
  @@map("questions")
}

/// One row per survey and language. content = {survey, groups, questions}.
model SurveyTranslation {
  id             String            @id @default(cuid())
  organizationId String
  organization   Organization      @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId       String
  survey         Survey            @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  language       String
  status         TranslationStatus @default(DRAFT)
  content        Json
  translatedAt   DateTime?         @db.Timestamptz(3)
  approvedAt     DateTime?         @db.Timestamptz(3)
  approvedById   String?
  createdAt      DateTime          @default(now()) @db.Timestamptz(3)
  updatedAt      DateTime          @updatedAt @db.Timestamptz(3)

  @@unique([surveyId, language])
  @@index([organizationId])
  @@map("survey_translations")
}

// ---------------------------------------------------------------------------
// Taking a survey
// ---------------------------------------------------------------------------

model SurveySession {
  id                       String         @id
  organizationId           String
  organization             Organization   @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId                 String
  survey                   Survey         @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  userId                   String
  user                     User           @relation(fields: [userId], references: [id], onDelete: Cascade)
  channel                  SessionChannel @default(WEB)
  attemptNumber            Int            @default(1)
  status                   SessionStatus  @default(IN_PROGRESS)
  progressPercentage       Float          @default(0)
  answers                  Json           @default("{}")
  timeSpentSeconds         Int            @default(0)
  currentQuestionIndex     Int            @default(0)
  startedAt                DateTime       @default(now()) @db.Timestamptz(3)
  lastActivityAt           DateTime       @default(now()) @db.Timestamptz(3)
  completedAt              DateTime?      @db.Timestamptz(3)
  incompleteRemindersSent  Int            @default(0)
  lastIncompleteReminderAt DateTime?      @db.Timestamptz(3)

  responses SurveyResponse[]

  @@index([organizationId, surveyId, userId, status])
  @@index([organizationId, lastActivityAt])
  @@map("survey_sessions")
}

model SurveyResponse {
  id                       String         @id
  organizationId           String
  organization             Organization   @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId                 String
  survey                   Survey         @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  userId                   String?
  user                     User?          @relation(fields: [userId], references: [id], onDelete: SetNull)
  sessionId                String?
  session                  SurveySession? @relation(fields: [sessionId], references: [id], onDelete: SetNull)
  attemptNumber            Int?
  /// As typed by the respondent; not normalized (parity with the cooldown check).
  respondentEmail          String?
  respondentName           String?
  geoCountry               String?
  geoRegion                String?
  contextEvent             String?
  contextDescription       String?
  answers                  Json           @default("{}")
  score                    Float?
  overallPercentage        Float?
  scoreBreakdown           Json?
  groupResults             Json?
  completedAt              DateTime?      @db.Timestamptz(3)
  submittedAt              DateTime       @db.Timestamptz(3)
  nextEligibleDate         DateTime?      @db.Timestamptz(3)
  recurrenceRemindersSent  Int            @default(0)
  lastRecurrenceReminderAt DateTime?      @db.Timestamptz(3)
  recurrenceStatus         String?
  legacy                   Json?

  @@index([organizationId, surveyId, submittedAt])
  @@index([organizationId, userId])
  @@index([surveyId, respondentEmail])
  @@index([recurrenceRemindersSent])
  @@map("survey_responses")
}

model SurveyAssignment {
  id                String           @id
  organizationId    String
  organization      Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  surveyId          String
  survey            Survey           @relation(fields: [surveyId], references: [id], onDelete: Cascade)
  /// Python's member_id: a USER id.
  userId            String
  user              User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  /// Null means "system" (tenant enrollment).
  assignedById      String?
  assignedAt        DateTime         @default(now()) @db.Timestamptz(3)
  status            AssignmentStatus @default(PENDING)
  lastCompletedAt   DateTime?        @db.Timestamptz(3)
  currentDueDate    DateTime?        @db.Timestamptz(3)
  nextDueDate       DateTime?        @db.Timestamptz(3)
  remindersSent     Int              @default(0)
  lastReminderAt    DateTime?        @db.Timestamptz(3)

  @@unique([surveyId, userId])
  @@index([organizationId, assignedAt])
  @@index([organizationId, userId])
  @@map("survey_assignments")
}

/// One row per reminder email, written BEFORE the send (record of intent),
/// so a second replica or a retried tick cannot send it twice.
model EmailReminder {
  id             String        @id @default(cuid())
  organizationId String
  organization   Organization  @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  kind           ReminderKind
  /// The assignment, response or session the reminder is about.
  targetId       String
  number         Int
  recipientEmail String
  state          ReminderState @default(SENDING)
  providerId     String?
  lastError      String?
  createdAt      DateTime      @default(now()) @db.Timestamptz(3)
  sentAt         DateTime?     @db.Timestamptz(3)

  @@unique([kind, targetId, number])
  @@index([organizationId, createdAt])
  @@map("email_reminders")
}

/// Per person, not per organization. NOT in TENANT_MODELS.
model NotificationPreference {
  id                    String   @id @default(cuid())
  userId                String   @unique
  user                  User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  unsubscribeTokenHash  String   @unique
  emailRemindersEnabled Boolean  @default(true)
  reminderFrequency     String   @default("default")
  surveyReminders       Boolean  @default(true)
  organizationUpdates   Boolean  @default(true)
  quietHoursEnabled     Boolean  @default(false)
  quietHoursStart       String?
  quietHoursEnd         String?
  timezone              String   @default("UTC")
  mutedSurveyIds        String[] @default([])
  createdAt             DateTime @default(now()) @db.Timestamptz(3)
  updatedAt             DateTime @updatedAt @db.Timestamptz(3)

  @@map("notification_preferences")
}

// ---------------------------------------------------------------------------
// Credentials
// ---------------------------------------------------------------------------

model ApiKey {
  id               String        @id
  organizationId   String
  organization     Organization  @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  name             String
  /// "ip_live_" plus 4 characters: the lookup for legacy bcrypt keys.
  keyPrefix        String
  lastFour         String
  /// bcrypt of the full key, for keys minted by Python. Null for new keys.
  legacyBcryptHash String?
  /// sha-256 of the full key, for keys minted by the TS app.
  hashedKey        String?       @unique
  scopes           ApiKeyScope[] @default([FULL])
  createdById      String
  createdAt        DateTime      @default(now()) @db.Timestamptz(3)
  lastUsedAt       DateTime?     @db.Timestamptz(3)
  requestCount     Int           @default(0)
  expiresAt        DateTime?     @db.Timestamptz(3)
  revokedAt        DateTime?     @db.Timestamptz(3)

  @@index([organizationId])
  @@index([keyPrefix])
  @@map("api_keys")
}

/// Resolved before a tenant is bound. NOT in TENANT_MODELS (as TourCockpit).
model Session {
  id                   String    @id @default(cuid())
  userId               String
  user                 User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  tokenHash            String    @unique
  expiresAt            DateTime  @db.Timestamptz(3)
  createdAt            DateTime  @default(now()) @db.Timestamptz(3)
  lastSeenAt           DateTime? @db.Timestamptz(3)
  /// Names an organization, grants nothing: re-checked against OrganizationMember.
  activeOrganizationId String?

  @@index([userId])
  @@map("sessions")
}

model OtpCode {
  id        String    @id @default(cuid())
  email     String
  codeHash  String
  expiresAt DateTime  @db.Timestamptz(3)
  usedAt    DateTime? @db.Timestamptz(3)
  attempts  Int       @default(0)
  createdAt DateTime  @default(now()) @db.Timestamptz(3)

  @@index([email, createdAt])
  @@map("otp_codes")
}

model VerificationToken {
  id         String    @id @default(cuid())
  userId     String
  user       User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  kind       TokenKind
  tokenHash  String    @unique
  expiresAt  DateTime  @db.Timestamptz(3)
  consumedAt DateTime? @db.Timestamptz(3)
  createdAt  DateTime  @default(now()) @db.Timestamptz(3)

  @@index([userId])
  @@map("verification_tokens")
}

model OAuthState {
  id           String   @id @default(cuid())
  stateHash    String   @unique
  returnOrigin String?
  expiresAt    DateTime @db.Timestamptz(3)
  createdAt    DateTime @default(now()) @db.Timestamptz(3)

  @@map("oauth_states")
}

// ---------------------------------------------------------------------------
// Framework (copied from TourCockpit f952d0eea)
// ---------------------------------------------------------------------------

model IdempotencyKey {
  id             String       @id @default(cuid())
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  key            String
  requestHash    String
  status         Int
  contentType    String?
  body           String
  sending        Boolean      @default(false)
  createdAt      DateTime     @default(now()) @db.Timestamptz(3)

  @@unique([organizationId, key])
  @@index([organizationId, createdAt])
  @@map("idempotency_keys")
}

model Audit {
  id               String       @id @default(cuid())
  organizationId   String
  organization     Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  actorUserId      String?
  role             MemberRole?
  actorKind        String? // "USER" | "APIKEY" | "SYSTEM"
  apiKeyId         String?
  action           String
  entity           String
  entityId         String?
  beforeSummary    Json?
  afterSummary     Json?
  source           AuditSource  @default(SEMANTIC)
  requestId        String?
  revertsRequestId String?
  createdAt        DateTime     @default(now()) @db.Timestamptz(3)

  @@index([organizationId, createdAt])
  @@index([organizationId, entity, entityId])
  @@index([organizationId, requestId])
  @@map("audit")
}

// ---------------------------------------------------------------------------
// Migration bookkeeping (admin role only; no grant to the app role)
// ---------------------------------------------------------------------------

model MigrationRun {
  id          String    @id @default(cuid())
  /// "impactpulse" or "impactpulse_prod", and the snapshot it read.
  sourceDb    String
  snapshotRef String
  rehearsal   Boolean
  startedAt   DateTime  @default(now()) @db.Timestamptz(3)
  finishedAt  DateTime? @db.Timestamptz(3)
  /// Per collection: read, written, quarantined. Per table: rows, hash digest.
  counts      Json?
  verdict     String? // "go" | "no-go" with the failing checks

  quarantine MigrationQuarantine[]

  @@map("migration_runs")
}

model MigrationQuarantine {
  id             String             @id @default(cuid())
  runId          String
  run            MigrationRun       @relation(fields: [runId], references: [id], onDelete: Cascade)
  collection     String
  /// The Mongo id, or _id when the document has no id.
  sourceId       String
  organizationId String?
  /// A stable code: ORPHAN_SURVEY, NO_ORGANIZATION, EMAIL_CASE_COLLISION,
  /// DUPLICATE_MEMBERSHIP, ANSWER_SCHEMA, UNKNOWN_ENUM, BAD_DATE, SESSION_SHAPE_RESPONSE ...
  reason         String
  severity       QuarantineSeverity
  detail         Json?
  /// The source document as canonical JSON, so nothing is lost.
  document       Json
  createdAt      DateTime           @default(now()) @db.Timestamptz(3)
  resolvedAt     DateTime?          @db.Timestamptz(3)
  resolution     String?

  @@unique([collection, sourceId, reason])
  @@index([runId, severity])
  @@map("migration_quarantine")
}

Indexes and unique constraints, side by side

Every new unique constraint is one Python never enforced, so each is checked against the data in rehearsal before the cutover, and each violation has a quarantine rule (see the Cutover Runbook).

Open points on this page