# Sprint: Detect 1/2-content rows with wrong answers, demote to needs_revision, export

Branch off master: `fix/detect-and-demote-wrong-answer-half`. Single PR. Sequenced phases — do not skip.

## Background

Production has approved MCQ rows where the question text contains "1/2" / "½" / "\dfrac{1}{2}" / "\frac{1}{2}" patterns. Tonight's audit found 112 such rows across all qa_status values; 57 are approved. Pam believes some of these have wrong correct_answer values relative to the question's actual math — likely artifacts of incomplete authoring.

Goal:
1. LLM-evaluate each candidate row: does the marked correct_answer mathematically match the question as written?
2. For rows where the answer is wrong: demote to `qa_status = 'needs_revision'` in production.
3. Export final `needs_revision` MCQ and FIB-integer rows to two CSVs for Pam's manual review.

## Critical safety rules

1. **Production database, SSH to 152.42.223.228, db_host = Math-2025.**
2. **DB write occurs in Phase 3 only.** Phases 1, 2, 4 are read-only or LLM-only.
3. **Mandatory backup before any write.** Phase 3 begins with `mysqldump` of `questions` table, scoped or full, saved to a known location.
4. **No commits of production credentials or backup dumps** to the repo. Backups go outside the repo.
5. **Stop immediately on any unexpected condition** (hostname mismatch, count anomaly, query timeout). Alert Pam, do not "self-recover."

## Autonomy directive

Phases 1 and 2 are fully autonomous. **Pause between Phase 2 and Phase 3 only if `confidence = low` rows exceed 10**, otherwise proceed. Phase 3 runs autonomously after backup. Phase 4 (export) runs autonomously.

If unexpected conditions arise (production hostname mismatch, query failures, unexplained row count changes), stop and alert.

---

## Phase 1 — Pull candidate rows (read-only)

### Task 1.1 — Confirm production connection

```sql
SELECT @@hostname AS db_host, DATABASE() AS db_name, @@version AS mysql_version, NOW() AS query_time;
```

Verify `db_host = 'Math-2025'`. Stop if not.

### Task 1.2 — Pull candidate set

