Implementation Guide: Monitor mls for new listings matching buyer criteria and send personalized alerts

# After creating the Hetzner CX32 via the Hetzner Cloud Console (https://console.hetzner.cloud):
# SSH into the new server
ssh root@<SERVER_IP>
# Create a non-root user for operations
adduser mspadmin
usermod -aG sudo mspadmin
# Configure SSH key authentication
mkdir -p /home/mspadmin/.ssh
cp ~/.ssh/authorized_keys /home/mspadmin/.ssh/
chown -R mspadmin:mspadmin /home/mspadmin/.ssh
chmod 700 /home/mspadmin/.ssh
chmod 600 /home/mspadmin/.ssh/authorized_keys
# Disable password authentication and root login
sed -i 's/^PermitRootLogin yes/PermitRootLogin no/' /etc/ssh/sshd_config
sed -i 's/^#PasswordAuthentication yes/PasswordAuthentication no/' /etc/ssh/sshd_config
systemctl restart sshd
# Configure UFW firewall
ufw default deny incoming
ufw default allow outgoing
ufw allow 22/tcp
ufw allow 80/tcp
ufw allow 443/tcp
ufw enable
# Enable automatic security updates
apt update && apt install -y unattended-upgrades
dpkg-reconfigure -plow unattended-upgrades
# Set timezone
timedatectl set-timezone America/New_York

# Log in as mspadmin
su - mspadmin
# Install Docker prerequisites
sudo apt update
sudo apt install -y ca-certificates curl gnupg lsb-release
# Add Docker's official GPG key
sudo install -m 0755 -d /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
sudo chmod a+r /etc/apt/keyrings/docker.gpg
# Add Docker repository
echo "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
# Install Docker Engine
sudo apt update
sudo apt install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin
# Add mspadmin to docker group (avoids sudo for docker commands)
sudo usermod -aG docker mspadmin
newgrp docker
# Verify installation
docker --version
docker compose version

# Create project directory
mkdir -p ~/mls-agent/{n8n-data,postgres-data,nginx,backups}
cd ~/mls-agent
# Create the Docker Compose file
cat > docker-compose.yml << 'DOCKEREOF'
version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: mls-postgres
    restart: always
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n_user
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - ./postgres-data:/var/lib/postgresql/data
      - ./init-db:/docker-entrypoint-initdb.d
    ports:
      - '127.0.0.1:5432:5432'
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U n8n_user -d n8n']
      interval: 10s
      timeout: 5s
      retries: 5

  n8n:
    image: n8nio/n8n:latest
    container_name: mls-n8n
    restart: always
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      DB_TYPE: postgresdb
      DB_POSTGRESDB_HOST: postgres
      DB_POSTGRESDB_PORT: 5432
      DB_POSTGRESDB_DATABASE: n8n
      DB_POSTGRESDB_USER: n8n_user
      DB_POSTGRESDB_PASSWORD: ${POSTGRES_PASSWORD}
      N8N_HOST: ${N8N_DOMAIN}
      N8N_PORT: 5678
      N8N_PROTOCOL: https
      WEBHOOK_URL: https://${N8N_DOMAIN}/
      N8N_ENCRYPTION_KEY: ${N8N_ENCRYPTION_KEY}
      GENERIC_TIMEZONE: America/New_York
      N8N_BASIC_AUTH_ACTIVE: 'true'
      N8N_BASIC_AUTH_USER: ${N8N_ADMIN_USER}
      N8N_BASIC_AUTH_PASSWORD: ${N8N_ADMIN_PASSWORD}
    volumes:
      - ./n8n-data:/home/node/.n8n
    ports:
      - '127.0.0.1:5678:5678'

  nginx:
    image: nginx:alpine
    container_name: mls-nginx
    restart: always
    depends_on:
      - n8n
    ports:
      - '80:80'
      - '443:443'
    volumes:
      - ./nginx/conf.d:/etc/nginx/conf.d
      - ./nginx/ssl:/etc/letsencrypt
      - ./nginx/webroot:/var/www/certbot
DOCKEREOF
# Create environment file
cat > .env << 'ENVEOF'
POSTGRES_PASSWORD=CHANGE_ME_STRONG_PASSWORD_32CHARS
N8N_DOMAIN=alerts.clientbrokerage.com
N8N_ENCRYPTION_KEY=CHANGE_ME_RANDOM_64CHAR_HEX_STRING
N8N_ADMIN_USER=mspadmin
N8N_ADMIN_PASSWORD=CHANGE_ME_STRONG_ADMIN_PASSWORD
ENVEOF
# Generate secure passwords
sed -i "s/CHANGE_ME_STRONG_PASSWORD_32CHARS/$(openssl rand -hex 16)/" .env
sed -i "s/CHANGE_ME_RANDOM_64CHAR_HEX_STRING/$(openssl rand -hex 32)/" .env
sed -i "s/CHANGE_ME_STRONG_ADMIN_PASSWORD/$(openssl rand -base64 24)/" .env
# IMPORTANT: Record these credentials securely
cat .env
chmod 600 .env

