# 27 — Private & Off-Market Listings Hub

**Status:** Planning | **Depends on:** 01 (Geographic Extraction), 02 (PostgreSQL), 09 (RealScout Replacement)
**Estimated effort:** 120 hours | **Phase:** After platform replacements | **Type:** Feature module

---

## OVERVIEW

The Private Listings Hub is a secure, controlled-visibility platform for off-market and pre-market listings where agents maintain granular control over who sees what. Inspired by Luxury Presence's Private Listings Hub (launched March 2025), this module adds intelligent buyer matching, engagement tracking, and NAR Clear Cooperation compliance to Fogbreak's listing ecosystem.

**What it replaces:** None directly (new capability). Supplements traditional MLS listings with off-market distribution for luxury/complex deals. Addresses broker workflows where listings are intentionally withheld from MLS during staging, pre-marketing, or network-only phases.

**What it adds:**
- Private listing creation with granular visibility controls (by field, by user role, by specific agent)
- Buyer needs database (structured buyer requirements linked to CRM clients)
- Intelligent matching engine (rank private listings against buyer profiles by fit score)
- Engagement tracking (views, interest, showing requests, follow-up)
- Controlled distribution (office-exclusive, agent-invite, network broadcast, MLS promotion)
- NAR Clear Cooperation compliance monitoring
- Integration with showings, CRM, notifications, analytics

**Why this matters:** 75% of luxury real estate transactions start off-market. Agents need a secure, auditable platform to control visibility while coordinating with their teams and network. Existing tools (spreadsheets, email threads) create compliance risk and poor visibility. This module gives agents transparency into engagement and automated buyer matching.

---

## DATABASE SCHEMA

### Table: private_listings

```sql
CREATE TABLE private_listings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  property_id INT REFERENCES properties(id),
  listing_agent_id INT REFERENCES admins(id),
  listing_title VARCHAR(255) NOT NULL,
  description LONGTEXT,
  price DECIMAL(12,2),
  bedrooms INT,
  bathrooms DECIMAL(4,1),
  sqft INT,
  lot_size VARCHAR(50),
  year_built INT,
  address VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(2),
  zip VARCHAR(10),
  latitude DECIMAL(10,8),
  longitude DECIMAL(11,8),

  -- Visibility: 'public', 'brokerage-only', 'invite-only', 'agents-only'
  visibility_level VARCHAR(50) DEFAULT 'invite-only',

  -- Status: 'draft', 'active', 'promoted-to-mls', 'closed', 'withdrawn'
  status VARCHAR(50) DEFAULT 'draft',

  -- Compliance: track MLS submission deadline if applicable
  office_exclusive_until DATETIME,
  mls_ready_date DATETIME,

  -- Engagement settings
  show_price_to_public TINYINT DEFAULT 0,
  show_address_to_public TINYINT DEFAULT 0,
  show_photos_to_public TINYINT DEFAULT 0,
  show_details_to_agents TINYINT DEFAULT 1,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (property_id) REFERENCES properties(id),
  FOREIGN KEY (listing_agent_id) REFERENCES admins(id),
  INDEX (tenant_id, status),
  INDEX (listing_agent_id),
  INDEX (visibility_level)
);
```

### Table: private_listing_access

Controls field-level visibility. One row per user/access level.

```sql
CREATE TABLE private_listing_access (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  listing_id INT REFERENCES private_listings(id),
  user_id INT REFERENCES admins(id),
  access_type VARCHAR(50) DEFAULT 'view',

  -- Field visibility flags (JSON for scalability, or separate columns)
  can_see_price TINYINT DEFAULT 1,
  can_see_address TINYINT DEFAULT 1,
  can_see_photos TINYINT DEFAULT 1,
  can_see_details TINYINT DEFAULT 1,
  can_request_showing TINYINT DEFAULT 1,
  can_express_interest TINYINT DEFAULT 1,
  can_contact_listing_agent TINYINT DEFAULT 1,

  granted_by INT REFERENCES admins(id),
  granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (listing_id) REFERENCES private_listings(id),
  FOREIGN KEY (user_id) REFERENCES admins(id),
  INDEX (tenant_id, listing_id),
  INDEX (user_id)
);
```

### Table: buyer_needs

Structured buyer requirement profiles linked to CRM clients.