All rows where the question contains a "1/2"-pattern AND status is currently `approved` or `unreviewed` (skip already-`needs_revision` rows — they don't need demotion). Type 1 (MCQ) only for the demotion phase; Type 2 (FIB) is exported in Phase 4 but not LLM-evaluated for demotion in this sprint.

```sql
SELECT 
  id, skill_id, difficulty_id, type_id, qa_status,
  question, question_image,
  answer0, answer1, answer2, answer3,
  correct_answer
FROM questions
WHERE type_id = 1
  AND qa_status IN ('approved', 'unreviewed')
  AND (
    TRIM(question) = '1/2'
    OR TRIM(question) = '½'
    OR question REGEXP '\\\\\\\\d?frac\\{1\\}\\{2\\}'
    OR question LIKE '%1/2%'
    OR question LIKE '%½%'
  )
ORDER BY id;
```

Save to `storage/app/sprint-half-demote/candidates.csv`. Report count.

Expected ~108 rows based on tonight's audit (57 approved + 51 unreviewed minus the 4 already in needs_revision). Flag if materially different.

---

## Phase 2 — LLM math-check (read-only, API spend)

### Task 2.1 — Classification prompt

For each row in `candidates.csv`, call Claude API. Model: `claude-haiku-4-5-20251001`. Temperature: 0. Max tokens: 600.

Prompt template:

```
You are evaluating a Primary-school MCQ for mathematical correctness. The question text contains 
"1/2" or a fraction variant. Determine whether the marked correct_answer matches the math of 
the question as written.

QUESTION: {question}
ANSWER OPTIONS:
  0: {answer0}
  1: {answer1}
  2: {answer2}
  3: {answer3}
MARKED CORRECT: option {correct_answer} ({correct_answer_text})

Evaluate:
1. Is the "1/2" in the question text serving as legitimate fraction content, OR does it look like a 
   placeholder that was never filled in?
2. Does the math of the question-as-written produce the value of the marked-correct option?
3. If the answer is wrong, what option SHOULD have been marked correct? (Return -1 if no option matches.)

Return JSON only, no other text:
{
  "id": <row id>,
  "verdict": "content_answer_correct" | "content_answer_wrong" | "placeholder_suspected" | "ambiguous",
  "should_demote": true | false,
  "expected_correct_option": <0|1|2|3|-1>,
  "confidence": "high" | "medium" | "low",
  "reasoning": "<one sentence>"
}

Demotion rule (for should_demote):
- true: if verdict is "content_answer_wrong" OR "placeholder_suspected" with high confidence
- false: if verdict is "content_answer_correct"
- false: if verdict is "ambiguous" or any low-confidence judgment (let Pam decide)
```

### Task 2.2 — Stage verdicts

Output to `storage/app/sprint-half-demote/verdicts.csv` with columns:

```
id, verdict, should_demote, expected_correct_option, confidence, reasoning, 
question, answer0, answer1, answer2, answer3, correct_answer, qa_status
```

Rate-limit at 1 request/sec. On API error, log and continue. Expected total cost <$0.50.

### Task 2.3 — Verdict summary report

Write `storage/app/sprint-half-demote/phase-2-summary.md`:

```markdown
# Phase 2 Verdict Summary

## Counts
- Total candidates evaluated: N
- content_answer_correct (no action): N
- content_answer_wrong (demote): N
- placeholder_suspected (demote): N
- ambiguous (no action, manual review): N

## Confidence distribution
- high: N
- medium: N
- low: N (these will NOT be demoted automatically)

## should_demote = true count: N
This is the size of the demotion set.

## Cost
- Total API calls: N
- Total cost: $X.XX

## Sample rows
- 5 from content_answer_correct
- 5 from content_answer_wrong
- 5 from placeholder_suspected
- 5 from ambiguous (if any)
```

### Task 2.4 — Pause check

If `confidence = low` rows in the `should_demote = true` set > 10, **STOP and alert Pam**. Otherwise proceed to Phase 3.

(Rationale: a small number of low-confidence demotions is acceptable noise; a large number means the LLM is unsure about too many rows and Pam should review before demoting.)

---

## Phase 3 — Demote to needs_revision (DB WRITE)

### Task 3.1 — Backup

Take a scoped `mysqldump` of only the rows about to be modified, plus a 7-day buffer:

```bash
# Save outside the repo to avoid accidental commit
mkdir -p ~/db-backups
mysqldump \
  --host=<prod-host> \
  --user=<read-write-user> \
  --password \
  --no-create-info \
  --where="id IN (<comma-separated demotion ids>)" \
  api questions > ~/db-backups/2026-05-13-half-demote-backup.sql
```

Confirm the file exists, non-empty, and contains INSERT statements for the expected row count. Print backup file path to the console for Pam's records.

If the user-data restore is needed later:

```bash
mysql -h <prod-host> -u <user> -p api < ~/db-backups/2026-05-13-half-demote-backup.sql
```

(The dump uses `--no-create-info` so it won't drop the table on restore — it will INSERT the rows, which will fail on primary key collision if rows still exist. Manual UPDATE-from-backup is the actual restore path. Document this in the report.)

### Task 3.2 — Generate demotion SQL

Build `storage/app/sprint-half-demote/3-demotion.sql`:

```sql
-- Generated 2026-05-13 by sprint fix/detect-and-demote-wrong-answer-half
-- Demotes N rows from approved/unreviewed to needs_revision
-- Backup: ~/db-backups/2026-05-13-half-demote-backup.sql

START TRANSACTION;

UPDATE questions 
SET qa_status = 'needs_revision',
    updated_at = NOW()
WHERE id IN (
  -- One id per line, with original qa_status as inline comment
  <id>,  -- was approved
  <id>,  -- was unreviewed
  ...
);

-- Expected row count: N
-- Verify before COMMIT:
SELECT COUNT(*) AS demoted_count 
FROM questions 
WHERE id IN (<same id list>) 
  AND qa_status = 'needs_revision';

-- If demoted_count = N, COMMIT.
-- If not, ROLLBACK.

COMMIT;
```

### Task 3.3 — Run the SQL

Execute in a transaction. Verify the row count matches the expected demotion count. COMMIT if match; ROLLBACK if not.

Log to console:
- Backup file path
- Demotion SQL path
- Number of rows demoted
- Verification count (should match)
- COMMIT or ROLLBACK decision

If ROLLBACK fires: stop, alert Pam, do not proceed to Phase 4.

---

## Phase 4 — Export needs_revision rows to CSVs (read-only)

### Task 4.1 — Refresh needs_revision counts

```sql
SELECT type_id, COUNT(*) AS n
FROM questions
WHERE qa_status = 'needs_revision'
GROUP BY type_id
ORDER BY type_id;
```

Confirm the MCQ (type_id=1) count increased by the demotion count from Phase 3. The FIB (type_id=2) count should be unchanged (no demotions this sprint).

### Task 4.2 — Export MCQ CSV

```sql
SELECT 
  q.id, q.skill_id, s.skill_name, q.difficulty_id, d.difficulty AS difficulty_tier,
  q.is_diagnostic,
  q.question, q.question_image,
  q.answer0, q.answer1, q.answer2, q.answer3, q.correct_answer,
  CASE q.correct_answer
    WHEN 0 THEN q.answer0
    WHEN 1 THEN q.answer1
    WHEN 2 THEN q.answer2
    WHEN 3 THEN q.answer3
  END AS correct_answer_text,
  q.qa_status,
  DATE(q.created_at) AS created_date,
  DATE(q.updated_at) AS updated_date
FROM questions q
LEFT JOIN skills s ON q.skill_id = s.id
LEFT JOIN difficulties d ON q.difficulty_id = d.id
WHERE q.qa_status = 'needs_revision' AND q.type_id = 1
ORDER BY s.skill_name, q.difficulty_id, q.id;
```

Adjust `skills.skill_name` column name if schema differs. Save to `docs/audits/2026-05-13-needs-revision-mcq.csv`.

### Task 4.3 — Export FIB integer CSV

The FIB integer schema differs from MCQ. Confirm the actual answer column for FIB by inspecting one row first:

```sql
SELECT * FROM questions WHERE type_id = 2 LIMIT 1;
```

Look for the column holding the integer answer (likely `correct_answer` itself, or a separate column like `numeric_answer` / `expected_value`). Adapt the export query accordingly.

```sql
SELECT 
  q.id, q.skill_id, s.skill_name, q.difficulty_id, d.difficulty AS difficulty_tier,
  q.is_diagnostic,
  q.question, q.question_image,
  q.correct_answer,  -- or whichever column holds the integer answer
  q.qa_status,
  DATE(q.created_at) AS created_date,
  DATE(q.updated_at) AS updated_date
FROM questions q
LEFT JOIN skills s ON q.skill_id = s.id
LEFT JOIN difficulties d ON q.difficulty_id = d.id
WHERE q.qa_status = 'needs_revision' AND q.type_id = 2
ORDER BY s.skill_name, q.difficulty_id, q.id;
```

Save to `docs/audits/2026-05-13-needs-revision-fib-integer.csv`.

### Task 4.4 — Export summary

Write `docs/audits/2026-05-13-needs-revision-export.md`:

```markdown
# needs_revision Export Report — 13 May 2026

## Phase 3 demotion summary
- Candidate rows evaluated: N
- Demoted from approved → needs_revision: N
- Demoted from unreviewed → needs_revision: N
- Backup file: ~/db-backups/2026-05-13-half-demote-backup.sql

## Final needs_revision counts (post-demotion)
- MCQ (type_id=1): N rows
- FIB integer (type_id=2): N rows
- Other types: not exported

## CSV files
- docs/audits/2026-05-13-needs-revision-mcq.csv (N rows)
- docs/audits/2026-05-13-needs-revision-fib-integer.csv (N rows)

## Review workflow
Pam opens each CSV in a spreadsheet and reviews:
- For MCQ: scan question + correct_answer_text. Add a `pam_action` column with values like 
  ok / fix_text / fix_answer / delete / merge / approve. Save reviewed file separately.
- For FIB: same pattern, with question + correct_answer integer side by side.

When review is complete, next sprint applies the marked actions to production.

## Restore (if needed)
The Phase 3 backup contains the original qa_status values for the demoted rows. To restore:

\`\`\`bash
# View backup contents
cat ~/db-backups/2026-05-13-half-demote-backup.sql

# Manual restore (UPDATE-from-backup pattern, NOT INSERT-from-backup):
# Build UPDATE statements from the dump's INSERT VALUES, then run them.
\`\`\`

DO NOT run the raw mysqldump as a restore — it contains INSERTs that will fail on primary key 
collision. The dump is the authoritative record of pre-demotion state.
```

---

## PR description requirements

- Branch: `fix/detect-and-demote-wrong-answer-half`
- Title: `Detect 1/2-content wrong-answer rows, demote to needs_revision, export`
- Body must include:
  - Phase 1 candidate count
  - Phase 2 verdict distribution (counts per verdict, counts per confidence)
  - Phase 3 demotion count + backup file path
  - Phase 4 final CSV row counts + file paths
  - API spend total
  - Confirmation that production hostname was verified at Task 1
  - Any rows where LLM verdict seemed off (manual judgment, flag for Pam)

## Deliverables

1. `storage/app/sprint-half-demote/candidates.csv` (gitignored)
2. `storage/app/sprint-half-demote/verdicts.csv` (gitignored)
3. `storage/app/sprint-half-demote/phase-2-summary.md` (committed for traceability)
4. `storage/app/sprint-half-demote/3-demotion.sql` (committed for audit trail)
5. `docs/audits/2026-05-13-needs-revision-mcq.csv` (committed)
6. `docs/audits/2026-05-13-needs-revision-fib-integer.csv` (committed)
7. `docs/audits/2026-05-13-needs-revision-export.md` (committed)
8. `~/db-backups/2026-05-13-half-demote-backup.sql` (NOT committed; outside repo)

## Out of scope

- Reconstructing question text (separate future sprint after Pam's review of CSVs)
- Reconstructing or fixing FIB integer rows (export only, no LLM evaluation in this sprint)
- Promoting `needs_revision` back to `approved` (separate workflow, requires human approval)
- Other qa_status pools, other question types
- Any rendering/image audit of question_image URLs

## If anything goes wrong

- Production hostname mismatch at Task 1 → stop, alert
- Candidate count materially different from ~108 → stop, alert, do not proceed to LLM
- LLM API failures > 5% of calls → stop after Phase 2, alert with verdicts.csv as-is
- Backup file empty or short → stop, do not run Phase 3
- Phase 3 verification count mismatch → ROLLBACK, alert
- Phase 4 CSV files contain malformed rows → re-export with adjusted escaping, note in report