cd ~/mls-agent
# Create initial Nginx config (HTTP only, for Certbot challenge)
mkdir -p nginx/conf.d nginx/ssl nginx/webroot
cat > nginx/conf.d/default.conf << 'NGINXEOF'
server {
    listen 80;
    server_name alerts.clientbrokerage.com;

    location /.well-known/acme-challenge/ {
        root /var/www/certbot;
    }

    location / {
        return 301 https://$host$request_uri;
    }
}
NGINXEOF
# Start just Nginx for certificate issuance
docker compose up -d nginx
# Install Certbot on host and obtain certificate
sudo apt install -y certbot
sudo certbot certonly --webroot -w ~/mls-agent/nginx/webroot -d alerts.clientbrokerage.com --non-interactive --agree-tos --email msp-admin@yourmsp.com
# Copy certificates to Nginx volume
sudo cp -rL /etc/letsencrypt/live/alerts.clientbrokerage.com/ ~/mls-agent/nginx/ssl/
sudo chown -R mspadmin:mspadmin ~/mls-agent/nginx/ssl/
# Update Nginx config with SSL
cat > nginx/conf.d/default.conf << 'NGINXSSLEOF'
upstream n8n_backend {
    server n8n:5678;
}

server {
    listen 80;
    server_name alerts.clientbrokerage.com;

    location /.well-known/acme-challenge/ {
        root /var/www/certbot;
    }

    location / {
        return 301 https://$host$request_uri;
    }
}

server {
    listen 443 ssl http2;
    server_name alerts.clientbrokerage.com;

    ssl_certificate /etc/letsencrypt/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/privkey.pem;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers HIGH:!aNULL:!MD5;

    client_max_body_size 50M;

    location / {
        proxy_pass http://n8n_backend;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_read_timeout 300s;
        proxy_connect_timeout 300s;
    }
}
NGINXSSLEOF
# Restart Nginx with SSL config
docker compose restart nginx
# Set up auto-renewal cron
echo '0 3 * * * certbot renew --quiet && cp -rL /etc/letsencrypt/live/alerts.clientbrokerage.com/ /home/mspadmin/mls-agent/nginx/ssl/ && docker compose -f /home/mspadmin/mls-agent/docker-compose.yml restart nginx' | sudo tee /etc/cron.d/certbot-renew

