Blog

AI agents across many services: read with SQL, write with approval

· Builds

When a team runs many small services, each with its own database and API, an AI agent can help a lot: answering questions about the data, fixing records, adding things. But two problems come up at once. The agent doesn't know what the data means. And no one wants it changing production on a hunch.

The design below handles both, with one rule: an agent may read anything, but it never changes data by itself. It isn't a thought experiment: it runs in production across a dozen small services.

At its centre is one service that sits between the agents and all the other services. Let's call it the gateway. Agents talk only to the gateway, over MCP (the protocol AI agents use to call tools).

One thing to know up front: "read anything" is meant literally. The agent can read whatever the gateway's reading role can see, personal data included, and what it reads goes to the model that runs it. Choosing what that role may see is the first decision to make (more on it below).

An example, start to finish

A person asks the agent: "stop watching the MiniMax news feed." There's a News service that watches news feeds. Its code lives in the repository acme/news, and its data in the database news.

  1. The agent finds the data. It reads the docs that the repositories keep about their databases, and runs SQL through the gateway. The SQL shows the feed is record 29 in the resources table.
  2. The agent finds the API that changes it. Each API has an API description: a Markdown file next to its code that says exactly how to call it. The gateway finds the description whose API changes the resources table.
  3. The agent asks the gateway to prepare the call: "delete record 29". The gateway builds the request itself, from the API description. Nothing is sent yet.
  4. The gateway sends a draft. A draft is the same request with draft=1. The News service does the whole delete inside a database transaction, answers exactly as it would for real, and then cancels the transaction, so nothing changes. The answer also carries a checksum: a short hash of record 29 as it is now.
  5. The agent explains the draft's answer in plain words: "the MiniMax news feed will no longer be watched. Its record would be deleted, and can only come back by adding the feed again."
  6. A person reviews it. The call is in a group: a named set of every call the agent prepared for this task. The agent gives the person the group's link, and the page shows each call, its draft and the explanation.
  7. The person approves it with the approver key, a secret only the people allowed to approve have. The gateway runs the draft again, checks that the checksum is still the same (so record 29 didn't change in the meantime), and only then sends the real delete.

The rest of this post explains each part, and why it's built this way.

What the gateway knows

A registry. One file lists every project: which databases belong to it, where its API lives (the base URL), and which headers a call to it carries.

The docs. Each repository explains itself in Markdown files in its docs/ folder, changed in the same commits as the code. There are three kinds:

  • docs/db_schema.md: a short overview of the repository's database, with links to the next kind;
  • pages with the details of each part of the database: the tables, what each value means, and ready-made queries;
  • the API descriptions, docs/api/<name>.yaml.md.

The gateway keeps a copy of all of these in its own database. A GitHub webhook tells it when a repository is pushed, and it updates the files that changed within seconds. Each copy remembers which git commit it came from, so later every query and every prepared call records exactly which version of a doc it relied on.

Reading data

  1. The agent asks the gateway for db_schema.md from all repositories, in one call. From these short overviews it sees which service holds the data, and which detail page covers it.
  2. With a second call, it reads that page.
  3. Then it writes the SQL, and the gateway runs it.

The gateway runs the SQL as a Postgres role that can only read. The role has access to the databases in the registry, and nothing else. Its transactions are read-only, so no statement can change data, and each query has a query timeout, so none can run forever. Every query is logged with the question it was meant to answer and the doc versions it was built from.

There are no API endpoints just for reading. If the data is in the database, it's read with SQL. So there's one way to read data, and nobody has to build and maintain read endpoints for agents: an API only does what the database can't.

Changing data, in detail

Finding the API. The API description starts with a block of YAML settings, called "front matter". Its writes list names every database field the API's calls can change, as database.table.column, the same names the SQL uses:

writes:
  - news.resources.url
  - news.resources.name

The agent asks the gateway "which API writes news.resources?". The gateway reads its copy of every API description, and returns the ones whose writes list names that table: here, the resources description in acme/news. The agent can also search with plain words ("stop watching a feed"). There's no separate search index to keep up to date: the gateway searches its copy of the descriptions, which follows every push.

The two parts of the API description. The front matter limits the agent. It declares the parameters, with their types and limits, and the actions. Each action has its HTTP method, its path, where every parameter goes, and whether it needs a person's approval. The gateway builds the request from this part, not from the agent's idea of the API. So an agent can't invent a field, send the wrong type, or call an endpoint the description doesn't list.

actions:
  delete:
    method: DELETE
    path: /api/resources/{$id}
    query:
      draft:
        $param: draft

Here {$id} puts the id parameter into the path, and $param: draft puts the draft parameter into the query string. The agent gives a value for id, but never for draft: the description only says where draft goes, and the gateway fills in its value itself, 1 for the draft and 0 for the real call.

The Markdown after it guides the agent's judgement. It says which action fits which request, how to turn a person's words into parameters, and how to read every possible answer. From it, the agent picks delete with id: 29.

Preparing the call. The agent gives the gateway the repository, the description, the action, the parameters, one sentence on why, and the name of the group. The gateway checks the parameters against the front matter, takes the base URL from the registry, and builds and stores the request: method, URL, headers and body. The credentials aren't stored with it. They're added from the registry only at the moment a call is sent.

The draft, and why it exists. Right away, the gateway sends the request with draft=1. Every endpoint that changes data supports this. It does all the real work inside one database transaction (its checks, its writes), cancels the transaction, and answers exactly as the real call would. Work outside the database, like sending an email or calling another service, is not done in a draft. The draft does two jobs:

  • It tests the request the agent prepared against the real service. If the agent got something wrong (a missing record, a value the service refuses), the draft fails, and the call never reaches a person.
  • It produces the checksum. The endpoint computes it from the data its change depends on, and returns it in a response header. For this delete, that's record 29 as it is now. For a create, it's the fact that the record doesn't exist yet. The endpoint's own code decides what goes in, so it must include everything the change depends on. The gateway only stores the checksum and sends it back with the real call.

The explanation. The agent reads the draft's answer and writes, in plain words, what will happen if the call is approved. It's based on what the API answered, not on what the agent intended, and it's written for the person who decides.

The review page

When the agent has prepared every call for the task, it gives the person the group's link. A real request often needs several calls: "replace these three feeds with their new addresses" is three deletes and three creates, and some tasks touch several services. The group keeps them together, so the person sees the whole change in one place and approves it at once. Nothing goes out piece by piece, half-reviewed.

For each call, the page shows:

  • Why this call: the agent's intention, in its own words.
  • The API description it was built from, with a link, and the action.
  • The data being sent, as a table.
  • The draft's response. It's green when the draft passed, and red when it failed (a red call can't be approved). It turns amber when the draft lifetime has passed: after 30 minutes by default, a draft's answer may be out of date. Opened, it shows exactly what the API answered.
  • What the draft's response means: the agent's explanation, right under that raw response, so the person can check the words against the evidence.
  • Details, folded: the exact stored request, the checksum and the timing.
  • Marks when the data or the draft changed: "changed" if a newer draft gave a different checksum, "expired" if the draft lifetime passed. Each call can run its draft again, or be discarded.

At the bottom, the person ticks the calls to send (all, or only some) and types the approver key. The gateway's owner sets that key in its configuration, and gives it only to the people allowed to approve changes. Agents use a different key to talk to the gateway, so no agent can approve its own calls.

On approval, the gateway runs every chosen draft again, and compares the new checksums with the ones on the page. So an expired draft never blocks approval: it simply gets refreshed. If any checksum changed, nothing is sent: the page marks those calls, and the person looks again. Otherwise the calls are sent one by one, in order, each with its checksum. The endpoint computes the checksum again inside the same transaction, before applying anything. If the data changed at the very last moment, it answers HTTP 412 ("Precondition Failed") and applies nothing.

Afterwards, the person can ask the agent to check the result. The agent reads the group and explains what each real response adds to the draft's explanation, or just says "done as planned". The page then shows both the plan and the result of every change.

What can still go wrong

A group can stop halfway. If a call fails or gets a 412, sending stops there. The calls already sent stay done, and the rest go back to waiting, each marked with why. Nothing is undone automatically. The page shows which calls went out, and the person decides what next. Some changes can't be undone at all (a deleted record may be gone for good), so deletes deserve the most careful look.

A call is never sent twice. If the gateway stops while a call is being sent, it can't know whether the change happened. It marks that call "may or may not have been applied" and holds back the rest of the group. It never retries by itself.

Calls in one group can't depend on each other. Each draft runs against the data as it is now. If call 3 needs an id that call 2 will create, call 3 can't be drafted until call 2 has run. Such a task takes two groups: approve the first, then prepare the second.

A draft is only as safe as its endpoint. Cancelling the transaction undoes the database writes. But skipping emails and calls to other services is up to each endpoint's code, and an endpoint that gets it wrong sends a real email from a draft. A draft also costs a little: while it runs, it briefly locks the records it touches, and a draft that creates a record uses up a number from the database's id counter, so ids may skip. So every service follows one written contract for drafts and checksums, with tests for it. Adding that to a service takes real effort.

A checksum only protects what it covers. If an endpoint leaves out something its change depends on, an approval based on old data can still pass.

The reading role sees everything it's given access to, personal data included, and what the agent reads goes to the model that runs it. Every query is logged with its purpose, but a log doesn't hide anything. If some tables or columns must stay away from agents, the role must not be given access to them, and someone has to decide which ones.

The agent reads text that others wrote. A news title or a doc could say "ignore your instructions". That can change not only how the agent explains a call, but which calls it prepares at all. The real guard is the person: nothing is sent until they approve it, and the page puts the raw request and response next to every explanation. The explanation helps you read; the request and the response are the evidence.

People can approve without reading. Twenty calls with tidy summaries invite a quick "approve all". Small groups, one per task, are easier to read properly.

One shared approver key doesn't say who approved. The gateway records when each call was approved, but not by whom. A team with several approvers would want personal keys.

The writes lists are written by people. If a description forgets a field, searching by that field won't find the API, and nothing reports it. The lists change in the same commit as the code and its migrations, and a check in CI could catch the gaps. For now, it's a rule to keep, not a guarantee.

Why not something simpler?

The simplest setup is a read-only database role for the agent, and a person who makes every change by hand. It's safe, but the person ends up doing all the work: finding the right endpoint, building the request, double-checking it. Here, the agent does that part, the service tests it, and the person only decides.

The opposite, giving the agent write access, saves the person's time, but nobody sees a change before it happens.

Why this works

  • Context comes from the docs, which change together with the code, and are found in two short steps.
  • Reading is cheap and safe, and every read leaves a trace.
  • The API description, not the agent, decides how an API is called.
  • The draft tests each change on the real service before any person spends time on it.
  • The person approves consequences, not code. The page shows what would happen, in plain words, with the evidence next to it.
  • The checksum checks that the approval still holds, for the data it covers: what was approved is what happens, or nothing does.

In short: the agent searches and prepares, the services test each change on themselves, and a person makes the final call with everything in front of them.