Database Configuration

Overview

Pro Stock Tool uses MySQL (or MariaDB) as its database backend. This guide covers database installation, configuration, table creation, and connection setup.

Prerequisites

MySQL 5.7+ or MariaDB 10.3+
PHP MySQLi extension installed
Database administration access (root or equivalent)
Completed Server Setup

Database Installation

Install MySQL/MariaDB

Ubuntu/Debian (MySQL):

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql

Ubuntu/Debian (MariaDB):

sudo apt install mariadb-server
sudo systemctl start mariadb
sudo systemctl enable mariadb

CentOS/RHEL:

sudo yum install mysql-server
sudo systemctl start mysqld
sudo systemctl enable mysqld

macOS:

brew install mysql
brew services start mysql

Secure MySQL Installation

Run the security script to set root password and remove test databases:

sudo mysql_secure_installation

Follow the prompts:

Set root password: Yes
Remove anonymous users: Yes
Disallow root login remotely: Yes
Remove test database: Yes
Reload privilege tables: Yes

Verify Installation

Connect to MySQL:

mysql -u root -p

Check version:

SELECT VERSION();

Database Creation

Create Database

Connect to MySQL and create the database:

CREATE DATABASE prostocktool CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The utf8mb4 character set supports all Unicode characters including emojis and special symbols.

Create Database User

Create a dedicated user for the application:

CREATE USER 'prostockuser'@'localhost' IDENTIFIED BY 'your_secure_password';

Grant necessary privileges:

GRANT SELECT, INSERT, UPDATE, DELETE ON prostocktool.* TO 'prostockuser'@'localhost';
FLUSH PRIVILEGES;

Replace your_secure_password with a strong password. Use a password generator for production environments.

For development (grants all privileges):

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

Verify Database Access

Test the new user credentials:

mysql -u prostockuser -p prostocktool

List databases:

SHOW DATABASES;

Database Schema

Create the required tables for Pro Stock Tool:

Create Users Table

USE prostocktool;

CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    identidad VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_identidad (identidad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This table stores user account information with:

id: Unique user identifier
email: User email (unique)
nombre: User full name
identidad: National ID number (6-20 digits)
password: Bcrypt hashed password
creado_en: Account creation timestamp
actualizado_en: Last update timestamp

Create Bodegas Table

CREATE TABLE bodegas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    descripcion VARCHAR(300),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This table manages warehouse/storage locations with:

id: Unique warehouse identifier
nombre: Warehouse name (unique, max 100 chars)
descripcion: Optional description (max 300 chars)
fecha_creacion: Creation timestamp
fecha_actualizacion: Last update timestamp

Create Categorias Table

CREATE TABLE categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    descripcion TEXT,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create Subcategorias Table

CREATE TABLE subcategorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE CASCADE,
    INDEX idx_categoria (categoria_id),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create Proveedores Table

CREATE TABLE proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    contacto VARCHAR(100),
    telefono VARCHAR(20),
    email VARCHAR(100),
    direccion VARCHAR(255),
    ciudad VARCHAR(100),
    pais VARCHAR(100),
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create Parametros Table

CREATE TABLE parametros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo VARCHAR(50) NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tipo (tipo),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This table stores system parameters like movement concepts and inventory types.

Create Productos Table (Optional)

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    categoria_id INT,
    subcategoria_id INT,
    bodega_id INT,
    proveedor_id INT,
    precio_compra DECIMAL(10,2),
    precio_venta DECIMAL(10,2),
    stock_actual INT DEFAULT 0,
    stock_minimo INT DEFAULT 0,
    stock_maximo INT DEFAULT 0,
    fecha_vencimiento DATE,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
    FOREIGN KEY (subcategoria_id) REFERENCES subcategorias(id) ON DELETE SET NULL,
    FOREIGN KEY (bodega_id) REFERENCES bodegas(id) ON DELETE RESTRICT,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE SET NULL,
    INDEX idx_codigo (codigo),
    INDEX idx_nombre (nombre),
    INDEX idx_categoria (categoria_id),
    INDEX idx_bodega (bodega_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Verify Tables

Check that all tables were created:

SHOW TABLES;

View table structure:

DESCRIBE usuarios;
DESCRIBE bodegas;
DESCRIBE categorias;

Connection Configuration

Locate Connection File

The database connection is configured in database/conexion.php:

<?php

$host = "localhost";
$user = "root";
$pass = "";
$db = "prostocktool";

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_errno) {
    http_response_code(500);
    echo json_encode(["error" => "Error de conexión a la base de datos"]);
    exit;
}
?>

Update Credentials

Update the connection parameters with your database credentials:

<?php

$host = "localhost";              // Database host
$user = "prostockuser";           // Database user
$pass = "your_secure_password";   // Database password
$db = "prostocktool";             // Database name

$conn = new mysqli($host, $user, $pass, $db);
$conn->set_charset("utf8mb4");    // Ensure UTF-8 encoding

if ($conn->connect_errno) {
    http_response_code(500);
    echo json_encode(["error" => "Error de conexión a la base de datos"]);
    exit;
}
?>

Never commit database credentials to version control. Use environment variables or a separate config file that is gitignored.

Test Connection

Create a test file test-db.php in your web root:

<?php
require 'database/conexion.php';

$result = $conn->query("SELECT 'Connection successful!' as message");
if ($result) {
    $row = $result->fetch_assoc();
    echo json_encode(['success' => true, 'message' => $row['message']]);
} else {
    echo json_encode(['success' => false, 'error' => $conn->error]);
}
$conn->close();
?>

Visit http://prostocktool.local/test-db.php and you should see:

{"success":true,"message":"Connection successful!"}

Delete test-db.php after verification.

Environment-Based Configuration

For better security, use environment-based configuration:

Create .env File

Create a .env file in your project root:

DB_HOST=localhost
DB_USER=prostockuser
DB_PASS=your_secure_password
DB_NAME=prostocktool

Add .env to .gitignore:

echo ".env" >> .gitignore

Update conexion.php

<?php

// Load environment variables
function loadEnv($path) {
    if (!file_exists($path)) return;
    $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '=') !== false && strpos($line, '#') !== 0) {
            list($key, $value) = explode('=', $line, 2);
            $_ENV[trim($key)] = trim($value);
        }
    }
}