cd ~/mls-agent
mkdir -p init-db
cat > init-db/01-schema.sql << 'SQLEOF'
-- Buyer profiles and preferences
CREATE TABLE IF NOT EXISTS buyers (
    id SERIAL PRIMARY KEY,
    external_crm_id VARCHAR(255) UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    assigned_agent_name VARCHAR(200),
    assigned_agent_email VARCHAR(255),
    assigned_agent_phone VARCHAR(20),
    email_consent BOOLEAN DEFAULT FALSE,
    email_consent_timestamp TIMESTAMPTZ,
    sms_consent BOOLEAN DEFAULT FALSE,
    sms_consent_timestamp TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Buyer search criteria (one buyer can have multiple saved searches)
CREATE TABLE IF NOT EXISTS search_criteria (
    id SERIAL PRIMARY KEY,
    buyer_id INTEGER REFERENCES buyers(id) ON DELETE CASCADE,
    criteria_name VARCHAR(200) DEFAULT 'Primary Search',
    min_price NUMERIC(12,2),
    max_price NUMERIC(12,2),
    min_bedrooms INTEGER,
    max_bedrooms INTEGER,
    min_bathrooms NUMERIC(3,1),
    max_bathrooms NUMERIC(3,1),
    min_sqft INTEGER,
    max_sqft INTEGER,
    min_lot_sqft INTEGER,
    max_lot_sqft INTEGER,
    min_year_built INTEGER,
    max_year_built INTEGER,
    property_types TEXT[], -- e.g., ARRAY['SingleFamily','Condo','Townhouse']
    zip_codes TEXT[],
    cities TEXT[],
    counties TEXT[],
    neighborhoods TEXT[],
    must_have_features TEXT[], -- e.g., ARRAY['garage','pool','waterfront']
    exclude_features TEXT[],  -- e.g., ARRAY['hoa','55plus']
    max_hoa_fee NUMERIC(8,2),
    latitude NUMERIC(10,7),
    longitude NUMERIC(10,7),
    radius_miles NUMERIC(5,2),
    custom_notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Listing cache (avoids re-alerting on known listings)
CREATE TABLE IF NOT EXISTS listings_cache (
    id SERIAL PRIMARY KEY,
    mls_id VARCHAR(50) UNIQUE NOT NULL,
    listing_data JSONB NOT NULL,
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(2),
    zip_code VARCHAR(10),
    price NUMERIC(12,2),
    bedrooms INTEGER,
    bathrooms NUMERIC(3,1),
    sqft INTEGER,
    property_type VARCHAR(50),
    listing_date TIMESTAMPTZ,
    photo_url TEXT,
    listing_url TEXT,
    first_seen_at TIMESTAMPTZ DEFAULT NOW(),
    last_updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Alert history (audit trail for compliance)
CREATE TABLE IF NOT EXISTS alert_history (
    id SERIAL PRIMARY KEY,
    buyer_id INTEGER REFERENCES buyers(id),
    listing_mls_id VARCHAR(50),
    search_criteria_id INTEGER REFERENCES search_criteria(id),
    channel VARCHAR(10) NOT NULL, -- 'email' or 'sms'
    personalized_message TEXT,
    subject_line VARCHAR(500),
    delivery_status VARCHAR(50) DEFAULT 'pending',
    delivery_provider_id VARCHAR(255),
    sent_at TIMESTAMPTZ DEFAULT NOW(),
    delivered_at TIMESTAMPTZ,
    opened_at TIMESTAMPTZ,
    clicked_at TIMESTAMPTZ
);

-- Create indexes for performance
CREATE INDEX idx_listings_mls_id ON listings_cache(mls_id);
CREATE INDEX idx_listings_first_seen ON listings_cache(first_seen_at);
CREATE INDEX idx_listings_price ON listings_cache(price);
CREATE INDEX idx_listings_zip ON listings_cache(zip_code);
CREATE INDEX idx_criteria_buyer ON search_criteria(buyer_id);
CREATE INDEX idx_alerts_buyer ON alert_history(buyer_id);
CREATE INDEX idx_alerts_listing ON alert_history(listing_mls_id);
CREATE INDEX idx_alerts_sent ON alert_history(sent_at);
CREATE INDEX idx_buyers_active ON buyers(is_active) WHERE is_active = TRUE;
SQLEOF
# Bring up all services (this will run the init SQL on first start)
docker compose down
docker compose up -d
# Verify database tables were created
docker exec mls-postgres psql -U n8n_user -d n8n -c '\dt'

echo -n 'simplyrets_user:simplyrets_pass' | base64

# Create a CSV template for the client to fill out
cat > ~/mls-agent/buyer_import_template.csv << 'CSVEOF'
first_name,last_name,email,phone,assigned_agent_name,assigned_agent_email,email_consent,sms_consent,min_price,max_price,min_bedrooms,min_bathrooms,property_types,zip_codes,cities,must_have_features
John,Smith,john@example.com,+15125551234,Jane Agent,jane@brokerage.com,true,true,350000,500000,3,2,"SingleFamily;Townhouse","78704;78745","Austin","garage"
CSVEOF

# After client fills out the CSV, import using a psql command:
# First, copy the file into the container
docker cp ~/mls-agent/buyers_filled.csv mls-postgres:/tmp/buyers.csv

# Run the import script
docker exec -i mls-postgres psql -U n8n_user -d n8n << 'IMPORTSQL'
-- Create temporary import table
CREATE TEMP TABLE buyer_import (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    assigned_agent_name VARCHAR(200),
    assigned_agent_email VARCHAR(255),
    email_consent BOOLEAN,
    sms_consent BOOLEAN,
    min_price NUMERIC,
    max_price NUMERIC,
    min_bedrooms INTEGER,
    min_bathrooms NUMERIC,
    property_types TEXT,
    zip_codes TEXT,
    cities TEXT,
    must_have_features TEXT
);

\COPY buyer_import FROM '/tmp/buyers.csv' WITH (FORMAT csv, HEADER true);

-- Insert buyers
INSERT INTO buyers (first_name, last_name, email, phone, assigned_agent_name, assigned_agent_email, email_consent, email_consent_timestamp, sms_consent, sms_consent_timestamp)
SELECT first_name, last_name, email, phone, assigned_agent_name, assigned_agent_email, email_consent, CASE WHEN email_consent THEN NOW() END, sms_consent, CASE WHEN sms_consent THEN NOW() END
FROM buyer_import;

-- Insert search criteria
INSERT INTO search_criteria (buyer_id, min_price, max_price, min_bedrooms, min_bathrooms, property_types, zip_codes, cities, must_have_features)
SELECT b.id, bi.min_price, bi.max_price, bi.min_bedrooms, bi.min_bathrooms,
       string_to_array(bi.property_types, ';'),
       string_to_array(bi.zip_codes, ';'),
       string_to_array(bi.cities, ';'),
       string_to_array(bi.must_have_features, ';')
FROM buyer_import bi
JOIN buyers b ON b.email = bi.email;

DROP TABLE buyer_import;
IMPORTSQL

# Verify import
docker exec mls-postgres psql -U n8n_user -d n8n -c 'SELECT count(*) as buyer_count FROM buyers;'
docker exec mls-postgres psql -U n8n_user -d n8n -c 'SELECT count(*) as criteria_count FROM search_criteria;'

cd ~/mls-agent

# Create backup script
cat > backups/backup.sh << 'BACKUPEOF'
#!/bin/bash
BACKUP_DIR=/home/mspadmin/mls-agent/backups
DATE=$(date +%Y%m%d_%H%M%S)

# Backup PostgreSQL
docker exec mls-postgres pg_dump -U n8n_user -d n8n --format=custom > "$BACKUP_DIR/db_$DATE.dump"

# Backup n8n workflows (export via API)
curl -s -u "$N8N_ADMIN_USER:$N8N_ADMIN_PASSWORD" https://alerts.clientbrokerage.com/api/v1/workflows > "$BACKUP_DIR/workflows_$DATE.json"

# Backup n8n credentials metadata (not secrets)
curl -s -u "$N8N_ADMIN_USER:$N8N_ADMIN_PASSWORD" https://alerts.clientbrokerage.com/api/v1/credentials > "$BACKUP_DIR/credentials_meta_$DATE.json"

# Compress
tar -czf "$BACKUP_DIR/full_backup_$DATE.tar.gz" "$BACKUP_DIR/db_$DATE.dump" "$BACKUP_DIR/workflows_$DATE.json" "$BACKUP_DIR/credentials_meta_$DATE.json"

# Clean up individual files
rm -f "$BACKUP_DIR/db_$DATE.dump" "$BACKUP_DIR/workflows_$DATE.json" "$BACKUP_DIR/credentials_meta_$DATE.json"

# Retain last 30 days of backups
find "$BACKUP_DIR" -name 'full_backup_*.tar.gz' -mtime +30 -delete

echo "Backup completed: full_backup_$DATE.tar.gz"
BACKUPEOF

chmod +x backups/backup.sh

# Source env vars for the backup script
cat > backups/backup_env.sh << 'BENVEOF'
export N8N_ADMIN_USER=$(grep N8N_ADMIN_USER /home/mspadmin/mls-agent/.env | cut -d= -f2)
export N8N_ADMIN_PASSWORD=$(grep N8N_ADMIN_PASSWORD /home/mspadmin/mls-agent/.env | cut -d= -f2)
BENVEOF

# Schedule daily backup at 2 AM
(crontab -l 2>/dev/null; echo '0 2 * * * source /home/mspadmin/mls-agent/backups/backup_env.sh && /home/mspadmin/mls-agent/backups/backup.sh >> /home/mspadmin/mls-agent/backups/backup.log 2>&1') | crontab -

# Optional: sync backups to offsite storage (S3, Backblaze B2, etc.)
# apt install -y rclone
# rclone config  (configure your preferred cloud storage)
# Add to cron: rclone sync ~/mls-agent/backups remote:mls-backups

# Option A: Use UptimeRobot (free tier: 50 monitors, 5-min intervals)
# https://uptimerobot.com
# Add HTTP(S) monitor: https://alerts.clientbrokerage.com (expect 200 or 401)
# Add keyword monitor: Check n8n health endpoint

# Option B: Simple self-hosted health check script
cat > ~/mls-agent/healthcheck.sh << 'HCEOF'
#!/bin/bash
# Check if all containers are running
for container in mls-n8n mls-postgres mls-nginx; do
    if ! docker inspect -f '{{.State.Running}}' $container 2>/dev/null | grep -q true; then
        echo "ALERT: $container is not running!" | mail -s "MLS Agent: Container Down" msp-alerts@yourmsp.com
        docker compose -f /home/mspadmin/mls-agent/docker-compose.yml up -d
    fi
done

# Check disk usage
DISK_USAGE=$(df -h / | awk 'NR==2{print $5}' | tr -d '%')
if [ "$DISK_USAGE" -gt 85 ]; then
    echo "ALERT: Disk usage at ${DISK_USAGE}%" | mail -s "MLS Agent: Disk Warning" msp-alerts@yourmsp.com
fi

# Check if the MLS poller workflow ran in the last 30 minutes
LAST_RUN=$(docker exec mls-postgres psql -U n8n_user -d n8n -t -c "SELECT COUNT(*) FROM listings_cache WHERE first_seen_at > NOW() - INTERVAL '30 minutes';")
if [ "$(echo $LAST_RUN | tr -d ' ')" = "0" ]; then
    echo "WARNING: No new listings cached in 30 minutes. MLS poller may be stalled." | mail -s "MLS Agent: Poller Warning" msp-alerts@yourmsp.com
fi
HCEOF
chmod +x ~/mls-agent/healthcheck.sh

# Run health check every 10 minutes
(crontab -l 2>/dev/null; echo '*/10 * * * * /home/mspadmin/mls-agent/healthcheck.sh >> /home/mspadmin/mls-agent/healthcheck.log 2>&1') | crontab -

# Install mailutils for email alerts (or use msmtp for SMTP relay)
sudo apt install -y msmtp msmtp-mta

# Configure msmtp for alert emails via SendGrid SMTP
cat > ~/.msmtprc << 'MSMTPEOF'
account default
host smtp.sendgrid.net
port 587
auth on
user apikey
password SG.YOUR_SENDGRID_API_KEY
tls on
tls_trust_file /etc/ssl/certs/ca-certificates.crt
from alerts@clientbrokerage.com
logfile ~/.msmtp.log
MSMTPEOF
chmod 600 ~/.msmtprc

SELECT mls_id FROM listings_cache WHERE mls_id = '{{ $json.mlsId }}';

// SimplyRETS RESO-format JSON normalization into local schema

const listing = $input.first().json;

// SimplyRETS response normalization
const normalized = {
  mls_id: listing.mlsId || listing.listingId,
  address: [
    listing.address?.streetNumberText,
    listing.address?.streetName,
    listing.address?.streetSuffix
  ].filter(Boolean).join(' '),
  city: listing.address?.city || '',
  state: listing.address?.state || '',
  zip_code: listing.address?.postalCode || '',
  price: listing.listPrice || 0,
  bedrooms: listing.property?.bedrooms || 0,
  bathrooms: listing.property?.bathsFull + (listing.property?.bathsHalf * 0.5) || 0,
  sqft: listing.property?.area || 0,
  lot_sqft: listing.property?.lotSize || 0,
  property_type: listing.property?.type || 'Unknown',
  year_built: listing.property?.yearBuilt || null,
  listing_date: listing.listDate || new Date().toISOString(),
  photo_url: listing.photos?.[0] || '',
  listing_url: `https://www.simplyrets.com/listings/${listing.mlsId}`,
  hoa_fee: listing.association?.fee || null,
  description: listing.remarks || '',
  features: [
    listing.property?.parking?.description,
    listing.property?.pool ? 'pool' : null,
    listing.property?.waterFront ? 'waterfront' : null,
    listing.property?.garage ? 'garage' : null,
  ].filter(Boolean),
  listing_data: JSON.stringify(listing)
};

return [{ json: normalized }];

INSERT INTO listings_cache (mls_id, address, city, state, zip_code, price, bedrooms, bathrooms, sqft, property_type, listing_date, photo_url, listing_url, listing_data)
VALUES (
  '{{ $json.mls_id }}',
  '{{ $json.address.replace(/'/g, "''") }}',
  '{{ $json.city }}',
  '{{ $json.state }}',
  '{{ $json.zip_code }}',
  {{ $json.price }},
  {{ $json.bedrooms }},
  {{ $json.bathrooms }},
  {{ $json.sqft }},
  '{{ $json.property_type }}',
  '{{ $json.listing_date }}',
  '{{ $json.photo_url }}',
  '{{ $json.listing_url }}',
  '{{ $json.listing_data.replace(/'/g, "''") }}'
)
ON CONFLICT (mls_id) DO UPDATE SET
  price = EXCLUDED.price,
  listing_data = EXCLUDED.listing_data,
  last_updated_at = NOW();

