← 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, which drove the shift from a hardcoded enum to a DB-backed vendor_types table with seeded non-deletable system types.

Architecture Decisions

Seven 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.
arch-06 pdfmake-exceljs-instead-of-puppeteer-for-report-generation
Decision pdfmake (pure JS, JSON-defined PDF documents, no browser process) and ExcelJS (styled multi-sheet workbooks) for all 6 report types.
Why Reports run on a shared 2-core/8GB VPS alongside the API and Postgres. Puppeteer ships a ∼300MB Chromium binary and forks a full browser process per render, a real resource concern at that scale. The reports are tabular financials with no charts, well within pdfmake’s capabilities.
Rejected Puppeteer/HTML-to-PDF: rejected specifically for the VPS resource profile, not for capability reasons.
arch-07 rolesguard-decorator-instead-of-per-route-authorization-checks
Decision A single RolesGuard + @Roles() decorator applied across projects, vendors, transactions, investments, documents, users, reports, and dashboard controllers, one coordinated commit spanning the whole API surface. Paired with a frontend useIsReadOnly() hook disabling mutating UI actions for reviewers.
Why Needed a read-only REVIEWER role (for a CA consultant or audit reviewer) that could view all ERP data without mutating anything enforced consistently everywhere, not a UI-level toggle.
Rejected Piecemeal per-route authorization checks duplicated in each controller method, harder to audit, easy for a new route to miss the check.

Problems Hit

Nine 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.

outstanding-balance-aggregate-blind-to-partial-settlements

Symptom A vendor could show $0 owed while genuinely still owing money.
Root cause Outstanding-balance queries only summed transactions with status = 'DUE' at full t.amount. Once partial settlements shipped, a PARTIALLY_SETTLED transaction was invisible to these queries entirely, since the aggregation logic predated the new status and wasn’t updated in the same breath.
Fix COALESCE(SUM(t.amount - t.settled_amount), 0) ... WHERE status IN ('DUE', 'PARTIALLY_SETTLED'), landed in the same migration that introduced transaction_settlements and settled_amount The fix shipped with the feature that created the need for it.

soft-deleted-project-vendor-leaving-documents-live

Symptom Documents attached to a soft-deleted Project or Vendor remained visible and live.
Root cause The Documents module was initially built with no cascade awareness: soft-deleting a parent didn’t touch its attached document rows.
Fix softDeleteDocuments(entityType, id) called explicitly before each parent’s softDelete(), mirrored in vendors.service.ts. Caught and closed within the same build session, not after production data went inconsistent, though it’s per-service, not centrally enforced, so a future entity type could still forget to replicate the call.

orphaned-r2-objects-on-db-write-failure

Symptom A successful file upload to R2 followed by a failed DB write left an orphaned object in R2 with no corresponding record.
Root cause Upload and metadata write span two systems, R2 and Postgres, with no shared transaction between them.
Fix Upload to R2 first, wrap the DB save() in try/catch; on failure, call storageService.delete() as a compensating action and rethrow. Accepted, undocumented gap: a crash between the upload and the compensating delete isn’t covered: this is a synchronous try/catch, not a durable outbox/queue pattern.

vendor-type-recreate-blocked-by-soft-deleted-row

Symptom Deleting a vendor type (e.g. “Contractor”) and recreating one with the same name threw “already exists”, even though the original was soft-deleted.
Root cause create() only checked for a slug collision and threw unconditionally on any match, without checking whether the colliding row was isActive or already soft-deleted.
Fix if (existing.isActive) throw ...; else { existing.isActive = true; return this.repo.save(existing); } This reactivates the soft-deleted row instead of blocking on it. Also switched prune.ts to INSERT ... ON CONFLICT (slug) DO UPDATE SET is_active = true for idempotent system-type seeding. Found through real usage a month after the original bug shipped, not caught immediately.

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 →