Skip to main content

Payroll Indexing and Query Patterns

Summary​

Primary, unique, search, projection, outbox, audit, timeline, and report query indexes.

Audience​

Payroll developers, QA and support engineers, solution and security architects, and implementation partners.

Reference Content​

The sections below describe the confirmed logical storage contract and its implementation boundaries.

Purpose​

Inventory confirmed indexes and the query shapes they support.

Ownership​

PayrollDbContext owns the model configuration; the current snapshot confirms the resulting indexes.

Lifecycle​

Every persisted object has a primary key. The current model has 23 explicit secondary indexes: 10 unique and 13 non-unique.

Persisted fields​

Indexed field groups cover tenant/period/status, tenant/run/employee, tenant/employee/time, tenant/component, projection identities, event identity/type/time, outbox status/time, and audit chronology.

Relationships​

There are zero foreign-key indexes because the model defines zero foreign keys.

Constraints​

Unique indexes exist for period identity; run-detail identity; payslip identity; advance-summary identity; statutory-setting identity; workforce legacy identity; attendance and leave period projection identity; expense claim identity; and outbox event identity.

Indexes​

Non-unique search indexes cover runs, calculations, adjustments, advance transactions (two), overtime, salary components, audit, timeline, workforce extracted identity, expense period lookup, processed events, and outbox dispatch. There is no dedicated report-only index; report queries reuse these indexes where their predicates align.

Tenant behavior​

Twenty-one of 23 explicit indexes begin with tenant identity. The two exceptions are outbox event identity and outbox status/time.

Audit behavior​

Audit chronological lookup and employee timeline lookup each have a dedicated composite index. No action/entity audit index or run timeline index is configured.

Integration usage​

Projection unique indexes support idempotent upsert identities; processed-event and outbox indexes support integration processing patterns.

Known limitations​

Index presence does not prove query-plan use. No measured workload, execution plan, or performance baseline is included in source.

Requires confirmation​

  • Production query-plan and workload review.
  • Whether report, run timeline, audit classification, and relay workloads need additional indexes.

Source References​

  • microservices/src/payroll-service/Infrastructure/PayrollDbContext.cs
  • microservices/src/payroll-service/Domain/PayrollEntities.cs
  • microservices/src/payroll-service/Infrastructure/Migrations/PayrollDbContextModelSnapshot.cs

See Also​

Keywords​

  • Payroll database
  • Payroll Indexing and Query Patterns
  • Payroll persistence

Revision Information​

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