GitHub Copilot prompt to set up a Postgres database schema
Schema decisions are cheap to get right now and brutal to fix after launch. This prompt asks GitHub Copilot for raw SQL migrations, cascade rules chosen table by table, a shared updated_at trigger, and a partial unique index on invitations, then walks you through a delete test that proves the cascade behavior.
Last updated
Design a Postgres schema for a multi-tenant app as numbered SQL migration files in db/migrations/, no ORM models yet. Tables: organizations, users, memberships joining the two with a role column constrained by a CHECK to owner, admin, or member, and invitations with a unique token and an expires_at. Emails use citext with a unique index, every table gets created_at and updated_at as timestamptz defaulting to now(), and updated_at is maintained by one shared trigger function, written once, applied per table. Foreign keys choose deletion behavior deliberately: memberships cascade when their organization dies, invitations do too, but users never cascade from anything. Add a partial unique index so a user holds at most one pending invitation per organization. Include a down migration for every up. To verify, I will run the migrations against a scratch database, insert one org with two members, delete the org, and confirm users survive while memberships vanish.
Same task in other tools
Questions about this prompt
Why raw SQL instead of letting an ORM generate the schema?
CHECK constraints, partial unique indexes, and shared trigger functions are exactly the things ORM DSLs express badly or skip. Write the SQL first and map ORM models over it afterwards if you want them.
How do I fit soft deletes into this design?
Add deleted_at timestamptz where needed and change unique indexes to partial ones with WHERE deleted_at IS NULL. Ask for that explicitly, otherwise the plain unique index will block re-inviting a previously removed user.
Copilot wrote a separate trigger function for every table. Is that a problem?
It works but leaves you several copies to keep in sync. The requirement is one function referenced by many CREATE TRIGGER statements, so reject the duplicates and restate the shared-function constraint.