Mert

Model Showdown Round 5
ended with a leaderboard. Sonnet 4.6 won on the rubric. Opus 4.7 placed
second. Qwen 3.5 contributed almost nothing structural. That’s the
measurement story.

This is the methodology story — what happened after the scores were
revealed.

The Problem With Picking a Winner

The naive workflow after a bakeoff is: pick the best run, merge it to
main, ship it. Winner takes all.

That’s wrong, and Round 5 made it obvious why.

The winning run (Sonnet 4.6) had the best overall rubric score. It also
had a weaker path validator than Opus 4.7, and its orphan-matching logic
would have missed real-world cases that Opus 4.6 caught. The second-place
run (Opus 4.7) had the best validator and the cleanest route structure, but
the worst data source choice — reading from the build-time filesystem
instead of the live GitHub Contents API.

No individual run was what I’d ship. Each one had at least one bad call.
The bakeoff’s real output wasn’t a winner. It was a map.

When 4 of 4 models made the same design choice, that choice was obviously
right. When they diverged — on validation strictness, on data source, on
UX for destructive actions — that divergence was the signal. Those were the
actual design decisions, the ones worth spending judgment on.

The Three Passes

What emerged from Round 5 is a pattern I’ve now run twice and would reach
for again on any feature where the design space is unclear:

Pass 1 — Bakeoff. Run N models (I used 4) on the same prompt in
isolated sessions. Judge blind, before you know which branch is which.
Score against a rubric. The output of this pass isn’t any of the N
implementations — it’s the decision map. You now know which choices are
contested and which are obvious.

Pass 2 — Merge. Write down a merge plan before touching any code: for
each contested layer, which run’s approach wins and why. Then ask an agent
to compose the merged best-of from those inputs. The merge is strictly
better than any individual bakeoff run because it draws on information none
of the bakeoff contestants had — the scored comparison of all four.

For Round 5 the plan looked like this:

Qwen 3.5 contributed nothing structural to this table. The bakeoff said
“skip this one” clearly enough that there was nothing to debate. That’s
useful information too — knowing which pieces to skip is part of the map.

The merge was 13 files changed, +990/-9. One TypeScript error caught and
fixed. Build passed first try after that. Opened as a PR with the heritage
table in the description so future reviewers can trace any decision back to
its source run.

Pass 3 — Polish. The merged feature went live. I opened it against
real production data and spotted four things immediately: truncated
directory names with no tooltip, delete buttons invisible on touch devices,
no bulk delete UI despite the API supporting paths: [], and an orphaned
section header that would show with count 0 after the lone orphan was
deleted.

None of those were predictable before live use. You can’t predict friction
from a code review — you observe it. The polish pass had to come after the
merge because the artifact it was polishing didn’t exist until then.

The polish was 6 files changed, +265/-54 and about 20 minutes of agent
time.

When to Use It

The pattern has a real cost: the bakeoff is N full agent sessions, each
producing a complete implementation that you won’t ship. For Round 5 that
was ~$35 in inference and a few hours of judging.

That’s cheap insurance when the feature has any of these properties:

• Destructive verbs. Delete, update, payment, permission change. The
  cost of getting validation wrong outweighs the cost of the bakeoff.
• Multiple defensible architectures. Where should validation live?
  What’s the data source? How does auth thread through? When you genuinely
  don’t know the right answer, a bakeoff shows you the option space.
• Hard to change later. Database schemas. Public API contracts. Anything
  that will accumulate callers.

It’s overkill for a 20-line UI tweak or a feature with a single obvious
implementation. The signal value of the bakeoff scales with how uncertain
you are about the design.

What I’d Do Differently

Three things I’d change for the next run:

Name the contestant chats before pasting the prompt. All four Round 5
chats showed up as “New Chat” in the Coder API cost summary, which meant
20 minutes of token-volume detective work to figure out which cost belonged
to which run. Five seconds of effort would have prevented that.

Capture per-phase stats. I have clean bakeoff numbers. I don’t have
separate merge or polish numbers — they’re folded into the judging thread.
A lightweight wrapper script around each phase would make the next
iteration measurable end-to-end.

Write the polish friction items down before fixing them. I noticed four
issues and fixed them in one pass, which collapsed the “observed” list and
the “fixed” list into the same moment. Separating them — even by five
minutes — would have made the “what does live-review surface” lesson
sharper for the writeup. And occasionally you’ll notice something that
isn’t worth fixing.

By the Numbers

• 3 phases: Bakeoff (4 parallel attempts), Merge (1 informed pass), Polish (1 live-review pass)
• 4 implementations produced in the bakeoff, 0 shipped to main as-is
• 3 of 4 bakeoff runs contributed at least one structural piece to the merge
• 13 files changed in the merge pass (+990/-9)
• 6 files changed in the polish pass (+265/-54)
• 4 friction items caught in polish that couldn’t have been predicted before live use
• ~$35.56 inference cost for the bakeoff phase
• ~45 min bakeoff (parallel), ~30 min merge, ~20 min polish

金融数据可视化中的 K 线图（Candlestick Chart）是前端开发中一个典型难点——既要高效渲染大量历史数据，又要支持实时推送，还要保证缩放、拖拽足够流畅。市面上很多图表库能画折线图、柱状图，但真正为金融场景优化的并不多。

