schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

generator zod {
  provider = "zod-prisma-types"
}

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

enum PushTokenType {
  apns
  fcm
}

enum ApnsEnvironment {
  sandbox
  production
}

// v2 Push Notification Models
model DeviceRegistration {
  deviceId          String             @id
  pushToken         String?            @db.Text
  pushTokenType     PushTokenType      @default(apns)
  apnsEnv           ApnsEnvironment?
  pushFailures      Int                @default(0)
  disabled          Boolean            @default(false)
  addedAt           DateTime           @default(now())
  updatedAt         DateTime           @updatedAt
  lastSentAt        DateTime?
  lastFailureAt     DateTime?
  accountId         String?            @db.Uuid
  account           Account?           @relation(fields: [accountId], references: [id], onDelete: SetNull)
  clientIdentifiers ClientIdentifier[]

  @@unique([pushTokenType, apnsEnv, pushToken])
  @@index([pushToken])
  @@index([disabled, pushFailures])
  @@index([accountId])
}

model ClientIdentifier {
  id        String             @id
  deviceId  String
  device    DeviceRegistration @relation(fields: [deviceId], references: [deviceId], onDelete: Cascade)
  /// Set from JWT on subscribe. The webhook handler refuses push delivery
  /// when this value does not match DeviceRegistration.accountId so a stale
  /// ClientIdentifier from a previous account on the same device cannot
  /// wake the current account's device.
  accountId String?            @db.Uuid
  addedAt   DateTime           @default(now())
  updatedAt DateTime           @updatedAt

  @@index([deviceId])
}

model RuntimeConfig {
  key       String   @id
  value     String
  updatedAt DateTime @updatedAt
}

model InviteCode {
  id              String                 @id @default(uuid()) @db.Uuid
  code            String                 @unique @db.VarChar(8)
  name            String?                @db.VarChar(255)
  maxRedemptions  Int                    @default(1)
  redemptionCount Int                    @default(0)
  createdAt       DateTime               @default(now())
  redeemedAt      DateTime? // kept for backwards compat — updated on each redemption
  batchLabel      String?                @db.VarChar(255)
  parentCodeId    String?                @db.Uuid
  parentCode      InviteCode?            @relation("CodeLineage", fields: [parentCodeId], references: [id])
  childCodes      InviteCode[]           @relation("CodeLineage")
  redemptions     InviteCodeRedemption[] @relation("CodeRedemptions")
  generatedFrom   InviteCodeRedemption[] @relation("GeneratedCode")

  @@index([batchLabel])
  @@index([redeemedAt])
  @@index([parentCodeId])
}

model InviteCodeRedemption {
  id           String      @id @default(uuid()) @db.Uuid
  inviteCodeId String      @db.Uuid
  inviteCode   InviteCode  @relation("CodeRedemptions", fields: [inviteCodeId], references: [id])
  childCodeId  String?     @db.Uuid
  childCode    InviteCode? @relation("GeneratedCode", fields: [childCodeId], references: [id])
  redeemedAt   DateTime    @default(now())

  @@index([inviteCodeId])
  @@index([childCodeId])
}

model Account {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authMethods              AuthMethod[]
  agentTemplates           AgentTemplate[]
  agentTemplateGenerations AgentTemplateGeneration[]
  devices                  DeviceRegistration[]
  credits                  UserCredits?
  creditLedger             CreditLedger[]
  subscriptions            Subscription[]
  connectionGrants         ConnectionGrant[]
}

/// Consent record for backend-mediated Composio tool execution (fork Y).
///
/// iOS issues one row when the owner approves a capability request: "owner
/// (ownerAccountId) lets agent (granteeInboxId) use `toolkit` in this
/// conversation." The backend's POST /v2/composio/exec reads these to decide
/// whether an agent call is authorized, then resolves the connection and calls
/// Composio itself — the agent never holds or names a connection id.
///
/// ownerAccountId is set from the issuer's authenticated JWT (never a body
/// field), so an owner can only grant access to their own connections.
/// connectionId is the Composio connected-account id (a bearer capability); it
/// is stored backend-side and never returned to an agent. See
/// docs/plans/composio-exec-grant-mediation.md.
model ConnectionGrant {
  id             String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  ownerAccountId String    @db.Uuid
  ownerInboxId   String
  granteeInboxId String
  conversationId String
  toolkit        String
  /// Allowed Composio action slugs (e.g. GOOGLECALENDAR_EVENTS_LIST). Empty
  /// means the whole toolkit; tighten to per-action as scoped sessions prove out.
  /// Legacy/transition: superseded by bundleIds, kept as a fallback.
  actions        String[]  @default([])
  /// Granted permission-bundle ids (e.g. "calendar.events"). The backend
  /// resolves these to Composio actions at exec via bundles.config.ts — clients
  /// never see slugs. Empty + empty actions ⇒ whole toolkit (transition default).
  bundleIds      String[]  @default([])
  /// Catalog service version the client granted against. Stored for
  /// audit/telemetry only — exec always resolves bundles against the CURRENT
  /// catalog, never this value.
  serviceVersion Int?
  /// Composio connected-account id, when iOS pins it. Bearer capability —
  /// backend-only, never sent to an agent. Null ⇒ resolve from (owner, toolkit).
  connectionId   String?
  expiresAt      DateTime?
  revokedAt      DateTime?
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  owner Account @relation(fields: [ownerAccountId], references: [id], onDelete: Cascade)

  @@unique([ownerAccountId, granteeInboxId, conversationId, toolkit])
  @@index([granteeInboxId, conversationId])
  @@index([ownerAccountId])
}

