Database

Database management scripts, utilities, and documentation for the Momentum platform.

Overview

The Momentum platform uses Amazon Aurora Serverless v2 (PostgreSQL 15+) for data storage. The database is accessed via the AWS RDS Data API, which provides HTTPS-based queries without requiring VPC access.

Database Scripts

Query & Management

db-query-remote.sh - Interactive SQL query tool for remote database
db-migrate.sh - Database migration script for schema updates
Delete Non-Admin Users - Comprehensive user cleanup script

Key Features

No VPC Required - All scripts use RDS Data API over HTTPS
Safe by Default - Dry-run modes and confirmation prompts
Interactive Mode - REPL-style query interfaces
Terraform Integration - Automatically fetches database ARNs

Database Schema

The database schema is managed through migration files in backend/migrations/:

Core Tables

users - User accounts (synced from Cognito)
categories - Course categories
courses - Course definitions
lessons - Lesson content
enrollments - User course enrollments
progress - User lesson progress

Gamification

badges - Achievement definitions
user_badges - User badge awards
user_statistics - User performance metrics

AI Generation

course_generation_jobs - AI course generation tracking
course_reference_documents - PDF references for generation

Analytics

user_activity_log - Activity tracking
daily_platform_stats - Platform metrics
course_performance_stats - Course analytics

Additional Tables

payments - Payment transactions
user_demographics - User profile data
intro_video_script - Course intro scripts
lesson_video_script - Lesson video scripts
lesson_video_status - Video generation status

Prerequisites

All database scripts require:

AWS CLI - Installed and configured
```
brew install awscli
aws configure
```
jq - JSON processor
```
brew install jq
```
Terraform - Infrastructure deployed to get database ARNs
```
cd infrastructure/terraform
terraform output
```

Configuration

Database Connection

Database connection details are automatically fetched from Terraform outputs:

db_cluster_arn - Aurora cluster ARN
db_secret_arn - Secrets Manager ARN for credentials

AWS Permissions Required

rds-data:ExecuteStatement - Execute SQL queries
secretsmanager:GetSecretValue - Read database credentials
cognito-idp:AdminDeleteUser - Delete Cognito users (for cleanup scripts)

Common Operations

Query the Database

./scripts/database/db-query-remote.sh

# Or run a specific query
./scripts/database/db-query-remote.sh "SELECT COUNT(*) FROM users"

Run Migrations

./scripts/database/db-migrate.sh

Clean Up Test Users

# Dry-run to see what would be deleted
./scripts/database/delete-non-admin-users.sh --dry-run

# Execute the deletion (requires confirmation)
./scripts/database/delete-non-admin-users.sh --execute

Safety & Best Practices

Development

Always test queries in dry-run mode first
Use SELECT queries to verify data before DELETE or UPDATE
Keep migrations idempotent and reversible
Test migrations locally before production

Data Management

Regular backups are maintained by Aurora
Point-in-time recovery available
Encryption at rest and in transit
Never log sensitive data (passwords, tokens, PII)

User Data

Respect user privacy and GDPR requirements
Use cleanup scripts for test environments only
Document all data retention policies
Implement proper data deletion procedures

Troubleshooting

Connection Issues

# Verify AWS credentials
aws sts get-caller-identity

# Check Terraform outputs
cd infrastructure/terraform
terraform output db_cluster_arn
terraform output db_secret_arn

Permission Issues

Ensure your AWS user/role has the required RDS Data API permissions in IAM.

Query Timeouts

For long-running queries, consider:

Breaking into smaller operations
Adding appropriate indexes
Analyzing query plans with EXPLAIN

Delete Non-Admin Users Script