Explore Subagents for Investigation

Capsule overview

There's a pattern that separates developers who use AI effectively from those who don't: investigate before acting. When you face an unfamiliar codebase or a complex problem, your first instinct shouldn't be "ask Claude Code to fix it." It should be "ask Claude Code to explain how this works."

Claude Code has exploration capabilities that let you navigate files, follow imports, map dependencies, and understand a project's architecture — all before changing a single line. It's like having a senior colleague who has already read the entire codebase and can explain any part to you in 30 seconds.

But the key is in the framing. "Explain the code to me" produces generic answers. "Show me how a request flows from the POST /api/tasks endpoint until it's saved in the database" produces a precise map you can use to make decisions. In this capsule you learn to formulate investigation queries that produce actionable information.

Investigate vs Act: The Fundamental Difference

The anti-pattern: acting without investigating

Scenario: You receive a bug report — "Permissions aren't working right"

Developer with no prior investigation:
1. Opens the file they think is the problem
2. Asks Claude Code: "Fix the permissions bug"
3. Claude Code changes something
4. The bug persists or a new one appears
5. Repeats 3 more times
6. 45 minutes later, still not resolved

Why does it fail?
→ Neither you nor Claude Code understand how the permission system works
→ The "fix" is a patch that doesn't attack the root cause
→ Each attempt may break something else

The correct pattern: investigate, understand, act

Same scenario: "Permissions aren't working right"

Developer with prior investigation:
1. Asks Claude Code: "Explain how the permission system works
   in this project. Where are the roles defined? Where are they
   checked? What middleware applies?"
2. Claude Code navigates the codebase, finds 4 relevant files
3. Now you know: roles in models.py, checking in dependencies.py,
   middleware in middleware.py, and the config in settings.py
4. With that map, you diagnose: "The middleware checks roles but
   the endpoint doesn't pass the token's role to the middleware"
5. You edit 3 lines in dependencies.py
6. 15 minutes total, root-cause fix

Why does it work?
→ The investigation gives you a map of the system
→ With the map, the diagnosis is precise
→ The fix attacks the root cause, not a symptom

The difference isn't the tool — it's the prior step of understanding.

Claude Code's Exploration Capabilities

What Claude Code can do when it investigates

When you ask Claude Code to explore your codebase, it can:

• ✅ Navigate files — Open, read, and analyze any file in the project
• ✅ Follow imports — Trace where each module, class, or function comes from
• ✅ Map dependencies — Identify which files depend on which
• ✅ Explain flows — Follow execution from a point A to a point B
• ✅ Find patterns — Search all the places where a certain function, class, or pattern is used
• ✅ Analyze structure — Explain how the project is organized

What it CANNOT do

• ❌ Execute code — It can't run your application to see what happens
• ❌ See runtime state — It can't see variables, DB connections, or memory state
• ❌ Access external services — It can't make requests to your API or database
• ❌ See the full git history — It can see the current files but can't walk the entire commit history

The correct mental model

Think of Claude Code as an investigator who has access to all the documents of a case but can't go to the crime scene:

Investigator (Claude Code):
├── Can read all the case files (source code)
├── Can connect clues between documents (follow imports)
├── Can map relationships (dependencies)
├── Can create a report (flow explanation)
│
└── CANNOT go to the scene (execute code)
    CANNOT interrogate witnesses (runtime state)
    CANNOT recreate the events (reproduce bugs)

It's an advanced static analysis tool, not a runtime debugging tool. That makes it perfect for investigation prior to changes.

Types of Investigation and When to Use Each

Type 1: Understand a complete flow

When: You need to understand how a feature works from start to finish.

Example query:

Explain the complete authentication flow in this project.
From when the user sends their credentials until they receive
a JWT token. Which files are involved? Which functions
are called in what order?

What you get: A sequential map of the flow with the files and functions involved.

Example of a useful Claude Code answer:

Authentication flow:

1. POST /api/auth/login  (routers/auth.py:23)
   → Receives LoginRequest(email, password)
   
2. AuthService.authenticate()  (services/auth_service.py:15)
   → Looks up user by email in DB
   → Verifies password with bcrypt
   
3. TokenService.create_token()  (services/token_service.py:8)
   → Creates JWT with user_id, role, exp
   → Uses SECRET_KEY from config/settings.py
   
4. Response: {"access_token": "...", "token_type": "bearer"}

