c-relay-pg/plans/cleanup_sql_box_plan.md

# Cleanup Page — Editable SQL Box with Live Validation

## Goal

Add a prominent, editable SQL box to the Event Cleanup page that:

1. **Shows the SQL query being formed** as the user changes the form filters
   (follows, kinds, date range, max events).
2. **Allows direct editing** of the SQL.
3. **Live-checks validity** as the user types (debounced).
4. **Border turns red** when the SQL is invalid, **black** when valid.
5. The **edited SQL drives Preview** (must be a `SELECT`).
6. **Execute Delete** extracts the `WHERE` clause from the validated SQL and
   wraps it in the existing safe `DELETE ... id IN (subquery)` template — so the
   destructive path stays fenced to the `events` table with `ORDER BY`/`LIMIT`.

```mermaid
flowchart LR
  F[Form filters] --> B[SQL box content]
  B -->|debounce 400ms| V[EXPLAIN validate]
  V -->|ok| BC[black border]
  V -->|err| RC[red border]
  B -->|Preview button| P[Run edited SELECT]
  B -->|Execute button| E[Extract WHERE -> safe DELETE template]
  E --> D[DELETE FROM events WHERE id IN subquery]
```

---

## 1. New Endpoint: `admin/api/cleanup_validate.php`

A tiny POST endpoint that runs `EXPLAIN` on the submitted SQL and reports
validity. `EXPLAIN` parses + plans without executing, so it's a true,
side-effect-free validity check.

**Request:**
```json
{ "sql": "SELECT COUNT(*) ... FROM events e WHERE 1=1 AND ..." }
```

**Response (valid):**
```json
{ "valid": true }
```

**Response (invalid):**
```json
{ "valid": false, "error": "syntax error at or near ..." }
```

Implementation notes:
- `require_once __DIR__ . '/../lib/helpers.php';` then `db()`.
- Reject non-`SELECT` SQL with `valid:false` (the box should only ever hold
  the preview SELECT; the DELETE is never typed by the user).
- Wrap `$pdo->query("EXPLAIN " . $sql)` in try/catch; on `PDOException`
  return `valid:false` + `$e->getMessage()`.
- Use [`json_response()`](admin/lib/helpers.php:118).

---

## 2. Refactor `admin/api/cleanup.php`

Add two new request shapes alongside the existing form-filter paths (keep
back-compat for saved-query execute via `cleanup_queries.php`).

### 2a. Preview from raw SQL (GET or POST)

Accept a `sql` parameter (the edited SELECT). Flow:

1. Validate it starts with `SELECT` (case-insensitive).
2. Run `EXPLAIN` first; if it fails, return `{ error }`.
3. Execute the SELECT. It must return columns `match_count` and
   `total_size_bytes` (same shape as today). To keep this robust, the
   generated SQL the box shows will always be the standard preview SELECT:
   ```sql
   SELECT COUNT(*) AS match_count,
          COALESCE(SUM(pg_column_size(event_json)), 0) AS total_size_bytes
   FROM events e
   WHERE 1=1
     <user conditions>
   ```
   The user can add conditions but the SELECT-list stays fixed by convention.
   We run the user's SQL as-is for the count/size row.
4. For the kind breakdown, re-use the same `WHERE` by extracting it from the
   user SQL (regex `/\bWHERE\b(.*)/is`, stop at end — there is no ORDER BY in
   the preview SELECT) and substituting into the breakdown template.
5. Return the same JSON shape as today (`match_count`, `total_size_bytes`,
   `total_size_human`, `avg_size_per_event`, `kinds_breakdown`, `sql_preview`).

### 2b. Execute from raw WHERE (POST)

Accept a `where` parameter (the extracted WHERE clause, **without** the
leading `WHERE` keyword) plus `max_events`.

