Ship Live Metrics Into PostgreSQL, Fan Them Out, and Keep a Debug Overlay Nearby

Suggested cover: a metric insert stream, a queue handoff diagram and a compact on-frame debug overlay.

One frame summary can feed three consumers

Once a frame summary exists, the script needs to do three things with it quickly. It has to persist the data, make it available to the UI, and keep enough visual feedback nearby that a human can still sanity-check what the system is doing.

Those three concerns sound different, but they fit well together because they all derive from the same per-frame bucket. A good live pipeline avoids recomputing that summary and simply fans it out to the next consumers.

That fan-out idea is worth dwelling on because it is where many small systems either become elegant or become noisy. If every downstream step rebuilds its own understanding of the frame, the code grows wide very fast. If they all reuse the same summary, the architecture stays compact.

Stage	What happens	Why it stays useful
Insert	The frame summary is stored in the metrics table.	Historical charts and batch exports need a durable record.
Queue handoff	The same summary is pushed to a shared queue.	A UI thread can react without stealing work from the detector loop.
Overlay	A small text layer is drawn onto the live frame.	Operators get immediate feedback during tuning or debugging.

A plain row-builder before the real persistence block

        if detections[0].boxes.id is not None:
            frame_stats = defaultdict(lambda: [0, 0])

            for box_coords, track_id, class_id in zip(
                detections[0].boxes.xyxy.cpu().numpy(),
                detections[0].boxes.id.cpu().numpy(),
                detections[0].boxes.cls.cpu().numpy(),
            ):
                left, top, right, bottom = map(int, box_coords[:4])
                center_x, center_y = (left + right) // 2, (top + bottom) // 2
                vehicle_name = DETECTED_CLASSES.get(int(class_id), "unknown")

This supplemental block makes the shared payload idea visible before the stored fragment reaches the database write.

                color = palette.get(vehicle_name, (255, 255, 255))
                cv2.rectangle(frame_image, (left, top), (right, bottom), color, 2)
                cv2.circle(frame_image, (center_x, center_y), 3, color, -1)
                cv2.putText(
                    frame_image,
                    f"{vehicle_name}",
                    (left, top - 5),
                    cv2.FONT_HERSHEY_SIMPLEX,
                    0.4,
                    color,
                    1,
                )

The insert fragment keeps the schema-facing write logic compact, which is helpful when you later need to inspect or replay the stream.

The database step should still look almost boring. That is usually a good sign. The interesting part already happened earlier when the frame bucket was built. Persistence is mostly about using that work carefully.

                if track_id in track_cache:
                    speed_value = (
                        (center_x - track_cache[track_id][0]) ** 2
                        + (center_y - track_cache[track_id][1]) ** 2
                    ) ** 0.5
                    frame_stats[vehicle_name][1] += speed_value

                track_cache[track_id] = (center_x, center_y, vehicle_name)
                frame_stats[vehicle_name][0] += 1

Here the same summary crosses the boundary into a queue. The data is still small, readable and close to the original frame context.

A queue is often the quiet hero in this kind of script. It lets the UI consume updates at its own pace without teaching the detection loop about dashboard timing, widget state or rendering details.

            for vehicle_name, (vehicle_count, speed_total) in frame_stats.items():
                mean_speed = speed_total / (vehicle_count + 1)
                metrics_cursor.execute(
                    "INSERT INTO stream_metrics (time, vehicle_type_id, vehicle_class, intensity, avg_speed) VALUES (to_timestamp(%s), %s, %s, %s, %s)",
                    (
                        unix_time,
                        resolve_vehicle_type_id(vehicle_name),
                        vehicle_name,
                        vehicle_count,
                        mean_speed,
                    ),
                )

This extra helper is a tutorial-side way to talk about overlay policy without touching the recoverable fragment.

                event_buffer.put(
                    {
                        "time": unix_time,
                        "datetime": datetime.now(),
                        "vehicle_type": vehicle_name,
                        "intensity": vehicle_count,
                        "avg_speed": mean_speed,
                        "frame": frame_index,
                    }
                )

The last block keeps the human-visible overlay and the worker cleanup together because both belong to the end of the frame cycle.

This is also why the overlay is worth keeping during development, even if it disappears later in a production run. A compact on-frame summary gives you immediate feedback about whether the numbers being written and queued still match the image on screen.

• Write once from the frame bucket instead of recalculating per consumer.
• Push only the fields the UI really needs into the queue.
• Keep a tiny overlay around during development because it catches wrong assumptions very quickly.