# RLS Gap Audit — Cron Commands & Queued Jobs

**Date:** 2026-05-23  
**Scope:** Static analysis of all cron-scheduled commands and queued jobs  
**Project:** Лидерра CRM (Laravel 13 / PostgreSQL 16)  
**Auditor:** Claude Code (Phase A — discovery only, read-only, no code changes)

---

## Background

The portal uses PostgreSQL Row-Level Security (RLS). The policies use `current_setting('app.current_tenant_id')` to filter rows per tenant. On **DEV** the DB user is `postgres` (superuser, BYPASSRLS), so missing-GUC bugs are hidden. On **PRODUCTION** the role is `crm_app_user` / `liderra` (no BYPASSRLS), so any code that touches an RLS-protected table from a context where `app.current_tenant_id` is NOT set (cron commands, queued jobs outside an HTTP request) **fails** with:

```
ERROR: unrecognized configuration parameter "app.current_tenant_id"
```

or silently returns empty/wrong rows (if the policy uses `missing_ok = true`, which this codebase does NOT — it uses bare `current_setting()`).

The correct pattern for SaaS-admin/cron scope is to use the BYPASSRLS connection:

```php
DB::connection('pgsql_supplier')->table('...')   // role crm_supplier_worker, BYPASSRLS
```

Precedent: `IncidentsWatchFailures.php` was hotfixed 22.05.2026 this exact way.

---

## Section 1 — RLS-Protected Table Inventory

Tables with RLS policies referencing `current_setting('app.current_tenant_id')` (from `db/schema.sql`):

| Table | Notes |
|---|---|
| `users` | |
| `projects` | |
| `deals` | partitioned table |
| `reminders` | |
| `report_jobs` | |
| `pd_processing_log` | |
| `activity_log` | |
| `balance_transactions` | |
| `failed_webhook_jobs` | |
| `rejected_deals_log` | |
| `import_log` | |
| `in_app_notifications` | |
| `lead_charges` | also has `FORCE ROW LEVEL SECURITY` — even superuser subject |
| `webhook_dedup_keys` | |
| `outbound_webhook_subscriptions` | |
| `outbound_webhook_deliveries` | |
| `tenant_status_overrides` | |
| `tenant_custom_domains` | |
| `api_keys` | |
| `push_subscriptions` | |
| `comment_templates` | |
| `deal_tags` | |
| `import_unknown_statuses` | |
| `webhook_log` | |
| `tariff_subscriptions` | |
| `saas_invoices` | |
| `saas_upd_documents` | |
| `saas_transactions` | |
| `refund_requests` | |
| `tenant_consents` | |
| `project_limit_adjustments` | |
| `impersonation_tokens` | |
| `tenant_operations_log` | |
| `project_suppliers` | |
| `auth_log` | |

**Total: 35 RLS-protected tables.**

Tables explicitly confirmed **NOT** protected by RLS (safe to access on default connection from cron):

| Table | Notes |
|---|---|
| `supplier_leads` | no RLS |
| `supplier_projects` | no RLS |
| `system_settings` | no RLS |
| `saas_admin_users` | no RLS |
| `incidents_log` | no RLS |
| `supplier_csv_reconcile_log` | no RLS |
| `project_supplier_links` | no RLS |
| `supplier_sync_log` | no RLS |
| `tenants` | no RLS |

---

## Section 2 — Cron Commands and Queued Jobs Inventory

### Scheduled via `routes/console.php`

| Command / Job | Schedule | File |
|---|---|---|
| `projects:reset-delivered-today` | Daily 00:00 MSK | `ResetDeliveredTodayCommand.php` |
| `projects:reset-monthly` | Monthly 1st 00:00 MSK | `ResetMonthlyCountersCommand.php` |
| `partitions:create-months` | Daily | `PartitionsCreateMonths.php` |
| `RefreshSupplierSessionJob` | Hourly + daily 17:45 | `Supplier/RefreshSupplierSessionJob.php` |
| `SyncSupplierProjectsJob` | Daily 18:00 MSK | `Supplier/SyncSupplierProjectsJob.php` |
| `CleanupInactiveSupplierProjectsJob` | Daily 02:00 MSK | `Supplier/CleanupInactiveSupplierProjectsJob.php` |
| `supplier:retry-failed` | Hourly | `RetryFailedSupplierJobsCommand.php` |
| `CsvReconcileJob` | Every 30 min | `Supplier/CsvReconcileJob.php` |
| `incidents:watch-failures` | Every 10 min | `IncidentsWatchFailures.php` |

