Lesson 5 — SLA performance choropleth

The density choropleth from Lesson 4 answered where is work happening? — useful for capacity awareness. This lesson answers a different question: where are we keeping our promises and where are we failing?

That’s the SLA question. Every dispatch operation has service- level agreements — implicit or explicit promises about how quickly work gets done. “Same-day for urgent.” “Within 48 hours for standard.” “Within five business days for routine.” When those promises slip, customers notice; when patterns of slippage cluster geographically, operations notices.

A choropleth is the right visualisation for this. Same polygons, same map, same legend pattern as Lesson 4. But the colour scale is fundamentally different — and that difference is where this lesson’s substance lives.

What changes from Lesson 4

Three things, each with a teaching beat:

• The metric. No longer a count, but a percentage — completed-on-time as a fraction of total completed.
• The colour scale. No longer sequential (light to dark in one hue), but divergent — two hues meeting in the middle, representing “good” and “bad” with a “neutral” zone between.
• The query. More involved — joins, filters, time arithmetic, conditional aggregation. The first time the dispatch deck does real time-based reporting math.

Same component pattern, same gem features. Just a richer expression of what reports can do.

Defining “on time”

Before any code, the operational definition. We’ll say a job is on time if it was completed within a target window of when it was created. That target depends on the priority:

• Urgent — within 4 hours
• High — within 24 hours
• Normal — within 72 hours
• Low — within 7 days

(Real dispatch operations have more nuanced SLAs — business- hours-only counting, weekends excluded, holidays accommodated, priority-by-customer-tier, etc. We’re keeping it simple. The mechanism is what this lesson teaches; the policy is one configuration change away.)

A job is on-time if completed_at - created_at <= target. The SLA percentage for an SA is the fraction of completed jobs that hit their target.

We’ll only count completed jobs in the calculation. In-flight work isn’t “late” yet — it just hasn’t finished. SLA performance is fundamentally a retrospective measure.

The SLA module

The policy lives in one place — a Ruby module that owns both the data (the priority-to-target mapping) and a SQL generator for queries that need the policy at row level.

app/services/sla.rb:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

module Sla
  extend self

  TARGETS = {
    "urgent" => 4.hours,
    "high"   => 24.hours,
    "normal" => 72.hours,
    "low"    => 7.days
  }.freeze

  DEFAULT = 7.days

  # The SLA target for a given priority, as an
  # ActiveSupport::Duration. Used by application code asking
  # about a single job.
  #
  #   Sla.target("urgent")   # => 14400 seconds (4 hours)
  def target(priority)
    TARGETS.fetch(priority, DEFAULT)
  end

  # A SQL CASE expression that evaluates the same policy against
  # a column at row level. Used inside aggregation queries where
  # the database needs the policy without round-tripping to Ruby.
  #
  #   "AND j.completed_at - j.created_at <= #{Sla.sql_target('j.priority')}"
  #
  # Returns a complete SQL fragment. The column reference is
  # validated as an identifier shape — letters, digits,
  # underscores, dots — to prevent injection. The values are
  # hardcoded constants and so always safe.
  def sql_target(column)
    raise ArgumentError, "invalid column reference" unless column =~ /\A[a-zA-Z_][a-zA-Z0-9_.]*\z/

    cases = TARGETS.map { |priority, duration|
      "WHEN '#{priority}' THEN INTERVAL '#{duration.to_i} seconds'"
    }.join(" ")

    "CASE #{column} #{cases} ELSE INTERVAL '#{DEFAULT.to_i} seconds' END"
  end
end

A few things worth understanding about this shape.

TARGETS is the single source of truth. Both target and sql_target derive from it. Add a new priority — change one constant, and both Ruby callers and SQL queries pick it up automatically.

target(priority) is for application code. Anywhere Ruby asks “what’s the SLA for this job?” — a controller, a notification job, a job.on_time? predicate — it calls this.

sql_target(column) generates a SQL fragment. The output is a complete CASE WHEN ... ELSE ... END expression, ready to drop into a query as if it were a column reference. The generated SQL looks like:

1
2
3
4
5
6
7

CASE j.priority
  WHEN 'urgent' THEN INTERVAL '14400 seconds'
  WHEN 'high'   THEN INTERVAL '86400 seconds'
  WHEN 'normal' THEN INTERVAL '259200 seconds'
  WHEN 'low'    THEN INTERVAL '604800 seconds'
  ELSE              INTERVAL '604800 seconds'
END

Verbose, but Postgres handles CASE expressions efficiently — the planner compiles them to direct branches at query planning time.

The injection guard on sql_target’s argument matters because the column name is interpolated into the SQL string. Hardcoded callers like Sla.sql_target('j.priority') are safe; the regex prevents a future caller from accidentally passing user input. The interpolated values in the CASE expression come from TARGETS.keys — hardcoded constants, always safe.

