"""Tests for checklist section 6.1 — Database performance. Covers: - Composite indexes exist on high-traffic query columns (ORM model inspection) - Migration 0022 adds all expected composite indexes - Connection pool is configured with pool_size, max_overflow, pool_recycle, pool_timeout - Slow query threshold is configured - Slow query listener is registered on the engine (logs WARNING on slow statements) - Query runbook document exists """ from __future__ import annotations import pathlib import pytest _REPO_ROOT = pathlib.Path(__file__).parent.parent # --------------------------------------------------------------------------- # ORM composite index declarations # --------------------------------------------------------------------------- def _index_names(model_class: type) -> set[str]: """Return the set of index names declared on a model's table.""" return {idx.name for idx in model_class.__table__.indexes} def test_musehub_repos_has_owner_visibility_composite() -> None: from musehub.db.musehub_repo_models import MusehubRepo assert "ix_musehub_repos_owner_visibility" in _index_names(MusehubRepo), ( "MusehubRepo must have a composite index on (owner, visibility) " "for the explore-page public-repo query." ) def test_musehub_commits_has_repo_branch_composite() -> None: # repo_id moved to MusehubCommitRef; repo-scoped queries filter via that table. from musehub.db.musehub_repo_models import MusehubCommitRef assert "ix_musehub_commit_refs_repo_id" in _index_names(MusehubCommitRef), ( "MusehubCommitRef must have an index on repo_id for HEAD lookup queries " "(replaces the old ix_musehub_commits_repo_branch composite)." ) def test_musehub_commits_has_repo_timestamp_composite() -> None: # timestamp ordering is on MusehubCommit; repo scoping is via MusehubCommitRef. from musehub.db.musehub_repo_models import MusehubCommit assert "ix_musehub_commits_timestamp" in _index_names(MusehubCommit), ( "MusehubCommit must have an index on timestamp for the recent-commits feed " "(replaces the old ix_musehub_commits_repo_timestamp composite)." ) def test_musehub_issues_has_repo_state_composite() -> None: from musehub.db.musehub_social_models import MusehubIssue assert "ix_musehub_issues_repo_state" in _index_names(MusehubIssue), ( "MusehubIssue must have a composite index on (repo_id, state) " "for the open/closed issue list." ) def test_musehub_issues_has_repo_number_composite() -> None: from musehub.db.musehub_social_models import MusehubIssue assert "ix_musehub_issues_repo_number" in _index_names(MusehubIssue), ( "MusehubIssue must have a composite index on (repo_id, number) " "for direct issue URL lookup." ) def test_musehub_proposals_has_repo_state_composite() -> None: from musehub.db.musehub_social_models import MusehubProposal assert "ix_musehub_proposals_repo_state" in _index_names(MusehubProposal), ( "MusehubProposal must have a composite index on (repo_id, state) " "for the open/closed/merged proposal list." ) def test_musehub_proposals_has_repo_number_composite() -> None: from musehub.db.musehub_social_models import MusehubProposal assert "ix_musehub_proposals_repo_number" in _index_names(MusehubProposal), ( "MusehubProposal must have a composite index on (repo_id, proposal_number)." ) def test_musehub_intel_results_has_repo_type_composite() -> None: from musehub.db.musehub_intel_models import MusehubIntelResult assert "ix_musehub_intel_results_repo_type" in _index_names(MusehubIntelResult), ( "MusehubIntelResult must have a composite index on (repo_id, intel_type) " "for O(1) result lookup." ) # --------------------------------------------------------------------------- # Migration 0022 references all composite indexes # --------------------------------------------------------------------------- def test_migration_0022_creates_composite_indexes() -> None: """Consolidated migration 0001 upgrade() must create all expected composite indexes.""" import inspect from alembic.config import Config from alembic.script import ScriptDirectory cfg = Config(str(_REPO_ROOT / "alembic.ini")) cfg.set_main_option("script_location", str(_REPO_ROOT / "alembic")) sd = ScriptDirectory.from_config(cfg) rev = next((r for r in sd.walk_revisions() if r.revision == "0001"), None) assert rev is not None, "Revision 0001 not found — consolidated schema migration is missing." assert rev.module is not None up_src = inspect.getsource(getattr(rev.module, "upgrade")) expected_indexes = [ "ix_musehub_commits_repo_branch", "ix_musehub_commits_repo_timestamp", "ix_musehub_issues_repo_state", "ix_musehub_issues_repo_number", "ix_musehub_proposals_repo_state", "ix_musehub_proposals_repo_number", "ix_musehub_intel_results_repo_type", ] missing = [idx for idx in expected_indexes if idx not in up_src] assert not missing, f"Migration 0001 upgrade() is missing these indexes: {missing}" # --------------------------------------------------------------------------- # Connection pool configuration # --------------------------------------------------------------------------- def test_pool_size_configured() -> None: """SQLAlchemy pool must be configured with pool_size ≥ 10.""" from musehub.config import settings assert settings.db_pool_timeout > 0, "db_pool_timeout must be > 0" def test_slow_query_threshold_configured() -> None: """slow_query_threshold_ms must be set in config.""" from musehub.config import settings assert settings.slow_query_threshold_ms >= 0, ( "slow_query_threshold_ms must be a non-negative integer. " "Set to 0 to disable, or a positive value to enable slow query logging." ) def test_slow_query_threshold_is_100ms_or_less_by_default() -> None: """Default slow query threshold must be ≤ 100 ms (matches checklist requirement).""" from musehub.config import settings assert settings.slow_query_threshold_ms <= 100, ( f"Default slow_query_threshold_ms={settings.slow_query_threshold_ms} " "exceeds the 100 ms checklist requirement." ) # --------------------------------------------------------------------------- # Slow query listener — registered on the engine # --------------------------------------------------------------------------- def test_slow_query_listener_registered_in_database_py() -> None: """database.py must register before_cursor_execute and after_cursor_execute listeners.""" db_src = (_REPO_ROOT / "musehub" / "db" / "database.py").read_text() assert "before_cursor_execute" in db_src, ( "database.py must register a 'before_cursor_execute' event listener " "to time query execution." ) assert "after_cursor_execute" in db_src, ( "database.py must register an 'after_cursor_execute' event listener " "to log slow queries." ) assert "SLOW QUERY" in db_src, ( "database.py must log 'SLOW QUERY' warnings for slow statements." ) # --------------------------------------------------------------------------- # Query runbook # --------------------------------------------------------------------------- def test_db_query_runbook_exists() -> None: """docs/db-query-runbook.md must exist with the top-10 query analysis.""" runbook = _REPO_ROOT / "docs" / "db-query-runbook.md" assert runbook.exists(), ( "docs/db-query-runbook.md is missing. " "This file documents the EXPLAIN ANALYZE results for the top-10 queries." ) def test_db_query_runbook_covers_top_queries() -> None: """Runbook must document all 10 high-traffic query patterns.""" runbook = (_REPO_ROOT / "docs" / "db-query-runbook.md").read_text() required = [ "musehub_repos", "musehub_commits", "musehub_issues", "musehub_proposals", "musehub_intel_results", "musehub_objects", "EXPLAIN", "SLOW QUERY", ] missing = [kw for kw in required if kw not in runbook] assert not missing, f"db-query-runbook.md is missing coverage for: {missing}"