SELECT * FROM listings_cache WHERE mls_id = '{{ $json.new_listing_mls_id }}';

SELECT 
  b.id AS buyer_id,
  b.first_name,
  b.last_name,
  b.email,
  b.phone,
  b.assigned_agent_name,
  b.assigned_agent_email,
  b.assigned_agent_phone,
  b.email_consent,
  b.sms_consent,
  sc.id AS criteria_id,
  sc.criteria_name,
  sc.min_price,
  sc.max_price,
  sc.min_bedrooms,
  sc.min_bathrooms,
  sc.property_types,
  sc.zip_codes,
  sc.cities,
  sc.must_have_features,
  sc.exclude_features,
  sc.custom_notes
FROM search_criteria sc
JOIN buyers b ON b.id = sc.buyer_id
WHERE b.is_active = TRUE
  AND sc.is_active = TRUE
  -- Price range match
  AND (sc.min_price IS NULL OR {{ $json.price }} >= sc.min_price)
  AND (sc.max_price IS NULL OR {{ $json.price }} <= sc.max_price)
  -- Bedroom match
  AND (sc.min_bedrooms IS NULL OR {{ $json.bedrooms }} >= sc.min_bedrooms)
  -- Bathroom match
  AND (sc.min_bathrooms IS NULL OR {{ $json.bathrooms }} >= sc.min_bathrooms)
  -- Square footage match
  AND (sc.min_sqft IS NULL OR {{ $json.sqft }} >= sc.min_sqft)
  AND (sc.max_sqft IS NULL OR {{ $json.sqft }} <= sc.max_sqft)
  -- Property type match (if specified)
  AND (sc.property_types IS NULL OR '{{ $json.property_type }}' = ANY(sc.property_types))
  -- Location match: zip codes
  AND (sc.zip_codes IS NULL OR '{{ $json.zip_code }}' = ANY(sc.zip_codes))
  -- Location match: cities (case-insensitive)
  AND (sc.cities IS NULL OR LOWER('{{ $json.city }}') = ANY(SELECT LOWER(unnest(sc.cities))))
  -- Deduplication: haven't already sent this listing to this buyer
  AND NOT EXISTS (
    SELECT 1 FROM alert_history ah
    WHERE ah.buyer_id = b.id
    AND ah.listing_mls_id = '{{ $json.mls_id }}'
  )