本文将带你10 分钟内跑通一个可交互的贵金属 K 线看板，并给出接入真实行情 API。读完你将掌握：

• 最适合 K 线图的前端库选型对比
• 用 Lightweight Charts 快速渲染第一根 K 线
• 通过 HTTP 拉取历史数据 + WebSocket 接收实时更新
• 成交量副图、安全代理等工程要点

一、选型对比：哪个图表库更适合 K 线？

选型建议：

• 追求极轻量 + 金融专业性 → Lightweight Charts（本文使用）
• 需要丰富图表类型 + 中文文档 → ECharts
• 海量数据 + 极致渲染性能 → KLineChart

二、Lightweight Charts 极速上手（模拟数据版）

2.1 安装

npm install lightweight-charts

或直接使用 CDN：

<script src="https://unpkg.com/lightweight-charts/dist/lightweight-charts.standalone.production.js"></script>

2.2 最简代码：5 根 K 线

<div id="chart" style="width: 800px; height: 500px;"></div>

<script>
  const { createChart, CandlestickSeries } = LightweightCharts;

  const chart = createChart(document.getElementById("chart"), {
    width: 800,
    height: 500,
    layout: { backgroundColor: "#ffffff", textColor: "#333" },
  });

  const candlestickSeries = chart.addSeries(CandlestickSeries, {
    upColor: "#26a69a", // 阳线（涨）
    downColor: "#ef5350", // 阴线（跌）
    borderVisible: false,
  });

  candlestickSeries.setData([
    { time: "2024-01-01", open: 2040, high: 2060, low: 2020, close: 2055 },
    { time: "2024-01-02", open: 2055, high: 2080, low: 2045, close: 2070 },
    { time: "2024-01-03", open: 2070, high: 2090, low: 2060, close: 2085 },
    { time: "2024-01-04", open: 2085, high: 2100, low: 2075, close: 2080 },
    { time: "2024-01-05", open: 2080, high: 2095, low: 2065, close: 2075 },
  ]);
</script>

打开页面即可看到带十字光标、可缩放拖拽的 K 线图。

三、完整可运行示例（模拟实时推送）

保存为 index.html 打开，即可看到一个黄金 1 分钟 K 线图，并支持“添加一根新 K 线”来模拟实时推送：

<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="UTF-8" />
    <title>黄金 K 线图 · 模拟实时行情</title>
    <style>
      * {
        margin: 0;
        padding: 0;
        box-sizing: border-box;
      }
      body {
        background: #f5f7fa;
        display: flex;
        justify-content: center;
        align-items: center;
        min-height: 100vh;
        padding: 20px;
      }
      .card {
        background: white;
        border-radius: 16px;
        box-shadow: 0 8px 24px rgba(0, 0, 0, 0.08);
        padding: 20px;
        width: 100%;
        max-width: 1100px;
      }
      .header {
        display: flex;
        justify-content: space-between;
        margin-bottom: 16px;
        align-items: baseline;
      }
      h1 {
        font-size: 1.5rem;
      }
      .price-info {
        font-size: 1.25rem;
        font-weight: 600;
        color: #26a69a;
      }
      .chart-container {
        width: 100%;
        height: 500px;
      }
      .controls {
        margin-top: 16px;
        display: flex;
        gap: 12px;
        justify-content: flex-end;
      }
      button {
        background: #f1f5f9;
        border: none;
        padding: 8px 16px;
        border-radius: 8px;
        cursor: pointer;
      }
      .update-badge {
        font-size: 0.75rem;
        color: #94a3b8;
      }
    </style>
    <script src="https://unpkg.com/lightweight-charts/dist/lightweight-charts.standalone.production.js"></script>
  </head>
  <body>
    <div class="card">
      <div class="header">
        <div>
          <h1>黄金 1 分钟 K 线图</h1>
          <div class="update-badge" id="update-time">模拟数据演示</div>
        </div>
        <div class="price-info">
          最新价: <span id="latest-price">0.00</span>
        </div>
      </div>
      <div id="kline-chart" class="chart-container"></div>
      <div class="controls">
        <button id="reset-view">重置视图</button>
        <button id="add-data">添加 1 根模拟 K 线</button>
      </div>
    </div>

    <script>
      (function () {
        // 生成 30 根模拟 K 线
        const generateMockData = () => {
          let basePrice = 2040;
          const data = [];
          const now = new Date();
          for (let i = 30; i >= 1; i--) {
            const minute = new Date(now.getTime() - i * 60 * 1000);
            const timeStr = minute.toISOString().slice(0, 19).replace("T", " ");
            const variation = (Math.random() - 0.5) * 4;
            const open = basePrice;
            const close = +(open + variation).toFixed(2);
            const high = Math.max(open, close) + Math.random() * 2;
            const low = Math.min(open, close) - Math.random() * 2;
            data.push({
              time: timeStr,
              open: +open.toFixed(2),
              high: +high.toFixed(2),
              low: +low.toFixed(2),
              close,
            });
            basePrice = close;
          }
          return data;
        };

        let mockData = generateMockData();
        const chart = LightweightCharts.createChart(
          document.getElementById("kline-chart"),
          {
            width: document.getElementById("kline-chart").clientWidth,
            height: 500,
            layout: { backgroundColor: "#ffffff", textColor: "#1e293b" },
            grid: {
              vertLines: { color: "#e2e8f0" },
              horzLines: { color: "#e2e8f0" },
            },
            timeScale: { timeVisible: true, secondsVisible: false },
          }
        );
        const candlestickSeries = chart.addSeries(
          LightweightCharts.CandlestickSeries,
          {
            upColor: "#26a69a",
            downColor: "#ef5350",
            borderVisible: false,
          }
        );
        candlestickSeries.setData(mockData);
        chart.timeScale().fitContent();

        const updateLatestPrice = () => {
          if (mockData.length) {
            const latest = mockData[mockData.length - 1];
            document.getElementById("latest-price").innerText =
              latest.close.toFixed(2);
            document.getElementById(
              "update-time"
            ).innerHTML = `最后更新: ${latest.time}`;
          }
        };
        updateLatestPrice();

        const addNewCandle = () => {
          const last = mockData[mockData.length - 1];
          const now = new Date();
          const timeStr = now.toISOString().slice(0, 19).replace("T", " ");
          const variation = (Math.random() - 0.5) * 3;
          const open = last.close;
          const close = +(open + variation).toFixed(2);
          const high = Math.max(open, close) + Math.random() * 2;
          const low = Math.min(open, close) - Math.random() * 2;
          const newCandle = {
            time: timeStr,
            open: +open.toFixed(2),
            high: +high.toFixed(2),
            low: +low.toFixed(2),
            close,
          };
          mockData.push(newCandle);
          candlestickSeries.update(newCandle);
          updateLatestPrice();
          chart.timeScale().scrollToRealTime();
        };

        document
          .getElementById("add-data")
          .addEventListener("click", addNewCandle);
        document
          .getElementById("reset-view")
          .addEventListener("click", () => chart.timeScale().fitContent());
        window.addEventListener("resize", () =>
          chart.applyOptions({
            width: document.getElementById("kline-chart").clientWidth,
          })
        );
      })();
    </script>
  </body>
