Building a Multi-Region Video Platform Serving Asia-Pacific Markets

#php #webdev #tutorial #architecture

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.

DEV Community