Database

ZeroDrag uses PostgreSQL with Prisma ORM for type-safe database access and migrations.

What This System Does

The database system provides:

  • Type-safe database access via Prisma Client
  • Schema management and migrations
  • User accounts and authentication data
  • Subscription and plan management
  • Entitlement and feature gating data
  • AI usage tracking (optional)

Why It Exists

The database is the single source of truth for all user data, subscriptions, and entitlements. Prisma provides type safety, migrations, and a clean API for database operations.

When You Need to Care About It

You'll interact with the database when:

  • Adding new data models or fields
  • Creating custom queries or mutations
  • Seeding initial data (plans, entitlements)
  • Viewing or debugging data (Prisma Studio)
  • Running migrations in production

Key Concepts

Prisma Schema

The schema file (prisma/schema.prisma) defines all models, relationships, and database configuration. Changes here require generating a new Prisma Client.

Prisma Client

Generated TypeScript client for type-safe database access. Import from /lib/db.ts.

Migrations vs Push

prisma db push is for development (applies schema changes immediately).prisma migrate creates migration files for production deployments.

Schema Overview

Main tables in the database:

users

User accounts with email, name, role, and GitHub access fields.

accounts

OAuth provider links (Google, etc.). Managed by NextAuth.

sessions

Active user sessions. Managed by NextAuth.

plans

Subscription plans (free, pro) with provider-specific IDs (Stripe price ID, Razorpay plan ID).

subscriptions

User subscriptions. Provider-agnostic with status, period dates, and cancellation flags.

entitlements

Feature flags/permissions (basic_access, pro_features, api_access, etc.).

plan_entitlements

Junction table linking plans to entitlements. Defines which features each plan includes.

ai_usage

Optional AI API usage tracking. Only used if AI features are enabled.

Important Files

/prisma/schema.prisma

Database schema definition. Add new models and fields here.

/prisma/seed.ts

Initial data setup. Creates default plans and entitlements.

/lib/db.ts

Prisma Client instance. Import this for database queries.

Setup Commands

Generate Prisma Client

bash
pnpm db:generate

Generates TypeScript types from schema. Run after modifying schema.prisma.

Push Schema to Database

bash
pnpm db:push

Applies schema changes to database. Use for development.

Seed Initial Data

bash
pnpm db:seed

Creates default plans and entitlements. Safe to run multiple times (uses upsert).

All-in-One Setup

bash
pnpm db:setup

Runs generate, push, and seed in sequence. Use for initial setup.

Open Prisma Studio

bash
pnpm db:studio

Opens visual database browser. Useful for viewing and editing data.

Default Plans and Entitlements

The seed script creates:

Free Plan

  • Price: $0
  • Entitlements: basic_access

Pro Plan

  • Price: $29/month
  • Entitlements: basic_access, pro_features, api_access, priority_support

Default Entitlements

  • basic_access - Dashboard and basic features
  • pro_features - Advanced tools
  • api_access - REST API endpoints
  • priority_support - Faster support response

What You Can Customize

Adding New Models

Add models to prisma/schema.prisma, then run pnpm db:push.

Adding Fields to Existing Models

Safe to add optional fields. Required fields require data migration.

Modifying Seed Data

Edit prisma/seed.ts to change default plans, prices, or entitlements.

Adding New Plans

1. Add to seed script
2. Create in payment provider dashboard
3. Link provider IDs using update scripts

What NOT to Touch

Core Schema Relationships

Don't modify the relationship structure between plans, subscriptions, and entitlements. This is critical for the payment and feature gating systems.

NextAuth Models

The accounts and sessions models are managed by NextAuth. Don't modify their structure.

Related Sections