Database Schema

The starter kit uses Drizzle ORM with PostgreSQL for type-safe database operations.

Overview

All schema definitions are in /src/db/schema/ with the main schema exported from /src/db/schema.ts.

Database Providers

The starter kit supports various PostgreSQL providers. By default, it is configured for Neon using the @neondatabase/serverless driver for optimal performance in serverless/edge environments.

Neon (Default)

No additional setup is required beyond setting your DATABASE_URL.

Standard PostgreSQL (Local, Self-hosted, Supabase)

To use a standard PostgreSQL provider, you need to update the database initialization in src/db/index.ts. This is common for local development or when using providers like Supabase without their serverless driver.

Install dependencies:
```
pnpm add pg
pnpm add -D @types/pg
```

Update src/db/index.ts: The starter kit includes a pre-written configuration for standard PostgreSQL. Simply comment out the Neon section and uncomment the standard PostgreSQL section:

// src/db/index.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
export const db = drizzle(pool, { schema });

Drizzle Config: Your drizzle.config.ts is already compatible with any PostgreSQL database as it uses the standard connection string from your .env file.
Local Development: If you're running PostgreSQL locally (e.g., via Docker or native installation), your .env would look like:
```
DATABASE_URL="postgresql://postgres:password@localhost:5432/your_database"
```

Schema Files

File	Description
`schema.ts`	Main schema file with auth and core tables
`schema/blog.ts`	Blog posts, categories, tags
`schema/subscription.ts`	Subscription plans, payments
`schema/email-template.ts`	Email templates
`schema/testimonial.ts`	Testimonials
`schema/faq.ts`	FAQ items
`schema/changelog.ts`	Changelog entries
`schema/waitlist.ts`	Waitlist management
`schema/marketing-popup.ts`	Marketing popups

Authentication Tables

Users

Primary table for all users:

user {
  id: string (PK)
  name: string
  email: string (unique)
  emailVerified: boolean
  image: string | null
  role: "user" | "admin" (default: "user")

  // Ban management
  banned: boolean | null
  banReason: string | null
  banExpires: timestamp | null

  // Impersonation
  allowImpersonation: boolean (default: false)

  // Timestamps
  createdAt: timestamp
  updatedAt: timestamp
}

Relationships:

Has many sessions
Has many accounts (OAuth)
Has one subscription
Has many blogPosts (as author)

Sessions

User sessions for authentication:

session {
  id: string (PK)
  userId: string (FK -> user.id)
  token: string (unique)
  expiresAt: timestamp
  ipAddress: string | null
  userAgent: string | null
  impersonatedBy: string | null

  createdAt: timestamp
  updatedAt: timestamp
}

Cascade: Deletes when user is deleted

Accounts

OAuth provider accounts linked to users:

account {
  id: string (PK)
  userId: string (FK -> user.id)
  providerId: string  // "google", "github"
  accountId: string

  // OAuth tokens
  accessToken: string | null
  refreshToken: string | null
  idToken: string | null
  accessTokenExpiresAt: timestamp | null
  refreshTokenExpiresAt: timestamp | null

  scope: string | null
  password: string | null  // For email/password auth

  createdAt: timestamp
  updatedAt: timestamp
}

Verification

Email verification and password reset tokens:

verification {
  id: string (PK)
  identifier: string  // Email address
  value: string       // Token
  expiresAt: timestamp

  createdAt: timestamp | null
  updatedAt: timestamp | null
}

Blog System Tables

Blog Posts

blogPosts {
  id: integer (PK, auto-increment)
  slug: varchar(255) (unique)
  title: text
  excerpt: text | null
  content: text
  featuredImage: text | null

  // Publishing
  published: boolean (default: false)
  publishedAt: timestamp | null

  // Author
  authorId: text (FK -> user.id)

  // SEO
  seoTitle: text | null
  seoDescription: text | null

  // Metrics
  viewCount: integer (default: 0)
  likeCount: integer (default: 0)

  // Timestamps
  createdAt: timestamp
  updatedAt: timestamp
}

Indexes:
- slug (for fast lookups)
- published (for filtering)

Relationships:

Belongs to user (author)
Has many blogPostCategories
Has many blogPostTags
Has many blogPostLikes

Blog Categories

blogCategories {
  id: integer (PK, auto-increment)
  name: varchar(100)
  slug: varchar(100) (unique)
  description: text | null
  createdAt: timestamp
}

Relationships:

Has many blogPostCategories

Blog Tags

blogTags {
  id: integer (PK, auto-increment)
  name: varchar(100)
  slug: varchar(100) (unique)
  createdAt: timestamp
}

Relationships:

Has many blogPostTags

Blog Post Categories (Junction)

Many-to-many relationship between posts and categories:

