Workflow:PrefectHQ Prefect Database Cleanup With Approval
| Knowledge Sources | |
|---|---|
| Domains | Database_Maintenance, AI_Agents, Approval_Workflows |
| Last Updated | 2026-02-09 22:00 GMT |
Overview
End-to-end process for building a database cleanup workflow that supports either human approval via a Prefect UI form or autonomous AI approval using a pydantic-ai agent with Prefect MCP tools, enabling incremental trust building from manual oversight to full automation.
Description
This workflow addresses the operational challenge of database cleanup in self-hosted Prefect deployments. It implements a configurable approval gate that can operate in two modes: human approval (the flow pauses and presents a review form in the Prefect UI) or AI approval (a pydantic-ai agent investigates system health using Prefect MCP tools and returns a structured decision with confidence score). The workflow queries old flow runs matching a retention policy, generates a preview of what will be deleted, obtains approval through the configured channel, and performs batched deletion with rate limiting.
Key outputs:
- Deleted flow runs that exceed the retention period and match target states
- Approval audit trail (human notes or AI reasoning with confidence score)
- Full observability of the cleanup operation in the Prefect UI
Scope:
- From retention policy configuration to batched flow run deletion
- Supports dry-run mode for previewing without deleting
Usage
Execute this workflow when you need to manage database size in a self-hosted Prefect deployment by cleaning up old flow runs. Start with human approval in production to build confidence, then graduate to AI approval in development and staging environments. It is suitable for scheduled maintenance operations where you want configurable oversight.
Execution Steps
Step 1: Configure Retention Policy
Define the cleanup parameters using a structured configuration model: retention period (how far back to keep), target states (Completed, Failed, Cancelled), batch size, dry-run flag, and approval type (human or AI). The configuration model automatically renders as a UI form in Prefect.
Key considerations:
- Pydantic model validation ensures safe parameter bounds (batch_size 10-1000)
- The approval_type switch enables gradual transition from human to AI oversight
- Dry-run mode allows previewing without any destructive action
Step 2: Query Flow Runs for Cleanup
Use the Prefect client API to query flow runs that match the retention policy: runs with start times before the retention cutoff and in the specified terminal states. Fetch up to 5x the batch size to provide a comprehensive preview.
Key considerations:
- Filters are applied server-side for efficiency
- The query returns full flow run metadata for preview generation
- An empty result set exits early with a no-action status
Step 3: Generate Cleanup Preview
Create a human-readable preview showing the first several flow runs that will be deleted, including their names, states, and start times. This preview is shown to either the human reviewer or the AI agent for decision-making.
Key considerations:
- Preview is truncated to avoid overwhelming the reviewer
- Includes both a count and representative samples
Step 4: Obtain Approval
Route to the configured approval mechanism:
Human approval: The flow pauses using pause_flow_run and presents a CleanupApproval form in the Prefect UI. The reviewer sees the preview and can approve or reject with notes. A 1-hour timeout prevents indefinitely paused flows.
AI approval: A pydantic-ai agent with Prefect MCP tools investigates system health, reviews the cleanup proposal, and returns a structured CleanupDecision with approval status, confidence score (0-1), reasoning, and any concerns.
Key considerations:
- Human approval uses Prefect's interactive workflow (pause_flow_run with RunInput)
- AI approval wraps the agent in PrefectAgent for retry and timeout handling
- Both paths return a consistent (approved, notes) tuple
Step 5: Execute Batched Deletion
If approved and not in dry-run mode, delete the matched flow runs in batches with rate limiting (100ms between deletions). Handle already-deleted runs gracefully (ObjectNotFound treated as success). Log the final count of deleted versus total runs.
Key considerations:
- Batched deletion prevents overwhelming the API server
- Rate limiting with asyncio.sleep provides back-pressure
- Already-deleted runs are counted as successes for idempotency
- Individual deletion failures are logged but do not abort the batch