Apache SeaTunnel Job Failure and Zeta Cluster Health Sentinel
Audits Apache SeaTunnel Zeta Engine REST API for failed streaming or batch jobs and alerts on pipeline stalls.
Compute current ratio, debt-to-equity and margins from SEC XBRL filings in DuckDB and get Slack alerts on thresholds and sharp moves.
id: sec-xbrl-ratio-monitor
namespace: company.team
inputs:
- id: tickers
type: ARRAY
itemType: STRING
displayName: Watchlist
description: Tickers of the companies to monitor. Keep the trigger's `tickers`
in sync for automatic runs.
defaults:
- AAPL
- MSFT
- NVDA
- KO
- WMT
- id: current_ratio_min
type: FLOAT
displayName: Minimum current ratio
description: Alert when current assets / current liabilities falls below this.
defaults: 1.0
- id: debt_to_equity_max
type: FLOAT
displayName: Maximum debt-to-equity
description: Alert when interest-bearing debt / equity rises above this, or when
equity is negative.
defaults: 2.0
- id: current_ratio_move
type: FLOAT
displayName: Current ratio move
description: Alert when the current ratio changes by at least this much since
the previous quarter end.
defaults: 0.3
- id: debt_to_equity_move
type: FLOAT
displayName: Debt-to-equity move
description: Alert when debt-to-equity changes by at least this much since the
previous quarter end.
defaults: 0.5
- id: margin_move_pp
type: FLOAT
displayName: Margin move (percentage points)
description: Alert when the gross or operating margin changes by at least this
many points against the same quarter a year earlier.
defaults: 5
tasks:
- id: companies
type: io.kestra.plugin.edgar.company.Lookup
description: Resolves the watchlist to SEC CIKs and company names.
# SEC requires a User-Agent with your name or company and a contact email. Set it once in the KV store.
userAgent: "{{ kv('SEC_EDGAR_USER_AGENT', errorOnMissing=false) ?? '' }}"
tickers: "{{ inputs.tickers }}"
fetchType: FETCH
- id: each_company
type: io.kestra.plugin.core.flow.Loop
values: "{{ inputs.tickers }}"
# One at a time: SEC allows 10 requests per second across all your tools.
concurrencyLimit: 1
outputs:
- id: facts
type: STRING
value: "{{ outputs.facts_json.uri }}"
tasks:
- id: facts
type: io.kestra.plugin.edgar.xbrl.CompanyFacts
description: Three years of the us-gaap facts the ratios need.
userAgent: "{{ kv('SEC_EDGAR_USER_AGENT', errorOnMissing=false) ?? '' }}"
ticker: "{{ item.value }}"
taxonomies:
- us-gaap
units:
- USD
# No form filter: SEC can put a period's `frame` on a later 8-K that recasts it (Coca-Cola's 2023
# year-end balance sheet), and the ratios keep only framed values.
startDate: "{{ now() | dateAdd(-3, 'YEARS') | date('yyyy-MM-dd') }}"
concepts:
- AssetsCurrent
- LiabilitiesCurrent
- StockholdersEquity
- StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest
- LongTermDebt
- LongTermDebtAndCapitalLeaseObligationsIncludingCurrentMaturities
- LongTermDebtNoncurrent
- LongTermDebtCurrent
- LongTermDebtAndCapitalLeaseObligations
- LongTermDebtAndCapitalLeaseObligationsCurrent
- ShortTermBorrowings
- CommercialPaper
- Revenues
- RevenueFromContractWithCustomerExcludingAssessedTax
- SalesRevenueNet
- RevenueFromContractWithCustomerIncludingAssessedTax
- GrossProfit
- CostOfRevenue
- OperatingIncomeLoss
- id: facts_json
type: io.kestra.plugin.serdes.json.IonToJson
from: "{{ outputs.facts.uri }}"
- id: ratios
type: io.kestra.plugin.jdbc.duckdb.Query
description: One row per company and quarter with the four ratios.
inputFiles: |
{
{%- for company in outputs.each_company.outputs %}
"{{ company.item.value }}.jsonl": "{{ company.outputs.facts }}"{{ loop.last ? '' : ',' }}
{%- endfor %}
}
fetchType: STORE
sql: |
WITH facts AS (
SELECT regexp_extract(filename, '([^/]+)\.jsonl$', 1) AS ticker, concept,
CAST(value AS DOUBLE) AS value,
CAST("start" AS DATE) AS start_date, CAST("end" AS DATE) AS end_date,
CAST(filed AS DATE) AS filed, frame
FROM read_json_auto('{{ workingDir }}/*.jsonl', filename = true)
-- SEC sets `frame` on one value per concept and calendar period: the latest filed. The same
-- period reappears unframed as a comparative in later filings, and its fiscalYear/fiscalPeriod
-- describe that later filing, so periods are keyed on their dates and only framed values kept.
WHERE frame IS NOT NULL
),
dedup AS (
SELECT * FROM facts
QUALIFY row_number() OVER (PARTITION BY ticker, concept, frame ORDER BY filed DESC) = 1
),
flow_items AS (
SELECT * FROM dedup
WHERE start_date IS NOT NULL
AND concept IN ('Revenues', 'RevenueFromContractWithCustomerExcludingAssessedTax', 'SalesRevenueNet',
'RevenueFromContractWithCustomerIncludingAssessedTax', 'GrossProfit', 'CostOfRevenue',
'OperatingIncomeLoss')
),
quarters AS (
SELECT ticker, concept, start_date, end_date, value, false AS derived
FROM flow_items WHERE end_date - start_date BETWEEN 80 AND 100
),
years AS (
SELECT ticker, concept, start_date, end_date, value
FROM flow_items WHERE end_date - start_date BETWEEN 350 AND 380
),
-- A 10-K reports the fourth fiscal quarter of flow items only inside the full year: derive it as the
-- year minus its three reported quarters, when they tile the year from its first day. Balance-sheet
-- items never need this, the 10-K has the year-end balance sheet itself.
fourth_quarters AS (
SELECT y.ticker, y.concept, max(q.end_date) + 1 AS start_date, y.end_date,
y.value - sum(q.value) AS value, true AS derived
FROM years y
JOIN quarters q ON q.ticker = y.ticker AND q.concept = y.concept
AND q.start_date >= y.start_date AND q.end_date < y.end_date
GROUP BY y.ticker, y.concept, y.start_date, y.end_date, y.value
HAVING count(*) = 3
AND min(q.start_date) = y.start_date
AND sum(q.end_date - q.start_date + 1) = max(q.end_date) - y.start_date + 1
AND y.end_date - max(q.end_date) BETWEEN 80 AND 100
),
durations AS (
SELECT * FROM quarters
UNION ALL
SELECT f.* FROM fourth_quarters f
WHERE NOT EXISTS (SELECT 1 FROM quarters q
WHERE q.ticker = f.ticker AND q.concept = f.concept AND q.end_date = f.end_date)
),
income AS (
SELECT ticker, end_date,
max(value) FILTER (WHERE concept = 'Revenues') AS revenues,
max(value) FILTER (WHERE concept = 'RevenueFromContractWithCustomerExcludingAssessedTax') AS revenue_contracts,
max(value) FILTER (WHERE concept = 'SalesRevenueNet') AS sales_revenue_net,
max(value) FILTER (WHERE concept = 'RevenueFromContractWithCustomerIncludingAssessedTax') AS revenue_contracts_incl_tax,
max(value) FILTER (WHERE concept = 'GrossProfit') AS gross_profit_reported,
max(value) FILTER (WHERE concept = 'CostOfRevenue') AS cost_of_revenue,
max(value) FILTER (WHERE concept = 'OperatingIncomeLoss') AS operating_income,
bool_or(derived) AS derived_q4
FROM durations GROUP BY ticker, end_date
),
balance AS (
SELECT ticker, end_date, any_value(replace(frame, 'I', '')) AS calendar_quarter,
max(value) FILTER (WHERE concept = 'AssetsCurrent') AS assets_current,
max(value) FILTER (WHERE concept = 'LiabilitiesCurrent') AS liabilities_current,
max(value) FILTER (WHERE concept = 'StockholdersEquity') AS equity_parent,
max(value) FILTER (WHERE concept = 'StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest') AS equity_with_nci,
max(value) FILTER (WHERE concept = 'LongTermDebt') AS ltd_total,
max(value) FILTER (WHERE concept = 'LongTermDebtAndCapitalLeaseObligationsIncludingCurrentMaturities') AS ltd_leases_total,
max(value) FILTER (WHERE concept = 'LongTermDebtNoncurrent') AS ltd_noncurrent,
max(value) FILTER (WHERE concept = 'LongTermDebtCurrent') AS ltd_current,
max(value) FILTER (WHERE concept = 'LongTermDebtAndCapitalLeaseObligations') AS ltd_leases_noncurrent,
max(value) FILTER (WHERE concept = 'LongTermDebtAndCapitalLeaseObligationsCurrent') AS ltd_leases_current,
max(value) FILTER (WHERE concept = 'ShortTermBorrowings') AS short_term_borrowings,
max(value) FILTER (WHERE concept = 'CommercialPaper') AS commercial_paper
FROM dedup WHERE start_date IS NULL GROUP BY ticker, end_date
),
components AS (
SELECT coalesce(b.ticker, i.ticker) AS ticker, coalesce(b.end_date, i.end_date) AS period_end,
b.calendar_quarter, coalesce(i.derived_q4, false) AS derived_q4,
coalesce(i.revenues, i.revenue_contracts, i.sales_revenue_net, i.revenue_contracts_incl_tax) AS revenue,
i.gross_profit_reported, i.cost_of_revenue, i.operating_income,
b.assets_current, b.liabilities_current,
coalesce(b.equity_parent, b.equity_with_nci) AS equity,
coalesce(b.ltd_total, b.ltd_leases_total,
b.ltd_noncurrent + coalesce(b.ltd_current, 0),
b.ltd_leases_noncurrent + coalesce(b.ltd_leases_current, 0)) AS long_term_debt,
-- Commercial paper is a kind of short-term borrowing: never add both.
coalesce(b.short_term_borrowings, b.commercial_paper, 0) AS short_term_debt
FROM balance b FULL OUTER JOIN income i ON i.ticker = b.ticker AND i.end_date = b.end_date
)
SELECT ticker, period_end, calendar_quarter, derived_q4,
round(assets_current / nullif(liabilities_current, 0), 3) AS current_ratio,
-- Debt-to-equity (interest-bearing debt / equity); not meaningful when equity is negative.
round(CASE WHEN equity > 0 AND long_term_debt IS NOT NULL
THEN (long_term_debt + short_term_debt) / equity END, 3) AS debt_to_equity,
equity <= 0 AS negative_equity,
-- CostOfGoodsAndServicesSold can be one component of cost (McDonald's food and paper), so only
-- CostOfRevenue, total cost of revenue by definition, derives a missing gross profit.
round(100 * coalesce(gross_profit_reported, revenue - cost_of_revenue) / nullif(revenue, 0), 2) AS gross_margin_pct,
gross_profit_reported IS NULL AND cost_of_revenue IS NOT NULL AS gross_profit_derived,
round(100 * operating_income / nullif(revenue, 0), 2) AS operating_margin_pct,
round(revenue / 1e6, 1) AS revenue_musd,
round(equity / 1e6, 1) AS equity_musd
FROM components
ORDER BY ticker, period_end
- id: ratio_table
type: io.kestra.plugin.serdes.csv.IonToCsv
from: "{{ outputs.ratios.uri }}"
- id: freshness
type: io.kestra.plugin.jdbc.duckdb.Query
description: On a filing-triggered run, lists the companies whose new report is
not in SEC's company facts yet.
runIf: "{{ trigger is defined and trigger.filings is defined }}"
inputFiles:
ratios.csv: "{{ outputs.ratio_table.uri }}"
fetchType: FETCH
sql: |
WITH filed(cik, form, filing_date, report_date) AS (
VALUES {% for f in trigger.filings %}('{{ f.cik }}', '{{ f.form }}', DATE '{{ f.filingDate }}', TRY_CAST('{{ f.reportDate ?? '' }}' AS DATE)){{ loop.last ? '' : ', ' }}{% endfor %}
),
companies(ticker, cik) AS (
VALUES {% for c in outputs.companies.rows %}('{{ c.ticker }}', '{{ c.cik }}'){{ loop.last ? '' : ', ' }}{% endfor %}
)
SELECT c.ticker, f.form, f.filing_date, f.report_date, max(r.period_end) AS latest_in_facts
FROM filed f
JOIN companies c USING (cik)
LEFT JOIN read_csv_auto('{{ workingDir }}/ratios.csv') r ON r.ticker = c.ticker
WHERE f.report_date IS NOT NULL
GROUP BY c.ticker, f.form, f.filing_date, f.report_date
HAVING max(r.period_end) IS NULL OR max(r.period_end) < f.report_date
- id: wait_for_sec_data
type: io.kestra.plugin.core.execution.Fail
description: Fails so the flow-level retry runs again later, instead of
reporting on the previous quarter.
runIf: "{{ (outputs.freshness.size ?? 0) > 0 }}"
errorMessage: >-
SEC's company facts don't include {% for s in outputs.freshness.rows %}the
{{ s.form }} {{ s.ticker }} filed on {{ s.filing_date }} (period ended {{
s.report_date }}){{ loop.last ? '' : ', ' }}{% endfor %} yet. The flow is
retried every 30 minutes, up to 6 runs in total.
- id: alerts
type: io.kestra.plugin.jdbc.duckdb.Query
description: Evaluates each company's latest quarter against the thresholds.
inputFiles:
ratios.csv: "{{ outputs.ratio_table.uri }}"
fetchType: FETCH
sql: |
WITH ratios AS (SELECT * FROM read_csv_auto('{{ workingDir }}/ratios.csv')),
companies(ticker, cik, name) AS (
VALUES {% for c in outputs.companies.rows %}('{{ c.ticker }}', '{{ c.cik }}', '{{ c.name | replace({"'": "''"}) }}'){{ loop.last ? '' : ', ' }}{% endfor %}
),
latest AS (
SELECT * FROM ratios QUALIFY row_number() OVER (PARTITION BY ticker ORDER BY period_end DESC) = 1
),
compared AS (
SELECT l.*, c.name, c.cik,
p.period_end AS previous_end, p.current_ratio AS previous_current_ratio,
p.debt_to_equity AS previous_debt_to_equity,
y.period_end AS year_ago_end, y.gross_margin_pct AS year_ago_gross_margin_pct,
y.operating_margin_pct AS year_ago_operating_margin_pct
FROM latest l
JOIN companies c USING (ticker)
LEFT JOIN ratios p ON p.ticker = l.ticker AND l.period_end - p.period_end BETWEEN 80 AND 100
LEFT JOIN ratios y ON y.ticker = l.ticker AND l.period_end - y.period_end BETWEEN 350 AND 380
{%- if trigger is defined and trigger.filings is defined %}
-- A filing-triggered run only reports on the companies that just filed.
WHERE c.cik IN ({% for f in trigger.filings %}'{{ f.cik }}'{{ loop.last ? '' : ', ' }}{% endfor %})
{%- endif %}
),
checks AS (
SELECT ticker, name, period_end, 'current ratio' AS metric,
printf('current ratio %.2f is below the minimum of %.2f', current_ratio, {{ inputs.current_ratio_min }}) AS finding
FROM compared WHERE current_ratio < {{ inputs.current_ratio_min }}
UNION ALL
SELECT ticker, name, period_end, 'debt-to-equity',
printf('debt-to-equity (interest-bearing debt / equity) %.2f is above the maximum of %.2f', debt_to_equity, {{ inputs.debt_to_equity_max }})
FROM compared WHERE debt_to_equity > {{ inputs.debt_to_equity_max }}
UNION ALL
SELECT ticker, name, period_end, 'debt-to-equity',
format('equity is negative ({:,.0f} million USD), so debt-to-equity is not meaningful', equity_musd)
FROM compared WHERE negative_equity
UNION ALL
SELECT ticker, name, period_end, 'current ratio',
printf('current ratio moved from %.2f to %.2f since the quarter ended %s', previous_current_ratio, current_ratio, previous_end)
FROM compared WHERE abs(current_ratio - previous_current_ratio) >= {{ inputs.current_ratio_move }}
UNION ALL
SELECT ticker, name, period_end, 'debt-to-equity',
printf('debt-to-equity moved from %.2f to %.2f since the quarter ended %s', previous_debt_to_equity, debt_to_equity, previous_end)
FROM compared WHERE abs(debt_to_equity - previous_debt_to_equity) >= {{ inputs.debt_to_equity_move }}
UNION ALL
SELECT ticker, name, period_end, 'gross margin',
printf('gross margin %.1f%% vs %.1f%% in the quarter ended %s (%+.1f points)', gross_margin_pct, year_ago_gross_margin_pct, year_ago_end, gross_margin_pct - year_ago_gross_margin_pct)
FROM compared WHERE abs(gross_margin_pct - year_ago_gross_margin_pct) >= {{ inputs.margin_move_pp }}
UNION ALL
SELECT ticker, name, period_end, 'operating margin',
printf('operating margin %.1f%% vs %.1f%% in the quarter ended %s (%+.1f points)', operating_margin_pct, year_ago_operating_margin_pct, year_ago_end, operating_margin_pct - year_ago_operating_margin_pct)
FROM compared WHERE abs(operating_margin_pct - year_ago_operating_margin_pct) >= {{ inputs.margin_move_pp }}
)
SELECT ticker, name, period_end, metric, finding,
printf('%s (%s), quarter ended %s: %s', ticker, name, period_end, finding) AS message
FROM checks
ORDER BY ticker, metric
- id: report
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.alerts.size > 0 }}"
then:
- id: log_alerts
type: io.kestra.plugin.core.log.Log
level: WARN
message: |
{{ outputs.alerts.size }} ratio alert(s):
{% for a in outputs.alerts.rows %}- {{ a.message }}
{% endfor %}
- id: slack
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Optional. Without the SLACK_WEBHOOK_URL secret this step fails as a
warning; the alerts above are already logged.
allowFailure: true
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
# A full JSON payload: `messageText` starting with a number is cut to that number (kestra-io/plugin-slack#103).
payload: >-
{{ outputs.alerts.rows | jq('{text: ((length | tostring) + " SEC ratio
alert(s):\n" + (map("• " + .message) | join("\n")))}') | first |
toJson }}
else:
- id: all_clear
type: io.kestra.plugin.core.log.Log
message: No ratio crossed a threshold in the latest quarter.
outputs:
- id: ratio_table
type: FILE
description: Every company and quarter with its ratios (CSV).
value: "{{ outputs.ratio_table.uri }}"
- id: alert_count
type: INT
value: "{{ outputs.alerts.size ?? 0 }}"
# A filing can reach EDGAR before its numbers reach the company facts API, and SEC can be briefly
# unavailable: a failed run is retried as a new execution, with the same trigger data.
retry:
type: constant
interval: PT30M
maxAttempts: 6
behavior: CREATE_NEW_EXECUTION
triggers:
- id: new_quarterly_report
type: io.kestra.plugin.edgar.filings.Trigger
description: Runs when a watched company files a 10-Q or 10-K. Enable after
setting SEC_EDGAR_USER_AGENT.
userAgent: "{{ kv('SEC_EDGAR_USER_AGENT', errorOnMissing=false) ?? '' }}"
tickers:
- AAPL
- MSFT
- NVDA
- KO
- WMT
forms:
- 10-Q
- 10-K
interval: PT30M
disabled: true
Compute four ratios for a watchlist from the XBRL numbers companies file with the SEC, and alert when one crosses a threshold or moves sharply. No API key or paid data feed.
new_quarterly_report (EDGAR trigger) runs the flow when a watched company files a 10-Q or 10-K.each_company reads three years of us-gaap facts per ticker with xbrl.CompanyFacts.ratios (DuckDB) keeps one value per period (SEC's frame), derives fiscal Q4 of income items
as the year minus its three quarters, and computes per quarter:freshness fails a triggered run on purpose while SEC's company facts lack the new report; the
flow retry reruns it every 30 minutes (6 runs), so the new quarter gets checked, not the last.alerts checks the latest quarter against the thresholds, the balance-sheet ratios against the
previous quarter and the margins against a year earlier, for the companies that filed.ratio_table CSV of every quarter.Credit, treasury and equity analysts, and finance teams watching suppliers or customers.
An event trigger and flow-level retries absorb SEC's publishing lag; the finance logic is one SQL query versioned with the flow.
Kestra 2.0 with plugin-edgar 1.0.0 (auto-installed on save in a local OSS instance, otherwise
kestra plugins install io.kestra.plugin:plugin-edgar:1.0.0).
SLACK_WEBHOOK_URL (optional): without it the Slack step ends in a warning; alerts stay logged.company.team > KV Store: key SEC_EDGAR_USER_AGENT, type STRING, value
your name and email (Example Co admin@example.com). SEC rejects requests without one.ratio_table.tickers (a trigger can't read inputs) and enable it.each_company and add a column in ratios.Audits Apache SeaTunnel Zeta Engine REST API for failed streaming or batch jobs and alerts on pipeline stalls.
Load bounded JSON batches with a transactional PostgreSQL ledger, then archive the SFTP source. Retry an archive failure without inserting duplicate records.
When a fund manager you follow files its quarterly 13F, compare its holdings with the previous quarter and post the new positions, exits and largest changes to Slack.
Normalize Segment webhook batches, check event names and required property types against your taxonomy contract, and escalate violations to GitHub and Slack.
List an SFTP dropzone on a schedule, flag regular files that have sat past a staleness threshold, alert Slack, and optionally quarantine them with Move.
Pick up PGP files from SFTP, decrypt them, verify the partner's signature, refuse replays and quarantine anything that fails.
Poll SEC EDGAR for new 8-K filings from the companies you follow, summarize each one and rate its sentiment with a local Ollama model, check every figure against the filing, and remember what was already processed.
Download a CSV dataset and email it as an attachment through the Resend SMTP API, with the API token kept safe in a Kestra Secret.
When a fund manager you follow files its quarterly 13F, compare its holdings with the previous quarter and post the new positions, exits and largest changes to Slack.
Sync external API data into a custom ServiceNow table with automatic upsert (update or insert) logic.
React to signed Sent webhook events in real time, alert Slack when a message's own status has not reached DELIVERED, and log a governance audit trail.
Drive a headless browser via Selenium Grid to check whether a page sets tracking cookies before consent, and alert Slack on a GDPR-style violation.
