Database

Dual Database Setup

The API connects to two MySQL databases:

Main database ($link) - Core application data (users, houses, viewings, perks, etc.)
Bills database ($billsLink) - UK-only bills data (quotes, orders, meters, etc.)

The bills database connection is only established when $USA is falsy (i.e., UK mode).

Connection Setup

From api/include/configdb.php:


$link = db_connect();  // Main database - always connected
 
if (!(array_key_exists('USA', $_ENV) && json_decode($_ENV['USA']))) {
    $billsLink = db_connect($_ENV['DB_DATABASE_BILLS']);  // Bills DB - UK only
}

The db_connect() function uses mysqli with retry logic (3 attempts, 5-second timeout, 1-second delay between retries).

Database Names by Environment

Environment	Main DB	Bills DB
UK Dev	`housr_database_dev`	`housr_bills_dev`
UK UAT	`housr_database_uat`	`housr_bills_test`
UK Prod	`housr_database_prod`	`housr_bills_prod`
US Dev	`housr_database_us_dev`	N/A
US UAT	`housr_database_us_uat`	N/A
US Prod	`housr_database_us_prod`	N/A
Demo	`housr_database_demo_app`	`housr_bills_test`

Database Servers

UK (UAT/Prod): Aurora MySQL cluster in eu-west-2 (London)
US/Dev: RDS instance in us-east-1 / us-east-2

Query Patterns

Direct mysqli (most common)

Most endpoints use raw mysqli_query() calls:


$sql = "SELECT * FROM users WHERE id = '$uuid'";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_assoc($result);

Prepared statements (newer code)

Some endpoints use prepared statements:


$sql = "SELECT * FROM houses WHERE city = ? AND status = 1";
$statement = mysqli_prepare($link, $sql);
mysqli_stmt_bind_param($statement, 's', $city);
mysqli_stmt_execute($statement);
$result = mysqli_stmt_get_result($statement);

Utility functions

api/utils/dbFunctions.php provides reusable query helpers:


findById($link, 'users', $userId);           // SELECT * WHERE id = ?
findByColumn($link, 'users', 'email', $email); // SELECT * WHERE column = ?
findAllByColumn($link, 'houses', 'city', $city);
findByCondition($link, 'contracts', "user_id=$id AND status='active'");
updateValue($link, 'users', 'city', 'London', "id=$userId");
deleteByCondition($link, 'liked_houses', "tenant_id=$userId AND house_id=$houseId");

Key Tables (Main Database)

Users & Auth

Table	Purpose
`users`	Core user table (students, agents/landlords, admins). `role` field: 1=admin, 3=student
`user_sessions`	Login session tracking
`api_keys`	API keys for external access
`feature_flags`	Feature flag toggles (`feature`, `enabled`)

Properties

Table	Purpose
`houses`	Property listings (title, address, lat/lng, bedrooms, price_pw/price_pm, images JSON, agent_id, city, status)
`liked_houses`	User likes (tenant_id, house_id)
`houses_viewed`	View tracking for trending calculation
`houses_housr_plus`	Housr Plus premium listings
`boost_credits`	Boost credit system for property promotion
`house_image_rooms`	Room labels for property images
`house_lease_terms`	US lease terms by academic year
`building_details`	US building info (title, logo, cover image)
`property_operators`	US property management companies
`super_agents`	Super agent designation
`video_tours`	Video tour links for properties
`uob_approved_agents`	UoB-approved letting agents (Birmingham)

Viewings

Table	Purpose
`viewing_requests`	Viewing/tour requests (user_id, house_id, time, status, slot_id)
`viewing_slots`	Agent-defined viewing time slots
`viewing_ratings`	Post-viewing ratings
`viewing_request_rewards`	Viewing reward codes per city
`bookable_viewings`	Agents with bookable viewing slots

Chat

Table	Purpose
`chat_rooms`	Chat rooms (name, house_id, status)
`chat_room_members`	Room membership (room_id, user_id)
`chat_room_moderation`	US moderation records

Friends & Groups

Table	Purpose
`friends`	Friend relationships (sender_id, receiver_id, status)
`housemate_groups`	Housemate group membership (group_id, user_id, status)
`user_house_filters`	Saved house search filters per user

Perks

Table	Purpose
`perks`	Perk listings (company, deal, type, city, category, etc.)
`perks_redeemed`	Perk redemption records
`perk_categories`	Perk category definitions
`perk_codes`	Unique perk codes (perk_id, user_id, code)
`perk_tokens`	Wallet tokens (perk_id, user_id, redeemed_at)
`perk_exclusives`	Exclusive perk access (perk_id, exclusiveable_type, exclusiveable_id)
`perk_partners`	Perk partner businesses (subscription_tier)
`perks_ref`	Referral code tracking

Roomie

Table	Purpose
`roomie_profiles`	Roomie profiles (role, personality, study_year, smoker, preferences)
`roomie_blocked_users`	Block list

Contracts & Bills

Table	Purpose
`contracts`	Tenancy contracts
`contract_members`	Contract member assignments
`huddle_contracts`	Huddle (bills provider) contract links

Notifications

Table	Purpose
`scheduled_notifications`	Queued push notifications
`portal_notifications`	Portal (landlord) notifications

Moderation (US)

Table	Purpose
`ratings_moderation`	Moderation ratings
`ratings_periods`	Rating period windows
`ratings_students`	Student-to-student ratings
`ratings_users`	User rating aggregates

Bills Database (UK Only)

Table	Purpose
`bills_included_prices`	Bills pricing by city and bedrooms
`bills_included_agents`	Agents with bills included (which utilities)
Various Huddle tables	Bills order, payment, and meter data

Charset

Most endpoints set charset before queries:


mysqli_set_charset($link, "utf8mb4");
// or
mysqli_set_charset($link, "utf8");