Database Schema | Client Guides

Advanced Developer Content

Prerequisites:

Understanding of relational databases
Familiarity with SQL concepts
Basic knowledge of data relationships

This guide explains what data your SaaS manages and how it's organized. The system uses Prisma ORM with PostgreSQL (production) and SQLite (local development).

Overview

Your SaaS manages 4 core types of data:

Users: People who sign up and use your application
Subscriptions: Billing and payment information
Todos: Personal task lists (example feature)
Admins: Users with administrative privileges

ℹ️

Why This Matters

Understanding this structure helps you customize your SaaS. Each user owns their subscription and data. When a user deletes their account, all their data is automatically removed.

Core Data Models

User Model

The User model is the center of your B2C SaaS. Each user has:

Authentication: Email and OAuth connections (GitHub, Google)
Subscription: One paid plan (free, pro, or enterprise)
Todos: Their personal todo list items
Profile: Name, email, profile image

Key Relationships:

One user → One subscription (or none if on free plan)
One user → Many todos (personal task list)
User data is automatically deleted when account is removed

ℹ️

Why This Matters

Users own their data (todos, subscription). This B2C model is simpler than multi-tenant systems - each user manages their own content independently.

Subscription Model

The Subscription model manages billing and payment information. Each user has at most one active subscription.

What It Tracks:

Payment Provider: Either Stripe or LemonSqueezy
Plan Tier: Free, Pro, or Enterprise
Subscription Status: Active, canceled, past due, etc.
Billing Period: When the current period starts and ends
Customer ID: Reference to the payment provider's customer record

ℹ️

Why This Matters

This model connects your user accounts to payment providers. When a user upgrades from Free to Pro, this is where that information is stored. The system supports both Stripe and LemonSqueezy simultaneously.

Todo Model

The Todo model demonstrates user-owned content. It's a simple example you can use as a template for your own features.

What It Includes:

Content: The task description
Status: Completed or not completed
Pinned: Priority tasks appear first
Owner: Which user created this todo

💡

Using as a Template

The Todo model shows the basic pattern for user-owned data: each item belongs to one user, and users can only see/edit their own items. You can copy this pattern when building your own features.

Admin Model

The Admin model controls who has administrative access to your SaaS. Admin users can view system statistics, manage users, and access special features.

How It Works:

Admins are identified by email address
When someone logs in, the system checks if their email is in the Admin table
Admins see extra menu items and can access the admin dashboard
You can add/remove admins through the admin management interface

⚠️

Security Note

Admin access is powerful - admins can view all user data and system statistics. Only give admin access to trusted team members.

How Data Connects

Here's a visual representation of how your data is organized:

┌─────────────────┐
│      User       │  (Person who signed up)
│                 │
│ - Email         │
│ - Name          │
│ - Image         │
└────────┬────────┘
         │
         │ owns
         │
         ├──► Subscription (billing info)
         │    - Plan: free/pro/enterprise
         │    - Status: active/canceled
         │    - Provider: Stripe or LemonSqueezy
         │
         └──► Todos (personal tasks)
              - Content: "Task description"
              - Completed: true/false
              - Pinned: true/false

┌─────────────────┐
│     Admin       │  (Separate - not linked to User)
│                 │
│ - Email         │  (admin@yourcompany.com)
└─────────────────┘

ℹ️

Key Insight

Each user is the center of their own data. When you delete a user account, their subscription and todos are automatically removed too. This is called "cascade delete" - it keeps your database clean.

Common Questions

Can I add custom fields to these models?

Yes! See the Adding Database Models guide to learn how to add fields or create new models.

Where is the database configuration?

Database setup is handled through environment variables. See the Quick Start guide for local development, or Production Deployment for production setup.

How do I view my database?

Run npm run db:studio to open Prisma Studio - a visual interface for browsing and editing your data at http://localhost:5555.

What about OAuth Account and Session data?

These are managed automatically by Auth.js (the authentication library). You don't need to work with them directly - they handle login sessions and OAuth connections behind the scenes.

Related Guides