blogPostCategories {
  postId: integer (FK -> blogPosts.id)
  categoryId: integer (FK -> blogCategories.id)
}

Blog Post Tags (Junction)

Many-to-many relationship between posts and tags:

blogPostTags {
  postId: integer (FK -> blogPosts.id)
  tagId: integer (FK -> blogTags.id)
}

Blog Post Likes

Tracks likes by fingerprint (anonymous users):

blogPostLikes {
  id: integer (PK, auto-increment)
  postId: integer (FK -> blogPosts.id)
  fingerprint: varchar(255)  // Browser fingerprint
  createdAt: timestamp
}

Index: (postId, fingerprint) for unique constraint

Subscription Tables

Subscription Plans

subscriptionPlans {
  id: string (PK, UUID)
  name: text  // "Free", "Pro", "Startup"
  slug: text (unique)  // "free", "pro", "startup"
  description: text | null

  // Stripe integration
  stripePriceIdMonthly: text | null
  stripePriceIdYearly: text | null

  // Pricing (in cents)
  priceMonthly: integer (default: 0)
  priceYearly: integer (default: 0)

  // Features & limits
  features: jsonb (string[])
  limits: jsonb ({
    storage?: number
    apiCalls?: number
    teamMembers?: number
    [key: string]: number | undefined
  })

  // Status
  isActive: boolean (default: true)
  sortOrder: integer (default: 0)

  createdAt: timestamp
  updatedAt: timestamp
}

Subscriptions

User subscriptions:

subscriptions {
  id: string (PK, UUID)
  userId: string (FK -> user.id)
  planId: string (FK -> subscriptionPlans.id)

  // Stripe data
  stripeCustomerId: text | null
  stripeSubscriptionId: text (unique) | null
  stripePriceId: text | null
  stripeCurrentPeriodEnd: timestamp | null

  // Status
  status: text (default: "inactive")
  // Values: active, canceled, past_due, trialing, incomplete
  billingCycle: text (default: "monthly")
  // Values: monthly, yearly

  // Cancellation
  canceledAt: timestamp | null
  cancelAtPeriodEnd: boolean (default: false)

  // Trial
  trialEndsAt: timestamp | null

  createdAt: timestamp
  updatedAt: timestamp
}

Indexes:
- userId
- stripeCustomerId
- stripeSubscriptionId
- status

Invoices

Payment records from Stripe:

invoices {
  id: string (PK, UUID)
  userId: string (FK -> user.id)
  subscriptionId: string (FK -> subscriptions.id)

  // Stripe data
  stripeInvoiceId: text (unique)
  stripeCustomerId: text

  // Amounts (in cents)
  amountPaid: integer
  amountDue: integer
  currency: text (default: "usd")

  // Status: paid, open, void, uncollectible
  status: text

  // URLs
  hostedInvoiceUrl: text | null
  invoicePdf: text | null

  // Billing period
  periodStart: timestamp
  periodEnd: timestamp
  paidAt: timestamp | null

  createdAt: timestamp
}

Indexes:
- userId
- stripeInvoiceId
- status

Usage Tracking

For metered billing (optional):

usageTracking {
  id: string (PK, UUID)
  userId: string (FK -> user.id)
  subscriptionId: string (FK -> subscriptions.id)

  // Metrics
  metricName: text  // "api_calls", "storage_gb", etc.
  metricValue: integer (default: 0)

  // Period
  periodStart: timestamp
  periodEnd: timestamp

  createdAt: timestamp
  updatedAt: timestamp
}

Indexes:
- userId
- metricName
- (periodStart, periodEnd)

Security Tables

Security Audit Log

Comprehensive security event logging:

securityAuditLog {
  id: string (PK, UUID)
  userId: string (FK -> user.id) | null
  sessionId: string | null

  // Event details
  eventType: text  // login, logout, auth_failure, etc.
  eventCategory: text  // authentication, authorization, data_access
  severity: text  // low, medium, high, critical
  description: text

  // Request context
  ipAddress: text | null
  userAgent: text | null
  requestPath: text | null
  requestMethod: text | null

  // Additional data
  metadata: jsonb | null
  success: boolean

  createdAt: timestamp
}

Event Types:

user_login, user_logout
failed_login_attempt
password_reset_requested, password_reset_completed
email_verified
role_changed
permission_denied
file_uploaded, file_deleted

Marketing Tables

Contact Submissions

contactSubmission {
  id: string (PK, UUID)
  fullName: text
  email: text
  phone: text | null
  company: text | null
  subject: text
  message: text

  // Admin management
  read: boolean (default: false)
  readAt: timestamp | null

  createdAt: timestamp
}

