Claude Code prompt to set up a Postgres database schema
A schema is easier to review as \d output than as a wall of CREATE TABLE statements. This prompt has Claude Code derive entities from your actual application code, apply the migrations against a local Postgres, and paste what the database reports back, including a down-and-up rollback pass so the migration files are proven reversible.
Last updated
Design and apply a Postgres schema for this app's {{resource}} domain. Read the application code first and derive entities from how the code actually uses data, then show me an entity list with relationships before writing SQL. Rules: every table gets a bigint identity or uuid primary key, created_at and updated_at with a trigger for the latter, foreign keys with explicit ON DELETE behavior chosen per relation and justified in a comment, NOT NULL by default, and CHECK constraints for enums instead of raw text. Write it as numbered migration files that apply cleanly to an empty database. Include indexes only for lookups the code performs today, each with a comment naming the query it serves. Apply the migrations to a local database, run \d on each table, and paste the output. Finish with a rollback test: migrate down and up again without errors.Customize it
Runs in your browser. Nothing you type here is sent anywhere.
Design and apply a Postgres schema for this app's users domain. Read the application code first and derive entities from how the code actually uses data, then show me an entity list with relationships before writing SQL. Rules: every table gets a bigint identity or uuid primary key, created_at and updated_at with a trigger for the latter, foreign keys with explicit ON DELETE behavior chosen per relation and justified in a comment, NOT NULL by default, and CHECK constraints for enums instead of raw text. Write it as numbered migration files that apply cleanly to an empty database. Include indexes only for lookups the code performs today, each with a comment naming the query it serves. Apply the migrations to a local database, run \d on each table, and paste the output. Finish with a rollback test: migrate down and up again without errors.
Same task in other tools
Questions about this prompt
I do not have Postgres running locally. Can the verification still happen?
Ask Claude Code to start a disposable container with Docker, apply the migrations there, and paste the \d output before tearing it down. The rollback pass runs in the same container.
The database already has tables. How does the prompt change?
Make it additive: have the agent introspect the live schema first, then write only new numbered migrations that build on what exists. Applied migrations are never edited, which keeps other environments reproducible.
Every foreign key came back as ON DELETE CASCADE. Should I accept that?
No. Cascade is a per-relation decision, which is why the prompt demands a justifying comment on each one. Challenge any cascade that can reach user data; RESTRICT with application-level cleanup is often the safer answer.