enum PublishStatus {
  draft
  published
  unlisted
  archived
}

model AgentTemplate {
  id               String        @id @default(uuid()) @db.Uuid
  slug             String
  ownerAccountId   String        @db.Uuid
  forkedFromId     String?       @db.Uuid
  agentName        String
  jobTitle         String?
  description      String?
  prompt           String        @db.Text
  category         String?
  emoji            String?
  avatarUrl        String?
  tools            String[]      @default([])
  connections      String[]      @default([])
  version          Int           @default(1)
  firstPublishedAt DateTime?
  status           PublishStatus @default(draft)
  featured         Boolean       @default(false)
  // Curation weight for the featured gallery, sorted descending: the heaviest
  // template leads. 0 means unranked, so anything featured without an explicit
  // weight sits below every curated row instead of displacing the lead slot.
  featuredRank     Int           @default(0)
  createdAt        DateTime      @default(now())
  updatedAt        DateTime      @updatedAt

  owner       Account                   @relation(fields: [ownerAccountId], references: [id])
  forkedFrom  AgentTemplate?            @relation("Forks", fields: [forkedFromId], references: [id])
  forks       AgentTemplate[]           @relation("Forks")
  generations AgentTemplateGeneration[]

  @@index([slug])
  @@index([status, createdAt, id])
  @@index([status, category, createdAt, id])
  @@index([status, featured, createdAt, id])
  @@index([status, featured, featuredRank, id])
  @@index([forkedFromId])
}

// Short, quirky prompt suggestions served to the maker's empty composer.
// Dedicated table, intentionally separate from published AgentTemplates: a
// hint is just a string, with no owner, slug, avatar, tools, or connections.
// The public read endpoint enforces the API contract (<= 350 chars, capped
// item count); the DB column stays generous TEXT so curation isn't lossy.
model AgentPromptHint {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  text      String   @db.Text
  published Boolean  @default(true)
  sortOrder Int      @default(0)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([published, sortOrder])
}

enum GenerationStatus {
  pending
  running
  done
  failed
}

model AgentTemplateGeneration {
  id              String           @id @default(uuid()) @db.Uuid
  ownerAccountId  String           @db.Uuid
  source          String
  idempotencyKey  String
  inputs          Json
  twitterContext  Json?
  clientDeviceId  String?          @db.VarChar(128)
  // Partial AgentTemplate the caller pinned at submit time — a
  // server-allowlisted subset of fields the executor overlays onto the
  // generator's output so the persisted template uses the caller-pinned
  // values verbatim. The wire shape is validated by `TemplatePrefillSchema`
  // in `handlers/generations-post.ts`; new pinnable fields are added there.
  // Stored on the row because the executor is fire-and-forget and re-reads
  // the row; this is the simplest handoff channel.
  prefill         Json?
  // Optional caller-supplied builder/system prompt override (admin dashboard);
  // privileged (agent-API-key only) and fed to the generator by the executor.
  builderPrompt   String?          @db.Text
  // Optional caller-supplied model override (admin dashboard); privileged
  // (agent-API-key only) and used by the executor for the main generation call
  // instead of the default builder model.
  builderModel    String?          @db.VarChar(256)
  // Neutral service ids (e.g. "googlecalendar") the caller flagged the agent as
  // using — validated against the supported-services catalog at submit, read by
  // the executor (which appends a capabilities directive to the generator and
  // overlays these onto the produced template's `connections`). Open: stamping a
  // connection grants nothing, it only records the template uses the service;
  // the grant itself is issued later, at provisioning. Stored raw like
  // `inputs`/`prefill` (the executor normalizes against the catalog).
  connections     String[]         @default([])
  // The draft agent shown while the build runs — { agentName, emoji,
  // description } — written by the distill stage and surfaced as `preview` on
  // in-progress (202) poll responses. The full agent arrives via `templateId`
  // on the terminal 200, so only the identity is ever populated here. Named
  // `preview`, not `draft`, since `draft` is a PublishStatus value. Distinct
  // from the caller-pinned `prefill` INPUT above (kept separate so the
  // idempotency dedupe, which compares `prefill`, is unaffected).
  preview         Json?
  // Build-narration lines (string[]) pregenerated by the distill stage,
  // surfaced as `progressPhrases` on in-progress (202) poll responses. Both
  // this and `preview` are omitted from the terminal 200.
  progressPhrases Json?
  templateId      String?          @db.Uuid
  publishStatus   PublishStatus    @default(draft)
  reply           String?          @db.Text
  status          GenerationStatus @default(pending)
  error           String?          @db.Text
  expiresAt       DateTime?
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt

  owner    Account        @relation(fields: [ownerAccountId], references: [id])
  template AgentTemplate? @relation(fields: [templateId], references: [id])

  @@unique([ownerAccountId, idempotencyKey])
  @@index([ownerAccountId])
  @@index([status, updatedAt])
  @@index([expiresAt])
}