Files involved:
- routers/auth.py         → Endpoints
- services/auth_service.py → Auth logic
- services/token_service.py → Token generation
- models/user.py          → User model with password hash
- config/settings.py      → SECRET_KEY, ALGORITHM, EXPIRATION

Type 2: Find all the uses of a component

When: You need to understand the impact of changing something.

Example query:

Find all the places in this project where the user's role
is checked (role checking, permission checking). Include
middleware, decorators, dependency functions, and inline
checks in endpoints.

What you get: An exhaustive list of the points where authorization is checked.

Real application:

# Claude Code finds these 6 role-checking points:

# 1. dependencies/auth.py - Dependency injection for endpoints
async def get_current_user(token: str = Depends(oauth2_scheme)):
    payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
    return payload

# 2. dependencies/auth.py - Admin check
async def require_admin(user: dict = Depends(get_current_user)):
    if user.get("role") != "admin":
        raise HTTPException(status_code=403, detail="Admin required")

# 3. middleware/auth_middleware.py - Global middleware
class AuthMiddleware(BaseHTTPMiddleware):
    async def dispatch(self, request, call_next):
        if request.url.path in self.public_paths:
            return await call_next(request)
        # checks token...

# 4. routers/users.py:45 - Inline check
@router.delete("/users/{user_id}")
async def delete_user(user_id: int, current_user = Depends(get_current_user)):
    if current_user["role"] != "admin" and current_user["id"] != user_id:
        raise HTTPException(403)

# 5. routers/tasks.py:78 - Ownership check
@router.patch("/tasks/{task_id}")
async def update_task(task_id: int, current_user = Depends(get_current_user)):
    task = get_task(task_id)
    if task.owner_id != current_user["id"] and current_user["role"] != "admin":
        raise HTTPException(403)

# 6. services/report_service.py:12 - Check in service
def generate_report(user_role: str, report_type: str):
    if report_type == "financial" and user_role != "admin":
        raise PermissionError("Financial reports require admin role")

Now you know there are 6 role-checking points scattered across 5 files, with 3 different patterns (dependency injection, middleware, inline). If you need to change how permissions work, you know exactly where to look.

Type 3: Map the dependencies of a change

When: You want to change something and need to know what else is affected.

Example query:

If I change the structure of the User model in models/user.py 
(add a 'department' field), which other files do I need to 
modify? Which schemas, endpoints, and services use this model?

What you get: An impact analysis before making the change.

Impact of adding a 'department' field to User:

Files that need changes:
├── models/user.py         → Add department column
├── schemas/user.py        → Add field to UserCreate, UserResponse
├── migrations/             → New Alembic migration
├── routers/users.py       → Update the creation endpoint
└── services/user_service.py → Update the creation logic

Files that might need changes:
├── routers/admin.py       → If you filter users by department
├── services/report_service.py → If reports group by department
└── tests/test_users.py    → Update fixtures and assertions

Files that are NOT affected:
├── routers/auth.py        → Auth doesn't use department
├── routers/tasks.py       → Tasks depend on user_id, not department
└── middleware/            → Middleware doesn't access department

This is a 2-minute investment that saves 30 minutes of discovering broken dependencies after making the change.

Type 4: Investigate a bug before diagnosing

When: You have a bug and need to understand the context before trying to fix it.

Example query:

I have a bug: POST /api/tasks returns 500 when the user 
is an admin. It works fine for regular users. Before looking 
for the fix, I need to understand:
1. How is a task created? (complete flow)
2. Is there a difference in the code path between admin and regular user?
3. What validations are applied on creation?

What you get: Enough context for a precise diagnosis.

How to Formulate Effective Investigation Queries

The 5 rules of a good query

Rule 1: Be specific about what you want to know

❌ Bad:  "Explain this code to me"
✅ Good: "Explain how the POST /api/tasks endpoint validates 
         the input and persists it in the database"

Rule 2: Give context on why you're investigating

❌ Bad:  "Where is the User class used?"
✅ Good: "I'm going to add a 'department' field to the User class. 
         I need to know all the files that create, read, 
         or modify users to assess the impact of the change"

Rule 3: Ask for an actionable format

❌ Bad:  "How does authentication work?"
✅ Good: "Describe the authentication flow step by step, 
         indicating file and line number for each step. 
         Include what's validated at each point"

