Buildd From Ideas · RMS Pipeline

🔄 Data Flow — End to End

📊 Google Sheets

3 sheets / tabs
Comp data + Property Master

→

🐍 Python ETL

✅ OAuth2 live
comp_prices daily via GitHub Actions
property_master on-demand

→

🗄️ ETL Tables (9)

✅ Raw → Clean → History
291,563 history rows

→

🧠 Feature Store

Unified feature vectors
per property × date × run

🏨 PMS Systems

Choice Hotels, Wyndham
Scaling to 500+ properties

→

🤖 Scraper Bots

Engineer's system
OTP + auto-login

→

🗄️ App Tables (21)

Live operational data
700 pricing records

→

🧠 Feature Store

Unified feature vectors
per property × date × run

📈 CoStar STR

Monthly STAR Excel files
7 hotels × Live + Static

→

🐍 STR Parser

✅ costar_str.py
Occ/ADR/RevPAR/MPI/ARI/RGI

→

🗄️ STR Tables (4)

str_properties + monthly
+ daily + day-of-week

→

🧠 Feature Store

Unified feature vectors
per property × date × run

↓

🧠 Feature Store

→

⚡ FT-Transformer

Neural Network
+ Rule Engine Ensemble

→

👤 Rev Manager

Human-in-the-Loop
Accept / Override

→

🤖 AI Reasoning

Claude API
Explain + Communicate

→

📤 PMS Push

API or Manual
Price goes live

✅ Pipeline Status — Live as of 2026-04-24

Authentication
OAuth2 Desktop App — larry@buildfromideas.com
Token auto-refreshes. Service account blocked by org policy.

Daily Automation
3 ETL jobs daily via GitHub Actions (7AM Central). 21 comp_prices batches, 12 property_master batches completed.
CoStar STR monthly

Scale Target
500+ properties · 20+ active clients · Growing daily
Universal key: bfi_property_id

Pricing Engine
7-step model designed: Rank Waterfall → Base Price → Heat Signal → Multipliers → Floor/Ceiling
Room category matrix live · FT-Transformer + AI reasoning layer

📥 ETL Pipeline Tables — from Google Sheets 9 TABLES · ✅ LIVE — OAUTH2 CONNECTED · COMP_PRICES DAILY AT 7AM

property_master_raw

Raw copy of Sheet 1 — ✅ 30 rows (2026-04-24). On-demand refresh.

✅ 30 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned row ID
scraped_at	TIMESTAMP	When this row was loaded from Sheets
+ all columns from Property_Master_Raw_AWS tab (dynamic)

active_properties_raw → clean → history

🟢 SOURCE OF TRUTH — Only active hotels (is_active=1). Raw/clean/history pattern. All downstream scripts JOIN to active_properties_clean.

✅ 25 active

Table	Purpose	Notes
active_properties_raw	Unfiltered active rows	Truncated daily
active_properties_clean	Normalized, deduplicated	PRIMARY KEY on property_id
active_properties_history	Daily snapshots	load_batch_id = YYYYMMDD, never overwritten
active_properties	Legacy alias	Synced from clean — all JOINs use this
property_master_history	ALL properties archive	Active + inactive, daily snapshots

property_master_clean

Validated & cleaned version — all properties (active + inactive). Column names normalized to lowercase.

29 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned
scraped_at	TIMESTAMP	Load timestamp
+ cleaned columns (nulls removed, types cast)

comp_set_mapping_raw

Raw copy of COMP_MAPPING tab — which hotels compete with which

119 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned
scraped_at	TIMESTAMP	Load timestamp
+ all columns from COMP_MAPPING tab

comp_set_mapping_clean

Validated & cleaned version of competitor mapping

106 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned
scraped_at	TIMESTAMP	Load timestamp
+ cleaned comp mapping columns

comp_prices_raw

Raw copy of COMP_ALL_PRICES_EXTENDED tab — ✅ 15,725 rows (2026-04-24)

✅ 15,725 rows

