# Property Models Architecture - Exploration Findings\n\n## Exploration Scope\nThis document summarizes findings from a comprehensive exploration of the PropertyWebBuilder Rails project's property model architecture as of December 4, 2025.\n\n## Key Questions Answered\n\n### 1. What is the Current Source of Truth for Property Data?\n\n**ANSWER**: The **normalized schema** with three separate tables is the source of truth:\n\n```\n┌─────────────────────────────────────────┐\n│    Pwb::RealtyAsset (Physical Data)     │\n│         UUID Primary Key                │\n│  - Location, dimensions, classification │\n│  - Immutable property characteristics   │\n│  - Foreign key holder for everything    │\n└─────────────────────────────────────────┘\n          ↓                    ↓\n    ┌──────────────┐    ┌──────────────┐\n    │SaleListing   │    │RentalListing │\n    │(Transaction) │    │(Transaction) │\n    └──────────────┘    └──────────────┘\n```\n\n**Details**:\n- **pwb_realty_assets** table (UUID primary key): Stores physical property attributes\n- **pwb_sale_listings** table (UUID primary key): Stores sale transaction data\n- **pwb_rental_listings** table (UUID primary key): Stores rental transaction data\n- Each listing table has a foreign key to `pwb_realty_assets.id`\n\n**Why This Design**:\nSeparates the *physical asset* (building/land) from *commercial transactions* (selling/renting). Allows a property to be simultaneously For Sale and For Rent with independent listings.\n\n### 2. Where Should Translations Be Stored Now That Prop Is Deprecated?\n\n**ANSWER**: Translations remain in the **Pwb::Prop model using Mobility JSONB backend**.\n\n**Important Note**: Pwb::Prop is **NOT technically deprecated yet** - it's still actively used.\n\n**Storage Implementation**:\n```sql\nCREATE TABLE pwb_props (\n  id SERIAL PRIMARY KEY,\n  reference VARCHAR,\n  translations JSONB,  -- ← Stores all localized content\n  ...\n)\n\nCREATE INDEX index_pwb_props_on_translations USING gin (translations);\n```\n\n**JSONB Format**:\n```json\n{\n  \"en\": {\n    \"title\": \"Beautiful Villa with Pool\",\n    \"description\": \"Stunning 3-bed villa...\"\n  },\n  \"es\": {\n    \"title\": \"Hermosa Villa con Piscina\",\n    \"description\": \"Espectacular villa de 3 dormitorios...\"\n  }\n}\n```\n\n**Access Patterns**:\n```ruby\n# Via Prop model directly\nprop = Pwb::Prop.find_by(reference: 'PROP-001')\nprop.title              # Current I18n.locale\nprop.title_en           # English via Mobility accessor\nprop.title_es           # Spanish via Mobility accessor\n\n# Via RealtyAsset (delegated)\nasset = Pwb::RealtyAsset.find(uuid)\nasset.prop.title        # Finds Prop via reference, returns title\n\n# Via Property view (backward compatible)\nproperty = Pwb::Property.find(uuid)\nproperty.title          # Delegates to property.prop.title\n```\n\n**Migration History**:\n- Originally used Globalize gem with separate translation tables\n- Recently migrated to Mobility gem with JSONB backend (Dec 4, 2025)\n- Migration files:\n  - `20251204205742_add_mobility_translations_columns.rb` - Added JSONB columns\n  - `20251204205743_migrate_globalize_to_mobility.rb` - Migrated data from Globalize to JSONB\n\n**Future Path**: Prop model can be completely removed once translations are migrated elsewhere (not implemented yet).\n\n### 3. What is the Relationship Between RealtyAsset and Property?\n\n**ANSWER**: **RealtyAsset is the write model, Property is the read model**.\n\n```\n┌──────────────────────────────────────────────────────────────┐\n│                       APPLICATION                            │\n├──────────────────────────────────────────────────────────────┤\n│  Controller reads from:      Controller writes to:           │\n│  Pwb::Property               Pwb::RealtyAsset                │\n│  (Materialized View)         (Database Table)                │\n└──────────────────────────────────────────────────────────────┘\n         │                              │\n         │                              │\n         ▼                              ▼\n┌──────────────────┐        ┌──────────────────────┐\n│ Materialized     │        │ RealtyAsset          │\n│ View (Read-Only) │        │ + SaleListing        │\n│                  │        │ + RentalListing      │\n│ pwb_properties   │        │ (Writable Tables)    │\n└──────────────────┘        └──────────────────────┘\n         ▲                              │\n         │   after_commit callbacks    │\n         └──────────────────────────────┘\n```\n\n**Key Difference**:\n\n| Aspect | RealtyAsset | Property |\n|--------|-------------|----------|\n| **Type** | Database table | Materialized view |\n| **Primary Key** | UUID | UUID (same as asset) |\n| **Writable** | YES | NO (readonly? always true) |\n| **Indexed** | Basic indexes | Extensive indexes (13 total) |\n| **Use Case** | Admin editing, backend logic | Frontend searches, read-only operations |\n| **Performance** | Moderate (requires JOINs at app level) | Excellent (pre-computed & indexed) |\n\n**Why This Hybrid Approach**:\n1. **Normalization** (RealtyAsset): Avoids data duplication, enforces data integrity\n2. **Denormalization** (Property): Avoids complex JOINs in application code, provides optimized reads\n3. **Auto-refresh**: Callbacks automatically update the view when underlying data changes\n\n**Technical Implementation**:\n\nThe materialized view is created using the Scenic gem:\n\n```ruby\n# db/migrate/20251204185426_create_pwb_properties_materialized_view.rb\ncreate_view :pwb_properties, materialized: true, version: 1\nadd_index :pwb_properties, :id, unique: true  # Required for concurrent refresh\nadd_index :pwb_properties, :website_id\nadd_index :pwb_properties, :visible\nadd_index :pwb_properties, :for_sale\nadd_index :pwb_properties, :for_rent\n# ... 8 more indexes\n```\n\nView SQL joins the three tables:\n\n```sql\nSELECT\n  a.id,\n  a.reference,\n  a.website_id,\n  -- All asset fields\n  a.count_bedrooms,\n  a.street_address,\n  ...\n  -- Sale listing fields\n  sl.id AS sale_listing_id,\n  COALESCE(sl.visible, false) AND NOT COALESCE(sl.archived, true) AS for_sale,\n  COALESCE(sl.price_sale_current_cents, 0) AS price_sale_current_cents,\n  ...\n  -- Rental listing fields\n  rl.id AS rental_listing_id,\n  COALESCE(rl.visible, false) AND NOT COALESCE(rl.archived, true) AS for_rent,\n  ...\n  -- Computed fields\n  (sl.visible OR rl.visible) AS visible,\n  COALESCE(sl.highlighted, false) OR COALESCE(rl.highlighted, false) AS highlighted\nFROM pwb_realty_assets a\nLEFT JOIN pwb_sale_listings sl ON sl.realty_asset_id = a.id AND sl.archived = false\nLEFT JOIN pwb_rental_listings rl ON rl.realty_asset_id = a.id AND rl.archived = false\n```\n\n### 4. Any Notes About Migration Path from Prop to RealtyAsset?\n\n**ANSWER**: The migration is **complete and already executed**. Here's the timeline:\n\n#### Commits Involved\n- `1f82e17e` (Dec 4, 2025, 18:35:44 UTC) - \"Normalize property tables into assets and listings\"\n- `a3e6d367` (Dec 4, 2025) - \"Add scenic gem and Pwb::Property materialized view for read-optimized queries\"\n- `599b0483` (Dec 4, 2025) - \"Fix seeding to work better with new models\"\n- `d3a7f295` (Dec 4, 2025) - \"Specs for new models\"\n\n#### Migration Phases Completed\n\n**Phase 1: Schema Creation** ✅\n- Migration `20251204180440_create_normalized_property_tables.rb`\n- Created three new tables with UUIDs\n- Established foreign key relationships\n\n**Phase 2: Model Definition** ✅\n- Created `Pwb::RealtyAsset` model\n- Created `Pwb::SaleListing` model\n- Created `Pwb::RentalListing` model\n- Added associations, scopes, and helper methods\n\n**Phase 3: Data Migration** ✅\n- Rake task migrated existing props to normalized tables\n- Created corresponding sale/rental listings\n- Migrated photos, features, and translations\n- Result: 6 properties successfully migrated\n\n**Phase 4: Materialized View** ✅\n- Migration `20251204185426_create_pwb_properties_materialized_view.rb`\n- Created `pwb_properties` view using Scenic gem\n- Added 13 indexes for query optimization\n- Implemented auto-refresh via after_commit callbacks\n\n**Phase 5: Controller Updates** ✅\n- `SiteAdmin::PropsController` updated to use `Property` for reads, `RealtyAsset` for writes\n- Other controllers updated to use new models\n- Pattern: Use `Pwb::Property` for reads, `Pwb::RealtyAsset` for writes\n\n**Phase 6: Translations Migration** ✅\n- Migrated from Globalize to Mobility JSONB backend (Dec 4, 2025)\n- Data preserved in `pwb_props.translations` column\n- Mobility gem provides locale accessors (`title_en`, `title_es`, etc.)\n\n#### Remaining Work ⏳\n\n1. **Testing**: Comprehensive RSpec tests for new models\n2. **Old Schema**: `pwb_props` table still exists (kept for backward compatibility)\n3. **Prop Deprecation**: Once confident, can remove `Pwb::Prop` model entirely\n\n#### Migration Pattern Used\n\n```ruby\n# BEFORE (Single monolithic table)\nclass SiteAdmin::PropsController\n  def index\n    @props = Pwb::Prop.visible.for_sale\n  end\n  \n  def update\n    @prop.update!(params)\n  end\nend\n\n# AFTER (Normalized + View)\nclass SiteAdmin::PropsController\n  before_action :set_property, only: [:index, :show]        # Use view for reads\n  before_action :set_realty_asset, only: [:edit_*, :update] # Use asset for writes\n  \n  def index\n    @props = Pwb::Property.visible.for_sale  # Read from view\n  end\n  \n  def update\n    @prop.update!(asset_params)             # Write to asset\n    # View auto-refreshes after transaction commits\n  end\nend\n```\n\n## Architecture Decisions and Trade-offs\n\n### Why Normalize?\n\n**Problem with Monolithic Approach**:\n- Single `pwb_props` table mixed physical attributes with transactional data\n- Cannot independently manage multiple listings for same property\n- Difficult to support property being for sale AND for rent simultaneously\n- Data integrity challenges\n\n**Solution: Normalization**\n- Separates concerns: asset vs. transactions\n- Allows independent management of sale and rental listings\n- Better data modeling\n- Easier to add new transaction types in future\n\n### Why Materialized View?\n\n**Problem with Normalized Approach**:\n- Every query requires JOINs across three tables\n- Application code becomes complex\n- Query performance degrades with more data\n\n**Solution: Materialized View**\n- Pre-computed joined result stored as a table\n- All data in one place with optimized indexes\n- Application code remains simple\n- Excellent read performance\n\n**Trade-off**:\n- View is eventually consistent (updated after commits)\n- View size grows with data volume\n- Refresh operation has a small cost (mitigated by concurrent refresh)\n\n### Why Keep Prop Model?\n\n**Reason**: Translations (title, description) in multiple languages\n- Moving translations would require refactoring entire system\n- Prop model is lightweight now (mainly just translations)\n- Can be removed in future migration if needed\n\n## Files and Directory Structure\n\n### Models\n```\napp/models/pwb/\n├── realty_asset.rb          # Physical property (writable)\n├── sale_listing.rb          # Sale transaction (writable)\n├── rental_listing.rb        # Rental transaction (writable)\n├── property.rb              # Materialized view (read-only)\n└── prop.rb                  # Translations (legacy)\n```\n\n### Database\n```\ndb/\n├── migrate/\n│   ├── 20251204180440_create_normalized_property_tables.rb\n│   ├── 20251204181516_add_realty_asset_id_to_related_tables.rb\n│   ├── 20251204185426_create_pwb_properties_materialized_view.rb\n│   ├── 20251204205742_add_mobility_translations_columns.rb\n│   └── 20251204205743_migrate_globalize_to_mobility.rb\n├── views/\n│   └── pwb_properties_v01.sql    # Materialized view definition\n└── schema.rb\n```\n\n### Documentation\n```\ndocs/architecture/migrations/\n├── pwb_props_normalization.md                 # Migration guide\n└── pwb_props_normalization_walkthrough.md     # Implementation details\n```\n\n### Controllers Using New Models\n```\napp/controllers/\n├── site_admin/props_controller.rb             # Uses Property + RealtyAsset\n├── pwb/props_controller.rb                    # Uses Property\n├── site_admin/dashboard_controller.rb         # Uses Property\n└── tenant_admin/dashboard_controller.rb       # Uses Property\n```\n\n## Usage Patterns\n\n### Read Operations (Use Pwb::Property)\n```ruby\n# List visible properties\nPwb::Property.visible.for_sale\n\n# Search\nPwb::Property.properties_search(\n  sale_or_rental: 'sale',\n  count_bedrooms: 2,\n  for_sale_price_till: 500000\n)\n\n# Single property\nproperty = Pwb::Property.find(uuid)\nproperty.title\nproperty.for_sale\nproperty.prop_photos\n```\n\n### Write Operations (Use Normalized Models)\n```ruby\n# Create property\nasset = Pwb::RealtyAsset.create!(\n  reference: 'PROP-001',\n  count_bedrooms: 3,\n  website_id: 1\n)\n\n# Add sale listing\nasset.sale_listings.create!(visible: true, price_sale_current_cents: 50000000)\n\n# View auto-refreshes after commit\n```\n\n### Translation Operations (Use Pwb::Prop)\n```ruby\nprop = Pwb::Prop.find_by(reference: 'PROP-001')\nprop.title_en\nprop.title_es\nprop.update!(title: 'New Title')\n```\n\n## Summary Table\n\n| Question | Answer |\n|----------|--------|\n| **What is the source of truth?** | RealtyAsset (physical data) + SaleListing/RentalListing (transactions) |\n| **Where are translations?** | Pwb::Prop model using Mobility JSONB column |\n| **What's the read model?** | Pwb::Property (materialized view) |\n| **Is Prop deprecated?** | Not officially yet, but no longer primary model |\n| **Is migration complete?** | Yes, completed Dec 4, 2025 |\n| **What gem manages view?** | Scenic gem |\n| **How is view refreshed?** | Automatically via after_commit callbacks |\n| **Can I write to Property?** | No, it's read-only (readonly? returns true) |\n| **How many indexes on view?** | 13 indexes for optimized queries |\n| **Multiple listings support?** | Yes, property can be for_sale AND for_rent |\n\n## Verification Commands\n\n```bash\n# Check record counts\nrails runner \"\n  puts 'Properties: ' + Pwb::Property.count.to_s\n  puts 'Assets: ' + Pwb::RealtyAsset.count.to_s\n  puts 'Sales: ' + Pwb::SaleListing.count.to_s\n  puts 'Rentals: ' + Pwb::RentalListing.count.to_s\n\"\n\n# Test Property model\nrails runner \"\n  p = Pwb::Property.first\n  puts 'ID: ' + p.id.to_s\n  puts 'Title: ' + p.title.to_s\n  puts 'For Sale: ' + p.for_sale.to_s\n  puts 'For Rent: ' + p.for_rent.to_s\n  puts 'Readonly: ' + p.readonly?.to_s\n\"\n\n# Refresh view\nrails runner \"Pwb::Property.refresh; puts 'View refreshed'\"\n```\n\n---\n\n## Conclusion\n\nThe PropertyWebBuilder application has successfully migrated from a monolithic property model to a well-architected normalized schema with an optimized materialized view for reads. This provides:\n\n✅ Clean separation of physical assets from transactional listings  \n✅ Support for properties with multiple simultaneous listings  \n✅ Optimized read performance for frontend operations  \n✅ Backward compatibility through the Property view  \n✅ Automatic view refresh after every write  \n✅ Translation support via Mobility JSONB backend  \n\nThe migration is complete and the system is operational. Remaining work is primarily testing and eventual cleanup of the legacy Prop model.\n\n---\n\n**Exploration Date**: December 4, 2025\n**Status**: Complete\n**Architecture Status**: Stable and Documented\n