MySQL as a Workflow Engine: Building an LLM Prompt Gate

Introduction

Many production systems rely on LLM calls as an essential part of their critical paths. They can be used in evaluating support tickets, directing user queries to appropriate services, and a wide spectrum of automation tasks. Prompts, including both instructions and model settings, are central to all such LLM calls. A fairly large-scale system with enough use cases can end up with a large set of prompts.

We can assume that prompts' behavior, at least to some extent, is similar to that of code or configuration in the sense that a minor change (e.g. a single word added or dropped) can cause drastic changes in the output. However, prompts are still usually saved as text or json files. Saving a change to that file, assuming authorized, means the new prompt is actually live and the next production call is using it. No testing or regression measurements are involved.

This article walks through a case study in fixing that. The idea is an evaluation gate that forces prompt updates to be checked and tested against pre-configured datasets before actually going live.

The interesting part isn't the gate itself, but rather how it's built. It's a multi-stage asynchronous pipeline running entirely in MySQL tables along with a few background workers.

The problem

A system involves a large set of prompts, each utilized in a specific use case. These prompts are typically often updated not only by engineers, but also by analysts, operations, and stakeholders beyond. Every edit goes straight to production, implying that a fix for some case can silently break many others, only to be found out about when it's already too late, or at least in a reactive manner.

To keep things simple, let's assume every prompt's output is a single binary decision (yes or no). Hence, we have to deal with one metric while regression testing, namely accuracy.

For every prompt, at least one dataset should be maintained. The dataset is basically a list of labelled items, each with the correct expected decision the LLM should produce. Each prompt-dataset pair has its own accuracy bar. For instance, a broad regression dataset can have a 95% threshold, while a small specific dataset of known hard cases needs to stay above 80%.

The ultimate requirements when someone submits a prompt change are:

1. The change is staged, not applied.
2. The staged prompt is run against every dataset attached to the prompt.
3. Accuracy is computed for each dataset.
4. The prompt's change only goes live if every dataset has its bar cleared. Otherwise, the live prompt stays intact.

Obviously, this has to be handled asynchronously, since a single prompt update submission can mean hundreds of LLM calls or even more, each one taking a couple of seconds or more. Moreover, timeouts and rate limits imposed by LLM providers could be a real issue. Work has to be asynchronous and durable to enable failure recovery. Such problems typically imply distributed message queues or fully-fledged workflow orchestrators. However, a different approach was taken here. A multi-stage pipeline living in MySQL tables and driven by background workers was implemented.

Architecture at a glance

The API service stages submitted prompt update and halts immediately. The actual heavy lifting of moving the update through the pipeline is done through four independent background workers:

1. Materialize: Fetch and store each dataset's rows for the run, page by page.
2. Execute: Claim chunks of dataset items, call the LLM, and compute and store the verdict based on the output.
3. Finalize: Close runs whose items are all scored (i.e. have a verdict), and compute their metrics.
4. Reconcile: Once all of the update's runs are done, the update is either applied or rejected.

The workers never communicate with each other. MySQL, storing the state, is the only coordination medium. Each worker's job will have a detailed elaboration down the line.

The data model

