03 — Database Schema

Every table, column type, length, nullability, default, index, foreign key, and OnDelete behavior in the SQL Server schema. Grounded in src/AssetTracking.Persistence/Configurations/*.cs.

Common conventions — apply to every BaseEntity-derived table unless overridden:

Column Type Default Notes

Id uniqueidentifier (Guid) — PK, ValueGeneratedNever. Always set explicitly by handlers; AuditInterceptor falls back to Guid.NewGuid()

Code nvarchar(50) — Required. Filtered unique index IX_{Table}_Code with WHERE [IsDeleted] = 0 so soft-deleted rows can have their codes reused

CreatedAt datetime2 now Set by AuditInterceptor

CreatedBy nvarchar(100) "anonymous" if no user context Set by AuditInterceptor

ModifiedAt datetime2 null Set on updates

ModifiedBy nvarchar(100) null Set on updates

IsDeleted bit 0 Soft-delete flag

DeletedAt datetime2 null Set when row is soft-deleted

DeletedBy nvarchar(100) null Set when row is soft-deleted

RowVersion rowversion auto EF concurrency token

A global query filter e => !e.IsDeleted is applied to every BaseEntity-derived DbSet (see 01 §Persistence). Bypass with IgnoreQueryFilters().

Three tables are NOT BaseEntity and don't carry these columns: AuditLogs, RequestLogs, CodeSequences. They're called out explicitly below.

Column	Type	Default	Notes
`Id`	`uniqueidentifier` (Guid)	—	PK, `ValueGeneratedNever`. Always set explicitly by handlers; `AuditInterceptor` falls back to `Guid.NewGuid()`
`Code`	`nvarchar(50)`	—	Required. Filtered unique index `IX_{Table}_Code` with `WHERE [IsDeleted] = 0` so soft-deleted rows can have their codes reused
`CreatedAt`	`datetime2`	`now`	Set by `AuditInterceptor`
`CreatedBy`	`nvarchar(100)`	`"anonymous"` if no user context	Set by `AuditInterceptor`
`ModifiedAt`	`datetime2`	null	Set on updates
`ModifiedBy`	`nvarchar(100)`	null	Set on updates
`IsDeleted`	`bit`	`0`	Soft-delete flag
`DeletedAt`	`datetime2`	null	Set when row is soft-deleted
`DeletedBy`	`nvarchar(100)`	null	Set when row is soft-deleted
`RowVersion`	`rowversion`	auto	EF concurrency token

Index of tables

#	Table	Module	BaseEntity?
1	`Users`	Identity	yes
2	`Roles`	Identity	yes
3	`Permissions`	Identity	yes
4	`UserRoles`	Identity	yes
5	`RolePermissions`	Identity	yes
6	`UserPermissions`	Identity	yes
7	`RefreshTokens`	Identity	yes
8	`LoginAudits`	Identity	yes
9	`Organizations`	Master Data	yes
10	`Locations`	Master Data	yes
11	`Classifications`	Master Data	yes
12	`Vendors`	Master Data	yes
13	`Manufacturers`	Master Data	yes
14	`Assets`	Assets	yes
15	`AssetDetails`	Assets	yes
16	`AssetStatuses`	Assets	yes
17	`AssetStatusHistory`	Assets	yes
18	`AssetLocationHistory`	Assets	yes
19	`AssetOrganizationHistory`	Assets	yes
20	`AssetCustodyHistory`	Assets	yes
21	`AuditPlans`	Audits	yes
22	`AuditPlanScopes`	Audits	yes
23	`AuditAssignments`	Audits	yes
24	`AuditAssignmentAssets`	Audits	yes
25	`AuditResults`	Audits	yes
26	`AuditResultLines`	Audits	yes
27	`AuditReviewActions`	Audits	yes
28	`AssetTransfers`	Transfers	yes
29	`AssetTransferLines`	Transfers	yes
30	`CheckOuts`	Custody	yes
31	`MaintenancePlans`	Maintenance	yes
32	`MaintenancePlanAssets`	Maintenance	yes
33	`MaintenanceRequests`	Maintenance	yes
34	`WorkOrders`	Maintenance	yes
35	`NotificationTemplates`	Notifications	yes
36	`Notifications`	Notifications	yes
37	`NotificationDeliveries`	Notifications	yes
38	`NotificationPreferences`	Notifications	yes
39	`Documents`	Documents	yes
40	`DocumentLinks`	Documents	yes
41	`HierarchyConfigs`	Settings	yes
42	`HierarchyLevels`	Settings	yes
43	`AppSettings`	Settings	yes (singleton)
44	`Translations`	Settings	yes
45	`EmailProviderSettings`	Settings	yes (singleton)
46	`AuditLogs`	System	no
47	`RequestLogs`	System	no
48	`CodeSequences`	System	no

High-level entity relationships

erDiagram
  Users ||--o{ UserRoles : has
  Roles ||--o{ UserRoles : grants
  Roles ||--o{ RolePermissions : has
  Permissions ||--o{ RolePermissions : in
  Users ||--o{ UserPermissions : overrides
  Permissions ||--o{ UserPermissions : in
  Users ||--o{ RefreshTokens : owns
  Users ||--o{ LoginAudits : "attempted by"

  Organizations ||--o{ Organizations : "parent of"
  Locations ||--o{ Locations : "parent of"
  Classifications ||--o{ Classifications : "parent of"

  Assets }o--|| Organizations : in
  Assets }o--|| Locations : at
  Assets }o--|| Classifications : "classified as"
  Assets }o--|| AssetStatuses : has
  Assets }o--o| Users : "custody of"
  Assets ||--o| AssetDetails : "has details"
  AssetDetails }o--o| Manufacturers : made_by
  AssetDetails }o--o| Vendors : sold_by

  Assets ||--o{ AssetStatusHistory : history
  Assets ||--o{ AssetLocationHistory : history
  Assets ||--o{ AssetOrganizationHistory : history
  Assets ||--o{ AssetCustodyHistory : history

  AuditPlans ||--o{ AuditPlanScopes : scoped_by
  AuditPlans ||--o{ AuditAssignments : "broken into"
  AuditAssignments ||--o{ AuditAssignmentAssets : snapshots
  AuditAssignments ||--o| AuditResults : produces
  AuditResults ||--o{ AuditResultLines : has
  AuditResultLines ||--o{ AuditReviewActions : reviewed

  AssetTransfers ||--o{ AssetTransferLines : has
  CheckOuts }o--|| Assets : "current custody"

  MaintenancePlans ||--o{ MaintenancePlanAssets : targets
  MaintenancePlans ||--o{ WorkOrders : "generates"
  MaintenanceRequests ||--o| WorkOrders : "promotes to"

  NotificationTemplates ||--o{ Notifications : renders
  Notifications ||--o{ NotificationDeliveries : "queues delivery for"
  Users ||--o{ NotificationPreferences : sets

  Documents ||--o{ DocumentLinks : linked_via

Identity

`Users`

Column	Type	Null	Default	Notes
BaseEntity columns				see top of doc
`Email`	`nvarchar(256)`	no	—	Unique (`IX_Users_Email`)
`UserName`	`nvarchar(100)`	no	—	Unique (`IX_Users_UserName`)
`FullNamePrimary`	`nvarchar(200)`	yes	—
`FullNameSecondary`	`nvarchar(200)`	yes	—
`PasswordHash`	`nvarchar(500)`	yes	—	PBKDF2 (Microsoft.AspNetCore.Identity.PasswordHasher)
`SecurityStamp`	`nvarchar(100)`	no	new Guid string	Regenerated on permission/role changes; invalidates JWT/cache
`PhoneNumber`	`nvarchar(30)`	yes	—
`PhoneConfirmed`	`bit`	no	`0`
`EmailConfirmed`	`bit`	no	`0`
`LockoutEnd`	`datetime2`	yes	—
`AccessFailedCount`	`int`	no	`0`	Resets on success
`PreferredLanguage`	`nvarchar(5)`	no	`"en"`	ISO 639-1
`PreferredTheme`	`nvarchar(10)`	no	`"System"`	enum: `Light`/`Dark`/`System`
`AvatarUrl`	`nvarchar(500)`	yes	—
`UserType`	`nvarchar(20)`	no	`"Interactive"`	enum: `Interactive`/`Mobile`
`IsActive`	`bit`	no	`1`
`LastLoginAt`	`datetime2`	yes	—
`LastLoginIp`	`nvarchar(50)`	yes	—
`ManagerId`	`uniqueidentifier`	yes	—	FK → `Users.Id` (`OnDelete=Restrict`)
`OrganizationId`	`uniqueidentifier`	yes	—	No FK enforced
`DefaultLocationId`	`uniqueidentifier`	yes	—	No FK enforced

Indexes: UX (Email), UX (UserName), IX (IsActive, IsDeleted).

Cascade child rels:

UserRoles (Cascade)
UserPermissions (Cascade)
RefreshTokens (Cascade)

`Roles`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(100)`	no	Unique (`IX_Roles_NamePrimary`)
`NameSecondary`	`nvarchar(100)`	yes
`DescriptionPrimary`	`nvarchar(1000)`	yes
`DescriptionSecondary`	`nvarchar(1000)`	yes
`IsSystem`	`bit`	no	Cannot be deleted via API
`IsBuiltIn`	`bit`	no	Auto-syncs new permissions on startup

Cascade: RolePermissions (Cascade), UserRoles (Cascade).

`Permissions`

Column	Type	Null	Notes
`Key`	`nvarchar(100)`	no	Unique (`IX_Permissions_Key`). Format: `resource.action` (e.g. `asset.create`)
`Module`	`nvarchar(50)`	no	Indexed for grouped lookups
`NamePrimary`	`nvarchar(150)`	no
`NameSecondary`	`nvarchar(150)`	yes
`DescriptionPrimary`	`nvarchar(500)`	yes
`DescriptionSecondary`	`nvarchar(500)`	yes
`IsDangerous`	`bit`	no	UI hint — red badge in role editor
`SortOrder`	`int`	no	Within module

`UserRoles`

Column	Type	Null	Notes
`UserId`	`uniqueidentifier`	no	FK → `Users.Id` (Cascade from User)
`RoleId`	`uniqueidentifier`	no	FK → `Roles.Id` (Cascade from Role)
`AssignedBy`	`uniqueidentifier`	no	User id
`AssignedAt`	`datetime2`	no
`ValidFrom`	`datetime2`	no
`ValidUntil`	`datetime2`	yes	null = no expiration

Index: UX (UserId, RoleId) WHERE [IsDeleted] = 0 — filtered so a re-assignment after revocation works.

`RolePermissions`

Column	Type	Null	Notes
`RoleId`	`uniqueidentifier`	no	FK → `Roles.Id` (Cascade)
`PermissionId`	`uniqueidentifier`	no	FK → `Permissions.Id` (Cascade)
`AddedBy`	`uniqueidentifier`	no	User id
`AddedAt`	`datetime2`	no

Index: UX (RoleId, PermissionId) WHERE [IsDeleted] = 0.

`UserPermissions`

Column	Type	Null	Notes
`UserId`	`uniqueidentifier`	no	FK → `Users.Id` (Cascade)
`PermissionId`	`uniqueidentifier`	no	FK → `Permissions.Id` (Cascade)
`Effect`	`nvarchar(10)`	no	`"Grant"` / `"Deny"`
`Reason`	`nvarchar(500)`	yes	Free text
`GrantedBy`	`uniqueidentifier`	no	User id
`GrantedAt`	`datetime2`	no
`ValidFrom`	`datetime2`	no
`ValidUntil`	`datetime2`	yes	null = no expiration

Index: UX (UserId, PermissionId) WHERE [IsDeleted] = 0.

`RefreshTokens`

Column	Type	Null	Notes
`UserId`	`uniqueidentifier`	no	FK → `Users.Id` (Cascade)
`FamilyId`	`uniqueidentifier`	no	Rotation chain — all tokens that descend from one login share this
`TokenHash`	`nvarchar(200)`	no	Indexed (`IX_RefreshTokens_TokenHash`); base64 SHA-256 of the actual refresh token (never store plaintext)
`IssuedAt`	`datetime2`	no
`ExpiresAt`	`datetime2`	no
`ReplacedByTokenHash`	`nvarchar(200)`	yes	Set during rotation to point at successor
`RevokedAt`	`datetime2`	yes	Set on rotation, manual revoke, or reuse-detected family revoke
`RevokedReason`	`nvarchar(200)`	yes	`"rotation"` / `"reuseDetected"` / etc.
`DeviceId`	`nvarchar(100)`	yes	Captured from request
`IpAddress`	`nvarchar(50)`	yes

Indexes: IX (TokenHash), IX (UserId, FamilyId), IX (UserId, RevokedAt).

The C# entity exposes computed IsRevoked, IsExpired, IsActive — these are Ignored in EF mapping (no columns).

`LoginAudits`

Column	Type	Null	Notes
`UserId`	`uniqueidentifier`	yes	FK → `Users.Id` (`OnDelete=SetNull`); null when login attempt was for an unknown email
`AttemptedEmail`	`nvarchar(256)`	no	What the caller typed
`Result`	`nvarchar(30)`	no	enum: `Success`/`InvalidPassword`/`LockedOut`/`Disabled`
`IpAddress`	`nvarchar(50)`	yes
`UserAgent`	`nvarchar(500)`	yes
`DeviceId`	`nvarchar(100)`	yes
`OccurredAt`	`datetime2`	no

Index: IX (AttemptedEmail, OccurredAt).

Master Data

`Organizations`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no	Indexed
`NameSecondary`	`nvarchar(200)`	yes
`DescriptionPrimary`	`nvarchar(2000)`	yes
`DescriptionSecondary`	`nvarchar(2000)`	yes
`ImageBaseUrl`	`nvarchar(500)`	yes
`Level`	`int`	no	default `1` (root); derived from parent's level + 1 on insert
`ParentOrganizationId`	`uniqueidentifier`	yes	FK → `Organizations.Id` (`Restrict`)
`ManagerId`	`uniqueidentifier`	yes	FK → `Users.Id` (`SetNull`)

Indexes: IX (NamePrimary), IX (Level, ParentOrganizationId).

`Locations`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no	Indexed
`NameSecondary`	`nvarchar(200)`	yes
`DescriptionPrimary`	`nvarchar(2000)`	yes
`DescriptionSecondary`	`nvarchar(2000)`	yes
`ImageBaseUrl`	`nvarchar(500)`	yes
`LocationType`	`nvarchar(20)`	no	enum: `Site`/`Building`/`Floor`/`Zone`/`Room`/`Rack`/`Vehicle`/`Outdoor`
`Latitude`	`decimal(9, 6)`	yes
`Longitude`	`decimal(9, 6)`	yes
`GeofenceRadiusMeters`	`int`	yes
`Address`	`nvarchar(500)`	yes
`Level`	`int`	no	default `1`
`ParentLocationId`	`uniqueidentifier`	yes	FK → `Locations.Id` (`Restrict`)

Indexes: IX (NamePrimary), IX (Level, ParentLocationId).

`Classifications`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no	Indexed
`NameSecondary`	`nvarchar(200)`	yes
`DescriptionPrimary`	`nvarchar(2000)`	yes
`DescriptionSecondary`	`nvarchar(2000)`	yes
`ImageBaseUrl`	`nvarchar(500)`	yes
`DefaultUsefulLifeMonths`	`int`	yes
`RequiresSerialNumber`	`bit`	no	`0`
`RequiresMaintenancePlan`	`bit`	no	`0`
`Level`	`int`	no	default `1`
`ParentClassId`	`uniqueidentifier`	yes	FK → `Classifications.Id` (`Restrict`)

Indexes: IX (NamePrimary), IX (Level, ParentClassId).

`Vendors`

Column	Type	Null
`NamePrimary`	`nvarchar(200)`	no (indexed)
`NameSecondary`	`nvarchar(200)`	yes
`ContactName`	`nvarchar(150)`	yes
`ContactEmail`	`nvarchar(256)`	yes
`ContactPhone`	`nvarchar(30)`	yes
`Address`	`nvarchar(500)`	yes
`TaxId`	`nvarchar(50)`	yes
`Notes`	`nvarchar(2000)`	yes

`Manufacturers`

Column	Type	Null
`NamePrimary`	`nvarchar(200)`	no (indexed)
`NameSecondary`	`nvarchar(200)`	yes
`Website`	`nvarchar(500)`	yes
`SupportEmail`	`nvarchar(256)`	yes
`SupportPhone`	`nvarchar(30)`	yes
`Notes`	`nvarchar(2000)`	yes

Assets

`Assets`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no
`NameSecondary`	`nvarchar(200)`	yes
`DescriptionPrimary`	`nvarchar(2000)`	yes
`DescriptionSecondary`	`nvarchar(2000)`	yes
`ImageBaseUrl`	`nvarchar(500)`	yes
`OrganizationId`	`uniqueidentifier`	no	FK → `Organizations.Id` (`Restrict`)
`LocationId`	`uniqueidentifier`	no	FK → `Locations.Id` (`Restrict`)
`ClassificationId`	`uniqueidentifier`	no	FK → `Classifications.Id` (`Restrict`)
`StatusId`	`uniqueidentifier`	no	FK → `AssetStatuses.Id` (`Restrict`)
`CurrentCustodianId`	`uniqueidentifier`	yes	FK → `Users.Id` (`SetNull`)
`IsCritical`	`bit`	no	`0`
`ConditionRating`	`int`	yes	1–5
`AcquisitionMethod`	`nvarchar(20)`	no	enum: `Purchased`/`Leased`/`Donated`/`Transferred`/`Other`
`LastSeenAt`	`datetime2`	yes	Updated by audit submissions
`LastSeenLocationId`	`uniqueidentifier`	yes	No FK enforced

Indexes: IX (OrganizationId, LocationId, ClassificationId), IX (StatusId). Code unique-filtered (per BaseEntity convention).

Code shape: {6-digit-seq}{classificationCode} — generated via CodeGeneratorService.NextAssetSequenceAsync with IgnoreQueryFilters so freed codes from soft-deleted assets are never reissued.

`AssetDetails`

1:1 with Assets. Cascade-deleted when parent Asset is deleted.

Column	Type	Null	Notes
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Cascade`)
`SerialNumber`	`nvarchar(100)`	yes	Indexed (filtered: `WHERE SerialNumber IS NOT NULL`)
`ManufacturerId`	`uniqueidentifier`	yes	FK → `Manufacturers.Id` (`SetNull`)
`VendorId`	`uniqueidentifier`	yes	FK → `Vendors.Id` (`SetNull`)
`Model`	`nvarchar(150)`	yes
`Inches`	`decimal(5, 2)`	yes	Display-size helper for IT assets
`Color`	`nvarchar(50)`	yes
`Weight`	`decimal(8, 3)`	yes	kg
`Price`	`decimal(18, 2)`	yes
`CurrencyCode`	`nvarchar(3)`	yes	ISO 4217
`PurchaseDate`	`datetime2`	yes
`PurchaseOrderNumber`	`nvarchar(50)`	yes
`WarrantyStart`	`datetime2`	yes
`WarrantyEnd`	`datetime2`	yes
`ExpectedUsefulLifeMonths`	`int`	yes
`CustomFieldsJson`	`nvarchar(max)`	yes	Free-form JSON

`AssetStatuses`

Column	Type	Null	Notes
`Key`	`nvarchar(50)`	no	Unique
`NamePrimary`	`nvarchar(100)`	no
`NameSecondary`	`nvarchar(100)`	yes
`Color`	`nvarchar(20)`	yes	Hex/named
`SortOrder`	`int`	no
`IsTerminal`	`bit`	no	E.g., `Disposed` is terminal

`AssetStatusHistory`

Column	Type	Null	Notes
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Cascade`)
`OldStatusId`	`uniqueidentifier`	yes	FK → `AssetStatuses.Id` (`NoAction`)
`NewStatusId`	`uniqueidentifier`	no	FK → `AssetStatuses.Id` (`NoAction`)
`ChangedAt`	`datetime2`	no
`ChangedBy`	`uniqueidentifier`	no	User id (no FK)
`Reason`	`nvarchar(500)`	yes
`Source`	`nvarchar(20)`	no	enum: `Manual`/`Audit`/`Transfer`/`CheckOut`/`CheckIn`/`Maintenance`/`BulkImport`/`System`
`SourceRefId`	`uniqueidentifier`	yes	The audit-line / transfer / etc.

Index: IX (AssetId, ChangedAt).

`AssetLocationHistory`

Same shape as AssetStatusHistory substituting Old/NewLocationId (FK → Locations.Id, NoAction).

`AssetOrganizationHistory`

Same shape substituting Old/NewOrganizationId (FK → Organizations.Id, NoAction).

`AssetCustodyHistory`

Same shape substituting Old/NewCustodianId (FK → Users.Id, NoAction).

Audits

`AuditPlans`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no
`NameSecondary`	`nvarchar(200)`	yes
`DescriptionPrimary`	`nvarchar(2000)`	yes
`DescriptionSecondary`	`nvarchar(2000)`	yes
`Status`	`nvarchar(20)`	no	enum: `Draft`/`Scheduled`/`InProgress`/`Completed`/`Cancelled`
`Priority`	`nvarchar(10)`	no	enum: `Low`/`Normal`/`High`/`Urgent`
`ResolvedAssetCount`	`int`	yes	Cached, set on create

Index: IX (Status). Cascade child rels: Scopes, Assignments.

Removed in migration 20260503144117_RemoveAuditPlanDates: StartDate, EndDate.

`AuditPlanScopes`

Column	Type	Null	Notes
`AuditPlanId`	`uniqueidentifier`	no	FK → `AuditPlans.Id` (`Cascade`)
`ScopeType`	`nvarchar(20)`	no	enum: `Location`/`Organization`/`Classification`/`All`/`AssetList`/`Combined`
`TargetId`	`uniqueidentifier`	yes	Used by `Location`/`Organization`/`Classification`
`IncludeChildren`	`bit`	no	Tree expansion flag
`AssetIdsJson`	`nvarchar(max)`	yes	JSON array — used by `AssetList`
`OrganizationId`	`uniqueidentifier`	yes	Used by `Combined`
`LocationId`	`uniqueidentifier`	yes	Used by `Combined`
`ClassificationId`	`uniqueidentifier`	yes	Used by `Combined`

`AuditAssignments`

Column	Type	Null	Notes
`AuditPlanId`	`uniqueidentifier`	no	FK → `AuditPlans.Id` (`Cascade`)
`AssignedUserId`	`uniqueidentifier`	no	FK → `Users.Id` (`Restrict`)
`Status`	`nvarchar(20)`	no	enum: `Assigned`/`Downloaded`/`InProgress`/`Submitted`/`InReview`/`Completed`/`Cancelled`
`DownloadedAt`	`datetime2`	yes
`SubmittedAt`	`datetime2`	yes
`DeviceId`	`nvarchar(100)`	yes
`PayloadVersion`	`int`	no	`1`
`DraftPayload`	`nvarchar(max)`	yes	Opaque mobile-side scan progress JSON
`DraftUpdatedAt`	`datetime2`	yes

Index: IX (AssignedUserId, Status). 1:1 to AuditResults (NoAction).

Removed in migration 20260503150720_RemoveAuditAssignmentDueDate: DueDate.

`AuditAssignmentAssets`

Snapshot of expected state at assignment time — drives stable discrepancy comparison.

Column	Type	Null	Notes
`AuditAssignmentId`	`uniqueidentifier`	no	FK → `AuditAssignments.Id` (`Cascade`)
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)
`ExpectedOrganizationId`	`uniqueidentifier`	no
`ExpectedLocationId`	`uniqueidentifier`	no
`ExpectedClassificationId`	`uniqueidentifier`	no
`ExpectedStatusId`	`uniqueidentifier`	no
`ExpectedCustodianId`	`uniqueidentifier`	yes

Index: UX (AuditAssignmentId, AssetId).

`AuditResults`

Column	Type	Null	Notes
`AuditAssignmentId`	`uniqueidentifier`	no	FK → `AuditAssignments.Id` (`NoAction`); 1:1
`SubmittedBy`	`uniqueidentifier`	no	User id
`SubmittedAt`	`datetime2`	no
`ClientSubmissionId`	`uniqueidentifier`	no	Indexed — idempotency key
`DeviceInfo`	`nvarchar(500)`	yes
`Latitude`	`decimal(9, 6)`	yes
`Longitude`	`decimal(9, 6)`	yes
`ReviewStatus`	`nvarchar(20)`	no	enum: `PendingReview`/`InReview`/`UnderClarification`/`Approved`/`Rejected`
`ReviewStartedAt`	`datetime2`	yes
`ReviewCompletedAt`	`datetime2`	yes
`ReviewedBy`	`uniqueidentifier`	yes	FK → `Users.Id` (`NoAction`)

`AuditResultLines`

Column	Type	Null	Notes
`AuditResultId`	`uniqueidentifier`	no	FK → `AuditResults.Id` (`Cascade`)
`AssetId`	`uniqueidentifier`	yes	FK → `Assets.Id` (`NoAction`)
`Outcome`	`nvarchar(30)`	no	enum: `Found`/`NotFound`/`LocationMismatch`/`OrganizationMismatch`/`Extra`
`IdentificationMethod`	`nvarchar(10)`	no	enum: `Qr`/`Manual`
`ObservedOrganizationId`	`uniqueidentifier`	yes
`ObservedLocationId`	`uniqueidentifier`	yes
`ObservedClassificationId`	`uniqueidentifier`	yes
`ObservedConditionRating`	`int`	yes
`Notes`	`nvarchar(2000)`	yes
`PhotosCount`	`int`	no	Source-of-truth is `DocumentLink` rows; this is a denorm

`AuditReviewActions`

Column	Type	Null	Notes
`AuditResultLineId`	`uniqueidentifier`	no	FK → `AuditResultLines.Id` (`Cascade`)
`Action`	`nvarchar(10)`	no	enum: `Approve`/`Reject`/`Modify`
`ModifiedOrganizationId`	`uniqueidentifier`	yes	Used when `Action=Modify`
`ModifiedLocationId`	`uniqueidentifier`	yes
`ModifiedClassificationId`	`uniqueidentifier`	yes
`Notes`	`nvarchar(2000)`	yes
`ActedAt`	`datetime2`	no
`ActedBy`	`uniqueidentifier`	no	User id

Transfers

`AssetTransfers`

Column	Type	Null	Notes
`FromOrganizationId`	`uniqueidentifier`	yes	FK → `Organizations.Id` (`NoAction`)
`ToOrganizationId`	`uniqueidentifier`	yes	FK → `Organizations.Id` (`NoAction`)
`FromLocationId`	`uniqueidentifier`	yes	FK → `Locations.Id` (`NoAction`)
`ToLocationId`	`uniqueidentifier`	yes	FK → `Locations.Id` (`NoAction`)
`Reason`	`nvarchar(max)`	no	Quill-edited rich HTML — unbounded for inline base64 images (migration `20260424120000_RelaxTransferReasonLength` removed the 500-char cap)
`Status`	`nvarchar(20)`	no	enum: `Draft`/`Submitted`/`Approved`/`InTransit`/`Completed`/`Rejected`/`Cancelled`
`RequestedBy`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`RequestedAt`	`datetime2`	no
`ApprovedBy`	`uniqueidentifier`	yes	FK → `Users.Id` (`NoAction`) — set on Approve OR Reject
`ApprovedAt`	`datetime2`	yes
`CompletedBy`	`uniqueidentifier`	yes	FK → `Users.Id` (`NoAction`)
`CompletedAt`	`datetime2`	yes
`ExpectedCompletionDate`	`datetime2`	yes
`RejectionReason`	`nvarchar(500)`	yes

Indexes: IX (Status), IX (RequestedBy). Cascade: Lines.

`AssetTransferLines`

Column	Type	Null	Notes
`AssetTransferId`	`uniqueidentifier`	no	FK → `AssetTransfers.Id` (`Cascade`)
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)
`ReceivedAt`	`datetime2`	yes	Set on `ReceiveTransferLine`
`ReceivedStatus`	`nvarchar(20)`	yes	enum: `Ok`/`Damaged`/`Missing`
`Notes`	`nvarchar(1000)`	yes

Index: UX (AssetTransferId, AssetId) — same asset can't appear twice in one transfer.

Custody

`CheckOuts`

Column	Type	Null	Notes
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)
`CustodianId`	`uniqueidentifier`	no	FK → `Users.Id` (`Restrict`)
`CheckedOutBy`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`) — issuer
`CheckedOutAt`	`datetime2`	no
`ExpectedReturnAt`	`datetime2`	yes
`Purpose`	`nvarchar(500)`	yes
`Status`	`nvarchar(20)`	no	enum: `Active`/`Returned`/`Overdue`/`Lost`/`Cancelled`
`CheckedInBy`	`uniqueidentifier`	yes	FK → `Users.Id` (`NoAction`)
`CheckedInAt`	`datetime2`	yes
`ReturnConditionRating`	`int`	yes	1–5
`ReturnNotes`	`nvarchar(1000)`	yes
`PreviousStatusId`	`uniqueidentifier`	yes	The asset's status before check-out, restored on check-in

Indexes: IX (CustodianId, Status), UX (AssetId) WHERE [Status] = 'Active' — at most one active check-out per asset.

Maintenance

`MaintenancePlans`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(200)`	no
`NameSecondary`	`nvarchar(200)`	yes
`Frequency`	`nvarchar(20)`	no	enum: `Daily`/`Weekly`/`Monthly`/`Quarterly`/`Yearly`/`CustomDays`
`CustomIntervalDays`	`int`	yes	Used when `Frequency=CustomDays`
`NextDueDate`	`datetime2`	yes	Drives generate eligibility
`LastPerformedDate`	`datetime2`	yes
`EstimatedDurationHours`	`decimal(5, 2)`	yes
`DefaultVendorId`	`uniqueidentifier`	yes	FK → `Vendors.Id` (`SetNull`)
`IsActive`	`bit`	no	`1`

Index: IX (IsActive, NextDueDate). Cascade: Assets (the join).

Removed in migration 20260503161238_MaintenancePlanMultiAsset: AssetId, ClassificationId (replaced by the M:N join — see below).

`MaintenancePlanAssets`

Column	Type	Null	Notes
`MaintenancePlanId`	`uniqueidentifier`	no	FK → `MaintenancePlans.Id` (`Cascade`)
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)

Index: UX (MaintenancePlanId, AssetId).

`MaintenanceRequests`

Column	Type	Null	Notes
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)
`ReportedBy`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`ReportedAt`	`datetime2`	no
`Severity`	`nvarchar(20)`	no	enum: `Low`/`Medium`/`High`/`Critical`
`Summary`	`nvarchar(200)`	no
`Description`	`nvarchar(4000)`	yes
`Status`	`nvarchar(30)`	no	enum: `Open`/`UnderReview`/`PromotedToWorkOrder`/`Rejected`/`Cancelled`
`LinkedWorkOrderId`	`uniqueidentifier`	yes	FK → `WorkOrders.Id` (`NoAction`) — set on Promote
`ReviewedBy`	`uniqueidentifier`	yes	FK → `Users.Id` (`NoAction`)
`ReviewedAt`	`datetime2`	yes
`RejectionReason`	`nvarchar(500)`	yes

Indexes: IX (Status), IX (AssetId, ReportedAt).

`WorkOrders`

Column	Type	Null	Notes
`AssetId`	`uniqueidentifier`	no	FK → `Assets.Id` (`Restrict`)
`OriginType`	`nvarchar(20)`	no	enum: `Plan`/`Request`/`Manual`
`OriginRefId`	`uniqueidentifier`	yes	Plan or Request id
`Type`	`nvarchar(20)`	no	enum: `Preventive`/`Corrective`/`Inspection`/`Calibration`/`Other`
`Priority`	`nvarchar(20)`	no	enum: `Low`/`Normal`/`High`/`Critical`
`Summary`	`nvarchar(200)`	no
`Description`	`nvarchar(4000)`	yes
`Status`	`nvarchar(20)`	no	enum: `Open`/`Assigned`/`InProgress`/`OnHold`/`Completed`/`Cancelled`
`AssignedToUserId`	`uniqueidentifier`	yes	FK → `Users.Id` (`SetNull`)
`AssignedToVendorId`	`uniqueidentifier`	yes	FK → `Vendors.Id` (`SetNull`)
`ScheduledStart`	`datetime2`	yes
`ScheduledEnd`	`datetime2`	yes
`ActualStart`	`datetime2`	yes
`ActualEnd`	`datetime2`	yes
`DowntimeHours`	`decimal(8, 2)`	yes
`LaborCost`	`decimal(18, 2)`	yes
`PartsCost`	`decimal(18, 2)`	yes
`TotalCost`	`decimal(18, 2)`	yes
`CurrencyCode`	`nvarchar(3)`	yes
`ResolutionNotes`	`nvarchar(4000)`	yes
`PreviousStatusId`	`uniqueidentifier`	yes	Asset's status before WO `InProgress`

Indexes: IX (AssignedToUserId, Status, ScheduledStart), IX (Status), IX (AssetId, Status).

Notifications

`NotificationTemplates`

Column	Type	Null	Notes
`Key`	`nvarchar(100)`	no	Event id (e.g., `audit.assigned`)
`Channel`	`nvarchar(10)`	no	enum: `InApp`/`Email`
`SubjectPrimary`	`nvarchar(300)`	yes
`SubjectSecondary`	`nvarchar(300)`	yes
`BodyPrimary`	`nvarchar(8000)`	no
`BodySecondary`	`nvarchar(8000)`	yes
`IsActive`	`bit`	no	`1`

Index: UX (Key, Channel).

`Notifications`

Column	Type	Null	Notes
`RecipientUserId`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`Title`	`nvarchar(300)`	no
`Body`	`nvarchar(4000)`	no	Plain-text-cleaned for InApp
`Severity`	`nvarchar(10)`	no	enum: `Info`/`Success`/`Warning`/`Error`
`Link`	`nvarchar(1000)`	yes	Relative path (e.g., `/transfers/{id}`)
`IsRead`	`bit`	no	`0`
`ReadAt`	`datetime2`	yes
`TemplateKey`	`nvarchar(100)`	no
`ContextJson`	`nvarchar(max)`	yes	The merge-fields dict

Index: IX (RecipientUserId, IsRead, CreatedAt) — drives the inbox view.

`NotificationDeliveries`

Email queue. Drained by NotificationDeliveryWorker.

Column	Type	Null	Notes
`NotificationId`	`uniqueidentifier`	yes	FK → `Notifications.Id` (`SetNull`)
`RecipientUserId`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`Channel`	`nvarchar(10)`	no	Always `Email` currently
`Status`	`nvarchar(20)`	no	enum: `Queued`/`Sending`/`Sent`/`Failed`/`Bounced`/`Rejected`
`Attempts`	`int`	no	`0`
`LastAttemptAt`	`datetime2`	yes
`NextAttemptAt`	`datetime2`	yes	Backoff-driven
`ProviderMessageId`	`nvarchar(200)`	yes	Returned by SMTP server
`ErrorMessage`	`nvarchar(2000)`	yes
`Subject`	`nvarchar(300)`	yes	Rendered
`Body`	`nvarchar(8000)`	no	Rendered HTML
`Recipient`	`nvarchar(500)`	yes	Resolved email address

Indexes: IX (Status, NextAttemptAt) — drives worker poll, IX (RecipientUserId, Channel).

`NotificationPreferences`

Column	Type	Null	Notes
`UserId`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`TemplateKey`	`nvarchar(100)`	no
`Channel`	`nvarchar(10)`	no
`IsEnabled`	`bit`	no	`1`

Index: UX (UserId, TemplateKey, Channel).

Documents

`Documents`

Column	Type	Null	Notes
`NamePrimary`	`nvarchar(300)`	no
`NameSecondary`	`nvarchar(300)`	yes
`FileName`	`nvarchar(300)`	no	Original upload name
`MimeType`	`nvarchar(100)`	no
`SizeBytes`	`bigint`	no
`StorageKey`	`nvarchar(500)`	no	Path within `IFileStorage` root
`Sha256`	`nvarchar(64)`	no	Indexed — duplicate detection
`UploadedBy`	`uniqueidentifier`	no	FK → `Users.Id` (`NoAction`)
`UploadedAt`	`datetime2`	no
`ScanStatus`	`nvarchar(20)`	no	enum: `Pending`/`Clean`/`Infected`/`ScanFailed`
`ScanAt`	`datetime2`	yes
`Category`	`nvarchar(30)`	no	enum: `Invoice`/`Warranty`/`Manual`/`Photo`/`Certificate`/`AuditEvidence`/`Other` (indexed)

Cascade: Links.

`DocumentLinks`

Column	Type	Null	Notes
`DocumentId`	`uniqueidentifier`	no	FK → `Documents.Id` (`Cascade`)
`OwnerType`	`nvarchar(30)`	no	enum: `Asset`/`AssetTransfer`/`WorkOrder`/`MaintenanceRequest`/`AuditResult`/`AuditResultLine`/`User`/`Organization`/`Vendor`/`CheckOut`
`OwnerId`	`uniqueidentifier`	no	The owner row's id (no FK — polymorphic)
`LinkedBy`	`uniqueidentifier`	no	User id
`LinkedAt`	`datetime2`	no

Indexes: IX (OwnerType, OwnerId) — drives "documents for owner X" lookups; UX (DocumentId, OwnerType, OwnerId) — same doc can't be linked twice to the same owner.

Settings

`HierarchyConfigs`

Column	Type	Null	Notes
`EntityType`	`nvarchar(20)`	no	enum: `Location`/`Organization`/`Classification` (Unique)

`HierarchyLevels`

Column	Type	Null	Notes
`HierarchyConfigId`	`uniqueidentifier`	no	FK → `HierarchyConfigs.Id` (`Restrict`)
`LevelNumber`	`int`	no
`LabelPrimary`	`nvarchar(100)`	no
`LabelSecondary`	`nvarchar(100)`	yes
`CodeDigits`	`int`	no	default `2` — width of per-level segment in generated codes

`AppSettings`

Singleton (id = 11111111-1111-1111-1111-111111111111).

Column	Type	Null	Default
`PrimaryLanguageCode`	`nvarchar(10)`	no	`"en"`
`SecondaryLanguageCode`	`nvarchar(10)`	no	`"ar"`

`Translations`

Column	Type	Null
`Key`	`nvarchar(200)`	no
`LanguageCode`	`nvarchar(10)`	no
`Value`	`nvarchar(2000)`	no

Index: UX (Key, LanguageCode) WHERE [IsDeleted] = 0.

`EmailProviderSettings`

Singleton (id = 22222222-2222-2222-2222-222222222222).

Column	Type	Null	Notes
`Enabled`	`bit`	no	Master switch
`Host`	`nvarchar(200)`	no	`"smtp.gmail.com"` default
`Port`	`int`	no	`587` default
`EnableSsl`	`bit`	no	`1` default
`Username`	`nvarchar(200)`	yes
`PasswordEncrypted`	`nvarchar(4000)`	yes	base64 ciphertext (`IDataProtector`); roomy for ~33% inflation
`FromAddress`	`nvarchar(200)`	no
`FromName`	`nvarchar(200)`	yes
`ReplyToAddress`	`nvarchar(200)`	yes
`RedirectAllEnabled`	`bit`	no	Staging escape hatch
`RedirectAllToAddress`	`nvarchar(200)`	yes
`BatchSize`	`int`	no	Worker tuning
`PollIntervalSeconds`	`int`	no
`MaxAttempts`	`int`	no

System

`AuditLogs`

Not a BaseEntity — append-only. No Code, IsDeleted, or soft-delete query filter.

Column	Type	Null	Notes
`Id`	`uniqueidentifier`	no	PK
`OccurredAt`	`datetime2`	no	Indexed
`UserId`	`uniqueidentifier`	yes	Indexed; null for anonymous (e.g., seed)
`UserEmail`	`nvarchar(256)`	yes	Snapshot at change time
`EntityName`	`nvarchar(120)`	no	`nameof(T)`
`EntityId`	`uniqueidentifier`	no
`EntityCode`	`nvarchar(100)`	yes	Snapshot of `Code` at change time
`Action`	`nvarchar(20)`	no	`"Created"`/`"Updated"`/`"Deleted"`
`OldValues`	`nvarchar(max)`	yes	JSON
`NewValues`	`nvarchar(max)`	yes	JSON
`Changes`	`nvarchar(max)`	yes	JSON diff
`IpAddress`	`nvarchar(50)`	yes
`CorrelationId`	`uniqueidentifier`	yes	Indexed; threads back to `RequestLogs`

Indexes: IX (OccurredAt), IX (EntityName, EntityId), IX (UserId), IX (CorrelationId).

Retention: 7 years (operator-driven cleanup).

`RequestLogs`

Not a BaseEntity. Lives in Persistence/Entities/RequestLog.cs.

Column	Type	Null	Notes
`Id`	`uniqueidentifier`	no	PK
`CorrelationId`	`uniqueidentifier`	no	Indexed
`UserId`	`uniqueidentifier`	yes	Null for anonymous
`Method`	`nvarchar(10)`	no
`Path`	`nvarchar(500)`	no
`QueryString`	`nvarchar(2000)`	yes
`StatusCode`	`int`	no
`DurationMs`	`int`	no
`IpAddress`	`nvarchar(50)`	yes
`UserAgent`	`nvarchar(500)`	yes
`OccurredAt`	`datetime2`	no	Indexed

Retention: 90 days.

`CodeSequences`

Not a BaseEntity. Lives in Persistence/Entities/CodeSequence.cs. Backs CodeGeneratorService.NextSequenceAsync.

Column	Type	Null	Notes
`Id`	`uniqueidentifier`	no	PK
`EntityType`	`nvarchar(50)`	no	E.g. `"Asset"`
`Period`	`nvarchar(20)`	no	E.g. `"2026"`
`LastValue`	`bigint`	no	Monotonic increment

Index: UX (EntityType, Period).

Reads use WITH (UPDLOCK, ROWLOCK) to serialize concurrent inserts.

Migration history

The repository ships a single consolidated migration (InitialCreate) that captures the entire schema documented above. The historical step-by-step migration chain was squashed on 2026-05-04 — earlier migration files no longer exist on disk.

Going forward, new schema changes are added incrementally on top of this baseline:

dotnet ef migrations add MyChange \
    --project src/AssetTracking.Persistence \
    --startup-project src/AssetTracking.API

AppDbContextModelSnapshot.cs reflects the cumulative state and is updated automatically by dotnet ef migrations add.

Manual SQL helpers

src/AssetTracking.Persistence/Migrations/Manual_SoftDeleteOrphanedAssetChildren.sql — operator-run script used after a master-data deletion that leaves orphan rows in AssetDetails, history tables, etc. Not part of the migration history; idempotent and safe to re-run.

Cleanup of obsolete permissions

DatabaseSeeder.obsoleteKeys (in Persistence/Seeding/DatabaseSeeder.cs) is the single source of truth for permission keys we used to ship and have since removed. Rows in Permissions whose Key is in that list are hard-deleted on every startup — along with any RolePermissions and UserPermissions referencing them. See 02 §Identity.

Where to go next

If you want…	See
The C# entity classes themselves	02-backend-modules
Endpoint URLs and request/response shapes	07-api-reference
Build/migration/seed commands	06-operations