9.2 KiB
9.2 KiB
Database Schema — Swish Auto Care GMS
MySQL 8.x — Full Schema Reference
Setup Instructions
CREATE DATABASE IF NOT EXISTS swish_gms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swish_gms;
Table 1 — admin_users
Stores the single admin login for the demo.
CREATE TABLE admin_users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(150) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Seed: password is 'swish@2024' (bcrypt hashed at runtime)
INSERT INTO admin_users (username, full_name, password_hash)
VALUES ('admin', 'Swish Admin', '$2b$10$PLACEHOLDER_BCRYPT_HASH');
-- NOTE: Generate real hash in seed script using bcrypt.hash('swish@2024', 10)
Table 2 — staff
All staff members of Swish Auto Care.
CREATE TABLE staff (
id INT AUTO_INCREMENT PRIMARY KEY,
staff_code VARCHAR(20) UNIQUE NOT NULL, -- SAC-STF-001
full_name VARCHAR(150) NOT NULL,
role VARCHAR(100), -- Detailer, Washer, Supervisor
mobile_no VARCHAR(15),
date_of_joining DATE,
monthly_salary DECIMAL(10,2) DEFAULT 0.00,
is_active TINYINT(1) DEFAULT 1, -- 1=Active, 0=Inactive
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Auto-code generation logic (in backend):
// Get last staff code, increment
SELECT staff_code FROM staff ORDER BY id DESC LIMIT 1;
// Parse number, increment, format as SAC-STF-00N
Table 3 — job_cards
Core operational table. Every vehicle service creates a row here.
CREATE TABLE job_cards (
id INT AUTO_INCREMENT PRIMARY KEY,
job_no VARCHAR(30) UNIQUE NOT NULL, -- SAC-YYYYMMDD-001
job_type ENUM('full','wash') NOT NULL, -- full=Full Service, wash=Quick Wash
reg_no VARCHAR(20) NOT NULL,
car_name VARCHAR(100) NOT NULL,
owner_name VARCHAR(150) NOT NULL,
mobile_no VARCHAR(15),
service_type VARCHAR(100),
assigned_staff_id INT,
estimated_amount DECIMAL(10,2),
final_amount DECIMAL(10,2),
payment_mode ENUM('cash','upi','card'),
status ENUM('active','done') DEFAULT 'active',
notes TEXT,
job_date DATETIME DEFAULT CURRENT_TIMESTAMP,
closed_at DATETIME NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (assigned_staff_id) REFERENCES staff(id) ON DELETE SET NULL
);
CREATE INDEX idx_job_cards_date ON job_cards(job_date);
CREATE INDEX idx_job_cards_status ON job_cards(status);
CREATE INDEX idx_job_cards_reg ON job_cards(reg_no);
Job number generation logic (backend):
// Get today's date as YYYYMMDD
// Count jobs created today, add 1
// Format: SAC-20260324-001
const today = new Date().toISOString().slice(0,10).replace(/-/g,'');
const count = await countTodayJobs(); // SELECT COUNT(*) WHERE DATE(job_date) = CURDATE()
const seq = String(count + 1).padStart(3, '0');
const job_no = `SAC-${today}-${seq}`;
Table 4 — attendance
Daily attendance records for each staff member.
CREATE TABLE attendance (
id INT AUTO_INCREMENT PRIMARY KEY,
staff_id INT NOT NULL,
attendance_date DATE NOT NULL,
status ENUM('present','absent','half') NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_staff_date (staff_id, attendance_date),
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
);
CREATE INDEX idx_attendance_date ON attendance(attendance_date);
Note: The UNIQUE KEY on (staff_id, attendance_date) prevents duplicate entries. Use INSERT ... ON DUPLICATE KEY UPDATE for upsert behavior.
Table 5 — staff_payments
All salary and payment records for staff.
CREATE TABLE staff_payments (
id INT AUTO_INCREMENT PRIMARY KEY,
staff_id INT NOT NULL,
payment_date DATE NOT NULL,
payment_month VARCHAR(10) NOT NULL, -- e.g. 'March 2026'
days_present INT DEFAULT 0,
calculated_salary DECIMAL(10,2) DEFAULT 0.00,
paid_amount DECIMAL(10,2) NOT NULL, -- Admin can override
payment_type ENUM('salary','advance','bonus','deduction') NOT NULL,
payment_mode ENUM('cash','bank','upi') NOT NULL,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE RESTRICT
);
CREATE INDEX idx_payments_staff ON staff_payments(staff_id);
CREATE INDEX idx_payments_date ON staff_payments(payment_date);
Table 6 — parts
Parts and product inventory master.
CREATE TABLE parts (
id INT AUTO_INCREMENT PRIMARY KEY,
part_code VARCHAR(20) UNIQUE NOT NULL, -- SAC-PRT-001
name VARCHAR(200) NOT NULL,
category VARCHAR(100), -- Chemicals, Films, Tools, Consumables
unit VARCHAR(30), -- ml, sheet, piece, bottle
purchase_price DECIMAL(10,2) DEFAULT 0.00,
selling_price DECIMAL(10,2) DEFAULT 0.00,
stock_qty INT DEFAULT 0,
low_stock_alert INT DEFAULT 5,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Table 7 — parts_sales
Each parts sale transaction. Stock auto-decrements on insert.
CREATE TABLE parts_sales (
id INT AUTO_INCREMENT PRIMARY KEY,
part_id INT NOT NULL,
job_card_id INT NULL, -- Optional link to job card
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL, -- Selling price at time of sale (snapshot)
total_amount DECIMAL(10,2) NOT NULL, -- quantity × unit_price
customer_name VARCHAR(150),
payment_mode ENUM('cash','upi','card') NOT NULL,
sale_date DATETIME DEFAULT CURRENT_TIMESTAMP,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (part_id) REFERENCES parts(id) ON DELETE RESTRICT,
FOREIGN KEY (job_card_id) REFERENCES job_cards(id) ON DELETE SET NULL
);
CREATE INDEX idx_parts_sales_date ON parts_sales(sale_date);
Stock deduction trigger (handle in backend, not DB trigger):
// On parts_sales INSERT:
await db.query(
'UPDATE parts SET stock_qty = stock_qty - ? WHERE id = ? AND stock_qty >= ?',
[quantity, part_id, quantity]
);
// Check affected rows — if 0, insufficient stock, rollback
Table 8 — cash_ledger
Manual cash in/out entries by admin (miscellaneous transactions).
CREATE TABLE cash_ledger (
id INT AUTO_INCREMENT PRIMARY KEY,
entry_type ENUM('in','out') NOT NULL,
amount DECIMAL(10,2) NOT NULL,
category VARCHAR(100), -- Utilities, Supplies, Advance, Refund, Other
description VARCHAR(500) NOT NULL, -- Mandatory reason
entry_date DATETIME DEFAULT CURRENT_TIMESTAMP,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE INDEX idx_cash_ledger_date ON cash_ledger(entry_date);
Complete Schema File (run this to initialize)
Save as backend/src/config/schema.sql and run once:
-- Full initialization script
CREATE DATABASE IF NOT EXISTS swish_gms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swish_gms;
-- [all CREATE TABLE statements above in order]
-- admin_users → staff → job_cards → attendance → staff_payments → parts → parts_sales → cash_ledger
Key Relationships
admin_users (standalone — auth only)
staff
├── job_cards.assigned_staff_id → staff.id
├── attendance.staff_id → staff.id
└── staff_payments.staff_id → staff.id
parts
└── parts_sales.part_id → parts.id
job_cards
└── parts_sales.job_card_id → job_cards.id (optional)
Seed Data (for demo)
-- Staff seed
INSERT INTO staff (staff_code, full_name, role, monthly_salary) VALUES
('SAC-STF-001', 'Rajan Mehta', 'Senior Detailer', 18000),
('SAC-STF-002', 'Suresh Kumar', 'Washer', 12000),
('SAC-STF-003', 'Vikram Patel', 'PPF Technician', 22000),
('SAC-STF-004', 'Arjun Shah', 'Supervisor', 25000);
-- Parts seed
INSERT INTO parts (part_code, name, category, unit, purchase_price, selling_price, stock_qty) VALUES
('SAC-PRT-001', 'Ceramic Coat 9H', 'Chemicals', 'bottle', 1200, 2500, 15),
('SAC-PRT-002', 'PPF Film Roll (1m)', 'Films', 'sheet', 800, 1800, 10),
('SAC-PRT-003', 'Microfiber Cloth Set', 'Consumables', 'piece', 150, 350, 50),
('SAC-PRT-004', 'Car Shampoo (1L)', 'Chemicals', 'bottle', 120, 280, 30),
('SAC-PRT-005', 'Dashboard Polish', 'Chemicals', 'bottle', 90, 220, 25);