Employees CRUD

A web-based employee management system built to practice full CRUD workflows, Mexican RFC and CURP generation, and automatic change tracking through MySQL triggers.

Snapshot

Overview

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.

The problem

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.

• Employee records need structured personal and employment data, including birth state and hire date.
• Mexican tax and identity identifiers (RFC and CURP) follow formatting rules that should stay consistent across inserts.
• Administrative actions should leave a trace when records change, without forcing every PHP script to duplicate logging logic.

Who it was for

• Developers learning PHP, MySQL, and Ajax-based CRUD patterns
• Small internal demos where a lightweight employee registry is enough
• Anyone reviewing how database triggers can enforce identifier generation and audit trails

My role

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.

What the project does

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.

• Lists employees in a sortable, searchable table with Spanish UI labels
• Adds new employees through a modal form with Mexican state selection
• Edits existing records, including manually adjustable RFC and CURP on update
• Deletes records after a confirmation dialog
• Shows audit events with previous and current values where applicable
• Refreshes table content after each Ajax action without reloading the full page

Key features

Employee CRUD with Ajax

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.

Automatic RFC and CURP generation

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.

Database-driven audit log

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.

Searchable tables with DataTables

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.

Confirmation-driven deletes

Alertify confirmation dialogs guard delete actions on both employees and audit entries, giving basic protection against accidental removals in a demo environment.

Technical approach

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.

Design decisions

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.

• Used modals instead of separate form pages so users stay in the table context while editing records.
• Limited list queries to 100 rows to keep demo tables responsive without adding server-side pagination logic in PHP.
• Stored audit messages as concatenated text snapshots rather than normalized diff fields, which made the log easy to inspect during development.
• Kept RFC and CURP editable on update so corrections were possible after automatic generation on insert.
• Localized DataTables strings to Spanish to match the rest of the UI copy.

Challenges and tradeoffs

• Identifier generation in SQL is powerful but harder to test and refactor than equivalent application-layer code.
• Concatenating POST values directly into SQL simplified the prototype but left the endpoints vulnerable to injection and unsuitable for production as written.
• Reloading table fragments after each Ajax call reinitializes DataTables, which is simple but not the most efficient update pattern at scale.
• The audit log stores human-readable strings instead of structured JSON, which makes downstream querying and reporting limited.
• There is no user authentication, role model, or input validation layer beyond basic HTML form controls.

What I learned

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.

• Triggers are a strong fit for rules that must never be bypassed, like audit logging on every mutation.
• Ajax partial updates make small admin tools feel responsive, but they require deliberate reinitialization of table plugins.
• Domain-specific identifier rules belong in a clearly documented place, whether that is SQL or a dedicated service layer.
• Early architectural shortcuts, like raw SQL strings from user input, become obvious once the core feature set works.

Current status

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.

If I revisited this today

• Replace string-built SQL with prepared statements and centralize database access in a small data layer.
• Move RFC and CURP generation into tested PHP or service code, keeping triggers only where they still add clear guarantees.
• Add authentication, server-side validation, and basic role separation between viewers and editors.
• Store audit events in structured JSON columns with timestamps, action types, and actor identity.
• Expose CRUD through a REST API and rebuild the front end with modern tooling instead of fragment reloads.
• Add schema migration files and seed data so the project can be reproduced reliably on a new machine.