This pattern — Ruby module owns policy, exposes both application-shaped and SQL-shaped interfaces — is one worth internalising. Beats putting the same logic in a database function, beats inlining a CASE everywhere, beats trying to cram it into ActiveRecord scope chaining. It keeps the policy where the team’s mental model lives (Ruby) while making it available everywhere it’s needed.

The query

The SQL shape, with Sla.sql_target interpolated into it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

module SaSlaPerformance
  extend self

  WINDOW_DAYS = 90

  # Fraction-of-jobs-on-time, by SA, over the last 90 days.
  # Returns a GeoJSON FeatureCollection. Each feature carries
  # its boundary plus completed_count, on_time_count, and
  # on_time_pct.
  def call
    rows = ActiveRecord::Base.connection.select_all(<<~SQL).rows
      SELECT sa.id,
             sa.code,
             sa.name,
             ST_AsGeoJSON(sa.boundary)::jsonb AS geometry,
             COUNT(j.id) FILTER (
               WHERE j.completed_at IS NOT NULL
             ) AS completed_count,
             COUNT(j.id) FILTER (
               WHERE j.completed_at IS NOT NULL
                 AND j.completed_at - j.created_at <= #{Sla.sql_target('j.priority')}
             ) AS on_time_count
      FROM service_areas sa
      LEFT JOIN jobs j ON j.service_area_id = sa.id
                  AND j.completed_at >= NOW() - INTERVAL '#{WINDOW_DAYS} days'
      GROUP BY sa.id, sa.code, sa.name, sa.boundary
    SQL

    {
      type: "FeatureCollection",
      features: rows.map { |id, code, name, geometry, completed, on_time|
        completed = completed.to_i
        on_time   = on_time.to_i
        pct       = completed.zero? ? nil : (on_time.to_f / completed * 100).round(1)

        {
          type: "Feature",
          id:   id,
          geometry:   JSON.parse(geometry),
          properties: {
            code:            code,
            name:            name,
            completed_count: completed,
            on_time_count:   on_time,
            on_time_pct:     pct
          }
        }
      }
    }
  end
end

Two things worth understanding about the query shape.

COUNT(*) FILTER (WHERE ...) is Postgres’s filtered aggregate syntax. It counts rows that match the filter, ignoring others — without filtering the rest of the result set. Two filtered aggregates in one query give us “completed jobs” and “on-time jobs” simultaneously, both grouped by SA. The alternative is two separate queries plus a manual join, or a subquery — both messier.

completed_at - created_at in Postgres returns an interval (e.g. '01:23:45' — one hour, twenty-three minutes, forty-five seconds). Comparing intervals to other intervals just works. Postgres time arithmetic is clean.

The 90-day window matters. SLA performance over too short a period (one week) reflects recent operational quirks more than capability; over too long a period (two years) it includes performance from before whatever staffing or process changes shape today’s reality. 90 days is a common operational horizon — recent enough to be relevant, long enough to wash out one-off events.

Run the service object

In console:

1
2
3
4
5
6
7

result = SaSlaPerformance.call
result[:features].first[:properties]
# => { code: "10101", name: "Bondi - Bondi Junction",
#      completed_count: 247, on_time_count: 189, on_time_pct: 76.5 }

result[:features].count { |f| f[:properties][:on_time_pct].nil? }
# => some number — SAs with no completed jobs in the last 90 days

The data shape is the same FeatureCollection as Lesson 4. The properties differ — three numbers per SA instead of one. SAs with zero completed jobs in the window have nil for percentage; we’ll handle that case in the colour expression.

The controller and route

Add to Api::ServiceAreasController:

1
2
3

def sla_performance
  render json: SaSlaPerformance.call
end

Wire the route:

1
2
3
4
5
6
7
8
9

resources :service_areas, only: [] do
  collection do
    get :national
    get :regional
    get :local
    get :density_current_state
    get :sla_performance
  end
end

A new page in the app

Same shape as the choropleth report, different content. New controller action and component.

In ReportsController:

1
2
3

def sla_performance
  render Components::Reports::SlaPerformance.new
end

Route in config/routes.rb:

1
2
3

get "/reports/sla_performance",
    to: "reports#sla_performance",
    as: :sla_performance_report

Add to the sidebar’s REPORTS constant:

1
2
3
4

REPORTS = [
  { label: "Choropleth Report", path: "/reports/choropleth",      icon: :chart_bar, roles: %w[manager] },
  { label: "SLA Performance",   path: "/reports/sla_performance", icon: :chart_pie, roles: %w[manager] }
].freeze

Page component at app/components/reports/sla_performance.rb:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

