Lesson 173: Mock Audit Deficiency Recurrence Trend Board and Sprint Hardening Budget (2026)

Why this matters now

Lesson 172 gave you a single 90-minute mock-audit tabletop with a seven-dimension scoring rubric and a deficiency-ticket conversion. Run it twice per cert window across Q3 2026 (six overlapping cert windows under the Gamescom-adjacent / Steam autumn deck verification refresh / Meta holiday window stack) and the team produces twelve scored tabletops by the end of Q3. Each tabletop produces between zero and twelve deficiency rows. The team now holds an evidence catalogue with somewhere between forty and one hundred and twenty deficiency rows tagged by failureModeTag, cert_window_id, rubric_dimension, and severity.

That catalogue is gold if it is consumed. It is noise if it is not.

Through Q3 and into Q4 2026 the partner readbacks shifted. Where 2024 partner audit packets asked "did you pass the most recent mock?" and 2025 packets asked "show me the most recent three mock-audit logs," 2026 Q4 partner readbacks ask for cross-window deficiency class trends before signing the year-end risk letter. The new question is: "Which dimension keeps going red? Which failureModeTag recurs across cert windows? What did you do about it last sprint, and what is the hardening plan for the next sprint?"

A team that runs the rehearsals but cannot answer that question is treated by the partner reviewer as a team that does not learn from its own drills. A team that can answer it with a recurrence histogram and a deterministic sprint hardening budget allocator is treated as a team with mature governance — and partner reviewers protect mature governance teams from over-rotation when one cert window goes red.

This lesson is the trend consumer for Lesson 172. The output is a board, a histogram, an allocator, and a Friday operating review block. The board surfaces which Lessons 162-171 controls keep producing deficiencies. The histogram quantifies. The allocator turns the histogram into a sprint-hardening budget in a mechanical way that the team and partner reviewers can both audit. The Friday block makes it a routine, not a panic move.

Lesson objectives

By the end of this lesson you will have:

• A deficiency export schema consolidating mock_audit_log plus mock_audit_deficiency_ticket rows across cert windows into a single normalised deficiency_event view.
• A recurrence histogram binned by rubric_dimension and failureModeTag over a rolling twelve cert-window lookback so a single bad window does not dominate the picture.
• A trend board that classifies dimensions as stable / improving / hot / structural-red based on deterministic thresholds.
• A sprint hardening budget allocator that converts the trend board into a fixed-percentage allocation of the next sprint's hardening hours across Lessons 162-171.
• A Friday operating review block (Block 6 - Recurrence and Hardening) that surfaces the board in the team's weekly cadence.
• A partner readback table that exports board state plus allocator output in a format Q4 partner reviewers consume directly.

Prerequisites from earlier lessons

This lesson assumes:

• Lesson 172 is shipped and at least two cert windows have been rehearsed (so the catalogue has at least four mock-audit logs and a non-trivial deficiency row count).
• Lesson 171 publish gate exists so the trend board can pin a block_reason_recurrence_red carve-out path (introduced below).
• Lesson 170 FAQ discipline exists so the readback export below has a place to live.
• Lesson 167 synthetic replay diff gate exists so the trend board can correlate epsilon_policy_version drift against deficiency clusters.
• Lesson 165 footer schema semver exists so the deficiency export rows can be footer_schema_semver-stamped.
• Lesson 164 leadership-partner dictionary exists so dimension labels match across surfaces.
• Lesson 160 retros exist so the trend board feeds an established consumer (not a brand-new one).

If any of these are missing, build the prerequisite first. The trend board is only as good as the data feeding it; thin or unstamped data produces a board that looks busy but cannot defend a partner question.

The deficiency export schema

The starting fact: across twelve mock-audit tabletops over Q3 2026, the team holds twelve mock_audit_log rows and somewhere between forty and one hundred and twenty mock_audit_deficiency_ticket rows. These tables exist per Lesson 172 schema. The trend board needs a single normalised view over both that pins the columns retros and partner reviewers will read from.

Create a view deficiency_event joining the two tables:

