You have probably seen the demos of AI-powered code generators. You type in a prompt like “build a multi-tenant project management SaaS,” and a few minutes later, you have a beautiful dashboard, a frontend router, and a database schema generated in the background. It feels incredibly fast, but once you peak under the hood - particularly at the database schema - the magic starts to show its seams.
Large Language Models (LLMs) are excellent at predicting text and writing UI components, but they do not understand relational algebra. When an AI agent scaffolds a database, it optimizes for the easiest path to render a visual element. The result is almost always a collection of flat, denormalized, and fragile tables that will break the moment you load real production data.
If you are building an application using modern AI code generators like Bolt or Lovable, you cannot afford to let the AI make arbitrary database decisions. You have to guide it. You can write prompts that force the AI to respect classical database rules, keep your relational schemas clean, and protect your data integrity.
Why LLMs Fail at Relational Database Design
Database engines operate on strict, mathematical set theory. To prevent data anomalies, update conflicts, and slow queries, a database requires a structured relational model. LLMs, on the other hand, operate on semantic probability. They write schemas based on what looks visually plausible in code, which introduces three common architectural failures.
The Denormalized Flat Table Trap
AI tools hate creating join tables. It is much easier for an LLM to build a single users table and cram a list of comma-separated string tags directly into a text field than it is to set up a proper junction table. When your frontend needs to filter users by their tags, the AI writes complex, unindexed regular expressions to parse that text field. This approach works fine when you have three test records in your browser, but it will collapse your database CPU as soon as your user base scales.
Bypassing Constraints
In a relational database, constraints are your last line of defense. They prevent empty records, guarantee unique emails, and ensure that deleting a parent record clean up its child records. Because constraints require strict logical configuration, AI generators regularly omit them. They will build tables without primary keys, omit NOT NULL flags on mandatory fields, and ignore foreign keys entirely. This makes the database highly fragile, where a single bug in your application layer can corrupt your entire data set.
Schema Drift and Regression Loops
If you let your AI builder generate database changes incrementally as you visually modify the frontend, you will quickly encounter database schema drift. The AI will write a new migration file that conflicts with your existing tables, drop columns containing real data to resolve a rename conflict, or create redundant tables to support a new visual component.
To avoid these problems, you need to enforce relational database rules from the very first prompt.
Rule 1: Enforce Third Normal Form (3NF)
To keep your data clean and queries fast, your tables must be normalized. Normalization is the process of organizing data to minimize redundancy and dependency. In a professional application, you should aim for Third Normal Form (3NF).
To get normalized tables from an AI, you must explicitly define how it should handle attributes:
- First Normal Form (1NF): Every column must contain atomic (indivisible) values. The AI must never store comma-separated lists, JSON arrays, or semi-structured strings inside a single database field.
- Second Normal Form (2NF): All non-key attributes must be fully dependent on the primary key. If a table has a composite key, every column must relate to the entire key, not just a part of it.
- Third Normal Form (3NF): No column should depend on another non-key column (no transitive dependencies). For example, if you have a
projectstable, do not store the client name and client phone number directly in it. The project should link to aclientstable using a foreign key, and the client details should live there.
The Normalization Prompt Template
When initializing a database using SQL DDL or prompting your AI builder, use an explicit system rule:
“When generating the relational database schema, design all tables in Third Normal Form (3NF). Do not use JSON arrays, JSONB columns, or comma-separated text fields to store multi-valued attributes or relations. Instead, extract these into normalized child tables or junction tables with foreign key constraints. Ensure that non-key attributes depend strictly on the primary key, avoiding transitive dependencies.”
By forcing the AI to work under these constraints, you prevent it from taking shortcuts to save developer effort on the UI side.
Rule 2: Explicitly Define Data Integrity Constraints
A database without constraints is a ticking time bomb. If you want a database that remains clean and consistent, you must instruct the AI to write explicit rules directly into the SQL schema definition.
Primary and Foreign Keys
Always demand that every table has a single, clearly defined primary key. For modern web applications, UUIDs are generally preferred over autoincrementing integers because they prevent ID enumeration attacks and allow you to generate IDs safely on the client side before writing to the database.
Furthermore, every relation must use explicit foreign keys. If a tasks table has a project_id field, that field must point directly to the projects table. You must also instruct the AI to specify deletion behaviors:
- Use
ON DELETE CASCADEif the child record should be deleted when the parent is deleted (e.g., deleting a project should delete its tasks). - Use
ON DELETE RESTRICTorON DELETE SET NULLif the child records should remain intact (e.g., deleting a user should not delete the history of team logs they authored).
Not Null, Unique, and Check Constraints
Never let the AI create columns that accept NULL values by default unless the field is genuinely optional. Email fields, usernames, and organization IDs should always be defined as NOT NULL.
Unique constraints should be explicitly set on fields like emails, tokens, or custom slugs. Finally, check constraints (CHECK) are highly useful to prevent invalid data states before they reach your tables. For example, a status column should be restricted to a specific list of strings (like pending, active, or archived).
The Integrity Prompt Template
Incorporate these rules into your generation workflow:
“For every database table, you must define a primary key using UUIDs. Every relation must be enforced with a foreign key constraint, explicitly stating the deletion policy (use CASCADE for owned sub-resources, and RESTRICT or SET NULL for independent entities). Apply NOT NULL to all mandatory fields. Use UNIQUE constraints for unique identifying columns, and use CHECK constraints to restrict status columns to valid string sets.”
Rule 3: Design Relational Junction Tables
Many-to-many relationships are a common point of failure for AI generators. If users can belong to multiple workspaces, or projects can have multiple tags, you need a junction table to connect them.
A proper junction table should contain:
- A foreign key pointing to the first table.
- A foreign key pointing to the second table.
- A composite primary key composed of both foreign keys to prevent duplicate relationships.
- Cascading deletion rules so that removing a user or workspace automatically cleans up the junction record.
When you prompt your AI, it will often try to bypass this structure because managing joins in React components requires slightly more frontend code. You must resist this. Force the AI to use junction tables, and let it write the relational queries (using JOIN statements in SQL or relational queries in Supabase client libraries) to load the data.
The Pragmatic Builder’s Workflow: Schema First
To maintain full control of your application, you should separate database design from frontend development. Letting an AI write your database schema while it is designing your buttons is a recipe for regression loops.
Instead, follow this workflow:
- Design the schema first: Ask the AI to write a raw SQL DDL file based on the rules we outlined.
- Review the SQL manually: Ensure that every table is normalized, constraints are defined, and indexes are set up on foreign keys.
- Execute the migrations: Run the SQL script directly in your database editor (such as the Supabase SQL editor).
- Import the schema into your builder: Once the tables are live in your database, prompt your AI builder (like Lovable or Bolt) to read the existing schema rather than generating a new one.
This schema-first approach ensures that your database remains clean, standard, and fully structured, even when the AI writes the frontend code.
An Alternative: Structured No-Code Without Database Overhead
Managing SQL migrations, foreign key constraints, and normalized structures requires a lot of developer overhead, even with AI assistance. If you are building internal business tools, directory sites, or client portals, you do not always need to manage a raw Postgres database.
This is where structured visual builders like Softr offer a different approach. Rather than writing SQL DDL, you can connect your application directly to structured data platforms like Airtable, Google Sheets, or SmartSuite.
This model provides several distinct advantages:
- Visual relationship management: You can set up relational fields (like linking a client to a project) visually in your data source without worrying about raw SQL junction tables or foreign key deletion policies.
- Granular user permissions: Softr handles user permissions and roles visually in the editor, meaning you do not have to write complex database security policies (like Postgres Row Level Security) to keep client data private.
- Predictable development: Because you are configuring pre-built components rather than prompting an LLM to regenerate code, you avoid the regression loops that plague generated apps. You can change your layout or add new database fields without any risk of breaking existing queries.
If you need a highly custom SaaS MVP with complex background compute, prompting database schemas for a Postgres backend is a great path. But if you need to deploy an operational tool, using a structured tool like Softr will save you hours of database maintenance.