Cosmos DB Expired Document Retention Gate
Query a Cosmos DB container for documents that should have expired but are still present, classify the run by violation count, alert Slack, and optionally delete them and verify.
Monitors PostgreSQL primary key sequences to alert database administrators before 32-bit integer sequence overflow halts application insert writes.
In PostgreSQL applications, primary keys are traditionally created using serial (a 32-bit integer sequence capped at 2,147,483,647). As business transaction volumes expand, high-write tables, such as audit logs, payment transactions, and telemetry events, quietly consume billions of sequence values.
When a sequence reaches its maximum integer limit, PostgreSQL throws a fatal runtime exception: ERROR: nextval: reached maximum value of sequence. Subsequent INSERT statements are rejected, causing immediate application outages for end users.
Migrating a high-traffic table from a 32-bit integer to a 64-bit bigint under emergency outage conditions requires downtime and exclusive locks. This blueprint implements a proactive sentinel that audits pg_sequences daily, detects sequences crossing 80% capacity, and alerts database administrators with exact remediation steps months before outages occur.
daily_sequence_audit trigger (io.kestra.plugin.core.trigger.Schedule) executes every morning at 06:00 UTC.query_sequence_inventory task (io.kestra.plugin.jdbc.postgresql.Query) queries pg_sequences for last_value, max_value, and data type definitions.analyze_sequence_capacities task (io.kestra.plugin.scripts.python.Script) filters sequences exceeding capacity_warning_percentage and compiles sequence_risk_report.json.evaluate_risk_trigger flowable task (io.kestra.plugin.core.flow.If) evaluates whether any critical sequences were detected.alert_dba_sequence_exhaustion (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) delivers an actionable triage card with migration recommendations.export_sequence_manifest task records sequence capacity metrics for compliance tracking.flowchart TD
A[Schedule: Daily 06:00 UTC] --> B[query_sequence_inventory: PostgreSQL]
B --> C[analyze_sequence_capacities: Python Capacity Analyzer]
C --> D{Sequence Capacity Exceeded?}
D -- Yes --> E[alert_dba_sequence_exhaustion: Slack]
D -- No --> F[log_compliant_sequences_status: Log]
E --> G[export_sequence_manifest: Return JSON]
F --> G
Ad-hoc SQL scripts often get forgotten until an integer overflow brings down an entire payment or user-registration flow. Kestra automates the inspection, analysis, and alerting into a scheduled, auditable workflow with zero ongoing maintenance.
| Name | Type | Default | Description |
|---|---|---|---|
postgres_url |
STRING | jdbc:postgresql://... |
JDBC URL of PostgreSQL database. |
postgres_username |
STRING | db_monitor |
Monitoring database user with catalog access. |
capacity_warning_percentage |
FLOAT | 80.0 |
Warning threshold percentage (e.g. 80.0 = 80%). |
slack_channel |
STRING | #dba-alerts |
Slack channel destination for alerts. |
{{ outputs.analyze_sequence_capacities.vars.risk_detected }}: Boolean flag indicating if any sequence breached the threshold.{{ outputs.analyze_sequence_capacities.vars.top_sequence }}: Schema-qualified name of the sequence with highest utilization.{{ outputs.analyze_sequence_capacities.vars.top_usage_pct }}: Utilization percentage of the top offender.{{ outputs.analyze_sequence_capacities.outputFiles['sequence_risk_report.json'] }}: Complete JSON diagnostic report.POSTGRES_PASSWORD: Password for the PostgreSQL database user.SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL.POSTGRES_PASSWORD and SLACK_WEBHOOK_URL in your Kestra namespace secrets.ALTER SEQUENCE ... AS bigint is insufficient if the underlying table column is still defined as integer; both must be migrated to bigint.ALTER TABLE t ALTER COLUMN id TYPE bigint requires an exclusive table rewrite lock on older PostgreSQL versions; plan schema migrations during scheduled maintenance or use shadow columns.io.kestra.plugin.jdbc.postgresql.Query to inspect foreign key columns pointing to the expiring primary key sequence to ensure related tables are migrated concurrently.