Database Schema for RealtyAsset, SaleListing, and RentalListing in PropertyWebBuilder

The PropertyWebBuilder platform implements a normalized three-table schema where pwb_realty_assets stores immutable property data, while pwb_sale_listings and pwb_rental_listings track transaction-specific details, enforced by database-level unique partial indexes that guarantee only one active listing per asset.

This architecture separates physical property attributes from market-specific metadata, enabling a single realty asset to host multiple historical or future listings without data duplication. In the etewiah/property_web_builder repository, this schema is defined in the CreateNormalizedPropertyTables migration and backed by ActiveRecord models that handle monetary values, internationalization, and multi-tenant scoping.

Schema Overview and Migration

The entire database structure is created by a single migration located at [db/migrate/20251204180440_create_normalized_property_tables.rb](https://github.com/etewiah/property_web_builder/blob/master/db/migrate/20251204180440_create_normalized_property_tables.rb). This migration establishes three core tables using UUID primary keys for distributed-safe identification.

Table	Purpose	Primary Key	Foreign Key Constraints
pwb_realty_assets	Physical property records (address, dimensions, location)	id :uuid	None (root table)
pwb_sale_listings	Sale transaction details and pricing	id :uuid	realty_asset_id → pwb_realty_assets(id)
pwb_rental_listings	Rental transaction details and seasonal pricing	id :uuid	realty_asset_id → pwb_realty_assets(id)

The RealtyAsset Table

The pwb_realty_assets table serves as the immutable source of truth for physical property characteristics. According to the model defined in [app/models/pwb/realty_asset.rb](https://github.com/etewiah/property_web_builder/blob/master/app/models/pwb/realty_asset.rb), this table stores:

• Location data: street_address, city, postal_code, latitude, longitude
• Physical attributes: count_bedrooms, count_bathrooms, plot_area, constructed_area, energy_rating
• Categorization keys: prop_origin_key, prop_state_key, prop_type_key
• Multi-tenant scoping: website_id for isolating data between property websites

The model defines the central relationships that link assets to their listings:

class RealtyAsset < ApplicationRecord
  has_many :sale_listings, class_name: 'Pwb::SaleListing', dependent: :destroy
  has_many :rental_listings, class_name: 'Pwb::RentalListing', dependent: :destroy
end

Using dependent: :destroy ensures that deleting a property automatically cascades to remove all associated sale and rental listings, maintaining referential integrity at the application level.

SaleListing Schema and Model

The pwb_sale_listings table captures all market-specific data for property sales. As implemented in [app/models/pwb/sale_listing.rb](https://github.com/etewiah/property_web_builder/blob/master/app/models/pwb/sale_listing.rb), the schema supports monetized pricing, internationalization, and gamification features.

Key Schema Columns

• Pricing: price_sale_current_cents and price_sale_current_currency (managed by the Money gem via monetize)
• Commission tracking: commission_cents and commission_currency
• Business state: active, visible, highlighted, archived, reserved
• SEO control: noindex boolean to prevent search engine indexing
• Gamification: game_price_cents, game_price_currency, and game_listing_type for price-guessing features
• Internationalization: translations JSONB column storing localized titles, descriptions, and meta tags via the Mobility gem

Model Concerns and Validations

The SaleListing model includes several concerns to enforce business rules:

• ListingStateable: Enforces the constraint that only one sale listing per asset can be active simultaneously
• SeoValidatable: Validates SEO field lengths and formats per locale
• Gameable: Provides accessors for the price-guessing game API
• RefreshesPropertiesView: Triggers materialized view refreshes for search performance

class SaleListing < ApplicationRecord
  include NtfyListingNotifications
  include ListingStateable
  include SeoValidatable
  include RefreshesPropertiesView
  include Gameable
  extend Mobility

  self.table_name = 'pwb_sale_listings'
  belongs_to :realty_asset, class_name: 'Pwb::RealtyAsset'
  
  monetize :price_sale_current_cents, with_model_currency: :price_sale_current_currency
  monetize :commission_cents, with_model_currency: :commission_currency
  
  translates :title, :description, :seo_title, :meta_description
end

RentalListing Schema and Model

The pwb_rental_listings table mirrors the sale listing structure while adding rental-specific fields for seasonal and short-term markets. The implementation in [app/models/pwb/rental_listing.rb](https://github.com/etewiah/property_web_builder/blob/master/app/models/pwb/rental_listing.rb) supports dual rental modes through boolean flags and tiered pricing.

Rental-Specific Columns

• Rental type flags: for_rent_short_term and for_rent_long_term (differentiate vacation rentals from residential leases)
• Base pricing: price_rental_monthly_current_cents with Money gem monetization
• Seasonal pricing: price_rental_monthly_low_season_cents and price_rental_monthly_high_season_cents for dynamic rate management
• Shared fields: Identical SEO, gamification, and state columns as sale listings

Model Scopes and Associations

The model includes the same concerns as SaleListing plus specific scopes for filtering rental types:

class RentalListing < ApplicationRecord
  include NtfyListingNotifications
  include ListingStateable
  include SeoValidatable
  include RefreshesPropertiesView
  include Gameable
  extend Mobility

  self.table_name = 'pwb_rental_listings'
  belongs_to :realty_asset, class_name: 'Pwb::RealtyAsset'

  monetize :price_rental_monthly_current_cents, with_model_currency: :price_rental_monthly_current_currency
  monetize :price_rental_monthly_low_season_cents, with_model_currency: :price_rental_monthly_current_currency
  monetize :price_rental_monthly_high_season_cents, with_model_currency: :price_rental_monthly_current_currency

  scope :for_rent_short_term, -> { where(for_rent_short_term: true) }
  scope :for_rent_long_term, -> { where(for_rent_long_term: true) }

  translates :title, :description, :seo_title, :meta_description
end

Data Integrity Constraints

The schema enforces critical business rules at the database level through partial unique indexes. Both pwb_sale_listings and pwb_rental_listings include an index on (realty_asset_id, active) WHERE active = true that prevents duplicate active listings for the same asset.

This constraint works in conjunction with the ListingStateable concern in [app/models/concerns/listing_stateable.rb](https://github.com/etewiah/property_web_builder/blob/master/app/models/concerns/listing_stateable.rb) to ensure that:

• A property cannot have two simultaneous active sale listings
• A property cannot have two simultaneous active rental listings
• Deactivating one listing allows activation of another

Foreign key constraints in the migration ensure referential integrity, preventing orphaned listings if a realty asset is removed (though the dependent: :destroy configuration in the model makes this unlikely in practice).

Multi-Tenant Architecture

The website_id column on pwb_realty_assets provides the foundation for multi-tenancy. While the base models (Pwb::RealtyAsset, Pwb::SaleListing, Pwb::RentalListing) access all records, tenant-scoped subclasses (e.g., PwbTenant::SaleListing) automatically filter queries by website_id.

This design allows background jobs and administrative scripts to use the unscoped models for cross-tenant analytics, while web requests utilize tenant-scoped variants for data isolation.

Practical Usage Examples

Creating a Property with Sale Listing

# Create the realty asset (physical property)

asset = Pwb::RealtyAsset.create!(
  reference: 'PROP-001',
  street_address: '123 Main St',
  city: 'Springfield',
  count_bedrooms: 3,
  count_bathrooms: 2,
  plot_area: 150.0,
  constructed_area: 140.0,
  website_id: 42
)

# Create active sale listing (enforced unique by database index)

sale = Pwb::SaleListing.create!(
  realty_asset: asset,
  reference: 'SALE-001',
  price_sale_current_cents: 350_000_00,
  price_sale_current_currency: 'EUR',
  active: true,
  visible: true,
  translations: {
    en: { title: 'Townhouse for Sale', description: 'Spacious downtown home' }
  }
)

Creating a Seasonal Rental Listing

rental = Pwb::RentalListing.create!(
  realty_asset: asset,
  reference: 'RENT-001',
  price_rental_monthly_current_cents: 2_500_00,
  price_rental_monthly_current_currency: 'EUR',
  price_rental_monthly_low_season_cents: 2_000_00,
  price_rental_monthly_high_season_cents: 3_000_00,
  for_rent_short_term: true,
  active: true,
  visible: true
)

Querying Active Listings by Tenant

website_id = 42

# Active sales for specific tenant

active_sales = PwbTenant::SaleListing
                 .joins(:realty_asset)
                 .where(pwb_realty_assets: { website_id: website_id })
                 .where(active: true)

# Short-term rentals only

vacation_rentals = PwbTenant::RentalListing
                     .joins(:realty_asset)
                     .where(pwb_realty_assets: { website_id: website_id })
                     .for_rent_short_term
                     .where(active: true)

Summary

• Normalized architecture: The database schema separates immutable property data (pwb_realty_assets) from transactional market data (pwb_sale_listings and pwb_rental_listings), eliminating duplication.
• Database constraints: Partial unique indexes on (realty_asset_id, active) enforce the business rule of only one active listing per property type at the database level.
• Monetary precision: All price fields use integer cents with currency columns, processed via the Money gem to prevent floating-point errors.
• Internationalization: Both listing tables store translated content in JSONB columns using the Mobility gem, supporting unlimited locales per listing.
• Multi-tenancy: The website_id column on the assets table enables secure data isolation between property websites using tenant-scoped model subclasses.

Frequently Asked Questions

What prevents a property from having multiple active sale listings simultaneously?

The database schema includes a partial unique index on (realty_asset_id, active) WHERE active = true in both the pwb_sale_listings and pwb_rental_listings tables. This index raises a uniqueness violation if code attempts to insert a second active listing for the same asset, working in conjunction with the ListingStateable concern in the application layer to ensure data consistency.

How does the schema handle different currencies and price formats?

All monetary values store as integer cents in columns like price_sale_current_cents or price_rental_monthly_current_cents, paired with separate *_currency columns. The models use the monetize macro from the Money gem to provide high-level price objects that handle formatting, conversion, and currency symbols while maintaining database precision.

Can a single property be listed for both sale and rent at the same time?

Yes, the schema supports concurrent sale and rental listings because they reside in separate tables with independent active constraints. A pwb_realty_asset can have one active pwb_sale_listings record and one active pwb_rental_listings record simultaneously, allowing "sale or rent" marketing strategies without data conflicts.

Where is the database schema definition located in the repository?

The complete schema definition resides in the migration file [db/migrate/20251204180440_create_normalized_property_tables.rb](https://github.com/etewiah/property_web_builder/blob/master/db/migrate/20251204180440_create_normalized_property_tables.rb), which creates all three tables, establishes foreign key relationships, and adds the critical partial indexes for data integrity.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}