Azure Cosmos DB Integration Setup

Source: `docs/integrations/AZURE_COSMOS_DB_SETUP.md`View on GitHub

Azure Cosmos DB Integration Setup

This guide explains how to set up Azure Cosmos DB integration for syncing rental listings to your CRM.

Overview

The Azure Cosmos DB integration allows you to:

Sync rental listings from Azure Cosmos DB to your Supabase properties table
Preserve CRM data (status, priority, notes, PM company assignments)
Auto-link properties to Property Management Companies by matching names
Keep listings updated with the latest data from Azure

Architecture

Azure Cosmos DB (Listings)
         ↓
    Sync Service
         ↓
Supabase Properties Table
         ↓
CRM Features (Inspections, Units, Contacts)

Step 1: Install Azure Cosmos DB SDK

npm install @azure/cosmos

Step 2: Get Azure Cosmos DB Credentials

From your Azure Portal:

Navigate to your Cosmos DB account
Go to Keys section in the left sidebar
Copy the following values:
- URI (Endpoint)
- PRIMARY KEY (or Secondary Key)
- Database Name
- Container Name (where your listings are stored)

Step 3: Add Environment Variables

Add these variables to your .env.local file:

# Existing Supabase credentials
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-supabase-anon-key

# Azure Cosmos DB credentials
AZURE_COSMOS_ENDPOINT=https://your-account.documents.azure.com:443/
AZURE_COSMOS_KEY=your-primary-or-secondary-key-here
AZURE_COSMOS_DATABASE_ID=your-database-name
AZURE_COSMOS_CONTAINER_ID=listings

Important Security Notes:

✅ .env.local is already in .gitignore - never commit it
✅ Azure credentials are server-side only (not exposed to browser)
✅ Use PRIMARY KEY for production, can use SECONDARY KEY for development
❌ Never expose these credentials in client-side code

Step 4: Run Database Migration

Run this SQL in your Supabase SQL Editor:

# File: DATABASE_MIGRATION_AZURE_LISTINGS.sql

This adds all necessary columns to the properties table:

Azure reference fields (azure_listing_id, sync_status)
Listing fields (price, beds, baths, description)
Property manager fields
Images, fees, and metadata

Step 5: Verify Configuration

Check Azure configuration status:

# GET request to check status
curl http://localhost:3000/api/sync-properties

Or in your application, the sync button will show an error if Azure is not configured.

Step 6: Run Your First Sync

Navigate to the Properties tab in your CRM
Click the "Sync Azure" button (purple button next to "Add Property")
Confirm the sync operation
Wait for completion (progress shown in console)

The sync will:

✅ Fetch listings from Azure Cosmos DB
✅ Create new properties for new listings
✅ Update existing properties with latest Azure data
✅ Preserve your CRM fields (status, priority, notes, PM assignments)
✅ Auto-link properties to PMCs by matching property manager names

Data Flow

Azure Fields (Read-Only from Azure)

These fields are overwritten on each sync:

address, city, state, zip_code
price, beds, baths, sqft
description, full_description
property_manager, property_manager_phone
images, availability, market_status
pets, parking_detail, fees
All Azure timestamps

CRM Fields (Preserved During Sync)

These fields are never overwritten by sync:

status (Active, Prospect, etc.)
priority (High, Medium, Low)
notes (Your internal notes)
pm_company_id (Your PM company assignment)
owner_contact_id
management_type

CRM-Only Tables (Not in Azure)

These are completely separate from Azure:

Inspections - Track property inspections
Units - Manage individual units and tenants
Contacts - Store contact information
Property-Contact relationships - Link contacts to properties

Sync Frequency

Manual Sync

Click "Sync Azure" button anytime
Best for testing and on-demand updates

Automatic Sync (Optional)

You can set up automatic syncing using:

Option A: Vercel Cron (if deployed on Vercel)

// vercel.json
{
  "crons": [{
    "path": "/api/sync-properties",
    "schedule": "0 */6 * * *"  // Every 6 hours
  }]
}

Option B: GitHub Actions

# .github/workflows/sync-azure.yml
name: Sync Azure Listings
on:
  schedule:
    - cron: '0 */6 * * *'  # Every 6 hours
  workflow_dispatch:  # Manual trigger

