# 30 — Collaborative Buyer/Seller Boards

**Version:** 1.0 | **Last Updated:** March 29, 2026 | **Status:** Planning Phase | **Dependency:** Instructions 10 (AI Copy), 17 (Client Portal), 27 (Real-Time Notifications)

---

## OVERVIEW

Introduce a Pinterest-meets-Trello collaborative workspace for buyers and sellers to collaborate with agents in real time. Boards are the shared single source of truth for property search, deal management, and transaction prep. Both agent and client can add content (property cards, notes, files, checklists), comment, rate, and track progress together.

**Three Board Types:**
1. **Buyer Search Boards** — Collaborative property browsing. Client and agent save listings side-by-side. Cards show photos, price, specs, agent notes, buyer ratings, status (interested/toured/passed/offer).
2. **Seller Prep Boards** — Checklist-driven listing preparation. Sections for staging, repairs, documents, photography, pricing. Assign tasks, set due dates, upload completion photos.
3. **Deal Boards** — Active transaction workspace. Timeline of milestones, compliance checklist (tied to transactions.php), communication thread, key dates countdown. Single source of truth during closing.

**Real-time Collaboration:** WebSocket presence (Socket.io) shows who's viewing the board. Live updates on card adds/edits. Typing indicators. Optional voice notes (ties to instruction 29).

**Mobile-First:** React Native (instruction 16) with swipe-to-rate property cards, camera integration for adding photos, offline queue for updates.

**Approval Workflow:** Content is added instantly (optimistic updates), but certain actions (publishing a note to client portal, sharing externally) route to approval queue if enabled.

---

## DATABASE SCHEMA

### Table: `boards`
Metadata for each board. Owner is always an agent. One board can have multiple members.

```sql
CREATE TABLE boards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  owner_id INT NOT NULL,  -- admin.id (agent who created it)
  title VARCHAR(255) NOT NULL,
  description TEXT,
  board_type ENUM('buyer_search', 'seller_prep', 'deal') DEFAULT 'buyer_search',
  cover_image_url VARCHAR(500),

  -- Buyer Search specific
  buyer_id INT,  -- FK: clients.id

  -- Seller Prep specific
  seller_id INT,  -- FK: clients.id (listing agent's seller)
  listing_mls VARCHAR(20),  -- FK: properties.mls_id for the listing being prepped

  -- Deal Board specific
  deal_id INT,  -- FK: transactions.id

  privacy_level ENUM('private', 'team', 'shared_with_client') DEFAULT 'private',
  allow_client_edit BOOLEAN DEFAULT true,
  allow_comments BOOLEAN DEFAULT true,
  allow_voice_notes BOOLEAN DEFAULT true,

  is_archived BOOLEAN DEFAULT false,
  color_tag VARCHAR(7) DEFAULT '#1A3347',  -- Brand color

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

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_id) REFERENCES admins(id),
  FOREIGN KEY (buyer_id) REFERENCES clients(id),
  FOREIGN KEY (seller_id) REFERENCES clients(id),
  FOREIGN KEY (deal_id) REFERENCES transactions(id),
  INDEX idx_tenant_owner (tenant_id, owner_id),
  INDEX idx_tenant_buyer (tenant_id, buyer_id),
  INDEX idx_tenant_deal (tenant_id, deal_id)
);
```

### Table: `board_members`
Access control. Who can view/edit this board.

```sql
CREATE TABLE board_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  board_id INT NOT NULL,
  user_id INT NOT NULL,  -- admin.id or client email if external
  user_type ENUM('agent', 'client', 'co_agent', 'team_member') DEFAULT 'agent',
  access_level ENUM('view', 'comment', 'edit') DEFAULT 'view',
  email VARCHAR(255),  -- If user_type='client' and no registered account
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_viewed_at TIMESTAMP,

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

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_tenant_board (tenant_id, board_id),
  INDEX idx_board_user (board_id, user_id),
  UNIQUE KEY uq_board_user (board_id, user_id)
);
```

### Table: `board_items`
Individual cards/notes/files on a board.

