AI-Assisted Software Engineering

Agents Need Memory, Not Just Permissions — an engineering history for Oracle work

A coding agent can write good SQL. It cannot remember what it already ran. sql-runner gives Oracle Database projects an inspectable engineering history — built out of text files, not embeddings.

In April I wrote about why I stopped letting AI run SQL . The argument was simple: an agent that generates and executes SQL in one motion leaves nothing behind. No file, no review step, no trail. The alternative was almost boring — let the AI write the script, save it, read it, then run it explicitly through SQLcl. Assistant, not operator.

Then I hit an issue that cost me many hours.

I had a particularly complex database build that was failing. I was using Codex to generate SQL scripts, but it was not retaining a record of what it had already run. It went into a loop: build option A failed, so it tried B; when that failed, C; then it returned to A. That was incredibly frustrating, so I decided to build some memory into the process.

sql-runner is a small SQLcl-based runner for Oracle Database projects, and one of the build tools in the stack I have been assembling. It exists to close a gap that the file-and-review workflow leaves wide open.

The Gap Scripts Don’t Close

Saving the SQL to a file solves the review problem. It does not solve the memory problem.

If Codex helps me write a script today and comes back tomorrow to make a related change, what does it actually know? It can read the repository. It can see the script files and the commits. But the repository does not tell it which scripts were actually run, against which environment, at which commit, with what result, and with what errors.

That is not a theoretical concern. It is exactly the missing context that leads an agent to repeat a failed approach, assume a package compiled when it did not, or write a second migration without understanding what the first one did.

Database work is unforgiving about this in a way application code is not. A SQL script changes shared state. It touches data, privileges, packages, indexes, constraints, views, and the assumptions other systems already depend on. Humans compensate for the missing history with memory, chat scrollback, and a rough sense of what just happened. Agents have none of that. They need it written down.

Every Change Is an Engineering Episode

The model underneath the tool is deliberately small. Each database change is an engineering episode, and every episode has three parts:

  • Intent — why the change exists
  • Action — the SQL that was executed
  • Outcome — what actually happened

Most tooling records the middle one. Version control captures the action. Deployment logs capture, at best, the action and a return code. The intent — the reasoning, the alternatives rejected, the thing the author was trying to avoid — evaporates into a chat window and is gone.

That is the part agents most need and are least likely to have.

So the runner captures all three, in that order, in a plain text file. The log is tagged [INTENT], [ACTION], [OUTCOME] because those are not formatting conventions. They are the model.

What sql-runner Is

A thin, auditable execution layer around SQLcl. It is not a migration framework and it is not trying to replace Liquibase, Flyway, or the deployment process you might already have.

It does four things:

  • runs SQL scripts through saved SQLcl connections
  • writes structured logs under logs/<env>/runs
  • extracts an INTENT block from the script and stores it with the run
  • indexes the log history into SQLite at .codex/db-history.sqlite

One principle sits underneath all four:

The logs are the source of truth. SQLite is only a query surface.

If the two ever disagree, the log file wins. The index exists because agents can query SQLite quickly — recent runs, failures, similar changes, activity tied to a Git commit — but it can always be rebuilt from the logs. The durable artefact is plain text. The convenience is disposable.

That is a far better trade than hiding the evidence in a transient terminal session, or in a database table that only exists if the tooling happened to populate it correctly.

The Small Contract Around Each Script

The runner expects scripts to carry a short INTENT block near the top:

-- INTENT:
-- Purpose: Validate date-range and source-context analytics report semantics for admin reports.
-- Approach: Seed isolated analytics events and assert recent/source filtered rollups.
-- Reason: Protect the admin statistics UI from misleading date-range report regressions.
-- Expected objects:
--   ANALYTICS_EVENTS
-- Risk: Low
-- Prior history checked: Analytics/admin report and ORDS deployment history reviewed in db-history.
-- END INTENT

This is not ceremony. It is a compact explanation for the next reader, who may be you in six months, may be another developer, and increasingly may be an agent trying to work out whether a proposed change is new, related to something already attempted, or a repetition of an idea that already failed.

Note the last line. Prior history checked is a small piece of enforced honesty. Before anything runs, the author has to say whether they looked — and what they found.

Of course, I don’t write these sections myself — I get the LLM to do this when it builds SQL scripts. The SKILL.md that ships with the scripts tells the LLM the standards expected when it generates them.

What a Run Looks Like

Setup is in the README. The short version: SQLcl holds the real connection details, and sql-runner maps a logical environment name to a saved SQLcl connection.

A run is deliberately unremarkable:

bin/run-sql.sh --env dev --script deploy/create/03_customer_prefs.sql

