Modeling hospital procurement bidding: lessons from my graduation project

My graduation project at Hanoi University of Science and Technology (2024) was a system for a hospital's medical supplies department: clinical departments propose purchases, the supplies department takes each proposal through public procurement bidding, and the equipment that wins the tender goes into the managed inventory. The running version is at bid.vuminhhoa.com (the interface is in Vietnamese).

The hardest part wasn't the code but understanding and modeling the process. Public procurement has many steps, each step produces paperwork, and the people doing the work think in steps, not in tables. This post covers the main modeling decisions, which ones held up, and what I would do differently.

The process to digitize

Simplified, a purchase goes through three stages:

The six procurement phases follow the Vietnamese contractor selection sequence, where each "publish" step means posting to the national e-procurement system:

About 30 steps in total, and nearly every one has the same shape: a date and a document. Spotting that early makes everything downstream simpler (the last section covers what happened when I spotted it late).

Proposal and procurement are one record

A department's proposal and the procurement activity share one table, told apart by an isProposal flag:

• A department creates a proposal: a new row, isProposal = true, proposal status "Pending".
• The supplies department approves it: the same row flips to isProposal = false and starts the activity status at "Waiting to start".

The main benefit is that a procurement activity keeps its whole history, from who proposed and who approved on which date down to every bidding step, without joining two tables or copying data between stages. The cost is one table with columns that only mean something in one stage (proposal approver, activity approver...). For a process where each stage always inherits from the previous one, I think that trade-off is worth it.

Status is split into two fields, because the two stages have separate life cycles:

One lesson: write the allowed transitions down as a table and enforce it on the server, with each transition as its own action ("submit", "approve", "cancel") rather than a field anyone can write. The business rules then live in one place, and the UI only asks "what can be done from this state?".

Each phase is a JSON column

Thirty steps, each with a date and a document, would be some sixty columns. Instead each phase is one JSON column, and its default shape lives in code:

export const defaultContractorPlan = {
  plan:     { date: null, document: null },
  review:   { date: null, document: null },
  approval: { date: null, document: null },
  decision: { date: null, document: null },
  publish:  { date: null },
};

On read, the server merges the stored value over the default:

contractorPlan: { ...defaultContractorPlan, ...(bidding.contractorPlan || {}) }

Adding a step then means editing a constant and a form, with no migration. Older activities get the new step, empty.

The trade-off to know up front: data in JSON is harder to query and to constrain. The database can't check types or index it as easily as a regular column. This works when the steps are mostly recorded and displayed; if you need reports like "how many tenders are stuck at bidding document appraisal", pull at least the progress state out into its own column.

Documents must follow the record's life cycle

Each step has an attached file in a file storage service. Orphaned files come easily: a user replaces a file and the old one stays; an activity is deleted and its files remain.

The fix is to have the form track changes by field path and send three lists on save:

{
  ...data,
  createdFields: ['technical_bid.draft.document'],
  updatedFields: ['contract_signing.contract.document'],
  deletedFields: ['expert_team.purchase_meeting.document'],
}

The server walks each list: created uploads; updated deletes the old file and uploads the new one; deleted removes the file and sets the field to null. Deleting a whole activity walks all six JSON columns to clean up its documents.

The general lesson: don't make the server guess what changed by diffing the whole object; with nested data that's both costly and error-prone. The client knows exactly what the user just did, and sending "what changed" lets the server do the right thing. Also, calls to file storage aren't part of the database transaction, so order the steps so that a failure halfway leaves a stray file (which can be cleaned up), never a record pointing to a file that's gone.

Acceptance is one transaction

The last step, approving the activity, is also when the winning equipment enters the inventory. The equipment list is prepared beforehand as an Excel file, the format the supplies department already works in, and kept on the activity as a "preview list". The equipment only really exists once the activity is approved.

Duplicate model numbers are checked at two levels:

1. Within the file, in the browser as soon as the Excel is read: duplicate rows are highlighted to fix before sending.
2. Against existing data, on the server, inside the approval transaction.

Shortened from biddingService.ts:

