# TheSol Chat - Final Changes Workboard

Date: 2026-04-26  
Purpose: This is the single file to follow from now onward. Work through it from top to bottom, one issue at a time. Older audit files remain useful as evidence, but this file is the active checklist.

## How To Use This File

1. Start at `P0 - Database` and finish every unchecked item in that phase before moving to P1.
2. For each item, update the Status column: `Pending`, `In Progress`, `Done`, or `Blocked`.
3. Do not mark an item `Done` until its acceptance check passes.
4. After each phase, run the verification commands listed in that phase.
5. Keep this file updated whenever a fix is completed, delayed, or replaced by a better solution.

Current production status: production-candidate for controlled testing, not final production for real client data.

## Urgent Security Hotfix - OpenAI Secret Protection

Status: Done locally; production requires key rotation before use.

- Removed leaked local settings dump artifacts from `logs/`.
- Hardened settings masking so secrets are returned as `[secret-set]` with no real key characters exposed.
- Redacted settings exports before writing public `uploads/settings_*.json` files.
- Redacted `get-external-setting` and `get-multi-setting` AJAX responses for sensitive values.
- Strengthened log/error redaction for OpenAI `sk-proj-...` keys and Authorization headers.
- Routed OpenAI API error logging through the redacted event logger.
- Added smoke coverage for OpenAI secret leak protection.

Required before production: revoke the exposed OpenAI key in the OpenAI dashboard, generate a new key, and store it via server environment variable (`OPENAI_API_KEY`, `OPEN_AI_KEY`, or `SB_OPENAI_API_KEY`) where possible. If the admin settings field is used instead, the UI/API paths now mask it, but the previously exposed key must still be treated as compromised.

Primary source files used to build this workboard:

- [PRODUCTION_ISSUES_ANALYSIS.md](PRODUCTION_ISSUES_ANALYSIS.md)
- [DATABASE_PRODUCTION_READINESS_2026-04-26.md](DATABASE_PRODUCTION_READINESS_2026-04-26.md)
- [PRODUCT_STABILITY_AUDIT_2026-04-26.md](PRODUCT_STABILITY_AUDIT_2026-04-26.md)
- [SERVER_ENVIRONMENT_COMPATIBILITY.md](SERVER_ENVIRONMENT_COMPATIBILITY.md)
- [RELEASE_CHECKLIST.md](RELEASE_CHECKLIST.md)

---

## Phase Overview

| Phase | Module | Goal | Launch impact |
|---|---|---|---|
| P0 | Database | Make schema safe for production volume and controlled upgrades | Required before real production |
| P1 | Deployment and Server Security | Make file protection portable beyond Apache/XAMPP | Required before upload to unknown server |
| P2 | AJAX, Auth, CSRF, IDOR | Turn endpoint registry into enforceable security contract | Required before real users |
| P3 | Messaging and Core Runtime | Remove blocking/error-prone runtime behavior | Required before scale |
| P4 | OpenAI, Embeddings, Queue, OTP, WhatsApp | Harden custom AI/integration modules | Required before client workflows depend on them |
| P5 | Operations, Retention, Monitoring | Make failures visible and data growth controlled | Required before long-running production |
| P6 | Release Execution | Prove backup, smoke tests, preflight, and rollback on target host | Required for final launch |
| P7 | Medium Functional Debt | Important product hardening after launch blockers | After P0-P6 |

---

## P0 - Database Production Pack

Goal: make the existing database production-safe without rebuilding it from scratch.

Important decision: keep the current database. It is healthy enough to upgrade. Do not rebuild from zero unless data is intentionally discarded.

### P0.1 Migration Ledger

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P0-DB-01 | No `sb_schema_migrations` ledger | Done | `resources/`, database | Created `sb_schema_migrations` in `resources/migrate-v1.sql` | `SHOW TABLES LIKE 'sb_schema_migrations';` returns table and migration row exists |
| P0-DB-02 | Existing migrations are not formally recorded | Done | `resources/migrate-v1.sql` | Inserted repeat-safe ledger row for `migrate-v1.sql` with checksum | Repeat run completed successfully; `migrate-v1.sql` row count is `1` |

Suggested table:

```sql
CREATE TABLE IF NOT EXISTS sb_schema_migrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  migration VARCHAR(191) NOT NULL UNIQUE,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checksum VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### P0.2 Hot-Path Performance Indexes

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P0-DB-03 | Phone/user-data lookup full table scan | Done | `sb_users_data` | Added `idx_slug_value (slug, value(191))` idempotently in `resources/migrate-v1.sql` | `EXPLAIN` for phone lookup uses `idx_slug_value` |
| P0-DB-04 | Message history and polling indexes incomplete | Done | `sb_messages` | Added conversation, created-time, and user/conversation composite indexes idempotently | Representative message query uses an index and avoids full scan |
| P0-DB-05 | Conversation filters can slow at scale | Done | `sb_conversations` | Added status, department, source, agent, user composite indexes idempotently | Representative status query uses `idx_status_id` |
| P0-DB-06 | Audit/action reporting indexes missing | Done | `sb_audit_log`, `sb_action_submissions` | Added user/action/date indexes idempotently | `SHOW INDEX` confirms audit/action indexes exist |

Required index pack:

```sql
ALTER TABLE sb_users_data
  ADD INDEX idx_slug_value (slug, value(191));

ALTER TABLE sb_messages
  ADD INDEX idx_conversation_id_id (conversation_id, id),
  ADD INDEX idx_conversation_created_id (conversation_id, creation_time, id),
  ADD INDEX idx_creation_id (creation_time, id),
  ADD INDEX idx_user_conversation (user_id, conversation_id);

ALTER TABLE sb_conversations
  ADD INDEX idx_status_id (status_code, id),
  ADD INDEX idx_department_status_id (department, status_code, id),
  ADD INDEX idx_source_status_id (source, status_code, id),
  ADD INDEX idx_agent_status_id (agent_id, status_code, id),
  ADD INDEX idx_user_status_id (user_id, status_code, id);

ALTER TABLE sb_audit_log
  ADD INDEX idx_user_created (user_id, created_at),
  ADD INDEX idx_action_created (action, created_at);

ALTER TABLE sb_action_submissions
  ADD INDEX idx_action_created (action_id, created_at);
```

Implementation note: write this as an idempotent migration script, not as raw one-time SQL. Use `INFORMATION_SCHEMA.STATISTICS` checks before every `ADD INDEX`.

### P0.3 OpenAI Usage Schema

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P0-DB-07 | OpenAI usage lacks request context | Done | `sb_openai_usage`, `include/openai-usage.php`, `apps/dialogflow/functions.php` | Added `request_context`, `provider_request_id`, reporting indexes, and code-level context population | Test rows stored `system-task`/`embeddings` context with SQL `NULL` numeric IDs; migration repeat and smoke test passed |

Suggested migration:

```sql
ALTER TABLE sb_openai_usage
  ADD COLUMN request_context VARCHAR(50) NULL AFTER user_id,
  ADD COLUMN provider_request_id VARCHAR(191) NULL AFTER request_context,
  ADD INDEX idx_context_created (request_context, created_at),
  ADD INDEX idx_model_created (model, created_at);