ORDER BY b.id;

const response = $input.first().json.message.content;
const lines = response.split('\n');
let subject = '';
let emailBody = '';
let smsBody = '';

// Parse structured response
for (const line of lines) {
  if (line.startsWith('SUBJECT:')) subject = line.replace('SUBJECT:', '').trim();
  if (line.startsWith('EMAIL_BODY:')) emailBody = response.split('EMAIL_BODY:')[1].split('SMS_BODY:')[0].trim();
  if (line.startsWith('SMS_BODY:')) smsBody = response.split('SMS_BODY:')[1].trim();
}

return [{ json: { subject, emailBody, smsBody, ...($input.first().json) } }];

INSERT INTO alert_history (buyer_id, listing_mls_id, search_criteria_id, channel, personalized_message, subject_line, delivery_status)
VALUES ({{ $json.buyer_id }}, '{{ $json.mls_id }}', {{ $json.criteria_id }}, 'email', '{{ $json.emailBody.replace(/'/g, "''") }}', '{{ $json.subject.replace(/'/g, "''") }}', 'sent');

INSERT INTO alert_history (buyer_id, listing_mls_id, search_criteria_id, channel, personalized_message, delivery_status)
VALUES ({{ $json.buyer_id }}, '{{ $json.mls_id }}', {{ $json.criteria_id }}, 'sms', '{{ $json.smsBody.replace(/'/g, "''") }}', 'sent');

