Lesson 193: Q1 2027 Rehearsal Completion Score Rollup Export from Attendance and mock_audit_log (2026)

Direct answer: Lesson 181 booked ICS holds. Lesson 182 proved attendance. Lesson 193 rolls up scored mock_audit_log rows into rehearsal_completion_rollup, exports weighted rehearsal_completion_v1.json for Q1 2027 planning decks, and blocks publish on rehearsal_completion_incomplete until every required T-14 and T-3 rehearsal for the window shows completed attendance + scored log + pass bit.

Why this matters now (October 2026 planning)

October 2026 leadership planning for Q1 2027 intake shifted from “we have calendar invites” to “show the scored rehearsal log”:

• Partner risk letters (Lesson 189) ask for completion rate, not invite count.
• Lesson 191 quorum crosswalk is meaningless if T-3 never scored.
• Lesson 192 hotfix re-exports do not replace missing tabletops—rollup exposes gaps honestly.
• Course-Planner post–Lesson 187 arc closes here: operations → attendance → scoring → exportable proof.

Lesson objectives

You will implement:

• Materialized view rehearsal_completion_rollup
• Weighted completion score per cert_window_id (T-14 vs T-3 weights)
• Attendance-gated inclusion (only completed + scored rows)
• Export job export_rehearsal_completion_v1
• Publish gate rehearsal_completion_incomplete
• REHEARSAL_COMPLETION_ROLLUP_RECEIPT.json under release-evidence/05-operations/

Prerequisites

• Lesson 181 — cert_rehearsal_event, ICS UIDs, phases t_minus_14 / t_minus_3
• Lesson 182 — cert_rehearsal_attendance, bootstrap mock_audit_log
• Lesson 172 — weighted score rules (weighted_score_min 90, per-dimension floor 50)
• Lesson 191 — optional quorum manifest before executive export
• Lesson 192 — hotfix semver rows; rollup reads active rubric per log row

rehearsal_completion_rollup (materialized view)

CREATE MATERIALIZED VIEW rehearsal_completion_rollup AS
SELECT
  c.cert_window_id,
  c.rehearsal_phase,
  e.ics_uid,
  a.attendance_id,
  a.attendance_status,
  m.mock_audit_log_id,
  m.rubric_version,
  m.status AS log_status,
  m.pass_bit,
  m.weighted_score,
  CASE
    WHEN a.attendance_status = 'completed'
     AND m.status = 'scored'
     AND m.pass_bit IS NOT NULL
    THEN true
    ELSE false
  END AS counts_toward_completion
FROM cert_window_calendar c
JOIN cert_rehearsal_event e ON e.cert_window_id = c.cert_window_id
LEFT JOIN cert_rehearsal_attendance a ON a.ics_uid = e.ics_uid
LEFT JOIN mock_audit_log m ON m.attendance_id = a.attendance_id
WHERE c.planning_year = 2027;

Refresh after each tabletop scores:

REFRESH MATERIALIZED VIEW CONCURRENTLY rehearsal_completion_rollup;

Attendance-gated rule: rows with attendance_status != 'completed' or log_status != 'scored' contribute zero to numerator; they still appear as gaps in the export (fail-closed transparency).

Weighted completion score

Per cert_window_id:

def completion_score(rows: list[dict]) -> float:
    num = 0.0
    den = 0.0
    for r in rows:
        w = 0.55 if r["rehearsal_phase"] == "t_minus_14" else 0.45
        den += w
        if r["counts_toward_completion"]:
            num += w
    return round(100.0 * num / den, 2) if den else 0.0

Pass threshold for planning deck: completion_score >= 100.0 and both phases pass_bit = true when scored.

rehearsal_completion_v1.json export

