DORA Delivery Scoreboard with DuckDB

Compute five DORA metrics from a 30-day deploy history using SQL and an AI gate.

Introduction

30 Second Summary

Every software team eventually gets asked the same question: are we shipping faster than last year? Most answer with a gut feeling or a number they picked because it was easy to measure, and neither survives a follow-up question.

In this project, you will build a delivery scoreboard that computes five DORA-aligned metrics from a captured 30-day deployment history. You will define each metric in SQL inside DuckDB, gate one with an AI classification, and render the results as a self-contained page you can open offline.

What You'll Build

You will double-click a single HTML file and see five metric cards with values computed from deployment data, each carrying a plain-language sentence explaining what that number cannot prove.

By the end of this project, you'll have:

  • A working delivery scoreboard with five DORA-aligned metrics computed from a captured 30-day history, rendered as a self-contained inline-SVG page that opens by double-clicking it in any browser, offline.
  • A requirements brief, component view, build plan, and four MADR v4 decision records, each naming the alternative it rejected, the tradeoff it accepted, and the trigger that would reverse it.
  • An AI-gated classification that labels 20 deploys as incident-triggered or planned, runs a dual-test acceptance based on accuracy and count, and either computes rework rate or refuses with both scores visible.
  • Secret Mission: Audit a real repository's Git history without replacing the teaching corpus. Document which delivery fields it can prove and which production integrations are still needed.

Do I need any specific tools?

You need Python 3.9 or newer, pip, Git, a text editor, and a flat-rate AI client you already use (Claude, ChatGPT, Gemini, Copilot, or equivalent). No cloud account, no remote repository, and no prior DevOps experience required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. The next few steps move fast, so this is your moment to get clear on the goal.

Set Up the Project and Write Design Documents

A delivery metric is only as trustworthy as its definition. Without a requirements brief and decision records, the numbers your scoreboard produces are just outputs with no audit trail.

This step sets up the project folder, installs DuckDB, creates a pre-built skeleton for the collector, and writes the design documents that make every design choice defensible.

In this step, get ready to:
  • Initialise the project folder with Git and install DuckDB.
  • Create the starter skeleton and project structure.
  • Write design documents: a requirements brief, component view, build plan, and four MADR v4 decision records.
Initialise the project and install DuckDB

Everything starts with a folder on your Desktop, a Git repository, and one install.

You'll use an IDE to create and review the project files. Choose Visual Studio Code, Cursor, or Google Antigravity IDE.

✔️ I already have an IDE

Keep the IDE you already use. Launch it now so it is ready when your project folder exists.

  • Press Cmd+Space (macOS), the Windows key (Windows), or the Super key (Linux) to open system search.
  • Type the name of your IDE and press Enter to launch it.

ⓧ I need to install an IDE

Choose one of the three IDEs above. Use the tab for your operating system to install and launch it.

macOS

  • Open the official download page for Visual Studio Code, Cursor, or Google Antigravity IDE.
  • Download the macOS installer for your chosen IDE.
  • Open the downloaded installer.
  • Move the application to the Applications folder or complete the prompts shown by the installer.
  • Press Cmd+Space to open Spotlight.
  • Type the name of your IDE and press Enter to launch it.

Windows

  • Open the official download page for Visual Studio Code, Cursor, or Google Antigravity IDE.
  • Download the Windows installer for your chosen IDE.
  • Run the downloaded installer.
  • Complete the setup prompts. Approve the permission request if Windows shows one.
  • Press the Windows key to open system search.
  • Type the name of your IDE and press Enter to launch it.

Linux

  • Open the official download page for Visual Studio Code, Cursor, or Google Antigravity IDE.
  • Download the .deb package for Debian or Ubuntu, or the .rpm package for Fedora, RHEL, or SUSE.
  • Open the downloaded package in your distribution's software installer.
  • Click Install. Enter your system password if Linux requests permission.
  • Press the Super key to open system search.
  • Type the name of your IDE and press Enter to launch it.
  • Press Cmd+Space (macOS), the Windows key (Windows), or the Super key (Linux) to open system search.
  • Type Terminal (macOS or Linux) or PowerShell (Windows), then press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder, move into it, and initialise a Git repository by running these commands:
mkdir delivery-scoreboard
cd delivery-scoreboard
git init

You should see a message like Initialized empty Git repository printed in the terminal.

What does git init do?

git init creates a hidden .git folder that tracks every change you commit. Each commit is a snapshot you can inspect or roll back to.

  • Install DuckDB by running:
pip install duckdb

What is DuckDB?

DuckDB is an embedded analytics database that runs as a Python library. There is no server to start, no background process, and no configuration file. You import it and query in-memory tables with SQL.

  • Verify the install worked by running:
python -c "import duckdb; print(duckdb.__version__)"

You should see a version number printed, such as 1.5.5 or 1.4.5. The exact version depends on your Python version.

Seeing an error on the version check?

  • On macOS, python may not exist. Try python3 instead. If it works, use python3 for all commands in this project.
  • On Windows, if you see an error about the Microsoft Visual C++ Redistributable, download and install it from the Microsoft website, then run pip install duckdb again.
  • If pip is not found, try pip3 instead.

Help me install DuckDB for my delivery scoreboard project.

Create the starter skeleton and project structure

The scoreboard collector needs four components: a data loader, a metric runner, an AI gate, and an SVG renderer. A pre-built skeleton handles all file I/O and rendering. Your work in the next steps is writing the SQL definitions and building the data corpus.

  • Create the data and docs directories inside delivery-scoreboard by running these commands:
mkdir data
mkdir docs
  • Confirm both directories exist by running:
ls

The folder structure is ready. You should see data and docs in the output.

  • Return to the IDE you launched earlier.
  • Click File in the top menu.
  • Click Open Folder.
  • Select the delivery-scoreboard folder on your Desktop.
  • Click Open to load the project.
  • Create a new file from the IDE's Explorer sidebar.
  • Name the file TASKS.md and save it inside delivery-scoreboard.
  • Paste the following checklist into TASKS.md.
# Delivery Scoreboard - Tasks

- [ ] Set up project folder, Git, and DuckDB
- [ ] Create starter skeleton
- [ ] Write requirements brief, component view, and build plan
- [ ] Write four decision records
- [ ] Build the captured corpus
- [ ] Define five metrics in SQL
- [ ] Run AI labelling pass
- [ ] Run the AI gate
- [ ] Write cannot-prove annotations and generate readout
- [ ] Write after-action review and README
- [ ] Deliver teach-back
- [ ] Tag final commit

What does this checklist do?

Each box maps to a build step. You will tick them as you go. At the end of the project, every box ticked and a clean working tree is the definition of done.

  • Save TASKS.md.
  • Verify your project structure so far by running:
ls

You should see data, docs, and TASKS.md listed.

Now create the collector skeleton. Open your AI client (Claude, ChatGPT, Gemini, Copilot, or whichever flat-rate client you use). You will send it a detailed prompt that describes every function the skeleton needs.

  • Send your AI client the following prompt to generate the skeleton:
Create a Python file called scoreboard.py with these exact specifications:

Imports: json, duckdb, sys, pathlib.Path

Three data loaders, each taking (conn, json_path):
- load_deploys: CREATE TABLE "deploys" with deploy_id/author/commit_message/linked_issue_title (VARCHAR), committed_at/pr_opened_at/merged_at/deployed_at (TIMESTAMP), and is_incident_triggered (BOOLEAN). INSERT each record, preserving NULL labels.
- load_incidents: CREATE TABLE "incidents" with incident_id/causal_deploy_id (VARCHAR), impairment_start/restored_at (TIMESTAMP).
- load_sealed_raw: CREATE TABLE "sealed_raw" with deploy_id (VARCHAR), is_incident_triggered (BOOLEAN).

run_metrics(conn, sql_path): read SQL file, split on double-newline boundaries. Extract metric name from first "-- name" comment line (strip dashes and spaces). Collect non-comment lines as the query. Return dict {name: {"query": sql, "name": name}}.
execute_metric(conn, query): return conn.execute(query).fetchone().
run_three_clock_check(conn): compute MEDIAN hours for three clocks (committed_at, pr_opened_at, merged_at, each to deployed_at) using epoch(interval)/3600.0. Return 3-tuple.
run_gate(conn, sealed_labels_path): read deploy_id and is_incident_triggered predictions from the loaded deploys table. Return None and "deploy labels not populated" if any prediction is NULL. Compare populated predictions to sealed labels. Check accuracy >= 18 AND predicted_count == sealed_count. On pass, CREATE TABLE "deploy_labels" from the populated deploys table and return the result dict. On fail, return the failure dict.

