gabriel / musehub public
feat BREAKING proposals task/proposal-list-indexes #1 / 1
AI Agent gabriel · 127 days ago · May 8, 2026 · Diff

feat(proposals): Phase 7 — composite indexes for proposal list query patterns

Migration 0044 adds 4 CONCURRENTLY-built indexes:

1. ix_musehub_proposals_repo_state_created (repo_id, state, created_at DESC) Covers: WHERE repo_id = ? AND state = ? ORDER BY created_at DESC/ASC Pattern: newest / oldest sort — the 90% case

2. ix_musehub_proposals_repo_state_risk (repo_id, state, risk_score DESC NULLS LAST) Covers: WHERE repo_id = ? AND state = ? ORDER BY risk_score DESC/ASC Pattern: risk_desc / risk_asc sort

3. ix_musehub_proposal_reviews_proposal_state (proposal_id, state) Covers: WHERE proposal_id IN (?) AND state = 'approved' GROUP BY proposal_id Pattern: _prefetch_for_batch approval count query

4. ix_musehub_identities_handle_type (handle, identity_type) Covers: WHERE handle IN (?) AND identity_type = 'agent' Pattern: author_type filter JOIN

ORM model __table_args__ updated to match (MusehubProposal, MusehubProposalReview, MusehubIdentity).

EXPLAIN harness: 5 tests verify index presence in pg_catalog and confirm index-based access plans for each query pattern.

Completes issue #35 — Proposals List: State Transition Queue.

sha256:b26003ce4b5387465ee38372620c7c3dc422c15b6cd3a43ca021f5eb6f6aa48c sha
sha256:5ea0fd185ca71c3410d95ab9d2c242e8d3e59449f9f3490c0f8b376fe94859e6 parent
+33 ~3 symbols
sha256:aed4d48b5beaf4498702d66228340a253733523ebd3e915db696931c515e878a snapshot
+33
symbols added
~3
symbols modified
0
dead code introduced
Semantic Changes 36 symbols
~ alembic/versions/0044_proposal_list_query_indexes.py .py 13 symbols added
+ _IDX_IDENTITY_HANDLE_TYPE variable variable _IDX_IDENTITY_HANDLE_TYPE L20–20
+ _IDX_PROPOSAL_STATE_CREATED variable variable _IDX_PROPOSAL_STATE_CREATED L17–17
+ _IDX_PROPOSAL_STATE_RISK variable variable _IDX_PROPOSAL_STATE_RISK L18–18
+ _IDX_REVIEW_PROPOSAL_STATE variable variable _IDX_REVIEW_PROPOSAL_STATE L19–19
+ branch_labels variable variable branch_labels L13–13
+ depends_on variable variable depends_on L14–14
+ down_revision variable variable down_revision L12–12
+ downgrade function function downgrade L61–65
+ annotations import import annotations L6–6
+ op import import op L9–9
+ sa import import sa L8–8
+ revision variable variable revision L11–11
+ upgrade function function upgrade L23–58
~ tests/test_proposal_list_phase7.py .py 20 symbols added
+ TestProposalListIndexes class class TestProposalListIndexes L104–199
+ test_all_four_indexes_exist_in_pg_catalog method async_method test_all_four_indexes_exist_in_pg_catalog L180–199
+ test_ix_identity_handle_type_used_for_author_type_filter method async_method test_ix_identity_handle_type_used_for_author_type_filter L163–177
+ test_ix_repo_state_created_used_for_list_sort_newest method async_method test_ix_repo_state_created_used_for_list_sort_newest L108–123
+ test_ix_repo_state_risk_used_for_risk_sort method async_method test_ix_repo_state_risk_used_for_risk_sort L126–140
+ test_ix_review_proposal_state_used_for_approval_prefetch method async_method test_ix_review_proposal_state_used_for_approval_prefetch L143–160
+ _explain function async_function _explain L87–91
+ _has_index_scan function function _has_index_scan L94–96
+ _make_repo function async_function _make_repo L36–56
+ _now function function _now L32–33
+ _plan_indexes function function _plan_indexes L73–84
+ _plan_nodes function function _plan_nodes L59–70
+ AsyncSession import import AsyncSession L24–24
+ annotations import import annotations L16–16
+ datetime import import datetime L20–20
+ json import import json L18–18
+ pytest import import pytest L22–22
+ text import import text L23–23
+ timezone import import timezone L20–20
+ uuid import import uuid L19–19
~ musehub/db/musehub_models.py .py 3 symbols modified
~ MusehubIdentity
~ MusehubProposal
~ MusehubProposalReview
← Older Oldest on task/proposal-list-indexes
All commits
Newer → Latest on task/proposal-list-indexes

0 comments

No comments yet. Be the first to start the discussion.

To add a comment, use the Muse CLI: muse hub commit comment sha256:b26003ce4b5387465ee38372620c7c3dc422c15b6cd3a43ca021f5eb6f6aa48c --body "your comment"