Cosmos DB Containers Overview¶

Database: lco-construction Last Updated: 2026-01-19

Container Inventory¶

Container	Partition Key	Document Type	Purpose
`Projects`	`/clientId`	`project`	Project management
`Clients`	`/clientId`	`client`	Client management
`Crews`	`/crewId`	`crew`	Crew templates
`CrewTrades`	`/tradeCode`	`crew_trade`	Trade codes (labor classifications)
`CrewMembers`	`/locationKey`	`crew_member`	Labor rates by location
`Services`	`/projectId`	`service`	Project services
`Subcontractors`	`/projectId`	`subcontractor`	Subcontractor rates
`Equipment`	`/equipmentCode`	`equipment`	Equipment master
`equipment-tags`	`/projectId`	`equipment_tag`	Project equipment pricing
`Material`	`/materialCode`	`material`	Material master
`ProjectMaterialCosts`	`/projectId`	`project_material_cost`	Material cost overrides
`ServiceCrews`	`/serviceCrewId`	`service_crew`	Service crew assignments
`MaterialTakeoff`	`/projectId`	`material_takeoff`	MTO items
`WBS`	`/wbsCode`	`wbs`	Work breakdown structure
`COA`	`/scope`	`coa-item`	Chart of Accounts
`Tasks`	`/projectId`	`task`	Async task tracking
`Estimations`	`/serviceId`	`estimation`	Estimation documents
`coa-kpis`	`/group`	`coa_kpi`	COA KPI definitions
`ScheduleActivities`	`/projectId`	`schedule_activity`	P6 schedule activities
`ApiKeys`	`/keyPrefix`	`api_key`	API key storage
`Packages`	`/projectId`	`package`	Work packages
`SystemsSubsystems`	`/projectId`	`system_subsystem`	System hierarchy
`FileUploads`	`/projectId`	`file_upload`	Centralized upload tracking
`DropdownOptions`	`/dropdownType`	`dropdown_option`	Global dropdown values

Detailed Container Schemas¶

Projects¶

Stores project metadata and configuration.

Field	Type	Description
`id`	string	Document ID (same as projectCode)
`projectId`	string	Unique project identifier
`projectCode`	string	Human-readable project code
`clientId`	string	Partition key - Reference to client
`projectName`	string	Project display name
`location`	object	Location info (country, province, region, city)
`projectType`	enum	commercial, industrial, residential, infrastructure, energy, mixed
`contractType`	enum	lumpSum, costPlus, unitPrice, guaranteed, timeAndMaterials
`status`	enum	planning, active, onHold, completed, cancelled
`year`	int	Project year
`quarter`	enum	Q1, Q2, Q3, Q4
`workingDays`	int	Working days per week (1-7)
`workingHours`	int	Working hours per day (1-24)
`blendedRates`	object	Project-level blended rate configuration
`wbsIds`	string[]	Associated WBS IDs
`servicesSummary`	object	Summary of services by type

Clients¶

Stores client information.

Field	Type	Description
`id`	string	Document ID
`clientId`	string	Partition key
`clientCode`	string	Unique client code
`clientName`	string	Client display name
`clientType`	enum	government, private, ppp, international
`industry`	enum	oil-gas, infrastructure, commercial, industrial, residential, mixed
`contactInfo`	object	Contact details (name, email, phone, address)
`projectCount`	int	Total project count
`activeProjectCount`	int	Active project count

Services¶

Stores project services (estimation, loan monitoring, etc.).

Field	Type	Description
`id`	string	Document ID
`serviceId`	string	Unique service identifier
`projectId`	string	Partition key
`clientId`	string	Reference to client
`serviceName`	string	Service display name
`serviceType`	enum	estimation, loanMonitoring, projectControls, claims
`owner`	string	Service owner name
`status`	enum	draft, in-progress, completed, approved
`dateCreated`	string	Creation date (YYYY-MM-DD)

Subcontractors¶

Stores project-scoped subcontractor rates. See SUBCONTRACTOR_SCHEMA.md for details.

Field	Type	Description
`id`	string	Document ID
`subcontractorId`	string	Unique subcontractor identifier
`projectId`	string	Partition key
`subcontractorCode`	string	Unique code within project (e.g., SUB-001)
`subcontractorName`	string	Display name
`allInRate`	float	All-in hourly/unit rate
`description`	string	Optional description
`contactPerson`	string	Contact person name
`contactEmail`	string	Contact email
`contactPhone`	string	Contact phone

Crews¶

Stores crew templates (universal configurations).

