Prevent your wild and crazy coding agent do really dangerous things to your Cloudflare D1 data

sqlite-d1-cloudflare-safety-guard.ts

#!/usr/bin/env bun
/**
 * Claude Code PreToolUse hook: D1/SQLite Safety Guard
 *
 * Blocks operations that WILL cause problems with D1/SQLite schema management.
 *
 * ## Why This Hook Exists
 *
 * On 2026-01-23, the Ceetrix staging database was DELETED to work around a failed migration.
 * The migration used table recreation to modify a CHECK constraint.
 * This hook prevents similar incidents by blocking patterns that lead to trouble.
 *
 * ## D1/SQLite ALTER TABLE Limitations
 *
 * SQLite only supports these ALTER TABLE operations:
 * - RENAME TABLE
 * - RENAME COLUMN (SQLite 3.25+)
 * - ADD COLUMN
 * - DROP COLUMN (SQLite 3.35+)
 *
 * EVERYTHING ELSE requires table recreation, which is dangerous and error-prone.
 *
 * Sources:
 * - https://www.sqlite.org/lang_altertable.html
 * - https://developers.cloudflare.com/d1/sql-api/sql-statements/
 *
 * @see hooks/__tests__/db-safety-guard.test.ts for test coverage
 */

interface HookEvent {
  tool_name: string;
  tool_input: {
    command?: string;      // Bash
    file_path?: string;    // Edit/Write
    content?: string;      // Write
    old_string?: string;   // Edit
    new_string?: string;   // Edit
  };
}

/**
 * BLOCKED PATTERNS TABLE
 *
 * | Pattern                    | Why Blocked                                          | Alternative                        |
 * |----------------------------|------------------------------------------------------|------------------------------------|
 * | CHECK (...)                | Cannot be altered without table recreation           | Validate in application layer      |
 * | DROP TABLE (non-temp)      | Destroys data, cannot be undone                      | Soft delete or backup first        |
 * | TRUNCATE TABLE             | Destroys all data instantly                          | DELETE with WHERE clause           |
 * | CREATE TABLE *_new + DROP  | Table recreation pattern - risky, no rollback        | Use ALTER TABLE ADD/DROP COLUMN    |
 * | ALTER TABLE ... MODIFY     | Not supported in SQLite                              | Not possible - design around it    |
 * | ALTER TABLE ... ALTER      | Not supported in SQLite                              | Not possible - design around it    |
 * | ALTER TABLE ... CHANGE     | Not supported in SQLite (MySQL syntax)               | Not possible - design around it    |
 * | ADD CONSTRAINT             | Not supported in SQLite for existing tables          | Add constraints at CREATE time     |
 * | DROP CONSTRAINT            | Not supported in SQLite                              | Validate in application layer      |
 * | wrangler d1 delete         | Deletes entire database                              | Never do this                      |
 * | database_id change         | Abandons existing database                           | Never do this                      |
 *
 * ALLOWED PATTERNS:
 * - ALTER TABLE ... ADD COLUMN (safe, additive)
 * - ALTER TABLE ... DROP COLUMN (safe in SQLite 3.35+, D1 supports this)
 * - ALTER TABLE ... RENAME COLUMN (safe in SQLite 3.25+)
 * - ALTER TABLE ... RENAME TO (safe)
 * - DROP TABLE IF EXISTS *_new/*_old/*_temp/*_backup (cleanup of temp tables)
 * - All operations on localhost (no --remote, no --env staging/production)
 */

// =============================================================================
// PATTERNS
// =============================================================================