Column	Type	Notes
property_id	VARCHAR	bfi_property_id — universal connector
bdc_hotel_id	VARCHAR	Booking.com hotel ID
bdc_property_name	VARCHAR	Competitor hotel name
checkin	VARCHAR	Check-in date
price	VARCHAR	Room price (raw — text from sheet)
currency	VARCHAR	Currency code
+ 23 more columns: rateplan_id, rateplan_name, is_available, no_guests, meal_plan, cancellation_policy, last_scrapped_at_IN_UTC, queueId, rateshopId, roomDescription, roomAmenities, sourceUrl, isPromo, closed, discount, promoName, taxStatus, taxType, taxAmount, isRatePerStay, minStay, optionalExtras, payPlan

comp_prices_clean

Cleaned competitor pricing — ✅ 15,725 rows, prices as NUMERIC, nulls handled

✅ 15,725 rows

Column	Type	Notes
property_id	VARCHAR	bfi_property_id — universal connector
bdc_hotel_id	VARCHAR	Booking.com hotel ID
bdc_property_name	VARCHAR	Competitor hotel name
checkin	VARCHAR	Check-in date
price	NUMERIC	Room price (cleaned — cast to number)
currency	VARCHAR	Currency code
+ 23 more columns (same as raw, with prices/amounts cast to NUMERIC, empty strings → NULL)

comp_prices_history

📸 Daily snapshot archive — one batch per day. Never overwritten. 291,563 rows as of 2026-04-24.

291,563 rows · Grows daily

Column	Type	Notes
load_timestamp	TIMESTAMP	Exact UTC time of snapshot
load_batch_id	VARCHAR	Date-based batch ID (e.g. "20260323") — used for idempotent daily reloads
property_id	VARCHAR	bfi_property_id — universal connector
bdc_hotel_id	VARCHAR	Booking.com hotel ID
bdc_property_name	VARCHAR	Competitor hotel name
checkin	VARCHAR	Check-in date
price	NUMERIC	Room price
currency	VARCHAR	Currency code (e.g. USD)
last_scrapped_at_in_utc	VARCHAR	When price was scraped from source
+ 20 more columns: rateplan, availability, meal plan, cancellation, tax, promo, room details

agg_market_percentiles_by_scrape

Market percentile distribution per scrape run

11,818 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned
scrape_date	DATE	Date of aggregation
+ P10, P25, P50, P75, P90 price percentiles

agg_market_price_statistics_by_scrape

Market-wide price statistics per scrape run

11,818 rows

Column	Type	Notes
id	BIGSERIAL	Auto-assigned
scrape_date	DATE	Date of aggregation
+ avg_price, min_price, max_price, std_dev, count

🏨 Core Business Tables FROM ENGINEER'S APP · LIVE OPERATIONAL DATA

properties

Master list of all 5 hotels. Central table — everything links to this.

5 rows

Column	Type	Notes
id	BIGSERIAL	Primary key — referenced by 5+ tables
uuid	CHAR(36)	Unique ID used in app URLs
property_code	VARCHAR(50)	PA672, CA322, 33517, NA, NA2
hotel_name	VARCHAR(255)	Comfort Inn, Quality Inn, Days Inn, etc.
bfi_property_id	VARCHAR(100)	Build From Ideas internal ID
pms_name / pms_id	VARCHAR	Property Management System identifier
bdc_property_name/id	VARCHAR	Booking.com property name & ID
city / state	VARCHAR	Location
owner_name	VARCHAR(255)	Hotel owner's name

update_records ⭐

THE CORE TABLE. Every pricing decision per hotel per date. This is where the algorithm lives.