### Not currently scheduled (exist in codebase, run ad-hoc or via Windows Task Scheduler separately)

| Command | File |
|---|---|
| `reminders:dispatch-due` | `RemindersDispatchDue.php` |
| `reports:cleanup-expired` | `ReportsCleanupExpired.php` |
| `supplier:check-webhook-secret` | `CheckSupplierWebhookSecretCommand.php` |
| `supplier:import-projects` | `ImportSupplierProjectsCommand.php` |
| `supplier:session:refresh` | `SupplierSessionRefreshCommand.php` |

### Queued jobs (dispatched from HTTP / other jobs)

| Job file | Dispatched from |
|---|---|
| `GenerateReportJob.php` | `ReportJobController` (HTTP) |
| `ImportLeadsJob.php` | `ImportController` (HTTP) |
| `ProcessWebhookJob.php` | `WebhookController` (HTTP) |
| `RouteSupplierLeadJob.php` | `CsvReconcileJob`, `ProcessWebhookJob` |
| `SyncSupplierProjectJob.php` | `SyncSupplierProjectsJob` |

---

## Section 3 — Findings

### 3.1 Summary

| Verdict | Count |
|---|---|
| ❌ GAP | 4 |
| ⚠️ AMBIGUOUS | 0 |
| ✅ SAFE | 14 |
| ➖ N/A (no RLS tables touched) | 2 |
| **Total analyzed** | **20** |

*(10 command files + 10 job files)*

---

### 3.2 Full Audit Matrix

| File | DB tables touched | Connection used | Verdict | Notes |
|---|---|---|---|---|
| **COMMANDS** | | | | |
| `ResetDeliveredTodayCommand.php` | `projects` (UPDATE) | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | Explicit `DB::connection('pgsql_supplier')` |
| `ResetMonthlyCountersCommand.php` | `tenants`, `projects` (UPDATE) | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | Explicit `DB::connection('pgsql_supplier')` |
| `PartitionsCreateMonths.php` | `deals`, `supplier_lead_costs` partition DDL | default (pg_class + DDL) | ✅ SAFE | DDL (`CREATE TABLE PARTITION OF`) is not subject to RLS policy evaluation |
| `RemindersDispatchDue.php` | `reminders` (SELECT), `reminders` (UPDATE in loop) | **default** (no tenant set) | ❌ **GAP** | See finding RLS-01 |
| `ReportsCleanupExpired.php` | `report_jobs` (SELECT/UPDATE), `pd_processing_log` (INSERT) | **default** (no tenant set) | ❌ **GAP** | See finding RLS-02 |
| `IncidentsWatchFailures.php` | `failed_webhook_jobs`, `incidents_log`, `saas_admin_users` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | Hotfixed 22.05.2026 |
| `RetryFailedSupplierJobsCommand.php` | `failed_webhook_jobs` (SELECT/UPDATE) | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | Explicit `DB::connection('pgsql_supplier')` |
| `CheckSupplierWebhookSecretCommand.php` | `system_settings` | default | ➖ N/A | `system_settings` has no RLS; deploy-time only |
| `ImportSupplierProjectsCommand.php` | `users` (via `User::on('pgsql_supplier')`), `supplier_*` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | Uses `Model::on('pgsql_supplier')` |
| `SupplierSessionRefreshCommand.php` | none (dispatches job, no DB ops) | — | ➖ N/A | Only dispatches `RefreshSupplierSessionJob` |
| **JOBS** | | | | |
| `GenerateReportJob.php` | `report_jobs` (SELECT + multiple UPDATE) | **default** (no tenant set) | ❌ **GAP** | See finding RLS-03 |
| `ImportLeadsJob.php` | `import_log`, `supplier_leads`, `deals`, `balance_transactions` | default, wrapped in `DB::transaction` + `SET LOCAL app.current_tenant_id` | ✅ SAFE | Correct pattern: SET LOCAL inside transaction |
| `ProcessWebhookJob.php` | `webhook_dedup_keys`, `projects`, `supplier_leads`, `deals`, `balance_transactions` (handle); `failed_webhook_jobs` (failed()) | handle: `DB::transaction` + `SET LOCAL`; failed(): **default, no tenant** | ❌ **GAP** | See finding RLS-04 |
| `RouteSupplierLeadJob.php` | `supplier_leads`, `deals`, `balance_transactions`, `failed_webhook_jobs` | `pgsql_supplier` for failed(); `SET LOCAL` for deal creation | ✅ SAFE | `failed()` uses `DB::connection('pgsql_supplier')` |
| `SyncSupplierProjectJob.php` | `supplier_projects`, `supplier_sync_log`, `project_supplier_links` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | All ops via `DB::connection('pgsql_supplier')` |
| `Supplier/CleanupInactiveSupplierProjectsJob.php` | `supplier_projects`, `project_supplier_links` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | |
| `Supplier/CsvReconcileJob.php` | `supplier_csv_reconcile_log`, `supplier_leads` | `pgsql_supplier` for log; `supplier_leads` has no RLS | ✅ SAFE | |
| `Supplier/DeleteSupplierProjectJob.php` | `supplier_projects`, `project_supplier_links` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | |
| `Supplier/RefreshSupplierSessionJob.php` | none (Redis/Cache only) | — | ➖ N/A | No DB operations |
| `Supplier/SyncSupplierProjectsJob.php` | `supplier_projects`, `project_supplier_links` | `pgsql_supplier` (BYPASSRLS) | ✅ SAFE | |

