When I set out to build TopVideoHub, a video discovery platform covering 9 Asia-Pacific regions, I had to design an architecture that could fetch, store, and serve trending video data from Japan, South Korea, Taiwan, Singapore, Vietnam, Thailand, and Hong Kong — alongside the US and UK.
Here's how I built it with PHP 8.3, SQLite, and LiteSpeed.
Architecture Overview
The platform has three main components:
Data Pipeline — Cron-based fetcher that pulls trending videos from YouTube Data API v3 per region
Storage Layer — SQLite database with FTS5 for multi-language search
Serving Layer — LiteSpeed with three-layer caching for sub-100ms responses
The Data Pipeline
Each region has its own fetch cycle. The fetcher runs on staggered cron schedules to distribute API quota usage:
<?php
// fetch_videos.php — Called by cron
declare(strict_types=1);
require_once __DIR__ . '/app/Database.php';
require_once __DIR__ . '/app/YouTubeApi.php';
require_once __DIR__ . '/app/QuotaManager.php';
$db = Database::getInstance();
$quota = new QuotaManager($db);
$api = new YouTubeApi($quota);
$regions = ['US', 'GB', 'JP', 'KR', 'TW', 'SG', 'VN', 'TH', 'HK'];
foreach ($regions as $region) {
if (!$quota->canFetch()) {
echo "Quota exhausted, stopping.\n";
break;
}
echo "Fetching {$region}...\n";
$videos = $api->fetchTrending($region, maxResults: 50);
$inserted = 0;
foreach ($videos as $video) {
$existing = $db->findByVideoId($video['id']);
if ($existing) {
$db->addRegion($existing['id'], $region);
} else {
$db->insertVideo($video, $region);
$inserted++;
}
}
echo " {$region}: {$inserted} new, " . count($videos) . " total\n";
}
The QuotaManager tracks daily usage per API key and rotates keys when one approaches its limit:
class QuotaManager {
private const int DAILY_LIMIT = 10000;
private const int SAFETY_MARGIN = 1000;
public function canFetch(): bool {
$used = $this->db->query(
"SELECT COALESCE(SUM(cost), 0) FROM api_usage WHERE date = ?",
[date('Y-m-d')]
)->fetchColumn();
return $used < (self::DAILY_LIMIT - self::SAFETY_MARGIN);
}
}
Multi-Region Database Schema
The database needs to handle videos that trend in multiple regions simultaneously:
CREATE TABLE videos (
id INTEGER PRIMARY KEY,
video_id TEXT UNIQUE NOT NULL,
title TEXT NOT NULL,
channel_title TEXT,
thumbnail_url TEXT,
view_count INTEGER DEFAULT 0,
published_at TEXT,
category_id INTEGER,
fetched_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE video_regions (
video_id INTEGER REFERENCES videos(id),
region TEXT NOT NULL,
trending_rank INTEGER,
fetched_at TEXT DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (video_id, region)
);
CREATE INDEX idx_regions_region ON video_regions(region);
A K-pop video trending in KR, TW, TH, SG, and VN has one row in videos and five rows in video_regions. This normalized design prevents data duplication while enabling cross-region queries.
Serving Content Fast
For the serving layer, I use LiteSpeed's built-in page cache with tag-based purging:
// In the controller
public function home(): void {
header('X-LiteSpeed-Cache-Control: public, max-age=10800');
header('X-LiteSpeed-Tag: page:home');
// ... render template
}
public function category(string $slug): void {
header('X-LiteSpeed-Cache-Control: public, max-age=10800');
header('X-LiteSpeed-Tag: page:category, cat:' . $slug);
// ... render template
}
When new trending data arrives, the cron job purges relevant cache tags:
// After fetching new videos
header('X-LiteSpeed-Purge: tag=page:home');
foreach ($updatedCategories as $cat) {
header('X-LiteSpeed-Purge: tag=cat:' . $cat, false);
}
This ensures users see fresh content within minutes of a fetch while getting cached responses the rest of the time.
Results
TopVideoHub serves trending videos from 9 regions with:
- Sub-100ms average response time
- Under $10/month hosting
- Zero infrastructure management
The simplest stack often wins. PHP 8.3 + SQLite + LiteSpeed is a combination that deserves more attention for content platforms.