Attendance Database Schema Reference
Summary
The current Attendance EF model contains 13 persisted objects, 18 indexes, six unique indexes, one modeled foreign key, and global tenant filters on 12 objects.
Audience
- Backend developers, QA engineers, database reviewers, architects, and schema-automation authors
Reference Content
Source-reference keys
| Key | Repository-relative source |
|---|---|
| S1 | microservices/src/attendance-service/Infrastructure/AttendanceDbContext.cs |
| S2 | microservices/src/attendance-service/Infrastructure/Migrations/AttendanceDbContextModelSnapshot.cs |
| S3 | microservices/src/attendance-service/Infrastructure/Persistence.cs |
| S4 | microservices/src/attendance-service/Backfill/ShiftPolicyBackfillTypes.cs |
DbContexts
| Context | Classification | Responsibility | Source |
|---|---|---|---|
AttendanceDbContext | Runtime/design model | All 13 Attendance-owned and operational objects | S1 |
ShiftPolicyBackfillDbContext | Transitional specialized target | Same model, but preserves legacy Shift compatibility identity | S4 |
The design-time factory creates AttendanceDbContext; it is not another persisted model.
Persisted objects
| Group | Object | Purpose / classification | Tenant scope | ID | Relationships | Uniqueness / indexes | Source |
|---|---|---|---|---|---|---|---|
| Attendance core | AttendanceRecords | Daily employee outcome / master | Filtered | Integer | Logical punch/reopen links | employee/date and approval lookup, non-unique | S1 |
| Punch/reopening | AttendancePunches | Captured punch aggregate / master | Filtered | Integer | Logical employee/date | employee/time index | S1 |
| Punch/reopening | AttendanceReopenRequests | Correction workflow / master | Filtered | Integer | Optional logical AttendanceId | employee/date/status index | S1 |
| Time Office | Shifts | Work window / master, Transitional | Filtered | Integer + compatibility integer | Logical assignments | unique compatibility; tenant/active | S1 |
| Time Office | WeeklyOffRules | Tenant off-day / master | Filtered | Integer | None | unique tenant/day | S1 |
| Time Office | AttendancePolicies | Tenant calculation policy / Foundation | Filtered | Integer | Owns rules | tenant/active; unique filtered default | S1 |
| Time Office | AttendancePolicyRules | Policy child / Foundation | Filtered | Integer | Required policy FK, cascade | unique policy/key | S1 |
| Time Office | EmployeeShiftAssignments | Effective roster / master, Transitional | Filtered | Integer | Logical Shift and employee refs | employee/from; Shift lookup | S1 |
| Employee profile | EmployeeProfiles | Attendance-local read model | Filtered | External integer | External employee ref | tenant/active | S1 |
| Payroll snapshots | PayrollApprovedSnapshots | Period snapshot / read model | Filtered | Integer | External employee/period refs | unique employee/period | S1 |
| Audit/timeline | AttendanceAuditLogs | Technical audit / history | Filtered | GUID | Logical entity ref | tenant/time | S1 |
| Audit/timeline | AttendanceTimeline | Business activity / history | Filtered | GUID | Logical entity ref | tenant/time | S1 |
| Messaging | OutboxMessages | Integration delivery state / operational | No global filter | GUID | Event context only | unique event; status/time | S1 |
Relationship and constraint inventory
| Relationship or rule | Database enforcement | Source |
|---|---|---|
| Policy to rules | Required FK with cascade delete | S1, S2 |
| Assignment to Shift | No FK; application lookup | S1, S3 |
| Reopen to Attendance | Nullable identifier; no FK | S1, S2 |
| Punch to Attendance | Employee/date convention; no FK | S1, S2 |
| Employee references | External numeric identifiers; no FK | S1, S2 |
| Daily Attendance uniqueness | No unique constraint | S1, S2 |
| Pending reopen / assignment overlap | Application checks only | S3 |
Migration inventory
| Migration | Objects/change | Source |
|---|---|---|
| Initial Attendance schema | Seven initial objects and nine indexes | microservices/src/attendance-service/Infrastructure/Migrations/20260702135737_InitialAttendanceSchema.cs |
| Time Office foundation | Six objects, one FK, and nine indexes | microservices/src/attendance-service/Infrastructure/Migrations/20260709123504_AddShiftWeeklyOffPolicyFoundation.cs |
| Assignment compatibility user | One nullable column | microservices/src/attendance-service/Infrastructure/Migrations/20260709143830_AddEmployeeShiftAssignmentUserId.cs |
Query filters and transaction participants
| Participant | Behavior | Source |
|---|---|---|
| 12 business/history objects | privileged-or-current-tenant global filter | S1 |
| Outbox | unfiltered operational polling | S1, S3 |
| Aggregate state | tracked by repositories and committed by unit of work | S3 |
| Audit/timeline | participates when added before the save | S3 |
| Domain-event outbox | added before base save | S1 |
| Payroll lock service | external; outside database transaction | S3 |
Compatibility components
| Component | Role | Source |
|---|---|---|
| Specialized target context | Preserve Shift compatibility identity | S4 |
| Monolith source reader | Read-only source access | microservices/src/attendance-service/Backfill/AttendanceMonolithSourceReader.cs |
| Runner and validators | Batch, resume, dry-run, and parity | microservices/src/attendance-service/Backfill/ShiftWeeklyOffBackfillRunner.cs |
Source References
microservices/src/attendance-service/Infrastructure/AttendanceDbContext.csmicroservices/src/attendance-service/Infrastructure/Migrations/AttendanceDbContextModelSnapshot.csmicroservices/src/attendance-service/Backfill/ShiftPolicyBackfillTypes.csmicroservices/src/attendance-service/Infrastructure/Persistence.cs
Related Articles
See Also
Keywords
- Attendance persistence
- EF Core
- PostgreSQL
Revision Information
- Status: Draft
- Last reviewed: 2026-07-15
- Review cycle: Quarterly