Database Architecture

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

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

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

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(),
});

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),
});

export const embeddings = pgTable("embeddings", {
  id: uuid("id").primaryKey().defaultRandom(),
  resourceId: uuid("resource_id").references(() => knowledgeBaseFiles.id, { onDelete: "cascade" }),
 
  // Vector column with HNSW index
  embedding: halfvec("embedding", { dimensions: 3072 }),
 
  metadata: jsonb("metadata"),
});
 
// HNSW index for similarity search
// CREATE INDEX ON embeddings USING hnsw (embedding halfvec_cosine_ops)

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();
  },
};

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);
}

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;
  });
}

// 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)

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

CREATE INDEX ON embeddings USING hnsw (embedding halfvec_cosine_ops);

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

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

// 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;
  },
};