What you'll build: the SLA engine of an ITSM — the deterministic clock that turns a signed service-level contract into live numbers. SLA policy becomes data (a per-priority response/resolve matrix in sla_definitions), a per-incident clock (incident_sla) computes each ticket's due dates and flips response_breached / resolve_breached the instant now() passes them, and a breach log (sla_breaches) records every miss and escalates it to the owning group's manager. It all reads itsm/core's incidents on one DataK3 bucket — and it's pure SQL: deterministic clock math, no model in the loop, so the engine runs under a service account with only k3.editor + ignite.app-developer — no ignite.model-user, no token-billed calls.

What you'll learn:

  • Model an SLA contract as merge-keyed policy rows — one active definition per priority, tunable without a code change.
  • Compute a per-incident clock with opened_at + to_minutes(target) — response and resolve tracked independently.
  • Detect a breach the moment now() passes a due date, write it once (idempotent), and escalate it by how overdue.
  • Deploy a pure-SQL itsm-sla-monitor engine that proves the least-privilege claim: k3.editor + ignite.app-developer, no Models role.
  • Make a per-tick recompute survive its own estate — why a plain INSERT is not an upsert, why one NULL opened_at froze every ticket's breach flags, and why a recompute must count what it skipped.
  • Decide who is allowed to call a machine heartbeat: /sla/tick takes no identity at all, and the reasoning generalises to every recompute route you will ever write.
  • Answer the scheduler question out loud — Ignite is request-invoked, so a deployment must choose a pinned always-on loop or an external timer.
  • Run every step two ways — by prompting an agent over the MCP, or the dodil data CLI.

The problem — and why it matters

The person who lives and dies by SLAs is the service-desk lead / on-call manager, and the number behind them is real money. Enterprise support contracts carry service-level credits: miss the P1 resolve target and the customer's next invoice is discounted 5–25%. Managed-service providers write the same penalties into every MSA. So "is this ticket about to breach, and who do I wake up?" is not a reporting nicety — it's the difference between hitting a contractual number and cutting a credit cheque.

The classic stack answers it badly. The SLA clock lives inside the ITSM platform (priced per agent seat), the breach history is ETL'd into a warehouse overnight, and the "which tickets breach in the next hour" dashboard is therefore always a few hours stale — exactly the window where an escalation still matters. Worse, the clock is a black box: you can't see why a ticket is due when it's due, and tuning a target means a change request against the vendor's config.

On DataK3 the SLA clock is three merge-keyed tables and one deterministic query over the same incidents rows the rest of the ITSM already holds — read-your-writes, no ETL, no second copy. The policy is data you can read; the clock is SQL you can audit; the breach fires the instant the row crosses its due date. And because it's clock arithmetic, not judgement, there is no model — the engine is the provable least-privilege workload in the suite.

PieceLands inPillar
SLA policy (target matrix per priority)sla_definitions (merge-keyed)SQL
Per-incident clock (due dates + breach flags)incident_sla (merge-keyed on incident_id)SQL
Breach + escalation logsla_breaches (merge-keyed on <incident>:<kind>)SQL
The clock engineitsm-sla-monitor (/tick)Ignite (pure SQL — no Models)

NOTE

Connect the DODIL MCP once — then every step shows an Ask your agent tab (the default — DODIL is agent-native) and a CLI tab. This whole skill was built and validated by prompting an agent over this MCP.

This skill consumes incidents / services / groups from itsm/core. If core isn't installed yet, Step 1 stubs just the columns the clock reads so this runs standalone.

Prerequisites

  • A DODIL organization with the dodil CLI authenticated (dodil auth login) or the DODIL MCP connected to your agent (Claude Code, Cursor, VS Code, Codex). Headless? Check auth_status first — an agent can't do the browser login for you.
  • export BUCKET=itsm — one bucket is the whole ITSM's data plane. (This is the bucket param, default itsm. itsm is four characters, so it needs no suffix: DataK3 rejects a bucket name shorter than three, which is why the GL suite next door had to settle for gl-suite rather than gl.)

Step 1 — Stub the masters you consume (standalone only)