Flow:
1. Sanitize: the where clause must only reference `events e` columns / the
   `caching_followed_pubkeys` subquery. Reject if it contains `;`, `--`,
   `/*`, `DELETE`, `UPDATE`, `INSERT`, `DROP`, `TRUNCATE`, `GRANT`, `REVOKE`
   (case-insensitive) — defense in depth.
2. Build the safe DELETE (same as today):
   ```sql
   DELETE FROM events e
   WHERE e.id IN (
       SELECT e2.id FROM events e2
       WHERE 1=1 AND <user where>
       ORDER BY e2.created_at ASC
       [LIMIT :max_events]
   )
   ```
3. Run a preview count first (same WHERE) so we can report `freed_bytes`
   proportionally, exactly like the current code.
4. Clear stats cache files, return `deleted_count`/`freed_bytes`/etc.

Keep the existing form-filter POST path working (saved-query execute uses
it via `cleanup_queries.php`).

---

## 3. UI Changes — `admin/index.php` cleanup section

Replace the collapsible SQL Preview `<details>` block
([`admin/index.php`](admin/index.php:539)) with a prominent editable box
**inside** the `cleanup-builder-group`, right above the action buttons so
the user sees the query as they build it.

```html
<!-- SQL box — live, editable, validated -->
<div class="form-group" id="cleanup-sql-box-group">
  <label for="cleanup-sql-box">SQL Query
    <span id="cleanup-sql-status" style="font-size:11px;margin-left:8px"></span>
  </label>
  <textarea id="cleanup-sql-box" rows="10" spellcheck="false"
    style="width:100%;font-family:monospace;font-size:12px;
           border:2px solid #000;border-radius:4px;padding:8px;
           white-space:pre;overflow:auto"></textarea>
  <div style="font-size:11px;color:var(--text-muted,#888);margin-top:2px">
    Edit the SELECT to refine your query. Border turns red if invalid.
    Execute uses your WHERE clause inside a safe DELETE template.
  </div>
</div>
```

- Border default `2px solid #000` (black = valid/unknown).
- `#cleanup-sql-status` shows "Valid", "Invalid: ...", or "" while typing.
- The old `cleanup-results-sql` `<details>` block is removed (the box
  replaces it). The results group keeps summary + breakdown.

---

## 4. JS Changes — `admin/assets/app.js`

### 4a. Rebuild SQL from filters (mirror of PHP `build_filters`)

Add `buildCleanupSql(filters)` that returns the preview SELECT string,
duplicating the PHP condition logic on the client:

```js
function buildCleanupSql(f) {
  let sql = "SELECT COUNT(*) AS match_count,\n"
          + "       COALESCE(SUM(pg_column_size(event_json)), 0) AS total_size_bytes\n"
          + "FROM events e\n"
          + "WHERE 1=1";
  const conds = [];
  if (f.kinds.length) conds.push("e.kind IN (" + f.kinds.join(",") + ")");
  if (f.from_date) {
    const cast = /^\d{4}-\d{2}-\d{2}$/.test(f.from_date) ? "::date" : "::timestamp";
    conds.push("e.created_at >= EXTRACT(EPOCH FROM '" + f.from_date + "'" + cast + ")::BIGINT");
  }
  if (f.to_date) { /* same pattern */ }
  if (f.follows_filter === 'follows')
    conds.push("e.pubkey IN (SELECT pubkey FROM caching_followed_pubkeys)");
  else if (f.follows_filter === 'non_follows')
    conds.push("e.pubkey NOT IN (SELECT pubkey FROM caching_followed_pubkeys)");
  if (conds.length) sql += "\n  AND " + conds.join("\n  AND ");
  return sql;
}
```

Note: client-built SQL uses literal values (no `:param` placeholders) since
this string is for display + EXPLAIN + direct execution. The PHP execute
path re-validates server-side.

### 4b. Live validation (debounced)