class Components::Reports::SlaPerformance < Components::Base
  def view_template
    div(class: "h-full flex flex-col") do
      header(class: "px-6 py-4 border-b border-slate-200") do
        h1(class: "text-2xl font-bold text-slate-900") { "SLA Performance" }
        p(class: "text-sm text-slate-500 mt-1") do
          plain "Percentage of jobs completed within target time, by service area. Last 90 days."
        end
      end

      div(class: "flex-1") do
        render Components::SlaPerformanceMap.new
      end
    end
  end
end

The map component — divergent scale

The substantive new thing in this lesson. Create app/components/sla_performance_map.rb:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

class Components::SlaPerformanceMap < Components::Base
  prop :id,     String, default: -> { "sla-performance-map" }
  prop :height, String, default: -> { "100%" }

  # Divergent colour scale — red (poor) to amber (mediocre) to
  # green (good). Anchored at the values that matter: 70% is
  # "concerning", 85% is "the line", 95% is "excellent." Five
  # stops describe the curve.
  COLOUR_RAMP = [
    [50,  "#dc2626"],  # red-600     - failing
    [70,  "#ef4444"],  # red-500     - poor
    [85,  "#f59e0b"],  # amber-500   - on the threshold
    [95,  "#10b981"],  # emerald-500 - good
    [100, "#059669"]   # emerald-600 - excellent
  ].freeze

  # Colour for SAs with no completed jobs in the window. A neutral
  # grey communicates "no data" — visually distinct from any value
  # on the active ramp.
  NO_DATA_COLOUR = "#cbd5e1"  # slate-300

  POPUP_TEMPLATE = <<~HTML.freeze
    <div class="vera-popup">
      <div class="vera-popup__customer">{{name}}</div>
      <div class="vera-popup__address">SA3 {{code}}</div>
      <div class="vera-popup__description">
        {{#if on_time_pct}}
          On-time: <strong>{{on_time_pct}}%</strong> ({{on_time_count}} of {{completed_count}})
        {{else}}
          No completed jobs in the last 90 days
        {{/if}}
      </div>
    </div>
  HTML

  def view_template
    render Vera::Map.new(id: @id, height: @height, style: :voyager,
                         zoom_indicator: :bottom_left, loading_indicator: true) do |m|
      m.control :navigation
      m.control :fullscreen
      m.control :scale, unit: :metric
      m.overlay :legend, phlex: Components::SlaPerformanceLegend.new,
                         position: :top_right

      add_choropleth(m)
    end
  end

  private

  def add_choropleth(m)
    m.source :sa_sla,
             url:        "/api/service_areas/sla_performance.json",
             fit_bounds: { padding: 20 }

    m.layer :sa_fill, source: :sa_sla, type: :fill,
            paint: {
              fill_color:   choropleth_expression,
              fill_opacity: 0.75
            },
            on_hover: { fill_opacity: 0.9 },
            popup:    { template: POPUP_TEMPLATE }

    m.layer :sa_outline, source: :sa_sla, type: :line,
            paint: {
              line_color:   "#475569",
              line_width:   0.5,
              line_opacity: 0.5
            },
            on_hover: {
              line_color:   "#0f172a",
              line_width:   2,
              line_opacity: 1
            }
  end

  # MapLibre expression: if on_time_pct is null (no completed jobs
  # in window), use the no-data colour; otherwise interpolate
  # linearly through the ramp.
  def choropleth_expression
    [
      "case",
      ["==", ["typeof", ["get", "on_time_pct"]], "number"],
      [
        "interpolate",
        ["linear"],
        ["get", "on_time_pct"],
        *COLOUR_RAMP.flatten
      ],
      NO_DATA_COLOUR
    ]
  end
end

A few things worth understanding here.

The COLOUR_RAMP is a divergent scale. Sequential ramps (Lesson 4) work for “more is more” metrics. Divergent ramps work when the metric has a meaningful midpoint — a value where “above” and “below” mean different things. SLA performance is exactly this. 95% is good; 60% is bad; 80% is mediocre. Each region of the scale conveys different qualitative information, not just “more or less of the same thing.”

The hue shift (red → amber → green) crosses through the visual language of traffic lights and dashboards. Readers don’t need to read the legend to know red is bad and green is good. The colours themselves do half the communicating.

The threshold positions matter. 50% is “failing” not because 50 is special but because anything below it indicates an operation in serious distress. 95% is “excellent” because real operations rarely sustain higher. The breakpoints at 70 and 85 shape the curve to be most informative around the values most SAs actually achieve. If your operation routinely runs at 99%+, you’d compress the ramp to 90-100; if it routinely runs at 60-80, you’d shift downward.

The ["case", ...] expression handles the no-data case. MapLibre’s ["case", condition1, value1, condition2, value2, ..., else] is its conditional construct — return the first matching value. Here we test if on_time_pct is a number (it won’t be if the property is nil/null in the GeoJSON); when it isn’t, return the no-data colour.

The check ["==", ["typeof", ["get", "on_time_pct"]], "number"] is verbose but reliable. MapLibre’s ["typeof", ...] returns the type of an expression result; comparing it to "number" handles null and undefined correctly.

Order of evaluation — case evaluates conditions in order, returning the first match. This means the no-data check must come first. If we put the interpolate call first and tested for null afterward, the interpolate would error on null inputs before the case got to its else branch.

The legend

Create app/components/sla_performance_legend.rb:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

class Components::SlaPerformanceLegend < Components::Base
  RAMP = Components::SlaPerformanceMap::COLOUR_RAMP

  def view_template
    div(class: "bg-white rounded-lg shadow-lg p-4 w-72") do
      div(class: "text-sm font-semibold text-slate-700 mb-3") do
        plain "On-time completion rate"
      end

      div(class: "space-y-1.5") do
        gradient_bar
        scale_labels
      end

      no_data_note
    end
  end

  private

  def gradient_bar
    div(
      class: "h-3 rounded",
      style: "background: linear-gradient(to right, #{gradient_stops})"
    )
  end

  def scale_labels
    div(class: "flex justify-between text-xs text-slate-500") do
      RAMP.each do |threshold, _|
        span { "#{threshold}%" }
      end
    end
  end

  # Map ramp thresholds onto the bar's percentage range. The ramp
  # spans 50-100 here, so 50% is at 0% of the bar, 100% at 100%.
  def gradient_stops
    min = RAMP.first.first.to_f
    max = RAMP.last.first.to_f
    range = max - min

    RAMP.map { |threshold, color|
      pct = ((threshold - min) / range * 100).round(1)
      "#{color} #{pct}%"
    }.join(", ")
  end

  def no_data_note
    div(class: "mt-3 pt-3 border-t border-slate-100 flex items-center gap-2") do
      div(class: "w-3 h-3 rounded",
          style: "background: #{Components::SlaPerformanceMap::NO_DATA_COLOUR}")
      span(class: "text-xs text-slate-500") do
        plain "No completed jobs in the period"
      end
    end
  end
end

Mostly the same shape as Lesson 4’s legend, with two differences worth noting.

The gradient_stops calculation is range-aware. Lesson 4’s ramp went from 0 to 300; this one goes from 50 to 100. The position of each stop on the bar is (threshold - min) / range, not threshold / max. A stop at 70% lands at 40% of the bar ((70 - 50) / 50 = 40%) — without the range adjustment it would be misplaced.

The no-data note is a small swatch + label below the gradient bar. Communicates that there’s a colour outside the ramp meaning “no data.” A choropleth that uses a no-data colour without explaining it leaves the reader puzzled at grey polygons.

Look what just happened

Sign in as the manager. The Reports section now has two items: Choropleth Report and SLA Performance. Click the new one.

The map fills with colour. Most SAs are some shade of green, amber, or grey. Sydney metro shows a mix — most SAs solidly green (high on-time rate), a few in amber (concerning), maybe one or two in red (problem areas). Outback regions appear grey — no completed jobs in the window means no signal either way.

Hover any green SA. The popup shows something like “On-time: 94.2% (487 of 517).” A red SA: “On-time: 62.1% (38 of 61).” A grey SA: “No completed jobs in the last 90 days.”

The legend in the top-right shows the divergent ramp red → amber → green, with thresholds 50, 70, 85, 95, 100. A separate swatch below explains the grey.

What this introduced

Several things that recur in real reporting:

Single-source-of-truth for policy. Sla::TARGETS is the canonical SLA policy. target(priority) serves Ruby callers; sql_target(column) generates SQL fragments. Both consumers draw from the same constant — no drift between application behaviour and reporting behaviour.

Filtered aggregates. COUNT(*) FILTER (WHERE ...) lets one query produce multiple counts over different conditions — substantially cleaner than subqueries for related metrics.

Divergent colour scales. When a metric has a meaningful midpoint (target, threshold, expected value), the visual communication is much sharper using two-hue scales than single-hue gradients. Pick the scale type that matches your metric’s qualitative shape.

No-data handling. Real datasets have gaps. Letting the absence be a distinct visual category — grey, dashed, hashed — preserves the integrity of the active scale. A “0%” rendered in red would falsely classify “no jobs” as “every job missed” which is operationally wrong.

["case", ...] paint expressions. Conditional logic in the style language. Useful whenever per-feature paint depends on more than a simple property lookup.

Where this leaves us

The dispatch deck has its second reporting page, with a more nuanced metric and a more sophisticated visualisation. The choropleth pattern is now established as a tool — same shape, different scales for different stories.

The next lesson takes the maps in a different direction. Instead of adding visualisation layers, we’ll wire the map itself to drive a sidebar — clicking an SA reveals a detail panel with summary information for that area. The map becomes an input device, not just a display.