M4 Database Schema

The M4 database stores reporting data synced from Hostify, including listings, reservations, guests, and transactions. It is a PostgreSQL schema managed via Drizzle.

Core Tables

Table	Purpose	Notes
guests	Guest profiles synced from Hostify	Linked to reservations by IDs
listings	Property/listing records	Hostify listing metadata
reservations	Booking records	Primary reporting entity
reservation_custom_fields	Custom fields per reservation	Linked to reservations
reservation_fees	Fee breakdown per reservation	Linked to reservations
transactions	Financial transactions	Linked to reservations by hostify ID
transaction_tags	Tags for transactions	Linked to transactions
users	Internal user accounts for reporting	Uses user_role enum

Key Relationships

• reservation_custom_fields.reservation_id → reservations.id
• reservation_fees.reservation_id → reservations.id
• transaction_tags.transaction_id → transactions.id
• transactions.reservation_hostify_id → reservations.hostify_id

Enums

Enum	Values
user_role	owner, admin, finance, operations

Notes

• Reservations and transactions are linked by reservations.hostify_id rather than the internal reservation id.
• reservation_custom_fields and reservation_fees extend reservations without bloating the core table.

Source

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