DATABASE DESIGN DOCUMENT

Roundtrip · A Traxs Company Product · Version 1.0 · March 2026

SQL Server schema design, table definitions, indexing strategy, multi-tenancy data isolation, and migration approach for the Roundtrip platform.

Attribute	Value
Document Title	Roundtrip Database Design Document
Version	1.0 — Initial Release
Status	Draft — In Review
Database	SQL Server 2022+
ORM	Entity Framework Core 9 + Dapper (reporting queries)
Related Docs	Domain Model v1.0, SAD v1.0
Date	March 2026

01 — DESIGN PRINCIPLES

Design Principles

Principle	Applied As
Multi-tenancy first	Every tenant-scoped table has a TenantId (uniqueidentifier NOT NULL) column. EF Core Global Query Filter applies it on every query automatically. SQL Server Row-Level Security provides defence-in-depth.
No hard deletes	All tenant-owned entities use IsDeleted (bit) + DeletedAt (datetime2). Physical deletes never occur on tenant data. System tables may use hard deletes.
Temporal tables for audit	ServiceTickets, Invoices, InventoryItems, and Clients use SQL Server Temporal Tables. Full row history is maintained automatically with FOR SYSTEM_TIME queries.
Strongly typed GUIDs	All primary keys are uniqueidentifier (GUID). No integer identity keys — GUIDs prevent enumeration attacks and simplify distributed scenarios.
Owned types for value objects	EF Core Owned Entities map C# value objects (ServiceAddress, ContactInfo, Money) to columns in the parent table — no join tables for value objects.
Denormalisation for history	PartUsage stores ItemName and UnitCostAtTime as snapshot copies so historical records remain accurate even if the inventory item changes.
Indexes on every FK	Every foreign key column has a non-clustered index. Query plan analysis reviewed before every migration.
Schema per tier (Professional)	Professional tier tenants get a dedicated schema (e.g., tenant_acme.Tickets) within the shared database. EF Core schema name is resolved from TenantContext at runtime.

02 — SCHEMA OVERVIEW

Schema Overview

Database Layout

Shared Database: roundtrip_shared
│
├── Schema: dbo (system / platform tables)
│   ├── Tenants
│   ├── HangfireJobs (Hangfire schema)
│   └── __EFMigrationsHistory
│
├── Schema: dbo (Standard tier — row-level TenantId isolation)
│   ├── ServiceTickets ← Temporal Table
|   |-- ServiceTicketHistory
│   ├── PartUsages
│   ├── TicketNotes
│   ├── TicketAttachments
│   ├── Clients ← Temporal Table
│   ├── ClientAddresses
|   |-- ClientHistory
│   ├── Technicians
│   ├── TenantUsers
|   |-- TenantSettings 
│   ├── InventoryItems ← Temporal Table
|   |-- InventoryItemsHistory
|   |-- PartUsages
│   ├── StockMovements
│   ├── Invoices ← Temporal Table
|   |-- InvoicesHistory
│   ├── InvoiceLineItems
│   ├── InvoiceDeliveries
│   ├── Quotes
│   ├── QuoteLineItems
│   ├── TechnicianRoutes
│   |── RouteStops
    |-- Notifications  
    |__ ReportJobs

Key Legend

Symbol	Meaning
PK	Primary Key — uniqueidentifier, clustered index
FK	Foreign Key — with non-clustered index on the FK column
NOT NULL	Column is required — cannot be null
NULL	Column is optional — nullable
TEMPORAL	Table has SQL Server Temporal Table history tracking (SysStartTime, SysEndTime)

03 — PLATFORM TABLES

Platform Tables

Tenants

The master tenant registry. Contains one row per registered business. Consulted by the tenant resolution middleware on every request.

Tenants — dbo schema — one row per registered business