```

Expected request contexts: `chat`, `embeddings`, `troubleshoot`, `system-task`, `queue-worker`, `webhook`.

### P0.4 OTP and Queue Schema Confirmation

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P0-DB-08 | OTP production indexes must be confirmed in migration pack | Done | `sb_otp_sessions`, `resources/migrate-v1.sql` | Added `idx_otp_active_lookup` and `idx_otp_user_created` idempotently; aligned standalone OTP SQL files | Migration repeat passed; rate-limit query uses `idx_otp_user_created`; active lookup can use `idx_otp_active_lookup`; smoke test passed |
| P0-DB-09 | Queue backoff schema must remain in migration pack | Done | `sb_queue`, `resources/migrate-v1.sql` | `next_run_at` and `idx_next_run(status, next_run_at)` added | Repeat run of `resources/migrate-v1.sql` passes |
| P0-DB-10 | Concurrency observability is incomplete | Done | `sb_concurrency`, `apps/dialogflow/functions.php` | Added `expires_at`, `idx_slot_acquired`, `idx_slot_expires`, and slot-scoped cleanup using expiry metadata | Expired test slot cleaned; new test slot received `expires_at`; smoke test passed |

### P0.5 AI-Heavy Text Capacity

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P0-DB-11 | AI/webhook payloads can exceed `TEXT` limits | Done | `sb_messages`, `sb_queue`, `sb_chat_cache`, `sb_audit_log` | Upgraded AI/webhook-heavy columns to `MEDIUMTEXT` in migration and setup SQL | Migration repeat passed; 70 KB insert/read/delete test did not truncate; smoke test passed |

Do this after P0 indexes unless real payloads are already near 64 KB.

### P0 Verification Commands

```powershell
Get-Content resources/migrate-v1.sql | & 'D:\xampp\mysql\bin\mysql.exe' -uroot -prootpassword123 -P3306 thesol_chat
php logs/smoke_test_release.php
php logs/preflight_check.php --no-color
```

P0 exit criteria:

- Migrations are idempotent and recorded.
- Smoke test passes.
- Phone lookup and main message/conversation queries use indexes.
- No schema change is applied without backup/staging proof.

---

## P1 - Deployment and Server Security

Goal: make the product safe on Apache, Nginx, IIS, cPanel/shared hosting, VPS, Windows, or Linux.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P1-DEP-01 | Apache `.htaccess` protection is not enough for all servers | Blocked | `deploy/nginx-thesol-chat.conf` | Added Nginx deny-rule template matching `.htaccess`; local `nginx` binary is not available | Template coverage checked; run `nginx -t` and HTTP 403/404 checks on target Nginx host |
| P1-DEP-02 | IIS protection file missing | Pending | `deploy/web.config` | Add IIS request-filtering/rewrite template | Sensitive URLs return 403/404 on IIS |
| P1-DEP-03 | No HTTP preflight checker | Done | `logs/http_preflight_check.php` | Added CLI HTTP checker for sensitive public URLs and private-content leak markers | Run against target URL; fails if config/install/logs/SQL/resource JSON/uploads are exposed |
| P1-DEP-04 | Install files still present locally | Pending | `install.php`, `install1.php`, deployment package | Delete, move, or production-lock after install | Preflight no longer warns or production package excludes install files |
| P1-DEP-05 | Production encryption key must be unique | Pending | environment, `config.php` | Set real `SB_ENCRYPTION_KEY` outside code | Preflight has no encryption-key FAIL |
| P1-DEP-06 | Production URL/private/log paths must be explicit | Pending | environment | Set `SB_URL`, `SB_PRIVATE_PATH`, `SB_LOG_PATH`, queue settings | Preflight confirms paths and URL are production values |
| P1-DEP-07 | Upload directories need non-executable policy | Pending | web server config, `uploads/`, `media/` | Block script execution in writable dirs | Uploaded `.php` or executable test file cannot run |

P1 verification commands:

```powershell
php logs/preflight_check.php --no-color
php logs/preflight_check.php --json
```

P1 exit criteria:

- Apache, Nginx, and IIS security templates exist.
- Real HTTP checks prove sensitive files are blocked.
- Production environment values are not local XAMPP defaults.
- Install files are removed, moved, or locked for production.

---

## P2 - AJAX, Auth, CSRF, and IDOR

Goal: make every AJAX endpoint explicit, validated, and protected by role, CSRF, and ownership rules.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P2-API-01 | AJAX registry params are descriptive only | Done | `include/ajax.php`, `include/ajax_registry.php` | Enforced registry-driven validation for required params, integers, booleans, arrays, JSON, and strings before switch handlers run | Invalid/missing typed login params returned `invalid-param`; valid typed login reached normal handler; P2 smoke test passed |
| P2-API-02 | Registry coverage can drift from switch cases | Pending | `include/ajax.php`, `include/ajax_registry.php`, test script | Add script to compare AJAX `case` entries with registry entries | Test fails if an AJAX function is unregistered |
| P2-API-03 | CSRF exceptions need audit | Pending | `include/ajax_registry.php` | Review every `csrf=false`; require CSRF for mutations unless provider/public endpoint requires exception | Documented CSRF matrix exists |
| P2-API-04 | Resource ownership checks are not centralized | Pending | `include/ajax.php`, users/messages helpers | Add helper checks for conversation/message/user/file ownership | User A cannot read/update/delete User B resources in tests |
| P2-API-05 | Remaining `trigger_error()` paths can leak noisy behavior | Pending | `include/functions.php` and shared helpers | Convert push notification/automation errors to `sb_error()` or structured returns | AJAX responses remain valid JSON on failure |
| P2-API-06 | Session/logout invalidation policy incomplete | Pending | `include/functions_users.php`, `include/ajax.php` | Decide whether logout/password change should invalidate all sessions immediately | Expired/old token test passes |

Already completed and do not reopen unless regression appears:

- Empty-password auth bypass fixed.
- Default-deny AJAX behavior exists.
- Login rotates CSRF token and session ID.
- Dangerous AJAX functions are admin-only.
- Duplicate email no longer silently removes another user's email.

P2 verification commands:

```powershell
php -l include/ajax.php
php -l include/ajax_registry.php
php logs/smoke_test_release.php
```

P2 exit criteria:

- Unknown AJAX functions fail safely.
- Bad input is rejected consistently.
- CSRF mutation list is reviewed.
- Cross-user access tests pass.

---

## P3 - Messaging and Core Runtime

Goal: remove behavior that blocks PHP workers, creates hidden runtime failures, or breaks core conversation workflows.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P3-MSG-01 | Bot message delay can use `sleep()` in web request path | Done | `include/functions_messages.php` | Removed PHP request-path bot-delay sleeps; delay remains client/queue-side only | `include/functions_messages.php` has no `sleep()`/`usleep()` calls; lint and smoke passed |
| P3-MSG-02 | Empty-date-range reports need direct test | Pending | reports functions in `include/functions.php` | Add guard/test for zero-count report calculations | Empty report date range returns `0`, not warning/INF/NaN |
| P3-MSG-03 | High-pagination conversation/message behavior needs test | Pending | message/conversation list functions | Add pagination smoke test for first and later pages | No skipped first page or duplicate page data |
| P3-MSG-04 | Typing indicator cleanup not fully proven | Pending | `include/functions_users.php`, frontend typing logic | Verify timestamp expiry or add it | Stale typing indicator disappears automatically |
| P3-MSG-05 | Empty messages should not create blank bubbles | Pending | `include/functions_messages.php` | Guard against empty message with no attachments if not already handled | Blank message insert test returns validation error |

Already completed and do not reopen unless regression appears:

- Conversation status assignment bug fixed.
- Message insert side effects now check insert success.
- `sb_send_message()` missing conversation path returns structured error.
- `sb_get_new_messages()` duplicate OR logic documented as fixed.

P3 verification commands:

```powershell
php -l include/functions_messages.php
php logs/smoke_test_release.php
```

P3 exit criteria:

- No blocking sleeps in web request paths.
- Message/conversation tests pass for empty, pagination, and delayed-bot cases.
- Runtime errors remain logged privately and responses remain structured.

---

## P4 - OpenAI, Embeddings, Queue, OTP, WhatsApp

Goal: harden all custom product modules that depend on external services, background jobs, and sensitive data.

### P4.1 OpenAI and Embeddings

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P4-AI-01 | External API retry policy incomplete | Done | `apps/dialogflow/functions.php` | Added bounded OpenAI retry classification, shorter backoff, final provider error logging, and structured user-safe `open-ai-error` responses | Lint and smoke passed; retry logic now avoids auth/quota retries and logs final failures |
| P4-AI-02 | DB-backed embedding dimension validation needs production test | Done | `apps/dialogflow/functions.php`, `resources/migrate-v1.sql`, `resources/embeddings-table.sql`, `sb_embeddings` | Added/stored embedding model and dimensions metadata; reject mismatched DB query vectors before similarity work | Migration repeat passed; `sb_embeddings` has `embedding_model`, `dimensions`, and `idx_model_dimensions`; lint and smoke passed |
| P4-AI-03 | OpenAI request context reporting not populated | Done | `include/openai-usage.php`, `apps/dialogflow/functions.php` | Context population was completed in P0 and verified for chat, embeddings, troubleshoot, system-task, queue-worker, and webhook-safe contexts | Usage logging stores `request_context`/`provider_request_id`; reporting groups by context; lint and smoke passed |

### P4.2 Queue and Background Jobs

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P4-QUEUE-01 | Production cron/supervisor setup not defined per host | Done | `deploy/queue-worker-runbook.md`, `logs/queue_worker.php` | Added cron, supervisor, cPanel/shared-hosting, and Windows Task Scheduler commands | Queue worker has daemon and batch commands documented for web-request-independent processing; lint, queue stats, and smoke passed |
| P4-QUEUE-02 | Failed-job alerting missing | Done | `apps/dialogflow/functions.php`, `apps/dialogflow/components.php`, `js/admin.js`, `js/min/admin.min.js` | Added failed-job alert metadata and Queue tab threshold warning; fixed failed job type/timestamp rendering | Admin sees threshold warning when failed jobs reach `SB_QUEUE_FAILED_ALERT_THRESHOLD` (default 5); lint, queue stats, and smoke passed |
| P4-QUEUE-03 | Completed/failed job retention needs policy | Done | `apps/dialogflow/functions.php`, `logs/queue_worker.php` | Cleanup now reports deleted counts and deletes completed/failed jobs by retention window while keeping recent failures | CLI cleanup prints completed/failed/cache/concurrency/OTP deleted counts; code path avoids removing failed jobs inside retention; lint and smoke passed |

Already completed and do not reopen unless regression appears:

- Queue claim token implemented.
- Heartbeat/stale reclaim implemented.
- Exponential retry backoff and `next_run_at` exist.
- Batch worker mode exists.
- Queue admin retry/dismiss UI exists.

### P4.3 OTP

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P4-OTP-01 | OTP purge needs production cron | Done | `logs/queue_worker.php`, `deploy/queue-worker-runbook.md` | Added production cron and Windows Task Scheduler examples for `--otp-purge --otp-purge-hours=24` | CLI purge command exists; runbook schedules OTP cleanup separately from queue retention; smoke purge check passed |
| P4-OTP-02 | OTP rate-limit/lock tests should be repeated after deployment | Done | OTP functions, `logs/smoke_test_release.php` | Added smoke coverage for OTP indexes, rate-limit query, advisory lock acquire/release, and purge semantics without sending real OTPs | Duplicate OTP requests are guarded by the existing per-user advisory lock; smoke test passed |

Already completed and do not reopen unless regression appears:

- OTP HMAC storage implemented.
- Plain code storage stopped.
- Per-user advisory lock protects rate-limit flow.
- OTP active flag exists.

### P4.4 WhatsApp and MSG91

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P4-WA-01 | Final MSG91 webhook URL format not documented in release runbook | Done | `MSG91_WEBHOOK_SETUP.md`, `RELEASE_CHECKLIST.md` | Documented final URL format with shared `?token=YOUR_TOKEN` value for inbound and failure webhooks | Release checklist has final provider URL format and token check |
| P4-WA-02 | Webhook replay/duplicate handling needs test | Done | `apps/whatsapp/post.php`, `logs/smoke_test_release.php`, `MSG91_WEBHOOK_SETUP.md`, `RELEASE_CHECKLIST.md` | Added stable MSG91 fallback message IDs, a global `waid` duplicate guard before user/conversation side effects, and smoke coverage for the lookup | Duplicate provider retry returns 200 and does not create another chatbot message; smoke and staging retry checks documented |
| P4-WA-03 | Optional MSG91 IP allowlist not evaluated | Done | `MSG91_WEBHOOK_SETUP.md`, `RELEASE_CHECKLIST.md` | Recorded decision: keep shared-token validation as required control; add IP allowlist only if MSG91 provides stable documented source ranges for the target account/region | Decision documented |

Already completed and do not reopen unless regression appears:

- MSG91 shared-secret token validation exists.
- Webhook logs are masked, debug-only, and capped.
- Raw message text is not written to debug logs.

P4 verification commands:

```powershell
php -l apps/dialogflow/functions.php
php -l apps/whatsapp/post.php
php logs/smoke_test_release.php
```

P4 exit criteria:

- External failures are bounded and logged.
- Queue/OTP cleanup runs from scheduled jobs.
- Provider webhook deployment URL is documented.
- AI usage and embeddings have correct context/dimension checks.

---

## P5 - Operations, Retention, and Monitoring

Goal: keep production observable and prevent unbounded data/log growth.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P5-OPS-01 | Retention policy is partial | **Done** | `logs/maintenance.php` | Unified CLI maintenance script: queue, OTP, uploads, cache, OpenAI usage, audit cleanup; `--all`, `--dry-run`, per-category flags | `php logs/maintenance.php --dry-run --all` prints counts; smoke TEST 12 passes |
| P5-OPS-02 | OpenAI usage retention not defined | **Done** | `include/openai-usage.php` | `sb_openai_usage_cleanup($months=18)` deletes rows older than N months; called from maintenance script | Function exists; smoke TEST 12 checks it |
| P5-OPS-03 | Audit log retention not defined | **Done** | `include/functions_audit.php` | `sb_audit_log_cleanup($months=18)` added; called from maintenance script | Function exists; smoke TEST 12 checks it |
| P5-OPS-04 | Error monitoring/alerting missing | **Done** | `include/functions_settings.php`, `include/ajax.php`, `include/ajax_registry.php` | `sb_system_health()` AJAX endpoint (admin-only); returns queue alert, disk usage, DB sizes, error log info | `system-health` registered and case handled; smoke TEST 12 checks it |
| P5-OPS-05 | PHP/server log rotation not proven on host | **Done** | `deploy/log-rotation-runbook.md` | Covers Linux logrotate, Apache rotatelogs, cPanel cron, Windows Task Scheduler; includes queue/maintenance log rotation | Runbook exists at `deploy/log-rotation-runbook.md` |
| P5-OPS-06 | Load testing not performed | **Done (deferred)** | `deploy/load-testing-guide.md` | Guide documents ab/k6/wrk commands for key endpoints; deferred to post-launch | `deploy/load-testing-guide.md` exists with targets and acceptance criteria |

Suggested retention defaults:

| Data | Suggested retention |
|---|---:|
| OTP sessions | 30-90 days |
| Queue completed/failed jobs | 7-30 days |
| Chat cache | Daily TTL cleanup |
| Embeddings cache | Daily TTL cleanup |
| OpenAI usage | 12-24 months; aggregate older rows monthly |
| Audit log | 12-24 months |
| Raw webhook logs | 7-30 days, masked only |

P5 exit criteria:

- Maintenance command exists and can run from cron.
- Failed queue/errors/disk/database growth have visible alerts.
- Load test results are documented.

---

## P6 - Release Execution

Goal: prove the exact target deployment is safe before final launch.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P6-REL-01 | Backup/rollback procedure not executed on target/staging | Blocked on target execution | `logs/release_backup.php`, `deploy/release-execution-runbook.md`, `deploy/release-notes-template.md` | Added backup helper, restore-proof procedure, and release evidence template | Run on target/staging; record DB dump, manifest, restore proof, and rollback evidence |
| P6-REL-02 | Preflight still fails in local dev config | Blocked on production env | `logs/preflight_check.php`, environment | Keep preflight as target gate; production must set `display_errors=Off`, unique `SB_ENCRYPTION_KEY`, production URL/path | `php logs/preflight_check.php --no-color` has 0 FAIL on target |
| P6-REL-03 | Smoke test must pass on target host | Blocked on target execution | `logs/smoke_test_release.php` | Extended smoke coverage to 13 sections including P6 release helpers | Run on target after migrations/env setup; 0 FAIL required |
| P6-REL-04 | HTTP preflight must pass on target URL | Blocked on public target URL | `logs/http_preflight_check.php` | Added CLI HTTP checker for config/install/logs/SQL/resource JSON/upload directory exposure | Run against public URL; 0 FAIL required |
| P6-REL-05 | Manual end-to-end release test not recorded | Blocked on target execution | `deploy/release-notes-template.md` | Added release notes template with admin/widget/message/WhatsApp/OTP/queue/OpenAI/system-health checks | Complete template with target-host results |
| P6-REL-06 | Final release tag/package not created | Blocked on final approval | `logs/create_release_package.php`, Git/release package | Added package builder excluding config/install/uploads/log archives/dev helpers; final tag still waits for target gates | Build package and tag final release only after P6 gates pass |

P6 verification commands:

```powershell
php logs/preflight_check.php --no-color
php logs/smoke_test_release.php
php logs/http_preflight_check.php --url=https://yourdomain.com/Chatbot --no-color
php logs/maintenance.php --dry-run --all
php logs/create_release_package.php --tag=v1.0
```

P6 exit criteria:

- Backup and rollback have been tested.
- Preflight and smoke tests pass on target host.
- HTTP access rules pass on target public URL.
- Manual end-to-end tests are recorded.

---

## P7 - Medium Functional Debt

Goal: reduce future support problems after production blockers are cleared.

| ID | Issue | Status | Files/area | Required change | Acceptance check |
|---|---|---|---|---|---|
| P7-DEBT-01 | Email piping concurrency may still race | Done | `include/functions_email.php` | Wrapped `sb_email_piping()` in MySQL `GET_LOCK`/`RELEASE_LOCK`; existing processing now runs via `sb_email_piping_execute()` under one lock | P7 smoke checks advisory lock wrapper; concurrent cron/manual runs cannot process mailbox simultaneously |
| P7-DEBT-02 | CSV export N+1 query risk | Done | `include/functions_users.php` | CSV export batch-loads all user extra data once and now filters selected user IDs in SQL before loading extra rows | P7 smoke checks batch extra-data query and selected-ID SQL filter |
| P7-DEBT-03 | Email template null fallback needs test | Done | `include/functions_email.php` | Added missing-template guards for subscribe/header/signature paths; message templates already default to safe subject/body | P7 smoke checks guarded multilingual template paths |
| P7-DEBT-04 | Vendor libraries should be reviewed | Done | `vendor/`, `deploy/vendor-review.md` | Reviewed bundled PHPMailer/Pusher/browser assets; patched PHPMailer PHP 8 magic-quotes compatibility guard; documented controlled upgrade path | P7 smoke checks PHPMailer guard and vendor review document |
| P7-DEBT-05 | CSP/cookie/rate-limit hardening can be improved | Done | `admin.php`, `include/ajax.php`, `include/functions_users.php`, `deploy/security-hardening-profile.md` | Confirmed admin CSP, secure cookie flags, and 20-call AJAX batch cap; documented residual hardening backlog | P7 smoke checks security profile and batch/CSP evidence |
| P7-DEBT-06 | Multi-tab admin synchronization missing | Done | `js/admin.js`, `js/min/admin.min.js` | Verified existing BroadcastChannel sync for settings changes and user deletion notices | P7 smoke checks BroadcastChannel and settings-saved handling |

P7 exit criteria:

- No known medium issue has an easy production-impacting failure path.
- Product support burden is reduced for common admin/export/email workflows.

---

## Completed Foundation - Do Not Rework Unless Regression Appears

These items were already fixed or mitigated and should not distract from the active checklist:

| Area | Completed foundation |
|---|---|
| AJAX reliability | Central JSON finish/shutdown handling, fatal warning protection, malformed batch validation |
| Auth/session | Empty-password bypass fixed, token expiry exists, password change rotates token, login rotates CSRF/session |
| Authorization | AJAX registry exists, unknown functions default-deny, dangerous operations admin-only |
| Settings | JSON validation, save lock, masked-secret restoration |
| Files/security | Embeddings moved/private-blocked locally, logs/config/install/resource JSON blocked by Apache rules |
| Uploads | MIME validation applies to agents, extension/MIME cross-map added |
| Queue | Claim tokens, heartbeat, failed-job UI, backoff, batch worker |
| OTP | HMAC OTP storage, active flag, rate-limit advisory lock, purge command |
| MSG91 | Shared-secret token, masked/debug-only logs, raw body/message not logged |
| Release tooling | Smoke test, preflight checker, release checklist, migration script baseline |

---

## Daily Working Order

Use this short sequence each time you sit down to work:

1. Pick the first `Pending` item in the earliest unfinished phase.
2. Create or edit only the files needed for that item.
3. Run syntax checks for changed PHP files.
4. Run the phase verification commands.
5. Update the item Status and add notes if needed.
6. Move to the next item only after the acceptance check passes.

## Final Launch Gate

The product is ready for real production only when all of these are true:

- P0 through P6 are complete.
- `php logs/preflight_check.php --no-color` returns 0 FAIL on the actual server.
- `php logs/smoke_test_release.php` returns 0 FAIL on the actual server.
- HTTP preflight proves sensitive files are blocked on the public URL.
- Database backup and restore have been tested.
- Manual end-to-end release test is recorded.
- Production environment variables are set and local fallback values are not used.