700 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
property_id	BIGINT	→ properties.id
scraping_run_id	BIGINT	→ scraping_runs.id (nullable)
record_date	DATE	Date this pricing record applies to
algo_output_price	DECIMAL(10,4)	💡 Algorithm's recommended price
standard_price	DECIMAL(10,4)	Actual price set by revenue manager
competitor_set_avg_price	DECIMAL(10,4)	Average of comp set prices
occupancy_percent	DECIMAL(5,4)	Current occupancy (0-1)
forecasted_occupancy_percent	DECIMAL(5,4)	Predicted occupancy
actual_adr / actual_revpar	DECIMAL(10,2)	Actual ADR & RevPAR for the day
ly_adr / ly_occupancy_percent	DECIMAL	Last year comparisons
mtd_occupancy/adr/revpar	DECIMAL	Month-to-date running totals
otb_revenue / ly_revenue / budget_revenue	DECIMAL(12,2)	Revenue tracking vs LY vs budget
direct/ota/gds/other channel revenue	DECIMAL(10,2)	Revenue by booking channel
mpi / ari / rgi	DECIMAL(5,2)	Market Penetration / Rate / Revenue indices
market_occupancy/adr/revpar	DECIMAL	Market averages for comparison
total_rooms / ooo / otb_rooms / avl_rooms	SMALLINT	Room inventory breakdown
goppar / trevpar / alos	DECIMAL	GOPPAR, TRevPAR, Avg Length of Stay
updated_by_rm	SMALLINT	1 = revenue manager manually overrode algo

weather_snapshots

Intraday pricing intelligence. Multiple snapshots per day (morning/afternoon) showing market movement.

Live data

Column	Type	Notes
id	BIGSERIAL	Primary key
property_id	BIGINT	→ properties.id
snapshot_date	DATE	Date of the snapshot
snapshot_time	TIME	Time taken (e.g., 8:05 AM, 4:15 PM)
snapshot_label	VARCHAR(50)	RC, LC-1, LC-2 (Revenue Check labels)
comp_avg_price	DECIMAL(10,2)	Competitor set average at this moment
adr_ly / adr_ty	DECIMAL(10,2)	ADR Last Year vs This Year
occupancy_ly / occupancy_ty	DECIMAL(5,2)	Occupancy % LY vs TY
forecasted_occupancy_ly/ty	DECIMAL(5,2)	Forecast LY vs TY
revenue_ly / revenue_ty	DECIMAL(12,2)	Revenue LY vs TY
saleable_rooms / ooo / sold_rooms	SMALLINT	Current room availability
rate_to_pmr_for_the_day	DECIMAL(10,2)	Recommended rate to property manager

properties_characteristics_history

Daily room inventory record — how many rooms available/sold/OOO each day

Daily records

Column	Type	Notes
id	BIGSERIAL	Primary key
property_id	BIGINT	→ properties.id
record_date	DATE	Date of this inventory snapshot
physical_capacity	INTEGER	Total rooms in hotel
out_of_order	INTEGER	Rooms unavailable (maintenance, etc.)
on_book	INTEGER	Rooms currently with reservations
saleable_rooms	INTEGER	Rooms available for sale

deleted_properties

Archive of removed hotels with full history snapshot preserved

1 row

Column	Type	Notes
id	BIGSERIAL	Primary key
original_id	BIGINT	Original property ID before deletion
property_code / hotel_name	VARCHAR	Preserved from original
deleted_at	TIMESTAMP	When property was removed
characteristics_data	JSONB	Full history of room data at deletion
update_records_data	JSONB	Full pricing history at deletion

⚙️ PMS Integration & Scraping Tables CHOICE HOTELS · WYNDHAM · AUTOMATED DATA COLLECTION

hotel_pms_platforms

PMS login credentials and config for each connected system (Choice, Wyndham)

2 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
platform_name	VARCHAR(100)	Choice Max, Wyndham
pms_type	VARCHAR(50)	choice \| wyndham
username / email	VARCHAR(255)	Login credentials
password	TEXT	Encrypted password
status	VARCHAR(50)	active \| inactive
last_scraped_at	TIMESTAMP	Last successful data pull
config	JSONB	Extra config (e.g., last 4 digits of card)

property_hotel_pms_platform

Maps which hotel is managed in which PMS system

4 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
property_id	BIGINT	→ properties.id
hotel_pms_platform_id	BIGINT	→ hotel_pms_platforms.id

PA672, CA322, NA2 → Choice Max | 33517 → Wyndham

scraping_runs

Log of every automated data collection job — success, failure, how many records created