await sequelize.transaction(async (t) => {
  await Bidding.update(approval, { where: { id }, transaction: t });

  const duplicates = await Equipment.findAll({
    where: { modelNumber: { [Op.in]: modelNumbers } }, transaction: t,
  });
  if (duplicates.length > 0) throw new ConflictError('duplicateEquipment', duplicates);

  await Equipment.bulkCreate(equipmentList, { transaction: t });
  await Activity.create({ actor, action: `imported ${equipmentList.length} items...` }, { transaction: t });
});
// After commit: update the search index.

Three things matter here:

• The status change, the new equipment and the log entry succeed or fail together. An activity can't be "Completed" while the inventory is missing its equipment.
• A duplicate error returns the duplicate rows themselves, so the UI can show them for the user to fix, instead of "Something went wrong".
• The search index is updated after commit. The database is the source of truth; the index is a copy that a reindex script can rebuild at any time. Updating the index inside the transaction would leave it holding equipment that doesn't exist if the transaction rolls back. (The Vietnamese search setup for this inventory has its own post.)

It's also worth knowing the general limit of "check, then write": two concurrent transactions can both see "no duplicate". The reliable guard for this is a unique constraint in the database; the check in code is still worth keeping to return a readable message.

Permissions in the department's own words

Permissions are named the way the supplies department talks: "Create purchase proposal", "Approve procurement activity", "View all equipment"... An administrator creates roles and ticks permissions, so a change in who does what needs no code change.

Two axes are worth keeping apart:

• Permission answers "which actions can you take": create, edit, approve.
• Scope answers "whose data can you see": staff of one department only see their department's proposals and equipment, unless they hold "view all".

Merging the two (say, a role called "Internal Medicine staff") multiplies roles by the number of departments. Kept apart, one role serves every department and the scope comes from the user's department.

To test permissions, the project has a script that simulates the whole supplies department: five roles, five staff members tied to departments, all six bidding phases through to acceptance. The part I find most worth copying: every user tries both what they're allowed to do and what they aren't. A permission test that only exercises the allowed path proves half of it.

An activity log for people to read

Every significant action writes one line with who, did what, to which object, like "… imported 12 items from procurement activity …". The dashboard shows this timeline so the whole department can see where things stand.

This log is for following the work, not for evidence that can't be denied. If you need history that even a database administrator can't quietly rewrite, see a tamper-evident audit log in PostgreSQL.

Two years later

The project ran from March to July 2024. I've come back to it a few times since: moving to TypeScript, adding equipment search with Elasticsearch, caching, Google sign-in and single sign-on.

The data model's skeleton is the same as in 2024: one table with an isProposal flag, six JSON columns for six phases, a preview equipment list. What had to be redone was the shape inside the JSON columns. The 2024 version used flat field names in unaccented Vietnamese, two separate fields per step:

// 2024
{ ngayHopHoiDongMuaSam: null, taiLieuHopHoiDongMuaSam: null,
  ngayPheDuyetDuToan: null,   taiLieuPheDuyetDuToan: null, ... }

// later
{ purchaseMeeting: { date: null, document: null },
  budgetApproval:  { date: null, document: null }, ... }

With each step as a { date, document } object, the form needs one component for every step, and the file cleanup only looks for document keys instead of remembering sixty field names. That's the price of noticing "every step is a date plus a document" late.

If you're building software for a multi-step administrative process, my advice is to sit with the people who do the work and draw the process before drawing the tables: list the steps, the paperwork each one produces, who approves it, then look for the shape the steps share. The database usually falls out of that.

Summary

• Most of the roughly 30 procurement steps share one shape, a date and a document. Spotting that early simplifies the forms, the storage and the file cleanup; I spotted it late and had to reshape the JSON.
• A proposal and its procurement activity are one row with an isProposal flag, so the whole history stays in one record, at the cost of columns that only mean something in one stage.
• Write the allowed status transitions down as a table, enforce them on the server, and expose each transition as its own action.
• One JSON column per phase, merged over a default shape in code, lets you add steps without a migration, but it is harder to query and constrain than regular columns.
• Have the client send which fields were created, updated and deleted, so the server can manage attached files without diffing nested objects.
• Approval, new equipment and the log entry go in one transaction; the search index is updated after commit, and a unique constraint is the reliable guard against duplicates.
• Keep permissions (which actions) separate from scope (whose data), and test both the allowed and the forbidden paths for every user.

If you've modeled a similar process differently, I'd love to hear about it. My email is on the home page.