Delete Non-Admin Users Script

Overview

The delete-non-admin-users.sh script provides a safe and comprehensive way to remove all non-admin users from the Momentum platform. This includes deleting users from both the database and AWS Cognito, along with all their associated data.

Location

scripts/database/delete-non-admin-users.sh

Purpose

This script is useful for:

Development/Testing: Cleaning up test users while preserving admin accounts
Data Privacy: Removing user data when required (e.g., GDPR compliance testing)
Environment Reset: Resetting a development or staging environment to a clean state

What Gets Deleted

The script removes the following data for non-admin users (users with role != 'ADMIN'):

Database Tables

chat_messages - Customer support chat messages
chat_sessions - Customer support chat sessions
email_logs - Email tracking and delivery logs
progress - User lesson progress and completion status
enrollments - Course enrollment records
payments - Payment transactions
user_activity_log - Activity tracking logs
user_badges - Achievement and badge awards
user_statistics - User performance statistics
users - User records themselves

External Services

AWS Cognito - User accounts in the Cognito User Pool

What Is Preserved

All users with role = 'ADMIN' are preserved
All courses, lessons, categories, and badges remain intact
All system data and configuration is untouched

Usage

Basic Usage

# Dry-run mode (default) - shows what would be deleted without making changes
./scripts/database/delete-non-admin-users.sh

# Or explicitly
./scripts/database/delete-non-admin-users.sh --dry-run

# Execute mode - actually performs the deletion
./scripts/database/delete-non-admin-users.sh --execute

Help

./scripts/database/delete-non-admin-users.sh --help

Safety Features

1. Dry-Run by Default

The script defaults to dry-run mode, showing exactly what would be deleted without making any changes.

2. Explicit Confirmation

When running in --execute mode, the script requires typing the exact confirmation phrase:

DELETE ALL NON-ADMIN USERS

3. Statistics Display

Before deletion, the script shows:

Total number of users
Number of admin users (that will be preserved)
Number of non-admin users (that will be deleted)
Count of all related data that will be deleted

4. Foreign Key Safety

The script deletes data in the correct order to respect database foreign key constraints:

Child records first (progress, enrollments, payments, etc.)
Cognito users
Database user records last

5. Admin Preservation

All SQL queries explicitly exclude admin users with WHERE role != 'ADMIN' to ensure they are never deleted.

Backup Strategy

⚠️ IMPORTANT: Before running this script in execute mode, ensure you have a backup strategy.

Creating a Manual Snapshot (Recommended)

Before deleting users, create a manual snapshot of your Aurora cluster:

# Create a manual snapshot with timestamp
aws rds create-db-cluster-snapshot \
  --db-cluster-identifier momentum-cluster \
  --db-cluster-snapshot-identifier pre-user-deletion-$(date +%Y%m%d-%H%M%S)

Verifying Automated Backups

Check when the last automated backup was taken:

# Get latest restorable time for your cluster
aws rds describe-db-clusters \
  --db-cluster-identifier momentum-cluster \
  --query 'DBClusters[0].LatestRestorableTime' \
  --output text

Point-in-Time Recovery

Aurora Serverless supports point-in-time recovery (PITR):

Retention Period: Typically 7-35 days (check your configuration)
Granularity: Can restore to any point within the retention period
Recovery Time: Usually 5-15 minutes

To restore to a point in time:

# Restore to a specific time (before deletion)
aws rds restore-db-cluster-to-point-in-time \
  --db-cluster-identifier momentum-cluster-restored \
  --source-db-cluster-identifier momentum-cluster \
  --restore-to-time "2025-12-21T10:00:00Z" \
  --use-latest-restorable-time

Recovery Procedure

If you need to recover deleted users:

Stop application traffic to prevent new data
Create a new cluster from the snapshot or PITR
Verify data integrity in the restored cluster
Update application to point to restored cluster
Migrate Cognito users (requires separate recovery)

Note: Cognito users cannot be restored from Aurora backups. You’ll need to:

Re-create Cognito accounts manually, or
Have users sign up again, or
Restore from a Cognito backup if available

Cost Considerations

Manual Snapshots: Free storage for up to 100% of your database size
Automated Backups: Included in Aurora pricing
Restored Cluster: Charges apply while running

Best Practices

