# Audit-chain race condition fix Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Закрыть race condition в `audit_chain_hash()` trigger и восстановить целостность hash-цепи на партициях `activity_log_y2026_m05` (с id=599) и `balance_transactions_y2026_m05` (с id=462) для соответствия 152-ФЗ.

**Architecture:** Тригер `audit_chain_hash()` BEFORE INSERT читает `prev_log_hash` из последней строки партиции без блокировки. При параллельных INSERT'ах (вебхуки от поставщика обрабатываются queue worker'ами параллельно) несколько concurrent транзакций получают одинаковый prev_hash → ветвление цепи → валидатор находит mismatch.

Решение в два шага: (1) добавить advisory-lock per partition в функцию `audit_chain_hash()` — сериализует INSERT'ы в каждую партицию без блокирования параллельных операций в других партициях; (2) artisan-команда `audit:rebuild-chain` пересчитывает hash'и в указанной партиции с указанного id, временно отключая `audit_block_mutation` через `session_replication_role='replica'`.

**Tech Stack:** PostgreSQL 16 (pgcrypto), PHP 8.3 / Laravel 13, Pest 4, `pgsql_supplier` connection (BYPASSRLS) для chain rebuild.

**Контекст находки:** docs/superpowers/plans/2026-05-29-stage5-monitoring-checklist.md → day 1 → Finding 1. Полный data dump — GitHub Actions run `26616602381` artifact `investigate-day1-round3/investigate3.log`. Битые партиции:

- `activity_log_y2026_m05`: 6 mismatch, first id=599
- `balance_transactions_y2026_m05`: 6 mismatch, first id=462

Гипотеза race-condition'а подтверждается timestamp'ами: id 597-601 в `activity_log_y2026_m05` все имеют `created_at` в окне `2026-05-25 15:30:43-44 UTC` (5 INSERT'ов за 2 секунды от concurrent webhook handler'ов).

`last_success_at` валидатора `audit:verify-chains` = `2026-05-25 01:00:03 UTC` — до 25.05 ~15:30 цепь была sequential (мало вебхуков), потом пошёл массовый поток.

---

## Task 1: Воспроизводящий тест race condition

**Files:**

- Create: `app/tests/Feature/Audit/AuditChainRaceConditionTest.php`

- [ ] **Step 1: Failing test parallel INSERT**

```php
<?php

declare(strict_types=1);

namespace Tests\Feature\Audit;

use App\Models\ActivityLog;
use App\Models\Tenant;
use Illuminate\Support\Facades\DB;
use Tests\TestCase;

it('audit_chain_hash trigger preserves sequential chain under concurrent INSERTs', function (): void {
    $tenant = Tenant::factory()->create();
    DB::statement("SET LOCAL app.current_tenant_id = " . $tenant->id);

    // Snapshot existing rows count for tenant
    $startCount = DB::table('activity_log')
        ->where('tenant_id', $tenant->id)
        ->count();

    // Spawn 5 concurrent transactions inserting into activity_log
    // (mimics 5 queue workers processing webhooks at the same instant)
    $pids = [];
    for ($i = 0; $i < 5; $i++) {
        $pid = pcntl_fork();
        if ($pid === 0) {
            // child process — own DB connection (forked from parent)
            DB::reconnect();
            DB::statement("SET LOCAL app.current_tenant_id = " . $tenant->id);
            DB::table('activity_log')->insert([
                'tenant_id' => $tenant->id,
                'event' => 'deal.created',
                'context' => json_encode(['worker' => $i]),
                'created_at' => now(),
            ]);
            exit(0);
        }
        $pids[] = $pid;
    }
    foreach ($pids as $pid) {
        pcntl_waitpid($pid, $status);
    }

    // Verify chain integrity — every row's log_hash must derive from prev row
    $rows = DB::table('activity_log')
        ->where('tenant_id', $tenant->id)
        ->orderBy('id')
        ->get(['id', 'log_hash', 'event']);

    expect($rows->count())->toBe($startCount + 5);

    // Walk chain — each row's log_hash should match hash(prev_log_hash || row data)
    $prevHash = null;
    foreach ($rows as $row) {
        if ($prevHash !== null) {
            // pseudo-check: rows after the first must reference the prev's hash
            // Real validation logic is in audit:verify-chains command
            expect($row->log_hash)->not->toBe($prevHash); // hashes are unique
        }
        $prevHash = $row->log_hash;
    }

    // Run validator command, it should find no mismatches in this partition
    $exitCode = $this->artisan('audit:verify-chains', [
        '--partition' => 'activity_log_' . now()->format('\\y\\Y_\\m\\m'),
    ])->run();

    expect($exitCode)->toBe(0);
})->skip(! function_exists('pcntl_fork'), 'pcntl required for race-condition test');
```

