GameForge Database Setup Guide

This guide covers the complete setup and management of the PostgreSQL database for the GameForge ML platform.

1. Prerequisites
2. Quick Start
3. Manual Installation
4. Database Schema
5. Configuration
6. Migrations
7. Backup & Restore
8. Monitoring
9. Troubleshooting

• PostgreSQL 16+: Latest stable version recommended
• PowerShell 5.1+: For Windows automation scripts
• Windows 10/11: Development environment
• 8GB RAM: Minimum for development
• 10GB free space: For database and backups

Run the automated setup script to install PostgreSQL, create the database, and apply the schema:

# Navigate to the database scripts directory
cd database/scripts

# Run complete setup (install + configure + test)
.\setup-database.ps1 -All

This will:

• Install PostgreSQL 16 via winget/chocolatey
• Create gameforge_dev database
• Create gameforge_user with appropriate permissions
• Apply the complete schema
• Create sample data for development
• Generate configuration files

If you prefer manual setup or the automated script fails:

1. Download PostgreSQL 16 from postgresql.org
2. Run installer with these settings:
   • Port: 5432 (default)
   • Superuser: postgres
   • Remember the superuser password
3. Add PostgreSQL bin to your PATH

# Connect to PostgreSQL as superuser
psql -U postgres -h localhost

# In psql prompt:
CREATE DATABASE gameforge_dev;
CREATE USER gameforge_user WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE gameforge_dev TO gameforge_user;
\q

# Apply the schema
psql -U gameforge_user -h localhost -d gameforge_dev -f database/schema.sql

# Apply sample data (optional)
psql -U gameforge_user -h localhost -d gameforge_dev -f database/sample-data.sql

The GameForge database includes the following core tables:

• User account management
• Role-based access control (basic_user, premium_user, admin, super_admin)
• Authentication and security features
• API quota tracking

• Game development projects
• Collaboration and team management
• Project metadata and settings

• File storage tracking (models, datasets, textures, etc.)
• Version control and metadata
• Access control and download tracking

• Track AI service usage
• Request status and cost tracking
• Performance monitoring

• Model registry and versioning
• Training metadata and metrics
• Deployment tracking

• Dataset versioning and lineage
• Quality metrics and validation
• Data drift detection

• Security and compliance tracking
• User action logging
• System audit trail

• UUID Primary Keys: For distributed systems
• Full-text Search: On projects and assets
• Automated Timestamps: Created/updated tracking
• Enum Types: For consistent status values
• JSON Columns: For flexible metadata storage
• Performance Indexes: Optimized for common queries

Copy the template and customize:

cp database/.env.template database/.env.database

Key configuration options:

# Database Connection
DATABASE_URL=postgresql://gameforge_user:securepassword@localhost:5432/gameforge_dev
DB_HOST=localhost
DB_PORT=5432
DB_NAME=gameforge_dev
DB_USER=gameforge_user
DB_PASSWORD=securepassword

# Connection Pool Settings
DB_POOL_SIZE=20
DB_POOL_TIMEOUT=30

# Security
DB_SSL_MODE=prefer
DB_AUDIT_ENABLED=true

Test your database connection:

# Test connection
psql -U gameforge_user -h localhost -d gameforge_dev -c "SELECT version();"

# Test with script
.\database\scripts\setup-database.ps1 -Test

Use the migration script to manage schema changes:

cd database/scripts

# Check migration status
.\migrate.ps1 -Action status

# Apply pending migrations
.\migrate.ps1 -Action migrate

# Create new migration
.\migrate.ps1 -Action create -MigrationName "add_user_preferences"

# Dry run (preview changes)
.\migrate.ps1 -Action migrate -DryRun

1. Create new migration file:

   .\migrate.ps1 -Action create -MigrationName "add_feature_x"

2. Edit the generated file in database/migrations/

3. Apply the migration:

   .\migrate.ps1 -Action migrate

• Atomic Operations: Wrap in BEGIN/COMMIT
• Rollback Plan: Consider reverse migrations
• Test First: Use -DryRun flag
• Backup: Create backup before major changes
• Index Creation: Use CONCURRENTLY for large tables

cd database/scripts

