Database

DO NOT upgrade any Supabase or Drizzle dependencies unless you’re following their official migration guide and understand the breaking changes. Upgrading without proper migration can break your database connections, schema, and RLS policies. The current versions in the boilerplate are tested and stable together.

This guide covers working with Supabase PostgreSQL and Drizzle ORM in Titan projects.

Core Concepts

PostgreSQL: Supabase’s underlying database engine
Drizzle ORM: Type-safe database client for TypeScript
Row Level Security (RLS): PostgreSQL’s feature for row access control
Migrations: Version-controlled database schema changes

Development vs Production

Environment	Characteristics	Considerations
Dev	Safe for experimentation, can be reset	Not for real user data
Production	Contains real user data	Test all changes in dev first

Initial Setup

After you’ve spun up a fresh Titan project, you would’ve had a thorough conversation with agent mode in Cursor to come up with all the relevant documentation and all the tables you would need for your MVP.

You can now use this guide by leveraging the database schema that you’ve created to start creating your initial tables in your database, and then subsequently making changes to the database schema as you see fit over time.

Run the Database Initialization Script

This will create a new database in Supabase and apply the initial schema to it - i.e. create all the tables defined in db/schema/index.ts.
You can optionally seed the database with test data (It will ask you if you want to do this).

 bun run db:init

Verify the Setup

This will open the database studio where you can explore your database to check that the tables have been created correctly.

bun run db:studio

Data Access Patterns

Titan uses three primary patterns for database access. Each serves different needs:

Pattern	Use Cases	Benefits	When to Use
Direct Drizzle in Server Actions	Server components, form submissions	Type safety, best performance	When you need the fastest queries and don’t need loading states
React Query + Server Actions	Client UI with loading states	Caching, loading states	When users need to see loading spinners and you want to cache data
Supabase Client	Client-side auth, storage	RLS security, simple API	When you need real-time updates or client-side authentication

Why These Patterns Exist

The Problem: Different parts of your app have different needs:

Server pages need fast, direct database access
Interactive UIs need loading states and caching
Client-side features need security and real-time updates

The Solution: Use the right pattern for each situation instead of forcing one approach everywhere.

The good news is that Titan already comes with a titan-best-practices.mdc cursorrules file that ensure that the LLM you choose will follow these best practices based on the context.

1. Direct Drizzle (Server-Side)

Why use this?

Fastest possible database queries (no extra network hops)
Full TypeScript safety (catches errors at compile time)
Perfect for server pages that render once

When to use:

Loading data for server components (pages that don’t need interactivity)
Form submissions and mutations
API routes
Any time you don’t need loading states or caching

// utils/actions/users.ts
'use server'
import { createDirectClient } from '@/lib/drizzle'
import { users } from '@/db/schema'
import { eq } from 'drizzle-orm'

export async function getUser(id: string) {
  const db = createDirectClient()
  const user = await db.select().from(users).where(eq(users.id, id))
  return user[0] || null
}

2. React Query with Server Actions

Why use this?

Shows loading spinners while data loads (better UX)
Caches data so repeated requests are instant
Automatically refetches stale data
Handles error states gracefully

When to use:

Interactive components that need loading states
Data that gets requested multiple times (user profiles, lists)
When you want automatic background refetching
Client components that need to show “Loading…” or error messages

// 1. Server action
'use server'
import { createDirectClient } from '@/lib/drizzle'
import { users } from '@/db/schema'

export async function getUsers() {
  const db = createDirectClient()
  return db.select().from(users)
}

// 2. React Query hook
'use client'
import { useQuery } from '@tanstack/react-query'
import { getUsers } from '@/utils/actions/users'

export function useUsers() {
  return useQuery({
    queryKey: ['users'],
    queryFn: getUsers,
    staleTime: 60 * 1000 // Cache for 1 minute
  })
}

// 3. Use in component
function UsersList() {
  const { data: users, isLoading, error } = useUsers()
  
  if (isLoading) return <div>Loading users...</div>
  if (error) return <div>Error loading users</div>
  
  return (
    <div>
      {users?.map(user => <div key={user.id}>{user.name}</div>)}
    </div>
  )
}

3. Supabase Client (Client-Side)

Why use this?

Automatically enforces Row Level Security (RLS) policies
Enables real-time subscriptions (data updates live)
Handles authentication seamlessly
Works directly from the browser

When to use:

Real-time features (chat, live updates, collaborative editing)
Client-side authentication flows
When you need RLS to automatically filter data by user
File uploads to Supabase Storage

Don’t use for:

Simple data fetching (React Query + Server Actions is better)
Server-side operations (Direct Drizzle is faster)

'use client'
import { createClient } from '@/lib/supabase'
import { useState, useEffect } from 'react'

