Activity Log

{"file_name":"TOTALS_CUTOFF_2026-04-12.odt","mime_type":"application/vnd.oasis.opendocument.text"}

[]

{"backend_document_id":"16","document_type":"upload","title":"Codex - 04-13 All Customer Totals Tables and Logic","slug":"codex-04-13-all-customer-totals-tables-and-logic","summary_text":"Codex - 04-13 All Customer Totals Tables and Logic","content_markdown":null,"content_html":null,"file_name":"TOTALS_CUTOFF_2026-04-12.odt","stored_name":"20260412-190147-cdbfac59.odt","mime_type":"application/vnd.oasis.opendocument.text","file_size_bytes":"31229","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260412-190147-cdbfac59.odt","is_deleted":"0","created_at":"2026-04-12 15:01:47","updated_at":"2026-04-12 15:01:47","editor_content":"Totals Cutover Documentation Date: 2026-04-12 Cutover Date: 2026-04-10 Purpose This documents the totals logic now used by the dashboard snapshot endpoints after go-live. Rule: Through 2026-04-09 : use imported totals from customer_totals Starting 2026-04-10 : use live payment data from payments This change was applied to: frontend/api/totals_snapshot.php frontend/api/totals_snapshot_live.php webui/api/totals_snapshot.php webui/api/totals_snapshot_live.php Current Logic The application now uses this date split: If the requested range ends before 2026-04-10 Read totals from customer_totals Fallback to CustomerTotals only if customer_totals has no rows for that range If the requested range starts on or after 2026-04-10 Read totals from live payments joined to orders , visits , and customers If the requested range crosses the cutoff Sum customer_totals for the pre-cutover side Sum live payments for the post-cutover side Add the two result sets together Fallback behavior If neither side returns data, the code can still fall back to: customer_payment_transactions daily_customer_totals customer_totals CustomerTotals Those are safety fallbacks only, not the intended source-of-truth for this cutover rule Tables Used Primary tables: customer_totals Imported totals table Source of truth for dates through 2026-04-09 payments Live payment rows Source of truth for dates on and after 2026-04-10 orders Used to connect payments to visits visits Used for visit date and customer linkage customers Used for customer counts and is_new_customer Fallback and audit tables: CustomerTotals Legacy Access-style table name Fallback only daily_customer_totals Stored daily rollups Fallback/audit only customer_payment_transactions Ledger table Useful for reconciliation and auditing Not the primary source for the current dashboard cutover logic Totals Columns Imported totals columns: total_customers new_customers cash_sales check_sales credit_card_sales edwards total_sales total_sales_no_edwards Live payment totals are calculated into these output buckets: total_customers new_customers cash check credit tips edwards total_sales total_sales_no_edwards Notes: customer_totals does not have a dedicated tips column For imported totals, tips are effectively included wherever they were represented in the import values In live totals, tips are broken out separately and also still contribute to total_sales In live totals, credit excludes cash , check , and edwards No Application DB View Required The application does not currently require a database view for this logic. It is implemented directly in PHP in the four snapshot endpoints listed above. If you want a database-level validation helper, use the optional views below. Optional Validation Views These views are for manual validation only. They are not required by the app. 1. Imported totals through 2026-04-09 CREATE OR REPLACE VIEW vw_totals_import_pre_2026_04_10 AS SELECT DATE(customer_date) AS totals_date, SUM(COALESCE(total_customers, 0)) AS total_customers, SUM(COALESCE(new_customers, 0)) AS new_customers, ROUND(SUM(COALESCE(cash_sales, 0)), 2) AS cash, ROUND(SUM(COALESCE(check_sales, 0)), 2) AS `check`, ROUND(SUM(COALESCE(credit_card_sales, 0)), 2) AS credit, 0.00 AS tips, ROUND(SUM(COALESCE(edwards, 0)), 2) AS edwards, ROUND(SUM(COALESCE(total_sales, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0)+COALESCE(edwards,0))), 2) AS total_sales, ROUND(SUM(COALESCE(total_sales_no_edwards, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0))), 2) AS total_sales_no_edwards FROM customer_totals WHERE DATE(customer_date) <= '2026-04-09' GROUP BY DATE(customer_date); 2. Live totals from 2026-04-10 forward CREATE OR REPLACE VIEW vw_totals_live_post_2026_04_09 AS SELECT DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)) AS totals_date, COUNT(DISTINCT c.customer_id) AS total_customers, COUNT(DISTINCT CASE WHEN COALESCE(c.is_new_customer, 0) = 1 THEN c.customer_id END) AS new_customers, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS cash, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS `check`, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_type, '')) LIKE '%tip%' THEN p.amount ELSE 0 END), 2) AS tips, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') OR (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') OR LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE p.amount END), 2) AS credit, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN p.amount ELSE 0 END), 2) AS edwards, ROUND(SUM(COALESCE(p.amount, 0)), 2) AS total_sales, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE COALESCE(p.amount, 0) END), 2) AS total_sales_no_edwards FROM payments p LEFT JOIN orders o ON o.order_id = p.order_id LEFT JOIN visits v ON v.visit_id = o.visit_id LEFT JOIN customers c ON c.customer_id = v.customer_id WHERE DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)) >= '2026-04-10' GROUP BY DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)); 3. Combined validation view CREATE OR REPLACE VIEW vw_totals_cutover_2026_04_10 AS SELECT * FROM vw_totals_import_pre_2026_04_10 UNION ALL SELECT * FROM vw_totals_live_post_2026_04_09; Copy/Paste Validation Queries A. Check imported totals only for 2026-04-09 and earlier SELECT ROUND(SUM(COALESCE(total_sales, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0)+COALESCE(edwards,0))), 2) AS total_sales, ROUND(SUM(COALESCE(total_sales_no_edwards, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0))), 2) AS total_sales_no_edwards, ROUND(SUM(COALESCE(cash_sales, 0)), 2) AS cash, ROUND(SUM(COALESCE(check_sales, 0)), 2) AS `check`, ROUND(SUM(COALESCE(credit_card_sales, 0)), 2) AS credit, ROUND(SUM(COALESCE(edwards, 0)), 2) AS edwards, SUM(COALESCE(total_customers, 0)) AS total_customers, SUM(COALESCE(new_customers, 0)) AS new_customers FROM customer_totals WHERE DATE(customer_date) BETWEEN '2026-04-01' AND '2026-04-09'; B. Check live totals only for 2026-04-10 and later SELECT ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS cash, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS `check`, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_type, '')) LIKE '%tip%' THEN p.amount ELSE 0 END), 2) AS tips, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') OR (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') OR LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE p.amount END), 2) AS credit, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN p.amount ELSE 0 END), 2) AS edwards, ROUND(SUM(COALESCE(p.amount, 0)), 2) AS total_sales, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE COALESCE(p.amount, 0) END), 2) AS total_sales_no_edwards, COUNT(DISTINCT c.customer_id) AS total_customers, COUNT(DISTINCT CASE WHEN COALESCE(c.is_new_customer, 0) = 1 THEN c.customer_id END) AS new_customers FROM payments p LEFT JOIN orders o ON o.order_id = p.order_id LEFT JOIN visits v ON v.visit_id = o.visit_id LEFT JOIN customers c ON c.customer_id = v.customer_id WHERE DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)) BETWEEN '2026-04-10' AND '2026-04-12'; C. Check month total using the same cutover logic as the app SELECT ROUND(SUM(total_sales), 2) AS total_sales, ROUND(SUM(total_sales_no_edwards), 2) AS total_sales_no_edwards, ROUND(SUM(cash), 2) AS cash, ROUND(SUM(`check`), 2) AS `check`, ROUND(SUM(credit), 2) AS credit, ROUND(SUM(tips), 2) AS tips, ROUND(SUM(edwards), 2) AS edwards, SUM(total_customers) AS total_customers, SUM(new_customers) AS new_customers FROM ( SELECT ROUND(SUM(COALESCE(total_sales, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0)+COALESCE(edwards,0))), 2) AS total_sales, ROUND(SUM(COALESCE(total_sales_no_edwards, COALESCE(cash_sales,0)+COALESCE(check_sales,0)+COALESCE(credit_card_sales,0))), 2) AS total_sales_no_edwards, ROUND(SUM(COALESCE(cash_sales, 0)), 2) AS cash, ROUND(SUM(COALESCE(check_sales, 0)), 2) AS `check`, ROUND(SUM(COALESCE(credit_card_sales, 0)), 2) AS credit, 0.00 AS tips, ROUND(SUM(COALESCE(edwards, 0)), 2) AS edwards, SUM(COALESCE(total_customers, 0)) AS total_customers, SUM(COALESCE(new_customers, 0)) AS new_customers FROM customer_totals WHERE DATE(customer_date) BETWEEN '2026-04-01' AND '2026-04-09' UNION ALL SELECT ROUND(SUM(COALESCE(p.amount, 0)), 2) AS total_sales, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE COALESCE(p.amount, 0) END), 2) AS total_sales_no_edwards, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS cash, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') AND NOT ( LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' ) THEN p.amount ELSE 0 END), 2) AS `check`, ROUND(SUM(CASE WHEN (LOWER(COALESCE(p.payment_method, '')) LIKE '%cash%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%cash%') OR (LOWER(COALESCE(p.payment_method, '')) LIKE '%check%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%check%') OR LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN 0 ELSE p.amount END), 2) AS credit, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_type, '')) LIKE '%tip%' THEN p.amount ELSE 0 END), 2) AS tips, ROUND(SUM(CASE WHEN LOWER(COALESCE(p.payment_method, '')) LIKE '%edward%' OR LOWER(COALESCE(p.payment_type, '')) LIKE '%edward%' OR LOWER(COALESCE(o.order_status, '')) LIKE '%edward%' OR LOWER(COALESCE(v.visit_type, '')) LIKE '%edward%' OR LOWER(COALESCE(NULLIF(c.full_name, ''), v.customer_name_snapshot, '')) LIKE '%edward%' THEN p.amount ELSE 0 END), 2) AS edwards, COUNT(DISTINCT c.customer_id) AS total_customers, COUNT(DISTINCT CASE WHEN COALESCE(c.is_new_customer, 0) = 1 THEN c.customer_id END) AS new_customers FROM payments p LEFT JOIN orders o ON o.order_id = p.order_id LEFT JOIN visits v ON v.visit_id = o.visit_id LEFT JOIN customers c ON c.customer_id = v.customer_id WHERE DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)) BETWEEN '2026-04-10' AND '2026-04-12' ) t; Quick Daily Checks Check one day from imported totals: SELECT * FROM customer_totals WHERE DATE(customer_date) = '2026-04-09'; Check one day from live payments: SELECT p.payment_id, p.order_id, v.visit_id, c.customer_id, c.full_name, p.payment_date, p.payment_method, p.payment_type, p.amount FROM payments p LEFT JOIN orders o ON o.order_id = p.order_id LEFT JOIN visits v ON v.visit_id = o.visit_id LEFT JOIN customers c ON c.customer_id = v.customer_id WHERE DATE(COALESCE(p.payment_date, o.created_at, v.visit_date)) = '2026-04-10' ORDER BY COALESCE(p.payment_date, o.created_at, v.visit_date), p.payment_id; Related Files totals_snapshot.php totals_snapshot_live.php totals_snapshot.php totals_snapshot_live.php totals_service.php report_totals_audit.php","is_text_editable":1,"can_edit_inline":0}

