Condor AI Detections

Condor is the AI that watches the camera feeds and says "there's smoke on that ridge." When it fires, that's not a single fact — it's an event (something is happening, seen by one or more cameras) with a set of detections underneath it (the individual boxes the model drew, each with a score and a geolocation) and annotations (the segmentation detail). Storing that faithfully, writing it fast, and reading it back whole is what the Condor SQL layer is for — and it's central enough to the product to deserve its own section.

The one idea to hold on to

A Condor detection is a three-level graph — event → COCO detections → COCO annotations — and it's stored across three SQL tables but read back in a single round trip. The read stored procedure returns three result sets, and QueryMultipleAsync stitches them into assembled events in memory:

using var multi = await sqlConnection.QueryMultipleAsync(commandDefinition);
var events      = (await multi.ReadAsync<CondorCameraEvent>()).ToList();
var detections  = (await multi.ReadAsync<COCODetection>()).ToList();
var annotations = (await multi.ReadAsync<COCOAnnotation>()).ToList();

foreach (var evt in events) {
    evt.CocoDetections  = detections.Where(d => d.CondorCameraEventId == evt.Id).ToList();
    evt.CocoAnnotations = annotations.Where(a => a.CondorCameraEventId == evt.Id).ToList();
}

One database call, one fully-hydrated object graph. The relational shape is the right fit here precisely because it is relational: one event owns many detections, each detection has annotations, and you want to query across them by device, date range, and confidence.

Gotcha: the three result sets must be read in order

QueryMultipleAsync is positional — the code reads events, then detections, then annotations, matching the order the stored procedure SELECTs them. If someone reorders the SELECTs in the proc without reordering the ReadAsync<T> calls, Dapper will try to map detections onto CondorCameraEvent and fail (or worse, map the wrong columns). The proc and the repository are one contract; change them together.

Writing: everything goes in by table-valued parameter

Condor writes are bulk by nature — a single event can carry many device ids and dozens of detections — so nothing is inserted a row at a time. Device ids ride in on the StringList TVP; detections and annotations use their own COCODetectionList and COCOAnnotationList types, built from a DataTable:

Detections = BuildDetectionDataTable(detections).AsTableValuedParameter("COCODetectionList")

CreateCameraEventAsync, UpdateCameraEventAsync, and UpsertCameraEventAsync all pass DeviceIds this way; UpsertCameraEventAsync keys on the external CondorCameraEventId so a re-reported event updates in place instead of duplicating.

Gotcha: bulk insert is delete-then-insert, not merge

COCODetectionRepository.BulkInsertAsync and the annotation equivalent delete the existing rows for the event and then insert the new set. That's what makes a re-processed event idempotent — but it also means a failure partway through leaves the event with its old detections gone and the new ones not yet written. It's a replace, not an append; treat the whole bulk insert as one unit of work.

The Cosmos side, and how detections arrive

The SQL tables are the durable, queryable record. There's also a Cosmos CondorEventRepository holding real-time event state with a Ttl so Cosmos expires it automatically — the fast, ephemeral view versus SQL's system of record. Its container name is the one hardcoded quirk called out in the Cosmos lesson ("condorEvent", not from config).

Detections enter the system through the ProcessAiDetections queue (see One Service, Nine Queues): a consumer dequeues a detection payload, and the bulk-insert path above lands it in SQL. The AddCondorClient extension wires the HTTP client that talks to the Condor API itself.

Wrapping up

A Condor detection is an event with COCO detections and annotations beneath it, stored in three SQL tables and read back as one assembled graph in a single QueryMultipleAsync round trip. Writes are always bulk, through table-valued parameters, and the COCO bulk insert is a delete-then-insert replace. SQL is the durable record; the Cosmos condorEvent container is the TTL'd real-time mirror, fed via the ProcessAiDetections queue.

Next, the business logic that sits on top of the data layer: The Alert Lifecycle.