Database - CharleOS

CharleOS uses PostgreSQL (hosted on Neon) with Drizzle ORM for type-safe database access. All schema is defined in code, and migrations are generated automatically.

Overview

Technology Stack

Component	Technology	Purpose
Database	PostgreSQL 15	Relational database
Hosting	Neon Serverless	Auto-scaling PostgreSQL
ORM	Drizzle ORM	Type-safe query builder
Migrations	Drizzle Kit	Schema migration tool
Admin UI	Drizzle Studio	Visual database browser

Key Features

Type Safety

Schema defined in TypeScript (lib/db/schema.ts)
Types automatically inferred for queries
Compile-time type checking

Developer Experience

db:push for instant local schema updates
db:studio for visual database browsing
Auto-generated migrations for production

Architecture

Single source of truth (schema.ts)
Automatic type generation
Zero runtime overhead

Database Structure

CharleOS has 47 tables organized into functional groups:

Core Entities

Users, Clients, Tasks, Quotes, Projects

Scheduling

Subtasks, Blocks, Schedule, Capacity

Billing & Time

Time Entries, Plans, Metrics, Efficiency

Auxiliary

Help Desk, Leave, Notifications, Activity Log

See the Schema page for detailed table documentation.

Common Workflows

Local Development

Update Schema

Modify lib/db/schema.ts to add/change tables or columns

Push to Database

npm run db:push

Instantly applies changes to your local dev database

Verify Changes

npm run db:studio

Open Drizzle Studio to browse your updated schema

Production Deployment

Generate Migration

npm run db:generate

Creates SQL migration file in drizzle/ directory

Review Migration

Check the generated SQL in drizzle/XXXX_name.sql to ensure it’s correct

Commit & Deploy

Commit the migration file and schema changes. Migrations run automatically on deployment.

Available Commands

Command	Purpose	When to Use
`npm run db:push`	Push schema changes to database	Local development
`npm run db:generate`	Generate migration file	Before deploying to production
`npm run db:studio`	Open Drizzle Studio	Browse/edit data visually
`npm run db:seed`	Seed development data	Fresh local setup
`npm run db:sync`	Sync migrations to Neon	After adding migration files

Local vs Production:

Use db:push for local dev (fast, no migration files)
Use db:generate for production (creates migration files for deployment)

Querying the Database

CharleOS uses Drizzle ORM for all database queries. Here’s a quick example:

Basic Query

import { db } from "@/lib/db";
import { task } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

// Select all tasks
const tasks = await db.select().from(task);

// Select with filter
const clientTasks = await db
  .select()
  .from(task)
  .where(eq(task.clientId, "client-123"));

// Insert
const newTask = await db
  .insert(task)
  .values({
    displayId: 123,
    title: "New task",
    clientId: "client-123",
  })
  .returning();

Joins and Relations

import { db } from "@/lib/db";
import { task, client } from "@/lib/db/schema";

// Join tasks with clients
const tasksWithClients = await db
  .select({
    task,
    clientName: client.name,
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id));

Drizzle provides full TypeScript types for all queries. If you try to query a column that doesn’t exist, you’ll get a compile error.

Database Configuration

Connection

The database connection is configured via the DATABASE_URL environment variable:

DATABASE_URL=postgresql://user:password@host:5432/database?sslmode=require

Local development:

Uses DEV branch on Neon (safe to experiment)
Configured in .env.local

Production:

Uses main branch on Neon
Configured via Vercel environment variables

Connection Pooling

Drizzle uses @neondatabase/serverless with connection pooling:

// lib/db/index.ts
import { drizzle } from "drizzle-orm/neon-serverless";
import { Pool } from "@neondatabase/serverless";

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

Benefits:

Efficient connection reuse
Auto-scaling with Neon
Fast query execution

Schema Organization

The schema is organized into logical sections in lib/db/schema.ts:

// ============================================
// AUTHENTICATION (Better Auth)
// ============================================
export const user = pgTable("user", { ... });
export const session = pgTable("session", { ... });
export const account = pgTable("account", { ... });

// ============================================
// CLIENT MANAGEMENT
// ============================================
export const client = pgTable("client", { ... });
export const clientUser = pgTable("client_user", { ... });

// ============================================
// TASK MANAGEMENT
// ============================================
export const task = pgTable("task", { ... });
export const subtask = pgTable("subtask", { ... });

// ... more sections ...

This organization makes it easy to find and understand related tables.

Type Safety

One of the biggest advantages of Drizzle is automatic type inference:

Auto-Generated Types

import { task } from "@/lib/db/schema";

// Types are automatically inferred from schema
type Task = typeof task.$inferSelect;  // Select type (what you get)
type NewTask = typeof task.$inferInsert; // Insert type (what you send)

// Example usage
function processTask(task: Task) {
  console.log(task.title);     // ✅ TypeScript knows this exists
  console.log(task.invalid);   // ❌ Compile error - doesn't exist
}

Query Type Safety