```sql
CREATE TABLE board_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  board_id INT NOT NULL,
  created_by INT NOT NULL,  -- admin.id or client_id
  item_type ENUM('property_card', 'note', 'photo', 'file', 'checklist_item', 'voice_note') DEFAULT 'note',

  -- Property card data
  property_id INT,  -- FK: properties.id (if item_type='property_card')
  property_mls VARCHAR(20),
  property_title VARCHAR(255),
  property_photo_url VARCHAR(500),
  property_price INT,
  property_beds DECIMAL(2,1),
  property_baths DECIMAL(2,1),
  property_sqft INT,
  property_address VARCHAR(255),

  -- Note/text data
  title VARCHAR(255),
  content TEXT,
  content_format ENUM('plain', 'markdown', 'html') DEFAULT 'plain',

  -- Photo/file data
  file_type ENUM('image', 'document', 'video'),
  file_url VARCHAR(500),  -- S3 or upload server
  file_name VARCHAR(255),
  file_size INT,  -- bytes

  -- Checklist item
  checklist_section VARCHAR(100),  -- e.g. 'staging', 'repairs', 'documents', 'photography', 'pricing'
  is_completed BOOLEAN DEFAULT false,
  assigned_to INT,  -- admin.id (who's responsible)
  due_date DATE,

  -- Voice note (ties to instruction 29)
  voice_note_url VARCHAR(500),
  voice_note_duration INT,  -- seconds

  status ENUM('active', 'archived', 'hidden') DEFAULT 'active',
  sort_order INT DEFAULT 0,  -- For drag-and-drop reordering

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

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES admins(id),
  FOREIGN KEY (property_id) REFERENCES properties(id),
  FOREIGN KEY (assigned_to) REFERENCES admins(id),
  INDEX idx_tenant_board (tenant_id, board_id),
  INDEX idx_board_type (board_id, item_type),
  INDEX idx_sort (board_id, sort_order)
);
```

### Table: `board_item_comments`
Threaded comments per item. Rich text.

```sql
CREATE TABLE board_item_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  board_item_id INT NOT NULL,
  author_id INT NOT NULL,  -- admin.id
  content TEXT NOT NULL,
  content_format ENUM('plain', 'markdown', 'html') DEFAULT 'plain',
  is_edited BOOLEAN DEFAULT false,
  edited_at TIMESTAMP NULL,

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

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (board_item_id) REFERENCES board_items(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES admins(id),
  INDEX idx_tenant_item (tenant_id, board_item_id)
);
```

### Table: `board_item_ratings`
Star ratings (1-5) per item per member. Property cards are rated by both agent and buyer.

```sql
CREATE TABLE board_item_ratings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  board_item_id INT NOT NULL,
  user_id INT NOT NULL,  -- admin.id
  rating INT DEFAULT 0,  -- 0-5 stars
  status VARCHAR(50),  -- 'interested', 'toured', 'passed', 'offer', NULL for notes
  rating_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (board_item_id) REFERENCES board_items(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES admins(id),
  UNIQUE KEY uq_rating (board_item_id, user_id),
  INDEX idx_tenant_item (tenant_id, board_item_id)
);
```

### Table: `board_activity`
Audit log of all actions. Used for activity feeds, notifications, and compliance.

```sql
CREATE TABLE board_activity (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  board_id INT NOT NULL,
  user_id INT NOT NULL,  -- admin.id who triggered the action
  action_type VARCHAR(50),  -- 'board_created', 'item_added', 'item_rated', 'comment_added', 'member_added', etc.
  entity_type VARCHAR(50),  -- 'board', 'item', 'comment', 'member'
  entity_id INT,  -- id of board_items or board_members, etc.
  description TEXT,
  ip_address VARCHAR(45),

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES admins(id),
  INDEX idx_tenant_board (tenant_id, board_id),
  INDEX idx_action (action_type, created_at)
);
```

---

## API ENDPOINTS (boards.php)

All endpoints: `/api/boards.php?action=<action>`

### Board Management

**POST createBoard**
```
Body: { title, description, board_type ('buyer_search'|'seller_prep'|'deal'),
        cover_image_url, buyer_id?, seller_id?, deal_id?, privacy_level }
Response: { success: true, data: { board_id, board } }
Rules: Session required. Auto-set owner_id from session. Auto-create board_members row for owner.
```

