Video Analytics Engine

The short version

Watch-time analytics looks like a counter and behaves like a distributed systems problem. The client is untrusted, delivery is at-least-once, and any cached aggregate over an append-only log will eventually disagree with the log.

This engine makes the defensible version the only version:

• watched seconds are recomputed server-side from raw segment ranges, never read from the client;
• ingest is idempotent twice — once per HTTP request, once per piece of evidence;
• reads come from a materialised projection that can be dropped and rebuilt from the evidence log;
• drift is measurable, repairable in bounded pages, and verifiable afterwards.

The engine comes from a production video platform, where it was hardened across four numbered schema migrations and a long repair history. I extracted the measurement core — the algorithms, policies, SQL contracts and failure semantics — and rebuilt it without the product surface: no player SDK, no provider integration, no playback-token layer.

The problem

A video platform needs to answer a simple-sounding question: how much of this video did this learner actually watch? The naive implementation is a counter. Three properties make that counter wrong at the same time.

There is a fourth, subtler trap: a union cannot be updated by addition. If a learner rewatches a range, the unique figure has to be the size of the union of every range they have seen. Adding the new segment's length to the stored total double-counts the rewatch. The same mistake appears one level up when per-session unique totals are summed.

// ✗ A union cannot be updated by addition.
progress.uniqueContentSeconds += segment.endSecond - segment.startSecond;

// ✗ Summing per-session unique totals double-counts a rewatch.
const total = sessions.reduce((sum, s) => sum + s.uniqueWatchedSeconds, 0);

// ✓ Merge the stored ranges with the new ones, then re-measure.
const merged = mergeRanges([...progress.watchedRanges, newRange]);
progress.uniqueContentSeconds = sumRanges(merged);

The project exists to make the correct version the only version.

The model: evidence, not totals

The client sends evidence — intervals plus the wall-clock time it claims to have spent on them. It never sends a "watched seconds" number. The server turns evidence into metrics in exactly one place.

Two details matter more than they look:

1. Paused time falls out of the data model. A segment is a contiguous stretch of playback. Pausing closes the segment, so no segment spans a pause. There is no "remove paused time" filter to forget.
2. Unknown duration is not zero. If the authoritative duration cannot be resolved, the raw union is preserved rather than clipped to zero. "We do not know how long the video is" and "they watched nothing" are different facts, and collapsing them loses data.

The two watch-time numbers exist because they answer different questions. engagedWallClock is real time spent with playback running. uniqueWatched is how much of the content was seen once. A video watched at 2× for 30 minutes covers 60 minutes of content but is 30 minutes of study. Reporting one under the label of the other is how "learned 40 hours this week" ends up meaning something else.

Architecture

Three paths, deliberately separate:

• Write path — synchronous and transactional. One BEGIN IMMEDIATE covers the evidence insert, the session update, the projection upsert and the outbox row. If any part fails, none of it happened, which is what makes the client's retry safe.
• Read path — never touches raw evidence. It reads the projection and also returns the canonical recompute and the drift between them, so a discrepancy is visible without opening an admin tool.
• Repair path — the only thing allowed to rewrite derived state, and it does so in bounded, resumable pages.

The broker never decides whether a segment counts. That is enforced by a unique index in the store. The broker only decides when the projection updates — which is why the system behaves identically with the in-process broker and with Kafka.

The decisions that carried the design

1. Two layers of idempotency, because one is never enough

The scope key is session-independent on purpose. A client that was offline long enough for its session to expire replays its segments under a new session id. If dedup identity included the session, the replay would be counted twice — the exact failure the layer exists to prevent. The scope is a generated column (subject_type || ':' || COALESCE(subject_id, 'unknown')), so application code cannot get it wrong.

2. The idempotency claim happens after parsing

If the claim were taken before parsing, a malformed request would consume the key. The client's corrected retry would then be answered "already applied" — a cheerful success for a request that never happened. The evidence would be dropped and the watch time lost, with no error anywhere. The claim is released in the catch block when the write fails, so a genuine retry is not suppressed.

3. A duplicate is marked, never deleted

Duplicates are preserved for audit and excluded from every calculation by a flag. Deleting evidence destroys the ability to explain why a number is what it is.

4. The projection is a cache, and repair is a product feature

watch_segments is append-only and never updated or deleted. subject_progress is derived and rebuildable. The incremental path (fold each batch into the stored projection) and the authoritative path (recompute from every raw segment) must agree. That is not a hope — a test applies incrementally, recomputes from scratch, and asserts the two agree within 0.02 s.

5. The broker is optional and never in the critical path

Events are written to an outbox table in the same transaction as the evidence, then a relay publishes them. If the process dies between commit and publish, the row survives and the event goes out next pass. The row is marked published after the publish returns. If the process dies in between, the event is published twice rather than not at all — and publishing twice is safe because the consumer is idempotent. The opposite ordering would lose events.

MemoryBroker is not a mock. It implements real partition assignment, monotonic offsets, consumer groups, at-least-once delivery, a dead-letter queue, lag accounting and offset replay. KafkaBroker exposes the same semantics over Kafka when KAFKA_BROKERS is set.

6. The tracker is player-agnostic