Field	Type	Description
`id`	string	Document ID
`crewId`	string	Partition key
`crewCode`	string	Unique crew code
`crewName`	string	Crew display name
`discipline`	enum	Electrical, Mechanical, Civil, Structural, Piping, Instrumentation, General, Mixed
`composition`	object	Manpower and equipment composition
`productivityFactor`	float	Productivity adjustment factor
`isTemplate`	bool	True for template crews
`isActive`	bool	Active status
`totalCrewSize`	int	Calculated total crew size

CrewTrades¶

Stores trade codes (labor classifications).

Field	Type	Description
`id`	string	Document ID
`tradeId`	string	Unique trade identifier
`tradeCode`	string	Partition key - Unique trade code (e.g., ELEC, PLMB)
`laborDesignation`	string	Labor designation/classification
`description`	string	Trade description
`category`	string	Trade category

CrewMembers¶

Stores labor rates by location and time context.

Field	Type	Description
`id`	string	Document ID
`memberId`	string	Unique member identifier
`locationKey`	string	Partition key - Composite location key
`tradeCode`	string	Reference to crew trade
`laborDesignation`	string	Labor designation
`location`	object	Location info (country, province, region)
`timeContext`	object	Year and quarter
`rates`	object	Base rate, overtime, double time, triple time
`skillLevel`	enum	Entry, Intermediate, Advanced, Expert, Foreman, Supervisor
`validFrom`	date	Rate validity start
`validTo`	date	Rate validity end
`isActive`	bool	Active status

Equipment¶

Stores equipment master data.

Field	Type	Description
`id`	string	Document ID
`equipmentId`	string	Unique equipment identifier
`equipmentCode`	string	Partition key - Unique equipment code
`equipmentName`	string	Equipment name/model
`description`	string	Equipment description
`category`	string	Equipment category
`baseUnitPrice`	float	Base unit price
`currency`	string	Currency code (default: USD)

equipment-tags¶

Stores project-specific equipment pricing.

Field	Type	Description
`id`	string	Document ID
`equipmentTagId`	string	Unique tag identifier
`projectId`	string	Partition key
`tagNumber`	string	Tag identifier (e.g., TAG-001)
`description`	string	Equipment description
`tagEquipmentUnitPrice`	float	Base unit price
`tagEquipmentFreight`	float	Freight cost
`tagEquipmentSpareParts`	float	Spare parts cost
`tagEquipmentVendorsRep`	string	Vendor rep name
`totalTagEquipmentUnitPrice`	float	Computed total

Material¶

Stores material master data with location/time pricing.

Field	Type	Description
`id`	string	Document ID
`materialId`	string	Unique material identifier
`materialCode`	string	Partition key - Unique material code
`materialName`	string	Material name
`materialDescription`	string	Material description
`materialType`	string	Material type/category
`unitPrice`	string	Unit price
`unitOfMeasure`	string	Unit of measure
`year`	string	Pricing year
`quarter`	string	Pricing quarter
`country`	string	Country
`province`	string	Province
`region`	string	Region

ProjectMaterialCosts¶

Stores project-specific material cost overrides.

Field	Type	Description
`id`	string	Document ID
`projectId`	string	Partition key
`itemNo`	string	Reference to MTO item
`materialId`	string	Reference to material
(various)	mixed	Material cost fields by discipline

ServiceCrews¶

Stores service crew assignments with indirect costs.

Field	Type	Description
`id`	string	Document ID
`serviceCrewId`	string	Partition key
`serviceId`	string	Reference to service
`projectId`	string	Reference to project
`crewId`	string	Reference to crew
`crewName`	string	Crew display name
`crewCode`	string	Crew code
`numberOfMembers`	int	Total crew size
`equipmentQuantity`	int	Equipment count
`labourMembers`	array	Labor composition details
`equipmentMembers`	array	Equipment composition details
`labourRate`	float	Base labour rate
`equipmentRate`	float	Equipment rate
`blendedRate`	float	Blended rate with indirect costs
`indirectCosts`	object	Labour and equipment indirect cost percentages
`isCustomCrew`	bool	True if modified from template

MaterialTakeoff¶

Stores MTO items across 9 disciplines. See MATERIAL_TAKEOFF_SCHEMAS.md for complete schema.

Field	Type	Description
`id`	string	Document ID
`mtoId`	string	Internal MTO identifier
`projectId`	string	Partition key
`serviceId`	string	Reference to service
`discipline`	enum	Discipline type discriminator
`itemNo`	string	Item number (unique within project)
`quantity`	float	Base quantity
`uom`	string	Unit of measure
(many more)	mixed	See MTO schema documentation

WBS¶

Stores work breakdown structures (universal and project-specific).

