website_id foreign keys.\n\n---\n\n## 1. Database Configuration\n\n### Current Setup\n\nFile: /config/database.yml\n\nTwo database connections are configured:\n\nPrimary Database:\n- Stores: All website records (pwb_websites table)\n- Stores: All tenant-scoped data initially\n- Connection: primary\n- URL: PWB_DATABASE_URL environment variable\n\nTenant Shard 1:\n- Purpose: Optional shard for tenant data at scale\n- Connection: tenant_shard_1\n- URL: PWB_TENANT_SHARD_1_DATABASE_URL environment variable\n- Migrations: /db/tenant_shard_1_migrate/\n- Status: Configured but not used by default\n\n### Development Environment\n\nyaml\ndevelopment:\n primary:\n adapter: postgresql\n database: pwb_development\n pool: <%= ENV.fetch(\"RAILS_MAX_THREADS\") { 5 } %>\n \n tenant_shard_1:\n adapter: postgresql\n database: pwb_development_shard_1\n migrations_paths: db/tenant_shard_1_migrate\n\n\n### Production Environment\n\nyaml\nproduction:\n primary:\n adapter: postgresql\n database: pwb_production\n url: <%= ENV['PWB_DATABASE_URL'] %>\n password: <%= ENV['PWB_DATABASE_PASSWORD'] %>\n prepared_statements: false\n \n tenant_shard_1:\n adapter: postgresql\n database: pwb_production_shard_1\n url: <%= ENV['PWB_TENANT_SHARD_1_DATABASE_URL'] %>\n password: <%= ENV['PWB_DATABASE_PASSWORD'] %>\n prepared_statements: false\n\n\n---\n\n## 2. Sharding Implementation\n\n### How Sharding Works\n\nRails 6+ connects_to feature enables transparent sharding:\n\nFile: /app/models/pwb_tenant/application_record.rb\n\nruby\nmodule PwbTenant\n class ApplicationRecord < ActiveRecord::Base\n self.abstract_class = true\n self.table_name_prefix = 'pwb_'\n \n # Automatic tenant scoping\n acts_as_tenant :website, class_name: 'Pwb::Website'\n \n # Define available shards\n connects_to shards: {\n default: { writing: :primary, reading: :primary },\n shard_1: { writing: :tenant_shard_1, reading: :tenant_shard_1 }\n }\n end\nend\n\n\n### Shard Assignment\n\nEach website declares which shard owns its data:\n\nruby\n# In Pwb::Website model\nwebsite = Pwb::Website.create!(\n subdomain: 'myagency',\n shard_name: 'default' # Or 'shard_1' for sharded tenants\n)\n\n\n### Database Selection Logic\n\n1. Controller sets current tenant:\n ruby\n ActsAsTenant.current_tenant = Pwb::Current.website\n \n\n2. acts_as_tenant checks website's shard:\n ruby\n website.shard_name # Returns 'default' or 'shard_1'\n \n\n3. Rails routes query to correct database:\n ruby\n # If current_website.shard_name = 'shard_1'\n Pwb::Page.all # Routes to tenant_shard_1 database\n \n # If current_website.shard_name = 'default'\n Pwb::Page.all # Routes to primary database\n \n\n### Sharding Diagram\n\n\n┌─────────────────────────────────────────────────────────────────┐\n│ Rails Application │\n└──────────────────┬──────────────────────────────────────────────┘\n │\n ┌────────┴────────┐\n │ │\n ▼ ▼\n ┌──────────────┐ ┌──────────────┐\n │ primary │ │ tenant_shard │\n │ Database │ │ _1 │\n │ │ │ │\n │ pwb_websites │ │ │\n │ pwb_pages │ │ pwb_pages │\n │ pwb_contents │ │ pwb_contents │\n │ pwb_messages │ │ pwb_messages │\n │ ... │ │ ... │\n └──────────────┘ └──────────────┘\n\n\n---\n\n## 3. Schema Synchronization\n\n### Migrations Strategy\n\nPrimary Database Migrations:\n- Location: /db/migrate/\n- Run on: primary database\n- Contains: All tables (websites + baseline tenant tables)\n\nShard-Specific Migrations:\n- Location: /db/tenant_shard_1_migrate/\n- Run on: tenant_shard_1 database\n- Contains: Only tenant-scoped tables (must mirror primary)\n\n### Running Migrations\n\nMigrate both databases:\nbash\nrails db:migrate # Migrates primary\nrails db:migrate:tenant_shard_1 # Migrates shard_1\n\n\nImportant: Both databases must have the same schema for tenant-scoped tables.\n\n### What Goes Where\n\nAlways in Primary (Global Data):\n- pwb_websites\n- pwb_users\n- pwb_user_memberships\n- pwb_subscriptions\n- pwb_agencies\n- pwb_addresses\n- Any other non-scoped models\n\nBaseline in Primary, Can Be in Shards (Tenant Data):\n- pwb_pages\n- pwb_contents\n- pwb_messages\n- pwb_contacts\n- pwb_realty_assets\n- pwb_sale_listings\n- pwb_rental_listings\n- pwb_field_keys\n- pwb_links\n- All other website-scoped models\n\n---\n\n## 4. Table Schema: Tenant Isolation Pattern\n\nEvery tenant-scoped table follows this pattern:\n\nsql\nCREATE TABLE pwb_pages (\n id BIGINT PRIMARY KEY,\n website_id BIGINT NOT NULL, -- ← REQUIRED: Foreign key to website\n slug VARCHAR(255) NOT NULL,\n title VARCHAR(255),\n visible BOOLEAN DEFAULT true,\n created_at TIMESTAMP,\n updated_at TIMESTAMP,\n \n -- Foreign key constraint\n CONSTRAINT fk_pages_website \n FOREIGN KEY (website_id) \n REFERENCES pwb_websites(id),\n \n -- Unique constraint per website\n CONSTRAINT unique_slug_per_website \n UNIQUE (website_id, slug),\n \n -- Index for fast filtering\n INDEX idx_pages_website_id (website_id)\n);\n\n\n### Key Components\n\n1. website_id Column:\n - Type: BIGINT NOT NULL\n - Foreign key to pwb_websites(id)\n - Every row must belong to exactly one website\n - Cannot be NULL\n\n2. Unique Indexes Per Website:\n - UNIQUE (website_id, slug) - slug unique per website, not globally\n - UNIQUE (website_id, reference) - reference unique per website\n - Prevents conflicts between websites\n\n3. Website-Scoped Indexes:\n - INDEX (website_id) - Fast filtering queries\n - INDEX (website_id, status) - Fast filtered queries\n - All queries include website_id in WHERE clause\n\n### Real Examples\n\npwb_pages Table:\nsql\nCREATE TABLE pwb_pages (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n website_id BIGINT NOT NULL,\n slug VARCHAR(255) NOT NULL,\n visible BOOLEAN DEFAULT true,\n page_type VARCHAR(100),\n created_at TIMESTAMP,\n updated_at TIMESTAMP,\n \n CONSTRAINT fk_pages_website \n FOREIGN KEY (website_id) REFERENCES pwb_websites(id),\n CONSTRAINT unique_page_slug_per_website \n UNIQUE (website_id, slug),\n \n INDEX idx_pages_website_id (website_id),\n INDEX idx_pages_visible (website_id, visible)\n);\n\n\npwb_messages Table:\nsql\nCREATE TABLE pwb_messages (\n id BIGINT PRIMARY KEY AUTO_INCREMENT,\n website_id BIGINT NOT NULL,\n origin_email VARCHAR(255),\n subject VARCHAR(255),\n content TEXT,\n read BOOLEAN DEFAULT false,\n read_at TIMESTAMP,\n created_at TIMESTAMP,\n updated_at TIMESTAMP,\n \n CONSTRAINT fk_messages_website \n FOREIGN KEY (website_id) REFERENCES pwb_websites(id),\n \n INDEX idx_messages_website_id (website_id),\n INDEX idx_messages_website_read (website_id, read)\n);\n\n\n---\n\n## 5. Querying with Sharding\n\n### Transparent Routing\n\nWhen ActsAsTenant.current_tenant is set, queries automatically route:\n\nruby\n# In a request for website_id=42 where shard_name='shard_1'\nActsAsTenant.current_tenant = website_42\n\n# This query routes to tenant_shard_1:\nPwb::Page.all\n\n# Equivalent to:\nActiveRecord::Base.connected_to(shard: :shard_1) do\n Pwb::Page.all\nend\n\n\n### Accessing Different Shards\n\nAccess specific shard explicitly:\nruby\n# Access primary database\nActiveRecord::Base.connected_to(shard: :default) do\n Pwb::Page.all\nend\n\n# Access shard_1\nActiveRecord::Base.connected_to(shard: :shard_1) do\n Pwb::Page.all\nend\n\n\nWithout tenant context:\nruby\n# If no current_tenant set, uses :default shard\nActsAsTenant.current_tenant = nil\nPwb::Page.all # Routes to primary database\n\n\n### Cross-Shard Queries (Advanced)\n\nSometimes you need to query multiple shards:\n\nruby\n# Get all pages across all shards\nall_pages = []\n\nActiveRecord::Base.connected_to(shard: :default) do\n all_pages += Pwb::Page.all\nend\n\nActiveRecord::Base.connected_to(shard: :shard_1) do\n all_pages += Pwb::Page.all\nend\n\nall_pages # Combined results\n\n\nNote: This must be done manually - no automatic cross-shard joins.\n\n---\n\n## 6. Migration Workflow\n\n### Adding New Column to Tenant-Scoped Table\n\nStep 1: Create migration\nbash\nrails generate migration AddColorToPwbPages color:string\n\n\nStep 2: Migration file (db/migrate/xxx.rb)\nruby\nclass AddColorToPwbPages < ActiveRecord::Migration[6.0]\n def change\n add_column :pwb_pages, :color, :string\n end\nend\n\n\nStep 3: Copy migration to shard path\nbash\ncp db/migrate/20250107_add_color_to_pwb_pages.rb \\\n db/tenant_shard_1_migrate/\n\n\nStep 4: Run migrations on both databases\nbash\nrails db:migrate # Migrates primary\nrails db:migrate:tenant_shard_1 # Migrates shard_1\n\n\n### Adding New Tenant-Scoped Table\n\nStep 1: Generate migration\nbash\nrails generate model CustomField website:references name:string\n\n\nStep 2: Migration creates table with website_id\nruby\nclass CreateCustomFields < ActiveRecord::Migration[6.0]\n def change\n create_table :pwb_custom_fields do |t|\n t.references :website, foreign_key: { to_table: :pwb_websites }\n t.string :name\n t.timestamps\n end\n \n add_index :pwb_custom_fields, :website_id\n add_index :pwb_custom_fields, [:website_id, :name], unique: true\n end\nend\n\n\nStep 3: Copy to shard path\nbash\ncp db/migrate/xxx.rb db/tenant_shard_1_migrate/\n\n\nStep 4: Run both\nbash\nrails db:migrate # Primary\nrails db:migrate:tenant_shard_1 # Shard 1\n\n\n---\n\n## 7. Scaling Scenarios\n\n### Scenario 1: Early Stage (Default)\n\nSetup:\n- All websites in primary database\n- No sharding needed\n- Single PostgreSQL instance\n\n\nwebsite_1 ┐\nwebsite_2 ├─→ PRIMARY DB (pwb_production)\nwebsite_3 │\nwebsite_4 ┘\n\n\nConfiguration:\nruby\n# All websites\nwebsite.shard_name = 'default'\n\n\n### Scenario 2: Growth (Selective Sharding)\n\nSetup:\n- Large tenants moved to shard_1\n- Smaller tenants remain in primary\n- Two PostgreSQL instances\n\n\nsmall_1 ┐\nsmall_2 ├─→ PRIMARY DB\nsmall_3 │\nsmall_4 ┘\n\nlarge_1 ┐\nlarge_2 ├─→ SHARD_1 DB\nlarge_3 │\n\n\nConfiguration:\nruby\n# Large tenant\nwebsite.update!(shard_name: 'shard_1')\n\n# Small tenant\nwebsite.update!(shard_name: 'default')\n\n\nMigration:\n1. Create shard_1 database\n2. Run migrations on shard_1\n3. Manually migrate data from primary to shard_1\n4. Update website.shard_name\n5. Test queries route correctly\n\n### Scenario 3: Enterprise (Multiple Shards)\n\nSetup:\n- Add shard_2, shard_3, etc.\n- Distribute by region or load\n- Multiple PostgreSQL instances\n\nruby\n# In database.yml\nconnects_to shards: {\n default: { writing: :primary, reading: :primary },\n shard_1: { writing: :tenant_shard_1, reading: :tenant_shard_1 },\n shard_2: { writing: :tenant_shard_2, reading: :tenant_shard_2 },\n shard_3: { writing: :tenant_shard_3, reading: :tenant_shard_3 }\n}\n\n\n---\n\n## 8. Database Maintenance\n\n### Backup Strategy\n\nBackup All Shards:\nbash\n# Primary database\npg_dump pwb_production > primary.sql\n\n# Shard 1\npg_dump pwb_production_shard_1 > shard_1.sql\n\n# Restore\npsql pwb_production < primary.sql\npsql pwb_production_shard_1 < shard_1.sql\n\n\n### Monitoring\n\nMonitor All Shards:\nsql\n-- Count websites per shard\nSELECT shard_name, COUNT(*) \nFROM pwb_websites \nGROUP BY shard_name;\n\n-- Size of shard databases\nSELECT\n schemaname,\n tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size\nFROM pg_tables\nWHERE schemaname NOT IN ('pg_catalog', 'information_schema')\nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;\n\n\n### Rebalancing (Moving Tenant Between Shards)\n\nAdvanced: Migrate website from shard_1 to shard_2\n\nruby\n# 1. Get data from shard_1\nActiveRecord::Base.connected_to(shard: :shard_1) do\n website_data = Pwb::Website.find(website_id)\n pages = website_data.pages\n messages = website_data.messages\n # ... all tenant data\nend\n\n# 2. Insert into shard_2\nActiveRecord::Base.connected_to(shard: :shard_2) do\n # Create website\n new_website = Pwb::Website.create!(website_data.attributes.except('id'))\n \n # Recreate pages\n pages.each do |page|\n Pwb::Page.create!(page.attributes.except('id').merge(website_id: new_website.id))\n end\n \n # ... recreate all associated data\nend\n\n# 3. Update mapping\nwebsite.update!(shard_name: 'shard_2')\n\n# 4. Verify\nActiveRecord::Base.connected_to(shard: :shard_2) do\n Pwb::Page.where(website_id: website_id).count # Should match old count\nend\n\n# 5. Clean up shard_1\nActiveRecord::Base.connected_to(shard: :shard_1) do\n # Delete old data (if verified successful)\nend\n\n\n---\n\n## 9. Performance Considerations\n\n### Query Patterns\n\nFast (Indexed):\nruby\n# Uses website_id index\nPwb::Page.where(website_id: website_id)\n\n# Uses (website_id, slug) unique index\nPwb::Page.where(website_id: website_id, slug: slug)\n\n# Uses (website_id, visible) index\nPwb::Page.where(website_id: website_id, visible: true)\n\n\nSlow (Full table scan if no index):\nruby\n# ❌ Avoid without index on (website_id, status)\nPwb::Page.where(website_id: website_id, custom_status: 'draft')\n\n# ✅ Add index if needed\nadd_index :pwb_pages, [:website_id, :custom_status]\n\n\n### Materialized Views\n\nThe system uses materialized views for heavy read patterns:\n\nruby\n# Listed properties materialized view\nPwb::ListedProperty.where(website_id: website_id).for_sale\n\n# Refresh after writes\nPwb::ListedProperty.refresh\n\n\nThis denormalizes complex queries (joins with sale/rental listings) into a simple table.\n\n---\n\n## 10. Troubleshooting\n\n### Query Routing Wrong Shard\n\nSymptom: Data not found after creation\n\nCause: ActsAsTenant.current_tenant not set correctly\n\nSolution:\nruby\n# Check current shard\nActsAsTenant.current_tenant # Should be set\nActsAsTenant.current_tenant.shard_name # Check shard\n\n# Verify routing\nActiveRecord::Base.connection.pool.db_config # Check connection\n\n\n### Schema Out of Sync\n\nSymptom: Column exists in primary but not shard_1\n\nCause: Migrations not run on both databases\n\nSolution:\nbash\n# Check migration status\nrails db:migrate:status\nrails db:migrate:status:tenant_shard_1\n\n# Run pending migrations on both\nrails db:migrate\nrails db:migrate:tenant_shard_1\n\n\n### Sharding Configuration Issues\n\nSymptom: Can't find website in expected shard\n\nCause: website.shard_name doesn't match configuration\n\nSolution:\nruby\n# Verify shard names in code match database.yml\nActiveRecord::Base.connects_to shards: {\n default: { writing: :primary, reading: :primary },\n shard_1: { writing: :tenant_shard_1, reading: :tenant_shard_1 }\n}\n\n# Check website config\nwebsite.shard_name # Should be 'default' or 'shard_1' (or other configured shard)\n\n# Verify database connection\nActiveRecord::Base.connected_to(shard: :shard_1) do\n ActiveRecord::Base.connection.execute(\"SELECT 1\")\nend\n\n\n---\n\n## Summary\n\nPropertyWebBuilder's database architecture:\n- Single database by default - All tenants in primary (pwb_production)\n- Optional sharding - Move large tenants to shard_1 or beyond\n- Transparent routing - Queries automatically go to correct shard based on current tenant\n- Schema sync required - Migrations must run on all shards\n- Row-level isolation - website_id columns prevent data mixing\n- Scalable - Add more shards as needed without code changes\n\nStart simple (all in primary), shard when needed for performance.\n"}