Column	Type	Null	Key	Notes
TenantId	uniqueidentifier	NO	PK	NEWID() — primary key, used in all FK references
TenantSlug	nvarchar(30)	NO		URL-safe slug (e.g. acme-hvac). Unique across all tenants. Used in subdomain routing.
CompanyName	nvarchar(200)	NO		Display name of the Business
Plan	nvarchar(20)	NO		CHECK: Starter \| Standard \| Professional \| Enterprise
Status	nvarchar(20)	NO		CHECK: Active \| Suspended \| Cancelled \| Trial
IsolationModel	nvarchar(20)	NO		CHECK: SharedSchema \| DedicatedSchema \| DedicatedDatabase
SchemaName	nvarchar(60)	YES		NULL for SharedSchema; e.g. tenant_acme for DedicatedSchema
ConnectionString	nvarchar(500)	YES		NULL except for DedicatedDatabase tenants. Encrypted at application layer.
Timezone	nvarchar(60)	NO		IANA timezone (e.g. America/New_York). Used for scheduled jobs.
DefaultCurrency	nchar(3)	NO		ISO 4217 (e.g. USD, CAD, AUD)
LogoBlobKey	nvarchar(500)	YES		Blob storage key for tenant logo
TrialEndsAt	datetime2	YES		NULL for paid tenants. Trial expiry timestamp.
CreatedAt	datetime2	NO		UTC timestamp of tenant registration
IsDeleted	bit	NO		Soft delete flag. Default 0.
DeletedAt	datetime2	YES		UTC timestamp of soft deletion

Indexes: UQ: TenantSlug · IX: Status, Plan

04 — TICKETS TABLES

Tickets Tables

ServiceTickets — TEMPORAL TABLE — core work record

Column	Type	Null	Key	Notes
TicketId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK	FK → Tenants.TenantId. Part of composite index with Status.
TicketNumber	nvarchar(20)	NO		Format RT-YYYY-NNNNN. Unique within tenant.
ClientId	uniqueidentifier	NO	FK	FK → Clients.ClientId
ClientAddressId	uniqueidentifier	NO	FK	FK → ClientAddresses.ClientAddressId — service location for this ticket
TechnicianId	uniqueidentifier	YES	FK	FK → Technicians.TechnicianId. NULL = unassigned.
ServiceDescription	nvarchar(2000)	NO		Freetext description of work requested
Priority	nvarchar(20)	NO		CHECK: Low \| Normal \| High \| Urgent
Status	nvarchar(20)	NO		CHECK: Open \| Assigned \| InProgress \| OnHold \| Completed \| Cancelled
OnHoldReason	nvarchar(50)	YES		NULL unless Status = OnHold. CHECK: AwaitingParts \| CustomerNotHome \| Rescheduled \| AwaitingApproval
RequestedWindowStart	time	YES		Preferred arrival window start. NULL = no preference.
RequestedWindowEnd	time	YES		Preferred arrival window end.
CompletionNote	nvarchar(4000)	YES		Required when Status = Completed. Min 10 chars enforced at domain layer.
CreatedAt	datetime2	NO		UTC timestamp of ticket creation
AssignedAt	datetime2	YES		UTC timestamp of first assignment
StartedAt	datetime2	YES		UTC timestamp when technician set InProgress
CompletedAt	datetime2	YES		UTC timestamp of completion
IsDeleted	bit	NO		Soft delete. Default 0.
DeletedAt	datetime2	YES
SysStartTime	datetime2	NO		TEMPORAL: system-managed row validity start
SysEndTime	datetime2	NO		TEMPORAL: system-managed row validity end

Indexes: UQ: TenantId + TicketNumber · IX: TenantId + Status + CreatedAt DESC (dispatcher list query) · IX: TenantId + TechnicianId + Status (technician dashboard query) · IX: TenantId + ClientId (client history query)

PartUsages — Child records of ServiceTickets

Column	Type	Null	Key	Notes
PartUsageId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK	FK → Tenants. Redundant but required for EF Core Global Query Filter.
TicketId	uniqueidentifier	NO	FK	FK → ServiceTickets.TicketId
InventoryItemId	uniqueidentifier	NO	FK	FK → InventoryItems.InventoryItemId
ItemName	nvarchar(200)	NO		Denormalised snapshot of item name at time of use
UnitCostAmount	decimal(18,4)	NO		Snapshot of unit cost at time of use (Money VO — Amount)
UnitCostCurrency	nchar(3)	NO		Snapshot of currency at time of use (Money VO — Currency)
QuantityUsed	int	NO		CHECK: > 0
PhotoBlobKey	nvarchar(500)	YES		Optional photo evidence of part usage
RecordedByTechId	uniqueidentifier	NO	FK	FK → Technicians.TechnicianId
RecordedAt	datetime2	NO

Indexes: IX: TicketId · IX: TenantId + InventoryItemId

TicketNotes — Notes and internal comments on tickets

