Auditing prod with real customer data without leaving a single row

I had a system already in production, with customer data flowing through it every day, and the owner asked me to audit the entire system, simulate real usage on every page, and remove every trace without damaging existing data. That sounds contradictory, because simulating usage means writing data.

There was no staging server with the same data as prod, and copying customer data to another machine for testing posed a bigger risk than the problem I was trying to solve. So I chose a third option: test on the live server itself, but wrap everything in a transaction and roll it back at the end, then count the rows in every table before and after as evidence that nothing was left behind.

The system name, domain, database name, and paths in this article are examples; the method and the pitfalls I encountered are real.

Why I did not use tinker

When most people hear "run Laravel code on a server," they immediately think of artisan tinker. I tried two approaches, and neither worked.

What actually worked was dropping tinker, writing a plain php script in the application root, and running it directly with php. That gave me unrestricted script length, full stack traces on failure, and a process that ended when the script ended.

# วางไฟล์ขึ้นไป รัน แล้วลบทิ้งทันทีในบรรทัดเดียว
scp -P 2233 audit-harness.php user@host:~/app/
ssh -p 2233 user@host '/usr/local/php82/bin/php ~/app/audit-harness.php; rm -f ~/app/audit-harness.php'

The missing line that breaks everything

This is where I spent the most time. Bootstrapping Laravel manually involves two lines everyone knows: the autoloader and bootstrap/app.php, but there is a third line most examples leave out.

<?php
require __DIR__.'/vendor/autoload.php';
$app = require_once __DIR__.'/bootstrap/app.php';

// บรรทัดนี้ขาดไม่ได้ — ถ้าไม่มี Eloquent จะเด้ง "Call to a member function connection() on null"
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();

That line is essential because bootstrap/app.php only assembles the container; it has not run any service providers yet. That means the database manager has not been bound into the container, so the first Model call tries to find a connection that is still null and fails with a message that misleadingly suggests an incorrect .env configuration, which is not the problem at all.

Count before, run, then count after

The core of this work is not the rollback, because rollback is just a command. The core is evidence that it actually rolled everything back, so I started by recording the row count of every table in the database before touching anything.

function snapshotCounts(): array {
    $db = DB::connection()->getDatabaseName();
    $tables = DB::select(
        'SELECT TABLE_NAME AS t FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?', [$db]
    );
    $out = [];
    foreach ($tables as $row) {
        // นับจริงด้วย COUNT(*) เท่านั้น — TABLE_ROWS ของ information_schema เป็นค่าประมาณ
        $out[$row->t] = (int) DB::table($row->t)->count();
    }
    ksort($out);
    return $out;
}

$before = snapshotCounts();

Once the work is done, count again and compare each table. Any difference means something was left behind.

$after = snapshotCounts();
$diff  = [];
foreach ($after as $t => $n) {
    $b = $before[$t] ?? 0;
    if ($n !== $b) { $diff[$t] = "$b -> $n"; }
}

if ($diff) {
    echo "RESULT: INCOMPLETE\n";
    foreach ($diff as $t => $d) { echo "PROBLEM: ตาราง $t เปลี่ยน $d\n"; }
    exit(1);
}
echo "RESULT: OK (ทุกตารางเท่าเดิม)\n";

I made it finish with exit(1) on failure so that if I later hook it into automation, nobody accidentally reads a failure as a pass. The verdict must come from the single RESULT line, not from whether the script finished without an error.

Simulating real saves — calling controllers directly

The point I insisted on was calling the same code path a real user follows, rather than writing my own Model::create() call and claiming I had tested it. What I wanted to know was whether validation passed, document number generation worked, and the automatic accounting tied to events ran in full; those things live in the controller and service layers, not in the model.

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\PatientController;

DB::beginTransaction();
try {
    $req = Request::create('/patients', 'POST', [
        'first_name' => 'ทดสอบ',
        'last_name'  => 'ระบบ',
        'phone'      => '0800000000',
        'birth_date' => '2530-01-01',
    ]);
    // controller หลายตัวเรียก auth()->user() ข้างใน ต้องผูกผู้ใช้ให้ก่อน
    $req->setUserResolver(fn () => $tester);

    $controller = new PatientController();
    $resp = $controller->store($req);

    echo "store() คืนสถานะ ".$resp->getStatusCode()."\n";
    echo "แถวใหม่ในตาราง patients: ".DB::table('patients')->count()."\n";
} catch (\Throwable $e) {
    echo "PROBLEM: ".get_class($e).' — '.$e->getMessage()."\n";
} finally {
    DB::rollBack();          // ต้องอยู่ใน finally เสมอ ไม่ว่าจะพังหรือไม่
}

finally matters a great deal. If I simply put rollBack() at the end of the try block and the code throws an exception halfway through, the transaction stays open until the connection closes, which on a live server means row locks remain held and may block real users trying to work.