Production has a guard:

ALLOW_PROD_SQL=yes bin/run-sql.sh --env prod --script deploy/create/00_full.sql

That is not an approval system. It is a speed bump. If a script is going to touch prod, the command should say so unambiguously.

Each run writes a log to logs/<env>/runs/<run_id>.log:

[INFO] START

[INFO] RUN_ID=20260612_141057_admin_analytics_reports_68685
[INFO] ENV=dev
[INFO] CONNECTION=dev
[INFO] SCRIPT=test/contract/admin_analytics_reports.sql
[INFO] SCRIPT_PATH=/[redacted]/admin_analytics_reports.sql
[INFO] SCRIPT_SHA256=c0067bcba087d15056b286171f37293c56ca2e296e1a26db46f19e1a3a0fcd73
[INFO] GIT_COMMIT=9d5baa2
[INFO] GIT_DIRTY=true
[INFO] LOG_LEVEL=normal
[INFO] TIMESTAMP=20260612_141057
[INFO] LOG_FILE=/[redacted]/logs/dev/runs/20260612_141057_admin_analytics_reports_68685.log

[INTENT]
Purpose: Validate date-range and source-context analytics report semantics for admin reports.
Approach: Seed isolated analytics events and assert recent/source filtered rollups.
Reason: Protect the admin statistics UI from misleading date-range report regressions.
Expected objects:
  ANALYTICS_EVENTS
Risk: Low
Prior history checked: Analytics/admin report and ORDS deployment history reviewed in db-history.
[END INTENT]

[ACTION]

SQLcl: Release 26.1 Production on Fri Jun 12 14:10:59 2026

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.2.0 - Production
Version 23.26.2.2.0

Elapsed: 00:00:00.075
Elapsed: 00:00:00.069
-- ADMIN ANALYTICS REPORTS Contract --
  PASS: Recent range excludes old events
  PASS: Source-filtered daily count
  PASS: Source-filtered top page count
  PASS: Source-filtered referrer count
  PASS: Source-filtered country count
  PASS: Source-filtered session pageview count

ADMIN ANALYTICS REPORTS contract complete.
Passed: 6
Failed: 0

Elapsed: 00:00:00.151
Elapsed: 00:00:00.184
[INFO] USER_ERRORS_CHECK

Elapsed: 00:00:00.468
Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.2.2.0 - Production
Version 23.26.2.2.0
[OUTCOME]
STATUS=SUCCESS
SQLCL_EXIT=0

[INFO] SQLCL_EXIT=0
[INFO] END

A structured text log is greppable by a human and parseable by an agent, and neither of them needs a bespoke reader to do it.

Three details earn their place quietly:

The script hash tells you whether a script was edited after the run that logged it. Small thing; saves a lot of confusion later.

The log hash The log is hashed too, so corruption or after-the-fact edits are detectable."

The USER_ERRORS check runs after every script, so package, procedure, function, trigger, and view compile failures land in the same log. A SQL script can finish “successfully” and still leave invalid objects behind. The next agent should not have to rediscover that.

Making History Searchable

Once the logs exist, they can be interrogated - here are some examples:

bin/db-history-recent.sh dev 5
bin/db-history-failures.sh package
bin/db-history-similar.sh "customer preference package compile failure"
bin/db-history-script.sh customer_prefs
bin/db-history-commit.sh a7f3d9b
bin/db-history-show.sh 20260622_184501

db-history-similar.sh is the one that matters. It searches intent, summary, script and path, using FTS5 where available and a simpler fallback where not.

Two small design decisions in here that I would defend at length. db-history-show.sh prints the run record and the path to the log — it does not dump the log. Context windows are finite and an agent should choose to read the evidence, not have it forced on it. And the searchable summary attached to each run is generated by shell logic: script name, status, environment, commit, key Oracle errors, a short excerpt. No AI calls.

Memory That Cannot Hallucinate

The obvious way to build agent memory in 2026 is to embed everything, throw it in a vector store, and retrieve by similarity. I deliberately did not.

No vector database. No embedding store. No knowledge graph. Text files and a full-text index.

The reason is structural. This tool exists to make a probabilistic agent more reliable. If the memory layer is itself probabilistic — if the summary is model-generated, if retrieval is fuzzy and unexplainable, if the record of what happened has been through an inference step — then it can be wrong in exactly the way the agent is wrong, and confidently so. A memory that hallucinates is worse than no memory, because the agent will trust it.

Deterministic tools should sit underneath probabilistic ones, not the other way round.