[]

{"customer_payment_transaction_id":"4499","payment_id":"63617","order_id":"104082","visit_id":"129329","customer_id":"3534","customer_name":"Zuheily Castaneda","transaction_date":"2026-04-12 10:30:00","payment_method":"Credit Card","payment_type":"Deposit","bucket":"credit","amount":"100.00","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-12 13:45:19","updated_at":"2026-04-12 13:45:19"}

{"customer_payment_transaction_id":"4499","payment_id":"63617","order_id":"104082","visit_id":"129329","customer_id":"3534","customer_name":"Zuheily Castaneda","transaction_date":"2026-03-29 10:30:00","payment_method":"Credit Card","payment_type":"Deposit","bucket":"credit","amount":"100.00","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-12 13:45:00","updated_at":"2026-04-12 13:45:00"}

[]

{"customer_payment_transaction_id":"4500","payment_id":"63618","order_id":"104082","visit_id":"129329","customer_id":"3534","customer_name":"Zuheily Castaneda","transaction_date":"2026-04-12 10:30:00","payment_method":"Credit Card","payment_type":"Balance","bucket":"credit","amount":"104.93","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-12 13:45:19","updated_at":"2026-04-12 13:45:19"}

{"customer_payment_transaction_id":"4500","payment_id":"63618","order_id":"104082","visit_id":"129329","customer_id":"3534","customer_name":"Zuheily Castaneda","transaction_date":"2026-03-29 10:30:00","payment_method":"Credit Card","payment_type":"Balance","bucket":"credit","amount":"104.93","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-12 13:45:00","updated_at":"2026-04-12 13:45:00"}