marketingBanner {
  id: string (PK, UUID)
  description: text
  linkText: text
  linkUrl: text
  color: text
  visible: boolean (default: true)
  updatedAt: timestamp
}

Testimonials

testimonials {
  id: string (PK, UUID)
  userId: string (FK -> user.id) | null
  name: text
  role: text | null
  company: text | null
  content: text
  rating: integer  // 1-5
  image: text | null

  // Admin approval
  status: enum ("pending", "approved", "rejected")
  adminNote: text | null
  approvedBy: string | null
  approvedAt: timestamp | null

  createdAt: timestamp
  updatedAt: timestamp
}

FAQs

faqItems {
  id: string (PK, UUID)
  question: text
  answer: text
  category: text | null
  sortOrder: integer (default: 0)
  published: boolean (default: true)
  createdAt: timestamp
  updatedAt: timestamp
}

Changelog

changelogEntries {
  id: string (PK, UUID)
  version: text  // "1.0.0"
  title: text
  content: text
  type: enum ("feature", "bugfix", "improvement", "breaking")
  published: boolean (default: false)
  publishedAt: timestamp | null
  createdAt: timestamp
  updatedAt: timestamp
}

Waitlist

waitlistEntries {
  id: string (PK, UUID)
  email: text (unique)
  name: text | null
  metadata: jsonb | null

  // Status
  status: enum ("pending", "invited", "joined")
  invitedAt: timestamp | null
  joinedAt: timestamp | null

  createdAt: timestamp
}

waitlistSettings {
  id: string (PK, UUID)
  enabled: boolean (default: false)
  title: text
  description: text
  successMessage: text
  updatedAt: timestamp
}

Email Templates

emailTemplates {
  id: string (PK, UUID)
  type: text (unique)  // "welcome", "password_reset", etc.
  subject: text
  htmlContent: text
  textContent: text | null

  // Variables available in template
  variables: jsonb (string[])

  active: boolean (default: true)
  createdAt: timestamp
  updatedAt: timestamp
}

Feedback

User feedback submissions:

feedback {
  id: string (PK, UUID)
  userId: string (FK -> user.id) | null
  type: text  // "bug", "feature", "general"

  message: text
  url: text | null  // Page URL where feedback was submitted

  // Admin management
  status: text (default: "pending")
  // Values: pending, in_progress, completed, rejected
  adminNote: text | null

  createdAt: timestamp
  updatedAt: timestamp
}

Database Operations

Querying Data

import { db } from "@/db";
import { blogPosts, user } from "@/db/schema";
import { eq, and, desc } from "drizzle-orm";

// Find all published posts
const posts = await db.query.blogPosts.findMany({
  where: eq(blogPosts.published, true),
  orderBy: [desc(blogPosts.publishedAt)],
  with: {
    author: true,
    postCategories: {
      with: { category: true },
    },
    postTags: {
      with: { tag: true },
    },
  },
});

// Find single user by email
const user = await db.query.user.findFirst({
  where: eq(user.email, "user@example.com"),
  with: {
    subscriptions: {
      with: { plan: true },
    },
  },
});

Inserting Data

// Insert blog post
const [post] = await db
  .insert(blogPosts)
  .values({
    slug: "my-post",
    title: "My Post",
    content: "Post content",
    authorId: userId,
    published: false,
  })
  .returning();

// Insert with relations
await db.insert(blogPostCategories).values({
  postId: post.id,
  categoryId: 1,
});

Updating Data

await db
  .update(blogPosts)
  .set({
    title: "Updated Title",
    updatedAt: new Date(),
  })
  .where(eq(blogPosts.id, postId));

Deleting Data

await db.delete(blogPosts).where(eq(blogPosts.id, postId));
// Cascade deletes blogPostCategories, blogPostTags, blogPostLikes

Migrations

Generate Migration

After changing schema:

pnpm drizzle-kit generate

Apply Migration

pnpm drizzle-kit migrate

Push Schema (Development)

Quick schema updates without migrations:

pnpm db:push

Schema Relationships

graph TD
    User -->|has many| Sessions
    User -->|has many| Accounts
    User -->|has one| Subscription
    User -->|has many| BlogPosts

    BlogPosts -->|belongs to| User
    BlogPosts -->|has many| BlogPostCategories
    BlogPosts -->|has many| BlogPostTags
    BlogPosts -->|has many| BlogPostLikes

    BlogPostCategories -->|belongs to| BlogPosts
    BlogPostCategories -->|belongs to| BlogCategories

    BlogPostTags -->|belongs to| BlogPosts
    BlogPostTags -->|belongs to| BlogTags

    Subscription -->|belongs to| User
    Subscription -->|belongs to| SubscriptionPlan
    Subscription -->|has many| Invoices
    Subscription -->|has many| UsageTracking

Database Schema

On this page