This section contains documentation for all database-related aspects of Elluminar, including schema design, migrations, triggers, and Row Level Security (RLS) policies.
Database Provider: Supabase (PostgreSQL)
Version: PostgreSQL 15+
Authentication: Supabase Auth integrated with custom user tables
schema/supabase-schema.md- Complete database schema with all tables, relationships, and constraints
migrations/trigger-migration.md- Database trigger setup for automatic user creation- Physical Migrations: Located in
../../scripts/supabase/migrations/
rls-policies/- Row Level Security policy documentation and examples
- Database Triggers: Automatically create user records when Supabase Auth creates users
- Profile Creation: Default consultee profiles created automatically
- CUID Integration: Bridge between Supabase Auth UUIDs and existing CUID system
- User Isolation: Users can only access their own data
- Role-Based Access: Different permissions for consultants, consultees, staff, and admins
- Service Role Access: Elevated permissions for database triggers and server operations
users: Central user table with CUID and email bridge to Supabase AuthConsultantProfile: Consultant-specific data and ratingsConsulteeProfile: Consultee preferences and communication settingsStaffProfile: Staff and admin user profilesDomain: Consultation domains/categories
- Email-Based Linking: Uses email as natural bridge between auth systems
- Foreign Key Integrity: Proper relationships between all tables
- Audit Fields: Created/updated timestamps on all tables
- Review Schema: Start with
schema/supabase-schema.md - Understand Migrations: Read
migrations/trigger-migration.md - Check RLS Policies: Review security model in
rls-policies/
- Migration Setup: Use files in
../../scripts/supabase/migrations/ - Monitor Triggers: Check trigger execution and performance
- RLS Monitoring: Ensure security policies are working correctly
- Supabase Automatic: Daily automated backups
- Point-in-Time Recovery: Available for the last 7 days
- Manual Snapshots: Created before major migrations
- Query Performance: Monitor slow queries and optimize
- Trigger Performance: Track trigger execution times
- Connection Pooling: Supabase handles connection management
- RLS Policy Review: Regular review of security policies
- Access Logging: Monitor database access patterns
- Permission Audits: Verify user permissions are appropriate
-
Initial Schema (August 2025)
- Created all core tables
- Set up basic RLS policies
- Established foreign key relationships
-
Trigger-Based Authentication (August 31, 2025) ⭐
- Added
handle_new_user()trigger function - Updated RLS policies for server-side operations
- Resolved authentication context issues
- Status: ✅ Successfully deployed and tested
- Added
- Symptom: RLS permission errors during client operations
- Solution: Database triggers handle user creation server-side
- Documentation: Authentication Troubleshooting
- Symptom: Migration scripts fail to apply
- Solution: Check permissions and dependencies
- Documentation: Migration Troubleshooting
- Symptom: Slow queries or trigger execution
- Solution: Analyze query plans and add indexes
- Monitoring: Use Supabase dashboard performance metrics
- Check Logs: Supabase dashboard → Logs section
- Query Performance: Dashboard → Performance insights
- Schema Visualization: Dashboard → Table editor
- SQL Editor: Dashboard → SQL editor for debugging
- Authentication Documentation - How authentication integrates with database
- Development Documentation - Database integration in the app
- Troubleshooting - Common database issues