[]

{"payment_id":"63617","order_id":"104082","payment_date":"2026-04-12 00:00:00","payment_type":"Deposit","payment_method":"Credit Card","amount":"100.00","notes":null,"legacy_payment_id":null,"source_payment_key":null}

{"payment_id":"63617","order_id":"104082","payment_date":"2026-03-29 00:00:00","payment_type":"Deposit","payment_method":"Credit Card","amount":"100.00","notes":null,"legacy_payment_id":null,"source_payment_key":null}

[]

{"payment_id":"63618","order_id":"104082","payment_date":"2026-04-12 00:00:00","payment_type":"Balance","payment_method":"Credit Card","amount":"104.93","notes":null,"legacy_payment_id":null,"source_payment_key":null}

{"payment_id":"63618","order_id":"104082","payment_date":"2026-03-29 00:00:00","payment_type":"Balance","payment_method":"Credit Card","amount":"104.93","notes":null,"legacy_payment_id":null,"source_payment_key":null}

{"file_name":"IMPLEMENTATION_STATUS_2026-04-12.md","mime_type":"application/octet-stream"}

[]

{"backend_document_id":"15","document_type":"upload","title":"Codes - 04-13-2026 DONE / VERIFIED / BLOCKED","slug":"codes-04-13-2026-done-verified-blocked","summary_text":"Codes - 04-13-2026 DONE / VERIFIED / BLOCKED","content_markdown":null,"content_html":null,"file_name":"IMPLEMENTATION_STATUS_2026-04-12.md","stored_name":"20260412-164417-5a72fe80.md","mime_type":"application/octet-stream","file_size_bytes":"5341","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260412-164417-5a72fe80.md","is_deleted":"0","created_at":"2026-04-12 12:44:17","updated_at":"2026-04-12 12:44:17","editor_content":"# CustomerDB Implementation Status\n\nDate: 2026-04-12\nOwner: Codex + Front Desk Team\nFormat: DONE / VERIFIED / BLOCKED\n\n## DONE\n\n- Added cron smoke-test preflight for morning jobs:\n  - `backend/jobs/smoke_morning_jobs.php`\n  - Wired in cron manager command chain:\n    - `backend/cron_manager.php`\n    - `backend/README.md`\n\n- Fixed unified appointments SQL ambiguity (`customer_id`) and stabilized combined feed:\n  - `backend/appointment_service.php`\n\n- Implemented Setmore import lock + booking source + stronger note extraction:\n  - `backend/setmore_service.php`\n  - `backend/morning_jobs_service.php`\n  - `backend/install_schema.php`\n\n- Added single transaction ledger foundation (`customer_payment_transactions`) and tip support:\n  - `backend/install_schema.php`\n  - `backend/payment_ledger_service.php`\n  - `backend/payment_ledger.php`\n  - `backend/live_table_manager_service.php`\n\n- Added ledger rebuild and legacy CSV import tooling:\n  - `backend/jobs/rebuild_payment_ledger.php`\n  - `backend/jobs/import_customer_totals_csv_to_ledger.php`\n\n- Added reconciliation and audit backend modules:\n  - `backend/report_reconciliation.php`\n  - `backend/report_totals_audit.php`\n  - `backend/report_unmatched.php`\n  - Linked in:\n    - `backend/reports.php`\n    - `backend/index.php`\n\n- Switched report/snapshot paths to ledger-first behavior with fallbacks:\n  - `backend/report_service.php`\n  - `frontend/api/totals_snapshot.php`\n  - `frontend/api/totals_snapshot_live.php`\n  - `webui/api/totals_snapshot.php`\n  - `webui/api/totals_snapshot_live.php`\n\n- Stabilized visit save and item type persistence:\n  - `frontend/api/visit_save.php`\n  - `frontend/api/visit_get.php`\n  - `frontend/api/visit_report_generate.php`\n  - `backend/visit_service.php`\n  - migration:\n    - `migrations/20260411_order_items_item_type_name_no_fk.sql`\n  - schema:\n    - `mariadb_core_schema.sql`\n\n- Updated receipt/work-order logic to prefer ledger totals:\n  - `frontend/receipt.html`\n  - `frontend/work_order.html`\n  - `frontend/api/visit_get.php`\n  - `frontend/api/visit_report_generate.php`\n\n- Updated visit email receipt generation to use live visit data and ledger-aware totals:\n  - `frontend/visit.html`\n\n- Payroll robustness updates (Yana/Jana alias handling, paste parser hardening, clearer import diagnostics):\n  - `backend/payroll_service.php`\n  - `backend/payroll.php`\n\n- Added frontend activity monitor for backend checks:\n  - `backend/frontend_activity.php`\n\n- Added frontend item type management page and APIs:\n  - `frontend/item_types.html`\n  - `frontend/api/item_type_update.php`\n  - `frontend/api/item_type_delete.php`\n\n- Fixed front desk title/refresh weird text artifacts:\n  - `frontend/index.html`\n\n- Introduced backend canonical loaders under `webui/backend/*` to reduce drift:\n  - Example files:\n    - `webui/backend/bootstrap.php`\n    - `webui/backend/payment_ledger.php`\n    - `webui/backend/report_reconciliation.php`\n\n## VERIFIED\n\n- Morning cron smoke chain is working from user-provided run output:\n  - smoke test passes\n  - morning jobs run completes with `Exit Code: 0`\n\n- Legacy import command executed successfully by user:\n  - imported rows: `2757`\n  - date range imported: `2023-12-24` to `2026-04-06`\n  - forward resync rows: `55`\n\n- PHP syntax check passed for critical modules:\n  - `backend/payment_ledger.php`\n  - `backend/payment_ledger_service.php`\n  - `backend/report_reconciliation.php`\n  - `backend/report_totals_audit.php`\n  - `backend/jobs/import_customer_totals_csv_to_ledger.php`\n  - `backend/jobs/rebuild_payment_ledger.php`\n  - `backend/jobs/smoke_morning_jobs.php`\n  - `backend/morning_jobs_service.php`\n  - `backend/appointment_service.php`\n  - `frontend/api/visit_get.php`\n  - `frontend/api/visit_save.php`\n  - `frontend/api/visit_report_generate.php`\n  - `backend/payroll_service.php`\n  - `backend/payroll.php`\n\n## BLOCKED\n\n- Month-end totals still not fully matched to expected Access truth for selected periods:\n  - April 2025 mismatch remains\n  - April 2026 daily range mismatch remains for at least one target comparison\n  - Edwards bucket attribution still has unresolved edge cases on some records\n\n- Full parity confirmation still needed for all frontend totals surfaces:\n  - `reports.html`\n  - dashboard `index.html`\n  - `webui.html`\n  - Goal: all must reconcile against `customer_payment_transactions` outputs\n\n- Receipt parity final QA still pending:\n  - Need side-by-side validation on known problematic visits:\n    - print receipt\n    - email receipt\n    - backend generated receipt report\n\n- Payroll API alias parity incomplete:\n  - `backend/api/payroll_import.php` still strictly accepts only `Yana` or `Lola`\n  - Should be aligned with alias handling in `backend/payroll_service.php`\n\n- Release hygiene blocked by broad workspace churn:\n  - Many unrelated changed/deleted files exist in current worktree\n  - Needs isolation/staging before safe production commit/deploy\n\n## Next Actions\n\n1. Run reconciliation for April 2025 and April 2026 target windows and fix row-level ledger bucket/date mismatches.\n2. Confirm dashboard and report UI totals are reading ledger-backed endpoints only.\n3. Execute receipt parity QA on named customer/visit samples and sign off.\n4. Align payroll import API alias handling with payroll service.\n5. Prepare a clean staged commit containing only intended production files.\n","is_text_editable":1,"can_edit_inline":1}

