Database

Local Development

Run a local SQL Server instance with Docker:

docker run --name sql2022 \
  -e 'ACCEPT_EULA=Y' \
  -e 'MSSQL_SA_PASSWORD=<password>' \
  -p 1433:1433 \
  -d mcr.microsoft.com/mssql/server:2022-latest

Build and deploy the schema:

cd src/data/app
dotnet build
SqlPackage /Action:Publish \
  /SourceFile:"data.app.dacpac" \
  /TargetUser:sa \
  /TargetPassword:<password> \
  /TargetServerName:. \
  /TargetDatabaseName:Formation \
  /TargetEncryptConnection:False \
  /v:environment=dev \
  /p:BlockOnPossibleDataLoss=False

Schema Overview

The database uses a relational model with the following core tables:

Table	Description
Scheme	Property schemes
Development	Developments within schemes
Company	Company directory
Address	Physical addresses with geometry
AuditLog	Change audit trail

Geometry

Spatial data is stored using SQL Server’s geometry type and accessed via NetTopologySuite in EF Core. The API exposes geometry as WKT (Well-Known Text) strings.

-- Example: Find schemes within 5km of a point
SELECT * FROM Scheme s
JOIN Address a ON s.AddressId = a.AddressId
WHERE a.Location.STDistance(geometry::Point(51.5074, -0.1278, 4326)) < 5000

Full-Text Search

Full-text indexing is enabled on key fields. The full-text catalogue is defined in the SQL project under FullTextCatalogs/.

Audit Logging

All write operations are automatically logged via IAuditLogger:

Column	Description
TableName	Entity type
OperationType	INSERT, UPDATE, DELETE, MERGE
UserName	From JWT claims
PreviousValue	JSON of old state
NewValue	JSON of new state