> ## 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 Database Tables

> The FM module provides facilities and asset management capabilities including asset tracking, inventory management, work orders, preventive maintenance schedul…

**Prefix:** `fm_`\
**Table Count:** 22\
**Last Updated:** 2026-01-10

***

## Overview

The FM module provides facilities and asset management capabilities including asset tracking, inventory management, work orders, preventive maintenance scheduling, and vendor management.

***

## Table Categories

### 1. Asset Management (4 tables)

| Table                          | Columns | Description           |
| ------------------------------ | ------- | --------------------- |
| `fm_assets`                    | 47      | Asset master records  |
| `fm_asset_locations`           | 12      | Location history      |
| `fm_asset_depreciation`        | 14      | Depreciation tracking |
| `fm_asset_maintenance_history` | 16      | Maintenance history   |

**Asset Fields (47 columns):**

```typescript theme={null}
interface Asset {
  id: string;
  asset_tag: string;
  name: string;
  description: string;
  category: AssetCategory;
  manufacturer: string;
  model: string;
  serial_number: string;
  purchase_date: string;
  purchase_price: number;
  warranty_expiration?: string;
  location_id: string;
  assigned_to?: string;
  status: AssetStatus;
  condition: AssetCondition;
  // Depreciation
  depreciation_method?: string;
  useful_life_years?: number;
  salvage_value?: number;
  current_value?: number;
  // Maintenance
  last_maintenance_date?: string;
  next_maintenance_date?: string;
  maintenance_schedule_id?: string;
  // Custom
  custom_fields: Record<string, any>;
}
```

**Asset Status:**

* `in_service` - Active and operational
* `spare` - Available backup
* `under_repair` - Being repaired
* `retired` - No longer in service
* `disposed` - Disposed of
* `lost` - Cannot be located

**Asset Condition:**

* `excellent` - Like new
* `good` - Minor wear
* `fair` - Moderate wear
* `poor` - Significant wear
* `critical` - Needs replacement

### 2. Inventory Management (4 tables)

| Table                         | Columns | Description                |
| ----------------------------- | ------- | -------------------------- |
| `fm_inventory_items`          | 24      | Inventory item definitions |
| `fm_inventory_locations`      | 14      | Storage locations          |
| `fm_inventory_item_locations` | 12      | Item-to-location mapping   |
| `fm_inventory_transactions`   | 16      | Stock movements            |

**Transaction Types:**

* `receipt` - Stock received
* `issue` - Stock issued
* `transfer` - Location transfer
* `adjustment` - Inventory adjustment
* `return` - Stock returned
* `scrap` - Stock scrapped

**Inventory Tracking:**

```typescript theme={null}
interface InventoryItem {
  id: string;
  item_number: string;
  name: string;
  description: string;
  category: string;
  unit_of_measure: string;
  reorder_point: number;
  reorder_quantity: number;
  min_quantity: number;
  max_quantity: number;
  current_quantity: number;  // Computed from locations
  unit_cost: number;
  vendor_id?: string;
}
```

### 3. Work Order System (4 tables)

| Table                       | Columns | Description        |
| --------------------------- | ------- | ------------------ |
| `fm_work_orders`            | 49      | Work order records |
| `fm_work_order_attachments` | 12      | Attached files     |
| `fm_work_order_history`     | 14      | Status history     |
| `fm_work_order_materials`   | 12      | Materials used     |

**Work Order Fields (49 columns):**

```typescript theme={null}
interface WorkOrder {
  id: string;
  work_order_number: string;
  title: string;
  description: string;
  type: WorkOrderType;
  priority: Priority;
  status: WorkOrderStatus;
  // Location
  site_id: string;
  location: string;
  asset_id?: string;
  // Assignment
  requested_by: string;
  assigned_to?: string;
  team_id?: string;
  // Scheduling
  requested_date: string;
  scheduled_start?: string;
  scheduled_end?: string;
  actual_start?: string;
  actual_end?: string;
  // Financials
  estimated_hours?: number;
  actual_hours?: number;
  estimated_cost?: number;
  actual_cost?: number;
  // PM Link
  pm_schedule_id?: string;
  // Custom
  custom_fields: Record<string, any>;
}
```

**Work Order Types:**

* `corrective` - Break/fix repairs
* `preventive` - Scheduled maintenance
* `emergency` - Urgent issues
* `project` - Project work
* `inspection` - Inspections

**Work Order Status Flow:**

```
open → assigned → in_progress → pending_parts → completed → closed
                        ↓
                    on_hold
```

**Priority Levels:**

* `critical` - Immediate (safety issue)
* `high` - Within 4 hours
* `medium` - Within 24 hours
* `low` - Within 1 week
* `scheduled` - As scheduled

### 4. Preventive Maintenance (5 tables)

| Table                            | Columns | Description             |
| -------------------------------- | ------- | ----------------------- |
| `fm_pm_templates`                | 20      | PM template definitions |
| `fm_pm_template_checklist_items` | 12      | Checklist items         |
| `fm_pm_template_materials`       | 10      | Required materials      |
| `fm_pm_schedules`                | 18      | Active PM schedules     |
| `fm_pm_completions`              | 16      | Completion records      |