Rule 4: Scope it down

❌ Bad:  "Analyze the whole project"
✅ Good: "Analyze only the files in routers/ and services/ 
         related to the tasks feature"

Rule 5: Ask for what's missing, not what you already know

❌ Bad:  "Explain what FastAPI does"
✅ Good: "I already know the project uses FastAPI with SQLAlchemy.
         What I don't know is how the database session is 
         configured and whether it uses async or sync"

Anatomy of a professional investigation query

Optimal structure:

1. CONTEXT: What you know and why you're investigating
   "I'm reviewing a bug where admins can't create tasks"

2. SPECIFIC QUESTION: What you need to know
   "I need to understand the task creation flow and whether there are 
    differences in the code path for different roles"

3. DESIRED FORMAT: How you want the answer
   "List the files involved with the specific functions
    that are called, in execution order"

4. LIMITS: What you don't need
   "I don't need to understand authentication — I already know it. 
    Just the flow after auth"

Complete example: a well-formulated query

I'm investigating why the PATCH /api/tasks/{id} endpoint 
sometimes doesn't persist changes to the database. 
The bug is intermittent — it works most of the time.

I need to understand:
1. The complete PATCH flow: from the request to the commit
2. How the SQLAlchemy session is handled (is there an explicit commit?)
3. Whether there's any middleware or hook that could interfere with the session
4. Whether the session is shared between requests or is per-request

Show me the files and functions involved with the relevant
lines. I don't need to understand the GET or DELETE flow — 
just PATCH.

This query will produce a precise, focused investigation that saves you 20-30 minutes of reading code manually.

When to Use Subagents vs Manual Investigation

Not every investigation requires Claude Code. Sometimes it's faster to search manually. The decision depends on the context:

Use exploration subagents when:

✅ The codebase is new to you
   → You don't know where things are
   → Claude Code navigates faster than you

✅ The dependencies cross multiple files
   → One import leads to another that leads to another
   → Claude Code follows the chain automatically

✅ You need a complete map of a flow
   → The flow touches 5+ files
   → Claude Code can map everything in one query

✅ You don't know what to look for
   → "How does X work in this project?"
   → Claude Code explores and summarizes for you

✅ The project has unfamiliar conventions
   → Does it use the repository pattern? A service layer? Something custom?
   → Claude Code identifies the architectural patterns

Investigate manually when:

✅ You know exactly where the problem is
   → "The bug is on line 45 of task_service.py"
   → Open the file and read — faster than formulating a query

✅ It's a single file with no complex dependencies
   → A utility function, a helper, a config
   → Reading it takes 30 seconds

✅ You need to see runtime state
   → Variables, DB state, connections
   → Claude Code can't see this — use pdb or logging

✅ The codebase is yours and you know it well
   → You already know the architecture, you already know where to look
   → Grep is faster than formulating the query

✅ It's a simple text search
   → "Where is this variable used?"
   → grep or find-in-files is instant

Quick decision table

Complete Workflow: Investigate → Understand → Plan → Change

The 4-step flow

┌─────────────┐     ┌─────────────┐     ┌──────────────┐     ┌──────────┐
│ INVESTIGATE │ ──→ │ UNDERSTAND  │ ──→ │    PLAN      │ ──→ │  CHANGE  │
│             │     │             │     │              │     │          │
│ Subagent:   │     │ "Now I know │     │ "I need to   │     │ You run  │
│ "How does   │     │  how the    │     │  change X    │     │ the plan │
│  this       │     │  system     │     │  in file A,  │     │ with     │
│  work?"     │     │  works"     │     │  Y in file B"│     │ precision│
│             │     │             │     │              │     │          │
└─────────────┘     └─────────────┘     └──────────────┘     └──────────┘

Practical example: Add permission validation to an endpoint

Context: The DELETE /api/tasks/{id} endpoint lets any authenticated user delete any task. You need to add permission validation: only the owner or an admin can delete.

Step 1: Investigate

Query to Claude Code:

I need to add permission validation to the 
DELETE /api/tasks/{id} endpoint. Before making the change:

1. How is the DELETE endpoint currently implemented?
2. How do the other endpoints (PATCH, etc.) check permissions?
3. Is there any existing dependency injection or middleware
   for permission checking that I could reuse?
4. Does the Task model have an owner_id field or similar?