Column	Type	Null	Key	Notes
TicketNoteId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
TicketId	uniqueidentifier	NO	FK	FK → ServiceTickets
AuthorUserId	uniqueidentifier	NO		Keycloak UserId of the note author
AuthorName	nvarchar(200)	NO		Denormalised display name for history consistency
NoteText	nvarchar(max)	NO
IsInternalOnly	bit	NO		Default 0. Internal notes hidden from technician PWA.
CreatedAt	datetime2	NO

Indexes: IX: TicketId

TicketAttachments — File attachments linked to tickets

Column	Type	Null	Key	Notes
AttachmentId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
TicketId	uniqueidentifier	NO	FK
FileName	nvarchar(500)	NO
BlobKey	nvarchar(500)	NO		Storage key in blob storage (Azurite / S3-compatible)
ContentType	nvarchar(100)	NO		MIME type (e.g. image/jpeg, application/pdf)
FileSizeBytes	bigint	NO
UploadedByUserId	uniqueidentifier	NO
UploadedAt	datetime2	NO

Indexes: IX: TicketId

05 — CLIENTS TABLES

Clients Tables

Clients — TEMPORAL TABLE — business client records

Column	Type	Null	Key	Notes
ClientId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
CompanyName	nvarchar(200)	YES		NULL for residential clients
IsCommercial	bit	NO		Default 0. Required with CompanyName.
FirstName	nvarchar(100)	NO		ContactInfo VO — first name
LastName	nvarchar(100)	NO		ContactInfo VO — last name
Email	nvarchar(320)	YES		ContactInfo VO. At least one of Email or PrimaryPhone required (enforced at domain layer).
PrimaryPhone	nvarchar(30)	YES		ContactInfo VO
SecondaryPhone	nvarchar(30)	YES		ContactInfo VO
Notes	nvarchar(max)	YES		Persistent client-level notes (not ticket notes)
PreferredTechnicianId	uniqueidentifier	YES	FK	FK → Technicians. Default assignment suggestion.
IsArchived	bit	NO		Soft archive. Default 0.
ArchivedAt	datetime2	YES
CreatedAt	datetime2	NO
SysStartTime	datetime2	NO		TEMPORAL
SysEndTime	datetime2	NO		TEMPORAL

Indexes: IX: TenantId + IsArchived · IX: TenantId + Email (duplicate detection)

ClientAddresses — One-to-many service addresses per client

Column	Type	Null	Key	Notes
ClientAddressId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
ClientId	uniqueidentifier	NO	FK	FK → Clients.ClientId
Label	nvarchar(100)	NO		User-defined label e.g. 'Main Office', 'Warehouse'
IsPrimary	bit	NO		Exactly one per client must be true (enforced at app layer)
Street	nvarchar(300)	NO		ServiceAddress VO
City	nvarchar(100)	NO		ServiceAddress VO
StateProvince	nvarchar(100)	NO		ServiceAddress VO
PostalCode	nvarchar(20)	NO		ServiceAddress VO
Country	nchar(2)	NO		ServiceAddress VO — ISO 3166-1 alpha-2
Latitude	decimal(9,6)	YES		Populated on geocoding. NULL until geocoded.
Longitude	decimal(9,6)	YES		Populated on geocoding.
CreatedAt	datetime2	NO

Indexes: IX: ClientId · IX: TenantId + PostalCode (proximity queries)

06 — USERS TABLES

Users Tables

Technicians — Field staff — maps to Keycloak user in tenant realm

Column	Type	Null	Key	Notes
TechnicianId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
KeycloakUserId	uniqueidentifier	NO		Keycloak sub claim. Unique per tenant.
FirstName	nvarchar(100)	NO
LastName	nvarchar(100)	NO
Email	nvarchar(320)	NO
Phone	nvarchar(30)	YES
Status	nvarchar(20)	NO		CHECK: Available \| EnRoute \| OnSite \| Unavailable \| OffDuty
LastKnownLat	decimal(9,6)	YES		GpsCoordinate VO — latitude
LastKnownLng	decimal(9,6)	YES		GpsCoordinate VO — longitude
LocationUpdatedAt	datetime2	YES
ProfilePhotoBlobKey	nvarchar(500)	YES
IsActive	bit	NO		Default 1. Deactivated technicians cannot log in.
CreatedAt	datetime2	NO

Indexes: UQ: TenantId + KeycloakUserId · IX: TenantId + IsActive + Status

TechnicianSkills — Skills associated with each technician

