# Best Practices

## When to Create Versions

✅ **DO:** Let triggers handle versioning automatically  
❌ **DON'T:** Manually call versioning functions  
❌ **DON'T:** Bypass triggers with direct SQL  

## Performance

- Run `cleanup_old_versions()` monthly
- Keep 50-100 versions per entity
- Use indexes for queries
- Implement pagination for large version lists

## Security

- Never expose `created_by` user IDs to public
- Always check RLS policies
- Validate rollback permissions server-side
- Use session variables for attribution

## Testing

Test version creation on:
- INSERT (creates version_number: 1)
- UPDATE (increments version_number)
- Rollback (creates new version with change_type='restored')

## Attribution

Always set `app.current_user_id` to original submitter, NOT moderator.

```typescript
// ✅ CORRECT
await supabase.rpc('set_session_variable', {
  key: 'app.current_user_id',
  value: submission.user_id,  // Original submitter
});

// ❌ WRONG
await supabase.rpc('set_session_variable', {
  key: 'app.current_user_id',
  value: auth.uid(),  // Moderator who approved
});
```

## Automated Cleanup

### pg_cron Schedule

The versioning system automatically cleans up old versions monthly via `pg_cron`:

- **Schedule:** 2 AM UTC on the 1st of each month
- **Retention:** 50 most recent versions per entity (configurable)
- **Tables affected:** All `*_versions` tables (`park_versions`, `ride_versions`, `company_versions`, `ride_model_versions`)

### Manual Cleanup

To run cleanup manually for a specific entity type:

```sql
-- Clean up old park versions, keeping 50 most recent per park
SELECT cleanup_old_versions('park', 50);

-- Clean up rides, keeping 100 versions
SELECT cleanup_old_versions('ride', 100);
```

Or use the TypeScript utility:

```typescript
import { cleanupVersions } from '@/lib/versioningUtils';

// Clean up old park versions
const deletedCount = await cleanupVersions('park', 50);
console.log(`Deleted ${deletedCount} old versions`);
```

### Monitoring Cleanup Jobs

Check pg_cron job status:

```sql
-- View all cleanup jobs
SELECT * FROM cron.job WHERE jobname LIKE 'cleanup%';

-- View recent job execution history
SELECT * FROM cron.job_run_details 
WHERE jobname LIKE 'cleanup%'
ORDER BY start_time DESC 
LIMIT 10;
```

### Adjusting Retention Policy

To keep more or fewer versions, update the cron schedule:

```sql
-- Unschedule existing job
SELECT cron.unschedule('cleanup-park-versions');

-- Reschedule with new retention count
SELECT cron.schedule(
  'cleanup-park-versions',
  '0 2 1 * *',  -- 2 AM UTC on 1st of month
  $$SELECT cleanup_old_versions('park', 100);$$  -- Keep 100 versions
);
```

### Version Storage Monitoring

Monitor version table sizes:

```sql
-- Count versions per entity type
SELECT 'parks' as entity_type, COUNT(*) as total_versions
FROM park_versions
UNION ALL
SELECT 'rides', COUNT(*) FROM ride_versions
UNION ALL
SELECT 'companies', COUNT(*) FROM company_versions
UNION ALL
SELECT 'ride_models', COUNT(*) FROM ride_model_versions;

-- Get oldest version date per entity type
SELECT 'parks' as entity_type, MIN(created_at) as oldest_version
FROM park_versions
UNION ALL
SELECT 'rides', MIN(created_at) FROM ride_versions;
```