161 lines
4.5 KiB
Markdown
161 lines
4.5 KiB
Markdown
# Supabase Database Tests
|
|
|
|
This directory contains comprehensive pgTAP tests for the Xtablo database, covering all tables, RLS policies, triggers, functions, views, and indexes.
|
|
|
|
## Prerequisites
|
|
|
|
- Supabase CLI installed (minimum version v1.11.4)
|
|
- Local Supabase project initialized
|
|
|
|
## Test Files
|
|
|
|
The tests are organized into 10 files, covering different aspects of the database:
|
|
|
|
1. **01_schema_structure.test.sql** - Tests table existence, columns, data types, and basic constraints
|
|
2. **02_rls_policies_core.test.sql** - Tests RLS policies for core tables (tablos, tablo_access, tablo_invites)
|
|
3. **03_rls_policies_notes.test.sql** - Tests RLS policies for notes and note sharing
|
|
4. **04_rls_policies_other.test.sql** - Tests RLS policies for feedbacks and events
|
|
5. **05_triggers.test.sql** - Tests all database triggers and trigger functions
|
|
6. **06_stripe_functions.test.sql** - Tests Stripe integration functions and security
|
|
7. **07_views.test.sql** - Tests database views and their behavior
|
|
8. **08_indexes_performance.test.sql** - Tests index coverage and performance optimizations
|
|
9. **09_notifications.test.sql** - Tests notifications table structure, triggers, and trigger behavior
|
|
10. **10_rls_policies_notifications.test.sql** - Tests RLS policies for notifications table
|
|
|
|
## Running Tests
|
|
|
|
### Run All Tests
|
|
|
|
To run all database tests:
|
|
|
|
```bash
|
|
supabase test db
|
|
```
|
|
|
|
### Run Specific Test File
|
|
|
|
To run a specific test file:
|
|
|
|
```bash
|
|
supabase test db --file supabase/tests/database/01_schema_structure.test.sql
|
|
```
|
|
|
|
### Run Tests with Verbose Output
|
|
|
|
```bash
|
|
supabase test db --verbose
|
|
```
|
|
|
|
## Test Coverage
|
|
|
|
### Tables Tested
|
|
|
|
- profiles
|
|
- feedbacks
|
|
- tablos
|
|
- tablo_access
|
|
- tablo_invites
|
|
- events
|
|
- notes
|
|
- shared_notes
|
|
- note_access
|
|
- notifications
|
|
|
|
### RLS Policies Tested
|
|
|
|
- ✅ User isolation and access control
|
|
- ✅ Tablo ownership and sharing
|
|
- ✅ Note privacy and public sharing
|
|
- ✅ Event access based on tablo permissions
|
|
- ✅ Feedback insertion restrictions
|
|
- ✅ Notification visibility and update permissions
|
|
|
|
### Triggers Tested
|
|
|
|
- ✅ Auto-creation of tablo_access for owners
|
|
- ✅ Profile last_signed_in updates
|
|
- ✅ Tablo invite status updates on login
|
|
- ✅ Stripe subscription profile updates
|
|
- ✅ Notification creation for all major table changes (tablos, tasks, events, notes, tablo_access, tablo_invites)
|
|
|
|
### Functions Tested
|
|
|
|
- ✅ Stripe customer and subscription queries
|
|
- ✅ User payment status checks
|
|
- ✅ Security definer permissions
|
|
- ✅ Active subscription retrieval
|
|
|
|
### Views Tested
|
|
|
|
- ✅ user_tablos view with access levels
|
|
- ✅ active_subscriptions view
|
|
- ✅ Security invoker settings
|
|
|
|
### Indexes Tested
|
|
|
|
- ✅ Foreign key indexes
|
|
- ✅ Query optimization indexes
|
|
- ✅ Unique constraints
|
|
- ✅ Performance coverage
|
|
|
|
## Test Results
|
|
|
|
After running tests, you'll see output like:
|
|
|
|
```
|
|
supabase/tests/database/01_schema_structure.test.sql .. ok
|
|
supabase/tests/database/02_rls_policies_core.test.sql .. ok
|
|
supabase/tests/database/03_rls_policies_notes.test.sql .. ok
|
|
supabase/tests/database/09_notifications.test.sql .. ok
|
|
supabase/tests/database/10_rls_policies_notifications.test.sql .. ok
|
|
All tests successful.
|
|
Files=10, Tests=382, 1 wallclock secs
|
|
Result: PASS
|
|
```
|
|
|
|
## Troubleshooting
|
|
|
|
### Test Failures
|
|
|
|
If tests fail, check:
|
|
|
|
1. All migrations have been applied to your local database
|
|
2. The stripe schema exists (from stripe-sync-engine)
|
|
3. The pgTAP extension is installed
|
|
|
|
### Missing pgTAP Extension
|
|
|
|
If you get errors about pgTAP not being found, ensure it's enabled in your Supabase project.
|
|
|
|
### Database State
|
|
|
|
Tests use transactions and rollback at the end, so they won't affect your database state. Each test file creates its own test data and cleans up automatically.
|
|
|
|
## Continuous Integration
|
|
|
|
These tests can be integrated into your CI/CD pipeline:
|
|
|
|
```bash
|
|
# In your CI script
|
|
supabase start
|
|
supabase db reset
|
|
supabase test db
|
|
```
|
|
|
|
## Writing New Tests
|
|
|
|
When adding new features:
|
|
|
|
1. Add schema tests to `01_schema_structure.test.sql`
|
|
2. Add RLS policy tests to the appropriate RLS test file
|
|
3. Add trigger/function tests to `05_triggers.test.sql` or `06_stripe_functions.test.sql`
|
|
4. Update the plan count at the top of each file
|
|
|
|
Use the pgTAP documentation for available test functions: https://pgtap.org/documentation.html
|
|
|
|
## Total Test Count
|
|
|
|
- **382 tests** across 10 test files (71 notifications schema/triggers/NULL-actor + 19 notifications RLS)
|
|
- Comprehensive coverage of all database components
|
|
- Security-focused testing for RLS and permissions
|
|
- Full notification system coverage including triggers, RLS, and NULL actor (service role) scenarios
|