{"file_name":"Codex-Update-04-12-2026.odt","mime_type":"application/vnd.oasis.opendocument.text"}

[]

{"backend_document_id":"14","document_type":"upload","title":"Codex - Updates Fixes Issues 04-13-2026","slug":"codex-updates-fixes-issues-04-13-2026","summary_text":"Codex - Updates Fixes Issues 04-13-2026 Detailed Documentation","content_markdown":null,"content_html":null,"file_name":"Codex-Update-04-12-2026.odt","stored_name":"20260412-164224-b4b11b83.odt","mime_type":"application/vnd.oasis.opendocument.text","file_size_bytes":"49423","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260412-164224-b4b11b83.odt","is_deleted":"0","created_at":"2026-04-12 12:42:24","updated_at":"2026-04-12 12:42:24","editor_content":"Comprehensive implementation log (through  April 12, 2026 ) and remaining work: Scope Snapshot The branch currently has very large churn ( 242 files changed ), including canonical backend work plus broad  webui/backend/*  compatibility-loader rewiring. Core functional work for your issues is concentrated in backend/frontend reporting, Setmore import, visit save/receipt logic, payroll import, and payment ledger. I also validated syntax for key new/modified PHP modules (all clean). What Was Implemented Cron hardening + morning smoke test Added cron-safe preflight script to fail fast when runtime prerequisites are missing:  backend/jobs/smoke_morning_jobs.php Smoke test now explicitly checks  mysqli  extension and required functions (including  app_unified_appointments ). Wired cron “Morning Import” and “Morning Jobs” commands to run smoke test first:  backend/cron_manager.php Documented smoke command chain in backend docs:  backend/README.md Fixed unified appointments SQL ambiguity / cron fatal Added/updated centralized unified appointment query service:  backend/appointment_service.php Fixed ambiguous  customer_id  in union query by qualifying Setmore side as  setmore_appointments.customer_id . Included  setmore_import_locked  in unified rows (used by workflow/import safeguards). Setmore import improvements (notes + booking source + lock flag) Implemented robust note extraction from multiple payload fields and flattened additional fields:  backend/setmore_service.php Added booking source extraction ( booking_source  /  booked_from  / parsed note line “Booked from ...”) and write-through to visits when column exists. Added import lock behavior ( setmore_import_locked ) so re-import does not overwrite locked visits. Added lock column bootstrap/ensure + bulk lock helper:  backend/morning_jobs_service.php Added schema evolution for lock and ledger fields in installer:  backend/install_schema.php Visit save 500 fix + item type reliability Added  item_type_name  persistence to  order_items  and updated reads to prefer stored label (fallback to  item_types ):  frontend/api/visit_save.php ,  frontend/api/visit_get.php ,  frontend/api/visit_report_generate.php ,  backend/visit_service.php Added migration to backfill  item_type_name  and drop fragile FK dependency on type table:  migrations/20260411_order_items_item_type_name_no_fk.sql Updated base schema accordingly:  mariadb_core_schema.sql Payment ledger architecture (single transaction table with tips) Added/expanded canonical ledger table  customer_payment_transactions  with  tip_amount  and indexes:  backend/install_schema.php Implemented sync/rebuild services from  payments  with bucket classification ( cash/check/credit/edwards/tip ):  backend/payment_ledger_service.php Added ledger rebuild CLI:  backend/jobs/rebuild_payment_ledger.php Added ledger UI/report page with filters and summary cards:  backend/payment_ledger.php Added ledger table to Live Table Manager map:  backend/live_table_manager_service.php Legacy CustomerTotals import into ledger + forward resync Added CLI import from Access CSV into ledger ( source=legacy_customer_totals ) and optional forward rebuild from next date:  backend/jobs/import_customer_totals_csv_to_ledger.php Your run imported  2757  rows for  2023-12-24  to  2026-04-06  and resynced  55  rows forward ( 2026-04-07  to  2026-04-12 ). Report totals now ledger-first (with fallback strategy) Added ledger-driven daily/period/month/year aggregation paths, including Edwards handling and totals-no-Edwards logic:  backend/report_service.php Updated dashboard snapshot APIs to use ledger when range rows exist (instead of always preferring legacy tables):  frontend/api/totals_snapshot.php ,  frontend/api/totals_snapshot_live.php ,  webui/api/totals_snapshot.php ,  webui/api/totals_snapshot_live.php New reconciliation / audit backend modules Added totals reconciliation page (report total vs ledger total vs daily mismatch rows + exact ledger rows):  backend/report_reconciliation.php Added daily source-vs-source totals audit page:  backend/report_totals_audit.php Added unmatched report attachment helper page:  backend/report_unmatched.php Added navigation links in backend home/reports:  backend/index.php ,  backend/reports.php Receipts and work orders moved toward ledger truth visit_get  now returns ledger payment rows/totals (deposit/tip/paid-toward-balance):  frontend/api/visit_get.php PDF receipt/work-order generator uses ledger-first totals and item_type_name fallback:  frontend/api/visit_report_generate.php Frontend receipt/work-order screens updated to prefer  ledger_totals :  frontend/receipt.html ,  frontend/work_order.html Save flow includes tip safety net so Square tip is persisted as a Tip payment row if missing in payload:  frontend/api/visit_save.php ,  backend/visit_service.php Email receipt vs print receipt mismatch Reworked email receipt generation path in visit page to build from live  visit_get + settings_get  data and ledger-aware totals, replacing simplified/legacy divergence path:  frontend/visit.html Payroll module fixes Added alias normalization and clause support ( Jana/Janna  ->  Yana ) so counts/reports don’t zero out due name variants:  backend/payroll_service.php Improved pasted Excel parsing robustness (delimiter/header detection, fallback parsing, skip-reason diagnostics):  backend/payroll_service.php UI now surfaces import diagnostics and styled KPI cards:  backend/payroll.php Frontend activity monitor Added backend page to monitor frontend edits/signatures/status changes using  backend_change_log :  backend/frontend_activity.php UI cleanup for weird characters Frontdesk title and refresh text normalized to plain readable values:  frontend/index.html Item types management module Added standalone item type manager page and update/delete APIs:  frontend/item_types.html ,  frontend/api/item_type_update.php ,  frontend/api/item_type_delete.php Backend/WebUI canonicalization Large set of  webui/backend/*  files converted to thin loaders pointing to canonical  /backend/*  files to reduce drift; examples:  webui/backend/bootstrap.php ,  webui/backend/payment_ledger.php ,  webui/backend/report_reconciliation.php Validation Done Ran PHP lint on critical modules; all reported “No syntax errors detected”: backend/payment_ledger.php ,  backend/payment_ledger_service.php ,  backend/report_reconciliation.php ,  backend/report_totals_audit.php backend/jobs/import_customer_totals_csv_to_ledger.php ,  backend/jobs/rebuild_payment_ledger.php ,  backend/jobs/smoke_morning_jobs.php backend/morning_jobs_service.php ,  backend/appointment_service.php frontend/api/visit_get.php ,  frontend/api/visit_save.php ,  frontend/api/visit_report_generate.php backend/payroll_service.php ,  backend/payroll.php What Is Still Left To Do Finalize totals parity for April 2025 / April 2026 edge cases Remaining mismatch indicates ledger contents/classification still differ from your Access truth set for some rows (especially Edwards split + date bucketing). Action left: run reconciliation for target ranges and correct source rows/bucket rules until report total == ledger total == expected monthly total. Confirm all frontend totals pages are fully ledger-backed end-to-end You asked  reports.html ,  dashboard ,  index.html ,  webui.html  to use new table. Snapshot endpoints were updated, but full UI-path verification for each page still needs click-through + data diff. Receipt equivalence final QA Email receipt builder was unified in  frontend/visit.html , but still needs real-record side-by-side test against print receipt on the problematic visits to confirm exact parity in totals/labels/lines. Payroll import API parity backend/api/payroll_import.php  still hard-validates worker as only  Yana/Lola  (no alias normalization there). If that endpoint is used directly, aliases may still fail. Should align this API with service-layer alias logic. Ledger population completeness You imported Access CSV and forward-resynced, but full historical backfill strategy still needs final sign-off: Decide whether to trust imported Access rows as authoritative for all prior dates and keep live rebuild only forward. Run one full-range ledger audit after any additional historical imports. Potentially risky broad file churn review before release There are many unrelated changed/deleted files (including docs storage artifacts and backups). Needs staging/cleanup before commit/deploy so only intended production changes are shipped.","is_text_editable":1,"can_edit_inline":0}

