{"id":324,"date":"2026-09-07T13:27:23","date_gmt":"2026-09-04T13:27:23","guid":{"rendered":"https:\/\/softcrony.com\/blog\/?p=324"},"modified":"2026-09-04T13:27:23","modified_gmt":"2026-09-04T13:27:23","slug":"database-audit-ai-generated-schema-queries","status":"publish","type":"post","link":"https:\/\/softcrony.com\/blog\/database-audit-ai-generated-schema-queries\/","title":{"rendered":"Database Audit: What AI Misses When It Writes Your Queries and Schema"},"content":{"rendered":"

This is Part 6 of our AI Code Audit Series<\/a> \u2014 covering the essential audits every team should run when building with AI agentic editors.<\/p>\n

The database audit is the final post in this series and in many ways the most consequential. Application code can be refactored. Business logic can be corrected. Security vulnerabilities can be patched. But database schema decisions that reach production with real data are significantly harder and more expensive to fix than any of the problems covered in the previous five posts.<\/p>\n

Changing a column’s data type on a table with two million rows requires a careful migration that locks the table, copies data, and validates the result \u2014 an operation that takes hours and requires a maintenance window. Adding a NOT NULL constraint to a column that already contains NULL values requires a data cleanup pass before the constraint can be added. Splitting a poorly designed table into two properly normalised tables requires migrating data, updating all application code that touches the affected tables, and careful coordination across the entire team.<\/p>\n

AI agentic editors introduce consistent, predictable database problems. This post covers all of them \u2014 with the audit checks to find them and the corrections to apply before they’re locked into production.<\/p>\n

Why AI Gets Database Design Wrong<\/h2>\n

AI generates database schemas that are locally reasonable \u2014 each table makes sense for the specific feature being built. What’s missing is the global perspective: how tables relate to each other across the full application, whether the data types chosen will accommodate the full range of values the application will encounter, whether constraints correctly enforce business rules at the database level, and whether the schema will support the queries the application will need to run efficiently at scale.<\/p>\n

AI also generates schemas in the context of individual prompts. If a feature is built in one session, the schema is designed for that feature. When a related feature is built in a different session, the AI makes independent schema decisions without knowing what was decided before \u2014 producing schemas that are internally inconsistent and that will require reconciliation as the application grows.<\/p>\n

Database problems are also silent in development. Wrong data types accept values correctly until a value exceeds the column’s capacity. Missing constraints allow invalid data until someone enters it. Poor normalisation works fine until data update anomalies surface months later. By the time these problems are visible, data has already been affected.<\/p>\n

Schema Problem 1 \u2014 Wrong Data Types<\/h2>\n

AI chooses data types that work for the test cases in the prompt \u2014 not necessarily for the full range of values the column will encounter in production.<\/p>\n

\/\/ PROBLEMATIC \u2014 common AI-generated data type mistakes\r\nSchema::create('products', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->string('name');              \/\/ No length limit \u2014 defaults to 255, may be too short for some products\r\n    $table->float('price');              \/\/ NEVER use float for money \u2014 floating point imprecision\r\n    $table->integer('stock');            \/\/ Signed integer allows negative stock \u2014 nonsensical\r\n    $table->string('description');       \/\/ 255 chars \u2014 far too short for product descriptions\r\n    $table->string('status');            \/\/ Uncontrolled string \u2014 should be enum\r\n    $table->string('sku');               \/\/ No unique constraint \u2014 duplicates allowed\r\n    $table->integer('weight');           \/\/ Integers for weight lose precision (1.5kg becomes 1 or 2)\r\n    $table->string('phone');             \/\/ Phone numbers are not numeric strings \u2014 correct type but needs format\r\n    $table->integer('user_id');          \/\/ Should be unsignedBigInteger to match id() columns\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ CORRECT \u2014 appropriate data types for each column\r\nSchema::create('products', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->string('name', 500);                    \/\/ Longer limit for product names\r\n    $table->decimal('price', 10, 2);                \/\/ ALWAYS decimal for money \u2014 exact precision\r\n    $table->unsignedInteger('stock')->default(0);   \/\/ Unsigned prevents negative values\r\n    $table->text('description')->nullable();         \/\/ TEXT for long content\r\n    $table->enum('status', [                        \/\/ Enum enforces valid values at DB level\r\n        'draft',\r\n        'active',\r\n        'out_of_stock',\r\n        'discontinued'\r\n    ])->default('draft');\r\n    $table->string('sku', 100)->unique();           \/\/ Unique constraint prevents duplicates\r\n    $table->decimal('weight_kg', 8, 3)->nullable(); \/\/ Decimal for weight precision\r\n    $table->string('phone', 20)->nullable();        \/\/ Consistent length for phone numbers\r\n    $table->foreignId('user_id')->constrained();    \/\/ foreignId() creates correct unsigned bigint\r\n    $table->timestamps();\r\n\r\n    $table->index(['status', 'created_at']);        \/\/ Composite index for common query\r\n});<\/code><\/pre>\n
The money problem is critical.<\/strong> Float and double types use binary floating point representation which cannot exactly represent most decimal fractions. \u20b999.99 stored as a float might retrieve as \u20b999.98999999999999 or \u20b999.99000000000001. In financial applications, these imprecisions accumulate into real discrepancies. Always use decimal(10, 2) for money \u2014 or store amounts as integers in the smallest currency unit (paise) and divide by 100 for display.<\/p>\n
\/\/ The float money problem demonstrated\r\n$price = 0.1 + 0.2;\r\nvar_dump($price === 0.3); \/\/ bool(false) \u2014 floating point cannot represent 0.3 exactly\r\nvar_dump($price);          \/\/ float(0.30000000000000004)\r\n\r\n\/\/ Correct approach \u2014 store as decimal or integer\r\n\/\/ In migration: $table->decimal('price_inr', 10, 2);\r\n\/\/ In model: protected $casts = ['price_inr' => 'decimal:2'];\r\n\r\n\/\/ Or store as integer paise\r\n\/\/ $table->unsignedInteger('price_paise'); \/\/ \u20b999.99 stored as 9999\r\n\/\/ Display: number_format($product->price_paise \/ 100, 2) \/\/ \"99.99\"<\/code><\/pre>\n
Audit check \u2014 find wrong data types in existing migrations:<\/strong><\/p>\n
# Find float\/double columns that should be decimal\r\ngrep -rn \"float\\|double\" database\/migrations --include=\"*.php\"\r\n# Every result for financial data is a finding\r\n\r\n# Find string columns that should be text\r\ngrep -rn \"->string(\" database\/migrations --include=\"*.php\" | \\\r\n  grep -i \"description\\|content\\|body\\|notes\\|comment\\|bio\\|address\"\r\n\r\n# Find integer foreign keys that should be foreignId\/unsignedBigInteger\r\ngrep -rn \"->integer('.*_id')\" database\/migrations --include=\"*.php\"\r\n\r\n# Check actual column types in database directly\r\nSELECT \r\n    TABLE_NAME,\r\n    COLUMN_NAME,\r\n    DATA_TYPE,\r\n    COLUMN_TYPE\r\nFROM INFORMATION_SCHEMA.COLUMNS\r\nWHERE TABLE_SCHEMA = 'your_database'\r\nAND DATA_TYPE IN ('float', 'double')\r\nORDER BY TABLE_NAME, COLUMN_NAME;<\/code><\/pre>\n
Schema Problem 2 \u2014 Missing Constraints<\/h2>\n
AI generates migrations that create tables without the constraints that enforce data integrity at the database level. Application code validates data before it reaches the database \u2014 but application bugs, direct database access, failed transactions, and race conditions can all result in invalid data reaching the database without constraint enforcement.<\/p>\n
\/\/ PROBLEMATIC \u2014 missing constraints allow invalid data\r\nSchema::create('order_items', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->integer('order_id');         \/\/ No foreign key \u2014 orphaned records possible\r\n    $table->integer('product_id');       \/\/ No foreign key \u2014 references to deleted products\r\n    $table->integer('quantity');         \/\/ No check \u2014 negative quantities allowed\r\n    $table->float('unit_price');         \/\/ Float for money (wrong) + no check for negative\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ CORRECT \u2014 constraints enforce integrity at database level\r\nSchema::create('order_items', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->foreignId('order_id')\r\n          ->constrained()\r\n          ->onDelete('cascade');         \/\/ Delete items when order is deleted\r\n    $table->foreignId('product_id')\r\n          ->constrained()\r\n          ->onDelete('restrict');        \/\/ Prevent deleting products with order history\r\n    $table->unsignedInteger('quantity'); \/\/ Unsigned prevents negative quantities\r\n    $table->decimal('unit_price', 10, 2); \/\/ Decimal for money\r\n    $table->decimal('subtotal', 10, 2);   \/\/ Stored subtotal \u2014 not recalculated\r\n    $table->timestamps();\r\n\r\n    \/\/ Composite unique \u2014 same product cannot appear twice in same order\r\n    $table->unique(['order_id', 'product_id']);\r\n});\r\n\r\n\/\/ For MySQL 8+ \u2014 check constraints for business rules\r\nDB::statement('ALTER TABLE order_items ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0)');\r\nDB::statement('ALTER TABLE order_items ADD CONSTRAINT chk_price_positive CHECK (unit_price >= 0)');\r\nDB::statement('ALTER TABLE order_items ADD CONSTRAINT chk_subtotal_correct CHECK (ABS(subtotal - (unit_price * quantity)) < 0.01)');<\/code><\/pre>\n
Missing NOT NULL constraints<\/strong> are equally common \u2014 AI generates nullable columns for fields that should always have values:<\/p>\n
\/\/ PROBLEMATIC \u2014 nullable columns that should always have values\r\nSchema::create('users', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->string('name')->nullable();   \/\/ Name should always exist\r\n    $table->string('email')->nullable();  \/\/ Email should always exist and be unique\r\n    $table->string('role');               \/\/ String role with no constraint\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ CORRECT\r\nSchema::create('users', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->string('name', 255);                    \/\/ NOT NULL by default in Laravel\r\n    $table->string('email', 255)->unique();          \/\/ NOT NULL + unique\r\n    $table->enum('role', ['admin', 'manager', 'staff', 'customer'])\r\n          ->default('customer');                     \/\/ Enforced values + default\r\n    $table->timestamp('email_verified_at')->nullable(); \/\/ Correctly nullable\r\n    $table->timestamps();\r\n});<\/code><\/pre>\n
Audit check \u2014 find missing constraints:<\/strong><\/p>\n
# Find foreign key columns without constraints\r\nSELECT \r\n    col.TABLE_NAME,\r\n    col.COLUMN_NAME\r\nFROM INFORMATION_SCHEMA.COLUMNS col\r\nLEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu \r\n    ON col.TABLE_NAME = kcu.TABLE_NAME \r\n    AND col.COLUMN_NAME = kcu.COLUMN_NAME\r\n    AND kcu.REFERENCED_TABLE_NAME IS NOT NULL\r\nWHERE col.TABLE_SCHEMA = 'your_database'\r\nAND col.COLUMN_NAME LIKE '%\\_id'\r\nAND col.COLUMN_NAME != 'id'\r\nAND kcu.COLUMN_NAME IS NULL;\r\n\r\n# Find nullable columns that likely shouldn't be\r\ngrep -rn \"->nullable()\" database\/migrations --include=\"*.php\" | \\\r\n  grep -i \"name\\|email\\|status\\|type\\|user_id\\|order_id\"<\/code><\/pre>\n
Schema Problem 3 \u2014 Poor Normalisation<\/h2>\n
AI agentic editors frequently denormalise data \u2014 storing the same information in multiple places, or embedding related data as JSON columns when a proper related table is the correct approach.<\/p>\n
\/\/ PROBLEMATIC \u2014 poorly normalised schema\r\nSchema::create('orders', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->foreignId('user_id')->constrained();\r\n\r\n    \/\/ Customer details duplicated \u2014 should reference users table\r\n    $table->string('customer_name');\r\n    $table->string('customer_email');\r\n    $table->string('customer_phone');\r\n\r\n    \/\/ Address stored as JSON \u2014 no queryability, no constraints\r\n    $table->json('shipping_address');\r\n\r\n    \/\/ Items stored as JSON \u2014 cannot be queried, indexed, or constrained\r\n    $table->json('items');\r\n\r\n    $table->decimal('total', 10, 2);\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ CORRECT \u2014 properly normalised with related tables\r\n\/\/ orders table \u2014 only order-specific data\r\nSchema::create('orders', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->foreignId('user_id')->constrained();\r\n    $table->foreignId('shipping_address_id')->constrained('addresses');\r\n    $table->string('order_number', 50)->unique();\r\n    $table->enum('status', ['pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'returned']);\r\n    $table->decimal('subtotal', 10, 2);\r\n    $table->decimal('discount_amount', 10, 2)->default(0);\r\n    $table->decimal('shipping_cost', 10, 2)->default(0);\r\n    $table->decimal('tax_amount', 10, 2)->default(0);\r\n    $table->decimal('total', 10, 2);\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ Separate addresses table \u2014 reusable, queryable, constrained\r\nSchema::create('addresses', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->foreignId('user_id')->constrained();\r\n    $table->string('name', 255);\r\n    $table->string('line1', 500);\r\n    $table->string('line2', 500)->nullable();\r\n    $table->string('city', 100);\r\n    $table->string('state', 100);\r\n    $table->string('pincode', 10);\r\n    $table->string('phone', 20);\r\n    $table->boolean('is_default')->default(false);\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ Separate order_items table \u2014 proper line items\r\nSchema::create('order_items', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->foreignId('order_id')->constrained()->onDelete('cascade');\r\n    $table->foreignId('product_id')->constrained()->onDelete('restrict');\r\n    $table->string('product_name', 500);  \/\/ Snapshot of name at order time\r\n    $table->decimal('unit_price', 10, 2); \/\/ Snapshot of price at order time\r\n    $table->unsignedInteger('quantity');\r\n    $table->decimal('subtotal', 10, 2);\r\n    $table->timestamps();\r\n});<\/code><\/pre>\n
When JSON columns are acceptable<\/strong> \u2014 JSON columns are legitimate for genuinely unstructured, variable, non-queryable data. Product metadata that varies by category, feature flags, third-party API response storage. They're wrong when used for structured data that will be queried, filtered, or joined.<\/p>\n
\/\/ Legitimate JSON usage \u2014 variable metadata that is never queried individually\r\nSchema::create('products', function (Blueprint $table) {\r\n    $table->id();\r\n    $table->string('name');\r\n    $table->decimal('price', 10, 2);\r\n    $table->json('specifications')->nullable(); \/\/ {'color': 'red', 'material': 'cotton', 'care': '...'}\r\n    \/\/ specifications vary by category and are displayed as a block \u2014 never filtered individually\r\n    $table->timestamps();\r\n});\r\n\r\n\/\/ Illegitimate JSON usage \u2014 data that IS queried individually\r\n\/\/ WRONG: $table->json('address'); \/\/ If you ever do WHERE address->city = 'Mumbai'\r\n\/\/ RIGHT: separate addresses table with proper columns<\/code><\/pre>\n
Schema Problem 4 \u2014 Unsafe Migration Practices<\/h2>\n
AI-generated migrations are written for a fresh database \u2014 they don't account for the reality that production databases have existing data, ongoing transactions, and table sizes that make some migration operations dangerous or impossible without careful planning.<\/p>\n
\/\/ DANGEROUS \u2014 migrations that are unsafe on production databases with data\r\n\r\n\/\/ Danger 1: Adding NOT NULL column without default to table with existing rows\r\nSchema::table('users', function (Blueprint $table) {\r\n    $table->string('phone')->notNull(); \/\/ FAILS if any existing row has no phone\r\n});\r\n\r\n\/\/ Danger 2: Changing column type on large table\r\nSchema::table('products', function (Blueprint $table) {\r\n    $table->decimal('price', 10, 2)->change(); \/\/ Full table rewrite \u2014 locks table\r\n});\r\n\r\n\/\/ Danger 3: Adding unique index to column with existing duplicates\r\nSchema::table('users', function (Blueprint $table) {\r\n    $table->unique('email'); \/\/ FAILS if duplicate emails exist\r\n});\r\n\r\n\/\/ SAFE \u2014 migrations that account for existing data\r\n\r\n\/\/ Safe: Adding NOT NULL column \u2014 provide default or make nullable first\r\nSchema::table('users', function (Blueprint $table) {\r\n    $table->string('phone', 20)->nullable()->after('email'); \/\/ Add as nullable\r\n});\r\n\/\/ Then in a second migration after backfilling data:\r\nSchema::table('users', function (Blueprint $table) {\r\n    $table->string('phone', 20)->notNull()->change(); \/\/ Make NOT NULL after data exists\r\n});\r\n\r\n\/\/ Safe: Large table column changes \u2014 use pt-online-schema-change or gh-ost\r\n\/\/ For tables > 1M rows, never use standard ALTER TABLE in production\r\n\/\/ Use: pt-online-schema-change --alter \"MODIFY price DECIMAL(10,2)\" D=mydb,t=products\r\n\r\n\/\/ Safe: Adding unique index \u2014 clean duplicates first\r\n\/\/ Step 1: Identify and resolve duplicates\r\n$duplicates = DB::select(\"\r\n    SELECT email, COUNT(*) as count \r\n    FROM users \r\n    GROUP BY email \r\n    HAVING count > 1\r\n\");\r\n\/\/ Handle duplicates in application logic or data cleanup script\r\n\r\n\/\/ Step 2: Then add the constraint\r\nSchema::table('users', function (Blueprint $table) {\r\n    $table->unique('email');\r\n});\r\n\r\n\/\/ Always wrap related schema changes in transactions\r\nDB::transaction(function () {\r\n    Schema::table('orders', function (Blueprint $table) {\r\n        $table->string('order_number', 50)->nullable()->after('id');\r\n    });\r\n\r\n    \/\/ Backfill existing rows\r\n    DB::table('orders')->whereNull('order_number')->chunkById(500, function ($orders) {\r\n        foreach ($orders as $order) {\r\n            DB::table('orders')\r\n                ->where('id', $order->id)\r\n                ->update(['order_number' => 'ORD-' . str_pad($order->id, 8, '0', STR_PAD_LEFT)]);\r\n        }\r\n    });\r\n\r\n    Schema::table('orders', function (Blueprint $table) {\r\n        $table->string('order_number', 50)->notNull()->unique()->change();\r\n    });\r\n});<\/code><\/pre>\n
Audit check \u2014 find dangerous migration patterns:<\/strong><\/p>\n
# Find migrations adding NOT NULL columns without defaults\r\ngrep -rn \"notNull\\(\\)\\|->nullable(false)\" database\/migrations --include=\"*.php\"\r\n# Review each \u2014 is this a new table (safe) or existing table (potentially dangerous)?\r\n\r\n# Find ->change() calls on potentially large tables\r\ngrep -rn \"->change()\" database\/migrations --include=\"*.php\"\r\n\r\n# Find unique index additions that might fail on existing data\r\ngrep -rn \"->unique()\" database\/migrations --include=\"*.php\" | \\\r\n  grep -v \"Schema::create\" # Unique on existing tables needs data audit first<\/code><\/pre>\n
Query Problem 1 \u2014 Unsafe Raw Queries<\/h2>\n
Beyond SQL injection (covered in the security audit), AI-generated raw queries have additional problems \u2014 incorrect escaping of special characters, wrong handling of NULL values, and improper handling of dynamic ORDER BY and column names.<\/p>\n
\/\/ PROBLEMATIC \u2014 unsafe dynamic query construction\r\npublic function getSortedProducts(string $sortColumn, string $sortDirection): Collection\r\n{\r\n    \/\/ SQL injection via column name \u2014 not caught by parameter binding\r\n    return DB::select(\"SELECT * FROM products ORDER BY {$sortColumn} {$sortDirection}\");\r\n}\r\n\r\n\/\/ PROBLEMATIC \u2014 NULL handling errors\r\npublic function findByOptionalCategory(?int $categoryId): Collection\r\n{\r\n    if ($categoryId) {\r\n        return Product::where('category_id', $categoryId)->get();\r\n    }\r\n    \/\/ Wrong: this returns products WITH a category, not without one\r\n    return Product::where('category_id', null)->get(); \/\/ Should use whereNull()\r\n}\r\n\r\n\/\/ CORRECT \u2014 whitelist dynamic column names\r\npublic function getSortedProducts(string $sortColumn, string $sortDirection): Collection\r\n{\r\n    $allowedColumns    = ['name', 'price', 'created_at', 'stock'];\r\n    $allowedDirections = ['asc', 'desc'];\r\n\r\n    $column    = in_array($sortColumn, $allowedColumns) ? $sortColumn : 'created_at';\r\n    $direction = in_array(strtolower($sortDirection), $allowedDirections) ? $sortDirection : 'desc';\r\n\r\n    return Product::orderBy($column, $direction)->get();\r\n}\r\n\r\n\/\/ CORRECT \u2014 proper NULL handling\r\npublic function findByOptionalCategory(?int $categoryId): Collection\r\n{\r\n    return Product::when(\r\n        $categoryId !== null,\r\n        fn($q) => $q->where('category_id', $categoryId),\r\n        fn($q) => $q->whereNull('category_id')\r\n    )->get();\r\n}\r\n\r\n\/\/ CORRECT \u2014 handle NULL in comparisons\r\n\/\/ WRONG: WHERE column = NULL (always returns no rows in SQL)\r\n\/\/ RIGHT: WHERE column IS NULL\r\nProduct::whereNull('deleted_at')->get();      \/\/ Correct\r\nProduct::where('deleted_at', null)->get();    \/\/ Also correct \u2014 Eloquent handles this\r\n\/\/ DB::select(\"WHERE deleted_at = NULL\") \u2014 WRONG \u2014 never matches in SQL<\/code><\/pre>\n
Query Problem 2 \u2014 Transaction Misuse<\/h2>\n
AI-generated code frequently omits transactions for operations that must be atomic \u2014 multiple related database changes that must all succeed or all fail together.<\/p>\n
\/\/ PROBLEMATIC \u2014 related operations without transaction\r\npublic function transferStock(int $fromProductId, int $toProductId, int $quantity): void\r\n{\r\n    $fromProduct = Product::findOrFail($fromProductId);\r\n    $toProduct   = Product::findOrFail($toProductId);\r\n\r\n    \/\/ If application crashes between these two updates \u2014 stock is lost\r\n    $fromProduct->decrement('stock', $quantity);\r\n    $toProduct->increment('stock', $quantity);\r\n\r\n    StockTransfer::create([\r\n        'from_product_id' => $fromProductId,\r\n        'to_product_id'   => $toProductId,\r\n        'quantity'        => $quantity,\r\n    ]);\r\n}\r\n\r\n\/\/ CORRECT \u2014 atomic transaction\r\npublic function transferStock(int $fromProductId, int $toProductId, int $quantity): void\r\n{\r\n    DB::transaction(function () use ($fromProductId, $toProductId, $quantity) {\r\n        \/\/ Lock rows for update \u2014 prevents race conditions\r\n        $fromProduct = Product::lockForUpdate()->findOrFail($fromProductId);\r\n        $toProduct   = Product::lockForUpdate()->findOrFail($toProductId);\r\n\r\n        if ($fromProduct->stock < $quantity) {\r\n            throw new InsufficientStockException(\r\n                \"Insufficient stock in product {$fromProductId}\"\r\n            );\r\n        }\r\n\r\n        $fromProduct->decrement('stock', $quantity);\r\n        $toProduct->increment('stock', $quantity);\r\n\r\n        StockTransfer::create([\r\n            'from_product_id' => $fromProductId,\r\n            'to_product_id'   => $toProductId,\r\n            'quantity'        => $quantity,\r\n            'transferred_by'  => auth()->id(),\r\n        ]);\r\n        \/\/ If any of the above fails \u2014 entire transaction rolls back\r\n    });\r\n}\r\n\r\n\/\/ CORRECT \u2014 transaction with manual rollback control\r\npublic function processPayment(Order $order, array $paymentData): Payment\r\n{\r\n    DB::beginTransaction();\r\n\r\n    try {\r\n        $payment = Payment::create([\r\n            'order_id' => $order->id,\r\n            'amount'   => $order->total,\r\n            'status'   => 'processing',\r\n        ]);\r\n\r\n        $gatewayResponse = $this->gateway->charge($paymentData, $order->total);\r\n\r\n        $payment->update([\r\n            'status'             => 'completed',\r\n            'gateway_payment_id' => $gatewayResponse->id,\r\n        ]);\r\n\r\n        $order->update(['status' => 'confirmed', 'payment_id' => $payment->id]);\r\n\r\n        DB::commit();\r\n        return $payment;\r\n\r\n    } catch (PaymentGatewayException $e) {\r\n        DB::rollBack();\r\n        throw $e;\r\n    } catch (\\Throwable $e) {\r\n        DB::rollBack();\r\n        Log::error('Payment processing failed', ['order_id' => $order->id, 'error' => $e->getMessage()]);\r\n        throw $e;\r\n    }\r\n}<\/code><\/pre>\n
Audit check \u2014 find multi-step operations without transactions:<\/strong><\/p>\n
# Find methods that do multiple DB writes without transaction wrapper\r\ngrep -rn \"->save()\\|->update(\\|->create(\\|->delete()\" app\/Services --include=\"*.php\" -l | \\\r\nwhile read file; do\r\n    count=$(grep -c \"->save()\\|->update(\\|->create(\\|->delete()\" \"$file\")\r\n    if [ \"$count\" -gt 2 ]; then\r\n        transactions=$(grep -c \"DB::transaction\\|beginTransaction\" \"$file\")\r\n        if [ \"$transactions\" -eq 0 ]; then\r\n            echo \"WARNING: $file has $count DB writes but no transactions\"\r\n        fi\r\n    fi\r\ndone<\/code><\/pre>\n
The Complete Database Audit Checklist<\/h2>\n
Data Types<\/strong><\/p>\n