Azure SQL Database

Azure SQL Database is the relational store for structured data — locations with geospatial geometry, camera assignments, alert subscriptions, user/agency RBAC, notifications, audit events, activity logs, and feature flags. Data access uses Dapper as a micro-ORM with stored procedures for complex operations.

Registration

services.AddAlertCASQLDatabaseServices(configuration);

This registers:

ISqlConnectionFactory (singleton) — creates SqlConnection objects from the connection string
19 SQL repositories (scoped)
IGeometryService and ILocationService (scoped)

Configuration

{
  "SqlDatabaseOptions": {
    "ConnectionString": ""
  },
  "LocationOptions": {}
}

Architecture

Base Class: `SqlRepository`

All SQL repositories inherit from SqlRepository, which provides:

Method	Description
`GetByQueryAsync<T>()`	Generic query returning all records
`GetByIdAsync<T>(int id)`	Get single record by ID
`BuildStringDataTable(List<string> list)`	Helper for building table-valued parameters for stored procedures

All queries use ISqlConnectionFactory to create connections and Dapper for execution.

Repositories

LocationRepository

Manages geographic locations with WKT (Well-Known Text) geometry support. Locations represent boundaries such as counties, cities, CALFIRE districts, utility boundaries, and custom user-defined areas.

Interface: ILocationRepository

Method	Description
`CreateAsync(Location location)`	Create location with geometry validation
`CreateAsync(IEnumerable<Location> locations)`	Batch create
`UpdateAsync(Location location)`	Update with WKT validation
`DeleteAsync(int id)`	Delete location
`GetByIdAsync(int id)`	Get by ID
`GetAllByEntraIdAsync(string entraId)`	Get user's custom locations
`GetAllByLocationTypesAsync(List<LocationType> types)`	Filter by location type
`GetPredefinedLocationsAsync(List<string> types)`	Get system-defined locations
`GetIntersectingLocationsAsync(int sourceLocationId)`	Find locations that overlap the source (uses SQL Server geometry)

All create/update operations validate WKT geometry via IGeometryService before persisting.

AlertSubscriptionRepository

Manages user alert subscriptions with geographic location associations.

Interface: IAlertSubscriptionRepository

Method	Description
`CreateAsync(AlertSubscription subscription)`	Create subscription
`UpdateAsync(AlertSubscription subscription)`	Update subscription
`DeleteAsync(int id)`	Delete subscription
`GetAllByEntraUserIdAsync(string entraUserId)`	Get user's subscriptions with locations
`GetByIdAsync(int id)`	Get by ID
`TogglePauseAsync(string entraUserId, bool isPaused)`	Pause/resume all user subscriptions
`CreateSubscriptionLocationAsync(AlertSubscriptionLocation location)`	Add location to subscription
`GetIntersectingSubscribers(int sourceLocationId)`	Find subscriptions covering a location
`GetAlertSubscriptionLocationsByAlertSubscriptionId(int)`	Get locations for a subscription
`DeleteAlertSubscriptionLocationAsync(int subId, int locId)`	Remove location from subscription

CameraSqlRepository

Manages camera RBAC — camera records plus user-camera and agency-camera assignments.

Interface: ICameraSqlRepository

Method	Description
`CameraInsertAsync(CameraInsert insert)`	Create camera record
`CamerasAll()`	Get all cameras
`AgencyCamerasAll()`	Get all agency-camera assignments
`UserCamerasAll()`	Get all user-camera assignments
`UserCameraInsertAsync(UserCameraInsert insert)`	Assign camera(s) to user
`UserCameraDeleteAsync(UserCameraInsert insert)`	Unassign camera(s) from user
`UserCamerasByEntraIdAsync(string entraUserId)`	Get user's cameras (direct + via agency)
`AgencyCameraInsertAsync(AgencyCameraInsert insert)`	Assign camera(s) to agency
`AgencyCameraDeleteAsync(AgencyCameraInsert insert)`	Unassign camera(s) from agency

CameraLeaseRepository

Manages camera lease/control sessions — when a user takes control of a camera for PTZ operations.

Interface: ICameraLeaseRepository