- [ ] **Step 2: Run failing test, expect MISMATCH detection**

Run from app root:

```
cd /var/www/liderra/app && php artisan test --filter='AuditChainRaceConditionTest' --stop-on-failure
```

Expected: FAIL — concurrent INSERT'ы создадут branches в hash chain → `audit:verify-chains` exit code != 0.

- [x] **Step 3: Commit failing test**

```
git add app/tests/Feature/Audit/AuditChainRaceConditionTest.php
git commit -m "test(audit): failing test for audit_chain_hash race condition (5 concurrent INSERTs in same partition cause chain branch)"
```

Commit: `06fbb238` — test(audit): failing test for audit_chain_hash race condition

---

## Task 2: Миграция — advisory lock в audit_chain_hash()

**Files:**

- Create: `app/database/migrations/2026_05_30_000001_add_advisory_lock_to_audit_chain_hash.php`

- [ ] **Step 1: Migration up()**

```php
<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement(<<<'SQL'
            CREATE OR REPLACE FUNCTION audit_chain_hash() RETURNS trigger AS $$
            DECLARE
                prev_hash bytea;
                lock_key bigint;
            BEGIN
                -- Lock key derived from table OID — serializes INSERTs to the
                -- same partition without blocking inserts to other partitions.
                lock_key := ('x' || lpad(to_hex(TG_RELID::int), 16, '0'))::bit(64)::bigint;
                PERFORM pg_advisory_xact_lock(lock_key);

                EXECUTE format(
                    'SELECT log_hash FROM %I ORDER BY id DESC LIMIT 1',
                    TG_TABLE_NAME
                ) INTO prev_hash;

                NEW.log_hash := digest(
                    COALESCE(prev_hash, '\x'::bytea) ||
                    convert_to(COALESCE(NEW.tenant_id::text, ''), 'UTF8') ||
                    convert_to(COALESCE(NEW.event, NEW.type, ''), 'UTF8') ||
                    convert_to(COALESCE(NEW.context::text, ''), 'UTF8') ||
                    convert_to(NEW.created_at::text, 'UTF8'),
                    'sha256'
                );

                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        SQL);
    }

    public function down(): void
    {
        // Revert to non-locking version (used for hash chain restore demo).
        DB::statement(<<<'SQL'
            CREATE OR REPLACE FUNCTION audit_chain_hash() RETURNS trigger AS $$
            DECLARE
                prev_hash bytea;
            BEGIN
                EXECUTE format(
                    'SELECT log_hash FROM %I ORDER BY id DESC LIMIT 1',
                    TG_TABLE_NAME
                ) INTO prev_hash;
                NEW.log_hash := digest(
                    COALESCE(prev_hash, '\x'::bytea) ||
                    convert_to(COALESCE(NEW.tenant_id::text, ''), 'UTF8') ||
                    convert_to(COALESCE(NEW.event, NEW.type, ''), 'UTF8') ||
                    convert_to(COALESCE(NEW.context::text, ''), 'UTF8') ||
                    convert_to(NEW.created_at::text, 'UTF8'),
                    'sha256'
                );
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        SQL);
    }
};
```

NB: до правки нужно проверить **реальную** формулу `audit_chain_hash` через `pg_get_functiondef('audit_chain_hash'::regproc)` — выше показан шаблон с правдоподобной формулой. Перед миграцией снять реальный текст и подставить в `up()` (добавив только `pg_advisory_xact_lock`).

- [ ] **Step 2: Local test after migration**

```
php artisan migrate --path=database/migrations/2026_05_30_000001_add_advisory_lock_to_audit_chain_hash.php
php artisan test --filter='AuditChainRaceConditionTest'
```

Expected: PASS — advisory lock сериализует INSERT'ы → цепь sequential → validator passes.

- [x] **Step 3: Commit migration + green test**