</html>

四、接入真实行情 API

真实场景中需要对接行情数据源。下面以支持 REST + WebSocket 的行情 iTick API 为例，展示接入方法。你可以替换成任何提供类似接口的服务（如自己的后端、第三方数据商等）。

4.1 历史 K 线：REST API 拉取

iTick 的行情 API 提供了期货历史 K 线接口：

GET https://api.itick.org/future/kline?symbol=GC&region=US&kType=1&limit=100

返回格式示例：

{
  "code": 0,
  "data": [
    {
      "t": 1704067200000,
      "o": 2040.5,
      "h": 2060.2,
      "l": 2020.8,
      "c": 2055.3,
      "v": 12500
    }
  ]
}

前端调用时，建议通过自己的后端代理，避免暴露 API 密钥。后端示例（Node.js + Express）：

app.get("/api/kline", async (req, res) => {
  const { symbol, region, kType, limit } = req.query;
  const response = await fetch(
    `https://api.itick.org/future/kline?symbol=${symbol}&region=${region}&kType=${kType}&limit=${limit}`,
    { headers: { token: process.env.API_KEY } }
  );
  const data = await response.json();
  res.json(data);
});

前端调用并转换为 Lightweight Charts 所需格式：

fetch("/api/kline?symbol=GC&region=US&kType=1&limit=100")
  .then((res) => res.json())
  .then((data) => {
    if (data.code === 0 && data.data) {
      const klineData = data.data.map((item) => ({
        time: Math.floor(item.t / 1000), // 毫秒时间戳 → 秒
        open: item.o,
        high: item.h,
        low: item.l,
        close: item.c,
      }));
      candlestickSeries.setData(klineData);
      chart.timeScale().fitContent();
    }
  });

注意：不同的 API 返回的时间戳单位可能不同（毫秒/秒），需要根据实际情况转换。

4.2 实时推送：WebSocket 订阅 K 线更新

同样以后端代理 WebSocket 为例，或者直接在前端连接行情网关（需确保 API Key 不暴露）。以下直接展示前端连接某行情 WebSocket 的示例（假设该服务允许前端直接使用临时 token）：

let ws = null;

function connectWebSocket() {
  ws = new WebSocket("wss://api.itick.org/future");

  ws.onopen = () => {
    // 发送认证（具体格式依 API 而定）
    ws.send(JSON.stringify({ action: "auth", token: "your_temp_token" }));
  };

  ws.onmessage = (event) => {
    const msg = JSON.parse(event.data);
    if (msg.code === 1 && msg.msg === "Connected Successfully") {
      console.log("连接成功");
    }

    // 认证成功后订阅 K 线频道
    if (msg.code === 1 && msg.resAc === "auth") {
      ws.send(
        JSON.stringify({
          ac: "subscribe",
          params: "GC$US,SI$US", // 格式为：{symbol}${region}
          types: "kline@1", // 可选：depth, quote, tick, kline@1
        })
      );
    }

    // 处理 K 线推送
    if (msg.type === "kline@1") {
      const candle = msg.data;
      candlestickSeries.update({
        time: Math.floor(candle.t / 1000),
        open: candle.o,
        high: candle.h,
        low: candle.l,
        close: candle.c,
      });
    }
  };

  ws.onclose = () => setTimeout(() => connectWebSocket(), 3000);
  ws.onerror = (err) => console.error("WebSocket error", err);
}

