Database Index Recommendations

All collections use project_id as the leading key to support multi-tenant data isolation.

MongoDB Collections

ProjectDocument

db.project.create_index([("project_id", 1)], unique=True)
db.project.create_index([("owner_user_id", 1), ("name", 1)])
db.project.create_index([("owner_username", 1), ("name", 1)], unique=True)

ProjectMembershipDocument

db.project_membership.create_index([("project_id", 1), ("user_id", 1)])
db.project_membership.create_index([("project_id", 1), ("username", 1)], unique=True)
db.project_membership.create_index([("user_id", 1), ("status", 1)])
db.project_membership.create_index([("username", 1), ("status", 1)])

ChipDocument

db.chip.create_index([("project_id", 1), ("chip_id", 1), ("username", 1)], unique=True)
db.chip.create_index([("project_id", 1), ("username", 1), ("installed_at", -1)])

QubitDocument

db.qubit.create_index([("project_id", 1), ("chip_id", 1), ("qid", 1), ("username", 1)], unique=True)
db.qubit.create_index([("project_id", 1), ("chip_id", 1)])

CouplingDocument

db.coupling.create_index([("project_id", 1), ("chip_id", 1), ("qid", 1), ("username", 1)], unique=True)
db.coupling.create_index([("project_id", 1), ("chip_id", 1)])

ExecutionHistoryDocument

db.execution_history.create_index([("project_id", 1), ("execution_id", 1)], unique=True)
db.execution_history.create_index([("project_id", 1), ("chip_id", 1), ("start_at", -1)])
db.execution_history.create_index([("project_id", 1), ("chip_id", 1)])
db.execution_history.create_index([("project_id", 1), ("username", 1), ("start_at", -1)])
db.execution_history.create_index([("project_id", 1), ("user_id", 1), ("start_at", -1)])

Usage: Used by _extract_best_metrics() for querying execution history within a project

Query Pattern:

ExecutionHistoryDocument.find({
    "project_id": project_id,
    "chip_id": chip_id,
    "start_at": {"$gte": cutoff_time}
}).sort([("start_at", -1)]).limit(1000)

TaskDocument

db.task.create_index([("project_id", 1), ("name", 1), ("username", 1)], unique=True)
db.task.create_index([("project_id", 1), ("username", 1)])

BackendDocument

db.backend.create_index([("project_id", 1), ("name", 1), ("username", 1)], unique=True)
db.backend.create_index([("project_id", 1), ("username", 1)])

TagDocument

db.tag.create_index([("project_id", 1), ("name", 1), ("username", 1)], unique=True)
db.tag.create_index([("project_id", 1), ("username", 1)])

FlowDocument

db.flows.create_index([("project_id", 1), ("username", 1), ("name", 1)])
db.flows.create_index([("project_id", 1), ("username", 1), ("created_at", -1)])
db.flows.create_index([("project_id", 1), ("chip_id", 1)])

ExecutionLockDocument

db.execution_lock.create_index([("project_id", 1)], unique=True)

ExecutionCounterDocument

db.execution_counter.create_index([("project_id", 1), ("date", 1), ("username", 1), ("chip_id", 1)], unique=True)

ChipHistoryDocument

db.chip_history.create_index([("project_id", 1), ("chip_id", 1), ("username", 1), ("recorded_date", 1)], unique=True)
db.chip_history.create_index([("project_id", 1), ("chip_id", 1), ("recorded_date", -1)])

QubitHistoryDocument

db.qubit_history.create_index([("project_id", 1), ("chip_id", 1), ("qid", 1), ("username", 1), ("recorded_date", 1)], unique=True)
db.qubit_history.create_index([("project_id", 1), ("chip_id", 1), ("recorded_date", -1)])

CouplingHistoryDocument

db.coupling_history.create_index([("project_id", 1), ("chip_id", 1), ("qid", 1), ("username", 1), ("recorded_date", 1)], unique=True)
db.coupling_history.create_index([("project_id", 1), ("chip_id", 1), ("recorded_date", -1)])

