Windsurf prompt to set up a Postgres database schema
Schema decisions are cheap now and brutal later, so this prompt makes them explicit: timestamptz, snake_case, justified ON DELETE choices, and indexes tied to call sites Cascade finds in your repository layer. Windsurf applies the migrations and prints the table structure, so you review applied reality, not intentions.
Last updated
Design a Postgres schema for {{resource}} management as numbered migration files, not a dump. Tables: users, one per {{resource}}, and a join table with a composite primary key, all carrying created_at and updated_at with a trigger keeping updated_at current. Conventions: snake_case identifiers, text over varchar, timestamptz everywhere, and foreign keys with an explicit ON DELETE choice justified in a SQL comment per relation. Add indexes only for access patterns visible in code: read the repository layer first and cite the call site behind each index. Every migration gets a matching rollback file. Apply the migrations to the local database from the Windsurf terminal, run one insert, update, and select round trip, and paste the psql \d output for each table.Customize it
Runs in your browser. Nothing you type here is sent anywhere.
Design a Postgres schema for users management as numbered migration files, not a dump. Tables: users, one per users, and a join table with a composite primary key, all carrying created_at and updated_at with a trigger keeping updated_at current. Conventions: snake_case identifiers, text over varchar, timestamptz everywhere, and foreign keys with an explicit ON DELETE choice justified in a SQL comment per relation. Add indexes only for access patterns visible in code: read the repository layer first and cite the call site behind each index. Every migration gets a matching rollback file. Apply the migrations to the local database from the Windsurf terminal, run one insert, update, and select round trip, and paste the psql \d output for each table.
Same task in other tools
Questions about this prompt
Why must every index cite a call site?
Speculative indexes slow writes and obscure which queries actually need help. Making Cascade quote the repository code behind each index keeps the schema honest to real access patterns.
How do I adapt this when migrations already exist?
Tell Cascade the migration tool and the next sequence number, and instruct it to extend rather than restart. It should read the latest applied migration first so new files build on the current state.
The updated_at trigger never fires. What happened?
Either the trigger was created in a migration that ran before the function it calls, or an ORM issues updates that bypass it. Have Cascade check migration order, then run the insert, update, select round trip and compare both timestamps.