---

### 3.3 Detailed Gap Findings

#### RLS-01 — `RemindersDispatchDue.php` → `reminders`

**Severity:** P1 (production crash on first run after deploy to `crm_app_user`)  
**File:** `app/app/Console/Commands/RemindersDispatchDue.php`  
**Trigger:** Windows Task Scheduler / cron, not in `routes/console.php` schedule

**Failing code (initial cross-tenant SELECT):**

```php
$pending = Reminder::query()
    ->where('is_sent', false)
    ->whereNull('completed_at')
    ->where('remind_at', '<=', $now)
    ->orderBy('remind_at')
    ->limit($limit)
    ->get();
```

The `Reminder` Eloquent model uses the default DB connection. On production (`crm_app_user`), executing any query on `reminders` when `app.current_tenant_id` GUC is not set throws:

```
ERROR: unrecognized configuration parameter "app.current_tenant_id"
```

Note: the per-tenant processing inside the loop correctly wraps individual operations in `DB::transaction()` with `SET LOCAL app.current_tenant_id = $tenantId`, but this does NOT protect the initial bulk SELECT that runs before any tenant context is established.

---

#### RLS-02 — `ReportsCleanupExpired.php` → `report_jobs` + `pd_processing_log`

**Severity:** P1 (production crash on first run)  
**File:** `app/app/Console/Commands/ReportsCleanupExpired.php`  
**Trigger:** Windows Task Scheduler / cron daily

**Sub-gap A — `report_jobs` SELECT:**

```php
$jobs = ReportJob::query()
    ->where('status', ReportJob::STATUS_DONE)
    ->whereNotNull('file_path')
    ->where('expires_at', '<', Carbon::now())
    ->get();
```

Uses default connection, `report_jobs` has RLS, no `app.current_tenant_id` set → crashes.

**Sub-gap B — `pd_processing_log` INSERT (via `PdAuditLogger`):**

```php
app(PdAuditLogger::class)->record(
    event: PdAuditEvent::REPORT_FILE_DELETED,
    ...
);
// PdAuditLogger::record() does:
DB::table('pd_processing_log')->insert([...]);
```

`PdAuditLogger` always uses the default DB connection. `pd_processing_log` has RLS. Called from cron without tenant context → crashes on INSERT.

The `->update(['status' => ReportJob::STATUS_DELETED])` inside the loop would also fail on production for the same reason (default connection, no GUC).

---

#### RLS-03 — `GenerateReportJob.php` → `report_jobs`

**Severity:** P1 (production crash on every report generation request)  
**File:** `app/app/Jobs/GenerateReportJob.php`  
**Trigger:** Dispatched from HTTP controller (`ReportJobController`) → processed by queue worker in separate process with fresh DB connection