```sql
CREATE TABLE buyer_needs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  client_id INT REFERENCES clients(id),
  agent_id INT REFERENCES admins(id),

  -- Search criteria (JSON for flexibility)
  min_price DECIMAL(12,2),
  max_price DECIMAL(12,2),
  min_sqft INT,
  max_sqft INT,
  min_bedrooms INT,
  max_bedrooms INT,
  min_bathrooms DECIMAL(4,1),
  max_bathrooms DECIMAL(4,1),

  -- Geography (multiple allowed; JSON array of { city, zip, market_area })
  preferred_locations JSON,

  -- Features (JSON array: { feature, importance: 'must-have' | 'nice-to-have' })
  features JSON,

  -- Timeline: when buyer intends to close
  timeline_months INT,

  -- Status: 'active', 'on-hold', 'closed'
  status VARCHAR(50) DEFAULT 'active',

  -- Timestamps and metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_matched_at DATETIME,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (client_id) REFERENCES clients(id),
  FOREIGN KEY (agent_id) REFERENCES admins(id),
  INDEX (tenant_id, client_id),
  INDEX (status),
  INDEX (agent_id)
);
```

### Table: buyer_matches

Records listing-to-buyer match results with match scores.

```sql
CREATE TABLE buyer_matches (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  listing_id INT REFERENCES private_listings(id),
  buyer_need_id INT REFERENCES buyer_needs(id),

  -- Match scoring (0-100)
  price_score DECIMAL(5,2),
  size_score DECIMAL(5,2),
  location_score DECIMAL(5,2),
  feature_score DECIMAL(5,2),
  overall_score DECIMAL(5,2),

  -- Match reason (why this listing matches: "Price point match", "Location match", "Feature match", etc.)
  match_reason VARCHAR(255),

  -- Status: 'new', 'notified', 'interested', 'showing-scheduled', 'offer-made', 'withdrawn'
  status VARCHAR(50) DEFAULT 'new',

  -- Track notifications
  notified_agent_id INT REFERENCES admins(id),
  notified_at DATETIME,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (listing_id) REFERENCES private_listings(id),
  FOREIGN KEY (buyer_need_id) REFERENCES buyer_needs(id),
  FOREIGN KEY (notified_agent_id) REFERENCES admins(id),
  INDEX (tenant_id, listing_id),
  INDEX (status),
  INDEX (overall_score DESC),
  UNIQUE (listing_id, buyer_need_id)
);
```

### Table: private_listing_views

Engagement tracking: who viewed what, when, how long.

```sql
CREATE TABLE private_listing_views (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  listing_id INT REFERENCES private_listings(id),
  viewer_id INT REFERENCES admins(id),

  -- View details
  view_type VARCHAR(50) DEFAULT 'listing-page',
  duration_seconds INT,
  fields_viewed JSON,

  -- Engagement
  expressed_interest TINYINT DEFAULT 0,
  requested_showing TINYINT DEFAULT 0,
  requested_info TINYINT DEFAULT 0,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (listing_id) REFERENCES private_listings(id),
  FOREIGN KEY (viewer_id) REFERENCES admins(id),
  INDEX (tenant_id, listing_id),
  INDEX (viewer_id),
  INDEX (created_at)
);
```

### Table: private_listing_invites

Track agent invitations and acceptance/rejection.

```sql
CREATE TABLE private_listing_invites (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT,
  listing_id INT REFERENCES private_listings(id),
  inviting_agent_id INT REFERENCES admins(id),
  invited_agent_id INT REFERENCES admins(id),
  invited_email VARCHAR(255),

  -- Status: 'pending', 'accepted', 'rejected', 'expired'
  status VARCHAR(50) DEFAULT 'pending',

  -- Invite token (for email link)
  invite_token VARCHAR(255) UNIQUE,
  expires_at DATETIME,
  accepted_at DATETIME,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (listing_id) REFERENCES private_listings(id),
  FOREIGN KEY (inviting_agent_id) REFERENCES admins(id),
  FOREIGN KEY (invited_agent_id) REFERENCES admins(id),
  INDEX (tenant_id, listing_id),
  INDEX (invited_agent_id),
  INDEX (invite_token)
);
```

---

## API ENDPOINTS

All endpoints follow standard response format: `{"success": true/false, "data": {...}, "message": "..."}`. Auth required for all.

### POST /api/private_listings.php?action=createPrivateListing

Create a new private listing.

**Request:**
```json
{
  "property_id": 123,
  "listing_title": "Carmel Estate - Oceanfront",
  "description": "...",
  "price": 8500000,
  "bedrooms": 5,
  "bathrooms": 4.5,
  "sqft": 6200,
  "visibility_level": "invite-only",
  "show_price_to_public": false,
  "show_address_to_public": false,
  "show_photos_to_public": false,
  "office_exclusive_until": "2026-04-15"
}
```

