Back to Experience
Jan 2024 - May 2024

County Asset and Document Management Platform

Replaced a shared Microsoft Access database with a centralized web application and REST backend, migrating thousands of records with zero downtime.

The problem

County staff tracked physical equipment, property, and official documents using a shared desktop Microsoft Access database. Concurrent access caused frequent file locking, slow query responses, and data corruption risks.

Under contract for Chippewa County Information Systems (Jan 2024 - May 2024), I replaced the shared Access file with a browser-accessible web application and REST API running on the county network.

Database schema and migration

  • Designed a normalized relational database schema in MySQL and MariaDB to eliminate duplicate fields and enforce referential integrity with foreign keys.
  • Wrote migration scripts in PHP and SQL to extract, validate, and transfer historical records without downtime.
  • Verified record row counts and data checksums before and after the cutover to ensure data fidelity.

Web application and REST APIs

  • Built PHP REST endpoints using PDO prepared statements to protect against SQL injection during searches, record updates, and file uploads.
  • Built an asynchronous search and editing interface in vanilla JavaScript using Fetch and AJAX, enabling staff to search and update inventory without page reloads.
  • Configured Apache rewrite rules to route API requests through a single front controller and serve static files directly.

Administration, auditing, and deployment

  • Built administrative screens for user accounts, department assignments, and permissions so non-technical staff can manage access safely.
  • Implemented role-based access control (RBAC) with secure server sessions and bcrypt password hashing.
  • Added an audit log view that records timestamps, user IDs, and previous values for all database modifications.
  • Built export utilities allowing staff to download compliance reports in CSV, JSON, and formatted PDF formats.
  • Deployed the application on a dedicated Linux virtual machine with daily backup cron jobs and restricted file permissions.

Technologies used

  • Backend and APIs: PHP, REST APIs, PDO (Prepared Statements), Apache 2.4, mod_rewrite, Session Management
  • Database architecture: MySQL, MariaDB, 3NF Normalization, B-Tree Indexing, Composite Indexing, Foreign Keys, ACID Transactions
  • Frontend: Vanilla JavaScript (ES6+ Fetch / AJAX), HTML5, CSS3, Responsive Layouts
  • Security and administration: Role-Based Access Control (RBAC), Admin Dashboards, User Management, Bcrypt Password Hashing, Audit Logging
  • Infrastructure and tooling: Linux Virtual Machine, Bash Automation, Cron Scheduling, Automated Backups, Git