CREATE VIEW deficiency_event AS
SELECT
  d.deficiency_id,
  d.cert_window_id,
  l.rehearsal_kind,            -- 'T-14' or 'T-3'
  l.rehearsal_dt_utc,
  d.rubric_dimension,          -- 1..7 per Lesson 172 rubric
  d.failure_mode_tag,          -- allow-listed per Lesson 172 (no free text)
  d.severity,                  -- 'cert_blocking' | 'major' | 'minor'
  d.sla_tier,                  -- 'T-14' (168h) | 'T-3' (48h)
  d.status,                    -- 'open' | 'resolved' | 'withdrawn' | 'carved_out'
  d.resolved_dt_utc,
  d.carve_out_id,
  d.footer_schema_semver,      -- pinned from the rubric run
  d.epsilon_policy_version     -- pinned from the rubric run
FROM mock_audit_deficiency_ticket d
JOIN mock_audit_log l USING (mock_audit_id);

Every row in deficiency_event is a single observation of a single deficiency, time-stamped to its rehearsal. The view is the input to every histogram, board, and readback below.

The recurrence histogram

The board uses a rolling twelve-window lookback. Twelve is the smallest window that smooths a single bad cert week without diluting structural patterns; experience across small teams running this drill puts the noise floor at roughly two windows and the signal floor at roughly six, so twelve gives clean room on both sides.

Compute the histogram daily as a materialised view deficiency_recurrence_12w:

CREATE MATERIALIZED VIEW deficiency_recurrence_12w AS
WITH recent_windows AS (
  SELECT DISTINCT cert_window_id
  FROM deficiency_event
  WHERE rehearsal_dt_utc >= NOW() - INTERVAL '180 days'
  ORDER BY cert_window_id DESC
  LIMIT 12
)
SELECT
  e.rubric_dimension,
  e.failure_mode_tag,
  COUNT(*)                                          AS event_count,
  COUNT(DISTINCT e.cert_window_id)                  AS window_count,
  COUNT(*) FILTER (WHERE e.severity = 'cert_blocking') AS cert_blocking_count,
  COUNT(*) FILTER (WHERE e.status = 'carved_out')    AS carve_out_count,
  AVG(
    CASE
      WHEN e.resolved_dt_utc IS NULL THEN NULL
      ELSE EXTRACT(EPOCH FROM (e.resolved_dt_utc - e.rehearsal_dt_utc)) / 3600
    END
  )                                                  AS avg_resolution_hours,
  MAX(e.rehearsal_dt_utc)                            AS most_recent_dt_utc
FROM deficiency_event e
JOIN recent_windows w USING (cert_window_id)
GROUP BY e.rubric_dimension, e.failure_mode_tag;

The histogram now has one row per (rubric_dimension, failure_mode_tag) pair, with five quantitative columns and one timestamp column. This is the board's data source.

Refresh nightly:

REFRESH MATERIALIZED VIEW CONCURRENTLY deficiency_recurrence_12w;

The CONCURRENTLY clause matters: the morning operating review reads the board, and you do not want the board locked while refreshing.

The trend board classification

Each histogram row gets a deterministic classification. No subjective judgement, no edit boxes — the classification is mechanical so the team and the partner reviewer cannot argue about it:

structural-red means: this dimension + failure-mode pair shows up in at least half of recent cert windows, with at least two cert-blocking severity events. This is the partner reviewer's "what are you doing about this?" row. It cannot stay structural-red for two consecutive quarters without explicit carve-out documentation.

hot is the same pattern with one cert-blocking event — the early warning. recurring-minor is the slow drip — never blocking, but always present. improving is the success story — the row was hot or structural-red and has gone quiet for at least sixty days. stable is everything else.

Surface the board as a view:

CREATE VIEW deficiency_trend_board AS
SELECT
  rubric_dimension,
  failure_mode_tag,
  event_count,
  window_count,
  cert_blocking_count,
  carve_out_count,
  avg_resolution_hours,
  most_recent_dt_utc,
  CASE
    WHEN window_count >= 6 AND cert_blocking_count >= 2 THEN 'structural-red'
    WHEN window_count >= 4 AND cert_blocking_count >= 1 THEN 'hot'
    WHEN window_count >= 4 AND cert_blocking_count = 0 THEN 'recurring-minor'
    WHEN window_count >= 4 AND most_recent_dt_utc < NOW() - INTERVAL '60 days' THEN 'improving'
    ELSE 'stable'
  END AS classification
