Encore OS — Documentation

Generated by npm run db:docs:generate — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate.

Core: fm_ · Tables: 29 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`fm_asset_depreciation`

Depreciation records are immutable for audit purposes. Corrections require new reversal entries. No UPDATE or DELETE policies exist by design. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`depreciation_month`	date	no
`fiscal_year_id`	uuid	yes
`beginning_book_value`	numeric(12,2)	no
`depreciation_amount`	numeric(12,2)	no
`ending_book_value`	numeric(12,2)	no
`depreciation_method`	text	no
`depreciation_rate`	numeric(5,4)	yes
`calculated_at`	timestamp with time zone	no	now()
`calculated_by`	uuid	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

asset_id → fm_assets(id)
calculated_by → pf_profiles(id)
fiscal_year_id → fa_fiscal_years(id)
organization_id → pf_organizations(id)

Policies:

fm_asset_dep_insert (INSERT)
fm_asset_dep_select (SELECT)
fm_asset_depreciation_insert (INSERT)
fm_asset_depreciation_select (SELECT)

`fm_asset_locations`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`site_id`	uuid	yes
`building`	text	yes
`room`	text	yes
`location_description`	text	yes
`moved_at`	timestamp with time zone	no	now()
`moved_by`	uuid	yes
`move_reason`	text	yes
`previous_site_id`	uuid	yes
`previous_building`	text	yes
`previous_room`	text	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

asset_id → fm_assets(id)
moved_by → pf_profiles(id)
organization_id → pf_organizations(id)
previous_site_id → pf_sites(id)
site_id → pf_sites(id)

Policies:

fm_asset_loc_insert (INSERT)
fm_asset_loc_select (SELECT)

`fm_asset_maintenance_history`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`work_order_id`	uuid	no
`work_order_number`	text	no
`work_order_date`	date	no
`work_order_type`	text	yes
`work_order_priority`	text	yes
`work_order_status`	text	no
`labor_cost`	numeric(12,2)	yes	0
`material_cost`	numeric(12,2)	yes	0
`total_cost`	numeric(12,2)	yes	0
`description`	text	yes
`resolution`	text	yes
`technician_notes`	text	yes
`recorded_at`	timestamp with time zone	no	now()
`recorded_by`	uuid	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

asset_id → fm_assets(id)
organization_id → pf_organizations(id)
recorded_by → pf_profiles(id)
work_order_id → fm_work_orders(id)

Policies:

fm_asset_maint_insert (INSERT)
fm_asset_maint_select (SELECT)

`fm_assets`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`asset_tag`	text	no
`name`	text	no
`description`	text	yes
`asset_category`	text	no
`asset_type`	text	yes
`manufacturer`	text	yes
`model_number`	text	yes
`serial_number`	text	yes
`status`	text	no	’active’::text
`condition`	text	yes
`acquisition_date`	date	yes
`installation_date`	date	yes
`in_service_date`	date	yes
`retirement_date`	date	yes
`disposal_date`	date	yes
`disposal_method`	text	yes
`disposal_notes`	text	yes
`expected_life_years`	integer	yes
`purchase_price`	numeric(12,2)	yes
`current_book_value`	numeric(12,2)	yes
`salvage_value`	numeric(12,2)	yes
`depreciation_method`	text	yes	’straight_line’::text
`depreciation_rate`	numeric(5,4)	yes
`last_depreciation_date`	date	yes
`warranty_expiration_date`	date	yes
`warranty_details`	text	yes
`service_contract_vendor_id`	uuid	yes
`service_contract_expiration_date`	date	yes
`service_contract_details`	text	yes
`current_building`	text	yes
`current_room`	text	yes
`current_location_description`	text	yes
`total_maintenance_cost`	numeric(12,2)	yes	0
`work_order_count`	integer	yes	0
`last_maintenance_date`	date	yes
`next_scheduled_maintenance`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`warranty_alert_sent_days`	jsonb	no	’[]‘::jsonb	Array of alert threshold days already sent (e.g., [90, 60, 30])
`service_contract_alert_sent_days`	jsonb	no	’[]‘::jsonb	Array of alert threshold days already sent for service contracts

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
service_contract_vendor_id → fm_vendors(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fm_assets_delete (DELETE)
fm_assets_insert (INSERT)
fm_assets_select (SELECT)
fm_assets_update (UPDATE)

`fm_fleet_drivers`

FM-13: Fleet driver/operator records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	yes
`first_name`	text	no
`last_name`	text	no
`email`	text	yes
`phone`	text	yes
`license_number`	text	yes
`license_state`	character varying(2)	yes
`license_expiration`	date	yes
`license_class`	text	yes
`has_cdl`	boolean	yes	false
`cdl_endorsements`	text[]	yes
`cdl_expiration`	date	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)
user_id → pf_profiles(id)

