Legacy: Database Setup (Supabase/Postgres)
This guide preserves the previous Day 1 database setup workflow using Supabase and PostgreSQL. VibeReference now defaults to Convex + Clerk. Use this only for legacy projects.
1.4 Database Setup Introduction (Supabase/Postgres)
Goal: Define the data structure (schema) required for the web application, creating a set of migration files that will fully install a custom Postgres database into your Supabase instance. The migration files include:
000-foundation.sql- Establishes extensions, schemas, and basic types001-structure.sql- Defines core tables and utility functions002-relationships.sql- Establishes relationships between tables and optimizes query performance003-security.sql- Implements security, automation, and API functionsseed.sql- Populates reference tables with initial dataset
Process: Follow this chat pattern with an AI chat tool such as Claude.ai. Pay attention to the notes in [brackets] and replace the brackets with your own thoughts and ideas.
Timeframe: 30-45 minutes
Table of Contents
- 1.4.1: Initial Schema Analysis
- 1.4.2: Foundation Migration
- 1.4.3: Structure Migration
- 1.4.4: Relationships Migration
- 1.4.5: Security Migration
- 1.4.6: Seed Data
- 1.4.7: Schema Validation
- 1.4.8: Schema Refinement (Optional)
- 1.4.9: Installation Instructions
Have this link open and ready to copy/paste in: 0-learn/supabase-database-patterns.md
1.4 Database Setup Generation
1.4.1: Initial Schema Analysis
You are a Supabase and Postgres and SQL expert tasked with helping an entrepreneur turn product requirements into a well-structured Supabase database. You'll help analyze requirements and create properly organized migration files.
Migration Overview: We are starting with initial schema analysis only. This step focuses on understanding our database requirements without writing any SQL code yet. Later steps will handle the actual migration files (foundation, structure, relationships, and security).
Please paste your completed `product-requirements.md` document from step 1.1 and the `supabase-database-patterns.md` file below:
<product-requirements>
{{product-requirements.md}}
</product-requirements>
<supabase-database-patterns>
{{supabase-database-patterns.md}}
</supabase-database-patterns>
Supabase has reserved schemas with special purposes that can be made use of when it makes sense (see `0-learn/supabase-database-patterns.md`). My custom schemas should be organized as:
#### Custom Schema Structure
- `api` - User-generated content and application data, including user-specific instances of templates
- `internal` - Sensitive internal operations not directly accessible to users, financial data (credit balances, transactions), and system-generated records requiring special access controls
- `reference` - Publicly available lookup tables and reusable templates for features that have both templates and user customizations
#### Optional Custom Schemas (use if needed by requirements)
- `analytics` - Reporting data (primarily views)
- `audit` - Tracking changes
- `config` - Application configuration data
- `stripe` - Synced data from Stripe webhooks (if using Stripe)
Based on these documents, please analyze the core entities needed for your database schema:
1. The schema organization:
- What custom schemas should we create?
- What types of data should go in each schema?
- What enums should we place in the reference schema?
- Should we create domain types for common patterns (like email, url)?
2. Core business objects specific to your application:
- What are the main entities that represent your application's unique value?
- How do these entities relate to users?
- What timestamps and audit fields should be included?
- Should we implement soft deletes with deleted_at timestamps?
3. Profile and authentication extensions:
- How should we extend the default auth.users table?
- What additional user-related data do we need to store?
- How should we handle user roles and permissions?
4. Subscription and billing tables (if applicable):
- Do we need to implement Stripe integration tables?
- What customizations do we need for your pricing model?
- How will we sync subscription data between Stripe and our application?
5. Usage tracking and analytics requirements:
- What usage metrics do we need to track?
- How will this data be structured for reporting?
- Do we need any materialized views for analytics?
For each entity, please identify:
- Key attributes/columns
- Primary identifiers (UUIDs vs serial IDs)
- Basic relationships to other entities
- Access patterns (who needs to read/write this data)
Don't write SQL yet - just create an initial analysis of entities, relationships, and key security/access patterns.
1.4.2: Foundation Migration
Thank you for the initial analysis. Now let's create our first migration file: `000-foundation.sql` which will establish the foundation of our database structure.
Migration Overview: We are at step 1 of 4 in our migration process. This first migration (000-foundation.sql) will ONLY set up extensions, schemas, and basic types. Later migrations will handle tables, relationships, and security policies.
When creating database schemas, follow these best practices:
- Clearly comment out what product requirement it is necessary for and why
- Use UUIDs as primary keys for better distribution and security
- Include created_at and updated_at timestamps on all tables
- Use foreign key constraints to maintain referential integrity
- Consider soft deletes with a deleted_at timestamp instead of hard deletes
In this migration, we'll include:
1. EXTENSIONS
- Enable all required PostgreSQL extensions for your application
- It's acceptable to explicitly list default extensions using `CREATE EXTENSION IF NOT EXISTS`
- Include both Supabase defaults and additional extensions needed for your functionality
- Only enable an optional extension if it is a must have AND is available in the list of optional extensions in `supabase-database-patterns`
2. SCHEMAS
- Create all custom schemas needed for our application
- Document the purpose of each schema
3. TYPES AND DOMAINS
- Define all enums and custom types
- Create domain types if needed (email, url, etc.)
- Place enums in the reference schema
Please generate the complete 000-foundation.sql file based on our application requirements.
1.4.3: Structure Migration
Great! Now let's create our second migration file: `001-structure.sql` which will define the core data structures of our application.
Migration Overview: We are at step 2 of 4 in our migration process. This second migration (001-structure.sql) focuses ONLY on creating reference tables, core tables, and utility functions. Most relationships and foreign keys will be handled in the next migration, but you CAN include foreign keys to the default auth.users table since it already exists in Supabase. Before proceeding, verify that your 000-foundation.sql file includes all necessary extensions, custom schemas, domain types, and enums that will be used by your tables. Do NOT include RLS policies, most triggers, or advanced indexes in this migration as these will be handled in subsequent migrations.
This migration will include:
1. REFERENCE TABLES
- Create lookup/reference tables in the reference schema
- These tables should contain static data that rarely changes
- Include appropriate constraints and descriptions
- Provide clear comments explaining the purpose of each table
2. CORE TABLES
- Define main business entity tables
- For each table, include:
- Primary key (using UUIDs)
- Required columns with appropriate data types
- Default values where applicable
- NOT NULL constraints
- CHECK constraints for data validation
- Timestamp fields (created_at, updated_at)
- Description of each column's purpose
- Clearly comment out what product requirement it is necessary for and why
- Verify each column has necessary constraints (NOT NULL, CHECK, etc.)
- Foreign keys TO auth.users table CAN be defined here
- Most other foreign keys should be saved for 002-relationships.sql
- Basic indexes on auth.users foreign keys may be included
- For user profiles, follow this pattern:
- Store extended user data in api.profiles, not directly in auth.users
- Link profiles to auth users with a foreign key reference to auth.users(id)
- Do NOT create complex triggers (save these for 003-security.sql)
3. UTILITY FUNCTIONS
- Create reusable utility functions that will be used by triggers and policies
- Follow security best practices for function definitions:
- Set explicit search paths to prevent injection (SET search_path = public, pg_temp)
- Use appropriate security context (SECURITY DEFINER vs INVOKER)
- Include proper error handling
- Organize functions by security needs:
- Place sensitive functions (financial, permissions) in the `internal` schema
- Create user-facing functions with permission checks in the `api` schema
- Use `public` schema only for general, non-sensitive utilities
- Consider creating "wrapper" functions in `api` schema that verify permissions before calling sensitive internal functions
- Verify that functions have appropriate comments explaining their purpose
- IMPORTANT: Ensure all functions referencing tables are defined AFTER those tables have been created
4. STRIPE TABLES (if applicable)
- Include ALL recommended Stripe tables, not just customers:
- stripe.customers - Maps Stripe customers to application users
- stripe.products - Stores product definitions
- stripe.prices - Stores pricing information
- stripe.subscriptions - Tracks active subscriptions
- stripe.webhook_events - Records webhook events
- stripe.invoices - Tracks invoice data
- stripe.payment_methods - Stores payment methods
- stripe.charges - Records charge data
Please generate the complete 001-structure.sql file.
1.4.4: Relationships Migration
Now let's create our third migration file: `002-relationships.sql` which will establish the relationships between our tables and optimize query performance.
Migration Overview: We are at step 3 of 4 in our migration process. This third migration (002-relationships.sql) focuses ONLY on defining relationships between tables, creating indexes, and setting up views. Security and RLS policies will be handled in the next migration. Before proceeding, verify that your previous migrations include all necessary tables referenced by foreign keys and any columns that will be indexed or used in views. Do NOT create triggers, RLS policies, new tables, or utility functions in this migration as these belong in other migration files.
IMPORTANT: To avoid conflicts with relationships already defined in 001-structure.sql, use DO blocks to check if constraints or indexes already exist before creating them. Unlike ADD COLUMN, the ADD CONSTRAINT syntax does NOT support IF NOT EXISTS.
For example, to safely add a foreign key constraint:
DO $$
BEGIN
-- Check if constraint doesn't already exist before adding it
IF NOT EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = 'fk_some_table_other_id' AND conrelid = 'api.some_table'::regclass
) THEN
ALTER TABLE api.some_table
ADD CONSTRAINT fk_some_table_other_id
FOREIGN KEY (other_id) REFERENCES api.other_table(id);
END IF;
END $$;
For indexes, use a similar pattern:
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_indexes WHERE indexname = 'some_table_other_id_idx'
) THEN
CREATE INDEX some_table_other_id_idx ON api.some_table(other_id);
END IF;
END $$;
This migration will include:
1. RELATIONSHIPS AND FOREIGN KEYS
- Define all relationships between tables that were NOT already established in 001-structure.sql
- Use DO blocks to check if constraints already exist before adding them
- Specify foreign key constraints with appropriate actions:
- ON DELETE (CASCADE, SET NULL, RESTRICT)
- ON UPDATE (CASCADE, RESTRICT)
- Add unique constraints or composite keys where needed
- Implement check constraints to enforce business rules
- IMPORTANT: PostgreSQL does not allow subqueries in CHECK constraints - use these alternatives instead:
- Use a foreign key constraint if the relationship is direct
- Implement a trigger function to enforce complex business rules
- Create stored procedures to validate data before insertion/update
- Clearly comment out what product requirement each relationship satisfies and why
2. INDEXES
- Create indexes for:
- Foreign keys that will be frequently queried
- Columns often used in WHERE clauses
- Columns used for sorting (ORDER BY)
- Consider composite indexes for multi-column queries
- Add GIN indexes for JSONB or array columns if needed
- Remember that each index:
- Speeds up read operations
- Slows down write operations
- Increases storage requirements
- Clearly comment out what product requirement each index supports and why
3. VIEWS
- Create views for:
- Common complex queries
- Reporting and analytics
- Joining data across schemas
- Consider materialized views only for complex analytics that are infrequently updated
- Clearly comment out what product requirement each view addresses and why
For foreign keys, follow this pattern:
- Name constraints clearly (e.g., fk_posts_user_id)
- Consider the impact of cascading deletes
- Index foreign key columns
Please generate the complete 002-relationships.sql file.
1.4.5: Security Migration
Now let's create our final migration file: `003-security.sql` to implement security, automation, and API functions.
Migration Overview: We are at step 4 of 4 in our migration process. This final migration (`003-security.sql`) focuses ONLY on implementing triggers, RLS policies, storage configuration, and custom API functions to secure our data and automate processes. Before proceeding, verify that your previous migrations include all necessary tables that need RLS policies, foreign key relationships needed by security policies, any views that need RLS policies, and utility functions needed by triggers.
When working with operations that affect multiple related tables (such as truncate operations), consider foreign key constraint impacts. Use CASCADE options appropriately, manage the order of operations carefully, and document any operations that could result in data loss.
This migration will include:
1. TRIGGERS
- Create triggers for:
- Updating timestamp fields (updated_at)
- Creating related records automatically
- Enforcing business rules
- Syncing data between tables
- Follow these best practices:
- Use proper schema qualification for function references
- Create explicit trigger statements (avoid dynamic SQL)
- Test trigger behavior thoroughly
- Handle the NEW record properly in trigger functions
- Clearly comment out what product requirement each trigger supports and why
2. RLS POLICIES
- Enable Row Level Security on all tables with user data
- Implement policies for each operation type:
- FOR SELECT - who can view which records
- FOR INSERT - who can create records
- FOR UPDATE - who can modify which records
- FOR DELETE - who can remove which records
- Use common policy patterns:
- Ownership-based access (users see only their data)
- Role-based access (permissions based on user roles)
- Relationship-based access (team members can see team data)
- For all tables, carefully consider:
- Who can create records (authentication, role requirements)
- Who can view records (ownership, team/group access, public visibility)
- Who can update records (owners, administrators, collaborators)
- Who can delete records (deletion permissions vs soft delete strategy)
- Clearly comment out what product requirement each policy supports and why
3. STORAGE CONFIGURATION
- Configure storage buckets based on access patterns:
- public - Openly accessible files
- protected - Authenticated-only access
- private - User-specific private files
- Apply RLS policies to storage buckets to control file access
- Always fully qualify column names to avoid ambiguity
4. CUSTOM API FUNCTIONS
- Define database functions for complex operations
- Ensure each function:
- Has proper security context
- Sets explicit search paths
- Includes error handling
- Returns appropriate types
- Clearly comment out what product requirement each function satisfies and why
For RLS policies, always be aware that:
- Tables with RLS enabled but no policies block all access by default
- Policies should be created before or immediately after enabling RLS
Please generate the complete `003-security.sql` file.
1.4.6: Seed Data
Now that we have our database schema defined, let's create a seed.sql file to populate our reference tables with essential data.
Migration Overview: All migration files are now complete. We're now creating a separate seed.sql file to populate reference tables with initial data. This is not a migration file but will be used after migrations to provide essential lookup data.
For seed data, focus exclusively on:
1. Populating reference tables with required values
2. Including standard lookup data the application needs to function
3. NOT inserting any user data or user-generated content
4. Providing only minimal reference data needed for operation
Examples of appropriate seed data include:
- Status options and types
- Category definitions
- Configuration settings
- Role definitions
- Permission types
- Standard options for dropdown menus
- Other static lookup values
For each reference table:
- Add a comment explaining what data is being inserted
- Use INSERT statements with appropriate values
- Provide consistent IDs for stable references
- Clearly comment out what product requirement each seed data entry supports and why
Please generate a complete seed.sql file we can save as 'supabase/seed.sql'.
1.4.7: Schema Validation
Now that we've created our migration files and seed data, let's validate our schema against the original `product-requirements.md` document and the `supabase-database-patterns.md` file.
Migration Overview: All migrations and seed data are complete. We're now validating our schema against requirements to ensure we haven't missed anything before implementation. No new SQL code will be generated in this step.
Please review our database design and compare it to both the original product requirements and database patterns:
1. Completeness check:
- Are all the key entities from the product requirements represented?
- Have we implemented all necessary relationships?
- Does the schema support all the main user flows described in the requirements?
- Is there anything missing from the core business objects?
2. Pattern implementation:
- Have we correctly organized our schemas according to the patterns?
- Is financial/billing data appropriately placed in the `internal` schema?
- Are templates and user-specific instances properly separated between `reference` and `api` schemas?
- Are we using proper authentication and profile patterns?
- Have we correctly implemented the subscription tables (if needed)?
- Are we following the security best practices for RLS policies?
- Do all our functions properly set search paths and security contexts?
3. Identify any gaps or misalignments:
- Are there any requirements not fully supported by this schema?
- Did we make any design compromises that should be documented?
- Are there any areas where we diverged from the patterns, and if so, why?
- Do we need any additional tables or relationships for future requirements?
4. Security assessment:
- Have we enabled RLS on all user-data tables?
- Do all sensitive tables have appropriate policies?
- Have we handled storage security properly?
- Are there any potential security holes in our design?
5. Scalability and performance considerations:
- Will this schema design scale with expected user growth?
- Have we identified potential performance bottlenecks?
- Are our indexing strategies appropriate for expected query patterns?
- Have we avoided premature optimization?
6. Database maintenance:
- Have we implemented proper triggers for timestamps and auditing?
- Are our foreign key constraints appropriate?
- Is our schema organized logically for future modifications?
Please provide a thoughtful analysis highlighting both the strengths of our schema design and areas that might need further refinement before final implementation.
1.4.8: Schema Refinement (Optional)
Based on our schema validation, I'd like to make some refinements before finalizing:
Migration Overview: This optional step allows for refinements to any of our previously created migration files based on the validation results. We may modify any of the four migration files or the seed data as needed.
1. [Add any concerns about the proposed schema]
2. [Mention any missing entities or attributes]
3. [Note any performance or scaling considerations]
4. [Suggest any simplifications or optimizations]
Please review these concerns and suggest specific modifications to our migration files to address these issues:
1. How should we modify our schema to address [concern 1]?
2. What additional tables or columns do we need for [concern 2]?
3. What indexing or optimization strategy do you recommend for [concern 3]?
4. How can we simplify [concern 4] while maintaining functionality?
Please provide specific SQL statements or modifications we should make to our migration files.
1.4.9: Installation Instructions
After generating your migration files and seed data, here's how to install them:
Using the Supabase Dashboard:
- Go to your Supabase project dashboard
- Navigate to the SQL Editor
- Create a new query for each migration file
- Run the migrations in order (000, 001, 002, 003)
- Run the seed.sql file last
- If you encounter any errors:
- Copy the complete error message
- Paste it back to Claude with a request to fix the issue
- Apply the corrected SQL script
Using the Supabase CLI locally:
- Save each migration file in the
supabase/migrations/directory following this naming pattern:20230101000000_foundation.sql20230101000001_structure.sql20230101000002_relationships.sql20230101000003_security.sql
- Save the seed script as
supabase/seed.sql - Run
supabase db resetto apply all migrations and seed data - This gives you a fresh database with schema and test data each time
For your production environment:
- Apply seed data only for essential reference tables
- Remove any test accounts before deployment
After running these, your database will be fully set up with proper security policies, optimizations, and initial data for your application.