membership-be/MIGRATIONS.md

# Database Migrations Guide

This document explains how to set up the database for the LOAF membership platform on a fresh server.

---

## Quick Start (Fresh Database Setup)

For a **brand new deployment** on a fresh PostgreSQL database:

```bash
# 1. Create PostgreSQL database
psql -U postgres
CREATE DATABASE membership_db;
CREATE USER membership_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE membership_db TO membership_user;
\q

# 2. Run initial schema migration
psql -U postgres -d membership_db -f migrations/000_initial_schema.sql

# 3. Seed permissions and RBAC roles
python seed_permissions_rbac.py

# 4. Create superadmin user
python create_admin.py
```

**That's it!** The database is now fully configured and ready for use.

---

## Migration Files Overview

### Core Migration (Run This First)

**`000_initial_schema.sql`** ✅ **START HERE**
- Creates all 10 ENUM types (userstatus, userrole, rsvpstatus, etc.)
- Creates all 17 base tables:
  - Core: users, events, event_rsvps, event_galleries
  - Financial: subscription_plans, subscriptions, donations
  - Documents: newsletter_archives, financial_reports, bylaws_documents
  - RBAC: permissions, roles, role_permissions
  - System: storage_usage, user_invitations, import_jobs
- Creates 30+ performance indexes
- Initializes default storage_usage record
- **Status:** Required for fresh deployment
- **Run Order:** #1

---

### Incremental Migrations (Historical - Only for Existing Databases)

These migrations were created to update existing databases incrementally. **If you're starting fresh with 000_initial_schema.sql, you DO NOT need to run these** as their changes are already included in the initial schema.

| File | Purpose | Included in 000? | Run on Fresh DB? |
|------|---------|------------------|------------------|
| `001_add_member_since_field.sql` | Adds member_since field to users | ✅ Yes | ❌ No |
| `002_rename_approval_to_validation.sql` | Renames approval-related fields | ✅ Yes | ❌ No |
| `003_add_tos_acceptance.sql` | Adds TOS acceptance tracking | ✅ Yes | ❌ No |
| `004_add_reminder_tracking_fields.sql` | Adds reminder sent flags | ✅ Yes | ❌ No |
| `005_add_rbac_and_invitations.sql` | Adds RBAC permissions & invitations | ✅ Yes | ❌ No |
| `006_add_dynamic_roles.sql` | Adds dynamic roles table | ✅ Yes | ❌ No |
| `009_create_donations.sql` | Creates donations table | ✅ Yes | ❌ No |
| `010_add_rejection_fields.sql` | Adds rejection tracking to users | ✅ Yes | ❌ No |

**Note:** These files are kept for reference and for updating existing production databases that were created before 000_initial_schema.sql existed.

---

### Ad-Hoc Fix Migrations (Legacy - Do Not Run)

These were one-time fixes for specific issues during development:

- `add_calendar_uid.sql` - Added calendar UID field (now in 000)
- `complete_fix.sql` - Added various profile fields (now in 000)
- `fix_storage_usage.sql` - Fixed storage_usage initialization (now in 000)
- `sprint_1_2_3_migration.sql` - Combined early sprint migrations (obsolete)
- `verify_columns.sql` - Debugging script (not a migration)

**Status:** Do NOT run these on any database. They are archived for historical reference only.

---

## Python Migration Scripts (Data Migrations)

These scripts migrate **data**, not schema. Run these AFTER the SQL migrations if you have existing data to migrate:

| Script | Purpose | When to Run |
|--------|---------|-------------|
| `migrate_add_manual_payment.py` | Migrates manual payment data | Only if you have existing subscriptions with manual payments |
| `migrate_billing_enhancements.py` | Migrates billing cycle data | Only if you have existing subscription plans |
| `migrate_multistep_registration.py` | Migrates old registration format | Only if upgrading from Phase 0 |
| `migrate_password_reset.py` | Migrates password reset tokens | Only if you have password reset data |
| `migrate_role_permissions_to_dynamic_roles.py` | Migrates RBAC permissions | Run after seeding permissions (if upgrading) |
| `migrate_status.py` | Migrates user status enum values | Only if upgrading from old status values |
| `migrate_users_to_dynamic_roles.py` | Assigns users to dynamic roles | Run after seeding roles (if upgrading) |

**For Fresh Deployment:** You do NOT need to run any of these Python migration scripts. They are only for migrating data from older versions of the platform.

---

## Complete Deployment Workflow

### Scenario 1: Fresh Server (Brand New Database)

```bash
# Step 1: Create database
psql -U postgres << EOF
CREATE DATABASE membership_db;
CREATE USER membership_user WITH PASSWORD 'secure_password_here';
GRANT ALL PRIVILEGES ON DATABASE membership_db TO membership_user;
EOF

# Step 2: Run initial schema
psql postgresql://membership_user:secure_password_here@localhost/membership_db \
  -f migrations/000_initial_schema.sql

# Expected output:
# Step 1/8 completed: ENUM types created
# Step 2/8 completed: Core tables created
# ...
# ✅ Migration 000 completed successfully!

# Step 3: Seed permissions (59 permissions across 10 modules)
python seed_permissions_rbac.py

# Expected output:
# ✅ Seeded 59 permissions
# ✅ Created 5 system roles
# ✅ Assigned permissions to roles

# Step 4: Create superadmin user (interactive)
python create_admin.py

# Follow prompts to create your first superadmin account

# Step 5: Verify database
psql postgresql://membership_user:secure_password_here@localhost/membership_db -c "
SELECT
  (SELECT COUNT(*) FROM users) as users,
  (SELECT COUNT(*) FROM permissions) as permissions,
  (SELECT COUNT(*) FROM roles) as roles,
  (SELECT COUNT(*) FROM subscription_plans) as plans;
"

# Expected output (fresh database):
# users | permissions | roles | plans
# ------+-------------+-------+-------
#   1   |     59      |   5   |   0
```