The client consumes ten standard media callbacks — play, playing, timeupdate, pause, seeking, seeked, ratechange, ended, waiting, stalled — exactly what a native <video> element emits. No player SDK, no iframe, no provider metadata.

The interesting part is seek detection without a seek event. A dropped frame, a device stall or a slow network can move the playhead without the browser reporting a seek. The fix is arithmetic: if the playhead moved further than wall-clock time and the playback rate could explain, continuity is broken and the segment must close.

const wallDeltaSec = (now - lastTickWall) / 1000;
const maxAllowedDelta = Math.max(0.35, wallDeltaSec * playbackRate * 1.35);
const isUnreportedJump = Math.abs(delta) > maxAllowedDelta;

if (isUnreportedJump) {
  closeActiveSegment(); // continuity is broken
  transition("SEEKING");
}

The 1.35 tolerance exists because a legitimate jump is faster than real time by exactly the playback rate, and stutters add noise on top.

The client also has a durable outbox: IndexedDB primary, a coalesced localStorage mirror, bounded at 300 records and 7 days, oldest evicted first. The server's per-segment verdict is what removes a record — the client never decides that a write landed.

The repair engine

A projection can drift, so drift is treated as a first-class state:

audit (read-only)
  → dry run (show what would change)
    → bounded apply (keyset paged, per-subject savepoint)
      → verify (re-audit; delta must now be 0)

A repair that locks a million rows for an hour is not a repair, it is an outage. The engine walks (student, subject_type, subject_id) in ascending keyset order, holds at most one page of locks at a time, and returns a cursor so an interrupted run resumes where it stopped. Each subject is repaired inside its own savepoint, so one bad row is reported and skipped instead of aborting the batch.

The strongest check is the simplest: drop the projection and rebuild it from the log. If the rebuilt numbers match the incremental path, that path is correct. If they do not, it has a bug — and this is the operation that finds it.

Failure handling

Every row below is a state the system is designed to encounter, not a hypothetical.

The permanent-versus-retryable split in rejection reasons is deliberate. A permanent rejection is dropped and the client is told to forget it. A retryable rejection means the segment is fine and the batch was too big — the client splits and resends. Getting this backwards is expensive in both directions: rejecting a legitimate segment loses real watch time, and accepting an impossible one lets a client inflate its own metrics.

Trade-offs

• No authentication in the engine. Identity fields are dimensions of evidence, not permission claims. A gateway or product layer adds access control without changing the measurement path. Access control answers who may watch what; this engine answers what was watched. Coupling them would make analytics depend on a player provider, a token format or an entitlement system — none of which belong in the number.
• SQLite with node:sqlite. Zero provisioning, no Docker, no migration ledger. The file is created on first boot from one idempotent DDL script. For this write volume and a single-node demo, that is less machinery than a migration system, and it cannot get out of sync. Postgres is the obvious next step at higher concurrency.
• No migrations folder. A migration system exists to evolve a schema across deployments. This schema is created at boot, and the only evolution that matters — adding a column — is covered by ALTER TABLE … ADD COLUMN IF NOT EXISTS. The trade is deliberate: a migration ledger is real infrastructure with real failure modes.
• Duplicates are retained. That costs storage, but it buys the ability to explain every number.
• The in-process broker is real. It makes the demo runnable with zero infrastructure while still exercising partition, offset, lag, dead-letter and replay semantics.

Verification

The Vitest suite covers the watch math, incremental-versus-recompute equivalence, drift detection and repair, and a purity guard that fails if player-provider or credential coupling appears in app/, engine/ or server/.

The API scenarios double as a demo script:

1. Replay a heartbeat with the same Idempotency-Key → deduplicated: true, no write.
2. Replay the same segments under a new session id → duplicateSegmentIds, zero contribution.
3. Claim a segment beyond the authoritative duration → permanent rejection.
4. Claim 60 seconds of content in 10 seconds of wall clock → permanent plausibility rejection.
5. Send a 200-second segment in a 120-second budget → retryable rejection; split and resend.
6. Send a heartbeat after finalisation → 409 SESSION_ENDED, terminal, client replays elsewhere.
7. Run ?op=audit → read-only drift report.
8. Run ?op=repair with dryRun, then apply → drift shown, repaired, verified.
9. Run ?op=rebuild → projection deleted and rebuilt from evidence; numbers must match.
10. ?op=broker → relay and consumer-group stats; the broker is observable but outside the write path.

Steps 7–9 are the differentiator. A projection that can prove itself wrong, then repair itself in bounded pages, is a different class of system from a counter.

What I would do next

• Move the store to Postgres and partition watch_segments by time, keeping the same SQL contracts.
• Add alerting on broker lag, drift delta and rejection-rate changes, not just on errors.
• Put authentication and entitlement at the gateway, leaving the measurement path untouched.
• Publish an OpenAPI contract and contract tests for the heartbeat verdict.
• Run a k6 load test against the hot path to find the write ceiling before the next product surface does.
• Add retention and compaction for resolved duplicates and published outbox rows.

What this project demonstrates

The interesting work is not the endpoint that accepts a heartbeat. It is deciding what the client is allowed to mean, making retries safe at two levels, keeping a derived read model honest, and building the repair path before it is needed. The number is the product; everything else exists to make that number survive a client that lies, a network that retries and a cache that drifts.