Documentation Index
Fetch the complete documentation index at: https://mintlify.com/JFKoryy/autopart-pro/llms.txt
Use this file to discover all available pages before exploring further.
AutoPart Pro stores all application data in a single MySQL 8 database named autopart_pro. The schema is composed of four production tables — users, products, sales, and sale_items — plus a test_connection table used to verify database connectivity during initial setup. Foreign keys between sales, sale_items, and users/products enforce referential integrity at the database level, and cascade deletion ensures that removing a sale automatically removes its associated line items.
Entity Relationship Summary
users ──────────────< sales >──────────── sale_items >──────────── products
(one user has (one sale has (each line item
many sales) many items) references one product)
| Relationship | Type | On Delete |
|---|
sales.user_id → users.id | Many-to-one | Restrict (default) |
sale_items.sale_id → sales.id | Many-to-one | CASCADE |
sale_items.product_id → products.id | Many-to-one | Restrict (default) |
ON DELETE CASCADE on sale_items.sale_id means that deleting a row from sales automatically deletes all associated sale_items rows. Deleting a user or product that is still referenced by a sale will fail with a foreign key constraint error.
Table: users
Stores all accounts regardless of role. The role column controls access across both the API middleware and the frontend’s permission map.
-- Initial CREATE TABLE (from database.sql)
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role ENUM('admin', 'employee') DEFAULT 'employee',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Migration: expand role enum and change default to 'client'
ALTER TABLE users
MODIFY COLUMN role ENUM('admin', 'employee', 'client') NOT NULL DEFAULT 'client';
role as NOT NULL with a default of 'client'.
| Column | Type | Notes |
|---|
id | INT AUTO_INCREMENT | Primary key |
name | VARCHAR(100) | Display name |
email | VARCHAR(100) UNIQUE | Used as the login identifier |
password | VARCHAR(255) | bcrypt hash — never stored in plain text |
role | ENUM | 'admin', 'employee', or 'client'; defaults to 'client' on self-registration |
created_at | TIMESTAMP | Set automatically on insert |
The password column is sized at VARCHAR(255) to accommodate the full bcrypt output string, which is always 60 characters but the extra headroom future-proofs the column if the hashing algorithm changes.
Table: products
Holds the complete inventory catalogue. The schema was extended over time via ALTER TABLE migrations, which is why min_stock, image_url, category, and description appear as separate ADD COLUMN statements in database.sql.
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
brand VARCHAR(100) NOT NULL,
compatible_cars TEXT,
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
ALTER TABLE products ADD COLUMN min_stock INT NOT NULL DEFAULT 5;
ALTER TABLE products ADD COLUMN image_url VARCHAR(255) DEFAULT NULL;
ALTER TABLE products ADD COLUMN category VARCHAR(100) DEFAULT NULL,
ADD COLUMN description TEXT DEFAULT NULL;
| Column | Type | Notes |
|---|
id | INT AUTO_INCREMENT | Primary key |
sku | VARCHAR(50) UNIQUE | Stock-keeping unit; always stored in uppercase (see below) |
name | VARCHAR(255) | Human-readable product name |
brand | VARCHAR(100) | Manufacturer / brand name |
compatible_cars | TEXT | Free-text list of compatible vehicle makes and models |
price | DECIMAL(10,2) | Unit sale price; defaults to 0.00 |
stock | INT | Current quantity on hand; defaults to 0 |
min_stock | INT | Low-stock threshold; defaults to 5. A low-stock event fires when stock drops to or below this value |
image_url | VARCHAR(255) | Optional URL for the product image |
category | VARCHAR(100) | Optional category label |
description | TEXT | Optional long-form description |
created_at | TIMESTAMP | Set on insert |
updated_at | TIMESTAMP | Automatically updated on every row modification |
SKU Auto-Uppercasing
The productMiddleware.js validation middleware normalises every incoming SKU to uppercase before it reaches the controller, ensuring consistent storage and lookup regardless of what case the client sends:
// backend/src/middleware/productMiddleware.js
if (sku !== undefined) {
req.body.sku = sku.toUpperCase().trim();
}
"br-alt-001", "BR-ALT-001", and " br-alt-001 " all become "BR-ALT-001" in the database.
Table: sales
One row per completed transaction. The user_id foreign key ties every sale to the account that placed it.
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
status ENUM('pending', 'completed', 'cancelled') DEFAULT 'completed',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
| Column | Type | Notes |
|---|
id | INT AUTO_INCREMENT | Primary key |
user_id | INT NOT NULL | FK → users.id — the buyer |
total | DECIMAL(10,2) | Pre-calculated order total |
status | ENUM | 'pending', 'completed' (default), or 'cancelled' |
created_at | TIMESTAMP | Order timestamp |
Table: sale_items
Each row is one line item belonging to a sale — a specific product, the quantity purchased, and the unit price locked in at the moment of purchase.
CREATE TABLE sale_items (
id INT AUTO_INCREMENT PRIMARY KEY,
sale_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
| Column | Type | Notes |
|---|
id | INT AUTO_INCREMENT | Primary key |
sale_id | INT NOT NULL | FK → sales.id — cascades on delete |
product_id | INT NOT NULL | FK → products.id |
quantity | INT | Number of units in this line item |
unit_price | DECIMAL(10,2) | Price captured at sale time — independent of future product price changes |
unit_price is stored per line item rather than being derived from products.price at query time. This preserves historical accuracy — if a product’s price changes after a sale is recorded, past sales remain correct.
Full Schema Reference
The complete database.sql file, including the test-connection table and all migration ALTER statements, is reproduced below for reference:
CREATE DATABASE IF NOT EXISTS autopart_pro;
USE autopart_pro;
-- Connectivity smoke-test table
CREATE TABLE IF NOT EXISTS test_connection (
id INT AUTO_INCREMENT PRIMARY KEY,
status_message VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Core tables
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role ENUM('admin', 'employee') DEFAULT 'employee',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
brand VARCHAR(100) NOT NULL,
compatible_cars TEXT,
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Migrations (applied after initial create)
ALTER TABLE products ADD COLUMN min_stock INT NOT NULL DEFAULT 5;
ALTER TABLE users
MODIFY COLUMN role ENUM('admin', 'employee', 'client') NOT NULL DEFAULT 'client';
ALTER TABLE products ADD COLUMN image_url VARCHAR(255) DEFAULT NULL;
ALTER TABLE products ADD COLUMN category VARCHAR(100) DEFAULT NULL,
ADD COLUMN description TEXT DEFAULT NULL;
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
status ENUM('pending', 'completed', 'cancelled') DEFAULT 'completed',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE sale_items (
id INT AUTO_INCREMENT PRIMARY KEY,
sale_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO test_connection (status_message)
VALUES ('¡Conexión de la Fase 1 exitosa desde la DB!');