**Failing code:**

```php
public function handle(...): void
{
    $job = ReportJob::query()->find($this->reportJobId);
    // ^ hits report_jobs on DEFAULT connection, no app.current_tenant_id set

    if (!$job) { return; }

    $job->update(['status' => ReportJob::STATUS_PROCESSING]);
    // ^ same issue

    // ... generates report ...

    $job->update([
        'status' => ReportJob::STATUS_DONE,
        'file_path' => ...,
        'expires_at' => ...,
    ]);
    // ^ same issue
}
```

The job constructor receives only `$reportJobId: int`. There is no `$tenantId` field and no `SET LOCAL app.current_tenant_id` anywhere in the file. The queue worker process starts with a fresh DB connection where the GUC is absent → first `ReportJob::query()->find()` throws on production.

---

#### RLS-04 — `ProcessWebhookJob::failed()` → `failed_webhook_jobs`

**Severity:** P1 (silently fails to log webhook failures on production; the original `handle()` failure is thus unrecorded, masking outages)  
**File:** `app/app/Jobs/ProcessWebhookJob.php`  
**Trigger:** Queue worker — called by Laravel when `handle()` exhausts retries

**Failing code (in `failed()` callback):**

```php
public function failed(Throwable $e): void
{
    DB::table('failed_webhook_jobs')->insert([
        'tenant_id' => $this->tenantId,
        'webhook_source' => $this->webhookSource,
        'payload' => json_encode($this->payload),
        'exception' => $e->getMessage(),
        'failed_at' => now(),
    ]);
}
```

The code uses `DB::table()` (default connection). A comment in the file suggests this was intentional to avoid RLS filtering, but on production `crm_app_user` is NOT BYPASSRLS — even `DB::table()` (raw query builder) goes through the same RLS policy that calls `current_setting('app.current_tenant_id')`. Without the GUC being set, PostgreSQL throws.

**Contrast with RouteSupplierLeadJob**, which correctly uses:

```php
DB::connection('pgsql_supplier')->table('failed_webhook_jobs')->insert([...]);
```

---

## Section 4 — Recommended Fixes

### Fix RLS-01: `RemindersDispatchDue.php`

Replace the initial Eloquent SELECT with a raw cross-tenant query via BYPASSRLS connection, then add `$tenantId` context to the per-reminder processing:

```php
// BEFORE (broken on prod):
$pending = Reminder::query()
    ->where('is_sent', false)
    ->whereNull('completed_at')
    ->where('remind_at', '<=', $now)
    ->limit($limit)
    ->get();

// AFTER (safe):
$pending = DB::connection('pgsql_supplier')
    ->table('reminders')
    ->where('is_sent', false)
    ->whereNull('completed_at')
    ->where('remind_at', '<=', $now)
    ->orderBy('remind_at')
    ->limit($limit)
    ->get(); // returns stdClass rows, not Eloquent models
```

The existing per-tenant loop structure (with `SET LOCAL` inside `DB::transaction()`) can remain largely intact; the loop variable becomes a plain object. Alternatively, add `tenant_id` to the SELECT and group by tenant before the loop.

---

### Fix RLS-02: `ReportsCleanupExpired.php`

**Sub-fix A — `report_jobs`:** Use `ReportJob::on('pgsql_supplier')` or raw BYPASSRLS query:

```php
// BEFORE:
$jobs = ReportJob::query()->where(...)->get();

// AFTER:
$jobs = DB::connection('pgsql_supplier')
    ->table('report_jobs')
    ->where('status', ReportJob::STATUS_DONE)
    ->whereNotNull('file_path')
    ->where('expires_at', '<', Carbon::now())
    ->get();
```

**Sub-fix B — `pd_processing_log`:** Extend `PdAuditLogger::record()` to accept an optional `$connection` parameter, defaulting to `'pgsql_supplier'` for cron callers:

```php
// Option 1: pass connection to PdAuditLogger
app(PdAuditLogger::class)->record(
    event: PdAuditEvent::REPORT_FILE_DELETED,
    connection: 'pgsql_supplier',
    ...
);

// Option 2 (simpler for cron): suppress pd_processing_log in cleanup cron
// (report file deletion is an operational action, not a user-facing PD event)
```

---

