Database Documentation Index¶
Database: Azure Cosmos DB (NoSQL)
Database Name: lco-construction
Last Updated: 2025-11-27
Overview¶
LCO DMA uses Azure Cosmos DB as its primary data store. The database follows a document-oriented model with containers partitioned for optimal query performance and scalability.
Quick Links¶
| Document | Description |
|---|---|
| Containers Overview | Complete container inventory with partition keys and document types |
| Material Takeoff Schemas | MTO discipline schemas, calculated fields, and formulas |
| COA Schema | Chart of Accounts structure and hierarchy management |
| Subcontractor Schema | Project-scoped subcontractor rates |
Container Summary¶
| Container | Partition Key | Primary Use |
|---|---|---|
Projects |
/clientId |
Project management |
Clients |
/clientId |
Client management |
Crews |
/crewId |
Crew templates |
CrewTrades |
/tradeCode |
Trade codes (labor classifications) |
CrewMembers |
/locationKey |
Labor rates by location |
Services |
/projectId |
Project services (estimation, etc.) |
Subcontractors |
/projectId |
Subcontractor rates per project |
Equipment |
/equipmentCode |
Equipment master data |
equipment-tags |
/projectId |
Project-specific equipment pricing |
Material |
/materialCode |
Material master data |
ProjectMaterialCosts |
/projectId |
Material cost overrides per project |
ServiceCrews |
/serviceCrewId |
Service crew assignments with indirect costs |
MaterialTakeoff |
/projectId |
MTO items across 9 disciplines |
WBS |
/wbsCode |
Work breakdown structures |
COA |
/scope |
Chart of Accounts items |
Tasks |
/projectId |
Async task tracking |
Data Relationships¶
Entity Relationship Diagram¶
┌─────────────────────────────────────────────────────────────────────────────┐
│ CLIENT HIERARCHY │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────────┐ ┌───────────────┐ │
│ │ Client │ 1 ────> │ Project │ 1 ────> │ Service │ │
│ │ │ N │ │ N │ (estimation) │ │
│ └──────────┘ └──────┬───────┘ └───────┬───────┘ │
│ │ │ │
│ │ │ │
│ ┌───────────────┼───────────────┐ │ │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌────────────┐ ┌────────────┐ ┌──────────┐ ┌─────────────┐ │
│ │Subcontractor│ │EquipmentTag│ │ Tasks │ │ServiceCrew │ │
│ │ (rates) │ │ (pricing) │ │ (async) │ │(assignments)│ │
│ └────────────┘ └────────────┘ └──────────┘ └──────┬──────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────┐ │
│ │ MaterialTakeoff │ │
│ │ (MTO - 9 discs) │ │
│ └──────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────────┐
│ REFERENCE TABLES │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ CrewTrades │ ────> │ CrewMembers │ │ Crews │ │
│ │ (trade codes)│ │ (labor rates)│ │ (templates) │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ Equipment │ │ Material │ │ProjectMaterial│ │
│ │ (master) │ │ (master) │ │ Costs │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ WBS │ │ COA │ (Both support universal + │
│ │ (breakdown) │ │ (accounts) │ project-specific scopes) │
│ └──────────────┘ └──────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Relationship Summary¶
| From | To | Relationship | Key Field |
|---|---|---|---|
| Client | Project | 1:N | clientId |
| Project | Service | 1:N | projectId |
| Project | Subcontractor | 1:N | projectId |
| Project | EquipmentTag | 1:N | projectId |
| Project | ProjectMaterialCost | 1:N | projectId |
| Project | Task | 1:N | projectId |
| Service | ServiceCrew | 1:N | serviceId |
| Service | MaterialTakeoff | 1:N | serviceId |
| CrewTrade | CrewMember | 1:N | tradeCode |
| Crew | ServiceCrew | 1:N | crewId |
MTO Assignment References¶
MTO items can reference:
- WBS: wbsPath, wbsText for work breakdown assignment
- Crew: crewCode, crewName for labor assignment
- Subcontractor: subcontractorCode, subcontractorName for subcontracted work
- COA: coaCode, coaName for cost categorization
- KPI: kpiCode, kpiName for performance tracking
Key Design Decisions¶
Partition Strategy¶
- Project-centric data (
/projectId): Services, MTO, Subcontractors, EquipmentTags, MaterialCosts, Tasks - Client-centric data (
/clientId): Projects, Clients - Self-partitioned masters: Crews, Equipment, Material use their own codes as partition keys
Document Type Discriminator¶
All documents include a type field for filtering within containers:
- "project", "client", "service", "material_takeoff", "subcontractor", etc.
Scope Pattern¶
WBS and COA support dual scopes:
- "universal" - Shared templates available to all projects
- "project" - Project-specific instances (requires projectId)
Schema Sources¶
| Location | Purpose |
|---|---|
/backend/app/schemas/*.py |
Python Pydantic models (source of truth) |
/frontend/src/types/*.ts |
TypeScript interfaces (must sync with backend) |
/schemas/*.json |
Excel discipline sheet schemas |
Related Documentation¶
- API Reference - REST API endpoints
- Backend Architecture - Repository patterns
- Frontend Types - TypeScript type definitions