A web-based employee management system built to practice full CRUD workflows, Mexican RFC and CURP generation, and automatic change tracking through MySQL triggers.
| Field | Details |
|---|---|
| Type | Full-stack web app |
| Context | Personal Academic project |
| Role | Solo developer |
| Year |
| 2020 |
| Status | Completed prototype |
| Main focus | Employee CRUD, identifier generation, and audit logging |
Employees CRUD is a browser-based application for managing employee records in a MySQL database. I built it to practice a classic LAMP-style stack with a clear separation between a Bootstrap front end, PHP endpoints, and database logic that lives partly in SQL triggers.
The app goes beyond basic CRUD by generating RFC and CURP values from employee data at insert time and maintaining a bitácora (audit log) whenever records are created, updated, or deleted. It was a personal learning project, not a production HR system, but it reflects how I think about keeping business rules close to the data layer when they are stable and repeatable.
Small teams and learning environments often need a simple way to maintain employee records without standing up a full HR platform. The challenge is not only storing names and dates, but also handling domain-specific identifiers and keeping a reliable history of changes.
I designed and built the project end to end: the UI with Bootstrap modals and DataTables, the Ajax layer in JavaScript, the PHP endpoints for create/read/update/delete, the MySQL schema assumptions, and the trigger scripts for RFC/CURP generation and bitácora logging. I also wired the navigation between the employee view and the audit log view.
The application lets users browse, search, add, edit, and delete employee records from the browser. On create, the database generates RFC and CURP values from the submitted data. Every insert, update, and delete is captured automatically in a separate audit table that can be reviewed on its own page.
Create, update, and delete operations run through jQuery Ajax calls to small PHP scripts. Successful responses reload only the table component, which keeps interactions fast and avoids full page refreshes.
$.ajax({
type: "POST",
url: "php/agregarDatos.php",
data: cadena,
success: function (r) {
if (r == 1) {
$("#table").load("components/table.php");
alertify.success("Empleado agregado con exito!");
} else {
alertify.error("Fallo el servidor!");
}
},
});I used partial reloads so the DataTable could reinitialize after each mutation while keeping the modal workflow simple on the main page.
A BEFORE INSERT trigger derives RFC and CURP from name parts, birth date, gender, and birth state. It normalizes accented characters, applies naming edge cases, looks up state abbreviations, and builds the identifier strings before the row is stored.
SET NEW.emp_rfc = CONCAT(@flastname,@fvlp,@mlastname,@firstname,@daterfc,@hclave);
SET NEW.emp_curp = CONCAT(@flastname,@fvlp,@mlastname,@firstname,@daterfc,@gender,@state,@flpc,@flmc,@fnc,@dif,@random);I pushed this logic into MySQL so every insert followed the same rules, regardless of which PHP endpoint handled the request.
Separate AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers write to a bitacora table with the employee number, activity description, and serialized old/new data. The audit page reads from that table independently of the main CRUD scripts.
INSERT INTO bitacora (emp_no,activity,new_data)
VALUES (NEW.emp_no,"Se insertó un nuevo empleado",CONCAT('Información Actual: ', NEW.birth_date,' ',NEW.first_name,' ',NEW.last_name,' ',NEW.gender,' ',NEW.hire_date,' ',NEW.second_lastname,' ',NEW.birth_state,' ',NEW.emp_rfc,' ',NEW.emp_curp));This kept the PHP layer focused on CRUD while guaranteeing that logging could not be skipped by a missing application call.
Both the employee list and audit log use DataTables with Spanish labels, pagination, and column sorting. The employee table loads the latest 100 records ordered by employee number.
Alertify confirmation dialogs guard delete actions on both employees and audit entries, giving basic protection against accidental removals in a demo environment.
The stack follows a traditional server-rendered PHP model augmented with client-side Ajax. index.php and bitacora.php provide the shell layout, navigation, and modals. Table bodies are loaded from components/table.php and components/table_bitacora.php, which query MySQL directly and render HTML rows.
PHP endpoints under php/ accept POST payloads and execute SQL statements through mysqli. A shared conection.php file centralizes the database connection for local Apache/MySQL development.
The most important business rules live in SQL:
generateRFCCURP runs before insert and computes identifiers from employee fields plus a lookup against an estados table for CURP state codes.EmployeeInsert, EmployeeUpdated, and EmployeeDelete maintain the audit trail after data changes.The front end uses Bootstrap for layout, Alertify for notifications and confirmations, and jQuery for DOM updates and Ajax transport. There is no authentication layer, no ORM, and no API abstraction beyond the PHP scripts the JavaScript calls directly.
I kept the interface straightforward because the goal was clarity over visual complexity. Bootstrap modals separate create and edit flows, and the fixed top navigation makes it obvious how to switch between employees and the audit log.
Building this project helped me connect front-end interaction patterns with database behavior in a way that pure tutorial CRUD examples often skip. I learned when trigger-based logic is worth the tradeoff, and where application code still needs to own validation, security, and UX.
This project is no longer maintained and should be understood as a completed personal prototype. I keep it in my portfolio because it shows an early stage of my full-stack work: practical CRUD delivery, database trigger design, and attention to locale-specific business data like RFC and CURP.