```
git add app/database/migrations/2026_05_30_000001_add_advisory_lock_to_audit_chain_hash.php
git commit -m "fix(audit): add pg_advisory_xact_lock to audit_chain_hash trigger (closes race on concurrent INSERTs to same partition)"
```

Commit: `41fb0d94` — fix(audit): add pg_advisory_xact_lock to audit_chain_hash trigger

**NB (factual pre-deploy check, 29.05.2026):** testing DB already had the advisory lock function applied (recorded in migrations table but file was missing). Verbatim formula confirmed matches db/schema.sql:3107-3127: `digest(COALESCE(prev_hash, ''::bytea) || NEW::text::bytea, 'sha256')`. Migration `up()` uses this exact formula + adds advisory lock; `down()` restores verbatim original.

---

## Task 3: Artisan command `audit:rebuild-chain`

**Files:**

- Create: `app/app/Console/Commands/AuditRebuildChain.php`
- Test: `app/tests/Feature/Audit/AuditRebuildChainTest.php`

- [ ] **Step 1: Failing test**

```php
<?php

declare(strict_types=1);

namespace Tests\Feature\Audit;

use App\Models\Tenant;
use Illuminate\Support\Facades\DB;
use Tests\TestCase;

it('audit:rebuild-chain repairs broken hash chain from given id', function (): void {
    $tenant = Tenant::factory()->create();
    DB::statement("SET LOCAL app.current_tenant_id = " . $tenant->id);

    // Insert 3 valid rows via normal flow (trigger writes correct hashes)
    DB::table('activity_log')->insert([
        ['tenant_id' => $tenant->id, 'event' => 'a', 'created_at' => now()],
        ['tenant_id' => $tenant->id, 'event' => 'b', 'created_at' => now()->addMicrosecond()],
        ['tenant_id' => $tenant->id, 'event' => 'c', 'created_at' => now()->addMicroseconds(2)],
    ]);

    // Manually corrupt row 2's hash (simulating race-condition branch)
    DB::statement("SET session_replication_role = 'replica'");
    DB::table('activity_log')
        ->where('event', 'b')
        ->update(['log_hash' => "\\xdead"]);
    DB::statement("SET session_replication_role = 'origin'");

    // Verify validator finds the mismatch
    $exitBefore = $this->artisan('audit:verify-chains')->run();
    expect($exitBefore)->not->toBe(0);

    // Run rebuild from row 'b' onwards
    $partition = 'activity_log_' . now()->format('\\y\\Y_\\m\\m');
    $fromId = DB::table('activity_log')->where('event', 'b')->value('id');

    $this->artisan('audit:rebuild-chain', [
        '--partition' => $partition,
        '--from-id' => $fromId,
    ])->assertExitCode(0);

    // Validator now passes
    $exitAfter = $this->artisan('audit:verify-chains')->run();
    expect($exitAfter)->toBe(0);
});
```

- [ ] **Step 2: Run failing test**

Expected: FAIL — `audit:rebuild-chain` command does not exist yet.

- [ ] **Step 3: Implement command**

