← Projects

Asia Builders
ERP

The CEO of Asia Group can now see real-time profit/loss across all active projects, outstanding vendor dues, and a government-audit-ready expense report — in under 60 seconds — replacing a process that previously took 3 days of manual Excel consolidation.

Client Mr. Rauf Khan, CEO — Asia Group of Companies
Role Lead Engineer
Stack NestJS · Next.js 15 · PostgreSQL · Cloudflare R2
Domain asiabuilderz.com SSL configuration in progress
Owner dashboard showing active project P&L, recent transactions, and expense breakdown
ERP interface showing vendor management and transaction tracking

The Problem

Three countries,
one Excel file each.

Asia Group of Companies was managing simultaneous construction projects across UAE, Oman, and Pakistan through a patchwork of separate Excel files — one per project, manually updated. There was no consolidated view of profit/loss, no reliable vendor payment tracking, and no way to quickly determine which vendors had outstanding dues.

Audit preparation for government regulators took 2–3 days of manual receipt collection. Data loss risk was real. Financial accuracy was low. The accountant, owner, and auditor had no shared, authoritative source of truth — and the CEO in Dubai was working from emailed snapshots that were already stale on arrival.

Requirements I Gathered

Six questions
before a line of code.

Before architecture started, these questions shaped every decision:

  1. Who touches what?

    Three roles emerged: OWNER (full access, delete rights), ACCOUNTANT (data entry, no deletes on completed projects), REVIEWER (read + edit with audit trail). Role boundaries drove every permission check in the system.

  2. What is a “vendor” really?

    Contractors (agreement-based, PAID-only, contract progress tracked) behave fundamentally differently from Suppliers and Service providers (flexible status, no agreement). This distinction drove the isContractor boolean and cascaded into transaction validation throughout the service layer.

  3. What is a “transaction” really?

    Income never has a vendor (uses clientName instead). Expenses always require a vendor. RECEIVED status is income-only. These are cross-field constraints — not expressible in DTO validation alone, enforced in the service layer.

  4. How are unpaid expenses tracked?

    DUE expenses needed a settlement flow — partial payments, multiple dues settled by one payment, bidirectional visibility. This shaped the transaction_settlements join table design.

  5. What does “audit ready” mean?

    Government auditors need running balance, CNIC, cheque numbers, physical file references, and vendor identity in one exportable document. This defined the Government Audit report schema.

  6. How should vendor types be managed?

    The business needed to add custom types (e.g. “Electrician”) without a code deploy — drove the shift from a hardcoded enum to a DB-backed vendor_types table with seeded non-deletable system types.

Architecture Decisions

Five calls that
shaped the system.

arch-01 db-backed-vendor-types-instead-of-typescript-enum
Decision Vendor types stored in a vendor_types DB table. System-defined types (CONTRACTOR, SUPPLIER, SERVICE) seeded in migration and flagged non-deletable; user-created types freely manageable.
Why Client needed to add custom types at runtime (e.g. “Electrician”) without touching code or redeploying.
Rejected Hardcoded VendorType enum — would require a code change + migration for every new type.
arch-02 store-cloudflare-r2-object-key-not-signed-url
Decision Store the R2 object key in the DB. Generate signed URLs on-demand at request time. Saga/compensating pattern: if DB save fails after R2 upload succeeds, the orphaned R2 object is deleted immediately.
Why Signed URLs expire after 1 hour. Storing the key means URLs are always fresh. R2 is S3-compatible, so the AWS SDK is reused as-is.
Rejected Storing full signed URLs — they expire silently, causing broken downloads days later with no observable error at write time.
arch-03 independent-per-section-dashboard-queries
Decision 6 independent dashboard endpoints: stats, active-projects, upcoming-payments, expense-breakdown, profit-overview, recent-transactions.
Why If one query fails (e.g. expense breakdown), the other five sections still load. The dashboard remains useful even during partial outages.
Rejected Single-query dashboard — one bad SQL kills the entire screen.
arch-04 getRawMany-plus-separate-count-instead-of-getManyAndCount
Decision getRawMany() + typed result interface + separate count query builder for paginated lists.
Why TypeORM’s getManyAndCount() with subquery addSelect + ORDER BY causes a databaseName undefined crash — TypeORM loses track of the subquery alias when wrapping for pagination.
Rejected getManyAndCount() — hit this crash in production on the global transactions list.
arch-05 nestjs-feature-module-isolation-entities-registered-in-owning-module
Decision TypeOrmModule.forFeature([Entity]) inside the owning feature module. AppModule imports the feature module, not the raw entities.
Why Registering raw entities in AppModule directly breaks the module boundary and causes double-registration errors at startup.
Rejected Centralizing all entity registration in AppModule — caused the VendorTypeEntity bug post-implementation.

Problems Hit

Five bugs worth
writing down.

vendor-routes-returning-404-after-controller-was-written

Symptom All /vendors endpoints returned 404.
Root cause Two separate issues: (1) controller route decorators had double prefixes — @Controller('vendors') already sets the base path, so @Get('vendors/:id') resolved to /vendors/vendors/:id. (2) VendorsModule was never registered in AppModule — NestJS doesn’t auto-discover modules.
Fix Fixed route decorators; imported VendorsModule in AppModule.

getManyAndCount-crash-on-global-transactions-list