// Node 6: Build LLM Context
const listing = $('Fetch Listing Details').first().json;
const buyer = $input.first().json;

const userPrompt = `
Generate a personalized listing alert for this buyer and property:

BUYER INFORMATION:
- Name: ${buyer.first_name} ${buyer.last_name}
- Search Name: ${buyer.criteria_name || 'Primary Search'}
- Price Range: $${buyer.min_price?.toLocaleString() || 'Any'} - $${buyer.max_price?.toLocaleString() || 'Any'}
- Bedrooms Wanted: ${buyer.min_bedrooms || 'Any'}+
- Bathrooms Wanted: ${buyer.min_bathrooms || 'Any'}+
- Preferred Areas: ${buyer.cities?.join(', ') || buyer.zip_codes?.join(', ') || 'Not specified'}
- Must-Have Features: ${buyer.must_have_features?.join(', ') || 'None specified'}
- Property Types: ${buyer.property_types?.join(', ') || 'Any'}
- Additional Notes: ${buyer.custom_notes || 'None'}

NEW LISTING:
- Address: ${listing.address}, ${listing.city}, ${listing.state} ${listing.zip_code}
- Price: $${listing.price?.toLocaleString()}
- Bedrooms: ${listing.bedrooms}
- Bathrooms: ${listing.bathrooms}
- Square Feet: ${listing.sqft?.toLocaleString()}
- Property Type: ${listing.property_type}
- Year Built: ${listing.year_built || 'Unknown'}
- Description: ${listing.description?.substring(0, 500) || 'No description available'}
- Listing Photo: ${listing.photo_url || 'Not available'}

AGENT:
- Name: ${buyer.assigned_agent_name}
- Phone: ${buyer.assigned_agent_phone}
- Email: ${buyer.assigned_agent_email}

Generate the SUBJECT, EMAIL_BODY, and SMS_BODY now.
`;

return [{ json: { ...buyer, mls_id: listing.mls_id, price: listing.price, address: listing.address, city: listing.city, photo_url: listing.photo_url, listing_url: listing.listing_url, userPrompt } }];

