Attendance Indexing and Query Patterns

Summary

The current model configures 18 indexes across 13 objects. They target tenant/date, workflow, effective-date, active/default, history-ordering, and outbox polling patterns.

Audience

• Backend developers, database reviewers, QA engineers, and support engineers

Reference Content

Index inventory

Object	Confirmed index purpose	Enforcement
Attendance record	tenant/employee/date; tenant/date/approval/status	Both non-unique
Punch	tenant/employee/time	Non-unique chronological lookup
Reopen request	tenant/employee/date/status	Non-unique pending lookup
Employee profile	tenant/active	Non-unique availability lookup
Payroll snapshot	tenant/employee/period	Unique
Shift	global compatibility identity; tenant/active	Unique + non-unique
Weekly off	tenant/day	Unique
Policy	tenant/active; filtered tenant default	Non-unique + unique
Policy rule	policy/key	Unique
Shift assignment	tenant/employee/from; tenant/Shift	Non-unique
Audit and timeline	tenant/time each	Non-unique
Outbox	event identity; status/time	Unique + non-unique

Queries commonly use AsNoTracking for lists and projections, order daily Attendance by date, punches by time, assignments by latest effective start, history newest-first, and outbox oldest-first. Timeline/audit and outbox polling use bounded Take; most Attendance list queries do not expose database pagination and this gap is Requires confirmation for large tenants.

No performance guarantee follows from index presence. Employee/date uniqueness is not enforced despite the corresponding index.

Source References

• microservices/src/attendance-service/Infrastructure/AttendanceDbContext.cs
• microservices/src/attendance-service/Infrastructure/Persistence.cs
• microservices/src/attendance-service/Application/Services/AttendanceQueries.cs
• microservices/src/attendance-service/Application/Services/ShiftPolicyQueries.cs

Related Articles

• Attendance database
• Attendance technical documentation
• Attendance API documentation

See Also

• Attendance business documentation
• Attendance overview

Keywords

• Attendance persistence
• EF Core
• PostgreSQL

Revision Information

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