# MuseHub — Database Query Runbook

Run these `EXPLAIN ANALYZE` checks before each production deployment and after
any schema or index change.  No sequential scans on tables expected to hold
more than ~10 k rows.

---

## How to run

```bash
# Connect to the production DB:
psql $DATABASE_URL

# For each query below, prepend EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT):
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...
```

Look for:
- `Seq Scan` on large tables — add an index if present
- `Rows Removed by Filter` >> `Rows` — index selectivity is poor
- `actual time=` values > 100 ms — investigate or add a covering index

---

## Top-10 highest-traffic queries

### Q1 — Repo lookup by URL slug (every page load)
```sql
SELECT * FROM musehub_repos
WHERE owner = 'gabriel' AND slug = 'neo-soul'
  AND deleted_at IS NULL;
```
**Expected plan:** `Index Scan using uq_musehub_repos_owner_slug`

---

### Q2 — List repos for a user (dashboard)
```sql
SELECT * FROM musehub_repos
WHERE owner = 'gabriel' AND visibility = 'public'
  AND deleted_at IS NULL
ORDER BY pushed_at DESC
LIMIT 20;
```
**Expected plan:** `Index Scan using ix_musehub_repos_owner_visibility`

---

### Q3 — HEAD commit for a branch (every push/fetch)
```sql
SELECT * FROM musehub_commits
WHERE repo_id = '<repo_uuid>' AND branch = 'main'
ORDER BY timestamp DESC
LIMIT 1;
```
**Expected plan:** `Index Scan using ix_musehub_commits_repo_branch`

---

### Q4 — Open issue list (issue tracker)
```sql
SELECT * FROM musehub_issues
WHERE repo_id = '<repo_uuid>' AND state = 'open'
ORDER BY created_at DESC
LIMIT 25;
```
**Expected plan:** `Index Scan using ix_musehub_issues_repo_state`

---

### Q5 — Issue by number (direct URL)
```sql
SELECT * FROM musehub_issues
WHERE repo_id = '<repo_uuid>' AND number = 42;
```
**Expected plan:** `Index Scan using ix_musehub_issues_repo_number`

---

### Q6 — Open proposals (Proposal list)
```sql
SELECT * FROM musehub_proposals
WHERE repo_id = '<repo_uuid>' AND state = 'open'
ORDER BY created_at DESC
LIMIT 25;
```
**Expected plan:** `Index Scan using ix_musehub_proposals_repo_state`

---

### Q7 — Intel result lookup (intel endpoints)
```sql
SELECT * FROM musehub_intel_results
WHERE repo_id = '<repo_uuid>' AND intel_type = 'code.symbol_history';
```
**Expected plan:** `Index Scan using ix_musehub_intel_results_repo_type`

SLOW QUERY threshold: 100 ms. Alert if `actual time` exceeds threshold.

---

### Q8 — Per-repo storage quota sum (every push)
```sql
SELECT COALESCE(SUM(o.size_bytes), 0)
FROM musehub_objects o
JOIN musehub_object_refs r ON r.object_id = o.object_id
WHERE r.repo_id = '<repo_uuid>' AND o.deleted_at IS NULL;
```
**Expected plan:** `Index Scan using pk_musehub_object_refs` (repo_id prefix) +
`Index Scan using musehub_objects pkey`

---

### Q9 — Recent commit feed (repo home page)
```sql
SELECT * FROM musehub_commits
WHERE repo_id = '<repo_uuid>'
ORDER BY timestamp DESC
LIMIT 10;
```
**Expected plan:** `Index Scan using ix_musehub_commits_repo_timestamp`

---

### Q10 — Per-user storage sum (MCP push quota)
```sql
SELECT COALESCE(SUM(o.size_bytes), 0)
FROM musehub_objects o
JOIN musehub_object_refs ref ON ref.object_id = o.object_id
JOIN musehub_repos      r   ON r.repo_id = ref.repo_id
WHERE r.owner_user_id = '<user_uuid>'
  AND r.deleted_at IS NULL;
```
**Expected plan:** `Index Scan using ix_musehub_repos_owner_user_id` +
`Index Scan using pk_musehub_object_refs` + `Index Scan using musehub_objects pkey`

---

## Slow query log

Queries exceeding `SLOW_QUERY_THRESHOLD_MS` (default 100 ms) are logged at
`WARNING` level by the SQLAlchemy `after_cursor_execute` listener in
`musehub/db/database.py`.

To monitor in production:

```bash
# Tail the app log for SLOW QUERY entries:
journalctl -u musehub -f | grep "SLOW QUERY"

# Or set log_min_duration_statement in Postgres for server-side capture:
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();
```

Alerts should be wired to any line matching `SLOW QUERY` in the application
log, or to `pg_stat_statements` entries with `mean_exec_time > 100`.