115 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
hotel_pms_platform_id	BIGINT	→ hotel_pms_platforms.id
started_at / completed_at	TIMESTAMP	Job timing
status	VARCHAR(50)	running \| completed \| failed
days_scraped	INTEGER	How many dates were pulled
records_created	INTEGER	How many rows added to update_records
error_message	TEXT	Error details if status = failed
metadata	JSONB	Extra scraping context

pms_otp

One-time password (OTP) management for PMS 2-factor login. Choice & Wyndham require OTP.

2 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
pms_type	VARCHAR(50)	choice \| wyndham
otp	VARCHAR(10)	Current OTP code (nullable)
expires_at	TIMESTAMP	OTP expiry time
is_used / requested / is_wrong	SMALLINT	OTP status flags
retry_count	SMALLINT	Failed OTP attempts
path	VARCHAR(500)	Path to Python scraper script

👤 Client Management Tables WHO HAS ACCESS TO WHICH HOTELS

clients

Hotel clients who log in to the RMS app to see their property dashboards

1 row

Column	Type	Notes
id	BIGSERIAL	Primary key
name / email	VARCHAR(255)	Client's name and login email
is_active	SMALLINT	1 = active, 0 = deactivated
invitation_token	VARCHAR(255)	Token for first-time login invite
invitation_sent/accepted_at	TIMESTAMP	Invitation tracking
last_login_at	TIMESTAMP	Most recent login

client_property

Maps which clients can see which hotels. One client can have multiple hotels.

3 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
client_id	BIGINT	→ clients.id
property_id	BIGINT	→ properties.id

roles

User permission roles for the app (admin, revenue_manager, client, etc.)

0 rows (defined)

Column	Type	Notes
id	BIGSERIAL	Primary key
name / display_name	VARCHAR(255)	Role identifier and friendly name
description	TEXT	What this role can do
is_active	SMALLINT	1 = active role

users

Internal system users (admins, revenue managers at Build From Ideas)

2 rows

Column	Type	Notes
id	BIGSERIAL	Primary key
name / email	VARCHAR(255)	Staff member name and login
role	VARCHAR(255)	user \| admin
password	VARCHAR(255)	Hashed (bcrypt)

📈 CoStar STR Tables — Monthly STAR Reports 4 TABLES · ✅ PARSER BUILT · 7 HOTELS × LIVE + STATIC

str_properties

Hotel registry: STR ID, name, owner, owner_group, bfi_property_id link

7 rows

Column	Type	Notes
str_id	INTEGER	CoStar STR property ID (Primary Key)
property_name	VARCHAR	Hotel name from CoStar
owner_name	VARCHAR	Hotel owner (extracted from Comp sheet)
owner_group	VARCHAR	Portfolio grouping for dashboard rollup
bfi_property_id	VARCHAR	→ Universal connector to all other tables
market_name / class	VARCHAR	CoStar market classification

str_monthly_performance

18 trailing months × 7 hotels: Occ / ADR / RevPAR + MPI / ARI / RGI + ranks

~252 rows

Column	Type	Notes
str_id	INTEGER	→ str_properties.str_id
year / month	INTEGER	Report period
report_type	VARCHAR	live \| static
occ_my / occ_comp / occ_index	NUMERIC	Occupancy: My Property, Comp Set, MPI
adr_my / adr_comp / adr_index	NUMERIC	ADR: My Property, Comp Set, ARI
revpar_my / revpar_comp / revpar_index	NUMERIC	RevPAR: My Property, Comp Set, RGI
occ_rank / adr_rank / revpar_rank	INTEGER	Rank within competitive set

str_daily_performance

Daily Occ / ADR / RevPAR for report month — per-day detail

~392 rows

Column	Type	Notes
str_id	INTEGER	→ str_properties.str_id
record_date	DATE	Specific day in report month
occ_my / occ_comp / occ_index	NUMERIC	Daily occupancy metrics
adr_my / adr_comp / adr_index	NUMERIC	Daily ADR metrics
revpar_my / revpar_comp / revpar_index	NUMERIC	Daily RevPAR metrics

str_dow_performance

