From BugHound to UFT: Redesigning a Bug Submission Platform with MySQL, Node.js, and Angular

Data Design

The original BugHound system stored bug reports in MongoDB. To leverage existing expertise, the data model was rebuilt in MySQL. Tables were defined according to the original MongoDB schema, documented in a data dictionary, and created with foreign‑key constraints named fk_table_field to enforce cascade updates and deletions. New file‑related fields were extracted into a separate demandfiles table that stores uploader, upload time and download count.

Technical Practice

Phase 1 : Backend rendered pages with the ejs template engine; the frontend used jQuery for client‑side logic.

Phase 2 : Angular replaced jQuery. The ng-include directive moved header/footer assembly to the client, allowing removal of ejs.

Phase 3 : Page‑header flicker was eliminated by converting the app to a single‑page Angular application using ui‑router and ui‑view. All routes now load index.html. Local authentication was added: on entry the server returns the user identity, which is stored in a service. Route changes intercept requests via stateChangeStart / stateChangeSuccess. To avoid premature rendering, the app broadcasts an event with $broadcast and child components listen with $on.

Schedule Table Design – Version 1

Each demand contains a list of schedule entries with startDate and endDate. The frontend computes the week’s start ( ws) and end ( we) dates, then determines which days each demand occupies. The resulting blocks array records the occupied day indices (e.g., [0,1,2] and [4,5]).

[{... data: [{demandID:xx, startDate:xx, endDate:xx, ...}, …], blocks:[{demandID:xx, block:[0,1,2]}, {demandID:xx, block:[4,5]}] }]

These blocks drive the HTML table: each block’s length determines how many td cells it spans.

Schedule Table Design – Version 2

To simplify calculations, the schedule now stores startDate as a day count since 1970‑01‑01 and a days field for duration. Overlapping demands are allowed. Two helper arrays are introduced:

var weekpile = [0,0,0,0,0,0,0]; // tracks stack level per day
var wee = [[],[],[],[],[],[],[]]; // each sub‑array holds indices of demands starting that day

The weekpile value determines a demand’s vertical layer; this value is stored as pilefloor in each block object.

blocks: [{id:xx, demandID:xx, startDate:xx, days:xx, weekdays:[0,1,2], pilefloor:n}, …]

The wee array points to block indices, enabling the UI to compute margin‑top for each weekday column based on the first block’s pilefloor. The width of each demand bar is set to days * 100%, and table cells are generated by iterating over wee.

Collaboration

Source code is hosted at https://github.com/o2team/UFT. Two collaboration workflows are used:

Feature branches are pushed and merged via pull requests, with a designated maintainer performing the final merge.

Local changes are synchronized by fetching the remote master ( git fetch origin master) and merging ( git merge FETCH_HEAD). When conflicts arise, p4merge is configured as the mergetool.

[diff]
    tool = p4merge
[difftool "p4merge"]
    cmd = "p4merge $LOCAL $REMOTE"
[merge]
    tool = p4merge
[mergetool "p4merge"]
    cmd = "p4merge $BASE $LOCAL $REMOTE $MERGED"
    trustExitCode = true
    keepBackup = false

Temporary File Cleanup

Files uploaded to a temp directory are periodically removed by a scheduled task that deletes any file whose last access time is older than three days, preventing uncontrolled growth of the folder.

Future Plans

Introduce MySQL views and functions to simplify data access.

Separate the model layer from business logic for a cleaner MVC architecture.

Expand demand‑status handling and add real‑time system notifications (non‑email).