# Draft Database Schema Task

Generate comprehensive database schema from project requirements with Row Level Security (RLS) and performance optimization.

## Workflow Steps

### 1. Requirements Analysis
**Objective:** Understand project data needs and user workflows

**Process:**
- Analyze project requirements document (PRD) if available
- Identify core business entities and relationships
- Map user personas to data access patterns
- Define security and compliance requirements
- Assess scalability and performance needs

**Interactive Elicitation (elicit=true):**
- Project name and domain
- Core entities (users, products, orders, etc.)
- Entity relationships (one-to-one, one-to-many, many-to-many)
- Authentication requirements (Supabase Auth integration?)
- Data access patterns and user roles
- Security level (Basic, Standard RLS, Advanced multi-tenant)
- Performance requirements (concurrent users, data volume)

### 2. Entity Design
**Objective:** Design robust data models with proper relationships

**Process:**
- Create primary entities with appropriate attributes
- Define data types optimized for PostgreSQL/Supabase
- Establish foreign key relationships
- Plan for soft deletes and audit trails
- Design for extensibility and future requirements

**Output:**
- Entity relationship diagram (text-based)
- Table definitions with columns and data types
- Relationship mapping and constraints
- Index planning for performance

### 3. Security Implementation
**Objective:** Implement Row Level Security (RLS) and authentication integration

**Process:**
- Enable RLS on all user-facing tables
- Create security policies based on user roles and ownership
- Integrate with Supabase Auth using auth.uid()
- Design multi-tenant data isolation if required
- Plan audit logging and compliance tracking

**Output:**
- RLS policy definitions
- Security model documentation
- Authentication integration points
- Compliance and audit trail setup

### 4. Performance Optimization
**Objective:** Optimize schema for query performance and scalability

**Process:**
- Create strategic indexes for common query patterns
- Plan for database partitioning if needed
- Design efficient pagination and filtering
- Optimize for read vs write performance based on usage
- Plan caching strategies at database level

**Output:**
- Index definitions and rationale
- Query optimization guidance
- Scalability planning notes
- Performance monitoring recommendations

### 5. Schema Generation
**Objective:** Generate production-ready SQL schema file

**Process:**
- Use database-schema-tmpl.yaml template
- Generate complete PostgreSQL schema with:
  - Table definitions with constraints
  - Row Level Security policies
  - Performance indexes
  - Trigger functions (updated_at, etc.)
  - Sample data insertion (optional)
- Create schema migration strategy
- Generate rollback procedures

**Files Created:**
- `schema-[timestamp].sql` - Complete database schema
- `schema-documentation-[timestamp].md` - Comprehensive documentation
- `rls-policies-[timestamp].sql` - Security policies (if separate)
- `indexes-[timestamp].sql` - Performance indexes (if separate)

### 6. Documentation and Validation
**Objective:** Create comprehensive documentation and validate design

**Process:**
- Generate schema documentation using database-docs-tmpl.yaml
- Create API integration examples
- Document security model and user access patterns
- Provide troubleshooting and monitoring guidance
- Generate test data and validation scripts

**Output:**
- Complete schema documentation
- Integration examples for common frameworks
- Security implementation guide
- Performance monitoring setup
- Test and validation procedures

## Success Criteria

- ✅ Schema supports all identified business requirements
- ✅ Row Level Security properly isolates user data
- ✅ Performance optimized for expected load patterns
- ✅ Compatible with Supabase platform and tools
- ✅ Comprehensive documentation for development team
- ✅ Validation scripts and test data provided
- ✅ Migration and rollback procedures documented

## Integration Points

- **Next Step:** Use provision-supabase-backend.md to deploy schema to live database
- **Templates:** database-schema-tmpl.yaml, database-docs-tmpl.yaml
- **Validation:** database-testing.md for connectivity and security testing
- **Optimization:** database-optimization.md for performance tuning