**GET getBoard**
```
Query: ?id=<board_id>
Response: { success: true, data: { board, members, items, item_count_by_type } }
Rules: Check board_members access_level. Return items in sort_order.
       Include item ratings and comment counts.
```

**PATCH updateBoard**
```
Body: { id, title?, description?, cover_image_url?, color_tag?, privacy_level?, allow_client_edit? }
Response: { success: true, data: { board } }
Rules: Session required. Only board owner can update.
```

**DELETE deleteBoard**
```
Query: ?id=<board_id>
Response: { success: true, message: "Board archived" }
Rules: Only owner can delete. Soft-delete: set is_archived=true. Cascade to items but keep audit log.
```

### Board Members & Access

**POST addMember**
```
Body: { board_id, email?, user_id?, access_level ('view'|'comment'|'edit') }
Response: { success: true, data: { membership } }
Rules: Owner/co-owner only. If email (client external), send invite link.
       Create pending membership, auto-accept if internal user.
```

**PATCH updateMember**
```
Body: { board_id, user_id, access_level }
Response: { success: true, data: { membership } }
Rules: Owner only.
```

**DELETE removeMember**
```
Query: ?board_id=<id>&user_id=<id>
Response: { success: true }
Rules: Owner only. Cannot remove owner. Set last_viewed_at.
```

**GET getBoardsByClient**
```
Query: ?client_id=<id>&board_type=<type>?
Response: { success: true, data: [ { board, member_count, item_count } ] }
Rules: Return all boards where client_id=buyer_id or seller_id. Sorted by updated_at DESC.
```

### Board Items (Cards, Notes, Files, Checklists)

**POST addItem**
```
Body: { board_id, item_type, title?, content?, file_url?, property_id?,
        checklist_section?, assigned_to?, due_date?, voice_note_url? }
Response: { success: true, data: { item } }
Rules: Session required. Check access_level >= 'comment'.
       Auto-set created_by, created_at. Set sort_order to max + 1 (append to end).
       Trigger board_activity log.
```

**PATCH updateItem**
```
Body: { id, title?, content?, file_url?, is_completed?, status?, assigned_to?, due_date? }
Response: { success: true, data: { item } }
Rules: Only item creator or board owner can edit.
```

**DELETE deleteItem**
```
Query: ?id=<item_id>
Response: { success: true }
Rules: Only creator or owner. Soft-delete: set status='hidden'.
```

**PATCH reorderItems**
```
Body: { board_id, items: [ { id, sort_order } ] }
Response: { success: true, data: { items } }
Rules: Check access_level >= 'edit'. Update sort_order in batch.
```

### Comments & Ratings

**POST addComment**
```
Body: { board_item_id, content, format? ('plain'|'markdown'|'html') }
Response: { success: true, data: { comment } }
Rules: Check access_level >= 'comment'.
```

**PATCH updateComment**
```
Body: { id, content }
Response: { success: true, data: { comment } }
Rules: Only comment author can edit. Set is_edited=true, edited_at.
```

**DELETE deleteComment**
```
Query: ?id=<comment_id>
Response: { success: true }
Rules: Author or board owner only. Soft-delete.
```

**POST rateItem**
```
Body: { board_item_id, rating (0-5), status? ('interested'|'toured'|'passed'|'offer') }
Response: { success: true, data: { rating } }
Rules: Upsert board_item_ratings row. Only allow status on property_card items.
```

### Aggregations & Feeds

**GET getBoardActivity**
```
Query: ?board_id=<id>&limit=50&offset=0
Response: { success: true, data: [ activity records ] }
Rules: Most recent first. Paginate.
```

**GET getBoardDashboard**
```
Query: ?board_id=<id>
Response: { success: true, data: { board, summary_stats: { total_items, items_by_type,
            members_count, last_activity_at }, item_highlights } }
Rules: Return summary for board overview card.
```

---

## IMPLEMENTATION STEPS

### Phase 1: Backend Infrastructure (Days 1-2)

1. **Create boards.php**
   - Copy auth/config pattern from admin.php
   - Auto-migrate tables on first call
   - Implement all CRUD endpoints above
   - All queries: PDO prepared statements, tenant_id filters
   - Standard JSON response pattern

