CloudPOS Documentation

# Database Setup

CloudPOS uses MySQL 8+ with Prisma ORM for database management.

# Prerequisites

  • ✅ MySQL 8.0+ installed and running
  • ✅ Database user with privileges
  • ✅ .env file configured with DATABASE_URL

# Step 1: Create Database

# Option 1: Using MySQL Command Line

mysql -u root -p

CREATE DATABASE cloudpos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EXIT;

# Option 2: Using MySQL Workbench

  1. Open MySQL Workbench
  2. Connect to your MySQL server
  3. Right-click → Create Schema
  4. Name: cloudpos
  5. Collation: utf8mb4_unicode_ci
  6. Click Apply

# Option 3: Using phpMyAdmin

  1. Open phpMyAdmin
  2. Click "New" in left sidebar
  3. Database name: cloudpos
  4. Collation: utf8mb4_unicode_ci
  5. Click Create

# Step 2: Configure Database User (Optional)

For production, create a dedicated database user:

CREATE USER 'cloudpos'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT ALL PRIVILEGES ON cloudpos.* TO 'cloudpos'@'localhost';
FLUSH PRIVILEGES;

Update .env:

DATABASE_URL="mysql://cloudpos:STRONG_PASSWORD@localhost:3306/cloudpos"

# Step 3: Generate Prisma Client

Prisma Client is the database access layer. Generate it after schema changes:

cd server
npm run prisma:generate

When to run:

  • After cloning the project
  • After pulling code updates
  • After schema changes

# Step 4: Run Migrations

Migrations create and update database tables:

cd server
npm run prisma:migrate

First migration:

  • You'll be prompted to name the migration
  • Press Enter to use default name
  • This creates all tables

Subsequent migrations:

  • Run when schema changes are made
  • Migrations are versioned and tracked

# Step 5: Seed Database (Optional)

Seed data creates sample data for testing:

cd server
npm run prisma:seed

What gets created:

  • 2 sample tenants (Acme Corporation, Tech Solutions)
  • 3 roles (Administrator, Manager, Cashier)
  • 3 users (admin@acme.com, manager@acme.com, cashier@acme.com)
  • Sample products, categories, customers, sales
  • Default permissions

Default credentials:

  • Email: admin@acme.com
  • Password: password123

⚠️ Change passwords after first login!

# Step 6: Create Admin User (SaaS Admin Panel)

Create the first admin user for the SaaS admin panel:

cd server
npx ts-node prisma/create-admin.ts

Default credentials:

  • Email: admin@cloudpos.com
  • Password: admin123

⚠️ Change password immediately!

# Database Management

# Prisma Studio (Database GUI)

Open a visual database browser:

cd server
npm run prisma:studio

Access at: http://localhost:5555

# View Database Schema

cd server
cat prisma/schema.prisma

# Reset Database (⚠️ Deletes All Data)

cd server
npx prisma migrate reset

This will:

  1. Drop all tables
  2. Recreate database
  3. Run all migrations
  4. Run seed (if configured)

# Common Database Operations

# Backup Database

# Create backup
mysqldump -u root -p cloudpos > backup_$(date +%Y%m%d).sql

# With custom user
mysqldump -u cloudpos -p cloudpos > backup_$(date +%Y%m%d).sql

# Restore Database

# Restore from backup
mysql -u root -p cloudpos < backup_20240101.sql

# With custom user
mysql -u cloudpos -p cloudpos < backup_20240101.sql

# Check Database Size

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'cloudpos'
GROUP BY table_schema;

# List All Tables

USE cloudpos;
SHOW TABLES;

# Database Schema Overview

# Core Tables

  • tenants (clients) - Multi-tenant isolation
  • users - User accounts
  • roles - Role definitions
  • permissions - Permission definitions
  • role_permissions - Role-permission mapping
  • user_roles - User-role assignment

# POS Tables

  • branches - Store locations
  • registers - Cash registers
  • products - Product catalog
  • categories - Product categories
  • customers - Customer records
  • suppliers - Supplier records
  • sales - Sales transactions
  • sale_items - Sale line items
  • purchases - Purchase orders
  • payments - Payment records

# SaaS Tables

  • plans - Subscription plans
  • subscriptions - Tenant subscriptions
  • subscription_payments - Payment records
  • payment_gateways - Payment gateway configs
  • admins - SaaS admin users
  • admin_roles - Admin role definitions

# System Tables

  • audit_logs - Activity logs
  • email_config - Email settings
  • email_templates - Email templates
  • languages - Language definitions
  • currencies - Currency definitions

# Troubleshooting

# Migration Fails

Error: Migration failed or Table already exists

Solutions:

  1. Check if tables exist:

    USE cloudpos;
SHOW TABLES;

  2. Reset and re-migrate (⚠️ deletes data):

    npx prisma migrate reset
npm run prisma:migrate

  3. Push schema without migrations:

    npm run prisma:db:push

# Connection Refused

Error: Can't reach database server

Solutions:

  1. Check MySQL is running:

    # Windows (Laragon)
# Check Laragon dashboard

# macOS
brew services list

# Linux
sudo systemctl status mysql

  2. Test connection:

    mysql -u root -p -h localhost

  3. Check DATABASE_URL in .env:

    • Format: mysql://user:password@host:port/database
    • Verify credentials

# Authentication Failed

Error: Access denied for user

Solutions:

  1. Check username and password in DATABASE_URL

  2. Verify user has privileges:

    SHOW GRANTS FOR 'cloudpos'@'localhost';

  3. Grant privileges:

    GRANT ALL PRIVILEGES ON cloudpos.* TO 'cloudpos'@'localhost';
FLUSH PRIVILEGES;

# Character Encoding Issues

Error: Special characters display incorrectly

Solutions:

  1. Verify database charset:

    SHOW CREATE DATABASE cloudpos;
-- Should show: CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

  2. Recreate database with correct charset:

    DROP DATABASE cloudpos;
CREATE DATABASE cloudpos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Prisma Client Not Generated

Error: PrismaClient is not generated

Solution:

cd server
npm run prisma:generate

# Production Database Tips

  1. ✅ Regular Backups

    • Daily automated backups
    • Store backups off-server

  2. ✅ Optimize Performance

    • Add indexes for frequently queried columns
    • Monitor slow queries

  3. ✅ Security

    • Use dedicated database user
    • Strong passwords
    • Limit user privileges

  4. ✅ Monitoring

    • Monitor database size
    • Check connection pool usage
    • Monitor query performance

Next: See Admin Panel to access and configure the system.

← Admin Panel →