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.

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:

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

Response (valid):

{ "valid": true }

Response (invalid):

{ "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().

---

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:

   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):

   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) 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.

<!-- 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:

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)

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.