📊 Application Layer
    ↓ (TypeScript interfaces)
🔧 Drizzle ORM
    ↓ (SQL queries)
🐘 PostgreSQL Database
    ↓ (Data storage)
☁️  Cloud Provider (Neon, Supabase, Railway)

packages/database/src/
├── schema/
│   ├── index.ts           # Main schema exports
│   ├── users.ts          # User-related tables
│   ├── auth.ts           # Authentication tables
│   ├── subscriptions.ts  # Payment/subscription tables
│   └── audit.ts          # Audit logging tables
├── lib/
│   ├── db.ts            # Database client
│   ├── migrate.ts       # Migration utilities
│   └── seed.ts          # Seed data functions
├── queries/
│   ├── users.ts         # User queries
│   ├── auth.ts          # Auth queries
│   └── subscriptions.ts # Subscription queries
└── migrations/          # Database migrations
    ├── 0001_initial.sql
    └── meta/            # Migration metadata

import { db } from '@packages/database';
import { users } from '@packages/database/schema';
import { eq } from 'drizzle-orm';

// Type-safe queries
const user = await db.select().from(users).where(eq(users.id, userId));
// user is typed as User automatically

// packages/database/src/lib/db.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const connectionString = process.env.DATABASE_URL!;
const client = postgres(connectionString);

export const db = drizzle(client, { schema });

users            -- User profiles
accounts         -- OAuth accounts (GitHub, Google)
sessions         -- User sessions
verification_tokens -- Email verification
password_reset_tokens -- Password reset

subscriptions    -- User subscriptions
payments         -- Payment records
audit_logs       -- System audit trail
user_preferences -- User settings

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  avatar: text('avatar_url'),
  role: text('role', { enum: ['user', 'admin'] }).default('user'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Types are automatically inferred
type User = typeof users.$inferSelect;
type CreateUser = typeof users.$inferInsert;

// Usage
const createUser = async (userData: CreateUser): Promise<User> => {
  const [user] = await db.insert(users).values(userData).returning();
  return user;
};

# Generate migrations after schema changes
pnpm db:generate

# Apply migrations
pnpm db:push  # Development
pnpm db:migrate  # Production

// Automatic prepared statement caching
const getUserById = async (userId: string) => {
  return db.select().from(users).where(eq(users.id, userId));
};
// Drizzle automatically caches this query as a prepared statement

export const usersRelations = relations(users, ({ many, one }) => ({
  accounts: many(accounts),
  sessions: many(sessions),
  subscription: one(subscriptions, {
    fields: [users.id],
    references: [subscriptions.userId],
  }),
}));

// Usage with relations
const userWithSubscription = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    subscription: true,
  },
});

# Add to .env.local
DATABASE_URL="postgresql://username:password@host:port/database?sslmode=require"

# Push schema to database
pnpm db:push

# Open visual database browser
pnpm db:studio

import { db } from '@packages/database';
import { users } from '@packages/database/schema';

// Create user
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning();

// Read user
const user = await db.select().from(users).where(eq(users.id, userId));

// Update user
await db.update(users)
  .set({ name: 'Jane Doe', updatedAt: new Date() })
  .where(eq(users.id, userId));

// Delete user
await db.delete(users).where(eq(users.id, userId));

# Neon
DATABASE_URL="postgresql://username:password@ep-xyz.us-east-1.aws.neon.tech/neondb?sslmode=require"

# Supabase
DATABASE_URL="postgresql://postgres:password@db.xyz.supabase.co:5432/postgres"

# Railway
DATABASE_URL="postgresql://postgres:password@roundhouse.proxy.rlwy.net:12345/railway"

# 1. Start development
pnpm dev

# 2. Make schema changes
# Edit: packages/database/src/schema/

# 3. Push changes to database
pnpm db:push

# 4. Visual database management
pnpm db:studio

# 1. Modify schema files
# packages/database/src/schema/users.ts

# 2. Generate migration (production)
pnpm db:generate

# 3. Apply changes
pnpm db:push      # Development (direct schema sync)
pnpm db:migrate   # Production (apply migrations)

# Add demo data for development
pnpm seed:demo

# Clear all data
pnpm seed:clear

# Reset and reseed
pnpm seed:reset

// ✅ Good - Descriptive names, proper constraints
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}));

// ❌ Avoid - Generic names, missing constraints
export const users = pgTable('users', {
  id: text('id'),  // No primary key constraint
  data: text('data'), // Generic column name
  created: timestamp('created'), // No default value
});

// ✅ Good - Dedicated query functions
export const getUserById = async (userId: string) => {
  return db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      subscription: true,
    },
  });
};

export const getUsersByRole = async (role: 'user' | 'admin') => {
  return db.select().from(users).where(eq(users.role, role));
};

// ❌ Avoid - Inline queries in components
const user = await db.select().from(users).where(eq(users.id, userId));

import { DatabaseError } from '@packages/database/errors';

export const createUser = async (userData: CreateUser) => {
  try {
    const [user] = await db.insert(users).values(userData).returning();
    return { success: true, data: user };
  } catch (error) {
    if (error.code === '23505') { // Unique constraint violation
      return { success: false, error: 'Email already exists' };
    }
    throw new DatabaseError('Failed to create user', error);
  }
};

// Add indexes for frequently queried columns
export const users = pgTable('users', {
  // ... columns
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  roleIdx: index('users_role_idx').on(table.role),
  createdAtIdx: index('users_created_at_idx').on(table.createdAt),
}));

// ✅ Good - Select only needed columns
const userEmails = await db
  .select({ email: users.email, name: users.name })
  .from(users);

// ❌ Avoid - Select all columns when not needed
const userEmails = await db.select().from(users);

import { z } from 'zod';

const createUserSchema = z.object({
  email: z.string().email(),
  name: z.string().min(2).max(100),
  role: z.enum(['user', 'admin']).default('user'),
});

export const createUser = async (input: unknown) => {
  const validated = createUserSchema.parse(input);
  // Safe to use validated data
  return db.insert(users).values(validated).returning();
};

-- Enable RLS on sensitive tables (if needed)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy examples (applied via migrations)
CREATE POLICY users_select_policy ON users 
  FOR SELECT USING (auth.uid() = id);