Field	Type	Description
`id`	string	Document ID
`wbsId`	string	Unique WBS identifier
`wbsCode`	string	Partition key - Unique WBS code
`wbsName`	string	WBS name/title
`description`	string	WBS description
`scope`	enum	`universal` or `project`
`projectId`	string	Project ID (required if scope=project)
`serviceId`	string	Optional service reference
`templateId`	string	Reference to universal template
`items`	array	Hierarchical WBS items

WBS Item structure: - id - Unique item identifier - level - Hierarchy level (1 = root) - code - Short alphanumeric code - text - Descriptive label - parentId - Parent item reference

COA¶

Stores Chart of Accounts items. See CONTAINERS_OVERVIEW.md for details.

Field	Type	Description
`id`	string	Document ID
`scope`	enum	Partition key - `universal` or `project`
`level`	int	Hierarchy level (1 = root)
`code`	string	COA code (e.g., 100, 110)
`kpis`	string	Description/KPI text
`parentId`	string	Parent item reference
`projectId`	string	Project ID (required if scope=project)
`enforceKpi`	bool	When true, descendants use this node's KPI for MTO

Tasks¶

Stores async task tracking for long-running operations.

Field	Type	Description
`id`	string	Document ID
`taskId`	string	Unique task identifier
`projectId`	string	Partition key
`taskType`	string	Type of task (e.g., import, export, calculation)
`status`	enum	pending, running, completed, failed
`createdAt`	datetime	Creation timestamp
`startedAt`	datetime	Start timestamp
`completedAt`	datetime	Completion timestamp
`progress`	object	Current progress (current, total, percentage, message)
`result`	any	Task result (if completed)
`error`	string	Error message (if failed)
`metadata`	object	Additional task metadata
`retryCount`	int	Number of retry attempts

ApiKeys¶

Stores API key credentials for programmatic access.

Field	Type	Description
`id`	string	Document ID
`keyPrefix`	string	Partition key - First 8 chars of key
`keyHash`	string	bcrypt hash of full key
`name`	string	Key name/description
`role`	enum	Reader, Contributor
`expiresAt`	datetime	Optional expiration
`isRevoked`	bool	Revocation status

FileUploads¶

Centralized tracking of all file uploads across the system.

Field	Type	Description
`id`	string	Document ID
`projectId`	string	Partition key
`uploadType`	enum	24 types (MTO, estimation, schedule, config, etc.)
`uploadCategory`	enum	async_import, async_delete, config_save, sync_import
`status`	enum	pending, uploaded, processing, completed, failed, cancelled
`filename`	string	Original filename
`blobPath`	string	Azure Blob Storage path
`taskId`	string	Associated task ID

ScheduleActivities¶

Stores P6 schedule activities for projects.

Field	Type	Description
`id`	string	Document ID
`activityId`	string	P6 activity identifier
`projectId`	string	Partition key
`activityName`	string	Activity name
`startDate`	date	Planned start
`endDate`	date	Planned finish
`duration`	float	Duration in days
`wbsCode`	string	Associated WBS code

DropdownOptions¶

Global dropdown values for form fields.

Field	Type	Description
`id`	string	Document ID
`dropdownType`	string	Partition key - Type (PROJECT_TYPE, CONTRACT_TYPE, etc.)
`value`	string	Option value
`label`	string	Display label
`sortOrder`	int	Display order
`isActive`	bool	Active status

Indexing Strategy¶

Default Indexes¶

All containers use Cosmos DB's automatic indexing on all paths by default.

Custom Indexes (Recommended)¶

For optimal query performance:

MaterialTakeoff: Composite index on (projectId, discipline, serviceId)
Services: Composite index on (projectId, serviceType)
COA: Composite index on (scope, projectId, level)
Tasks: Composite index on (projectId, status, createdAt DESC)

Data Lifecycle¶

Soft Delete Pattern¶

Documents are typically not hard-deleted. Use isActive or status fields for logical deletion.

TTL (Time-to-Live)¶

Consider enabling TTL on: - Tasks container for automatic cleanup of old task records - Temporary data containers

Cross-Container Relationships¶

Documents maintain referential integrity through ID fields:

Client.id ──> Project.clientId
Project.projectId ──> Service.projectId
Project.projectId ──> Subcontractor.projectId
Project.projectId ──> MaterialTakeoff.projectId
Project.projectId ──> EquipmentTag.projectId
Service.serviceId ──> ServiceCrew.serviceId
Service.serviceId ──> MaterialTakeoff.serviceId
CrewTrade.tradeCode ──> CrewMember.tradeCode
Crew.crewId ──> ServiceCrew.crewId

Note: Cosmos DB does not enforce foreign key constraints. Application code must maintain referential integrity.