Database Documentation

# Development database
wrangler d1 create sonicjs-dev

# Production database
wrangler d1 create sonicjs-ai

name = "sonicjs-ai"
main = "src/index.ts"
compatibility_date = "2024-06-01"
compatibility_flags = ["nodejs_compat"]

# Development database binding
[[d1_databases]]
binding = "DB"
database_name = "sonicjs-dev"
database_id = "your-dev-database-id"

# R2 Bucket for media storage
[[r2_buckets]]
binding = "MEDIA_BUCKET"
bucket_name = "sonicjs-media-dev"

# KV Namespace for caching
[[kv_namespaces]]
binding = "CACHE_KV"
id = "your-kv-namespace-id"
preview_id = "your-preview-kv-id"

# Production environment
[env.production]
name = "sonicjs-ai-prod"

[[env.production.d1_databases]]
binding = "DB"
database_name = "sonicjs-ai"
database_id = "your-prod-database-id"

[[env.production.r2_buckets]]
binding = "MEDIA_BUCKET"
bucket_name = "sonicjs-media-prod"

[[env.production.kv_namespaces]]
binding = "CACHE_KV"
id = "your-prod-kv-id"

// In Cloudflare Worker
export default {
  async fetch(request: Request, env: Env) {
    const db = env.DB // D1 Database
    const bucket = env.MEDIA_BUCKET // R2 Bucket
    const cache = env.CACHE_KV // KV Namespace

    // Your application logic
  }
}

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  password_hash TEXT,
  role TEXT NOT NULL DEFAULT 'viewer',
  avatar TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  last_login_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,

  -- Extended profile fields
  phone TEXT,
  bio TEXT,
  avatar_url TEXT,
  timezone TEXT DEFAULT 'UTC',
  language TEXT DEFAULT 'en',
  email_notifications INTEGER DEFAULT 1,
  theme TEXT DEFAULT 'dark',
  two_factor_enabled INTEGER DEFAULT 0,
  two_factor_secret TEXT,
  password_reset_token TEXT,
  password_reset_expires INTEGER,
  email_verified INTEGER DEFAULT 0,
  email_verification_token TEXT,
  invitation_token TEXT,
  invited_by TEXT REFERENCES users(id),
  invited_at INTEGER,
  accepted_invitation_at INTEGER
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role);

CREATE TABLE collections (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  schema TEXT NOT NULL, -- JSON schema definition
  is_active INTEGER NOT NULL DEFAULT 1,
  managed INTEGER DEFAULT 0 NOT NULL, -- Config-managed collections
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- Indexes
CREATE INDEX idx_collections_name ON collections(name);
CREATE INDEX idx_collections_active ON collections(is_active);
CREATE INDEX idx_collections_managed ON collections(managed);

CREATE TABLE content (
  id TEXT PRIMARY KEY,
  collection_id TEXT NOT NULL REFERENCES collections(id),
  slug TEXT NOT NULL,
  title TEXT NOT NULL,
  data TEXT NOT NULL, -- JSON content data
  status TEXT NOT NULL DEFAULT 'draft',
  published_at INTEGER,
  author_id TEXT NOT NULL REFERENCES users(id),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,

  -- Scheduling fields
  scheduled_publish_at INTEGER,
  scheduled_unpublish_at INTEGER,

  -- Review workflow
  review_status TEXT DEFAULT 'none',
  reviewer_id TEXT REFERENCES users(id),
  reviewed_at INTEGER,
  review_notes TEXT,

  -- SEO and metadata
  meta_title TEXT,
  meta_description TEXT,
  featured_image_id TEXT REFERENCES media(id),
  content_type TEXT DEFAULT 'standard',

  -- Workflow state
  workflow_state_id TEXT DEFAULT 'draft',
  embargo_until INTEGER,
  expires_at INTEGER,
  version_number INTEGER DEFAULT 1,
  is_auto_saved INTEGER DEFAULT 0
);

-- Indexes
CREATE INDEX idx_content_collection ON content(collection_id);
CREATE INDEX idx_content_author ON content(author_id);
CREATE INDEX idx_content_status ON content(status);
CREATE INDEX idx_content_published ON content(published_at);
CREATE INDEX idx_content_slug ON content(slug);

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

// Users table schema
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').notNull().unique(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  passwordHash: text('password_hash'),
  role: text('role').notNull().default('viewer'),
  avatar: text('avatar'),
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
  lastLoginAt: integer('last_login_at'),
  createdAt: integer('created_at').notNull(),
  updatedAt: integer('updated_at').notNull(),
});

