Expense Indexing and Query Patterns
Summary
The EF model defines primary, unique, relationship-supporting, tenant, workflow, and relay indexes. The catalog below lists their mapped properties without exposing physical database identifiers.
Audience
Backend developers, database reviewers, QA, architects, DevOps, support, and implementation teams.
Confirmed persistence
| Persisted object | Properties | Kind | Confirmed query use |
|---|---|---|---|
| ExpenseClaim | Id | Primary key | Aggregate lookup |
| ExpenseClaim | CompatId | Unique secondary index | Compatibility lookup and backfill identity |
| ExpenseClaim | TenantId, Status | Secondary composite index | Tenant-scoped status listing |
| ExpenseClaim | WorkflowInstanceId | Secondary index | Workflow correlation |
| ExpenseReceiptReference | Id | Primary key | Receipt-reference identity |
| ExpenseReceiptReference | ExpenseClaimId | Relationship-supporting index | Claim-to-receipt relationship |
| ExpenseReceiptReference | TenantId, ExpenseClaimId | Secondary composite index | Tenant-scoped claim receipt lookup |
| ExpenseAuditLog | Id | Primary key | Evidence identity |
| ExpenseAuditLog | TenantId, OccurredAtUtc | Secondary composite index | Tenant-scoped recent evidence |
| ExpenseTimeline | Id | Primary key | Timeline identity |
| ExpenseTimeline | TenantId, OccurredAtUtc | Secondary composite index | Tenant-scoped recent activity |
| OutboxMessage | Id | Primary key | Message identity and relay update |
| OutboxMessage | EventId | Unique secondary index | Duplicate event prevention |
| OutboxMessage | Status, OccurredAtUtc | Secondary composite index | Oldest-first Pending/Failed relay scan |
| ProcessedIntegrationEvent | Id | Primary key | Ledger-record identity |
| ProcessedIntegrationEvent | EventId | Unique secondary index | Consumer idempotency |
Claims list by optional status or employee reference, recent-first with a fixed cap. Detail reads load receipt children. Relay reads select Pending or Failed records oldest-first. Evidence reads are recent-first and no-tracking.
No employee/date composite index, general pagination, full-text search, cache, partitioning, or performance target is confirmed.
Requires confirmation
Production migration governance, retention, archival, masking, performance targets, concurrency roadmap, operational-store access, and rollback ownership remain unconfirmed unless narrowed above.
Source References
microservices/src/expense-service/Infrastructure/ExpenseDbContext.csmicroservices/src/expense-service/Application/Queries/ExpenseQueries.csmicroservices/src/expense-service/Infrastructure/Persistence.cs
Related Articles
See Also
Keywords
- Expense database
- Indexing and Query Patterns
Revision Information
- Status: Draft
- Last reviewed: 2026-07-16
- Review cycle: Quarterly