The problem nobody warns you about
On a busy Tuesday evening, a Spanish football clip we'd indexed two hours earlier started trending. Within twenty minutes, the watch page was being hit by twelve thousand concurrent visitors, every one of them refreshing the page every few seconds to see the view count tick up. Our SQLite database was fine — WAL mode handles concurrent reads beautifully — but the LiteSpeed access logs were a sea of GET /watch/... requests, every single one rendering a full page with the same template, the same recommendations, the same everything, just to update one integer.
I run ViralVidVault, a European-focused viral video discovery platform built on PHP 8.4, SQLite in WAL mode, LiteSpeed, and a thin Cloudflare Workers layer for edge logic. We don't operate at YouTube scale — but we do operate at "one viral video can spike a single endpoint by 50× for an hour" scale, which is arguably worse because you can't justify the infrastructure of a giant.
Server-Sent Events solved this in about a hundred and fifty lines of code, total, including the Cloudflare Worker that dedupes increments at the edge. No WebSocket server, no Redis pub/sub, no separate Node.js process. Just PHP streaming text/event-stream chunks from a long-lived request, SQLite as the source of truth, and the browser's built-in EventSource doing the reconnection work for free.
This article is a walk-through of the actual implementation, including the GDPR considerations that matter when most of your traffic is European, the LiteSpeed quirks I wish I'd known on day one, and the SQLite WAL behaviour under a fanout-write workload.
Why SSE, specifically
I evaluated three options before committing:
- WebSockets: bidirectional, but I don't need bidirectional. Server-side I'd need either a separate WS process (Ratchet, Swoole, Workerman) or to lean on a managed service. Both add operational surface area for a feature that just pushes integers.
- Long polling: works everywhere, but every reconnection is a full HTTP request with full headers, cookies, TLS handshake amortised — at 12k concurrent users polling every 2 seconds, that's 6,000 requests per second hitting the origin. Cloudflare can absorb some of it but the cache-key permutations get ugly.
-
Server-Sent Events: one long-lived HTTP/1.1 connection per client, server pushes events whenever it wants, the browser handles reconnection and message ordering via
Last-Event-ID. The connection lives behind Cloudflare just fine as long as you flush properly and the worker passes through thetext/event-streamcontent type without buffering.
The bidirectional argument is the one most people get stuck on. You don't need WebSockets just because "real-time" is in the requirements. If clients only need to receive, SSE is half the complexity.
The other thing nobody mentions: SSE plays beautifully with HTTP caching primitives. The stream URL itself is uncacheable, obviously, but the initial page load that bootstraps the EventSource is fully cacheable. That matters when Cloudflare is your front door.
The architecture in one paragraph
A visitor lands on /watch/{slug}. The HTML response is served from LiteSpeed's page cache for anonymous users (most of our traffic), takes about 8ms at the origin and zero ms at the edge. Embedded in the page is a tiny script that opens an EventSource connection to /sse/views/{video_id}. That endpoint is a PHP script running under LiteSpeed's lsphp worker pool, configured to bypass the page cache and stream events for up to 60 seconds before closing (the client reconnects automatically). The script polls the SQLite videos table every 2 seconds and only emits an event when the view count has actually changed.
View increments come from a Cloudflare Worker that fronts the /api/view POST endpoint. The Worker checks Workers KV to see if this IP-prefix plus video pair has already been counted in the last 60 seconds; if so, it returns a 204 No Content without touching origin. If not, it forwards to origin which updates the SQLite counter inside a tight transaction.
That's it. Three components, each doing one thing.
The Cloudflare Worker for view dedupe
This sits in front of the PHP origin and handles the write path. The reason it's at the edge: IP-based dedupe needs sub-50ms latency to feel honest, and round-tripping to origin just to get rejected is wasteful when 80% of refresh-spam is filterable on the IP+video tuple.
export default {
async fetch(request, env) {
if (request.method !== 'POST') {
return new Response('Method not allowed', { status: 405 });
}
const url = new URL(request.url);
const match = url.pathname.match(/^\/api\/view\/([a-zA-Z0-9_-]+)$/);
if (!match) return new Response('Not found', { status: 404 });
const videoId = match[1];
const ip = request.headers.get('CF-Connecting-IP') || '0.0.0.0';
// GDPR: truncate IPv4 last octet, IPv6 last 80 bits before hashing
const truncated = ip.includes(':')
? ip.split(':').slice(0, 3).join(':') + '::'
: ip.split('.').slice(0, 3).join('.') + '.0';
const hashBuf = await crypto.subtle.digest(
'SHA-256',
new TextEncoder().encode(truncated + ':' + videoId + ':' + env.SALT)
);
const hash = [...new Uint8Array(hashBuf)]
.slice(0, 16)
.map(b => b.toString(16).padStart(2, '0'))
.join('');
const kvKey = `view:${hash}`;
if (await env.VIEW_DEDUPE.get(kvKey)) {
return new Response(null, { status: 204 });
}
// 60s window, no PII retained beyond TTL
await env.VIEW_DEDUPE.put(kvKey, '1', { expirationTtl: 60 });
const originResp = await fetch(
`https://origin.viralvidvault.com/api/view/${videoId}`,
{ method: 'POST', headers: { 'X-Forwarded-By': 'edge-worker' } }
);
return new Response(null, { status: originResp.status });
}
};
A few things worth pointing out. The IP truncation happens before hashing — this is the difference between "we processed personal data and immediately discarded it" and "we never processed personal data in a form that could identify an individual." The German DPA has been explicit about this distinction for years. The salt is environment-scoped and rotated quarterly; without it, you could rainbow-table the hash space for any /24 trivially.
The 60-second KV TTL means the deduplication window is intentionally short. If a viewer genuinely returns to the same video an hour later, that's a legitimate second view. If they refresh seventeen times in a minute, that's one view. This is policy as much as technical implementation, but it falls out cleanly from the TTL.
The PHP SSE endpoint
This is the read path — the long-lived connection that streams the current view count to the browser. The endpoint lives at public/sse/views.php and is routed via the same front controller as the rest of the app.
<?php
declare(strict_types=1);
namespace App\Controllers;
use PDO;
final class StreamController
{
private const TICK_INTERVAL_US = 2_000_000;
private const MAX_LIFETIME_S = 60;
private const HEARTBEAT_S = 15;
public function viewCount(string $videoId): void
{
if (!preg_match('/^[a-zA-Z0-9_-]{8,32}$/', $videoId)) {
http_response_code(400);
return;
}
// SSE headers — order matters for LiteSpeed
header('Content-Type: text/event-stream; charset=utf-8');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('X-Accel-Buffering: no');
header('Connection: keep-alive');
@ini_set('output_buffering', '0');
@ini_set('zlib.output_compression', '0');
while (ob_get_level() > 0) { ob_end_flush(); }
ob_implicit_flush(true);
ignore_user_abort(false);
set_time_limit(self::MAX_LIFETIME_S + 5);
$db = \App\Database::readOnly();
$stmt = $db->prepare('SELECT view_count FROM videos WHERE id = :id');
$eventId = (int)($_SERVER['HTTP_LAST_EVENT_ID'] ?? 0);
$deadline = time() + self::MAX_LIFETIME_S;
$lastHeartbeat = time();
$stmt->execute([':id' => $videoId]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row === false) {
$this->emit($eventId, 'error', ['code' => 'not_found']);
return;
}
$lastSent = (int)$row['view_count'];
$this->emit($eventId++, 'views', ['count' => $lastSent]);
while (time() < $deadline) {
if (connection_aborted()) { break; }
usleep(self::TICK_INTERVAL_US);
$stmt->execute([':id' => $videoId]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row === false) { break; }
$current = (int)$row['view_count'];
if ($current !== $lastSent) {
$this->emit($eventId++, 'views', ['count' => $current]);
$lastSent = $current;
$lastHeartbeat = time();
} elseif (time() - $lastHeartbeat >= self::HEARTBEAT_S) {
echo ": keepalive\n\n";
@ob_flush();
@flush();
$lastHeartbeat = time();
}
}
}
private function emit(int $id, string $event, array $data): void
{
echo "id: {$id}\n";
echo "event: {$event}\n";
echo 'data: ' . json_encode($data, JSON_UNESCAPED_SLASHES) . "\n\n";
@ob_flush();
@flush();
}
}
The thing that bit me on day one: LiteSpeed's mod_lsapi will happily buffer your output unless you set X-Accel-Buffering: no and turn off zlib.output_compression. The combination of ob_implicit_flush(true) plus explicit flush() after each emit is belt-and-braces but necessary; LiteSpeed handles flushing slightly differently from mod_php and the explicit flush() is what actually pushes bytes onto the wire.
The 60-second max lifetime is deliberate. The browser will reconnect automatically when the connection closes, so capping the request lifetime keeps lsphp worker slots from being held hostage. With our worker pool at 80 and a 60s lifetime, we can comfortably handle around 4,800 sustained SSE clients per origin server before backpressure starts. We have never come close.
The HTTP_LAST_EVENT_ID header is sent by the browser on reconnect — if you assign monotonically increasing IDs (as we do), you can resume from where the client left off. For a view counter this barely matters because we always send the current value, but the protocol is designed for use cases where event ordering matters, and respecting the header costs nothing.
SQLite WAL behaviour under fanout reads
A common worry: "you're polling SQLite every 2 seconds from thousands of connections, won't that destroy it?" In WAL mode, no. WAL is specifically designed for high-concurrency reads with occasional writes, and SELECT against a primary key with the row in cache is something SQLite does in microseconds.
The numbers from a recent peak (about 3,400 concurrent SSE clients across our pool):
- Average read latency on
SELECT view_count FROM videos WHERE id = ?: 0.04ms - 99th percentile: 0.21ms
- WAL file size: stayed under 4MB (checkpoints every 1000 pages)
- CPU on the SQLite process: less than 8% of one core
The write path is where you have to think. SQLite serialises writes — one BEGIN IMMEDIATE at a time. For 12k visitors trying to increment the counter, that would be a problem if they were all hitting origin. That's exactly why the Cloudflare Worker shields the write path: by the time increment requests reach SQLite, they have been deduped to roughly one per IP-prefix per minute per video, which during a viral spike comes out to maybe 80 writes per second peak. SQLite eats that for breakfast.
The increment itself uses a tight transaction:
<?php
declare(strict_types=1);
namespace App\Controllers;
final class ViewController
{
public function increment(string $videoId): void
{
if (!preg_match('/^[a-zA-Z0-9_-]{8,32}$/', $videoId)) {
http_response_code(400);
return;
}
// Trust only the edge worker; reject direct hits to origin
if (($_SERVER['HTTP_X_FORWARDED_BY'] ?? '') !== 'edge-worker') {
http_response_code(403);
return;
}
$db = \App\Database::writable();
$db->exec('BEGIN IMMEDIATE');
try {
$stmt = $db->prepare(
'UPDATE videos
SET view_count = view_count + 1,
last_viewed_at = strftime(\'%s\', \'now\')
WHERE id = :id'
);
$stmt->execute([':id' => $videoId]);
if ($stmt->rowCount() === 0) {
$db->exec('ROLLBACK');
http_response_code(404);
return;
}
$db->exec('COMMIT');
} catch (\Throwable $e) {
$db->exec('ROLLBACK');
error_log('view_increment_failed: ' . $e->getMessage());
http_response_code(500);
return;
}
http_response_code(204);
}
}
The shared-secret-via-header pattern (X-Forwarded-By: edge-worker) is the minimum acceptable bar here. In production we also check the Cloudflare-issued CF-Ray header and validate against the published Cloudflare edge IP ranges, but for clarity I have left that out.
BEGIN IMMEDIATE is important. PHP's PDO beginTransaction() issues plain BEGIN, and on a hot write path you want immediate locking to avoid the late-upgrade deadlock. I switched to exec('BEGIN IMMEDIATE') after a one-time SQLITE_BUSY retry storm and never saw the issue again.
The browser side
This is the easy part. Modern browsers have EventSource built in, reconnection logic included.
(function () {
const el = document.querySelector('[data-view-counter]');
if (!el) return;
const videoId = el.dataset.videoId;
if (!videoId) return;
const source = new EventSource(`/sse/views/${encodeURIComponent(videoId)}`);
source.addEventListener('views', (event) => {
try {
const data = JSON.parse(event.data);
if (typeof data.count === 'number') {
const current = parseInt(el.textContent.replace(/[^\d]/g, ''), 10) || 0;
animateTo(el, current, data.count);
}
} catch (_) { /* malformed payload, ignore */ }
});
source.addEventListener('error', () => {
// Browser will reconnect automatically; nothing to do
});
// Fire-and-forget view increment, deduped at the edge
fetch(`/api/view/${encodeURIComponent(videoId)}`, {
method: 'POST',
keepalive: true,
credentials: 'omit',
}).catch(() => {});
document.addEventListener('visibilitychange', () => {
if (document.hidden) source.close();
});
function animateTo(node, from, to) {
if (to <= from) { node.textContent = to.toLocaleString('en-GB'); return; }
const diff = to - from;
const steps = Math.min(diff, 30);
const stepMs = 600 / steps;
let i = 0;
const tick = () => {
i++;
const val = Math.round(from + (diff * i / steps));
node.textContent = val.toLocaleString('en-GB');
if (i < steps) setTimeout(tick, stepMs);
};
tick();
}
})();
The credentials: 'omit' on the POST is intentional and important. We do not need cookies on the increment endpoint, and including them would create a tracking vector that is harder to defend under GDPR. The increment is keyed entirely on the truncated IP hash, which is not personal data once the truncation has happened (per the German DPA's published guidance and the Article 29 Working Party opinion 4/2007, both of which we cite in our DPIA).
The visibilitychange handler closes the EventSource when the tab is hidden. This isn't strictly necessary — the browser will eventually close idle connections — but it's polite to the origin and frees a worker slot immediately.
LiteSpeed configuration gotchas
The three things I had to change in .htaccess and the lsws configuration to make this work cleanly:
-
Disable LiteSpeed cache on the SSE URL pattern. Adding
RewriteRule ^sse/ - [E=Cache-Control:no-cache]inside the<IfModule LiteSpeed>block ensures the cache module doesn't try to cache the streaming response. Without this, the first viewer's stream gets cached and replayed to subsequent viewers, which is hilarious in development and catastrophic in production. -
Increase
connection_keepalive_timeout. The default is 5 seconds. For a 60-second SSE stream you obviously need more. We set 90 seconds inhttpd_config.conf. -
Confirm
gzipis off fortext/event-stream. By default LiteSpeed compresses based on MIME type. The compression buffer breaks the streaming semantics. Addingtext/event-streamto the compression exclusions fixed sporadic "stuck" connections that I spent an embarrassing amount of time blaming on Cloudflare.
The Cloudflare side needed one change: in the page rule for /sse/*, set "Cache Level: Bypass" and turn off "Auto Minify" and "Rocket Loader" for that path. The rest works out of the box. Cloudflare does not buffer responses with Content-Type: text/event-stream — I confirmed this with their support team last year because the docs were ambiguous at the time.
GDPR compliance, briefly
Because the European market is the entire point, this isn't a footnote. The implementation above is designed to be defensible by construction, not by policy bolted on after the fact:
- No cookies on any SSE or increment endpoint.
credentials: 'omit'enforces this client-side; the server'sSet-Cookiepolicy enforces it server-side. - IP truncation before hashing means we never store personal data, even transiently in KV.
- The 60-second TTL on the dedupe KV means there is no persistent profile being built.
- No third-party analytics SDKs on the streaming pages. The view counter is internal; nothing leaves the perimeter.
- The salt-rotation schedule (quarterly) means even if someone exfiltrated a KV snapshot, the window for correlation attacks is bounded.
Our cookie banner doesn't fire for the streaming functionality because there is nothing to consent to — no personal data is being processed in an identifying form. That's a design outcome, not a legal opinion, but it falls out of the architecture.
What I would skip in your version
If you're building this in a hurry and don't care about a few of the things I cared about:
- Skip the Cloudflare Worker initially. A PHP-side dedupe using an APCu shared cache will get you 90% of the way there, until traffic forces the edge.
- Skip the heartbeat unless you actually see connections dying. Modern browsers and Cloudflare are good about keeping idle HTTP/1.1 connections open for at least 30s.
- Skip the count-up animation if your designer doesn't ask for it. The plain integer change is fine.
What you should keep:
- The
X-Accel-Buffering: noheader. Non-negotiable. - The IP truncation. If you're serving European traffic, this is the difference between a tidy DPIA and a long uncomfortable conversation.
- The connection lifetime cap. Without it, your worker pool will eventually fill up and the failure mode is opaque.
Conclusion
Server-Sent Events are the right tool for one-way live updates, and they're embarrassingly well-supported by every browser, every CDN, and every PHP worker pool. The fact that I had to write zero lines of Node.js, deploy zero new processes, and add zero new dependencies to ship a live view counter to production is a property worth optimising for. The architecture above has been running on ViralVidVault since the autumn and the operational story is "nothing to report" — which is the best possible operational story.
If you take one thing from this article, take the discipline of separating the read path (long-lived SSE stream, polls SQLite) from the write path (short-lived HTTP POST, deduped at the edge). The moment those start sharing code is the moment your worker pool starts looking strange under load. Keep them apart and SSE will repay you handsomely.













