---
id: add-missing-indexes-identity
name: Add Missing Indexes — Identity & Project Domain
status: completed
depends_on: []
scope: narrow
risk: low
impact: component
level: implementation
---

## Description

W03 (partial): Add missing indexes for the identity and project domain tables. Also remove the redundant index identified in the review.

Missing indexes:
- `projects`: `idx_projects_org_id` on `(orgId)` — find projects for an org
- `workspaces`: `idx_workspaces_project_id` on `(projectId)` — find workspaces for a project
- `spokes`: `idx_spokes_name` on `(name)` — look up spoke by name

Redundant index to remove:
- `api_keys`: `idx_api_keys_key_hash` is redundant with `unq_api_keys_key_hash` (UNIQUE constraint auto-creates an index)

## Acceptance Criteria

- [ ] `table-reference.md` indexes section includes `idx_projects_org_id`, `idx_workspaces_project_id`, `idx_spokes_name`
- [ ] Per-domain docs (`projects.md`, `spokes.md`) reference the new indexes
- [ ] `idx_api_keys_key_hash` removed from `table-reference.md` with a note that UNIQUE constraint covers it
- [ ] All new indexes have documented purpose (query pattern)

## References

- docs/reviews/storage-architecture-review-2026-04-21.md#W03
- docs/architecture/storage/table-reference.md:87-145
- docs/architecture/storage/identity.md
- docs/architecture/storage/projects.md
- docs/architecture/storage/spokes.md

## Notes

> To be filled by implementation agent

## Summary

> To be filled on completion