Reviewing an AI-Proposed Schema Change

The CREATE TABLE script in front of you was written by an assistant, and it parses cleanly. Review it in two passes: read the script itself for what it leaves unsaid, then import it into your DbSchema design model and compare that model against the database, so the only statement that reaches the server is a migration script you approved.

The first pass catches what a syntax check never will, because the omissions are legal SQL. The second pass catches the rest, by putting the proposal next to the schema you already have instead of next to nothing. DbSchema's AI Assistant makes the gate hard to skip: it returns the SQL in a code block in the panel and executes nothing against your database.

What a generated schema script leaves out

Here is a proposal for two tables, as PostgreSQL 17 would accept it:

CREATE TABLE projects (
  id bigserial PRIMARY KEY,
  name varchar(255) NOT NULL
);
CREATE TABLE tasks (
  id bigserial PRIMARY KEY,
  project_id bigint,
  title varchar(255) NOT NULL,
  status varchar(255)
);

Four things are missing from those ten lines, and none of them is an error the parser can raise.

The column project_id carries no REFERENCES clause, so the relationship exists in the column name only. Nothing rejects a task whose project_id was never a project, and nothing decides what happens to the tasks when a project row is deleted. The relationship is also invisible to every tool that reads the catalog, which is why a diagram drawn from this script shows two unconnected boxes.

Adding the constraint later fixes the integrity but not the reads. The PostgreSQL 17 documentation is explicit that the declaration of a foreign key constraint does not automatically create an index on the referencing columns, and that because deleting a referenced row requires a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. On a table of a few thousand rows nobody notices; on a few million, every parent delete pays for a sequential scan.

The status column is varchar(255) and nullable, which states nothing about the values it holds. If the application writes four statuses, a CHECK constraint or an enum type says so in the schema, and a NULL then means something specific instead of meaning nothing in particular. And varchar(255) on title is a default rather than a decision: the length that belongs there is the one the application enforces.

What the DbSchema AI Assistant is given

The review is easier when you know what the model was working from. In DbSchema, that is a list you build yourself: only the DDL of the tables you attach travels with the question, meaning the table, column, type and relationship definitions. The AI Assistant never connects to the database and never reads table contents, so query results, row values, passwords and connection details stay inside DbSchema.

1. Click the vertical AI Assistant tab on the right edge of the DbSchema window.
2. Click Attach, then the plus button, to open Attach Table DDL.
3. Tick the schema, or just the tables the question is about.
4. Read the preview, which is the exact text sent, then send the question.

Attaching two tables instead of thirty is worth the extra click twice over: the answer is more precise because the context is smaller, and the authentication tables and audit logs never appear in a prompt at all. Nothing in this step touches the database or the model file, since attaching DDL only reads what DbSchema has already reverse-engineered. Where even table names must not leave the machine, select Ollama in AI Settings and run a local model, and nothing is sent anywhere. What travels and what does not is set out in what an AI assistant sees of your database.

Reading the script before anything runs

The answer comes back as Markdown with the SQL inside a code block, which is the point at which you are still the only one who can run it. Read for the statements that cannot be taken back, and for the ones that take a lock while they work.

The third row is the one that surprises people. In PostgreSQL 17, adding a column with a non-volatile default evaluates the default once and stores the result in the table's metadata, and in neither that case nor the no-default case is a rewrite of the table required. Adding a column with a volatile default, or changing the type of an existing column, does require the entire table and its indexes to be rewritten. ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a subform is noted otherwise, so a rewrite on a large table is a lock every other query queues behind.

Check the dialect while you are there. A type name, a collation, or an identity column that is valid on one engine is a syntax error on another, so the script has to be read against the engine you are deploying to rather than against SQL in general.

Where the generated script meets the design model

Running the script straight against a database leaves you with no record of what changed and no way to read it as a diff, which is the first thing to want from a schema comparison. DbSchema keeps the schema in a local project file of plain XML, so the proposal can be brought into that file first and read the way you read any other change.

1. Open Model → Import from External Format and leave Source on Files.
2. Select the file holding the CREATE TABLE statements. DbSchema opens it in an SQL Editor and parses the statements into the model, with no column mapping step.
3. Open Schema → Compare Model with Database.
4. Work through the diff, which lists added, removed and modified tables, columns, indexes and foreign keys, and choose per object whether to update the model, push the change to the database, or skip it.

A folder of migration scripts takes the same route with Source set to "Folder with migration scripts" and Target set to Current Design, which replays the scripts in the order a database would have applied them and opens the synchronization dialog against the model you already have. Nothing is executed while it reads: the scripts are parsed as text, and no connection is needed.