<!-- used in the n8n SendGrid node with Content Type set to HTML -->

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>New Listing Alert</title>
</head>
<body style="margin:0;padding:0;background-color:#f4f4f4;font-family:Arial,Helvetica,sans-serif;">
  <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="max-width:600px;margin:0 auto;background:#ffffff;">
    <!-- Header -->
    <tr>
      <td style="background-color:#1a365d;padding:20px;text-align:center;">
        <h1 style="color:#ffffff;margin:0;font-size:22px;">🏡 New Listing Alert</h1>
        <p style="color:#a0c4ff;margin:5px 0 0;font-size:14px;">From {{ $json.assigned_agent_name }} at [Brokerage Name]</p>
      </td>
    </tr>
    <!-- Property Photo -->
    <tr>
      <td style="padding:0;">
        <img src="{{ $json.photo_url }}" alt="Property Photo" style="width:100%;height:auto;display:block;max-height:300px;object-fit:cover;" />
      </td>
    </tr>
    <!-- Property Details -->
    <tr>
      <td style="padding:20px;">
        <h2 style="color:#1a365d;margin:0 0 10px;font-size:20px;">{{ $json.address }}, {{ $json.city }}</h2>
        <table role="presentation" width="100%" cellpadding="8" cellspacing="0" style="background:#f0f7ff;border-radius:8px;margin:10px 0;">
          <tr>
            <td style="text-align:center;border-right:1px solid #d0e4ff;">
              <strong style="font-size:20px;color:#1a365d;">${{ $json.price?.toLocaleString() }}</strong><br>
              <span style="font-size:12px;color:#666;">Price</span>
            </td>
            <td style="text-align:center;border-right:1px solid #d0e4ff;">
              <strong style="font-size:20px;color:#1a365d;">{{ $json.bedrooms }}</strong><br>
              <span style="font-size:12px;color:#666;">Beds</span>
            </td>
            <td style="text-align:center;border-right:1px solid #d0e4ff;">
              <strong style="font-size:20px;color:#1a365d;">{{ $json.bathrooms }}</strong><br>
              <span style="font-size:12px;color:#666;">Baths</span>
            </td>
            <td style="text-align:center;">
              <strong style="font-size:20px;color:#1a365d;">{{ $json.sqft?.toLocaleString() }}</strong><br>
              <span style="font-size:12px;color:#666;">Sq Ft</span>
            </td>
          </tr>
        </table>
        <!-- Personalized Message -->
        <div style="padding:15px 0;font-size:15px;line-height:1.6;color:#333;">
          {{ $json.emailBody }}
        </div>
        <!-- CTA Button -->
        <table role="presentation" width="100%" cellpadding="0" cellspacing="0">
          <tr>
            <td style="text-align:center;padding:15px 0;">
              <a href="{{ $json.listing_url }}" style="background-color:#2563eb;color:#ffffff;padding:14px 30px;text-decoration:none;border-radius:6px;font-size:16px;font-weight:bold;display:inline-block;">View Full Listing</a>
            </td>
          </tr>
        </table>
      </td>
    </tr>
    <!-- Agent Contact -->
    <tr>
      <td style="padding:15px 20px;background:#f8fafc;border-top:1px solid #e2e8f0;">
        <p style="margin:0;font-size:14px;color:#333;"><strong>Your Agent:</strong> {{ $json.assigned_agent_name }}</p>
        <p style="margin:3px 0;font-size:14px;color:#333;">📞 {{ $json.assigned_agent_phone }} | 📧 {{ $json.assigned_agent_email }}</p>
      </td>
    </tr>
    <!-- MLS Attribution & Compliance Footer -->
    <tr>
      <td style="padding:15px 20px;background:#e2e8f0;font-size:11px;color:#666;">
        <p style="margin:0;">Listing data provided by [MLS Name]. Information deemed reliable but not guaranteed. Listing courtesy of the listing broker. MLS# {{ $json.mls_id }}.</p>
        <p style="margin:8px 0 0;">© {{ new Date().getFullYear() }} [Brokerage Legal Name] | [Brokerage Address]</p>
        <p style="margin:8px 0 0;"><a href="{{ $json.unsubscribe_url || '#' }}" style="color:#2563eb;">Unsubscribe from listing alerts</a> | <a href="[privacy_policy_url]" style="color:#2563eb;">Privacy Policy</a></p>
      </td>
    </tr>
  </table>
</body>
</html>

const contact = $input.first().json;

// Follow Up Boss custom fields mapping
// These must be created in FUB by the client's admin
const customFields = contact.customFields || {};

const buyer = {
  external_crm_id: String(contact.id),
  first_name: contact.firstName || '',
  last_name: contact.lastName || '',
  email: contact.emails?.[0]?.value || null,
  phone: contact.phones?.[0]?.value || null,
  assigned_agent_name: contact.assignedTo?.name || '',
  assigned_agent_email: contact.assignedTo?.email || '',
  assigned_agent_phone: '', // FUB may not expose agent phone via API
  email_consent: customFields['AI Alert Email Consent'] === 'Yes',
  sms_consent: customFields['AI Alert SMS Consent'] === 'Yes',
  // Search criteria from custom fields
  min_price: parseFloat(customFields['AI Alert Min Price']) || null,
  max_price: parseFloat(customFields['AI Alert Max Price']) || null,
  min_bedrooms: parseInt(customFields['AI Alert Min Beds']) || null,
  min_bathrooms: parseFloat(customFields['AI Alert Min Baths']) || null,
  property_types: customFields['AI Alert Property Types']?.split(',').map(s => s.trim()) || null,
  zip_codes: customFields['AI Alert Zip Codes']?.split(',').map(s => s.trim()) || null,
  cities: customFields['AI Alert Cities']?.split(',').map(s => s.trim()) || null,
  must_have_features: customFields['AI Alert Must Have']?.split(',').map(s => s.trim()) || null,
  custom_notes: customFields['AI Alert Notes'] || null
};

return [{ json: buyer }];

INSERT INTO buyers (external_crm_id, first_name, last_name, email, phone, assigned_agent_name, assigned_agent_email, email_consent, email_consent_timestamp, sms_consent, sms_consent_timestamp, updated_at)
VALUES (
  '{{ $json.external_crm_id }}',
  '{{ $json.first_name }}',
  '{{ $json.last_name }}',
  {{ $json.email ? "'" + $json.email + "'" : 'NULL' }},
  {{ $json.phone ? "'" + $json.phone + "'" : 'NULL' }},
  '{{ $json.assigned_agent_name }}',
  '{{ $json.assigned_agent_email }}',
  {{ $json.email_consent }},
  {{ $json.email_consent ? 'NOW()' : 'NULL' }},
  {{ $json.sms_consent }},
  {{ $json.sms_consent ? 'NOW()' : 'NULL' }},
  NOW()
)
ON CONFLICT (external_crm_id) DO UPDATE SET
  first_name = EXCLUDED.first_name,
  last_name = EXCLUDED.last_name,
  email = EXCLUDED.email,
  phone = EXCLUDED.phone,
  assigned_agent_name = EXCLUDED.assigned_agent_name,
  assigned_agent_email = EXCLUDED.assigned_agent_email,
  email_consent = EXCLUDED.email_consent,
  sms_consent = EXCLUDED.sms_consent,
  updated_at = NOW()
