M2 Database Schema

The M2 database supports maintenance operations, work orders, vendors, emergencies, and purchasing. It is a PostgreSQL schema generated from Prisma migrations.

Core Tables

Area	Tables
Identity	`users`
Properties	`villas`, `villa_locations`, `villa_tags`, `villa_documents`, `villa_inspections`, `utility_meters`, `utility_readings`
Assets	`assets`, `maintenance_logs`
Work Orders	`work_orders`, `work_order_comments`, `attachments`, `work_order_tasks`, `work_order_activities`, `work_order_additional_costs`
Vendors	`vendors`, `vendor_pricing`, `vendor_performance_logs`, `service_contracts`
Emergencies	`emergency_incidents`, `emergency_action_logs`, `response_teams`, `team_members`, `emergency_contacts`
Preventive Maintenance	`pmp_templates`, `pmp_schedules`, `checklists`, `checklist_items`, `work_order_checklists`, `work_order_checklist_items`
Field Ops	`field_notes`, `timesheets`
Inventory	`parts`, `inventory_stock`, `inventory_transactions`, `work_order_parts`
Purchasing	`purchase_orders`, `purchase_order_items`, `po_attachments`
Requests	`requests`, `request_comments`, `request_attachments`
Teams	`work_order_teams`, `work_order_team_members`
Zones	`zones`

Key Relationships

villas.zoneId → zones.id
assets.villaId → villas.id
maintenance_logs.assetId → assets.id
work_orders.villaId → villas.id
work_orders.assetId → assets.id
work_orders.assignedToId → users.id
work_orders.reportedById → users.id
work_orders.vendorId → vendors.id
work_orders.requestId → requests.id
work_orders.locationId → villa_locations.id
work_orders.teamId → work_order_teams.id
work_order_comments.workOrderId → work_orders.id
attachments.workOrderId → work_orders.id
vendor_performance_logs.vendorId → vendors.id
service_contracts.vendorId → vendors.id
vendor_pricing.vendorId → vendors.id
emergency_incidents.villaId → villas.id
emergency_incidents.reportedById → users.id
emergency_incidents.assignedTeamId → response_teams.id
emergency_action_logs.incidentId → emergency_incidents.id
team_members.teamId → response_teams.id
pmp_schedules.templateId → pmp_templates.id
field_notes.authorId → users.id
timesheets.userId → users.id
checklist_items.checklistId → checklists.id
inventory_stock.partId → parts.id
inventory_transactions.inventoryId → inventory_stock.id
purchase_orders.supplierId → vendors.id
purchase_orders.requestedById → users.id
purchase_orders.approvedById → users.id
purchase_order_items.purchaseOrderId → purchase_orders.id
purchase_order_items.partId → parts.id
po_attachments.purchaseOrderId → purchase_orders.id
requests.requestedById → users.id
requests.assignedToId → users.id
requests.approvedById → users.id
requests.villaId → villas.id
request_comments.requestId → requests.id
request_attachments.requestId → requests.id
work_order_parts.workOrderId → work_orders.id
work_order_parts.partId → parts.id
work_order_checklists.workOrderId → work_orders.id
work_order_checklist_items.checklistId → work_order_checklists.id
work_order_team_members.teamId → work_order_teams.id
work_order_team_members.userId → users.id
villa_locations.villaId → villas.id
work_order_tasks.workOrderId → work_orders.id
work_order_tasks.assignedToId → users.id
work_order_additional_costs.workOrderId → work_orders.id
work_order_activities.workOrderId → work_orders.id
villa_documents.villaId → villas.id
villa_inspections.villaId → villas.id
utility_meters.villaId → villas.id
utility_readings.meterId → utility_meters.id
villa_tags.villaId → villas.id

Enums

