# How the Database Schema Is Organized in SimStudio AI Using Drizzle ORM and pgvector

> Discover how SimStudio AI organizes its database schema with Drizzle ORM and pgvector. Learn about relational data storage and high-dimensional embedding management in PostgreSQL.

- Repository: [Sim/sim](https://github.com/simstudioai/sim)
- Tags: architecture
- Published: 2026-05-02

---

**SimStudio AI stores all relational data in PostgreSQL with a unified schema defined in [`packages/db/schema.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/schema.ts) that uses Drizzle ORM's `pgTable` builder and the `pgvector` extension for high‑dimensional embedding storage.**

The `simstudioai/sim` repository implements a monorepo‑wide database layer where every table, index, and foreign‑key relationship is declared in a single TypeScript file. This approach provides compile‑time type safety across the entire platform while leveraging PostgreSQL's native vector capabilities for AI‑powered knowledge retrieval.

## Central Schema Definition in [`packages/db/schema.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/schema.ts)

All database definitions live in **[`packages/db/schema.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/schema.ts)**, which acts as the canonical source of truth for the entire application. The file follows a consistent three‑step pattern:

1. **Import core column types** – `text`, `timestamp`, `jsonb`, `integer`, and the **`vector`** type from `drizzle-orm/pg-core`.
2. **Define tables** using `pgTable(name, columns, indexes)`, where the third argument configures indexes, unique constraints, and composite keys.
3. **Export each table** for consumption via absolute imports (`@/db/schema`) throughout the monorepo.

Every table uses string IDs as primary keys (generated externally) and declares foreign‑key relationships with `.references(() => otherTable.id, { onDelete: 'cascade' })` to maintain referential integrity.

## Core Tables and Relationships

The schema organizes platform data into logical groups:

### Identity and Workspace Tables

The **`user`** table stores core identity data with a unique index on `email`, while **`workspace`** acts as an owner‑level container for workflows and files. The `workspace` table includes foreign keys to both `user` (as `ownerId`) and `organization`, plus indexes on `ownerId` and `workspaceMode` for fast filtering.

### Workflow Tables

Automation graphs are modeled through three interconnected tables:

- **`workflow`** – Stores the top‑level automation with columns for `userId`, `workspaceId`, `folderId`, and deployment status. A unique composite constraint prevents duplicate names within the same workspace and folder.
- **`workflow_blocks`** – Represents individual nodes with `type`, `positionX/Y`, and a `subBlocks` JSONB column for flexible configuration. Indexed on `workflowId` and `type`.
- **`workflow_edges`** – Manages directed connections via `sourceBlockId` and `targetBlockId` with composite indexes for bidirectional lookups.
- **`workflow_subflows`** – Handles loop and parallel constructs with a `config` JSONB column and indexes on `workflowId` plus `type`.

### Knowledge Base with pgvector

The **`knowledge_base`** table supports AI‑retrieval augmentation by storing document chunks alongside their vector embeddings. It includes columns for `embeddingModel`, `embeddingDimension` (defaulting to 1536), and a foreign‑key relationship to both `user` and `workspace`.

### Execution Logs and Auth Tables

Telemetry data flows into **`workflow_execution_logs`**, **`job_execution_logs`**, and **`workflow_execution_snapshots`**, each indexed on `trigger`, `level`, and temporal columns. Authentication tables (`api_key`, `session`, `account`, `verification`) enforce security through `check` constraints and unique indexes, with special handling for workspace‑type API keys.

## Implementing pgvector for Vector Embeddings

SimStudio AI leverages the `pgvector` extension through Drizzle's custom type system:

```ts
import { vector } from 'drizzle-orm/pg-core';

export const knowledgeBase = pgTable(
  'knowledge_base',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').references(() => user.id),
    workspaceId: text('workspace_id').references(() => workspace.id),
    embeddingModel: text('embedding_model').notNull().default('text-embedding-3-small'),
    embeddingDimension: integer('embedding_dimension').notNull().default(1536),
    // Vector column for storing high-dimensional embeddings
    embedding: vector('embedding', { dimensions: 1536 }).notNull(),
  },
  (t) => ({
    // IVFFlat index for approximate nearest neighbor search
    embeddingIdx: index('knowledge_base_embedding_idx').using('ivfflat').on(t.embedding),
  })
);

```

**Key implementation details:**

- **Column definition** – `vector('embedding', { dimensions: N })` maps to PostgreSQL's `vector(N)` type.
- **Indexing strategy** – The `ivfflat` index enables efficient approximate nearest‑neighbor (ANN) queries using the `<->` distance operator.
- **Similarity queries** – The platform performs semantic search using raw SQL fragments within Drizzle's query builder:

```ts
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import { knowledgeBase } from '@/db/schema';

const similarChunks = await db
  .select()
  .from(knowledgeBase)
  .where(sql`${knowledgeBase.embedding} <-> ${queryVector} < 0.7`)
  .orderBy(sql`${knowledgeBase.embedding} <-> ${queryVector}`)
  .limit(5);

```

## Schema Configuration and Migration Setup

The database layer includes three supporting files alongside [`schema.ts`](https://github.com/simstudioai/sim/blob/main/schema.ts):

- **[`packages/db/drizzle.config.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/drizzle.config.ts)** – Configures the PostgreSQL dialect, connection credentials, and migration runner settings.
- **[`packages/db/constants.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/constants.ts)** – Houses shared defaults (such as credit limits) referenced by the schema for `default` values.
- **[`packages/db/scripts/migrate.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/scripts/migrate.ts)** – CLI script that generates and applies migrations based on the current [`schema.ts`](https://github.com/simstudioai/sim/blob/main/schema.ts) definitions, ensuring the database stays synchronized with code changes.

## Practical Examples

### Defining a New Table with Vector Support

```ts
import { pgTable, text, timestamp, vector, index } from 'drizzle-orm/pg-core';

export const documentEmbedding = pgTable(
  'document_embedding',
  {
    id: text('id').primaryKey(),
    docId: text('doc_id').notNull(),
    // 768-dimensional embedding (e.g., for sentence-transformers)
    embedding: vector('embedding', { dimensions: 768 }).notNull(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
  },
  (t) => ({
    embeddingIdx: index('doc_embedding_idx').using('ivfflat').on(t.embedding),
  })
);

```

### Adding Foreign‑Key Relationships

```ts
export const userProfile = pgTable(
  'user_profile',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    bio: text('bio'),
    avatarUrl: text('avatar_url'),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  (t) => ({
    userIdx: index('user_profile_user_idx').on(t.userId),
  })
);

```

### Performing Similarity Searches

```ts
import { sql } from 'drizzle-orm';
import { documentEmbedding } from '@/db/schema';

// queryVec is a Float32Array of length 768
const results = await db
  .select()
  .from(documentEmbedding)
  .where(sql`${documentEmbedding.embedding} <-> ${queryVec} < 0.5`)
  .orderBy(sql`${documentEmbedding.embedding} <-> ${queryVec}`)
  .limit(10);

```

## Summary

- **Unified schema location** – All tables are defined in [`packages/db/schema.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/schema.ts), providing a single source of truth for the monorepo.
- **Type‑safety** – Drizzle ORM's `pgTable` builder ensures compile‑time validation of columns, indexes, and foreign keys.
- **Vector storage** – The `vector` type from `drizzle-orm/pg-core` integrates `pgvector` for 1536‑dimensional (or custom) embeddings with `ivfflat` indexing.
- **Performance‑focused** – Composite indexes on foreign keys and specialized ANN indexes on vector columns keep queries fast at scale.
- **Monorepo integration** – Absolute imports (`@/db/schema`) and centralized migration scripts maintain clean package boundaries.

## Frequently Asked Questions

### What is the purpose of the `ivfflat` index on vector columns?

The `ivfflat` index creates an inverted file index with flat quantization for approximate nearest‑neighbor search. This allows PostgreSQL to perform cosine similarity or Euclidean distance calculations on high‑dimensional vectors (such as OpenAI embeddings) significantly faster than sequential scanning, which is essential for real‑time AI knowledge retrieval in SimStudio AI.

### How does SimStudio AI handle database migrations?

Migrations are managed through **[`packages/db/scripts/migrate.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/scripts/migrate.ts)**, which reads the current schema definitions from [`packages/db/schema.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/schema.ts) and applies changes to the PostgreSQL instance. Developers run this script via CLI to generate migration files and execute them, ensuring the database schema remains synchronized with the TypeScript definitions across deployments.

### Can I use different embedding dimensions for specific knowledge bases?

Yes. While the default `knowledge_base` table in [`schema.ts`](https://github.com/simstudioai/sim/blob/main/schema.ts) defines `embeddingDimension` as 1536 (matching OpenAI's `text-embedding-3-small`), you can modify the `vector` column dimensions parameter or create separate tables with different dimension sizes (such as 768 for `all-MiniLM-L6-v2` or 3072 for `text-embedding-3-large`). Each variation requires its own `ivfflat` index configured for that specific vector size.

### Where are the database connection settings configured?

Connection parameters and Drizzle ORM configuration reside in **[`packages/db/drizzle.config.ts`](https://github.com/simstudioai/sim/blob/main/packages/db/drizzle.config.ts)**. This file specifies the PostgreSQL dialect, connection string, migration folder paths, and any driver‑specific options, separating infrastructure concerns from the table definitions in [`schema.ts`](https://github.com/simstudioai/sim/blob/main/schema.ts).