AgentPress Docs

This document describes the database implementation using Drizzle ORM with PostgreSQL in the AgentPress monorepo.

Overview

AgentPress uses:

PostgreSQL 18.1 with pgvector extension
Drizzle ORM for type-safe database operations
30+ tables organized by domain

Configuration

Drizzle Config

File: packages/api/drizzle.config.ts

export default defineConfig({
  schema: "./src/db",
  out: "./migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  strict: true,
  casing: "camelCase",
});

Database Connection

File: packages/api/src/db/config.ts

Two database clients are configured:

// Main client for OLTP operations
export const db = drizzle(client, { schema });

// Analytics client with 30-second timeout
export const analyticsDb = drizzle(analyticsClient, { schema });

Environment Variables

DATABASE_URL=postgresql://postgres:postgres@localhost:5555/AgentPress

Schema Organization

Location: packages/api/src/db/schema/

Core Tables

Table	Purpose
`users`	User accounts and authentication
`orgs`	Organizations/workspaces
`org_members`	User-organization membership
`sessions`	User session management
`invitations`	User invitations
`passwordResets`	Password reset tokens

Agent & Conversation Tables

Table	Purpose
`agents`	AI agent definitions with model config
`agentVersions`	Agent version history
`threads`	Conversations with message counts
`messages`	Individual messages with parts
`personas`	Agent persona configurations

Knowledge Base Tables

Table	Purpose
`knowledgeBases`	Knowledge base definitions
`knowledgeBaseFiles`	File-based sources
`knowledgeBaseFreeformTexts`	Freeform text entries
`knowledgeBaseTables`	Structured table data
`knowledgeBaseWebUrls`	Web URLs with crawling
`embeddings`	KB and offer vector embeddings (halfvec(1536), model-tagged)

Tool & Task Tables

Table	Purpose
`tools`	Tool definitions with schemas
`tasks`	Task execution records
`schedules`	Recurring/one-time task templates

Schema Definitions

Users Table

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).unique().notNull(),
  username: varchar("username", { length: 255 }).unique(),
  firstName: varchar("first_name", { length: 255 }),
  lastName: varchar("last_name", { length: 255 }),

  // OAuth fields
  githubId: text("github_id"),
  googleId: text("google_id"),

  // Password (bcrypt hash)
  password: text("password"),

  // Role & Provider
  role: userRoleEnum("role").notNull().default(EUserRole.GUEST),
  provider: authProviderEnum("provider").notNull().default(EProvider.EMAIL),

  // Timestamps
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Agents Table

export const agents = pgTable("agents", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  prompt: text("prompt"),

  // Model Configuration
  primaryModelProvider: varchar("primary_model_provider", { length: 255 }),
  primaryModel: varchar("primary_model", { length: 255 }),
  primaryModelTemperature: real("primary_model_temperature"),

  // Tool & RAG Configuration (JSONB arrays)
  toolsEnabled: jsonb("tools_enabled").$type<string[]>(),
  ragEnabled: jsonb("rag_enabled").$type<string[]>(),

  // Status
  enabled: boolean("enabled").default(true),
  archived: boolean("archived").default(false),
});

Embeddings Table (Vector)

export const embeddings = pgTable("embeddings", {
  id: uuid("id").primaryKey().defaultRandom(),
  orgId: uuid("org_id")
    .notNull()
    .references(() => orgs.id, { onDelete: "cascade" }),
  resourceId: uuid("resource_id").notNull(),
  content: text("content").notNull(),

  // KB/offer vector storage is fixed at 1536 dimensions
  embedding: halfvec("embedding", { dimensions: 1536 }).notNull(),
  embeddingModel: varchar("embedding_model", { length: 50 })
    .notNull()
    .default("text-embedding-3-small"),

  metadata: jsonb("metadata").default({}),
});

// HNSW index for similarity search
// CREATE INDEX ON embeddings USING hnsw (embedding halfvec_cosine_ops)

The embedding_model column records which supported embedding model produced each stored chunk. Retrieval first discovers the stored models in the requested RAG scope, generates one query vector per model, and filters vector comparisons by the same embedding_model. This allows older chunks to remain searchable after the global embedding setting changes. Gemini knowledge-base embedding models are requested with 1536 output dimensions, and text-embedding-3-large is not exposed for knowledge-base embeddings because the shared KB/offer store is halfvec(1536).