Column	Type	Null	Key	Notes
TechnicianSkillId	uniqueidentifier	NO	PK
TechnicianId	uniqueidentifier	NO	FK
TenantId	uniqueidentifier	NO	FK
SkillName	nvarchar(100)	NO		e.g. HVAC, Plumbing, Electrical, Refrigerant Certified

Indexes: IX: TechnicianId

TenantUsers — Non-technician users: Dispatchers, BillingStaff, Admins

Column	Type	Null	Key	Notes
TenantUserId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
KeycloakUserId	uniqueidentifier	NO		Keycloak sub claim. Unique per tenant.
FirstName	nvarchar(100)	NO
LastName	nvarchar(100)	NO
Email	nvarchar(320)	NO
Role	nvarchar(30)	NO		CHECK: TenantAdmin \| Dispatcher \| BillingStaff \| ReadOnly
IsActive	bit	NO		Default 1.
CreatedAt	datetime2	NO

Indexes: UQ: TenantId + KeycloakUserId · UQ: TenantId + Email · IX: TenantId + Role

07 — INVENTORY TABLES

Inventory Tables

InventoryItems — TEMPORAL TABLE — parts and consumables catalogue

Column	Type	Null	Key	Notes
InventoryItemId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
Sku	nvarchar(50)	NO		SKU VO. Unique within tenant.
Name	nvarchar(200)	NO
Description	nvarchar(1000)	YES
Category	nvarchar(100)	YES		User-defined category (Filters, Refrigerant, Electrical, etc.)
UnitOfMeasure	nvarchar(30)	NO		each \| litre \| metre \| kg \| pair
UnitCostAmount	decimal(18,4)	NO		Money VO — amount
UnitCostCurrency	nchar(3)	NO		Money VO — ISO 4217 currency
QuantityOnHand	int	NO		CHECK: >= 0
ReorderThreshold	int	NO		CHECK: >= 0. Alert fires when `QuantityOnHand <= this value`.
Barcode	nvarchar(100)	YES
PhotoBlobKey	nvarchar(500)	YES
IsArchived	bit	NO		Default 0. Archived items cannot have stock consumed.
ArchivedAt	datetime2	YES
CreatedAt	datetime2	NO
SysStartTime	datetime2	NO		TEMPORAL
SysEndTime	datetime2	NO		TEMPORAL

Indexes: UQ: TenantId + Sku · IX: TenantId + Category · IX: TenantId + QuantityOnHand (low stock query)

StockMovements — Immutable audit log of every stock quantity change

Column	Type	Null	Key	Notes
StockMovementId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
InventoryItemId	uniqueidentifier	NO	FK
MovementType	nvarchar(20)	NO		CHECK: Consumption \| Adjustment \| Receipt
QuantityDelta	int	NO		Negative = removed, positive = added
QuantityAfter	int	NO		Snapshot of QuantityOnHand immediately after this movement
TicketId	uniqueidentifier	YES	FK	FK → ServiceTickets. Set for Consumption movements only.
AdjustmentReason	nvarchar(50)	YES		CHECK (when Type=Adjustment): ReceivedShipment \| Damaged \| Theft \| AuditCorrection \| InitialSetup
RecordedByUserId	uniqueidentifier	NO		Keycloak UserId
RecordedAt	datetime2	NO

Indexes: IX: InventoryItemId + RecordedAt DESC · IX: TenantId + TicketId

08 — BILLING TABLES

Billing Tables

Invoices — TEMPORAL TABLE — financial billing documents

Column	Type	Null	Key	Notes
InvoiceId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
InvoiceNumber	nvarchar(20)	NO		Format INV-YYYY-NNNNN. Unique within tenant.
SourceTicketId	uniqueidentifier	NO	FK	FK → ServiceTickets
ClientId	uniqueidentifier	NO	FK
Status	nvarchar(20)	NO		CHECK: Draft \| Sent \| Viewed \| Paid \| Overdue \| Cancelled
TaxRatePercent	decimal(5,4)	NO		TaxRate VO — decimal 0.0000 to 1.0000
SubtotalAmount	decimal(18,4)	NO		Computed from line items (stored for query performance)
TaxAmount	decimal(18,4)	NO
TotalAmount	decimal(18,4)	NO
Currency	nchar(3)	NO
IsLocked	bit	NO		Default 0. Set true on Send. Prevents line item edits.
IssuedAt	datetime2	NO
DueDate	datetime2	YES
SentAt	datetime2	YES
PaidAt	datetime2	YES
SysStartTime	datetime2	NO		TEMPORAL
SysEndTime	datetime2	NO		TEMPORAL

