c-relay-pg/plans/php_admin_page_plan.md

# PHP Admin Page for Caching Service

## Problem

The existing Nostr-based admin API (`caching_follows_status` kind-23456
command) encrypts its JSON response with NIP-44, which has a hard 64KB
plaintext limit. With 679+ followed pubkeys, the response exceeds this
limit by ~2-4×, causing `Encryption result code: -15`
(`NOSTR_ERROR_NIP44_BUFFER_TOO_SMALL`) and the UI shows
"Failed to load".

Rather than paginating the NIP-44 admin command (complex, still limited),
we build a **separate traditional admin page** using PHP + nginx that
queries PostgreSQL directly. This bypasses the NIP-44 limit entirely,
scales to thousands of follows, and runs in parallel with the existing
Nostr admin API (no changes to the relay binary needed).

## Architecture

```mermaid
flowchart LR
    Browser[Admin Browser] -->|HTTPS| Nginx
    Nginx -->|/admin/ *.php| PHP_FPM[PHP-FPM]
    Nginx -->|/ and /api| Relay[C-Relay-PG :8888]
    PHP_FPM -->|PDO| PostgreSQL[(PostgreSQL crelay)]
    Relay -->|libpq| PostgreSQL
```

- **nginx** fronts everything on 443 (existing SSL setup).
  - `/` and `/api` → proxy to C-Relay-PG (existing, unchanged)
  - `/admin/` → PHP-FPM (new)
- **PHP-FPM** runs as a pool (e.g. `www` or a dedicated `relay-admin`
  pool). PHP files live in `/opt/c-relay-pg/admin/` (or similar).
- **PDO** connects to PostgreSQL using the same `crelay` credentials
  the relay uses. Read-only queries for display; write queries only for
  config updates (with auth guard).
- **No relay restart needed** — this is purely nginx + PHP-FPM
  configuration + static files.

## Tech Stack

- **PHP 8.x** with PDO PostgreSQL extension (`php-pgsql`)
- **PHP-FPM** managed by systemd (or the existing nginx PHP-FPM pool)
- **nginx** `location /admin/` block with `fastcgi_pass` to PHP-FPM
- **Frontend**: vanilla HTML + JS (no build step), same visual style as
  the existing `api/index.html` admin page. Optionally use HTMX or
  Alpine.js for lightweight interactivity, but vanilla JS is sufficient.

## Authentication

The PHP admin page needs its own auth since it's not going through the
Nostr admin command path. Options (in order of simplicity):

1. **HTTP Basic Auth** (nginx-level) — simplest, sufficient for a
   single-admin relay. Add `auth_basic` to the `/admin/` location block
   with a `.htpasswd` file.
2. **Session-based login** — PHP login form, password hash in a config
   file, session cookie. More flexible but more code.
3. **Nostr login (NIP-07 extension)** — the page could verify a signed
   auth event from the admin's browser extension, matching against the
   `admin_pubkey` in the config table. Most aligned with the existing
   system but most complex.

**Recommendation:** Start with HTTP Basic Auth (option 1) for the MVP,
upgrade to Nostr login later if desired.

## Database Access

PHP connects via PDO with the same credentials the relay uses:
```
host=localhost port=5432 dbname=crelay user=crelay password=crelay
```

Store the connection string in a PHP config file
(`/opt/c-relay-pg/admin/config.php`) that is NOT web-accessible (outside
the document root or protected by nginx).

## Pages / Endpoints

### 1. Dashboard (`/admin/index.php`)
Overview cards:
- Service state, heartbeat age, config generation (from
  `caching_service_state`)
- Followed author count, selected relay count, connected relay count
- Events fetched, inbox inserts, inbox pending count
- Backfill progress: X/Y authors complete
- Active backfill target (pubkey + relay) from `caching_backfill_active`
- Error relays summary (count of rows with `consecutive_errors >= 3`)

### 2. Follows (`/admin/follows.php`)
The main table that was failing via NIP-44. Paginated server-side:
- Page size: 50 or 100 follows per page
- Columns: Name (from kind-0), npub, Root?, Events in DB, Backfill
  Complete, Relays (expandable), Last Seen
- JOIN with `events` table for kind-0 profile metadata (name,
  display_name, picture, nip05)