// Collections table schema
export const collections = sqliteTable('collections', {
  id: text('id').primaryKey(),
  name: text('name').notNull().unique(),
  displayName: text('display_name').notNull(),
  description: text('description'),
  schema: text('schema', { mode: 'json' }).notNull(),
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),
  managed: integer('managed', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

// Type inference
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Collection = typeof collections.$inferSelect;
export type NewCollection = typeof collections.$inferInsert;

// Zod validation schemas
export const insertUserSchema = createInsertSchema(users, {
  email: (schema) => schema.email(),
  firstName: (schema) => schema.min(1),
  lastName: (schema) => schema.min(1),
  username: (schema) => schema.min(3),
});

export const selectUserSchema = createSelectSchema(users);

import { drizzle } from 'drizzle-orm/d1';
import * as schema from './schema';

export function createDb(d1: D1Database) {
  return drizzle(d1, { schema });
}

// Usage in worker
export default {
  async fetch(request: Request, env: Env) {
    const db = createDb(env.DB);

    // Now you can use db with full type safety
    const users = await db.select().from(schema.users);

    return new Response(JSON.stringify(users));
  }
}

Migration SQL Files (.sql)
         ↓
   Build Script (generate-migrations.ts)
         ↓
   TypeScript Bundle (migrations-bundle.ts)
         ↓
   Runtime Execution (MigrationService)

// scripts/generate-migrations.ts
// Reads SQL files and generates TypeScript bundle

// Generated output structure:
export interface BundledMigration {
  id: string           // e.g., '001'
  name: string         // e.g., 'Create Users'
  filename: string     // e.g., '001_create_users.sql'
  description: string  // e.g., 'Migration 001: Create Users'
  sql: string          // The actual SQL content
}

export const bundledMigrations: BundledMigration[] = [...]

// Helper functions
export function getMigrationSQLById(id: string): string | null
export function getMigrationList(): Array<Omit<BundledMigration, 'sql'>>

export class MigrationService {
  constructor(private db: D1Database) {}

  /**
   * Initialize migrations tracking table
   */
  async initializeMigrationsTable(): Promise<void> {
    const createTableQuery = `
      CREATE TABLE IF NOT EXISTS migrations (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        filename TEXT NOT NULL,
        applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        checksum TEXT
      )
    `;

    await this.db.prepare(createTableQuery).run();
  }

  /**
   * Get migration status
   */
  async getMigrationStatus(): Promise<MigrationStatus> {
    await this.initializeMigrationsTable();

    const migrations = await this.getAvailableMigrations();
    const appliedMigrations = migrations.filter(m => m.applied);
    const pendingMigrations = migrations.filter(m => !m.applied);

    return {
      totalMigrations: migrations.length,
      appliedMigrations: appliedMigrations.length,
      pendingMigrations: pendingMigrations.length,
      migrations
    };
  }

  /**
   * Run pending migrations
   */
  async runPendingMigrations(): Promise<{ success: boolean; message: string; applied: string[] }> {
    const status = await this.getMigrationStatus();
    const pendingMigrations = status.migrations.filter(m => !m.applied);

    if (pendingMigrations.length === 0) {
      return {
        success: true,
        message: 'All migrations are up to date',
        applied: []
      };
    }

    const applied: string[] = [];

    for (const migration of pendingMigrations) {
      try {
        await this.applyMigration(migration);
        await this.markMigrationApplied(migration.id, migration.name, migration.filename);
        applied.push(migration.id);
      } catch (error) {
        console.error(`Failed to apply migration ${migration.id}:`, error);
        break;
      }
    }

    return {
      success: true,
      message: `Applied ${applied.length} migration(s)`,
      applied
    };
  }
}

# Local development (uses local D1 database)
npm run db:migrate

# Production
npm run db:migrate:prod

# Using wrangler directly
wrangler d1 migrations apply DB --local
wrangler d1 migrations apply DB --env production

# Check migration status
wrangler d1 migrations list DB --local

/**
 * Sync all collection configurations to the database
 */
export async function syncCollections(db: D1Database): Promise<CollectionSyncResult[]> {
  console.log('🔄 Starting collection sync...');

  const results: CollectionSyncResult[] = [];
  const configs = await loadCollectionConfigs();

  for (const config of configs) {
    const result = await syncCollection(db, config);
    results.push(result);
  }

  const created = results.filter(r => r.status === 'created').length;
  const updated = results.filter(r => r.status === 'updated').length;
  const unchanged = results.filter(r => r.status === 'unchanged').length;
  const errors = results.filter(r => r.status === 'error').length;

  console.log(`✅ Collection sync complete: ${created} created, ${updated} updated, ${unchanged} unchanged, ${errors} errors`);

  return results;
}

# Sync collections from config files
npm run sync-collections

import { eq } from 'drizzle-orm';
import { users, content, collections } from './db/schema';

// Create user
const newUser = await db.insert(users).values({
  id: crypto.randomUUID(),
  email: 'john@example.com',
  username: 'john_doe',
  firstName: 'John',
  lastName: 'Doe',
  passwordHash: await hashPassword('secret123'),
  role: 'editor',
  createdAt: Date.now(),
  updatedAt: Date.now()
}).returning();

// Create content
const newContent = await db.insert(content).values({
  id: crypto.randomUUID(),
  collectionId: 'blog-posts-collection',
  slug: 'my-first-post',
  title: 'My First Post',
  data: JSON.stringify({
    body: '<p>Hello world!</p>',
    excerpt: 'My first blog post'
  }),
  status: 'draft',
  authorId: newUser[0].id,
  createdAt: Date.now(),
  updatedAt: Date.now()
}).returning();

// Get user by email
const user = await db.select()
  .from(users)
  .where(eq(users.email, 'john@example.com'))
  .limit(1);

// Get all published content
const publishedContent = await db.select()
  .from(content)
  .where(eq(content.status, 'published'))
  .orderBy(desc(content.publishedAt))
  .limit(20);

// Get content with joins
const contentWithAuthor = await db.select({
  id: content.id,
  title: content.title,
  slug: content.slug,
  authorName: sql`${users.firstName} || ' ' || ${users.lastName}`,
  authorEmail: users.email
})
.from(content)
.leftJoin(users, eq(content.authorId, users.id))
.where(eq(content.status, 'published'));

import { desc, asc, count } from 'drizzle-orm';

async function getContentPaginated(page: number, limit: number = 20) {
  const offset = (page - 1) * limit;

  // Get paginated results
  const results = await db.select()
    .from(content)
    .where(eq(content.status, 'published'))
    .orderBy(desc(content.publishedAt))
    .limit(limit)
    .offset(offset);

  // Get total count
  const [{ total }] = await db.select({ total: count() })
    .from(content)
    .where(eq(content.status, 'published'));

  return {
    data: results,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1
    }
  };
}

// Define prepared statement
const getUserStmt = db.prepare('SELECT * FROM users WHERE email = ?');

// Execute with parameter binding
const user = await getUserStmt.bind('john@example.com').first();

// Batch insert for better performance
const batch = [];

for (const item of dataArray) {
  const stmt = db.prepare(
    'INSERT INTO content (id, title, slug, collection_id, data, author_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
  );

  batch.push(stmt.bind(
    crypto.randomUUID(),
    item.title,
    item.slug,
    item.collectionId,
    JSON.stringify(item.data),
    item.authorId,
    Date.now(),
    Date.now()
  ));
}

// Execute all at once
await db.batch(batch);

-- Essential single-column indexes
CREATE INDEX idx_content_status ON content(status);
CREATE INDEX idx_content_published ON content(published_at);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_media_type ON media(mime_type);

-- Composite indexes for multi-column queries
CREATE INDEX idx_content_collection_status ON content(collection_id, status);
CREATE INDEX idx_content_author_status ON content(author_id, status, published_at DESC);
CREATE INDEX idx_media_folder_type ON media(folder, mime_type);

-- Covering indexes (include commonly selected columns)
CREATE INDEX idx_content_published_covering ON content(status, published_at, title, slug);

-- Partial indexes (filter specific conditions)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;
CREATE INDEX idx_published_content ON content(published_at DESC) WHERE status = 'published';

// Three-tier caching: Memory → KV → Database
class CachedQuery {
  private memoryCache = new Map<string, any>();

  async get(key: string, fetcher: () => Promise<any>, ttl: number = 3600) {
    // Check memory cache
    if (this.memoryCache.has(key)) {
      return this.memoryCache.get(key);
    }

    // Check KV cache
    const cached = await env.CACHE_KV.get(key, 'json');
    if (cached) {
      this.memoryCache.set(key, cached);
      return cached;
    }

    // Fetch from database
    const data = await fetcher();

    // Store in caches
    this.memoryCache.set(key, data);
    await env.CACHE_KV.put(key, JSON.stringify(data), { expirationTtl: ttl });

    return data;
  }

  async invalidate(key: string) {
    this.memoryCache.delete(key);
    await env.CACHE_KV.delete(key);
  }
}

// Usage
const cache = new CachedQuery();

const publishedPosts = await cache.get(
  'content:published:latest',
  async () => {
    return await db.select()
      .from(content)
      .where(eq(content.status, 'published'))
      .orderBy(desc(content.publishedAt))
      .limit(20);
  },
  3600 // 1 hour TTL
);

# Export entire database to SQL file
wrangler d1 export sonicjs-dev --output=backup-dev.sql
wrangler d1 export sonicjs-ai --output=backup-prod.sql --env production

# Export with timestamp
DATE=$(date +%Y%m%d_%H%M%S)
wrangler d1 export sonicjs-ai --output=backups/backup-$DATE.sql --env production

# Import from SQL file
wrangler d1 execute sonicjs-dev --file=backup-dev.sql --local
wrangler d1 execute sonicjs-ai --file=backup-prod.sql --env production

# Create new database
wrangler d1 create <database-name>

# List all databases
wrangler d1 list

# Delete database (careful!)
wrangler d1 delete <database-name>

# Get database info
wrangler d1 info <database-name>

# Execute SQL file
wrangler d1 execute DB --file=./script.sql --local
wrangler d1 execute DB --file=./script.sql --env production

# Execute SQL command directly
wrangler d1 execute DB --command="SELECT * FROM users LIMIT 5" --local

# Execute with JSON output
wrangler d1 execute DB --command="SELECT * FROM content" --json --local