loadEnv(__DIR__ . '/../.env');

$host = $_ENV['DB_HOST'] ?? 'localhost';
$user = $_ENV['DB_USER'] ?? 'root';
$pass = $_ENV['DB_PASS'] ?? '';
$db = $_ENV['DB_NAME'] ?? 'prostocktool';

$conn = new mysqli($host, $user, $pass, $db);
$conn->set_charset("utf8mb4");

if ($conn->connect_errno) {
    http_response_code(500);
    echo json_encode(["error" => "Error de conexión a la base de datos"]);
    exit;
}
?>

Database Optimization

Indexing Strategy

Pro Stock Tool tables include indexes on frequently queried columns:

usuarios: email, identidad
bodegas: nombre
categorias: nombre, activo
productos: codigo, nombre, categoria_id, bodega_id

Query Optimization Tips

Use prepared statements for user input:

$stmt = $conn->prepare("SELECT * FROM usuarios WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();

Limit result sets:
```
SELECT * FROM productos LIMIT 100;
```

Use specific columns instead of SELECT *:

SELECT id, nombre, email FROM usuarios;

Database Backup

Regular backups are essential:

# Full database backup
mysqldump -u root -p prostocktool > prostocktool_backup_$(date +%Y%m%d).sql

# Backup specific tables
mysqldump -u root -p prostocktool usuarios bodegas > backup.sql

# Restore from backup
mysql -u root -p prostocktool < prostocktool_backup_20260303.sql

MySQL Configuration

Optimize MySQL settings in /etc/mysql/my.cnf or /etc/my.cnf:

[mysqld]
# Connection settings
max_connections = 100
wait_timeout = 600

# Buffer pool size (set to 70-80% of RAM for dedicated DB server)
innodb_buffer_pool_size = 1G

# Query cache (MySQL 5.7 only)
query_cache_type = 1
query_cache_size = 64M

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Restart MySQL after changes:

sudo systemctl restart mysql

Troubleshooting

Connection Refused

Check if MySQL is running:

sudo systemctl status mysql

Start if stopped:

sudo systemctl start mysql

Access Denied

Verify user privileges:

SHOW GRANTS FOR 'prostockuser'@'localhost';

Reset user password:

ALTER USER 'prostockuser'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

Character Encoding Issues

Verify database charset:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Convert existing tables:

ALTER TABLE usuarios CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Foreign Key Constraints

If you get foreign key errors when deleting:

-- Temporarily disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;
-- Perform operations
SET FOREIGN_KEY_CHECKS = 1;

Security Best Practices

Follow these security guidelines for production databases:

Use strong passwords: 16+ characters with mixed case, numbers, and symbols
Limit user privileges: Grant only necessary permissions
Disable remote root: Never allow root login from remote hosts
Use SSL/TLS: Encrypt database connections
Regular backups: Automate daily backups
Update regularly: Keep MySQL updated with security patches
Monitor logs: Check error and slow query logs regularly
Use prepared statements: Prevent SQL injection attacks

Configuration

Development

Overview

Prerequisites

Database Installation

Database Creation

Database Schema

Connection Configuration

Environment-Based Configuration

Database Optimization

Indexing Strategy

Query Optimization Tips

Database Backup

MySQL Configuration

Troubleshooting

Connection Refused

Access Denied

Character Encoding Issues

Foreign Key Constraints

Security Best Practices

Next Steps

Frontend Setup

Server Setup

Build docs developers (and LLMs) love

Configuration

Development

​Overview

​Prerequisites

​Database Installation

​Database Creation

​Database Schema

​Connection Configuration

​Environment-Based Configuration

​Database Optimization

​Indexing Strategy

​Query Optimization Tips

​Database Backup

​MySQL Configuration

​Troubleshooting

​Connection Refused

​Access Denied

​Character Encoding Issues

​Foreign Key Constraints

​Security Best Practices

​Next Steps

Frontend Setup

Server Setup

Build docs developers (and LLMs) love

Overview

Prerequisites

Database Installation

Database Creation

Database Schema

Connection Configuration

Environment-Based Configuration

Database Optimization

Indexing Strategy

Query Optimization Tips

Database Backup

MySQL Configuration

Troubleshooting

Connection Refused

Access Denied

Character Encoding Issues

Foreign Key Constraints

Security Best Practices

Next Steps