- JOIN with `caching_backfill_relay_progress` for per-relay status
- Filter: all / incomplete only / root only / error relays only
- Search by name or pubkey prefix
- Sort by: events_fetched desc, name, last_seen, backfill_complete

SQL (paginated):
```sql
SELECT fp.pubkey, fp.is_root, fp.backfill_complete, fp.events_fetched,
       fp.first_seen, fp.last_seen,
       e.content::json->>'name'        AS name,
       e.content::json->>'display_name' AS display_name,
       e.content::json->>'picture'     AS picture,
       e.content::json->>'nip05'       AS nip05,
       (SELECT COUNT(*) FROM events WHERE pubkey = fp.pubkey) AS total_events
FROM caching_followed_pubkeys fp
LEFT JOIN LATERAL (
    SELECT content FROM events
    WHERE pubkey = fp.pubkey AND kind = 0
    ORDER BY created_at DESC LIMIT 1
) e ON true
ORDER BY fp.is_root DESC, fp.events_fetched DESC
LIMIT $page_size OFFSET $offset;
```

### 3. Relay Progress (`/admin/relays.php`)
Per (author, relay) backfill status from
`caching_backfill_relay_progress`:
- Columns: Author (name + npub), Relay URL, Until Cursor, Complete,
  Events Fetched, Consecutive Errors, Last Status, Updated At
- Filter: incomplete only / error relays / complete / all
- Highlight rows with `consecutive_errors >= 3`
- Show `last_status` (now descriptive: `error: tls/ws handshake failed`,
  `eose`, `timeout`, etc.)

### 4. Config (`/admin/config.php`)
Read and edit caching config values from the `config` table:
- Read: `caching_root_npubs`, `caching_bootstrap_relays`,
  `caching_kinds`, `caching_admin_kinds`, all `caching_*` keys
- Edit: form to update values, bumps `caching_config_generation` on
  save (so the caching service hot-reloads)
- This replaces the "Apply Configuration" button for caching settings

### 5. Inbox (`/admin/inbox.php`)
Monitor the `caching_event_inbox` queue:
- Pending count by source_class (live / backfill / discovery)
- Oldest pending age
- Recent events preview (last 20, with kind + pubkey + source)
- This helps diagnose if the poller is keeping up

## File Structure

```
/opt/c-relay-pg/admin/           # PHP document root (or symlink)
├── config.php                   # DB connection config (NOT web-served)
├── index.php                    # Dashboard
├── follows.php                  # Followed pubkeys table (paginated)
├── relays.php                   # Per-relay backfill progress
├── config.php                   # Caching config editor
├── inbox.php                    # Inbox queue monitor
├── api/
│   ├── follows.php              # JSON endpoint for follows (AJAX)
│   ├── relays.php               # JSON endpoint for relay progress
│   ├── status.php               # JSON endpoint for dashboard stats
│   └── config.php               # JSON endpoint for config get/set
├── assets/
│   ├── style.css                # Shared CSS (match existing admin theme)
│   └── app.js                   # Shared JS (pagination, search, refresh)
└── .htpasswd                    # HTTP Basic Auth passwords
```

In the repo, these live under `admin/` (new top-level directory,
parallel to `api/`).

## nginx Configuration

Add to the existing `server` block in
[`examples/deployment/nginx-proxy/nginx.conf`](../examples/deployment/nginx-proxy/nginx.conf):

```nginx
# PHP admin page for caching service (direct PostgreSQL access)
location /admin/ {
    root /opt/c-relay-pg;
    index index.php;

    # HTTP Basic Auth
    auth_basic "Relay Admin";
    auth_basic_user_file /opt/c-relay-pg/admin/.htpasswd;

    # Pass .php files to PHP-FPM
    location ~ \.php$ {
        fastcgi_pass unix:/run/php/php8.2-fpm.sock;
        fastcgi_index index.php;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    }

    # Deny access to config.php (contains DB credentials)
    location = /admin/config.php {
        deny all;
    }
}
```

## Implementation Plan

### Step 1 — Server setup (one-time)
- Install PHP 8.x + PHP-FPM + `php-pgsql` on the relay server
- Create `/opt/c-relay-pg/admin/` directory
- Configure PHP-FPM pool (or use default `www` pool)
- Add the nginx `location /admin/` block
- Create `.htpasswd` with `htpasswd` / `openssl passwd`
- Reload nginx

