Job retention stopped after two cleanup failures: 270k completed rows retained, and status is unbounded free text

[chubes4]

A local install has accumulated 271,542 rows in wp_datamachine_jobs, of which 270,381 (99.6%) are completed. Retention exists (#879), so this is not a missing feature — the cleanup runs appear to have failed and then nothing retried.

Two job rows record the failure in their own status text:

status "failed - Failed to create cleanup run."          : 1
status "failed - Failed to schedule cleanup chunk jobs." : 1

Observed

jobs: 271542 | oldest: 2026-05-10 20:52:38 | newest: 2026-08-31 22:03:07
  completed                         : 270381
  pending                           :    422
  failed - packet_failure           :    345
  failed - Exception: …TypeError…   :    332
  completed_no_items                :     23
  failed - scheduler_path_lost…     :     14
  processing                        :     11
  failed - Failed to schedule cleanup chunk jobs. : 1
  failed - Failed to create cleanup run.          : 1
pending AS actions: 20

Newest row is 18 days old at time of writing, so the table is not actively growing — it grew, cleanup failed, and the backlog stayed.

Two separate problems

1. Cleanup failure is terminal and silent

"Failed to create cleanup run" and "Failed to schedule cleanup chunk jobs" each occurred once and left 270k completed rows in place. Nothing retried and nothing surfaced. A retention mechanism whose failure mode is "stop permanently, record one row, carry on" is indistinguishable from having no retention.

Worth considering: retry with backoff, and a health signal when completed-job count exceeds the configured retention horizon by some factor.

2. status is an unbounded free-text column

It currently holds entire exception messages, including absolute file paths and third-party error strings:

failed - Exception: DataMachineCode\Workspace\WorktreeContextInjector::has_owner_terminal_disposable_cleanup_signal():
  Argument #1 ($metadata) must be of type array, null given, called in /Users/…/wp-content/plugins/dat…

failed - wp-ai-client request failed: … cURL error 28: Operation timed out after 513455 milliseconds …

Consequences:

• GROUP BY status produces one group per distinct error string rather than per state, so the column can't be aggregated or indexed usefully.
• Every unique error text is a distinct value, defeating any status index.
• Row size inflates for the rows that matter least.
• Absolute filesystem paths land in a data column.

A bounded status enum plus a separate nullable error_message (or a reference into the logs table) would fix aggregation, indexing and row size together.

Why this surfaced

A consumer reported a 47-second, 1.2 GB query against this table on a file-backed storage backend, where a full scan is one file read per row. The storage engine has its own defect and is tracked separately — but 271k unpruned rows is what made it reachable, and the two cleanup failures are why they are still there.

There is also a separate TypeError visible above (has_owner_terminal_disposable_cleanup_signal() receiving null for an array parameter, 332 occurrences) which looks like its own bug and is not addressed here.

---

AI assistance disclosure: investigated by Anthropic's Claude running in OpenCode, directed by me. The agent profiled the query path, aggregated the job table by status, and drafted this report. I reviewed the findings before filing.

[chubes4]

Correction: the retention failure is a storage-engine limitation, not a Data Machine bug

I filed this suggesting retention "stopped after two cleanup failures" and proposed retry-with-backoff. Having reproduced it, that framing is wrong and the retry suggestion would not have helped.

This site runs markdown-database-integration as its database backend. Its native engine cannot execute DELETE on a partitioned table at all — not bulk, not bounded, not by primary key:

$wpdb->query( "DELETE FROM `wp_datamachine_jobs` WHERE status=%s AND created_at<%s LIMIT 5" );
// false — "mdi-native supports one bounded generic UPDATE or DELETE."

$wpdb->query( $wpdb->prepare( "DELETE FROM `wp_datamachine_jobs` WHERE job_id = %d", 25020 ) );
// false — "mdi-native can mutate only a persisted generic snapshot table."

Reads against the same predicate work and correctly count 270,381 matching rows.

So Jobs::delete_old_jobs() returning false, and JobsCommand printing Failed to delete jobs., are both correct behaviour. The method's int|false contract is being honoured; the storage layer genuinely cannot perform the delete. Retrying would fail identically every time.

Tracked upstream at Automattic/markdown-database-integration#416.

What still stands

The second half of the original report is unaffected and worth keeping: status is an unbounded free-text column. It currently stores entire exception messages including absolute filesystem paths and third-party error strings, so GROUP BY status yields one group per distinct error text rather than per state, and no index on it can be useful. A bounded status enum plus a separate error_message field would fix aggregation, indexing and row size together. That is independent of any storage backend.

The TypeError noted earlier (has_owner_terminal_disposable_cleanup_signal() receiving null for an array parameter, 332 occurrences) also still stands as its own bug.

What I would suggest instead of retry

Nothing here needs changing for the delete path. If anything, a health signal is still worth considering — when a retention run reports false rather than a row count, that is a storage-capability problem the operator should see surfaced rather than discover months later at 271k rows. But the failure itself is not Data Machine's to fix.

---

AI assistance disclosure: reproduced by Anthropic's Claude in OpenCode, directed by me; I reviewed the evidence before posting.