Method	Description
`CreateAsync(CameraLeaseInsert lease)`	Create lease
`UpdateAsync(CameraLease lease, int id)`	Update lease end time
`LatestByCameraIdAsync(string cameraId)`	Get active lease for camera
`LatestByEntraUserId(string entraUserId)`	Get user's active lease
`CameraLeaseAndMovementLatestByCameraIdAsync(string cameraId)`	Get lease with movement info
`CreateLeaseAndMovement(CameraLeaseAndMovementInsert insert)`	Atomic create lease + movement
`ExecuteLeaseProcess(CameraLeaseAndMovementInsert insert)`	Full lease process via stored procedure

CameraMovementRepository

Tracks camera PTZ movement history — pan, tilt, zoom, focus, and brightness changes.

Interface: ICameraMovementRepository

Method	Description
`CreateProcedureAsync(CameraMovementInsert insert)`	Record movement via stored procedure
`LatestByCameraIdAsync(string cameraId)`	Get most recent movement with reason
`GetAllByCameraIdAsync(string cameraId)`	Full movement history
`GetLatestPositionByCameraIdAsync(string cameraId)`	Get latest PTZ position (aggregated)
`GetLatestByCameraIdsAsync(List<string> cameraIds)`	Bulk get latest movements

UserAgencySqlRepository

Manages user and agency master data and their associations.

Interface: IUserAgencySqlRepository

Method	Description
`UserInsertAsync(UserInsert insert)`	Create/activate user
`UserDeleteAsync(string entraUserId)`	Deactivate user
`AgencyInsertAsync(AgencyInsert insert)`	Create agency
`AgencyDeleteAsync(string name)`	Deactivate agency
`UserAgencyInsertAsync(UserAgencyInsert insert)`	Assign user to agency
`UserAgencyDeleteAsync(UserAgencyInsert insert)`	Remove user from agency
`UserAll()`	Get all users (obsolete — use `UserSqlRepository.UserAll`)
`AgencyAll()`	Get all agencies
`UserAgencyAll()`	Get all user-agency assignments

AlertMessageRepository

Creates alert messages via stored procedure with associated locations.

Interface: IAlertMessageRepository extends ISqlRepository — CreateAsync(AlertMessage). Inherits GetByIdAsync<T>(int) from the base.

AuditEventRepository

Tracks system audit events with transaction grouping.

Interface: IAuditEventRepository — InsertAuditAsync(AuditEvent), GetByActionNameAsync(string), GetByEntraUserIdAsync(string), GetByTransactionIdAsync(string)

ActivityLogRepository

Logs user activity related to cameras.

Interface: IActivityLogRepository — AddMessage(ActivityLogInsert), GetLatestMessagesByCamera(string, int?)

FeatureFlagRepository

Manages feature flags.

Interface: IFeatureFlagRepository — GetAllAsync(), InsertFeatureFlagAsync(string, bool), UpdateIsEnabledAsync(bool, string)

NotificationRepository

Manages notification records.

Interface: INotificationRepository extends ISqlRepository

Method	Description
`CreateAsync(Notification notification)`	Create notification
`UpdateAsync(Notification notification)`	Update notification
`GetRecipientsByNotificationId(int notificationId)`	Get notification recipients as `UserNotificationDTO`

UserNotificationRepository

Maps notifications to recipient users.

Interface: IUserNotificationRepository — CreateAsync(List<UserNotification>) (batch insert)

UserSqlRepository

Provides user lookups from the SQL database.

Interface: IUserSqlRepository — GetUsersByEntraUserIdsAsync(List<string>), UserAll()

AlertMessage Junction Repositories

Three repositories manage the many-to-many relationships between alert messages and their associated recipients:

Repository	Interface	Methods
`AlertMessageUserRepository`	`IAlertMessageUserRepository`	`CreateAsync(List<AlertMessageUser>)`, `GetByAlertMessageIdAsync(int)`
`AlertMessageAgencyRepository`	`IAlertMessageAgencyRepository`	`CreateAsync(List<AlertMessageAgency>)`, `GetByAlertMessageIdAsync(int)`
`AlertMessageLocationRepository`	`IAlertMessageLocationRepository`	`CreateAsync(List<AlertMessageLocation>)`, `GetByAlertMessageIdAsync(int)`

CondorCameraEventRepository

Manages Condor AI camera event records in SQL Server — creates, updates, upserts, and queries smoke/fire detection events from the Condor API. Supports QueryMultipleAsync to return events with their associated COCO detection and annotation data in a single database call.