Indexes: UQ: TenantId + InvoiceNumber · IX: TenantId + Status + IssuedAt DESC · IX: TenantId + ClientId

InvoiceLineItems — Itemized charges on an invoice

Column	Type	Null	Key	Notes
LineItemId	uniqueidentifier	NO	PK
InvoiceId	uniqueidentifier	NO	FK
TenantId	uniqueidentifier	NO	FK
SortOrder	int	NO		Display order of line items on the invoice
Description	nvarchar(500)	NO
Quantity	decimal(18,4)	NO		CHECK: > 0
UnitPriceAmount	decimal(18,4)	NO
TotalAmount	decimal(18,4)	NO		Computed: Quantity × UnitPriceAmount
LineItemType	nvarchar(20)	NO		CHECK: Labour \| Part \| Discount \| Other

Indexes: IX: InvoiceId

Quotes — Pre-work cost estimates

Column	Type	Null	Key	Notes
QuoteId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
QuoteNumber	nvarchar(20)	NO		Format QT-YYYY-NNNNN
ClientId	uniqueidentifier	NO	FK
TicketId	uniqueidentifier	YES	FK	Optional link to a ticket
Status	nvarchar(20)	NO		CHECK: Draft \| Sent \| Viewed \| Approved \| Declined \| Expired
TotalAmount	decimal(18,4)	NO
Currency	nchar(3)	NO
ExpiresAt	datetime2	NO
TermsAndConditions	nvarchar(max)	YES
IsConverted	bit	NO		Default 0. True once converted to Invoice.
ApprovedByName	nvarchar(200)	YES
ApprovedAt	datetime2	YES
CreatedAt	datetime2	NO

Indexes: UQ: TenantId + QuoteNumber · IX: TenantId + Status

09 — ROUTING TABLES

Routing Tables

TechnicianRoutes — One route per technician per day

Column	Type	Null	Key	Notes
TechnicianRouteId	uniqueidentifier	NO	PK
TenantId	uniqueidentifier	NO	FK
TechnicianId	uniqueidentifier	NO	FK
RouteDate	date	NO		The calendar date this route covers (DateOnly)
IsOptimized	bit	NO		Default 0. Set true by OptimizeRoutes job.
OptimizedAt	datetime2	YES
Version	int	NO		Incremented on each re-optimization. Default 1.
CreatedAt	datetime2	NO

Indexes: UQ: TenantId + TechnicianId + RouteDate · IX: TenantId + RouteDate

RouteStops — Ordered stops on a technician route

Column	Type	Null	Key	Notes
RouteStopId	uniqueidentifier	NO	PK
TechnicianRouteId	uniqueidentifier	NO	FK
TenantId	uniqueidentifier	NO	FK
TicketId	uniqueidentifier	NO	FK	FK → ServiceTickets
StopOrder	int	NO		1-based ordering. CHECK: >= 1
Street	nvarchar(300)	NO		Denormalised ServiceAddress snapshot
City	nvarchar(100)	NO
Latitude	decimal(9,6)	YES
Longitude	decimal(9,6)	YES
EstimatedArrival	datetime2	YES
DrivingMinutesPrev	int	YES		Estimated driving minutes from previous stop
EstimatedDurationMin	int	YES		Estimated job duration in minutes
IsCompleted	bit	NO		Default 0
CompletedAt	datetime2	YES
IsPinned	bit	NO		Default 0. Pinned stops are skipped during re-optimization.

Indexes: IX: TechnicianRouteId + StopOrder · IX: TenantId + TicketId

10 — INDEXING & PERFORMANCE

Indexing Strategy

Query Pattern Analysis

The most frequent and performance-critical queries in Roundtrip are identified below. Each has an index strategy designed to support it.

