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