Interface: ICondorCameraEventRepository

Method	Description
`GetCameraEventsByDeviceIdAsync(string deviceId)`	Get events by device ID with COCO detections and annotations (QueryMultiple)
`CreateCameraEventAsync(CondorCameraEvent cameraEvent)`	Create a new camera event with device IDs (TVP)
`UpdateCameraEventAsync(CondorCameraEvent cameraEvent)`	Update an existing camera event
`UpsertCameraEventAsync(CondorCameraEvent cameraEvent)`	Insert or update a camera event by CondorCameraEventId
`GetCameraEventsByDeviceIdAndDateRangeAsync(string deviceId, DateTime startDate, DateTime endDate)`	Query events by device ID within a date range

COCODetectionRepository

Manages COCO-format object detection results from Condor AI. Uses table-valued parameters (COCODetectionList) for efficient bulk insertion.

Interface: ICOCODetectionRepository

Method	Description
`BulkInsertAsync(int condorCameraEventId, List<CocoDetectionDTO> detections)`	Delete existing and bulk insert detections for a camera event using TVP

COCOAnnotationRepository

Manages COCO-format annotation data from Condor AI, including segmentation masks and area calculations. Uses table-valued parameters (COCOAnnotationList) for efficient bulk insertion.

Interface: ICOCOAnnotationRepository

Method	Description
`BulkInsertAsync(int condorCameraEventId, List<CocoAnnotationDTO> annotations)`	Delete existing and bulk insert annotations for a camera event using TVP

Geospatial Services

GeometryService

Handles geometric operations using NetTopologySuite and ProjNET for coordinate transformations:

Method	Description
`GetGeometryFromWKT(string wkt)`	Parses WKT strings into geometries
`ConvertGeoJsonToWKT(string geojson)`	GeoJSON → WKT
`ConvertWKTtoGeoJson(string wkt)`	WKT → GeoJSON
`GeometriesOverlap(...)`	Checks if two geometries intersect (3 overloads)
`GetCircleFromPoint(double lat, double lon, double radiusMiles)`	Creates accurate circles with latitude-aware degree conversion
`AddBuffer(Geometry geometry, double bufferMiles)`	Buffers geometries
`ValidateGeometryFromWKT(string wkt)`	Validates WKT geometry
`ValidateGeometryFromGeoJson(string geojson)`	Validates GeoJSON geometry

LocationService

Manages location data import from GeoJSON files and ESRI entities. Supports 18+ location types including CALFIRE districts, counties, cities, utility boundaries, forests, and more.

Method	Description
`ImportLocationsFileAsync(...)`	Downloads and processes GeoJSON files from blob storage
`ConvertESRIEntityToLocationAsync(...)`	Transforms ESRI polygons to locations
`GetPredefinedLocationsAsync(List<string> types)`	Returns grouped predefined locations

DACPAC Database Project

The Alert.CA.Infrastructure.Data project is a SQL Server database project (.sqlproj) that produces a DACPAC for deployment:

26 tables covering cameras, locations, users, agencies, alerts, notifications, audit, feature flags, Condor AI camera events, and COCO detection/annotation data
49 stored procedures for complex operations (e.g., AlertSubscriptionInsert, CameraLeaseProcessProc, LocationGetIntersectingLocations, CondorCameraEventUpsert, COCODetectionBulkInsert)
User-defined types for table-valued parameters (StringList, COCODetectionList, COCOAnnotationList)
Pre/post deployment scripts for data initialization

The DACPAC is deployed via the CI/CD pipeline to Azure SQL Database.

Registration​

Configuration​

Architecture​

Base Class: SqlRepository​

Repositories​

LocationRepository​

AlertSubscriptionRepository​

CameraSqlRepository​

CameraLeaseRepository​

CameraMovementRepository​

UserAgencySqlRepository​

AlertMessageRepository​

AuditEventRepository​

ActivityLogRepository​

FeatureFlagRepository​

NotificationRepository​

UserNotificationRepository​

UserSqlRepository​

AlertMessage Junction Repositories​

CondorCameraEventRepository​

COCODetectionRepository​

COCOAnnotationRepository​

Geospatial Services​

GeometryService​

LocationService​

DACPAC Database Project​