```js
let sqlValidateTimer = null;
function onCleanupSqlInput() {
  const box = document.getElementById('cleanup-sql-box');
  clearTimeout(sqlValidateTimer);
  document.getElementById('cleanup-sql-status').textContent = 'checking...';
  sqlValidateTimer = setTimeout(validateCleanupSql, 400);
}
async function validateCleanupSql() {
  const sql = document.getElementById('cleanup-sql-box').value.trim();
  const box = document.getElementById('cleanup-sql-box');
  const status = document.getElementById('cleanup-sql-status');
  if (!sql) { box.style.borderColor = '#000'; status.textContent = ''; return; }
  try {
    const res = await fetch('api/cleanup_validate.php', {
      method:'POST', headers:{'Content-Type':'application/json'},
      body: JSON.stringify({sql})
    });
    const d = await res.json();
    if (d.valid) {
      box.style.borderColor = '#000';
      status.textContent = 'Valid'; status.style.color = 'green';
    } else {
      box.style.borderColor = '#c0392b';
      status.textContent = 'Invalid: ' + (d.error||'').slice(0,120);
      status.style.color = '#c0392b';
    }
  } catch(e) {
    box.style.borderColor = '#c0392b';
    status.textContent = 'Validation request failed';
  }
}
```

Wire `onCleanupSqlInput` to the textarea's `input` event (in page init).

### 4c. Form filters regenerate the SQL box

Add `refreshCleanupSqlFromForm()` that calls `readCleanupFilters()` then
`buildCleanupSql()` and sets the textarea value, then triggers validation.
Attach `change`/`input` listeners to: follows radios, kinds input,
from/to date+time, max events. Also call it at the end of
`newCleanupQuery()` and `loadSavedCleanupQuery()`.

### 4d. Preview uses the edited SQL

Rewrite `previewCleanup()`:
- Read the SQL straight from `#cleanup-sql-box`.
- POST it to `api/cleanup.php` with `{action:'preview_sql', sql}` (or GET
  with the sql in the body — use POST to avoid URL length limits).
- Refuse if border is currently red (alert "Fix the SQL first").
- `renderCleanupResults()` stays the same; `sql_preview` echoes the box.

### 4e. Execute extracts WHERE

Rewrite `executeCleanup()`:
- Read SQL from the box, extract the WHERE clause:
  `const m = sql.match(/\bWHERE\b([\s\S]*)$/i); const where = m ? m[1].trim() : '';`
- POST to `api/cleanup.php` with `{action:'execute_where', where, max_events}`.
- Refuse if border is red.
- Confirmation dialog message uses the preview count (fetch preview first,
  same as today).

### 4f. Saved-query flows

`loadSavedCleanupQuery()` already populates the form; after it does, call
`refreshCleanupSqlFromForm()` so the box reflects the loaded query.
`runSavedCleanupPreview()` / `runSavedCleanupExecute()` continue to work —
they load the form then call `previewCleanup()` / the execute path, which
now read from the box.

---

## 5. Files Touched

| File | Change |
|------|--------|
| `admin/api/cleanup_validate.php` | **NEW** — EXPLAIN-based validator |
| `admin/api/cleanup.php` | Add `preview_sql` + `execute_where` paths |
| `admin/index.php` | Replace SQL preview `<details>` with editable textarea box |
| `admin/assets/app.js` | `buildCleanupSql`, debounced validation, form->box sync, Preview/Execute from box |

No DB schema changes. No changes to `cleanup_queries.php` (saved-query CRUD
unchanged; its `execute` action still uses the form-filter path, which
remains in `cleanup.php`).

---

## 6. Safety

- Preview only ever runs a `SELECT` (enforced server-side + the box is
  seeded with a SELECT).
- Execute never runs user SQL directly; it extracts the WHERE and injects
  it into the fixed `DELETE ... id IN (subquery)` template, with a
  denylist of dangerous keywords and comment markers.
- `EXPLAIN` validation has zero side effects.
- Existing destructive-path safety (subquery + ORDER BY + LIMIT) preserved.