Database Setup - Hazel Chat

Hazel Chat uses PostgreSQL with Drizzle ORM for type-safe database operations. This guide covers database setup, schema management, and migrations.

PostgreSQL Requirements

Version

Hazel Chat requires PostgreSQL 14 or later with logical replication enabled.

Electric SQL Configuration

Electric SQL requires specific PostgreSQL settings for real-time replication:

-- Required for Electric SQL
wal_level = logical
max_wal_senders = 10
max_replication_slots = 5
hot_standby = on
hot_standby_feedback = on

-- Recommended for performance
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB

Electric SQL will not work without wal_level=logical. This must be set before initializing the database.

Quick Start

Using Docker (Recommended)

The easiest way to set up PostgreSQL is using Docker Compose:

Start PostgreSQL

docker compose up -d postgres

This starts PostgreSQL 17 with all required configuration.

Wait for health check

docker compose ps postgres

Wait until the status shows healthy.

Initialize database

cd packages/db
bun run db:push

This creates all tables and indexes.

Manual PostgreSQL Setup

If you prefer to run PostgreSQL outside Docker:

Install PostgreSQL

# macOS
brew install postgresql@17

# Ubuntu/Debian
sudo apt install postgresql-17

Configure postgresql.conf

Edit /etc/postgresql/17/main/postgresql.conf:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 5
hot_standby = on
hot_standby_feedback = on
max_connections = 200

Restart PostgreSQL

sudo systemctl restart postgresql

Create database

createdb -U postgres app

Set DATABASE_URL

export DATABASE_URL="postgresql://user:password@localhost:5432/app"

Drizzle ORM

Overview

Hazel Chat uses Drizzle ORM for:

Type-safe queries - Full TypeScript inference
Schema-first design - Define schema in TypeScript
Migration management - Automatic migration generation
Effect-TS integration - Functional error handling

Project Structure

packages/db/
├── src/
│   ├── schema/           # Table definitions
│   │   ├── users.ts
│   │   ├── organizations.ts
│   │   ├── channels.ts
│   │   ├── messages.ts
│   │   └── index.ts      # Schema exports
│   ├── services/         # Database services
│   │   ├── database.ts   # Database layer
│   │   └── model-repository.ts
│   └── index.ts
├── drizzle/              # Generated migrations
├── drizzle.config.ts     # Drizzle configuration
└── package.json

Configuration

Drizzle is configured in drizzle.config.ts:

packages/db/drizzle.config.ts

import { defineConfig } from "drizzle-kit"

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema/index.ts",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  verbose: true,
  strict: true,
  migrations: {
    table: "drizzle_migrations",
    schema: "public"
  }
})

Schema Definition

Example Schema

Schemas are defined using Drizzle’s TypeScript API:

packages/db/src/schema/users.ts

import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core"

export const usersTable = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  externalId: text("external_id").notNull().unique(),
  email: text("email").notNull(),
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  avatarUrl: text("avatar_url"),
  userType: text("user_type").notNull().default("user"), // 'user' | 'machine'
  isOnboarded: boolean("is_onboarded").notNull().default(false),
  timezone: text("timezone"),
  settings: jsonb("settings"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
  deletedAt: timestamp("deleted_at")
})

Core Tables

Hazel Chat includes these core tables:

Table	Description
users	User accounts and profiles
organizations	Multi-tenant organizations
channels	Chat channels (public/private)
channel_members	Channel membership
messages	Chat messages
attachments	File attachments
bots	Bot accounts
bot_installations	Bot installations per org
notifications	User notifications
pinned_messages	Pinned message references
integration_connections	External integrations

See the complete schema in packages/db/src/schema/.

Migrations

Generating Migrations

When you modify the schema, generate a migration:

cd packages/db
bun run db:generate

This creates a new migration file in drizzle/ with SQL statements.

Applying Migrations

# Push schema directly (no migration files)
bun run db:push

Development: Use db:push for rapid iteration without migration files.Production: Use db:migrate to apply migrations safely with rollback support.

Migration Commands

packages/db/package.json

{
  "scripts": {
    "db:push": "drizzle-kit push --force",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio"
  }
}

Drizzle Studio

Explore your database with Drizzle Studio:

cd packages/db
bun run db:studio

Open http://localhost:4983 to browse tables, run queries, and edit data.

Database Layer

Database Service

The Database service provides Effect-TS integration:

packages/db/src/services/database.ts

import { Database } from "@hazel/db"
import { Effect, Layer } from "effect"

