Database Indexes

Overview

Critical indexes for multi-dimensional security queries.

Organizational Indexes

-- Direct org-scoped entities
CREATE INDEX idx_event_org_id ON event(org_id);
CREATE INDEX idx_document_org_id ON document(org_id);

-- Linked org lookups
CREATE INDEX idx_linked_org_user_active
    ON linked_org(org_user_id, active);
CREATE INDEX idx_linked_org_composite
    ON linked_org(organisation_id, access_level, active);

Personal Indexes

-- Direct person-scoped entities
CREATE INDEX idx_person_profile_person_id
    ON person_profile(person_id);
CREATE INDEX idx_event_entry_person_id
    ON event_entry(person_id);

-- Linked person lookups
CREATE INDEX idx_linked_person_from_active
    ON linked_person(from_principal_id, active);
CREATE INDEX idx_linked_person_composite
    ON linked_person(to_person_id, access_level, active);

Composite Indexes for Dual-Scoped

CREATE INDEX idx_event_entry_org_person
    ON event_entry(event_id, person_id);

Foreign Key Indexes

CREATE INDEX idx_race_event_id ON race(event_id);
CREATE INDEX idx_result_race_id ON result(race_id);