> ## Documentation Index
> Fetch the complete documentation index at: https://docs.charle.agency/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Working with PostgreSQL and Drizzle ORM in 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:

<CardGroup cols={2}>
  <Card title="Core Entities" icon="database">
    Users, Clients, Tasks, Quotes, Projects
  </Card>

  <Card title="Scheduling" icon="calendar">
    Subtasks, Blocks, Schedule, Capacity
  </Card>

  <Card title="Billing & Time" icon="clock">
    Time Entries, Plans, Metrics, Efficiency
  </Card>

  <Card title="Auxiliary" icon="gear">
    Help Desk, Leave, Notifications, Activity Log
  </Card>
</CardGroup>

See the [Schema page](/developer/database/schema) for detailed table documentation.

## Common Workflows

### Local Development

<Steps>
  <Step title="Update Schema">
    Modify `lib/db/schema.ts` to add/change tables or columns
  </Step>

  <Step title="Push to Database">
    ```bash theme={null}
    npm run db:push
    ```

    Instantly applies changes to your local dev database
  </Step>

  <Step title="Verify Changes">
    ```bash theme={null}
    npm run db:studio
    ```

    Open Drizzle Studio to browse your updated schema
  </Step>
</Steps>

### Production Deployment

<Steps>
  <Step title="Generate Migration">
    ```bash theme={null}
    npm run db:generate
    ```

    Creates SQL migration file in `drizzle/` directory
  </Step>

  <Step title="Review Migration">
    Check the generated SQL in `drizzle/XXXX_name.sql` to ensure it's correct
  </Step>

  <Step title="Commit & Deploy">
    Commit the migration file and schema changes. Migrations run automatically on deployment.
  </Step>
</Steps>

## 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   |

<Warning>
  **Local vs Production:**

  * Use `db:push` for local dev (fast, no migration files)
  * Use `db:generate` for production (creates migration files for deployment)
</Warning>

## Querying the Database

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

### Basic Query

```typescript theme={null}
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

```typescript theme={null}
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));
```

<Info>
  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.
</Info>

## 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:

```typescript theme={null}
// 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`:

```typescript theme={null}
// ============================================
// 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

```typescript theme={null}
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 theme={null}
// 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:

```bash theme={null}
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](https://console.neon.tech/)

## Best Practices

<AccordionGroup>
  <Accordion title="Always Use the ORM" icon="code">
    Never write raw SQL queries. Use Drizzle's query builder:

    ```typescript theme={null}
    // ✅ 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'");
    ```
  </Accordion>

  <Accordion title="Use Transactions for Multi-Step Operations" icon="lock">
    When multiple database operations must succeed or fail together:

    ```typescript theme={null}
    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
    });
    ```
  </Accordion>

  <Accordion title="Test Schema Changes Locally First" icon="flask">
    Always test schema changes with `db:push` locally before generating migrations:

    1. Update schema in `lib/db/schema.ts`
    2. Run `npm run db:push` to test locally
    3. Fix any issues
    4. Run `npm run db:generate` to create migration
    5. Commit and deploy
  </Accordion>

  <Accordion title="Keep Services Separate from Routes" icon="folder">
    Database queries should live in `lib/services/`, not in API routes:

    ```typescript theme={null}
    // ✅ 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)...
    }
    ```
  </Accordion>
</AccordionGroup>

## Common Patterns

### Pagination

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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:**

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

### Migration Conflicts

**Error:** "Migration X not found" or "Migration conflict"

**Fix:**

```bash theme={null}
# 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

## Related Documentation

<CardGroup cols={2}>
  <Card title="Schema" icon="table" href="/developer/database/schema">
    Complete database schema reference
  </Card>

  <Card title="Drizzle ORM" icon="code" href="/developer/database/drizzle">
    Query builder syntax and patterns
  </Card>

  <Card title="Migrations" icon="arrow-right-arrow-left" href="/developer/database/migrations">
    Managing schema changes over time
  </Card>

  <Card title="Project Structure" icon="folder-tree" href="/developer/getting-started/project-structure">
    Where database code lives
  </Card>
</CardGroup>