Always test in development first
Create a manual snapshot before execution
Verify the snapshot was created successfully
Document the snapshot identifier for easy recovery
Run dry-run mode first to verify statistics

Example Output

Dry-Run Mode

$ ./scripts/database/delete-non-admin-users.sh --dry-run

Loading AWS Cognito configuration...
✓ Cognito User Pool: us-east-1_XXXXXX

Loading database configuration...
✓ Database configuration loaded

==================================
   Current Database Statistics
==================================

Total users: 6
Admin users: 2
Non-admin users: 4

Data that will be deleted for non-admin users:
  Progress records: 15
  Enrollments: 5
  Payments: 0
  Activity logs: 0
  User badges: 1
  User statistics: 4

==================================
   Starting Deletion Process
==================================

Step 1: Fetching non-admin users...
Found 4 non-admin users

Step 2: Deleting user-related data...

[DRY-RUN] Deleting progress records
SQL: DELETE FROM progress WHERE user_id IN (SELECT id FROM users WHERE role != 'ADMIN')
[DRY-RUN] Deleting enrollments
SQL: DELETE FROM enrollments WHERE user_id IN (SELECT id FROM users WHERE role != 'ADMIN')
...

Step 3: Deleting users from Cognito...

[DRY-RUN] Would delete Cognito user: user@example.com
[DRY-RUN] Would delete Cognito user: another@example.com
...

Step 4: Deleting users from database...
[DRY-RUN] Deleting non-admin users
SQL: DELETE FROM users WHERE role != 'ADMIN'

==================================
   Deletion Complete!
==================================

==================================
   Dry-Run Complete
==================================

This was a DRY-RUN. No changes were made.
To actually perform the deletion, run:
  ./scripts/database/delete-non-admin-users.sh --execute

Execute Mode

$ ./scripts/database/delete-non-admin-users.sh --execute

# Shows statistics...

==================================
   ⚠️  CRITICAL WARNING ⚠️
==================================

You are about to PERMANENTLY DELETE all non-admin users!
This action CANNOT be undone!

This will delete:
  - All non-admin users from the database
  - All non-admin users from Cognito
  - All user progress, enrollments, payments, and activity

Type 'DELETE ALL NON-ADMIN USERS' to confirm: DELETE ALL NON-ADMIN USERS

# Proceeds with deletion...

==================================
   Deletion Complete!
==================================

Remaining users (admins only): 2

Prerequisites

Required Tools

AWS CLI - Must be installed and configured
```
brew install awscli
aws configure
```
jq - JSON processor for parsing AWS responses
```
brew install jq
```

Required Configuration

Terraform - Infrastructure must be deployed to get database ARNs
Frontend .env.local - Must contain NEXT_PUBLIC_USER_POOL_ID

AWS Permissions

The AWS CLI user/role must have:

RDS Data API - Permission to execute SQL statements
Cognito - Permission to delete users (cognito-idp:AdminDeleteUser)
Secrets Manager - Permission to read database credentials
Terraform State - Access to read Terraform outputs

Technical Details

Database Connection

Uses AWS RDS Data API (no VPC required)
Connects via HTTPS to Aurora Serverless v2
Database ARNs obtained from Terraform outputs

Cognito Integration

Uses aws cognito-idp admin-delete-user
User Pool ID from frontend .env.local
Handles non-existent users gracefully

Error Handling

Exits on any error (set -e)
Validates required tools are installed
Validates configuration is available
Provides clear error messages

Testing

A comprehensive test suite is available at:

scripts/testing/test-delete-non-admin-users.sh

Running Tests

./scripts/testing/test-delete-non-admin-users.sh

Test Coverage

The test suite includes 32 tests covering:

Script existence and permissions
Help documentation
Dry-run mode functionality
Configuration loading
Safety features (admin preservation)
Error handling
Data integrity checks
Deletion order validation

Warnings

⚠️ THIS OPERATION IS IRREVERSIBLE

Once executed:

User data cannot be recovered
Cognito accounts cannot be restored
All user progress is permanently lost

Always:

Run in dry-run mode first
Verify the statistics match your expectations
Ensure you have backups if needed
Double-check you’re targeting the correct environment

Support

For issues or questions:

Check the GitHub Issues
Review the script’s --help output
Run the test suite to verify functionality