> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Facilities Management Entity Relationship Diagram

> Entity relationship diagram for the Facilities Management module

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-11\
> **Module:** Facilities Management\
> **Prefix:** `fm_`\
> **Tables:** 22

## Changelog

* **1.0.0** (2026-01-11): Initial ERD documentation

## Overview

The Facilities Management module handles asset tracking, preventive maintenance scheduling, work order management, and inventory control for physical facilities.

## Assets ERD

```mermaid theme={null}
erDiagram
    fm_assets ||--o{ fm_asset_maintenance_history : "has maintenance"
    fm_assets ||--o{ fm_work_orders : "work on"
    fm_assets }o--|| fm_asset_categories : categorized
    fm_assets }o--|| pf_sites : "located at"
    
    fm_assets {
        uuid id PK
        uuid organization_id FK
        uuid site_id FK
        uuid category_id FK
        string asset_tag
        string name
        string description
        string serial_number
        string manufacturer
        string model
        date purchase_date
        decimal purchase_cost
        decimal current_value
        enum status
        date warranty_expiration
        jsonb specifications
        jsonb custom_fields
    }
    
    fm_asset_categories {
        uuid id PK
        uuid organization_id FK
        string name
        string code
        uuid parent_id FK
        integer useful_life_years
        enum depreciation_method
        uuid default_gl_account_id FK
    }
    
    fm_asset_maintenance_history {
        uuid id PK
        uuid asset_id FK
        uuid work_order_id FK
        date maintenance_date
        enum maintenance_type
        text description
        decimal cost
        uuid performed_by FK
        text notes
    }
```

## Work Orders ERD

```mermaid theme={null}
erDiagram
    fm_work_orders ||--o{ fm_work_order_tasks : contains
    fm_work_orders ||--o{ fm_work_order_parts : uses
    fm_work_orders ||--o{ fm_work_order_history : logs
    fm_work_orders }o--|| fm_assets : "for asset"
    fm_work_orders }o--|| pf_sites : "at site"
    
    fm_work_orders {
        uuid id PK
        uuid organization_id FK
        uuid site_id FK
        uuid asset_id FK
        string work_order_number
        string title
        text description
        enum priority
        enum status
        enum work_type
        uuid requested_by FK
        uuid assigned_to FK
        timestamp due_date
        timestamp completed_at
        decimal estimated_hours
        decimal actual_hours
        decimal total_cost
        jsonb custom_fields
    }
    
    fm_work_order_tasks {
        uuid id PK
        uuid work_order_id FK
        string task_name
        text description
        integer task_order
        boolean is_completed
        uuid completed_by FK
        timestamp completed_at
    }
    
    fm_work_order_parts {
        uuid id PK
        uuid work_order_id FK
        uuid inventory_item_id FK
        decimal quantity_used
        decimal unit_cost
        decimal total_cost
    }
    
    fm_work_order_history {
        uuid id PK
        uuid work_order_id FK
        enum event_type
        string old_value
        string new_value
        uuid changed_by FK
        timestamp changed_at
        text notes
    }
```

## Preventive Maintenance ERD

```mermaid theme={null}
erDiagram
    fm_pm_schedules ||--o{ fm_pm_tasks : defines
    fm_pm_schedules ||--o{ fm_work_orders : generates
    fm_pm_schedules }o--|| fm_assets : "for asset"
    
    fm_pm_schedules {
        uuid id PK
        uuid organization_id FK
        uuid asset_id FK
        string name
        text description
        enum frequency_type
        integer frequency_value
        date last_performed
        date next_due
        boolean is_active
        integer lead_time_days
        jsonb checklist
    }
    
    fm_pm_tasks {
        uuid id PK
        uuid schedule_id FK
        string task_name
        text instructions
        integer task_order
        integer estimated_minutes
        jsonb required_parts
    }
```

## Inventory ERD

```mermaid theme={null}
erDiagram
    fm_inventory_items ||--o{ fm_inventory_transactions : tracks
    fm_inventory_items ||--o{ fm_work_order_parts : "used in"
    fm_inventory_items }o--|| fm_inventory_locations : "stored at"
    fm_inventory_items }o--|| fm_inventory_categories : categorized
    
    fm_inventory_items {
        uuid id PK
        uuid organization_id FK
        uuid category_id FK
        uuid location_id FK
        string sku
        string name
        text description
        string unit_of_measure
        decimal quantity_on_hand
        decimal reorder_point
        decimal reorder_quantity
        decimal unit_cost
        uuid preferred_vendor_id FK
        jsonb custom_fields
    }
    
    fm_inventory_categories {
        uuid id PK
        uuid organization_id FK
        string name
        string code
        uuid parent_id FK
    }
    
    fm_inventory_locations {
        uuid id PK
        uuid organization_id FK
        uuid site_id FK
        string name
        string description
        boolean is_active
    }
    
    fm_inventory_transactions {
        uuid id PK
        uuid inventory_item_id FK
        enum transaction_type
        decimal quantity
        decimal unit_cost
        string reference_number
        uuid work_order_id FK
        uuid created_by FK
        timestamp created_at
        text notes
    }
```

## Vendors ERD

```mermaid theme={null}
erDiagram
    fm_vendors ||--o{ fm_work_orders : "assigned to"
    fm_vendors ||--o{ fm_inventory_items : supplies
    fm_vendors ||--o{ fm_vendor_contracts : "has contracts"
    
    fm_vendors {
        uuid id PK
        uuid organization_id FK
        string vendor_number
        string name
        string contact_name
        string phone
        string email
        text address
        enum vendor_type
        boolean is_active
        jsonb certifications
        jsonb custom_fields
    }
    
    fm_vendor_contracts {
        uuid id PK
        uuid vendor_id FK
        string contract_number
        date start_date
        date end_date
        decimal contract_value
        text terms
        string document_path
        enum status
    }
```

## RLS Policy Coverage

| Table                           | SELECT | INSERT | UPDATE | DELETE | WITH CHECK | Status          |
| ------------------------------- | ------ | ------ | ------ | ------ | ---------- | --------------- |
| fm\_assets                      | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |
| fm\_asset\_categories           | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |
| fm\_asset\_maintenance\_history | ✅      | ✅      | -      | -      | -          | History table   |
| fm\_work\_orders                | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |
| fm\_work\_order\_history        | ✅      | ✅      | -      | -      | -          | History table   |
| fm\_pm\_schedules               | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |
| fm\_inventory\_items            | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |
| fm\_inventory\_transactions     | ✅      | ✅      | -      | -      | -          | Transaction log |
| fm\_vendors                     | ✅      | ✅      | ✅      | ✅      | ✅          | Complete        |

## Key Tables

### fm\_assets

Physical assets with lifecycle tracking, specifications, and warranty info.

### fm\_work\_orders

Maintenance and repair work orders with task tracking.

### fm\_pm\_schedules

Preventive maintenance schedules generating recurring work orders.

### fm\_inventory\_items

Spare parts and supplies inventory with reorder management.

## Helper Functions

| Function                                    | Purpose                       |
| ------------------------------------------- | ----------------------------- |
| `fm_has_org_access(org_id, user_id)`        | Check FM module access        |
| `fm_get_overdue_pm(org_id)`                 | Get overdue PM schedules      |
| `fm_get_low_inventory(org_id)`              | Get items below reorder point |
| `fm_calculate_asset_depreciation(asset_id)` | Calculate current value       |

## Cross-Module Dependencies

* **PF:** Organizations, sites, profiles
* **FA:** Asset depreciation, expense tracking
* **HR:** Work order assignments, technician tracking