290 lines
9.2 KiB
Markdown
290 lines
9.2 KiB
Markdown
# Database Schema — Swish Auto Care GMS
|
||
## MySQL 8.x — Full Schema Reference
|
||
|
||
---
|
||
|
||
## Setup Instructions
|
||
|
||
```sql
|
||
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.
|
||
|
||
```sql
|
||
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.
|
||
|
||
```sql
|
||
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):**
|
||
```js
|
||
// 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.
|
||
|
||
```sql
|
||
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):**
|
||
```js
|
||
// 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.
|
||
|
||
```sql
|
||
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.
|
||
|
||
```sql
|
||
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.
|
||
|
||
```sql
|
||
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.
|
||
|
||
```sql
|
||
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):**
|
||
```js
|
||
// 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).
|
||
|
||
```sql
|
||
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:
|
||
|
||
```sql
|
||
-- 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)
|
||
|
||
```sql
|
||
-- 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);
|
||
```
|