Query Patterns

Basic CRUD

import { eq } from "drizzle-orm";
import { agents } from "../db";
import { db } from "../db/config";

export const agentRepo = {
  getAll: async () => {
    return db.select().from(agents).orderBy(agents.createdAt);
  },

  getById: async (id: string) => {
    return db
      .select()
      .from(agents)
      .where(eq(agents.id, id))
      .then((rows) => rows[0]);
  },

  create: async (data: IAgent) => {
    return db
      .insert(agents)
      .values({ ...data, createdAt: new Date(), updatedAt: new Date() })
      .returning();
  },

  update: async (data: Partial<IAgent>) => {
    const { id, ...rest } = data;
    return db
      .update(agents)
      .set({ ...rest, updatedAt: new Date() })
      .where(eq(agents.id, id!))
      .returning()
      .then((rows) => rows[0]);
  },

  delete: async (id: string) => {
    return db.delete(agents).where(eq(agents.id, id)).returning();
  },
};

Filtering with Pagination

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

getPaginated: async (options: GetOptions) => {
  let conditions: SQL[] = [eq(threads.userId, options.user.id)];

  if (options.search) {
    conditions.push(ilike(threads.name, `%${options.search}%`));
  }

  if (options.agentId) {
    conditions.push(eq(threads.agentId, options.agentId));
  }

  const offset = ((options.page || 1) - 1) * (options.pageSize || 20);

  return db
    .select()
    .from(threads)
    .where(and(...conditions))
    .orderBy(desc(threads.createdAt))
    .limit(options.pageSize || 20)
    .offset(offset);
};

Transactions

async createUser(data: UserData): Promise<IUser | undefined> {
  const now = new Date();

  return db.transaction(async (tx) => {
    // Create user
    const [createdUser] = await tx
      .insert(users)
      .values({ ...data, createdAt: now, updatedAt: now })
      .returning();

    // Add to default org
    const defaultOrg = await this.getDefaultOrg();
    if (createdUser && defaultOrg) {
      await tx.insert(orgMembers).values({
        userId: createdUser.id,
        orgId: defaultOrg.id,
      });
    }

    return createdUser;
  });
}

Indexes

Composite Indexes

// Thread filtering with date ordering
index("threads_user_id_created_at_idx").on(threads.userId, threads.createdAt);

// Message ordering within threads
index("messages_thread_id_created_at_idx").on(
  messages.threadId,
  messages.createdAt,
);

Full-Text Search Index

CREATE INDEX messages_text_content_gin_idx
ON messages USING gin(to_tsvector('english', text_content));

Vector Index (HNSW)

CREATE INDEX ON embeddings USING hnsw (embedding halfvec_cosine_ops);

Migrations

Location

Migrations: apps/api/migrations/
Format: SQL files with timestamp prefix
Tracking: __drizzle_migrations__ table

Commands

cd apps/api

# Generate migration from schema changes
bun db:generate

# Apply pending migrations
bun db:migrate

# Push schema directly (development only)
bun db:push

# Open Drizzle Studio
bun db:studio

Repository Pattern

Repositories provide data access abstraction:

Location: packages/api/src/repositories/

repositories/
├── agentRepo.ts
├── analyticsRepo.ts
├── embeddingsRepo.ts
├── feedbackRepo.ts
├── knowledgeBasesRepo.ts
├── messages/
│   ├── messageRepo.ts
│   └── cachedMessageRepo.ts
├── threads/
│   ├── threadRepo.ts
│   └── cachedThreadRepo.ts
├── tasksRepo.ts
└── userRepo.ts

Cached Repository Pattern

// cachedThreadRepo.ts
export const cachedThreadRepository = {
  ...threadRepository,

  async getById(id: string) {
    const cached = await redis.get(`thread:${id}`);
    if (cached) return JSON.parse(cached);

    const thread = await threadRepository.getById(id);
    await redis.setex(`thread:${id}`, 300, JSON.stringify(thread));
    return thread;
  },
};

Best Practices

Always use transactions for multi-table operations
Add indexes for frequently filtered columns
Use JSONB for flexible schema fields
Denormalize read-heavy aggregated data
Separate analytics client for long-running queries
Test migrations in development before production
Use returning() to get inserted/updated rows

Database Architecture