Database Schema & Prisma Architecture

Saints Gaming uses Prisma ORM backed by SQLite for local development (prisma/db/dev.db) with seamless migration paths to MariaDB / MySQL for production deployments.

1. Database Architecture & Datasource

The database configuration in prisma/schema.prisma is optimized for high query velocity and structured JSON serialization:

prisma
datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

generator client {
  provider   = "prisma-client-js"
  engineType = "library"
}

2. Core Entity Models & Schema Definitions

3. Relational Architecture & Foreign Keys

┌──────────────┐          1:N           ┌──────────────┐
│     User     ├───────────────────────►│GameCharacter │
└──────┬───────┘                        └──────────────┘
       │ 1:N
       ├───────────────────────────────►┌──────────────┐
       │                                │    Thread    │
       │                                └──────┬───────┘
       │                                       │ 1:N
       │                                       ▼
       │                                ┌──────────────┐
       └───────────────────────────────►│    Reply     │
                                        └──────────────┘

• Cascading Deletions: Deleting a User cascades to delete associated sessions, forum replies, and profile customization assets (onDelete: Cascade).
• JSON Column Strategy: High-complexity composite states (such as WorldMap.gridData or GameCharacter.stateData) are stored as validated JSON strings to maximize SQLite read/write throughput.

4. Schema Synchronization & Migration Workflow

Execute database maintenance commands via npm:

bash
# Push schema updates directly to local SQLite DB during development
npx prisma db push

# Create and apply migration files for production MariaDB
npx prisma migrate dev --name init_schema

# Regenerate Prisma Client TypeScript type definitions
npx prisma generate

# Open visual database browser
npx prisma studio