Which half of the pair you change is a choice you make per difference. Accepting a difference into the model rewrites the .dbs file and leaves the database untouched. Pushing one to the database is the opposite. When the model is the version you want, Schema → Synchronize Model with Database generates the migration statements, which you can edit in the dialog before clicking Execute. Execute is the only step in the whole review that changes the server. Save the model to its .dbs file before you synchronize, so the previous state is on disk if the deployment has to be undone. Saving the model to a file and schema synchronization are both in the Pro edition.

Constraints and indexes to confirm before deployment

The repairs belong in the model, and the mode you are in decides where they land. Connected, every schema change you make in DbSchema is executed against the database as you make it; disconnected, it is saved only to the .dbs file until you synchronize. Choose Disconnected from the connection menu for a review, so the corrections gather in the model and arrive at the database as one migration you have read. Then drag from the connector handle on tasks.project_id to projects.id, and DbSchema creates the foreign key and draws the relationship line; double-click that line to open the Foreign Key Editor and set what happens to the children when a parent goes.

NO ACTION is the PostgreSQL default and raises an error while a referencing row still exists. CASCADE deletes the referencing rows with the parent. SET NULL and SET DEFAULT put a null or the column default in their place. Picking one is a domain decision that the generated script silently made for you by omitting it, and it is worth a minute of thought: a task that outlives its project is either a bug or a requirement, and only you know which.

The corrected pair, as the model then generates it:

ALTER TABLE tasks
  ADD CONSTRAINT tasks_project_id_fkey FOREIGN KEY (project_id)
  REFERENCES projects (id) ON DELETE CASCADE;
CREATE INDEX tasks_project_id_idx ON tasks (project_id);
ALTER TABLE tasks
  ADD CONSTRAINT tasks_status_check CHECK (status IN ('open', 'active', 'blocked', 'done'));

Test that on a copy of production-sized data before it goes near production, and time it. The foreign key itself is cheap to add; the index behind it is the statement whose duration you want to know in advance.

What to watch after the change is live

A migration that ran cleanly can still be the wrong change, and the evidence shows up in the application before it shows up in the database. Compare request latency on the endpoints that hit the changed tables against the numbers from before the deployment, and read the slow query log for statements that were fast last week.

The index you added is the one claim worth checking directly. PostgreSQL counts index scans in pg_stat_user_indexes, where idx_scan is the number of index scans initiated on that index:

SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'tasks';

An idx_scan still at zero a day after deployment means the planner is not using the index, and the index is now pure write overhead on every insert into tasks. Either the query you built it for is shaped differently from what you assumed, or it never runs. Both are worth knowing before the next release adds a second index on the same guess.

Download DbSchema, reverse-engineer the database you are responsible for, and run one AI-proposed change through the compare dialog before you run it anywhere else. The .dbs model file commits alongside the application code that depends on it, so last release's schema is still in your history when a change has to be reversed. Schema synchronization and saving the model to a file are Pro features, and everything the review needs before that point, the connection, the reverse-engineered diagram and the SQL editor, is in the free Community edition. The AI Assistant is a separate credits subscription that works on Community, Pro and Architect alike, while using your own provider key requires Architect, and a new installation starts with a 15-day Architect trial.

Frequently asked questions

What is an ai schema generator?

An ai schema generator turns a description in plain English into CREATE TABLE statements. DbSchema's AI Assistant does this from inside the design model, so the statements come back written for the database you are connected to and can be merged into the diagram rather than retyped.

Can an online schema generator see my actual database data?

DbSchema's AI Assistant sends the DDL of the tables you attach and nothing else. It never connects to the database on the assistant's behalf, and the attachment preview shows the exact text before it leaves, so the answer is one you read rather than one you take on trust.

Why do AI-generated schemas often fail at scale?

The statements that hurt at scale are legal at any size, so nothing rejects them on the way in. A foreign key without an index behind it is the clearest case: PostgreSQL creates no index for the referencing columns, and the cost only appears once the child table is large enough for a sequential scan to matter.

How should I test a json schema test data generator output?

Load it into a staging copy that carries the same constraints as production, because the constraints are what tell you whether the generated values are valid. DbSchema's Data Generator, in the Pro edition, fills the tables in the order you set, so a referenced table is populated before the table that refers to it. Its load_values_from_pk pattern then takes each foreign key value from the primary keys already in the referenced table.

What is the safest way to apply an AI-proposed schema change?

Treat the output as a draft script rather than a migration. Import it into the DbSchema design model, compare that model against the live database, settle each difference in the model, and let the synchronization dialog generate the migration you finally execute.