RETURNING id;

INSERT INTO search_criteria (buyer_id, min_price, max_price, min_bedrooms, min_bathrooms, property_types, zip_codes, cities, must_have_features, custom_notes, updated_at)
VALUES (
  {{ $json.id }},
  {{ $json.min_price || 'NULL' }},
  {{ $json.max_price || 'NULL' }},
  {{ $json.min_bedrooms || 'NULL' }},
  {{ $json.min_bathrooms || 'NULL' }},
  {{ $json.property_types ? "ARRAY['" + $json.property_types.join("','") + "']" : 'NULL' }},
  {{ $json.zip_codes ? "ARRAY['" + $json.zip_codes.join("','") + "']" : 'NULL' }},
  {{ $json.cities ? "ARRAY['" + $json.cities.join("','") + "']" : 'NULL' }},
  {{ $json.must_have_features ? "ARRAY['" + $json.must_have_features.join("','") + "']" : 'NULL' }},
  {{ $json.custom_notes ? "'" + $json.custom_notes.replace(/'/g, "''") + "'" : 'NULL' }},
  NOW()
)
ON CONFLICT ON CONSTRAINT search_criteria_pkey DO UPDATE SET
  min_price = EXCLUDED.min_price,
  max_price = EXCLUDED.max_price,
  updated_at = NOW();

UPDATE buyers
SET sms_consent = FALSE,
    updated_at = NOW()
WHERE phone = '{{ $json.From }}'
RETURNING id, first_name, last_name, email;

INSERT INTO alert_history (buyer_id, channel, personalized_message, delivery_status, sent_at)
VALUES ({{ $json.id }}, 'sms', 'OPT-OUT: Buyer replied STOP', 'opt_out', NOW());

<?xml version="1.0" encoding="UTF-8"?>
<Response>
  <Message>You have been unsubscribed from listing alerts. Reply START to re-subscribe.</Message>
</Response>

const crypto = require('crypto');
const secret = 'YOUR_UNSUBSCRIBE_SECRET'; // Set as n8n environment variable
const expectedToken = crypto.createHmac('sha256', secret)
  .update($json.query.buyer_id)
  .digest('hex');

if ($json.query.token !== expectedToken) {
  throw new Error('Invalid unsubscribe token');
}
return $input.all();

UPDATE buyers SET email_consent = FALSE, updated_at = NOW()
WHERE id = {{ $json.query.buyer_id }};

SELECT ah.id, ah.personalized_message, ah.subject_line, ah.sent_at,
       b.first_name, b.last_name
FROM alert_history ah
JOIN buyers b ON b.id = ah.buyer_id
WHERE ah.sent_at > NOW() - INTERVAL '7 days'
AND ah.channel = 'email'
ORDER BY ah.sent_at DESC;

const items = $input.all();
const flagged = [];

// Protected class related terms and phrases to flag
const prohibitedPatterns = [
  // Race/National Origin
  /\b(white|black|african|hispanic|latino|asian|chinese|mexican|indian|arab|jewish|christian|muslim|catholic)\s*(neighborhood|area|community|district|enclave)/gi,
  /\b(ethnic|racial|minority|diverse|homogeneous)\b/gi,
  // Religion
  /\b(church|mosque|synagogue|temple)\s*(nearby|close|walking)/gi,
  // Familial Status
  /\b(no\s*kids|child-?free|adult\s*(only|community|living)|singles\s*only|couples\s*only|empty\s*nest)/gi,
  /\b(55\+|senior\s*(only|living|community)|age\s*restricted)/gi,
  // Sex/Gender
  /\b(bachelor|bachelorette|man\s*cave|she\s*shed|master\s*(bedroom|suite|bath))\b/gi,
  // Disability
  /\b(handicap|cripple|wheelchair\s*bound|mentally|insane\s*asylum)\b/gi,
  // Steering language
  /\b(perfect\s*for\s*(families|singles|couples|retirees|young\s*professionals))/gi,
  /\b(up\s*and\s*coming|gentrifying|transitional|rough|dangerous|safe)\s*(area|neighborhood)/gi,
  // School quality as proxy
  /\b(best|top|great|excellent|poor|bad)\s*school/gi
];

for (const item of items) {
  const text = (item.json.personalized_message || '') + ' ' + (item.json.subject_line || '');
  const matches = [];
  
  for (const pattern of prohibitedPatterns) {
    const found = text.match(pattern);
    if (found) {
      matches.push(...found);
    }
  }
  
  if (matches.length > 0) {
    flagged.push({
      alert_id: item.json.id,
      buyer: `${item.json.first_name} ${item.json.last_name}`,
      sent_at: item.json.sent_at,
      flagged_terms: matches.join(', '),
      message_excerpt: text.substring(0, 200)
    });
  }
}

return [{ json: { total_reviewed: items.length, total_flagged: flagged.length, flagged_items: flagged } }];

cd ~/mls-agent && docker compose pull && docker compose up -d