[]

{"item_type_id":"238","type_name":"Dance costume"}

{"item_type_id":"238","type_name":"Dance costume"}

[]

{"item_type_id":"410","type_name":"tanktop"}

{"item_type_id":"410","type_name":"Tank Top"}

[]

{"item_type_id":"5","type_name":"Formal Dress"}

{"deleted":true}

[]

{"item_type_id":"6","type_name":"Mother of the Bride"}

{"deleted":true}

[]

{"item_type_id":"7","type_name":"Mother of the Groom"}

{"deleted":true}

[]

{"item_type_id":"17","type_name":"Coat"}

{"deleted":true}

[]

{"item_type_id":"18","type_name":"Other Formal Wear"}

{"deleted":true}

[]

{"item_type_id":"19","type_name":"Other"}

{"deleted":true}

[]

{"item_type_id":"21","type_name":"Mother Of Bride Gown Top Shoulder Alterations"}

{"deleted":true}

[]

{"item_type_id":"153","type_name":"Formal"}

{"deleted":true}

[]

{"item_type_id":"154","type_name":"Non-Formal"}

{"deleted":true}

[]

{"item_type_id":"155","type_name":"Casual"}

{"deleted":true}

[]

{"item_type_id":"156","type_name":"Bridesmaid Dress Bridemaid Dress"}

