reference data models

Data models

The schema is prisma/schema.prisma, a PostgreSQL datasource with directUrl and the vector extension enabled. The client generator enables postgresqlExtensions and produces @prisma/client. All IDs are cuid() strings, and every model carries createdAt / updatedAt unless noted.

This page is a summary. For data access patterns, the Prisma singleton, and how the desktop router reaches the database, see Data and Prisma.

Models

Model	Table	Key fields	Relations
User	users	email unique, supabaseId unique, name?, avatarUrl?, locale default id, deletedAt? (soft delete)	prompts, evaluations, apiKeys, subscription, usageRecords
UserApiKey	user_api_keys	provider (LLMProvider), encryptedKey, iv, authTag; unique on [userId, provider]	user on delete cascade
Prompt	prompts	rawInput, optimizedText, taskType, tone, format, targetLlm, tags String[], isPublic, embedding (vector(1536)), deletedAt? (soft delete)	user cascade, evaluations
Evaluation	evaluations	promptId?, promptText, structureScore, clarityScore, completenessScore, specificityScore, overallScore (all Float), feedback Json, suggestions String[], provider, model	prompt on delete set null, user cascade
PromptTemplate	prompt_templates	slug unique, name, description, category (TemplateCategory), template Json, variables Json, isActive	none
Subscription	subscriptions	userId unique, tier (SubscriptionTier), status (SubscriptionStatus), billingInterval?, period fields, gatewayCustomerId?, gatewaySubscriptionId?	user cascade, payments
Payment	payments	subscriptionId, amount Int (IDR), currency default IDR, status (PaymentStatus), method? (PaymentMethod), gatewayPaymentId? unique, gatewayResponse? Json, invoiceNumber? unique	subscription cascade
UsageRecord	usage_records	type (UsageType), count, date (@db.Date); unique on [userId, type, date]	user cascade
FeatureFlag	feature_flags	key unique, name, description?, enabledFor SubscriptionTier[], isGlobal	none
RateLimitCounter	rate_limit_counters	action, scope (RateLimitScope), keyHash, windowStart, windowEnd, count; unique on [action, scope, keyHash, windowStart]	none
EmailJob	email_jobs	type (EmailJobType), toEmail, payload Json, status (EmailJobStatus), attempts, maxAttempts, nextAttemptAt, lastAttemptAt?, sentAt?, lastError?, idempotencyKey? unique	none

Indexes

• Prompt: [userId, deletedAt], [taskType], [isPublic, deletedAt].
• Evaluation: [userId], [promptId].
• PromptTemplate: [category, isActive].
• Subscription: [status], [currentPeriodEnd].
• Payment: [subscriptionId], [status], [gatewayPaymentId].
• UsageRecord: [userId, date].
• RateLimitCounter: [windowEnd].
• EmailJob: [status, nextAttemptAt].

Soft delete

Two models carry a nullable deletedAt instead of hard-deleting: User and Prompt. Prompt queries filter on deletedAt through the [userId, deletedAt] and [isPublic, deletedAt] indexes, so listings hide soft-deleted rows while the record remains for reference.

Vector column

Prompt.embedding is an Unsupported("vector(1536)") column. The datasource declares extensions = [vector], and lib/constants.ts fixes EMBEDDING_DIMENSIONS = 1536 with EMBEDDING_MODEL = 'text-embedding-3-small'. Because Prisma does not type unsupported columns, embedding reads and writes go through raw SQL.

Enums

• LLMProvider: CLAUDE, OPENAI, MISTRAL, QWEN, GROK, LOCAL.
• TaskType: CODING, EMAIL, ANALYSIS, CREATIVE, RESEARCH, BUSINESS, EDUCATION, MARKETING, GENERAL.
• PromptTone: PROFESSIONAL, CASUAL, TECHNICAL, ACADEMIC, CREATIVE, PERSUASIVE.
• OutputFormat: DETAILED, CONCISE, STRUCTURED, STEP_BY_STEP, CONVERSATIONAL.
• TemplateCategory: CODING, EMAIL, ANALYSIS, CREATIVE, RESEARCH, BUSINESS, EDUCATION, MARKETING.
• SubscriptionTier: GRATIS, PRO, TIM, ENTERPRISE.
• SubscriptionStatus: ACTIVE, TRIALING, PAST_DUE, CANCELED, EXPIRED.
• BillingInterval: MONTHLY, ANNUAL.
• PaymentStatus: PENDING, PAID, FAILED, REFUNDED, EXPIRED.
• PaymentMethod: CREDIT_CARD, BANK_TRANSFER, EWALLET_GOPAY, EWALLET_OVO, EWALLET_DANA, EWALLET_SHOPEEPAY, QRIS.
• UsageType: TRANSFORM, OPTIMIZE, EVALUATE, RECOMMEND.
• RateLimitScope: IP, EMAIL.
• EmailJobType: WELCOME.
• EmailJobStatus: PENDING, PROCESSING, RETRYING, SENT, FAILED.