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

SimStudio AI stores all relational data in PostgreSQL with a unified schema defined in 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

All database definitions live in 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:

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:

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:

• packages/db/drizzle.config.ts – Configures the PostgreSQL dialect, connection credentials, and migration runner settings.
• packages/db/constants.ts – Houses shared defaults (such as credit limits) referenced by the schema for default values.
• packages/db/scripts/migrate.ts – CLI script that generates and applies migrations based on the current schema.ts definitions, ensuring the database stays synchronized with code changes.

Practical Examples

Defining a New Table with Vector Support

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

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

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, 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, which reads the current schema definitions from 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 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. 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}