model AuthMethod {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  accountId   String   @db.Uuid
  // Validated by CHECK constraint in migration + AuthMethodType TS union in
  // src/accounts/auth-method-type.ts. Postgres enums are avoided because
  // adding/removing values is non-transactional and new values can't be used
  // in the same migration that defines them.
  type        String
  externalKey String
  addedAt     DateTime @default(now())

  account Account @relation(fields: [accountId], references: [id])

  @@unique([type, externalKey])
  @@index([accountId])
}

model AuthNonce {
  nonce     String   @id
  createdAt DateTime @default(now())
}

enum LedgerReason {
  consume
  grant
  refill
  adjust
}

model UserCredits {
  accountId String   @id @db.Uuid
  balance   BigInt
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  account   Account  @relation(fields: [accountId], references: [id])
}

model GrantKind {
  id            String         @id @db.VarChar(64)
  name          String
  description   String?
  active        Boolean        @default(true)
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
  ledgerEntries CreditLedger[]
}

model CreditLedger {
  id               String       @id @default(uuid()) @db.Uuid
  accountId        String       @db.Uuid
  delta            BigInt
  reason           LedgerReason
  idempotencyKey   String
  scope            String       @default("transaction") @db.VarChar(32)
  balanceAfter     BigInt?
  usdCostMicros    BigInt?
  markupRate       Decimal?     @db.Decimal(8, 4)
  creditsPerDollar BigInt?
  model            String?
  requestId        String?
  note             String?
  grantKindId      String?      @db.VarChar(64)
  grantKind        GrantKind?   @relation(fields: [grantKindId], references: [id], onDelete: SetNull, onUpdate: Cascade)
  account          Account      @relation(fields: [accountId], references: [id])
  createdAt        DateTime     @default(now())

  @@unique([accountId, idempotencyKey])
  @@index([accountId, createdAt])
  @@index([requestId])
  @@index([grantKindId])
}

enum SubscriptionPeriod {
  monthly
  annual
}

enum SubscriptionStatus {
  trial
  active
  grace
  billingRetry
  expired
  revoked
}

enum AppleEnv {
  sandbox
  production
}

enum BillingProvider {
  apple
  googlePlay
}

