# Specification: 024-staff-chat-backend

## Status

| Field | Value |
|-------|-------|
| **Created** | 2025-01-01 |
| **Current Phase** | ✅ Phase 16 Complete - Implementation Done |
| **Last Updated** | 2026-01-03 |
| **Implementation Started** | 2026-01-01 |
| **PRD Reviewed** | 2025-01-01 (Codex) |
| **SDD Completed** | 2026-01-01 (7 ADRs confirmed) |
| **SDD Reviewed** | 2026-01-01 (Codex) - All blockers resolved |
| **PLAN Completed** | 2026-01-01 (16 phases, 180+ tasks, TDD structure) |
| **PLAN Reviewed** | 2026-01-01 (Codex) - All blockers addressed |
| **Phase 16 Code Reviewed** | 2026-01-03 (Codex) - 3 critical fixes applied |

## Documents

| Document | Status | Notes |
|----------|--------|-------|
| product-requirements.md | completed | Codex review completed, all blockers resolved |
| solution-design.md | completed | All 7 ADRs confirmed by user |
| implementation-plan.md | completed | 16 phases complete, 502 tests passing |

## Implementation Progress

| Phase | Name | Status | Notes |
|-------|------|--------|-------|
| 1 | Database Foundation | ✅ Complete | 9 store migrations + 1 central migration |
| 2 | Entity Models | ✅ Complete | 7 models, 76 tests passing |
| 3 | Repository Layer | ✅ Complete | 9 repositories, 42 tests, Codex reviewed + fixes applied |
| 4 | Channel Access Service | ✅ Complete | 49 tests, 156 assertions |
| 5 | Message Service | ✅ Complete | 49 tests, 164 assertions |
| 6 | Notification Service | ✅ Complete | 43 tests, 135 assertions |
| 7 | Outbox Worker Job | ✅ Complete | 11 tests, 26 assertions |
| 8 | Retention Cleanup Job | ✅ Complete | 16 tests, 16 assertions |
| 9 | Web API Controller | ✅ Complete | 28 tests, 226 assertions |
| 10 | Mobile API Controller | ✅ Complete | 25 tests |
| 11 | File Upload & Attachments | ✅ Complete | 26 tests, 56 assertions |
| 12 | Event Integration | ✅ Complete | 18 tests, 97 assertions |
| 13 | Rate Limiting | ✅ Complete | 29 tests, 120 assertions |
| 14 | Analytics Events | ✅ Complete | 32 tests, 206 assertions |
| 15 | Default Channel Init | ✅ Complete | Deployment script, lazy init |
| 16 | Integration & E2E | ✅ Complete | 502 tests, all PRD features verified |

**Status values**: `pending` | `in_progress` | `completed` | `skipped`

## Codex PLAN Review Summary (2026-01-01)

### Blockers Identified & Resolved

| Finding | Resolution |
|---------|------------|
| Membership sync mechanism missing (ADR-3 "within 5 minutes") | Clarified: Dynamic computation on every access, no sync job needed |
| Outbox latency conflict (5s polling vs <200ms SDD target) | Clarified: Inline publish + outbox for retry only |
| Read receipt spec mismatch (PRD per-message vs ADR-2 high water mark) | Added Spec Reconciliation Gates section; ADR-2 is canonical |
| HTTP status code misalignment (read/mute endpoints) | Added explicit tests for 204 responses |

### Important Improvements Made

- Added central DB migration task (T1.3) for notification preferences schema
- Added OutboxRepository to Phase 3 for clear ownership (vs duplicated in Phase 6/7)
- Added explicit tests: reaction limit (20), mention limit (10), read receipt idempotency
- Added mute endpoint tests with 204 response verification
- Added Ably token capability scoping test
- Added API Contract Checkpoints after Phases 9/10
- Added Ably/FCM Smoke Test checkpoint after Phase 6/7
- Added Risks & Mitigations section
- Added Standard DoD template per phase
- Clarified deployment script path: `userfrosting/bin/staff-chat-init-channels`
- Clarified WorkbookPageController hook point discovery task

### Enhancements Made

- Each phase now has parallel indicators in header
- Dependency diagram updated with checkpoint markers
- DoD standardized across all 16 phases
- Spec Reconciliation Gates table added

### Review Outcome

✅ **Ready for Implementation** - All blockers resolved, plan is actionable with 16 phases and 180+ tasks.

---

## Codex Phase 16 Code Review Summary (2026-01-03)

### Critical Security Fixes Applied

| Issue | File | Resolution |
|-------|------|------------|
| Path traversal vulnerability | `StaffChatRetentionJob.php:472` | Use `str_starts_with()` with `DIRECTORY_SEPARATOR` |
| `retentionDays=0` bypass | `StaffChatApiController.php:1142` | Validate `>= 1` in controller |
| Reversed test assertion | `RetentionJobIntegrationTest.php:372` | Added fail-fast index checks |

### Test Improvements Made

- Fixed non-asserting mute filtering test with proper push notification assertions
- Added `$channelId` parameter to `createMockMessage()` helper to prevent hardcoded values
- Added explicit `assertNotFalse()` checks before index comparisons

### Deferred Items (With Rationale)

| Item | Reason |
|------|--------|
| HTTP-level E2E tests | Controller layer already unit tested; service integration is the gap |
| Rate limiting integration tests | Service fully unit tested with RedisMock |
| 24-hour edit window integration test | Logic validated in unit tests |

### Review Outcome

✅ **All critical fixes applied** - 502 tests passing, PHPStan clean.

---

## Codex Phase 3 Code Review Summary (2026-01-01)

### Critical Schema Mismatches Found & Fixed

