id: sla-service-credits-calculation
namespace: company.team
description: |
Monthly customer SLA credit run: merge incidents per service and region so overlapping
outages are not counted twice, remove contractual exclusions (scheduled maintenance announced
in advance, customer-caused incidents), compute each customer's uptime on the services and
regions they actually use, apply the credit schedule of their SLA tier with the contract cap,
split credits owed automatically from those that need a claim within the claim window, issue
the credit notes, reduce revenue, and lock the month.
inputs:
- id: month
type: STRING
displayName: Month (YYYY-MM)
defaults: "2026-09"
validator: ^\d{4}-(0[1-9]|1[0-2])$
- id: scenario
type: SELECT
displayName: Demo scenario
description: ISSUES adds an incident with no end time, an incident that ends
before it starts, and a customer subscribed to a service that is not in
the catalog, which must stop the run.
values:
- CLEAN
- ISSUES
defaults: CLEAN
- id: maintenance_notice_hours
type: INT
displayName: Maintenance notice (hours)
description: Scheduled maintenance announced at least this long before it starts
is excluded from downtime.
defaults: 72
- id: approved_by
type: STRING
displayName: Approved by
description: Needed when total credits exceed 25,000 or any customer reaches the
contract cap.
defaults: ""
- id: notify_slack
type: BOOL
displayName: Notify Slack
description: Post a failure alert to Slack. Needs the SLACK_WEBHOOK_URL secret.
defaults: false
variables:
lock_key: sla_credits
pack_dir: "sla-credits/{{ inputs.month }}"
concurrency:
limit: 1
tasks:
- id: state
type: io.kestra.plugin.core.output.OutputValues
values:
closed: "{{ (kv(vars.lock_key, errorOnMissing=false) ?? {'closed': []}) |
jq('.closed') | first | toJson }}"
- id: period_gate
type: io.kestra.plugin.core.flow.If
condition: "{{ fromJson(outputs.state.values.closed) contains inputs.month }}"
then:
- id: already_closed
type: io.kestra.plugin.core.execution.Fail
errorMessage: "SLA credits for {{ inputs.month }} are already issued."
- id: credits
type: io.kestra.plugin.jdbc.duckdb.Queries
description: >
One DuckDB session: services, SLA tiers, contracts, subscriptions,
incidents and claims (demo generator or your incident tool, status page
and billing), merged downtime, uptime per customer, credits, credit notes,
the revenue journal and checks.
outputFiles:
- customers
- downtime
- credit_notes
- journal
- blockers
fetchType: FETCH_ONE
sql: |
SET VARIABLE m0 = TIMESTAMP '{{ inputs.month }}-01 00:00:00';
SET VARIABLE m1 = (TIMESTAMP '{{ inputs.month }}-01 00:00:00' + INTERVAL 1 MONTH);
SET VARIABLE minutes = date_diff('minute', getvariable('m0'), getvariable('m1'));
-- =======================================================================
-- Catalog, SLA tiers and their credit schedule. Replace with your SLA terms.
-- =======================================================================
CREATE TABLE services (service VARCHAR); INSERT INTO services VALUES ('API'), ('DASHBOARD'), ('DATA_EXPORT');
CREATE TABLE tiers (tier VARCHAR, below_uptime DOUBLE, credit_pct DOUBLE, cap_pct DOUBLE, auto_credit BOOLEAN, claim_days INT);
INSERT INTO tiers VALUES
('ENTERPRISE', 0.9995, 0.10, 0.15, true, NULL), ('ENTERPRISE', 0.999, 0.25, 0.15, true, NULL), ('ENTERPRISE', 0.99, 0.50, 0.15, true, NULL),
('BUSINESS', 0.999, 0.10, 0.30, false, 30), ('BUSINESS', 0.99, 0.25, 0.30, false, 30), ('BUSINESS', 0.95, 0.30, 0.30, false, 30),
('STANDARD', 0.995, 0.05, 0.10, false, 15), ('STANDARD', 0.98, 0.10, 0.10, false, 15);
-- Customers, their tier, region and monthly fee per service. Replace with billing.
CREATE TABLE contracts AS
SELECT 'CUS-' || lpad(i::VARCHAR, 3, '0') AS customer, ['ENTERPRISE', 'BUSINESS', 'BUSINESS', 'STANDARD', 'STANDARD'][1 + i % 5] AS tier,
['eu-west', 'us-east', 'ap-south'][1 + i % 3] AS region,
CASE WHEN i % 5 = 0 THEN 'EUR' ELSE 'USD' END AS currency
FROM range(1, 81) t(i);
CREATE TABLE subscriptions AS
SELECT customer, s.service, round(CASE tier WHEN 'ENTERPRISE' THEN 9000 WHEN 'BUSINESS' THEN 2500 ELSE 600 END * w * (0.8 + (hash(customer) % 40)::INT / 100.0), 2) AS monthly_fee
FROM contracts, (VALUES ('API', 0.6), ('DASHBOARD', 0.3), ('DATA_EXPORT', 0.1)) s(service, w)
WHERE s.service <> 'DATA_EXPORT' OR hash(customer) % 2 = 0;
{% if inputs.scenario == 'ISSUES' %}
INSERT INTO subscriptions VALUES ('CUS-004', 'ML_SCORING', 1200);
{% endif %}
-- =======================================================================
-- Incidents from the incident tool, with the regions affected. Two API
-- incidents overlap in us-east; one maintenance window was announced late.
-- =======================================================================
CREATE TABLE incidents (incident VARCHAR, service VARCHAR, region VARCHAR, started_at TIMESTAMP, ended_at TIMESTAMP, kind VARCHAR, announced_at TIMESTAMP, cause VARCHAR);
INSERT INTO incidents VALUES
('INC-301', 'API', 'us-east', getvariable('m0') + INTERVAL '3 days 14 hours', getvariable('m0') + INTERVAL '3 days 16 hours 10 minutes', 'OUTAGE', NULL, 'Database failover'),
('INC-302', 'API', 'us-east', getvariable('m0') + INTERVAL '3 days 15 hours 30 minutes', getvariable('m0') + INTERVAL '3 days 17 hours', 'OUTAGE', NULL, 'Cache stampede during recovery'),
('INC-305', 'DASHBOARD', 'eu-west', getvariable('m0') + INTERVAL '11 days 9 hours', getvariable('m0') + INTERVAL '11 days 9 hours 25 minutes', 'OUTAGE', NULL, 'Bad deploy'),
('MNT-01', 'API', 'eu-west', getvariable('m0') + INTERVAL '14 days 2 hours', getvariable('m0') + INTERVAL '14 days 4 hours', 'MAINTENANCE', getvariable('m0') + INTERVAL '9 days', 'Database upgrade'),
('MNT-02', 'API', 'ap-south', getvariable('m0') + INTERVAL '20 days 1 hour', getvariable('m0') + INTERVAL '20 days 2 hours 30 minutes', 'MAINTENANCE', getvariable('m0') + INTERVAL '19 days 20 hours', 'Network change, announced 5 hours ahead'),
('INC-311', 'DATA_EXPORT', 'ap-south', getvariable('m0') + INTERVAL '22 days 6 hours', getvariable('m0') + INTERVAL '23 days 1 hour', 'OUTAGE', NULL, 'Storage quota'),
('INC-314', 'API', 'eu-west', getvariable('m0') + INTERVAL '26 days 18 hours', getvariable('m0') + INTERVAL '26 days 18 hours 7 minutes', 'OUTAGE', NULL, 'Certificate expiry');
-- A customer-caused incident: CUS-017 exhausted its own rate limit.
CREATE TABLE customer_caused (incident VARCHAR, customer VARCHAR); INSERT INTO customer_caused VALUES ('INC-305', 'CUS-017');
{% if inputs.scenario == 'ISSUES' %}
INSERT INTO incidents VALUES
('INC-320', 'API', 'eu-west', getvariable('m0') + INTERVAL '28 days', NULL, 'OUTAGE', NULL, 'Still open'),
('INC-321', 'DASHBOARD', 'us-east', getvariable('m0') + INTERVAL '12 days 5 hours', getvariable('m0') + INTERVAL '12 days 4 hours', 'OUTAGE', NULL, 'Times swapped');
{% endif %}
-- Claims received for the month (for tiers that need a claim).
CREATE TABLE claims AS SELECT customer, (getvariable('m1') + INTERVAL 6 DAY)::DATE AS claimed_on FROM contracts WHERE tier <> 'ENTERPRISE' AND region = 'us-east' AND hash(customer) % 3 <> 0;
-- =======================================================================
-- Checks.
-- =======================================================================
CREATE TABLE blockers AS
SELECT 'OPEN_INCIDENT' AS code, incident AS ref, service || ' ' || region || ' incident has no end time; downtime cannot be measured' AS detail FROM incidents WHERE ended_at IS NULL
UNION ALL SELECT 'END_BEFORE_START', incident, 'Ends ' || ended_at || ' before it starts ' || started_at FROM incidents WHERE ended_at < started_at
UNION ALL SELECT 'UNKNOWN_SERVICE', customer || ' ' || service, 'Subscription to a service that is not in the catalog' FROM subscriptions WHERE service NOT IN (SELECT service FROM services);
-- =======================================================================
-- Countable downtime per service and region: exclusions removed, clipped
-- to the month, overlapping intervals merged (gaps-and-islands).
-- =======================================================================
CREATE TABLE countable AS
SELECT incident, service, region, greatest(started_at, getvariable('m0')) AS s, least(ended_at, getvariable('m1')) AS e
FROM incidents
WHERE ended_at IS NOT NULL AND ended_at > started_at AND started_at < getvariable('m1') AND ended_at > getvariable('m0')
AND NOT (kind = 'MAINTENANCE' AND announced_at <= started_at - INTERVAL ({{ inputs.maintenance_notice_hours }}) HOUR);
CREATE TABLE merged AS
WITH o AS (
SELECT *, max(e) OVER (PARTITION BY service, region ORDER BY s ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end FROM countable
), g AS (
SELECT *, sum(CASE WHEN prev_end IS NULL OR s > prev_end THEN 1 ELSE 0 END) OVER (PARTITION BY service, region ORDER BY s) AS grp FROM o
)
SELECT service, region, min(s) AS s, max(e) AS e, string_agg(incident, '+' ORDER BY s) AS incidents FROM g GROUP BY service, region, grp;
-- Customer-caused incidents are excluded for that customer only.
CREATE TABLE customer_downtime AS
SELECT su.customer, su.service, c.region, su.monthly_fee, c.tier,
coalesce(sum(date_diff('minute', m.s, m.e)), 0) AS raw_minutes,
coalesce(sum(CASE WHEN EXISTS (SELECT 1 FROM customer_caused cc WHERE cc.customer = su.customer AND m.incidents LIKE '%' || cc.incident || '%')
THEN 0 ELSE date_diff('minute', m.s, m.e) END), 0) AS minutes
FROM subscriptions su JOIN contracts c USING (customer) LEFT JOIN merged m ON m.service = su.service AND m.region = c.region
WHERE su.service IN (SELECT service FROM services)
GROUP BY ALL;
-- Uptime and credit per customer and service, capped per contract.
CREATE TABLE per_service AS
SELECT d.*, 1 - d.minutes / getvariable('minutes')::DOUBLE AS uptime,
coalesce((SELECT max(t.credit_pct) FROM tiers t WHERE t.tier = d.tier AND 1 - d.minutes / getvariable('minutes')::DOUBLE < t.below_uptime), 0) AS credit_pct
FROM customer_downtime d;
CREATE TABLE customers AS
SELECT p.customer, p.tier, p.region, c.currency, sum(p.monthly_fee) AS monthly_fee,
string_agg(p.service || ' ' || round(100 * p.uptime, 3) || '%', ', ' ORDER BY p.service) AS uptime_by_service,
min(p.uptime) AS worst_uptime, sum(p.monthly_fee * p.credit_pct) AS credit_before_cap,
(SELECT max(cap_pct) FROM tiers t WHERE t.tier = p.tier) * sum(p.monthly_fee) AS cap,
(SELECT bool_or(auto_credit) FROM tiers t WHERE t.tier = p.tier) AS auto_credit,
(SELECT max(claim_days) FROM tiers t WHERE t.tier = p.tier) AS claim_days,
(SELECT min(claimed_on) FROM claims cl WHERE cl.customer = p.customer) AS claimed_on
FROM per_service p JOIN contracts c USING (customer) GROUP BY p.customer, p.tier, p.region, c.currency;
ALTER TABLE customers ADD COLUMN credit DOUBLE; ALTER TABLE customers ADD COLUMN status VARCHAR;
UPDATE customers SET credit = round(least(credit_before_cap, cap), 2);
UPDATE customers SET status = CASE WHEN credit = 0 THEN 'NO_CREDIT'
WHEN auto_credit THEN 'AUTO_CREDIT'
WHEN claimed_on IS NOT NULL AND claimed_on <= (getvariable('m1') + INTERVAL (claim_days) DAY)::DATE THEN 'CLAIMED'
ELSE 'AWAITING_CLAIM' END;
-- Credit notes for credits owed now; awaiting claims are disclosed, not issued.
CREATE TABLE credit_notes AS
SELECT 'CN-' || strftime(getvariable('m0'), '%Y%m') || '-' || customer AS credit_note, customer, currency, credit AS amount,
'SLA credit {{ inputs.month }}: ' || uptime_by_service AS memo
FROM customers WHERE status IN ('AUTO_CREDIT', 'CLAIMED') ORDER BY customer;
CREATE TABLE journal AS
SELECT currency, '4900' AS account, 'Revenue reduction: SLA credits' AS account_name, round(sum(amount), 2) AS debit, 0.0 AS credit FROM credit_notes GROUP BY currency
UNION ALL SELECT currency, '2180', 'Credits payable to customers', 0.0, round(sum(amount), 2) FROM credit_notes GROUP BY currency;
COPY (SELECT customer, tier, region, monthly_fee, uptime_by_service, round(100 * worst_uptime, 3) AS worst_uptime_pct, round(credit_before_cap, 2) AS credit_before_cap,
round(cap, 2) AS cap, credit, status, claimed_on FROM customers ORDER BY credit DESC, customer) TO '{{ outputFiles.customers }}' (HEADER, DELIMITER ',');
COPY (SELECT service, region, s AS started_at, e AS ended_at, date_diff('minute', s, e) AS minutes, incidents FROM merged ORDER BY s) TO '{{ outputFiles.downtime }}' (HEADER, DELIMITER ',');
COPY credit_notes TO '{{ outputFiles.credit_notes }}' (HEADER, DELIMITER ',');
COPY journal TO '{{ outputFiles.journal }}' (HEADER, DELIMITER ',');
COPY blockers TO '{{ outputFiles.blockers }}' (HEADER, DELIMITER ',');
SELECT
(SELECT string_agg(service || '/' || region || ' ' || date_diff('minute', s, e) || ' min (' || incidents || ')', ', ' ORDER BY s) FROM merged) AS downtime,
(SELECT sum(date_diff('minute', s, e)) FROM countable)::INT AS minutes_before_merge,
(SELECT sum(date_diff('minute', s, e)) FROM merged)::INT AS minutes_after_merge,
(SELECT string_agg(incident || ' (' || cause || ')', ', ') FROM incidents WHERE kind = 'MAINTENANCE' AND incident NOT IN (SELECT incident FROM countable)) AS excluded_maintenance,
(SELECT string_agg(incident || ' (' || cause || ')', ', ') FROM incidents WHERE kind = 'MAINTENANCE' AND incident IN (SELECT incident FROM countable)) AS counted_maintenance,
(SELECT count(*) FROM customers WHERE credit > 0)::INT AS customers_with_credit,
(SELECT round(sum(credit), 2) FROM customers WHERE status IN ('AUTO_CREDIT', 'CLAIMED'))::DOUBLE AS credits_issued,
(SELECT round(coalesce(sum(credit), 0), 2) FROM customers WHERE status = 'AWAITING_CLAIM')::DOUBLE AS awaiting_claim,
(SELECT string_agg(status || ' ' || n, ', ') FROM (SELECT status, count(*) AS n FROM customers WHERE credit > 0 GROUP BY status)) AS by_status,
(SELECT count(*) FROM customers WHERE credit > 0 AND credit_before_cap > cap)::INT AS capped,
(SELECT coalesce(string_agg(customer || ' ' || uptime_by_service, '; '), '') FROM customers WHERE customer IN (SELECT customer FROM customer_caused)) AS customer_caused_detail,
(SELECT count(*) FROM blockers)::INT AS blockers,
(SELECT coalesce(string_agg(code || ' ' || ref || ': ' || detail, '; ' ORDER BY code, ref), '') FROM blockers) AS blocker_detail;
- id: result
type: io.kestra.plugin.core.output.OutputValues
values:
r: "{{ (outputs.credits.outputs | last).row | toJson }}"
- id: log_run
type: io.kestra.plugin.core.log.Log
message: |
SLA credits {{ inputs.month }} ({{ inputs.scenario }}).
{{ outputs.result.values.r | jq('"Downtime after merging overlaps (\(.minutes_before_merge) -> \(.minutes_after_merge) min): \(.downtime). Maintenance excluded: \(.excluded_maintenance). Maintenance counted (late notice): \(.counted_maintenance)."') | first }}
{{ outputs.result.values.r | jq('"\(.customers_with_credit) customers owed a credit (\(.by_status)), \(.capped) at the contract cap. Issued \(.credits_issued), awaiting a claim \(.awaiting_claim). Customer-caused exclusion: \(.customer_caused_detail)."') | first }}
{{ outputs.result.values.r | jq('"Blockers: \(.blockers). \(.blocker_detail)"') | first }}
- id: evidence
type: io.kestra.plugin.core.namespace.UploadFiles
namespace: "{{ flow.namespace }}"
filesMap:
"{{ render(vars.pack_dir) }}/customers.csv": "{{ outputs.credits.outputFiles.customers }}"
"{{ render(vars.pack_dir) }}/downtime.csv": "{{ outputs.credits.outputFiles.downtime }}"
"{{ render(vars.pack_dir) }}/credit-notes.csv": "{{ outputs.credits.outputFiles.credit_notes }}"
"{{ render(vars.pack_dir) }}/journal.csv": "{{ outputs.credits.outputFiles.journal }}"
"{{ render(vars.pack_dir) }}/blockers.csv": "{{ outputs.credits.outputFiles.blockers }}"
- id: decide
type: io.kestra.plugin.core.flow.Switch
value: >-
{%- set r = outputs.result.values.r -%} {%- if (r | jq('.blockers > 0') |
first) -%}BLOCKED {%- elseif (r | jq('.credits_issued > 25000 or .capped >
0') | first) and inputs.approved_by == '' -%}NEEDS_APPROVAL {%- else
-%}ISSUE{%- endif -%}
cases:
ISSUE:
- id: lock_month
type: io.kestra.plugin.core.kv.Set
key: "{{ vars.lock_key }}"
kvType: JSON
value: >-
{{ {'old': (kv(vars.lock_key, errorOnMissing=false) ?? {'closed':
[], 'months': {}}), 'm': inputs.month, 'r':
fromJson(outputs.result.values.r), 'by': inputs.approved_by}
| jq('{closed: (.old.closed + [.m]), months: (.old.months + {(.m): {issued: .r.credits_issued, awaiting_claim: .r.awaiting_claim, approved_by: .by}})}') | first | toJson }}
- id: log_issued
type: io.kestra.plugin.core.log.Log
message: "SLA credits for {{ inputs.month }} issued: {{ outputs.result.values.r
| jq('.credits_issued') | first }}. Credit notes in {{
render(vars.pack_dir) }}/credit-notes.csv."
NEEDS_APPROVAL:
- id: approval_required
type: io.kestra.plugin.core.execution.Fail
errorMessage: >-
Credits of {{ outputs.result.values.r | jq('.credits_issued') |
first }}, {{ outputs.result.values.r | jq('.capped') | first }}
customer(s) at the contract cap. Review {{ render(vars.pack_dir)
}}/customers.csv and run again with approved_by.
defaults:
- id: blocked
type: io.kestra.plugin.core.execution.Fail
errorMessage: "SLA credits for {{ inputs.month }} cannot be computed: {{
outputs.result.values.r | jq('.blocker_detail') | first }}"
errors:
- id: alert_flow_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
runIf: '{{ inputs.notify_slack }}'
description: A blocked month, a missing approval or a failed calculation must
reach a person, not only the execution list.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'SLA credits run for ' ~ inputs.month ~ ' failed (execution ' ~ execution.id ~ '). The month is blocked by a data check, waits for approval or could not be calculated. Check the execution logs.'} | toJson }}