Claude Code's answer:

Investigation complete:

1. DELETE /api/tasks/{id} (routers/tasks.py:92)
   - Receives task_id and current_user (via Depends)
   - Calls task_service.delete_task(task_id)
   - Does NOT check ownership or role
   
2. PATCH /api/tasks/{id} (routers/tasks.py:67)
   - It does check: task.owner_id == current_user["id"]
   - But it doesn't allow admins — only the owner can edit
   
3. Existing dependencies (dependencies/auth.py):
   - get_current_user() → extracts the user from the token
   - require_admin() → checks role == "admin"
   - There is NO dependency for "owner or admin"
   
4. Task model (models/task.py:12):
   - Has owner_id (ForeignKey to users)
   - Relationship: owner = relationship("User")

Step 2: Understand

With this information, you now understand:

• The DELETE endpoint has no validation — any authenticated user can delete
• PATCH has partial validation (only owner, not admin)
• There's no reusable dependency for "owner or admin"
• The Task model does have owner_id, so the check is possible

Step 3: Plan

Plan based on the investigation:

1. Create a new dependency: require_owner_or_admin(task_id, current_user)
   → In dependencies/auth.py
   → Checks: task.owner_id == current_user["id"] OR role == "admin"

2. Apply it to the DELETE endpoint
   → Add Depends(require_owner_or_admin)

3. Also update PATCH
   → Replace the inline check with the new dependency
   → Now admins can edit too

4. Add tests for the 3 scenarios:
   → Owner can delete ✅
   → Admin can delete ✅
   → Another user cannot delete ❌ (403)

Step 4: Change

Now you execute the plan with precision because you know exactly which files to touch, which pattern to follow (you saw how PATCH does it), and which dependency to create.

from fastapi import Depends, HTTPException
from sqlalchemy.orm import Session
from app.database import get_db
from app.models import Task
from app.dependencies.auth import get_current_user


async def require_owner_or_admin(
    task_id: int,
    current_user: dict = Depends(get_current_user),
    db: Session = Depends(get_db),
):
    task = db.query(Task).get(task_id)
    if not task:
        raise HTTPException(status_code=404, detail="Task not found")

    is_owner = task.owner_id == current_user["id"]
    is_admin = current_user.get("role") == "admin"

    if not is_owner and not is_admin:
        raise HTTPException(
            status_code=403,
            detail="Only the task owner or an admin can perform this action",
        )
    return task

Without the investigation in step 1, you could have:

• Not discovered that require_admin already existed (duplicating code)
• Not seen that PATCH has a partial check (inconsistency)
• Not known that Task has owner_id (looking for the wrong field)
• Not seen the project's dependency pattern (implementing it in an incompatible way)

Common Mistakes When Investigating with Subagents

Mistake 1: Queries that are too vague

❌ "Explain the project to me"
   → A generic 2-page answer that doesn't help you

✅ "Explain to me how a POST /api/tasks request goes through 
    authentication, validation, and gets persisted in the DB.
    Only the files and functions involved."
   → A precise, actionable answer

Mistake 2: Not giving context about the problem

❌ "Where is the get_user function used?"
   → A list of 15 uses with no prioritization

✅ "I'm going to change the return type of get_user() from a dict to 
    a Pydantic UserResponse model. Which files call 
    get_user() and would need to be updated to handle the 
    new return type?"
   → A prioritized list of impacts with context

Mistake 3: Asking for investigation when you should act

❌ Investigating for 10 minutes an off-by-one error that you can see 
   on the stack trace line
   → Opening the file and fixing it takes 30 seconds

✅ Investigating when you genuinely don't understand the system
   → The cost of investigating pays off in better decisions

Mistake 4: Not verifying the investigation

❌ Claude Code says: "The auth middleware applies to all endpoints"
   You: "Ok, I trust it" → It turns out /api/health is excluded

✅ Claude Code says: "The auth middleware applies to all endpoints"
   You: Open middleware.py and confirm → You discover 3 excluded paths

Exploration subagents are investigation tools, not oracles. Verify critical findings.

Mistake 5: Over-investigating (analysis paralysis)

❌ You spend 30 minutes investigating every aspect of the codebase
   before making a 3-line change
   → The investigation should be proportional to the change

✅ 5-minute investigation for a change that touches 3 files
   15-minute investigation for an architectural change
   No investigation for a typo fix