**Response:**
```json
{
  "success": true,
  "data": {
    "listing_id": 456,
    "status": "draft",
    "visibility_level": "invite-only"
  },
  "message": "Private listing created. Share invite link with agents."
}
```

**Implementation:**
- Validate tenant_id from session
- Validate property_id ownership (listing_agent_id = current user or is admin)
- Insert row into `private_listings` with tenant_id
- Return listing_id and invite link

---

### PUT /api/private_listings.php?action=updateVisibility

Update visibility settings for a listing.

**Request:**
```json
{
  "listing_id": 456,
  "visibility_level": "brokerage-only",
  "show_price_to_public": true,
  "show_address_to_public": false
}
```

**Implementation:**
- Verify current user is listing_agent_id or admin
- Update visibility flags
- Log change to audit trail (future: compliance_log table)

---

### POST /api/private_listings.php?action=inviteAgents

Send invite to specific agents.

**Request:**
```json
{
  "listing_id": 456,
  "agent_ids": [10, 11, 12],
  "message": "Check this out for your buyer client."
}
```

**Implementation:**
- Generate invite_token (random 64-char string)
- Insert rows into `private_listing_invites`
- Send emails via email.php with invite link
- Set expires_at to 14 days from now

---

### POST /api/private_listings.php?action=revokeAccess

Revoke access to a listing from a specific agent.

**Request:**
```json
{
  "listing_id": 456,
  "user_id": 10
}
```

**Implementation:**
- Set revoked_at = NOW() in `private_listing_access`
- Remove from all shares
- Notify revoked user (optional, configurable)

---

### GET /api/private_listings.php?action=getMatches

Get list of matching buyers for a private listing.

**Query params:** `listing_id`, `status` (optional, filter by 'new', 'interested', etc.)

**Response:**
```json
{
  "success": true,
  "data": {
    "matches": [
      {
        "match_id": 1001,
        "buyer_name": "John Doe",
        "buyer_agent": "Jane Smith",
        "overall_score": 92,
        "match_reason": "Price and location perfect fit",
        "status": "new",
        "notified_at": null
      }
    ],
    "total": 3
  }
}
```

**Implementation:**
- Query `buyer_matches` for listing_id, ordered by overall_score DESC
- Return top matches with buyer details from clients/admins
- Exclude withdrawn/closed matches

---

### POST /api/private_listings.php?action=documentBuyerNeed

Create or update buyer need profile.

**Request:**
```json
{
  "client_id": 789,
  "min_price": 5000000,
  "max_price": 10000000,
  "min_bedrooms": 4,
  "max_bedrooms": 6,
  "preferred_locations": [
    { "city": "Carmel-by-the-Sea", "importance": "must-have" },
    { "city": "Pacific Grove", "importance": "nice-to-have" }
  ],
  "features": [
    { "feature": "Ocean view", "importance": "must-have" },
    { "feature": "Wine cellar", "importance": "nice-to-have" }
  ],
  "timeline_months": 6
}
```

**Implementation:**
- Insert/update `buyer_needs` with tenant_id
- Immediately trigger matchmaking job: score against all active private listings in tenant
- Insert matches into `buyer_matches` table
- Return newly created buyer_need_id and match count

---

### GET /api/private_listings.php?action=getEngagementReport

Get engagement metrics for a private listing.

**Query params:** `listing_id`

**Response:**
```json
{
  "success": true,
  "data": {
    "listing_id": 456,
    "total_views": 47,
    "unique_viewers": 12,
    "average_duration": 342,
    "expressed_interest": 8,
    "showing_requests": 3,
    "info_requests": 5,
    "buyers_matched": 6,
    "timeline": [
      { "date": "2026-03-28", "views": 12, "interest": 2 }
    ]
  }
}
```

**Implementation:**
- Query `private_listing_views` for listing_id
- Aggregate by date, engagement type
- Return engagement summary + timeline

---

### GET /api/private_listings.php?action=checkComplianceStatus

Check NAR Clear Cooperation compliance for a listing.

**Query params:** `listing_id`

**Response:**
```json
{
  "success": true,
  "data": {
    "compliance_status": "office-exclusive",
    "office_exclusive_until": "2026-04-15",
    "days_remaining": 17,
    "alert": null,
    "mls_ready_date": "2026-04-20",
    "recommendation": "Ready to submit to MLS on 2026-04-20"
  }
}
```

