template-saas-database-v2/migrations

Database Migrations

This directory contains SQL migration scripts for schema changes.

Naming Convention

V{YYYYMMDD}_{NNN}__{description}.sql       # UP migration
V{YYYYMMDD}_{NNN}__{description}_DOWN.sql  # DOWN migration (rollback)

Example:

• V20260120_001__migrate_auth_sessions_structure.sql
• V20260120_001__migrate_auth_sessions_structure_DOWN.sql

Migration Order

Migrations should be applied in order by version number:

1. V20260120_001 - auth.sessions structure changes
2. V20260120_002 - billing.subscriptions structure changes
3. V20260120_003 - audit.audit_logs structure changes

Running Migrations

Apply UP migration

psql -h localhost -U postgres -d template_saas -f migrations/V20260120_001__migrate_auth_sessions_structure.sql

Apply DOWN migration (rollback)

psql -h localhost -U postgres -d template_saas -f migrations/V20260120_001__migrate_auth_sessions_structure_DOWN.sql

Migration Details

V20260120_001 - Auth Sessions

Changes:

• Renames session_token -> token_hash (VARCHAR(64) -> VARCHAR(255))
• Converts is_active (BOOLEAN) -> status (ENUM: active, expired, revoked)
• Adds device_name, browser, os, location columns
• Adds revoked_at, revoked_reason columns
• Updates cleanup function to use status

V20260120_002 - Billing Subscriptions

Changes:

• Adds Stripe integration columns (stripe_subscription_id, stripe_customer_id)
• Adds billing interval column (ENUM: month, year)
• Adds trial/cancellation columns (trial_start, cancel_at, cancel_reason)
• Adds pricing columns (price_amount, currency)
• Renames cancelled_at -> canceled_at (American spelling)
• Updates subscription_status enum: trial -> trialing

V20260120_003 - Audit Logs

Changes:

• Renames entity_type -> resource_type
• Renames entity_id -> resource_id
• Adds actor columns (user_email, actor_type)
• Adds resource_name, severity columns
• Adds context columns (request_id, session_id)
• Adds HTTP columns (endpoint, http_method, response_status, duration_ms)
• Splits changes JSONB -> old_values, new_values, changed_fields
• Updates audit.log_event() function

Safety Notes

1. Always backup before migrating
2. Run in transaction - All migrations use BEGIN/COMMIT
3. Test on staging first
4. Idempotent checks - Migrations check if changes already exist
5. DOWN migrations may lose data - VARCHAR truncation, column drops

Related Files

• DDL definitions: ../ddl/schemas/
• Enum definitions: ../ddl/02-enums.sql
• Functions: ../ddl/03-functions.sql