Policies:

fm_fleet_drivers_delete (DELETE)
fm_fleet_drivers_insert (INSERT)
fm_fleet_drivers_select (SELECT)
fm_fleet_drivers_update (UPDATE)

`fm_fleet_fuel_logs`

FM-13: Fuel purchase records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vehicle_id`	uuid	no
`driver_id`	uuid	yes
`purchase_date`	date	no	CURRENT_DATE
`fuel_type`	fuel_type_enum	no	’gasoline’::fuel_type_enum
`gallons`	numeric(8,3)	no
`price_per_gallon`	numeric(6,3)	yes
`total_cost`	numeric(10,2)	no
`odometer_reading`	integer	yes
`mpg_calculated`	numeric(5,2)	yes
`station_name`	text	yes
`station_location`	text	yes
`receipt_number`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
driver_id → fm_fleet_drivers(id)
organization_id → pf_organizations(id)
updated_by → users(id)
vehicle_id → fm_fleet_vehicles(id)

Policies:

fm_fleet_fuel_logs_delete (DELETE)
fm_fleet_fuel_logs_insert (INSERT)
fm_fleet_fuel_logs_select (SELECT)
fm_fleet_fuel_logs_update (UPDATE)

`fm_fleet_maintenance_schedules`

FM-13: Preventive maintenance schedules for fleet vehicles RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vehicle_id`	uuid	no
`name`	text	no
`description`	text	yes
`trigger_type`	maintenance_trigger_enum	no	’mileage’::maintenance_trigger_enum
`mileage_interval`	integer	yes
`time_interval_days`	integer	yes
`last_completed_date`	date	yes
`last_completed_odometer`	integer	yes
`next_due_date`	date	yes
`next_due_odometer`	integer	yes
`is_active`	boolean	no	true
`auto_create_work_order`	boolean	yes	false
`work_order_template_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)
vehicle_id → fm_fleet_vehicles(id)

Policies:

fm_fleet_maintenance_schedules_delete (DELETE)
fm_fleet_maintenance_schedules_insert (INSERT)
fm_fleet_maintenance_schedules_select (SELECT)
fm_fleet_maintenance_schedules_update (UPDATE)

`fm_fleet_mileage_logs`

FM-13: Historical odometer readings RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vehicle_id`	uuid	no
`trip_id`	uuid	yes
`odometer_reading`	integer	no
`miles_driven`	integer	yes
`recorded_at`	timestamp with time zone	no	now()
`source`	text	yes	’manual’::text
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
trip_id → fm_fleet_trips(id)
vehicle_id → fm_fleet_vehicles(id)

Policies:

fm_fleet_mileage_logs_delete (DELETE)
fm_fleet_mileage_logs_insert (INSERT)
fm_fleet_mileage_logs_select (SELECT)
fm_fleet_mileage_logs_update (UPDATE)

`fm_fleet_reservations`

FM-13 Phase 2: Vehicle reservation records for shared/pool fleet vehicles RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vehicle_id`	uuid	no
`driver_id`	uuid	no
`start_at`	timestamp with time zone	no
`end_at`	timestamp with time zone	no
`purpose`	text	yes
`notes`	text	yes
`status`	text	no	’confirmed’::text	Reservation status: confirmed or cancelled
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
driver_id → fm_fleet_drivers(id)
organization_id → pf_organizations(id)
updated_by → users(id)
vehicle_id → fm_fleet_vehicles(id)

Policies:

fm_fleet_reservations_delete (DELETE)
fm_fleet_reservations_insert (INSERT)
fm_fleet_reservations_select (SELECT)
fm_fleet_reservations_update (UPDATE)

`fm_fleet_trips`

FM-13: Fleet trip records for mileage tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vehicle_id`	uuid	no
`driver_id`	uuid	yes
`trip_date`	date	no	CURRENT_DATE
`trip_type`	trip_type_enum	no	’business’::trip_type_enum
`purpose`	text	yes
`destination`	text	yes
`start_odometer`	integer	yes
`end_odometer`	integer	yes
`miles_driven`	integer	yes
`mileage_rate`	numeric(6,4)	yes
`trip_cost`	numeric(10,2)	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
driver_id → fm_fleet_drivers(id)
organization_id → pf_organizations(id)
updated_by → users(id)
vehicle_id → fm_fleet_vehicles(id)

