Persist Live Detections and Roll Them Into an Hourly Mart

Suggested cover: live detection rows landing in PostgreSQL before an hourly aggregation step promotes them into history.

Persistence becomes easier to trust when write paths stay narrow

The database side of a live computer vision script only feels messy when multiple concerns are hidden inside the same write path. In practice there are two jobs here: save the current rows, then roll them up into a calmer historical shape.

Those jobs can live in the same file without becoming the same function. The live insert path should stay close to the row structure already produced by the detector, while the mart refresh can stay close to SQL and timing.

That division matters even more in articles because it lets the reader separate the operational write loop from the analytical history layer. One supports immediacy. The other supports reporting.

           # WRITE TO POSTGRES
           if db_ok:
               try:
                   cur.execute("""
                       INSERT INTO mart.current_traffic
                       (camera_id, vehicle_type, track_id, x_coord, y_coord, avg_speed, count, last_update)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
                   """, ('Cam_01', label, int(track_id), float(x_c), float(y_c), float(speed), 1))
                   conn.commit()
               except:
                   conn.rollback()

This adjacent component shows the row shape in isolation so the real insert path does not have to explain its tuple structure at the same time.

A small packing helper like that is not required, but it highlights a useful mental model: if the row shape is stable, the write function can remain small.

       # Convert the frame back to OpenCV
       frame = cv2.cvtColor(np.array(img_pil), cv2.COLOR_RGB2BGR)
   # Display output
   cv2.imshow("Smart Traffic System", cv2.resize(frame, (1280, 720)))
   if cv2.waitKey(1) & 0xFF == ord("q"):
       break

The fifth marked slice is the narrow write path. It keeps the insert statement visible and leaves the transaction rule in one easy-to-find place.

Persistence term	Meaning	Why it belongs here
Current table	The table that stores fresh tracked rows.	It is the operational landing zone for the live pipeline.
History table	The rolled-up hourly view of the same traffic.	It gives dashboards a steadier reporting layer.
Rollback	The fallback after a failed insert attempt.	It keeps a bad write from poisoning the next one.

Alternative component: a tiny transaction wrapper

cap.release()
if db_ok: cur.close(); conn.close()
cv2.destroyAllWindows()

This component is intentionally nearby and color-separated. It reads like a neighboring design option rather than part of the stored numbered flow.

The hourly rollup deserves its own stage because it has a different rhythm. It is not called for every object or even for every frame. It turns a live stream into a reporting cadence.

CREATE SCHEMA IF NOT EXISTS mart;

CREATE TABLE IF NOT EXISTS mart.current_traffic (
    id SERIAL PRIMARY KEY,
    camera_id VARCHAR(50) NOT NULL,
    vehicle_type VARCHAR(50) NOT NULL,
    track_id INTEGER NOT NULL,
    x_coord FLOAT NOT NULL,
    y_coord FLOAT NOT NULL,
    avg_speed FLOAT,
    count INTEGER DEFAULT 1,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The sixth marked slice keeps the SQL-heavy refresh logic together, including the repeat cadence and the upsert behavior.

This pairing works because the first function writes what is happening now, while the second keeps history queryable later. They belong to the same pipeline, but they speak to different time horizons.

• Keep the live insert path close to the row shape produced by the detector.
• Let history refresh logic stay mostly SQL so the mart rule remains inspectable.
• Use a simple cadence for rollups because operational clarity often beats micro-optimization here.