Database Schema

All PHP backends (API v1, API v2, Portal) and Property Feeds share a single MySQL database. The Webapp has read-only access for search queries.

Shared Database Architecture

The shared database is the backbone of Housr. Every service reads from and writes to the same tables, which ensures data consistency but requires careful coordination for schema changes.

Who connects to the database:

Service	Access	ORM	Notes
API v1	Read/Write	Eloquent	Primary data owner for legacy features
API v2	Read/Write	Eloquent	Module-specific models and migrations
Portal	Read/Write	Eloquent	135+ models, full admin access
Property Feeds	Write	Eloquent	Imports property data from external sources
Webapp	Read-only	mysql2 (raw)	Direct queries for search performance

Key Tables

The database covers the full Housr domain. Major table groups include:

Users and Auth

users — All user accounts (students, agents, landlords, admins)
User profiles, preferences, and settings

Properties

houses — Property listings
rooms — Individual rooms within properties
house_images, room_images — Media
Property amenities, descriptions, pricing

Bookings and Viewings

viewings — Scheduled property viewings
Booking requests and confirmations

Communication

chats — Chat threads
chat_messages — Individual messages (synced with Firebase Realtime DB)

Bills

bills — Bill records
Bill payments, providers, schedules

Perks and Events

perks — Perk/discount offers
events — Events listings

CRM and Leads

Enquiries, lead tracking
HubSpot sync records

Migrations

Migrations live in multiple places:

API v1: database/migrations/ — Legacy migrations, the original schema
API v2: Modules/*/Database/Migrations/ — Module-specific migrations for new features
Portal: database/migrations/ — Portal-specific tables (Filament settings, admin features)

When adding a new table or column:

If it’s for a new API v2 feature, put the migration in the relevant API v2 module
If it’s for Portal-only functionality, put it in Portal
Never add new migrations to API v1

Webapp Direct DB Access

The Webapp connects directly to MySQL (via mysql2/promise) for search queries. This bypasses the API layer for performance reasons.

Connection pool configured in src/lib/db.ts
Search queries in src/features/search/actions/
Results cached with unstable_cache
Read-only — the Webapp never writes to the database

Schema Change Guidelines

Because multiple services share the database:

Test migrations against all consumers — A column rename or removal can break API v1, API v2, Portal, or the Webapp’s search queries
Use additive changes when possible — Add new columns as nullable, add new tables freely
Coordinate destructive changes — Removing columns, changing types, or renaming requires updating all affected repos first
Run migrations from one place — Typically API v2 for new features, but ensure the migration runs in the shared database