// TypeScript knows the exact shape of the result
const result = await db
  .select({
    taskTitle: task.title,
    clientName: client.name,
  })
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id));

// result has type: Array<{ taskTitle: string; clientName: string | null }>

Database Tools

Drizzle Studio

Visual database browser built into the development workflow:

npm run db:studio

Features:

Browse all tables
Filter and search data
Edit records directly
View relationships
Run custom queries

Access: Opens at https://local.drizzle.studio (secure local HTTPS)

Neon Console

The Neon dashboard provides:

Database branching (dev/staging/prod)
Query history and analytics
Connection pooling stats
Backups and point-in-time restore

Access: console.neon.tech

Best Practices

Always Use the ORM

Never write raw SQL queries. Use Drizzle’s query builder:

// ✅ Good: Type-safe query
const tasks = await db.select().from(task).where(eq(task.status, "active"));

// ❌ Bad: Raw SQL loses type safety
const tasks = await db.execute("SELECT * FROM task WHERE status = 'active'");

Use Transactions for Multi-Step Operations

When multiple database operations must succeed or fail together:

await db.transaction(async (tx) => {
  // Create task
  const [task] = await tx.insert(task).values(...).returning();
  
  // Create subtasks
  await tx.insert(subtask).values([...]);
  
  // If any operation fails, everything rolls back
});

Test Schema Changes Locally First

Always test schema changes with db:push locally before generating migrations:

Update schema in lib/db/schema.ts
Run npm run db:push to test locally
Fix any issues
Run npm run db:generate to create migration
Commit and deploy

Keep Services Separate from Routes

Database queries should live in lib/services/, not in API routes:

// ✅ Good: lib/services/tasks.ts
export async function getTaskById(id: string) {
  return await db.select().from(task).where(eq(task.id, id));
}

// ❌ Bad: Directly in app/api/tasks/[id]/route.ts
export async function GET(req: Request) {
  const data = await db.select().from(task)...
}

Common Patterns

Pagination

import { desc } from "drizzle-orm";

const PAGE_SIZE = 20;
const page = 1;

const tasks = await db
  .select()
  .from(task)
  .orderBy(desc(task.createdAt))
  .limit(PAGE_SIZE)
  .offset((page - 1) * PAGE_SIZE);

Filtering

import { and, eq, like, gte } from "drizzle-orm";

const tasks = await db
  .select()
  .from(task)
  .where(
    and(
      eq(task.clientId, clientId),
      like(task.title, `%${searchTerm}%`),
      gte(task.createdAt, startDate)
    )
  );

Aggregation

import { count, sum } from "drizzle-orm";

const stats = await db
  .select({
    totalTasks: count(task.id),
    totalMinutes: sum(subtask.estimatedMinutes),
  })
  .from(task)
  .leftJoin(subtask, eq(task.id, subtask.taskId))
  .where(eq(task.clientId, clientId));

Troubleshooting

Connection Issues

Error: “Connection refused” or “SSL required” Fix:

Check DATABASE_URL in .env.local
Ensure Neon database is active (not auto-paused)
Verify SSL mode is enabled: ?sslmode=require

Migration Conflicts

Error: “Migration X not found” or “Migration conflict” Fix:

# Reset local database to match migrations
npm run db:push

# Or regenerate migrations
rm drizzle/XXXX_problematic.sql
npm run db:generate

Type Errors

Error: “Property ‘xyz’ does not exist on type…” Fix:

Restart TypeScript server in your IDE
Schema types are auto-generated - if you just changed the schema, wait a moment for TS to catch up
Run npm run type-check to see all type errors

Schema

Complete database schema reference

Drizzle ORM

Query builder syntax and patterns

Migrations

Managing schema changes over time

Project Structure

Where database code lives

​Overview

​Technology Stack

​Key Features

​Database Structure

Core Entities

Scheduling

Billing & Time

Auxiliary

​Common Workflows

​Local Development

​Production Deployment

​Available Commands

​Querying the Database

​Basic Query

​Joins and Relations

​Database Configuration

​Connection

​Connection Pooling

​Schema Organization

​Type Safety

​Auto-Generated Types

​Query Type Safety

​Database Tools

​Drizzle Studio

​Neon Console

​Best Practices

​Common Patterns

​Pagination

​Filtering

​Aggregation

​Troubleshooting

​Connection Issues

​Migration Conflicts

​Type Errors

​Related Documentation

Schema

Drizzle ORM

Migrations

Project Structure

Overview

Technology Stack

Key Features

Database Structure

Common Workflows

Local Development

Production Deployment

Available Commands

Querying the Database

Basic Query

Joins and Relations

Database Configuration

Connection

Connection Pooling

Schema Organization

Type Safety

Auto-Generated Types

Query Type Safety

Database Tools

Drizzle Studio

Neon Console

Best Practices

Common Patterns

Pagination

Filtering

Aggregation

Troubleshooting

Connection Issues

Migration Conflicts

Type Errors

Related Documentation