Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
75.32%
119 / 158
33.33%
4 / 12
 CRAP
0.00%
0 / 1
QueryAuditor
75.32%
119 / 158
33.33%
4 / 12
 122.69
0.00%
0 / 1
 register
100.00%
6 / 6
100.00%
1 / 1
 1
 beforeExec
84.21%
32 / 38
0.00%
0 / 1
 9.32
 afterExec
91.67%
33 / 36
0.00%
0 / 1
 12.08
 handleInsert
0.00%
0 / 22
0.00%
0 / 1
 56
 writeRow
95.24%
20 / 21
0.00%
0 / 1
 9
 resolveUserId
75.00%
9 / 12
0.00%
0 / 1
 9.00
 parseTable
80.00%
4 / 5
0.00%
0 / 1
 5.20
 findWhereStart
66.67%
2 / 3
0.00%
0 / 1
 2.15
 idOf
100.00%
2 / 2
100.00%
1 / 1
 1
 keyFor
100.00%
1 / 1
100.00%
1 / 1
 1
 equal
75.00%
6 / 8
0.00%
0 / 1
 5.39
 scrub
100.00%
4 / 4
100.00%
1 / 1
 3
1<?php
2
3namespace App\Audit;
4
5use Illuminate\Database\Connection;
6use Illuminate\Database\Events\QueryExecuted;
7use Illuminate\Support\Facades\DB;
8
9/**
10 * Captures every INSERT/UPDATE/DELETE at the DB layer and writes a
11 * full old vs new snapshot to tbl_audit_logs.
12 *
13 * Hooks DB::beforeExecuting (to fetch pre-state) + DB::listen (to fetch
14 * post-state and persist). Works regardless of whether the write came from
15 * Eloquent save(), Model::where()->update(), DB::table()->update(), or raw SQL.
16 */
17class QueryAuditor
18{
19    /**
20     * Tables that should never be audited.
21     */
22    private const SKIP_TABLES = [
23        'tbl_audit_logs',
24        'tbl_user_activity',
25        'tbl_quotations_log',
26        'tbl_orders_update_logs',
27        'tbl_g3w_orders_update_logs',
28        'tbl_notification_logs',
29        'tbl_incentive_plan_parameters_log',
30        'tbl_follow_up_logs',
31        'tbl_itv_email_logs',
32        'tbl_g3w_resync_runs',
33        'tbl_sendgrid_webhook',
34        'tbl_resend_emails',
35        'migrations',
36        'sessions',
37        'cache',
38        'cache_locks',
39        'failed_jobs',
40        'jobs',
41        'job_batches',
42        'password_reset_tokens',
43        'personal_access_tokens',
44    ];
45
46    /**
47     * Sensitive field names redacted from snapshots.
48     */
49    private const REDACTED_FIELDS = [
50        'password',
51        'remember_token',
52        'api_token',
53        'token_expires_at',
54    ];
55
56    /**
57     * Re-entrancy guard. Our own SELECTs and INSERT into tbl_audit_logs
58     * trigger the same DB::listen hook — this prevents infinite recursion.
59     */
60    private bool $busy = false;
61
62    /**
63     * Pre-state captured in beforeExecuting, keyed by query hash. Cleared
64     * after the matching afterExec processes it.
65     */
66    private array $preState = [];
67
68    public function register(): void
69    {
70        DB::beforeExecuting(function (string $query, array $bindings, Connection $connection) {
71            $this->beforeExec($query, $bindings, $connection);
72        });
73
74        DB::listen(function (QueryExecuted $event) {
75            $this->afterExec($event);
76        });
77    }
78
79    private function beforeExec(string $query, array $bindings, Connection $connection): void
80    {
81        if ($this->busy) {
82            return;
83        }
84
85        $sql = ltrim($query);
86        $verb = strtolower(strtok($sql, " \t\n"));
87
88        if (! in_array($verb, ['update', 'delete'], true)) {
89            return;
90        }
91
92        $table = $this->parseTable($sql, $verb);
93        if (! $table || in_array($table, self::SKIP_TABLES, true)) {
94            return;
95        }
96
97        // Skip JOIN updates — too complex to safely refetch.
98        if (preg_match('/\bjoin\b/i', $sql)) {
99            return;
100        }
101
102        $whereStart = $this->findWhereStart($sql);
103        if ($whereStart === null) {
104            return; // No WHERE — refusing to refetch the whole table.
105        }
106
107        $whereClause = trim(substr($sql, $whereStart + 7));
108        $beforeWhere = substr($sql, 0, $whereStart);
109        $setBindingCount = substr_count($beforeWhere, '?');
110        $whereBindings = array_slice($bindings, $setBindingCount);
111
112        $this->busy = true;
113        try {
114            $oldRows = $connection->select(
115                "SELECT * FROM `{$table}` WHERE {$whereClause}",
116                $whereBindings,
117            );
118        } catch (\Throwable) {
119            $this->busy = false;
120
121            return;
122        }
123        $this->busy = false;
124
125        if (empty($oldRows)) {
126            return;
127        }
128
129        $key = $this->keyFor($query, $bindings);
130        $this->preState[$key] = [
131            'verb' => $verb,
132            'table' => $table,
133            'rows' => $oldRows,
134            'where' => $whereClause,
135            'where_bindings' => $whereBindings,
136            'connection' => $connection->getName(),
137        ];
138    }
139
140    private function afterExec(QueryExecuted $event): void
141    {
142        if ($this->busy) {
143            return;
144        }
145
146        $sql = ltrim($event->sql);
147        $verb = strtolower(strtok($sql, " \t\n"));
148
149        // INSERT auditing disabled (FIRE-982 follow-up, 2026-04-29):
150        //
151        // The previous implementation called `handleInsert()` from inside
152        // DB::listen, which fires synchronously after each INSERT. The
153        // audit row insert that this triggered overwrote the connection's
154        // per-connection LAST_INSERT_ID(), so Eloquent then read the
155        // audit_logs row's id when assigning `$model->id` — every
156        // `Model::create()` returned the wrong id (e.g. id=31 when the
157        // row was actually persisted at id=41712).
158        //
159        // The Manual-Create flow exposed it: getNumber() → stub created at
160        // 41706 but returned id=21 → updateQuotation/21 hit the
161        // "quotation_not_found" guard.
162        //
163        // MySQL has SELECT LAST_INSERT_ID(<value>) to overwrite the
164        // per-connection lastInsertId, but PDO::lastInsertId() resets to
165        // 0 after any non-INSERT statement, so the restoration trick
166        // doesn't survive the trip back through PDO. The clean options
167        // (deferred buffer / separate connection / Eloquent observer)
168        // are bigger refactors than warranted for the immediate bug.
169        //
170        // Skipping INSERT auditing leaves CREATE events untracked in
171        // tbl_audit_logs, but every newly-inserted row is its own audit
172        // trail in its source table. UPDATE / DELETE auditing — the
173        // mutation-of-existing-data case — is unaffected.
174        if ($verb === 'insert') {
175            return;
176        }
177
178        if (! in_array($verb, ['update', 'delete'], true)) {
179            return;
180        }
181
182        $key = $this->keyFor($event->sql, $event->bindings);
183        if (! isset($this->preState[$key])) {
184            return;
185        }
186
187        $pre = $this->preState[$key];
188        unset($this->preState[$key]);
189
190        if ($verb === 'delete') {
191            foreach ($pre['rows'] as $row) {
192                $this->writeRow($pre['table'], $this->idOf($row), 'deleted', (array) $row, null);
193            }
194
195            return;
196        }
197
198        // UPDATE → refetch new state for the same WHERE
199        $this->busy = true;
200        try {
201            $newRows = DB::connection($pre['connection'])->select(
202                "SELECT * FROM `{$pre['table']}` WHERE {$pre['where']}",
203                $pre['where_bindings'],
204            );
205        } catch (\Throwable) {
206            $this->busy = false;
207
208            return;
209        }
210        $this->busy = false;
211
212        $oldById = [];
213        foreach ($pre['rows'] as $row) {
214            $oldById[$this->idOf($row)] = (array) $row;
215        }
216
217        foreach ($newRows as $newRow) {
218            $newArr = (array) $newRow;
219            $id = $this->idOf($newRow);
220            $oldArr = $oldById[$id] ?? null;
221
222            if ($oldArr !== null && $this->equal($oldArr, $newArr)) {
223                continue;
224            }
225
226            $this->writeRow($pre['table'], $id, 'updated', $oldArr, $newArr);
227        }
228    }
229
230    private function handleInsert(string $sql, array $bindings, Connection $connection): void
231    {
232        if (! preg_match('/^insert\s+(?:ignore\s+)?into\s+`?([^\s`(]+)`?/i', $sql, $m)) {
233            return;
234        }
235        $table = $m[1];
236
237        if (in_array($table, self::SKIP_TABLES, true)) {
238            return;
239        }
240
241        $this->busy = true;
242        try {
243            $lastId = $connection->getPdo()->lastInsertId();
244            if (! $lastId) {
245                $this->busy = false;
246
247                return;
248            }
249
250            $newRow = $connection->table($table)->where('id', $lastId)->first();
251            if (! $newRow) {
252                $this->busy = false;
253
254                return;
255            }
256
257            $this->busy = false;
258            $this->writeRow($table, $lastId, 'created', null, (array) $newRow);
259
260            // After our audit INSERT, MySQL's per-connection LAST_INSERT_ID()
261            // points at the audit row's id. The Eloquent driver that triggered
262            // *this* original INSERT is about to read LAST_INSERT_ID() to set
263            // `$model->id` — without restoring, every `Model::create()` ends
264            // up with the audit log's id instead of the real one. Concretely
265            // observed: TblQuotations::create() returned id=26 while the row
266            // was actually persisted at id=41709.
267            //
268            // SELECT LAST_INSERT_ID(<value>) is the documented MySQL knob to
269            // overwrite the per-connection lastInsertId without altering any
270            // table.
271            try {
272                $this->busy = true;
273                $connection->statement('SELECT LAST_INSERT_ID(?)', [(int) $lastId]);
274            } catch (\Throwable) {
275                // best-effort
276            } finally {
277                $this->busy = false;
278            }
279        } catch (\Throwable) {
280            $this->busy = false;
281        }
282    }
283
284    private function writeRow(string $table, mixed $id, string $event, ?array $oldData, ?array $newData): void
285    {
286        if ($oldData !== null) {
287            $oldData = $this->scrub($oldData);
288        }
289        if ($newData !== null) {
290            $newData = $this->scrub($newData);
291        }
292
293        $request = function_exists('request') ? request() : null;
294        $userId = $this->resolveUserId($request);
295
296        $this->busy = true;
297        try {
298            DB::table('tbl_audit_logs')->insert([
299                'auditable_type' => $table,
300                'auditable_id' => is_numeric($id) ? (int) $id : 0,
301                'event' => $event,
302                'old_data' => $oldData !== null ? json_encode($oldData) : null,
303                'new_data' => $newData !== null ? json_encode($newData) : null,
304                'user_id' => $userId,
305                'ip_address' => $request?->ip(),
306                'url' => $request?->fullUrl() ? mb_substr($request->fullUrl(), 0, 500) : null,
307                'created_at' => now(),
308                'updated_at' => now(),
309            ]);
310        } catch (\Throwable) {
311            // Never break the host request because of audit failure.
312        } finally {
313            $this->busy = false;
314        }
315    }
316
317    /**
318     * Resolve the authenticated user id for Titan's auth flow:
319     *   1. AuthenticateWithToken middleware sets it via $request->setUserResolver()
320     *      and also writes 'backend-user-id' header (server-derived, trusted).
321     *   2. Some controllers fall back to the client-supplied 'User-ID' header.
322     */
323    private function resolveUserId($request): ?int
324    {
325        if (! $request) {
326            return null;
327        }
328
329        $user = $request->user();
330        if ($user && $user->getKey()) {
331            return (int) $user->getKey();
332        }
333
334        $backend = $request->header('backend-user-id');
335        if ($backend && is_numeric($backend)) {
336            return (int) $backend;
337        }
338
339        $client = $request->header('User-ID');
340        if ($client && is_numeric($client)) {
341            return (int) $client;
342        }
343
344        return null;
345    }
346
347    private function parseTable(string $sql, string $verb): ?string
348    {
349        if ($verb === 'update' && preg_match('/^update\s+`?([A-Za-z0-9_]+)`?/i', $sql, $m)) {
350            return $m[1];
351        }
352        if ($verb === 'delete' && preg_match('/^delete\s+from\s+`?([A-Za-z0-9_]+)`?/i', $sql, $m)) {
353            return $m[1];
354        }
355
356        return null;
357    }
358
359    private function findWhereStart(string $sql): ?int
360    {
361        // Crude but covers the patterns Laravel's query builder generates.
362        if (preg_match('/\swhere\s/i', $sql, $m, PREG_OFFSET_CAPTURE)) {
363            return $m[0][1];
364        }
365
366        return null;
367    }
368
369    private function idOf(mixed $row): mixed
370    {
371        $arr = (array) $row;
372
373        return $arr['id'] ?? null;
374    }
375
376    private function keyFor(string $sql, array $bindings): string
377    {
378        return md5($sql.'|'.serialize($bindings));
379    }
380
381    private function equal(array $a, array $b): bool
382    {
383        if (count($a) !== count($b)) {
384            return false;
385        }
386        foreach ($a as $k => $v) {
387            if (! array_key_exists($k, $b)) {
388                return false;
389            }
390            if ((string) $v !== (string) $b[$k]) {
391                return false;
392            }
393        }
394
395        return true;
396    }
397
398    private function scrub(array $data): array
399    {
400        foreach (self::REDACTED_FIELDS as $field) {
401            if (array_key_exists($field, $data)) {
402                $data[$field] = '[REDACTED]';
403            }
404        }
405
406        return $data;
407    }
408}