Indexing and Query Patterns
Summary
Verified indexes and source-confirmed read, report, history, compatibility, and outbox access patterns.
Audience
Backend engineers, database reviewers, architects, QA, support, operations, security reviewers, and product owners.
Overview
The current snapshot contains fourteen indexes: three unique and eleven non-unique. Twelve are explicitly configured; two parent-key indexes are convention generated for child relationships.
Confirmed persistence behavior
| Logical item | Classification or owner | Tenant/filter | Identity or mapping | Relationship or purpose | Index or behavior |
|---|---|---|---|---|---|
| ReviewCycle | Tenant plus status | Non-unique | Scoped lifecycle lists | ||
| Appraisal | Compatibility identity | Unique global | Compatibility lookup | ||
| Appraisal | Tenant plus status | Non-unique | Lifecycle lists | ||
| Appraisal | Tenant plus cycle | Non-unique | Cycle reports and lists | ||
| Appraisal | Workflow identity | Non-unique | Callback correlation | ||
| Goal | Parent identity | Non-unique convention | Relationship loading | ||
| Goal | Tenant plus parent | Non-unique | Scoped child access | ||
| Review | Parent identity | Non-unique convention | Relationship loading | ||
| Review | Tenant plus parent | Non-unique | Scoped child access | ||
| Audit | Tenant plus occurrence | Non-unique | Newest-first history | ||
| Timeline | Tenant plus occurrence | Non-unique | Newest-first history | ||
| Outbox | Event identity | Unique | Outbound deduplication | ||
| Outbox | Status plus occurrence | Non-unique | Relay polling | ||
| Processed event | Event identity | Unique | Inbound deduplication |
- Appraisal command loads include goals and reviews.
- List queries use bounded result sets and explicit projection.
- Reports filter by cycle and status and project directly.
- Audit, timeline, and outbox diagnostics use no-tracking newest-first projections.
- Compatibility loads by globally unique compatibility identity.
- No cache, cursor pagination, full-text index, employee-cycle index, or performance guarantee is implemented.
Classification
Implemented baseline indexing with Foundation scaling controls.
Requires confirmation
Index usage measurement, pagination, cache policy, reporting isolation, hot-path thresholds, and tenant-prefixed uniqueness require confirmation.
Related Articles
See Also
Keywords
- Performance persistence
- Indexing and Query Patterns
- Draft database documentation
Source References
microservices/src/performance-service/Infrastructure/PerformanceDbContext.csmicroservices/src/performance-service/Infrastructure/Migrations/PerformanceDbContextModelSnapshot.csmicroservices/src/performance-service/Application/Queries/PerformanceQueries.csmicroservices/src/performance-service/Application/Queries/PerformanceReports.csmicroservices/src/performance-service/Infrastructure/Persistence.cs
Revision Information
- Status: Draft
- Last reviewed: 2026-07-16
- Review cycle: Quarterly