Skip to main content

​
Overview

Dashboard Dilemas uses MySQL/MariaDB as its database engine. The application connects via PDO with UTF-8 encoding and includes automatic error logging for connection failures.

​
Database Connection

​
Environment Variables

Configure your database connection using environment variables:
.env
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=dilemas

​
Connection Configuration

The database connection is established in includes/db.php with the following PDO options:
includes/db.php
$host = getenv('DB_HOST') ?: 'localhost';
$db = getenv('DB_NAME') ?: 'dilemas';
$user = getenv('DB_USER') ?: 'root';
$pass = getenv('DB_PASSWORD') ?: 'root';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
The application uses utf8mb4 encoding to support emoji avatars and international characters.

​
Error Handling

Connection failures are automatically logged to dev/debug/error.log and display a user-friendly error page instead of exposing stack traces: 
$logDir = __DIR__ . '/../dev/debug';
$logFile = $logDir . '/error.log';
if (!is_dir($logDir))
    mkdir($logDir, 0755, true);

$logLine = '[' . date('Y-m-d H:i:s') . '] DB CONNECTION ERROR: '
    . $e->getMessage() . ' (Code: ' . $e->getCode() . ')' . PHP_EOL;
file_put_contents($logFile, $logLine, FILE_APPEND | LOCK_EX);
Ensure the dev/debug/ directory is writable by the web server for error logging to function properly.

​
Database Schema

The application uses four main tables with foreign key relationships.

​
Tables Overview

de_app_users

Stores user information, avatars, and activity timestamps

de_app_games

Contains dilemma games with categories and client information

de_app_sessions

Tracks user game sessions with scores and duration

de_app_areas

Defines application areas with color coding

​
Schema Structure

​
de_app_users

Stores user profiles and activity tracking: 
CREATE TABLE IF NOT EXISTS `de_app_users` (
  `id` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `avatar` varchar(10) DEFAULT '👤',
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastActive` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​
de_app_games

Defines available dilemma games: 
CREATE TABLE IF NOT EXISTS `de_app_games` (
  `id` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `client` varchar(100) DEFAULT NULL,
  `icon` varchar(50) DEFAULT '🎮',
  `clientLogo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​
de_app_sessions

Tracks user game sessions with foreign key constraints: 
CREATE TABLE IF NOT EXISTS `de_app_sessions` (
  `id` varchar(50) NOT NULL,
  `userId` varchar(50) NOT NULL,
  `gameId` varchar(50) NOT NULL,
  `score` int(11) DEFAULT 0,
  `duration` int(11) DEFAULT 0,
  `startedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `endedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `gameId` (`gameId`),
  CONSTRAINT `fk_session_user` FOREIGN KEY (`userId`) 
    REFERENCES `de_app_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_session_game` FOREIGN KEY (`gameId`) 
    REFERENCES `de_app_games` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Cascading deletes ensure that when a user or game is removed, all related sessions are automatically deleted.

​
de_app_areas

Defines organizational areas with color coding: 
CREATE TABLE IF NOT EXISTS `de_app_areas` (
  `id` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `color` varchar(20) DEFAULT '#000000',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

​
Database Setup

​
Initial Setup

  1. Create your MySQL database:
mysql -u root -p

CREATE DATABASE dilemas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. Import the schema:
mysql -u root -p dilemas < sql/database.sql
  1. Configure your .env file with the database credentials.

​
Sample Data

The sql/database.sql file includes sample data for testing: 
INSERT INTO `de_app_users` (`id`, `name`, `email`, `avatar`, `createdAt`, `lastActive`) VALUES
('u1', 'Carlos Mendez', '[email protected]', '👨', NOW(), NOW()),
('u2', 'Ana Lopez', '[email protected]', '👩', NOW(), NOW()),
('u3', 'Pedro Silva', '[email protected]', '👨', NOW(), NOW()),
('u4', 'Maria Garcia', '[email protected]', '👩', NOW(), NOW());

​
Maintenance

​
Backup Database

mysqldump -u root -p dilemas > backup_$(date +%Y%m%d).sql

​
Check Connection Status

Monitor database errors in the log file: 
tail -f dev/debug/error.log

​
Next Steps

Environment Variables

Configure all application settings

Email Configuration

Set up SMTP for notifications

Build docs developers (and LLMs) love

Get started for freeTalk to us