# Database Schema for RealtyAsset, SaleListing, and RentalListing in PropertyWebBuilder

> Explore the RealtyAsset SaleListing and RentalListing database schema for PropertyWebBuilder. Understand how a normalized three-table design with unique indexes optimizes property data management.

- Repository: [Ed Tee/property_web_builder](https://github.com/etewiah/property_web_builder)
- Tags: api-reference
- Published: 2026-03-01

---

**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/main/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/main/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:

```ruby
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/main/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

```ruby
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/main/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:

```ruby
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/main/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

```ruby

# 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

```ruby
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

```ruby
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/main/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.