Database Indexing

Optimize query performance with indexes:

indexes: [
  // Single field index
  { fields: ['name'], unique: false },

  // Unique index — one holder per organization
  { fields: ['email'], unique: 'organization' },

  // Unique index — one holder across the whole installation
  { fields: ['hostname'], unique: 'global' },

  // Compound index
  { fields: ['type', 'is_active'], unique: false },

  // Lookup field index
  { fields: ['owner'], unique: false },
]

Two ways to say "unique" — one vocabulary for both

Uniqueness can be declared in two places: on a field, or as a declared index. Both use the same word to say which boundary the value must be unique within (ADR-0120):

// Field level — the scope is the same word in both places
email: Field.email({ unique: 'organization' }),   // one contact per org
hostname: Field.text({ unique: 'global' }),       // one holder, platform-wide

indexes: [
  // per organization — you do NOT list organization_id yourself
  { fields: ['department', 'code'], unique: 'organization' },
  // platform-wide — exactly the columns you list
  { fields: ['source', 'dedup_key'], unique: 'global' },
]

Two rules make the choice safe to write and safe to deploy:

1. You state a business boundary, never a deployment shape. The same app package runs unmodified under every tenancy posture — no index shape reads the posture, so moving a deployment between postures has no schema consequence.
2. 'organization' is NULL-safe. Rows with no organization (platform rows, and every row on a single-organization deployment) collapse into one platform bucket and are unique among themselves. Without this a plain (organization_id, …) composite enforces nothing on those rows, because SQL UNIQUE treats every NULL as distinct.

// ⚠️ contradictory — the global index wins, the per-organization scope is dead
email: Field.email({ unique: 'organization' }),
indexes: [{ fields: ['email'], unique: 'global' }],

Already have a hand-written organization composite?

Metadata written before the vocabulary existed spells the per-organization constraint by listing the column itself:

// the legacy spelling — still valid, still materialized exactly as written
indexes: [{ fields: ['name', 'organization_id'], unique: true }]

This keeps working forever and forces no migration, so os lint only suggests the change (unique/legacy-organization-composite). Opting in is worth it because it closes the NULL hole above — keep fields exactly as they are and change the scope word:

indexes: [{ fields: ['name', 'organization_id'], unique: 'organization' }]

The listed organization column becomes NULL-safe in place; no second organization key part is added. Because this genuinely tightens the constraint, it surfaces as a recreate_index migration guarded by a duplicate pre-flight probe — if rows the old index wrongly admitted are still there, os migrate plan reports them instead of failing a boot. See CLI · migrations.

Installing an app into an isolated deployment

Under the isolated posture, organizations are separate customers. A 'global' unique on an app's own object therefore constrains across customers — right for a DNS hostname or an external provider id, almost never right for a business rule. Installing such an app stops and lists each index so the installer can confirm it or rewrite it to 'organization'; the answer is recorded in the install manifest and never asked again. os doctor and os migrate plan report the same finding for apps installed before that check existed, or for environments whose posture changed afterwards.

When to Add Indexes

✅ Add indexes for:

• Lookup/reference fields
• Fields used in filters
• Fields used for sorting
• Fields in WHERE clauses
• High-cardinality fields

❌ Avoid indexes for:

• Low-cardinality fields (e.g., boolean)
• Fields rarely queried
• Frequently updated fields

See also

• Data Modeling overview
• Schema Design