FROM deficiency_recurrence_12w
ORDER BY
  CASE
    WHEN window_count >= 6 AND cert_blocking_count >= 2 THEN 1
    WHEN window_count >= 4 AND cert_blocking_count >= 1 THEN 2
    WHEN window_count >= 4 AND cert_blocking_count = 0 THEN 3
    WHEN window_count >= 4 AND most_recent_dt_utc < NOW() - INTERVAL '60 days' THEN 4
    ELSE 5
  END,
  cert_blocking_count DESC,
  event_count DESC;

The board sorts highest-priority classification to the top, then by cert-blocking event count, then by total event count. The morning operating review scans top-to-bottom and stops at the first stable row.

The sprint hardening budget allocator

The board surfaces patterns; the allocator turns the patterns into a mechanical sprint plan. Without the allocator, the team argues at every retro about which dimension to harden next. With the allocator, the conversation is "the allocator says 35% Lesson 167, 25% Lesson 170, 15% Lesson 165, 25% reserved; here is what we ship this sprint."

The allocator runs over the trend board at the start of every two-week sprint. Each board row contributes a weight to its mapped lesson:

Each rubric_dimension maps to a Lesson per the Lesson 172 rubric:

Sum the weights by mapped lesson. Convert to percentages of the sprint's hardening hours (typically 30% of the sprint capacity is hardening-budget; the remaining 70% is feature work). Cap any single lesson at 40% of the hardening budget to prevent over-rotation and reserve 20% unallocated for incident-driven hardening discovered mid-sprint.

A typical Q3 2026 board after twelve cert windows might allocate:

Note the normalisation: if pre-cap exceeds 80%, the cap and reserved share are applied last so the team always has unallocated incident-driven hardening capacity.

Pin the allocator output in a sprint_hardening_budget table the team retro reads from:

CREATE TABLE sprint_hardening_budget (
  sprint_id TEXT PRIMARY KEY,                -- e.g. '2026-Q4-S01'
  generated_dt_utc TIMESTAMPTZ NOT NULL,
  lesson_167_pct NUMERIC(5,2) NOT NULL,
  lesson_170_pct NUMERIC(5,2) NOT NULL,
  lesson_165_pct NUMERIC(5,2) NOT NULL,
  lesson_171_pct NUMERIC(5,2) NOT NULL,
  lessons_164_166_pct NUMERIC(5,2) NOT NULL,
  lesson_163_pct NUMERIC(5,2) NOT NULL,
  reserved_pct NUMERIC(5,2) NOT NULL,
  trend_board_snapshot_hash TEXT NOT NULL,   -- SHA-256 of the deficiency_trend_board rows
  approved_by TEXT NOT NULL,                 -- team lead role, signer-acked
  CHECK (
    lesson_167_pct + lesson_170_pct + lesson_165_pct + lesson_171_pct
    + lessons_164_166_pct + lesson_163_pct + reserved_pct = 100.00
  )
);

The check constraint at the database level prevents drift; the row is only insertable when the percentages sum to exactly 100. This is the same discipline as Lesson 172's rubric weights-sum-to-100 enforcement, applied to sprint budget instead of audit scoring.

The Friday operating review block (Block 6 - Recurrence and Hardening)

The team's Friday operating review already has five blocks per earlier lessons (Build and QA, Cert and Compliance, Wishlist and Acquisition, Money and Mix, People and Capacity). Add Block 6 - Recurrence and Hardening:

• Inputs (read-only): deficiency_trend_board top ten rows, current sprint's sprint_hardening_budget row, the count of new structural-red rows added in the past week.
• Discussion (no edits): Is there a new structural-red row that the current sprint allocator does not weight enough? Is there a hot row trending toward structural-red within two more cert windows?
• Output (single yes/no metric): Did this week's deficiency closures match the sprint hardening budget's stated allocation? y if the closed-deficiency mix is within 25 percentage points of the allocation; n otherwise.

The yes/no metric is the discipline gate. If the team allocates 35% to Lesson 167 hardening but actually spent the week on Lesson 170 work, the answer is n and next week the allocator gets re-run with explicit reasoning for the divergence pinned in a sprint_hardening_budget_variance_note column. Tracking the divergence is what turns the allocator from a wall poster into a steering wheel.

The partner readback export

