c-relay-pg/admin/api/caching.php

<?php
/** admin2/api/caching.php — Caching service status + follows + reset actions. */
require_once __DIR__ . '/../lib/helpers.php';
$pdo = db();

// --- POST actions: reset backfill (all or per-user) ---
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $input = json_decode(file_get_contents('php://input'), true) ?: [];
    $action = $input['action'] ?? '';
    $pubkey = $input['pubkey'] ?? '';

    if ($action === 'reset_all') {
        // Reset backfill progress for ALL followed authors, then bump config
        // generation so the running caching service hot-reloads and re-drains.
        try {
            $pdo->beginTransaction();
            $pdo->exec("UPDATE caching_followed_pubkeys
                        SET until_cursor = 0, backfill_complete = FALSE,
                            events_fetched = 0,
                            updated_at = EXTRACT(EPOCH FROM NOW())::BIGINT");
            // Also reset per-relay progress rows
            $pdo->exec("UPDATE caching_backfill_relay_progress
                        SET complete = FALSE, events_fetched = 0,
                            until_cursor = 0, last_status = 'reset',
                            consecutive_errors = 0,
                            updated_at = EXTRACT(EPOCH FROM NOW())::BIGINT");
            // Bump config generation to trigger hot-reload
            $pdo->exec("UPDATE config SET value = (COALESCE(value::int, 0) + 1)::text
                        WHERE key = 'caching_config_generation'");
            $pdo->commit();
            json_response(['ok' => true, 'message' => 'All backfill progress reset. Caching service will re-drain.']);
        } catch (PDOException $e) {
            $pdo->rollBack();
            json_response(['ok' => false, 'error' => $e->getMessage()], 500);
        }
    } elseif ($action === 'reset_user' && $pubkey) {
        // Reset backfill progress for a single followed author.
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare("UPDATE caching_followed_pubkeys
                                   SET until_cursor = 0, backfill_complete = FALSE,
                                       events_fetched = 0,
                                       updated_at = EXTRACT(EPOCH FROM NOW())::BIGINT
                                   WHERE pubkey = ?");
            $stmt->execute([$pubkey]);
            // Also reset per-relay progress for this author
            $stmt2 = $pdo->prepare("UPDATE caching_backfill_relay_progress
                                    SET complete = FALSE, events_fetched = 0,
                                        until_cursor = 0, last_status = 'reset',
                                        consecutive_errors = 0,
                                        updated_at = EXTRACT(EPOCH FROM NOW())::BIGINT
                                    WHERE author_pubkey = ?");
            $stmt2->execute([$pubkey]);
            // Bump config generation to trigger hot-reload
            $pdo->exec("UPDATE config SET value = (COALESCE(value::int, 0) + 1)::text
                        WHERE key = 'caching_config_generation'");
            $pdo->commit();
            json_response(['ok' => true, 'message' => "Backfill reset for pubkey. Caching service will re-fetch."]);
        } catch (PDOException $e) {
            $pdo->rollBack();
            json_response(['ok' => false, 'error' => $e->getMessage()], 500);
        }
    } else {
        json_response(['ok' => false, 'error' => 'Unknown action'], 400);
    }
    exit;
}

// Caching config values (for toggle controls). The external daemon is
// controlled by live/backfill; the inbox poller remains a main-relay setting.
$config = [];
try {
    $cfg_rows = $pdo->query("SELECT key, value FROM config WHERE key IN ('caching_inbox_enabled','caching_live_enabled','caching_backfill_enabled')")->fetchAll();
    foreach ($cfg_rows as $r) { $config[$r['key']] = $r['value']; }
} catch (PDOException $e) {}

// Service state
$state = [];
try { $state = $pdo->query("SELECT * FROM caching_service_state WHERE id = 1")->fetch() ?: []; } catch (PDOException $e) {}

// Active target
$active = ['pubkey' => '', 'relay' => ''];
try { $active = $pdo->query("SELECT active_pubkey AS pubkey, active_relay AS relay FROM caching_backfill_active WHERE id = 1")->fetch() ?: $active; } catch (PDOException $e) {}

// Inbox
$inbox = ['pending' => 0, 'live' => 0, 'backfill' => 0];
try {
    $inbox = $pdo->query("SELECT COUNT(*) AS pending, COUNT(*) FILTER (WHERE source_class='live') AS live, COUNT(*) FILTER (WHERE source_class='backfill') AS backfill FROM caching_event_inbox")->fetch() ?: $inbox;
} catch (PDOException $e) {}

// Upstream relay status (per-relay connection state). The unified
// caching_relays table is authoritative; the old table is only a migration
// fallback so the Backfill page cannot display stale legacy statuses.
$upstreamRelays = [];
try {
    $upstreamRelays = $pdo->query("
        SELECT relay_url, status_code, status_text, updated_at
        FROM caching_relays
        ORDER BY relay_url
    ")->fetchAll();
} catch (PDOException $e) {
    try {
        $upstreamRelays = $pdo->query("
            SELECT relay_url, status_code, status_text, updated_at
            FROM caching_upstream_relays
            ORDER BY relay_url
        ")->fetchAll();
    } catch (PDOException $ignored) {}
}

// Follows (paginated, with names from profiles cache + per-relay progress)
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 50;
$offset = ($page - 1) * $perPage;

$follows = [];
$totalFollows = 0;
try {
    $totalFollows = (int)$pdo->query("SELECT COUNT(*) FROM caching_followed_pubkeys")->fetchColumn();
} catch (PDOException $e) {}

try {
    $follows = $pdo->query("
        SELECT fp.pubkey, fp.is_root, fp.backfill_complete, fp.events_fetched,
               fp.last_event_at,
               p.name, p.display_name,
               (SELECT count(*) FROM events WHERE pubkey = fp.pubkey) AS total_events,
               (SELECT count(*) FROM caching_backfill_relay_progress WHERE author_pubkey = fp.pubkey) AS relay_count,
               (SELECT count(*) FROM caching_backfill_relay_progress WHERE author_pubkey = fp.pubkey AND complete = false) AS relay_incomplete
        FROM caching_followed_pubkeys fp
        LEFT JOIN profiles p ON p.pubkey = fp.pubkey
        ORDER BY fp.is_root DESC, fp.events_fetched DESC
        LIMIT $perPage OFFSET $offset
    ")->fetchAll();
    // Fetch per-relay progress for all followed pubkeys in one query
    $relayProgress = [];
    try {
        $rpRows = $pdo->query("
            SELECT author_pubkey, relay_url, complete, events_fetched, until_cursor, last_status, updated_at
            FROM caching_backfill_relay_progress
            ORDER BY author_pubkey, events_fetched DESC
        ")->fetchAll();
        foreach ($rpRows as $rp) {
            $relayProgress[$rp['author_pubkey']][] = $rp;
        }
    } catch (PDOException $e) {}
    foreach ($follows as &$f) {
        $f['name'] = profile_display_name($f);
        $f['npub'] = function_exists('hex_to_npub') ? hex_to_npub($f['pubkey']) : substr($f['pubkey'], 0, 20);
        $f['relays'] = $relayProgress[$f['pubkey']] ?? [];
    }
} catch (PDOException $e) {}

json_response([
    'state' => $state,
    'active' => $active,
    'inbox' => $inbox,
    'follows' => $follows,
    'config' => $config,
    'upstreamRelays' => $upstreamRelays,
    'totalFollows' => $totalFollows,
    'page' => $page,
    'perPage' => $perPage,
]);