Detecting and Replacing Stale Video Content Automatically

Introduction

Videos disappear from the internet constantly. They get deleted, marked private, or copyright-struck. If you run a video curation platform, stale content is your enemy. Here's the automated detection and replacement system I built for ViralVidVault.

Why Videos Go Stale

From monitoring thousands of videos across viralvidvault.com, here are the most common reasons:

• Deleted by uploader — ~40% of stale videos
• Made private — ~25%
• Copyright claim — ~20%
• Region-restricted — ~10% (especially relevant for European content)
• Terms violation — ~5%

The Freshness Check Pipeline

<?php

class FreshnessChecker
{
    private \PDO $db;
    private YouTubeClient $youtube;

    public function __construct(\PDO $db, YouTubeClient $youtube)
    {
        $this->db = $db;
        $this->youtube = $youtube;
    }

    public function checkBatch(int $batchSize = 50): FreshnessResult
    {
        // Get videos due for checking, oldest-checked first
        $stmt = $this->db->prepare('
            SELECT id, title, category_id, last_checked
            FROM videos
            WHERE is_active = 1
            ORDER BY last_checked ASC
            LIMIT :limit
        ');
        $stmt->execute([':limit' => $batchSize]);
        $videos = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        if (empty($videos)) {
            return new FreshnessResult(total: 0, fresh: [], stale: []);
        }

        $ids = array_column($videos, 'id');
        $apiResults = $this->youtube->getVideoStats($ids);

        // Map API results by ID
        $found = [];
        foreach ($apiResults as $item) {
            $found[$item['id']] = $item;
        }

        $fresh = [];
        $stale = [];

        foreach ($videos as $video) {
            if (isset($found[$video['id']])) {
                $fresh[] = $video['id'];
                $this->markChecked($video['id']);
            } else {
                $stale[] = [
                    'id' => $video['id'],
                    'title' => $video['title'],
                    'category_id' => $video['category_id'],
                    'reason' => 'not_found_in_api',
                ];
                $this->markStale($video['id']);
            }
        }

        return new FreshnessResult(
            total: count($videos),
            fresh: $fresh,
            stale: $stale
        );
    }

    private function markChecked(string $videoId): void
    {
        $this->db->prepare('UPDATE videos SET last_checked = CURRENT_TIMESTAMP WHERE id = ?')
                 ->execute([$videoId]);
    }

    private function markStale(string $videoId): void
    {
        $this->db->prepare('UPDATE videos SET is_active = 0, stale_at = CURRENT_TIMESTAMP WHERE id = ?')
                 ->execute([$videoId]);
    }
}

The Replacement Queue

When a video is marked stale, we queue a replacement from the same category:

<?php

class ReplacementQueue
{
    private \PDO $db;
    private YouTubeClient $youtube;

    public function __construct(\PDO $db, YouTubeClient $youtube)
    {
        $this->db = $db;
        $this->youtube = $youtube;
    }

    public function processQueue(): int
    {
        // Get stale videos grouped by category
        $stmt = $this->db->query('
            SELECT category_id, COUNT(*) as count
            FROM videos
            WHERE is_active = 0 AND replaced_by IS NULL
            GROUP BY category_id
        ');
        $categories = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $replaced = 0;

        foreach ($categories as $cat) {
            $trending = $this->youtube->getTrending(
                regionCode: 'US',
                categoryId: $cat['category_id'],
                maxResults: (int)$cat['count']
            );

            foreach ($trending as $newVideo) {
                // Check if we already have this video
                $exists = $this->db->prepare('SELECT 1 FROM videos WHERE id = ?');
                $exists->execute([$newVideo['id']]);

                if (!$exists->fetch()) {
                    $this->insertReplacement($newVideo, $cat['category_id']);
                    $replaced++;
                }
            }
        }

        return $replaced;
    }

    private function insertReplacement(array $video, int $categoryId): void
    {
        $stmt = $this->db->prepare('
            INSERT INTO videos (id, title, channel_title, category_id, thumbnail_url, views, published_at)
            VALUES (:id, :title, :channel, :cat, :thumb, :views, :pub)
        ');
        $stmt->execute([
            ':id' => $video['id'],
            ':title' => $video['snippet']['title'],
            ':channel' => $video['snippet']['channelTitle'],
            ':cat' => $categoryId,
            ':thumb' => $video['snippet']['thumbnails']['high']['url'] ?? '',
            ':views' => (int)($video['statistics']['viewCount'] ?? 0),
            ':pub' => $video['snippet']['publishedAt'],
        ]);
    }
}

Smart Check Scheduling

Not all videos need checking at the same frequency:

private function getCheckPriority(array $video): string
{
    $age = time() - strtotime($video['fetched_at']);
    $daysSinceFetch = $age / 86400;

    return match(true) {
        $daysSinceFetch < 1  => 'hourly',    // Fresh content: check often
        $daysSinceFetch < 7  => 'daily',     // This week: daily
        $daysSinceFetch < 30 => 'weekly',    // This month: weekly
        default              => 'biweekly',  // Older: every 2 weeks
    };
}

Results

Since implementing this system on ViralVidVault:

• Stale rate: ~3% of videos go stale per week
• Detection time: Average 12 hours from deletion to detection
• Auto-replacement: 95% of stale videos replaced within one cron cycle
• Zero broken embeds: Users always see working content

Key Takeaways

1. Batch your API status checks (50 IDs per request)
2. Use oldest-checked-first ordering for fair rotation
3. Replace stale content with same-category alternatives
4. Check newer content more frequently than old content
5. Track staleness patterns to predict future issues

---

Part of the "Building ViralVidVault" series.