Simulating page visits — looping through every GET route

The second part was checking that every web page still opened after the latest code changes. This did not need a transaction because GET should not write anything anyway, but I still counted rows before and after, because if any route quietly wrote something, I wanted to know.

The trick that made this practical was filling URL parameters with ids that actually existed in the database instead of blindly inserting 1, because the row with id 1 had often been deleted long ago. Otherwise, I would get 404 responses across the board even though the system was not actually broken.

use Illuminate\Contracts\Http\Kernel as HttpKernel;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Route;

$kernel = $app->make(HttpKernel::class);
Auth::loginUsingId($testerId);

$pass = $fail = 0;
foreach (Route::getRoutes() as $route) {
    if (!in_array('GET', $route->methods(), true)) continue;
    $uri = $route->uri();
    if (str_starts_with($uri, 'api/') || str_contains($uri, '{')) {
        $uri = fillParams($uri);          // เติม id จริงจาก DB
        if ($uri === null) continue;      // ไม่มีข้อมูลให้ทดสอบ ข้ามไป
    }

    $resp = $kernel->handle(Request::create('/'.$uri, 'GET'));
    $code = $resp->getStatusCode();
    if ($code === 200) { $pass++; }
    else { $fail++; echo "PROBLEM: /$uri -> $code\n"; }
}
echo "GET route ผ่าน $pass / ล้มเหลว $fail\n";

On my latest run, all 49 routes returned 200, which is a more meaningful number than manually clicking through ten pages and declaring everything fine.

What rollback cannot undo

I need to be clear here, or readers might assume this method is completely safe. A transaction only controls what is inside a database that supports transactions. Many other things a controller touches along the way cannot be taken back once they have happened.

The first two are easy to guard against by switching drivers when running the script. This is what I did.

// ตัดขาไม่ให้ของหลุดออกนอกเครื่องระหว่างทดสอบ
config([
    'mail.default'       => 'log',      // เมลลง log ไม่ส่งจริง
    'queue.default'      => 'sync',     // job ทำทันทีในเส้นเดียวกัน จะได้อยู่ใต้ transaction
    'filesystems.default'=> 'local',
]);

The last item needs checking before starting, because even a single MyISAM table immediately invalidates the evidence that "nothing was left behind."

-- ต้องได้ผลลัพธ์ว่าง ถ้ามีแถวโผล่มา แปลว่าตารางนั้นไม่รองรับ transaction
SELECT TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
   AND ENGINE <> 'InnoDB';

What to do before touching the live server

However confident I am, I always do these two things first. Together, they take less than five minutes.

1. Take a database dump first, with both the date and the reason in the filename so I can tell later why I made it.
2. Compare the entire file tree between my machine and the live server using md5sum to make sure I am actually testing the same code version.
3. Check that every table uses InnoDB.
4. Run the script, read the RESULT line, and immediately delete the file in the same command.

# 1) dump ก่อน ชื่อไฟล์บอกเหตุผลด้วย
mysqldump --single-transaction --quick --no-tablespaces appdb \
  | gzip > ~/backups/appdb-20260914-before-audit.sql.gz

# 2) เทียบ tree สองฝั่ง (ควรต่างแค่ไฟล์ที่ตั้งใจแก้กับ bootstrap/cache)
find app config routes resources -type f -name '*.php' -exec md5sum {} \; | sort -k2 > /tmp/prod.md5
# ฝั่งเครื่องเราทำแบบเดียวกันแล้ว diff กัน

The second item matters more than it might seem. I once failed to track down a bug on my own machine because two files differed between the live server and my local copy: someone had edited them directly on the server months earlier and forgotten to mention it. Comparing md5 across the entire tree takes only a few seconds and settles that question conclusively.

Finishing cleanly

My final rule is that a script like this must not remain on the live server for even a minute after use. It is a php file sitting in the application root, which under some configurations may be directly accessible from the web, and it has full database access. Leaving it behind creates a vulnerability of my own making.

So I always put the delete command on the same line as the run command, separated by a semicolon rather than &&, because I want it deleted even if the script fails halfway through. Then I finish by verifying that it really is gone.

ssh -p 2233 user@host '
  cd ~/app
  /usr/local/php82/bin/php audit-harness.php; rc=$?
  rm -f audit-harness.php
  ls -la audit-harness.php 2>&1 | head -n1
  exit $rc
'

This method works well for systems too tightly coupled to isolate for testing, and for servers whose datasets cannot be moved elsewhere for privacy reasons. Personally, I do not see it as a replacement for the automated test suite that should exist. It is a tool for answering whether the real system still works today, which is a different question from the one unit tests answer. If your constraints differ, adapt it as needed. If I missed an explanation or made a typo, I will come back and fix it later.