Query Pattern	Table	Index Used	Notes
Dispatcher ticket list: all open tickets for tenant, sorted by priority + created	ServiceTickets	IX: TenantId + Status + CreatedAt DESC	Covering index includes Priority for sort
Technician dashboard: my assigned tickets today	ServiceTickets	IX: TenantId + TechnicianId + Status	Status = Assigned/InProgress only
Client service history: all tickets for a client	ServiceTickets	IX: TenantId + ClientId	Date-ordered in application layer
Low stock query: items at or below threshold	InventoryItems	IX: TenantId + QuantityOnHand	Filtered index on IsArchived = 0
Inventory lookup by SKU or barcode	InventoryItems	UQ: TenantId + SKU; IX: Barcode	SKU unique index doubles as lookup index
Invoice aging: sent invoices by due date	Invoices	IX: TenantId + Status + DueDate	For overdue calculation job
Route for technician today	TechnicianRoutes + RouteStops	UQ: TenantId+TechId+Date, IX: RouteId+Order	Two-table join; both indexed
Stock movement history for an item	StockMovements	IX: InventoryItemId + RecordedAt DESC	Supports audit log paging

Filtered Indexes

SQL Server filtered indexes are used where queries always include a specific predicate, reducing index size and improving performance:

-- Only active, non-deleted tickets (the vast majority of dispatcher queries)
CREATE NONCLUSTERED INDEX IX_ServiceTickets_Active
ON ServiceTickets (TenantId, Status, CreatedAt DESC)
WHERE IsDeleted = 0;

-- Only non-archived inventory items (normal stock queries)
CREATE NONCLUSTERED INDEX IX_InventoryItems_Active
ON InventoryItems (TenantId, QuantityOnHand)
WHERE IsArchived = 0;

-- Active technicians only (dispatch board query)
CREATE NONCLUSTERED INDEX IX_Technicians_Active
ON Technicians (TenantId, Status)
WHERE IsActive = 1;

Temporal Table Query Examples

-- Who changed this ticket's status and when?
SELECT Status, AssignedAt, TechnicianId, SysStartTime AS ChangedAt
FROM ServiceTickets
FOR SYSTEM_TIME ALL
WHERE TicketId = @ticketId
ORDER BY SysStartTime;

-- What was the inventory level for this item last Tuesday?
SELECT QuantityOnHand, UnitCostAmount
FROM InventoryItems
FOR SYSTEM_TIME AS OF '2026-02-25T00:00:00'
WHERE InventoryItemId = @itemId AND TenantId = @tenantId;

11 — MIGRATION STRATEGY

Migration Strategy

EF Core Migrations

All schema changes are managed through EF Core Migrations. No manual SQL schema changes are made directly to any environment. Every migration is code-reviewed and tested against a Testcontainers SQL Server instance before merging.

# Create a new migration
dotnet ef migrations add AddTechnicianSkillsTable \
  --project src/Roundtrip.Infrastructure \
  --startup-project src/Roundtrip.Api \
  --output-dir Persistence/Migrations

# Apply migrations to dev database
dotnet ef database update \
  --project src/Roundtrip.Infrastructure \
  --startup-project src/Roundtrip.Api

# Generate SQL script for production (reviewed before applying)
dotnet ef migrations script --idempotent \
  --project src/Roundtrip.Infrastructure \
  --startup-project src/Roundtrip.Api \
  --output migrations.sql

Migration Rules

Every migration must be additive or non-destructive in production. Dropping columns uses a two-phase approach: mark nullable, deploy, then drop in next release.
Renaming columns is always done as add-new + migrate-data + drop-old across multiple releases, never in a single migration.
All migrations run inside a transaction. If any step fails, the migration rolls back completely.
The idempotent migration script is generated for every release and reviewed by a second developer before production deployment.
Temporal table modifications require disabling the system-time period, altering, then re-enabling — this is handled in the migration builder with raw SQL.

Tenant Database Provisioning

When a new Enterprise tier tenant is onboarded, their dedicated database is provisioned by the TenantOnboardingJob in Hangfire:

// TenantOnboardingJob — step 3: provision database
public async Task ProvisionDatabaseAsync(TenantId tenantId)
{
    var dbName = $"roundtrip_{tenantSlug}";
    await _masterDb.ExecuteAsync(
        $"CREATE DATABASE [{dbName}] COLLATE SQL_Latin1_General_CP1_CI_AS");

    var tenantConnStr = BuildConnectionString(dbName);

    // Apply all migrations to the new tenant database
    var tenantDb = new ApplicationDbContext(tenantConnStr);
    await tenantDb.Database.MigrateAsync();

    // Store encrypted connection string in Tenants table
    await _tenantRegistry.SetConnectionStringAsync(tenantId,
        _encryptor.Encrypt(tenantConnStr));
}

Roundtrip · Database Design Document v1.0 · A Traxs Company Product · March 2026

Design Principles

Schema Overview

Database Layout​

Key Legend​