TaskResultHistoryDocument

Primary storage for task execution results. Linked to executions via execution_id.

db.task_result_history.create_index([("project_id", 1), ("task_id", 1)], unique=True)
db.task_result_history.create_index([("project_id", 1), ("execution_id", 1)])  # Join with execution_history
db.task_result_history.create_index([("project_id", 1), ("chip_id", 1), ("start_at", -1)])
db.task_result_history.create_index([
    ("project_id", 1), ("chip_id", 1), ("name", 1), ("qid", 1), ("start_at", -1)
])  # Latest task result queries

# Partial sparse indexes for the dashboard notes summary — only index rows
# that have a user-authored note or an AI review note.
db.task_result_history.create_index(
    [("project_id", 1), ("chip_id", 1), ("user_note.updated_at", -1)],
    name="project_chip_user_note_idx",
    partialFilterExpression={"user_note.updated_at": {"$type": "date"}},
)
db.task_result_history.create_index(
    [("project_id", 1), ("chip_id", 1), ("ai_review_note.updated_at", -1)],
    name="project_chip_ai_review_note_idx",
    partialFilterExpression={"ai_review_note.updated_at": {"$type": "date"}},
)

# Cool-down filter — list task results for a specific cool-down cycle.
# Sparse so empty (legacy) rows are not indexed.
db.task_result_history.create_index(
    [("project_id", 1), ("cooldown_id", 1), ("start_at", -1)],
    name="project_cooldown_start_idx",
    partialFilterExpression={"cooldown_id": {"$gt": ""}},
)

Usage: Used by ExecutionService._fetch_tasks_for_execution() for retrieving tasks by execution

Query Pattern:

TaskResultHistoryDocument.find({
    "project_id": project_id,
    "execution_id": execution_id,
}).sort([("start_at", ASCENDING)])

CryostatDocument

db.cryostat.create_index(
    [("project_id", 1), ("cryo_id", 1)], unique=True, name="project_cryo_unique"
)

CooldownDocument

db.cooldown.create_index(
    [("project_id", 1), ("cooldown_id", 1)],
    unique=True,
    name="project_cooldown_unique",
)
db.cooldown.create_index(
    [("project_id", 1), ("cryo_id", 1), ("started_at", -1)],
    name="project_cryo_started_idx",
)
db.cooldown.create_index(
    [("project_id", 1), ("chip_ids", 1), ("started_at", -1)],
    name="project_chip_idx",
)
db.cooldown.create_index(
    [("project_id", 1), ("started_at", -1)], name="project_started_idx"
)

NoteEventDocument

Append-only audit log for every note edit (qubit / coupling / metric / task). Powers the per-chip timeline, per-target timeline, and full-text knowledge search.

db.note_event.create_index(
    [("project_id", 1), ("chip_id", 1), ("created_at", -1)],
    name="chip_chrono_idx",
)
db.note_event.create_index(
    [("project_id", 1), ("scope", 1), ("target_id", 1), ("created_at", -1)],
    name="target_chrono_idx",
)
db.note_event.create_index(
    [("content", "text")],
    name="content_text_idx",
    default_language="english",
)

Usage: Written by NoteService on every upsert/delete. Read by:

• GET /chips/{chip_id}/note-events — chip timeline
• GET /note-events/by-target?scope=&target_id= — per-target timeline
• GET /note-events/search?q= — cross-chip text search

MetricNoteDocument

Current-state storage for dashboard metric notes. One row represents one target metric note in one operational scope: explicit cool-down, manual time range, or legacy global scope.