**PM Schedule Types:**

* `time_based` - Every X days/weeks/months
* `meter_based` - Every X hours/miles/cycles
* `condition_based` - Based on condition triggers

**PM Template Example:**

```typescript theme={null}
interface PMTemplate {
  id: string;
  name: string;
  description: string;
  asset_category: string;
  frequency_type: 'time_based' | 'meter_based';
  frequency_value: number;
  frequency_unit: string;
  estimated_hours: number;
  checklist_items: ChecklistItem[];
  required_materials: Material[];
  skills_required: string[];
}
```

### 5. Vendor Management (4 tables)

| Table                      | Columns | Description              |
| -------------------------- | ------- | ------------------------ |
| `fm_vendors`               | 26      | Vendor master records    |
| `fm_vendor_sites`          | 12      | Vendor service locations |
| `fm_vendor_certifications` | 14      | Vendor certifications    |
| `fm_vendor_work_orders`    | 10      | Vendor work order links  |

**Vendor Types:**

* `contractor` - General contractors
* `supplier` - Parts/materials suppliers
* `service` - Service providers
* `consultant` - Consultants

### 6. Module Settings (1 table)

| Table                | Columns | Description      |
| -------------------- | ------- | ---------------- |
| `fm_module_settings` | 30      | FM configuration |

**Key Settings:**

| Setting                       | Type    | Default  |
| ----------------------------- | ------- | -------- |
| `work_order_auto_number`      | boolean | true     |
| `work_order_prefix`           | text    | 'WO-'    |
| `default_priority`            | text    | 'medium' |
| `pm_advance_days`             | integer | 7        |
| `low_stock_alert_enabled`     | boolean | true     |
| `require_work_order_approval` | boolean | false    |

***

## Common Query Patterns

### Get Assets by Location

```typescript theme={null}
const { data } = await supabase
  .from('fm_assets')
  .select(`
    *,
    location:pf_sites(name),
    assigned_to:pf_profiles(first_name, last_name),
    maintenance_history:fm_asset_maintenance_history(
      maintenance_date,
      description,
      cost
    )
  `)
  .eq('organization_id', orgId)
  .eq('site_id', siteId)
  .eq('status', 'in_service');
```

### Get Open Work Orders

```typescript theme={null}
const { data } = await supabase
  .from('fm_work_orders')
  .select(`
    *,
    asset:fm_assets(name, asset_tag),
    assigned_to:pf_profiles(first_name, last_name),
    materials:fm_work_order_materials(
      item:fm_inventory_items(name),
      quantity
    )
  `)
  .eq('organization_id', orgId)
  .in('status', ['open', 'assigned', 'in_progress'])
  .order('priority', { ascending: true });
```

### Get PM Schedule Due This Week

```typescript theme={null}
const { data } = await supabase
  .from('fm_pm_schedules')
  .select(`
    *,
    template:fm_pm_templates(
      name,
      estimated_hours,
      checklist_items:fm_pm_template_checklist_items(*)
    ),
    asset:fm_assets(name, asset_tag, location_id)
  `)
  .eq('organization_id', orgId)
  .lte('next_due_date', addDays(new Date(), 7))
  .eq('is_active', true);
```

### Get Low Stock Items

```typescript theme={null}
const { data } = await supabase
  .from('fm_inventory_item_locations')
  .select(`
    quantity,
    item:fm_inventory_items(
      item_number,
      name,
      reorder_point,
      reorder_quantity
    ),
    location:fm_inventory_locations(name)
  `)
  .eq('organization_id', orgId)
  .lt('quantity', supabase.raw('item.reorder_point'));
```

### Get Vendor Performance

```typescript theme={null}
const { data } = await supabase
  .from('fm_vendor_work_orders')
  .select(`
    vendor:fm_vendors(name),
    work_order:fm_work_orders(
      status,
      actual_cost,
      actual_hours
    )
  `)
  .eq('organization_id', orgId);
```

***

## RLS Policies

FM uses site-based RLS:

* Work orders visible to site staff
* Assets visible by location assignment
* Inventory by location access

**Helper Functions:**

* `fm_user_has_site_access()` - Site access check
* `fm_user_can_manage_work_order()` - WO management
* `fm_user_can_view_inventory()` - Inventory access

***

## Cross-Module Integration

**FM → FA Integration:**

```
fm_assets → fa_accounts (depreciation entries)
fm_work_orders → fa_vendor_bills (contractor invoices)
fm_inventory_transactions → fa_journal_entries (inventory adjustments)
```

**FM → HR Integration:**

```
fm_work_orders.assigned_to → hr_employees
fm_vendor_certifications → hr_credential_types (credential tracking)
```

***

## See Also

* [FM Module Specs](https://github.com/Encore-OS/encoreos/tree/development/specs/fm)
* [Asset Management Spec](https://github.com/Encore-OS/encoreos/blob/development/specs/fm/specs/FM-05-asset-management.md)
* [Database Schema Overview](/architecture/DATABASE_SCHEMA)