Asset Indexes and Constraints

Summary

Source-backed inventory of every non-primary-key index and database constraint in the Asset bounded context: 14 non-PK indexes (3 unique, 7 composite, 6 tenant-prefixed), plus the explicit evidence that asset-code and serial-number uniqueness are not enforced by the database.

Audience

Backend engineers, database reviewers, architects, QA, support, operations, security reviewers, and product owners.

Overview

The schema was created by a single initial migration. Twelve indexes are configured explicitly in OnModelCreating and two are convention-generated foreign-key indexes. Uniqueness is enforced only for the compatibility identity and the two messaging event ids. Business-code uniqueness lives in the application layer, and serial numbers have no uniqueness at all.

Confirmed persistence behavior

Verified index inventory (14 non-PK)

Index	Entity	Fields	Unique	Composite	Tenant-prefixed	Purpose	Source
UX_Assets_CompatId	Asset	CompatId	Yes	No	No	Compatibility-id uniqueness + lookup	AssetDbContext
IX_Assets_AssetCode	Asset	AssetCode	No	No	No	Code lookup (non-unique)	AssetDbContext
IX_Assets_Tenant_Status	Asset	TenantId, Status	No	Yes	Yes	Tenant + status filtering	AssetDbContext
IX_Assets_Tenant_Type	Asset	TenantId, AssetType	No	Yes	Yes	Tenant + type filtering	AssetDbContext
IX_Assets_WorkflowInstance	Asset	WorkflowInstanceId	No	No	No	Workflow-callback lookup	AssetDbContext
IX_AssetAssignmentHistory_AssetId	History	AssetId	No	No	No	FK index (convention)	snapshot
IX_AssetHistory_Tenant_Asset	History	TenantId, AssetId	No	Yes	Yes	Tenant + asset history	AssetDbContext
IX_AssetDocumentReferences_AssetId	Document ref	AssetId	No	No	No	FK index (convention)	snapshot
IX_AssetDocuments_Tenant_Asset	Document ref	TenantId, AssetId	No	Yes	Yes	Tenant + asset documents	AssetDbContext
IX_AssetAuditLogs_TenantId_OccurredAtUtc	Audit	TenantId, OccurredAtUtc	No	Yes	Yes	Tenant audit by time	AssetDbContext
IX_AssetTimeline_Tenant_Occurred	Timeline	TenantId, OccurredAtUtc	No	Yes	Yes	Tenant timeline by time	AssetDbContext
IX_OutboxMessages_EventId	Outbox	EventId	Yes	No	No	Event-id uniqueness	shared kernel
IX_OutboxMessages_Status_OccurredAtUtc	Outbox	Status, OccurredAtUtc	No	Yes	No	Relay polling order	shared kernel
UX_ProcessedEvents_EventId	Processed event	EventId	Yes	No	No	Idempotency uniqueness	AssetDbContext

Uniqueness evidence

• Asset code: uniqueness is application-layer only (CodeExistsAsync over the tenant-filtered query). IX_Assets_AssetCode is non-unique — there is no database unique constraint on the code. A source comment notes a unique index here would be global across tenants, which is why it is not applied.
• Serial number: no uniqueness of any kind — no index and no constraint.
• CompatId: unique via UX_Assets_CompatId.
• EventId: unique on both the outbox (IX_OutboxMessages_EventId) and the processed-event ledger (UX_ProcessedEvents_EventId).
• Status filtering has no dedicated status-only index; status queries use the composite IX_Assets_Tenant_Status.
• Assignment history is indexed by AssetId (convention FK) and by the composite IX_AssetHistory_Tenant_Asset.
• Document references are indexed by AssetId (convention FK) and the composite IX_AssetDocuments_Tenant_Asset.

Totals

• Total non-PK indexes: 14
• Unique: 3 (UX_Assets_CompatId, IX_OutboxMessages_EventId, UX_ProcessedEvents_EventId)
• Composite: 7
• Tenant-prefixed: 6
• Non-unique: 11

Database-enforced constraints (beyond indexes)

• Primary-key uniqueness on all 7 tables.
• Two required cascade foreign keys (history → asset, document reference → asset). See Keys and Relationships.
• NOT NULL and max-length on required columns.

Classification

Implemented index and constraint set with Transitional gaps in business-key enforcement.

Requires confirmation

The following are not present in the verified inventory and are recommendations only — schema changes to confirm, not documented behavior:

• A per-tenant unique constraint on asset code (currently application-only).
• Any uniqueness policy for serial numbers (currently none).
• A dedicated status-only or assignee-only index (currently status is served by the tenant-status composite; assignee columns are not separately indexed).
• A concurrency token / row version (none present anywhere).

Diagram

Related Articles

• Asset database documentation
• Keys and Relationships

See Also

• Entity Mappings
• Tenant Isolation
• Value-Object Conversions

Keywords

• Asset indexes
• Indexes and Constraints
• Indexes constraints

Source References

• microservices/src/asset-service/Infrastructure/AssetDbContext.cs
• microservices/src/asset-service/Infrastructure/Migrations/AssetDbContextModelSnapshot.cs
• microservices/src/asset-service/Infrastructure/Migrations/20260707123634_InitialAssetSchema.cs
• microservices/src/asset-service/Domain/Repositories.cs

Revision Information

• Status: Draft
• Last reviewed: 2026-07-17
• Review cycle: Quarterly