model Subscription {
  id                    String             @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  accountId             String             @db.Uuid
  provider              BillingProvider
  productId             String
  // Plain string column (no Postgres enum). Application code defines
  // the valid set in src/subscriptions/tiers.ts. Currently only `plus`
  // is written; the previous `builder`/`pro` values were backfilled to
  // `plus` in migration 20260529190000_drop_subscription_tier_enum.
  tier                  String
  period                SubscriptionPeriod
  status                SubscriptionStatus
  // Apple's stable per-subscription-line identifier (constant across renewals
  // and tier upgrades within a subscription group). Nullable: Google rows
  // identify themselves by purchaseToken instead.
  originalTransactionId String?
  // UUID our client generates per-purchase and passes to StoreKit; Apple
  // echoes it back on every receipt/notification. Lets us bind an Apple
  // subscription to the signed-in `accountId` without storing Apple IDs.
  appAccountToken       String?            @db.Uuid
  // Google Play purchaseToken. Per-purchase opaque token; rotated by Play on
  // upgrade/downgrade (the new purchase carries the old one in
  // linkedPurchaseToken).
  purchaseToken         String?
  linkedPurchaseToken   String?
  // Google Play obfuscatedExternalAccountId — analog of Apple's
  // appAccountToken. Android client sets it via
  // BillingFlowParams.Builder.setObfuscatedAccountId at purchase time.
  obfuscatedAccountId   String?
  startedAt             DateTime
  currentPeriodStart    DateTime
  currentPeriodEnd      DateTime
  // Mirrors Apple's `autoRenewStatus` / Google's `autoRenewingEnabled`.
  willRenew             Boolean            @default(true)
  // True while the active period is an introductory offer (free trial or
  // pay-as-you-go). Derived at verify time from the JWS `offerType` (Apple)
  // or the active line item's offer tags (Google).
  isInTrial             Boolean            @default(false)
  cancelledAt           DateTime?
  gracePeriodEnd        DateTime?
  // Apple-only: which Apple environment the sub came from. Null for Google.
  environment           AppleEnv?
  createdAt             DateTime           @default(now())
  updatedAt             DateTime           @updatedAt

  account  Account          @relation(fields: [accountId], references: [id])
  receipts BillingReceipt[]

  // Per-provider uniqueness. Postgres treats NULLs as distinct in unique
  // indexes, so an Apple row (purchaseToken=NULL) and a Google row
  // (originalTransactionId=NULL) don't collide here.
  @@unique([provider, originalTransactionId], name: "subscription_apple_otx_unique")
  @@unique([provider, appAccountToken], name: "subscription_apple_aat_unique")
  @@unique([provider, purchaseToken], name: "subscription_play_token_unique")
  @@unique([provider, obfuscatedAccountId], name: "subscription_play_oid_unique")
  @@index([accountId])
  @@index([accountId, provider])
  @@index([status, currentPeriodEnd])
}

model BillingReceipt {
  id                     String          @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  subscriptionId         String          @db.Uuid
  provider               BillingProvider
  // Explicit dedupe key. Apple verify rows use `apple-verify:{transactionId}`;
  // Apple S2S rows use `apple-ssn:{notificationUUID}`; Google verify rows use
  // `play-verify:{orderId|purchaseToken}`; Google RTDN rows use
  // `play-rtdn:{messageId}`.
  idempotencyKey         String          @unique
  // Generic external dedup id — Apple notificationUUID or Pub/Sub messageId.
  // Null for direct verify receipts.
  externalNotificationId String?         @unique
  // Per-transaction identifier. Apple: transactionId (each renewal mints a
  // new one). Google: latestOrderId from the fetched purchase. Not unique:
  // multiple notifications can reference the same transaction.
  transactionId          String
  notificationType       String
  notificationSubtype    String?
  // Raw payload from the provider — Apple JWS or the Google purchase JSON /
  // RTDN envelope. Kept for audit + replay.
  signedPayload          String          @db.Text
  receivedAt             DateTime        @default(now())

  subscription Subscription @relation(fields: [subscriptionId], references: [id])

  @@index([transactionId])
  @@index([provider, transactionId])
  @@index([subscriptionId, receivedAt])
}

model TelemetryBatch {
  batchId    String   @id
  receivedAt DateTime @default(now())

  @@index([receivedAt])
}

model AdminAudit {
  id             String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  accountId      String   @db.Uuid
  actorEmail     String
  action         String
  deltaCredits   BigInt
  reason         String
  idempotencyKey String
  createdAt      DateTime @default(now())

  @@unique([accountId, idempotencyKey])
  @@index([accountId, createdAt])
  @@index([actorEmail, createdAt])
  @@index([createdAt, id])
}

/// Per-PR agent variant (dev XMTP network only). A named bundle pinning an
/// ephemeral runtime (assistantWorkerUrl) and/or a bench builder-prompt slug
/// (builderPromptSlug). Written only by the convos-assistants
/// variant CI (agent API key + dev-only route gate); read by the dev app picker
/// and applied server-side at generation + join. `slug` is the
/// natural key — the PR slug (e.g. "pr-1234"); the ephemeral host is
/// ephemeral-<slug>.convos.fun. Ownerless: there is no Account relation because
/// variants are infrastructure, not user content.
model AgentVariant {
  slug               String    @id
  label              String
  whatToTest         String    @db.Text
  /// Lifecycle: building | ready | failed | stale. Plain string (no Postgres
  /// enum) — validated in app code via AgentVariantStatusSchema, matching the
  /// project's move away from non-transactional enum migrations (see the
  /// AuthMethod.type / Subscription.tier comments).
  status             String    @default("building")
  /// The ephemeral runtime base URL, or null for the default dev runtime.
  assistantWorkerUrl String?
  /// A bench/Braintrust prompt slug, or null for the canonical generator.
  builderPromptSlug  String?
  prUrl              String
  branch             String
  commit             String
  expiresAt          DateTime?
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt

  @@index([status, createdAt])
}