wp datamachine flows list times out with ~300 flows

[chubes4]

Problem

Running wp datamachine flows list on the events site (336 flows) timed out after 30 seconds. This is not a large dataset — the command likely does N+1 queries (loading full flow configs, pipeline joins, or scheduling status checks per row).

Impact

Agents and operators can't quickly check flow status via CLI. The events site is the primary consumer and 336 flows should be well within acceptable performance.

Likely Cause

The command probably:

• Loads full flow_config and scheduling_config JSON blobs per row
• Joins to pipelines table without index optimization
• Possibly resolves execution status from the jobs table per flow

Suggestion

• Add a --fields flag to limit which columns are fetched (skip large JSON blobs by default)
• Add pagination (--per-page, --page)
• Consider a summary mode that only shows flow_id, name, pipeline, interval, and last run status
• Ensure the query uses indexed lookups

[chubes4]

Fixes shipped in v0.55.0–v0.55.2

Three rounds of optimization, each addressing a different layer:

v0.55.0 (batch DB queries)

• Replaced per-pipeline flow loop with single paginated query — get_all_flows_paginated() and count_all_flows() on the DB layer. Was 88 queries for 88 pipelines → 1 query.
• Batched latest-job lookup — get_latest_jobs_by_flow_ids() already supported arrays but was called per-flow. Now called once for all flow IDs. Was 336 queries → 1.

v0.55.1 (batch AS queries + service caching)

• Batched next-run lookup — New batch_get_next_run_times() method queries actionscheduler_actions once for all flow IDs instead of per-flow as_next_scheduled_action(). Was 336 queries → 1.
• Cached HandlerAbilities and SettingsDisplayService — Were instantiated per-flow inside format_flow_for_response(). Now cached as static properties.

v0.55.2 (skip handler enrichment for list views)

• New list output mode — CLI list views now use output_mode=list which formats flow data with status fields but skips the expensive per-step handler settings display, default application, and filter hooks. Single-flow detail views still use full mode.

Remaining bottleneck

The command is still slow (~60s+) due to loading and JSON-decoding 336 flow_config blobs. Each blob can be several KB of handler configs, step configs, etc. The next step would be:

• Selective column query — SELECT flow_id, flow_name, pipeline_id, scheduling_config FROM flows without flow_config for list views
• The CLI only needs handler slugs (extractable from a lightweight join or summary column) and scheduling info
• This would reduce the data transfer from ~several MB of JSON to ~50KB of scalar fields

Leaving this open for the selective column query optimization.