// Create database layer
export const DatabaseLive = Layer.unwrapEffect(
  Effect.gen(function* () {
    return Database.layer({
      url: Redacted.make(process.env.DATABASE_URL),
      ssl: true
    })
  })
)

// Use in your app
const program = Effect.gen(function* () {
  const db = yield* Database.Database

  // Execute queries
  const users = yield* db.execute(
    (client) => client.select().from(usersTable)
  )
})

Transaction Context

The database layer provides automatic transaction context propagation:

import { Database } from "@hazel/db"
import { Effect } from "effect"

const createUserWithOrg = Effect.gen(function* () {
  const db = yield* Database.Database

  // All operations inside transaction use the same tx
  return yield* db.transaction(
    Effect.gen(function* () {
      const user = yield* UserRepo.insert({ email: "[email protected]" })
      const org = yield* OrganizationRepo.insert({ name: "ACME" })
      const member = yield* OrgMemberRepo.insert({ userId: user.id, orgId: org.id })

      return { user, org, member }
    })
  )
})

No manual transaction threading required! The transaction client is automatically available via Effect Context.

Repository Pattern

Creating a Repository

Repositories provide type-safe CRUD operations:

packages/backend-core/src/repos/user.repo.ts

import { ModelRepository, schema } from "@hazel/db"
import { User } from "@hazel/domain/models"
import { Effect } from "effect"

export class UserRepo extends Effect.Service<UserRepo>()("UserRepo", {
  accessors: true,
  effect: Effect.gen(function* () {
    const baseRepo = yield* ModelRepository.makeRepository(
      schema.usersTable,
      User.Model,
      {
        idColumn: "id",
        name: "User"
      }
    )

    return baseRepo
  }),
  dependencies: [DatabaseLive]
}) {}

Repository Methods

All repositories include:

// Create
const user = yield* UserRepo.insert({
  email: "[email protected]",
  firstName: "Alice",
  lastName: "Smith"
})

// Read
const userOption = yield* UserRepo.findById(userId)
const user = yield* UserRepo.with(userId, (user) => Effect.succeed(user))

// Update
const updated = yield* UserRepo.update({
  id: userId,
  firstName: "Alice Updated"
})

// Delete
yield* UserRepo.deleteById(userId)

Custom Queries

Add custom methods to repositories:

export class UserRepo extends Effect.Service<UserRepo>()("UserRepo", {
  accessors: true,
  effect: Effect.gen(function* () {
    const baseRepo = yield* ModelRepository.makeRepository(/*...*/)
    const db = yield* Database.Database

    const findByEmail = (email: string, tx?: TxFn) =>
      db.makeQuery(
        (execute, data) =>
          execute((client) =>
            client
              .select()
              .from(schema.usersTable)
              .where(eq(schema.usersTable.email, data.email))
          ),
        policyRequire("User", "select")
      )({ email }, tx)

    const findByExternalId = (externalId: string, tx?: TxFn) =>
      db.makeQuery(
        (execute, data) =>
          execute((client) =>
            client
              .select()
              .from(schema.usersTable)
              .where(eq(schema.usersTable.externalId, data.externalId))
          ),
        policyRequire("User", "select")
      )({ externalId }, tx)

    return {
      ...baseRepo,
      findByEmail,
      findByExternalId
    }
  })
}) {}

Policy-Based Authorization

The database layer integrates with authorization policies:

import { policyRequire, policyUse } from "@hazel/backend-core"

// Define policy requirement in repository
const insert = (data, tx?) =>
  db.makeQueryWithSchema(
    schema.insert,
    (execute, input) => execute((client) => client.insert(table).values(input)),
    policyRequire("User", "create")  // Require policy check
  )(data, tx)

// Provide policy in route handler
yield* UserRepo.insert(data).pipe(
  policyUse(UserPolicy.canCreate(organizationId))
)

Connection Management

Connection String Format

DATABASE_URL=postgresql://[user]:[password]@[host]:[port]/[database]?[params]

SSL/TLS Configuration

DATABASE_URL=postgresql://user:password@localhost:5432/app

Connection Pooling

For production, use connection pooling:

# PgBouncer
DATABASE_URL=postgresql://user:password@pgbouncer:6432/app?sslmode=require

# Supabase Pooler
DATABASE_URL=postgresql://user:[email protected]:6543/postgres?pgbouncer=true

Database Seeding

Create seed data for development:

scripts/seed.ts

import { UserRepo, OrganizationRepo } from "@hazel/backend-core"
import { Database } from "@hazel/db"
import { Effect, Layer } from "effect"