Policies:

fm_fleet_trips_delete (DELETE)
fm_fleet_trips_insert (INSERT)
fm_fleet_trips_select (SELECT)
fm_fleet_trips_update (UPDATE)

`fm_fleet_vehicles`

FM-13: Fleet vehicle master records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`vin`	character varying(17)	no
`vehicle_number`	text	yes
`plate_number`	text	yes
`plate_state`	character varying(2)	yes
`make`	text	no
`model`	text	no
`year`	integer	no
`color`	text	yes
`fuel_type`	fuel_type_enum	no	’gasoline’::fuel_type_enum
`status`	vehicle_status_enum	no	’active’::vehicle_status_enum
`assigned_driver_id`	uuid	yes
`current_odometer`	integer	no	0
`odometer_unit`	text	no	’miles’::text
`registration_expiration`	date	yes
`insurance_expiration`	date	yes
`inspection_expiration`	date	yes
`asset_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assigned_driver_id → fm_fleet_drivers(id)
created_by → users(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

fm_fleet_vehicles_delete (DELETE)
fm_fleet_vehicles_insert (INSERT)
fm_fleet_vehicles_select (SELECT)
fm_fleet_vehicles_update (UPDATE)

`fm_inventory_item_locations`

Junction table tracking quantity of each item at each location RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`inventory_item_id`	uuid	no
`location_id`	uuid	no
`quantity`	numeric(12,2)	no	0
`bin_location`	character varying(50)	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
inventory_item_id → fm_inventory_items(id)
location_id → fm_inventory_locations(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fm_inventory_item_locations_delete (DELETE)
fm_inventory_item_locations_insert (INSERT)
fm_inventory_item_locations_select (SELECT)
fm_inventory_item_locations_update (UPDATE)

`fm_inventory_items`

Master inventory item records with quantity, cost, and reorder management RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`item_number`	character varying(50)	no	”::character varying
`item_name`	character varying(200)	no
`description`	text	yes
`sku`	character varying(100)	yes
`category`	character varying(50)	no
`unit_of_measure`	character varying(20)	no
`current_quantity`	numeric(12,2)	no	0
`reorder_point`	numeric(12,2)	no	0
`reorder_quantity`	numeric(12,2)	no	0
`unit_cost`	numeric(12,4)	no	0
`average_cost`	numeric(12,4)	no	0
`last_purchase_cost`	numeric(12,4)	yes
`last_purchase_date`	date	yes
`preferred_vendor_id`	uuid	yes		FK to fm_vendors - constraint added when FM-03 is implemented
`asset_id`	uuid	yes		FK to fm_assets - constraint added when FM-05 is implemented
`default_location_id`	uuid	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“manufacturer”: “3M”, “model_number”: “XYZ-123”, “warranty_months”: 12, “min_order_qty”: 5, “hazmat_class”: “flammable”}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_location_id → fm_inventory_locations(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fm_inventory_items_delete (DELETE)
fm_inventory_items_insert (INSERT)
fm_inventory_items_select (SELECT)
fm_inventory_items_update (UPDATE)

`fm_inventory_locations`

Storage locations for inventory items (warehouses, sites, vehicles) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`location_name`	character varying(100)	no
`location_type`	character varying(20)	no
`description`	text	yes
`address`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“capacity_sqft”: 5000, “climate_controlled”: true, “security_level”: “high”, “manager_name”: “John Smith”, “operating_hours”: “8AM-6PM”}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fm_inventory_locations_delete (DELETE)
fm_inventory_locations_insert (INSERT)
fm_inventory_locations_select (SELECT)
fm_inventory_locations_update (UPDATE)

`fm_inventory_transactions`

Append-only transaction history for all inventory movements - DO NOT UPDATE OR DELETE RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`inventory_item_id`	uuid	no
`transaction_number`	character varying(50)	no	”::character varying
`transaction_type`	character varying(20)	no
`transaction_date`	timestamp with time zone	no	now()
`quantity_change`	numeric(12,2)	no
`unit_cost`	numeric(12,4)	yes
`total_cost`	numeric(12,4)	yes	(quantity_change * COALESCE(unit_cost, (0)::numeric))
`work_order_id`	uuid	yes
`purchase_order_id`	uuid	yes		FK to fa_purchase_orders - constraint added when FA-04 is implemented
`vendor_invoice_id`	uuid	yes		FK to fa_vendor_bills - constraint added when FA integration available
`location_id`	uuid	yes
`transfer_to_location_id`	uuid	yes
`transfer_from_location_id`	uuid	yes
`notes`	text	yes
`reason`	character varying(100)	yes
`reference_number`	character varying(100)	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
inventory_item_id → fm_inventory_items(id)
location_id → fm_inventory_locations(id)
organization_id → pf_organizations(id)
transfer_from_location_id → fm_inventory_locations(id)
transfer_to_location_id → fm_inventory_locations(id)
updated_by → users(id)
work_order_id → fm_work_orders(id)

Policies:

fm_inventory_transactions_insert (INSERT)
fm_inventory_transactions_select (SELECT)

`fm_module_settings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`wo_number_prefix`	text	yes	’WO’::text
`default_priority`	text	yes	’medium’::text
`priority_levels`	text[]	yes	ARRAY[‘low’::text, ‘medium’::text, ‘high’::text, ‘urgent’::text]
`auto_assign_by_category`	boolean	yes	false
`require_wo_approval_above`	numeric(15,2)	yes
`default_due_days`	integer	yes	7
`low_stock_alert_threshold`	integer	yes	10
`default_unit_of_measure`	text	yes	’each’::text
`enable_reorder_points`	boolean	yes	true
`auto_create_po_at_reorder`	boolean	yes	false
`default_payment_terms_days`	integer	yes	30
`require_vendor_approval`	boolean	yes	true
`preferred_vendor_discount_pct`	numeric(5,2)	yes	0
`pm_alert_days`	integer[]	yes	ARRAY[30, 14, 7]
`pm_schedule_lookahead_days`	integer	yes	30
`auto_generate_pm_work_orders`	boolean	yes	true
`asset_tag_prefix`	text	yes	’AST’::text
`depreciation_method`	text	yes	’straight_line’::text
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`vendor_default_payment_terms_days`	integer	yes	30
`vendor_certification_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`vendor_require_certification_for_assignment`	boolean	yes	true
`vendor_number_prefix`	character varying(10)	yes	’VND’::character varying
`pm_compliance_priority`	text	yes	’high’::text
`wo_number_sequence`	integer	yes	1	Next sequence number for work order generation
`vendor_number_sequence`	integer	yes	1	Next sequence number for vendor generation
`asset_tag_sequence`	integer	yes	1	Next sequence number for asset tag generation
`fleet_license_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`fleet_registration_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`fleet_default_mileage_rate`	numeric(6,4)	yes	0.6550
`fleet_prevent_expired_license`	boolean	yes	true
`fleet_vehicle_prefix`	text	yes	’VEH’::text
`fleet_vehicle_sequence`	integer	yes	1
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fm_module_settings_delete (DELETE)
fm_module_settings_insert (INSERT)
fm_module_settings_select (SELECT)
fm_module_settings_update (UPDATE)

`fm_pm_completions`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pm_schedule_id`	uuid	no
`work_order_id`	uuid	no
`completed_date`	date	no
`completed_by`	uuid	yes
`checklist_items_total`	integer	yes
`checklist_items_passed`	integer	yes
`checklist_items_failed`	integer	yes
`completion_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

completed_by → pf_profiles(id)
organization_id → pf_organizations(id)
pm_schedule_id → fm_pm_schedules(id)
work_order_id → fm_work_orders(id)

Policies:

fm_pm_completions_insert (INSERT)
fm_pm_completions_select (SELECT)

`fm_pm_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`pm_template_id`	uuid	no
`asset_id`	uuid	yes		References fm_assets when FM-05 is implemented
`start_date`	date	no
`next_due_date`	date	no
`last_completed_date`	date	yes
`last_completed_work_order_id`	uuid	yes
`lookahead_days`	integer	yes	30
`auto_generate_work_orders`	boolean	yes	true
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