**Implementation:**
- Compare office_exclusive_until to current date
- If < 3 days away, return warning alert
- Return compliance status and recommendation

---

### POST /api/private_listings.php?action=promoteToMLS

Promote a private listing to MLS.

**Request:**
```json
{
  "listing_id": 456,
  "mls_notes": "Previously marketed off-market"
}
```

**Implementation:**
- Validate listing is in 'active' status
- Update status to 'promoted-to-mls'
- Create corresponding entry in properties table with mls_id (if MLS API integration exists, instruction 18)
- Notify all users with access
- Log to audit trail

---

### GET /api/private_listings.php?action=getPrivateListingFeed

Get paginated feed of private listings visible to current user.

**Query params:** `limit` (default 20), `offset`, `visibility` (filter by visibility level)

**Response:**
```json
{
  "success": true,
  "data": {
    "listings": [
      {
        "listing_id": 456,
        "title": "Carmel Estate",
        "price": 8500000,
        "status": "active",
        "visibility_level": "invite-only",
        "listing_agent": "Kean Matthams",
        "matched_to_you": true,
        "match_score": 92
      }
    ],
    "total": 14,
    "offset": 0
  }
}
```

**Implementation:**
- Query `private_listings` + `private_listing_access` to filter by user permissions
- Join with `buyer_matches` if user has active buyer needs
- Return paginated results

---

## IMPLEMENTATION STEPS

### Phase 1: Core Data Model (Week 1)

1. **Create migration script** (`migrations/27-private-listings.sql`)
   - Create 6 new tables with indexes
   - Add foreign key constraints
   - Add audit trail logging to config.php helpers

2. **Create api/private_listings.php** (new module)
   - Auto-migrate tables on first request (per architecture pattern)
   - Implement createPrivateListing, updateVisibility, inviteAgents endpoints
   - Standard auth checks (session token validation)
   - Use PDO prepared statements for all queries

3. **Update properties.php**
   - Add getPrivateListingByPropertyId endpoint
   - Integrate private listing status into property detail view

### Phase 2: Matching Engine (Week 2)

4. **Create matching algorithm** (api/private_listings.php → matchListingToBuyers function)
   - Score price fit: `100 - (abs(listing_price - buyer_avg_price) / buyer_max_price * 100)`
   - Score location: fuzzy match on city/zip against preferred_locations, return 0-100
   - Score features: count matching features as % of buyer feature list
   - Combine scores: `(price_score * 0.4) + (location_score * 0.3) + (feature_score * 0.3)`
   - Insert results into `buyer_matches` with overall_score
   - Suppress matches < 60 score (configurable threshold)

5. **Create cron job** (cron.php)
   - Job: `private_listing_matching`
   - Run every 4 hours
   - Detect new private_listings (created_at in last 4 hours)
   - For each new listing, call matchListingToBuyers
   - Queue notifications to relevant agents (via email.php)

6. **Add to drips.php**
   - Create drip campaign: "Private Listing Match" (triggered by buyer_matches.status='new')
   - Email template: "{buyer_agent}, we found {listing_title} for {buyer_name}. Match score: {overall_score}. [View Listing Button]"
   - Include all relevant listing details (address, price, features, photos)

### Phase 3: Engagement & Compliance (Week 3)

7. **Implement engagement tracking**
   - Update frontend (fogbreak.html) private listing view page
   - On page load, record view: POST to createPrivateListingView
   - Track time spent (count_ms), fields viewed (JSON), interest/showing/info clicks
   - Insert into `private_listing_views`

8. **Implement NAR compliance monitoring**
   - Add checkComplianceStatus endpoint
   - Create alert system: if office_exclusive_until < 3 days, fire notification
   - Add to daily_action_feed.js: compliance deadlines as high-priority items
   - Recommend MLS promotion date based on exclusive period

9. **Add access control**
   - Update fogbreak.html template: visibility_level selector
   - Show field-level toggles: show_price, show_address, show_photos
   - Update inviteAgents dialog: search by agent name, send custom message
   - Revoke access: one-click in agent list

### Phase 4: Analytics & Integration (Week 4)

10. **Add engagement report**
    - getEngagementReport endpoint
    - Dashboard card in fogbreak.html: "Engagement" tab shows views, interest, showings by listing
    - Chart: views over time, funnel (views → interest → showing → offer)
    - Export to CSV

11. **Integrate with showings.php**
    - When private listing showing requested, create entry in showings table
    - Link to private_listing_id
    - Track feedback → buyer_matches.status update

