Leave Indexing and Query Patterns
Summary
The current model has 16 explicit indexes: four unique and 12 non-unique. Query methods are tracked by default and mostly unpaginated.
Audience
Backend developers, QA, database reviewers, architects, and support engineers.
Reference Content
| Object | Verified index columns | Unique | Query use | Source |
|---|---|---|---|---|
| LeaveRequests | workflow instance; tenant/labor/from; tenant/status | No | ID, approved overlap, status/date/labor, newest/date order | microservices/src/leave-service/Infrastructure/LeaveDbContext.cs |
| LeaveBalances | tenant/user/type/year | Yes | exact balance and ordered list | microservices/src/leave-service/Infrastructure/Persistence.cs |
| LeaveTypes | tenant/name | Yes | exact name, name order | microservices/src/leave-service/Application/LeaveWorkflows.cs |
| LeavePolicies | tenant/type | Yes | active type resolution | microservices/src/leave-service/Infrastructure/Persistence.cs |
| Holidays | tenant/date | No | request date range and date ordering | microservices/src/leave-service/Application/LeaveWorkflows.cs |
| WeeklyOffs | tenant/day | No | full tenant list and ID order | microservices/src/leave-service/Infrastructure/Persistence.cs |
| EmployeeProfiles | tenant/user; tenant/external employee | No | labor, user, external employee, name order | microservices/src/leave-service/Messaging/EmployeeProfileConsumer.cs |
| CarryForwards | tenant/user/type/target year | No | repository query root only | microservices/src/leave-service/Infrastructure/LeaveDbContext.cs |
| Encashments | tenant/user/type/year | No | repository query root only | microservices/src/leave-service/Infrastructure/LeaveDbContext.cs |
| LeaveTimeline | tenant/labor/occurrence | No | newest-first, optional labor, maximum 500 | microservices/src/leave-service/Application/LeaveWorkflows.cs |
| AuditLogs | tenant/occurrence | No | write path; no public query confirmed | microservices/src/leave-service/Infrastructure/LeaveDbContext.cs |
| OutboxMessages | event identity; status/occurrence | Event identity only | Pending/Failed oldest-first bounded batch | microservices/src/leave-service/Infrastructure/Persistence.cs |
No AsNoTracking usage is present in Leave repository/query paths, so reads use EF tracking by default. General Leave, balance, type, policy, Holiday, weekly-off, and workspace lists have no pagination. Timeline and outbox polling are bounded. No performance guarantee follows from these indexes; execution plans, statistics, data distribution, and production volumes were not inspected.
Source References
- microservices/src/leave-service/Infrastructure/LeaveDbContext.cs
- microservices/src/leave-service/Infrastructure/Persistence.cs
- microservices/src/leave-service/Application/LeaveWorkflows.cs
- microservices/src/leave-service/Messaging/EmployeeProfileConsumer.cs
Related Articles
See Also
Keywords
- Index inventory
- Query patterns
- Pagination gaps
Revision Information
- Status: Draft
- Last reviewed: 2026-07-15
- Review cycle: Quarterly