SaaS Starter
Docs
Database Flow

Database Flow

Data persistence and management with PostgreSQL using Kysely and raw SQL

Overview

Manages data persistence using PostgreSQL with Kysely and raw SQL. Handles user authentication, session management, and subscription data with proper relationships and constraints.

Main libraries/services:

  • kysely - Type-safe SQL query builder
  • Custom raw SQL repositories - Fine-grained control over complex queries
  • PostgreSQL - Primary database (Neon hosting)
  • SQL migrations (/migrations) for schema management

File Map

šŸ“ migrations/             - SQL migration files
šŸ“ lib/db.ts               - Kysely client configuration
šŸ“ lib/db.types.ts         - Database type definitions for Kysely
šŸ“ repositories/           - Data access layer (Kysely + raw SQL)
šŸ“ services/               - Business logic built on repositories
šŸ“ actions/                - Server actions orchestrating database operations

Database Schema

Schema changes are managed through SQL migrations under /migrations. Key tables:

  • users ā€“ Stores profile data, OAuth linkage, and Stripe identifiers
  • accounts ā€“ OAuth provider accounts linked to users
  • sessions ā€“ NextAuth session tokens with expiry metadata
  • projects / project_members / project_invitations ā€“ Core project collaboration tables
  • subscription_plans / subscriptions ā€“ Billing and plan data

Refer to the latest migration files for the exact SQL definitions.

Step-by-Step Flow

User Creation (OAuth)

  1. User authenticates via OAuth provider
  2. NextAuth KyselyAdapter handles user creation through raw SQL repositories
  3. User record created with OAuth account linked
  4. Stripe customer ID generated (if needed)
  5. User role set to default "USER"

User Lookup Operations

  1. getUserById(id) - Fetches user by ID
  2. getUserByEmail(email) - Fetches user by email
  3. getCurrentUser() - Gets current session user
  4. All operations include proper error handling

Subscription Data Management

  1. getUserSubscriptionPlan(userId) - Fetches subscription data
  2. Stripe webhooks update subscription fields
  3. Plan validation against current period end
  4. Pricing data matching against Stripe price IDs

Database Connection Management

  1. Kysely client configured with connection pooling
  2. Development: Cached global instance
  3. Production: New instance per request
  4. Automatic connection management

Data Flow Diagram

[NextAuth] ā†’ [KyselyAdapter] ā†’ [User Creation/Update] ā†’ [Database]
     ā†“
[Webhooks] ā†’ [Subscription Updates] ā†’ [Database]
     ā†“
[Queries] ā†’ [Kysely Client + raw SQL] ā†’ [Database] ā†’ [Application Logic]

Database Operations

User CRUD

  • Create: Via NextAuth OAuth flow
  • Read: getUserById, getUserByEmail, getCurrentUser
  • Update: Via webhooks and user actions
  • Delete: Cascade delete with accounts/sessions

Subscription CRUD

  • Create: Via Stripe webhooks
  • Read: getUserSubscriptionPlan
  • Update: Via Stripe webhooks (renewals, changes)
  • Delete: Via Stripe cancellation webhooks

Environment Configuration

  • DATABASE_URL: PostgreSQL connection string
  • Neon: Hosted PostgreSQL service
  • Migrations: Managed via SQL files executed with preferred migration runner

Notes and TODOs

  • āœ… User authentication with OAuth integration
  • āœ… Session management with JWT strategy
  • āœ… Stripe subscription data persistence
  • āœ… Proper foreign key relationships
  • āœ… Cascade delete for data integrity
  • āš ļø No soft delete implementation
  • šŸ”„ Add user profile management
  • šŸ”„ Implement audit logging
  • šŸ”„ Add database backup strategies
  • šŸ”„ Consider adding user preferences table
  • šŸ”„ Add indexes for performance optimization
Email HandlingDashboard
Database Flow ā€“ SaaS Starter