# Supabase `scan_details` queue + worker

**Standalone deployment:** For a single-file worker plus bundled `Reports/` templates (copy/rsync to a dedicated server with systemd), see [`worker_security/README.md`](../worker_security/README.md).

## Apply the schema

1. Open Supabase Dashboard → **SQL Editor**.
2. Paste and run the contents of [`supabase/migrations/20260501120000_scan_details.sql`](../supabase/migrations/20260501120000_scan_details.sql).
3. Also run [`supabase/migrations/20260525130000_scan_details_report_path.sql`](../supabase/migrations/20260525130000_scan_details_report_path.sql) (adds `report_path` for saved report bundles).

Or, with Supabase CLI linked to a project: `supabase db push`.

## Table `public.scan_details`

| Column | Purpose |
|--------|---------|
| `requester_email` | Recipient for `SCAN_REPORT_EMAIL_TO` when sending reports |
| `requester_name` | Optional name for email greeting (`Hi {name},`); falls back to `Hi there,` |
| `target_url` | Passed to `scan.py --target` |
| `scan_action` | Stored for your own routing (e.g. `full`); not used by the worker yet |
| `started_at` | Set on claim |
| `completed_at` | Set when scan JSON is saved to `result` |
| `status` | `pending` → `running` (claimed) → `completed` (email sent) or `failed` (scan never persisted) |
| `result` | Full scan JSON (same shape as `update.json`) |
| `email_sent_at` | Set when email step succeeds |
| `report_path` | Relative path to saved report bundle, e.g. `Reports/generated/{id}/` (see `manifest.json` inside) |
| `error_message` | Last error (scan or **email/SMTP**, includes traceback); cleared on success. No separate errors table. |

**Email retry:** if the scan succeeded but email fails, the worker sets `status` back to **`pending`** and keeps **`result`**. The next claim picks the row and skips `scan.py`, only runs the report/email step.

**Ops alerts:** on scan/email failures, timeouts, tool warnings (`warnings` in scan JSON), or worker loop errors, the worker emails **`jaco@overdrive.co.za`** (override with `SCAN_OPS_ALERT_EMAIL`) with the stage, script path, scan row ID, target URL, and error text.

**RLS:** enabled with **no** policies for `anon` / `authenticated`. Only the **`service_role`** key should be used by the worker (it bypasses RLS). Do not expose the service key in a browser app.

## RPC `claim_next_scan_detail()`

- In Python (`supabase-py`), call with an explicit empty params dict: `client.rpc("claim_next_scan_detail", {}).execute()`.
- Returns **0 or 1** row, locked with `FOR UPDATE SKIP LOCKED`, and updates it to `running`.
- Eligible rows: `email_sent_at` is null, `status` is not `failed`, and either `pending` or **stale** `running` (`updated_at` older than 1 hour) for crash recovery.

## Environment

**Supabase (required for worker):**

- `SUPABASE_URL` — project URL
- `SUPABASE_SERVICE_ROLE_KEY` — **service_role** secret (never commit)

**Email / reports** — same as `generate_reports_from_update.py` (see its docstring). Typical keys: `SMTP_HOST`, `SMTP_PORT`, `SMTP_USER`, `SMTP_PASSWORD`, `SMTP_FROM`, or `SCAN_REPORT_*` overrides.

**Env files:** [`load_env.py`](../load_env.py) loads, in order: repo-root `.env`, `smtp.env`, `supabase.env`, and (for `worker_security`) `worker_security/.env`. File values **override** existing process env for SMTP and Supabase keys — so editing `.env` works even if an old `SMTP_HOST` was exported in the shell. Optional: `SCAN_SHIELD_ENV_FILE=/path/to/.env`. On startup, workers log `[env] SMTP host=…` (password never logged). **Restart** the worker after changing `.env`.

Copy [`supabase.env.example`](../supabase.env.example) to `supabase.env` and fill keys if you split Supabase credentials from `.env`.

## Run the worker

```bash
cd /path/to/scan_shield_v3
pip install -r requirements.txt
python scripts/scan_queue_worker.py
```

- Poll interval: **`SCAN_QUEUE_POLL_SEC`** (default **30** seconds).
- One row per loop iteration; after each job (success, failure, or retry) the worker sleeps again before the next claim.

## Enqueue a test row (SQL)

```sql
insert into public.scan_details (requester_email, target_url, scan_action)
values ('you@example.com', 'https://example.com/', 'full');
```

Use the Dashboard with a user that can insert (e.g. temporary policy), or insert via a local script using the service role.