Indexing Standard

Purpose

The Indexing Standard defines how future database specifications should propose and review indexes.

Index Drivers

Driver	Meaning
Tenant access	TenantId and OrganizationId are common leading query constraints.
Lookup	Stable ID, external reference, or business lookup.
Workflow	Status, owner, due date, priority, and lifecycle state.
Uniqueness	Business uniqueness within module and tenant scope.
Reporting	Projection or read-model query pattern.
Integration	Provider callback, external ID, and correlation lookup.

Rules

• Indexes must be justified by query patterns, uniqueness, or integrity.
• Tenant-scoped queries should consider TenantId and OrganizationId in index design.
• Every unique constraint must state its business scope.
• Avoid indexing every column by default.
• Review write-heavy tables for index cost.
• Outbox and audit tables need indexes for status, occurred time, correlation, and retention queries.

Index Review Flow

flowchart LR
    Query[Query Pattern]
    Scope[Tenant and Organization Scope]
    Cardinality[Cardinality]
    Cost[Write and Storage Cost]
    Index[Index Decision]

    Query --> Scope --> Cardinality --> Cost --> Index

Non-Goals

This document does not define final index names, PostgreSQL index types, partial index syntax, or physical tuning.