### Step 2 — PHP config + DB connection helper
- `admin/lib/db.php` — PDO connection helper, read-only by default
- `admin/config.php` — connection string (protected from web access)
- Test: `php -r "echo 'ok';"` + simple `SELECT 1` query

### Step 3 — Dashboard page (`index.php`)
- Query `caching_service_state` for overview stats
- Query `caching_backfill_active` for active target
- Query `caching_backfill_relay_progress` for error summary
- Display as cards + summary table
- Auto-refresh every 15s via JS `setInterval` + `fetch()`

### Step 4 — Follows page (`follows.php`)
- Server-side pagination (page param in URL)
- JOIN with `events` for kind-0 profile metadata
- Sub-query for per-pubkey event count
- Expandable relay progress per follow (AJAX load on click)
- Search + filter controls
- This is the page that replaces the broken NIP-44 `caching_follows_status`

### Step 5 — Relay progress page (`relays.php`)
- Full `caching_backfill_relay_progress` table with pagination
- Filter by complete/incomplete/error
- Show `last_status` and `consecutive_errors` columns
- Color-code: green=eose, yellow=timeout, red=error, gray=complete

### Step 6 — Config editor page (`config.php` → `admin/config-edit.php`)
- Read all `caching_*` config keys
- Form to edit `caching_root_npubs`, `caching_bootstrap_relays`,
  `caching_kinds`, etc.
- On save: `UPDATE config SET value = $1 WHERE key = $2` + bump
  `caching_config_generation`
- Confirmation dialog before applying

### Step 7 — Inbox monitor page (`inbox.php`)
- Pending counts by source_class
- Oldest pending age
- Recent 20 events preview
- Poller stats (from relay log or a new stats table)

### Step 8 — Styling + polish
- Match the existing `api/index.css` dark theme
- Responsive layout (works on mobile)
- Auto-refresh for dashboard and follows pages

## Security Considerations

1. **HTTP Basic Auth** on `/admin/` — minimum barrier
2. **`config.php` denied** via nginx `location` block (contains DB password)
3. **PDO prepared statements** everywhere — no SQL injection
4. **Read-only DB user** for display pages (optional: create a
   `crelay_readonly` role with SELECT-only on caching tables; use the
   full `crelay` user only for the config editor)
5. **HTTPS only** — the existing nginx SSL config covers this
6. **No CORS needed** — same origin as the relay

## Out of Scope (for MVP)

- Nostr NIP-07 extension login (use HTTP Basic Auth first)
- Write operations beyond config editing (reset backfill, etc. — those
  stay on the Nostr admin API)
- Real-time WebSocket updates (use polling instead — 15s interval is
  sufficient for a monitoring dashboard)
- Multi-user / role-based access

## Files to Create

| File | Purpose |
|------|---------|
| `admin/config.php` | DB connection config (protected) |
| `admin/lib/db.php` | PDO connection helper |
| `admin/lib/helpers.php` | Shared helpers (pagination, formatting) |
| `admin/index.php` | Dashboard |
| `admin/follows.php` | Followed pubkeys table (paginated) |
| `admin/relays.php` | Per-relay backfill progress |
| `admin/config-edit.php` | Caching config editor |
| `admin/inbox.php` | Inbox queue monitor |
| `admin/api/status.php` | JSON: dashboard stats |
| `admin/api/follows.php` | JSON: paginated follows |
| `admin/api/relays.php` | JSON: relay progress |
| `admin/assets/style.css` | Shared CSS |
| `admin/assets/app.js` | Shared JS |
| `admin/.htpasswd` | HTTP Basic Auth passwords |
| `examples/deployment/nginx-proxy/nginx.conf` | Add `/admin/` location block |

## Deployment

1. Copy `admin/` to `/opt/c-relay-pg/admin/` on the server
2. Install `php8.2-fpm php8.2-pgsql` (or distro-equivalent)
3. Create `.htpasswd`: `htpasswd -c /opt/c-relay-pg/admin/.htpasswd admin`
4. Edit `admin/config.php` with the PostgreSQL connection string
5. Add the nginx `/admin/` location block, reload nginx
6. Visit `https://relay.yourdomain.com/admin/`

No relay restart required. The PHP admin page runs entirely in parallel
with the existing relay and Nostr admin API.