const seed = Effect.gen(function* () {
  const db = yield* Database.Database

  yield* db.transaction(
    Effect.gen(function* () {
      // Create test user
      const user = yield* UserRepo.insert({
        externalId: "user_test_123",
        email: "[email protected]",
        firstName: "Test",
        lastName: "User",
        userType: "user",
        isOnboarded: true
      })

      // Create test organization
      const org = yield* OrganizationRepo.insert({
        name: "Test Organization",
        slug: "test-org"
      })

      console.log("Seed data created")
    })
  )
}).pipe(
  Effect.provide(DatabaseLive),
  Effect.provide(UserRepo.Default),
  Effect.provide(OrganizationRepo.Default)
)

// Run seed
Effect.runPromise(seed)

Backup and Restore

Backup Database

# Full backup
pg_dump -U user -d app -f backup.sql

# Schema only
pg_dump -U user -d app --schema-only -f schema.sql

# Data only
pg_dump -U user -d app --data-only -f data.sql

Restore Database

# Restore full backup
psql -U user -d app < backup.sql

# Restore to new database
createdb app_restored
psql -U user -d app_restored < backup.sql

Troubleshooting

Common Issues

Migration fails with 'relation already exists'

Reset the database and reapply migrations:

# Drop and recreate database
dropdb app
createdb app

# Reapply migrations
cd packages/db
bun run db:push

Electric SQL not syncing

Verify WAL level is set to logical:

SHOW wal_level;  -- Should return 'logical'

If not, update postgresql.conf and restart:

wal_level = logical

Connection pool exhausted

Increase max_connections or use connection pooling:

ALTER SYSTEM SET max_connections = 200;
SELECT pg_reload_conf();

Slow queries

Enable query logging to identify slow queries:

-- Log queries slower than 1 second
SET log_min_duration_statement = 1000;

Add indexes for frequently queried columns.

Performance Optimization

Indexing Strategy

Create indexes for:

Foreign keys
Frequently queried columns
Sort/filter columns

import { index } from "drizzle-orm/pg-core"

export const messagesTable = pgTable("messages", {
  // ...
}, (table) => ({
  channelIdIdx: index("messages_channel_id_idx").on(table.channelId),
  createdAtIdx: index("messages_created_at_idx").on(table.createdAt)
}))

Query Optimization

// Bad: N+1 queries
for (const user of users) {
  const org = yield* OrganizationRepo.findById(user.organizationId)
}

// Good: Single query with join
const usersWithOrgs = yield* db.execute(
  (client) =>
    client
      .select()
      .from(usersTable)
      .leftJoin(organizationsTable, eq(usersTable.organizationId, organizationsTable.id))
)

Next Steps

Repository Pattern

Learn about repositories and services

Deployment

Deploy database to production

Environment Variables

Database configuration reference

Electric SQL

Real-time sync architecture

Get Started

Core Features

Guides

Integrations

Architecture

​PostgreSQL Requirements

​Version

​Electric SQL Configuration

​Quick Start

​Using Docker (Recommended)

​Manual PostgreSQL Setup

​Drizzle ORM

​Overview

​Project Structure

​Configuration

​Schema Definition

​Example Schema

​Core Tables

​Migrations

​Generating Migrations

​Applying Migrations

​Migration Commands

​Drizzle Studio

​Database Layer

​Database Service

​Transaction Context

​Repository Pattern

​Creating a Repository

​Repository Methods

​Custom Queries

​Policy-Based Authorization

​Connection Management

​Connection String Format

​SSL/TLS Configuration

​Connection Pooling

​Database Seeding

​Backup and Restore

​Backup Database

​Restore Database

​Troubleshooting

​Common Issues

​Performance Optimization

​Indexing Strategy

​Query Optimization

​Next Steps

Repository Pattern

Deployment

Environment Variables

Electric SQL

Build docs developers (and LLMs) love

PostgreSQL Requirements

Version

Electric SQL Configuration

Quick Start

Using Docker (Recommended)

Manual PostgreSQL Setup

Drizzle ORM

Overview

Project Structure

Configuration

Schema Definition

Example Schema

Core Tables

Migrations

Generating Migrations

Applying Migrations

Migration Commands

Drizzle Studio

Database Layer

Database Service

Transaction Context

Repository Pattern

Creating a Repository

Repository Methods

Custom Queries

Policy-Based Authorization

Connection Management

Connection String Format

SSL/TLS Configuration

Connection Pooling

Database Seeding

Backup and Restore

Backup Database

Restore Database

Troubleshooting

Common Issues

Performance Optimization

Indexing Strategy

Query Optimization

Next Steps