Enum	Values
`UserRole`	`ADMIN`, `MANAGER`, `TECHNICIAN`, `VENDOR`
`StaffAvailability`	`AVAILABLE`, `ON_ASSIGNMENT`, `OFF_DUTY`, `ON_LEAVE`, `UNAVAILABLE`
`VillaStatus`	`OCCUPIED`, `VACANT`, `UNDER_RENOVATION`
`AssetCategory`	`HVAC`, `PLUMBING`, `ELECTRICAL`, `APPLIANCE`, `STRUCTURAL`, `POOL`, `LANDSCAPE`, `SECURITY`, `OTHER`
`AssetStatus`	`OPERATIONAL`, `NEEDS_MAINTENANCE`, `UNDER_REPAIR`, `DECOMMISSIONED`
`WorkOrderCategory`	`PLUMBING`, `ELECTRICAL`, `HVAC`, `STRUCTURAL`, `LANDSCAPING`, `POOL`, `APPLIANCE`, `CLEANING`, `PEST_CONTROL`, `OTHER`
`WorkOrderStatus`	`PENDING`, `IN_PROGRESS`, `ON_HOLD`, `COMPLETED`, `CANCELLED`
`Priority`	`LOW`, `MEDIUM`, `HIGH`, `URGENT`
`VendorCategory`	`PLUMBING`, `ELECTRICAL`, `HVAC`, `LANDSCAPING`, `POOL`, `GENERAL`, `SECURITY`, `CLEANING`, `PEST_CONTROL`
`VendorStatus`	`ACTIVE`, `INACTIVE`, `SUSPENDED`, `PENDING`
`ContractStatus`	`ACTIVE`, `EXPIRED`, `CANCELLED`, `PENDING_RENEWAL`
`IncidentCategory`	`FIRE`, `FLOOD`, `MEDICAL`, `SECURITY`, `POWER_OUTAGE`, `GAS_LEAK`, `STRUCTURAL`, `UTILITY`, `OTHER`
`IncidentSeverity`	`LOW`, `MEDIUM`, `HIGH`, `CRITICAL`
`IncidentStatus`	`REPORTED`, `DISPATCHED`, `IN_PROGRESS`, `RESOLVED`, `CLOSED`
`TeamRole`	`LEAD`, `TECHNICIAN`, `SUPPORT`
`PMPFrequency`	`DAILY`, `WEEKLY`, `BIWEEKLY`, `MONTHLY`, `QUARTERLY`, `SEMI_ANNUAL`, `ANNUAL`
`ZoneStatus`	`ACTIVE`, `MAINTENANCE`, `INACTIVE`
`PMPStatus`	`SCHEDULED`, `IN_PROGRESS`, `COMPLETED`, `OVERDUE`, `SKIPPED`
`NoteType`	`TEXT`, `VOICE`, `PHOTO`
`TimesheetStatus`	`ACTIVE`, `COMPLETED`, `APPROVED`
`ChecklistStatus`	`PENDING`, `IN_PROGRESS`, `COMPLETED`
`PartCategory`	`HVAC_PARTS`, `PLUMBING_PARTS`, `ELECTRICAL_PARTS`, `POOL_SUPPLIES`, `CLEANING_SUPPLIES`, `LANDSCAPING_SUPPLIES`, `APPLIANCE_PARTS`, `STRUCTURAL_MATERIALS`, `TOOLS`, `SAFETY_EQUIPMENT`, `OTHER`
`TransactionType`	`IN`, `OUT`, `ADJUSTMENT`, `TRANSFER`
`POStatus`	`DRAFT`, `PENDING_APPROVAL`, `APPROVED`, `ORDERED`, `PARTIALLY_RECEIVED`, `RECEIVED`, `CANCELLED`, `CLOSED`
`RequestType`	`MAINTENANCE`, `REPAIR`, `INSPECTION`, `IMPROVEMENT`, `PURCHASE`, `OTHER`
`RequestCategory`	`PLUMBING`, `ELECTRICAL`, `HVAC`, `STRUCTURAL`, `LANDSCAPING`, `POOL`, `APPLIANCE`, `CLEANING`, `PEST_CONTROL`, `SUPPLIES`, `EQUIPMENT`, `OTHER`
`RequestStatus`	`PENDING`, `UNDER_REVIEW`, `APPROVED`, `REJECTED`, `IN_PROGRESS`, `COMPLETED`, `CANCELLED`
`TaskStatus`	`PENDING`, `IN_PROGRESS`, `COMPLETED`, `BLOCKED`
`AssetCondition`	`EXCELLENT`, `GOOD`, `FAIR`, `POOR`, `CRITICAL`
`VillaDocumentType`	`FLOOR_PLAN`, `INSURANCE`, `WARRANTY`, `PERMIT`, `PHOTO`, `OTHER`
`InspectionStatus`	`PENDING`, `COMPLETED`, `OVERDUE`, `CANCELLED`
`UtilityType`	`ELECTRICITY`, `WATER`, `GAS`, `HEATING`, `COOLING`

Notes

Work orders are the central object, linking to villas, assets, vendors, teams, parts, and checklists.
Purchasing and inventory data are tied to work orders to enable cost tracking and replenishment.

Source

Schema extracted from D:\Development\m2-main\db_export\m2.sql.