Finding the Moment

In one line: the archive is only worth what it costs if someone can locate the thirty seconds that matter.

Two different questions

Operators ask two things, and they need different machinery.

"Show me camera 17, last Tuesday, 3pm to 3:30." Camera and time are both known. This is a range lookup, it must be fast, and it is the common case.

"Who entered the loading dock after hours?" Neither the camera nor the time is known — a description of content is. This cannot be answered by an index over segments at all; it needs analytics metadata produced when the footage was recorded.

Designs that conflate these end up either building a search engine for a problem that needs a B-tree, or promising content search from an index that only knows timestamps.

The segment index

The first query is a time-range lookup keyed by camera, and the schema is small:

segments
  camera_id     text
  started_at    timestamptz
  ended_at      timestamptz
  object_key    text
  bytes         bigint
  codec         text
  has_motion    boolean
  PRIMARY KEY (camera_id, started_at)

The primary key is the design. Ordering by (camera_id, started_at) puts every segment for one camera contiguous and time-sorted, so a range scan is a seek plus a sequential read:

SELECT object_key, started_at, ended_at
FROM segments
WHERE camera_id = 'cam-0417'
  AND ended_at   > '2026-08-26T15:00:00Z'
  AND started_at < '2026-08-26T15:30:00Z'
ORDER BY started_at;

Note the overlap comparison rather than BETWEEN. Segments have real durations, and a segment that starts before the window and ends inside it contains footage the operator asked for. Getting this wrong truncates the first few seconds of every playback, which is exactly the moment someone was looking for.

Sizing it: 648 million rows at roughly 150 bytes is under 100 GB — small, ordinary, and cheap next to the media. Partition by time so that expiring a day is a partition drop rather than a mass delete, which matters when retention is deleting 21,600 rows per camera per day.

Playback is a manifest, not a file

There is no file to hand the player. Thirty minutes of footage is a few hundred segment objects, and playback works by generating a manifest listing them in order with their real durations.

Two consequences worth naming.

Gaps must be represented, not skipped. If the camera was offline for four minutes, the manifest needs a discontinuity marker rather than a silent jump. An operator who sees 3:04 follow 3:00 with no indication will testify that nothing happened in between, and that is a serious thing to get wrong. The player should render the gap visibly on the timeline.

URLs are issued per request and expire. The player fetches objects directly from storage, so the manifest carries short-lived signed URLs. Never expose durable object URLs — a leaked manifest would otherwise be a permanent key to that footage.

Scrubbing

Dragging along a timeline is the hardest read pattern in the system. Each new position needs a different segment fetched and decoded, and users scrub fast.

Three things make it usable, none of which involve making the archive faster:

• A sparse thumbnail track. One small JPEG every few seconds, written at ingest, is a few kilobytes against megabytes of video. Scrubbing shows thumbnails and only loads real video when the user stops. This is the main trick, and it is cheap because thumbnails are tiny and generated once.
• Query the index, not storage. The timeline's shape — where footage exists, where the gaps are, where motion occurred — comes from the index, so the whole timeline renders without touching a single video object.
• Fetch on settle. Debounce the actual segment fetch until the drag stops, so a two-second scrub does not issue two hundred requests.

The content query

The second question needs metadata that only exists if something produced it at ingest.

The cheapest useful signal is motion, and the camera often provides it for free via ONVIF events — no analysis on your side. Richer signals (person, vehicle, license plate, direction) come from an analytics consumer reading the control plane, fetching the small fraction of segments it needs, and writing structured records back.

This is where the media-plane split pays off again: analytics subscribes to segment events, decides what to look at, and pulls only those bytes. It never sits in the ingest path, so it can be slow, restarted, backfilled or replaced without any risk to recording.

The detections table is the thing that makes an archive searchable, and it is worth stating that it is optional. A system with a segment index and no analytics still answers the common query perfectly well; a system with analytics and a weak index answers neither.

Next: the failure that matters most, which is the one nobody notices.