M3 Database Schema

The M3 database stores inventory items, locations, stock movements, and alerting. It is a PostgreSQL schema generated from Prisma migrations.

Core Tables

Table	Purpose	Notes
properties	Inventory properties (villas)	Top-level grouping
locations	Hierarchical storage locations	Supports parent-child locations
zones	Grouping of locations	Optional grouping layer
item_categories	Category tree for inventory items	Self-referencing parent-child
inventory_items	Core inventory item records	Links to property, location, category
item_photos	Item photo metadata	Linked to inventory_items
stock_movements	Audit trail of stock changes	Linked to inventory_items
linen_types	Linen type definitions	Used by linen tracking
linen_inventory	Linen item tracking	Linked to items and types
inventory_alerts	Low stock and condition alerts	Linked to items and properties
inventory_snapshots	Periodic stock snapshots	Linked to properties
consumable_types	Consumable item types	Used by consumables tracking
consumable_items	Consumable item records	Linked to types and properties
item_templates	Template definitions	For bulk item creation
template_items	Items within a template	Linked to item_templates
audit_log	System audit trail	Records changes and actions

Key Relationships

• locations.propertyId → properties.id
• locations.parentId → locations.id
• zones.locationId → locations.id
• item_categories.parentId → item_categories.id
• inventory_items.propertyId → properties.id
• inventory_items.locationId → locations.id
• inventory_items.zoneId → zones.id
• inventory_items.categoryId → item_categories.id
• item_photos.itemId → inventory_items.id
• stock_movements.itemId → inventory_items.id
• stock_movements.fromLocationId → locations.id
• stock_movements.toLocationId → locations.id
• linen_inventory.itemId → inventory_items.id
• linen_inventory.linenTypeId → linen_types.id
• inventory_alerts.itemId → inventory_items.id
• inventory_alerts.propertyId → properties.id
• inventory_snapshots.propertyId → properties.id
• consumable_items.propertyId → properties.id
• consumable_items.consumableTypeId → consumable_types.id
• template_items.templateId → item_templates.id

Enums

Enum	Values
ItemCategory	equipment, consumable, linen, operational, decor, safety, signage, kitchenware, tableware, bathroom, bedroom, outdoor, electronics, furniture
ItemCondition	new, excellent, good, fair, needs_repair, needs_replacement
LocationType	building, floor, room, area, zone_group, other
StockMovementType	initial, purchase, return_item, use, damage, loss, transfer_in, transfer_out, adjustment
AlertStatus	active, acknowledged, resolved

Notes

• inventory_items is the central table; most inventory data links to it directly.
• Location hierarchy uses a self-referencing locations.parentId for nesting.

Source

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