Symptom TypeError: Cannot read properties of undefined (reading 'databaseName') from inside TypeORM’s SelectQueryBuilder.
Root cause getManyAndCount() clones the query builder internally and loses subquery alias resolution when an addSelect subquery + ORDER BY are combined.
Fix Replaced with getRawMany() + typed interface + separate count query builder.

VendorTypeEntity-module-registration-error-post-implementation

Symptom Runtime error after the vendor types feature was implemented; application failed to boot.
Root cause VendorTypeEntity (a TypeORM entity) was placed in AppModule’s imports array, which expects NestJS modules — not entities.
Fix Moved VendorTypeEntity registration to VendorsModule via TypeOrmModule.forFeature([VendorTypeEntity]).

postgresql-password-special-characters-breaking-db-auth-on-vps

Symptom Auth failures when connecting to PostgreSQL on the Hostinger VPS despite correct credentials.
Root cause Passwords containing # and $ were being interpolated by shell scripts in the connection string.
Fix Switched to alphanumeric-only password to eliminate shell interpolation risk.

EXTRACT-DAY-FROM-date-subtraction-postgresql-type-error

Symptom SQL error: function pg_catalog.extract(unknown, integer) does not exist in dashboard queries.
Root cause date - date in PostgreSQL returns an integer (day count), not an interval. EXTRACT requires an interval or timestamp, not an integer.
Fix Replaced EXTRACT(DAY FROM ...) with plain date - date arithmetic throughout dashboard and reporting queries.

What Shipped

Every layer,
accounted for.

Backend — NestJS

  • 10 feature modules

    Auth, Users, Projects, Transactions, Vendors, Documents, Dashboard, Investments, Reports, VendorTypes — each module-isolated with its own entity registration.

  • Settlement flow

    POST /transactions/settle-dues — batch settlement with partial support. GET /projects/:id/vendors/:vendorId/transactions/open-dues for vendor-scoped outstanding balance.

Notable API endpoints

  • GET /dashboard/stats · active-projects · upcoming-payments · expense-breakdown · profit-overview · recent-transactions
  • Full CRUD: Projects · Vendors · Transactions · Investments · Documents
  • POST /transactions/settle-dues — batch settlement, partial support
  • POST /reports/profit-loss · expense-breakdown · vendor-payment · project-comparison · government-audit · investment-portfolio

Database — PostgreSQL 17

  • 7 migrations

    From initial schema through settlement tables, covering all entities and relationships incrementally.

  • 11 entities

    Full relational model for the construction finance domain.

users projects transactions transaction_categories transaction_settlements vendors vendor_types project_vendors documents investments investment_value_updates

Financial Core

  • Transaction engine

    INCOME / EXPENSE / DUE types with auto-generated reference codes (e.g. EXP-NIS-0001), auto-calculated balances, and receipt upload attached directly.

  • DUE settlement lifecycle

    DUE → PARTIALLY_SETTLED → SETTLED backed by transaction_settlements. Settlement modal previews open balances and records batch payments in one step.

  • Vendor management

    Complete vendor profiles (CNIC, phone, bank); contracted vs paid vs outstanding across all projects; isContractor flag drives agreement values and progress bars.

  • Live P&L dashboard

    All active projects on one screen. Per-project income/expense/net profit updated on every transaction save. 6 independent section endpoints — one failure doesn’t break the screen.

Reports — 6 types × 2 formats

  • P&L Report

    Date-range and project filters. PDF + Excel output.

  • Expense Breakdown

    Category-level breakdown with Recharts visualization. PDF + Excel.

  • Vendor Payment Report

    Outstanding dues and payment history per vendor. PDF + Excel.

  • Project Comparison

    Cross-project P&L side-by-side. PDF + Excel.

  • Government Audit Report

    Running balance, CNIC, cheque numbers, physical file references, and vendor identity in one document. PDF + Excel.

  • Investment Portfolio

    Valuation history timeline, ROI per holding. PDF + Excel.

Frontend — Next.js 15

  • Role-gated pages

    Projects list + detail, Vendors list + detail, Transactions (global + project-scoped + vendor-scoped), Documents, Investments.

  • Dashboard (6 sections)

    Each section loads independently via its own API call. Recharts for expense breakdown and profit overview visualizations.

  • Reports Hub

    6 report modals, each with date-range + project filters and direct PDF/Excel download.

  • Settings

    Profile management (name, phone, avatar in R2). Vendor type management — OWNER can add custom types without a code deploy.

Infrastructure

  • Hostinger KVM2 VPS

    PM2 process management + Nginx reverse proxy. PostgreSQL 17 on the same server. Full control, no managed database overhead.

  • Cloudflare R2 document storage

    S3-compatible. Receipts stored by object key; signed URLs generated on demand. Saga pattern: orphaned R2 objects deleted immediately on DB failure.

  • Domain

    Deployed at asiabuilderz.com. SSL/TLS via Certbot pending. Subdomain architecture (api.asiabuilderz.com) planned.

What I’d Do Differently

One schema,
designed upfront.

The transaction_settlements join table design — recording amount_applied per payment-to-due link — is correct. But the settlement feature was designed iteratively in one long session rather than up front, which meant the TransactionStatus enum grew incrementally and required two separate migrations to reach its final shape. Defining the full status lifecycle — including income-vs-expense asymmetry and settlement states — before the first migration would have produced one clean migration instead of three patched ones.

Have a business problem
worth solving?

Get in touch See all projects WhatsNextPlease →