What this prompt does
This prompt assigns the AI a senior database engineer role and makes it specify a runnable schema for a new feature, not a loose sketch. You describe the feature in [feature_description], pick your [db_engine] and [migration_tool], and state [scale]. It returns an ASCII ER diagram, CREATE TABLE statements with constraints and foreign keys, a migration file in your tool's format, seed data, the anticipated query patterns, and the exact indexes each pattern needs.
The structure works because it ties indexes to read patterns up front instead of bolting them on later. By asking for the query patterns and the indexes that support each, the prompt forces the design to account for how the tables will actually be read. [migration_tool] shapes the file format so the output drops into your stack, [db_engine] governs valid DDL, and [scale] drives the note on which columns will grow and whether the design holds. It aims for 3NF and only denormalises where it justifies the choice.
When to use it
- You are starting a new feature and want the schema right before any UI exists.
- The data model will outlive every other decision and you want it deliberate, not improvised.
- You need migrations, seed data, and indexes in one pass rather than three separate sessions.
- You want indexes chosen from real read patterns instead of retrofitted under load.
- You are unsure where to denormalise and want each exception justified.
- You want a quick ER diagram to sanity-check relationships before writing code.
Example output
You get an ASCII ER diagram of the new tables, the CREATE TABLE DDL with constraints and foreign keys, a reversible migration file in your chosen tool's format, a seed script with three representative rows per table covering normal and edge cases, a mapping of anticipated query patterns to supporting indexes, and a note on which columns grow and how the design holds at the stated scale.
Pro tips
- Make
[feature_description]concrete — "team workspaces with per-member roles" yields a sharper schema than a one-word feature name. - Match
[migration_tool]to your actual stack so the migration file is paste-ready rather than something you have to translate. - Set
[scale]realistically; it drives the growth note and whether the model flags columns that will balloon. - Decide indexes from the read patterns the model surfaces, since retrofitting them under production load is the painful path.
- Push back on denormalisation that is not justified — the prompt aims for 3NF, and unexplained shortcuts are worth questioning.
- Read the growth note carefully and flag any column it warns will balloon, since those are the ones that bite at the stated scale.
- Use the seed data's edge-case rows to test constraints early, before the table is full of real records, so a bad foreign key or null surfaces in development rather than production.
