thrilltrack-explorer/docs/submission-pipeline/VALIDATION_SETUP.md

# Schema Validation Setup Guide

This guide explains how to set up and use the automated schema validation tools to prevent field mismatches in the submission pipeline.

## Overview

The validation system consists of three layers:

1. **Pre-migration Script** - Quick validation before deploying migrations
2. **Integration Tests** - Comprehensive Playwright tests for CI/CD
3. **GitHub Actions** - Automated checks on every pull request

## Quick Start

### 1. Add NPM Scripts

Add these scripts to your `package.json`:

```json
{
  "scripts": {
    "validate-schema": "tsx scripts/validate-schema.ts",
    "test:schema": "playwright test schema-validation",
    "test:schema:ui": "playwright test schema-validation --ui",
    "pre-migrate": "npm run validate-schema"
  }
}
```

### 2. Environment Variables

Create a `.env.test` file:

```env
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key_here
```

**⚠️ Important**: Never commit this file! Add it to `.gitignore`:

```gitignore
.env.test
.env.local
```

### 3. Install Dependencies

If not already installed:

```bash
npm install --save-dev @supabase/supabase-js @playwright/test tsx
```

## Using the Validation Tools

### Pre-Migration Validation Script

**When to use**: Before applying any database migration

**Run manually:**
```bash
npm run validate-schema
```

**What it checks:**
- ✅ Submission tables match main tables
- ✅ Version tables have all required fields
- ✅ Critical fields are correct (e.g., `category` vs `ride_type`)
- ✅ Database functions exist and are accessible

**Example output:**
```
🔍 Starting schema validation...

Submission Tables:
────────────────────────────────────────────────────────────────────────────────
✅ Parks: submission table matches main table
✅ Rides: submission table matches main table
✅ Companies: submission table matches main table
✅ Ride Models: submission table matches main table

Version Tables:
────────────────────────────────────────────────────────────────────────────────
✅ Parks: version table has all fields
✅ Rides: version table has all fields
✅ Companies: version table has all fields
✅ Ride Models: version table has all fields

Critical Fields:
────────────────────────────────────────────────────────────────────────────────
✅ rides table does NOT have ride_type column
✅ rides table has category column
✅ ride_models has both category and ride_type

Functions:
────────────────────────────────────────────────────────────────────────────────
✅ create_entity_from_submission exists and is accessible
✅ update_entity_from_submission exists and is accessible
✅ process_approval_transaction exists and is accessible

════════════════════════════════════════════════════════════════════════════════
Total: 15 passed, 0 failed
════════════════════════════════════════════════════════════════════════════════

✅ All schema validations passed. Safe to deploy.
```

### Integration Tests

**When to use**: In CI/CD, before merging PRs, after major changes

**Run all tests:**
```bash
npm run test:schema
```

**Run in UI mode (for debugging):**
```bash
npm run test:schema:ui
```

**Run specific test suite:**
```bash
npx playwright test schema-validation --grep "Entity Tables"
```

**What it tests:**
- All pre-migration script checks PLUS:
- Field-by-field data type comparison
- NOT NULL constraint validation
- Foreign key existence checks
- Known field name variations (e.g., `height_requirement_cm` vs `height_requirement`)

### GitHub Actions (Automated)

**Automatically runs on:**
- Every pull request that touches:
  - `supabase/migrations/**`
  - `src/lib/moderation/**`
  - `supabase/functions/**`
- Pushes to `main` or `develop` branches
- Manual workflow dispatch

**What it does:**
1. Runs validation script
2. Runs integration tests
3. Checks for breaking migration patterns
4. Validates migration file naming
5. Comments on PRs with helpful guidance if tests fail

## Workflow Examples

### Before Creating a Migration

```bash
# 1. Make schema changes locally
# 2. Validate before creating migration
npm run validate-schema

# 3. If validation passes, create migration
supabase db diff -f add_new_field

# 4. Run validation again
npm run validate-schema

# 5. Commit and push
git add .
git commit -m "Add new field to rides table"
git push
```

### After Modifying Entity Schemas

```bash
# 1. Modified rides table schema
# 2. Run full test suite
npm run test:schema

# 3. Check specific validation
npx playwright test schema-validation --grep "rides"

# 4. Fix any issues
# 5. Re-run tests
npm run test:schema
```

### During Code Review

**PR Author:**
1. Ensure all validation tests pass locally
2. Push changes
3. Wait for GitHub Actions to complete
4. Address any automated feedback

**Reviewer:**
1. Check that GitHub Actions passed
2. Review schema changes in migrations
3. Verify documentation was updated
4. Approve if all checks pass