connectWebSocket();

安全提醒：任何 API 密钥、token 都不应直接写在前端代码中。上述示例仅为演示逻辑，实际生产环境应通过后端代理或短期令牌方式保护敏感信息。

五、进阶功能：成交量副图与性能优化

5.1 添加成交量柱状图

Lightweight Charts 支持多系列叠加，可轻松添加成交量副图：

const volumeSeries = chart.addSeries(LightweightCharts.HistogramSeries, {
  color: "#26a69a",
  priceFormat: { type: "volume" },
  priceScaleId: "", // 独立右侧轴
});

// 假设从 API 获取的数据中包含成交量 v
volumeSeries.setData(
  apiData.map((item) => ({
    time: Math.floor(item.t / 1000),
    value: item.v,
    color: item.close >= item.open ? "#26a69a" : "#ef5350",
  }))
);

5.2 技术指标（MACD / RSI）

Lightweight Charts 本身不提供指标计算，可以：

• 使用 ta.js 或 tulind 在前端计算。
• 将计算结果作为 LineSeries 叠加到图表。

const macdLine = chart.addSeries(LightweightCharts.LineSeries, {
  color: "#FF9800",
});
macdLine.setData(macdValues);

若需要完整的技术指标内置支持，可考虑使用 react-stockcharts 或 KLineChart。

5.3 性能优化建议

• 数据更新：大量历史数据使用 setData，单根实时 K 线使用 update。
• 视口管理：不需要一次性渲染几十万根 K 线，利用库自带的数据采样。
• 窗口 resize：绑定 resize 事件并调用 chart.applyOptions({ width })。

六、总结

本文介绍了如何用 Lightweight Charts 在 10 分钟内搭建一个贵金属 K 线图前端看板，并给出了接入真实行情数据的通用方法（REST 拉取历史 + WebSocket 实时更新）。关键技术点包括：

• 对比主流 K 线图表库的优缺点，根据场景选型。
• Lightweight Charts 核心用法：createChart、CandlestickSeries、setData / update。
• 模拟数据快速原型，以及如何替换为真实 API。
• 成交量副图、指标叠加等扩展思路。
• 安全警示：API 密钥绝不能写在前端，必须后端代理。

参考文档：https://docs.itick.org/websocket/future
GitHub：https://github.com/itick-org/

When people start learning Rust, they usually run into the same obstacle: the borrow checker.

I’ve been there, and everyone has. But the truth is this: The borrow checker isn’t against you; it simply translates what you meant into what the computer needs. Once you understand that, Rust’s borrow checker makes a lot more sense.

Let me show you the moment this made sense for me.

The Classic Problem

Here’s a tiny piece of code that looks harmless:

let mut name = String::from("Alice");
let r = &name;

name.push('!'); // this is an error.
println!("{}", r);

Rust says no. And at first, it’s frustrating; it seems like the compiler is being pedantic. But there’s a method to this.

Rust’s Two Rules

Rust has two simple rules:

1. You can have one mutable reference

2. OR any number of immutable references

The catch: You can never have both at the same time.

Why? Because if something is being changed, Rust doesn’t want other parts of your code reading a half‑changed value. And that’s all there is to it.

The “Two Doors” Analogy

Fixing the Code the Right Way

Here’s our earlier example, but fixed:

let mut name = String::from("Alice");

name.push('!');  // do the mutation first

let r = &name;   // then borrow immutably
println!("{}", r);

Or, if you really need both at once, you can clone. Cloning is not a crime, especially for beginners:

let mut name = String::from("Alice");
let r = name.clone();

name.push('!');
println!("original: {}", name);
println!("copy: {}", r);

A Trick That Helps Every Beginner

If you’re just starting out, follow this habit: If you’re mutating something, finish all your mutations before you borrow it. This one habit eliminates most borrow checker errors you’ll see.

Why Rust Does This (And Why It’s a Good Thing)

Languages like Python, Java, and JavaScript let you mutate data while other parts of your code are reading it. Most of the time, it’s totally harmless. But sometimes, it causes:

• sneaky race conditions (which usually only occur during production)

• strange bugs

• inconsistent state

• security issues

• data corruption

Rust prevents all of that, because it’s designed to.

If You’re Learning Rust, Here’s My Advice

Don’t fight the borrow checker. Instead:

• Borrow when you only need to read

• Mutate before you borrow

• Clone when you need independence

• Trust the compiler’s hints

• Keep your functions small

• Return owned data when in doubt

Rust rewards those who take the time to understand the mechanics. The borrow checker is just the first step in that shift in perspective.

Want More Beginner‑Friendly Rust Posts?

I’m working on a series covering:

• ownership

• borrowing

• lifetimes

• slices

• structs

• enums

• pattern matching

• error handling

• async basics

