Project Overview
This project implements a robust database management system for handling suppliers, products, customers, orders, and stock movements. It provides role-based access control (RBAC), audit logging for sensitive operations, and pre-built views for low-stock alerts and revenue tracking.
Database Schema
The schema is designed in PostgreSQL with referential integrity and validation checks. Below is a simplified ER diagram:
Key entities include Products, Orders, and Stock Movements to ensure traceability of inventory changes.
Implementation Highlights
- ✔ SQL schema with constraints and triggers for audit logging
- ✔ ETL loader in Python (
load_csv.py) for bulk CSV ingestion - ✔ Views/queries for low-stock products and monthly revenue
- ✔ Optional BI dashboards for visualization
Dashboards & Reports
Below are examples of analytics generated directly from the database:
Results & Business Value
- 📊 Clear visibility of current stock and reorder needs
- 🔒 Full audit trail for order lifecycle events
- ⚡ Automated revenue and sales performance analytics
- 📦 Scalable foundation ready for API and external system integration
How to Run
- Create database:
createdb arc_dbms - Apply schema:
psql arc_dbms -f 01_scheman.sql - Seed data:
psql arc_dbms -f 02_seed.sql - Optional queries:
psql arc_dbms -f 03_queries.sql - Load CSVs (optional ETL):
python load_csv.py --db postgresql://user:pass@localhost/arc_dbms