### Scenario 2: Upgrading Existing Database

If you already have a database with data and need to upgrade:

```bash
# Check what migrations have been applied
psql -d membership_db -c "SELECT * FROM users LIMIT 1;" # Check if tables exist

# Run missing migrations in order
# Example: If you're on migration 006, run 009 and 010
psql -d membership_db -f migrations/009_create_donations.sql
psql -d membership_db -f migrations/010_add_rejection_fields.sql

# Run data migrations if needed
python migrate_users_to_dynamic_roles.py  # If upgrading RBAC
python migrate_billing_enhancements.py    # If upgrading subscriptions

# Update permissions
python seed_permissions_rbac.py
```

---

## Verification & Troubleshooting

### Verify Database Schema

```bash
# Check all tables exist (should show 17 tables)
psql -d membership_db -c "\dt"

# Expected tables:
# users, events, event_rsvps, event_galleries
# subscription_plans, subscriptions, donations
# newsletter_archives, financial_reports, bylaws_documents
# permissions, roles, role_permissions
# storage_usage, user_invitations, import_jobs

# Check ENUM types (should show 8 types)
psql -d membership_db -c "SELECT typname FROM pg_type WHERE typcategory = 'E';"

# Expected ENUMs:
# userstatus, userrole, rsvpstatus, subscriptionstatus
# donationtype, donationstatus, invitationstatus, importjobstatus

# Check indexes (should show 30+ indexes)
psql -d membership_db -c "SELECT indexname FROM pg_indexes WHERE schemaname = 'public';"
```

### Common Issues

**Issue 1: "relation already exists"**
- **Cause:** Migration already run
- **Solution:** Safe to ignore. 000_initial_schema.sql uses `IF NOT EXISTS` checks.

**Issue 2: "type already exists"**
- **Cause:** ENUM type already created
- **Solution:** Safe to ignore. The migration checks for existing types.

**Issue 3: "permission denied"**
- **Cause:** Database user lacks privileges
- **Solution:**
  ```bash
  psql -U postgres -d membership_db
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO membership_user;
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO membership_user;
  ```

**Issue 4: "could not connect to database"**
- **Cause:** DATABASE_URL incorrect in .env
- **Solution:** Verify connection string format:
  ```
  DATABASE_URL=postgresql://username:password@localhost:5432/database_name
  ```

---

## Migration History & Rationale

### Why 000_initial_schema.sql?

The `000_initial_schema.sql` file was created to consolidate all incremental migrations (001-010) into a single comprehensive schema for fresh deployments. This approach:

✅ **Simplifies fresh deployments** - One file instead of 10
✅ **Reduces errors** - No risk of running migrations out of order
✅ **Faster setup** - Single transaction vs multiple files
✅ **Easier to maintain** - One source of truth for base schema
✅ **Preserves history** - Old migrations kept for existing databases

### Schema Evolution Timeline

```
Phase 0 (Early Development)
├── Basic users table
├── Events and RSVPs
└── Email verification

Phase 1 (Current - MVP)
├── 000_initial_schema.sql (COMPREHENSIVE)
│   ├── All ENUM types
│   ├── 17 tables
│   ├── 30+ indexes
│   └── Default data
├── seed_permissions_rbac.py (59 permissions, 5 roles)
└── create_admin.py (Interactive superadmin creation)

Phase 2 (Future - Multi-tenant SaaS)
├── Add tenant_id to all tables
├── Tenant isolation middleware
├── Per-tenant customization
└── Tenant provisioning automation
```

---

## Database Backup & Restore

### Backup

```bash
# Full database backup
pg_dump -U postgres membership_db > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U postgres membership_db | gzip > backup_$(date +%Y%m%d).sql.gz

# Schema only (no data)
pg_dump -U postgres --schema-only membership_db > schema_backup.sql

# Data only (no schema)
pg_dump -U postgres --data-only membership_db > data_backup.sql
```

### Restore

```bash
# From uncompressed backup
psql -U postgres -d membership_db < backup_20250118.sql

# From compressed backup
gunzip -c backup_20250118.sql.gz | psql -U postgres -d membership_db
```

---

## Production Deployment Checklist

Before deploying to production:

- [ ] PostgreSQL 13+ installed
- [ ] Database created with secure credentials
- [ ] `000_initial_schema.sql` executed successfully
- [ ] `seed_permissions_rbac.py` completed (59 permissions created)
- [ ] Superadmin user created via `create_admin.py`
- [ ] DATABASE_URL configured in backend `.env`
- [ ] Backend server connects successfully (`uvicorn server:app`)
- [ ] Test API endpoints: GET /api/auth/me (should work after login)
- [ ] Database backup configured (daily cron job)
- [ ] SSL/TLS enabled for PostgreSQL connections
- [ ] Firewall rules restrict database access
- [ ] Connection pooling configured (if high traffic)

---

## Additional Resources

- **Backend README:** See `README.md` for complete backend setup guide
- **API Documentation:** http://localhost:8000/docs (Swagger UI)
- **PostgreSQL Docs:** https://www.postgresql.org/docs/13/
- **SQLAlchemy Docs:** https://docs.sqlalchemy.org/en/20/

---

**Last Updated:** December 18, 2024
**Version:** 1.0.0
**Maintainer:** LOAF Development Team