If you want me to cover something specific, or if there’s a specific concept that hasn’t clicked yet, drop it in the comments! I’ll turn the most requested topic into another post.

Time to upgrade your extension to Umbraco 18?
A friendly heads up, the OpenAPI generation got an overhaul.

Microsoft.AspNetCore.OpenApi is Umbraco’s new library of choice for OpenAPI document generation, replacing Swashbuckle.AspNetCore. It ships with ASP.NET Core and is maintained by Microsoft alongside the framework, which means one fewer third-party dependency to worry about.

The trade-off: if your extension wired up its own OpenAPI document on v17, it won’t compile against v18. The Swashbuckle types it relied on are gone.

Two paths forward:

1. Migrate to Microsoft OpenAPI. Two options:
   • Use the AddBackOfficeOpenApiDocument extension method: Umbraco’s new helper. What the v18 template uses. Covers most extensions.
   • Use Microsoft’s AddOpenApi method directly: call Microsoft.AspNetCore.OpenApi yourself. For full control.
2. Keep using Swashbuckle (not recommended). Install Swashbuckle.AspNetCore in your own extension, wire it up, then plug your document into Umbraco’s Swagger UI via AddOpenApiDocumentToUi(...). When rewriting isn’t an option right now.

ℹ️ Note: the OpenAPI spec URL also changed from /umbraco/swagger/{documentName}/swagger.json to /umbraco/openapi/{documentName}.json. If you generate a client from the spec (e.g. via the v17 extension template’s package.json generate-client script), update the URL before regenerating.

Use the AddBackOfficeOpenApiDocument extension method

We added AddBackOfficeOpenApiDocument() in v18 to take the boilerplate out of registering an OpenAPI document. It’s an extension method on IUmbracoBuilder that wires up Umbraco’s sensible defaults so you don’t have to.

Out of the box, the helper registers your document, scopes it to endpoints decorated with [MapToApi(documentName)], applies Umbraco’s naming conventions for schemas and operation IDs, and adds the document to the Swagger UI dropdown at /umbraco/openapi/.

⚠️ Watch out: the first time you call AddBackOfficeOpenApiDocument (or AddOpenApi directly) and build, you’ll hit error CS9137: The 'interceptors' feature is not enabled in this namespace. The Microsoft.AspNetCore.OpenApi source generator (which surfaces your XML doc comments as descriptions in the OpenAPI document, among other compile-time enrichments) propagates through Umbraco’s project reference, but the property that enables it ships only with the Web SDK. Class-library projects don’t get it automatically, so add it to your .csproj:

<PropertyGroup>
  <InterceptorsNamespaces>$(InterceptorsNamespaces);Microsoft.AspNetCore.OpenApi.Generated</InterceptorsNamespaces>
</PropertyGroup>

Minimal usage

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddBackOfficeOpenApiDocument("my-api");
    }
}

This will add and configure a “my-api” OpenAPI document that will display the controllers/endpoints that have the attribute [MapToApi("my-api")].

Typical usage

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddBackOfficeOpenApiDocument("my-api", document => document
            .WithTitle("My API")
            .WithBackOfficeAuthentication());
    }
}

Besides adding a “my-api” OpenAPI document, this will set the document title to “My API” and declare that the document’s operations require backoffice authentication (which is what enables the “Authorize” flow in the Swagger UI).

Builder method reference

Aligning schema serialization

Schema generation needs to use the same JsonOptions your API uses at runtime, otherwise the generated SDKs and the actual payloads will not match. By default, Microsoft.AspNetCore.OpenApi generates schemas using the global HTTP JsonOptions, which means whoever hosts your extension can change them and that affects your schema.

For most backoffice extensions you want the BackOffice named JsonOptions (the same ones Umbraco’s Management API uses). Point the document at them with WithJsonOptions:

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddBackOfficeOpenApiDocument("my-api", document => document
            .WithJsonOptions(Constants.JsonOptionsNames.BackOffice));
    }
}

For this to be meaningful at runtime, your controllers also need to serialize with those same options. The v17 extension template inherits from a plain ControllerBase, which uses the default options instead. Fix that by adding [JsonOptionsName(Constants.JsonOptionsNames.BackOffice)] to your own base controller or to each controller directly.

Without it, the schema reflects backoffice serialization but the actual responses are serialized with the default options, and you’re back to the schema-vs-runtime mismatch.

Going beyond the defaults

The helper is not a closed box. Anything you’d normally do on OpenApiOptions (custom operation/schema/document transformers, your own CreateSchemaReferenceId, a replacement operation ID transformer, etc.) is still available through ConfigureOpenApiOptions. It just runs after Umbraco’s defaults, so you compose on top of them rather than starting from scratch.

For example, registering your own schema transformer:

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddBackOfficeOpenApiDocument("my-api", document => document
            .WithTitle("My API")
            .ConfigureOpenApiOptions(options =>
                options.AddSchemaTransformer<MyExtensionSchemaTransformer>()));
    }
}

Migrating an existing composer

If your extension followed the shape of the v17 dotnet template, the migration is: delete the old composer body and write the AddBackOfficeOpenApiDocument(...) call. Below is the same extension’s composer before and after.