12. **Update CRM**
    - When buyer_needs created, auto-create/update client record in clients table
    - Link buyer_matches to client interactions (log match as interaction event)
    - Update lead scoring: buyers with active needs get boost

13. **Integration testing**
    - Create private listing as Kean → invite Sharon
    - Sharon views → engagement recorded
    - Document buyer needs for test client
    - Matching job runs → buyer_matches created
    - drip campaign fires → email received
    - Promote to MLS → status updated
    - Check compliance report

---

## DATABASE QUERIES (PDO Examples)

### Insert private listing
```php
$stmt = $db->prepare("
  INSERT INTO private_listings (tenant_id, property_id, listing_agent_id, listing_title,
                                 price, bedrooms, bathrooms, visibility_level, status, created_at)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
");
$stmt->execute([$tenant_id, $property_id, $listing_agent_id, $title, $price, $beds, $baths, $vis, 'draft']);
```

### Get matches for listing
```php
$stmt = $db->prepare("
  SELECT bm.id, bm.overall_score, bm.match_reason, bm.status,
         c.name as buyer_name, a.email as buyer_agent
  FROM buyer_matches bm
  JOIN buyer_needs bn ON bm.buyer_need_id = bn.id
  JOIN clients c ON bn.client_id = c.id
  JOIN admins a ON bn.agent_id = a.id
  WHERE bm.tenant_id = ? AND bm.listing_id = ?
  ORDER BY bm.overall_score DESC
");
$stmt->execute([$tenant_id, $listing_id]);
$matches = $stmt->fetchAll(PDO::FETCH_ASSOC);
```

### Check compliance status
```php
$stmt = $db->prepare("
  SELECT office_exclusive_until, mls_ready_date,
         DATEDIFF(office_exclusive_until, NOW()) as days_remaining
  FROM private_listings
  WHERE tenant_id = ? AND id = ?
");
$stmt->execute([$tenant_id, $listing_id]);
```

---

## TESTING CRITERIA

### Functional Tests

- [ ] Create private listing with invite-only visibility
- [ ] Invite agent via email link → acceptance flow
- [ ] Revoke access → agent loses access
- [ ] Document buyer need → auto-matches to 3+ relevant listings
- [ ] Matching score calculated correctly (≥60 threshold)
- [ ] Engagement tracking records views, duration, interest
- [ ] getEngagementReport returns correct counts + timeline
- [ ] Compliance warning fires 3 days before exclusive period ends
- [ ] Promote to MLS updates status, creates notification
- [ ] Feed shows only listings user has access to

### Security Tests

- [ ] SQL injection on all fields (prepared statements)
- [ ] Cross-tenant data leak (tenant_id filters on all queries)
- [ ] Unauthorized access (non-listing agent cannot edit)
- [ ] Unauthorized viewing (non-invited agent cannot see private listing data)
- [ ] Session token validation on all endpoints

### Performance Tests

- [ ] Matching job completes < 30 sec for 100 listings + 50 buyer needs
- [ ] Feed query returns < 200ms for 50 listings
- [ ] Engagement tracking insert < 50ms (async)

### Compliance Tests

- [ ] office_exclusive_until enforced (cannot promote before date)
- [ ] Audit trail records all visibility changes
- [ ] Fair Housing audit on all drip emails (run through email.php audit)

---

## INTEGRATION POINTS

- **properties.php** — Link private listing to property record
- **showings.php** — Create showings from private listing requests
- **admin.php** — Buyer needs tied to CRM client lifecycle
- **email.php** — Send invites, match notifications, engagement alerts
- **drips.php** — "Private Listing Match" campaign
- **daily-action-feed.js** — Compliance deadlines, match notifications
- **cron.php** — Matching job + compliance alerts every 4 hours
- **tenants.php** — Multi-tenant isolation on all queries
- **notifications** (future) — In-app alerts for matches, engagement

---

## OPERATIONAL NOTES

- **Matching algorithm is tunable.** Adjust weights (0.4, 0.3, 0.3) per tenant. Add weights config table for A/B testing.
- **Privacy by default.** Listings always start with minimum visibility. Agents opt-in to sharing.
- **Audit trail critical.** Log every access grant, revoke, visibility change. Comply with luxury market expectations (documentation = trust).
- **NAR Clear Cooperation compliance is non-negotiable.** Alert if office-exclusive period violated.
- **Buyer matching is a lead magnet.** Auto-match + instant notification increases showing frequency, reduces market time.

---

End of instruction 27.