// CHECK constraint - cannot be modified without table recreation
const CHECK_CONSTRAINT_PATTERN = /\bCHECK\s*\(/i;

// Dangerous SQL operations
const DROP_TABLE_DANGEROUS = /\bDROP\s+TABLE\s+(?!IF\s+EXISTS\s+\S+_(new|old|temp|backup)\b)(?!\S+_(new|old|temp|backup)\b)/i;
const DROP_DATABASE_PATTERN = /\bDROP\s+DATABASE\b/i;
const TRUNCATE_PATTERN = /\bTRUNCATE\s+TABLE\b/i;

// Unsupported ALTER TABLE operations in SQLite
const ALTER_MODIFY_PATTERN = /\bALTER\s+TABLE\s+\S+\s+(MODIFY|ALTER|CHANGE)\s+/i;
const ADD_CONSTRAINT_PATTERN = /\bALTER\s+TABLE\s+\S+\s+ADD\s+CONSTRAINT\b/i;
const DROP_CONSTRAINT_PATTERN = /\bALTER\s+TABLE\s+\S+\s+DROP\s+CONSTRAINT\b/i;

// Table recreation pattern (CREATE *_new, copy, DROP original)
const TABLE_RECREATION_PATTERN = /\bCREATE\s+TABLE\s+(\w+)_new\b[\s\S]*?\bDROP\s+TABLE\s+\1\b/i;

// Remote operation detection
const REMOTE_OPERATION_PATTERN = /--remote|--env\s+(staging|production)/i;

// Wrangler D1 delete
const WRANGLER_DELETE_PATTERN = /\bwrangler\s+d1\s+delete\b/i;

// Database ID in wrangler.toml
const DATABASE_ID_PATTERN = /\bdatabase_id\s*=/;

// Migration file path
const MIGRATION_PATH_PATTERN = /migrations\/\d+.*\.sql$/;

// =============================================================================
// BLOCKED PATTERN DEFINITIONS
// =============================================================================

interface BlockedPattern {
  pattern: RegExp;
  reason: string;
  alternative: string;
  appliesToMigrations: boolean;
  appliesToRemoteSQL: boolean;
}

const BLOCKED_PATTERNS: BlockedPattern[] = [
  {
    pattern: CHECK_CONSTRAINT_PATTERN,
    reason: 'CHECK constraints cannot be altered without table recreation',
    alternative: 'Validate in application layer (route handlers, service layer)',
    appliesToMigrations: true,
    appliesToRemoteSQL: false,
  },
  {
    pattern: DROP_TABLE_DANGEROUS,
    reason: 'DROP TABLE destroys data and cannot be undone',
    alternative: 'Use temp table naming (*_new, *_old, *_temp, *_backup) or backup first',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: DROP_DATABASE_PATTERN,
    reason: 'DROP DATABASE destroys entire database',
    alternative: 'Never do this. Use Cloudflare dashboard if absolutely necessary.',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: TRUNCATE_PATTERN,
    reason: 'TRUNCATE TABLE destroys all data instantly',
    alternative: 'Use DELETE with WHERE clause for selective deletion',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: ALTER_MODIFY_PATTERN,
    reason: 'ALTER TABLE MODIFY/ALTER/CHANGE is not supported in SQLite',
    alternative: 'SQLite only supports: ADD COLUMN, DROP COLUMN, RENAME COLUMN, RENAME TABLE',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: ADD_CONSTRAINT_PATTERN,
    reason: 'ADD CONSTRAINT is not supported in SQLite for existing tables',
    alternative: 'Constraints must be defined at CREATE TABLE time. Validate in application layer.',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: DROP_CONSTRAINT_PATTERN,
    reason: 'DROP CONSTRAINT is not supported in SQLite',
    alternative: 'Constraints cannot be removed. Validate in application layer instead.',
    appliesToMigrations: true,
    appliesToRemoteSQL: true,
  },
  {
    pattern: TABLE_RECREATION_PATTERN,
    reason: 'Table recreation (CREATE *_new + DROP original) is risky with no rollback',
    alternative: 'Use ALTER TABLE ADD/DROP COLUMN. For constraint changes, validate in application layer.',
    appliesToMigrations: true,
    appliesToRemoteSQL: false,
  },
];

// =============================================================================
// HELPER FUNCTIONS
// =============================================================================

function isRemoteOperation(command: string): boolean {
  return REMOTE_OPERATION_PATTERN.test(command);
}

function formatBlockMessage(pattern: BlockedPattern): string {
  return `BLOCKED: ${pattern.reason}

Alternative: ${pattern.alternative}

D1/SQLite ALTER TABLE only supports:
  - ADD COLUMN
  - DROP COLUMN
  - RENAME COLUMN
  - RENAME TABLE

Everything else requires table recreation, which is dangerous.
See: https://www.sqlite.org/lang_altertable.html`;
}

// =============================================================================
// CHECK FUNCTIONS
// =============================================================================

function checkBashCommand(command: string): string | null {
  // D1 delete is always blocked
  if (WRANGLER_DELETE_PATTERN.test(command)) {
    return `BLOCKED: D1 database deletion.

Deleting D1 databases destroys all data permanently.
This caused staging data loss on 2026-01-23.

Alternative: Never do this. If absolutely necessary, use Cloudflare dashboard manually.`;
  }

  // Only check SQL patterns if targeting remote
  if (!isRemoteOperation(command)) {
    return null; // Localhost - allow everything
  }

  // Check d1 execute commands for dangerous SQL
  if (/\bd1\s+execute\b/i.test(command)) {
    for (const blocked of BLOCKED_PATTERNS) {
      if (blocked.appliesToRemoteSQL && blocked.pattern.test(command)) {
        return formatBlockMessage(blocked);
      }
    }
  }

  return null;
}

function checkMigrationFile(filePath: string, content: string): string | null {
  if (!MIGRATION_PATH_PATTERN.test(filePath)) {
    return null; // Not a migration file
  }

  for (const blocked of BLOCKED_PATTERNS) {
    if (blocked.appliesToMigrations && blocked.pattern.test(content)) {
      return formatBlockMessage(blocked);
    }
  }

  return null;
}

function checkWranglerToml(filePath: string, content: string): string | null {
  if (!filePath.endsWith('wrangler.toml')) {
    return null;
  }

  if (DATABASE_ID_PATTERN.test(content)) {
    return `BLOCKED: Database ID change detected in wrangler.toml.

Changing database_id abandons the existing database and all its data.
This caused staging data loss on 2026-01-23.

Alternative: Never change database_id. Fix migrations instead of recreating databases.`;
  }

  return null;
}

function checkFileEdit(filePath: string, content: string): string | null {
  // Check wrangler.toml first
  const tomlError = checkWranglerToml(filePath, content);
  if (tomlError) return tomlError;

  // Check migration files
  const migrationError = checkMigrationFile(filePath, content);
  if (migrationError) return migrationError;

  return null;
}

// =============================================================================
// MAIN HOOK
// =============================================================================

function preToolUse(event: HookEvent): void {
  const { tool_name, tool_input } = event;

  let error: string | null = null;

  if (tool_name === 'Bash' && tool_input.command) {
    error = checkBashCommand(tool_input.command);
  }

  if (tool_name === 'Write' && tool_input.file_path && tool_input.content) {
    error = checkFileEdit(tool_input.file_path, tool_input.content);
  }

  if (tool_name === 'Edit' && tool_input.file_path && tool_input.new_string) {
    error = checkFileEdit(tool_input.file_path, tool_input.new_string);
  }

  if (error) {
    console.error(`\n❌ ${error}\n`);
    process.exit(2);
  }
}

// =============================================================================
// STDIN HANDLER
// =============================================================================

let input = '';

process.stdin.on('data', (chunk) => {
  input += chunk;
});

process.stdin.on('end', () => {
  try {
    const eventData = JSON.parse(input);
    preToolUse(eventData);
    // Exit 0 = allow operation
  } catch (error) {
    console.error('[db-safety-guard] Error parsing event data:', error);
    process.exit(1);
  }
});