v17 (Swashbuckle)

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.Services.AddSingleton<IOperationIdHandler, CustomOperationHandler>();

        builder.Services.Configure<SwaggerGenOptions>(opt =>
        {
            opt.SwaggerDoc(Constants.ApiName, new OpenApiInfo
            {
                Title = "My Test Extension Backoffice API",
            });

            opt.OperationFilter<UmbracoExtensionOperationSecurityFilter>();
        });
    }

    public class UmbracoExtensionOperationSecurityFilter : BackOfficeSecurityRequirementsOperationFilterBase
    {
        protected override string ApiName => Constants.ApiName;
    }

    public class CustomOperationHandler : OperationIdHandler
    {
        // ...
    }
}

v18 (Microsoft OpenAPI + new builder)

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder) =>
        builder.AddBackOfficeOpenApiDocument(
            Constants.ApiName,
            document => document
                .WithTitle("My Test Extension Backoffice API")
                .WithBackOfficeAuthentication());
}

The helper registers UmbracoOperationIdTransformer by default, so the old IOperationIdHandler is no longer needed. If you want custom operation ID logic, register your own through ConfigureOpenApiOptions:

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddBackOfficeOpenApiDocument("my-api", document => document
            .ConfigureOpenApiOptions(options =>
                options.AddOperationTransformer((operation, context, _) =>
                {
                    operation.OperationId = $"{context.Description.ActionDescriptor.RouteValues["action"]}";
                    return Task.CompletedTask;
                })));
    }
}

Use Microsoft’s AddOpenApi method directly

If you want to register a document that isn’t a backoffice document, or you want to start from a clean slate without Umbraco’s defaults, call AddOpenApi yourself. The helper is just a thin wrapper around that method. Umbraco already wires the OpenAPI routing and Swagger UI hosting, so you don’t need to call MapOpenApi or configure endpoints. You only need to register your documents.

For the transformer model itself (how operation/schema/document transformers work), see Microsoft’s Customize OpenAPI documents docs.

For Umbraco’s specifics ([MapToApi] filtering via options.ShouldInclude, schema/operation ID conventions, the public UmbracoSchemaIdGenerator.Generate(type) helper), see the API versioning and OpenAPI docs.

A few things worth remembering:

• Backoffice authentication: call options.AddBackofficeSecurityRequirements() (from Umbraco.Cms.Api.Management.OpenApi) instead of subclassing BackOfficeSecurityRequirementsOperationFilterBase.
• Swagger UI registration: documents are no longer auto-listed. Call services.AddOpenApiDocumentToUi(documentName, uiTitle) to surface yours in the dropdown.
• InterceptorsNamespaces opt-in: same as the helper path. See the Watch out callout at the top of the helper section.

Keep using Swashbuckle

Not recommended. Consider this only if rewriting a complex Swashbuckle setup isn’t feasible right now. Trade-offs:

• Extra dependency on Swashbuckle.AspNetCore.
• OpenAPI 3.0 output, whereas the rest of Umbraco’s documents emit 3.1.
• Extra wiring you maintain yourself.
• Both libraries depend on Microsoft.OpenApi. A breaking change there that Swashbuckle doesn’t follow leaves your extension incompatible with the rest of the stack.

If you still want to go this route, see the Swashbuckle.AspNetCore README for general setup. The rest of this section is the Umbraco-specific wiring.

1. Install the NuGet package

Add a PackageReference to Swashbuckle.AspNetCore in your extension’s .csproj (or dotnet add package Swashbuckle.AspNetCore).

2. Register Swashbuckle and your document in a composer

In v17, Umbraco called AddSwaggerGen for you and your extension only configured SwaggerGenOptions. In v18 you have to call it yourself:

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.Services.AddSwaggerGen(opt =>
        {
            opt.SwaggerDoc(Constants.ApiName, new OpenApiInfo
            {
                Title = "My Test Extension Backoffice API",
                Version = "1.0",
            });

            var previousDocInclusion = opt.SwaggerGeneratorOptions.DocInclusionPredicate;
            opt.DocInclusionPredicate((docName, apiDesc) =>
                docName == Constants.ApiName
                    ? apiDesc.ActionDescriptor.HasMapToApiAttribute(docName)
                    : previousDocInclusion?.Invoke(docName, apiDesc) ?? true);

            // any operation filters, schema filters, or other v17 setup you had...
        });
    }
}

3. Host the Swagger JSON middleware via an IUmbracoPipelineFilter

Umbraco no longer calls app.UseSwagger(), so the middleware that serves the Swagger JSON isn’t running. Add it through Umbraco’s pipeline filter, and set the RouteTemplate to match UmbracoOpenApiOptions.RouteTemplate (default umbraco/openapi/{documentName}.json) so the dropdown link resolves.

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.Services.Configure<UmbracoPipelineOptions>(options =>
        {
            options.AddFilter(new UmbracoPipelineFilter("Swashbuckle")
            {
                PostPipeline = app =>
                {
                    var openApiOptions = app.ApplicationServices.GetRequiredService<IOptions<UmbracoOpenApiOptions>>().Value;
                    if (!openApiOptions.Enabled)
                    {
                        return;
                    }

                    var routeTemplate = openApiOptions.RouteTemplate;
                    app.UseWhen(
                        ctx => ctx.Request.Path == routeTemplate.Replace("{documentName}", Constants.ApiName).EnsureStartsWith('/'),
                        branch => branch.UseSwagger(c => c.RouteTemplate = routeTemplate));
                },
            });
        });
    }
}