jobs:
  sync:
    runs-on: ubuntu-latest
    steps:
      - name: Call sync API
        run: |
          curl -X POST https://your-domain.com/api/sync-properties

Auto-Linking Properties to PMCs

The sync automatically links properties to Property Management Companies by matching the property_manager field from Azure with your PMC company_name.

Example:

Azure listing has property_manager: "ABC Property Management"
You have a PMC named "ABC Property Management"
→ Automatically linked during sync

Monitoring Sync Status

Check Sync Status

GET /api/sync-properties

Returns:

Azure configuration status
Last sync date
Number of synced/manual properties

View Sync Health

Use this SQL query in Supabase:

SELECT * FROM properties_sync_status
ORDER BY last_synced_at DESC;

This view shows:

Which properties came from Azure
When they were last synced
Sync health (Up to Date, Outdated, Never Synced, Manual Entry)

Troubleshooting

"Azure Cosmos DB is not configured"

Check that all environment variables are set in .env.local
Restart your development server after adding variables
Verify credentials are correct in Azure Portal

"Sync failed: Request rate is large"

Azure Cosmos DB has rate limits (Request Units)
Reduce sync frequency
Consider upgrading your Azure tier

"Properties not showing up"

Check the query filter (state = "Oregon")
Verify data exists in Azure with that filter
Check browser console for errors

"Auto-linking not working"

Property manager name must partially match PMC company name
Case-insensitive matching is used
Example: "ABC Property Mgmt" will match "ABC Property Management"

"Existing properties being overwritten"

This is expected for Azure fields
CRM fields (status, notes, etc.) are always preserved
If a field shouldn't be overwritten, check if it's in the "preserve" list

Field Mapping Reference

Azure Field	Supabase Column	Overwritten on Sync?
`id`	`azure_listing_id`	Yes
`address`	`address`	Yes
`city`	`city`	Yes
`price`	`price`	Yes
`beds`	`beds`	Yes
`baths`	`baths`	Yes
`property_manager`	`property_manager`	Yes
-	`status`	No (CRM field)
-	`priority`	No (CRM field)
-	`notes`	No (CRM field)
-	`pm_company_id`	No (CRM field)

Full mapping available in DATABASE_MIGRATION_AZURE_LISTINGS.sql

API Endpoints

POST /api/sync-properties

Trigger a sync from Azure to Supabase

Request Body:

{
  "state": "Oregon",
  "city": "Portland",  // Optional
  "limit": 1000        // Optional
}

Response:

{
  "success": true,
  "result": {
    "total": 1500,
    "created": 200,
    "updated": 1300,
    "autoLinked": 450,
    "errors": 0
  }
}

GET /api/sync-properties

Get sync status and Azure configuration

Response:

{
  "success": true,
  "azureStatus": {
    "configured": true,
    "endpoint": "✓ Set",
    "key": "✓ Set",
    "database": "rental-listings",
    "container": "listings"
  },
  "syncStats": {
    "total": 1500,
    "synced": 1450,
    "manual": 50,
    "error": 0,
    "lastSyncDate": "2025-11-18T10:30:00Z"
  }
}

Best Practices

Test First: Run a limited sync first with limit: 10 to verify
Regular Syncs: Sync at least daily to keep data fresh
Monitor Errors: Check error logs regularly
Backup Before Major Sync: Use Supabase's backup feature
Review Auto-Links: Periodically check auto-linked properties for accuracy

Support & Questions

If you encounter issues:

Check the console logs for detailed error messages
Verify Azure Cosmos DB is accessible from your server
Test the API endpoint directly with curl/Postman
Check Supabase logs for database errors

Files Created

lib/cosmosdb.ts - Azure Cosmos DB configuration
lib/azureSync.ts - Sync logic and data transformation
app/api/sync-properties/route.ts - API endpoint
DATABASE_MIGRATION_AZURE_LISTINGS.sql - Database schema changes
lib/supabase.ts - Updated Property interface
app/components/PropertiesSection.tsx - Updated with sync button

Next Steps

After successful sync:

✅ Review synced properties
✅ Assign properties to PMCs (if not auto-linked)
✅ Add inspections, units, and contacts as needed
✅ Update status and priority for each property
✅ Set up automatic syncing for ongoing updates

Use links in each imported doc to open its source.