## Common Issues and Solutions

### Issue: "Missing fields" Error

**Symptom:**
```
❌ Rides: submission table matches main table
   └─ Missing fields: category
```

**Cause**: Field was added to main table but not submission table

**Solution:**
```sql
-- In your migration file
ALTER TABLE ride_submissions ADD COLUMN category TEXT NOT NULL;
```

### Issue: "Type mismatch" Error

**Symptom:**
```
❌ Rides: submission table matches main table
   └─ Type mismatches: max_speed_kmh: main=numeric, submission=integer
```

**Cause**: Data types don't match between tables

**Solution:**
```sql
-- In your migration file
ALTER TABLE ride_submissions 
  ALTER COLUMN max_speed_kmh TYPE NUMERIC USING max_speed_kmh::numeric;
```

### Issue: "Column does not exist" in Production

**Symptom**: Approval fails with `column "category" does not exist`

**Immediate action:**
1. Run validation script to identify issue
2. Create emergency migration to add missing field
3. Deploy immediately
4. Update functions if needed

**Prevention**: Always run validation before deploying

### Issue: Tests Pass Locally but Fail in CI

**Possible causes:**
- Different database state in CI vs local
- Missing environment variables
- Outdated schema in test database

**Solution:**
```bash
# Pull latest schema
supabase db pull

# Reset local database
supabase db reset

# Re-run tests
npm run test:schema
```

## Best Practices

### ✅ Do's

- ✅ Run validation script before every migration
- ✅ Run integration tests before merging PRs
- ✅ Update all three tables when adding fields (main, submission, version)
- ✅ Document field name variations in tests
- ✅ Check GitHub Actions results before merging
- ✅ Keep SCHEMA_REFERENCE.md up to date

### ❌ Don'ts

- ❌ Don't skip validation "because it's a small change"
- ❌ Don't add fields to only main tables
- ❌ Don't ignore failing tests
- ❌ Don't bypass CI checks
- ❌ Don't commit service role keys
- ❌ Don't modify submission pipeline functions without testing

## Continuous Integration Setup

### GitHub Secrets

Add to your repository secrets:

```
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key_here
```

**Steps:**
1. Go to repository Settings → Secrets and variables → Actions
2. Click "New repository secret"
3. Name: `SUPABASE_SERVICE_ROLE_KEY`
4. Value: Your service role key from Supabase dashboard
5. Save

### Branch Protection Rules

Recommended settings:

```
Branch: main
✓ Require status checks to pass before merging
  ✓ validate-schema (Schema Validation)
  ✓ migration-safety-check (Migration Safety Check)
✓ Require branches to be up to date before merging
```

## Troubleshooting

### Script Won't Run

**Error:** `tsx: command not found`

**Solution:**
```bash
npm install -g tsx
# or
npx tsx scripts/validate-schema.ts
```

### Authentication Errors

**Error:** `Invalid API key`

**Solution:**
1. Check `.env.test` has correct service role key
2. Verify key has not expired
3. Ensure environment variable is loaded:
   ```bash
   source .env.test
   npm run validate-schema
   ```

### Tests Timeout

**Error:** Tests timeout after 30 seconds

**Solution:**
```bash
# Increase timeout
npx playwright test schema-validation --timeout=60000
```

## Maintenance

### Adding New Entity Types

When adding a new entity type (e.g., `events`):

1. **Update validation script:**
   ```typescript
   // In scripts/validate-schema.ts
   await validateSubmissionTable('events', 'event_submissions', 'Events');
   await validateVersionTable('events', 'event_versions', 'Events');
   ```

2. **Update integration tests:**
   ```typescript
   // In tests/integration/schema-validation.test.ts
   test('events: submission table matches main table schema', async () => {
     // Add test logic
   });
   ```

3. **Update documentation:**
   - `docs/submission-pipeline/SCHEMA_REFERENCE.md`
   - This file (`VALIDATION_SETUP.md`)

### Updating Field Mappings

When version tables use different field names:

```typescript
// In both script and tests
const fieldMapping: { [key: string]: string } = {
  'new_main_field': 'version_field_name',
};
```

## Related Documentation

- [Schema Reference](./SCHEMA_REFERENCE.md) - Complete field mappings
- [Integration Tests README](../../tests/integration/README.md) - Detailed test documentation
- [Submission Pipeline](./README.md) - Pipeline overview
- [Versioning System](../versioning/README.md) - Version table details

## Support

**Questions?** Check the documentation above or review existing migration files.

**Found a bug in validation?** Open an issue with:
- Expected behavior
- Actual behavior
- Validation script output
- Database schema snippets