2. **Add board routes to admin.php**
   - Route to boards.php for all `action` values starting with `board_` or `item_`
   - Example: `?action=board_create` calls `boards.php?action=createBoard`

3. **Update fogbreak.html**
   - Add new tab: "Boards" between "Properties" and "Transactions"
   - Tab state: { boards: [], active_board_id: null, filter: 'all'|'buyer'|'seller'|'deal' }

### Phase 2: Frontend UI — Buyer Search Boards (Days 3-4)

4. **Board list view**
   - Display all boards where user is member
   - Filter by board_type, is_archived
   - Quick create button for each type
   - Sort by updated_at
   - Show member count, item count, last activity

5. **Board detail view — Buyer Search**
   - Header: title, cover image, members, settings (if owner)
   - Main area: grid of property cards (item_type='property_card')
   - Each card: photo, price, beds/baths, address, star rating, status badge
   - Drag-to-reorder (update sort_order)
   - Filter by rating, status
   - Add property button: opens property picker (ties to properties.php). Quick add via MLS #.

6. **Property card detail**
   - Click to expand: full listing details, agent notes, buyer notes (textarea), rating slider, status selector
   - Comments section below
   - Voice note button (ties to instruction 29)
   - History of edits (read-only)

### Phase 3: Frontend UI — Seller Prep & Deal Boards (Days 5-6)

7. **Board detail view — Seller Prep**
   - Kanban-style layout: columns for each checklist_section (staging, repairs, documents, photography, pricing)
   - Cards per section: title, due_date, assigned_to, completion checkbox
   - Click card to expand: full details, comments, uploaded photo proof
   - Add item button per column
   - Progress bar showing % complete

8. **Board detail view — Deal Board**
   - Timeline view: vertical line of milestones (inspection, appraisal, final walk, closing)
   - Each milestone: date, status, checkbox
   - Compliance checklist section (read from transactions.php, tied to transaction_id)
   - Key dates countdown (days to closing, days until inspection)
   - Communication thread (comments)
   - Linked documents (read from documents.php)

### Phase 4: Real-Time Collaboration (Days 7-8)

9. **Socket.io integration**
   - On board detail view, establish Socket.io connection
   - Rooms per board_id
   - Broadcast on: item added/updated/deleted, comment added, rating changed, member joined/left
   - Show presence: "Sharon is viewing this board" + avatar
   - Typing indicator on comment input

10. **Optimistic updates**
    - Frontend adds item locally immediately
    - Backend call in background
    - On conflict/error, revert + show error toast
    - Ensures smooth UX on slow connections

### Phase 5: Mobile Integration (Days 9-10)

11. **React Native board views**
    - List view: cards in vertical scroll
    - Detail view: horizontal swipe between cards
    - Swipe right to rate (1-5 stars)
    - Swipe left to toggle status (interested/toured/passed)
    - Camera button: take photo, upload directly to item
    - Offline queue: queue adds/edits when offline, sync on reconnect

12. **Push notifications**
    - On new comment: notify all board members
    - On property added to board: notify relevant party (buyer/seller)
    - On checklist due date: notify assigned person
    - Ties to instruction 27 (notifications)

### Phase 6: Integration & Workflows (Days 11-12)

13. **CRM interaction logging**
    - Every board activity creates interaction record (clients.interactions table)
    - Type: 'board_activity'
    - Log: "Added property to Buyer Board", "Commented on staging checklist", etc.
    - Ties to activity feed in client detail view

14. **Client portal integration (instruction 17)**
    - Show boards where privacy_level='shared_with_client' in client portal
    - Allow client to add items, comment, rate (respecting access_level)
    - Optionally hide board list from client portal, show only if invited

15. **Approval workflows (optional)**
    - For boards with approval_enabled=true, certain actions queue for approval:
      - Publishing note to client portal
      - Sharing board externally
      - Locking a completed checklist item
    - Approval table: `board_approvals` (id, board_id, action_type, entity_id, status, approved_by, approved_at)

---

## TESTING CRITERIA

### Unit Tests (PHP)
- [ ] createBoard: valid/invalid inputs, tenant isolation, auto-member creation
- [ ] addMember: access control, email validation, duplicate prevention
- [ ] addItem: property card validation, file upload validation, sort_order increment
- [ ] rateItem: upsert logic (no duplicate ratings), status enum validation
- [ ] All queries: PDO binding, NULL tenant_id handling

