Rate Limiting and API Key Management for YouTube Data API

CREATE TABLE api_keys (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    key_val  TEXT NOT NULL UNIQUE,
    provider TEXT NOT NULL DEFAULT 'youtube',
    quota_used   INTEGER NOT NULL DEFAULT 0,
    quota_limit  INTEGER NOT NULL DEFAULT 10000,
    quota_reset  TEXT,           -- ISO 8601 UTC timestamp of next reset
    is_active    INTEGER NOT NULL DEFAULT 1,
    error_count  INTEGER NOT NULL DEFAULT 0,
    last_error   TEXT,
    created_at   TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_api_keys_active ON api_keys(provider, is_active, quota_used);

<?php

class QuotaManager
{
    private PDO $db;
    private ?array $activeKey = null;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }

    /**
     * Get the key with most remaining quota.
     * Returns null if all keys are exhausted.
     */
    public function getActiveKey(): ?array
    {
        if ($this->activeKey !== null) {
            return $this->activeKey;
        }

        $stmt = $this->db->prepare(<<<SQL
            SELECT *
            FROM api_keys
            WHERE provider = 'youtube'
              AND is_active = 1
              AND (quota_used < quota_limit)
              AND (
                  quota_reset IS NULL
                  OR quota_reset < datetime('now')
                  OR quota_used < quota_limit
              )
            ORDER BY (quota_limit - quota_used) DESC
            LIMIT 1
        SQL);
        $stmt->execute();

        $this->activeKey = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        return $this->activeKey;
    }

    public function consume(string $keyVal, int $units): void
    {
        $this->db->prepare(<<<SQL
            UPDATE api_keys
            SET quota_used = quota_used + ?,
                quota_reset = COALESCE(
                    quota_reset,
                    datetime('now', '+1 day', 'start of day')
                )
            WHERE key_val = ?
        SQL)->execute([$units, $keyVal]);

        // Bust the in-memory cache so next call re-fetches
        $this->activeKey = null;
    }

    public function markError(string $keyVal, string $error): void
    {
        $this->db->prepare(<<<SQL
            UPDATE api_keys
            SET error_count = error_count + 1,
                last_error  = ?,
                is_active   = CASE WHEN error_count >= 5 THEN 0 ELSE is_active END
            WHERE key_val = ?
        SQL)->execute([$error, $keyVal]);

        $this->activeKey = null;
    }

    public function canFetch(int $unitsNeeded = 1): bool
    {
        $key = $this->getActiveKey();
        if ($key === null) {
            return false;
        }
        return ($key['quota_limit'] - $key['quota_used']) >= $unitsNeeded;
    }

    public function resetDailyQuotas(): void
    {
        // Called at midnight UTC by cron
        $this->db->exec(<<<SQL
            UPDATE api_keys
            SET quota_used = 0,
                error_count = 0,
                is_active = 1,
                quota_reset = NULL
            WHERE quota_reset < datetime('now')
        SQL);
    }
}

<?php

class YouTubeApi
{
    private QuotaManager $quota;
    private int $retryDelay = 2; // seconds

    public function __construct(QuotaManager $quota)
    {
        $this->quota = $quota;
    }

    public function fetchTrending(string $region, int $maxResults = 50): array
    {
        $key = $this->quota->getActiveKey();
        if ($key === null) {
            throw new QuotaExhaustedException("All API keys exhausted for region {$region}");
        }

        $url = 'https://www.googleapis.com/youtube/v3/videos?' . http_build_query([
            'part'           => 'snippet,statistics,contentDetails',
            'chart'          => 'mostPopular',
            'regionCode'     => $region,
            'maxResults'     => $maxResults,
            'videoCategoryId' => '',
            'key'            => $key['key_val'],
        ]);

        $response = $this->get($url);

        if ($response['http_code'] === 403) {
            // Quota exceeded — disable this key, try next
            $this->quota->markError($key['key_val'], 'quota_exceeded');
            $this->quota->consume($key['key_val'], $key['quota_limit']); // Force exhaustion
            return $this->fetchTrending($region, $maxResults); // Recurse with next key
        }

        if ($response['http_code'] !== 200) {
            $this->quota->markError($key['key_val'], "HTTP {$response['http_code']}");
            throw new ApiException("YouTube API error: {$response['http_code']}");
        }

        // Charge the quota: 1 unit for videos.list
        $this->quota->consume($key['key_val'], 1);

        $body = json_decode($response['body'], true);
        return $body['items'] ?? [];
    }

    private function get(string $url): array
    {
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 20,
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_HTTPHEADER     => ['Accept: application/json'],
        ]);
        $body     = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $error    = curl_error($ch);
        curl_close($ch);

        if ($error) {
            throw new NetworkException("cURL error: {$error}");
        }

        return ['http_code' => $httpCode, 'body' => $body];
    }
}

<?php

class RegionBudget
{
    // Units per day per region for search
    private const BUDGETS = [
        'JP' => 2000, // High-traffic
        'KR' => 2000,
        'US' => 1500,
        'GB' => 1000,
        'TW' => 800,
        'SG' => 600,
        'HK' => 600,
        'VN' => 400,
        'TH' => 400,
    ];

    public function canSearch(string $region): bool
    {
        $used = $this->getUsedToday($region);
        $budget = self::BUDGETS[$region] ?? 200;
        return $used + 100 <= $budget;
    }

    private function getUsedToday(string $region): int
    {
        // Read from SQLite daily_budget table
        $stmt = $this->db->prepare(<<<SQL
            SELECT COALESCE(SUM(units_used), 0)
            FROM quota_log
            WHERE region = ? AND DATE(used_at) = DATE('now')
        SQL);
        $stmt->execute([$region]);
        return (int) $stmt->fetchColumn();
    }
}

<?php

class CircuitBreaker
{
    private const THRESHOLD   = 5;   // failures before opening
    private const TIMEOUT     = 300; // seconds before half-open

    public function isOpen(string $service): bool
    {
        $state = $this->getState($service);
        if ($state['status'] === 'open') {
            if (time() - $state['opened_at'] > self::TIMEOUT) {
                $this->halfOpen($service);
                return false; // Allow one test request
            }
            return true; // Still open
        }
        return false;
    }

    public function recordSuccess(string $service): void
    {
        $this->setState($service, ['status' => 'closed', 'failures' => 0]);
    }

    public function recordFailure(string $service): void
    {
        $state = $this->getState($service);
        $failures = ($state['failures'] ?? 0) + 1;

        if ($failures >= self::THRESHOLD) {
            $this->setState($service, [
                'status'    => 'open',
                'failures'  => $failures,
                'opened_at' => time(),
            ]);
            error_log("Circuit breaker OPEN for {$service}");
        } else {
            $this->setState($service, ['status' => 'closed', 'failures' => $failures]);
        }
    }
}

<?php

// api_keys.conf (deployed alongside the app)
// youtube_key=AIzaSyABC123...
// youtube_key=AIzaSyDEF456...
// youtube_key=AIzaSyGHI789...

public function autoImportApiKeys(): void
{
    $conf = parse_ini_file(__DIR__ . '/../api_keys.conf', false, INI_SCANNER_RAW);
    $keys = (array)($conf['youtube_key'] ?? []);

    $stmt = $this->pdo->prepare(<<<SQL
        INSERT OR IGNORE INTO api_keys (key_val, provider)
        VALUES (?, 'youtube')
    SQL);

    foreach ($keys as $key) {
        $stmt->execute([trim($key)]);
    }
}