🌾 Farmer Management System

A full-stack database-driven web application built with Python Flask and MySQL for managing farmer records, agro-products, farming types, and transactional audit logs — with real-time SQL trigger-based change tracking.

---

🖥️ Tech Stack

Layer	Technology
Backend	Python 3, Flask, SQLAlchemy
Database	MySQL
Frontend	HTML5, CSS3, Bootstrap, Jinja2
Security	Werkzeug password hashing, Flask session management
DB Features	SQL Triggers, Stored Procedures, ACID-compliant CRUD

---

✨ Features

• 👨‍🌾 Farmer Registration & Management — Add, update, view, and delete farmer profiles
• 🌱 Farming Type Management — Categorize and manage farming types per farmer
• 🛒 Agro-Product Management — Full CRUD for agricultural products linked to farmers
• 🔐 Secure Authentication — Login system with hashed passwords and session handling
• 📋 Automated Audit Logging — SQL triggers automatically log every INSERT, UPDATE, and DELETE to a trig table
• 📦 Stored Procedure — proc stored procedure for efficient farmer record retrieval
• 📊 Relational Database Design — Normalized schema with foreign keys and joins

---

🗄️ Database Design

Tables

• farmers — core farmer records (name, location, contact, farming type)
• farming_type — lookup table for farming categories
• agro_products — products associated with each farmer
• users — authentication table with hashed passwords
• trig — audit log table populated automatically by triggers

SQL Triggers

-- Fires after every INSERT on the farmers table
AFTER INSERT ON farmers → logs to trig

-- Fires after every UPDATE on the farmers table  
AFTER UPDATE ON farmers → logs to trig

-- Fires after every DELETE on the farmers table
AFTER DELETE ON farmers → logs to trig

Stored Procedure

-- Retrieves all farmer records
CALL proc();

---

📁 Project Structure

Farmer_Management_System/
├── main.py                  # Flask app — routes, auth, DB logic
├── farmers.sql              # Full DB schema: tables, triggers, stored procedure
├── templates/               # Jinja2 HTML templates
│   ├── index.html
│   ├── login.html
│   ├── farmers.html
│   ├── add_farmer.html
│   └── ...
├── static/                  # CSS, JS, images
│   ├── style.css
│   └── ...
└── README.md

---

⚙️ Setup & Installation

Prerequisites

• Python 3.8+
• MySQL 8.0+
• pip

Steps

1. Clone the repository

git clone https://github.com/asthasingh0660/Farmer_Management_System.git
cd Farmer_Management_System

2. Install dependencies

pip install flask flask-login flask-sqlalchemy pymysql werkzeug

3. Set up the database

Open MySQL and run:

SOURCE farmers.sql;

This creates all tables, inserts sample data, and sets up the triggers and stored procedure automatically.

4. Configure the database connection

In main.py, update the connection string to match your MySQL credentials:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:YOUR_PASSWORD@localhost/farmers_db'

5. Run the application

python main.py

6. Open in browser

http://localhost:5000

---

🧪 Key Technical Concepts Demonstrated

Concept	Implementation
ORM vs Raw SQL	SQLAlchemy ORM for models + raw engine.execute() for triggers/procedures
SQL Triggers	Automatic audit trail on all DML operations
Stored Procedures	Encapsulated query logic callable via CALL proc()
Password Security	Werkzeug generate_password_hash / check_password_hash
Session Management	Flask-Login for persistent login state
Relational Design	Foreign keys linking farmers → products → farming types
ACID Compliance	MySQL InnoDB engine ensures transactional integrity

---

🚀 Future Improvements

• REST API endpoints for mobile integration
• Export farmer data to CSV/Excel
• Role-based access control (Admin vs Viewer)
• Dashboard with charts (crop distribution, product counts)
• Dockerize the application for easy deployment

---

👩‍💻 Author

Astha Singh GitHub

---

📄 License

This project is open source and available under the MIT License.