Skip to content

Material Takeoff (MTO) Schemas Documentation

Last Updated: 2025-11-27 Database: lco-construction Container: MaterialTakeoff Partition Key: /projectId

Table of Contents

  1. Overview
  2. Discipline Types
  3. Common Fields (All Disciplines)
  4. Calculated Columns by Discipline
  5. Calculation Formulas
  6. Frontend Implementation
  7. Schema Consistency

Overview

The Material Takeoff (MTO) system tracks construction materials, equipment, and associated costs across 9 engineering disciplines. Each discipline has:

  • Common fields shared across all disciplines
  • Discipline-specific fields for technical details
  • Calculated/computed columns with Excel-style formulas

Total Columns Across All Disciplines: 511 Total Calculated Columns: 50

Database Structure

Cosmos DB
└── lco-construction (database)
    └── MaterialTakeoff (container)
        ├── Partition Key: /projectId
        └── Documents by discipline type

Schema Versions

  • Backend: Python Pydantic models (/backend/app/schemas/mto.py)
  • Frontend: TypeScript interfaces (/frontend/src/types/mto.ts)
  • Excel Source: 9 discipline sheet schemas (/schemas/*.json)

Discipline Types

Discipline API Enum Columns Calculated Complexity Description
Electrical Equipment electrical_equipment 48 2 ⭐ Motors, MCCs, transformers, switchgear
Mechanical Equipment mechanical_equipment 50 2 ⭐ Pumps, tanks, heat exchangers, vessels
Bulk Electrical bulk_electrical 34 2 ⭐ Cables, conduits, cable trays
Instrumentation instrumentation 37 3 ⭐⭐ Sensors, control valves, transmitters
Piping piping 143 11 ⭐⭐⭐⭐⭐ Pipes, fittings, flanges, valves, bolts
Civil civil 52 8 ⭐⭐⭐⭐ Excavation, backfill, earthwork
Concrete concrete 46 9 ⭐⭐⭐⭐ Concrete, rebar, formwork
Structural structural 54 7 ⭐⭐⭐ Steel members, connections, coatings
Architectural architectural 47 6 ⭐⭐⭐ Walls, finishes, doors, windows

Common Fields (All Disciplines)

Primary Keys & Discriminator

  • id (string) - Document ID
  • mtoId (string) - MTO identifier (synced with id)
  • discipline (DisciplineType) - Discipline discriminator
  • projectId (string) - Partition key
  • serviceId (string, optional) - Reference to service
  • createdAt (string, datetime)
  • updatedAt (string, datetime)

Common Identification Fields

  • sourceFileName - Original import file name
  • itemNo - Item number
  • maturityLevel - Maturity level (IFC, IFR, etc.)
  • revision - Revision number
  • tagNo - Tag number (Equipment/Piping)
  • lineNo - Line number (Piping)
  • pfdNumber - PFD number
  • drawingNoOrPid - Drawing/P&ID number
  • systemSubsystem - System/Subsystem
  • function - Function

Description Fields

  • description - Main description
  • equipmentDescription - Equipment description
  • description1 through description5 - Additional description fields

Quantity & Wastage Fields

  • quantity (number) - Base quantity
  • uom (string) - Unit of measure (EA, M, M2, M3, etc.)
  • wastePercent (number) - Waste percentage
  • totalQuantityInclWastage (number) - CALCULATED: quantity * (1 + wastePercent)

Cost Fields

  • manhourPerUnit (number) - CALCULATED (complex formula by discipline)
  • materialUnitPrice (number) - Material unit price input
  • totalMaterialUnitPrice (number) - CALCULATED (complex formula)
  • tagEquipmentUnitPrice (number) - Equipment unit price input
  • totalEquipmentUnitPrice (number) - CALCULATED (complex formula)
  • subcontractorCostAllInRate (number) - Subcontractor all-in rate
  • totalEstimationUnitPrice (number) - CALCULATED Final estimation price (see Labor Cost Calculations)

Installation - Labour Fields (NEW)

  • laborProdFactor (number) - Labor productivity factor from discipline settings
  • totalManhours (number) - CALCULATED: quantity * manhourPerUnit * laborProdFactor
  • hourlyCost (number) - Hourly labor cost from crew blended rate
  • laborUnitCost (number) - CALCULATED: manhourPerUnit * laborProdFactor * hourlyCost
  • totalLaborCost (number) - CALCULATED: quantity * laborUnitCost

Subcontractor Assignment Fields

  • subcontractorCode (string) - Reference to subcontractor code
  • subcontractorName (string) - Subcontractor name for display

Logistics Fields

  • shippingResponsibility - Shipping responsibility
  • shippingWeight (number) - Shipping weight
  • shippingVolume (number) - Shipping volume
  • deliveryLeadTime (number) - Delivery lead time (weeks)
  • countryOfOrigin - Country of origin
  • materialFreight (number) - Material freight cost
  • materialTransportation (number) - Material transportation cost

Manufacturer Fields

  • manufacturer - Manufacturer
  • manufacturerSupplier - Manufacturer/Supplier
  • modelNumber - Model number
  • model - Model
  • serialNumber - Serial number
  • datasheetSpecificationReference - Datasheet/Spec reference

Installation/Testing Fields

  • testingCommissioningRequirements - Testing/Commissioning requirements
  • testingCalibrationRequirements - Testing/Calibration requirements
  • specialToolsRequired - Special tools required
  • specialInstallationRequirements - Special installation requirements

Other Fields

  • crew - Assigned crew
  • comments - Comments
  • basisOfEstimateAssumptions - Basis of estimate assumptions

Calculated Columns by Discipline

Electrical Equipment (2 calculated columns)

  1. Quantity - Base quantity calculation
  2. Total Quantity incl. Wastage - =quantity * (1 + wastePercent)

Mechanical Equipment (2 calculated columns)

  1. Quantity - Base quantity calculation
  2. Total Quantity incl. Wastage - =quantity * (1 + wastePercent)

Bulk Electrical (2 calculated columns)

  1. Quantity - Base quantity calculation
  2. Total Quantity incl. Wastage - =quantity * (1 + wastePercent)

Instrumentation (3 calculated columns)

  1. Quantity - Base quantity calculation
  2. Total Quantity incl. Wastage - =quantity * (1 + wastePercent)
  3. (1 additional - needs verification)

Piping (11 calculated columns) ⚠️ MOST COMPLEX

Fabrication Calculations

  1. Number of Cuts (numberOfCuts) 

    =ROUNDUP(quantity / pipeOrFittingLengthPerUnit, 0)
    Calculates cuts needed based on total length divided by pipe length

  2. Number of Bevels (numberOfBevels) 

    =numberOfCuts * 2  // Each cut needs 2 bevels
// OR
=numberOfCuts      // Or references cuts directly

  3. Weld Quantity (weldQuantity) 

    =numberOfCuts
// OR
=flangeQuantity  // References flange quantity

  4. Heat Tracing (heatTracing) 

    =quantity  // References total length

  5. Insulated Length/Area (insulatedLengthArea) 

    =quantity  // References total length

Quantity Calculation

  1. Quantity (quantity) - Multi-row formula based on item type: 

    // Row for Pipe:
=pipeLength

// Row for Fitting:
=fittingQuantity

// Row for Flange:
=flangeQuantity

// Row for Bolt:
=boltQuantity

// Row for Valve:
=valveQuantity

// Row for Support:
=supportQuantity

  2. Total Quantity incl. Wastage (totalQuantityInclWastage) 

    =quantity * (1 + wastePercent)

Cost Calculations

  1. Manhour per Unit (manhourPerUnit) - 35+ dependencies: 

    =(
  handlingErection * quantity +
  manhourPerCut * numberOfCuts +
  manhourPerBevel * numberOfBevels +
  preheatingEa * weldQuantity +
  manhoursPerWeld * weldQuantity +
  manhoursPerThread * threadQuantity +
  manhoursPerBolt * boltQuantity +
  manhourPerPressFit * pressFitQuantity +
  manhoursPerMechanicalJoint * mechanicalJointQuantity +
  stressRelievingEa * weldQuantity +
  manhoursPerValve * valveQuantity +
  manhoursPerFitting * fittingQuantity +
  manhoursPerSpecialtyItem * specialtyItemQuantity +
  manhoursPerPipeSupport * supportQuantity +
  inspectionTestingNdtRt * weldQuantity +
  manhoursPerUnitCoating * coatedLengthArea +
  manhoursPerUnitInsulation * insulatedLengthArea +
  manhoursPerUnitSandblastPaint * coatedLengthArea +
  manhoursPerUnitHeatTracing * heatTracing +
  inspectionTestingPipesHydrostatic * quantity
) / quantity

  2. Total Material Unit Price (totalMaterialUnitPrice) - 13 components: 

    =(
  pipeUnitPrice * totalQuantityInclWastage +
  fittingUnitPrice * fittingQuantity +
  flangeUnitPrice * flangeQuantity +
  boltUnitPrice * boltQuantity +
  valveUnitPrice * valveQuantity +
  specialtyItemUnitPrice * specialtyItemQuantity +
  coatingUnitPrice * coatedLengthArea +
  paintingUnitPrice * coatedLengthArea +
  insulationUnitPrice * insulatedLengthArea +
  pipeSupportUnitPrice * supportQuantity +
  heatTracingUnitPrice * heatTracing +
  materialTransportation * totalQuantityInclWastage +
  materialFreight * totalQuantityInclWastage
) / quantity

  3. Total Tag Equipment Unit Price (totalEquipmentUnitPrice) 

    =(
  tagEquipmentUnitPrice * quantity +
  tagEquipmentFreight +
  tagEquipmentSpareParts +
  tagEquipmentVendorsRep
) / quantity

Civil (8 calculated columns)

  1. Volume (volume) 

    =length * width * height

  2. Surface Area (area) 

    =length * width

  3. Quantity with Swell/Shrink (quantityWSwellShrink) 

    =volume * (1 + swellFactor - shrinkageFactor)

  4. Quantity (quantity) - Based on calculation type

  5. Total Quantity incl. Wastage (totalQuantityInclWastage) 

    =quantity * (1 + wastePercent)

  6. Manhour per Unit (manhourPerUnit) - Multi-component formula

  7. Total Material Unit Price (totalMaterialUnitPrice) 

    =(materialCosts + freight + transport) / quantity

  8. Total Tag Equipment Unit Price (totalEquipmentUnitPrice) 

    =(equipmentPrice * quantity + freight + spare + vendors) / quantity

Concrete (9 calculated columns)

  1. Volume (m³) (volumeM3) 

    =length * width * height

  2. Area (m²) (areaM2) 

    =length * width

  3. Formwork Area (formworkArea) - Based on geometry

  4. Total Weight (totalWeight) 

    =weightPerUnitLength * length * numberEa

  5. Quantity (quantity) - Can be volume, area, or weight based

  6. Total Quantity incl. Wastage (totalQuantityInclWastage) 

    =quantity * (1 + wastePercent)

  7. Manhour per Unit (manhourPerUnit) 

    =(manhoursExcavation + manhoursConcreteWork + manhoursSteelWork + manhoursFormwork) / quantity

  8. Total Material Unit Price (totalMaterialUnitPrice) 

    =(
  concreteUnitPrice * volumeM3 +
  rebarUnitPrice * totalWeight +
  formworkUnitPrice * formworkArea +
  admixturesUnitPrice * volumeM3
) / quantity

  9. Total Tag Equipment Unit Price (totalEquipmentUnitPrice)

Structural (7 calculated columns)

  1. Total Weight (totalWeight) 

    =weightPerUnitLength * lengthM * numberEa

  2. Surface Area (surfaceArea) 

    =6 * totalWeight  // For coating/treatment

  3. Quantity (quantity) 

    =totalWeight  // Quantity equals total weight

  4. Total Quantity incl. Wastage (totalQuantityInclWastage) 

    =quantity * (1 + wastePercent)

  5. Manhour per Unit (manhourPerUnit) 

    =(
  manhoursFabrication +
  manhoursHandling +
  manhoursInstallation +
  manhoursCoating +
  manhoursSandblastPaint +
  manhoursFireproofing
) / quantity

  6. Total Material Unit Price (totalMaterialUnitPrice) 

    =(
  unitPriceSteel * totalQuantityInclWastage +
  unitPriceCoating * surfaceArea +
  unitPriceSandblastPaint * surfaceArea +
  unitPriceFireproofing * surfaceArea
) / quantity

  7. Total Tag Equipment Unit Price (totalEquipmentUnitPrice) 

    =(
  tagEquipmentUnitPrice * quantity +
  tagEquipmentFreight +
  tagEquipmentSpareParts +
  tagEquipmentVendorsRep
) / quantity

Architectural (6 calculated columns)

  1. Area (m²) (areaM2) 

    =length * width

  2. Volume (m³) (volumeM3) 

    =height * width * length

  3. Quantity (quantity) - Conditional on UoM: 

    =IF(uom="m3",
  volumeM3 * numberEa,
  IF(uom="m2",
    areaM2 * numberEa,
    IF(uom="m",
      length * numberEa,
      numberEa
    )
  )
)
    This intelligently calculates quantity based on the unit of measure!

  4. Total Quantity incl. Wastage (totalQuantityInclWastage) 

    =quantity * (1 + wastePercent)

  5. Total Material Unit Price (totalMaterialUnitPrice) 

    =(
  materialUnitPrice1 * totalQuantityInclWastage +
  materialUnitPrice2 * totalQuantityInclWastage +
  materialUnitPrice3 * totalQuantityInclWastage
) / quantity

  6. Total Tag Equipment Unit Price (totalEquipmentUnitPrice) 

    =(
  tagEquipmentUnitPrice * quantity +
  tagEquipmentFreight +
  tagEquipmentSpareParts +
  tagEquipmentVendorsRep
) / quantity

Calculation Formulas

Standard Formula Patterns

All disciplines use these consistent patterns:

1. Wastage Calculation

totalQuantityInclWastage = quantity * (1 + wastePercent)

Example: - quantity = 100 m - wastePercent = 0.05 (5%) - totalQuantityInclWastage = 100 * (1 + 0.05) = 105 m

2. Material Unit Price

totalMaterialUnitPrice = (
  sum of (component_price * component_quantity) +
  freight_costs +
  transportation_costs
) / base_quantity

Pattern: Sum all material costs, then divide by quantity to get unit price

3. Equipment Unit Price

totalEquipmentUnitPrice = (
  equipment_price * quantity +
  freight +
  spare_parts +
  vendors_rep
) / base_quantity

Pattern: Equipment cost + one-time fees, divided by quantity

4. Manhour Calculation

manhourPerUnit = (
  sum of (manhour_rate * operation_quantity)
) / base_quantity

Pattern: Sum all labor operations, divide by quantity

5. Labor Cost Calculations (NEW)

These fields support the estimation workflow with crew-based labor costing:

// Input fields (set from discipline settings and crew assignment)
laborProdFactor: number;  // Productivity factor (default: 1.0)
hourlyCost: number;       // From crew blended rate

// Calculated fields
totalManhours = quantity * manhourPerUnit * laborProdFactor;
laborUnitCost = manhourPerUnit * laborProdFactor * hourlyCost;
totalLaborCost = quantity * laborUnitCost;

Field Descriptions:

Field Source Formula
laborProdFactor Discipline settings Manual input or inherited from project
hourlyCost ServiceCrew blended rate Retrieved from assigned crew
totalManhours Calculated quantity * manhourPerUnit * laborProdFactor
laborUnitCost Calculated manhourPerUnit * laborProdFactor * hourlyCost
totalLaborCost Calculated quantity * laborUnitCost

6. Total Estimation Unit Price (NEW)

The final calculated price for estimation purposes:

// If subcontractor is assigned with rate > 0, use their all-in rate
if (subcontractorCostAllInRate !== null && subcontractorCostAllInRate > 0) {
  totalEstimationUnitPrice = subcontractorCostAllInRate;
} else {
  // Otherwise, calculate from components
  totalEstimationUnitPrice = laborUnitCost + totalMaterialUnitPrice + totalEquipmentUnitPrice;
}

Logic: 1. Subcontractor Override: If subcontractorCostAllInRate > 0, it represents a complete price from the subcontractor and overrides all other calculations. 2. Component Calculation: Otherwise, sum the three cost components: - laborUnitCost - Labor cost per unit - totalMaterialUnitPrice - Material cost per unit - totalEquipmentUnitPrice - Equipment cost per unit

Frontend Implementation

Implementation Strategy

Phase 1: Calculation Engine (Utility Functions)

Create calculation utilities in /frontend/src/utils/mtoCalculations.ts:

// Calculation engine for MTO formulas
export const mtoCalculations = {
  // Common calculations (all disciplines)
  calculateWastage(quantity: number, wastePercent: number): number {
    return quantity * (1 + wastePercent);
  },

  // Geometric calculations
  calculateArea(length: number, width: number): number {
    return length * width;
  },

  calculateVolume(length: number, width: number, height: number): number {
    return length * width * height;
  },

  // Piping-specific calculations
  piping: {
    calculateNumberOfCuts(totalLength: number, lengthPerUnit: number): number {
      return Math.ceil(totalLength / lengthPerUnit);
    },

    calculateNumberOfBevels(numberOfCuts: number, bevelFactor: number = 2): number {
      return numberOfCuts * bevelFactor;
    },

    calculateManhourPerUnit(params: PipingManhourParams): number {
      const {
        handlingErection,
        quantity,
        manhourPerCut,
        numberOfCuts,
        // ... all 20+ parameters
      } = params;

      return (
        handlingErection * quantity +
        manhourPerCut * numberOfCuts +
        // ... rest of formula
      ) / quantity;
    },

    calculateTotalMaterialUnitPrice(params: PipingMaterialPriceParams): number {
      // Complex formula implementation
    },
  },

  // Structural-specific calculations
  structural: {
    calculateTotalWeight(weightPerLength: number, length: number, number: number): number {
      return weightPerLength * length * number;
    },

    calculateSurfaceArea(totalWeight: number, factor: number = 6): number {
      return factor * totalWeight;
    },
  },

  // Architectural-specific calculations
  architectural: {
    calculateQuantityByUoM(
      uom: string,
      volume: number,
      area: number,
      length: number,
      numberEa: number
    ): number {
      switch (uom) {
        case 'm3':
        case 'M3':
          return volume * numberEa;
        case 'm2':
        case 'M2':
          return area * numberEa;
        case 'm':
        case 'M':
          return length * numberEa;
        default:
          return numberEa;
      }
    },
  },

  // Add other disciplines as needed...
};

Phase 2: React Hooks for Calculations

Create hooks in /frontend/src/hooks/useMTOCalculations.ts:

export function useMTOCalculations(discipline: DisciplineType) {
  const calculateFields = useCallback((item: MaterialTakeoff) => {
    const calculated = { ...item };

    // Common calculations for all disciplines
    if (item.quantity && item.wastePercent) {
      calculated.totalQuantityInclWastage = mtoCalculations.calculateWastage(
        item.quantity,
        item.wastePercent
      );
    }

    // Discipline-specific calculations
    switch (discipline) {
      case DisciplineType.PIPING:
        return calculatePipingFields(calculated as PipingMTO);
      case DisciplineType.STRUCTURAL:
        return calculateStructuralFields(calculated as StructuralMTO);
      case DisciplineType.ARCHITECTURAL:
        return calculateArchitecturalFields(calculated as ArchitecturalMTO);
      // ... other disciplines
      default:
        return calculated;
    }
  }, [discipline]);

  return { calculateFields };
}

Phase 3: Real-time Calculation in Tables

Integrate into DataTable component:

// In DisciplineSheetPage.tsx
const { calculateFields } = useMTOCalculations(disciplineApiEnum);

// When user edits a cell
const handleCellEdit = (rowId: string, field: string, value: any) => {
  setMtoItems(prev =>
    prev.map(item => {
      if (item.id === rowId) {
        const updated = { ...item, [field]: value };
        // Recalculate all dependent fields
        return calculateFields(updated);
      }
      return item;
    })
  );
};

Phase 4: Save to Database

Only save when user clicks "Save":

const handleSave = async () => {
  setIsSaving(true);
  try {
    // Save all items with calculated fields
    await Promise.all(
      mtoItems.map(item =>
        mtoApi.updateMTO(projectId!, item.id, item)
      )
    );
    toast({ title: 'Saved successfully' });
  } catch (error) {
    toast({ title: 'Save failed', variant: 'destructive' });
  } finally {
    setIsSaving(false);
  }
};

Benefits of Frontend Calculations

  1. Instant Feedback - User sees results immediately when changing inputs
  2. No Backend Load - Calculations happen in browser
  3. Offline Capable - Works without network connection
  4. Better UX - No loading spinners for calculations
  5. Easy to Test - Pure functions are unit testable
  6. Flexible - Easy to add/modify formulas

Calculation Dependencies

Track which fields trigger recalculation:

const CALCULATION_DEPENDENCIES: Record<string, string[]> = {
  // When these fields change, recalculate these fields
  quantity: ['totalQuantityInclWastage', 'totalMaterialUnitPrice', 'manhourPerUnit'],
  wastePercent: ['totalQuantityInclWastage'],
  numberOfCuts: ['numberOfBevels', 'weldQuantity', 'manhourPerUnit'],
  // ... etc
};

Schema Consistency

Backend → Frontend Mapping

All schemas are consistent between backend Python and frontend TypeScript:

Backend (Python) Frontend (TypeScript) Database (Cosmos DB)
DisciplineType.PIPING DisciplineType.PIPING "piping"
quantity: float \| None quantity?: number "quantity": 100.5
tag_no: str \| None = Field(alias="tagNo") tagNo?: string "tagNo": "P-001"
total_quantity_incl_wastage totalQuantityInclWastage "totalQuantityInclWastage": 105

Key Points: - Backend uses snake_case with Pydantic alias for camelCase JSON - Frontend uses camelCase directly - Database stores in camelCase - All three are kept in sync via type definitions

Adding New Calculated Columns

When adding a new calculated column:

  1. Update Excel schema (/schemas/<Discipline>_schema.json)
  2. Add to calculated_columns array

  3. Include formula, dependencies, example cells

  4. Update backend schema (/backend/app/schemas/mto.py)

  5. Add field to discipline-specific class
  6. Mark as optional with default None

  7. Add Pydantic Field with alias

  8. Update frontend types (/frontend/src/types/mto.ts)

  9. Add field to discipline-specific interface
  10. Use camelCase naming

  11. Mark as optional

  12. Add calculation logic (/frontend/src/utils/mtoCalculations.ts)

  13. Implement formula in calculation engine

  14. Add to dependency tracking

  15. Update documentation (this file)

  16. Add to calculated columns list
  17. Document formula and purpose

References

Source Files

  • Excel Schemas: /schemas/*_schema.json
  • Schema Summary: /schemas/schemas_summary.json
  • Backend Repository: /backend/app/repositories/mto.py
  • Backend Schemas: /backend/app/schemas/mto.py
  • Frontend Types: /frontend/src/types/mto.ts
  • Frontend Pages:
  • /frontend/src/pages/DisciplineSheetPage.tsx
  • /frontend/src/pages/EstimationServicePage.tsx
  • Schema Verification Report: /tmp/schema_verification_report.md
  • API Reference: /docs/API_REFERENCE.md
  • Architecture: /docs/ARCHITECTURE.md

End of Document