Q4 2026 partner reviewers want the board, the histogram, and the allocator in one defensible format. Export the readback as a structured Markdown block that drops into the existing Lesson 170 FAQ-bound readback packet:

## Q[N] 2026 Mock Audit Recurrence Trend Board

**Generated:** 2026-MM-DD
**Lookback:** 12 cert windows (rolling 180 days)
**Trend board snapshot hash:** [SHA-256 hex]

### Classification summary

- structural-red rows: [N]
- hot rows: [N]
- recurring-minor rows: [N]
- improving rows: [N]
- stable rows: [N]

### Top structural-red rows (if any)

| Dimension | Failure mode tag | Windows | Cert-blocking | Avg resolution (hours) | Last seen |
|---|---|---|---|---|---|
| [Dim] | [Tag] | [N] | [N] | [N.N] | [Date] |

### Hardening allocation for sprint [SPRINT_ID]

| Mapped lesson | Allocation % |
|---|---|
| Lesson 167 - Diff gate | [N]% |
| Lesson 170 - FAQ discipline | [N]% |
| Lesson 165 - Footer schema | [N]% |
| Lesson 171 - Publish gate | [N]% |
| Lessons 164+166 - Alignment | [N]% |
| Lesson 163 - Carve-out trails | [N]% |
| Reserved (incident-driven) | [N]% |

Allocator output approved by: [ROLE], [DATE].
Variance note (if last sprint diverged > 25%): [text].

The hash on the readback packet binds the partner export to a specific snapshot of deficiency_trend_board. If a partner reviewer asks "show me the data behind this," the team queries the materialised view at that hash and reproduces the exact rows. This is the same forensic discipline as Lesson 171 tuple-drift hashes, applied to recurrence trend data.

Common mistakes to avoid

• Refreshing the materialised view manually before each review — let the nightly refresh own it. A view refreshed ten minutes before the meeting is a view nobody trusts.
• Allowing free-text in failure_mode_tag — the column is allow-listed per Lesson 172 and that constraint matters more on aggregation than it did on individual scoring. One free-text tag destroys two histogram bins.
• Letting structural-red rows accumulate without explicit carve-out documentation — partner reviewers read structural-red rows that have stayed red for two consecutive quarters as "team does not learn." Either harden, carve out (with Lesson 163 documentation), or accept the partner narrative cost.
• Skipping the cap and reserved-share normalisation — without it, a bad quarter rotates 80% of the next sprint's hardening hours into one lesson and the rest of the surface decays.
• Computing the histogram only at Q-boundaries — the rolling 12-window lookback is per-rehearsal, not per-quarter. A new structural-red row appearing in mid-October should surface in the October Friday operating review, not in December's quarterly retro.
• Treating the trend board as performance review fodder for individuals — the board scores the packet stack, not the people. Conflating destroys honest reporting and within two quarters the team stops surfacing real deficiencies in mock audits.
• Ignoring the improving rows — a row that moved from hot to improving still gets 0.5 weight in the allocator specifically so light maintenance prevents regression. Setting improving to 0.0 reliably produces a quarter-later relapse.

Verification checklist

• [ ] deficiency_event view exists and returns at least one row per (rehearsal, deficiency) pair from your existing mock_audit_log plus mock_audit_deficiency_ticket tables.
• [ ] deficiency_recurrence_12w materialised view exists, refreshes nightly, and uses CONCURRENTLY.
• [ ] deficiency_trend_board view classifies rows into the five categories with the deterministic SQL conditions above.
• [ ] sprint_hardening_budget table exists with the row-level CHECK constraint that percentages sum to 100.
• [ ] The board's top 10 rows are pinned in the Friday operating review agenda template under Block 6.
• [ ] At least one full sprint has run with the allocator output approved and the variance note column populated where divergence > 25%.
• [ ] The partner readback Markdown template has been included in the next Q4 2026 partner audit packet draft and reviewed by the FAQ owner per Lesson 170.

If any item is missing, fix it before the first Q4 cert window so the team is not assembling the trend board on the same day the partner reviewer asks for cross-window trends.

What you have just earned

After this lesson the team has a deterministic answer to the new 2026 Q4 partner question: which dimension keeps going red, what is the hardening plan, and how does that plan get its budget. The answer is mechanical (board classification rules), audited (snapshot hash on the export), and routine (Friday Block 6).