```php
<?php

declare(strict_types=1);

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

final class AuditRebuildChain extends Command
{
    protected $signature = 'audit:rebuild-chain
        {--partition= : Имя партиции, например activity_log_y2026_m05}
        {--from-id= : ID с которого начать пересчёт (включительно)}
        {--dry-run : Показать сколько строк затронет, без UPDATE}';

    protected $description = 'Пересчитать hash-цепь в партиции аудит-таблицы начиная с указанного id (восстановление после race condition)';

    public function handle(): int
    {
        $partition = (string) $this->option('partition');
        $fromId = (int) $this->option('from-id');
        $dryRun = (bool) $this->option('dry-run');

        if ($partition === '' || $fromId <= 0) {
            $this->error('--partition и --from-id обязательны');
            return self::FAILURE;
        }

        // Сначала валидируем что partition — известная audit-таблица
        $allowed = [
            'activity_log', 'balance_transactions',
            'auth_log', 'tenant_operations_log',
        ];
        $isAuditPartition = false;
        foreach ($allowed as $parent) {
            if (str_starts_with($partition, $parent . '_')) {
                $isAuditPartition = true;
                break;
            }
        }
        if (! $isAuditPartition) {
            $this->error("Partition '{$partition}' не относится к аудит-таблицам");
            return self::FAILURE;
        }

        $count = DB::connection('pgsql_supplier')
            ->table($partition)
            ->where('id', '>=', $fromId)
            ->count();

        $this->info("Партиция: {$partition}");
        $this->info("Начальный id: {$fromId}");
        $this->info("Строк для пересчёта: {$count}");

        if ($dryRun) {
            $this->warn('--dry-run: UPDATE не выполнен');
            return self::SUCCESS;
        }

        if (! $this->confirm("Пересчитать hash-цепь {$count} строк? Это меняет log_hash в проде.", false)) {
            $this->warn('Отменено');
            return self::FAILURE;
        }

        DB::connection('pgsql_supplier')->transaction(function () use ($partition, $fromId): void {
            // Отключаем audit_block_mutation триггер на время пересчёта
            DB::connection('pgsql_supplier')->statement("SET LOCAL session_replication_role = 'replica'");

            // Берём prev_hash до from_id
            $prevHash = DB::connection('pgsql_supplier')
                ->table($partition)
                ->where('id', '<', $fromId)
                ->orderByDesc('id')
                ->value('log_hash');

            $rows = DB::connection('pgsql_supplier')
                ->table($partition)
                ->where('id', '>=', $fromId)
                ->orderBy('id')
                ->get();

            foreach ($rows as $row) {
                // Пересчёт по той же формуле что в audit_chain_hash()
                $newHash = $this->computeChainHash($prevHash, $row, $partition);
                DB::connection('pgsql_supplier')
                    ->table($partition)
                    ->where('id', $row->id)
                    ->update(['log_hash' => $newHash]);
                $prevHash = $newHash;
            }
        });

        $this->info("Готово. Запустите audit:verify-chains для проверки.");
        return self::SUCCESS;
    }

    private function computeChainHash(?string $prevHash, object $row, string $partition): string
    {
        $parent = preg_replace('/_y\\d{4}_m\\d{2}$/', '', $partition);
        $eventField = $parent === 'balance_transactions' ? 'type' : 'event';
        $event = $row->{$eventField} ?? '';

        $payload = ($prevHash ?? '')
            . (string) ($row->tenant_id ?? '')
            . (string) $event
            . (string) ($row->context ?? '')
            . (string) $row->created_at;

        return hash('sha256', $payload, true);
    }
}
```

- [ ] **Step 4: Run test, expect PASS**

Expected: PASS — manual corruption detected by verify-chains, then rebuild repairs it, then verify-chains passes.

- [x] **Step 5: Commit**

```
git add app/app/Console/Commands/AuditRebuildChain.php app/tests/Feature/Audit/AuditRebuildChainTest.php
git commit -m "feat(audit): add audit:rebuild-chain command for race-condition recovery (replays sha256 chain in given partition from given id, uses pgsql_supplier BYPASSRLS connection + session_replication_role=replica to bypass audit_block_mutation trigger)"
```

Commit: `7081f2a7` — feat(audit): add audit:rebuild-chain command for race-condition recovery

**Tests (all GREEN, run from app/):**
- `audit:rebuild-chain repairs broken hash chain from given id in activity_log` — PASS
- `audit:rebuild-chain works for balance_transactions partition` — PASS
- `audit:rebuild-chain --dry-run does not modify hashes` — PASS
- `audit:rebuild-chain rejects unknown partition names` — PASS
- `audit_chain_hash trigger preserves sequential chain under concurrent INSERTs` — SKIP (no pcntl on Windows)
- `audit_chain_hash holds pg_advisory_xact_lock on the partition OID during INSERT` — PASS

**Implementation note:** COLUMN_CONFIG mirrors TABLE_CONFIG in VerifyAuditChains.php exactly. Hash formula is server-side PostgreSQL via RETURNING log_hash (avoids PHP bytea serialization issue). PostgreSQL PDO returns BYTEA as PHP stream resource — handled via bytesToHex().

---

## Task 4: Деплой миграции + rebuild на проде

**Pre-flight на dev:** убедиться что миграция Task 2 локально применена, Pest тесты Task 1 + Task 3 зелёные.

✅ **Dev pre-flight выполнен (29.05.2026):**
- Миграция `2026_05_30_000001_add_advisory_lock_to_audit_chain_hash` применена на `liderra_testing`
- `pg_get_functiondef('audit_chain_hash')` подтверждает `pg_advisory_xact_lock` + verbatim hash formula
- Tests: 5/5 pass + 1 skip (pcntl, Windows) — `AuditChainRaceConditionTest` + `AuditRebuildChainTest`
- Commits: `06fbb238` (test) → `41fb0d94` (migration) → `7081f2a7` (command+test) на `worktree-agent-acf422b86772ab536`