db.metric_note.create_index(
    [
        ("project_id", 1),
        ("chip_id", 1),
        ("target_type", 1),
        ("target_id", 1),
        ("metric_key", 1),
        ("scope_key", 1),
    ],
    unique=True,
    name="metric_note_unique_scope_idx",
)
db.metric_note.create_index(
    [
        ("project_id", 1),
        ("chip_id", 1),
        ("scope_key", 1),
        ("target_type", 1),
        ("target_id", 1),
    ],
    name="metric_note_summary_idx",
)
db.metric_note.create_index(
    [
        ("project_id", 1),
        ("chip_id", 1),
        ("scope_started_at", -1),
        ("scope_ended_at", -1),
    ],
    name="metric_note_time_scope_idx",
)

Usage: GET /chips/{chip_id}/notes-summary reads current notes for the requested cooldown_id or start_at/end_at. PUT/DELETE /chips/{chip_id}/.../metric-notes/{metric_key} writes the same scoped current-state row and appends an audit event to note_event.

Performance Impact

Without indexes:

• Collection scan: O(n) where n = total documents
• 1000+ documents: ~500ms+ query time

With compound index (project_id first):

• Index scan: O(log n + k) where k = matched documents
• 1000+ documents: ~10-50ms query time
• Multi-tenant isolation: Queries automatically scoped to project

Implementation

Add to your MongoDB initialization script or run manually:

// MongoDB shell
use qdash;

// Project indexes
db.project.createIndex({ project_id: 1 }, { unique: true, name: "project_id_unique" });
db.project.createIndex({ owner_user_id: 1, name: 1 }, { name: "owner_user_id_name_idx" });
db.project.createIndex({ owner_username: 1, name: 1 }, { unique: true, name: "owner_name_unique" });

// Membership indexes
db.project_membership.createIndex({ project_id: 1, user_id: 1 }, { name: "membership_project_user_id_idx" });
db.project_membership.createIndex({ project_id: 1, username: 1 }, { unique: true, name: "membership_unique" });
db.project_membership.createIndex({ user_id: 1, status: 1 }, { name: "user_id_status_idx" });
db.project_membership.createIndex({ username: 1, status: 1 }, { name: "user_status_idx" });

// Execution history indexes
db.execution_history.createIndex(
  { project_id: 1, chip_id: 1, start_at: -1 },
  { name: "metrics_best_query_idx" }
);

db.execution_history.createIndex(
  { project_id: 1, chip_id: 1 },
  { name: "metrics_chip_idx" }
);

db.execution_history.createIndex(
  { project_id: 1, user_id: 1, start_at: -1 },
  { name: "metrics_project_user_id_idx" }
);

db.execution_history.createIndex(
  { project_id: 1, username: 1, start_at: -1 },
  { name: "metrics_project_user_idx" }
);

Verification

Check if indexes exist:

db.execution_history.getIndexes();

Analyze query performance:

db.execution_history
  .find({
    project_id: "proj_123",
    chip_id: "test_chip",
    start_at: { $gte: ISODate("2025-01-01T00:00:00Z") },
  })
  .sort({ start_at: -1 })
  .limit(1000)
  .explain("executionStats");

Look for:

• executionStats.executionTimeMillis < 50ms
• winningPlan.inputStage.stage == "IXSCAN"
• executionStats.totalDocsExamined ≈ executionStats.nReturned

PostgreSQL - Prefect Metadata (Future)

To be documented when Prefect integration requires optimization

Monitoring

Query Performance Alerts

Set up monitoring for:

• Query execution time > 100ms
• Collection scans (COLLSCAN) on large collections
• Index usage ratio < 95%

Tools

• MongoDB Atlas: Built-in Performance Advisor
• Grafana: Custom dashboards with MongoDB exporter
• Application logs: Track slow queries via FastAPI middleware

Maintenance

Index Rebuilding

Schedule periodic index rebuilds during low-traffic periods:

db.execution_history.reIndex();

Frequency: Monthly or when index fragmentation > 30%

Index Statistics

Monitor index usage:

db.execution_history.aggregate([{ $indexStats: {} }]);

Remove unused indexes to reduce write overhead.