Advanced Example: Multi-Query Investigation

Sometimes a single query isn't enough. Complex problems require iterative investigation:

Scenario: Slow performance in the search endpoint

The GET /api/tasks/search?q=... endpoint takes 3 seconds 
with 10,000 tasks. You need to optimize it.

Query 1: Understand the current endpoint

Show me the complete implementation of the 
GET /api/tasks/search endpoint. How does it do the search? 
Does it use direct SQL, ORM queries, or full-text search?

Claude Code's answer:

# routers/tasks.py:34
@router.get("/tasks/search")
async def search_tasks(
    q: str,
    db: Session = Depends(get_db),
    current_user: dict = Depends(get_current_user),
):
    all_tasks = db.query(Task).all()
    results = []
    for task in all_tasks:
        if q.lower() in task.title.lower() or q.lower() in (task.description or "").lower():
            results.append(task)
    return results

Finding: It loads ALL tasks into memory and filters in Python. That explains the 3 seconds.

Query 2: Check what indexes exist

What indexes does the tasks table have in the database? 
Check the SQLAlchemy model and the Alembic migrations.
Is there any index on title or description?

Answer:

# models/task.py
class Task(Base):
    __tablename__ = "tasks"
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(200), nullable=False)  # no index
    description = Column(Text, nullable=True)      # no index
    owner_id = Column(Integer, ForeignKey("users.id"), index=True)
    status = Column(String(20), default="pending")
    created_at = Column(DateTime, default=datetime.utcnow)

Finding: There are no indexes on title or description.

Query 3: Evaluate options

The project uses PostgreSQL and SQLAlchemy. Is there any place
in the codebase where search is done with ILIKE, full-text 
search, or some more efficient search mechanism that I 
could use as a reference?

Answer:

There's no other endpoint that uses efficient search. 
All the filters in the project use SQLAlchemy's .filter()
with exact comparisons (==), not text search.

Result of the multi-query investigation:

Complete diagnosis:
1. The endpoint loads ALL tasks into memory (O(n) in Python)
2. There are no indexes on the search fields
3. There's no precedent for efficient search in the project

Optimization plan:
1. Replace the Python search with ILIKE in SQL
2. Add an index on title (and possibly GIN for full-text)
3. Add pagination (LIMIT/OFFSET)

Without the multi-query investigation, you could have tried to "optimize" the Python loop without realizing that the real problem is that there shouldn't be a loop at all — it should be a SQL query.

Connection to the Project

In the capstone project (Module 8)

Before touching a single line of the project's codebase, your first task will be to investigate:

1. Project structure — How is it organized? What patterns does it use?
2. Main flows — How do the main endpoints work?
3. Dependencies — What depends on what?
4. Problem areas — Where are the code smells, hallucinations, security holes?

Documenting your investigation is part of the deliverable. "I found 15 problems" isn't enough — you need "I investigated the codebase, understood the architecture, and based on that I identified 15 prioritized problems."

How investigation saves time in the project

Without prior investigation:
├── You fix a bug → you break a dependency you didn't know about
├── You rename a function → 3 files that use it break
├── You change a schema → the endpoint that uses it keeps using the old one
└── Total: 3 hours with a lot of back-and-forth

With prior investigation (15-20 minutes):
├── You have a dependency map
├── You know which files each change touches
├── You prioritize the changes by impact
└── Total: 1.5 hours with clean changes

Troubleshooting

Problem 1: "Claude Code's investigation answers are too generic"