The SLA clock reads three itsm/core masters: incidents (the ticket's priority, opened_at, resolved_at, state), services, and groups (the escalation targets). If itsm/core is already in your bucket, skip this step — those tables exist, and on the validated itsm estate they hold 13 CIs, 4 business services, 5 groups and 13 incidents that core seeded. Standalone, create minimal stubs (every non-key column nullable:true — data table create defaults columns to NOT NULL, and a partial seed row would 500 with NotNullViolation), then seed three incidents that exercise the clock: a P1 opened 6 hours ago and still unresolved (breaches its 240-minute resolve target), a P3 left in new for 3 hours (breaches its 60-minute response target — a different flag entirely), and a responded-but-unresolved P2 (response met, resolve still running).

You

In bucket itsm, stub the itsm/core masters the SLA clock reads. Create incidents (key id: number, short_description, ci_id, service_id, state, priority, assignment_group, opened_at timestamp, resolved_at timestamp), services (key service_id: name, business_service, owner_group, tier, sla_id), and groups (key group_id: name, manager, email, on_call). Make every non-key column nullable. Then seed three escalation groups (g-platform, g-dba, g-orders), one service, and three incidents: P1 INC1006 on pg-orders-primary opened 2026-09-08 11:30 UTC and in_progress; P3 INC1007 opened 2026-09-08 14:30 UTC and still new; P2 INC3001 opened 2026-09-08 15:00 UTC and in_progress.

DODIL MCP tools called
data_bucket_create→data_table_create→data_table_upsert
Agent

Created bucket itsm and stubbed incidents (pk id), services (pk service_id), groups (pk group_id) with every optional column nullable. Seeded 3 groups, 1 service, and 3 incidents — P1 INC1006 (opened 6h ago, unresolved, will breach resolve), P3 INC1007 (still new after 3h, will breach RESPONSE), responded P2 INC3001. Skip this step when itsm/core owns the masters: on the validated itsm estate core seeds 13 CIs, 4 business services, 5 groups and 13 incidents.

TIP

Never null/"" in a merge-key column. A keyed upsert reads a null/empty key back as null and the row silently drops. Here every key (id, service_id, group_id, later sla_id, incident_id, breach_id) is a concrete non-empty value. resolved_at is a non-key column, so null there is fine — it's exactly how the clock knows a ticket is still open.

Step 2 — SLA policy as data (sla_definitions)

The SLA contract is a table, not config buried in a vendor UI: one active row per priority with the response and resolve targets in minutes. This is the sla_targets param — change a number here and every due date and breach re-derives on the next tick, because the clock reads the policy rather than a hard-coded constant.

You

In itsm, create sla_definitions (key sla_id: name, priority, service_id, response_mins int, resolve_mins int, business_hours boolean, active boolean) and seed one active row per priority — P1 15/240, P2 30/480, P3 60/1440, P4 120/2880 (response/resolve minutes), 24x7.

DODIL MCP tools called
data_table_create→data_table_upsert
Agent

Created sla_definitions (pk sla_id) and seeded 4 active rows — P1 15/240, P2 30/480, P3 60/1440, P4 120/2880 response/resolve minutes, business_hours=false. This is the contractual matrix; the clock joins it by priority.

Step 3 — Run the clock (incident_sla)

The clock is one JOIN: match each open incident to its priority's definition, add the target minutes to opened_at, and compare to now(). to_minutes(n) turns an integer into an interval, so opened_at + to_minutes(resolve_mins) is the resolve due date. A breach is now() past the due date with the target unmet — resolved_at IS NULL for resolve, state = 'new' (never acknowledged) for response. Response and resolve are computed independently: a ticket can meet its response SLA and still be running against resolve. The clock table is merge-keyed on incident_id, so every tick writes one row per incident — idempotent.

You

In itsm, create incident_sla (key incident_id: sla_id, priority, response_due timestamp, resolve_due timestamp, responded_at, resolved_at, response_breached boolean, resolve_breached boolean, state, escalation_level int). Then for every open incident, join sla_definitions on priority and compute the clock: response_due = opened_at + response_mins, resolve_due = opened_at + resolve_mins, mark response_breached (state='new' and now past response_due) and resolve_breached (unresolved and now past resolve_due), and set escalation_level from how overdue the resolve is (cap at 2). Write one row per incident.

DODIL MCP tools called
data_table_create→data_pg
Agent

Created incident_sla (pk incident_id). On the full itsm estate the tick evaluated 6 open incidents and wrote 6 clock rows: INC1006 P1 resolve_breached=true escalation_level=2 (responded), INC1007 P3 response_breached=true (still new past its 60-minute response target), INC3001 P2 responded with the resolve clock still running, and three more inside target. One row per incident.

The P1 (INC1006, opened 11:30, 240-minute resolve target → due 15:30) is past due at a now() of 17:30, so resolve_breached is true and it's two hours overdue → escalation_level = 2. INC1007 shows the other flag: it is nowhere near its 1440-minute resolve target, but nobody has touched it in three hours, so it blew a 60-minute response target and response_breached is true with escalation_level 0. Response and resolve are genuinely independent columns, and this is why — the two failures have different owners and different remedies. The P2 has been acknowledged (state advanced past new) so responded is true — its response SLA is met — while its 8-hour resolve clock keeps running.

One NULL open time froze the breach flags for the whole estate

This is the best of the six integration bugs the ITSM conversion surfaced, and it is a one-line bug with an estate-wide blast radius.

Every one of the seven ITSM components passed its own ## Test alone. Stood up together on the one itsm bucket — as the suite app actually runs — six bugs surfaced in an afternoon, because components validated separately are internally consistent and still wrong together. This is the sharpest of them.

itsm/core's incident-create route did not stamp opened_at. Its IncidentIn schema simply had no such field, which is defensible in isolation: core is CRUD over a ticket table, and a caller who cares about the open time can supply it. So the first ticket created through the plain core route landed with a NULL open time, and nothing complained.

Then the clock ran. Look again at what it does: it joins every open incident and computes opened_at + minutes. One None in that column is a TypeError, the request 500s — and because the clock recomputes the whole estate on every tick, it 500s before writing anything. Every subsequent tick died on the same row.

The damage is not confined to the broken ticket. Every other incident's breach flags simply stopped advancing. The dashboard did not go red. It went stale, and a stale SLA board looks exactly like a healthy one: nothing breaching, nobody paged, numbers steady. An SLA system whose failure mode is reports green is worse than one that is visibly down.

It was fixed on both sides, and both were necessary:

  • The producer — itsm/core now stamps t0 when the caller doesn't supply one: row["opened_at"] = row.get("opened_at") or datetime.now(timezone.utc).replace(tzinfo=None).
  • The consumer — the clock skips a row it cannot compute and counts what it skipped, in the tick's own response body:
# sla_management.py (the shipped tick) — the guard, and the comment that explains it
for r in rows:
    opened = _naive(r["opened_at"])
    # A per-tick engine must never be ONE BAD ROW away from stopping. An incident with no
    # `opened_at`, or a policy row with no targets, has no computable clock — count it and
    # move on. Raising here takes the SLA engine down for the WHOLE estate, which is what
    # happened live on 2026-09-08 the first time a ticket was created through the core CRUD
    # route (which did not stamp t0): every subsequent tick 500'd on a TypeError and the
    # breach flags for every OTHER incident silently stopped advancing.
    if opened is None or r["response_mins"] is None or r["resolve_mins"] is None:
        skipped += 1
        continue

Fixing only the producer would have been the tempting call — it is one line, and it makes this NULL go away. It leaves the next one free to do it all again, and the next one arrives from a CSV import, an integration, or a UI form that nobody thought of as an incident producer. The rule worth carrying out of this: a batch recompute over N rows must be resilient to row N, and it must report what it skipped. A silent skip is a different bug wearing the same clothes; a counted skip — skipped_no_clock in the tick's return value — is an observable one you can alert on.

NOTE

The /tick recompute re-writes the same incident_id every run, so it must upsert with ON CONFLICT. incident_sla is merge-keyed on incident_id; a bare re-INSERT of an already-committed PK is rejected with a duplicate-key error (SQLSTATE 23505) — a plain INSERT is not an upsert on re-write. The engine writes INSERT … ON CONFLICT (incident_id) DO UPDATE SET … (DuckDB pg-wire supports ON CONFLICT, verified live); the managed data table upsert is the equivalent. The full proof, in both directions, is The scar, proved both ways below.

Step 4 — Detect breaches + escalate (sla_breaches)

The clock flags a breach; the breach log records it once and routes it. sla_breaches is keyed on <incident_id>:<kind> (e.g. 1006:resolve) so response and resolve breaches of the same ticket are distinct rows and re-running never duplicates them. escalated_to is the assignment group's manager (a JOIN to groups), and the on-call view is a rollup of open breaches by that manager.

You

In itsm, create sla_breaches (key breach_id: incident_id, sla_id, kind response|resolve, breached_at, escalated_to, escalation_level int, notified boolean). Insert one row per breached clock — breach_id = incident_id||':resolve' (or ':response'), breached_at = the due date, escalated_to = the assignment group's manager from groups. Then roll up open (unnotified) breaches by escalated_to for the on-call view.

DODIL MCP tools called
data_table_create→data_pg→data_sql
Agent

Created sla_breaches (pk breach_id). Wrote 2 breach rows across the estate: 1006:resolve escalated to [email protected] at level 2, and 1007:response at level 0 (a response miss does not escalate). On-call rollup: [email protected] holds both open breaches, max level 2.

That's the whole engine in three tables: policy, clock, log. A missed P1 is on the Platform manager's rollup within one tick of crossing 15:30 — not tomorrow morning after an ETL.

Two gaps that only appeared once the bridge shared the bucket

Both of these were invisible while SLA management owned its own bucket — for the dullest possible reason: nothing else ever closed a ticket. Run the clock next to itsm/major-incident, which resolves an incident when its bridge closes, and both surfaced within minutes.

1. The clock never retired a ticket somebody else resolved. The evaluation loop walks only open incidents, which is correct as far as it goes — but it means a ticket that closes leaves its clock row frozen at its last open reading, forever. Live on 2026-09-08, INC1006 still read state=in_progress, resolve_breached=true, escalation_level=2 long after the major-incident bridge had resolved it. tick now runs a retire pass: a second query for clock rows whose incident has closed since the last tick, written in the same commit.

What it writes is worth reading carefully, because the omissions are the design:

# the retire pass writes state, resolved_at, resolve_breached and a zeroed escalation level
retired.append({"incident_id": r["id"], "state": r["state"], "resolved_at": resolved,
                "resolve_breached": breached_on_close, "escalation_level": 0})

resolve_breached is now judged at the moment of resolution — did it land after the due date? — rather than the open-ticket question is it still running past due. Those are different questions and a closed ticket deserves the first one. escalation_level goes to 0 because a closed ticket escalates no further. And the response columns are deliberately absent from that dict: db.upsert only updates the columns it is given, so the response verdict recorded while the ticket was open survives untouched. That is a technique, not a detail — a partial upsert is how you retire half a row without inventing values for the other half. (It also has to be a separate upsert call from the open-clock rows, because db.upsert normalises one call to a single column set.)

2. A resolved breach paged a manager forever. GET /sla/oncall rolled up unnotified breaches with no reference to whether the ticket was still open, so INC1006's level-2 breach sat on [email protected]'s rollup after the bridge had closed it. The fix is the JOIN in the query above.

The temptation is to fix that by marking the breach row notified, or deleting it. Don't. sla_breaches is an audit log — a breach that happened stays on the record, because the credit note attached to it is real whether or not the ticket later closed. "What happened" and "what do I owe right now" are two different questions asked of the same table, and collapsing them into one either falsifies your audit trail or pages someone forever. The log is immutable; the rollup is where the filter belongs:

# sla_management.py — the on-call rollup, and why it joins
# JOIN to incidents and keep only OPEN tickets. `sla_breaches` is an audit LOG — a breach
# that happened stays on the record — but "what do I owe right now" must not keep paging a
# manager about a ticket that closed hours ago. Without this join, INC1006's level-2 breach
# sat on [email protected]'s rollup forever after the bridge resolved it (live, 2026-09-08).

Both components moving together is visible in the tick response itself. After the bridge took INC1006 open → mitigating → resolved, the next tick read evaluated 5, escalations 0, retired 1 — one fewer open incident to clock, the escalation gone, and one clock row retired. Two components, one copy of the rows, no integration job between them.

The scar, proved both ways

The shipped tick writes through db.upsert, which is INSERT … ON CONFLICT (<pk>) DO UPDATE SET col = EXCLUDED.col. That is not a stylistic preference, and the live itsm bucket proves it in both directions:

You

On the itsm bucket, run the SLA tick three times in a row and show the results and row counts are identical. Then, on the same live sla_breaches table, try a bare re-INSERT of the already-committed primary key 1006:resolve, and then the same statement with ON CONFLICT DO UPDATE.

DODIL MCP tools called
data_pg→data_sql
Agent

Three consecutive ticks all returned 200 with an identical body — evaluated 6, breaches 2, escalations 1. incident_sla stayed at 6 rows and sla_breaches at 2 across all three, every column byte-identical between runs. The counterfactual on the same table: a bare re-INSERT of the committed PK ('1006:resolve') was REJECTED with psycopg.errors.UniqueViolation — duplicate key value violates unique constraint sla_breaches_pkey. The same statement with ON CONFLICT (breach_id) DO UPDATE SET col = EXCLUDED.col was accepted and the row count was unchanged at 2.

The rule for any per-tick recompute on DataK3: a plain INSERT is not an upsert. It works exactly once per key — which is precisely why it survives a first test and dies on the second tick, in production, at 3am. Write idempotently with ON CONFLICT (<pk>) DO UPDATE, or delete the affected keys first, or use the managed data_table_upsert, which is idempotent on its own.

WARNING

DO UPDATE SET only accepts EXCLUDED.<col> references on the DataK3 pg wire. A literal or an expression — SET notified = true — is rejected with FeatureNotSupported. Put the constant in the VALUES list and reference EXCLUDED.notified instead. Two other quirks worth knowing before you debug them: data_pg DML always returns row_count: 0 regardless of how many rows it wrote (verify a conditional write with a follow-up SELECT), and at is a DuckDB reserved word, which is why the timestamp columns here are opened_at / breached_at and never a bare at.

Step 5 — The itsm-sla-monitor engine (image mode, pure SQL)

Steps 2–4 are the recompute the engine runs on a schedule. Package it as an image-mode Ignite app — a plain HTTP server behind a Dockerfile, built by the platform on deploy (Kaniko build-on-deploy, Lane B). It exposes GET /healthz (the probe) and POST /tick (recompute every open incident). The data plane is the drop-in Postgres wire (pg.uk-lon-1.dodil.io:5432, dbname=<bucket>, user=token, password=<the SA access token>). The /tick recompute re-writes the same incident_id every run, so it writes INSERT … ON CONFLICT (incident_id) DO UPDATE — a bare re-INSERT of an already-committed PK is rejected with duplicate-key 23505 (a plain INSERT is not an upsert on re-write; DuckDB pg-wire supports ON CONFLICT, or use the managed data table upsert).

Because there is no model — the clock is arithmetic — the service account needs only k3.editor (write the tables) + ignite.app-developer (the deploy/invoke identity). No ignite.model-user, no token-billed calls. That is the whole point of the pure-SQL skill, and it's verifiable: after the grants, auth service-account roles returns exactly those two.

# sla-monitor/server.py — IMAGE-mode Ignite app (HTTP server on $PORT), PURE SQL — no Models.
#   GET  /healthz -> 200 {"status":"ready"}   (probe path; no auth)
#   POST /tick    -> recompute incident_sla + sla_breaches for every open incident
# Only psycopg is third-party (the pg driver); everything else is stdlib. NO Models SDK, NO api.dodil.io.
 
from datetime import datetime, timedelta, timezone
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
 
from psycopg import errors as pg_errors
 
# --- hoisted policy knobs, injected as env at deploy ---
ESCALATION_LEVELS = int(os.environ.get("ESCALATION_LEVELS", "2"))
BUSINESS_HOURS    = os.environ.get("BUSINESS_HOURS", "false").lower() == "true"
BUCKET   = os.environ["BUCKET"]
SA_ID    = os.environ["DODIL_SERVICE_ACCOUNT_ID"]   # the cli-… serviceAccountId, NOT the uuid
SA_SECRET = os.environ["DODIL_SERVICE_ACCOUNT_SECRET"]
PG_HOST  = os.environ.get("PG_HOST", "pg.uk-lon-1.dodil.io")
PG_PORT  = int(os.environ.get("PG_PORT", "5432"))
ID_URL   = "https://id.dodil.io/realms/dodil/protocol/openid-connect/token"
# an explicit User-Agent is REQUIRED — stdlib urllib's default is Cloudflare-banned (HTTP 403 "1010").
UA = "itsm-sla-monitor/1.0"
 
 
def _token():
    # the ONLY network call: mint an access token (= the pg-wire password). No Models endpoint.
    body = urllib.parse.urlencode({"grant_type": "client_credentials",
                                   "client_id": SA_ID, "client_secret": SA_SECRET}).encode()
    req = urllib.request.Request(ID_URL, data=body, method="POST",
                                 headers={"User-Agent": UA,
                                          "Content-Type": "application/x-www-form-urlencoded"})
    with urllib.request.urlopen(req, timeout=30) as r:
        return json.loads(r.read().decode())["access_token"]
 
 
def _pg(token):
    return psycopg.connect(host=PG_HOST, port=PG_PORT, dbname=BUCKET,
                           user="token", password=token, sslmode="require",
                           connect_timeout=20, autocommit=False)
 
 
def _add_minutes(opened, mins):
    # 24x7: wall-clock elapsed. business_hours: advance only through Mon-Fri 09:00-17:00.
    if not BUSINESS_HOURS:
        return opened + timedelta(minutes=mins)
    cur, remaining = opened, mins
    while remaining > 0:
        if cur.weekday() >= 5:                                  # weekend -> Monday 09:00
            cur = (cur + timedelta(days=1)).replace(hour=9, minute=0, second=0, microsecond=0); continue
        day_start = cur.replace(hour=9, minute=0, second=0, microsecond=0)
        day_end   = cur.replace(hour=17, minute=0, second=0, microsecond=0)
        if cur < day_start: cur = day_start
        if cur >= day_end:                                      # after hours -> next day 09:00
            cur = (cur + timedelta(days=1)).replace(hour=9, minute=0, second=0, microsecond=0); continue
        avail = (day_end - cur).total_seconds() / 60
        step  = min(remaining, avail)
        cur += timedelta(minutes=step); remaining -= step
    return cur
 
 
def _retry(fn):
    # the tables engine is serializable — retry a write on a transient serialization/deadlock.
    for attempt in range(4):
        try:
            return fn()
        except (pg_errors.SerializationFailure, pg_errors.DeadlockDetected):
            if attempt == 3:
                raise
            time.sleep(0.4 * (attempt + 1))
 
 
def tick():
    token = _token()
    now = datetime.now(timezone.utc)
    evaluated = breaches = escalations = 0
    with _pg(token) as conn, conn.cursor() as cur:
        # open incidents joined to their active SLA definition
        cur.execute("""
            SELECT i.id, d.sla_id, i.priority, i.opened_at, i.resolved_at, i.state,
                   i.assignment_group, d.response_mins, d.resolve_mins, g.manager
              FROM incidents i
              JOIN sla_definitions d ON d.priority = i.priority AND d.active
              LEFT JOIN groups g ON g.group_id = i.assignment_group
             WHERE i.state NOT IN ('resolved','closed','cancelled')""")
        rows = cur.fetchall()
 
    for (iid, sla_id, prio, opened, resolved, state, grp, resp_m, res_m, manager) in rows:
        response_due = _add_minutes(opened, resp_m)
        resolve_due  = _add_minutes(opened, res_m)
        responded    = state != "new"
        response_breach = (state == "new") and now > response_due
        resolve_breach  = (resolved is None) and now > resolve_due
        level = 0
        if resolve_breach:
            hours_over = (now - resolve_due).total_seconds() / 3600
            level = min(ESCALATION_LEVELS, 1 + int(hours_over))
 
        def _write():
            with _pg(token) as conn, conn.cursor() as cur:
                # /tick re-writes the SAME incident_id (and breach_id = <iid>:<kind>) every run, so both
                # writes ON CONFLICT DO UPDATE — a bare re-INSERT of a committed PK raises duplicate-key
                # 23505. DuckDB pg-wire supports ON CONFLICT (verified live).
                cur.execute("""INSERT INTO incident_sla
                    (incident_id, sla_id, priority, response_due, resolve_due, responded_at,
                     resolved_at, response_breached, resolve_breached, state, escalation_level)
                    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    ON CONFLICT (incident_id) DO UPDATE SET sla_id=EXCLUDED.sla_id,
                     priority=EXCLUDED.priority, response_due=EXCLUDED.response_due,
                     resolve_due=EXCLUDED.resolve_due, responded_at=EXCLUDED.responded_at,
                     resolved_at=EXCLUDED.resolved_at, response_breached=EXCLUDED.response_breached,
                     resolve_breached=EXCLUDED.resolve_breached, state=EXCLUDED.state,
                     escalation_level=EXCLUDED.escalation_level""",
                    (iid, sla_id, prio, response_due, resolve_due,
                     (now if responded else None), resolved,
                     response_breach, resolve_breach, state, level))
                for kind, due, hit in (("response", response_due, response_breach),
                                       ("resolve",  resolve_due,  resolve_breach)):
                    if hit:
                        cur.execute("""INSERT INTO sla_breaches
                            (breach_id, incident_id, sla_id, kind, breached_at,
                             escalated_to, escalation_level, notified)
                            VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
                            ON CONFLICT (breach_id) DO UPDATE SET incident_id=EXCLUDED.incident_id,
                             sla_id=EXCLUDED.sla_id, kind=EXCLUDED.kind, breached_at=EXCLUDED.breached_at,
                             escalated_to=EXCLUDED.escalated_to, escalation_level=EXCLUDED.escalation_level,
                             notified=EXCLUDED.notified""",
                            (f"{iid}:{kind}", iid, sla_id, kind, due,
                             manager or grp, level, False))
                conn.commit()
        _retry(_write)
        evaluated += 1
        if response_breach or resolve_breach:
            breaches += 1
        if level > 0:
            escalations += 1
    return {"evaluated": evaluated, "breaches": breaches, "escalations": escalations}
 
 
class Handler(BaseHTTPRequestHandler):
    def _send(self, code, body):
        payload = json.dumps(body).encode()
        self.send_response(code)
        self.send_header("Content-Type", "application/json")
        self.send_header("Content-Length", str(len(payload)))
        self.end_headers()
        self.wfile.write(payload)
 
    def do_GET(self):
        if self.path == "/healthz":
            return self._send(200, {"status": "ready"})
        return self._send(404, {"error": "no_route", "path": self.path})
 
    def do_POST(self):
        if self.path != "/tick":
            return self._send(404, {"error": "no_route", "path": self.path})
        try:
            return self._send(200, tick())
        except Exception as e:
            return self._send(500, {"error": type(e).__name__, "detail": str(e)[:600]})
 
    def log_message(self, *a):
        pass
 
 
if __name__ == "__main__":
    port = int(os.environ.get("PORT", "8080"))
    print(f"itsm-sla-monitor serving on 0.0.0.0:{port} "
          f"business_hours={BUSINESS_HOURS} escalation_levels={ESCALATION_LEVELS} bucket={BUCKET}",
          flush=True)
    ThreadingHTTPServer(("0.0.0.0", port), Handler).serve_forever()

The image is a two-file build alongside server.py — the Dockerfile and its one pip dep:

# sla-monitor/Dockerfile
FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY server.py .
ENV PORT=8080
EXPOSE 8080
CMD ["python", "server.py"]
# sla-monitor/requirements.txt
psycopg[binary]==3.2.3

Give the engine its own least-privilege identity — and note there is no ignite.model-user:

You

Create a service account for itsm-sla-monitor, grant it ONLY k3.editor (write the SLA tables) and ignite.app-developer (deploy/invoke) — no ignite.model-user, it's pure SQL. Then show its roles to confirm exactly those two. Use its cli- serviceAccountId as DODIL_SERVICE_ACCOUNT_ID.

DODIL MCP tools called
auth_service-account_create→auth_service-account_grant-role→auth_service-account_roles
Agent

Created itsm-sla-monitor-sa, granted k3-authorization-service k3.editor + ignite-authorization-service ignite.app-developer. auth service-account roles returns exactly: k3.editor, ignite.app-developer — NO ignite.model-user. DODIL_SERVICE_ACCOUNT_ID is the cli- serviceAccountId (not the uuid).

Then deploy it as an image — the platform builds the Dockerfile on deploy (Kaniko build-on-deploy), so --allow-unauthenticated needs no pull secret and the app comes up at a public FQDN. Not --runtime python (that's compile mode):

You

Deploy my ./sla-monitor app to Ignite as itsm-sla-monitor in image mode (build the Dockerfile on deploy), port 8080, health /healthz, unauthenticated, with the service-account creds + ESCALATION_LEVELS=2 + BUSINESS_HOURS=false as runtime env. Then POST /tick.

DODIL MCP tools called
ignite_app_deploy→ignite_app_get
Agent

Deployed itsm-sla-monitor in image mode (Dockerfile built on deploy), serving /tick, health /healthz, request-invoked. POST /tick -> {evaluated:6, breaches:2, escalations:1}. Recomputes incident_sla + sla_breaches over the pg wire — pure SQL, no Models.

NOTE

Deploy: image mode (Lane B). itsm-sla-monitor ships as a Dockerfile + --dockerfile-path, Kaniko build-on-deploy — not --runtime python. The image-mode deploy pattern is validated live (2026-09-02) on the sibling CRM engines (deploys, serves /healthz unauthenticated, writes durably over the pg wire); itsm-sla-monitor reuses that identical handler/deploy shape, and its data logic (definitions, clock, breach, escalation, idempotency) is proven live query-by-query (see Test). If ignite app deploy returns IAM resource registration failed: broken pipe, deploy under a fresh app name — the half-created app can't be updated or deleted.

What actually calls /tick — the scheduler question, answered honestly

Read the deploy above again and notice what is missing: nothing is calling /tick.

Ignite is request-invoked, and there is no server-side scheduler. No cron: stanza, no timer trigger, nothing on the platform that will wake this app at :00 and :30. That is a real gap, not a design flourish, and ITSM is where it becomes impossible to hide — because an SLA clock that only advances when someone loads a page is not an SLA clock. Most workloads on this platform tolerate being button-triggered: a rollup, a report, a re-materialization all happen when a human asks. A breach that nobody computed is a credit note you find out about from the customer.

So a deployment must pick one of two patterns and say which:

(a) An always-on pinned app. Deploy with --reserved 1 --max-replicas 1 and run the poll loop inside the pod, calling tick() on an interval. The clock is then self-contained — one artifact, no external dependency, and the loop can't drift out of sync with the code it drives. The cost is a warm replica: the app never scales to zero, so you pay for it around the clock whether or not anything breaches.

# (a) pinned always-on: the app never scales to zero and runs its own loop
dodil ignite app deploy itsm-sla-monitor \
  --code ./sla-monitor --dockerfile-path Dockerfile \
  --port 8080 --health-path /healthz \
  --reserved 1 --max-replicas 1 \
  --env BUCKET="$BUCKET" --env TICK_INTERVAL_SECONDS=60

(b) An external scheduler. Cron on a box you own, a CI timer, a Kubernetes CronJob, any orchestrator that can POST /tick. The app stays scale-to-zero and you pay only for the ticks. The cost is that the clock now depends on infrastructure outside DODIL — one more thing to monitor, and one more thing whose silence looks exactly like "nothing is breaching".

# (b) external: the app stays scale-to-zero; something outside DODIL owns the heartbeat
*/1 * * * *  curl -fsS -X POST "$APP_URL/sla/tick" >> /var/log/itsm-sla.log 2>&1

There is no third option today, and neither is free. Pick deliberately, write the choice down next to the deploy, and monitor the heartbeat itself — the evaluated count in the tick response is a perfectly good liveness signal, and skipped_no_clock is the alert you now know to want.

Either way — and this is the part that drives the whole next section — the caller is a service account over a platform invoke, not a browser user.

Routes

The download (see Get the code) fronts this bucket with a small FastAPI app, routes.py — CRUD over the SLA policy + the masters it consumes, plus the three ops the raw tables can't answer on their own: the clock recompute (POST /sla/tick), one incident's live clock (GET /incidents/{id}/sla), and the on-call breach rollup (GET /sla/oncall). POST /sla/tick is the itsm-sla-monitor engine (Step 5) as a route — deploying it just wraps this function on a schedule.

The connection and the one write helper live in db.py (byte-identical to the CRM packages). A DataK3 bucket is a Postgres endpoint — db name = the bucket, user = the literal token, password = your DODIL token — so there's no data connect step in code, just fixed region constants. upsert() is the only writer every route uses — INSERT … ON CONFLICT DO UPDATE, so a retry, a shard replay, or a re-tick lands the row once (a bare re-INSERT of a committed PK raises duplicate-key 23505). This is pure SQL end to end — no Models token anywhere in the file.

CRUD — each write is an upsert then a commit (DataK3 has no read-your-writes inside an open transaction; the engine is expire_on_commit=False, so routes commit before they return):

# routes.py — CRUD over the SLA policy + the masters, keyed on the natural PK
@app.post("/sla_definitions")
def upsert_definition(d: SlaDefinitionIn, s: Session = Depends(db)):
    upsert(s, SlaDefinition, [d.model_dump()], key="sla_id")
    s.commit()
    return {"ok": True, "sla_id": d.sla_id}
 
 
@app.post("/incidents")
def upsert_incident(i: IncidentIn, s: Session = Depends(db)):
    upsert(s, Incident, [i.model_dump()], key="id")
    s.commit()
    return {"ok": True, "id": i.id}

Workflow op 1 — run the clock (SQL, idempotent). POST /sla/tick is the whole engine in one function: read every open incident joined to its active SLA definition, compute the two due dates (opened_at + target minutes, 24×7 or the business-calendar branch), flip the independent response/resolve breach flags, escalate a resolve breach by how overdue it is, and write the clock + breaches through upsert. Both writes are ON CONFLICT DO UPDATE, keyed on incident_id and <incident_id>:<kind>, so a re-tick re-writes the same rows — that's the point of a recompute engine. Live 2026-09-08 on the itsm bucket: three consecutive ticks each returned {"evaluated": 6, "breaches": 2, "escalations": 1}, with no 23505 and the tables holding exactly 6 clock rows and 2 breaches throughout — every column byte-identical between runs. Note the two guards the integration run put in: the skip that keeps one uncomputable row from killing the estate, and the retire pass that closes out tickets another component resolved:

# routes.py — workflow op 1: run the clock (recompute incident_sla + sla_breaches, idempotent)
# DELIBERATELY UNGATED — no `current_user`, no permission. /sla/tick is a MACHINE heartbeat
# called by the always-on pinned app's own loop or an external scheduler, i.e. by the service
# account over a PLATFORM invoke, which carries no end-user and therefore no `X-Dodil-User`.
# A `Depends(current_user)` here would 401 the clock — the engine would stop and the breach
# flags would silently go stale. Exposure is controlled at the ingress (`public_invoke=false`),
# not by a user permission.
@app.post("/sla/tick")
def tick(s: Session = Depends(db)):
    now = _now()
    # read pass — open incidents joined to their active SLA definition + the escalation manager
    rows = s.execute(text(
        "SELECT i.id AS id, d.sla_id AS sla_id, i.priority AS priority, i.opened_at AS opened_at, "
        "       i.resolved_at AS resolved_at, i.state AS state, i.assignment_group AS assignment_group, "
        "       d.response_mins AS response_mins, d.resolve_mins AS resolve_mins, g.manager AS manager "
        "  FROM incidents i "
        "  JOIN sla_definitions d ON d.priority = i.priority AND d.active "
        "  LEFT JOIN groups g ON g.group_id = i.assignment_group "
        " WHERE i.state NOT IN ('resolved','closed','cancelled')"
    )).mappings().all()
 
    clocks, breaches = [], []
    evaluated = breached = escalations = skipped = 0
    for r in rows:
        opened = _naive(r["opened_at"])
        # A per-tick engine must never be ONE BAD ROW away from stopping. An incident with no
        # `opened_at`, or a policy row with no targets, has no computable clock — count it and
        # move on. Raising here takes the SLA engine down for the WHOLE estate.
        if opened is None or r["response_mins"] is None or r["resolve_mins"] is None:
            skipped += 1
            continue
        response_due = _add_minutes(opened, r["response_mins"])
        resolve_due = _add_minutes(opened, r["resolve_mins"])
        responded = r["state"] != "new"
        response_breach = (r["state"] == "new") and now > response_due
        resolve_breach = (r["resolved_at"] is None) and now > resolve_due
        level = 0
        if resolve_breach:
            hours_over = (now - resolve_due).total_seconds() / 3600
            level = min(ESCALATION_LEVELS, 1 + int(hours_over))
 
        clocks.append({
            "incident_id": r["id"], "sla_id": r["sla_id"], "priority": r["priority"],
            "response_due": response_due, "resolve_due": resolve_due,
            "responded_at": (now if responded else None), "resolved_at": _naive(r["resolved_at"]),
            "response_breached": response_breach, "resolve_breached": resolve_breach,
            "state": r["state"], "escalation_level": level,
        })
        for kind, due, hit in (("response", response_due, response_breach),
                               ("resolve", resolve_due, resolve_breach)):
            if hit:
                breaches.append({
                    "breach_id": f"{r['id']}:{kind}", "incident_id": r["id"], "sla_id": r["sla_id"],
                    "kind": kind, "breached_at": due,
                    "escalated_to": (r["manager"] or r["assignment_group"]),
                    "escalation_level": level, "notified": False,
                })
        evaluated += 1
        if response_breach or resolve_breach:
            breached += 1
        if level > 0:
            escalations += 1
 
    # RETIRE pass — clock rows whose incident CLOSED since the last tick (the loop above only
    # walks OPEN incidents, so without this a resolved ticket stays frozen at its last open
    # reading forever). resolve_breached is re-judged at the moment of RESOLUTION; the response
    # columns are deliberately absent, because db.upsert only updates the columns it is given.
    retired = []
    for r in s.execute(text(
        "SELECT c.incident_id AS id, i.state AS state, i.opened_at AS opened_at, "
        "       i.resolved_at AS resolved_at, d.resolve_mins AS resolve_mins "
        "  FROM incident_sla c "
        "  JOIN incidents i ON i.id = c.incident_id "
        "  LEFT JOIN sla_definitions d ON d.priority = i.priority AND d.active "
        " WHERE i.state IN ('resolved','closed','cancelled') "
        "   AND (c.state IS NULL OR c.state NOT IN ('resolved','closed','cancelled'))"
    )).mappings().all():
        resolved, opened = _naive(r["resolved_at"]), _naive(r["opened_at"])
        breached_on_close = bool(
            resolved is not None and opened is not None and r["resolve_mins"] is not None
            and resolved > _add_minutes(opened, r["resolve_mins"]))
        retired.append({"incident_id": r["id"], "state": r["state"], "resolved_at": resolved,
                        "resolve_breached": breached_on_close, "escalation_level": 0})
 
    # write pass — ON CONFLICT DO UPDATE (a bare re-INSERT of a committed PK is a 23505)
    if clocks:
        upsert(s, IncidentSla, clocks, key="incident_id")
    if retired:
        # a SEPARATE upsert call: db.upsert normalises one call to a single column set, and
        # these rows deliberately carry fewer columns than the open-clock rows above.
        upsert(s, IncidentSla, retired, key="incident_id")
    if breaches:
        upsert(s, SlaBreach, breaches, key="breach_id")
    s.commit()
    return {"evaluated": evaluated, "breaches": breached, "escalations": escalations,
            "retired": len(retired), "skipped_no_clock": skipped}

The read pass runs in one committed transaction and the write pass in the next — DataK3 has no read-your-writes inside an open transaction, so the clock never re-reads its own staged writes. The two extra counters in that return value are not telemetry for its own sake: retired is how you see another component's work land, and skipped_no_clock is the alert that would have caught the NULL opened_at outage on the first tick instead of the tenth.

Workflow op 2 — one incident's clock (SQL). GET /incidents/{id}/sla is a keyed read of the computed row — due dates, breach flags, escalation level. Live for INC1006 after a tick: resolve_breached = true, escalation_level = 2.

Workflow op 3 — the on-call view (SQL). GET /sla/oncall rolls open (unnotified) breaches up by escalated_to — the manager's rollup a missed P1 lands on within one tick. Live on itsm: [email protected], open_breaches = 2, max_level = 2. The JOIN incidents is the fix from Two gaps — the breach log keeps every breach that ever happened, and the rollup shows only what is still owed:

# routes.py — workflow op 3: the on-call view — OPEN breaches rolled up by who owns them
@app.get("/sla/oncall")
def oncall(user=Depends(current_user), s: Session = Depends(db)):
    # `sla_breaches` is an audit LOG — a breach that happened stays on the record — but "what do
    # I owe right now" must not keep paging a manager about a ticket that closed hours ago.
    rows = s.execute(text(
        "SELECT b.escalated_to AS escalated_to, count(*) AS open_breaches, "
        "       max(b.escalation_level) AS max_level "
        "  FROM sla_breaches b JOIN incidents i ON i.id = b.incident_id "
        " WHERE NOT b.notified AND i.state NOT IN ('resolved','closed','cancelled') "
        " GROUP BY b.escalated_to ORDER BY open_breaches DESC"
    )).mappings().all()
    return {"oncall": [dict(r) for r in rows]}

Adding a new business operation touches only routes.py (and maybe models.py) — the plumbing in db.py is fixed. The pattern is one Pydantic *In schema + one @app.<verb> function: write via upsert, read after commit (see EXTENDING.md in the package). Every route is pure SQL, which is why the deployed engine's service account needs nothing but k3.editor — no ignite.model-user.

Auth — config at the edge, a role gate in the app

End-user login on Ignite is configuration, not code. The ITSM deploys with the itsm-suite dodil-appid pool attached (user_pool: itsm-suite in .dodil/deploy.yaml) and the per-cluster Ignite gateway runs the entire browser login at the edge — PKCE S256 against the pool issuer, an AEAD-sealed host-only session cookie, single-flight refresh, EdDSA JWT verification against trust anchors this app does not hold. It then injects the verified identity into every request it forwards:

  • X-Dodil-User — sub, email, connection, app_roles as plain JSON;
  • X-Dodil-User-Jwt — the raw verified pool token, carrying the catalog-expanded permissions claim;
  • X-Dodil-Auth-Source — pool for an app end-user, platform for an operator or service-account invoke.

Any inbound copy of those headers is stripped first, on every mode and every principal, so a caller can never forge them. The pod is only reachable through that ingress.

What survives in the package is a small auth.py that ships no verifier — no JWKS client, no issuer/audience environment variables, no crypto dependency, and no pyjwt in requirements.txt. Re-verifying the signature here would mean shipping all of that for a check that already passed at the edge, against anchors we do not hold. What remains is the one job the app still owns: role gating.

# auth.py — a header-trust reader, not a verifier (the gateway already checked the JWT)
def current_user(request: Request) -> dict:
    raw = request.headers.get("x-dodil-user")   # {"sub","email","connection","app_roles"}
    ...                                          # + permissions read off x-dodil-user-jwt
    raise HTTPException(401, "end-user login required — no X-Dodil-User from the gateway")
 
def require_permission(perm: str):
    """Gate a route on a pool permission: Depends(require_permission("itsm:change:approve"))."""
    def _dep(user: dict = Depends(current_user)) -> dict:
        if perm not in user["permissions"] and perm not in user["roles"]:
            raise HTTPException(403, f"missing permission: {perm}")
        return user
    return _dep

The gate audit — four permissions across seven components

ITSM permissions are namespaced <module>:<object>:<verb>, so one customer pool can carry every ERP module's roles without collision — itsm:change:approve is not crm:change:approve. Auditing all seven components left exactly four gates standing:

PermissionGatesWhy this one and not the rest
itsm:change:approvechange-management: POST /changes/{id}/assess, POST /changes/{id}/transition, POST /change-policiesaccepting the risk of a production change. change_approvals.decided_by records the gateway-vouched user
itsm:major:declaremajor-incident: POST /major/declaredeclaring a major incident pages the org. bridges.declared_by records who
itsm:incident:resolvemajor-incident: POST /major/bridges/{id}/statethe only route in the module that writes incidents.state='resolved' — which is what stops this clock
itsm:cmdb:rebuildcmdb-blast-radius: POST /graph/assembleTRUNCATEs impacts + service_map and DROP+CREATEs both graphs; every other component's blast radius is read out of what it leaves behind

Four of the seven components ended with zero gates, on purpose — itsm-core, itsm-incident-management, itsm-problem-management, and this one. CMDB CRUD, triage, clustering and running the SLA clock are the service desk's ordinary work. A permission that every agent on the desk must hold is ceremony, and ceremony is exactly what an auditor discounts: if everyone has it, it proves nothing about who did what. The audit therefore deleted three gates that earlier versions of these posts described — incidents:triage, problems:write, and cmdb:write on an idempotent per-CI precompute — and added one nobody had predicted, itsm:cmdb:rebuild, because the route it guards is the single most destructive operation in the module.

So SLA management carries no permission gate at all. Its CRUD and read routes take Depends(current_user) and nothing more: they need a signed-in human for attribution, not authorization. Notice which permission does the load-bearing work here anyway — itsm:incident:resolve, over in major-incident. Stopping the clock is the privileged act; reading it is not.

/sla/tick has no identity dependency at all — and that is the general rule

POST /sla/tick goes one step further than ungated. It carries no Depends(current_user) either, and that is deliberate enough to have its own comment in the shipped code:

# DELIBERATELY UNGATED — no `current_user`, no permission. /sla/tick is a MACHINE heartbeat
# called by the always-on pinned app's own loop or an external scheduler (see the module
# docstring), i.e. by the service account over a PLATFORM invoke, which carries no end-user
# and therefore no `X-Dodil-User`. A `Depends(current_user)` here would 401 the clock — the
# engine would stop and the breach flags would silently go stale. Exposure is controlled at
# the ingress (`public_invoke=false`), not by a user permission.

Follow the chain. Whichever scheduler pattern you picked, the caller is a service account over a platform invoke — X-Dodil-Auth-Source: platform, and no X-Dodil-User, because there is no end user. A current_user dependency on that route does not secure it; it 401s the clock. The engine stops, and the failure is silent in the worst possible way: every breach flag freezes at its last value, the board stays green, and nobody is paged. That is the same failure shape as the NULL opened_at outage above, reached by a different road — which is the tell that it is a category of mistake, not a slip.

The right control is the door, not the caller. Deploy with public_invoke=false so only a principal that can perform a platform invoke can reach the route at all, and let the ingress be the thing that says no. Asking for a badge the legitimate caller structurally cannot carry is not security; it is an outage with good intentions.

This generalises to every recompute route in every module — a rollup, a re-materialization, an embedding backfill, a nightly reconciliation. If a machine calls it, gate the door, not the caller.

The pool, created once

The pool is created once for the whole suite, with the role catalog those four gates check — the service desk's real org chart expressed as permissions:

You

Create a dodil-appid pool itsm-suite with email+password, and set its role catalog: a service-desk agent gets no special permissions; a change manager may approve changes; an incident commander may declare a major incident and resolve incidents; a CMDB admin may rebuild the graph.

Agent

Pool itsm-suite created — issuer https://appid.dodil.io/ihdiash/itsm-suite, audience pool:itsm-suite, email+password (local) enabled. Catalog set: agent holds NO permissions (the desk's ordinary work is ungated); change-manager = itsm:change:approve; incident-commander = itsm:major:declare + itsm:incident:resolve; cmdb-admin = itsm:cmdb:rebuild. A user's next token carries app_roles plus the catalog-expanded permissions claim the gates read.

agent holding no permissions is the point of the audit, not an oversight — the service desk does the overwhelming majority of the work in this module, and none of it needs a gate.

The two-plane rule is unchanged: the pool identifies the user; the app still reaches DataK3 through its own service account (sa_token.py mints and refreshes a client_credentials token for the pg-wire password). An app-user is never a bucket principal. Locally, with no gateway in front of uvicorn routes:app, opt in to a stub identity with DEV_ALLOW_ANON=1; the stub carries no permissions unless you grant them (DEV_USER_PERMISSIONS=itsm:change:approve,…), so gated routes stay gated on a laptop too. Today the pool is email+password (local); oauth/oidc/saml corporate SSO switch on per pool later with no app change. Full walkthroughs: App authentication and App roles.

Get the code

The package is a real download — code/itsm-sla-management/v1.tar. This post is a walkthrough of exactly those files; the tarball is source only (the image-mode itsm-sla-monitor deploy is in Step 5):

models.py          # SQLAlchemy — sla_definitions, incident_sla, sla_breaches + the itsm/core masters (stubbed)
routes.py          # FastAPI    — CRUD + the clock recompute (/sla/tick, idempotent) + one incident's clock + on-call rollup
db.py              # lazy engine + the ON CONFLICT upsert helper every route uses (shared, byte-identical)
sa_token.py        # mints + refreshes the service-account client_credentials token used as the pg password
auth.py            # header-trust role gate — reads what the gateway injected. NO verifier, no JWKS (shared)
.env.example       # BUCKET + PG_HOST/PG_PORT + DODIL_TOKEN + the clock knobs. No issuer, no audience.
requirements.txt   # sqlalchemy, psycopg[binary], fastapi, uvicorn, pydantic, httpx   (no pyjwt)
README.md          # what it is, how to run it
EXTENDING.md       # the pattern for adding a workflow route
PLATFORM.md        # the platform invariants you COPY rather than generate — identical in every package

Two of those deserve a note. auth.py no longer verifies anything — the JWKS client, the issuer/audience config and the pyjwt dependency are all gone, because the gateway did that work at the edge. And PLATFORM.md ships inside the tar so that whoever downloads the package gets the rules along with the code: every line in it is a scar from a real failure on this platform, and the six integration bugs on this page are the most recent additions to that list.

Run it — point .env at your bucket, create the tables from the models, serve:

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
 
cp .env.example .env      # then set DODIL_TOKEN (your `dodil auth login` token)
# BUCKET defaults to "itsm"; create it once (Step 1), then create the tables from the models:
python -c "import db, models; models.Base.metadata.create_all(db.engine)"
 
# no gateway in front of a laptop, so opt in to a stub identity (NEVER set this in a deployed env):
export DEV_ALLOW_ANON=1
 
uvicorn routes:app --reload
# POST /sla_definitions, /incidents, /groups, /services
# POST /sla/tick · GET /incidents/{id}/sla · GET /sla/oncall

models.Base.metadata.create_all is the ORM tab's every class at once — the same tables Steps 1–4 built by CLI, created from the natural-key models with no migration tool.

How the pillars map

This skill is deliberately one pillar — SQL. No graph, no vector, no model. That's what makes it the cheap, auditable, always-provable part of the ITSM.

JobThe usual stackOn DataK3
SLA policy (targets per priority)Vendor config screensla_definitions rows you can read + tune
Per-incident clock + due datesBlack-box platform timeropened_at + to_minutes(target) — auditable SQL
"Which tickets breach in the next hour?"Overnight ETL → warehouse dashboardnow() > resolve_due over live rows, read-your-writes
Escalation routingWorkflow-engine rulesJOIN the breach to groups.manager
Point your own tools at itPer-system drivers & credsdata connect — psql / BI straight at the same rows

One bucket, one bill, one auth context — and because the clock is deterministic arithmetic, the engine is the provably no-Models workload: k3.editor + ignite.app-developer, nothing more.

Customize — the decisions this skill asks you

Q1 · sla_targets — your response/resolve matrix

"What are your SLA targets — response and resolve minutes per priority?" The contractual knob. One active sla_definitions row per priority. Default is a ServiceNow-classic matrix — P1 15/240, P2 30/480, P3 60/1440, P4 120/2880 (response/resolve minutes). Change a number and every due date and breach re-derives on the next tick; the clock reads the policy, never a hard-coded constant.

Q2 · business_hours — 24×7 or a business calendar?

"Is the SLA clock 24×7, or does it pause outside business hours?"

  • false (default) → a 24×7 elapsed clock: due = opened_at + N minutes wall-clock. This is the provably k3.editor-only path and the one asserted in Test.
  • true → a business-calendar clock: the handler's add_business_minutes() advances only through Mon–Fri 09:00–17:00, so a ticket opened Friday evening isn't overdue Monday morning. Same three tables, the clock helper branches.

Q3 · escalation_levels — how many hops to management?

"How many escalation hops before a breach reaches management?" On a breached clock, escalation_level = LEAST(escalation_levels, 1 + hours_overdue) and escalated_to walks to the assignment group's manager / on-call. Default 2. Sets ESCALATION_LEVELS in itsm-sla-monitor and the Test assertion (the 5-hour-old P1 escalates to level 2).

Test

Every command below ran live against DataK3 on 2026-09-08, org IHDIASH, on the shared itsm bucket — with all seven ITSM components installed together, which is the only configuration that proves anything about a component that reads another's rows. The estate: 13 CIs, 14 typed edges, 4 business services, 5 groups, 13 incidents. The clock/breach/escalation SQL is proven query-by-query, the idempotency claim end-to-end in both directions (three identical ticks, and a bare re-INSERT still rejected 23505), and the retire/on-call fixes against a ticket the major-incident bridge actually closed. Real results are inline.

# 1) SLA policy seeded — one active row per priority
dodil data sql -b "$BUCKET" \
  "SELECT count(*) AS defs, sum(response_mins) AS resp, sum(resolve_mins) AS res
     FROM sla_definitions WHERE active"
#  defs = 4, resp = 225 (15+30+60+120), res = 5040 (240+480+1440+2880)
 
# 2) the tick over the whole estate — 6 open incidents, 2 breached, 1 escalated
curl -fsS -X POST "$APP_URL/sla/tick"
#  {"evaluated":6,"breaches":2,"escalations":1,"retired":0,"skipped_no_clock":0}
 
# 3) the 6h-old P1 breached its 240-minute resolve target and escalated to level 2
dodil data sql -b "$BUCKET" \
  "SELECT resolve_breached, escalation_level FROM incident_sla WHERE incident_id = 1006"
#  resolve_breached = true, escalation_level = 2
dodil data sql -b "$BUCKET" \
  "SELECT breach_id, kind, escalated_to, escalation_level FROM sla_breaches WHERE incident_id = 1006"
#  1006:resolve | resolve | [email protected] | 2
 
# 4) response and resolve are INDEPENDENT — INC1007 is nowhere near its resolve target but
#    blew its 60-minute RESPONSE target sitting in 'new'; a response miss does not escalate
dodil data sql -b "$BUCKET" \
  "SELECT response_breached, resolve_breached, escalation_level FROM incident_sla WHERE incident_id = 1007"
#  response_breached = true, resolve_breached = false, escalation_level = 0
 
# 5) idempotent — three consecutive ticks, byte-identical rows, no 23505
#    (and the counterfactual: a bare re-INSERT of '1006:resolve' raises UniqueViolation 23505)
dodil data sql -b "$BUCKET" \
  "SELECT (SELECT count(*) FROM incident_sla) AS clocks, (SELECT count(*) FROM sla_breaches) AS breaches"
#  clocks = 6, breaches = 2   (unchanged across all three runs — ON CONFLICT DO UPDATE)
 
# 6) the two components move together — after the major-incident bridge resolved INC1006,
#    the next tick retires its clock row and drops the escalation
curl -fsS -X POST "$APP_URL/sla/tick"
#  {"evaluated":5,"breaches":1,"escalations":0,"retired":1,"skipped_no_clock":0}
dodil data sql -b "$BUCKET" \
  "SELECT state, escalation_level FROM incident_sla WHERE incident_id = 1006"
#  state = resolved, escalation_level = 0     (was in_progress / 2 before the retire pass existed)
 
# 7) a resolved breach leaves the on-call rollup but STAYS in the audit log
dodil data sql -b "$BUCKET" \
  "SELECT count(*) AS logged FROM sla_breaches WHERE incident_id = 1006"
#  logged = 1     — the breach happened; the log keeps it. The rollup below no longer shows it.
 
# 8) the pure-SQL proof — the engine's SA carries exactly two roles, no model-user
dodil auth service-account roles "$SA_UUID"
#  ignite.app-developer, k3.editor   —   NO ignite.model-user
 
# 9) drop-in clients: same bucket, your own psql / BI tool
dodil data connect "$BUCKET" -o psql
#  postgresql://token:[email protected]:5432/itsm?sslmode=require

One-shot

With the DODIL MCP connected, paste this to scaffold the whole SLA engine at once:

Scaffold ITSM SLA management on DataK3 (one bucket, pure SQL — no model). Confirm each step.
 
1. (Standalone only — skip if itsm/core is present) Stub the masters the clock reads: incidents
   (key id: number, short_description, ci_id, service_id, state, priority, assignment_group, opened_at
   timestamp, resolved_at timestamp), services (key service_id), groups (key group_id: name, manager,
   email, on_call) — every non-key column nullable. Seed groups g-platform/g-dba/g-orders, one service,
   and three incidents: P1 INC1006 opened 2026-09-08 11:30 UTC in_progress on g-platform; P3 INC1007
   opened 14:30 and still new; P2 INC3001 opened 15:00 in_progress.
2. Create sla_definitions (key sla_id) and seed one active row per priority: P1 15/240, P2 30/480,
   P3 60/1440, P4 120/2880 (response/resolve minutes), 24x7.
3. Create incident_sla (key incident_id) and compute the clock for every open incident: response_due =
   opened_at + response_mins, resolve_due = opened_at + resolve_mins; response_breached (state='new' and
   now past response_due), resolve_breached (unresolved and now past resolve_due); escalation_level =
   LEAST(2, 1 + hours overdue). SKIP and COUNT any incident with a NULL opened_at instead of failing —
   one uncomputable row must never stop the recompute for the whole estate.
4. Create sla_breaches (key <incident_id>:<kind>) and write one row per breached clock, escalated_to =
   the assignment group's manager. Add a retire pass that closes out clock rows whose incident has since
   been resolved by another component. Roll up open breaches by escalated_to, JOINed to incidents so a
   resolved ticket leaves the rollup — the breach log itself is an audit record and keeps every row.
5. Every write is INSERT … ON CONFLICT (<pk>) DO UPDATE SET col = EXCLUDED.col — a bare re-INSERT of a
   committed key is rejected 23505, so a plain INSERT is not an upsert on re-write.
6. Deploy itsm-sla-monitor (image mode, own SA: k3.editor + ignite.app-developer ONLY — no model-user)
   whose /tick recomputes incident_sla + sla_breaches over the pg wire. /tick takes NO identity
   dependency — it is a machine heartbeat over a platform invoke; control exposure with
   public_invoke=false. There is no server-side scheduler: choose either an always-on pinned app
   (--reserved 1 --max-replicas 1, own loop) or an external scheduler, and say which.
7. Verify: sla_definitions = 4; a tick returns evaluated 6, breaches 2, escalations 1; INC1006
   resolve_breached + escalation_level 2 + a 1006:resolve breach escalated to [email protected];
   INC1007 response_breached with escalation_level 0; three consecutive ticks byte-identical
   (clocks=6, breaches=2); the SA has no ignite.model-user.

Ship it

The three SLA tables are declarative — the moment the rows land they're queryable over pg/bolt/grpc (data connect). The one workload is itsm-sla-monitor (Step 5): an image-mode Ignite app the platform builds on deploy. Remember that it does not run itself: Ignite is request-invoked and there is no server-side scheduler, so the deployment must pick either an always-on pinned app (--reserved 1 --max-replicas 1 with its own loop, never scaling to zero) or an external scheduler (cron or any orchestrator POSTing /sla/tick, leaving the app scale-to-zero but the heartbeat outside DODIL) — see the scheduler question. DODIL_SERVICE_ACCOUNT_ID is the cli-… serviceAccountId auth service-account create printed (not the uuid):

# IMAGE mode (Dockerfile built on deploy) — NOT --runtime python. Pure SQL: no ignite.model-user.
dodil ignite app deploy itsm-sla-monitor \
  --code ./sla-monitor --dockerfile-path Dockerfile \
  --port 8080 --health-path /healthz --allow-unauthenticated \
  --env BUCKET="$BUCKET" \
  --env DODIL_SERVICE_ACCOUNT_ID="$SA_ID" --env DODIL_SERVICE_ACCOUNT_SECRET="$SA_SECRET" \
  --env ESCALATION_LEVELS=2 --env BUSINESS_HOURS=false

The full supply chain — DODIL git → CI checks → a scanned registry image → versioning and rollback — is walked end to end in Ship a DODIL App.

Connect your tools

Everything this build wrote lives in one DataK3 bucket, reachable by your own stack — not just the CLI. data connect itsm prints the endpoints; point your tools straight at the same rows:

  • SQL over Postgres wire — psql, psycopg/asyncpg (Python), node-postgres (TS), or a BI tool (Grafana, Metabase) reading incident_sla / sla_breaches for a live SLA dashboard.

Full, live-validated walkthrough: Connect your tools.

The suite — seven components, one app

This package runs standalone (uvicorn routes:app) — that is what this post walks through, and the code/itsm-sla-management download is still exactly that. Deployed, the seven ITSM components compose into one app: itsm-suite-app is a single FastAPI with a router per component, one canonical models.py covering all 23 tables, and plain imports — no importlib loader — over one bucket (itsm) and one dodil-appid pool, so seven components mean one sign-in and one bill. Fetch it as code/itsm-suite-app. It ships by the ordinary git cycle (repo → CI → registry → CD): Ship a DODIL app. One app rather than seven is the ERP default; you split only for a stated reason — a public surface against a private engine, independent scaling, a distinct trust boundary — and ITSM has none of those.

That composition is not a packaging convenience, and this post is the evidence. The retire pass, the on-call JOIN, and the NULL opened_at guard all exist because the clock and the major-incident bridge were finally pointed at the same rows. Components validated separately are internally consistent and still wrong together — six times over, in one afternoon.

Conclusion

You now have the SLA engine of an ITSM on one DataK3 bucket: the contract as data (sla_definitions), a per-incident clock that flips response_breached / resolve_breached the moment now() passes a due date (incident_sla), and a breach log that escalates a missed P1 to the owning group's manager within one tick (sla_breaches). It reads itsm/core's incidents over one copy of the rows — no ETL, no stale warehouse — and because the clock is deterministic arithmetic, the itsm-sla-monitor engine is the provably no-Models workload: k3.editor + ignite.app-developer, nothing else.

Three things here outlive the SLA clock. A per-tick recompute must be idempotent — on DataK3 a plain INSERT works exactly once per key, so write ON CONFLICT … DO UPDATE or your engine dies on its second tick rather than its first. A per-tick recompute must be resilient to one bad row and must report what it skipped — a single NULL opened_at took down the breach flags for an entire estate, silently, and a green board is the worst possible failure mode. And a machine heartbeat takes no user identity: gate the door with public_invoke=false, never the caller, or you will 401 your own clock. All three came out of running the seven components on one bucket, which is the last lesson: components validated separately are internally consistent and still wrong together.

Next steps:

  • Build a ServiceNow-Style ITSM on DataK3 — the incident core, CMDB graph, and auto-triage this SLA clock runs alongside.
  • Compose the rest of the ITSM suite onto core: cmdb-blast-radius, incident-management, problem-management, change-management.