asset_id → fm_assets(id)
created_by → pf_profiles(id)
last_completed_work_order_id → fm_work_orders(id)
organization_id → pf_organizations(id)
pm_template_id → fm_pm_templates(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fm_pm_schedules_delete (DELETE)
fm_pm_schedules_insert (INSERT)
fm_pm_schedules_select (SELECT)
fm_pm_schedules_update (UPDATE)

`fm_pm_template_checklist_items`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pm_template_id`	uuid	no
`item_description`	text	no
`item_order`	integer	no
`pass_criteria`	text	yes
`is_required`	boolean	yes	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
pm_template_id → fm_pm_templates(id)
updated_by → pf_profiles(id)

Policies:

fm_pm_checklist_delete (DELETE)
fm_pm_checklist_insert (INSERT)
fm_pm_checklist_select (SELECT)
fm_pm_checklist_update (UPDATE)

`fm_pm_template_materials`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pm_template_id`	uuid	no
`inventory_item_id`	uuid	no
`quantity_required`	numeric(10,3)	no	1
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
inventory_item_id → fm_inventory_items(id)
organization_id → pf_organizations(id)
pm_template_id → fm_pm_templates(id)
updated_by → users(id)

Policies:

fm_pm_materials_delete (DELETE)
fm_pm_materials_insert (INSERT)
fm_pm_materials_select (SELECT)
fm_pm_materials_update (UPDATE)

`fm_pm_templates`

Preventive maintenance template definitions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`description`	text	yes
`asset_type`	text	no
`frequency`	text	no
`estimated_duration_hours`	numeric(5,2)	yes
`default_assignment_type`	text	yes
`default_assigned_to_employee_id`	uuid	yes
`default_assigned_to_vendor_id`	uuid	yes
`required_skills`	text[]	yes
`is_compliance_required`	boolean	yes	false
`compliance_type`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_assigned_to_vendor_id → fm_vendors(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fm_pm_templates_delete (DELETE)
fm_pm_templates_insert (INSERT)
fm_pm_templates_select (SELECT)
fm_pm_templates_update (UPDATE)

`fm_vendor_certifications`

FM-03: Vendor certifications with expiration tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`vendor_id`	uuid	no
`organization_id`	uuid	no
`certification_type`	character varying(50)	no
`certification_name`	character varying(255)	no
`certification_number`	character varying(100)	yes
`issuing_authority`	character varying(255)	yes
`issue_date`	date	yes
`expiration_date`	date	yes
`coverage_amount`	numeric(15,2)	yes
`coverage_details`	text	yes
`document_id`	uuid	yes
`reminder_90_sent`	boolean	yes	false
`reminder_60_sent`	boolean	yes	false
`reminder_30_sent`	boolean	yes	false
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
vendor_id → fm_vendors(id)

Policies:

fm_vendor_certs_delete (DELETE)
fm_vendor_certs_insert (INSERT)
fm_vendor_certs_select (SELECT)
fm_vendor_certs_update (UPDATE)

`fm_vendor_sites`

FM-03: Junction table linking vendors to sites they service RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`vendor_id`	uuid	no
`site_id`	uuid	no
`organization_id`	uuid	no
`is_primary_site`	boolean	no	false
`service_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)
vendor_id → fm_vendors(id)

Policies:

fm_vendor_sites_delete (DELETE)
fm_vendor_sites_insert (INSERT)
fm_vendor_sites_select (SELECT)
fm_vendor_sites_update (UPDATE)

`fm_vendor_work_orders`

FM-03: Vendor assignments to work orders RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`work_order_id`	uuid	no
`vendor_id`	uuid	no
`organization_id`	uuid	no
`assigned_at`	timestamp with time zone	no	now()
`assigned_by`	uuid	no
`quote_amount`	numeric(12,2)	yes
`quote_date`	date	yes
`quote_valid_until`	date	yes
`quote_notes`	text	yes
`quote_document_id`	uuid	yes
`invoice_number`	character varying(100)	yes
`invoice_amount`	numeric(12,2)	yes
`invoice_date`	date	yes
`invoice_document_id`	uuid	yes
`payment_status`	character varying(20)	no	’pending’::character varying
`payment_date`	date	yes
`payment_reference`	character varying(100)	yes
`vendor_status`	character varying(20)	no	’assigned’::character varying
`scheduled_start_date`	date	yes
`scheduled_end_date`	date	yes
`actual_start_date`	date	yes
`actual_end_date`	date	yes
`customer_rating`	integer	yes
`rating_notes`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

assigned_by → pf_profiles(id)
invoice_document_id → pf_documents(id)
organization_id → pf_organizations(id)
quote_document_id → pf_documents(id)
updated_by → pf_profiles(id)
vendor_id → fm_vendors(id)
work_order_id → fm_work_orders(id)

Policies:

fm_vendor_wo_delete (DELETE)
fm_vendor_wo_insert (INSERT)
fm_vendor_wo_select (SELECT)
fm_vendor_wo_update (UPDATE)

`fm_vendors`

FM-03: Vendor master records for facilities management RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_number`	character varying(50)	yes
`vendor_name`	character varying(255)	no
`dba_name`	character varying(255)	yes
`primary_contact_name`	character varying(255)	yes
`primary_contact_email`	character varying(255)	yes
`primary_contact_phone`	character varying(50)	yes
`address_line1`	character varying(255)	yes
`address_line2`	character varying(255)	yes
`city`	character varying(100)	yes
`state`	character varying(50)	yes
`postal_code`	character varying(20)	yes
`country`	character varying(100)	yes	’USA’::character varying
`tax_id`	character varying(20)	yes
`tax_id_type`	character varying(10)	yes
`vendor_category`	character varying(50)	no
`payment_terms_days`	integer	yes	30
`default_hourly_rate`	numeric(10,2)	yes
`is_active`	boolean	no	true
`is_preferred`	boolean	no	false
`is_approved`	boolean	no	false
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`total_work_orders`	integer	yes	0
`avg_completion_days`	numeric(5,2)	yes
`avg_cost_per_wo`	numeric(12,2)	yes
`customer_satisfaction_rating`	numeric(3,2)	yes
`on_time_completion_rate`	numeric(5,2)	yes
`rework_rate`	numeric(5,2)	yes
`last_performance_calc_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

fm_vendors_delete (DELETE)
fm_vendors_insert (INSERT)
fm_vendors_select (SELECT)
fm_vendors_update (UPDATE)

`fm_work_order_attachments`

FM-01: Photos, documents, and files attached to work orders RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`work_order_id`	uuid	no
`organization_id`	uuid	no
`file_name`	character varying(255)	no
`file_path`	character varying(500)	no
`file_type`	character varying(100)	yes
`file_size_bytes`	bigint	yes
`mime_type`	character varying(100)	yes
`document_id`	uuid	yes
`description`	text	yes
`attachment_type`	character varying(50)	yes	’general’::character varying
`uploaded_by`	uuid	no
`uploaded_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

organization_id → pf_organizations(id)
uploaded_by → pf_profiles(id)
work_order_id → fm_work_orders(id)

Policies:

fm_work_order_attachments_delete (DELETE)
fm_work_order_attachments_insert (INSERT)
fm_work_order_attachments_select (SELECT)

`fm_work_order_history`

FM-01: Audit trail of all changes to work orders RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`work_order_id`	uuid	no
`organization_id`	uuid	no
`action`	character varying(50)	no
`field_changed`	character varying(100)	yes
`old_value`	text	yes
`new_value`	text	yes
`notes`	text	yes
`performed_by`	uuid	no
`performed_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

organization_id → pf_organizations(id)
performed_by → pf_profiles(id)
work_order_id → fm_work_orders(id)

Policies:

fm_work_order_history_insert (INSERT)
fm_work_order_history_select (SELECT)

`fm_work_order_materials`

FM-01: Materials and inventory items used on work orders (integrates with FM-02) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`work_order_id`	uuid	no
`organization_id`	uuid	no
`inventory_item_id`	uuid	yes
`item_name`	character varying(255)	no
`item_description`	text	yes
`quantity_used`	numeric(10,2)	no	0
`unit_of_measure`	character varying(50)	yes
`unit_cost`	numeric(12,2)	yes
`total_cost`	numeric(12,2)	yes	(quantity_used * COALESCE(unit_cost, (0)::numeric))
`used_by`	uuid	yes
`used_at`	timestamp with time zone	no	now()
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → users(id)
used_by → pf_profiles(id)
work_order_id → fm_work_orders(id)

Policies:

fm_work_order_materials_delete (DELETE)
fm_work_order_materials_insert (INSERT)
fm_work_order_materials_select (SELECT)
fm_work_order_materials_update (UPDATE)

`fm_work_orders`

FM-01: Work orders for facilities maintenance requests and tasks RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`work_order_number`	character varying(50)	no
`title`	character varying(255)	no
`description`	text	yes
`status`	fm_work_order_status	no	’draft’::fm_work_order_status
`work_order_type`	fm_work_order_type	no	’request’::fm_work_order_type
`priority`	fm_work_order_priority	no	’medium’::fm_work_order_priority
`category`	fm_work_order_category	no	’general_maintenance’::fm_work_order_category
`location_name`	character varying(255)	yes
`location_details`	text	yes
`building`	character varying(100)	yes
`floor`	character varying(50)	yes
`room`	character varying(50)	yes
`assigned_to`	uuid	yes
`assigned_at`	timestamp with time zone	yes
`assigned_by`	uuid	yes
`requested_date`	date	yes
`due_date`	date	yes
`scheduled_start`	timestamp with time zone	yes
`scheduled_end`	timestamp with time zone	yes
`actual_start`	timestamp with time zone	yes
`actual_end`	timestamp with time zone	yes
`requester_id`	uuid	yes
`requester_name`	character varying(255)	yes
`requester_email`	character varying(255)	yes
`requester_phone`	character varying(50)	yes
`estimated_hours`	numeric(8,2)	yes
`actual_hours`	numeric(8,2)	yes
`estimated_cost`	numeric(12,2)	yes
`actual_labor_cost`	numeric(12,2)	yes
`actual_material_cost`	numeric(12,2)	yes
`total_actual_cost`	numeric(12,2)	yes	(COALESCE(actual_labor_cost, (0)::numeric) + COALESCE(actual_material_cost, (0)::numeric))
`asset_id`	uuid	yes
`vendor_id`	uuid	yes
`pm_schedule_id`	uuid	yes
`inspection_schedule_id`	uuid	yes
`resolution_notes`	text	yes
`resolution_code`	character varying(50)	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

assigned_by → pf_profiles(id)
closed_by → pf_profiles(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
requester_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

fm_work_orders_delete (DELETE)
fm_work_orders_insert (INSERT)
fm_work_orders_select (SELECT)
fm_work_orders_update (UPDATE)

​Entity relationships

​Tables

​fm_asset_depreciation

​fm_asset_locations

​fm_asset_maintenance_history

​fm_assets

​fm_fleet_drivers

​fm_fleet_fuel_logs

​fm_fleet_maintenance_schedules

​fm_fleet_mileage_logs

​fm_fleet_reservations

​fm_fleet_trips

​fm_fleet_vehicles

​fm_inventory_item_locations

​fm_inventory_items

​fm_inventory_locations

​fm_inventory_transactions

​fm_module_settings

​fm_pm_completions

​fm_pm_schedules

​fm_pm_template_checklist_items

​fm_pm_template_materials

​fm_pm_templates

​fm_vendor_certifications

​fm_vendor_sites

​fm_vendor_work_orders

​fm_vendors

​fm_work_order_attachments

​fm_work_order_history

​fm_work_order_materials

​fm_work_orders

Entity relationships

Tables

`fm_asset_depreciation`

`fm_asset_locations`

`fm_asset_maintenance_history`

`fm_assets`

`fm_fleet_drivers`

`fm_fleet_fuel_logs`

`fm_fleet_maintenance_schedules`

`fm_fleet_mileage_logs`

`fm_fleet_reservations`

`fm_fleet_trips`

`fm_fleet_vehicles`

`fm_inventory_item_locations`

`fm_inventory_items`

`fm_inventory_locations`

`fm_inventory_transactions`

`fm_module_settings`

`fm_pm_completions`

`fm_pm_schedules`

`fm_pm_template_checklist_items`

`fm_pm_template_materials`

`fm_pm_templates`

`fm_vendor_certifications`

`fm_vendor_sites`

`fm_vendor_work_orders`

`fm_vendors`

`fm_work_order_attachments`

`fm_work_order_history`

`fm_work_order_materials`

`fm_work_orders`