CONSTANTS: CANNOT_PROVE dict mapping five metric names to "Cannot prove:" annotations. METRIC_LABELS mapping names to display labels. METRIC_ORDER list: deployment_frequency, change_lead_time, change_fail_rate, failed_deployment_recovery_time, rework_rate.
format_value(name, value): int for frequency, "{:.1f}h" for times, "{:.1f}%" for rates.
generate_svg(computed, gate_result): inline SVG, five 260x220 cards, dark theme (#0d0d1a), monospace, green/red accents, cannot-prove text on each card.
generate_html(svg, computed, gate_result, clocks): full HTML page, all CSS inline, no external deps. Include dated DORA anchor (2025 report, 23 Sep 2025), GAO analogy labelled as inference, three honest limits.
main(): use Path(__file__).parent for paths. duckdb.connect(":memory:"). Load data/deploys.json, data/incidents.json, and data/sealed_labels.json from the data subfolder. Run metrics from metrics.sql. Assert deployment_frequency matches data/reference.json. Print the three-clock comparison. Run the gate with data/sealed_labels.json. Write scoreboard.html.

Use .format() for all string formatting (no f-strings). Keep all file I/O inside functions, not at module level.
  • Review the AI's output. Check that these exact function names appear: load_deploys, load_incidents, load_sealed_raw, run_metrics, execute_metric, run_three_clock_check, run_gate, generate_svg, generate_html, and main.
  • Check that the table names in the SQL are exactly deploys, incidents, sealed_raw, and deploy_labels.
  • Save the output as scoreboard.py in the delivery-scoreboard folder.

What does this skeleton do?

The skeleton handles four responsibilities. The data loaders read JSON files into DuckDB tables. The metric runner parses a SQL file and executes each query. The AI gate compares predicted labels to sealed ground truth. The renderer builds an inline SVG scoreboard and wraps it in a self-contained HTML page.

You will not write any of this file-handling code yourself. Your job in the next steps is to create the data corpus, write the SQL definitions, and run the AI classification.

  • Confirm the skeleton loads without errors by running:
python -c "import scoreboard; print('Skeleton loaded')"

You should see Skeleton loaded printed with no errors.

Seeing an ImportError or SyntaxError?

  • Make sure the file is named exactly scoreboard.py (not scoreboard.py.txt) and is saved in the delivery-scoreboard folder.
  • Make sure you are running the command from inside the delivery-scoreboard folder. Run pwd to check.
  • If you see a SyntaxError, ask your AI client to fix the specific error message. Paste the full traceback into the chat.

Help me fix an import error with my scoreboard.py skeleton.

  • Save your progress by committing everything:
git add .
git commit -m "Initial setup: skeleton, tasks, directories"

Git prints a summary of the files committed. You should see scoreboard.py and TASKS.md in the list.

Write the design documents with your AI client

Before writing any SQL or data files, you need three planning documents and four decision records. Each one is a drafting job you send to your AI client, review for accuracy, and save.

  • Send your AI client the following prompt to draft the requirements brief:
Write a requirements brief for a delivery scoreboard project.
The scoreboard computes five DORA-aligned metrics from a captured
30-day deployment history:
1. Deployment frequency
2. Change lead time (committed to deployed)
3. Change fail rate
4. Failed deployment recovery time
5. Deployment rework rate (AI-gated)

It uses DuckDB for SQL analytics, an AI classification gate
for rework rate with dual-test acceptance (accuracy and count),
and hand-written inline SVG for rendering.

Produce numbered acceptance criteria.
  • Review the output. Check that all five DORA metric names appear and the dual-test gate (accuracy and count) is mentioned.
  • Save the result as docs/requirements.md.
  • Expand the docs folder in your IDE's Explorer sidebar.
  • Click the New File button.
  • Name the file component-view.md.
  • Paste the following content into docs/component-view.md.
# Component View

1. **Captured corpus** (JSON) - deployment, incident, and sealed label data files
2. **DuckDB loader** (Python) - reads JSON files into DuckDB in-memory tables
3. **Metric definitions** (SQL) - five named queries with exclusion comments
4. **SVG renderer** (Python) - generates inline SVG metric cards and wraps them in HTML

Why separate the components?

Each component has a single responsibility. If a number is wrong, you check the SQL query. If the page looks wrong, you check the renderer. If the data is bad, you check the corpus. This separation makes every metric auditable.

  • Save docs/component-view.md.
  • Check your progress by running:
ls docs/

You should see requirements.md and component-view.md listed.

  • Create docs/build-plan.md with the five build steps:
# Build Plan

| Step | Task | Budget |
|------|------|--------|
| 1 | Build the captured corpus | 5 min |
| 2 | Define five metrics in SQL | 5 min |
| 3 | Wire the loader and run assertions | 5 min |
| 4 | Run the AI gate for rework rate | 5 min |
| 5 | Annotate the readout and close the folder | 10 min |

Why budget each step?

Timeboxing forces scope discipline. If the corpus takes 15 minutes instead of 5, that is a signal the schema is too complex or the AI drafting prompt needs to be more specific.

  • Save docs/build-plan.md.

Next are the four decision records. Each follows the MADR v4 bare-minimal structure: Context and Problem Statement, Considered Options, and Decision Outcome with Consequences.

What is MADR?

MADR stands for Markdown Any Decision Records. It is a lightweight template for recording technical decisions: what was decided, what alternatives were considered, and what triggers would reverse the decision. Version 4.0.0 was released in September 2024.

  • Send your AI client this prompt to draft the first decision record:
Write an ADR in MADR 4.0.0 bare-minimal format with three
sections: Context and Problem Statement, Considered Options,
and Decision Outcome (with Consequences).

Decision: use a captured corpus of deployment data (JSON files)
instead of calling a live repository API.

The captured corpus means authentication, pagination, and
rate-limiting are never exercised.

Reversal trigger: the scoreboard is promoted to a recurring
job that must reflect live state.
  • Review the output. Confirm the three sections are present and the reversal trigger is included in the consequences.
  • Save as docs/adr-001-captured-corpus.md.
  • Draft ADR-002 by sending this prompt:
Write an ADR in MADR 4.0.0 bare-minimal format (Context and
Problem Statement, Considered Options, Decision Outcome with
Consequences).

Decision: define all metric calculations in a SQL file executed
by DuckDB, instead of writing the arithmetic in Python loops.
This costs one install (DuckDB) and buys a definition a reviewer
can read without tracing control flow.

Reversal trigger: a metric requires stateful iteration that SQL
cannot express.
  • Review and save as docs/adr-002-sql-file.md.
  • Check your progress by running:
ls docs/

You should see requirements.md, component-view.md, build-plan.md, adr-001-captured-corpus.md, and adr-002-sql-file.md listed.

  • Draft ADR-003 by sending this prompt:
Write an ADR in MADR 4.0.0 bare-minimal format (Context and
Problem Statement, Considered Options, Decision Outcome with
Consequences).

Decision: use hand-written inline SVG for the scoreboard
instead of a plotting library (matplotlib, plotly). This costs
manual layout and buys zero dependencies, one output file,
full control over annotation placement, and a page that
survives being emailed.

Reversal trigger: the scoreboard grows past ten cards.
  • Review and save as docs/adr-003-inline-svg.md.
  • Draft ADR-004 by sending this prompt:
Write an ADR in MADR 4.0.0 bare-minimal format (Context and
Problem Statement, Considered Options, Decision Outcome with
Consequences).

Decision: use a 30-day window with 20 deploys. This means one
month is not a trend, and 20 deploys is a sample, not a
population.

Reversal trigger: the team commits to quarterly review and
needs 90-day windows.
  • Review and save as docs/adr-004-window-and-corpus-size.md.
  • Verify all seven design files are present by running:
ls docs/

You should see seven files: requirements.md, component-view.md, build-plan.md, and adr-001 through adr-004.

Why write decision records before code?

A decision record names the alternative it beat and the trigger that would reverse it. Without that, "why did we use DuckDB instead of pandas?" becomes a question nobody can answer six months later. The record is not documentation for its own sake. It is insurance against silent context loss.

  • Commit all design artifacts by running:
git add .
git commit -m "Design: requirements, component view, build plan, four ADRs"
  • Select TASKS.md in your IDE's Explorer sidebar.
  • Change [ ] to [x] for each of the first four setup and design tasks.
  • Save TASKS.md.

Before you check your work, how many commits do you expect to see from git log --oneline?

  • Check your commit history by running:
git log --oneline

You should see at least two commits: one for the initial setup and one for the design documents.

Missing files or commits?

  • If a design file is missing from docs/, check that you saved it with the exact filename listed above. ADR filenames must match: adr-001-captured-corpus.md, adr-002-sql-file.md, adr-003-inline-svg.md, adr-004-window-and-corpus-size.md.
  • If git log shows only one commit, you may have forgotten to run the second git add . and git commit after creating the design files.

Help me check my delivery-scoreboard project structure and Git history.

✔️ Awesome, everything is in place!

Great. Double check you have saved all files and that TASKS.md has the first four boxes ticked.

ⓧ I'd like to double check my setup

Verify each of these is present in your delivery-scoreboard folder:

  • scoreboard.py with functions load_deploys, load_incidents, load_sealed_raw, run_metrics, execute_metric, run_three_clock_check, run_gate, generate_svg, generate_html, and main.
  • TASKS.md with 12 checklist items, first four ticked.
  • Empty data/ directory.
  • docs/ directory with seven files: requirements.md, component-view.md, build-plan.md, and four ADR files.

Your project folder is set up, the skeleton is ready, and every design choice is documented. Next up, you will build the captured deployment corpus that gives the scoreboard something to measure.

Build the Captured Corpus

Step 1 gave you a design: requirements, decision records, a component view, and a build plan. The scoreboard has structure, but no data to compute anything from.

The corpus ships four files because the five metrics draw from different evidence. Deploys and incidents provide timing data, while the sealed labels and reference values protect the gate and assertions.

In this step, get ready to:
  • Draft 20 deploy records with four timestamp clocks per record.
  • Create incident records, sealed labels, and reference values.
  • Commit the corpus and verify record counts.
Create the deploy records

The 20 deploy records form a captured 30-day teaching corpus. Their IDs and timestamps align with incidents.json, sealed_labels.json, reference.json, and the assertions you run later. Keep the original fields unchanged. The is_incident_triggered field starts as null because your AI client populates it in Step 4.

Three of the 20 deploys are hotfix rework (their commit messages start with hotfix: and their linked issue titles reference incidents). Four deploys cause incidents. These are different sets: a causal deploy breaks production, and a rework deploy is the fix that follows.

  • Create data/deploys.json with the following 20 records:
[
  {"deploy_id":"d-001","committed_at":"2026-07-27T08:00:00Z","pr_opened_at":"2026-07-27T14:00:00Z","merged_at":"2026-07-28T10:00:00Z","deployed_at":"2026-07-28T11:00:00Z","author":"alice","commit_message":"feat: add pagination to /api/products endpoint","linked_issue_title":"FEAT-101: Product listing pagination","is_incident_triggered":null},
  {"deploy_id":"d-002","committed_at":"2026-07-28T09:00:00Z","pr_opened_at":"2026-07-28T16:00:00Z","merged_at":"2026-07-29T09:00:00Z","deployed_at":"2026-07-29T10:00:00Z","author":"bob","commit_message":"feat: add rate limiting to auth service","linked_issue_title":"FEAT-102: Auth rate limiting","is_incident_triggered":null},
  {"deploy_id":"d-003","committed_at":"2026-07-29T10:00:00Z","pr_opened_at":"2026-07-30T08:00:00Z","merged_at":"2026-07-30T14:00:00Z","deployed_at":"2026-07-30T15:00:00Z","author":"carol","commit_message":"refactor: simplify order validation logic","linked_issue_title":"TECH-201: Reduce order validation complexity","is_incident_triggered":null},
  {"deploy_id":"d-004","committed_at":"2026-07-31T11:00:00Z","pr_opened_at":"2026-07-31T17:00:00Z","merged_at":"2026-08-01T10:00:00Z","deployed_at":"2026-08-01T11:00:00Z","author":"dave","commit_message":"feat: add webhook retry with exponential backoff","linked_issue_title":"FEAT-103: Webhook reliability","is_incident_triggered":null},
  {"deploy_id":"d-005","committed_at":"2026-08-01T14:00:00Z","pr_opened_at":"2026-08-02T09:00:00Z","merged_at":"2026-08-02T15:00:00Z","deployed_at":"2026-08-02T16:00:00Z","author":"alice","commit_message":"feat: add bulk import for inventory items","linked_issue_title":"FEAT-104: Bulk inventory import","is_incident_triggered":null},
  {"deploy_id":"d-006","committed_at":"2026-08-02T17:00:00Z","pr_opened_at":"2026-08-02T17:30:00Z","merged_at":"2026-08-02T18:00:00Z","deployed_at":"2026-08-02T18:30:00Z","author":"alice","commit_message":"hotfix: revert bulk import, inventory counts drifting","linked_issue_title":"INC-301: Inventory count drift after bulk import deploy","is_incident_triggered":null},
  {"deploy_id":"d-007","committed_at":"2026-08-04T09:00:00Z","pr_opened_at":"2026-08-04T15:00:00Z","merged_at":"2026-08-05T10:00:00Z","deployed_at":"2026-08-05T11:00:00Z","author":"bob","commit_message":"feat: add search filters to admin dashboard","linked_issue_title":"FEAT-105: Admin search filters","is_incident_triggered":null},
  {"deploy_id":"d-008","committed_at":"2026-08-05T13:00:00Z","pr_opened_at":"2026-08-06T08:00:00Z","merged_at":"2026-08-06T14:00:00Z","deployed_at":"2026-08-06T15:00:00Z","author":"carol","commit_message":"feat: add CSV export for order reports","linked_issue_title":"FEAT-106: Order report export","is_incident_triggered":null},
  {"deploy_id":"d-009","committed_at":"2026-08-07T10:00:00Z","pr_opened_at":"2026-08-07T16:00:00Z","merged_at":"2026-08-08T11:00:00Z","deployed_at":"2026-08-08T12:00:00Z","author":"dave","commit_message":"feat: add email notification preferences","linked_issue_title":"FEAT-107: Notification preferences","is_incident_triggered":null},
  {"deploy_id":"d-010","committed_at":"2026-08-09T08:00:00Z","pr_opened_at":"2026-08-09T14:00:00Z","merged_at":"2026-08-10T09:00:00Z","deployed_at":"2026-08-10T10:00:00Z","author":"alice","commit_message":"feat: add caching layer for product catalog","linked_issue_title":"PERF-401: Product catalog performance","is_incident_triggered":null},
  {"deploy_id":"d-011","committed_at":"2026-08-10T12:00:00Z","pr_opened_at":"2026-08-10T12:30:00Z","merged_at":"2026-08-10T13:00:00Z","deployed_at":"2026-08-10T13:30:00Z","author":"alice","commit_message":"hotfix: disable cache, stale prices shown to customers","linked_issue_title":"INC-302: Stale pricing after cache deploy","is_incident_triggered":null},
  {"deploy_id":"d-012","committed_at":"2026-08-11T09:00:00Z","pr_opened_at":"2026-08-11T15:00:00Z","merged_at":"2026-08-12T10:00:00Z","deployed_at":"2026-08-12T11:00:00Z","author":"bob","commit_message":"feat: add two-factor auth for admin accounts","linked_issue_title":"SEC-501: Admin 2FA","is_incident_triggered":null},
  {"deploy_id":"d-013","committed_at":"2026-08-13T10:00:00Z","pr_opened_at":"2026-08-13T16:00:00Z","merged_at":"2026-08-14T09:00:00Z","deployed_at":"2026-08-14T10:00:00Z","author":"carol","commit_message":"feat: add audit logging for payment transactions","linked_issue_title":"SEC-502: Payment audit trail","is_incident_triggered":null},
  {"deploy_id":"d-014","committed_at":"2026-08-14T14:00:00Z","pr_opened_at":"2026-08-15T09:00:00Z","merged_at":"2026-08-15T15:00:00Z","deployed_at":"2026-08-15T16:00:00Z","author":"dave","commit_message":"feat: add scheduled report generation","linked_issue_title":"FEAT-108: Scheduled reports","is_incident_triggered":null},
  {"deploy_id":"d-015","committed_at":"2026-08-15T17:00:00Z","pr_opened_at":"2026-08-15T17:30:00Z","merged_at":"2026-08-15T18:00:00Z","deployed_at":"2026-08-15T18:30:00Z","author":"dave","commit_message":"hotfix: scheduled reports triggering duplicate sends","linked_issue_title":"INC-303: Duplicate report emails after scheduler deploy","is_incident_triggered":null},
  {"deploy_id":"d-016","committed_at":"2026-08-17T08:00:00Z","pr_opened_at":"2026-08-17T14:00:00Z","merged_at":"2026-08-18T09:00:00Z","deployed_at":"2026-08-18T10:00:00Z","author":"bob","commit_message":"feat: add dark mode to customer portal","linked_issue_title":"FEAT-109: Dark mode","is_incident_triggered":null},
  {"deploy_id":"d-017","committed_at":"2026-08-19T10:00:00Z","pr_opened_at":"2026-08-19T17:00:00Z","merged_at":"2026-08-20T10:00:00Z","deployed_at":"2026-08-20T11:00:00Z","author":"carol","commit_message":"feat: add order status webhooks for partners","linked_issue_title":"FEAT-110: Partner webhooks","is_incident_triggered":null},
  {"deploy_id":"d-018","committed_at":"2026-08-20T14:00:00Z","pr_opened_at":"2026-08-21T09:00:00Z","merged_at":"2026-08-21T15:00:00Z","deployed_at":"2026-08-21T16:00:00Z","author":"alice","commit_message":"refactor: migrate user service to async handlers","linked_issue_title":"TECH-202: User service async migration","is_incident_triggered":null},
  {"deploy_id":"d-019","committed_at":"2026-08-22T09:00:00Z","pr_opened_at":"2026-08-22T15:00:00Z","merged_at":"2026-08-23T10:00:00Z","deployed_at":"2026-08-23T11:00:00Z","author":"dave","commit_message":"feat: add real-time inventory sync with warehouse API","linked_issue_title":"FEAT-111: Warehouse inventory sync","is_incident_triggered":null},
  {"deploy_id":"d-020","committed_at":"2026-08-24T10:00:00Z","pr_opened_at":"2026-08-24T16:00:00Z","merged_at":"2026-08-25T09:00:00Z","deployed_at":"2026-08-25T10:00:00Z","author":"bob","commit_message":"feat: add customer feedback widget","linked_issue_title":"FEAT-112: Feedback widget","is_incident_triggered":null}
]

What is in each deploy record?

Every record has four timestamp clocks in chronological order: committed_at (code written), pr_opened_at (review requested), merged_at (approved), and deployed_at (running in production). In Step 3, you will see that choosing a different pair of these clocks gives a different lead-time median from the same 20 deploys.

Three records (d-006, d-011, d-015) are hotfix rework. Their commit messages start with hotfix: and their linked issue titles reference incidents. Four records (d-005, d-010, d-014, d-019) are the deploys that caused the incidents in the first place.

  • Save the complete teaching corpus as data/deploys.json.
  • Confirm that the file contains 20 ordered records, all nine required fields, and an unpopulated label field by running this command:
python -c "import json; d=json.load(open('data/deploys.json')); k={'deploy_id','committed_at','pr_opened_at','merged_at','deployed_at','author','commit_message','linked_issue_title','is_incident_triggered'}; assert len(d)==20; assert [r['deploy_id'] for r in d]==['d-{:03d}'.format(i) for i in range(1,21)]; assert all(isinstance(r,dict) and set(r)==k for r in d); assert all(r['is_incident_triggered'] is None for r in d); assert d[0]['committed_at']=='2026-07-27T08:00:00Z' and d[0]['deployed_at']=='2026-07-28T11:00:00Z'; print('20 valid teaching deploy records with empty labels')"

You should see 20 valid teaching deploy records with empty labels. This confirms the fixed corpus has the expected count, ordered IDs, nine-field schema, and canonical first deployment.

Corpus check failed?

  • Check that your terminal is inside the delivery-scoreboard folder containing the data directory.
  • Replace the complete contents of data/deploys.json with the supplied 20-record teaching corpus if Git history or another dataset overwrote it.
  • Keep the IDs d-001 through d-020 in order. Do not invent or duplicate records to reach 20.

Help me restore the captured corpus.

Create the incident records, sealed labels, and reference values

Incidents link back to the deploy that caused the degradation. Save the sealed answer key once as data/sealed_labels.json so the collector and AI gate read the same artifact.

  • Create data/incidents.json with four incident records:
[
  {"incident_id":"I-001","causal_deploy_id":"d-005","impairment_start":"2026-08-02T16:30:00Z","restored_at":"2026-08-02T18:00:00Z"},
  {"incident_id":"I-002","causal_deploy_id":"d-010","impairment_start":"2026-08-10T10:30:00Z","restored_at":"2026-08-10T13:00:00Z"},
  {"incident_id":"I-003","causal_deploy_id":"d-014","impairment_start":"2026-08-15T16:30:00Z","restored_at":"2026-08-15T18:00:00Z"},
  {"incident_id":"I-004","causal_deploy_id":"d-019","impairment_start":"2026-08-23T11:30:00Z","restored_at":"2026-08-23T14:00:00Z"}
]

What does each incident record capture?

Each incident has a causal_deploy_id pointing to the deploy that degraded production. The impairment_start and restored_at fields define the window of degradation. These fields power two metrics: change fail rate (which deploys caused incidents) and failed deployment recovery time (how long each incident lasted).

  • Create data/sealed_labels.json with 20 entries, one per deploy:
[
  {"deploy_id":"d-001","is_incident_triggered":false},
  {"deploy_id":"d-002","is_incident_triggered":false},
  {"deploy_id":"d-003","is_incident_triggered":false},
  {"deploy_id":"d-004","is_incident_triggered":false},
  {"deploy_id":"d-005","is_incident_triggered":false},
  {"deploy_id":"d-006","is_incident_triggered":true},
  {"deploy_id":"d-007","is_incident_triggered":false},
  {"deploy_id":"d-008","is_incident_triggered":false},
  {"deploy_id":"d-009","is_incident_triggered":false},
  {"deploy_id":"d-010","is_incident_triggered":false},
  {"deploy_id":"d-011","is_incident_triggered":true},
  {"deploy_id":"d-012","is_incident_triggered":false},
  {"deploy_id":"d-013","is_incident_triggered":false},
  {"deploy_id":"d-014","is_incident_triggered":false},
  {"deploy_id":"d-015","is_incident_triggered":true},
  {"deploy_id":"d-016","is_incident_triggered":false},
  {"deploy_id":"d-017","is_incident_triggered":false},
  {"deploy_id":"d-018","is_incident_triggered":false},
  {"deploy_id":"d-019","is_incident_triggered":false},
  {"deploy_id":"d-020","is_incident_triggered":false}
]

Why are the sealed labels separate from deploys?

The is_incident_triggered flag marks whether a deploy was unplanned rework responding to a production incident. This classification is the answer key. In Step 4, the AI will label all 20 deploys before this file is opened, and then both files are compared to score the gate.

Exactly 3 are true: d-006, d-011, and d-015. These are the hotfix rework deploys, not the deploys that caused the incidents.

  • Save both files.
  • Confirm the incident and label counts by running this command:
python -c "import json; print(len(json.load(open('data/incidents.json'))), 'incidents'); print(sum(1 for x in json.load(open('data/sealed_labels.json')) if x.get('is_incident_triggered')), 'incident-triggered')"

You should see 4 incidents on one line and 3 incident-triggered on the next.

Seeing 0 incident-triggered?

  • Open data/sealed_labels.json and check that d-006, d-011, and d-015 have "is_incident_triggered":true (no quotes around true).
  • If your editor saved "true" as a string instead of the boolean true, the count check will return 0. Remove the quotes around the boolean values.

My sealed labels count is wrong. What should I check?

  • Create data/reference.json with the expected metric values:
{
  "deployment_frequency": 20,
  "change_lead_time_committed": 26.0,
  "change_lead_time_pr_opened": 18.0,
  "change_lead_time_merged": 1.0,
  "d001_committed_lead_hours": 27.0,
  "change_fail_rate": 20.0,
  "failed_deployment_recovery_time": 2.0,
  "rework_rate": 15.0
}

Why store medians and one row?

DORA defines change lead time as committed-to-deployed. The reference file stores all three medians so Step 3 can prove that changing the clock changes the answer.

The d001_committed_lead_hours value independently locks the canonical 27-hour span for d-001. A one-row date edit can leave the 20-row median unchanged, so this check catches drift that the median misses.

  • Save data/reference.json.
  • Confirm all four data files exist by running:
ls data/

You should see deploys.json, incidents.json, reference.json, and sealed_labels.json listed.

Commit the corpus and verify
  • Stage and commit all four data files by running these commands:
git add data/
git commit -m "Add captured corpus: deploys, incidents, sealed labels, reference"
  • Tick the corpus boxes in TASKS.md.

Before you run the next command, predict: how many of the 20 deploys should the sealed labels mark as incident-triggered?

  • Verify the three record files together by running this command:
python -c "import json; d=json.load(open('data/deploys.json')); i=json.load(open('data/incidents.json')); s=json.load(open('data/sealed_labels.json')); print(len(d), 'deploys,', len(i), 'incidents,', sum(1 for x in s if x.get('is_incident_triggered')), 'incident-triggered')"

You should see: 20 deploys, 4 incidents, 3 incident-triggered. Twenty deploys total, four incidents caused by four of those deploys, and three deploys that were hotfix rework in response to incidents.

Seeing a SyntaxError on Windows?

Windows Command Prompt handles quotes differently from macOS and Linux terminals. If the one-liner fails, open a Python shell by running python in your terminal, then paste these lines one at a time:

import json
d = json.load(open('data/deploys.json'))
i = json.load(open('data/incidents.json'))
s = json.load(open('data/sealed_labels.json'))
print(len(d), 'deploys,', len(i), 'incidents,', sum(1 for x in s if x.get('is_incident_triggered')), 'incident-triggered')
exit()

The verify command is failing on my machine. What should I try?

Your corpus is locked in: 20 deploys, 4 incidents, 3 rework labels, and reference values for every metric. Next up, you will turn these flat JSON files into five auditable numbers by writing the SQL definitions that query them.

Define Five Metrics in SQL

In Step 2, you built a corpus of 20 deploys, 4 incidents, and a sealed answer key. The data is in place, but there is no way to ask questions of it yet.

Loading the data into DuckDB and writing SQL definitions turns flat JSON files into something a reviewer can audit by reading a WHERE clause. Each query carries a comment naming what it counts and what it excludes, because the exclusion is the arguable part. You will also discover that three different clock definitions give three different medians from the same 20 deploys.

In this step, get ready to:
  • Write five ordered SQL query definitions with their counts, exclusions, and limits documented.
  • Wire the collector to execute the first four metrics and protect them with three exact assertions.
  • Verify the six-line first-run output, including the three-clock diagnostic and the withheld rework-rate slot.
Write the five SQL definitions

Every metric lives in one SQL file. Each query block opens with a comment header that names what it counts, what it excludes, and what it cannot prove. The exclusion is the part a reviewer will argue about, so it belongs right next to the query.

  • In your code editor's file explorer, create metrics.sql inside the delivery-scoreboard folder beside scoreboard.py.
  • Add the first three metric definitions to metrics.sql by copying the SQL block below:
-- deployment_frequency
-- Counts: all deploys in the 30-day window
-- Excludes: nothing
-- Does not prove: that the changes were wanted
SELECT COUNT(*) AS value FROM deploys;

-- change_lead_time
-- Counts: median hours from code committed to version control to deployed in production
-- Excludes: nothing. This is DORA's definition: committed to running in production
-- Does not prove: that shorter is better regardless of batch size
-- NOTE: pr-opened-to-deployed and merged-to-deployed are different measures, not this one
SELECT
    MEDIAN(epoch(deployed_at - committed_at) / 3600.0) AS value
FROM deploys;

-- change_fail_rate
-- Counts: percentage of deploys that degraded production, identified by incident records
-- Excludes: severity. A five-minute blip and a four-hour outage count the same
-- Does not prove: that a lower rate means fewer users were affected
SELECT
    ROUND(
        CAST(COUNT(DISTINCT i.causal_deploy_id) AS DOUBLE)
        / (SELECT COUNT(*) FROM deploys) * 100,
        1
    ) AS value
FROM incidents i;

What do these three queries do?

  • deployment_frequency counts every deploy in the window. It excludes nothing. A high count does not prove the changes were wanted.
  • change_lead_time measures the median hours from committed_at to deployed_at. That is DORA's definition. The NOTE comment names two alternative clocks (pr-opened and merged) that are different measures.
  • change_fail_rate counts distinct causal deploys from the incidents table, divided by total deploys. A five-minute blip and a four-hour outage count the same because severity is excluded.
  • Add the final two metric definitions below the first three in metrics.sql by copying the SQL block below:
-- failed_deployment_recovery_time
-- Counts: median hours from production impairment to service restoration
-- Excludes: incidents not caused by a deployment
-- Does not prove: that faster recovery means fewer users were affected
SELECT
    MEDIAN(epoch(restored_at - impairment_start) / 3600.0) AS value
FROM incidents;

-- rework_rate
-- Counts: percentage of deploys that are unplanned work resulting from a production incident
-- Excludes: deploys classified as planned work
-- Does not prove: anything, unless the AI gate passed. See gate results
-- GATED: this query only runs if accuracy >= 18/20 AND predicted_count == sealed_count
SELECT
    ROUND(
        CAST(SUM(CASE WHEN is_incident_triggered THEN 1 ELSE 0 END) AS DOUBLE)
        / COUNT(*) * 100,
        1
    ) AS value
FROM deploy_labels;

What do these two queries do?

  • failed_deployment_recovery_time measures the median hours from impairment_start to restored_at. It does not tell you how many users were affected during that window.
  • rework_rate is gated. It reads from a deploy_labels table that only exists if the AI classification passes two tests. Without the gate, this query never runs.
  • Save metrics.sql.
  • Attach metrics.sql to your AI client's chat.
  • Paste the full contents of metrics.sql into the chat if your AI client does not accept file attachments.
  • Ask the AI client to validate the finished file by sending this prompt:
Review and validate the completed metrics.sql file.

Check all five query blocks:
- deployment_frequency counts every deploy in the 30-day window.
- change_lead_time measures the median hours from committed_at to deployed_at.
- change_fail_rate divides distinct causal deployment IDs by total deploys and rounds the percentage to one decimal place.
- failed_deployment_recovery_time measures the median hours from impairment_start to restored_at.
- rework_rate divides incident-triggered deploys by total labelled deploys and includes the GATED comment.

Also confirm that the file uses valid DuckDB SQL, aliases every result as value, keeps the five metric names in the correct order, and separates each query block with one blank line.

Return a PASS or FAIL for each metric. Do not rewrite code that passes. For each failure, explain the mismatch and provide only the corrected query block.
  • Review the AI client's checklist. You should see a PASS for all five metrics.
  • Compare any suggested correction with the two supplied SQL blocks before changing metrics.sql.

✔️ Awesome, I've got everything!

Double check you have saved metrics.sql.

ⓧ I'd like to double check the full code

Your metrics.sql should contain five query blocks in this order, each separated by a blank line:

  • deployment_frequency (the first block you added).
  • change_lead_time (second block).
  • change_fail_rate (third block).
  • failed_deployment_recovery_time (fourth block).
  • rework_rate with the GATED comment (fifth and final block).

Scroll up and confirm every comment header and every query matches the two code blocks above.

Wire the collector to run the queries

The skeleton already contains the data loaders and rendering logic. Keep its filename contract fixed: main() loads data/deploys.json and data/sealed_labels.json, while run_gate() checks whether every deploy record has a boolean is_incident_triggered value. The gate stays withheld while those values are null.

Keep the JSON keys exact

The loader variables match the files in the data folder. Keep these key names unchanged so each JSON value reaches the intended DuckDB column.

  • Use deploy_id, author, commit_message, linked_issue_title, committed_at, pr_opened_at, merged_at, deployed_at, and is_incident_triggered in data/deploys.json.
  • Use incident_id, causal_deploy_id, impairment_start, and restored_at in data/incidents.json.
  • Use deploy_id and is_incident_triggered in data/sealed_labels.json. Keep this answer key separate from the predictions stored in data/deploys.json.
  • Read expected metric values from data/reference.json without loading that file into a DuckDB table.
  • Use the next code block only as a reference for the output that main() must produce.
  • Do not send this reference block to your AI client.
  • Continue to the main() request below. Its prompt already includes the same six-line contract.
REFERENCE ONLY: EXPECTED STDOUT CONTRACT

PASS the first run only when it exits with code 0 and prints exactly:
26.0 18.0 1.0
deployment_frequency: 20
change_lead_time: 26.0
change_fail_rate: 20.0
failed_deployment_recovery_time: 2.0
rework_rate: WITHHELD (deploy labels not populated)

Rules:
- Line 1 comes from run_three_clock_check() in committed, PR-opened, merged order.
- The clock line is separate from the five named metric slots.
- Execute the first four metrics before the gate. Do not run rework_rate SQL until the gate passes.
- Map (None, "deploy labels not populated") to the exact WITHHELD line above.
- Use .format(), not f-strings.
- FAIL if stdout contains only 26.0 18.0 1.0.
- FAIL if any sealed-label source path raises FileNotFoundError.
- PASS the date-break only when the traceback ends with: AssertionError: lead-time assertion against reference.json failed

What does this reference contract check?

  • sealed_raw remains the DuckDB table name and is loaded from data/sealed_labels.json.
  • run_metrics() and execute_metric() must match the supplied reference functions line for line so blank-line parsing cannot drift.
  • run_gate() reads predicted is_incident_triggered values from the loaded deploys table and compares them with data/sealed_labels.json.
  • The first four metrics execute before the gate, while rework_rate executes only after the gate passes.
  • Open scoreboard.py in your code editor.
  • Add scoreboard.py to your AI client's context.
  • Ask the AI client to add only the SQL parser and query executor by sending this prompt:
Update scoreboard.py with only run_metrics(conn, sql_path) and execute_metric(conn, query).

Preserve every existing function, constant, and import.

Use these exact implementations:
def run_metrics(conn, sql_path):
    with open(sql_path, "r") as f:
        sql_text = f.read()
    blocks = sql_text.strip().split("\n\n")
    results = {}
    for block in blocks:
        lines = block.strip().split("\n")
        if not lines[0].startswith("--"):
            continue
        name = lines[0].lstrip("- ").strip()
        query_lines = [ln for ln in lines[1:] if not ln.startswith("--")]
        query = "\n".join(query_lines).strip()
        if not query:
            continue
        results[name] = {"query": query, "name": name}
    return results


def execute_metric(conn, query):
    return conn.execute(query).fetchone()

Do not redesign the parser. Keep the implementation compatible with Python 3.9.
  • Review the two functions that the AI client added to scoreboard.py.
  • Replace either function if it differs from the exact reference body below.
def run_metrics(conn, sql_path):
    with open(sql_path, "r") as f:
        sql_text = f.read()
    blocks = sql_text.strip().split("\n\n")
    results = {}
    for block in blocks:
        lines = block.strip().split("\n")
        if not lines[0].startswith("--"):
            continue
        name = lines[0].lstrip("- ").strip()
        query_lines = [ln for ln in lines[1:] if not ln.startswith("--")]
        query = "\n".join(query_lines).strip()
        if not query:
            continue
        results[name] = {"query": query, "name": name}
    return results


def execute_metric(conn, query):
    return conn.execute(query).fetchone()

What does this parsing logic do?

  • run_metrics splits the SQL file on blank lines, so each query block becomes its own string. It reads the metric name from the first comment line and strips out all other comment lines to get the raw query.
  • execute_metric is a one-liner that runs a query and returns the first row. The collector holds no arithmetic of its own. If a number is wrong, the query in metrics.sql is wrong.
  • Keep scoreboard.py in your AI client's context.
  • Ask the AI client to add only the three-clock comparison by sending this prompt:
Update scoreboard.py with only one new function: run_three_clock_check(conn).

Preserve every existing function, constant, and import.

Requirements:
- Run three separate DuckDB MEDIAN queries against the deploys table.
- Calculate deployed_at minus committed_at for the first clock.
- Calculate deployed_at minus pr_opened_at for the second clock.
- Calculate deployed_at minus merged_at for the third clock.
- Convert each interval to hours with epoch(...) divided by 3600.0.
- Return the three median values as one tuple in committed, PR-opened, merged order.

Keep the implementation compatible with Python 3.9. Make no other changes to scoreboard.py.
  • Review the new run_three_clock_check() function.
  • Confirm that it runs three separate MEDIAN queries.
  • Confirm that it returns the committed, PR-opened, and merged medians in that order.
  • Keep the updated scoreboard.py in your AI client's context.
  • Send the entire prompt in the next code block to your AI client.
  • Do not append the earlier reference block. This prompt already includes the filename contract and the six required output lines.
Update scoreboard.py with only run_gate() and main().

Preserve every other function, constant, import, and rendering call.

Requirements for run_gate():
- Change the signature to run_gate(conn, sealed_labels_path).
- Read deploy_id and is_incident_triggered from the loaded deploys table.
- Return (None, "deploy labels not populated") if any of the 20 prediction values is NULL.
- Load the answer key from data/sealed_labels.json through the existing sealed-label loader.
- Keep the existing 18-of-20 accuracy threshold and predicted-count equality test.
- Keep the existing gate-result messages and tuple return shape.
- Create or replace deploy_labels from the deploys table only after both gate tests pass.

Requirements for main():
- Create the DuckDB connection expected by the existing loaders.
- Load data/deploys.json with load_deploys().
- Load data/incidents.json with load_incidents().
- Call load_sealed_raw(conn, data_dir / "sealed_labels.json").
- Read the named queries from metrics.sql with run_metrics().
- Execute the first four metrics before calling the gate. Do not execute rework_rate SQL until the gate passes.
- Load data/reference.json after computing the first four metrics.
- Keep the three existing exact assertions unchanged.
- Call run_gate(conn, data_dir / "sealed_labels.json").
- Map (None, "deploy labels not populated") to rework_rate: WITHHELD (deploy labels not populated).
- Run run_three_clock_check() before printing the five named metric slots.
- Use .format(), not f-strings, for Python 3.9 compatibility.
  • Confirm that load_sealed_raw() reads data/sealed_labels.json.
  • Confirm that run_gate() reads predicted is_incident_triggered values from the loaded deploys table.
  • Confirm that data/reference.json loads after the first four metrics are computed.
  • Confirm that all three exact assertions from the prompt remain in main().
  • Confirm that the first run prints the six-line contract with rework_rate: WITHHELD (deploy labels not populated) as the final line.
  • Confirm that rework_rate SQL runs only after the gate passes.
  • Save scoreboard.py.

Why use three exact assertions?

Deployment frequency is an integer count. A tolerance band would either reproduce exact equality or allow the wrong count to pass.

The committed lead-time assertion protects the metric definition. The d-001 row assertion protects a canonical timestamp pair that a 20-row median can hide.

✔️ Awesome, I've got everything!

Double check you have saved scoreboard.py.

ⓧ I'd like to double check the full code

Your scoreboard.py should contain the following functions and constants, in this order:

  • load_deploys(conn, json_path) loads data/deploys.json into the deploys table.
  • load_incidents(conn, json_path) loads the incident records.
  • load_sealed_raw(conn, sealed_path) loads the separate answer key.
  • run_metrics(conn, sql_path) parses the SQL file into named query blocks.
  • execute_metric(conn, query) runs one query and returns its row.
  • run_three_clock_check(conn) calculates the committed, PR-opened, and merged medians.
  • run_gate(conn, sealed_labels_path) compares predictions stored in the deploys table with the sealed answer key.
  • CANNOT_PROVE, METRIC_LABELS, and METRIC_ORDER provide the rendering constants.
  • format_value(name, value) formats numbers for display.
  • generate_svg(computed, gate_result) and generate_html(...) build the readout.
  • main() wires the functions together with assertions and the gate call.

Use the three prompts above to confirm run_metrics(), execute_metric(), run_three_clock_check(), and main() each handle only their assigned responsibility.

Run, verify, and break on purpose

Both files are saved. Before you run the script, think about what you expect to see. There are 20 deploys with unpopulated labels and three different clocks on the same data.

  • Before you run this, predict: how many metrics will print a value, and what will the fifth slot say?
  • Run the collector by running this command:
python scoreboard.py

Four metrics print their values. The fifth slot reads rework_rate: WITHHELD (deploy labels not populated). That is correct because every is_incident_triggered value in data/deploys.json is still null.

What should I see?

26.0 18.0 1.0
deployment_frequency: 20
change_lead_time: 26.0
change_fail_rate: 20.0
failed_deployment_recovery_time: 2.0
rework_rate: WITHHELD (deploy labels not populated)

The first line is the three-clock diagnostic in committed, PR-opened, merged order. The next five lines are the named scoreboard slots.

No assertion-status line appears in this contract. A run containing only the three clock values fails because it omits the scoreboard.

Now prove the row-level assertion catches bad data. The 20-row median can remain 26.0, so this fixture-mutation test targets d-001 directly.

  • Open data/deploys.json in your code editor.
  • Find the first record with the deploy_id value d-001.
  • Replace its committed_at value of 2026-07-27T08:00:00Z with 2026-07-26T08:00:00Z.
  • Save data/deploys.json.
  • Run the collector again by running this command:
python scoreboard.py

The traceback must end with AssertionError: lead-time assertion against reference.json failed. A bare AssertionError is not enough.

  • Restore d-001's committed_at value to 2026-07-27T08:00:00Z.
  • Save data/deploys.json.
  • Run the collector again to confirm the six-line success output:
python scoreboard.py
  • Confirm that stdout matches the six exact lines in the earlier What should I see? callout.
  • Commit the restored data and collector by running these commands:
git add metrics.sql scoreboard.py
git commit -m "Add five SQL metric definitions and wire collector"
  • Tick the SQL definition boxes in TASKS.md and save.

Assertion still failing after restoring the file?

  • Make sure the committed_at value for d-001 is exactly 2026-07-27T08:00:00Z with no trailing spaces or missing characters.
  • Check that you saved the file after restoring the value. Unsaved changes in your editor will not be picked up by the script.
  • Help me fix my assertion error after restoring the data

Four metrics are locked in, and the fifth is honestly refusing to compute without classification data. Next up, you will run the AI gate to classify those 20 deploys and see if rework rate clears its threshold.

Run the AI Gate for Rework Rate

Your Step 3 scoreboard should print four named metric values and a fifth rework_rate: WITHHELD (deploy labels not populated) slot. If it prints only three clock medians, return to Step 3 and fix main() because the diagnostic does not count as the scoreboard readout.

Deployment rework rate needs a different kind of input. It needs to know WHY each deploy happened, not just when. That judgment lives in commit messages and linked issue titles, and it is the job you will hand to your AI client in this step.

In this step, get ready to:
  • Send the 20 deploy records to your AI client for classification.
  • Populate each deployment label inside data/deploys.json.
  • Run the dual-test gate and commit the verified deployment data.
Send deploys to your AI client for classification

The rework-rate query counts deploys that were unplanned work triggered by a production incident. Each record in data/deploys.json already has an is_incident_triggered field, but it remains null until your AI client classifies the record.

Commit messages like hotfix: revert bulk import, inventory counts drifting and linked issue titles like INC-301: Inventory count drift after bulk import deploy contain the signal. This is the fuzzy classification job a model handles well.

What is deployment rework rate?

DORA defines deployment rework rate as the ratio of deployments that are unplanned but happen as a result of an incident in production. A deploy that fixes a bug discovered in the previous release counts. A planned feature deploy does not.

The AI classifies each deployment by filling the is_incident_triggered field in data/deploys.json. The gate compares those predictions with the sealed ground truth before it computes the metric.

  • Open data/deploys.json in your text editor.
  • Copy the entire contents of data/deploys.json.
  • Open a new conversation in your AI client.
  • Paste this prompt into the conversation:
Classify each of the 20 deploy records that I provide as incident-triggered or planned.

Use the commit_message and linked_issue_title fields as evidence.

A deploy is incident-triggered when it appears to be unplanned work done in response to a production incident. Otherwise, treat it as planned.

Return all 20 complete deploy records in their original order. Preserve every existing key and value exactly. Replace only each is_incident_triggered value: use true for an incident-triggered deploy or false for a planned deploy.

Return only a valid JSON array. Do not include an explanation or a Markdown code fence.
  • Paste the copied deploy records on a new line beneath the prompt.
  • Send the completed prompt to your AI client.

What shape should the output be?

Your AI client should produce exactly 20 complete deploy records. Each record keeps the original nine keys, with is_incident_triggered changed from null to a JSON boolean. Here is the expected shape for the first record:

[
  {"deploy_id":"d-001","committed_at":"2026-07-27T08:00:00Z","pr_opened_at":"2026-07-27T14:00:00Z","merged_at":"2026-07-28T10:00:00Z","deployed_at":"2026-07-28T11:00:00Z","author":"alice","commit_message":"feat: add pagination to /api/products endpoint","linked_issue_title":"FEAT-101: Product listing pagination","is_incident_triggered":false}
]

📣 Do not share the sealed labels

Your AI client should see only the deploy records. Do not paste data/sealed_labels.json into the prompt. The sealed labels are the answer key. If the AI sees them, the gate test is meaningless.

  • Review the AI output. Confirm there are exactly 20 records with deploy_id values from d-001 through d-020.
  • Confirm that every original field and value remains unchanged.
  • Replace the complete contents of data/deploys.json with the returned JSON array.
  • Verify the file loaded correctly by running this command:
python -c "import json; d=json.load(open('data/deploys.json')); assert len(d)==20; assert all(type(x.get('is_incident_triggered')) is bool for x in d); assert d[0]['committed_at']=='2026-07-27T08:00:00Z' and d[0]['deployed_at']=='2026-07-28T11:00:00Z'; print(len(d), 'deploys,', sum(1 for x in d if x['is_incident_triggered']), 'incident-triggered')"

You should see 20 deploys followed by the number your AI predicted as incident-triggered. This confirms every deployment label is now a boolean.

Deployment-label validation failed?

  • Confirm that data/deploys.json still contains all 20 complete deploy records.
  • Check that every is_incident_triggered value is the JSON boolean true or false, without quotation marks.
  • Restore the supplied Step 2 corpus if the AI changed any IDs, timestamps, authors, messages, or issue titles.

Help me validate the populated deployment data.

Run the dual-test gate

The gate runs two tests, and both must pass. The first checks accuracy: did the AI match the sealed labels on at least 18 of 20 deploys? The second checks the predicted count: did the AI predict the same number of incident-triggered deploys as the sealed answer key?

Why two tests, not just accuracy?

Accuracy alone would be unsound. Two errors in opposite directions still score 18/20 while moving the predicted count between 1 and 5. That is a rate between 5% and 25%.

The count test catches this. A gate that passes a wrong numerator is not a gate. Both tests protect the number. If either fails, the collector refuses to compute the rate and prints WITHHELD with both scores.

Before you run this, take a moment to predict. Do you think your AI client classified all 20 correctly? How many did it predict as incident-triggered?

  • Run the scoreboard to trigger the gate by running this command:
python scoreboard.py

What should I see?

Your spacing may differ, but the values should match this output:

26.0 18.0 1.0

deployment_frequency: 20

change_lead_time: 26.0

change_fail_rate: 20.0

failed_deployment_recovery_time: 2.0

rework_rate: 15.0

The first line is the three-clock diagnostic. The five named lines are the scoreboard.

A numeric rework_rate means both gate tests passed. WITHHELD means the gate did not pass.

🙋‍♀️ Seeing the lead-time assertion fail?

AssertionError: lead-time assertion against reference.json failed means the Step 3 test timestamp is still changed. The script stops before it reaches the AI gate.

  • Open data/deploys.json in your code editor.
  • Find the record with "deploy_id": "d-001".
  • Restore its timestamp to "committed_at": "2026-07-27T08:00:00Z".
  • Save data/deploys.json.
  • Leave reference.json and the assertion in scoreboard.py unchanged.
  • Run the scoreboard again:
python scoreboard.py

Help me fix the d-001 lead-time assertion.

🙋‍♀️ Seeing a sealed-label FileNotFoundError?

  • Open scoreboard.py in your code editor.
  • Confirm that load_sealed_raw() reads data/sealed_labels.json.
  • Confirm that run_gate() receives the DuckDB connection and the sealed-label path only.
  • Confirm that run_gate() reads predicted is_incident_triggered values from the loaded deploys table.
  • Run the corrected scoreboard again:
python scoreboard.py

The command should exit without a FileNotFoundError and print the three-clock diagnostic plus five named metric slots.

Why is the sealed-label file still missing?

✔️ GATE PASSED

Your AI client cleared both tests. The scoreboard prints this line:

rework_rate: 15.0

That numeric result appears only when both gate tests pass. DuckDB computes 3 out of 20 as 15.0% after the sealed labels are joined to the deploys.

ⓧ WITHHELD

One or both tests failed. The output includes a line like:

rework_rate: WITHHELD [accuracy: N/20, predicted_count: M, sealed_count: 3]

This is the gate working correctly. The collector refused to compute a rate because the AI's labels did not meet the threshold. Note the accuracy score and predicted count. These go into your after-action review in the next step.

If you want to try again, restore the Step 2 version of data/deploys.json so every is_incident_triggered value is null. Run the AI classification again, replace the file with the newly populated records, and rerun the scoreboard.

What does this code do?

The run_gate() function in scoreboard.py reads predicted is_incident_triggered values from the loaded deploys table. It compares those predictions with data/sealed_labels.json to calculate accuracy and the predicted incident-triggered count.

If both tests pass, it creates the deploy_labels table from the populated deployment records. It then runs the rework_rate query from metrics.sql. If either test fails, it prints WITHHELD with both scores.

Commit and record the gate outcome

The gate outcome and any errors are evidence you will need for the after-action review in the next step. Record them now while they are on screen.

  • Record PASS when rework_rate is numeric, or WITHHELD when the gate rejects the labels.
  • Record the predicted incident count from the earlier label check.
  • Record all five named scoreboard values.
  • Stage and commit the populated deployment records by running these commands:
git add data/deploys.json
git commit -m "Populate deployment labels for rework rate gate"
  • Open TASKS.md and tick the boxes for the AI gate step.
  • Save TASKS.md.

Why record the gate result now?

The next step uses the gate outcome, predicted incident count, and five metric values in your after-action review. Capturing them now keeps the review tied to the terminal evidence.

Your five-metric scoreboard now has a real result in every slot. The numbers are computed, and the gate has either opened or held the line. Next up, you will annotate every metric with what it cannot prove, close the folder with a tagged commit, and deliver a 60-second walkthrough.

Annotate the Readout and Close the Folder

In Step 4, you ran the AI gate and scored the fifth metric. The numbers are computed. But a stakeholder could put this scoreboard on a slide and say "we deploy 20 times a month with a 20% fail rate" without noticing that a five-minute blip and a four-hour outage count the same way.

This step transforms the scoreboard from computed output into evidence that survives scrutiny. You will review the cannot-prove annotations that the skeleton already built into the readout, write an after-action review, and close the folder with a tagged commit.

In this step, get ready to:
  • Review the generated readout and verify every annotation is present and specific.
  • Write the after-action review and README.
  • Close the folder with a clean commit and local tag.
Review the readout and verify annotations

The scoreboard.py skeleton already contains the cannot-prove annotations, dated DORA anchor, GAO analogy, honest limits, and the what-this-does-not-fix section. Running the script generates a self-contained scoreboard.html with all of these baked in.

  • Generate the final readout by running this command:
python scoreboard.py

What does the console output show?

The script prints the five metric values (or WITHHELD for rework rate if the gate failed) and the three-clock comparison showing 26.0h, 18.0h, and 1.0h. A successful run also creates scoreboard.html in the same folder as scoreboard.py.

  • Open your AI client.
  • Attach scoreboard.html to the conversation.
  • Ask it to audit the readout by pasting this prompt:
Review the attached scoreboard.html as a content auditor. Do not rewrite it.

Return PASS or FAIL for each item:
1. Five metric cards are present.
2. Every card has a Cannot prove annotation that is specific to that metric.
3. The dated DORA anchor names the 2025 report and 23 September 2025.
4. The DORA safeguards cover supersession, retired performance tiers, and the 2023 and 2024 cluster counts.
5. The GAO analogy is labelled as an inference with a verify-before-repeating hedge.
6. The honest limits cover the untested transport, volatile 30-day window, and limits of judging a team from metrics.
7. A What this does not fix section is present.

For every FAIL, quote the missing or vague passage and explain what needs attention. Return OVERALL PASS only if every item passes.

The AI review catches omissions without adding another script to the project. An OVERALL PASS means the content is ready for your browser check.

Before you open the file, think about what you expect to see. Each metric card has a "Cannot prove:" sentence. Will those sentences be generic enough to fit any metric, or specific to one?

  • Open scoreboard.html by double-clicking it in Finder (macOS) or File Explorer (Windows). No terminal needed.

Five metric cards render in dark rectangles. Each card has an accent bar (green if the value is computed, red if withheld), the metric value in white, and an orange "Cannot prove:" sentence underneath.

What makes each annotation specific?

Each annotation names what THIS metric fails to establish. A sentence that fits any metric ("this metric is imperfect") does not count. The five annotations are:

  • Deployment Frequency: cannot prove that the changes were wanted, or that higher frequency means better outcomes at any batch size.
  • Change Lead Time: cannot prove that shorter is better regardless of batch size. This measures committed-to-deployed, not pr-open-to-merge.
  • Change Fail Rate: cannot prove severity. A five-minute blip and a four-hour outage count the same.
  • Recovery Time: cannot prove that faster recovery means fewer users were affected.
  • Rework Rate: cannot prove the AI classification is correct. A rework rate over one month does not prove a trend.

Scroll down past the metric cards. The readout continues with several sections that anchor the numbers in context.

What is the dated DORA anchor?

The anchor protects the reader from unsourced claims about the framework. It names the 2025 report title (State of AI-assisted Software Development), the announcement date (23 September 2025), and all five current metrics.

Two protective statements follow: no later annual report has superseded it, and no primary source says the performance tiers were retired. The cluster count is emergent from each year's data and was four again in 2023 and 2024.

Below the anchor, the GAO analogy is labelled as this build's inference, not GAO's claim. It carries a "verify against current reports before repeating" hedge. The honest limits section names three limits: the transport was never exercised, the 30-day window is volatile (one bad week moves every number), and a metric set does not tell you whether a team is good.

Page looks blank or garbled?

  • Make sure you opened scoreboard.html by double-clicking the file, not scoreboard.py.
  • If the cards show no values, re-run python scoreboard.py and check the console for errors. The script must finish without an AssertionError for the HTML to be complete.
  • Having trouble?
Write the after-action review and README

The after-action review captures what you learned from the build, including where the AI helped and where it was wrong. The README tells the next person what this project does and how to run it.

  • Open your AI client.
  • Attach scoreboard.html to the conversation.
  • Draft the after-action review by pasting this prompt:
You helped me build a DORA Delivery Scoreboard with DuckDB. Draft after-action.md using evidence from this conversation and the attached scoreboard.html.

Do not invent details. If evidence is missing, ask me only for those details before drafting.

Use this structure:
# After-Action Review
## What I built
## Where AI helped
## Where AI was wrong or uncertain
## What I would change next time

Mention the rework-rate gate, its predicted total, the SQL recount, and whether they matched. Explain corrections using concrete evidence from the build.

Return only the finished Markdown.
  • Read the draft against your actual build experience.
  • Correct any claim the AI cannot support with project evidence.
  • Save the reviewed draft as after-action.md beside scoreboard.py.
  • Attach after-action.md to the same AI conversation.
  • Draft the README by pasting this prompt:
Draft README.md for the DORA Delivery Scoreboard with DuckDB. Use only the attached scoreboard.html, after-action.md, and evidence from this conversation.

Do not invent features, commands, results, or limitations. Mark unsupported details for my review.

Use this structure:
# DORA Delivery Scoreboard with DuckDB
## What it does
## How to run
## What you will see
## What the metrics cannot prove
## What this does not fix

In How to run, include python scoreboard.py in a bash code block. Keep the explanation concise and write for someone opening the project for the first time.

Return only the finished Markdown.
  • Read the README against the generated scoreboard.
  • Correct any unsupported claim before saving it.
  • Save the reviewed draft as README.md beside scoreboard.py.
  • Confirm both files exist by running:
ls *.md

You should see after-action.md, README.md, and TASKS.md in the output.

Don't see all three files?

  • Make sure you saved both files in the project root folder, not inside docs/.
  • Check the file extensions. Your editor may have added .txt instead of .md.
  • Help me find my files.
Close the folder

The folder is not done until every TASKS.md box is ticked, the teach-back is delivered, the working tree is clean, and the last commit is tagged.

  • Open TASKS.md and tick every remaining box.
  • Save TASKS.md.

Now deliver the teach-back. Find one person who has never sat in a delivery review.

  • Explain the five metrics in 60 seconds, using your scoreboard as a visual aid.
  • If they claim the DORA performance tiers were "retired," correct the claim against the dated anchor. No primary source says the tiers were retired. The cluster count is emergent from each year's data.
  • Defend one of your four decision records when they ask "why that and not the alternative?"

No one around?

Explain it out loud to yourself or record a 60-second voice note. The point is to articulate the five numbers and their limits in plain language, not to have an audience.

  • Commit everything by running these commands:
git add -A
git commit -m "Close folder: readout, after-action, README"
  • Tag the commit locally by running:
git tag v1.0.0

Before you verify, predict what git status and git tag will show. A clean working tree? A tag named v1.0.0?

  • Verify a clean working tree by running:
git status
  • Verify the tag by running:
git tag

git status shows "nothing to commit, working tree clean." git tag shows v1.0.0.

Seeing uncommitted changes?

  • Check that you saved all files (especially TASKS.md) before running git add -A.
  • If you created new files after the commit, run git add -A and git commit --amend --no-edit to fold them into the last commit.
  • Help me get a clean working tree.

Secret mission

Audit Real Repository Evidence

Audit a repository you own without replacing the teaching corpus. You will inspect what Git can prove, identify the delivery evidence it cannot provide, and document the integrations a production scoreboard would need.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • Your project folder on the Desktop (contains scoreboard.py, metrics.sql, scoreboard.html, the data/ and docs/ directories, and all project files).
  • DuckDB Python package (installed via pip).

Keep everything running

No action needed. Choose this if you are still actively building or exploring the scoreboard.

  • Your project folder and all files remain on your Desktop, ready to open any time.
  • DuckDB stays installed and available for other Python projects.
  • Your Git history is preserved locally. You can revisit commits, re-run the collector, or regenerate the scoreboard whenever you like.

Pause - I'll come back to this later

Close any open editors and terminals. Your files and DuckDB installation stay intact for when you return.

  • Close any terminal windows open in the project folder.
  • Close the project files in your text editor.
  • Everything is saved on disk. When you come back, open the project folder in your editor and pick up where you left off.

Delete - I don't want to use this again

Remove all project files and the DuckDB package. Choose this if you do not plan to use this project again.

  • Open your Desktop in Finder (macOS) or File Explorer (Windows).
  • Find the project folder you created at the start of the project.
  • Right-click the folder and select Move to Trash (macOS) or Delete (Windows).
  • Empty your Trash or Recycle Bin to free the disk space.
  • Uninstall the DuckDB Python package by running:
pip uninstall duckdb
  • Type y and press Enter when prompted to confirm.

Nice Work!

Nice Work!

You just built a delivery scoreboard that turns "we ship faster than last year" into five auditable numbers. Each one is defined in SQL a reviewer can read, gated where the data is uncertain, and annotated with what it cannot prove.

What you learned:

  • Built a five-metric DORA-aligned delivery scoreboard from a captured 30-day deployment history, with every metric defined in a single metrics.sql file carrying a comment naming what it counts and what it excludes, rendered as a self-contained inline-SVG page that opens offline in any browser.
  • Wrote four MADR v4 decision records, a requirements brief, a component view, and a build plan before any code ran. Each record named the alternative it beat, the tradeoff it accepted, and the trigger that would reverse it.
  • Ran an AI-gated classification for deployment rework rate with a dual-test acceptance based on accuracy and count. You proved the numbers with three assertions and annotated every metric card with a cannot-prove sentence specific to that metric.
  • Secret Mission: Audited a real repository's Git history without modifying the teaching corpus. You documented the evidence gaps and the production integrations needed to close them.

Ready to quiz yourself?