At the end of the previous article in my series on building an app and database almost entirely through an LLM, there was a functional WordPress plugin accessing my PostgreSQL database sitting on Azure. It’s secure (protected against SQL Injection), and roughly does what I want – but is still not quite right.
So, in this – part six of the series – I’ll try to make the AI iterate some changes. Wish me luck.
Note for returning readers: I’m now using Claude, not Microsoft CoPilot, in line with a recent change we made at my place of work (Redgate).
And, for new readers, here’s how I do things: I get Claude to generate a prompt, and then Claude Code does the work. As I have done in every article, I’ll be posting my prompts and the AI responses. I mark these clearly, so it’s easy to see which parts are written by me and which come from the LLM. I’m writing most of this myself – promise!
This is the sixth part of Grant Fritchey’s series on building an app and database almost entirely through an LLM. The repo, including every prompt, lives on GitHub.
Fixing the UI
I want my app, dbRosetta, to educate you. For example, if you know that a procedure is used to return data in SQL Server, what’s the equivalent in Oracle and PostgreSQL? I also want some better sample data.
For this, here’s my prompt for Claude:
I’d like you to use the canonical_template.md to create a prompt for Claude Code.
In this case, I want to iterate on the application interface. The current state of the UI is visible here: https://www.scarydba.com/dbrosetta/
I want to change the input so that you define both a term, and the data platform that you already understand how that term applies. You then supply the term and the output then translates the term to one or more of the other data platforms.
For example: ‘transaction log’ in ‘SQL Server’, and I wanted to know what was the nearest equivalent in ‘PostgreSQL’ which would be ‘Write Ahead Log (WAL)’ as the answer. The UI has to change to allow for selecting the data platform in put.
It also should allow multiple selections for the output, either all data platforms, or, the ability to select one or more like a drop down with check boxes or something similar.
If necessary, we can change the data structures to meet these requirements.
I would also like additional data to be generated. Please find 6-8 terms that are different, similar to the ‘transaction log’ ‘write ahead log’ example above, across PostgreSQL, MySQL, Oracle and SQL Server.
Please generate the necessary prompt for Claude Code.
Now, Claude is generally more of a stickler for exactitude than I found with CoPilot. It generated this prompt by really closely following the template:
SYSTEM: You are a senior full-stack engineer working in the existing dbRosetta repository (Fastify + Prisma + PostgreSQL API, Flyway migrations, and a WordPress plugin front end). Make minimal, additive, well-tested changes that match the existing code style in each file you touch. Do not invent database facts; use only the seed data given to you verbatim.
USER: Implement a directional term-lookup feature that replaces dbRosetta’s current keyword search. The user provides a term and the sourceDialect platform they already know it in (postgresql, mysql, sqlserver, or oracle), then chooses targetDialects — one, several, or all of the remaining platforms — and gets back the equivalent term(s) on those platforms.
Migration — Add migrations/V4__add_term_equivalents_dialect_fk.sql:
Add nullable dialect_id INTEGER to dbrosetta.term_equivalents, with FOREIGN KEY (dialect_id) REFERENCES dbrosetta.dialects(id).
Backfill dialect_id by matching existing platform values to dialects.name or dialects.display_name case-insensitively (e.g. ‘SQL Server’ → the sqlserver dialect). Rows with no match (the SQLite rows) keep dialect_id NULL.
Add index idx_term_equivalents_dialect on dialect_id.
Add a partial unique constraint on (term_id, dialect_id) where dialect_id IS NOT NULL.
Do not drop the platform column or the existing uq_term_equivalents_term_platform constraint.
Prisma schema (services/dbrosetta-api/prisma/schema.prisma) — mirror the migration exactly: add optional dialectId Int? and a dialect Dialect? relation on TermEquivalent, add the reverse relation on Dialect, add the index. Regenerate the client as part of your work.
New API route — Add POST /api/v1/terms/lookup (new file services/dbrosetta-api/src/routes/v1/term-lookup.ts, registered in routes/v1/index.ts). Validate sourceDialect/targetDialects with zod against active dialects (400 with error: “invalid_dialect” on failure). Implement the two-step matching rule from the Guardrails section, and shape the response exactly per the Output Contract. Return 404 with error: “not_found” when nothing matches. Add unit tests in src/__tests__ covering: successful single-target lookup, successful “all platforms” lookup (source excluded), the canonical-term fallback path, invalid dialect (400), and unknown term (404).
Seed data — Update services/dbrosetta-api/prisma/seed.ts to add the 8 new terms and their per-platform equivalents listed in “Seed Data” below, using the same upsert/createMany pattern already used for the WAL term. Keep it idempotent (safe to re-run).
WordPress plugin:
includes/class-dbrosetta-client.php: add lookup_term_equivalents(string $term, string $sourceDialect, array $targetDialects = []) that POSTs { term, sourceDialect, targetDialects } to /terms/lookup and returns the decoded response or a WP_Error. Keep the existing get_dialects() method — use it to drive both dropdowns/checkboxes instead of any hardcoded option list.
templates/search-form.php: replace the current form with: a term text input (required); a required single-select “I know this term in” dropdown built from get_dialects(); a “Translate to” control offering an “All platforms” checkbox plus one checkbox per dialect (also built from get_dialects()), letting the user pick one, several, or all.
dbrosetta.php: update the shortcode handler to read term, source_dialect, and target_dialects[] from $_POST, sanitize each, require term and source_dialect, and call the new client method instead of search_terms().
templates/search-results.php: replace the current results block with rendering for the new response shape — the term’s description, the confirmed source-platform equivalent, and a Platform | Equivalent Term | Notes table for the returned results. Reuse the existing .dbrosetta-equivalents / .dbrosetta-table CSS classes where they fit; add only the minimal new CSS needed for the “All platforms” + checkbox control in assets/dbrosetta.css. On 404, reuse the existing “no results” block style with a message naming the term and source platform.
Seed Data (use verbatim — do not alter or invent facts)
For each concept below, create one Term (canonicalTerm/category/subcategory/description as given) and one TermEquivalent row per platform (platform string + notes as given; dialectId resolved from the matching dialect).
Buffer Pool / Buffer Cache — category System, subcategory Memory — description: “The in-memory cache of recently used data pages that reduces physical disk I/O.”
SQL Server: Buffer Pool — “Managed as part of sys.dm_os_buffer_descriptors; sized via ‘max server memory’.”
PostgreSQL: Shared Buffers — “Configured via the shared_buffers setting; PostgreSQL also relies heavily on the OS page cache.”
Oracle: Database Buffer Cache — “Part of the SGA; sized via DB_CACHE_SIZE or automatic memory management.”
MySQL: InnoDB Buffer Pool — “Configured via innodb_buffer_pool_size; caches both data and indexes for InnoDB tables.”
Row Versioning / MVCC Mechanism — category System, subcategory Concurrency — description: “The mechanism that lets readers see a consistent snapshot of data without blocking writers, by keeping prior versions of changed rows.”
SQL Server: Version Store — “Lives in tempdb; used by snapshot isolation and read-committed snapshot isolation (RCSI).”
PostgreSQL: MVCC — “Multiversion Concurrency Control; old row versions are retained until vacuumed, and changes are protected by the WAL.”
Oracle: Undo Segments — “Undo tablespace stores before-images used for read consistency and rollback.”
MySQL: InnoDB Undo Logs — “InnoDB’s MVCC implementation; undo logs support both rollback and consistent non-locking reads.”
Clustered Table Storage — category System, subcategory Storage — description: “Whether and how a table’s data rows are physically ordered on disk according to a key.”
SQL Server: Clustered Index — “A table has at most one; data rows are stored in key order in the index’s leaf level.”
PostgreSQL: Heap Table (no persistent clustering) — “Tables are unordered heaps by default; CLUSTER reorders rows once but does not maintain order on later writes.”
Oracle: Index-Organized Table (IOT) — “An alternative to a normal heap table where the table itself is stored as a B-tree index on its primary key.”
MySQL: Clustered Index (InnoDB Primary Key) — “InnoDB always clusters the table by its primary key; a table without an explicit primary key gets a hidden one.”
Identity / Auto-Increment Column — category DDL, subcategory Schema — description: “A column that automatically generates a unique, incrementing value for new rows.”
SQL Server: IDENTITY — “IDENTITY(seed, increment) property on a column.”
PostgreSQL: GENERATED ALWAYS AS IDENTITY / SERIAL — “SQL-standard IDENTITY columns (preferred) or the legacy SERIAL pseudo-type backed by a sequence.”
Oracle: IDENTITY Column — “Native IDENTITY columns since 12c; earlier versions combine a SEQUENCE with a trigger.”
MySQL: AUTO_INCREMENT — “AUTO_INCREMENT column attribute; only one per table, and it must be indexed.”
Temporary Workspace for Sorts/Spills — category System, subcategory Storage — description: “The storage area the database engine uses for sort operations, hash joins, and other data that spills to disk.”
SQL Server: tempdb — “A shared system database used for temp tables, sort/hash spills, and the version store.”
PostgreSQL: temp_tablespaces / Temporary Files — “Configured via temp_tablespaces; spill files are written under the pgsql_tmp directory.”
Oracle: TEMP Tablespace — “A dedicated temporary tablespace used for sorts, hash joins, and global temporary tables.”
MySQL: tmpdir / Internal Temporary Tables — “Controlled by the tmpdir setting; internal temp tables may be in-memory or on-disk depending on size and engine.”
Deadlock Diagnostic Artifact — category System, subcategory Concurrency — description: “The information the engine records when it detects and breaks a deadlock, used to diagnose the conflicting transactions.”
SQL Server: Deadlock Graph — “An XML deadlock graph captured via Extended Events (or the older trace flag 1222).”
PostgreSQL: Deadlock Detected Log Entry — “Logged to the server log when log_lock_waits/deadlock_timeout trigger detection; no XML graph, just structured log text.”
Oracle: ORA-00060 Deadlock Trace File — “Oracle raises ORA-00060 and writes a trace file to the diagnostic destination describing the waiters.”
MySQL: LATEST DETECTED DEADLOCK — “Found in the output of SHOW ENGINE INNODB STATUS, describing the transactions and locks involved.”
Query Execution Plan — category DQL, subcategory Query — description: “The plan the optimizer chooses to physically execute a query, showing operators, order, and estimated/actual cost.”
SQL Server: Execution Plan — “Viewable as estimated or actual plans, graphically or as XML, via SSMS or SET SHOWPLAN options.”
PostgreSQL: Query Plan (EXPLAIN) — “Produced by EXPLAIN [ANALYZE]; text-based tree of plan nodes with costs and, with ANALYZE, actual timings.”
Oracle: Explain Plan — “Produced by EXPLAIN PLAN FOR or the SQL*Plus AUTOTRACE/DBMS_XPLAN utilities.”
MySQL: EXPLAIN Output — “Produced by EXPLAIN [ANALYZE] or the optimizer trace; tabular by default, tree-style with EXPLAIN FORMAT=TREE.”
Optimizer Statistics — category System, subcategory Query Optimization — description: “The metadata about data distribution and cardinality that the query optimizer uses to choose a plan.”
SQL Server: Statistics — “Objects visible in sys.stats; maintained automatically via AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS.”
PostgreSQL: Planner Statistics — “Collected by ANALYZE (often via autovacuum) and stored in pg_statistic; consumed by the planner via pg_stats.”
Oracle: Optimizer Statistics — “Gathered and managed via the DBMS_STATS package, typically on an automated maintenance job.”
MySQL: Index/Table Statistics — “InnoDB persistent optimizer statistics (innodb_stats_persistent) refreshed by ANALYZE TABLE or background sampling.”
Examples (minimum required)
prompts/examples/directional-term-lookup/fixtures/minimal/input.json -> prompts/examples/directional-term-lookup/expected/minimal/output.json (happy path: single target platform)
prompts/examples/directional-term-lookup/fixtures/edge/input.json -> prompts/examples/directional-term-lookup/expected/edge/output.json (all-platforms, source excluded, case-insensitive input)
prompts/examples/directional-term-lookup/fixtures/no-match/input.json -> prompts/examples/directional-term-lookup/expected/no-match/output.json (unknown term, 404)
Post Processing
Run npx prisma generate and npx prisma migrate diff (or equivalent) to confirm the Prisma schema matches migration V4 before committing.
Run flyway validate and flyway migrate against a local/dev database to confirm V4 applies cleanly on top of V1–V3.
Run npm test in services/dbrosetta-api (unit tests, including the new term-lookup tests) and npm run seed to confirm the seed script is idempotent (run it twice, diff row counts).
Manually smoke-test the three fixtures below against the running API with curl/Postman and diff the response against the expected output (ignoring timestamps/ids).
Load the WordPress shortcode locally (or in a staging environment) and visually confirm: the source dropdown and target checkboxes are populated from /dialects, “All platforms” excludes the source platform from results, and the 404 case renders the existing no-results styling.
Artifact persistence: write run artifacts (test output, migration output, screenshots if applicable) to artifacts/<run_id>/.
Telemetry keys to record
prompt_version, model_settings, fixtures_hash, run_id, timestamp
Notes
This supersedes the search behavior originally specified in prompts/iterate-the-design-first-pass.md. The SQL-syntax translation feature (the translations table, Translation Prisma model, and its grid in the old search-results.php) is intentionally left in place structurally but removed from this page’s UI — it may get its own shortcode/page in a future pass, but that is out of scope here. SQLite remains only as legacy free-text data in term_equivalents and should not be added to dialects or exposed in the UI as part of this change.
Location for fixtures: prompts/examples/directional-term-lookup/
I’m not joking when I say I’m excited to see what Claude Code makes of this crazy prompt!
A key point I should make here: when I started this project, I wasn’t as experienced with the use of Agentic AI as I am now. I’ve learned a lot in this time.
For example, I’d now absolutely insist on a few more things from the AI at this point. I would push it to keep a log of what worked and what didn’t to avoid doing something silly multiple times. I would also be clearer about defining any measures needed to ensure the work is successful.
You did what, Claude?! (When the AI acts without asking)
I’m sitting there watching Claude Code do it’s thing – Determining, Honking, Noodling – when I notice it’s gone on a search through my laptop to see if there’s a running instance of PostgreSQL. There wasn’t, but it found my shutdown PostgreSQL installation and fired it up.
And I’m about to have a cow when I realize why: it wanted to validate the database changes and code before it tried to deploy.
You could have knocked me over with a feather.
I mean, look. Spelunking through my system trying to find a PostgreSQL cluster to connect to – yeah, I’m not happy with that. But making darned sure it can validate stuff before deploying it? HALLELUJAH!
Where we arrived, I had a functional app with all the changes I outlined. Cool. I went to test it, entered the one example I gave, and simply assumed it would be there. The response was as expected: ‘transaction log’, and there was no data returned.
OK. This is fine.
I go back and ask Visual Studio (VS) Code and it does a little digging, determining that the Prisma seed script for updating the database hadn’t run. The what now?
Yeah, it had used Prisma as an ORM (object-relational mapping), and then decided to start doing database deployments through it – despite all our other deployments going through Redgate Flyway. So, we had to roll that work back out of the code and then move it all into a migration script.
What I learned (again)
I feel like I’ve worked hard on getting guidance and guardrails in place over how the AI behaves. Yet still, the darned thing just seemingly bypasses them all when it’s in a mood. It’s reinforcing to me, repeatedly, that you absolutely must build a great big barrier between this insanely useful tool and your production systems.
AI is truly useful – but also wildly dangerous. Nothing I’ve done with it has hurt, but it’s only ever deploying through a defined pipeline. It’s not able to directly access “production” in a meaningful way.
The 3 key takeaways from my experience
Here are the 3 key takeaways from my trials and tribulations. It’s what I learned, to help you not repeat the same mistakes!
Don’t come into an old project with new assumptions
I’ve picked up quite a few new methods since I started this project. Silly me, I assumed they were in place. I should have told the AI to keep a log of what it attempted, what worked and what didn’t – stuff like that.
Different engines are…different
Comparing Claude and CoPilot’s answers to my various prompts for this project, you really see differences in behavior. Granted, the same prompt submitted twice in the same engine can also result in differences answers.
However, answers and behavior are two completely different things. It’s only when you change the AI engine that you notice the differences in behavior.
Re-examine everything if/when you change AI engine
I went at this like ‘meh, they’ll basically be the same in their behavior.’‘ Well, as made clear above, they are not the same! Sure, some patterns are the same (why do they want to deploy with that ORM tool so badly?), but others are unique.
Even with a valid and tested set of guidelines, if you swap the AI engine you’re using, you probably need to re-examine them – and how you work with the tool in general.
The last word: Claude’s take on dbRosetta right now
I asked Claude where it thinks dbRosetta stands right now. Here’s its response:
The core feature work is sound: the schema change was additive, the API contract was specific enough to leave little room for invention, and the plugin now answers the question you actually want answered instead of a generic keyword search. The deployment story is more turnkey too. None of that makes dbRosetta production software, but the discipline underneath it — Flyway-only schema changes, admin-gated writes, HTTPS-only calls — is real, and it’s what caught the Prisma detour before it did damage.
That detour is the more honest data point. Given a database change, I reached for the ORM’s own deploy path instead of the migration tool the project actually requires, and went looking for a Postgres instance on your machine without asking — a good instinct (validate before shipping) executed as an unauthorized action. Both were caught by you noticing a symptom, not by anything stopping the behavior at the source. Worth remembering as you widen my Azure access: scope that credential tighter than feels necessary, and verify compliance in the diff, not the prompt.
Simple Talk is brought to you by Redgate Software
FAQs
1. What feature was added to dbRosetta in this part of the series?
A directional term-lookup feature that replaces the original keyword search: users pick the platform they already know a term in, then choose one or more target platforms to see the equivalent term on each.
2. What did the AI agent do without being asked?
It searched the author’s laptop for a running PostgreSQL instance to validate database changes before deploying, found a shut-down installation, and started it up on its own.
3. Did the AI cause any deployment problems?
Yes. It used Prisma’s own deployment path for a database change instead of Flyway, the migration tool the project actually requires, so the author had to roll that work back out and move it into a proper migration script.
4. What's the main lesson from this part of the series?
That guardrails and guidelines you’ve set for an AI agent can still get bypassed, so anything touching real infrastructure needs a defined pipeline between the AI and production – not just trust in the prompt.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments