The Relational Core

A subscriber draws a circle on the map — "alert me within 5 miles of this ridge" — and later a fire is reported a few miles away. Deciding whether that fire falls inside that circle is a geometry question, and geometry questions are exactly what a relational database with spatial types is good at. That's the job of the SQL layer: the structured, queryable, overlap-aware half of the platform — locations, RBAC, alerts, movement history, audit, and feature flags.

The one rule that shapes every SQL repository

The C# library never sends ad-hoc SQL. It calls stored procedures, through Dapper. Every repository method opens a fresh connection, names a stored procedure, passes parameters, and maps the result onto a model. That's the whole pattern:

using SqlConnection conn = SqlConnectionFactory.GetConnection();
var command = new CommandDefinition(
    commandText: Constants.DBO_LOCATION_GET_BY_ID,   // "dbo.LocationGetById"
    commandType: CommandType.StoredProcedure,
    parameters: new { Id = id },
    cancellationToken: ct);
return await conn.QueryFirstOrDefaultAsync<Location>(command);

Two things make this safe and repeatable. The connection comes from ISqlConnectionFactory (a singleton that hands out fresh SqlConnection objects — you using them, never cache them), and every repository inherits SqlRepository for the shared helpers (GetByIdAsync<T>, GetByQueryAsync<T>, and the table-valued-parameter builder). The schema itself — 26 tables, 49 stored procedures, the user-defined types — lives in a separate database project and ships as a DACPAC.

Gotcha: a stored-proc name lives in two places that must agree

Each procedure is named once in its .sql file and again as a constant in Utilities/Constants.cs (DBO_LOCATION_GET_BY_ID = "dbo.LocationGetById"). Dapper sends the constant's string. If the two drift, you get a runtime "could not find stored procedure" error, never a compile error. And enums are stored as strings — pass them as .ToString() and register a DapperStringEnumTypeHandler<TEnum>, or the map comes back empty.

The interesting half: geometry

Most of the 19 repositories are thin proc calls you can read in a minute. The part that isn't obvious is GeometryService — the NetTopologySuite/ProjNET layer that turns map shapes into things SQL Server can compare. Two things create a location's shape, and it's worth being clear which is which:

• Complex polygons come from the frontend. When a user draws an arbitrary area on the map, it arrives as WKT or GeoJSON — any shape, however many vertices — and GeometryService parses and validates it, then it's stored as-is. This is the general case, and the shape is whatever the user drew.
• The circle is the built-in shape you get from a point and a radius. When all you have is a center and a distance (an anomaly reported at a lat/lon, say), there's no polygon to store, so GetCircleFromPoint generates one. It's a convenience for the point+radius case, not the only kind of area.

That generated circle hides a subtlety worth feeling for yourself. You can't just treat "5 miles" as a fixed number of degrees, because a degree of longitude is ~69 miles at the equator but shrinks toward the poles. So the same physical radius covers more degrees the farther north you go. GetCircleFromPoint corrects for this with the actual latitude before it buffers. Run it across a few latitudes and watch the same 5 miles buy more degrees going north:

MilesToDegreesAccurate — mirrors Services/Locations/GeometryService.cs

// Mirrors GeometryService.MilesToDegreesAccurate — verify against source if this looks stale.
  function milesToDegrees(miles, latitude) {
    const meters = miles * 1609.344;
    const earthRadiusMeters = 6378137.0;
    const latRad = latitude * (Math.PI / 180);
    // degreesPerMeter grows as cos(latitude) shrinks toward the poles
    const degreesPerMeter = 1 / (earthRadiusMeters * Math.cos(latRad) * (Math.PI / 180));
    return meters * degreesPerMeter;
  }

  const miles = 5;
  const lats = [0, 34, 40, 49];
  const lines = lats.map(function (lat) {
    return 'lat ' + lat + ': ' + milesToDegrees(miles, lat).toFixed(5) + ' deg for ' + miles + ' mi';
  });
  render(<pre style={{margin: 0}}>{lines.join('\n')}</pre>);

Output

At the equator (0°) five miles is one number of degrees; at California's 34–42° it's visibly larger, and the circle is built with 64 segments at that corrected radius. Get this wrong and every proximity alert near the state's northern edge is subtly too small.

What lives in the SQL store

The repositories group into a few concerns, and the full method list for each is one click away in source — reach for it rather than a table here:

• Locations & alerts — LocationRepository, AlertSubscriptionRepository, AlertMessageRepository and its three junction repositories (user / agency / location).
• RBAC — CameraSqlRepository (user↔camera, agency↔camera), UserAgencySqlRepository.
• Camera activity — CameraMovementRepository, CameraLeaseRepository, ActivityLogRepository.
• Platform — NotificationRepository, AuditEventRepository, FeatureFlagRepository, UserSqlRepository.

The Condor AI detection tables also live in SQL, but they're a domain of their own — event, COCO detections, and annotations read back as one graph — and get their own section: Condor AI Detections.

Gotcha: bulk inserts go through a user-defined type, not a loop

The COCO repositories don't insert detections one row at a time — they pass a List<…DTO> as a single table-valued parameter (COCODetectionList, COCOAnnotationList) built via the base class's BuildStringDataTable helper. Inserting in a loop instead works in tests and then melts under a real detection payload. If you add a bulk path, add a UDT.

Wrapping up

The SQL layer is Dapper calling stored procedures — never ad-hoc SQL — with the schema shipped as a DACPAC and the genuinely hard logic concentrated in GeometryService, where a physical radius has to be converted to degrees against real latitude before SQL Server can answer an overlap question. Keep the proc-name constants in sync, store enums as strings, and match the misspelled namespace.

Next, the metadata store with a trick of its own — one write, two tables: The Dual-Table Index Trick.