• prompt Represents the actually live prompt that production reads. Only the gate writes it.
• dataset Registers a labelled dataset and where its rows live. Datasets are typically stored in some warehouse or as .csv file in a blob storage. Every dataset abides by the same structure which is a single row per item, with an item_id and an expected_decision.
• prompt_dataset Maps a dataset to a prompt, along with the min_accuracy bar it must clear. Registering a mapping implies turning the gate on for a prompt.
• prompt_update Stores a single row per prompt update submission through the API service. It holds the prompt's candidate config (aka the new prompt) and the status of the prompt update.
• prompt_run Represents one evaluation of a prompt update's candidate config against one dataset. An update has one run per mapped dataset, and each run belongs to exactly one update.
• prompt_run_item Denotes a single item of a run. It holds for each item its expected decision, actual decision, and verdict (i.e. whether it's a match, mismatch, or error). (run_id, item_id) is unique.

The lease_owner / lease_until columns enable workers to coordinate and claim rows safely. More on them shortly.

The implicit state machines

No queue for each step is needed. Typically, we can add all datasets awaiting materialization to some task queue and upon processing, add the fetched items to a task queue with different execution logic. The pipeline would proceed similarly along with all the coordination required and callbacks that need to be passed downstream.

In this solution, the architecture's core design is that the status column forms a state machine. A worker polling rows with some state X is a consumer of queue X. No brokers or callbacks are needed to coordinate between the workers. Moving the state from X to Y is the hand off.

There are three state machines, one for each level of the hierarchy:

Every transition is validated:

UPDATE prompt_update
SET    status = 'REJECTED'
WHERE  id = :update_id
  AND  status = 'PENDING';

Transitions become idempotent and safe under race conditions with multiple workers.

The workers

The worker patterns

Every worker is an infinite loop that runs one small step, sleeps for some interval, and repeats. Each small step incrementally moves towards processing all the pending work in the MySQL tables. Each step roughly has the same three phases:

Nothing important lives in worker memory. Assuming while a worker's process is executing, the pod crashes, the process is killed, or some failure of any sort happens, the state in the tables remains intact. The next worker picks up from the last checkpoint in the database making for a durable and failure tolerant solution. To scale, multiple worker processes can be instantiated.

Now, for the hard part. Since the workers are polling a queue, at least abstractly, correct claiming logic needs to be handled to avoid having multiple workers picking up the same rows.

SELECT … FOR UPDATE SKIP LOCKED

The naive approach is to SELECT a pending row, process it, and then UPDATE it after processing completion. However, two workers can run that SELECT at the same moment, see the same row, and both process it.

Adding a FOR UPDATE fixes the correctness problem. The first worker acquires an exclusive lock on the row, so no one else can take it simultaneously. However, the other workers now wait for that lock. The "concurrent" workers end up having only one running at a time, practically rendering multiple workers instances useless in terms of performance.

SKIP LOCKED fixes that. It tells InnoDB: if a row is locked by someone else, act as if it isn't there and keep looking.

Now, every worker gets a different row, as fast as the database can hand them out. With ORDER BY id, rows are fetched in order, so the table behaves as a FIFO queue.

This simple yet highly powerful clause is what turns an ordinary table into a concurrent worker-safe task queue.

Leases: lock briefly, own for longer

What we have established so far is that each worker acquires an exclusive lock on some rows, does the processing, writes the updates, and finally releases the lock.

Now, what happens when the processing step takes too long? In our case study, the LLM call or the dataset items fetching, despite being paginated, could take some time. Holding the locks for that long hammers down the database, exhausts the connection pool, and slows all other workers or table readers down or even block them.

So, we split claiming from owning. The row lock lives only long enough to stamp a lease on the row, and then the transaction commits releasing the lock:

START TRANSACTION;

SELECT id
FROM   prompt_run_item
WHERE  run_id = :run_id
  AND  verdict IN ('PENDING', 'ERROR')
  AND  attempt < :max_attempts
  AND  (lease_until IS NULL OR lease_until < NOW())   -- free, or expired
ORDER  BY id
LIMIT  :chunk_size
FOR UPDATE SKIP LOCKED;

UPDATE prompt_run_item
SET    lease_owner = :me,
       lease_until = NOW() + INTERVAL 3 MINUTE,
       attempt     = attempt + 1
WHERE  id IN (:ids);

COMMIT;   -- the lock is released; the lease now protects the work

The worker then calls the LLM with no transaction open, and writes the result back only if it still owns the lease:

UPDATE prompt_run_item
SET    verdict = :verdict, actual_decision = :decision,
       lease_owner = NULL, lease_until = NULL
WHERE  id = :item_id
  AND  lease_owner = :me;

Similarly, the lease pattern is used in the same exact way with prompt_run for materialization.

Notice that a crashed worker or a killed process never releases its lease. Yet, no reaper process is required. The query above explains this since it treats an expired lease as no lease at all.

The four stages

Materialize
 - Claims a pending run with a lease.
 - Fetches the next page of the dataset after materialize_cursor.
 - Inserts the items, advances the cursor, and releases the lease.
 - If a worker dies mid-page, the lease expires and another worker re-reads the same page. This holds for the rest of the stages as well as highlighted in the worker patterns above.

Execute
 - Claims a chunk of items from a RUNNING run.
 - Calls the LLM for each item through the same code path production uses, ensuring that the evaluation closely mimics the production's actual behavior.
 - Records MATCH, MISMATCH, or ERROR.
 - Failed items are retried a few times with a proper backoff before their error becomes final.

Finalize
 - A polling mechanism for pending runs to know if all their items are finished.
 - Once a run has no unscored or leased items left, a simple GROUP BY verdict, expected_decision, actual_decision yields accuracy and a confusion matrix.
 - Stores metrics, and moves the run to DONE.
 - A run is moved to ERROR if at least a single item was consistently failing the retries and couldn't be scored.

Reconcile, the gate
 - A polling mechanism for pending updates to know if all their runs against their datasets are finished.
 - For each dataset, it compares every run's accuracy to the min_accuracy of its mapping in prompt_dataset.
 - If bar is cleared for all datasets, it writes the candidate config into prompt and moves the update to APPLIED, in one transaction.
 - Otherwise, update is moved to REJECTED, and the live config is untouched.

The API Service layer

From the user's side, none of this machinery is visible. The API service exposes the required operations and abstracts away the execution details:

• Submit a change for a prompt. The prompt update and one run per mapped dataset are created in a single transaction, and the caller immediately gets a tracking id, not an outcome. If the same prompt already has a pending submission, it's cancelled and the latest submission always wins.
• Check the status of a submission. For each dataset, the user sees the achieved accuracy against its bar and a sample of the items that failed, so a rejection becomes debuggable rather than a blind dead end.
• Register a dataset, and map it to a prompt with an accuracy bar.

A typical flow: an analyst submits a tweak and gets a tracking id. A few minutes later, the status shows the regression set at 96% (bar 95%) but the hard cases at 78% (bar 80%), so the change is rejected. They look at the failing items, adjust the instruction, and resubmit. This time both bars clear, and the new config goes live automatically.

Summary

We built an evaluation gate for LLM prompt changes. Every edit is staged, scored against labelled datasets, and goes live only when every accuracy bar is cleared. This entire process is automated with no human approval required.

The broader lesson is about orchestration. Sometimes, you don't need fancy infrastructure to model a multi-stage, failure tolerant, and durable workflow. A basic relational database, along with some of its not so basic features, and resourceful workarounds could suffice.

Further reading: For another case study utilizing MySQL's powerful FOR UPDATE SKIP LOCKED, check out this article:
Shopify scaling inventory reservations.