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 auth_user (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  email_verified INTEGER NOT NULL DEFAULT 0,
  image TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- 1. document_types: registered schemas (code/plugin-owned)
CREATE TABLE document_types (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  source TEXT NOT NULL,  -- 'system' | 'user'
  settings TEXT,         -- JSON
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- 2. documents: every content/media record + every historical version
--    q_* columns are VIRTUAL generated columns (json_extract from data JSON)
CREATE TABLE documents (
  id TEXT PRIMARY KEY,
  root_id TEXT NOT NULL,
  version_number INTEGER NOT NULL,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  type TEXT NOT NULL REFERENCES document_types(id),
  data TEXT NOT NULL,          -- JSON payload
  is_current_draft INTEGER NOT NULL DEFAULT 0,
  is_published INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL, -- seconds
  updated_at INTEGER NOT NULL  -- seconds
  -- q_* VIRTUAL generated columns added via MigrationService.ensureDocumentGeneratedColumns()
);

-- 3. document_references: typed strong/weak edges between documents
CREATE TABLE document_references (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  source_id TEXT NOT NULL REFERENCES documents(id),
  target_id TEXT NOT NULL REFERENCES documents(id),
  ref_type TEXT NOT NULL,
  strength TEXT NOT NULL DEFAULT 'strong'
);

-- 4. document_facets: indexed rows for multi-valued scalar fields (e.g. tags)
CREATE TABLE document_facets (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  document_id TEXT NOT NULL REFERENCES documents(id),
  facet_key TEXT NOT NULL,
  facet_value TEXT NOT NULL
);

-- 5. document_permissions: per-document ACL overrides
CREATE TABLE document_permissions (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL DEFAULT 'default',
  document_id TEXT NOT NULL REFERENCES documents(id),
  principal_type TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  action TEXT NOT NULL,
  effect TEXT NOT NULL DEFAULT 'allow'
);

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

// Better Auth tables — the only tables managed by Drizzle
export const authUser = sqliteTable('auth_user', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
  image: text('image'),
  createdAt: integer('created_at').notNull(),
  updatedAt: integer('updated_at').notNull(),
});

export const authSession = sqliteTable('auth_session', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => authUser.id),
  expiresAt: integer('expires_at').notNull(),
  createdAt: integer('created_at').notNull(),
  updatedAt: integer('updated_at').notNull(),
});

export const authAccount = sqliteTable('auth_account', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => authUser.id),
  providerId: text('provider_id').notNull(),
  accountId: text('account_id').notNull(),
  createdAt: integer('created_at').notNull(),
  updatedAt: integer('updated_at').notNull(),
});

export const authVerification = sqliteTable('auth_verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: integer('expires_at').notNull(),
  createdAt: integer('created_at').notNull(),
  updatedAt: integer('updated_at').notNull(),
});

// Document tables (documents, document_types, document_references,
// document_facets, document_permissions) are defined in raw migrations
// (0001_core.sql, 0002_documents.sql) and accessed via DocumentRepository.
// Do NOT add them here.

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

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

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

    // Auth table query (Drizzle OK here)
    const users = await db.select().from(schema.authUser);

    // Document query — always use DocumentRepository, not raw db.*
    // const repo = new DocumentRepository(env.DB, 'default');
    // const docs = await repo.list({ type: 'blog-post' });

    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

# 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

import { DocumentsService } from '@sonicjs/core/services/documents';

// Create a new document (draft)
const doc = await documentsService.create({
  tenantId: 'default',
  type: 'blog-post',
  data: {
    title: 'My First Post',
    slug: 'my-first-post',
    body: '<p>Hello world!</p>',
    excerpt: 'My first blog post',
  },
});

// Publish a document
await documentsService.publish({ rootId: doc.root_id, tenantId: 'default' });

import { DocumentRepository } from '@sonicjs/core/services/document-repository';

const repo = new DocumentRepository(env.DB, 'default');

// List published documents of a type (keyset pagination)
const posts = await repo.listPublished({
  type: 'blog-post',
  limit: 20,
});

// List current drafts
const drafts = await repo.listDrafts({ type: 'blog-post' });

// Keyset pagination on (updated_at, id) — preferred for document lists
const page1 = await repo.listPublished({ type: 'blog-post', limit: 20 });

// Next page — pass cursor from last item
const lastItem = page1[page1.length - 1];
const page2 = await repo.listPublished({
  type: 'blog-post',
  limit: 20,
  afterUpdatedAt: lastItem.updated_at,
  afterId: lastItem.id,
});

// Define prepared statement (auth tables — raw D1 or Drizzle both OK)
const getUserStmt = env.DB.prepare('SELECT * FROM auth_user WHERE email = ?');

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

// D1 batch — all document writes use raw prepare/bind/batch (R1)
const now = Math.floor(Date.now() / 1000); // documents use seconds
const batch = [];

for (const item of dataArray) {
  const id = crypto.randomUUID();
  const rootId = id;
  batch.push(
    env.DB.prepare(
      `INSERT INTO documents
         (id, root_id, version_number, tenant_id, type, data,
          is_current_draft, is_published, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, 1, 0, ?, ?)`
    ).bind(id, rootId, 1, 'default', item.type, JSON.stringify(item.data), now, now)
  );
}

// Execute all at once (atomic batch)
await env.DB.batch(batch);

-- Document repository indexes (from 0002_documents.sql)
CREATE INDEX idx_documents_root ON documents(root_id);
CREATE INDEX idx_documents_type ON documents(type);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_updated ON documents(updated_at DESC);

-- Partial indexes for draft/published axes
CREATE INDEX idx_documents_current_draft ON documents(root_id, tenant_id)
  WHERE is_current_draft = 1;
CREATE INDEX idx_documents_published ON documents(root_id, tenant_id)
  WHERE is_published = 1;

-- q_* virtual generated column indexes are added by
-- MigrationService.ensureDocumentGeneratedColumns() at bootstrap

-- Auth table indexes
CREATE INDEX idx_auth_user_email ON auth_user(email);
CREATE INDEX idx_auth_session_user ON auth_session(user_id);

// 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(
  'documents:blog-post:published:latest',
  async () => {
    const repo = new DocumentRepository(env.DB, 'default');
    return await repo.listPublished({ type: 'blog-post', 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 auth_user LIMIT 5" --local

# Execute with JSON output
wrangler d1 execute DB --command="SELECT * FROM documents WHERE is_published = 1" --json --local