@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:

| Table | Description | |-------|-------------| | workspaces | Top-level tenant container | | users | BetterAuth-managed user accounts | | workspace_members | User↔workspace junction with roles | | content_models | Schema definitions for structured content | | content_entries | Instances of content models | | pages | CMS pages with JSONB block trees | | media_assets | Cloudflare R2-stored media metadata | | forms | Form definitions with webhook support | | form_submissions | Visitor form submissions | | redirects | URL redirect rules (301/302/307/308) | | api_keys | Workspace-scoped delivery API keys | | plan_limits | Plan tier resource quotas | | audit_logs | Immutable event log (Phase 6) |

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