Database Initialization Guide

This guide explains how to use database initialization scripts to set up system base data.

Script Overview

The scripts/database/ directory contains the following scripts:

init-database.ts - Database Initialization Master Script

Executes all sub-initialization scripts in logical order:

1. seed-roles.ts - Initialize roles and permissions
2. seed-settings.ts - Initialize system settings
3. seed-plans.ts - Initialize subscription plans
4. seed-packages.ts - Initialize credit packages

set-super-admin.ts - Set Super Admin Script

Sets a specified user as super admin, supporting:

• ✅ Set by user ID
• ✅ Set by email
• ✅ Set by username
• ✅ Auto-enable disabled accounts
• ✅ Auto-create admin records

Initialization Methods

Method 1: Using TypeScript Scripts (Recommended)

Step 1: Run Database Migration

First ensure database table structure is created:

# Generate migration files (if schema changes)
pnpm drizzle-kit generate

# Push database schema to database (if changes)
pnpm drizzle-kit push

# Apply migration
pnpm drizzle-kit migrate

Step 2: Initialize Base Data

Development Environment:

pnpm tsx scripts/database/init-database.ts

Production Environment:

pnpm tsx --env-file=.env.production.local scripts/database/init-database.ts

Example Output:

🚀 Starting full database initialization...
==================================================

Executing script: seed-roles.ts...
Starting role and permission initialization...
Inserting permissions...
  ✓ Created permission: View Users (users:view)
  ✓ Created permission: Edit Users (users:update)
  ...

Inserting roles...
  ✓ Created role: Super Admin (super_admin) - 58 permissions
  ✓ Created role: Admin (admin) - 42 permissions
  ...

✅ Role and permission initialization complete!
✅ seed-roles.ts executed successfully

Executing script: seed-settings.ts...
Starting system settings initialization...
  ✓ Created setting: dateFormat
  ✓ Created setting: timeFormat
  ...
✅ System settings initialization complete!
✅ seed-settings.ts executed successfully

Executing script: seed-plans.ts...
Starting subscription plan initialization...
  ✓ Created plan: Free (free)
  ✓ Created plan: Basic (basic)
  ✓ Created plan: Pro (pro)
✅ Subscription plan initialization complete!
✅ seed-plans.ts executed successfully

Executing script: seed-packages.ts...
Starting credit package initialization...
  ✓ Created package: Small Pack (500 credits)
  ✓ Created package: Medium Pack (2000 credits)
  ✓ Created package: Large Pack (5000 credits)
✅ Credit package initialization complete!
✅ seed-packages.ts executed successfully

==================================================
🎉 All database initialization tasks completed!
Tip: You can now use scripts/database/set-super-admin.ts to set up admin accounts.

Running Individual Sub-scripts

If you only need to initialize partial data, you can run sub-scripts individually:

# Initialize roles and permissions only
pnpm tsx scripts/database/seed-roles.ts

# Initialize system settings only
pnpm tsx scripts/database/seed-settings.ts

# Initialize subscription plans only
pnpm tsx scripts/database/seed-plans.ts

# Initialize credit packages only
pnpm tsx scripts/database/seed-packages.ts

Method 2: Using SQL Files

If you need to execute SQL directly in the database, you can use pre-generated SQL files:

PostgreSQL:

psql -U your_user -d your_database -f scripts/database/init-data-postgres.sql

SQLite:

sqlite3 your_database.db < scripts/database/init-data-sqlite.sql

Initialized Data Details

Permission List (58 total)

User Management (8)

Subscription Management (4)

Subscription Plan Management (4)

Order Management (3)

Credits Management (3)

Credit Package Management (4)

Transaction Records (2)

Dashboard (1)

System Management (22)

Newsletter Management (4)

Contact Message Management (4)

Role List (4 total)

System Settings

System settings are divided into the following groups:

Subscription Plans (3 total)

Credit Packages (3 total)

Set Super Admin

Prerequisites

1. Database initialized (ran init-database.ts)
2. User registered in system

Usage

Method 1: Set by User ID

pnpm tsx scripts/database/set-super-admin.ts --userId=<USER_ID>

Method 2: Set by Email

pnpm tsx scripts/database/set-super-admin.ts --email=admin@example.com

Method 3: Set by Username

pnpm tsx scripts/database/set-super-admin.ts --username=admin

Production Environment

pnpm tsx --env-file=.env.production.local scripts/database/set-super-admin.ts --email=admin@example.com

Example Output

🔧 Starting super admin setup...
==================================================

🔍 Finding by email: admin@example.com

✓ Found user:
  ID: abc123def456
  Name: Admin User
  Email: admin@example.com
  Username: admin

🔍 Finding super admin role...
✓ Found role: Super Admin

🔍 Checking admin status...
✓ Created admin record

🔍 Checking role assignment...
✓ Assigned super admin role

==================================================
✅ Super admin setup complete!

User Info:
  ID: abc123def456
  Name: Admin User
  Email: admin@example.com
  Username: admin

Assigned Roles:
  - Super Admin (super_admin)

Tips:
  - This user can now access all admin dashboard features
  - Admin dashboard URL: /admin

FAQ

Q1: How to get User ID?

Method 1: Database Query

SELECT id, name, email, username FROM user WHERE email = 'user@example.com';

Method 2: Admin Dashboard

• Login to admin dashboard
• Go to user management page
• View user details

Method 3: Use Email or Username

• Use --email or --username parameter directly, no need to query ID

Q2: Can I run initialization script multiple times?

Yes. The script checks if data already exists:

• Existing permissions are skipped
• Existing roles have permissions updated
• Existing system settings are skipped
• Existing subscription plans and credit packages are skipped

Q3: How to modify subscription plans or credit packages?

Method 1: Admin Dashboard

• Login to admin dashboard
• Go to corresponding management page to modify

Method 2: Modify Script Directly

• Edit plans array in seed-plans.ts
• Edit packages array in seed-packages.ts
• Re-run the script

Q4: How to add custom permissions?

1. Edit permissions array in seed-roles.ts
2. Add new permission definition
3. Add permission name to corresponding role's permissions array
4. Re-run the script

Example:

// Add new permission
{ 
  module: 'reports', 
  action: 'view', 
  name: 'reports:view', 
  displayName: 'View Reports', 
  description: 'View system reports' 
}

// Add to role
{
  name: 'admin',
  permissions: [
    // ... other permissions
    'reports:view'
  ]
}

Q5: What if script execution fails?

Common Errors and Solutions:

1. Database Connection Failed

   • Check database configuration in .env file
   • Ensure database service is running

2. Table Does Not Exist

   • Run pnpm drizzle-kit migrate to create tables

3. User Not Found

   • Confirm user is registered
   • Check email/username spelling

4. Role Not Found

   • Run init-database.ts first to initialize roles

Q6: What's the difference between SQL files and TypeScript scripts?

Important Notes

1. Production Environment: Test in staging environment before running in production
2. Backup Data: Backup database before important operations
3. Permission Management: Super admin has all permissions, assign carefully
4. Payment Configuration: Payment gateway price IDs for subscription plans and credit packages need manual configuration
5. Multilingual: Add corresponding translations in public/locales if multilingual support is needed
6. Environment Variables: Use --env-file=.env.production.local parameter when executing in production environment