Scope the middleware to your own document’s URL. Otherwise Swashbuckle intercepts requests for Umbraco’s own documents (Management, Delivery) and returns 404 because they aren’t registered with AddSwaggerGen.

4. Register the document in the Swagger UI dropdown

Register the document in Umbraco’s Swagger UI dropdown:

public class UmbracoExtensionApiComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.Services.AddOpenApiDocumentToUi(Constants.ApiName, "My API");
    }
}

TL;DR

The helper is the simplest option for most extensions. AddBackOfficeOpenApiDocument() is what the v18 template uses, gives you Umbraco’s sensible defaults out of the box (naming conventions, Swagger UI registration, JsonOptions alignment), and lets you register an OpenAPI document in a single call.

Calling AddOpenApi yourself is the standard ASP.NET Core approach and works fine. It means more wiring and code to maintain, so reach for it when you need full control (non-backoffice documents, or skipping the Umbraco defaults).

If rewriting really isn’t an option right now, keeping Swashbuckle as your own dependency and plugging into the dropdown via AddOpenApiDocumentToUi(...) will get you through, but plan to revisit when you can.

Further reading

• Announcements #32: the official announcement, with the full set of changes and reasoning
• Custom Backoffice API: end-to-end backoffice API setup
• API versioning and OpenAPI: schema IDs, operation IDs, route configuration, endpoint filtering
• Umbraco 18 version-specific upgrade docs: official migration reference

We’ve all been there. You open a PR, and the SQL query looks like someone sneezed on the keyboard:

select u.id,u.name,u.email,o.total,o.created_at from users u join orders o on u.id=o.user_id where o.status='completed' and o.created_at >= '2024-01-01' order by o.created_at desc limit 50

It works. It returns the right data. But it’s a nightmare to review, debug, or extend.

SQL readability is not a cosmetic concern — it’s an engineering discipline. Messy SQL leads to logic errors, missed optimizations, and silent bugs that survive code review because nobody wants to untangle the mess.

This guide walks through the conventions, habits, and formatting rules that make SQL a joy to work with.

Why SQL Formatting Matters More Than You Think

Unlike application code, SQL queries often live in multiple places simultaneously — inside ORM raw query methods, migration files, stored procedures, BI tools, and debug logs. A single ugly query might be read by:

• A backend engineer debugging a slow endpoint
• A DBA optimizing an index
• A data analyst trying to understand business logic
• You, six months from now, at 11pm before a deploy

Consistent formatting makes all of these scenarios less painful.

The Core Rules

1. Capitalize SQL keywords

SQL keywords should be uppercase. Column names, table names, and aliases should be lowercase (or match your schema’s convention).

Bad:

select id, name from users where active = true

Good:

SELECT id, name
FROM users
WHERE active = true

This instantly distinguishes SQL structure from your data model.

2. One clause per line

Every major clause — SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT — gets its own line. No exceptions.

Bad:

SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.name HAVING COUNT(o.id) > 5 ORDER BY order_count DESC

Good:

SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY
    u.id,
    u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC

The second version can be scanned in seconds. The first requires active decoding.

3. Align your columns in the SELECT list

Put each selected column on its own line, indented consistently (2 or 4 spaces — pick one and stick to it):

SELECT
    account_id,
    account_name,
    region,
    plan_type,
    created_at
FROM accounts
WHERE status = 'active'

This makes it trivial to add, remove, or reorder columns during development.

4. Qualify every column name in joins

Once you have more than one table in a query, always prefix columns with their table alias. Never leave a reader guessing which table id or created_at belongs to.

Ambiguous:

SELECT id, name, total, created_at
FROM users
JOIN orders ON users.id = orders.user_id

Clear:

SELECT
    u.id,
    u.name,
    o.total,
    o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id

This also protects you when schema changes add a new column with a conflicting name — your query won’t silently start returning the wrong data.

5. Write meaningful aliases

Single-letter aliases like a, b, x are lazy and harmful. Use short but meaningful abbreviations.

-- Bad
SELECT a.name, b.total
FROM accounts a
JOIN invoices b ON a.id = b.account_id

-- Good
SELECT acc.name, inv.total
FROM accounts acc
JOIN invoices inv ON acc.id = inv.account_id

6. Put JOIN conditions on a separate, indented line (for complex joins)

When join conditions are multi-part or involve expressions, break them out clearly:

SELECT
    p.id,
    p.title,
    c.body AS latest_comment
FROM posts p
LEFT JOIN comments c
    ON c.post_id = p.id
    AND c.created_at = (
        SELECT MAX(created_at)
        FROM comments
        WHERE post_id = p.id
    )
WHERE p.published = true

7. Use CTEs to break up complexity

Common Table Expressions (CTEs) are one of the most underused tools in SQL. Instead of nesting subqueries three levels deep, break the logic into named, readable steps.

