Building a European Multi-Region Video Platform

#php #architecture #webdev #europe

Introduction

Most video aggregation platforms focus on US content. When I set out to build ViralVidVault, I wanted a platform that genuinely surfaced viral content from Poland, the Netherlands, Sweden, Norway, and Austria alongside the usual US and UK feeds. Here's how to architect a multi-region video platform.

The Region Strategy

YouTube's API lets you filter trending content by regionCode. The key insight is that each region has its own trending ecosystem. What's viral in Poland is often completely different from what trends in Sweden.

<?php

class RegionManager
{
    private const REGIONS = [
        'US' => ['name' => 'United States', 'priority' => 1, 'quota_weight' => 1.0],
        'GB' => ['name' => 'United Kingdom', 'priority' => 1, 'quota_weight' => 1.0],
        'PL' => ['name' => 'Poland', 'priority' => 2, 'quota_weight' => 1.2],
        'NL' => ['name' => 'Netherlands', 'priority' => 2, 'quota_weight' => 1.1],
        'SE' => ['name' => 'Sweden', 'priority' => 2, 'quota_weight' => 1.1],
        'NO' => ['name' => 'Norway', 'priority' => 2, 'quota_weight' => 1.1],
        'AT' => ['name' => 'Austria', 'priority' => 2, 'quota_weight' => 1.1],
    ];

    public function getActiveRegions(): array
    {
        return array_keys(self::REGIONS);
    }

    public function getQuotaWeight(string $region): float
    {
        return self::REGIONS[$region]['quota_weight'] ?? 1.0;
    }

    public function getRegionName(string $code): string
    {
        return self::REGIONS[$code]['name'] ?? $code;
    }
}

Cron Architecture for Multi-Region Fetching

The fetch pipeline runs as a single cron command that iterates through all regions:

<?php
// cron/fetch_videos.php

$regionManager = new RegionManager();
$fetcher = new VideoFetcher($apiKey);
$db = new Database(__DIR__ . '/../data/videos.db');

// Step 1: Fetch popular videos (global)
$popular = $fetcher->getPopular(maxResults: 50);
$db->upsertVideos($popular);
echo "Step 1: Fetched " . count($popular) . " popular videos\n";

// Step 2: Update categories
$categories = $fetcher->getCategories();
$db->syncCategories($categories);
echo "Step 2: Synced " . count($categories) . " categories\n";

// Step 3: Regional trending loop
foreach ($regionManager->getActiveRegions() as $region) {
    $trending = $fetcher->getTrending($region, maxResults: 25);
    $db->upsertVideos($trending, region: $region);
    echo "Step 3 [{$region}]: Fetched " . count($trending) . " trending videos\n";
    usleep(300000); // 300ms between regions for rate limiting
}

// Step 4: Stale video refresh
$staleCount = $db->refreshStaleVideos($fetcher);
echo "Step 4: Refreshed {$staleCount} stale videos\n";

// Step 5: Cleanup old content
$removedCount = $db->cleanupOldVideos(maxAgeDays: 90);
echo "Step 5: Removed {$removedCount} old videos\n";

Database Schema for Multi-Region Content

CREATE TABLE videos (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    channel_id TEXT,
    channel_title TEXT,
    category_id INTEGER,
    thumbnail_url TEXT,
    duration INTEGER,
    views INTEGER DEFAULT 0,
    likes INTEGER DEFAULT 0,
    published_at DATETIME,
    fetched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active INTEGER DEFAULT 1
);

CREATE TABLE video_regions (
    video_id TEXT NOT NULL,
    region TEXT NOT NULL,
    trending_rank INTEGER,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (video_id, region),
    FOREIGN KEY (video_id) REFERENCES videos(id)
);

CREATE INDEX idx_regions_region ON video_regions(region);
CREATE INDEX idx_videos_category ON videos(category_id, is_active);

Avoiding Content Duplication

The same video can trend in multiple regions simultaneously. The video_regions junction table handles this elegantly — one video record, multiple region associations:

public function upsertVideos(array $videos, string $region = 'US'): void
{
    $this->db->beginTransaction();

    foreach ($videos as $video) {
        // Upsert video record
        $stmt = $this->db->prepare('
            INSERT INTO videos (id, title, channel_id, channel_title, category_id, thumbnail_url, duration, views, likes, published_at)
            VALUES (:id, :title, :cid, :ctitle, :cat, :thumb, :dur, :views, :likes, :pub)
            ON CONFLICT(id) DO UPDATE SET
                views = excluded.views,
                likes = excluded.likes,
                fetched_at = CURRENT_TIMESTAMP
        ');
        $stmt->execute([/* ... */]);

        // Upsert region association
        $regionStmt = $this->db->prepare('
            INSERT INTO video_regions (video_id, region, trending_rank)
            VALUES (:vid, :region, :rank)
            ON CONFLICT(video_id, region) DO UPDATE SET
                trending_rank = excluded.trending_rank,
                last_seen = CURRENT_TIMESTAMP
        ');
        $regionStmt->execute([':vid' => $video['id'], ':region' => $region, ':rank' => $video['rank'] ?? null]);
    }

    $this->db->commit();
}

What Makes European Content Special

From running ViralVidVault for months, I've noticed distinct regional patterns:

Poland (PL) — Strong music and comedy scenes, very active engagement
Netherlands (NL) — Creative content, often bilingual (Dutch/English)
Sweden (SE) — Gaming and music dominate, high production quality
Norway (NO) — Outdoor/nature content trends uniquely here
Austria (AT) — German-language content with Central European flair

Key Takeaways

Treat each region as an independent trending ecosystem
Use a junction table to avoid video duplication across regions
Run regional fetches sequentially with rate limiting
Apply quota weights to prioritize underrepresented regions
SQLite with WAL mode handles concurrent reads during cron writes

You can explore the results at viralvidvault.com — browse by category or region to see how European viral content differs from the mainstream.

Part of the "Building ViralVidVault" series.

DEV Community