| Repository | Issue | Resolution |
|------------|-------|------------|
| AttachmentRepository | Missing `originalName` column | Added to all SELECT and INSERT queries |
| AuditLogRepository | Used `performedByEmployeeId` but schema has `employeeId` | Renamed column reference |
| OutboxRepository | Used `channelName` (string) but schema has `channelId` (int) | Changed to int parameter |
| OutboxRepository | Used `processedAt` for backoff but schema has `lastAttemptAt` | Use `lastAttemptAt` for backoff |
| ChannelMemberRepository | Used invalid `removed` enum value | Use DELETE instead (membership deletion) |
| ChannelMemberRepository | Missing `mutedAt` in queries | Added to all SELECT/UPDATE statements |

### Additional Improvements Made

- Added JSON encoding error handling in OutboxRepository::insert()
- Added safe JSON decoding with fallback in getPending()/getExhausted()
- Added getMember() method to ChannelMemberRepository
- Updated OutboxRepositoryTest to match new channelId API

### Review Outcome

✅ **All fixes applied** - 118 tests passing, PHPStan clean.

---

## Codex SDD Review Summary (2026-01-01)

### Findings Addressed

| Category | Finding | Resolution |
|----------|---------|------------|
| **BLOCKER** | Read receipts PRD/SDD mismatch | User confirmed ADR-2 (high water mark); PRD updated to match |
| **BLOCKER** | Missing channel member management endpoints | Added GET/POST/DELETE member endpoints |
| **BLOCKER** | Missing mentions inbox API | Added GET /mentions endpoint |
| **IMPORTANT** | Ably reliability conflict | Added ADR-7: Async outbox pattern with retries |
| **IMPORTANT** | Attachment download unspecified | Added signed URL download endpoint |
| **IMPORTANT** | System message metadata missing | Added systemSourceType/systemSourceId columns |
| **IMPORTANT** | Web Ably token endpoint missing | Added /:typeNum/api/staff-chat/ably-token |
| **IMPORTANT** | Reactions JSON schema not locked | Added schema in API spec comments |
| **IMPORTANT** | Emoji in URL path is brittle | Changed DELETE reactions to use body |
| **ENHANCEMENT** | Analytics/tracking not specified | Added Analytics & Tracking section |
| **ENHANCEMENT** | HTTP 204 responses per PRD | Updated reaction/mute endpoints |
| **ENHANCEMENT** | Mentions dedupe constraint | Added UNIQUE(messageId, mentionedEmployeeId) |

### New ADR Added
- **ADR-7: Ably Reliability Strategy** - Async with transactional outbox pattern

### PRD Updates Made
- Feature 6 (Read Receipts) acceptance criteria aligned with ADR-2 high water mark approach
- Read receipts edge cases updated

### SDD Changes Made
- Added 3 new API endpoints: member management, mentions inbox, attachment download
- Added Web Ably token endpoint
- Added staff_chat_event_outbox table for async publishing
- Added systemSourceType/systemSourceId to messages table
- Added pagination index (channelId, id) to messages
- Added UNIQUE constraint to mentions table
- Added Jobs/ directory with OutboxWorker and RetentionJob
- Added Analytics & Tracking section with all PRD events
- Updated reactions to use body instead of URL path for emoji
- Fixed HTTP response codes (204 for mute, reactions)
- Clarified Redis dependency for rate limiting
- Documented role-sync as on-demand (not cached)

---

## Codex PRD Review Summary (2025-01-01)

### Blockers Identified & Resolved

| Issue | Resolution |
|-------|------------|
| Authorization model undefined | Added "Technical Clarifications" section with role taxonomy mapping to existing permissions |
| Message/read semantics unclear | Documented ordering source of truth, idempotency rules, read receipt semantics |
| Storage boundaries undefined | Specified per-store DB for messages, filesystem for media, retention rules |

### Important Improvements Made

- Added concrete evidence to User Research (12+ feature requests, 80% external app usage, 3 incidents)
- Tightened acceptance criteria with measurable targets (character limits, HTTP codes, timing constraints)
- Added comprehensive edge cases for: Image Attachments, Push Notifications, Mentions, Reactions, Read Receipts, Search
- Clarified backend vs frontend scope with "Backend Capabilities" table
- Added rate limiting specifications

### Review Outcome

✅ **Ready for SDD** - All blockers resolved, PRD provides sufficient detail for solution design.

## Decisions Log

| Date | Decision | Rationale |
|------|----------|-----------|
| 2025-01-01 | Hybrid channel membership | Auto-sync with manual overrides provides flexibility |
| 2025-01-01 | Per-channel message retention | Different channels may have different compliance needs |
| 2025-01-01 | Local media storage | User preference; simpler v1; can migrate to CDN later |
| 2025-01-01 | Existing permission integration | Reduces complexity; leverages proven system |
| 2025-01-01 | Flat conversation (no threads) | Simpler UX and implementation for v1 |
| 2025-01-01 | No DMs - channels only | Keeps scope focused; all communication visible to appropriate levels |

## Context

**Feature**: Staff Chat Backend System

Build a backend for handling staff chat from the BuyerKiosk Team app, BuyerKiosk Live app, and the store workbook frontend. Similar to RMS chat with images, emojis, messages, GIFs, etc.

**Key Requirements:**
- Chat history preservation
- Team member pictures/avatars
- Channels with employee level gating (private channels for managers/owners/shift leads)
- Workbook frontend: only sees public store channel
- Event integration: tasks/notes appear in chat for visibility
- Feel like private Slack/Discord for store communication

**Existing Infrastructure:**
- Firebase: Push notifications already working
- Ably: Realtime updates already configured
- Multi-store architecture (typeNum pattern)

**Scope**: Backend only - frontends (workbook, Team app, Live app) built separately

---
*This file is managed by the specification-management skill.*