Replit Agent prompt to set up a Postgres database schema
Schema work is the one place where letting a builder improvise costs you later, because constraints are cheap now and migrations are not. This prompt has Replit Agent encode the rules in Postgres itself, with reversible migrations and a violation test per table. You end up with a schema that rejects bad data before application code ever sees it.
Last updated
Set up the Postgres schema for {{resource}} in this Repl using the database integration, with migrations checked into the project rather than hand-run SQL.
Tables: the core {{resource}} table plus its natural satellites, an owners reference, a status history table, and tags with a many-to-many join table. Every table gets created_at and updated_at with defaults, and updated_at maintained by one mechanism used everywhere, pick it and state it.
Constraints do the enforcement, not application code: NOT NULL on required columns, UNIQUE where a duplicate would mean corruption, foreign keys with an explicit ON DELETE choice per relation stated in a comment, and CHECK constraints on enum-like status columns.
Indexes: cover the queries the app will actually run, the list view sort, the owner filter, and the tag lookup. Name them consistently. No speculative index on every column.
Migrations: incremental files with both up and down, runnable from the workspace shell with a single documented command. Never edit an applied migration, always add a new one.
Seed: an idempotent script inserting a small realistic dataset. Running it twice must not duplicate rows.
Verify from the shell and paste the output: the migration command against a clean database, the seed run twice, a query plan for the list view showing its index being used, and one deliberate constraint violation per table proving the database rejects bad data. Finish by listing every file you created and what each contains.Customize it
Runs in your browser. Nothing you type here is sent anywhere.
Set up the Postgres schema for users in this Repl using the database integration, with migrations checked into the project rather than hand-run SQL. Tables: the core users table plus its natural satellites, an owners reference, a status history table, and tags with a many-to-many join table. Every table gets created_at and updated_at with defaults, and updated_at maintained by one mechanism used everywhere, pick it and state it. Constraints do the enforcement, not application code: NOT NULL on required columns, UNIQUE where a duplicate would mean corruption, foreign keys with an explicit ON DELETE choice per relation stated in a comment, and CHECK constraints on enum-like status columns. Indexes: cover the queries the app will actually run, the list view sort, the owner filter, and the tag lookup. Name them consistently. No speculative index on every column. Migrations: incremental files with both up and down, runnable from the workspace shell with a single documented command. Never edit an applied migration, always add a new one. Seed: an idempotent script inserting a small realistic dataset. Running it twice must not duplicate rows. Verify from the shell and paste the output: the migration command against a clean database, the seed run twice, a query plan for the list view showing its index being used, and one deliberate constraint violation per table proving the database rejects bad data. Finish by listing every file you created and what each contains.
Same task in other tools
Questions about this prompt
Why require down migrations while prototyping?
While you iterate in the workspace you will want to step one schema change backward without wiping data. Down files give you that, and combined with a checkpoint they make schema experiments cheap to abandon.
My status values change often, is a CHECK constraint too rigid?
If users can define statuses, move them to a lookup table with a foreign key. Keep CHECK for genuinely fixed machine states. Converting later is a small migration now and a painful backfill once bad values exist.
The seed script duplicates rows every rerun, what is wrong?
It is using plain inserts. The prompt requires idempotence, so have the agent switch to upserts keyed on a natural unique column, then run it twice from the shell and diff the row counts.