Database

Database

ChatHub uses PostgreSQL exclusively. The browser and installed PWA are clients of the server API; there is no PGlite, IndexedDB/Dexie, or Electron database runtime. Old local-edition JSON exports are handled only by the account import workflow under Settings -> Storage.

pgvector

Knowledge Base semantic retrieval is backed by the PostgreSQL vector extension. For Docker Compose, use pgvector/pgvector:pg16; for managed PostgreSQL, enable pgvector before starting ChatHub. Migration 0005_pgvector.sql runs CREATE EXTENSION IF NOT EXISTS vector.

Knowledge embeddings use vector(1024). The database has a B-tree index on the account/provider fingerprint and a partial HNSW vector_cosine_ops index for document-chunk nearest-neighbor search. Cached question vectors are not in that ANN index. Retrieval orders by cosine distance so PostgreSQL can use HNSW. See Knowledge Base and RAG.

Migration 0053_add_rag_embedding_indexes.sql creates the HNSW index with regular CREATE INDEX because Drizzle runs migrations in a transaction and PostgreSQL does not allow CREATE INDEX CONCURRENTLY there. Reads remain available during the build, but writes to embeddings are blocked until it finishes. Schedule a maintenance window before upgrading a deployment with a large embedding table.

Migration System

• Migrations live in packages/database/migrations/ and are baked into the Docker image
• Drizzle ORM tracks applied migrations via the drizzle_migrations table in PostgreSQL
• On a fresh empty DB, all migrations (0000 → latest) run automatically on first container start
• Never use bun run db:push against production — it bypasses migration tracking

Migration Safety Net

Every migration that adds a table or column also includes an idempotent ensure* helper (CREATE TABLE IF NOT EXISTS / ALTER TABLE ADD COLUMN IF NOT EXISTS) that runs on every container startup, protecting against journal drift.

Background conversation generation adds migrations 0054–0056 (conversation_generation_operations, events, and steps) plus ensureConversationGenerationOperations.cjs. Graphile Worker migrates its own graphile_worker schema; Drizzle does not manage it. See Background Conversation Generation.

assistant_memory Column Fix

If the UI shows repeated Request failed errors and logs contain column ... assistant_memory does not exist (PostgreSQL 42703), the database never received migration 0043_add_agent_assistant_memory.sql.

Fix:

1. Use a current image tag that includes 0043_add_agent_assistant_memory, set DATABASE_URL, then docker compose pull and docker compose up -d (add --force-recreate for a new container). Check logs for database migration pass.
2. Or apply the SQL manually:

ALTER TABLE agents ADD COLUMN IF NOT EXISTS assistant_memory text;

Migration Troubleshooting

If the database was created or altered outside Drizzle's migration chain, fixing drizzle_migrations by hand is error-prone. Prefer: restore from a known-good backup and run the official image entrypoint against it, or export data you need, wipe the Postgres volume, and let a fresh container apply 0000 → latest automatically.

Development Commands

bun run db:generate # generate migration SQL from schema changes
bun run db:migrate  # apply migrations locally
bun run db:studio   # open Drizzle Studio