Competitor video content monitoring platform

2026 · sole architect and developer · Python 3.12, FastAPI, PostgreSQL 16, React, Docker

A platform that watches competitors’ short-form video across two social networks, keeps engagement as a time series, and fires an alert when a video is outgrowing what that channel normally achieves at that age.

Dashboard

The problem

The client works a content-poor niche where video is the main channel, and wanted to know what was working for competitors before it stopped working. Two specific pains.

Manual review did not scale. Checking competitors meant opening each account and scrolling: “you have to go through every link in Instagram by hand and see what the clip even is.”

By the time a topic is obviously hot, it is cold. The client’s own framing: “otherwise it will turn out that by the moment it is clear the hot news interests the audience, it has long gone stale.” A daily digest of top videos is useless if the useful window is six hours wide.

So the product is not a leaderboard. It is a detector.

Architecture

A single FastAPI monolith, PostgreSQL 16, a React SPA behind nginx, three containers on one VPS. No microservices, no message broker, no orchestrator. For a system whose real constraint is external API quota rather than internal throughput, that is the right size.

Ingestion follows two very different paths, because the platforms are not comparable.

YouTube has an official API with a hard quota: 10 000 units per key per day. The naive route, search.list, costs 100 units per call. Reading the channel’s uploads playlist and then batching video IDs costs about 1 unit per 50 videos. That single change is the difference between refreshing a hundred channels a day and refreshing eight hundred every six hours.

Instagram offers no official API suited to collecting public competitor content for this use case, so everything runs through third-party scraper actors on a rented platform, with session cookies mounted into the container in two formats because different actors expect different ones.

The thumbnail subsystem exists because of one infrastructure fact

The VPS sits in a datacenter, and Instagram’s CDN blocks datacenter IPs. The backend physically cannot download a cover image. The workaround is a custom scraper actor running on residential proxies that downloads the images and posts them back to an upload endpoint behind a shared secret; nginx then serves them from a volume. A twelve-hour loop re-downloads them, because those CDN URLs expire after a few hours.

This is the kind of requirement that never appears in a specification and consumes a disproportionate share of the build.

The database

Twelve tables. The interesting decisions are all in how measurement history is treated.

One row per video per collection cycle

video_stats_snapshots is append-only: video id, views, likes, comments, captured at. No rollups, no aggregation, no time-series extension. At the actual volume, roughly thirty Instagram accounts at ten reels each across four cycles a day, this produces low thousands of rows per day and a steady state in the low hundreds of thousands under a ninety-day retention window. Plain Postgres is comfortable there, and reaching for a time-series extension would have added an operational dependency to solve a problem that does not exist.

Two details make the series usable.

A synthetic birth snapshot. On first insert, a zero row is written with captured_at = published_at, so every growth curve has an origin instead of starting wherever the crawler happened to find it.

Append on change, for scheduled runs. The Instagram scraper returns static numbers between refreshes, and appending identical rows produces flat stretches that look like a stalled video rather than an unobserved one. Manual runs always append, so a human can force a datapoint.

Soft delete, and why it mattered more than it sounds

The original design deleted videos that fell out of the scraper’s window, and the ORM relationship carried cascade="all, delete-orphan". The consequence, once traced:

As soon as a channel posts its eleventh reel, the oldest is deleted along with its entire history, and the baseline for that channel is abruptly impoverished.

The detector compares a video against its own channel’s history. Deleting rotated videos was quietly deleting exactly the data the product depends on, and it was invisible because nothing errored: the alerts simply got worse.

Replaced with removed_at, set when the parser stops returning a video and cleared if it reappears. Removed videos still feed the baseline and never generate a new alert. The cost was written down at the time rather than discovered later: the table grows, and after a year a channel accumulates hundreds of dead rows.

Constraints in the database, not in the application

app_settings is a single-row table, enforced by CHECK (id = 1), with fifteen range checks on the tunables: the virality threshold must be between 1 and 100, the alert cooldown between 1 and 720 hours. An admin panel writes to it.

This turned out to be the highest-leverage decision in the project. When alerts started duplicating in production and the client wrote “I am scrolling the last four days and it is all duplicates”, the behaviour was correct by design and the fix was one field in a form. No deploy, no code change.

Retention

A daily loop deletes snapshots older than ninety days. The reasoning is written into the docstring: the deepest bucket the detector reads is 72 hours, so ninety days is a generous floor. The tracker snapshot table has no equivalent cleanup, which is an oversight rather than a decision.

The detector, and two failures before it worked

This is the substance of the project, and it took three versions.

Version one compared views per hour over the last six hours against the median of the first twenty-four. It reported a four-hour-old livestream at 27 times baseline. The windows were asymmetric, there was no absolute floor, and the channel was small enough that noise dominated.