### Fix RLS-03: `GenerateReportJob.php`

Add `$tenantId` as a constructor parameter and wrap all `ReportJob` operations in a tenant-scoped transaction:

```php
public function __construct(
    private readonly int $reportJobId,
    private readonly int $tenantId,  // ADD THIS
) {}

public function handle(...): void
{
    DB::transaction(function () use (...) {
        DB::statement('SET LOCAL app.current_tenant_id = ' . $this->tenantId);

        $job = ReportJob::query()->find($this->reportJobId);
        if (!$job) { return; }

        $job->update(['status' => ReportJob::STATUS_PROCESSING]);
        // ... generate report ...
        $job->update(['status' => ReportJob::STATUS_DONE, ...]);
    });
}
```

The dispatch site (`ReportJobController`) must pass `$tenantId` when dispatching:

```php
// In ReportJobController:
GenerateReportJob::dispatch($reportJob->id, auth()->user()->tenant_id);
```

---

### Fix RLS-04: `ProcessWebhookJob::failed()`

Follow the `RouteSupplierLeadJob` precedent — use BYPASSRLS connection:

```php
public function failed(Throwable $e): void
{
    // BEFORE: DB::table('failed_webhook_jobs')->insert([...]);
    // AFTER:
    DB::connection('pgsql_supplier')
        ->table('failed_webhook_jobs')
        ->insert([
            'tenant_id' => $this->tenantId,
            'webhook_source' => $this->webhookSource,
            'payload' => json_encode($this->payload),
            'exception' => $e->getMessage(),
            'failed_at' => now(),
        ]);
}
```

---

## Section 5 — Risk Assessment

| Finding | Probability of prod crash | Impact | Priority |
|---|---|---|---|
| RLS-01 `RemindersDispatchDue` | HIGH — runs daily via Task Scheduler | Reminders never sent on prod | **P1** |
| RLS-02 `ReportsCleanupExpired` | HIGH — runs daily | Disk not cleaned; PD audit log broken | **P1** |
| RLS-03 `GenerateReportJob` | HIGH — every report request | All report downloads fail silently | **P1** |
| RLS-04 `ProcessWebhookJob::failed()` | HIGH — every webhook failure event | Webhook failures unlogged; `incidents:watch-failures` sees 0 failures; incidents masked | **P1** |

All four findings are P1. On production with `crm_app_user`, every single occurrence of these code paths will crash (RLS-01/02/03) or silently fail to write (RLS-04), with the crash cascading to undetectable outages.

---

## Appendix — Files Analyzed (20 total)

**Commands (10):**

1. `app/Console/Commands/ResetDeliveredTodayCommand.php` ✅
2. `app/Console/Commands/ResetMonthlyCountersCommand.php` ✅
3. `app/Console/Commands/PartitionsCreateMonths.php` ✅
4. `app/Console/Commands/RemindersDispatchDue.php` ❌ RLS-01
5. `app/Console/Commands/ReportsCleanupExpired.php` ❌ RLS-02
6. `app/Console/Commands/IncidentsWatchFailures.php` ✅
7. `app/Console/Commands/RetryFailedSupplierJobsCommand.php` ✅
8. `app/Console/Commands/CheckSupplierWebhookSecretCommand.php` ➖
9. `app/Console/Commands/ImportSupplierProjectsCommand.php` ✅
10. `app/Console/Commands/SupplierSessionRefreshCommand.php` ➖

**Jobs (10):**

1. `app/Jobs/GenerateReportJob.php` ❌ RLS-03
2. `app/Jobs/ImportLeadsJob.php` ✅
3. `app/Jobs/ProcessWebhookJob.php` ❌ RLS-04 (failed() method only)
4. `app/Jobs/RouteSupplierLeadJob.php` ✅
5. `app/Jobs/SyncSupplierProjectJob.php` ✅
6. `app/Jobs/Supplier/CleanupInactiveSupplierProjectsJob.php` ✅
7. `app/Jobs/Supplier/CsvReconcileJob.php` ✅
8. `app/Jobs/Supplier/DeleteSupplierProjectJob.php` ✅
9. `app/Jobs/Supplier/RefreshSupplierSessionJob.php` ➖
10. `app/Jobs/Supplier/SyncSupplierProjectsJob.php` ✅