function UserProfile({ userId }) {
  const [user, setUser] = useState(null)
  
  useEffect(() => {
    async function fetchUser() {
      const supabase = createClient()
      const { data } = await supabase
        .from('users')
        .select('*')
        .eq('id', userId)
        .single()
      setUser(data)
    }
    fetchUser()
  }, [userId])
  
  // Real-time example
  useEffect(() => {
    const supabase = createClient()
    const channel = supabase
      .channel('user-changes')
      .on('postgres_changes', 
        { event: 'UPDATE', schema: 'public', table: 'users' },
        (payload) => setUser(payload.new)
      )
      .subscribe()
    
    return () => supabase.removeChannel(channel)
  }, [])
}

Decision Tree: Which Pattern to Use?

Ask yourself these questions in order:

Is this running on the server? (Server Components, API routes, form actions) → Use Direct Drizzle - Fastest and most direct
Do users need to see loading states? (Interactive UI, dashboards) → Use React Query + Server Actions - Best user experience
Do you need real-time updates? (Chat, live collaboration, notifications) → Use Supabase Client - Only option for real-time
Do you need client-side auth? (Login forms, protected routes) → Use Supabase Client - Handles auth automatically

Common Beginner Mistakes

❌ Don’t do this:

Using Supabase Client for everything (slower, more complex)
Using Direct Drizzle in client components (won’t work)
Using React Query for server components (unnecessary overhead)

✅ Do this instead:

Match the pattern to your specific need
Start with Direct Drizzle for server-side data
Add React Query when you need loading states
Only use Supabase Client for real-time or auth features

Schema Design and Queries

Schema Definition

Define your database schema with TypeScript:

// db/schema/users.ts
import { pgTable, serial, text, timestamp, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
});

// Export all tables from index
// db/schema/index.ts
export * from './users';
export * from './products';

Best Practices

Organize tables by domain in separate files
Use UUID or serial for IDs
Add created/updated timestamps to all tables
Create indexes for frequently queried columns
Define relationships with references
Use enums for fixed values
Add appropriate constraints

Common Query Patterns

// Select
const users = await db.select().from(userTable);

// With conditions
const activeUsers = await db.select()
  .from(userTable)
  .where(eq(userTable.status, 'active'));

// Insert
const newUser = await db.insert(userTable)
  .values({ name: 'John', email: 'john@example.com' })
  .returning();

// Update
const updated = await db.update(userTable)
  .set({ status: 'inactive' })
  .where(eq(userTable.id, userId))
  .returning();

// Delete
const deleted = await db.delete(userTable)
  .where(eq(userTable.id, userId));

Row Level Security (RLS)

RLS restricts which rows a user can access based on their identity.

Common RLS Patterns

-- Users can only access their own data
CREATE POLICY "Users can access own data"
ON users FOR ALL
USING (auth.uid()::text = user_id);

-- Public read, authenticated write
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Auth users can create"
ON posts FOR INSERT
WITH CHECK (auth.uid()::text = author_id);

Authorization in Server Actions

Always add authorization checks in server actions:

'use server'
import { createDirectClient } from '@/lib/drizzle'
import { isAuthorized } from '@/utils/data/user/isAuthorized'

export async function updateUser(userId, data) {
  // Check authorization
  const { authorized, message } = await isAuthorized(userId)
  if (!authorized) throw new Error(message)
  
  // Proceed with database operation
  const db = createDirectClient()
  return db.update(users)
    .set(data)
    .where(eq(users.id, userId))
    .returning()
}

Migrations

Titan uses Drizzle for database migrations:

Development Workflow

The typical development workflow for database schema changes:

Create or modify schema in db/schema/ files and update RLS policies in db/rls.sql

// Example: Add a new column to an existing table
export const users = pgTable('users', {
  // Existing columns
  id: serial('id').primaryKey(),
  email: varchar('email').notNull().unique(),
  // New column being added
  phoneNumber: varchar('phone_number', { length: 15 }),
});

Generate migrations based on schema changes
```
bun run db:generate
```
Apply RLS policies
```
bun run db:rls
```
Push migrations to dev database
```
bun run db:push
```
Test your changes thoroughly with the updated schema

If you’re not satisfied with the schema, you have two options:

Option A: Reset and start over (for significant changes)

# Drop all tables and reset the schema
bun run db:drop

# Edit your schema files, then regenerate and push
bun run db:generate
bun run db:push

# Reapply RLS policies
bun run db:rls

# Optionally reseed with test data
bun run db:seed

Option B: Create additional migrations (for incremental changes)

# Edit your schema files with the new changes

# Generate new migration
bun run db:generate

# Push the new migration
bun run db:push

Repeat steps 1-5 until satisfied with your schema
When ready for production, follow the safe deployment process

Safe Dev-to-Production Workflow

Always follow this workflow to safely apply database changes:

ALWAYS backup first

# Using Supabase CLI (if available)
supabase db dump --project-ref YOUR_PROD_REF -f backup.sql

Apply to production