Day-of-week aggregations: Sun-Sat × 4 time periods × 7 hotels

~392 rows

Column	Type	Notes
str_id	INTEGER	→ str_properties.str_id
day_of_week	VARCHAR	Sunday through Saturday
time_period	VARCHAR	Current Month, 3-Month, Running 12, etc.
occ_my / occ_comp / occ_index	NUMERIC	DOW occupancy metrics
adr_my / adr_comp / adr_index	NUMERIC	DOW ADR metrics
revpar_my / revpar_comp / revpar_index	NUMERIC	DOW RevPAR metrics

🧠 Pricing Engine Tables — AI + Human-in-the-Loop NEW · DESIGNED 2026-04-01 · CREATED IN DB — Session 6

property_pricing_config

Per-property algorithm configuration: anchor percentile, quality rank, floors/ceilings, ensemble weights

1 per property

Column	Type	Notes
bfi_property_id	VARCHAR	Primary Key — universal connector
quality_rank	INTEGER	Rank within compset (e.g. 3 of 10)
compset_size	INTEGER	Total competitors in set
default_anchor_pctl	VARCHAR	Default comparison percentile (e.g. p30)
min_anchor_pctl	VARCHAR	Floor when competitors sell out
max_anchor_pctl	VARCHAR	Ceiling when competitors sell out
pricing_algo	VARCHAR	AI_comp_anchor, rule_only, etc.
delivery_mode	VARCHAR	api_push \| manual_input
ensemble_rule_weight	NUMERIC	Current rule engine weight (0-1)
ensemble_nn_weight	NUMERIC	Current FT-Transformer weight (0-1)

pricing_recommendations

Every pricing recommendation from the model — the audit trail. One row per property × date × run.

Grows 4x/day

Column	Type	Notes
id	BIGSERIAL	Primary key
bfi_property_id	VARCHAR	→ property_pricing_config
stay_date	DATE	Date this recommendation is for
run_timestamp	TIMESTAMP	When this pricing run executed
rule_engine_price	NUMERIC	Rule-based engine output
nn_price	NUMERIC	FT-Transformer output
ensemble_price	NUMERIC	Final blended recommendation
adjustment_factor	NUMERIC	Multiplier vs current rate
anchor_price_used	NUMERIC	Compset percentile anchor
anchor_pctl_used	VARCHAR	Which percentile was selected
pct_compset_sold_out	NUMERIC	% of competitors sold out
top_features_json	JSONB	SHAP values — top 5 feature attributions
explanation_text	TEXT	AI-generated plain English explanation

pricing_overrides

Every rev manager override with reason. The human feedback loop — this is how the model learns.

Training signal

Column	Type	Notes
id	BIGSERIAL	Primary key
recommendation_id	BIGINT	→ pricing_recommendations.id
bfi_property_id	VARCHAR	Property
stay_date	DATE	Date affected
algo_recommended_price	NUMERIC	What the model said
manager_set_price	NUMERIC	What the manager chose
override_reason	VARCHAR	rate_too_high, rate_too_low, owner_directive, event, stale_comp, other
override_note	TEXT	Free-text explanation
manager_id	VARCHAR	Who overrode (aaryan, roshan, etc.)
override_timestamp	TIMESTAMP	When the override happened

pricing_outcomes

Next-day reconciliation: actual RevPAR vs model recommendation vs manager override. The truth table.

Daily backfill

Column	Type	Notes
id	BIGSERIAL	Primary key
bfi_property_id	VARCHAR	Property
stay_date	DATE	The date that passed
algo_recommended_price	NUMERIC	What algo said
final_published_price	NUMERIC	What actually went live
was_overridden	BOOLEAN	Did manager change it?
actual_revpar	NUMERIC	Real RevPAR outcome
actual_occ	NUMERIC	Real occupancy
actual_adr	NUMERIC	Real ADR
algo_would_have_revpar	NUMERIC	Estimated RevPAR if algo price was used
override_improved_revpar	BOOLEAN	Did manager's override beat algo?

🔧 System / Infrastructure Tables (Laravel Framework) 7 TABLES · APP PLUMBING — NOT BUSINESS DATA

