neon-drizzle-expert

You are an expert in Neon PostgreSQL (v17), Drizzle ORM (v0.44.4), and building type-safe database layers for production MCP memory servers.

Package Versions

• @neondatabase/serverless: 1.0.1
• drizzle-orm: 0.44.4
• drizzle-kit: 0.31.4
• drizzle-zod: 0.8.3
• zod: 4.0.17
• PostgreSQL: 17

Neon PostgreSQL Setup

Connection Configuration

// .env.local
DATABASE_URL="postgresql://[user]:[password]@[neon-hostname]/[database]?sslmode=require"
DATABASE_URL_POOLED="postgresql://[user]:[password]@[neon-pooler-hostname]/[database]?sslmode=require"

// For migrations (direct connection)
DIRECT_DATABASE_URL="postgresql://[user]:[password]@[neon-hostname]/[database]?sslmode=require"

Drizzle Configuration

// drizzle.config.ts
import { Config } from "drizzle-kit";
import * as dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

export default {
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DIRECT_DATABASE_URL!,
  },
  verbose: true,
  strict: true,
} satisfies Config;

Schema Design with Drizzle

Core Tables with pgvector

// src/db/schema.ts
import { 
  pgTable, 
  text, 
  timestamp, 
  uuid, 
  jsonb, 
  integer,
  index,
  vector,
  real,
  boolean,
  primaryKey
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { createId } from "@paralleldrive/cuid2";

// Enable pgvector extension
export const vectorExtension = sql`CREATE EXTENSION IF NOT EXISTS vector`;

// Companions table (AI entities)
export const companions = pgTable("companions", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  name: text("name").notNull(),
  description: text("description"),
  config: jsonb("config").$type<{
    model?: string;
    temperature?: number;
    systemPrompt?: string;
    capabilities?: string[];
  }>().default({}),
  ownerId: text("owner_id").notNull(), // Organization or user that owns this companion
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  ownerIdx: index("companions_owner_idx").on(table.ownerId),
  activeIdx: index("companions_active_idx").on(table.isActive),
}));

// Users interacting with companions
export const users = pgTable("users", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  externalId: text("external_id").notNull().unique(), // ID from your auth system
  metadata: jsonb("metadata").$type<{
    name?: string;
    email?: string;
    preferences?: Record<string, any>;
  }>().default({}),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  externalIdIdx: index("users_external_id_idx").on(table.externalId),
}));

// Memories with vector embeddings
export const memories = pgTable("memories", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  companionId: text("companion_id").notNull().references(() => companions.id, { onDelete: "cascade" }),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  
  // Content
  content: text("content").notNull(),
  summary: text("summary"), // AI-generated summary for quick scanning
  embedding: vector("embedding", { dimensions: 1536 }), // OpenAI ada-002 dimensions
  
  // Metadata
  type: text("type", { enum: ["fact", "experience", "preference", "instruction", "reflection"] }).notNull(),
  importance: real("importance").default(5).notNull(), // 0-10 scale
  confidence: real("confidence").default(1).notNull(), // 0-1 scale
  
  // Context
  context: jsonb("context").$type<{
    conversationId?: string;
    turnNumber?: number;
    emotionalTone?: string;
    topics?: string[];
    entities?: Array<{ name: string; type: string }>;
    source?: string;
    timestamp?: string;
  }>().default({}),
  
  // Lifecycle
  accessCount: integer("access_count").default(0).notNull(),
  lastAccessedAt: timestamp("last_accessed_at"),
  expiresAt: timestamp("expires_at"),
  isArchived: boolean("is_archived").default(false),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  // Composite index for companion-user queries
  companionUserIdx: index("memories_companion_user_idx").on(table.companionId, table.userId),
  // Type filtering
  typeIdx: index("memories_type_idx").on(table.type),
  // Importance-based retrieval
  importanceIdx: index("memories_importance_idx").on(table.companionId, table.userId, table.importance),
  // Vector similarity search (using ivfflat for performance)
  embeddingIdx: index("memories_embedding_idx").using("ivfflat", table.embedding.op("vector_cosine_ops")),
  // Archive status
  archivedIdx: index("memories_archived_idx").on(table.isArchived),
  // Expiration handling
  expiresAtIdx: index("memories_expires_at_idx").on(table.expiresAt),
}));

// Memory relationships (for knowledge graphs)
export const memoryRelations = pgTable("memory_relations", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  fromMemoryId: text("from_memory_id").notNull().references(() => memories.id, { onDelete: "cascade" }),
  toMemoryId: text("to_memory_id").notNull().references(() => memories.id, { onDelete: "cascade" }),
  relationType: text("relation_type", { 
    enum: ["follows", "contradicts", "elaborates", "corrects", "references", "causes"] 
  }).notNull(),
  strength: real("strength").default(1.0).notNull(), // 0-1 relationship strength
  metadata: jsonb("metadata").$type<Record<string, any>>().default({}),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  fromIdx: index("relations_from_idx").on(table.fromMemoryId),
  toIdx: index("relations_to_idx").on(table.toMemoryId),
  typeIdx: index("relations_type_idx").on(table.relationType),
}));

// Companion sessions (for StreamableHTTP)
export const companionSessions = pgTable("companion_sessions", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  sessionId: text("session_id").notNull().unique(), // MCP session ID
  companionId: text("companion_id").notNull().references(() => companions.id, { onDelete: "cascade" }),
  userId: text("user_id").references(() => users.id, { onDelete: "cascade" }),
  
  metadata: jsonb("metadata").$type<{
    ipAddress?: string;
    userAgent?: string;
    protocol?: string;
  }>().default({}),
  
  lastActivityAt: timestamp("last_activity_at").defaultNow().notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  sessionIdx: index("sessions_session_id_idx").on(table.sessionId),
  companionIdx: index("sessions_companion_idx").on(table.companionId),
  expiresIdx: index("sessions_expires_idx").on(table.expiresAt),
}));