You also have an early-warning system for Lessons 162-171 controls. A hot row trending toward structural-red over two more cert windows is the team's signal to invest in hardening before the partner reviewer asks, not after. The allocator turns the signal into sprint planning that survives sprint-planning drift, because the percentages are pinned in a database row with a check constraint.

Most importantly, the rehearsal investment from Lesson 172 now compounds. Every tabletop is no longer a one-shot scoring event; it is a row in a histogram that steers next sprint's hardening budget. Twelve rehearsals per quarter produce a forty-row-plus trend board within two quarters. That board is the artefact partner reviewers ask to see — and the team that built it routinely is treated as the team that knows what it is doing.

Next lesson teaser

The next lesson (Lesson 174: Signer Route Fatigue Heat-Map, Backup Owner Promotion, and Escalation (2026)) covers signer-route fatigue heat-mapping and backup-owner promotion — modelling owner-route response times across overlapping cert windows, heat-mapping fatigue, and promoting backup owners with explicit ack routing before missed SLAs become Lesson 171 tuple_drift events or Lesson 170 unsigned FAQ mutations. This is the operational counterpart to today's trend board: the board surfaces what keeps going red; Lesson 174 surfaces who is one cert window away from missing an SLA.

After Lesson 174, the queue extends through Lessons 175-181 (WORM archive plus cold-storage retrieval drill, partner reply packet versioning, dictionary minor-increment migration guardrails, carved-back deficiency quorum, multi-region reviewer feedback ingestion, AI-assisted governance red-team prompts with human gate, and Q1 2027 cert-intake rehearsal calendar export from the Lesson 172 rubric JSON).

Continuity

• Paired Unity guide chapter (next Guide-Create pass will author): Unity 6.6 LTS OpenXR governance mock audit deficiency recurrence trend board and sprint hardening allocator preflight - editor-side Governance/Trend Board ScriptableObject group, BI-side bind contract on the deficiency_trend_board materialised view, and the allocator output pinned in Governance/Sprint Hardening Budget with the percentages-sum-to-100 invariant enforced at the inspector level.
• Help article: OpenXR Governance Partner SLA Snapshot vs Leadership Dashboard Rollup Mismatch (Quest) Fix - the dimension 1 failure mode this board's top rows surface in cert-blocking severity.
• Lesson 172 - Q3 2026 Submission Intake Mock Audit Tabletop Scoring Rubric (2026) - the mock_audit_log and mock_audit_deficiency_ticket tables this lesson aggregates from.
• Lesson 171 - Tuple Drift Automatic Block on Dashboard Publish Pipeline (2026) - the publish gate the trend board's structural-red rows can extend with a recurrence_red_unhardened carve-out path.
• Lesson 170 - Executive Readback Redlines Versus Partner Annex FAQ Discipline (2026) - the FAQ-bound readback packet this lesson's partner export drops into.
• Lesson 167 - Synthetic Replay Diff Gate (2026) - the dimension 5 hardening target the allocator most frequently routes budget to in early Q4 windows.
• Lesson 166 - Weekly SLA Snapshot Reconciliation Job (2026) - the dimension 4 hardening target paired with Lesson 164 in the allocator's alignment lane.
• Lesson 165 - Governance Packet Footer Metadata Schema Semver (2026) - the dimension 3 stamp pinned on every deficiency_event row for reproducible histograms.
• Lesson 164 - Leadership Partner SLA Dashboard Sync (2026) - the dictionary the rubric dimension labels resolve through.
• Lesson 163 - Governance Freeze Bypass Audit Trails (2026) - the carve-out lane the structural-red rows enter when explicit acceptance is the right call.
• Lesson 162 - Governance SLA Breach Forecasting Cert-Window Freeze Gates (2026) - the upstream gate this trend board's outputs ultimately feed.
• Lesson 160 - Governance Post-Incident Retros (2026) - retros consume deficiency_trend_board as input (the formal pipeline the Lesson 172 teaser anticipated).

A trend board is the team's promise to itself that mock-audit rehearsals are not a ceremony. Run Block 6 every Friday in Q4 2026 and the rehearsal investment from Q3 compounds into hardened governance instead of evaporating into the next sprint's feature work.