cache & cache_locks

Laravel application cache for fast page loads

Empty

Column	Type	Notes
"key"	VARCHAR(255)	Cache key (quoted — reserved word in PG)
value / owner	TEXT	Cached data / lock owner
expiration	INTEGER	Unix timestamp when cache expires

sessions

Active user sessions (who is logged in right now)

3 rows

Column	Type	Notes
id	VARCHAR(255)	Session token (Primary Key)
user_id	BIGINT	Logged-in user (nullable = anonymous)
ip_address	VARCHAR(45)	Client IP
payload	TEXT	Base64-encoded session data
last_activity	INTEGER	Unix timestamp

jobs, job_batches & failed_jobs

Laravel queue system — background task processing

Empty

Column	Type	Notes
id / uuid	BIGSERIAL/VARCHAR	Job identifier
queue	VARCHAR(255)	Which queue (default, emails, etc.)
payload	TEXT	Serialized job data
attempts	SMALLINT	How many times it tried to run

migrations & password_reset_tokens

Schema version tracking & password reset tokens

41 / 0

Column	Type	Notes
id / migration	SERIAL/VARCHAR	41 database migrations tracked
batch	INTEGER	Deployment batch number (1-28)
email / token	VARCHAR(255)	Password reset token storage

🔗 App Table Relationships (Engineer's 21 Tables)

properties → update_records

update_records.property_id → properties.id
Every pricing record belongs to one hotel. 700 rows across 5 properties (IDs 2–6).

properties → weather_snapshots

weather_snapshots.property_id → properties.id
Intraday pricing snapshots (morning/afternoon revenue checks) per hotel.

properties → properties_characteristics_history

properties_characteristics_history.property_id → properties.id
Daily room inventory records per property.

hotel_pms_platforms → scraping_runs

scraping_runs.hotel_pms_platform_id → hotel_pms_platforms.id
Each scraping job is linked to the PMS system it pulled data from.

scraping_runs → update_records

update_records.scraping_run_id → scraping_runs.id
Pricing records created by a scraper are linked to the run that created them.

properties ↔ hotel_pms_platforms

Via property_hotel_pms_platform join table.
PA672/CA322/NA2 → Choice Max (id=3). Days Inn (33517) → Wyndham (id=4).

clients ↔ properties

Via client_property join table.
1 client currently has access to 3 hotels.

🌉 Cross-Dataset Connections — How ETL & App Tables Join

⚠️ Architectural Decision: What is the Universal Property Identifier?

properties.id (integers 2–6) is a meaningless serial number — it only exists inside the engineer's system and has no value across datasets.

bfi_property_id is the true universal identifier (e.g. US_76126_001). It is the same field used in your ETL / comp mapping data as property_id, and it uniquely identifies each hotel across both systems.

Currently bfi_property_id only lives in the properties table. All child tables in the engineer's system (update_records, weather_snapshots, etc.) only store the integer property_id. This means every cross-dataset join must route through properties as a lookup bridge.

      -- Canonical join path: engineer data ↔ ETL data

      update_records ur

        JOIN properties p ON ur.property_id = p.id

        JOIN comp_set_mapping_clean csm ON p.bfi_property_id = csm.property_id

        JOIN comp_prices_clean cp ON csm.property_id = cp.property_id AND ur.record_date = cp.price_date

      -- bfi_property_id is the bridge between the two worlds

Future option (Phase 2, with engineer agreement): Add bfi_property_id directly as a column to update_records, weather_snapshots, and properties_characteristics_history. This would make every table directly joinable without needing the lookup, and make bfi_property_id the true primary connector across all 30 tables.

The two datasets — your ETL pipeline data (from Google Sheets) and the engineer's app data (from PMS scrapers) — are linked through the properties table using bfi_property_id as the universal bridge key.

Hotel	properties.id (used in update_records, etc.)	properties.bfi_property_id = comp_set_mapping.property_id	properties.bdc_property_id = comp_set_mapping.Agg_int_unique_id	PMS System
Comfort Inn PA672	2	us_19464_001	null — not on Booking.com	Choice Max
Quality Inn CA322	3	US_76126_001	171693	Choice Max
City Center Inn NA	4	US_23601_001	306176	Choice Max
Days Inn Gilroy 33517	5	US_95020_002	259410	Wyndham
Quality Inn Mobile NA2	6	US_36619_001	179470	Choice Max

🔑 Primary ID Bridge

properties.bfi_property_id = comp_set_mapping_clean.property_id

This links each hotel's live operational data (occupancy, ADR, RevPAR from PMS) to its competitor set (which hotels to track) defined in your Google Sheet.

🔑 Booking.com ID Bridge

properties.bdc_property_id = comp_set_mapping_clean.Agg_int_unique_id

Links each managed hotel to its Booking.com listing ID. Used to match comp pricing data scraped from BDC against your own hotel's BDC page. Note: PA672 has no BDC ID.

📊 The Pricing Algorithm Join (Phase 2)

The full join that powers pricing recommendations:

update_records (hotel's own ADR, occupancy, RevPAR, OTB rooms)
+ comp_prices_clean (what competitors are charging)
+ properties_characteristics_history (room inventory)
+ weather_snapshots (intraday market signals)

All joined via properties.id / bfi_property_id / date.

⚠️ Data Overlap Note

Both datasets contain competitor pricing fields. update_records.competitor_set_avg_price is populated by the PMS scraper. comp_prices_clean is your manually-tracked comp data from Google Sheets.

These are complementary, not duplicates — the scraper catches live rate changes; your Sheets data holds a curated historical record. The algorithm should use both.

📤 Live Google Sheet Outputs — Session 7 LIVE · OAUTH2 WRITE · SHARED WITH LARRY@BUILDFROMIDEAS.COM

Room Category Mapping Matrix

One tab per market. Rows = room categories, Cols = competitor hotels. Exact matches only.

18 markets

Feature	Detail
Script	run_room_category_matrix.py
Layout	Category \| Our Hotel \| Competitor 1 \| Competitor 2 \| ...
Matching	Exact category only (score=100). No adjacent/fuzzy.
Classification	Hierarchy-based with word-boundary regex + modifier stripping
Purpose	RM review before pricing engine goes live

Comp Mapping Output Sheet

3 tabs: Full Mapping, Unique Markets, Market Stats. Auto-refreshed after comp_mapping ETL.

Session 6

Feature	Detail
Script	run_comp_mapping_to_sheets.py
Source	comp_set_mapping_clean (from Compset_mapping tab)
Auto-trigger	Runs after etl/comp_mapping.py completes

RMS Pipeline — Database Architecture

🔄 Data Flow — End to End

✅ Pipeline Status — Live as of 2026-04-24

property_master_raw

active_properties_raw → clean → history

property_master_clean

comp_set_mapping_raw

comp_set_mapping_clean

comp_prices_raw

comp_prices_clean

comp_prices_history

agg_market_percentiles_by_scrape

agg_market_price_statistics_by_scrape

properties

update_records ⭐

weather_snapshots

properties_characteristics_history

deleted_properties

hotel_pms_platforms

property_hotel_pms_platform

scraping_runs

pms_otp

clients

client_property

roles

users

str_properties

str_monthly_performance

str_daily_performance

str_dow_performance

property_pricing_config

pricing_recommendations

pricing_overrides

pricing_outcomes

cache & cache_locks

sessions

jobs, job_batches & failed_jobs

migrations & password_reset_tokens

🔗 App Table Relationships (Engineer's 21 Tables)

properties → update_records

properties → weather_snapshots

properties → properties_characteristics_history

hotel_pms_platforms → scraping_runs

scraping_runs → update_records

properties ↔ hotel_pms_platforms

clients ↔ properties

🌉 Cross-Dataset Connections — How ETL & App Tables Join

🔑 Primary ID Bridge

🔑 Booking.com ID Bridge

📊 The Pricing Algorithm Join (Phase 2)

⚠️ Data Overlap Note

Room Category Mapping Matrix

Comp Mapping Output Sheet