Database & Schema Designer for Internal Apps (Entities → Relations → Validation Rules)
Produce a normalized Postgres schema with constraints, indexes, RLS notes, and sample seed data tailored to your workflow.
Prompt Overview
Featured AI Partner
Tips For You
Add created_at/updated_at to every table; index FKs; model approval state transitions explicitly; keep enums in check constraints for portability.
From Operations TeamNexusAi TechnologyProblem It Solves
Ad-hoc tables cause data issues later. This creates a robust schema and validation plan upfront.
Production DDL
Ready-to-run Postgres SQL with constraints.
RLS Blueprints
Role policies aligned to least privilege.
Index Plan
Hot-path performance notes and indexes.
Seed Data
Realistic inserts for instant testing.
AI Prompt Instructions
Act as: A data architect specializing in Postgres for internal CRUD and approvals systems using Supabase.
Why this task matters: Good schemas prevent duplicate data, broken approvals, and slow queries. Clear constraints protect operations and audits.
Important boundaries:
- Prefer 3NF with pragmatic denormalization for hot reads.
- Enforce data integrity in the database, not just the UI.
- Assume multi-tenant readiness via org_id or project_id when applicable.
User inputs:
- Entity list with key fields
- Relationships and approval states
- Query hotspots and reporting needs
- Security model (roles and access rules)
Objectives:
1) Normalize entities and relationships.
2) Propose primary/foreign keys, unique constraints, and check constraints.
3) Define indexes for critical queries.
4) Outline RLS policies and auth notes for Supabase.
5) Provide seed data and migration script outline.
Analysis workflow:
1) Build an entity/state map and identify lifecycle events.
2) For each entity, define columns, types, nullability, and defaults.
3) Specify constraints and cascading behavior.
4) Propose indexes (BTREE/GIN) with rationale.
5) Draft RLS policies per role for SELECT/INSERT/UPDATE/DELETE.
6) Provide SQL DDL and example seed inserts.
Required output format:
- ER Overview (text)
- SQL DDL blocks with comments
- RLS Policy list
- Index plan and rationale
- Seed data examples
- Migration checklist
Quality controls:
- Every FK is indexed.
- No ambiguous nullable foreign keys.
- Check constraints for enums/states.
Verification checklist:
- CRUD paths supported for each role
- Critical queries covered by indexes
- RLS denies cross-tenant access by default
Final instruction: Output production-ready SQL and a brief Supabase setup checklist.
Expected Outcome
ER Overview: org, user, role, request, approval, comment. Requests have states: draft, submitted, approved, rejected. SQL DDL: create table org (...); create table request (..., org_id uuid references org(id) on delete restrict, state text check (state in ('draft','submitted','approved','rejected'))); Indexes: on request(org_id, state), GIN on request(search_tsv). RLS: enable on request; policy org_read for select using (org_id = auth.org_id()).
Implementation Journey
Design schema in ChatGPT
Paste your entity list, relationships, and role matrix into ChatGPT. Run the prompt to get ER notes, SQL DDL with constraints, and RLS policies. Review the rationale for indexes.
25 minCreate DB in Supabase
Open Supabase SQL editor and apply the DDL. Enable RLS and add generated policies. Use the seed data to populate a test project and verify constraints.
20 minValidate queries
Run your top 5 queries in Supabase. Confirm indexes are used and latency is acceptable. Adjust index definitions if the query plan suggests full scans.
15 min