{
  "schema": "rehearsal_completion_v1",
  "cert_window_id": "q1_2027_meta_holiday",
  "exported_at_utc": "2026-10-15T09:00:00Z",
  "completion_score_percent": 100.0,
  "phases": [
    {
      "rehearsal_phase": "t_minus_14",
      "ics_uid": "[email protected]",
      "attendance_status": "completed",
      "log_status": "scored",
      "pass_bit": true,
      "weighted_score": 92,
      "rubric_version": "1.2.0"
    },
    {
      "rehearsal_phase": "t_minus_3",
      "ics_uid": "[email protected]",
      "attendance_status": "completed",
      "log_status": "scored",
      "pass_bit": true,
      "weighted_score": 94,
      "rubric_version": "1.2.0-hotfix.1"
    }
  ],
  "gaps": [],
  "pass": true
}

If gaps is non-empty, set "pass": false and list missing ics_uid + reason (no_show, draft_log, unscored).

Publish gate: rehearsal_completion_incomplete

-- block_reason = 'rehearsal_completion_incomplete'
WHEN EXISTS (
  SELECT 1
  FROM cert_window_calendar c
  WHERE c.cert_window_id = :active_window
    AND c.planning_year = 2027
    AND NOT EXISTS (
      SELECT 1 FROM rehearsal_completion_rollup r
      WHERE r.cert_window_id = c.cert_window_id
        AND r.counts_toward_completion = true
      GROUP BY r.cert_window_id
      HAVING COUNT(*) FILTER (WHERE r.rehearsal_phase = 't_minus_14') >= 1
         AND COUNT(*) FILTER (WHERE r.rehearsal_phase = 't_minus_3') >= 1
    )
);

Clears when export shows "pass": true and receipt SHA pinned in release-evidence/.

Pairs with panel_quorum_gap_open (Lesson 191) and rubric_hotfix_pending (Lesson 192)—resolve upstream gates before claiming completion.

Six-step export procedure

Common mistakes

• Counting scheduled attendance as complete—bootstrap never ran.
• Exporting before pass_bit computed—draft logs inflate false greens.
• Ignoring hotfix rubric version mismatch between phases—valid if each log pins its version.
• Rolling up 2026 windows into Q1 2027 deck—filter planning_year.
• Refreshing view without CONCURRENTLY during publish hour—blocks writers.

Troubleshooting

Verification checklist

• [ ] Both phases appear in rollup with counts_toward_completion = true
• [ ] rehearsal_completion_v1.json shows "pass": true
• [ ] gaps array empty for active window
• [ ] Receipt SHA in release-evidence/05-operations/
• [ ] rehearsal_completion_incomplete gate clears on publish pipeline

Mini exercise (25 minutes)

1. Seed two completed attendances (T-14 + T-3) with scored logs.
2. Refresh materialized view.
3. Run export; confirm completion_score_percent = 100.
4. Delete T-3 pass_bit; re-export—confirm "pass": false and gap entry.
5. Restore pass; pin receipt JSON.

Continuity (arc finale)

• Lesson 181 — calendar source.
• Lesson 182 — attendance + log bootstrap.
• Lesson 172 — scoring rules inside rollup.
• Lesson 191 — quorum before executive readout.
• Lesson 192 — hotfix versions appear per-phase in export.
• Resource: Q3 2026 submission intake mock-audit packet templates — tooling context.
• Next: Lesson 194 — Q1 2027 intake evidence folder assembler (rehearsal + year-end ZIP).

FAQ

Can one phase pass and we still ship the deck?
No—both T-14 and T-3 must be scored with pass_bit = true for pass: true.

Do cancelled rehearsals count?
They appear in gaps with attendance_status: cancelled; they do not count toward completion.

What if partner only cares about T-3?
Keep weights in JSON for transparency; do not hide T-14 gap—partners cross-check calendars.

When to refresh the materialized view?
After every mock_audit_log flips to scored and after hotfix re-exports (Lesson 192).

October 2026 planning earns trust when rehearsal_completion_v1.json shows scored, attended, passing tabletops—not invites sitting unread in inboxes. Close the post–Lesson 187 arc with receipts, then refill the queue for the next cert year.