Cause: The query is too broad or doesn't have enough context. Solution: Apply the 5 rules of effective queries. In particular, be specific about which flow or component you care about, give context on why you're investigating (what problem you're solving), and ask for an actionable format (files with lines, not generic prose).

Problem 2: "I don't know what to ask — I don't know the codebase"

Cause: This is normal when you face a new codebase. You don't know what you don't know. Solution: Start with structural queries: "How is this project organized? What's in each directory? What's the entry point?" That gives you the base map. Then dig into the specific flows you need to understand.

Problem 3: "Claude Code gives me incorrect information about the codebase"

Cause: It can happen, especially with large or complex codebases. Solution: Always verify critical findings. If Claude Code says "the X function isn't used in any other file," open the terminal and run grep -r "function_X" to confirm. Investigation with subagents is a starting point, not absolute truth.

Problem 4: "The investigation takes longer than just reading the code myself"

Cause: For small codebases or individual files, manual investigation is faster. Solution: Use the decision table: subagents for cross-file flows, unfamiliar codebases, and dependency mapping. Manual investigation for individual files, targeted bugs, and codebases you already know.

Problem 5: "I investigated a lot but I still don't know what to do"

Cause: Analysis paralysis, or the investigation isn't focused on the right problem. Solution: Reframe your problem. If after 3 queries you don't have clarity, it's likely you're investigating the wrong aspect. Ask something different or try reproducing the problem manually to get concrete data that guides the investigation.

Exercises

Exercise 1: Formulate investigation queries (Medium)

For each scenario, write an investigation query that would follow the 5 rules of effective queries:

Scenario A: You need to add pagination to the GET /api/tasks endpoint but you don't know how the project currently handles responses.

Scenario B: A bug: POST /api/tasks creates duplicate tasks intermittently. You don't know why.

Scenario C: You're asked to migrate from SQLite to PostgreSQL. You need to know how many files are affected.

Scenario D: The GET /api/tasks/{id} endpoint returns relationship data (tags, categories) but you don't know how the relationships are configured in SQLAlchemy.

I need to add pagination to the GET /api/tasks endpoint.
Before implementing:

1. How does it currently return the list of tasks? 
   Does it return all of them or is there some limit?
2. Is there any other endpoint in the project that already 
   implements pagination that I could use as a reference?
3. Does the response use a Pydantic schema or does it return 
   the SQLAlchemy models directly?
4. Are there existing tests for this endpoint that I would 
   need to update?

I only need to understand the response pattern, 
not the authentication or validation flow.

Bug: POST /api/tasks creates duplicate tasks 
intermittently (not always, just sometimes). Before 
diagnosing:

1. What's the complete creation flow? From the 
   request to the commit in the DB.
2. Is there any middleware, hook, or event listener that 
   runs during task creation?
3. Does the endpoint have retry logic or any mechanism 
   that could cause double execution?
4. How is the DB session handled? Could there be 
   a double commit?

The fact that it's intermittent suggests a timing 
issue — I'm looking for points where execution could 
duplicate.

I'm going to migrate the database from SQLite to PostgreSQL.
I need to assess the impact:

1. Where is the DB connection configured? 
   How many files reference it?
2. Are there queries with SQLite-specific syntax 
   that isn't compatible with PostgreSQL?
3. Are the Alembic migrations database-agnostic 
   or do they have SQLite-specific operations?
4. Is there any use of SQLite-exclusive features 
   (like autoincrement behavior)?
5. Do the tests use a separate DB or the same 
   production configuration?

List all the files I would have to modify 
with the type of change needed in each one.

The GET /api/tasks/{id} endpoint returns relationship 
data (tags, categories) and I need to understand 
how they're configured:

1. How is the Task model defined? What 
   relationships does it have?
2. Do the relationships use lazy loading, eager loading, 
   or selectin loading?
3. Does the response schema (Pydantic) include the 
   nested models or just IDs?
4. Are there association tables (many-to-many) or 
   are they direct relationships (one-to-many)?

I only need to understand the Task model's relationships,
not the other models.

Exercise 2: Investigation vs direct action (Easy)

For each situation, decide whether you should investigate with subagents first or act directly. Justify your decision.

1. You have a TypeError: 'NoneType' has no attribute 'id' on line 45 of task_service.py
2. You're asked to add an email notification system to the project
3. A test fails with AssertionError: expected 200, got 422
4. You need to understand why the project has two configuration files: config.py and settings.py
5. There's an f-string in a SQL query in user_repository.py:23

Exercise 3: Multi-query investigation (Hard)

You have this situation: a GET /api/reports/summary endpoint takes 8 seconds to respond. You know nothing about the codebase. Write a sequence of 3 investigation queries, where each query builds on what you'd expect to learn from the previous one.

Show me the complete implementation of the 
GET /api/reports/summary endpoint. What data does it compute? What 
tables does it query? How many queries does it run?

[Based on what I learned] The endpoint calls 
ReportService.generate_summary() which runs 5 separate 
queries. For each query:
1. Are there indexes on the columns it filters?
2. Does any query load relationships with lazy loading 
   that could cause N+1?
3. Could any of the 5 queries be combined 
   into a single one?

[Based on what I learned] Queries 2 and 3 cause 
N+1 loading and there are no indexes on created_at. 
Is there any place in the project where the following is used:
1. joinedload or selectinload to avoid N+1?
2. Caching (Redis, in-memory) for data that doesn't 
   change frequently?
3. Composite or partial indexes?

I want to know if there are precedents I could follow 
to maintain consistency.

Exercise 4: Evaluate the quality of an investigation (Medium)

Claude Code returns this investigation to you. Identify what's good, what's missing, and what you'd verify manually:

Investigation: Task creation flow

1. POST /api/tasks → routers/tasks.py:create_task()
2. Validates with the TaskCreate schema (schemas/task.py)
3. Calls task_service.create(task_data, user)
4. TaskService creates the model and does db.add() + db.commit()
5. Returns TaskResponse with the created task

The create_task function has no special middleware.
There's no duplicate validation.
The user is extracted from the JWT token.

Exercise 5: Plan a change based on investigation (Hard)

After investigating, you obtained this information:

Codebase: Inventory management API

Relevant files:
- models/product.py     → Product model (id, name, price, stock, category_id)
- models/category.py    → Category model (id, name, description)
- schemas/product.py    → ProductCreate, ProductUpdate, ProductResponse
- routers/products.py   → CRUD endpoints for products
- services/product_service.py → Business logic
- dependencies/auth.py  → get_current_user, require_admin

Findings:
1. ProductResponse doesn't include the category name (only category_id)
2. The GET /products endpoint doesn't allow filtering by category
3. There's no validation that category_id exists when creating a product
4. The DELETE endpoint doesn't check permissions (any user can delete)

Write a prioritized change plan, indicating for each change: which files it touches, the execution order, and whether it's a security, functionality, or UX fix.

Priority 1 (SECURITY):
─────────────────────────
4. Add permission checking to DELETE
   Files: routers/products.py, dependencies/auth.py
   Change: Add Depends(require_admin) to the DELETE endpoint
   Type: Security fix
   Reason: Any user can delete products — 
          critical vulnerability

Priority 2 (DATA INTEGRITY):
──────────────────────────────────
3. Validate that category_id exists when creating a product
   Files: services/product_service.py
   Change: Verify the category exists before creating
   Type: Data integrity fix
   Reason: Can create products with nonexistent categories

Priority 3 (FUNCTIONALITY):
────────────────────────────
2. Add a filter by category to GET /products
   Files: routers/products.py, services/product_service.py
   Change: Add an optional category_id query parameter
   Type: Feature
   Reason: Expected functionality that's missing

Priority 4 (UX):
─────────────────
1. Include the category name in ProductResponse
   Files: schemas/product.py, routers/products.py
   Change: Add category_name to ProductResponse, 
           do a join in the query
   Type: UX improvement
   Reason: The frontend needs to show the name, 
          not just the ID

Execution order: 4 → 3 → 2 → 1
(security first, UX last)

Summary

• Investigate before acting is the pattern that separates effective developers from those who struggle with AI tools
• Claude Code's exploration capabilities let you navigate files, follow imports, map dependencies, and understand flows — all before changing a line
• There are 4 types of investigation: understand flows, find uses, map impact, and investigate bugs
• The 5 rules of effective queries: be specific, give context, ask for an actionable format, scope it down, ask for what's missing
• Use subagents for unfamiliar codebases, cross-file flows, and dependency mapping
• Use manual investigation for individual files, targeted bugs, and familiar codebases
• The complete workflow is: investigate → understand → plan → change
• Verify critical findings — subagents are investigation tools, not oracles
• Investigation with subagents is the prerequisite for making good regenerate vs edit decisions (capsules 03-05)

Additional Resources

1. Anthropic — Claude Code Best Practices - Official Claude Code documentation including exploration capabilities
2. Architecture Decision Records - How to document architectural decisions based on investigation
3. Code Reading: The Open Source Perspective - Techniques for reading and understanding code
4. Working Effectively with Legacy Code - Michael Feathers — investigating existing codebases before making changes
5. The Pragmatic Programmer — Tracer Bullets - The technique of investigating and prototyping before building

Next capsule: When to Regenerate Code — the clear signals that regenerating is better than editing.

Debugging & Code Review with Claude Code — Module 7, Capsule 02 Claude Code Agentic Development Path — Guide #6 of 11