# Create backup
.\backup.ps1 -Action backup

# Create backup with custom path
.\backup.ps1 -Action backup -BackupPath "C:\backups\gameforge_$(Get-Date -Format 'yyyyMMdd').sql"

# Create backup and clean old files
.\backup.ps1 -Action backup -CleanOld -RetentionDays 30

# Restore from backup (WARNING: This overwrites existing data)
.\backup.ps1 -Action restore -RestoreFile "path\to\backup.sql"

# Manual backup
pg_dump -U gameforge_user -h localhost -d gameforge_dev -f backup.sql

# Manual restore
psql -U gameforge_user -h localhost -d gameforge_dev -f backup.sql

Monitor database performance and health:

# Database health check
.\backup.ps1 -Action monitor

# Maintenance tasks
.\backup.ps1 -Action maintain

• Database Size: Growth over time
• Active Connections: Connection pool usage
• Query Performance: Slow query identification
• Table Statistics: Insert/update/delete rates
• Index Usage: Index efficiency

-- Check database size
SELECT pg_size_pretty(pg_database_size('gameforge_dev'));

-- Check table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_stat_user_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check slow queries
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

Error: could not connect to server: Connection refused

Solutions:

1. Check if PostgreSQL service is running
2. Verify port 5432 is not blocked
3. Check connection parameters

Error: password authentication failed for user "gameforge_user"

Solutions:

1. Verify username and password
2. Check pg_hba.conf configuration
3. Ensure user exists with correct permissions

Error: database "gameforge_dev" does not exist

Solutions:

1. Create database: CREATE DATABASE gameforge_dev;
2. Run setup script: .\setup-database.ps1 -CreateDatabase

Error: permission denied for table users

Solutions:

1. Grant permissions: GRANT ALL PRIVILEGES ON DATABASE gameforge_dev TO gameforge_user;
2. Check role membership
3. Verify table ownership

# Check PostgreSQL service status
Get-Service postgresql*

# Test basic connectivity
psql -U postgres -h localhost -c "SELECT version();"

# Check database users
psql -U postgres -h localhost -c "\du"

# Check databases
psql -U postgres -h localhost -c "\l"

# Check table permissions
psql -U gameforge_user -h localhost -d gameforge_dev -c "\dp"

PostgreSQL logs are typically located at:

• Windows: C:\Program Files\PostgreSQL\16\data\log\
• Check for error messages and connection issues

1. Start Development Session:

   # Quick health check
   .\backup.ps1 -Action monitor

2. Schema Changes:

   # Create migration for schema changes
   .\migrate.ps1 -Action create -MigrationName "your_change"
   # Edit migration file
   # Apply migration
   .\migrate.ps1 -Action migrate

3. Data Refresh:

   # Reset to sample data
   psql -U gameforge_user -h localhost -d gameforge_dev -f database/sample-data.sql

1. Create Test Database:

   CREATE DATABASE gameforge_test;
   GRANT ALL PRIVILEGES ON DATABASE gameforge_test TO gameforge_user;

2. Apply Schema to Test DB:

   psql -U gameforge_user -h localhost -d gameforge_test -f database/schema.sql

3. Run Tests:

   # Set test database in environment
   $env:DB_NAME = "gameforge_test"
   # Run your tests

• Change default passwords
• Use SSL connections (DB_SSL_MODE=require)
• Restrict network access
• Enable audit logging
• Regular security updates
• Backup encryption
• Monitor access logs

-- Enable row-level security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create security policies
CREATE POLICY user_policy ON users 
FOR ALL TO gameforge_user 
USING (id = current_setting('app.current_user_id')::UUID);

Python (SQLAlchemy):

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql://gameforge_user:securepassword@localhost:5432/gameforge_dev"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Node.js (pg):

const { Pool } = require('pg');

const pool = new Pool({
  user: 'gameforge_user',
  host: 'localhost',
  database: 'gameforge_dev',
  password: 'securepassword',
  port: 5432,
});

• PostgreSQL Documentation
• GameForge API Documentation
• MLflow Integration Guide
• Security Policies

For support or questions, please refer to the main project documentation or create an issue in the project repository.