- [x] **Step 1: Снять реальный текст функции `audit_chain_hash` на проде**

⚠️ Выполнить перед деплоем — убедиться что прод-функция совпадает с migration template.
Через stage5-investigate-day1.yml или artisan-run SQL:
```sql
SELECT pg_get_functiondef('audit_chain_hash'::regproc);
```
Ожидаемый результат: НЕТ `pg_advisory_xact_lock` (race condition всё ещё активен), формула = `digest(COALESCE(prev_hash, ''::bytea) || NEW::text::bytea, 'sha256')`. Если формула отличается — обновить migration `up()` ДО деплоя.

- [ ] **Step 2: Merge worktree branch в main + деплой**

```bash
# Merge worktree branch commits into main
git checkout main
git merge worktree-agent-acf422b86772ab536 --no-ff -m "merge(audit): advisory lock fix + rebuild command from agent worktree"

# Deploy via GitHub Actions
gh workflow run deploy.yml --ref main
```

Это применит миграцию Task 2 на проде (advisory lock в `audit_chain_hash`).

- [ ] **Step 3: Backfill партиций**

⚠️ Требует расширения artisan-run whitelist — добавить `audit:rebuild-chain` в MUTATING_RE pattern в `.github/workflows/artisan-run.yml`.

После расширения whitelist:
```bash
gh workflow run artisan-run.yml --field command="audit:rebuild-chain --partition=activity_log_y2026_m05 --from-id=599 --force"
gh workflow run artisan-run.yml --field command="audit:rebuild-chain --partition=balance_transactions_y2026_m05 --from-id=462 --force"
```

Note: команда использует `--force` чтобы пропустить интерактивное подтверждение (CI-режим).

- [ ] **Step 4: Проверка**

```bash
gh workflow run artisan-run.yml --field command="audit:verify-chains"
```

Expected: 0 mismatch в обоих партициях, exit 0.

- [ ] **Step 5: Reset consecutive_failures на проде**

```sql
UPDATE scheduler_heartbeats
SET consecutive_failures = 0, last_success_at = NOW()
WHERE command_name = 'audit:verify-chains';
```

Через sql-runner workflow или ssh к боевому серверу.

- [x] **Step 6: Commit doc update (этот коммит)**

```
git add docs/superpowers/plans/2026-05-29-audit-chain-race-fix.md
git commit -m "docs(audit): mark Tasks 1-3 complete, add prod deploy instructions for Task 4"
```

Обновить `memory/project_audit_journaling_closure.md` — добавить «race condition найден и закрыт, ждёт деплоя».

---

## Self-review

**Spec coverage (контекст Finding 1):**

- ✅ Понять root-cause race condition → Task 1 (reproducing test)
- ✅ Починить trigger → Task 2 (advisory lock migration)
- ✅ Восстановить битые цепи → Task 3 (audit:rebuild-chain command)
- ✅ Деплой + rebuild на проде → Task 4
- ⚠️ Известен gap: реальная формула `audit_chain_hash` на проде неизвестна — Task 4 Step 1 закрывает.

**Risks/edge cases:**

- Advisory lock сериализует INSERT'ы в одной партиции — это снижает throughput. Для audit-таблиц это приемлемо (low write rate относительно бизнес-таблиц).
- `audit:rebuild-chain` использует `session_replication_role='replica'` — это **сессионный** SET, не глобальный. Не влияет на другие транзакции.
- Если миграция в Task 2 применена, а concurrent INSERT'ы продолжаются — новые INSERT'ы получат корректный prev_hash через lock. Перед запуском `audit:rebuild-chain` достаточно дождаться завершения текущих транзакций.

**Что НЕ покрыто (out of scope):**

- Auth_log_y2026_m05 / tenant_operations_log_y2026_m05 — НЕ в списке битых из round 1. Если позже найдутся — применить тот же `audit:rebuild-chain`.
- Hash-формула может зависеть от других колонок (помимо tenant_id/event/context/created_at). Task 4 Step 1 это закроет — реальный pg_get_functiondef vs шаблон Task 2.
