@bettercms-ai/db

Drizzle ORM schema package for BetterCMS — self-hosted Postgres with postgres driver.

Overview

This package contains all database table definitions for the BetterCMS backend. It uses Drizzle ORM with the postgres driver for self-hosted Postgres connections.

Schema

14 tables matching the PRD §9 Data Model:

Key Design Decisions

• Text IDs — all primary keys use text (nanoid) for consistency with BetterAuth
• Workspace isolation — every tenant-scoped table has a workspace_id text() FK
• Soft deletes — tables with deleted_at timestamp() for recoverable deletions
• JSONB — used for block_json, fields, data, and event_data columns
• Indexes — optimized for workspace-scoping, slug lookups, and status filters
• block_embedding — text column on pages for Phase 2 pgvector AI search

Usage

import { db, schema } from "@bettercms-ai/db";

// Query pages for a workspace
const pages = await db
  .select()
  .from(schema.pages)
  .where(eq(schema.pages.workspaceId, workspaceId));

// Insert a new page
await db.insert(schema.pages).values({
  id: nanoid(),
  workspaceId,
  title: "Hello World",
  slug: "hello-world",
  status: "draft",
  blockJson: [],
});

Scripts

bun run db:generate   # Generate migrations (drizzle-kit generate)
bun run db:push       # Push schema to Neon (drizzle-kit push)
bun run db:migrate    # Run migrations (drizzle-kit migrate)
bun run db:studio     # Open Drizzle Studio (drizzle-kit studio)

Environment

Requires DATABASE_URL in your environment:

DATABASE_URL=postgresql://user:password@localhost:5432/bettercms