### Integration Tests
- [ ] Two members on same board: both see updates in real-time (Socket.io)
- [ ] Member removed: can no longer access board
- [ ] Item reorder: sort_order persists and displays correctly on reload
- [ ] Comment threading: all comments visible, edits reflected, deletions soft-delete
- [ ] CRM logging: every board action appears in interactions table

### Frontend Tests
- [ ] Buyer Search: property cards load, filter by status/rating, drag-to-reorder
- [ ] Seller Prep: sections display, drag items between sections, completion % calculates correctly
- [ ] Deal Board: timeline displays milestones, compliance checklist linked to transaction
- [ ] Mobile: swipe-to-rate works, camera integration captures and uploads
- [ ] Real-time: socket.io broadcasts tested, presence shown, typing indicators appear
- [ ] Offline: items queue locally, sync on reconnect, no duplicates

### Security Tests
- [ ] Tenant isolation: user from tenant A cannot view boards from tenant B
- [ ] Access control: view-only members cannot edit, comment-only cannot delete
- [ ] XSS prevention: HTML content sanitized before render (use DOMPurify)
- [ ] File upload: validate file type/size server-side, store outside web root

---

## INTEGRATION POINTS

| System | Connection | Notes |
|--------|-----------|-------|
| **properties.php** | Property picker, property card metadata | Property data enriches cards |
| **transactions.php** | Deal Board links to deal_id, compliance checklist tied to transaction | Single source of truth |
| **documents.php** | File uploads tied to board items, secure sharing | Reuse upload infrastructure |
| **clients.php** | CRM interaction logging, client profile view | Board activity appears in interaction feed |
| **tenants.php** | Multi-tenant isolation, brand color from tenant config | Standard tenant pattern |
| **notifications.php** (instruction 27) | Member notified on comment, item add, due date | Push to mobile/email |
| **client-portal.php** (instruction 17) | Shared boards embedded in portal | Client can participate |
| **email.php** | Board share notifications, comment digests, checklist reminders | Tie-in with email engine |
| **cron.php** | Daily digest: upcoming due dates, items assigned but not started | Async processing |
| **Socket.io** (instruction 27) | Real-time presence, broadcasts, typing indicators | External library |

---

## DEPLOYMENT CHECKLIST

- [ ] boards.php created, tested locally
- [ ] All 6 tables auto-migrated on first API call
- [ ] fogbreak.html "Boards" tab added and styled
- [ ] Buyer Search board UI complete and functional
- [ ] Seller Prep board UI complete and functional
- [ ] Deal Board UI complete and functional
- [ ] Socket.io library added to frontend (via CDN or bundler)
- [ ] Mobile board views added to React Native (instruction 16)
- [ ] board_activity logging integrated with CRM
- [ ] Client portal boards visible (instruction 17)
- [ ] Cron job added: board_activity_digest (daily, 8 AM)
- [ ] Fair Housing audit: note content scanned (instruction 29 tie-in)
- [ ] GitHub commit + deploy to Bluehost

---

## NOTES

- **Presence awareness:** Socket.io room per board_id. On join, broadcast `{ type: 'user_joined', user: {...} }`. On leave, broadcast `{ type: 'user_left', user_id }`. Client-side display: "Sharon is viewing this board" with avatar.
- **Offline-first:** In React Native, queue all adds/edits to LocalStorage. On reconnect, sync in order. Backend deduplicates via transaction ID (client generates UUID on create).
- **Mobile UX:** Swipe right on property card to rate (1-5 star animation). Swipe left to toggle status. Swipe up for comments. Long-press to edit/delete.
- **Voice notes:** Ties to instruction 29. In board_items.item_type='voice_note', store voice_note_url + duration. Playback button with transcript (if auto-transcribed via Whisper API).
- **Brand colors:** color_tag from boards table inherits from tenant config. Each board can override.
- **No auto-publish:** All content added to board is immediately visible to members. But if approval_enabled=true, certain actions (publish to client portal, share externally) queue for approval. Default: false (instant visibility).