Text logs, a hash, a SQLite index, and a grep are unglamorous. They provide a deterministic record of what happened for the user to inspect. This is not a tamper-proof audit system: an agent with filesystem access could write false logs and hashes. The objective is to support a build process, not to establish audit evidence.

So, Does It Work?

An evidence trail the agent never reads is not memory. It is just files.

You can tell Codex to check the history before it writes anything. But instructions you have to repeat every session are instructions you will eventually forget. So the runner ships with a skill, and the skill carries a protocol:

Oracle database work must use bin/run-sql.sh. Before database work: search engineering history — by task description, by object names, by Oracle errors. Review similar previous runs. Include an INTENT block. Execute through the runner. Review the outcome and the logs. Do not repeat a previously failed approach unless you can explain why the new approach differs.

And the line that governs all of it:

The engineering history exists to provide experience, not merely audit records.

That distinction is the whole design. An audit trail is something you produce for someone else, after the fact, and hope never to need. Experience is something you consult before you act, because it changes what you do next.

The split is clean: the skill travels, the memory stays. Drop the skill into any Oracle project and Codex knows how to use the runner there. The logs are local to the project they describe, and are not meant to travel at all.

All this is theory unless the memory actually helps. My experience over the last six weeks is that it does. When running SQL scripts, Codex often tells me it is looking back through the history. A few times it seems to change its approach after checking the history. It’s a little tricky to prove that something doesn’t happen - but since using sql-runner, my experience is that the agent does not repeat failures.

Some unexpected benefits

Altough written for an agent to use autonomously, I find myself increasingly using sql-runner myself. Asking codex to run it on your behalf is the easiest way of querying the history :

I asked Codex ‘did we have any failures building modules’:

Yes—there were ORDS module build/deployment failures in June:
11 Jun, 18:31: deploy/create/90_rest.sql failed because /api/v1/ was already registered for the schema (ORA-00001, ORDS unique-prefix constraint).
11 Jun, 12:23: full deployment failed creating an ORDS template with a null URI template (ORA-01400).
11 Jun, 12:23: another full deployment hit the same duplicate /api/v1/ module-prefix issue.
11 Jun, 09:33: full deployment failed while rebuilding an application context—the dev user lacked DROP ANY CONTEXT (ORA-01031 / ORA-41726).
10 Jun, 22:46: the health-module verification script failed because it queried a non-existent MODULE_NAME column in USER_ORDS_TEMPLATES (ORA-00904).
These are deployment/configuration and verification failures; the logs do not show a PL/SQL package compilation failure in those runs.

I also have several projects running at the same time, and I can lose track of where I am in a long build. Now Codex can read the logs and tell me where I left off, what has completed, and give a close picture of the database state without connecting. I used to keep daily journal files; now that is largely unnecessary.

What This Is Not

The first version is deliberately small. No dependency analysis, no rollback generation, no schema reconstruction, no object lineage, no multi-agent orchestration. sql-runner does not model every deployment pattern, does not impose a directory structure beyond the runner and its logs, and does not replace release management. A Bash script is not a governance framework and should not pretend to be one.

One scope decision is a choice rather than an oversight. The history is project-local. The logs live in the project, they are not committed, and they are not a shared team audit database. This is the working memory of one project, in one checkout, for whoever — or whatever — picks it up next. If you need organisation-wide deployment audit, that is a different tool with different requirements, and you should not build it out of gitignored text files.

I should also be honest about one thing I promised in the last post and have not delivered: script ordering. The runner targets environments, handles errors, and logs execution. It does not sequence a deployment. That remains a manual dependency order in my own projects — tables, then indexes, then views, then packages — and I have not yet found an automation I trust more than the list.

The repository includes offline tests (make test) that run without a live database, covering the logging and indexing behaviour. The live smoke test needs SQLcl and a saved connection. That split matters: the parts of this tool that provide the memory should be testable without a database being available for every check.

The Test

Agent-assisted database development needs more than prompts and permissions. It needs experience.

The tool works if the agent can answer these, without reading every log file in the project:

  • Have we done something similar before?
  • Have we already tried this approach?
  • What failed previously?
  • What was this change actually for?
  • What happened when we ran it?
  • Which log holds the evidence?

That is the whole specification. Everything else — the hashes, the tags, the SQLite index, the skill — exists to make those six questions cheap to ask.

The repository is public, and the README has the setup steps:

github.com/simongriffiths/sql-runner

If you are building Oracle Database applications with a coding agent, the pattern is worth adopting even if you rewrite the implementation. Keep the SQL in files. Run it explicitly. Record the intent, the action, and the outcome. Make the history searchable. Then teach the agent to look.

Agents work better when they can see what came before.