{"deleted":true}

[]

{"item_type_id":"157","type_name":"Formal Alter Suit"}

{"deleted":true}

[]

{"item_type_id":"158","type_name":"bridesmaids dress"}

{"deleted":true}

[]

{"item_type_id":"159","type_name":"sports banquet"}

{"deleted":true}

[]

{"item_type_id":"160","type_name":"Suit jacket and pants"}

{"deleted":true}

[]

{"item_type_id":"168","type_name":"Baby Dress"}

{"deleted":true}

[]

{"item_type_id":"192","type_name":"Tuxedo Pants"}

{"deleted":true}

[]

{"item_type_id":"193","type_name":"Overskirt"}

{"deleted":true}

[]

{"item_type_id":"197","type_name":"Uniform Shirts"}

{"deleted":true}

[]

{"item_type_id":"198","type_name":"Mother Of Groom Dress"}

{"deleted":true}

[]

{"item_type_id":"199","type_name":"Bathing Suit"}

{"deleted":true}

[]

{"item_type_id":"203","type_name":"Romper"}

{"deleted":true}

[]

{"item_type_id":"205","type_name":"Bowling Jersey"}

{"deleted":true}

[]

{"item_type_id":"211","type_name":"Mother of the Bride Dress"}

{"deleted":true}

[]

