systems data and prisma

Data and Prisma

Active contributors: ferdiiskandar

Purpose

This system is the persistent store for everything the capsule keeps: users, encrypted provider keys, saved prompts, evaluations, templates, subscriptions, payments, daily usage, feature flags, rate-limit counters, and queued emails. PostgreSQL backs it through Prisma, with the pgvector extension enabled for semantic search.

Directory layout

  prisma/schema.prisma        models and enums
  prisma/migrations/          two SQL migrations and the lock file
  lib/db/prisma.ts            shared client singleton

Key abstractions

Symbol	Kind	Path
prisma	shared PrismaClient instance	lib/db/prisma.ts
User, UserApiKey, Prompt, Evaluation, PromptTemplate	core models	prisma/schema.prisma
Subscription, Payment, UsageRecord, FeatureFlag	billing and usage models	prisma/schema.prisma
RateLimitCounter, EmailJob	infrastructure models	prisma/schema.prisma
LLMProvider, TaskType, PromptTone, OutputFormat, TemplateCategory	content enums	prisma/schema.prisma
SubscriptionTier, SubscriptionStatus, BillingInterval, PaymentStatus, PaymentMethod, UsageType	billing enums	prisma/schema.prisma
RateLimitScope, EmailJobType, EmailJobStatus	infrastructure enums	prisma/schema.prisma

The datasource is PostgreSQL via DATABASE_URL with a DIRECT_URL for migrations, and the generator enables the postgresqlExtensions preview feature with extensions = [vector]. Eleven models exist. User is the hub: it owns prompts, evaluations, API keys, one subscription, and usage records, all with cascade deletes, and is matched to Supabase by supabaseId. UserApiKey is unique per (userId, provider) and stores the AES-256-GCM encryptedKey, iv, and authTag. Prompt carries the content fields, a tags array, an isPublic flag, an Unsupported("vector(1536)") embedding column, and a nullable deletedAt for soft delete; its indexes cover (userId, deletedAt), taskType, and (isPublic, deletedAt). Evaluation links optionally to a prompt (delete sets it null) and always to a user, scoring structure, clarity, completeness, and specificity plus an overall score.

Subscription is one row per user with a tier, status, optional billing interval, period and trial dates, and gateway ids. Payment belongs to a subscription, stores integer IDR amounts, and keeps a unique gatewayPaymentId and invoiceNumber plus a gatewayResponse JSON blob used to recover the requested tier at activation. UsageRecord is unique per (userId, type, date) with a @db.Date column, which is what makes the daily quota upsert atomic. PromptTemplate is keyed by a unique slug with JSON template and variables. FeatureFlag targets tiers through an enum array. RateLimitCounter is unique per (action, scope, keyHash, windowStart) with an index on windowEnd. EmailJob tracks status, attempts, maxAttempts, nextAttemptAt, and a unique idempotencyKey. Fourteen enums back these columns.

Two migrations exist: 20260322000000_init, which creates the schema, unique indexes, foreign keys, and the vector extension, and 20260322222500_shared_rate_limit_email_queue, which adds the rate-limit and email-job tables. migration_lock.toml records the PostgreSQL provider.

lib/db/prisma.ts exports one prisma instance, cached on globalThis outside production to survive hot reloads. Logging is query/error/warn in development and error only otherwise.

How it works

graph TD
    App[lib/* and desktop/*] --> Client[lib/db/prisma.ts singleton]
    Client --> PG[(PostgreSQL)]
    PG --> Vector[pgvector extension]
    Schema[prisma/schema.prisma] --> Client
    Migrations[prisma/migrations] --> PG
    Generate[db:generate placeholder URL] --> Client

Loading

The db:generate script lets Prisma Client generate without a live database. It defaults DATABASE_URL and DIRECT_URL to postgresql://placeholder:placeholder@127.0.0.1:5432/placeholder?schema=public when they are unset, then spawns the Prisma CLI's generate entry point directly. This keeps typecheck and the desktop build working in environments that have no database connection configured.

Integration points

Modules reach the database only through the singleton: provider keys in LLM providers, users in Authentication, subscriptions and usage in Billing and subscriptions, counters and jobs in Email and rate limiting, and vectors in Embeddings and search. A field-level summary is in Data models; connection variables are in Configuration.

Entry points for modification

• Add or change a model, enum, or index: prisma/schema.prisma, then a new migration under prisma/migrations/.
• Change connection settings or logging: lib/db/prisma.ts and the datasource block.
• Change build-time generation: the db:generate script in package.json.

Key source files

File	What it holds
prisma/schema.prisma	All 11 models, 14 enums, datasource, and indexes
lib/db/prisma.ts	Cached Prisma client singleton and log level
prisma/migrations/20260322000000_init/migration.sql	Initial schema and vector extension
prisma/migrations/20260322222500_shared_rate_limit_email_queue/migration.sql	Rate-limit and email-job tables
package.json	db:generate placeholder-URL script