Version two introduced age buckets at 2, 4, 6, 12, 24, 48 and 72 hours, comparing a video only against other videos at the same age, with linear interpolation between snapshots. It reported 22 times baseline. The calculation was arithmetically correct and the answer was still wrong: only older videos had snapshot coverage at the required bucket ages, so the baseline was silently the stale back catalogue. A patch went out and 390 alerts were purged.

Then I rejected my own patch, because the error was not in the guards:

The program simply divides view count by hours, but in fact clips grow quickly at first and then very slowly.

Linear interpolation assumes constant growth. Video view counts do not grow that way. A video first seen at 24 hours with 10 000 views did not have 830 views at hour two, which is what linear scaling claims; it had closer to 2 900.

Version three fits a power law, V(t) = a·t^b, by least squares in log-log space, with b = 0.5 as a single-point fallback and forward extrapolation capped at twice the age of the latest snapshot. The coefficient becomes the ratio of a video’s views at its deepest reached bucket against the channel’s median at the same bucket, and fires at three times.

Four independent guards keep false alarms down: age-matched comparison, a minimum of three samples per bucket, an absolute floor of fifty baseline views so tiny channels cannot produce huge ratios, and a 24-hour per-video cooldown with a supporting index.

One gap remains, and it was stated to the client rather than hidden: the detector can compare at a two-hour bucket, but ingestion runs every six hours, so a new video may not exist in the database when that bucket passes.

Background work without a broker

Eight asyncio loops start in the FastAPI lifespan and are cancelled on shutdown. Each reads its interval from app_settings, sleeps, works inside a try/except that logs and continues. Competitor refresh every 6 hours, virality scan every 2, trackers every 6, niche searches weekly, cleanup daily, thumbnails every 12.

Concurrency is controlled by in-process semaphores: three concurrent parses to stay under rate limits, eight for virality computation, and a global semaphore of one serialising all scraper dataset downloads, because the VPS could not handle them concurrently.

Retries are hand-rolled per call site, and deliberately narrow. A competitor parse retries three times with 15 and 30 second backoff, but only for an allowlist of transient error strings: DNS failures, connection resets, timeouts. Anything else fails immediately and marks the competitor as errored, rather than burning three attempts on a permanent failure.

Because there is no broker, a restart can leave jobs marked running forever. A boot-time sweep finds and fails them.

One bug worth recording. The snapshot loop originally slept at both the top and the bottom of its body, so the real cadence was twelve hours rather than six. Nothing failed. The data was simply half as dense as intended, for an unknown period, until someone read the loop rather than the config.

What the constraint actually was

Not the database, and not the code. It was quota and cost.

At a hundred Instagram competitors refreshed every six hours, the official scraper actor costs roughly $300 a month; a functionally equivalent alternative brings that to about $60. Selecting between actors on price and result quality, rather than accepting the default, was worth more than any code change in the project.

The largest single lesson was a wrong cost model held for the first weeks: billing is per item, not per run, so fetching thirty reels per account costs three times fetching ten rather than the same. Refresh depth, not refresh frequency, was the expensive dial, and the strategy was built around the wrong one until that was corrected.

Credentials are held in a database table rather than environment variables, so a key can be rotated, marked exhausted or replaced without a redeploy, and the active key is persisted so a restart resumes where it left off.

Migrations, honestly

Three incidents, all from the same root cause: the schema was originally created by create_all() at startup, and Alembic was added afterwards.

Production had never been baselined, so the first migration had to be stamped by hand.
Two revision identifiers exceeded the 32-character column Alembic uses to record them, aborting a migration mid-deploy. The rollback was clean, the identifiers were shortened, and the migration re-ran.
A permanent shim survives in the startup path: sixteen idempotent ALTER TABLE ... ADD COLUMN IF NOT EXISTS statements, run on every boot, labelled as debt in its own docstring.

And a deploy script that ran docker compose down -v, which drops the database volume. Fine until production had data in it, at which point a second script was written that recreates only the backend container and runs migrations inside it.

What I would do differently

Baseline Alembic on day one. Every one of the three migration incidents traces back to starting with create_all().

Use a connection pool. The project runs NullPool, opening a connection per session. It works at this volume and would not at ten times it.

Upsert in SQL. Video writes are read-then-write in Python, one select per video per cycle, protected only by unique constraints. INSERT ... ON CONFLICT would be one statement and would remove the race entirely.

Add a TTL to the tracker snapshot table. It grows without bound today.

Model growth before building the detector, not after. Two rewrites and 396 purged alerts were the price of assuming linear growth for something that is obviously not linear. The power-law model came from twenty minutes of reading, after several days of patching guards.