Adding Database Models

Learn how to add custom fields or create new data models for your features

Billing Setup

Configure Stripe and LemonSqueezy payment providers for your SaaS

Creating Features

Build complete features using the database → API → UI workflow

Prisma Documentation ↗

Official Prisma docs for advanced database features and syntax

💡

Next Steps

Now that you understand what data your SaaS manages:

Learn Adding Database Models to customize your data structure
Explore Billing Setup to understand subscription management
Review Creating Features for building custom functionality

Overview

Your SaaS manages 4 core types of data:

Users: People who sign up and use your application

Subscriptions: Billing and payment information

Todos: Personal task lists (example feature)

Admins: Users with administrative privileges

ℹ️

Why This Matters

Understanding this structure helps you customize your SaaS. Each user owns their subscription and data. When a user deletes their account, all their data is automatically removed.

Core Data Models

User Model

The User model is the center of your B2C SaaS. Each user has:

Authentication: Email and OAuth connections (GitHub, Google)
Subscription: One paid plan (free, pro, or enterprise)
Todos: Their personal todo list items
Profile: Name, email, profile image

Key Relationships:

One user → One subscription (or none if on free plan)
One user → Many todos (personal task list)
User data is automatically deleted when account is removed

ℹ️

Why This Matters

Users own their data (todos, subscription). This B2C model is simpler than multi-tenant systems - each user manages their own content independently.

Subscription Model

The Subscription model manages billing and payment information. Each user has at most one active subscription.

What It Tracks:

Payment Provider: Either Stripe or LemonSqueezy
Plan Tier: Free, Pro, or Enterprise
Subscription Status: Active, canceled, past due, etc.
Billing Period: When the current period starts and ends
Customer ID: Reference to the payment provider's customer record

ℹ️

Why This Matters

Todo Model

The Todo model demonstrates user-owned content. It's a simple example you can use as a template for your own features.

What It Includes:

Content: The task description
Status: Completed or not completed
Pinned: Priority tasks appear first
Owner: Which user created this todo

💡

Using as a Template

The Todo model shows the basic pattern for user-owned data: each item belongs to one user, and users can only see/edit their own items. You can copy this pattern when building your own features.

Admin Model

The Admin model controls who has administrative access to your SaaS. Admin users can view system statistics, manage users, and access special features.

How It Works:

Admins are identified by email address
When someone logs in, the system checks if their email is in the Admin table
Admins see extra menu items and can access the admin dashboard
You can add/remove admins through the admin management interface

⚠️

Security Note

Admin access is powerful - admins can view all user data and system statistics. Only give admin access to trusted team members.

How Data Connects

Here's a visual representation of how your data is organized:

┌─────────────────┐
│      User       │  (Person who signed up)
│                 │
│ - Email         │
│ - Name          │
│ - Image         │
└────────┬────────┘
         │
         │ owns
         │
         ├──► Subscription (billing info)
         │    - Plan: free/pro/enterprise
         │    - Status: active/canceled
         │    - Provider: Stripe or LemonSqueezy
         │
         └──► Todos (personal tasks)
              - Content: "Task description"
              - Completed: true/false
              - Pinned: true/false

┌─────────────────┐
│     Admin       │  (Separate - not linked to User)
│                 │
│ - Email         │  (admin@yourcompany.com)
└─────────────────┘

ℹ️

Key Insight

Each user is the center of their own data. When you delete a user account, their subscription and todos are automatically removed too. This is called "cascade delete" - it keeps your database clean.

Common Questions

Can I add custom fields to these models?

Yes! See the Adding Database Models guide to learn how to add fields or create new models.

Where is the database configuration?

Database setup is handled through environment variables. See the Quick Start guide for local development, or Production Deployment for production setup.

How do I view my database?

Run npm run db:studio to open Prisma Studio - a visual interface for browsing and editing your data at http://localhost:5555.

What about OAuth Account and Session data?

These are managed automatically by Auth.js (the authentication library). You don't need to work with them directly - they handle login sessions and OAuth connections behind the scenes.