# Migration 024 — Live private rooms (signalling + chat)

**Status:** DOCUMENTED ONLY — **NOT EXECUTED**  
**Authority required:** Brad Camp  
**Date drafted:** 2026-07-26  
**Phase:** Live Phase 2B.2 interim uses `App/secure/live/rooms/*.json` (web-denied).

## Purpose

Persist private two-person Live rooms, WebRTC signalling payloads, and private text chat without a second users/businesses system.

## Proposed SQL (DO NOT RUN until Brad authorizes)

```sql
-- Migration 024 — NOT EXECUTED

CREATE TABLE IF NOT EXISTS live_rooms (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_room_id CHAR(32) NOT NULL,
  host_user_id BIGINT UNSIGNED NOT NULL,
  business_id VARCHAR(64) NOT NULL,
  host_token_hash CHAR(64) NOT NULL,
  guest_token_hash CHAR(64) NOT NULL,
  guest_display_name VARCHAR(80) NULL,
  status ENUM('open','full','ended','expired') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_live_rooms_public (public_room_id),
  KEY idx_live_rooms_business (business_id),
  KEY idx_live_rooms_status (status),
  KEY idx_live_rooms_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS live_signals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  room_id BIGINT UNSIGNED NOT NULL,
  sender_role ENUM('host','guest') NOT NULL,
  signal_type VARCHAR(32) NOT NULL,
  payload_json MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  consumed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_live_signals_room (room_id, id),
  CONSTRAINT fk_live_signals_room FOREIGN KEY (room_id) REFERENCES live_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS live_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  room_id BIGINT UNSIGNED NOT NULL,
  sender_role ENUM('host','guest') NOT NULL,
  sender_name VARCHAR(80) NOT NULL,
  message_text VARCHAR(1000) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_live_messages_room (room_id, id),
  CONSTRAINT fk_live_messages_room FOREIGN KEY (room_id) REFERENCES live_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Interim storage (active)

| Path | Role |
|------|------|
| `App/secure/live/rooms/{public_room_id}.json` | Room + signals + messages |
| Parent `.htaccess` | Deny from all |

## Rules

- Do not create users/login/business tables for Live.
- Do not store access tokens in plain text (store SHA-256 hashes only).
- Do not run this migration without Brad’s written authorization.