Optimize LLM Catalog queries with eager loading + add CLAUDE.md query guidelines

[lbedner]

Summary

Optimize LLM service queries to use explicit JOINs and eager loading, reducing DB round-trips. Also update CLAUDE.md with query pattern guidelines so future development (human and AI) follows the optimized patterns.

Why This Matters

1. Scale readiness - Reduces DB round-trips for API use cases (3 queries → 1)
2. Pattern establishment - Sets the "right way" for future queries that AI agents will follow
3. Principle - Clean queries even if caching handles 99% of traffic

Current State

Function	Current Queries	Problem
get_model_info()	3 (model, price, modalities)	3 round-trips per call
get_current_config()	3 (model, price, modalities)	3 round-trips per call
_load_vendors/models/etc()	~100 (1 per item)	N+1 during seeding

Note: list_models() is already optimized with batch fetching.

Proposed Changes

1. llm_service.py - Use eager loading

# Before: 3 queries
model = (await session.exec(select(Model).where(...))).first()
prices = (await session.exec(select(Price).where(...))).all()
modalities = (await session.exec(select(Modality).where(...))).all()

# After: 1 query with eager loading
stmt = (
    select(LargeLanguageModel)
    .options(
        selectinload(LargeLanguageModel.llm_vendor),
        selectinload(LargeLanguageModel.llm_prices),
        selectinload(LargeLanguageModel.modalities),
    )
    .where(LargeLanguageModel.model_id == model_id)
)
model = (await session.exec(stmt)).first()
latest_price = max(model.llm_prices, key=lambda p: p.effective_date) if model.llm_prices else None

2. llm_fixtures.py - Pre-load and check in-memory

# Before: N queries
for vendor_data in VENDORS:
    existing = session.exec(select(Vendor).where(...)).first()  # Query per item!

# After: 1 query + in-memory lookup
existing_vendors = session.exec(select(LLMVendor)).all()
existing_names = {v.name for v in existing_vendors}
for vendor_data in VENDORS:
    if vendor_data["name"] not in existing_names:
        session.add(LLMVendor(**vendor_data))

3. CLAUDE.md - Add query guidelines section

Add "Database Query Patterns" section with good/bad examples for:

• Eager loading with selectinload()
• Batch existence checks (pre-load pattern)

This establishes the pattern for AI coding agents to follow.

Files to Modify

• aegis/templates/cookiecutter-aegis-project/{{cookiecutter.project_slug}}/app/services/ai/llm_service.py
• aegis/templates/cookiecutter-aegis-project/{{cookiecutter.project_slug}}/app/services/ai/fixtures/llm_fixtures.py
• CLAUDE.md

Testing

• Existing tests should pass (behavior unchanged)
• make check and make test-template