Adding Inventory & Inspection Modules to the VS Construction API

TL;DR: I added a full‑stack Inventory/Inspection feature to the VS construction app by extending the NestJS controller, updating the Postgres schema, and wiring the new tabs into the Next.js UI. The change closes the INM‑158 tickets and makes the Control de Obra module end‑to‑end testable.

---

The Problem

The original Control de Obra module only tracked hitos, avances, and cambios. Stakeholders asked for a way to log inventario (materials, equipment, tools) and inspecciones (quality checks) directly from the web UI.

Symptoms

• The UI showed only three tabs (avance, cambios, estimaciones). Clicking “Inventario” or “Inspecciones” returned a 404.
• The API had no routes for CRUD operations on inventory items, so the front‑end could not persist data.
• The database schema lacked a construction_inventory table, causing migrations to fail when the front‑end attempted to POST a new item.

These gaps broke the end‑to‑end flow described in the project’s documentation and prevented us from closing ticket INM‑158.

---

What I Tried First

My first attempt was to reuse the existing construction_documents table for inventory items, adding a type column to differentiate between documents and inventory. I added the column in apps/api/src/db/db.ts and created a quick controller method that filtered by type = 'inventory'.

// apps/api/src/db/db.ts (first attempt)
await query(`
  ALTER TABLE construction_documents
  ADD COLUMN IF NOT EXISTS type VARCHAR(20) DEFAULT 'document';
`);

The idea was to avoid a new migration, but it introduced several problems:

• The construction_documents table already stores large binary blobs; mixing inventory rows caused performance degradation when querying for documents.
• Validation rules for inventory (e.g., quantity, unit_price) didn’t exist, so the API returned null for those fields.
• The front‑end UI needed a dedicated endpoint (/inventario) to keep the route naming consistent, but reusing the generic /documents endpoint broke the REST conventions we follow in the rest of the codebase.

After a few failed integration tests (the API returned a 500 error when trying to insert an inventory row because the quantity column was missing), I decided to create a proper inventory table and a dedicated controller.

---

The Implementation

1. Database migration

I added a new table construction_inventory with the fields required for the inventory use‑case. The migration lives in apps/api/src/db/db.ts and runs automatically on app start via the existing migrate() helper.

// apps/api/src/db/db.ts – new migration block
await query(`
  CREATE TABLE IF NOT EXISTS construction_inventory (
    id            SERIAL PRIMARY KEY,
    construction_id INT NOT NULL REFERENCES constructions(id) ON DELETE CASCADE,
    category      VARCHAR(50) NOT NULL,          -- material | equipment | tool
    name          VARCHAR(255) NOT NULL,
    quantity      NUMERIC(12,2) NOT NULL,
    unit_price    NUMERIC(12,2) NOT NULL,
    received_at   TIMESTAMP WITH TIME ZONE DEFAULT now(),
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT now()
  );
`);

The migration also adds a foreign key to the constructions table, ensuring that inventory rows are always scoped to a specific obra.

2. NestJS controller – ConstructionController

I introduced a new set of endpoints under /inventario and /inspecciones. The diff shows the added methods; I’ll highlight the GET /inventario and POST /inventario handlers.

// apps/api/src/construction/construction.controller.ts
@Get("inventario")
@RequirePerm
async getInventory(@Query("constructionId") constructionId: number) {
  const rows = await this.db.query(
    `SELECT * FROM construction_inventory WHERE construction_id = $1`,
    [constructionId]
  );
  return rows;
}

@Post("inventario")
@RequirePerm
async createInventory(@Body() payload: CreateInventoryDto) {
  const {
    constructionId,
    category,
    name,
    quantity,
    unit_price,
  } = payload;

  const result = await this.db.query(
    `INSERT INTO construction_inventory
     (construction_id, category, name, quantity, unit_price)
     VALUES ($1, $2, $3, $4, $5) RETURNING *`,
    [constructionId, category, name, quantity, unit_price]
  );

  return result[0];
}

CreateInventoryDto lives in apps/api/src/construction/dto/create-inventory.dto.ts and validates the incoming payload with class-validator.

// apps/api/src/construction/dto/create-inventory.dto.ts
export class CreateInventoryDto {
  @IsInt()
  constructionId: number;

  @IsIn(["material", "equipment", "tool"])
  category: string;

  @IsString()
  @Length(1, 255)
  name: string;

  @IsNumber()
  @Min(0)
  quantity: number;

  @IsNumber()
  @Min(0)
  unit_price: number;
}

The same pattern was replicated for the Inspecciones module, using a separate table construction_inspections (not shown here to keep the article concise).

3. Front‑end – Next.js tab integration

The UI for a construction project lives in apps/web/src/app/ventas/desarrollos/[id]/obra/page.tsx. I added two new tabs to the TABS constant and wired the new pages to the router.

// apps/web/src/app/ventas/desarrollos/[id]/obra/page.tsx
const TABS = [
  { key: "avance",       label: "Avance",       icon: "📈" },
  { key: "cambios",      label: "Cambios",      icon: "🔄" },
  { key: "estimaciones", label: "Estimaciones", icon: "💰" },
  // NEW tabs
  { key: "inventario",   label: "Inventario",   icon: "📦" },
  { key: "inspecciones", label: "Inspecciones", icon: "🔎" },
];

Each tab loads a lazy component. For the inventory tab I created InventoryTab.tsx that calls the new API endpoint via fetch.

tsx
// apps/web/src/components/InventoryTab.tsx
import useSWR from "swr";

export default function InventoryTab({ constructionId }: { constructionId: number }) {
  const { data, error, mutate } = useSWR(
    `/api/construction/inventario?constructionId=${constructionId}`,
    url => fetch(url).then(res => res.json())
  );

  if (error) return <div>Failed to load inventory.</div>;
  if (!data) return <div>Loading…</div>;

  return (
    <div>
      <h2>Inventario</h2>
      <table>
        <thead>
          <tr>
            <th>Categoria</th><th>Nombre</th><th>Cantidad</th><th>Precio Unitario

---

*Part of my [Build in Public](https://dev.to/zaerohell) series — sharing the real process of building Building PlayaMXCRM from Playa del Carmen, México.*

*Repo: `zaerohell/VS` · 2026-08-10*

\#playadev #buildinpublic