Subquery hell:

SELECT avg_revenue, cohort
FROM (
    SELECT cohort, AVG(revenue) AS avg_revenue
    FROM (
        SELECT
            DATE_TRUNC('month', first_purchase_date) AS cohort,
            SUM(order_total) AS revenue
        FROM (
            SELECT u.id, MIN(o.created_at) AS first_purchase_date, o.total AS order_total
            FROM users u
            JOIN orders o ON u.id = o.user_id
            GROUP BY u.id, o.total
        ) user_orders
        GROUP BY cohort
    ) cohort_revenue
    GROUP BY cohort
) final
ORDER BY cohort

CTE version:

WITH user_first_purchase AS (
    SELECT
        u.id AS user_id,
        MIN(o.created_at) AS first_purchase_date
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
),

cohort_revenue AS (
    SELECT
        DATE_TRUNC('month', ufp.first_purchase_date) AS cohort,
        SUM(o.total) AS revenue
    FROM user_first_purchase ufp
    JOIN orders o ON ufp.user_id = o.user_id
    GROUP BY cohort
),

cohort_averages AS (
    SELECT
        cohort,
        AVG(revenue) AS avg_revenue
    FROM cohort_revenue
    GROUP BY cohort
)

SELECT
    cohort,
    avg_revenue
FROM cohort_averages
ORDER BY cohort

The CTE version is longer but infinitely more debuggable. You can run each CTE independently to validate intermediate results. This matters enormously in production debugging.

8. Comment non-obvious logic

SQL is often used for business logic that isn’t apparent from the data alone. Comment that context directly in the query.

SELECT
    customer_id,
    SUM(order_total) AS lifetime_value
FROM orders
WHERE
    -- Exclude test orders created by the QA team's shared account
    customer_id != 9999
    -- Only count orders after the platform migration (older data is unreliable)
    AND created_at >= '2022-06-01'
GROUP BY customer_id
HAVING SUM(order_total) > 0

Future you will be grateful.

Refactoring a Real Query: Before and After

Here’s a realistic before/after from a production analytics query:

Before:

select u.id,count(distinct s.id) as sessions,count(distinct e.id) as events,max(s.started_at) as last_seen from users u left join sessions s on s.user_id=u.id and s.started_at>now()-interval '30 days' left join events e on e.session_id=s.id and e.type not in ('pageview','heartbeat') where u.plan='pro' and u.deleted_at is null group by u.id having count(distinct s.id)>0 order by sessions desc limit 100

After:

SELECT
    u.id,
    COUNT(DISTINCT s.id)  AS sessions,
    COUNT(DISTINCT e.id)  AS events,
    MAX(s.started_at)     AS last_seen
FROM users u
LEFT JOIN sessions s
    ON s.user_id = u.id
    AND s.started_at > NOW() - INTERVAL '30 days'
LEFT JOIN events e
    ON e.session_id = s.id
    -- Exclude low-signal event types from engagement count
    AND e.type NOT IN ('pageview', 'heartbeat')
WHERE
    u.plan = 'pro'
    AND u.deleted_at IS NULL
GROUP BY u.id
HAVING COUNT(DISTINCT s.id) > 0
ORDER BY sessions DESC
LIMIT 100

Same query. Completely different readability. The second version makes the intent, filters, and join logic obvious at a glance.

Tooling: Format First, Then Review

If you’re working with legacy SQL or reviewing someone else’s query, formatting it first before reading it is one of the highest-ROI habits you can build. A well-formatted query reveals its structure — and its bugs.

For quick formatting without leaving your browser, this SQL formatter handles most SQL dialects and produces consistently indented, readable output in one click. It’s especially useful when you inherit a minified query from a log file or a BI tool export and need to understand it quickly.

For integrating formatting into your dev workflow, the sql-formatter npm package is excellent — it supports PostgreSQL, MySQL, SQLite, BigQuery, and more, and you can hook it into Prettier or a pre-commit check.

Conventions Worth Standardizing Across Your Team

If you’re working in a team, agree on these and write them down:

Enforcing these via a linter (e.g. SQLFluff) rather than hoping for consistency is even better — SQLFluff supports dozens of dialects and integrates with CI pipelines cleanly.

Quick Reference Cheat Sheet

-- ✅ Correct structure
WITH cte_name AS (
    SELECT
        column_one,
        column_two,
        column_three
    FROM table_name tn
    JOIN other_table ot ON tn.id = ot.table_id
    WHERE tn.some_condition = true
)

SELECT
    c.column_one,
    c.column_two,
    c.column_three,
    COUNT(t.id) AS related_count
FROM cte_name c
LEFT JOIN third_table t ON c.column_one = t.foreign_key
GROUP BY
    c.column_one,
    c.column_two,
    c.column_three
ORDER BY c.column_one ASC
LIMIT 1000

Final Thoughts

SQL style isn’t about personal preference — it’s about the next person (including yourself) who has to read, debug, or extend your query under pressure. Clean SQL is a form of documentation.

The conventions here are not the only valid choices. What matters is consistency. Pick a style, document it, and automate enforcement where possible.

If you have conventions or habits that have saved you in production, drop them in the comments — I’d love to see what others have standardized.