{"item_type_id":"161","type_name":"Choose"}

{"deleted":true}

[]

{"item_type_id":"162","type_name":"Bridemaid Dress"}

{"deleted":true}

[]

{"item_type_id":"163","type_name":"Jerseys"}

{"deleted":true}

[]

{"item_type_id":"165","type_name":"gown"}

{"deleted":true}

[]

{"item_type_id":"167","type_name":"dresses"}

{"deleted":true}

[]

{"item_type_id":"170","type_name":"30"}

{"deleted":true}

[]

{"item_type_id":"171","type_name":"50"}

{"deleted":true}

[]

{"item_type_id":"172","type_name":"Other Item"}

{"deleted":true}

[]

{"item_type_id":"173","type_name":"Jackets"}

{"deleted":true}

[]

{"item_type_id":"174","type_name":"27"}

{"deleted":true}

[]

{"item_type_id":"175","type_name":"7"}

{"deleted":true}

[]

{"item_type_id":"176","type_name":"21"}

{"deleted":true}

[]

{"item_type_id":"177","type_name":"Alter Suit"}

{"deleted":true}

[]

{"item_type_id":"178","type_name":"Table cloth"}

{"deleted":true}

[]

{"item_type_id":"180","type_name":"denim pant and skirt, vest"}

{"deleted":true}

[]

{"item_type_id":"181","type_name":"Wrap"}

{"deleted":true}