# Update your .env file to use the production database credentials first
bun run db:push
bun run db:rls

# OR use a production config
bun run db:push --config=drizzle-prod.config.ts
bun run db:rls

Quick Reference

Command Cheatsheet

# Drizzle Commands
bun run db:generate         # Generate migrations from schema changes
bun run db:push             # Apply migrations to Supabase (dev or prod based on config)
bun run db:studio           # Open database studio to explore/edit data
bun run db:rls              # Apply RLS policies
bun run db:init             # Initialize database (first-time setup)
bun run db:seed             # Seed database with test data
bun run db:drop             # Drop database tables (careful!)

Best Practices

For Database Access
- Use Direct Drizzle in Server Actions for performance and type safety
- Use React Query for client-side state management
- Always add authorization checks in server actions
For Schema Design
- Organize tables by domain
- Use proper indexes and constraints
- Add timestamps to all tables
For Security
- Define comprehensive RLS policies
- Use authorization checks in server actions
- Follow principle of least privilege
For Performance
- Use React Query’s caching
- Create proper database indexes
- Use transactions for related operations

Common Scenarios

Adding a New Table with RLS

Create or edit schema file in db/schema/
Export the table from the main schema file
Generate and apply migrations:
```
bun run db:generate
bun run db:push
```
Add RLS policies to db/rls.sql
Apply RLS policies:
```
bun run db:rls
```

Modifying an Existing Table (adding a new column etc.)

Edit the schema file
Generate and apply migrations:
```
bun run db:generate
bun run db:push
```

Iterative Schema Development

When to reset vs. when to create additional migrations:

Reset approach (Option A) is best when:

You’re in early development with no valuable data in the dev database
You’ve made complex structural changes that are difficult to migrate
You want a clean slate to avoid migration conflicts
You’re experimenting with significantly different schema designs

Incremental migration approach (Option B) is best when:

You have test data you want to preserve
Changes are small and straightforward
You’re closer to production and want to test actual migration paths
You want to maintain migration history for documentation

Remember that in production, you’ll almost always use incremental migrations unless you’re doing a complete application redesign.

Troubleshooting Database Issues

Reset database setup: bun run db:init
Check database state: bun run db:studio
For deeper issues, you may need to:
1. Drop all tables: bun run db:drop
2. Regenerate migrations: bun run db:generate
3. Apply migrations: bun run db:push
4. Apply RLS: bun run db:rls
5. Seed data: bun run db:seed

Setup & Configuration

Development & Deployment

Optimisations

Core Concepts

Development vs Production

Initial Setup

Data Access Patterns

Why These Patterns Exist

1. Direct Drizzle (Server-Side)

2. React Query with Server Actions

3. Supabase Client (Client-Side)

Decision Tree: Which Pattern to Use?

Common Beginner Mistakes

Schema Design and Queries

Schema Definition

Best Practices

Common Query Patterns

Row Level Security (RLS)

Common RLS Patterns

Authorization in Server Actions

Migrations

Development Workflow

Safe Dev-to-Production Workflow

Quick Reference

Command Cheatsheet

Best Practices

Common Scenarios

Adding a New Table with RLS

Modifying an Existing Table (adding a new column etc.)

Iterative Schema Development

Troubleshooting Database Issues

Setup & Configuration

Development & Deployment

Optimisations

​Core Concepts

​Development vs Production

​Initial Setup

​Data Access Patterns

​Why These Patterns Exist

​1. Direct Drizzle (Server-Side)

​2. React Query with Server Actions

​3. Supabase Client (Client-Side)

​Decision Tree: Which Pattern to Use?

​Common Beginner Mistakes

​Schema Design and Queries

​Schema Definition

​Best Practices

​Common Query Patterns

​Row Level Security (RLS)

​Common RLS Patterns

​Authorization in Server Actions

​Migrations

​Development Workflow

​Safe Dev-to-Production Workflow

​Quick Reference

​Command Cheatsheet

​Best Practices

​Common Scenarios

​Adding a New Table with RLS

​Modifying an Existing Table (adding a new column etc.)

​Iterative Schema Development

​Troubleshooting Database Issues

Core Concepts

Development vs Production

Initial Setup

Data Access Patterns

Why These Patterns Exist

1. Direct Drizzle (Server-Side)

2. React Query with Server Actions

3. Supabase Client (Client-Side)

Decision Tree: Which Pattern to Use?

Common Beginner Mistakes

Schema Design and Queries

Schema Definition

Best Practices

Common Query Patterns

Row Level Security (RLS)

Common RLS Patterns

Authorization in Server Actions

Migrations

Development Workflow

Safe Dev-to-Production Workflow

Quick Reference

Command Cheatsheet

Best Practices

Common Scenarios

Adding a New Table with RLS

Modifying an Existing Table (adding a new column etc.)

Iterative Schema Development

Troubleshooting Database Issues