System
Audit trail of every save, delete, import, and workflow update across the backend.
1,029 results · Page 17 of 21
{"document_type":"upload"}
{"backend_document_id":"20","document_type":"upload","title":"Complete Documentation up to date","slug":"complete-documentation-up-to-date","summary_text":null,"content_markdown":null,"content_html":null,"file_name":"COMPREHENSIVE_DOCUMENTATION.md","stored_name":"20260417-160828-6006d4f1.md","mime_type":"application/octet-stream","file_size_bytes":"22313","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260417-160828-6006d4f1.md","is_deleted":"0","created_at":"2026-04-17 12:08:29","updated_at":"2026-04-17 12:08:29","editor_content":"# Ella's Alterations - Comprehensive System Documentation\n\n**Generated:** April 17, 2026\n**Location:** C:\\code\\customerdb\n\n---\n\n## Table of Contents\n\n1. [System Overview](#1-system-overview)\n2. [Directory Structure](#2-directory-structure)\n3. [Database Schema](#3-database-schema)\n4. [API Endpoints](#4-api-endpoints)\n5. [External Integrations](#5-external-integrations)\n6. [Scheduled Jobs / Cron](#6-scheduled-jobs--cron)\n7. [Web Applications](#7-web-applications)\n8. [Settings & Configuration](#8-settings--configuration)\n9. [Migration Notes](#9-migration-notes)\n10. [Scripts & Tools](#10-scripts--tools)\n\n---\n\n## 1. System Overview\n\n### Purpose\nElla's Alterations is a comprehensive customer relationship management (CRM) andAlteration tracking system for a tailor shop. It manages customers, visits/alteration orders, appointments, payments, and provides integration with external services.\n\n### Server Information\n- **Primary Server:** kefa@192.168.7.202\n- **Mirror Drive:** /mnt/drive2 (mirrors /mnt/drive1)\n- **Database:** MariaDB - database `ellas_alterations`, user `kefa_admin`\n- **Web Server:** Apache 2.4 with mod-php\n- **PHP Version:** 8.3\n\n### Three Web Applications\n\n| App | Local URL | Public URL | Purpose |\n|-----|-----------|------------|---------|\n| Front Desk | http://192.168.7.202:8895 | https://ella.floridaalterations.com | Customer-facing operations |\n| Management | http://192.168.7.202:8894 | (LAN only) | Reports, admin, settings |\n| Scheduler | http://192.168.7.202:8897 | https://schedule.floridaalterations.com | Easy!Appointments booking |\n\n---\n\n## 2. Directory Structure\n\n```\ncustomerdb/\n├── backend/ # Main PHP backend API and services\n│ ├── api/ # REST API endpoints (~57 files)\n│ ├── bin/ # Binary/executable scripts\n│ ├── backup/ # Backup versions of services\n│ └── jobs/ # Background job scripts\n├── frontend/ # Front Desk app (port 8895)\n│ ├── *.html # HTML pages\n│ ├── api/ # PHP API endpoints\n│ └── reports/ # PDF reports (MMDDYYYY/filename.pdf)\n├── webui/ # Management app (port 8894)\n│ ├── *.html # HTML pages\n│ ├── api/ # PHP API endpoints\n│ ├── backend/ # Backend API copy\n│ └── email/ # Email templates\n├── schedule/ # Easy!Appointments installation\n├── schema/ # Database schema files\n├── migrations/ # SQL migrations\n├── scripts/ # PowerShell/Python import scripts\n├── docs/ # Documentation\n├── blog/ # Blog system and reengineering\n├── csv/ # CSV data files\n├── backups/ # Backup archives\n├── Topaz/ # Topaz signature pad configs\n├── webui/ # Second web interface\n│ ├── api/\n│ ├── backend/\n│ └── frontend/\n├── notused/ # Deprecated/unused files\n└── wordpress-plugin/ # WordPress integration\n```\n\n---\n\n## 3. Database Schema\n\n### Core Tables (from mariadb_core_schema.sql)\n\n#### customers\nMain customer registry.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| customer_id | INT UNSIGNED PK | Auto-increment |\n| legacy_customer_id | INT NULL | Original Access DB ID |\n| created_at | DATETIME | Record creation time |\n| full_name | VARCHAR(200) | Customer full name |\n| phone | VARCHAR(30) | Formatted (XXX) XXX-XXXX |\n| email | VARCHAR(255) | Email address |\n| pickup_due_at | DATETIME | Next pickup date (synced from EA) |\n| description | TEXT | Description |\n| notes | TEXT | Internal notes |\n| is_new_customer | TINYINT(1) | 1 if flagged as new |\n\n#### visits\nOne row per alteration job/order.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| visit_id | INT UNSIGNED PK | |\n| legacy_visit_id | INT NULL | Original Access DB ID |\n| customer_id | INT UNSIGNED FK | → customers |\n| visit_date | DATETIME | Drop-off date |\n| pickup_date | DATETIME | Expected pickup |\n| notes | TEXT | General visit notes |\n| customer_name_snapshot | VARCHAR(200) | |\n| phone_snapshot | VARCHAR(30) | |\n| email_snapshot | VARCHAR(255) | |\n| color | VARCHAR(100) | Garment color |\n| alterations_needed | TEXT | |\n| alterations_notes | TEXT | Detailed alteration instructions |\n| alterations_price | DECIMAL(10,2) | Manual price field |\n| total_alteration_price | DECIMAL(10,2) | Calculated from items |\n| expedited_fee | DECIMAL(10,2) | |\n| paid_date | DATETIME | When paid |\n| signature_text | TEXT | Text signature (legacy) |\n| signature_date | DATETIME | |\n| paid_flag | TINYINT(1) | 1 = paid |\n| source_key_value | VARCHAR(100) | |\n\n#### visit_signatures\nFull resolution signature images per visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| signature_id | BIGINT UNSIGNED PK | |\n| visit_id | INT UNIQUE | One signature per visit |\n| signature_date | DATETIME | |\n| image_format | VARCHAR(50) | Default 'image/png' |\n| original_filename | VARCHAR(255) | |\n| original_extension | VARCHAR(20) | |\n| file_size_bytes | BIGINT | |\n| sha256_hash | CHAR(64) | Hash for verification |\n| signature_image | LONGBLOB | Raw PNG binary |\n| created_at | DATETIME | |\n\n#### item_types\nMaster list of alteration types (e.g., Hem, Zipper, Taper).\n\n| Column | Type | Notes |\n|--------|------|-------|\n| item_type_id | INT UNSIGNED PK | |\n| type_name | VARCHAR(100) UNIQUE | Display name |\n\n#### orders\nOrders per visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_id | BIGINT UNSIGNED PK | |\n| visit_id | INT UNSIGNED FK | → visits |\n| customer_id | INT UNSIGNED FK | → customers |\n| created_at | DATETIME | |\n| no_tax | TINYINT(1) | Tax exempt |\n| picked_up | TINYINT(1) | |\n| source_table | VARCHAR(50) | |\n\n#### order_items\nLine items for each alteration within a visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_item_id | BIGINT UNSIGNED PK | |\n| order_id | BIGINT UNSIGNED FK | → orders |\n| legacy_alteration_id | INT NULL | |\n| item_type_id | INT FK | → item_types |\n| item_type_name | VARCHAR(100) | |\n| description | TEXT | |\n| quantity | INT | Default 1 |\n| unit_price | DECIMAL(10,2) | |\n| total_price | DECIMAL(10,2) | |\n| notes | TEXT | Per-item notes |\n| color | VARCHAR(100) | Garment color |\n| no_tax | TINYINT(1) | Tax exempt |\n| is_range_price | TINYINT(1) | |\n| range_price_label | VARCHAR(100) | |\n| paid_flag | TINYINT(1) | |\n| paid_type | VARCHAR(100) | |\n| picked_up | TINYINT(1) | |\n| created_at | DATETIME | |\n\n#### order_item_type_links\nMany-to-many links between order items and item types.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_item_type_link_id | BIGINT UNSIGNED PK | |\n| order_item_id | BIGINT UNSIGNED FK | → order_items |\n| item_type_id | INT UNSIGNED FK | → item_types |\n| slot_number | TINYINT UNSIGNED | |\n\n#### payments\nPayment records per order.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| payment_id | BIGINT UNSIGNED PK | |\n| order_id | BIGINT UNSIGNED FK | → orders |\n| payment_date | DATETIME | |\n| payment_type | VARCHAR(100) | |\n| payment_method | VARCHAR(100) | |\n| amount | DECIMAL(10,2) | |\n| notes | TEXT | |\n\n#### tax_payments\nTax payment records.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| tax_payment_id | INT UNSIGNED PK | |\n| legacy_tax_payment_id | INT NULL | |\n| payment_date | DATETIME | |\n| payment_type | VARCHAR(100) | |\n| payment_method | VARCHAR(100) | |\n| amount | DECIMAL(10,2) | |\n| notes | TEXT | |\n| receipt_blob | LONGBLOB | |\n\n#### change_log\nAudit log for changes.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| log_id | BIGINT UNSIGNED PK | |\n| legacy_log_id | INT NULL | |\n| table_name | VARCHAR(100) | |\n| record_id | BIGINT | |\n| visit_id | INT | |\n| field_name | VARCHAR(100) | |\n| old_value | TEXT | |\n| new_value | TEXT | |\n| changed_at | DATETIME | |\n| changed_by | VARCHAR(100) | |\n\n#### ads\nAdvertising/articles.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| ad_id | INT UNSIGNED PK | |\n| legacy_ad_id | INT NULL | |\n| article_text | TEXT | |\n| article_picture | LONGBLOB | |\n| site_submitted | VARCHAR(255) | |\n| views | INT | Default 0 |\n| date_posted | DATETIME | |\n\n#### staging_* tables\nStaging tables for data migration.\n\n| Table Name | Purpose |\n|------------|---------|\n| staging_customer_table_raw | Staging for legacy customer data |\n| staging_customer_visits_raw | Staging for legacy visits |\n| staging_alteration_items_raw | Staging for items |\n\n### Additional Backend Tables\n\n| Table Name | Purpose |\n|------------|---------|\n| setmore_appointments | Synced from Setmore |\n| backend_conversion_job | Document conversion jobs |\n| backend_job_runs | Background job execution log |\n| customer_totals | Daily customer totals |\n| daily_customer_totals | Daily summary totals |\n| customer_payment_transactions | Payment ledger |\n| legacy_customer_totals_monthly | Monthly totals (legacy) |\n| legacy_customer_totals_irs_monthly | IRS monthly reports |\n| backend_appointment_workflow | Appointment state machine |\n| backend_no_show_log | No-show tracking |\n| backend_error_log | Error logging |\n| backend_change_log | Change audit log |\n| backend_document | Document storage |\n| backend_document_version | Document version history |\n| backend_speed_dial | Quick links |\n| backend_square_terminal_code | Square terminal pairing codes |\n| backend_square_terminal_device | Square device registry |\n| cron_job | Scheduled job definitions |\n| cron_job_run | Cron execution history |\n| app_settings | Global configuration |\n| app_users | Login accounts |\n| remember_tokens | \"Remember Me\" tokens |\n| customer_reports | PDF report index |\n\n---\n\n## 4. API Endpoints\n\n### 4.1 Frontend API (port 8895 /api/)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **customer_get.php** | Get customer by ID |\n| **customer_save.php** | Create or update customer |\n| **customer_detail.php** | Customer + visits summary |\n| **customer_delete.php** | Delete customer |\n| **customer_list_hide.php** | Hide customer from list |\n| **search_customers.php** | Search by name/phone |\n| **visit_get.php** | Get visit + items |\n| **visit_save.php** | Create/update visit + items |\n| **visit_delete.php** | Delete visit |\n| **visit_report_generate.php** | Generate visit report |\n| **item_types.php** | List all alteration types |\n| **item_type_add.php** | Add new item type |\n| **item_type_update.php** | Update item type |\n| **item_type_delete.php** | Delete item type |\n| **pickups_today.php** | Orders due today |\n| **ready.php** | Orders ready for pickup |\n| **overdue.php** | Overdue orders |\n| **upcoming.php** | Orders due in next N days |\n| **workload_7days.php** | 7-day workload |\n| **save_signature.php** | Save canvas signature |\n| **reports_list.php** | Reports for customer |\n| **attach_reports.php** | Index all PDFs to DB |\n| **save_signature.php** | Save signature image |\n| **today_appointments_list.php** | Today's appointments |\n| **settings_get.php** | Get app settings |\n| **settings_save.php** | Save app settings |\n| **settings_css.php** | Live theme CSS |\n| **send_email.php** | Send email via SMTP |\n| **send_sms.php** | Send SMS via Twilio |\n| **send_reminders.php** | Send appointment reminders |\n| **ea_appointments.php** | EasyAppointments REST API |\n| **ea_webhook.php** | Receives EA webhook events |\n| **setmore.php** | Setmore API proxy |\n| **setmore_to_ea_import.php** | Import customers to EA |\n| **square_config.php** | Square public config |\n| **square_process.php** | Process Square payment |\n| **square_customer_log.php** | Customer Square log |\n| **square_terminal.php** | Square terminal |\n| **login.php** | Authenticate |\n| **logout.php** | Clear session |\n| **session_check.php** | Check if logged in |\n| **auth.php** | Session authentication |\n| **config.php** | App config constants |\n| **db.php** | Database connection |\n| **log_error.php** | Log client-side error |\n| **todo_list.php** | Todo list management |\n| **send_telegram.php** | Send Telegram messages |\n\n### 4.2 Management API (port 8894 /api/)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **login.php** | Authenticate |\n| **logout.php** | Clear session |\n| **session_check.php** | Check if logged in |\n| **customer_detail.php** | Customer + visits |\n| **search_customers.php** | Search customers |\n| **update_customer.php** | Update customer |\n| **users_list.php** | List all users |\n| **user_save.php** | Create/update user |\n| **list_report_dirs.php** | Date folders |\n| **list_report_files.php** | PDFs in folder |\n| **report_file.php** | Access report file |\n| **pickups_today.php** | Today's pickups |\n| **ready.php** | Ready for pickup |\n| **overdue.php** | Overdue |\n| **settings_get.php** | Get settings |\n| **settings_save.php** | Save settings |\n| **settings_css.php** | Live CSS theme |\n| **imap_list.php** | List IMAP folders |\n| **imap_fetch.php** | Fetch emails |\n| **imap_send.php** | Send email via IMAP |\n| **imap_action.php** | IMAP actions |\n| **sms_send_helper.php** | SMS sending helper |\n| **sms_webhook_receive.php** | Receive SMS webhooks |\n| **sms_inbox_api.php** | SMS inbox API |\n| **slideshow_list.php** | Slideshow images |\n| **slideshow_upload.php** | Upload slideshow |\n| **slideshow_delete.php** | Delete slideshow |\n| **users_list.php** | User management |\n| **report_list.php** | Report list |\n| **debug_filters.php** | Debug filters |\n| **sysadmin.php** | System admin |\n| **test.php** | Testing endpoint |\n\n### 4.3 Backend API (C:\\code\\customerdb\\backend\\api\\)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **customer.php** | Get customer details |\n| **customers.php** | List/search customers |\n| **customer_save.php** | Save/update customer |\n| **visit.php** | Get visit details |\n| **visit_delete.php** | Delete visit |\n| **appointments.php** | Unified appointments |\n| **appointments_summary.php** | Appointment statistics |\n| **setmore_appointments.php** | Setmore appointments |\n| **setmore_import.php** | Import from Setmore |\n| **module_*.php** | Module loaders |\n| **alteration_items.php** | Alteration management |\n| **alteration_items_summary.php** | Alteration statistics |\n| **gold_silver_*.php** | Gold/silver prices |\n| **bank_*.php** | Bank transactions |\n| **stocks_summary.php** | Stock data |\n| **medication_*.php** | Medication tracking |\n| **payroll_import.php** | Payroll import |\n| **car_maintenance_*.php** | Vehicle maintenance |\n| **duke_*.php** | Duke integration |\n| **reports_*.php** | Report generation |\n| **totals_import.php** | Customer totals import |\n| **documents.php** | Document management |\n| **document_upload.php** | File uploads |\n| **cust_email_*.php** | Email data |\n| **mail_*.php** | Email sending |\n| **reminders_create.php** | Create reminders |\n| **nightly_reports.php** | Nightly reports |\n| **morning_jobs.php** | Morning jobs |\n| **appointment_workflow.php** | Workflow management |\n| **sysadmin.php** | System admin |\n| **reporting_module.php** | Reporting |\n| **job_runs.php** | Job tracking |\n| **health.php** | Health check |\n| **overview.php** | Dashboard data |\n\n---\n\n## 5. External Integrations\n\n### 5.1 Square (Payment Processing)\n- **Purpose:** Point-of-sale payments, receipts, terminal management\n- **Features:**\n - Square Terminal device pairing/management\n - Payment processing with tips\n - Customer payment logging\n - Sandbox and live environments\n- **Tables:** `backend_square_terminal_code`, `backend_square_terminal_device`, `square_customer_log`\n\n### 5.2 Twilio (SMS)\n- **Purpose:** SMS notifications and reminders\n- **Features:**\n - Send SMS to customers\n - Appointment reminders\n - SMS consent tracking\n- **Settings:** `twilio_sid`, `twilio_token`, `twilio_from`\n\n### 5.3 Setmore (Appointment Booking)\n- **Purpose:** Appointment synchronization\n- **Features:**\n - Pull appointments from Setmore API\n - Link to customers/visits\n- **Table:** `setmore_appointments`\n- **Settings:** `setmore_refresh_token`, `setmore_staff_key`\n\n### 5.4 EasyAppointments (Alternative Booking)\n- **Purpose:** Full appointment booking platform\n- **Location:** C:\\code\\customerdb\\schedule\\\n- **Features:**\n - Customer/provider management\n - Web-based booking widget\n - REST API\n\n### 5.5 Telegram (Notifications)\n- **Purpose:** Bot-based notifications\n- **Features:**\n - Send notifications to channels\n - Visit alerts\n - Cron job status\n- **Settings:** `telegram_bot_token`, `telegram_chat_id`\n\n### 5.6 Ollama (AI - Optional)\n- **Purpose:** Local AI for email generation\n- **Settings:** `ollama_url`, `ollama_default_model`\n\n---\n\n## 6. Scheduled Jobs / Cron\n\n### Configured Cron Jobs\n\n| Job Label | Schedule | Purpose |\n|----------|----------|---------|\n| **Next-Day Customer Emails** | `0 20 * * *` (8 PM) | Pickup reminder emails |\n| **Morning Import** | `0 6 * * *` (6 AM) | Morning data import |\n| **Morning Jobs** | `0 9 * * *` (9 AM) | Appointment sync, workflow |\n| **Nightly Reports** | `0 20 * * *` (8 PM) | Generate nightly summaries |\n| **Database Backup** | `0 2 * * *` (2 AM) | Full MariaDB backup |\n| **Daily Cleanup** | `0 4 * * *` (4 AM) | Temp files, old logs |\n| **Auto Delete** | `15 4 * * *` (4:15 AM) | Clean placeholder records |\n\n### Background Job Scripts (backend/jobs/)\n\n| Script | Purpose |\n|--------|---------|\n| **run_morning_jobs.php** | Main morning job runner |\n| **smoke_morning_jobs.php** | Smoke test |\n| **send_nightly_reports.php** | Nightly report emailer |\n| **send_nextday_customer_emails.php** | Next-day reminders |\n| **delete_add_name_records.php** | Cleanup job |\n| **rebuild_customer_totals.php** | Rebuild totals |\n| **rebuild_daily_customer_totals.php** | Rebuild daily totals |\n| **rebuild_payment_ledger.php** | Rebuild payment ledger |\n| **import_customer_totals_csv.php** | CSV import |\n| **backfill_square_customer_log.php** | Square log backfill |\n| **run_cron_job.php** | Generic cron runner |\n\n---\n\n## 7. Web Applications\n\n### 7.1 Front Desk App (port 8895)\n\n| Page | Purpose |\n|------|---------|\n| **index.html** | Main dashboard, search, stats |\n| **customer.html** | Customer detail + visit history |\n| **visit.html** | Create/edit alteration order |\n| **customer_display.html** | Second monitor display |\n| **receipt.html** | Printable receipt |\n| **reports.html** | Order list by status |\n| **upcoming.html** | Next 7 days pickups |\n| **schedule.html** | Appointments calendar |\n| **login.html** | Login page |\n| **item_types.html** | Item type management |\n\n### 7.2 Management App (port 8894)\n\n| Page | Purpose |\n|------|---------|\n| **index.html** | Login page |\n| **webui.html** | Dashboard |\n| **customer.html** | Customer detail view |\n| **admin.html** | Reports browser |\n| **settings.html** | App settings editor |\n| **users.html** | User management |\n| **reports.html** | Order reports |\n\n---\n\n## 8. Settings & Configuration\n\n### app_settings Keys\n\n| Key | Purpose |\n|-----|---------|\n| theme_bg | Page background color |\n| theme_panel | Card/panel color |\n| theme_text | Text color |\n| theme_accent | Accent/button color |\n| theme_accent_deep | Darker accent |\n| theme_teal | Teal color |\n| theme_font | Font family |\n| smtp_host | Email server hostname |\n| smtp_port | Email server port |\n| smtp_secure | tls or ssl |\n| smtp_user | Email login |\n| smtp_pass | Email password |\n| smtp_from_email | From address |\n| smtp_from_name | From display name |\n| twilio_sid | Twilio Account SID |\n| twilio_token | Twilio Auth Token |\n| twilio_from | Twilio phone number |\n| imap_host | Incoming email server |\n| imap_port | IMAP port |\n| imap_ssl | SSL enable |\n| imap_user | IMAP login |\n| imap_pass | IMAP password |\n| square_token | Square API token |\n| square_location | Square location ID |\n| setmore_staff_key | Setmore API key |\n| setmore_refresh | Setmore refresh token |\n| require_report | PDF required |\n| require_signature | Signature required |\n| reports_base_path | Reports disk path |\n| reports_base_url | Reports URL |\n| ollama_url | Ollama AI URL |\n| ollama_default_model | Ollama model |\n| telegram_bot_token | Telegram bot token |\n| telegram_chat_id | Telegram chat ID |\n\n---\n\n## 9. Migration Notes\n\n### Access to MariaDB Workflow\n\n1. **Export source data from Access**\n - Export ordinary tables to CSV\n - Export attachment fields with VBA or DAO Recordset2 logic\n - Keep original IDs during migration\n\n2. **Load into MariaDB staging**\n - Use `LOAD DATA LOCAL INFILE` for CSV files\n - Load signatures separately into `visit_signatures`\n\n3. **Normalize**\n - `CustomerTable` → `customers`\n - `CustomerVisits` → `visits`\n - `AlterationItems` → `orders`, `order_items`, `order_item_type_links`\n - `2024TaxPayments` → `tax_payments`\n - `AlterationItemsChangeLog` → `change_log`\n\n4. **Signature BLOB path**\n - Export Access attachment to temp file\n - Convert BMP to PNG\n - Insert PNG bytes into `visit_signatures.signature_image`\n\n5. **Validation**\n - Compare source row counts vs target\n - Compare distinct legacy IDs\n - Compare sample signatures by SHA256\n\n---\n\n## 10. Scripts & Tools\n\n### PowerShell Scripts (scripts/)\n\n| Script | Purpose |\n|--------|---------|\n| **import_legacy_totals_from_excel.ps1** | Import totals from Excel |\n| **sync_backend_mirror.ps1** | Sync backend mirror |\n| **check_backend_mirror.ps1** | Check mirror status |\n\n### Python Scripts (scripts/)\n\n| Script | Purpose |\n|--------|---------|\n| **signature_blob_loader.py** | Load signature blobs |\n| **access_to_mariadb_parser.py** | Parse Access to MariaDB |\n| **import_access_v2.py** | Import Access v2 |\n| **import_csv_v2.py** | Import CSV v2 |\n\n### SQL Migrations (migrations/)\n\n| File | Purpose |\n|------|---------|\n| **20260407_add_conversion_job.sql** | Add conversion job table |\n| **20260411_order_items_item_type_name_no_fk.sql** | Order items no FK |\n| **20260416_blog_prompt_templates.sql** | Blog prompt templates |\n\n---\n\n## File Statistics\n\n- **Total PHP files:** 200+\n- **Total SQL files:** 100+\n- **Total HTML pages:** 20+\n- **Database tables:** 30+\n\n---\n\n*End of Documentation*","is_text_editable":1,"can_edit_inline":1}
{"backend_document_id":"20","document_type":"upload","title":"Complete Documentation up to date 04-17-2026","slug":"complete-documentation-up-to-date","summary_text":"Complete Documentation up to date 04-17-2026","content_markdown":"# Ella's Alterations - Comprehensive System Documentation\r\n\r\n**Generated:** April 17, 2026\r\n**Location:** C:\\code\\customerdb\r\n\r\n---\r\n\r\n## Table of Contents\r\n\r\n1. [System Overview](#1-system-overview)\r\n2. [Directory Structure](#2-directory-structure)\r\n3. [Database Schema](#3-database-schema)\r\n4. [API Endpoints](#4-api-endpoints)\r\n5. [External Integrations](#5-external-integrations)\r\n6. [Scheduled Jobs / Cron](#6-scheduled-jobs--cron)\r\n7. [Web Applications](#7-web-applications)\r\n8. [Settings & Configuration](#8-settings--configuration)\r\n9. [Migration Notes](#9-migration-notes)\r\n10. [Scripts & Tools](#10-scripts--tools)\r\n\r\n---\r\n\r\n## 1. System Overview\r\n\r\n### Purpose\r\nElla's Alterations is a comprehensive customer relationship management (CRM) andAlteration tracking system for a tailor shop. It manages customers, visits/alteration orders, appointments, payments, and provides integration with external services.\r\n\r\n### Server Information\r\n- **Primary Server:** kefa@192.168.7.202\r\n- **Mirror Drive:** /mnt/drive2 (mirrors /mnt/drive1)\r\n- **Database:** MariaDB - database `ellas_alterations`, user `kefa_admin`\r\n- **Web Server:** Apache 2.4 with mod-php\r\n- **PHP Version:** 8.3\r\n\r\n### Three Web Applications\r\n\r\n| App | Local URL | Public URL | Purpose |\r\n|-----|-----------|------------|---------|\r\n| Front Desk | http://192.168.7.202:8895 | https://ella.floridaalterations.com | Customer-facing operations |\r\n| Management | http://192.168.7.202:8894 | (LAN only) | Reports, admin, settings |\r\n| Scheduler | http://192.168.7.202:8897 | https://schedule.floridaalterations.com | Easy!Appointments booking |\r\n\r\n---\r\n\r\n## 2. Directory Structure\r\n\r\n```\r\ncustomerdb/\r\n├── backend/ # Main PHP backend API and services\r\n│ ├── api/ # REST API endpoints (~57 files)\r\n│ ├── bin/ # Binary/executable scripts\r\n│ ├── backup/ # Backup versions of services\r\n│ └── jobs/ # Background job scripts\r\n├── frontend/ # Front Desk app (port 8895)\r\n│ ├── *.html # HTML pages\r\n│ ├── api/ # PHP API endpoints\r\n│ └── reports/ # PDF reports (MMDDYYYY/filename.pdf)\r\n├── webui/ # Management app (port 8894)\r\n│ ├── *.html # HTML pages\r\n│ ├── api/ # PHP API endpoints\r\n│ ├── backend/ # Backend API copy\r\n│ └── email/ # Email templates\r\n├── schedule/ # Easy!Appointments installation\r\n├── schema/ # Database schema files\r\n├── migrations/ # SQL migrations\r\n├── scripts/ # PowerShell/Python import scripts\r\n├── docs/ # Documentation\r\n├── blog/ # Blog system and reengineering\r\n├── csv/ # CSV data files\r\n├── backups/ # Backup archives\r\n├── Topaz/ # Topaz signature pad configs\r\n├── webui/ # Second web interface\r\n│ ├── api/\r\n│ ├── backend/\r\n│ └── frontend/\r\n├── notused/ # Deprecated/unused files\r\n└── wordpress-plugin/ # WordPress integration\r\n```\r\n\r\n---\r\n\r\n## 3. Database Schema\r\n\r\n### Core Tables (from mariadb_core_schema.sql)\r\n\r\n#### customers\r\nMain customer registry.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| customer_id | INT UNSIGNED PK | Auto-increment |\r\n| legacy_customer_id | INT NULL | Original Access DB ID |\r\n| created_at | DATETIME | Record creation time |\r\n| full_name | VARCHAR(200) | Customer full name |\r\n| phone | VARCHAR(30) | Formatted (XXX) XXX-XXXX |\r\n| email | VARCHAR(255) | Email address |\r\n| pickup_due_at | DATETIME | Next pickup date (synced from EA) |\r\n| description | TEXT | Description |\r\n| notes | TEXT | Internal notes |\r\n| is_new_customer | TINYINT(1) | 1 if flagged as new |\r\n\r\n#### visits\r\nOne row per alteration job/order.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| visit_id | INT UNSIGNED PK | |\r\n| legacy_visit_id | INT NULL | Original Access DB ID |\r\n| customer_id | INT UNSIGNED FK | → customers |\r\n| visit_date | DATETIME | Drop-off date |\r\n| pickup_date | DATETIME | Expected pickup |\r\n| notes | TEXT | General visit notes |\r\n| customer_name_snapshot | VARCHAR(200) | |\r\n| phone_snapshot | VARCHAR(30) | |\r\n| email_snapshot | VARCHAR(255) | |\r\n| color | VARCHAR(100) | Garment color |\r\n| alterations_needed | TEXT | |\r\n| alterations_notes | TEXT | Detailed alteration instructions |\r\n| alterations_price | DECIMAL(10,2) | Manual price field |\r\n| total_alteration_price | DECIMAL(10,2) | Calculated from items |\r\n| expedited_fee | DECIMAL(10,2) | |\r\n| paid_date | DATETIME | When paid |\r\n| signature_text | TEXT | Text signature (legacy) |\r\n| signature_date | DATETIME | |\r\n| paid_flag | TINYINT(1) | 1 = paid |\r\n| source_key_value | VARCHAR(100) | |\r\n\r\n#### visit_signatures\r\nFull resolution signature images per visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| signature_id | BIGINT UNSIGNED PK | |\r\n| visit_id | INT UNIQUE | One signature per visit |\r\n| signature_date | DATETIME | |\r\n| image_format | VARCHAR(50) | Default 'image/png' |\r\n| original_filename | VARCHAR(255) | |\r\n| original_extension | VARCHAR(20) | |\r\n| file_size_bytes | BIGINT | |\r\n| sha256_hash | CHAR(64) | Hash for verification |\r\n| signature_image | LONGBLOB | Raw PNG binary |\r\n| created_at | DATETIME | |\r\n\r\n#### item_types\r\nMaster list of alteration types (e.g., Hem, Zipper, Taper).\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| item_type_id | INT UNSIGNED PK | |\r\n| type_name | VARCHAR(100) UNIQUE | Display name |\r\n\r\n#### orders\r\nOrders per visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_id | BIGINT UNSIGNED PK | |\r\n| visit_id | INT UNSIGNED FK | → visits |\r\n| customer_id | INT UNSIGNED FK | → customers |\r\n| created_at | DATETIME | |\r\n| no_tax | TINYINT(1) | Tax exempt |\r\n| picked_up | TINYINT(1) | |\r\n| source_table | VARCHAR(50) | |\r\n\r\n#### order_items\r\nLine items for each alteration within a visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_item_id | BIGINT UNSIGNED PK | |\r\n| order_id | BIGINT UNSIGNED FK | → orders |\r\n| legacy_alteration_id | INT NULL | |\r\n| item_type_id | INT FK | → item_types |\r\n| item_type_name | VARCHAR(100) | |\r\n| description | TEXT | |\r\n| quantity | INT | Default 1 |\r\n| unit_price | DECIMAL(10,2) | |\r\n| total_price | DECIMAL(10,2) | |\r\n| notes | TEXT | Per-item notes |\r\n| color | VARCHAR(100) | Garment color |\r\n| no_tax | TINYINT(1) | Tax exempt |\r\n| is_range_price | TINYINT(1) | |\r\n| range_price_label | VARCHAR(100) | |\r\n| paid_flag | TINYINT(1) | |\r\n| paid_type | VARCHAR(100) | |\r\n| picked_up | TINYINT(1) | |\r\n| created_at | DATETIME | |\r\n\r\n#### order_item_type_links\r\nMany-to-many links between order items and item types.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_item_type_link_id | BIGINT UNSIGNED PK | |\r\n| order_item_id | BIGINT UNSIGNED FK | → order_items |\r\n| item_type_id | INT UNSIGNED FK | → item_types |\r\n| slot_number | TINYINT UNSIGNED | |\r\n\r\n#### payments\r\nPayment records per order.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| payment_id | BIGINT UNSIGNED PK | |\r\n| order_id | BIGINT UNSIGNED FK | → orders |\r\n| payment_date | DATETIME | |\r\n| payment_type | VARCHAR(100) | |\r\n| payment_method | VARCHAR(100) | |\r\n| amount | DECIMAL(10,2) | |\r\n| notes | TEXT | |\r\n\r\n#### tax_payments\r\nTax payment records.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| tax_payment_id | INT UNSIGNED PK | |\r\n| legacy_tax_payment_id | INT NULL | |\r\n| payment_date | DATETIME | |\r\n| payment_type | VARCHAR(100) | |\r\n| payment_method | VARCHAR(100) | |\r\n| amount | DECIMAL(10,2) | |\r\n| notes | TEXT | |\r\n| receipt_blob | LONGBLOB | |\r\n\r\n#### change_log\r\nAudit log for changes.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| log_id | BIGINT UNSIGNED PK | |\r\n| legacy_log_id | INT NULL | |\r\n| table_name | VARCHAR(100) | |\r\n| record_id | BIGINT | |\r\n| visit_id | INT | |\r\n| field_name | VARCHAR(100) | |\r\n| old_value | TEXT | |\r\n| new_value | TEXT | |\r\n| changed_at | DATETIME | |\r\n| changed_by | VARCHAR(100) | |\r\n\r\n#### ads\r\nAdvertising/articles.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| ad_id | INT UNSIGNED PK | |\r\n| legacy_ad_id | INT NULL | |\r\n| article_text | TEXT | |\r\n| article_picture | LONGBLOB | |\r\n| site_submitted | VARCHAR(255) | |\r\n| views | INT | Default 0 |\r\n| date_posted | DATETIME | |\r\n\r\n#### staging_* tables\r\nStaging tables for data migration.\r\n\r\n| Table Name | Purpose |\r\n|------------|---------|\r\n| staging_customer_table_raw | Staging for legacy customer data |\r\n| staging_customer_visits_raw | Staging for legacy visits |\r\n| staging_alteration_items_raw | Staging for items |\r\n\r\n### Additional Backend Tables\r\n\r\n| Table Name | Purpose |\r\n|------------|---------|\r\n| setmore_appointments | Synced from Setmore |\r\n| backend_conversion_job | Document conversion jobs |\r\n| backend_job_runs | Background job execution log |\r\n| customer_totals | Daily customer totals |\r\n| daily_customer_totals | Daily summary totals |\r\n| customer_payment_transactions | Payment ledger |\r\n| legacy_customer_totals_monthly | Monthly totals (legacy) |\r\n| legacy_customer_totals_irs_monthly | IRS monthly reports |\r\n| backend_appointment_workflow | Appointment state machine |\r\n| backend_no_show_log | No-show tracking |\r\n| backend_error_log | Error logging |\r\n| backend_change_log | Change audit log |\r\n| backend_document | Document storage |\r\n| backend_document_version | Document version history |\r\n| backend_speed_dial | Quick links |\r\n| backend_square_terminal_code | Square terminal pairing codes |\r\n| backend_square_terminal_device | Square device registry |\r\n| cron_job | Scheduled job definitions |\r\n| cron_job_run | Cron execution history |\r\n| app_settings | Global configuration |\r\n| app_users | Login accounts |\r\n| remember_tokens | \"Remember Me\" tokens |\r\n| customer_reports | PDF report index |\r\n\r\n---\r\n\r\n## 4. API Endpoints\r\n\r\n### 4.1 Frontend API (port 8895 /api/)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **customer_get.php** | Get customer by ID |\r\n| **customer_save.php** | Create or update customer |\r\n| **customer_detail.php** | Customer + visits summary |\r\n| **customer_delete.php** | Delete customer |\r\n| **customer_list_hide.php** | Hide customer from list |\r\n| **search_customers.php** | Search by name/phone |\r\n| **visit_get.php** | Get visit + items |\r\n| **visit_save.php** | Create/update visit + items |\r\n| **visit_delete.php** | Delete visit |\r\n| **visit_report_generate.php** | Generate visit report |\r\n| **item_types.php** | List all alteration types |\r\n| **item_type_add.php** | Add new item type |\r\n| **item_type_update.php** | Update item type |\r\n| **item_type_delete.php** | Delete item type |\r\n| **pickups_today.php** | Orders due today |\r\n| **ready.php** | Orders ready for pickup |\r\n| **overdue.php** | Overdue orders |\r\n| **upcoming.php** | Orders due in next N days |\r\n| **workload_7days.php** | 7-day workload |\r\n| **save_signature.php** | Save canvas signature |\r\n| **reports_list.php** | Reports for customer |\r\n| **attach_reports.php** | Index all PDFs to DB |\r\n| **save_signature.php** | Save signature image |\r\n| **today_appointments_list.php** | Today's appointments |\r\n| **settings_get.php** | Get app settings |\r\n| **settings_save.php** | Save app settings |\r\n| **settings_css.php** | Live theme CSS |\r\n| **send_email.php** | Send email via SMTP |\r\n| **send_sms.php** | Send SMS via Twilio |\r\n| **send_reminders.php** | Send appointment reminders |\r\n| **ea_appointments.php** | EasyAppointments REST API |\r\n| **ea_webhook.php** | Receives EA webhook events |\r\n| **setmore.php** | Setmore API proxy |\r\n| **setmore_to_ea_import.php** | Import customers to EA |\r\n| **square_config.php** | Square public config |\r\n| **square_process.php** | Process Square payment |\r\n| **square_customer_log.php** | Customer Square log |\r\n| **square_terminal.php** | Square terminal |\r\n| **login.php** | Authenticate |\r\n| **logout.php** | Clear session |\r\n| **session_check.php** | Check if logged in |\r\n| **auth.php** | Session authentication |\r\n| **config.php** | App config constants |\r\n| **db.php** | Database connection |\r\n| **log_error.php** | Log client-side error |\r\n| **todo_list.php** | Todo list management |\r\n| **send_telegram.php** | Send Telegram messages |\r\n\r\n### 4.2 Management API (port 8894 /api/)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **login.php** | Authenticate |\r\n| **logout.php** | Clear session |\r\n| **session_check.php** | Check if logged in |\r\n| **customer_detail.php** | Customer + visits |\r\n| **search_customers.php** | Search customers |\r\n| **update_customer.php** | Update customer |\r\n| **users_list.php** | List all users |\r\n| **user_save.php** | Create/update user |\r\n| **list_report_dirs.php** | Date folders |\r\n| **list_report_files.php** | PDFs in folder |\r\n| **report_file.php** | Access report file |\r\n| **pickups_today.php** | Today's pickups |\r\n| **ready.php** | Ready for pickup |\r\n| **overdue.php** | Overdue |\r\n| **settings_get.php** | Get settings |\r\n| **settings_save.php** | Save settings |\r\n| **settings_css.php** | Live CSS theme |\r\n| **imap_list.php** | List IMAP folders |\r\n| **imap_fetch.php** | Fetch emails |\r\n| **imap_send.php** | Send email via IMAP |\r\n| **imap_action.php** | IMAP actions |\r\n| **sms_send_helper.php** | SMS sending helper |\r\n| **sms_webhook_receive.php** | Receive SMS webhooks |\r\n| **sms_inbox_api.php** | SMS inbox API |\r\n| **slideshow_list.php** | Slideshow images |\r\n| **slideshow_upload.php** | Upload slideshow |\r\n| **slideshow_delete.php** | Delete slideshow |\r\n| **users_list.php** | User management |\r\n| **report_list.php** | Report list |\r\n| **debug_filters.php** | Debug filters |\r\n| **sysadmin.php** | System admin |\r\n| **test.php** | Testing endpoint |\r\n\r\n### 4.3 Backend API (C:\\code\\customerdb\\backend\\api\\)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **customer.php** | Get customer details |\r\n| **customers.php** | List/search customers |\r\n| **customer_save.php** | Save/update customer |\r\n| **visit.php** | Get visit details |\r\n| **visit_delete.php** | Delete visit |\r\n| **appointments.php** | Unified appointments |\r\n| **appointments_summary.php** | Appointment statistics |\r\n| **setmore_appointments.php** | Setmore appointments |\r\n| **setmore_import.php** | Import from Setmore |\r\n| **module_*.php** | Module loaders |\r\n| **alteration_items.php** | Alteration management |\r\n| **alteration_items_summary.php** | Alteration statistics |\r\n| **gold_silver_*.php** | Gold/silver prices |\r\n| **bank_*.php** | Bank transactions |\r\n| **stocks_summary.php** | Stock data |\r\n| **medication_*.php** | Medication tracking |\r\n| **payroll_import.php** | Payroll import |\r\n| **car_maintenance_*.php** | Vehicle maintenance |\r\n| **duke_*.php** | Duke integration |\r\n| **reports_*.php** | Report generation |\r\n| **totals_import.php** | Customer totals import |\r\n| **documents.php** | Document management |\r\n| **document_upload.php** | File uploads |\r\n| **cust_email_*.php** | Email data |\r\n| **mail_*.php** | Email sending |\r\n| **reminders_create.php** | Create reminders |\r\n| **nightly_reports.php** | Nightly reports |\r\n| **morning_jobs.php** | Morning jobs |\r\n| **appointment_workflow.php** | Workflow management |\r\n| **sysadmin.php** | System admin |\r\n| **reporting_module.php** | Reporting |\r\n| **job_runs.php** | Job tracking |\r\n| **health.php** | Health check |\r\n| **overview.php** | Dashboard data |\r\n\r\n---\r\n\r\n## 5. External Integrations\r\n\r\n### 5.1 Square (Payment Processing)\r\n- **Purpose:** Point-of-sale payments, receipts, terminal management\r\n- **Features:**\r\n - Square Terminal device pairing/management\r\n - Payment processing with tips\r\n - Customer payment logging\r\n - Sandbox and live environments\r\n- **Tables:** `backend_square_terminal_code`, `backend_square_terminal_device`, `square_customer_log`\r\n\r\n### 5.2 Twilio (SMS)\r\n- **Purpose:** SMS notifications and reminders\r\n- **Features:**\r\n - Send SMS to customers\r\n - Appointment reminders\r\n - SMS consent tracking\r\n- **Settings:** `twilio_sid`, `twilio_token`, `twilio_from`\r\n\r\n### 5.3 Setmore (Appointment Booking)\r\n- **Purpose:** Appointment synchronization\r\n- **Features:**\r\n - Pull appointments from Setmore API\r\n - Link to customers/visits\r\n- **Table:** `setmore_appointments`\r\n- **Settings:** `setmore_refresh_token`, `setmore_staff_key`\r\n\r\n### 5.4 EasyAppointments (Alternative Booking)\r\n- **Purpose:** Full appointment booking platform\r\n- **Location:** C:\\code\\customerdb\\schedule\\\r\n- **Features:**\r\n - Customer/provider management\r\n - Web-based booking widget\r\n - REST API\r\n\r\n### 5.5 Telegram (Notifications)\r\n- **Purpose:** Bot-based notifications\r\n- **Features:**\r\n - Send notifications to channels\r\n - Visit alerts\r\n - Cron job status\r\n- **Settings:** `telegram_bot_token`, `telegram_chat_id`\r\n\r\n### 5.6 Ollama (AI - Optional)\r\n- **Purpose:** Local AI for email generation\r\n- **Settings:** `ollama_url`, `ollama_default_model`\r\n\r\n---\r\n\r\n## 6. Scheduled Jobs / Cron\r\n\r\n### Configured Cron Jobs\r\n\r\n| Job Label | Schedule | Purpose |\r\n|----------|----------|---------|\r\n| **Next-Day Customer Emails** | `0 20 * * *` (8 PM) | Pickup reminder emails |\r\n| **Morning Import** | `0 6 * * *` (6 AM) | Morning data import |\r\n| **Morning Jobs** | `0 9 * * *` (9 AM) | Appointment sync, workflow |\r\n| **Nightly Reports** | `0 20 * * *` (8 PM) | Generate nightly summaries |\r\n| **Database Backup** | `0 2 * * *` (2 AM) | Full MariaDB backup |\r\n| **Daily Cleanup** | `0 4 * * *` (4 AM) | Temp files, old logs |\r\n| **Auto Delete** | `15 4 * * *` (4:15 AM) | Clean placeholder records |\r\n\r\n### Background Job Scripts (backend/jobs/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **run_morning_jobs.php** | Main morning job runner |\r\n| **smoke_morning_jobs.php** | Smoke test |\r\n| **send_nightly_reports.php** | Nightly report emailer |\r\n| **send_nextday_customer_emails.php** | Next-day reminders |\r\n| **delete_add_name_records.php** | Cleanup job |\r\n| **rebuild_customer_totals.php** | Rebuild totals |\r\n| **rebuild_daily_customer_totals.php** | Rebuild daily totals |\r\n| **rebuild_payment_ledger.php** | Rebuild payment ledger |\r\n| **import_customer_totals_csv.php** | CSV import |\r\n| **backfill_square_customer_log.php** | Square log backfill |\r\n| **run_cron_job.php** | Generic cron runner |\r\n\r\n---\r\n\r\n## 7. Web Applications\r\n\r\n### 7.1 Front Desk App (port 8895)\r\n\r\n| Page | Purpose |\r\n|------|---------|\r\n| **index.html** | Main dashboard, search, stats |\r\n| **customer.html** | Customer detail + visit history |\r\n| **visit.html** | Create/edit alteration order |\r\n| **customer_display.html** | Second monitor display |\r\n| **receipt.html** | Printable receipt |\r\n| **reports.html** | Order list by status |\r\n| **upcoming.html** | Next 7 days pickups |\r\n| **schedule.html** | Appointments calendar |\r\n| **login.html** | Login page |\r\n| **item_types.html** | Item type management |\r\n\r\n### 7.2 Management App (port 8894)\r\n\r\n| Page | Purpose |\r\n|------|---------|\r\n| **index.html** | Login page |\r\n| **webui.html** | Dashboard |\r\n| **customer.html** | Customer detail view |\r\n| **admin.html** | Reports browser |\r\n| **settings.html** | App settings editor |\r\n| **users.html** | User management |\r\n| **reports.html** | Order reports |\r\n\r\n---\r\n\r\n## 8. Settings & Configuration\r\n\r\n### app_settings Keys\r\n\r\n| Key | Purpose |\r\n|-----|---------|\r\n| theme_bg | Page background color |\r\n| theme_panel | Card/panel color |\r\n| theme_text | Text color |\r\n| theme_accent | Accent/button color |\r\n| theme_accent_deep | Darker accent |\r\n| theme_teal | Teal color |\r\n| theme_font | Font family |\r\n| smtp_host | Email server hostname |\r\n| smtp_port | Email server port |\r\n| smtp_secure | tls or ssl |\r\n| smtp_user | Email login |\r\n| smtp_pass | Email password |\r\n| smtp_from_email | From address |\r\n| smtp_from_name | From display name |\r\n| twilio_sid | Twilio Account SID |\r\n| twilio_token | Twilio Auth Token |\r\n| twilio_from | Twilio phone number |\r\n| imap_host | Incoming email server |\r\n| imap_port | IMAP port |\r\n| imap_ssl | SSL enable |\r\n| imap_user | IMAP login |\r\n| imap_pass | IMAP password |\r\n| square_token | Square API token |\r\n| square_location | Square location ID |\r\n| setmore_staff_key | Setmore API key |\r\n| setmore_refresh | Setmore refresh token |\r\n| require_report | PDF required |\r\n| require_signature | Signature required |\r\n| reports_base_path | Reports disk path |\r\n| reports_base_url | Reports URL |\r\n| ollama_url | Ollama AI URL |\r\n| ollama_default_model | Ollama model |\r\n| telegram_bot_token | Telegram bot token |\r\n| telegram_chat_id | Telegram chat ID |\r\n\r\n---\r\n\r\n## 9. Migration Notes\r\n\r\n### Access to MariaDB Workflow\r\n\r\n1. **Export source data from Access**\r\n - Export ordinary tables to CSV\r\n - Export attachment fields with VBA or DAO Recordset2 logic\r\n - Keep original IDs during migration\r\n\r\n2. **Load into MariaDB staging**\r\n - Use `LOAD DATA LOCAL INFILE` for CSV files\r\n - Load signatures separately into `visit_signatures`\r\n\r\n3. **Normalize**\r\n - `CustomerTable` → `customers`\r\n - `CustomerVisits` → `visits`\r\n - `AlterationItems` → `orders`, `order_items`, `order_item_type_links`\r\n - `2024TaxPayments` → `tax_payments`\r\n - `AlterationItemsChangeLog` → `change_log`\r\n\r\n4. **Signature BLOB path**\r\n - Export Access attachment to temp file\r\n - Convert BMP to PNG\r\n - Insert PNG bytes into `visit_signatures.signature_image`\r\n\r\n5. **Validation**\r\n - Compare source row counts vs target\r\n - Compare distinct legacy IDs\r\n - Compare sample signatures by SHA256\r\n\r\n---\r\n\r\n## 10. Scripts & Tools\r\n\r\n### PowerShell Scripts (scripts/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **import_legacy_totals_from_excel.ps1** | Import totals from Excel |\r\n| **sync_backend_mirror.ps1** | Sync backend mirror |\r\n| **check_backend_mirror.ps1** | Check mirror status |\r\n\r\n### Python Scripts (scripts/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **signature_blob_loader.py** | Load signature blobs |\r\n| **access_to_mariadb_parser.py** | Parse Access to MariaDB |\r\n| **import_access_v2.py** | Import Access v2 |\r\n| **import_csv_v2.py** | Import CSV v2 |\r\n\r\n### SQL Migrations (migrations/)\r\n\r\n| File | Purpose |\r\n|------|---------|\r\n| **20260407_add_conversion_job.sql** | Add conversion job table |\r\n| **20260411_order_items_item_type_name_no_fk.sql** | Order items no FK |\r\n| **20260416_blog_prompt_templates.sql** | Blog prompt templates |\r\n\r\n---\r\n\r\n## File Statistics\r\n\r\n- **Total PHP files:** 200+\r\n- **Total SQL files:** 100+\r\n- **Total HTML pages:** 20+\r\n- **Database tables:** 30+\r\n\r\n---\r\n\r\n*End of Documentation*","content_html":"<h1>Ella's Alterations - Comprehensive System Documentation</h1>\n<p><strong>Generated:</strong> April 17, 2026</p>\n<p><strong>Location:</strong> C:\\code\\customerdb</p>\n<p>---</p>\n<h2>Table of Contents</h2>\n<p>1. [System Overview](#1-system-overview)</p>\n<p>2. [Directory Structure](#2-directory-structure)</p>\n<p>3. [Database Schema](#3-database-schema)</p>\n<p>4. [API Endpoints](#4-api-endpoints)</p>\n<p>5. [External Integrations](#5-external-integrations)</p>\n<p>6. [Scheduled Jobs / Cron](#6-scheduled-jobs--cron)</p>\n<p>7. [Web Applications](#7-web-applications)</p>\n<p>8. [Settings & Configuration](#8-settings--configuration)</p>\n<p>9. [Migration Notes](#9-migration-notes)</p>\n<p>10. [Scripts & Tools](#10-scripts--tools)</p>\n<p>---</p>\n<h2>1. System Overview</h2>\n<h3>Purpose</h3>\n<p>Ella's Alterations is a comprehensive customer relationship management (CRM) andAlteration tracking system for a tailor shop. It manages customers, visits/alteration orders, appointments, payments, and provides integration with external services.</p>\n<h3>Server Information</h3>\n<ul>\n<li><strong>Primary Server:</strong> kefa@192.168.7.202</li>\n<li><strong>Mirror Drive:</strong> /mnt/drive2 (mirrors /mnt/drive1)</li>\n<li><strong>Database:</strong> MariaDB - database `ellas_alterations`, user `kefa_admin`</li>\n<li><strong>Web Server:</strong> Apache 2.4 with mod-php</li>\n<li><strong>PHP Version:</strong> 8.3</li>\n</ul>\n<h3>Three Web Applications</h3>\n<p>| App | Local URL | Public URL | Purpose |</p>\n<p>|-----|-----------|------------|---------|</p>\n<p>| Front Desk | http://192.168.7.202:8895 | https://ella.floridaalterations.com | Customer-facing operations |</p>\n<p>| Management | http://192.168.7.202:8894 | (LAN only) | Reports, admin, settings |</p>\n<p>| Scheduler | http://192.168.7.202:8897 | https://schedule.floridaalterations.com | Easy!Appointments booking |</p>\n<p>---</p>\n<h2>2. Directory Structure</h2>\n<p>```</p>\n<p>customerdb/</p>\n<p>├── backend/ # Main PHP backend API and services</p>\n<p>│ ├── api/ # REST API endpoints (~57 files)</p>\n<p>│ ├── bin/ # Binary/executable scripts</p>\n<p>│ ├── backup/ # Backup versions of services</p>\n<p>│ └── jobs/ # Background job scripts</p>\n<p>├── frontend/ # Front Desk app (port 8895)</p>\n<p>│ ├── *.html # HTML pages</p>\n<p>│ ├── api/ # PHP API endpoints</p>\n<p>│ └── reports/ # PDF reports (MMDDYYYY/filename.pdf)</p>\n<p>├── webui/ # Management app (port 8894)</p>\n<p>│ ├── *.html # HTML pages</p>\n<p>│ ├── api/ # PHP API endpoints</p>\n<p>│ ├── backend/ # Backend API copy</p>\n<p>│ └── email/ # Email templates</p>\n<p>├── schedule/ # Easy!Appointments installation</p>\n<p>├── schema/ # Database schema files</p>\n<p>├── migrations/ # SQL migrations</p>\n<p>├── scripts/ # PowerShell/Python import scripts</p>\n<p>├── docs/ # Documentation</p>\n<p>├── blog/ # Blog system and reengineering</p>\n<p>├── csv/ # CSV data files</p>\n<p>├── backups/ # Backup archives</p>\n<p>├── Topaz/ # Topaz signature pad configs</p>\n<p>├── webui/ # Second web interface</p>\n<p>│ ├── api/</p>\n<p>│ ├── backend/</p>\n<p>│ └── frontend/</p>\n<p>├── notused/ # Deprecated/unused files</p>\n<p>└── wordpress-plugin/ # WordPress integration</p>\n<p>```</p>\n<p>---</p>\n<h2>3. Database Schema</h2>\n<h3>Core Tables (from mariadb_core_schema.sql)</h3>\n<p>#### customers</p>\n<p>Main customer registry.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| customer_id | INT UNSIGNED PK | Auto-increment |</p>\n<p>| legacy_customer_id | INT NULL | Original Access DB ID |</p>\n<p>| created_at | DATETIME | Record creation time |</p>\n<p>| full_name | VARCHAR(200) | Customer full name |</p>\n<p>| phone | VARCHAR(30) | Formatted (XXX) XXX-XXXX |</p>\n<p>| email | VARCHAR(255) | Email address |</p>\n<p>| pickup_due_at | DATETIME | Next pickup date (synced from EA) |</p>\n<p>| description | TEXT | Description |</p>\n<p>| notes | TEXT | Internal notes |</p>\n<p>| is_new_customer | TINYINT(1) | 1 if flagged as new |</p>\n<p>#### visits</p>\n<p>One row per alteration job/order.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| visit_id | INT UNSIGNED PK | |</p>\n<p>| legacy_visit_id | INT NULL | Original Access DB ID |</p>\n<p>| customer_id | INT UNSIGNED FK | → customers |</p>\n<p>| visit_date | DATETIME | Drop-off date |</p>\n<p>| pickup_date | DATETIME | Expected pickup |</p>\n<p>| notes | TEXT | General visit notes |</p>\n<p>| customer_name_snapshot | VARCHAR(200) | |</p>\n<p>| phone_snapshot | VARCHAR(30) | |</p>\n<p>| email_snapshot | VARCHAR(255) | |</p>\n<p>| color | VARCHAR(100) | Garment color |</p>\n<p>| alterations_needed | TEXT | |</p>\n<p>| alterations_notes | TEXT | Detailed alteration instructions |</p>\n<p>| alterations_price | DECIMAL(10,2) | Manual price field |</p>\n<p>| total_alteration_price | DECIMAL(10,2) | Calculated from items |</p>\n<p>| expedited_fee | DECIMAL(10,2) | |</p>\n<p>| paid_date | DATETIME | When paid |</p>\n<p>| signature_text | TEXT | Text signature (legacy) |</p>\n<p>| signature_date | DATETIME | |</p>\n<p>| paid_flag | TINYINT(1) | 1 = paid |</p>\n<p>| source_key_value | VARCHAR(100) | |</p>\n<p>#### visit_signatures</p>\n<p>Full resolution signature images per visit.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| signature_id | BIGINT UNSIGNED PK | |</p>\n<p>| visit_id | INT UNIQUE | One signature per visit |</p>\n<p>| signature_date | DATETIME | |</p>\n<p>| image_format | VARCHAR(50) | Default 'image/png' |</p>\n<p>| original_filename | VARCHAR(255) | |</p>\n<p>| original_extension | VARCHAR(20) | |</p>\n<p>| file_size_bytes | BIGINT | |</p>\n<p>| sha256_hash | CHAR(64) | Hash for verification |</p>\n<p>| signature_image | LONGBLOB | Raw PNG binary |</p>\n<p>| created_at | DATETIME | |</p>\n<p>#### item_types</p>\n<p>Master list of alteration types (e.g., Hem, Zipper, Taper).</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| item_type_id | INT UNSIGNED PK | |</p>\n<p>| type_name | VARCHAR(100) UNIQUE | Display name |</p>\n<p>#### orders</p>\n<p>Orders per visit.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| order_id | BIGINT UNSIGNED PK | |</p>\n<p>| visit_id | INT UNSIGNED FK | → visits |</p>\n<p>| customer_id | INT UNSIGNED FK | → customers |</p>\n<p>| created_at | DATETIME | |</p>\n<p>| no_tax | TINYINT(1) | Tax exempt |</p>\n<p>| picked_up | TINYINT(1) | |</p>\n<p>| source_table | VARCHAR(50) | |</p>\n<p>#### order_items</p>\n<p>Line items for each alteration within a visit.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| order_item_id | BIGINT UNSIGNED PK | |</p>\n<p>| order_id | BIGINT UNSIGNED FK | → orders |</p>\n<p>| legacy_alteration_id | INT NULL | |</p>\n<p>| item_type_id | INT FK | → item_types |</p>\n<p>| item_type_name | VARCHAR(100) | |</p>\n<p>| description | TEXT | |</p>\n<p>| quantity | INT | Default 1 |</p>\n<p>| unit_price | DECIMAL(10,2) | |</p>\n<p>| total_price | DECIMAL(10,2) | |</p>\n<p>| notes | TEXT | Per-item notes |</p>\n<p>| color | VARCHAR(100) | Garment color |</p>\n<p>| no_tax | TINYINT(1) | Tax exempt |</p>\n<p>| is_range_price | TINYINT(1) | |</p>\n<p>| range_price_label | VARCHAR(100) | |</p>\n<p>| paid_flag | TINYINT(1) | |</p>\n<p>| paid_type | VARCHAR(100) | |</p>\n<p>| picked_up | TINYINT(1) | |</p>\n<p>| created_at | DATETIME | |</p>\n<p>#### order_item_type_links</p>\n<p>Many-to-many links between order items and item types.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| order_item_type_link_id | BIGINT UNSIGNED PK | |</p>\n<p>| order_item_id | BIGINT UNSIGNED FK | → order_items |</p>\n<p>| item_type_id | INT UNSIGNED FK | → item_types |</p>\n<p>| slot_number | TINYINT UNSIGNED | |</p>\n<p>#### payments</p>\n<p>Payment records per order.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| payment_id | BIGINT UNSIGNED PK | |</p>\n<p>| order_id | BIGINT UNSIGNED FK | → orders |</p>\n<p>| payment_date | DATETIME | |</p>\n<p>| payment_type | VARCHAR(100) | |</p>\n<p>| payment_method | VARCHAR(100) | |</p>\n<p>| amount | DECIMAL(10,2) | |</p>\n<p>| notes | TEXT | |</p>\n<p>#### tax_payments</p>\n<p>Tax payment records.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| tax_payment_id | INT UNSIGNED PK | |</p>\n<p>| legacy_tax_payment_id | INT NULL | |</p>\n<p>| payment_date | DATETIME | |</p>\n<p>| payment_type | VARCHAR(100) | |</p>\n<p>| payment_method | VARCHAR(100) | |</p>\n<p>| amount | DECIMAL(10,2) | |</p>\n<p>| notes | TEXT | |</p>\n<p>| receipt_blob | LONGBLOB | |</p>\n<p>#### change_log</p>\n<p>Audit log for changes.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| log_id | BIGINT UNSIGNED PK | |</p>\n<p>| legacy_log_id | INT NULL | |</p>\n<p>| table_name | VARCHAR(100) | |</p>\n<p>| record_id | BIGINT | |</p>\n<p>| visit_id | INT | |</p>\n<p>| field_name | VARCHAR(100) | |</p>\n<p>| old_value | TEXT | |</p>\n<p>| new_value | TEXT | |</p>\n<p>| changed_at | DATETIME | |</p>\n<p>| changed_by | VARCHAR(100) | |</p>\n<p>#### ads</p>\n<p>Advertising/articles.</p>\n<p>| Column | Type | Notes |</p>\n<p>|--------|------|-------|</p>\n<p>| ad_id | INT UNSIGNED PK | |</p>\n<p>| legacy_ad_id | INT NULL | |</p>\n<p>| article_text | TEXT | |</p>\n<p>| article_picture | LONGBLOB | |</p>\n<p>| site_submitted | VARCHAR(255) | |</p>\n<p>| views | INT | Default 0 |</p>\n<p>| date_posted | DATETIME | |</p>\n<p>#### staging_* tables</p>\n<p>Staging tables for data migration.</p>\n<p>| Table Name | Purpose |</p>\n<p>|------------|---------|</p>\n<p>| staging_customer_table_raw | Staging for legacy customer data |</p>\n<p>| staging_customer_visits_raw | Staging for legacy visits |</p>\n<p>| staging_alteration_items_raw | Staging for items |</p>\n<h3>Additional Backend Tables</h3>\n<p>| Table Name | Purpose |</p>\n<p>|------------|---------|</p>\n<p>| setmore_appointments | Synced from Setmore |</p>\n<p>| backend_conversion_job | Document conversion jobs |</p>\n<p>| backend_job_runs | Background job execution log |</p>\n<p>| customer_totals | Daily customer totals |</p>\n<p>| daily_customer_totals | Daily summary totals |</p>\n<p>| customer_payment_transactions | Payment ledger |</p>\n<p>| legacy_customer_totals_monthly | Monthly totals (legacy) |</p>\n<p>| legacy_customer_totals_irs_monthly | IRS monthly reports |</p>\n<p>| backend_appointment_workflow | Appointment state machine |</p>\n<p>| backend_no_show_log | No-show tracking |</p>\n<p>| backend_error_log | Error logging |</p>\n<p>| backend_change_log | Change audit log |</p>\n<p>| backend_document | Document storage |</p>\n<p>| backend_document_version | Document version history |</p>\n<p>| backend_speed_dial | Quick links |</p>\n<p>| backend_square_terminal_code | Square terminal pairing codes |</p>\n<p>| backend_square_terminal_device | Square device registry |</p>\n<p>| cron_job | Scheduled job definitions |</p>\n<p>| cron_job_run | Cron execution history |</p>\n<p>| app_settings | Global configuration |</p>\n<p>| app_users | Login accounts |</p>\n<p>| remember_tokens | "Remember Me" tokens |</p>\n<p>| customer_reports | PDF report index |</p>\n<p>---</p>\n<h2>4. API Endpoints</h2>\n<h3>4.1 Frontend API (port 8895 /api/)</h3>\n<p>| Endpoint File | Purpose |</p>\n<p>|---------------|---------|</p>\n<p>| <strong>customer_get.php</strong> | Get customer by ID |</p>\n<p>| <strong>customer_save.php</strong> | Create or update customer |</p>\n<p>| <strong>customer_detail.php</strong> | Customer + visits summary |</p>\n<p>| <strong>customer_delete.php</strong> | Delete customer |</p>\n<p>| <strong>customer_list_hide.php</strong> | Hide customer from list |</p>\n<p>| <strong>search_customers.php</strong> | Search by name/phone |</p>\n<p>| <strong>visit_get.php</strong> | Get visit + items |</p>\n<p>| <strong>visit_save.php</strong> | Create/update visit + items |</p>\n<p>| <strong>visit_delete.php</strong> | Delete visit |</p>\n<p>| <strong>visit_report_generate.php</strong> | Generate visit report |</p>\n<p>| <strong>item_types.php</strong> | List all alteration types |</p>\n<p>| <strong>item_type_add.php</strong> | Add new item type |</p>\n<p>| <strong>item_type_update.php</strong> | Update item type |</p>\n<p>| <strong>item_type_delete.php</strong> | Delete item type |</p>\n<p>| <strong>pickups_today.php</strong> | Orders due today |</p>\n<p>| <strong>ready.php</strong> | Orders ready for pickup |</p>\n<p>| <strong>overdue.php</strong> | Overdue orders |</p>\n<p>| <strong>upcoming.php</strong> | Orders due in next N days |</p>\n<p>| <strong>workload_7days.php</strong> | 7-day workload |</p>\n<p>| <strong>save_signature.php</strong> | Save canvas signature |</p>\n<p>| <strong>reports_list.php</strong> | Reports for customer |</p>\n<p>| <strong>attach_reports.php</strong> | Index all PDFs to DB |</p>\n<p>| <strong>save_signature.php</strong> | Save signature image |</p>\n<p>| <strong>today_appointments_list.php</strong> | Today's appointments |</p>\n<p>| <strong>settings_get.php</strong> | Get app settings |</p>\n<p>| <strong>settings_save.php</strong> | Save app settings |</p>\n<p>| <strong>settings_css.php</strong> | Live theme CSS |</p>\n<p>| <strong>send_email.php</strong> | Send email via SMTP |</p>\n<p>| <strong>send_sms.php</strong> | Send SMS via Twilio |</p>\n<p>| <strong>send_reminders.php</strong> | Send appointment reminders |</p>\n<p>| <strong>ea_appointments.php</strong> | EasyAppointments REST API |</p>\n<p>| <strong>ea_webhook.php</strong> | Receives EA webhook events |</p>\n<p>| <strong>setmore.php</strong> | Setmore API proxy |</p>\n<p>| <strong>setmore_to_ea_import.php</strong> | Import customers to EA |</p>\n<p>| <strong>square_config.php</strong> | Square public config |</p>\n<p>| <strong>square_process.php</strong> | Process Square payment |</p>\n<p>| <strong>square_customer_log.php</strong> | Customer Square log |</p>\n<p>| <strong>square_terminal.php</strong> | Square terminal |</p>\n<p>| <strong>login.php</strong> | Authenticate |</p>\n<p>| <strong>logout.php</strong> | Clear session |</p>\n<p>| <strong>session_check.php</strong> | Check if logged in |</p>\n<p>| <strong>auth.php</strong> | Session authentication |</p>\n<p>| <strong>config.php</strong> | App config constants |</p>\n<p>| <strong>db.php</strong> | Database connection |</p>\n<p>| <strong>log_error.php</strong> | Log client-side error |</p>\n<p>| <strong>todo_list.php</strong> | Todo list management |</p>\n<p>| <strong>send_telegram.php</strong> | Send Telegram messages |</p>\n<h3>4.2 Management API (port 8894 /api/)</h3>\n<p>| Endpoint File | Purpose |</p>\n<p>|---------------|---------|</p>\n<p>| <strong>login.php</strong> | Authenticate |</p>\n<p>| <strong>logout.php</strong> | Clear session |</p>\n<p>| <strong>session_check.php</strong> | Check if logged in |</p>\n<p>| <strong>customer_detail.php</strong> | Customer + visits |</p>\n<p>| <strong>search_customers.php</strong> | Search customers |</p>\n<p>| <strong>update_customer.php</strong> | Update customer |</p>\n<p>| <strong>users_list.php</strong> | List all users |</p>\n<p>| <strong>user_save.php</strong> | Create/update user |</p>\n<p>| <strong>list_report_dirs.php</strong> | Date folders |</p>\n<p>| <strong>list_report_files.php</strong> | PDFs in folder |</p>\n<p>| <strong>report_file.php</strong> | Access report file |</p>\n<p>| <strong>pickups_today.php</strong> | Today's pickups |</p>\n<p>| <strong>ready.php</strong> | Ready for pickup |</p>\n<p>| <strong>overdue.php</strong> | Overdue |</p>\n<p>| <strong>settings_get.php</strong> | Get settings |</p>\n<p>| <strong>settings_save.php</strong> | Save settings |</p>\n<p>| <strong>settings_css.php</strong> | Live CSS theme |</p>\n<p>| <strong>imap_list.php</strong> | List IMAP folders |</p>\n<p>| <strong>imap_fetch.php</strong> | Fetch emails |</p>\n<p>| <strong>imap_send.php</strong> | Send email via IMAP |</p>\n<p>| <strong>imap_action.php</strong> | IMAP actions |</p>\n<p>| <strong>sms_send_helper.php</strong> | SMS sending helper |</p>\n<p>| <strong>sms_webhook_receive.php</strong> | Receive SMS webhooks |</p>\n<p>| <strong>sms_inbox_api.php</strong> | SMS inbox API |</p>\n<p>| <strong>slideshow_list.php</strong> | Slideshow images |</p>\n<p>| <strong>slideshow_upload.php</strong> | Upload slideshow |</p>\n<p>| <strong>slideshow_delete.php</strong> | Delete slideshow |</p>\n<p>| <strong>users_list.php</strong> | User management |</p>\n<p>| <strong>report_list.php</strong> | Report list |</p>\n<p>| <strong>debug_filters.php</strong> | Debug filters |</p>\n<p>| <strong>sysadmin.php</strong> | System admin |</p>\n<p>| <strong>test.php</strong> | Testing endpoint |</p>\n<h3>4.3 Backend API (C:\\code\\customerdb\\backend\\api\\)</h3>\n<p>| Endpoint File | Purpose |</p>\n<p>|---------------|---------|</p>\n<p>| <strong>customer.php</strong> | Get customer details |</p>\n<p>| <strong>customers.php</strong> | List/search customers |</p>\n<p>| <strong>customer_save.php</strong> | Save/update customer |</p>\n<p>| <strong>visit.php</strong> | Get visit details |</p>\n<p>| <strong>visit_delete.php</strong> | Delete visit |</p>\n<p>| <strong>appointments.php</strong> | Unified appointments |</p>\n<p>| <strong>appointments_summary.php</strong> | Appointment statistics |</p>\n<p>| <strong>setmore_appointments.php</strong> | Setmore appointments |</p>\n<p>| <strong>setmore_import.php</strong> | Import from Setmore |</p>\n<p>| <strong>module_*.php</strong> | Module loaders |</p>\n<p>| <strong>alteration_items.php</strong> | Alteration management |</p>\n<p>| <strong>alteration_items_summary.php</strong> | Alteration statistics |</p>\n<p>| <strong>gold_silver_*.php</strong> | Gold/silver prices |</p>\n<p>| <strong>bank_*.php</strong> | Bank transactions |</p>\n<p>| <strong>stocks_summary.php</strong> | Stock data |</p>\n<p>| <strong>medication_*.php</strong> | Medication tracking |</p>\n<p>| <strong>payroll_import.php</strong> | Payroll import |</p>\n<p>| <strong>car_maintenance_*.php</strong> | Vehicle maintenance |</p>\n<p>| <strong>duke_*.php</strong> | Duke integration |</p>\n<p>| <strong>reports_*.php</strong> | Report generation |</p>\n<p>| <strong>totals_import.php</strong> | Customer totals import |</p>\n<p>| <strong>documents.php</strong> | Document management |</p>\n<p>| <strong>document_upload.php</strong> | File uploads |</p>\n<p>| <strong>cust_email_*.php</strong> | Email data |</p>\n<p>| <strong>mail_*.php</strong> | Email sending |</p>\n<p>| <strong>reminders_create.php</strong> | Create reminders |</p>\n<p>| <strong>nightly_reports.php</strong> | Nightly reports |</p>\n<p>| <strong>morning_jobs.php</strong> | Morning jobs |</p>\n<p>| <strong>appointment_workflow.php</strong> | Workflow management |</p>\n<p>| <strong>sysadmin.php</strong> | System admin |</p>\n<p>| <strong>reporting_module.php</strong> | Reporting |</p>\n<p>| <strong>job_runs.php</strong> | Job tracking |</p>\n<p>| <strong>health.php</strong> | Health check |</p>\n<p>| <strong>overview.php</strong> | Dashboard data |</p>\n<p>---</p>\n<h2>5. External Integrations</h2>\n<h3>5.1 Square (Payment Processing)</h3>\n<ul>\n<li><strong>Purpose:</strong> Point-of-sale payments, receipts, terminal management</li>\n<li><strong>Features:</strong></li>\n<li>Square Terminal device pairing/management</li>\n<li>Payment processing with tips</li>\n<li>Customer payment logging</li>\n<li>Sandbox and live environments</li>\n<li><strong>Tables:</strong> `backend_square_terminal_code`, `backend_square_terminal_device`, `square_customer_log`</li>\n</ul>\n<h3>5.2 Twilio (SMS)</h3>\n<ul>\n<li><strong>Purpose:</strong> SMS notifications and reminders</li>\n<li><strong>Features:</strong></li>\n<li>Send SMS to customers</li>\n<li>Appointment reminders</li>\n<li>SMS consent tracking</li>\n<li><strong>Settings:</strong> `twilio_sid`, `twilio_token`, `twilio_from`</li>\n</ul>\n<h3>5.3 Setmore (Appointment Booking)</h3>\n<ul>\n<li><strong>Purpose:</strong> Appointment synchronization</li>\n<li><strong>Features:</strong></li>\n<li>Pull appointments from Setmore API</li>\n<li>Link to customers/visits</li>\n<li><strong>Table:</strong> `setmore_appointments`</li>\n<li><strong>Settings:</strong> `setmore_refresh_token`, `setmore_staff_key`</li>\n</ul>\n<h3>5.4 EasyAppointments (Alternative Booking)</h3>\n<ul>\n<li><strong>Purpose:</strong> Full appointment booking platform</li>\n<li><strong>Location:</strong> C:\\code\\customerdb\\schedule\\</li>\n<li><strong>Features:</strong></li>\n<li>Customer/provider management</li>\n<li>Web-based booking widget</li>\n<li>REST API</li>\n</ul>\n<h3>5.5 Telegram (Notifications)</h3>\n<ul>\n<li><strong>Purpose:</strong> Bot-based notifications</li>\n<li><strong>Features:</strong></li>\n<li>Send notifications to channels</li>\n<li>Visit alerts</li>\n<li>Cron job status</li>\n<li><strong>Settings:</strong> `telegram_bot_token`, `telegram_chat_id`</li>\n</ul>\n<h3>5.6 Ollama (AI - Optional)</h3>\n<ul>\n<li><strong>Purpose:</strong> Local AI for email generation</li>\n<li><strong>Settings:</strong> `ollama_url`, `ollama_default_model`</li>\n</ul>\n<p>---</p>\n<h2>6. Scheduled Jobs / Cron</h2>\n<h3>Configured Cron Jobs</h3>\n<p>| Job Label | Schedule | Purpose |</p>\n<p>|----------|----------|---------|</p>\n<p>| <strong>Next-Day Customer Emails</strong> | `0 20 <em> </em> *` (8 PM) | Pickup reminder emails |</p>\n<p>| <strong>Morning Import</strong> | `0 6 <em> </em> *` (6 AM) | Morning data import |</p>\n<p>| <strong>Morning Jobs</strong> | `0 9 <em> </em> *` (9 AM) | Appointment sync, workflow |</p>\n<p>| <strong>Nightly Reports</strong> | `0 20 <em> </em> *` (8 PM) | Generate nightly summaries |</p>\n<p>| <strong>Database Backup</strong> | `0 2 <em> </em> *` (2 AM) | Full MariaDB backup |</p>\n<p>| <strong>Daily Cleanup</strong> | `0 4 <em> </em> *` (4 AM) | Temp files, old logs |</p>\n<p>| <strong>Auto Delete</strong> | `15 4 <em> </em> *` (4:15 AM) | Clean placeholder records |</p>\n<h3>Background Job Scripts (backend/jobs/)</h3>\n<p>| Script | Purpose |</p>\n<p>|--------|---------|</p>\n<p>| <strong>run_morning_jobs.php</strong> | Main morning job runner |</p>\n<p>| <strong>smoke_morning_jobs.php</strong> | Smoke test |</p>\n<p>| <strong>send_nightly_reports.php</strong> | Nightly report emailer |</p>\n<p>| <strong>send_nextday_customer_emails.php</strong> | Next-day reminders |</p>\n<p>| <strong>delete_add_name_records.php</strong> | Cleanup job |</p>\n<p>| <strong>rebuild_customer_totals.php</strong> | Rebuild totals |</p>\n<p>| <strong>rebuild_daily_customer_totals.php</strong> | Rebuild daily totals |</p>\n<p>| <strong>rebuild_payment_ledger.php</strong> | Rebuild payment ledger |</p>\n<p>| <strong>import_customer_totals_csv.php</strong> | CSV import |</p>\n<p>| <strong>backfill_square_customer_log.php</strong> | Square log backfill |</p>\n<p>| <strong>run_cron_job.php</strong> | Generic cron runner |</p>\n<p>---</p>\n<h2>7. Web Applications</h2>\n<h3>7.1 Front Desk App (port 8895)</h3>\n<p>| Page | Purpose |</p>\n<p>|------|---------|</p>\n<p>| <strong>index.html</strong> | Main dashboard, search, stats |</p>\n<p>| <strong>customer.html</strong> | Customer detail + visit history |</p>\n<p>| <strong>visit.html</strong> | Create/edit alteration order |</p>\n<p>| <strong>customer_display.html</strong> | Second monitor display |</p>\n<p>| <strong>receipt.html</strong> | Printable receipt |</p>\n<p>| <strong>reports.html</strong> | Order list by status |</p>\n<p>| <strong>upcoming.html</strong> | Next 7 days pickups |</p>\n<p>| <strong>schedule.html</strong> | Appointments calendar |</p>\n<p>| <strong>login.html</strong> | Login page |</p>\n<p>| <strong>item_types.html</strong> | Item type management |</p>\n<h3>7.2 Management App (port 8894)</h3>\n<p>| Page | Purpose |</p>\n<p>|------|---------|</p>\n<p>| <strong>index.html</strong> | Login page |</p>\n<p>| <strong>webui.html</strong> | Dashboard |</p>\n<p>| <strong>customer.html</strong> | Customer detail view |</p>\n<p>| <strong>admin.html</strong> | Reports browser |</p>\n<p>| <strong>settings.html</strong> | App settings editor |</p>\n<p>| <strong>users.html</strong> | User management |</p>\n<p>| <strong>reports.html</strong> | Order reports |</p>\n<p>---</p>\n<h2>8. Settings & Configuration</h2>\n<h3>app_settings Keys</h3>\n<p>| Key | Purpose |</p>\n<p>|-----|---------|</p>\n<p>| theme_bg | Page background color |</p>\n<p>| theme_panel | Card/panel color |</p>\n<p>| theme_text | Text color |</p>\n<p>| theme_accent | Accent/button color |</p>\n<p>| theme_accent_deep | Darker accent |</p>\n<p>| theme_teal | Teal color |</p>\n<p>| theme_font | Font family |</p>\n<p>| smtp_host | Email server hostname |</p>\n<p>| smtp_port | Email server port |</p>\n<p>| smtp_secure | tls or ssl |</p>\n<p>| smtp_user | Email login |</p>\n<p>| smtp_pass | Email password |</p>\n<p>| smtp_from_email | From address |</p>\n<p>| smtp_from_name | From display name |</p>\n<p>| twilio_sid | Twilio Account SID |</p>\n<p>| twilio_token | Twilio Auth Token |</p>\n<p>| twilio_from | Twilio phone number |</p>\n<p>| imap_host | Incoming email server |</p>\n<p>| imap_port | IMAP port |</p>\n<p>| imap_ssl | SSL enable |</p>\n<p>| imap_user | IMAP login |</p>\n<p>| imap_pass | IMAP password |</p>\n<p>| square_token | Square API token |</p>\n<p>| square_location | Square location ID |</p>\n<p>| setmore_staff_key | Setmore API key |</p>\n<p>| setmore_refresh | Setmore refresh token |</p>\n<p>| require_report | PDF required |</p>\n<p>| require_signature | Signature required |</p>\n<p>| reports_base_path | Reports disk path |</p>\n<p>| reports_base_url | Reports URL |</p>\n<p>| ollama_url | Ollama AI URL |</p>\n<p>| ollama_default_model | Ollama model |</p>\n<p>| telegram_bot_token | Telegram bot token |</p>\n<p>| telegram_chat_id | Telegram chat ID |</p>\n<p>---</p>\n<h2>9. Migration Notes</h2>\n<h3>Access to MariaDB Workflow</h3>\n<p>1. <strong>Export source data from Access</strong></p>\n<ul>\n<li>Export ordinary tables to CSV</li>\n<li>Export attachment fields with VBA or DAO Recordset2 logic</li>\n<li>Keep original IDs during migration</li>\n</ul>\n<p>2. <strong>Load into MariaDB staging</strong></p>\n<ul>\n<li>Use `LOAD DATA LOCAL INFILE` for CSV files</li>\n<li>Load signatures separately into `visit_signatures`</li>\n</ul>\n<p>3. <strong>Normalize</strong></p>\n<ul>\n<li>`CustomerTable` → `customers`</li>\n<li>`CustomerVisits` → `visits`</li>\n<li>`AlterationItems` → `orders`, `order_items`, `order_item_type_links`</li>\n<li>`2024TaxPayments` → `tax_payments`</li>\n<li>`AlterationItemsChangeLog` → `change_log`</li>\n</ul>\n<p>4. <strong>Signature BLOB path</strong></p>\n<ul>\n<li>Export Access attachment to temp file</li>\n<li>Convert BMP to PNG</li>\n<li>Insert PNG bytes into `visit_signatures.signature_image`</li>\n</ul>\n<p>5. <strong>Validation</strong></p>\n<ul>\n<li>Compare source row counts vs target</li>\n<li>Compare distinct legacy IDs</li>\n<li>Compare sample signatures by SHA256</li>\n</ul>\n<p>---</p>\n<h2>10. Scripts & Tools</h2>\n<h3>PowerShell Scripts (scripts/)</h3>\n<p>| Script | Purpose |</p>\n<p>|--------|---------|</p>\n<p>| <strong>import_legacy_totals_from_excel.ps1</strong> | Import totals from Excel |</p>\n<p>| <strong>sync_backend_mirror.ps1</strong> | Sync backend mirror |</p>\n<p>| <strong>check_backend_mirror.ps1</strong> | Check mirror status |</p>\n<h3>Python Scripts (scripts/)</h3>\n<p>| Script | Purpose |</p>\n<p>|--------|---------|</p>\n<p>| <strong>signature_blob_loader.py</strong> | Load signature blobs |</p>\n<p>| <strong>access_to_mariadb_parser.py</strong> | Parse Access to MariaDB |</p>\n<p>| <strong>import_access_v2.py</strong> | Import Access v2 |</p>\n<p>| <strong>import_csv_v2.py</strong> | Import CSV v2 |</p>\n<h3>SQL Migrations (migrations/)</h3>\n<p>| File | Purpose |</p>\n<p>|------|---------|</p>\n<p>| <strong>20260407_add_conversion_job.sql</strong> | Add conversion job table |</p>\n<p>| <strong>20260411_order_items_item_type_name_no_fk.sql</strong> | Order items no FK |</p>\n<p>| <strong>20260416_blog_prompt_templates.sql</strong> | Blog prompt templates |</p>\n<p>---</p>\n<h2>File Statistics</h2>\n<ul>\n<li><strong>Total PHP files:</strong> 200+</li>\n<li><strong>Total SQL files:</strong> 100+</li>\n<li><strong>Total HTML pages:</strong> 20+</li>\n<li><strong>Database tables:</strong> 30+</li>\n</ul>\n<p>---</p>\n<p><em>End of Documentation</em></p>","file_name":"COMPREHENSIVE_DOCUMENTATION.md","stored_name":"20260417-160828-6006d4f1.md","mime_type":"application/octet-stream","file_size_bytes":"22313","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260417-160828-6006d4f1.md","is_deleted":"0","created_at":"2026-04-17 12:08:29","updated_at":"2026-04-17 12:08:52","editor_content":"# Ella's Alterations - Comprehensive System Documentation\r\n\r\n**Generated:** April 17, 2026\r\n**Location:** C:\\code\\customerdb\r\n\r\n---\r\n\r\n## Table of Contents\r\n\r\n1. [System Overview](#1-system-overview)\r\n2. [Directory Structure](#2-directory-structure)\r\n3. [Database Schema](#3-database-schema)\r\n4. [API Endpoints](#4-api-endpoints)\r\n5. [External Integrations](#5-external-integrations)\r\n6. [Scheduled Jobs / Cron](#6-scheduled-jobs--cron)\r\n7. [Web Applications](#7-web-applications)\r\n8. [Settings & Configuration](#8-settings--configuration)\r\n9. [Migration Notes](#9-migration-notes)\r\n10. [Scripts & Tools](#10-scripts--tools)\r\n\r\n---\r\n\r\n## 1. System Overview\r\n\r\n### Purpose\r\nElla's Alterations is a comprehensive customer relationship management (CRM) andAlteration tracking system for a tailor shop. It manages customers, visits/alteration orders, appointments, payments, and provides integration with external services.\r\n\r\n### Server Information\r\n- **Primary Server:** kefa@192.168.7.202\r\n- **Mirror Drive:** /mnt/drive2 (mirrors /mnt/drive1)\r\n- **Database:** MariaDB - database `ellas_alterations`, user `kefa_admin`\r\n- **Web Server:** Apache 2.4 with mod-php\r\n- **PHP Version:** 8.3\r\n\r\n### Three Web Applications\r\n\r\n| App | Local URL | Public URL | Purpose |\r\n|-----|-----------|------------|---------|\r\n| Front Desk | http://192.168.7.202:8895 | https://ella.floridaalterations.com | Customer-facing operations |\r\n| Management | http://192.168.7.202:8894 | (LAN only) | Reports, admin, settings |\r\n| Scheduler | http://192.168.7.202:8897 | https://schedule.floridaalterations.com | Easy!Appointments booking |\r\n\r\n---\r\n\r\n## 2. Directory Structure\r\n\r\n```\r\ncustomerdb/\r\n├── backend/ # Main PHP backend API and services\r\n│ ├── api/ # REST API endpoints (~57 files)\r\n│ ├── bin/ # Binary/executable scripts\r\n│ ├── backup/ # Backup versions of services\r\n│ └── jobs/ # Background job scripts\r\n├── frontend/ # Front Desk app (port 8895)\r\n│ ├── *.html # HTML pages\r\n│ ├── api/ # PHP API endpoints\r\n│ └── reports/ # PDF reports (MMDDYYYY/filename.pdf)\r\n├── webui/ # Management app (port 8894)\r\n│ ├── *.html # HTML pages\r\n│ ├── api/ # PHP API endpoints\r\n│ ├── backend/ # Backend API copy\r\n│ └── email/ # Email templates\r\n├── schedule/ # Easy!Appointments installation\r\n├── schema/ # Database schema files\r\n├── migrations/ # SQL migrations\r\n├── scripts/ # PowerShell/Python import scripts\r\n├── docs/ # Documentation\r\n├── blog/ # Blog system and reengineering\r\n├── csv/ # CSV data files\r\n├── backups/ # Backup archives\r\n├── Topaz/ # Topaz signature pad configs\r\n├── webui/ # Second web interface\r\n│ ├── api/\r\n│ ├── backend/\r\n│ └── frontend/\r\n├── notused/ # Deprecated/unused files\r\n└── wordpress-plugin/ # WordPress integration\r\n```\r\n\r\n---\r\n\r\n## 3. Database Schema\r\n\r\n### Core Tables (from mariadb_core_schema.sql)\r\n\r\n#### customers\r\nMain customer registry.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| customer_id | INT UNSIGNED PK | Auto-increment |\r\n| legacy_customer_id | INT NULL | Original Access DB ID |\r\n| created_at | DATETIME | Record creation time |\r\n| full_name | VARCHAR(200) | Customer full name |\r\n| phone | VARCHAR(30) | Formatted (XXX) XXX-XXXX |\r\n| email | VARCHAR(255) | Email address |\r\n| pickup_due_at | DATETIME | Next pickup date (synced from EA) |\r\n| description | TEXT | Description |\r\n| notes | TEXT | Internal notes |\r\n| is_new_customer | TINYINT(1) | 1 if flagged as new |\r\n\r\n#### visits\r\nOne row per alteration job/order.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| visit_id | INT UNSIGNED PK | |\r\n| legacy_visit_id | INT NULL | Original Access DB ID |\r\n| customer_id | INT UNSIGNED FK | → customers |\r\n| visit_date | DATETIME | Drop-off date |\r\n| pickup_date | DATETIME | Expected pickup |\r\n| notes | TEXT | General visit notes |\r\n| customer_name_snapshot | VARCHAR(200) | |\r\n| phone_snapshot | VARCHAR(30) | |\r\n| email_snapshot | VARCHAR(255) | |\r\n| color | VARCHAR(100) | Garment color |\r\n| alterations_needed | TEXT | |\r\n| alterations_notes | TEXT | Detailed alteration instructions |\r\n| alterations_price | DECIMAL(10,2) | Manual price field |\r\n| total_alteration_price | DECIMAL(10,2) | Calculated from items |\r\n| expedited_fee | DECIMAL(10,2) | |\r\n| paid_date | DATETIME | When paid |\r\n| signature_text | TEXT | Text signature (legacy) |\r\n| signature_date | DATETIME | |\r\n| paid_flag | TINYINT(1) | 1 = paid |\r\n| source_key_value | VARCHAR(100) | |\r\n\r\n#### visit_signatures\r\nFull resolution signature images per visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| signature_id | BIGINT UNSIGNED PK | |\r\n| visit_id | INT UNIQUE | One signature per visit |\r\n| signature_date | DATETIME | |\r\n| image_format | VARCHAR(50) | Default 'image/png' |\r\n| original_filename | VARCHAR(255) | |\r\n| original_extension | VARCHAR(20) | |\r\n| file_size_bytes | BIGINT | |\r\n| sha256_hash | CHAR(64) | Hash for verification |\r\n| signature_image | LONGBLOB | Raw PNG binary |\r\n| created_at | DATETIME | |\r\n\r\n#### item_types\r\nMaster list of alteration types (e.g., Hem, Zipper, Taper).\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| item_type_id | INT UNSIGNED PK | |\r\n| type_name | VARCHAR(100) UNIQUE | Display name |\r\n\r\n#### orders\r\nOrders per visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_id | BIGINT UNSIGNED PK | |\r\n| visit_id | INT UNSIGNED FK | → visits |\r\n| customer_id | INT UNSIGNED FK | → customers |\r\n| created_at | DATETIME | |\r\n| no_tax | TINYINT(1) | Tax exempt |\r\n| picked_up | TINYINT(1) | |\r\n| source_table | VARCHAR(50) | |\r\n\r\n#### order_items\r\nLine items for each alteration within a visit.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_item_id | BIGINT UNSIGNED PK | |\r\n| order_id | BIGINT UNSIGNED FK | → orders |\r\n| legacy_alteration_id | INT NULL | |\r\n| item_type_id | INT FK | → item_types |\r\n| item_type_name | VARCHAR(100) | |\r\n| description | TEXT | |\r\n| quantity | INT | Default 1 |\r\n| unit_price | DECIMAL(10,2) | |\r\n| total_price | DECIMAL(10,2) | |\r\n| notes | TEXT | Per-item notes |\r\n| color | VARCHAR(100) | Garment color |\r\n| no_tax | TINYINT(1) | Tax exempt |\r\n| is_range_price | TINYINT(1) | |\r\n| range_price_label | VARCHAR(100) | |\r\n| paid_flag | TINYINT(1) | |\r\n| paid_type | VARCHAR(100) | |\r\n| picked_up | TINYINT(1) | |\r\n| created_at | DATETIME | |\r\n\r\n#### order_item_type_links\r\nMany-to-many links between order items and item types.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| order_item_type_link_id | BIGINT UNSIGNED PK | |\r\n| order_item_id | BIGINT UNSIGNED FK | → order_items |\r\n| item_type_id | INT UNSIGNED FK | → item_types |\r\n| slot_number | TINYINT UNSIGNED | |\r\n\r\n#### payments\r\nPayment records per order.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| payment_id | BIGINT UNSIGNED PK | |\r\n| order_id | BIGINT UNSIGNED FK | → orders |\r\n| payment_date | DATETIME | |\r\n| payment_type | VARCHAR(100) | |\r\n| payment_method | VARCHAR(100) | |\r\n| amount | DECIMAL(10,2) | |\r\n| notes | TEXT | |\r\n\r\n#### tax_payments\r\nTax payment records.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| tax_payment_id | INT UNSIGNED PK | |\r\n| legacy_tax_payment_id | INT NULL | |\r\n| payment_date | DATETIME | |\r\n| payment_type | VARCHAR(100) | |\r\n| payment_method | VARCHAR(100) | |\r\n| amount | DECIMAL(10,2) | |\r\n| notes | TEXT | |\r\n| receipt_blob | LONGBLOB | |\r\n\r\n#### change_log\r\nAudit log for changes.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| log_id | BIGINT UNSIGNED PK | |\r\n| legacy_log_id | INT NULL | |\r\n| table_name | VARCHAR(100) | |\r\n| record_id | BIGINT | |\r\n| visit_id | INT | |\r\n| field_name | VARCHAR(100) | |\r\n| old_value | TEXT | |\r\n| new_value | TEXT | |\r\n| changed_at | DATETIME | |\r\n| changed_by | VARCHAR(100) | |\r\n\r\n#### ads\r\nAdvertising/articles.\r\n\r\n| Column | Type | Notes |\r\n|--------|------|-------|\r\n| ad_id | INT UNSIGNED PK | |\r\n| legacy_ad_id | INT NULL | |\r\n| article_text | TEXT | |\r\n| article_picture | LONGBLOB | |\r\n| site_submitted | VARCHAR(255) | |\r\n| views | INT | Default 0 |\r\n| date_posted | DATETIME | |\r\n\r\n#### staging_* tables\r\nStaging tables for data migration.\r\n\r\n| Table Name | Purpose |\r\n|------------|---------|\r\n| staging_customer_table_raw | Staging for legacy customer data |\r\n| staging_customer_visits_raw | Staging for legacy visits |\r\n| staging_alteration_items_raw | Staging for items |\r\n\r\n### Additional Backend Tables\r\n\r\n| Table Name | Purpose |\r\n|------------|---------|\r\n| setmore_appointments | Synced from Setmore |\r\n| backend_conversion_job | Document conversion jobs |\r\n| backend_job_runs | Background job execution log |\r\n| customer_totals | Daily customer totals |\r\n| daily_customer_totals | Daily summary totals |\r\n| customer_payment_transactions | Payment ledger |\r\n| legacy_customer_totals_monthly | Monthly totals (legacy) |\r\n| legacy_customer_totals_irs_monthly | IRS monthly reports |\r\n| backend_appointment_workflow | Appointment state machine |\r\n| backend_no_show_log | No-show tracking |\r\n| backend_error_log | Error logging |\r\n| backend_change_log | Change audit log |\r\n| backend_document | Document storage |\r\n| backend_document_version | Document version history |\r\n| backend_speed_dial | Quick links |\r\n| backend_square_terminal_code | Square terminal pairing codes |\r\n| backend_square_terminal_device | Square device registry |\r\n| cron_job | Scheduled job definitions |\r\n| cron_job_run | Cron execution history |\r\n| app_settings | Global configuration |\r\n| app_users | Login accounts |\r\n| remember_tokens | \"Remember Me\" tokens |\r\n| customer_reports | PDF report index |\r\n\r\n---\r\n\r\n## 4. API Endpoints\r\n\r\n### 4.1 Frontend API (port 8895 /api/)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **customer_get.php** | Get customer by ID |\r\n| **customer_save.php** | Create or update customer |\r\n| **customer_detail.php** | Customer + visits summary |\r\n| **customer_delete.php** | Delete customer |\r\n| **customer_list_hide.php** | Hide customer from list |\r\n| **search_customers.php** | Search by name/phone |\r\n| **visit_get.php** | Get visit + items |\r\n| **visit_save.php** | Create/update visit + items |\r\n| **visit_delete.php** | Delete visit |\r\n| **visit_report_generate.php** | Generate visit report |\r\n| **item_types.php** | List all alteration types |\r\n| **item_type_add.php** | Add new item type |\r\n| **item_type_update.php** | Update item type |\r\n| **item_type_delete.php** | Delete item type |\r\n| **pickups_today.php** | Orders due today |\r\n| **ready.php** | Orders ready for pickup |\r\n| **overdue.php** | Overdue orders |\r\n| **upcoming.php** | Orders due in next N days |\r\n| **workload_7days.php** | 7-day workload |\r\n| **save_signature.php** | Save canvas signature |\r\n| **reports_list.php** | Reports for customer |\r\n| **attach_reports.php** | Index all PDFs to DB |\r\n| **save_signature.php** | Save signature image |\r\n| **today_appointments_list.php** | Today's appointments |\r\n| **settings_get.php** | Get app settings |\r\n| **settings_save.php** | Save app settings |\r\n| **settings_css.php** | Live theme CSS |\r\n| **send_email.php** | Send email via SMTP |\r\n| **send_sms.php** | Send SMS via Twilio |\r\n| **send_reminders.php** | Send appointment reminders |\r\n| **ea_appointments.php** | EasyAppointments REST API |\r\n| **ea_webhook.php** | Receives EA webhook events |\r\n| **setmore.php** | Setmore API proxy |\r\n| **setmore_to_ea_import.php** | Import customers to EA |\r\n| **square_config.php** | Square public config |\r\n| **square_process.php** | Process Square payment |\r\n| **square_customer_log.php** | Customer Square log |\r\n| **square_terminal.php** | Square terminal |\r\n| **login.php** | Authenticate |\r\n| **logout.php** | Clear session |\r\n| **session_check.php** | Check if logged in |\r\n| **auth.php** | Session authentication |\r\n| **config.php** | App config constants |\r\n| **db.php** | Database connection |\r\n| **log_error.php** | Log client-side error |\r\n| **todo_list.php** | Todo list management |\r\n| **send_telegram.php** | Send Telegram messages |\r\n\r\n### 4.2 Management API (port 8894 /api/)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **login.php** | Authenticate |\r\n| **logout.php** | Clear session |\r\n| **session_check.php** | Check if logged in |\r\n| **customer_detail.php** | Customer + visits |\r\n| **search_customers.php** | Search customers |\r\n| **update_customer.php** | Update customer |\r\n| **users_list.php** | List all users |\r\n| **user_save.php** | Create/update user |\r\n| **list_report_dirs.php** | Date folders |\r\n| **list_report_files.php** | PDFs in folder |\r\n| **report_file.php** | Access report file |\r\n| **pickups_today.php** | Today's pickups |\r\n| **ready.php** | Ready for pickup |\r\n| **overdue.php** | Overdue |\r\n| **settings_get.php** | Get settings |\r\n| **settings_save.php** | Save settings |\r\n| **settings_css.php** | Live CSS theme |\r\n| **imap_list.php** | List IMAP folders |\r\n| **imap_fetch.php** | Fetch emails |\r\n| **imap_send.php** | Send email via IMAP |\r\n| **imap_action.php** | IMAP actions |\r\n| **sms_send_helper.php** | SMS sending helper |\r\n| **sms_webhook_receive.php** | Receive SMS webhooks |\r\n| **sms_inbox_api.php** | SMS inbox API |\r\n| **slideshow_list.php** | Slideshow images |\r\n| **slideshow_upload.php** | Upload slideshow |\r\n| **slideshow_delete.php** | Delete slideshow |\r\n| **users_list.php** | User management |\r\n| **report_list.php** | Report list |\r\n| **debug_filters.php** | Debug filters |\r\n| **sysadmin.php** | System admin |\r\n| **test.php** | Testing endpoint |\r\n\r\n### 4.3 Backend API (C:\\code\\customerdb\\backend\\api\\)\r\n\r\n| Endpoint File | Purpose |\r\n|---------------|---------|\r\n| **customer.php** | Get customer details |\r\n| **customers.php** | List/search customers |\r\n| **customer_save.php** | Save/update customer |\r\n| **visit.php** | Get visit details |\r\n| **visit_delete.php** | Delete visit |\r\n| **appointments.php** | Unified appointments |\r\n| **appointments_summary.php** | Appointment statistics |\r\n| **setmore_appointments.php** | Setmore appointments |\r\n| **setmore_import.php** | Import from Setmore |\r\n| **module_*.php** | Module loaders |\r\n| **alteration_items.php** | Alteration management |\r\n| **alteration_items_summary.php** | Alteration statistics |\r\n| **gold_silver_*.php** | Gold/silver prices |\r\n| **bank_*.php** | Bank transactions |\r\n| **stocks_summary.php** | Stock data |\r\n| **medication_*.php** | Medication tracking |\r\n| **payroll_import.php** | Payroll import |\r\n| **car_maintenance_*.php** | Vehicle maintenance |\r\n| **duke_*.php** | Duke integration |\r\n| **reports_*.php** | Report generation |\r\n| **totals_import.php** | Customer totals import |\r\n| **documents.php** | Document management |\r\n| **document_upload.php** | File uploads |\r\n| **cust_email_*.php** | Email data |\r\n| **mail_*.php** | Email sending |\r\n| **reminders_create.php** | Create reminders |\r\n| **nightly_reports.php** | Nightly reports |\r\n| **morning_jobs.php** | Morning jobs |\r\n| **appointment_workflow.php** | Workflow management |\r\n| **sysadmin.php** | System admin |\r\n| **reporting_module.php** | Reporting |\r\n| **job_runs.php** | Job tracking |\r\n| **health.php** | Health check |\r\n| **overview.php** | Dashboard data |\r\n\r\n---\r\n\r\n## 5. External Integrations\r\n\r\n### 5.1 Square (Payment Processing)\r\n- **Purpose:** Point-of-sale payments, receipts, terminal management\r\n- **Features:**\r\n - Square Terminal device pairing/management\r\n - Payment processing with tips\r\n - Customer payment logging\r\n - Sandbox and live environments\r\n- **Tables:** `backend_square_terminal_code`, `backend_square_terminal_device`, `square_customer_log`\r\n\r\n### 5.2 Twilio (SMS)\r\n- **Purpose:** SMS notifications and reminders\r\n- **Features:**\r\n - Send SMS to customers\r\n - Appointment reminders\r\n - SMS consent tracking\r\n- **Settings:** `twilio_sid`, `twilio_token`, `twilio_from`\r\n\r\n### 5.3 Setmore (Appointment Booking)\r\n- **Purpose:** Appointment synchronization\r\n- **Features:**\r\n - Pull appointments from Setmore API\r\n - Link to customers/visits\r\n- **Table:** `setmore_appointments`\r\n- **Settings:** `setmore_refresh_token`, `setmore_staff_key`\r\n\r\n### 5.4 EasyAppointments (Alternative Booking)\r\n- **Purpose:** Full appointment booking platform\r\n- **Location:** C:\\code\\customerdb\\schedule\\\r\n- **Features:**\r\n - Customer/provider management\r\n - Web-based booking widget\r\n - REST API\r\n\r\n### 5.5 Telegram (Notifications)\r\n- **Purpose:** Bot-based notifications\r\n- **Features:**\r\n - Send notifications to channels\r\n - Visit alerts\r\n - Cron job status\r\n- **Settings:** `telegram_bot_token`, `telegram_chat_id`\r\n\r\n### 5.6 Ollama (AI - Optional)\r\n- **Purpose:** Local AI for email generation\r\n- **Settings:** `ollama_url`, `ollama_default_model`\r\n\r\n---\r\n\r\n## 6. Scheduled Jobs / Cron\r\n\r\n### Configured Cron Jobs\r\n\r\n| Job Label | Schedule | Purpose |\r\n|----------|----------|---------|\r\n| **Next-Day Customer Emails** | `0 20 * * *` (8 PM) | Pickup reminder emails |\r\n| **Morning Import** | `0 6 * * *` (6 AM) | Morning data import |\r\n| **Morning Jobs** | `0 9 * * *` (9 AM) | Appointment sync, workflow |\r\n| **Nightly Reports** | `0 20 * * *` (8 PM) | Generate nightly summaries |\r\n| **Database Backup** | `0 2 * * *` (2 AM) | Full MariaDB backup |\r\n| **Daily Cleanup** | `0 4 * * *` (4 AM) | Temp files, old logs |\r\n| **Auto Delete** | `15 4 * * *` (4:15 AM) | Clean placeholder records |\r\n\r\n### Background Job Scripts (backend/jobs/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **run_morning_jobs.php** | Main morning job runner |\r\n| **smoke_morning_jobs.php** | Smoke test |\r\n| **send_nightly_reports.php** | Nightly report emailer |\r\n| **send_nextday_customer_emails.php** | Next-day reminders |\r\n| **delete_add_name_records.php** | Cleanup job |\r\n| **rebuild_customer_totals.php** | Rebuild totals |\r\n| **rebuild_daily_customer_totals.php** | Rebuild daily totals |\r\n| **rebuild_payment_ledger.php** | Rebuild payment ledger |\r\n| **import_customer_totals_csv.php** | CSV import |\r\n| **backfill_square_customer_log.php** | Square log backfill |\r\n| **run_cron_job.php** | Generic cron runner |\r\n\r\n---\r\n\r\n## 7. Web Applications\r\n\r\n### 7.1 Front Desk App (port 8895)\r\n\r\n| Page | Purpose |\r\n|------|---------|\r\n| **index.html** | Main dashboard, search, stats |\r\n| **customer.html** | Customer detail + visit history |\r\n| **visit.html** | Create/edit alteration order |\r\n| **customer_display.html** | Second monitor display |\r\n| **receipt.html** | Printable receipt |\r\n| **reports.html** | Order list by status |\r\n| **upcoming.html** | Next 7 days pickups |\r\n| **schedule.html** | Appointments calendar |\r\n| **login.html** | Login page |\r\n| **item_types.html** | Item type management |\r\n\r\n### 7.2 Management App (port 8894)\r\n\r\n| Page | Purpose |\r\n|------|---------|\r\n| **index.html** | Login page |\r\n| **webui.html** | Dashboard |\r\n| **customer.html** | Customer detail view |\r\n| **admin.html** | Reports browser |\r\n| **settings.html** | App settings editor |\r\n| **users.html** | User management |\r\n| **reports.html** | Order reports |\r\n\r\n---\r\n\r\n## 8. Settings & Configuration\r\n\r\n### app_settings Keys\r\n\r\n| Key | Purpose |\r\n|-----|---------|\r\n| theme_bg | Page background color |\r\n| theme_panel | Card/panel color |\r\n| theme_text | Text color |\r\n| theme_accent | Accent/button color |\r\n| theme_accent_deep | Darker accent |\r\n| theme_teal | Teal color |\r\n| theme_font | Font family |\r\n| smtp_host | Email server hostname |\r\n| smtp_port | Email server port |\r\n| smtp_secure | tls or ssl |\r\n| smtp_user | Email login |\r\n| smtp_pass | Email password |\r\n| smtp_from_email | From address |\r\n| smtp_from_name | From display name |\r\n| twilio_sid | Twilio Account SID |\r\n| twilio_token | Twilio Auth Token |\r\n| twilio_from | Twilio phone number |\r\n| imap_host | Incoming email server |\r\n| imap_port | IMAP port |\r\n| imap_ssl | SSL enable |\r\n| imap_user | IMAP login |\r\n| imap_pass | IMAP password |\r\n| square_token | Square API token |\r\n| square_location | Square location ID |\r\n| setmore_staff_key | Setmore API key |\r\n| setmore_refresh | Setmore refresh token |\r\n| require_report | PDF required |\r\n| require_signature | Signature required |\r\n| reports_base_path | Reports disk path |\r\n| reports_base_url | Reports URL |\r\n| ollama_url | Ollama AI URL |\r\n| ollama_default_model | Ollama model |\r\n| telegram_bot_token | Telegram bot token |\r\n| telegram_chat_id | Telegram chat ID |\r\n\r\n---\r\n\r\n## 9. Migration Notes\r\n\r\n### Access to MariaDB Workflow\r\n\r\n1. **Export source data from Access**\r\n - Export ordinary tables to CSV\r\n - Export attachment fields with VBA or DAO Recordset2 logic\r\n - Keep original IDs during migration\r\n\r\n2. **Load into MariaDB staging**\r\n - Use `LOAD DATA LOCAL INFILE` for CSV files\r\n - Load signatures separately into `visit_signatures`\r\n\r\n3. **Normalize**\r\n - `CustomerTable` → `customers`\r\n - `CustomerVisits` → `visits`\r\n - `AlterationItems` → `orders`, `order_items`, `order_item_type_links`\r\n - `2024TaxPayments` → `tax_payments`\r\n - `AlterationItemsChangeLog` → `change_log`\r\n\r\n4. **Signature BLOB path**\r\n - Export Access attachment to temp file\r\n - Convert BMP to PNG\r\n - Insert PNG bytes into `visit_signatures.signature_image`\r\n\r\n5. **Validation**\r\n - Compare source row counts vs target\r\n - Compare distinct legacy IDs\r\n - Compare sample signatures by SHA256\r\n\r\n---\r\n\r\n## 10. Scripts & Tools\r\n\r\n### PowerShell Scripts (scripts/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **import_legacy_totals_from_excel.ps1** | Import totals from Excel |\r\n| **sync_backend_mirror.ps1** | Sync backend mirror |\r\n| **check_backend_mirror.ps1** | Check mirror status |\r\n\r\n### Python Scripts (scripts/)\r\n\r\n| Script | Purpose |\r\n|--------|---------|\r\n| **signature_blob_loader.py** | Load signature blobs |\r\n| **access_to_mariadb_parser.py** | Parse Access to MariaDB |\r\n| **import_access_v2.py** | Import Access v2 |\r\n| **import_csv_v2.py** | Import CSV v2 |\r\n\r\n### SQL Migrations (migrations/)\r\n\r\n| File | Purpose |\r\n|------|---------|\r\n| **20260407_add_conversion_job.sql** | Add conversion job table |\r\n| **20260411_order_items_item_type_name_no_fk.sql** | Order items no FK |\r\n| **20260416_blog_prompt_templates.sql** | Blog prompt templates |\r\n\r\n---\r\n\r\n## File Statistics\r\n\r\n- **Total PHP files:** 200+\r\n- **Total SQL files:** 100+\r\n- **Total HTML pages:** 20+\r\n- **Database tables:** 30+\r\n\r\n---\r\n\r\n*End of Documentation*","is_text_editable":1,"can_edit_inline":1}
{"file_name":"COMPREHENSIVE_DOCUMENTATION.md","mime_type":"application/octet-stream"}
[]
{"backend_document_id":"20","document_type":"upload","title":"Complete Documentation up to date","slug":"complete-documentation-up-to-date","summary_text":null,"content_markdown":null,"content_html":null,"file_name":"COMPREHENSIVE_DOCUMENTATION.md","stored_name":"20260417-160828-6006d4f1.md","mime_type":"application/octet-stream","file_size_bytes":"22313","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260417-160828-6006d4f1.md","is_deleted":"0","created_at":"2026-04-17 12:08:29","updated_at":"2026-04-17 12:08:29","editor_content":"# Ella's Alterations - Comprehensive System Documentation\n\n**Generated:** April 17, 2026\n**Location:** C:\\code\\customerdb\n\n---\n\n## Table of Contents\n\n1. [System Overview](#1-system-overview)\n2. [Directory Structure](#2-directory-structure)\n3. [Database Schema](#3-database-schema)\n4. [API Endpoints](#4-api-endpoints)\n5. [External Integrations](#5-external-integrations)\n6. [Scheduled Jobs / Cron](#6-scheduled-jobs--cron)\n7. [Web Applications](#7-web-applications)\n8. [Settings & Configuration](#8-settings--configuration)\n9. [Migration Notes](#9-migration-notes)\n10. [Scripts & Tools](#10-scripts--tools)\n\n---\n\n## 1. System Overview\n\n### Purpose\nElla's Alterations is a comprehensive customer relationship management (CRM) andAlteration tracking system for a tailor shop. It manages customers, visits/alteration orders, appointments, payments, and provides integration with external services.\n\n### Server Information\n- **Primary Server:** kefa@192.168.7.202\n- **Mirror Drive:** /mnt/drive2 (mirrors /mnt/drive1)\n- **Database:** MariaDB - database `ellas_alterations`, user `kefa_admin`\n- **Web Server:** Apache 2.4 with mod-php\n- **PHP Version:** 8.3\n\n### Three Web Applications\n\n| App | Local URL | Public URL | Purpose |\n|-----|-----------|------------|---------|\n| Front Desk | http://192.168.7.202:8895 | https://ella.floridaalterations.com | Customer-facing operations |\n| Management | http://192.168.7.202:8894 | (LAN only) | Reports, admin, settings |\n| Scheduler | http://192.168.7.202:8897 | https://schedule.floridaalterations.com | Easy!Appointments booking |\n\n---\n\n## 2. Directory Structure\n\n```\ncustomerdb/\n├── backend/ # Main PHP backend API and services\n│ ├── api/ # REST API endpoints (~57 files)\n│ ├── bin/ # Binary/executable scripts\n│ ├── backup/ # Backup versions of services\n│ └── jobs/ # Background job scripts\n├── frontend/ # Front Desk app (port 8895)\n│ ├── *.html # HTML pages\n│ ├── api/ # PHP API endpoints\n│ └── reports/ # PDF reports (MMDDYYYY/filename.pdf)\n├── webui/ # Management app (port 8894)\n│ ├── *.html # HTML pages\n│ ├── api/ # PHP API endpoints\n│ ├── backend/ # Backend API copy\n│ └── email/ # Email templates\n├── schedule/ # Easy!Appointments installation\n├── schema/ # Database schema files\n├── migrations/ # SQL migrations\n├── scripts/ # PowerShell/Python import scripts\n├── docs/ # Documentation\n├── blog/ # Blog system and reengineering\n├── csv/ # CSV data files\n├── backups/ # Backup archives\n├── Topaz/ # Topaz signature pad configs\n├── webui/ # Second web interface\n│ ├── api/\n│ ├── backend/\n│ └── frontend/\n├── notused/ # Deprecated/unused files\n└── wordpress-plugin/ # WordPress integration\n```\n\n---\n\n## 3. Database Schema\n\n### Core Tables (from mariadb_core_schema.sql)\n\n#### customers\nMain customer registry.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| customer_id | INT UNSIGNED PK | Auto-increment |\n| legacy_customer_id | INT NULL | Original Access DB ID |\n| created_at | DATETIME | Record creation time |\n| full_name | VARCHAR(200) | Customer full name |\n| phone | VARCHAR(30) | Formatted (XXX) XXX-XXXX |\n| email | VARCHAR(255) | Email address |\n| pickup_due_at | DATETIME | Next pickup date (synced from EA) |\n| description | TEXT | Description |\n| notes | TEXT | Internal notes |\n| is_new_customer | TINYINT(1) | 1 if flagged as new |\n\n#### visits\nOne row per alteration job/order.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| visit_id | INT UNSIGNED PK | |\n| legacy_visit_id | INT NULL | Original Access DB ID |\n| customer_id | INT UNSIGNED FK | → customers |\n| visit_date | DATETIME | Drop-off date |\n| pickup_date | DATETIME | Expected pickup |\n| notes | TEXT | General visit notes |\n| customer_name_snapshot | VARCHAR(200) | |\n| phone_snapshot | VARCHAR(30) | |\n| email_snapshot | VARCHAR(255) | |\n| color | VARCHAR(100) | Garment color |\n| alterations_needed | TEXT | |\n| alterations_notes | TEXT | Detailed alteration instructions |\n| alterations_price | DECIMAL(10,2) | Manual price field |\n| total_alteration_price | DECIMAL(10,2) | Calculated from items |\n| expedited_fee | DECIMAL(10,2) | |\n| paid_date | DATETIME | When paid |\n| signature_text | TEXT | Text signature (legacy) |\n| signature_date | DATETIME | |\n| paid_flag | TINYINT(1) | 1 = paid |\n| source_key_value | VARCHAR(100) | |\n\n#### visit_signatures\nFull resolution signature images per visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| signature_id | BIGINT UNSIGNED PK | |\n| visit_id | INT UNIQUE | One signature per visit |\n| signature_date | DATETIME | |\n| image_format | VARCHAR(50) | Default 'image/png' |\n| original_filename | VARCHAR(255) | |\n| original_extension | VARCHAR(20) | |\n| file_size_bytes | BIGINT | |\n| sha256_hash | CHAR(64) | Hash for verification |\n| signature_image | LONGBLOB | Raw PNG binary |\n| created_at | DATETIME | |\n\n#### item_types\nMaster list of alteration types (e.g., Hem, Zipper, Taper).\n\n| Column | Type | Notes |\n|--------|------|-------|\n| item_type_id | INT UNSIGNED PK | |\n| type_name | VARCHAR(100) UNIQUE | Display name |\n\n#### orders\nOrders per visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_id | BIGINT UNSIGNED PK | |\n| visit_id | INT UNSIGNED FK | → visits |\n| customer_id | INT UNSIGNED FK | → customers |\n| created_at | DATETIME | |\n| no_tax | TINYINT(1) | Tax exempt |\n| picked_up | TINYINT(1) | |\n| source_table | VARCHAR(50) | |\n\n#### order_items\nLine items for each alteration within a visit.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_item_id | BIGINT UNSIGNED PK | |\n| order_id | BIGINT UNSIGNED FK | → orders |\n| legacy_alteration_id | INT NULL | |\n| item_type_id | INT FK | → item_types |\n| item_type_name | VARCHAR(100) | |\n| description | TEXT | |\n| quantity | INT | Default 1 |\n| unit_price | DECIMAL(10,2) | |\n| total_price | DECIMAL(10,2) | |\n| notes | TEXT | Per-item notes |\n| color | VARCHAR(100) | Garment color |\n| no_tax | TINYINT(1) | Tax exempt |\n| is_range_price | TINYINT(1) | |\n| range_price_label | VARCHAR(100) | |\n| paid_flag | TINYINT(1) | |\n| paid_type | VARCHAR(100) | |\n| picked_up | TINYINT(1) | |\n| created_at | DATETIME | |\n\n#### order_item_type_links\nMany-to-many links between order items and item types.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| order_item_type_link_id | BIGINT UNSIGNED PK | |\n| order_item_id | BIGINT UNSIGNED FK | → order_items |\n| item_type_id | INT UNSIGNED FK | → item_types |\n| slot_number | TINYINT UNSIGNED | |\n\n#### payments\nPayment records per order.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| payment_id | BIGINT UNSIGNED PK | |\n| order_id | BIGINT UNSIGNED FK | → orders |\n| payment_date | DATETIME | |\n| payment_type | VARCHAR(100) | |\n| payment_method | VARCHAR(100) | |\n| amount | DECIMAL(10,2) | |\n| notes | TEXT | |\n\n#### tax_payments\nTax payment records.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| tax_payment_id | INT UNSIGNED PK | |\n| legacy_tax_payment_id | INT NULL | |\n| payment_date | DATETIME | |\n| payment_type | VARCHAR(100) | |\n| payment_method | VARCHAR(100) | |\n| amount | DECIMAL(10,2) | |\n| notes | TEXT | |\n| receipt_blob | LONGBLOB | |\n\n#### change_log\nAudit log for changes.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| log_id | BIGINT UNSIGNED PK | |\n| legacy_log_id | INT NULL | |\n| table_name | VARCHAR(100) | |\n| record_id | BIGINT | |\n| visit_id | INT | |\n| field_name | VARCHAR(100) | |\n| old_value | TEXT | |\n| new_value | TEXT | |\n| changed_at | DATETIME | |\n| changed_by | VARCHAR(100) | |\n\n#### ads\nAdvertising/articles.\n\n| Column | Type | Notes |\n|--------|------|-------|\n| ad_id | INT UNSIGNED PK | |\n| legacy_ad_id | INT NULL | |\n| article_text | TEXT | |\n| article_picture | LONGBLOB | |\n| site_submitted | VARCHAR(255) | |\n| views | INT | Default 0 |\n| date_posted | DATETIME | |\n\n#### staging_* tables\nStaging tables for data migration.\n\n| Table Name | Purpose |\n|------------|---------|\n| staging_customer_table_raw | Staging for legacy customer data |\n| staging_customer_visits_raw | Staging for legacy visits |\n| staging_alteration_items_raw | Staging for items |\n\n### Additional Backend Tables\n\n| Table Name | Purpose |\n|------------|---------|\n| setmore_appointments | Synced from Setmore |\n| backend_conversion_job | Document conversion jobs |\n| backend_job_runs | Background job execution log |\n| customer_totals | Daily customer totals |\n| daily_customer_totals | Daily summary totals |\n| customer_payment_transactions | Payment ledger |\n| legacy_customer_totals_monthly | Monthly totals (legacy) |\n| legacy_customer_totals_irs_monthly | IRS monthly reports |\n| backend_appointment_workflow | Appointment state machine |\n| backend_no_show_log | No-show tracking |\n| backend_error_log | Error logging |\n| backend_change_log | Change audit log |\n| backend_document | Document storage |\n| backend_document_version | Document version history |\n| backend_speed_dial | Quick links |\n| backend_square_terminal_code | Square terminal pairing codes |\n| backend_square_terminal_device | Square device registry |\n| cron_job | Scheduled job definitions |\n| cron_job_run | Cron execution history |\n| app_settings | Global configuration |\n| app_users | Login accounts |\n| remember_tokens | \"Remember Me\" tokens |\n| customer_reports | PDF report index |\n\n---\n\n## 4. API Endpoints\n\n### 4.1 Frontend API (port 8895 /api/)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **customer_get.php** | Get customer by ID |\n| **customer_save.php** | Create or update customer |\n| **customer_detail.php** | Customer + visits summary |\n| **customer_delete.php** | Delete customer |\n| **customer_list_hide.php** | Hide customer from list |\n| **search_customers.php** | Search by name/phone |\n| **visit_get.php** | Get visit + items |\n| **visit_save.php** | Create/update visit + items |\n| **visit_delete.php** | Delete visit |\n| **visit_report_generate.php** | Generate visit report |\n| **item_types.php** | List all alteration types |\n| **item_type_add.php** | Add new item type |\n| **item_type_update.php** | Update item type |\n| **item_type_delete.php** | Delete item type |\n| **pickups_today.php** | Orders due today |\n| **ready.php** | Orders ready for pickup |\n| **overdue.php** | Overdue orders |\n| **upcoming.php** | Orders due in next N days |\n| **workload_7days.php** | 7-day workload |\n| **save_signature.php** | Save canvas signature |\n| **reports_list.php** | Reports for customer |\n| **attach_reports.php** | Index all PDFs to DB |\n| **save_signature.php** | Save signature image |\n| **today_appointments_list.php** | Today's appointments |\n| **settings_get.php** | Get app settings |\n| **settings_save.php** | Save app settings |\n| **settings_css.php** | Live theme CSS |\n| **send_email.php** | Send email via SMTP |\n| **send_sms.php** | Send SMS via Twilio |\n| **send_reminders.php** | Send appointment reminders |\n| **ea_appointments.php** | EasyAppointments REST API |\n| **ea_webhook.php** | Receives EA webhook events |\n| **setmore.php** | Setmore API proxy |\n| **setmore_to_ea_import.php** | Import customers to EA |\n| **square_config.php** | Square public config |\n| **square_process.php** | Process Square payment |\n| **square_customer_log.php** | Customer Square log |\n| **square_terminal.php** | Square terminal |\n| **login.php** | Authenticate |\n| **logout.php** | Clear session |\n| **session_check.php** | Check if logged in |\n| **auth.php** | Session authentication |\n| **config.php** | App config constants |\n| **db.php** | Database connection |\n| **log_error.php** | Log client-side error |\n| **todo_list.php** | Todo list management |\n| **send_telegram.php** | Send Telegram messages |\n\n### 4.2 Management API (port 8894 /api/)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **login.php** | Authenticate |\n| **logout.php** | Clear session |\n| **session_check.php** | Check if logged in |\n| **customer_detail.php** | Customer + visits |\n| **search_customers.php** | Search customers |\n| **update_customer.php** | Update customer |\n| **users_list.php** | List all users |\n| **user_save.php** | Create/update user |\n| **list_report_dirs.php** | Date folders |\n| **list_report_files.php** | PDFs in folder |\n| **report_file.php** | Access report file |\n| **pickups_today.php** | Today's pickups |\n| **ready.php** | Ready for pickup |\n| **overdue.php** | Overdue |\n| **settings_get.php** | Get settings |\n| **settings_save.php** | Save settings |\n| **settings_css.php** | Live CSS theme |\n| **imap_list.php** | List IMAP folders |\n| **imap_fetch.php** | Fetch emails |\n| **imap_send.php** | Send email via IMAP |\n| **imap_action.php** | IMAP actions |\n| **sms_send_helper.php** | SMS sending helper |\n| **sms_webhook_receive.php** | Receive SMS webhooks |\n| **sms_inbox_api.php** | SMS inbox API |\n| **slideshow_list.php** | Slideshow images |\n| **slideshow_upload.php** | Upload slideshow |\n| **slideshow_delete.php** | Delete slideshow |\n| **users_list.php** | User management |\n| **report_list.php** | Report list |\n| **debug_filters.php** | Debug filters |\n| **sysadmin.php** | System admin |\n| **test.php** | Testing endpoint |\n\n### 4.3 Backend API (C:\\code\\customerdb\\backend\\api\\)\n\n| Endpoint File | Purpose |\n|---------------|---------|\n| **customer.php** | Get customer details |\n| **customers.php** | List/search customers |\n| **customer_save.php** | Save/update customer |\n| **visit.php** | Get visit details |\n| **visit_delete.php** | Delete visit |\n| **appointments.php** | Unified appointments |\n| **appointments_summary.php** | Appointment statistics |\n| **setmore_appointments.php** | Setmore appointments |\n| **setmore_import.php** | Import from Setmore |\n| **module_*.php** | Module loaders |\n| **alteration_items.php** | Alteration management |\n| **alteration_items_summary.php** | Alteration statistics |\n| **gold_silver_*.php** | Gold/silver prices |\n| **bank_*.php** | Bank transactions |\n| **stocks_summary.php** | Stock data |\n| **medication_*.php** | Medication tracking |\n| **payroll_import.php** | Payroll import |\n| **car_maintenance_*.php** | Vehicle maintenance |\n| **duke_*.php** | Duke integration |\n| **reports_*.php** | Report generation |\n| **totals_import.php** | Customer totals import |\n| **documents.php** | Document management |\n| **document_upload.php** | File uploads |\n| **cust_email_*.php** | Email data |\n| **mail_*.php** | Email sending |\n| **reminders_create.php** | Create reminders |\n| **nightly_reports.php** | Nightly reports |\n| **morning_jobs.php** | Morning jobs |\n| **appointment_workflow.php** | Workflow management |\n| **sysadmin.php** | System admin |\n| **reporting_module.php** | Reporting |\n| **job_runs.php** | Job tracking |\n| **health.php** | Health check |\n| **overview.php** | Dashboard data |\n\n---\n\n## 5. External Integrations\n\n### 5.1 Square (Payment Processing)\n- **Purpose:** Point-of-sale payments, receipts, terminal management\n- **Features:**\n - Square Terminal device pairing/management\n - Payment processing with tips\n - Customer payment logging\n - Sandbox and live environments\n- **Tables:** `backend_square_terminal_code`, `backend_square_terminal_device`, `square_customer_log`\n\n### 5.2 Twilio (SMS)\n- **Purpose:** SMS notifications and reminders\n- **Features:**\n - Send SMS to customers\n - Appointment reminders\n - SMS consent tracking\n- **Settings:** `twilio_sid`, `twilio_token`, `twilio_from`\n\n### 5.3 Setmore (Appointment Booking)\n- **Purpose:** Appointment synchronization\n- **Features:**\n - Pull appointments from Setmore API\n - Link to customers/visits\n- **Table:** `setmore_appointments`\n- **Settings:** `setmore_refresh_token`, `setmore_staff_key`\n\n### 5.4 EasyAppointments (Alternative Booking)\n- **Purpose:** Full appointment booking platform\n- **Location:** C:\\code\\customerdb\\schedule\\\n- **Features:**\n - Customer/provider management\n - Web-based booking widget\n - REST API\n\n### 5.5 Telegram (Notifications)\n- **Purpose:** Bot-based notifications\n- **Features:**\n - Send notifications to channels\n - Visit alerts\n - Cron job status\n- **Settings:** `telegram_bot_token`, `telegram_chat_id`\n\n### 5.6 Ollama (AI - Optional)\n- **Purpose:** Local AI for email generation\n- **Settings:** `ollama_url`, `ollama_default_model`\n\n---\n\n## 6. Scheduled Jobs / Cron\n\n### Configured Cron Jobs\n\n| Job Label | Schedule | Purpose |\n|----------|----------|---------|\n| **Next-Day Customer Emails** | `0 20 * * *` (8 PM) | Pickup reminder emails |\n| **Morning Import** | `0 6 * * *` (6 AM) | Morning data import |\n| **Morning Jobs** | `0 9 * * *` (9 AM) | Appointment sync, workflow |\n| **Nightly Reports** | `0 20 * * *` (8 PM) | Generate nightly summaries |\n| **Database Backup** | `0 2 * * *` (2 AM) | Full MariaDB backup |\n| **Daily Cleanup** | `0 4 * * *` (4 AM) | Temp files, old logs |\n| **Auto Delete** | `15 4 * * *` (4:15 AM) | Clean placeholder records |\n\n### Background Job Scripts (backend/jobs/)\n\n| Script | Purpose |\n|--------|---------|\n| **run_morning_jobs.php** | Main morning job runner |\n| **smoke_morning_jobs.php** | Smoke test |\n| **send_nightly_reports.php** | Nightly report emailer |\n| **send_nextday_customer_emails.php** | Next-day reminders |\n| **delete_add_name_records.php** | Cleanup job |\n| **rebuild_customer_totals.php** | Rebuild totals |\n| **rebuild_daily_customer_totals.php** | Rebuild daily totals |\n| **rebuild_payment_ledger.php** | Rebuild payment ledger |\n| **import_customer_totals_csv.php** | CSV import |\n| **backfill_square_customer_log.php** | Square log backfill |\n| **run_cron_job.php** | Generic cron runner |\n\n---\n\n## 7. Web Applications\n\n### 7.1 Front Desk App (port 8895)\n\n| Page | Purpose |\n|------|---------|\n| **index.html** | Main dashboard, search, stats |\n| **customer.html** | Customer detail + visit history |\n| **visit.html** | Create/edit alteration order |\n| **customer_display.html** | Second monitor display |\n| **receipt.html** | Printable receipt |\n| **reports.html** | Order list by status |\n| **upcoming.html** | Next 7 days pickups |\n| **schedule.html** | Appointments calendar |\n| **login.html** | Login page |\n| **item_types.html** | Item type management |\n\n### 7.2 Management App (port 8894)\n\n| Page | Purpose |\n|------|---------|\n| **index.html** | Login page |\n| **webui.html** | Dashboard |\n| **customer.html** | Customer detail view |\n| **admin.html** | Reports browser |\n| **settings.html** | App settings editor |\n| **users.html** | User management |\n| **reports.html** | Order reports |\n\n---\n\n## 8. Settings & Configuration\n\n### app_settings Keys\n\n| Key | Purpose |\n|-----|---------|\n| theme_bg | Page background color |\n| theme_panel | Card/panel color |\n| theme_text | Text color |\n| theme_accent | Accent/button color |\n| theme_accent_deep | Darker accent |\n| theme_teal | Teal color |\n| theme_font | Font family |\n| smtp_host | Email server hostname |\n| smtp_port | Email server port |\n| smtp_secure | tls or ssl |\n| smtp_user | Email login |\n| smtp_pass | Email password |\n| smtp_from_email | From address |\n| smtp_from_name | From display name |\n| twilio_sid | Twilio Account SID |\n| twilio_token | Twilio Auth Token |\n| twilio_from | Twilio phone number |\n| imap_host | Incoming email server |\n| imap_port | IMAP port |\n| imap_ssl | SSL enable |\n| imap_user | IMAP login |\n| imap_pass | IMAP password |\n| square_token | Square API token |\n| square_location | Square location ID |\n| setmore_staff_key | Setmore API key |\n| setmore_refresh | Setmore refresh token |\n| require_report | PDF required |\n| require_signature | Signature required |\n| reports_base_path | Reports disk path |\n| reports_base_url | Reports URL |\n| ollama_url | Ollama AI URL |\n| ollama_default_model | Ollama model |\n| telegram_bot_token | Telegram bot token |\n| telegram_chat_id | Telegram chat ID |\n\n---\n\n## 9. Migration Notes\n\n### Access to MariaDB Workflow\n\n1. **Export source data from Access**\n - Export ordinary tables to CSV\n - Export attachment fields with VBA or DAO Recordset2 logic\n - Keep original IDs during migration\n\n2. **Load into MariaDB staging**\n - Use `LOAD DATA LOCAL INFILE` for CSV files\n - Load signatures separately into `visit_signatures`\n\n3. **Normalize**\n - `CustomerTable` → `customers`\n - `CustomerVisits` → `visits`\n - `AlterationItems` → `orders`, `order_items`, `order_item_type_links`\n - `2024TaxPayments` → `tax_payments`\n - `AlterationItemsChangeLog` → `change_log`\n\n4. **Signature BLOB path**\n - Export Access attachment to temp file\n - Convert BMP to PNG\n - Insert PNG bytes into `visit_signatures.signature_image`\n\n5. **Validation**\n - Compare source row counts vs target\n - Compare distinct legacy IDs\n - Compare sample signatures by SHA256\n\n---\n\n## 10. Scripts & Tools\n\n### PowerShell Scripts (scripts/)\n\n| Script | Purpose |\n|--------|---------|\n| **import_legacy_totals_from_excel.ps1** | Import totals from Excel |\n| **sync_backend_mirror.ps1** | Sync backend mirror |\n| **check_backend_mirror.ps1** | Check mirror status |\n\n### Python Scripts (scripts/)\n\n| Script | Purpose |\n|--------|---------|\n| **signature_blob_loader.py** | Load signature blobs |\n| **access_to_mariadb_parser.py** | Parse Access to MariaDB |\n| **import_access_v2.py** | Import Access v2 |\n| **import_csv_v2.py** | Import CSV v2 |\n\n### SQL Migrations (migrations/)\n\n| File | Purpose |\n|------|---------|\n| **20260407_add_conversion_job.sql** | Add conversion job table |\n| **20260411_order_items_item_type_name_no_fk.sql** | Order items no FK |\n| **20260416_blog_prompt_templates.sql** | Blog prompt templates |\n\n---\n\n## File Statistics\n\n- **Total PHP files:** 200+\n- **Total SQL files:** 100+\n- **Total HTML pages:** 20+\n- **Database tables:** 30+\n\n---\n\n*End of Documentation*","is_text_editable":1,"can_edit_inline":1}
{"document_type":"upload"}
{"backend_document_id":"19","document_type":"upload","title":"Change Hours","slug":"change-hours","summary_text":"Change Business Hours","content_markdown":"# **Places to change hours**\r\n*** https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\n* Website\r\n* facebook\r\n* twitter\r\n* nextdoor\r\n* apple\r\n* yelp**","content_html":"<h1>**Places to change hours**</h1>\n<p><em>*</em> https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info</p>\n<ul>\n<li>Website</li>\n<li>facebook</li>\n<li>twitter</li>\n<li>nextdoor</li>\n<li>apple</li>\n<li>yelp**</li>\n</ul>","file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-17 12:02:14","editor_content":"# **Places to change hours**\r\n*** https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\n* Website\r\n* facebook\r\n* twitter\r\n* nextdoor\r\n* apple\r\n* yelp**","is_text_editable":1,"can_edit_inline":1}
{"backend_document_id":"19","document_type":"upload","title":"Birthdays","slug":"birthdays","summary_text":"Birthday Dates","content_markdown":"Birthdates:\r\nMe - July 5, 1979\r\nChristy - December 30\r\nKhey - February 22\r\nGian - April 26\r\nJimbo - October 7\r\nLola Julie- October 28\r\nAte pia – September 9\r\nSherill – July 30\r\n\r\nAarons birthday 03/07/\r\ndads birthday 05\r\ndarrens birthday05\r\nmoms birthday 01/21","content_html":"<p>Birthdates:</p>\n<p>Me - July 5, 1979</p>\n<p>Christy - December 30</p>\n<p>Khey - February 22</p>\n<p>Gian - April 26</p>\n<p>Jimbo - October 7</p>\n<p>Lola Julie- October 28</p>\n<p>Ate pia – September 9</p>\n<p>Sherill – July 30</p>\n<p>Aarons birthday 03/07/</p>\n<p>dads birthday 05</p>\n<p>darrens birthday05</p>\n<p>moms birthday 01/21</p>","file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-17 12:02:57","editor_content":"Birthdates:\r\nMe - July 5, 1979\r\nChristy - December 30\r\nKhey - February 22\r\nGian - April 26\r\nJimbo - October 7\r\nLola Julie- October 28\r\nAte pia – September 9\r\nSherill – July 30\r\n\r\nAarons birthday 03/07/\r\ndads birthday 05\r\ndarrens birthday05\r\nmoms birthday 01/21","is_text_editable":1,"can_edit_inline":1}
{"document_type":"upload"}
{"backend_document_id":"19","document_type":"upload","title":"Change Hours","slug":"change-hours","summary_text":"Change Business Hours","content_markdown":"https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\nWebsite\r\nfacebook\r\ntwitter\r\nnextdoor\r\napple\r\nyelp","content_html":"<p>https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info</p>\n<p>Website</p>\n<p>facebook</p>\n<p>twitter</p>\n<p>nextdoor</p>\n<p>apple</p>\n<p>yelp</p>","file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-17 12:01:43","editor_content":"https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\nWebsite\r\nfacebook\r\ntwitter\r\nnextdoor\r\napple\r\nyelp","is_text_editable":1,"can_edit_inline":1}
{"backend_document_id":"19","document_type":"upload","title":"Change Hours","slug":"change-hours","summary_text":"Change Business Hours","content_markdown":"# **Places to change hours**\r\n*** https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\n* Website\r\n* facebook\r\n* twitter\r\n* nextdoor\r\n* apple\r\n* yelp**","content_html":"<h1>**Places to change hours**</h1>\n<p><em>*</em> https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info</p>\n<ul>\n<li>Website</li>\n<li>facebook</li>\n<li>twitter</li>\n<li>nextdoor</li>\n<li>apple</li>\n<li>yelp**</li>\n</ul>","file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-17 12:02:14","editor_content":"# **Places to change hours**\r\n*** https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\n* Website\r\n* facebook\r\n* twitter\r\n* nextdoor\r\n* apple\r\n* yelp**","is_text_editable":1,"can_edit_inline":1}
{"document_type":"upload"}
{"backend_document_id":"19","document_type":"upload","title":"Claude and Curser Reporting Changes 04-16","slug":"claude-and-curser-reporting-changes-04-16","summary_text":"Claude and Curser Reporting Changes 04-16","content_markdown":null,"content_html":null,"file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-16 14:21:20","editor_content":"# Reporting Module Updates Log\n\nDate: 2026-04-15 \nOwner: Codex (implementation) \nScope: Backend reporting module (`backend/reporting_module.php`, `backend/api/reporting_module.php`, `backend/reporting_module_service.php`)\n\n## Purpose\n\nThis document tracks:\n\n- Updates completed\n- Areas implemented vs planned\n- Data sources used by each report\n- Corrections and refactors made\n- API/UI calls and usage patterns\n- Completion timeline\n\n## Completion Timeline\n\n### 2026-04-15 (Initial build)\n\n- Added backend report engine:\n - `backend/reporting_module_service.php`\n- Added API endpoint:\n - `backend/api/reporting_module.php`\n- Added backend themed page:\n - `backend/reporting_module.php`\n- Linked module into existing reporting surfaces:\n - `backend/module_service.php`\n - `backend/reports.php`\n\n### 2026-04-15 (Coverage expansion)\n\n- Expanded report families for:\n - Sales\n - Cost / P&L / Balance Sheet / Cash Flow\n - Customer service and quality\n - Labor/time and trend analysis\n - Compliance and insurance\n - Marketing/sales\n- Added planned placeholders for unsupported schema areas:\n - Inventory\n - Employee performance\n - Goals vs actuals\n - Referral source tracking\n - Missed fitting and seamstress productivity\n\n### 2026-04-15 (Legend + documentation controls)\n\n- Added report metadata registry:\n - status\n - completed date\n - data sources\n - report logic notes\n- Added in-app \"Data Source Legend\" panel in:\n - `backend/reporting_module.php`\n- Added catalog column for completion date in UI.\n\n### 2026-04-15 (Settings integration for backend theming)\n\n- Aligned frontend settings APIs with backend/webui theme controls so backend modules can use centralized style settings:\n - `frontend/api/settings_save.php`\n - `frontend/api/settings_get.php`\n - `frontend/api/settings_css.php`\n- Added missing persistence + exposure for:\n - backend-specific theme keys (`backend_theme_*`)\n - advanced typography and sizing keys (`theme_heading_font`, `theme_border_width`, `theme_radius_*`, `theme_panel_padding`, `theme_button_padding_*`)\n - today-pill color keys and extended button label/color keys\n- Result: settings entered in Settings module now propagate to backend pages that consume `/api/settings_css.php` and CSS variables from `page_helpers.php`.\n\n## Report Areas: Status and Data Uses\n\n| Report Key | Status | Completed On | Data Sources | Notes |\n|---|---|---|---|---|\n| `sales_reports` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Sales trend rollup |\n| `sales_revenue_by_category` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Revenue by service/category |\n| `sales_trends` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Day/month/quarter/year buckets |\n| `top_selling_services` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Quantity and gross sales |\n| `sales_by_customer` | Implemented | 2026-04-15 | `customers`, `orders`, `visits`, `payments` | Customer revenue ranking |\n| `sales_goals_vs_actuals` | Planned | - | Requires goals table/config | Pending schema |\n| `cost_reports` | Implemented | 2026-04-15 | `bills_entry`, `bills_item` | Materials/labor/overhead split |\n| `inventory_reports` | Planned | - | Requires inventory schema | Pending schema |\n| `profit_and_loss_statement` | Implemented | 2026-04-15 | `payments`, `bills_entry`, `bills_item` | Revenue minus expenses |\n| `balance_sheet` | Implemented | 2026-04-15 | `bills_balance`, `orders`, `order_items`, `payments`, `bills_entry` | Assets/liabilities/equity snapshot |\n| `cash_flow_statement` | Implemented | 2026-04-15 | `payments`, `bills_entry`, `tax_payments` | Inflows/outflows/net |\n| `customer_service_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Overdue/aging/volume indicators |\n| `time_and_labor_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Billed labor trend proxy |\n| `trends_and_analysis_reports` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Monthly KPI trend |\n| `compliance_reports` | Implemented | 2026-04-15 | `payments`, `tax_payments` | Sales/tax snapshot |\n| `insurance_reports` | Implemented | 2026-04-15 | `bills_entry`, `bills_item` | Insurance bill tracking |\n| `quality_control_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Open/overdue quality indicators |\n| `marketing_and_sales_reports` | Implemented | 2026-04-15 | `customers`, `orders`, `visits`, `payments` | Top customer activity |\n| `employee_performance_reports` | Planned | - | Requires worker attribution | Pending schema |\n| `workroom_queue` | Implemented | 2026-04-15 | `visits`, `customers`, `orders` | Queue + rush/stage |\n| `missed_fitting_alert` | Planned | - | Requires fitting milestones | Pending schema |\n| `ready_for_pickup_aging` | Implemented | 2026-04-15 | `visits`, `customers`, `orders`, `order_items` | 14+ day pickup aging |\n| `revenue_by_category` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Alias report |\n| `average_order_value` | Implemented | 2026-04-15 | `orders`, `visits`, `payments` | AOV |\n| `outstanding_balances` | Implemented | 2026-04-15 | `orders`, `customers`, `visits`, `order_items`, `payments` | WIP balances |\n| `customer_lifetime_value` | Implemented | 2026-04-15 | `customers`, `orders`, `payments` | Top 20% CLV |\n| `referral_source_tracking` | Planned | - | Requires referral source | Pending schema |\n| `dormant_clients` | Implemented | 2026-04-15 | `customers`, `visits`, `orders`, `payments` | 12+ month inactive clients |\n| `seamstress_productivity` | Planned | - | Requires worker time logs | Pending schema |\n| `garment_type_frequency` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Garment/service frequency |\n| `projected_completion_vs_deadline` | Implemented | 2026-04-15 | `visits`, `customers`, `orders` | Deadline risk status |\n\n## Corrections and Refactors Applied\n\n- Standardized report availability through metadata-based support checks:\n - `app_reporting_module_is_supported()`\n- Introduced single report metadata registry:\n - `app_reporting_module_definitions()`\n- Added metadata accessor:\n - `app_reporting_module_metadata()`\n- Updated UI catalog to include completion dates.\n- Added in-page data-source legend to reduce ambiguity during audits and reconciliation reviews.\n\n## Calls and Usage\n\n### Backend UI Calls\n\n- Open reporting module page:\n - `/backend/reporting_module.php`\n- Run report in browser:\n - `/backend/reporting_module.php?report=sales_trends&start_date=2026-01-01&end_date=2026-04-15&group_by=month&limit=25`\n\n### API Calls\n\n- Catalog:\n - `/backend/api/reporting_module.php?report=catalog`\n- Sales by customer:\n - `/backend/api/reporting_module.php?report=sales_by_customer&start_date=2026-01-01&end_date=2026-04-15&limit=100`\n- P&L:\n - `/backend/api/reporting_module.php?report=profit_and_loss_statement&start_date=2026-01-01&end_date=2026-04-15`\n- Balance sheet:\n - `/backend/api/reporting_module.php?report=balance_sheet&end_date=2026-04-15`\n\n### Internal Function Calls (Core)\n\n- Catalog and metadata:\n - `app_reporting_module_catalog()`\n - `app_reporting_module_metadata()`\n- Dispatcher:\n - `app_reporting_module_run()`\n- Service/report functions:\n - `app_reporting_module_sales_trends()`\n - `app_reporting_module_profit_and_loss()`\n - `app_reporting_module_balance_sheet()`\n - `app_reporting_module_cash_flow()`\n - `app_reporting_module_cost_reports()`\n - plus additional report-specific handlers\n\n## Pending Gaps / Next Corrections\n\n1. Add dedicated inventory schema (stock ledger, reorder points, usage) and implement `inventory_reports`.\n2. Add referral source persistence (`customers.referral_source` or linked table) and implement `referral_source_tracking`.\n3. Add employee/time attribution tables and implement:\n - `employee_performance_reports`\n - `seamstress_productivity`\n4. Add sales goals table to enable `sales_goals_vs_actuals`.\n5. Add fitting milestone fields/events to enable `missed_fitting_alert`.\n\n## Change Control Notes\n\n- All new report logic was added non-destructively.\n- Existing reporting endpoints were not removed.\n- The module keeps planned reports visible for roadmap tracking.\n","is_text_editable":1,"can_edit_inline":1}
{"backend_document_id":"19","document_type":"upload","title":"Change Hours","slug":"change-hours","summary_text":"Change Business Hours","content_markdown":"https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\nWebsite\r\nfacebook\r\ntwitter\r\nnextdoor\r\napple\r\nyelp","content_html":"<p>https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info</p>\n<p>Website</p>\n<p>facebook</p>\n<p>twitter</p>\n<p>nextdoor</p>\n<p>apple</p>\n<p>yelp</p>","file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-17 12:01:43","editor_content":"https://business.apple.com/companies/1508340657688417792/maps/locations/1554057468158415192/info\r\nWebsite\r\nfacebook\r\ntwitter\r\nnextdoor\r\napple\r\nyelp","is_text_editable":1,"can_edit_inline":1}
[]
{"customer_id":"489082","legacy_customer_id":null,"created_at":"2026-04-17 11:20:50","full_name":"Wanda Laezza","phone":"8139972753","email":"lasagnas@aol.com","pickup_due_at":null,"description":null,"notes":"","is_new_customer":"1","setmore_customer_key":null}
{"customer_id":"489082","legacy_customer_id":null,"created_at":"2026-04-17 11:20:00","full_name":"Wanda Laezza","phone":"8139972753","email":"lasagnas@aol.com","pickup_due_at":"2026-05-04 09:29:00","description":null,"notes":"3 Skirts @ 20\r\n2 pants @ 22\r\nPaid Square 111.28","is_new_customer":"1","setmore_customer_key":null}
{"file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","mime_type":"application/octet-stream"}
[]
{"backend_document_id":"19","document_type":"upload","title":"Claude and Curser Reporting Changes 04-16","slug":"claude-and-curser-reporting-changes-04-16","summary_text":"Claude and Curser Reporting Changes 04-16","content_markdown":null,"content_html":null,"file_name":"REPORTING_MODULE_UPDATES_2026-04-15.md","stored_name":"20260416-182120-cf1442b8.md","mime_type":"application/octet-stream","file_size_bytes":"8228","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182120-cf1442b8.md","is_deleted":"0","created_at":"2026-04-16 14:21:20","updated_at":"2026-04-16 14:21:20","editor_content":"# Reporting Module Updates Log\n\nDate: 2026-04-15 \nOwner: Codex (implementation) \nScope: Backend reporting module (`backend/reporting_module.php`, `backend/api/reporting_module.php`, `backend/reporting_module_service.php`)\n\n## Purpose\n\nThis document tracks:\n\n- Updates completed\n- Areas implemented vs planned\n- Data sources used by each report\n- Corrections and refactors made\n- API/UI calls and usage patterns\n- Completion timeline\n\n## Completion Timeline\n\n### 2026-04-15 (Initial build)\n\n- Added backend report engine:\n - `backend/reporting_module_service.php`\n- Added API endpoint:\n - `backend/api/reporting_module.php`\n- Added backend themed page:\n - `backend/reporting_module.php`\n- Linked module into existing reporting surfaces:\n - `backend/module_service.php`\n - `backend/reports.php`\n\n### 2026-04-15 (Coverage expansion)\n\n- Expanded report families for:\n - Sales\n - Cost / P&L / Balance Sheet / Cash Flow\n - Customer service and quality\n - Labor/time and trend analysis\n - Compliance and insurance\n - Marketing/sales\n- Added planned placeholders for unsupported schema areas:\n - Inventory\n - Employee performance\n - Goals vs actuals\n - Referral source tracking\n - Missed fitting and seamstress productivity\n\n### 2026-04-15 (Legend + documentation controls)\n\n- Added report metadata registry:\n - status\n - completed date\n - data sources\n - report logic notes\n- Added in-app \"Data Source Legend\" panel in:\n - `backend/reporting_module.php`\n- Added catalog column for completion date in UI.\n\n### 2026-04-15 (Settings integration for backend theming)\n\n- Aligned frontend settings APIs with backend/webui theme controls so backend modules can use centralized style settings:\n - `frontend/api/settings_save.php`\n - `frontend/api/settings_get.php`\n - `frontend/api/settings_css.php`\n- Added missing persistence + exposure for:\n - backend-specific theme keys (`backend_theme_*`)\n - advanced typography and sizing keys (`theme_heading_font`, `theme_border_width`, `theme_radius_*`, `theme_panel_padding`, `theme_button_padding_*`)\n - today-pill color keys and extended button label/color keys\n- Result: settings entered in Settings module now propagate to backend pages that consume `/api/settings_css.php` and CSS variables from `page_helpers.php`.\n\n## Report Areas: Status and Data Uses\n\n| Report Key | Status | Completed On | Data Sources | Notes |\n|---|---|---|---|---|\n| `sales_reports` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Sales trend rollup |\n| `sales_revenue_by_category` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Revenue by service/category |\n| `sales_trends` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Day/month/quarter/year buckets |\n| `top_selling_services` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Quantity and gross sales |\n| `sales_by_customer` | Implemented | 2026-04-15 | `customers`, `orders`, `visits`, `payments` | Customer revenue ranking |\n| `sales_goals_vs_actuals` | Planned | - | Requires goals table/config | Pending schema |\n| `cost_reports` | Implemented | 2026-04-15 | `bills_entry`, `bills_item` | Materials/labor/overhead split |\n| `inventory_reports` | Planned | - | Requires inventory schema | Pending schema |\n| `profit_and_loss_statement` | Implemented | 2026-04-15 | `payments`, `bills_entry`, `bills_item` | Revenue minus expenses |\n| `balance_sheet` | Implemented | 2026-04-15 | `bills_balance`, `orders`, `order_items`, `payments`, `bills_entry` | Assets/liabilities/equity snapshot |\n| `cash_flow_statement` | Implemented | 2026-04-15 | `payments`, `bills_entry`, `tax_payments` | Inflows/outflows/net |\n| `customer_service_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Overdue/aging/volume indicators |\n| `time_and_labor_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Billed labor trend proxy |\n| `trends_and_analysis_reports` | Implemented | 2026-04-15 | `payments`, `orders`, `visits` | Monthly KPI trend |\n| `compliance_reports` | Implemented | 2026-04-15 | `payments`, `tax_payments` | Sales/tax snapshot |\n| `insurance_reports` | Implemented | 2026-04-15 | `bills_entry`, `bills_item` | Insurance bill tracking |\n| `quality_control_reports` | Implemented | 2026-04-15 | `visits`, `orders` | Open/overdue quality indicators |\n| `marketing_and_sales_reports` | Implemented | 2026-04-15 | `customers`, `orders`, `visits`, `payments` | Top customer activity |\n| `employee_performance_reports` | Planned | - | Requires worker attribution | Pending schema |\n| `workroom_queue` | Implemented | 2026-04-15 | `visits`, `customers`, `orders` | Queue + rush/stage |\n| `missed_fitting_alert` | Planned | - | Requires fitting milestones | Pending schema |\n| `ready_for_pickup_aging` | Implemented | 2026-04-15 | `visits`, `customers`, `orders`, `order_items` | 14+ day pickup aging |\n| `revenue_by_category` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Alias report |\n| `average_order_value` | Implemented | 2026-04-15 | `orders`, `visits`, `payments` | AOV |\n| `outstanding_balances` | Implemented | 2026-04-15 | `orders`, `customers`, `visits`, `order_items`, `payments` | WIP balances |\n| `customer_lifetime_value` | Implemented | 2026-04-15 | `customers`, `orders`, `payments` | Top 20% CLV |\n| `referral_source_tracking` | Planned | - | Requires referral source | Pending schema |\n| `dormant_clients` | Implemented | 2026-04-15 | `customers`, `visits`, `orders`, `payments` | 12+ month inactive clients |\n| `seamstress_productivity` | Planned | - | Requires worker time logs | Pending schema |\n| `garment_type_frequency` | Implemented | 2026-04-15 | `order_items`, `item_types`, `orders`, `visits` | Garment/service frequency |\n| `projected_completion_vs_deadline` | Implemented | 2026-04-15 | `visits`, `customers`, `orders` | Deadline risk status |\n\n## Corrections and Refactors Applied\n\n- Standardized report availability through metadata-based support checks:\n - `app_reporting_module_is_supported()`\n- Introduced single report metadata registry:\n - `app_reporting_module_definitions()`\n- Added metadata accessor:\n - `app_reporting_module_metadata()`\n- Updated UI catalog to include completion dates.\n- Added in-page data-source legend to reduce ambiguity during audits and reconciliation reviews.\n\n## Calls and Usage\n\n### Backend UI Calls\n\n- Open reporting module page:\n - `/backend/reporting_module.php`\n- Run report in browser:\n - `/backend/reporting_module.php?report=sales_trends&start_date=2026-01-01&end_date=2026-04-15&group_by=month&limit=25`\n\n### API Calls\n\n- Catalog:\n - `/backend/api/reporting_module.php?report=catalog`\n- Sales by customer:\n - `/backend/api/reporting_module.php?report=sales_by_customer&start_date=2026-01-01&end_date=2026-04-15&limit=100`\n- P&L:\n - `/backend/api/reporting_module.php?report=profit_and_loss_statement&start_date=2026-01-01&end_date=2026-04-15`\n- Balance sheet:\n - `/backend/api/reporting_module.php?report=balance_sheet&end_date=2026-04-15`\n\n### Internal Function Calls (Core)\n\n- Catalog and metadata:\n - `app_reporting_module_catalog()`\n - `app_reporting_module_metadata()`\n- Dispatcher:\n - `app_reporting_module_run()`\n- Service/report functions:\n - `app_reporting_module_sales_trends()`\n - `app_reporting_module_profit_and_loss()`\n - `app_reporting_module_balance_sheet()`\n - `app_reporting_module_cash_flow()`\n - `app_reporting_module_cost_reports()`\n - plus additional report-specific handlers\n\n## Pending Gaps / Next Corrections\n\n1. Add dedicated inventory schema (stock ledger, reorder points, usage) and implement `inventory_reports`.\n2. Add referral source persistence (`customers.referral_source` or linked table) and implement `referral_source_tracking`.\n3. Add employee/time attribution tables and implement:\n - `employee_performance_reports`\n - `seamstress_productivity`\n4. Add sales goals table to enable `sales_goals_vs_actuals`.\n5. Add fitting milestone fields/events to enable `missed_fitting_alert`.\n\n## Change Control Notes\n\n- All new report logic was added non-destructively.\n- Existing reporting endpoints were not removed.\n- The module keeps planned reports visible for roadmap tracking.\n","is_text_editable":1,"can_edit_inline":1}
{"file_name":"PAY_BILLS_ENHANCED_FEATURES.md","mime_type":"application/octet-stream"}
[]
{"backend_document_id":"18","document_type":"upload","title":"Open Code 04-16 Pay Bills changes","slug":"open-code-04-16-pay-bills-changes","summary_text":"Open Code 04-16 Pay Bills changes","content_markdown":null,"content_html":null,"file_name":"PAY_BILLS_ENHANCED_FEATURES.md","stored_name":"20260416-182019-18adea90.md","mime_type":"application/octet-stream","file_size_bytes":"8500","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260416-182019-18adea90.md","is_deleted":"0","created_at":"2026-04-16 14:20:19","updated_at":"2026-04-16 14:20:19","editor_content":"# Pay Bills - Enhanced Features Documentation\n\n**Last Updated:** April 16, 2026 \n**File:** `/backend/bills.php` \n**Server:** `kefa@192.168.7.202` \n**Data Location:** `/mnt/drive1/customerdb/`\n\n---\n\n## Overview\n\nThe Pay Bills page has been enhanced with new features for better bank account management and payment tracking visibility.\n\n---\n\n## New Features\n\n### 1. Bank Account Types\n\nEach bank account now has a **type classification**:\n\n| Type | Icon | Description |\n|------|------|-------------|\n| **Pay Bill** | 💳 | Used for paying bills - appears in bank dropdown |\n| **Show Balance** | 👁 | Track only - excluded from bill payment dropdowns |\n\n**Why this matters:** Accounts marked as \"Show Balance\" won't clutter the bill payment dropdown but still track their running balance.\n\n### 2. Bank URL (Popup Window)\n\nEach account can have a **Bank URL** that opens in a popup window.\n\n- Click the \"🌐 Bank\" button next to any account\n- Opens the URL in a 1200x800 popup window\n- Useful for quick access to bank login without leaving the bills page\n\n### 3. Bal After Pills (Status Badges)\n\nThe \"Bal After\" column now shows **color-coded pills**:\n\n| Status | Appearance | Meaning |\n|--------|------------|---------|\n| **Paid** | Green pill with ✓ | Bill amount has been paid |\n| **Due** | Red pill with ! | Still needs to be paid |\n| **—** | Gray pill | No amount entered |\n\n### 4. Save All Buttons\n\nTwo save buttons added for convenience:\n\n1. **Save All Balances** - In the bank accounts section\n2. **Save All Bills** - At the bottom of the bills table\n\nBoth show \"Saving…\" → \"Saved ✓\" feedback.\n\n### 5. Auto-Update\n\nAll changes auto-save after 600ms of inactivity (debounced).\n\n---\n\n## Database Schema Changes\n\n### New Columns in `bills_accounts` Table\n\n```sql\n-- MySQL / MariaDB\nALTER TABLE bills_accounts\nADD COLUMN account_type ENUM('pay_bill', 'show_balance') DEFAULT 'pay_bill' AFTER name,\nADD COLUMN bank_url TEXT DEFAULT '' AFTER account_type;\n\n-- SQLite\nALTER TABLE bills_accounts ADD COLUMN account_type TEXT DEFAULT 'pay_bill';\nALTER TABLE bills_accounts ADD COLUMN bank_url TEXT DEFAULT '';\n```\n\n### New Tables Created\n\nIf tables don't exist, the following are auto-created:\n\n```sql\nCREATE TABLE bills_accounts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n account_type TEXT DEFAULT 'pay_bill',\n bank_url TEXT DEFAULT '',\n sort_order INTEGER DEFAULT 0,\n created_at TEXT DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE bills_monthly (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n year INTEGER NOT NULL,\n month INTEGER NOT NULL,\n bills_account_id INTEGER,\n opening_balance REAL DEFAULT 0,\n UNIQUE(year, month, bills_account_id)\n);\n\nCREATE TABLE bills_items (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n default_due REAL DEFAULT 0,\n notes TEXT DEFAULT '',\n sort_order INTEGER DEFAULT 0,\n created_at TEXT DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE bills_entries (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n year INTEGER NOT NULL,\n month INTEGER NOT NULL,\n bills_item_id INTEGER NOT NULL,\n bills_account_id INTEGER DEFAULT 0,\n payment_due REAL DEFAULT 0,\n amount_paid REAL DEFAULT 0,\n is_paid INTEGER DEFAULT 0,\n paid_date TEXT DEFAULT '',\n UNIQUE(year, month, bills_item_id)\n);\n\nCREATE TABLE bills_copy_log (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n year INTEGER NOT NULL,\n month INTEGER NOT NULL,\n copied_from_year INTEGER NOT NULL,\n copied_from_month INTEGER NOT NULL,\n copied_at TEXT DEFAULT CURRENT_TIMESTAMP\n);\n```\n\n---\n\n## Deployment Instructions\n\n### Step 1: Upload the File\n\n**Option A: Direct Copy (if file is on local machine)**\n\n```bash\n# From local machine to server\nscp bills.php kefa@192.168.7.202:/var/www/floridaalterations.com/backend/bills.php\n```\n\n**Option B: If editing on server directly**\n\n```bash\nssh kefa@192.168.7.202\n# Then edit the file at:\n# /var/www/floridaalterations.com/backend/bills.php\n```\n\n### Step 2: Set Permissions\n\n```bash\nssh kefa@192.168.202\nchmod 644 /var/www/floridaalterations.com/backend/bills.php\nchown www-data:www-data /var/www/floridaalterations.com/backend/bills.php\n```\n\n### Step 3: Clear Cache (Optional)\n\n```bash\n# If using OPcache\nssh kefa@192.168.7.202\nphp -r \"opcache_get_status();\" 2>/dev/null && echo \"Consider: php-fpm restart\"\n```\n\n### Step 4: Access the Page\n\n```\nhttps://ella.floridaalterations.com/backend/bills.php\n```\n\n---\n\n## How to Use\n\n### Adding a New Account with Bank URL\n\n1. Click **\"+ Add Account\"** button\n2. Enter account name (e.g., \"Chase Checking\")\n3. Select account type:\n - **Pay Bill** - For accounts you'll pay bills from\n - **Show Balance** - For tracking only\n4. Enter Bank URL (e.g., `https://www.chase.com/login`)\n5. Click **\"Add Account\"**\n6. Enter the opening balance and click **\"Save Balances\"**\n\n### Editing an Existing Account\n\n1. Click **\"Edit\"** next to the account\n2. Update name, type, or URL\n3. Click **\"Save Changes\"**\n\n### Switching Account Type\n\n1. Edit the account\n2. Change the radio button selection\n3. Click **\"Save Changes\"**\n\n### Opening Bank in Popup\n\n1. Click the **\"🌐 Bank\"** button next to an account\n2. The bank URL opens in a new popup window\n\n### Marking a Bill as Paid\n\n1. Enter the **Paid ($)** amount (or it auto-calculates)\n2. The **Bal After** pill turns **green** with a ✓ checkmark\n3. Row background turns light blue to indicate paid status\n\n### Viewing Running Balance\n\n- The sidebar shows **Running Totals** for each pay-bill account\n- Updates automatically as you enter payments\n\n---\n\n## File Location Reference\n\n| Item | Server Path |\n|------|-------------|\n| bills.php | `/var/www/floridaalterations.com/backend/bills.php` |\n| Data directory | `/mnt/drive1/customerdb/` |\n| Database | `/mnt/drive1/customerdb/customer.db` |\n| Backup | `/mnt/drive1/customerdb/backup/` |\n| Logs | `/mnt/drive1/customerdb/logs/` |\n\n---\n\n## Troubleshooting\n\n### Database Connection Error\n\n```\nError: Connection failed\n```\n\n**Solution:**\n```bash\n# Check if MySQL is running\nssh kefa@192.168.7.202\nsudo systemctl status mysql\n# or\nsudo systemctl status mariadb\n```\n\n### Table Doesn't Exist\n\nIf you see errors about missing tables:\n\n```bash\n# The tables are auto-created on first page load\n# Just refresh the page once\n```\n\n### Bank URL Not Opening\n\n- Verify the URL includes `https://` or `http://`\n- Check browser popup blocker settings\n- Some banking sites block popup windows\n\n### Bal After Shows \"—\"\n\n**Causes:**\n1. No bank account selected for the bill\n2. Opening balance not saved for the account\n3. Account is marked as \"Show Balance\" type\n\n**Solution:**\n1. Select a bank account from the dropdown\n2. Save the opening balance for that account\n\n### Changes Not Saving\n\n1. Check browser console for JavaScript errors\n2. Verify network tab shows POST requests completing\n3. Clear browser cache and retry\n\n---\n\n## Quick Commands Reference\n\n```bash\n# Connect to server\nssh kefa@192.168.7.202\n\n# View current bills.php\ncat /var/www/floridaalterations.com/backend/bills.php | head -50\n\n# Backup bills.php before updating\ncp /var/www/floridaalterations.com/backend/bills.php /mnt/drive1/customerdb/backup/bills.php.$(date +%Y%m%d)\n\n# Upload new file\nscp bills.php kefa@192.168.7.202:/var/www/floridaalterations.com/backend/bills.php\n\n# Set correct permissions\nchmod 644 /var/www/floridaalterations.com/backend/bills.php\nchown www-data:www-data /var/www/floridaalterations.com/backend/bills.php\n\n# Check PHP error logs\nssh kefa@192.168.7.202 \"tail -50 /var/log/php*-fpm.log\"\n\n# Restart PHP-FPM if needed\nssh kefa@192.168.7.202 \"sudo systemctl restart php*-fpm\"\n```\n\n---\n\n## Security Notes\n\n- Bank URLs are stored as-is - ensure only trusted users can edit accounts\n- No sensitive data encrypted in the database\n- Consider HTTPS-only for all bank URLs\n- Session-based access control should be configured at the application level\n\n---\n\n## Future Enhancements (Suggested)\n\n1. **Auto-fetch balance** - API integration with banks (requires bank credentials)\n2. **Email notifications** - Remind when bills are due\n3. **Export to CSV** - Download bill history\n4. **Recurring bills** - Auto-create monthly bills\n5. **Budget tracking** - Set monthly budget limits per category\n\n---\n\n## Version History\n\n| Version | Date | Changes |\n|---------|------|---------|\n| 1.0 | April 16, 2026 | Initial release with account types, bank URLs, status pills |\n| - | - | Auto-save functionality |\n| - | - | Save All buttons added |\n\n---\n\n*End of Documentation*\n","is_text_editable":1,"can_edit_inline":1}
[]
{"target_database":"ellas_alterations_backup","tables":[{"source_table":"customers","backup_table":"customers__snapshot_20260411_151957_before_final_input","row_count":3177},{"source_table":"order_items","backup_table":"order_items__snapshot_20260411_151957_before_final_input","row_count":108},{"source_table":"orders","backup_table":"orders__snapshot_20260411_151957_before_final_input","row_count":992},{"source_table":"payments","backup_table":"payments__snapshot_20260411_151957_before_final_input","row_count":1275},{"source_table":"visits","backup_table":"visits__snapshot_20260411_151957_before_final_input","row_count":992}]}
[]
[]
{"target_database":"ellas_alterations_backup","tables":[{"source_table":"customers","backup_table":"customers__snapshot_20260410_171516","row_count":3174},{"source_table":"order_items","backup_table":"order_items__snapshot_20260410_171516","row_count":90},{"source_table":"orders","backup_table":"orders__snapshot_20260410_171516","row_count":979},{"source_table":"payments","backup_table":"payments__snapshot_20260410_171516","row_count":1260},{"source_table":"visits","backup_table":"visits__snapshot_20260410_171516","row_count":979}]}
[]
[]
[]
{"target_database":"ellas_alterations_backup","tables":[{"source_table":"customers","backup_table":"customers__snapshot_20260414_204542","row_count":3243},{"source_table":"visits","backup_table":"visits__snapshot_20260414_204542","row_count":1232},{"source_table":"orders","backup_table":"orders__snapshot_20260414_204542","row_count":1232},{"source_table":"order_items","backup_table":"order_items__snapshot_20260414_204542","row_count":394},{"source_table":"payments","backup_table":"payments__snapshot_20260414_204542","row_count":1315},{"source_table":"customer_totals","backup_table":"customer_totals__snapshot_20260414_204542","row_count":5668},{"source_table":"daily_customer_totals","backup_table":"daily_customer_totals__snapshot_20260414_204542","row_count":546},{"source_table":"customer_payment_transactions","backup_table":"customer_payment_transactions__snapshot_20260414_204542","row_count":2844},{"source_table":"customer_reports","backup_table":"customer_reports__snapshot_20260414_204542","row_count":10943},{"source_table":"visit_signatures","backup_table":"visit_signatures__snapshot_20260414_204542","row_count":70},{"source_table":"visit_activity_log","backup_table":"visit_activity_log__snapshot_20260414_204542","row_count":515},{"source_table":"setmore_appointments","backup_table":"setmore_appointments__snapshot_20260414_204542","row_count":145},{"source_table":"backend_no_show_log","backup_table":"backend_no_show_log__snapshot_20260414_204542","row_count":119},{"source_table":"square_customer_log","backup_table":"square_customer_log__snapshot_20260414_204542","row_count":31},{"source_table":"backend_job_runs","backup_table":"backend_job_runs__snapshot_20260414_204542","row_count":26}]}
[]
{"customer_payment_transaction_id":"5426","payment_id":"63373","order_id":"103787","visit_id":"129034","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-09 16:59:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.55","tip_amount":"0.55","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5412","payment_id":"63336","order_id":"103780","visit_id":"129027","customer_id":"488990","customer_name":"Angela Dixon","transaction_date":"2026-04-07 16:49:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"0.98","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5413","payment_id":"63337","order_id":"103777","visit_id":"129024","customer_id":"200484","customer_name":"Angela Dixon","transaction_date":"2026-04-07 16:52:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"0.70","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5414","payment_id":"63338","order_id":"103777","visit_id":"129024","customer_id":"200484","customer_name":"Angela Dixon","transaction_date":"2026-04-07 16:52:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.00","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5415","payment_id":"63340","order_id":"103758","visit_id":"129005","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-07 10:44:00","payment_method":"Square","payment_type":"Deposit","bucket":"credit","amount":"0.59","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5416","payment_id":"63341","order_id":"103758","visit_id":"129005","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-07 16:58:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.59","tip_amount":"0.59","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5417","payment_id":"63350","order_id":"103779","visit_id":"129026","customer_id":"488989","customer_name":"Julie Sahagun","transaction_date":"2026-04-07 11:04:00","payment_method":"Cash","payment_type":"Deposit","bucket":"cash","amount":"100.00","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5419","payment_id":"63356","order_id":"103785","visit_id":"129032","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-09 16:19:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"6.41","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5420","payment_id":"63357","order_id":"103785","visit_id":"129032","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-09 16:19:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.00","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5421","payment_id":"63360","order_id":"103784","visit_id":"129031","customer_id":"488991","customer_name":"Angela Dixon","transaction_date":"2026-04-09 16:40:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"3.20","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5422","payment_id":"63361","order_id":"103784","visit_id":"129031","customer_id":"488991","customer_name":"Angela Dixon","transaction_date":"2026-04-09 16:40:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.00","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5423","payment_id":"63366","order_id":"103781","visit_id":"129028","customer_id":"488991","customer_name":"Angela Dixon","transaction_date":"2026-04-09 16:53:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"3.34","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5424","payment_id":"63367","order_id":"103781","visit_id":"129028","customer_id":"488991","customer_name":"Angela Dixon","transaction_date":"2026-04-09 16:53:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"0.00","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
{"customer_payment_transaction_id":"5425","payment_id":"63372","order_id":"103787","visit_id":"129034","customer_id":"200483","customer_name":"Christian Sistoso","transaction_date":"2026-04-09 16:59:00","payment_method":"Square","payment_type":"Full Payment","bucket":"credit","amount":"2.73","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
[]
{"target_database":"ellas_alterations_backup","tables":[{"source_table":"customers","backup_table":"customers__snapshot_20260414_191633","row_count":3243},{"source_table":"visits","backup_table":"visits__snapshot_20260414_191633","row_count":1232},{"source_table":"orders","backup_table":"orders__snapshot_20260414_191633","row_count":1232},{"source_table":"order_items","backup_table":"order_items__snapshot_20260414_191633","row_count":394},{"source_table":"payments","backup_table":"payments__snapshot_20260414_191633","row_count":1315}]}
[]
{"customer_payment_transaction_id":"5418","payment_id":"63351","order_id":"103779","visit_id":"129026","customer_id":"488989","customer_name":"Julie Sahagun","transaction_date":"2026-04-07 11:04:00","payment_method":"Cash","payment_type":"Full Payment","bucket":"cash","amount":"32.68","tip_amount":"0.00","is_new_customer":"1","source":"payments","created_at":"2026-04-14 15:13:42","updated_at":"2026-04-14 15:13:42"}
{"deleted":true}
[]
[]
{"customer_date":"2026-04-07T19:12","customer_name":"Manual Entry Testing Square","dopu_can":"Pick Up","edwards":0,"edwards_type":"","update_flag":""}
[]
[]
{"customer_date":"2026-04-09T19:11","customer_name":"Edwards Delivery","dopu_can":"Edwards","edwards":150,"edwards_type":"other","update_flag":""}
[]
[]
[]
[]
{"customer_payment_transaction_id":"4958","payment_id":"63756","order_id":"104088","visit_id":"129335","customer_id":"489028","customer_name":"Tessa Sa","transaction_date":"2026-04-12 00:00:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"25.06","tip_amount":"25.06","is_new_customer":"0","source":"payments","created_at":"2026-04-14 14:02:33","updated_at":"2026-04-14 14:02:33"}
{"customer_payment_transaction_id":"4958","payment_id":"63756","order_id":"104088","visit_id":"129335","customer_id":"489028","customer_name":"Tessa Sa","transaction_date":"2026-04-12 00:00:00","payment_method":"Square","payment_type":"Tip","bucket":"tip","amount":"25.06","tip_amount":"0.00","is_new_customer":"0","source":"payments","created_at":"2026-04-14 14:02:00","updated_at":"2026-04-14 14:02:00"}
[]
{"content_item_id":"122","legacy_access_id":"65","slug":"headtotoesuitoverview","title":"A Tailor’s Guide: What Is The Head to Toe Suit Overview.","canonical_url":"link","short_url":null,"speakable_url":null,"status":"draft","workflow_state":"pending","review_count":"0","is_web_complete":"0","is_social_complete":"0","is_complete":"0","legacy_short_name":"HeadtoToeSuitOverview","legacy_short_name_1":"continue section \"Introduction: A Suit Is Not Just Clothing, It’s A System\" do not include the title, Provide a comprehensive 500-600 word overview \" , leaving no stone unturned and illuminating even the subtlest nuances, remember do not use the em-dash","legacy_short_name_2":"Introduction: A Suit Is Not Just Clothing, It’s A System\n\nSection 1: What Head to Toe Suit Fit Really Means\n\nSection 2: Why Tailors Look at the Whole Suit, Not One Wrinkle\n\nSection 3: Shoulders and Chest Are the Foundation of Structure\n\nSection 4: Waist","legacy_short_name_3":null,"source_batch_id":"4","source_row_number":"61","created_at":"2026-03-20 12:11:11","updated_at":"2026-03-24 09:18:36"}
{"content_item_id":"122","legacy_access_id":"65","slug":"headtotoesuitoverview","title":"A Tailor's Guide: What Is The Head to Toe Suit Overview.","canonical_url":"link","short_url":null,"speakable_url":null,"status":"draft","workflow_state":"pending","review_count":"0","is_web_complete":"0","is_social_complete":"0","is_complete":"0","legacy_short_name":"HeadtoToeSuitOverview","legacy_short_name_1":"continue section \"Introduction: A Suit Is Not Just Clothing, It’s A System\" do not include the title, Provide a comprehensive 500-600 word overview \" , leaving no stone unturned and illuminating even the subtlest nuances, remember do not use the em-dash","legacy_short_name_2":"Introduction: A Suit Is Not Just Clothing, It’s A System\n\nSection 1: What Head to Toe Suit Fit Really Means\n\nSection 2: Why Tailors Look at the Whole Suit, Not One Wrinkle\n\nSection 3: Shoulders and Chest Are the Foundation of Structure\n\nSection 4: Waist","legacy_short_name_3":null,"source_batch_id":"4","source_row_number":"61","created_at":"2026-03-20 12:11:11","updated_at":"2026-04-13 16:09:52"}
{"file_name":"END_OF_DAY_2026-04-13.md","mime_type":"application/octet-stream"}
[]
{"backend_document_id":"17","document_type":"upload","title":"Codex - End of day documentation 04-13-26","slug":"codex-end-of-day-documentation-04-13-26","summary_text":"Codex - End of day documentation 04-13-26","content_markdown":null,"content_html":null,"file_name":"END_OF_DAY_2026-04-13.md","stored_name":"20260413-200137-4e26a8e2.md","mime_type":"application/octet-stream","file_size_bytes":"11054","storage_path":"/mnt/drive1/customerdb/backend/documents_storage/20260413-200137-4e26a8e2.md","is_deleted":"0","created_at":"2026-04-13 16:01:37","updated_at":"2026-04-13 16:01:37","editor_content":"# End Of Day Documentation - April 13, 2026\n\n## Session Scope\nThis document captures the production fixes and feature work completed on **Monday, April 13, 2026** for Florida Alterations CustomerDB, focused on:\n\n- totals correctness (today, month, and year comparison)\n- rebuild operations from backend UI\n- visit save stability and data sync\n- range-to-unit pricing behavior\n- Square payment traceability and reporting\n- next-day email cron stability\n\nThis is the end-of-day technical handoff and restart guide for tomorrow.\n\n---\n\n## Executive Summary\nToday we stabilized and aligned core reporting with business rules:\n\n1. **Year Comparison fixed** to use full prior month totals (not prior-year MTD comparable day).\n2. **Today totals fixed** to use strict `payments.payment_date` logic for today pills.\n3. **Totals rebuild UI added** so customer/daily rebuild can be run from backend (no SSH needed).\n4. **Range pricing behavior fixed** so range text parses last amount into unit price only on blur (no typing interruption).\n5. **Square customer history implemented** (new table + logging + customer history page + visit button integration).\n6. **Next-day emails cron fixed** for schema variance (`status` column missing in `setmore_appointments`).\n\n---\n\n## Business Rules Locked In Today\n\n## 1) Year Comparison\n- Prior implementation used prior-year MTD comparable day and showed values like `3858.83` for April 2025 MTD.\n- New behavior for year comparison uses **full prior month** range (example: `2025-04-01` through `2025-04-30`), showing `10215.20` for April 2025 when daily totals table contains that total.\n- Comparable-day value is still provided in API as `last_year_mtd` for any future UI that still needs it.\n\n## 2) Today totals\n- `today_totals` now use **strict payment-date window**:\n - `payment_date >= today 00:00:00`\n - `payment_date < tomorrow 00:00:00`\n- This avoids inflated totals from `COALESCE(payment_date, NOW())` when historical rows have NULL payment_date.\n\n## 3) Range field behavior\n- Range text accepts values such as `100-250` or `$100 - $250`.\n- Last numeric value (`250`) is applied to `unit_price`.\n- Auto-fill now occurs only **on field blur** (loss of focus), not on every keystroke.\n\n## 4) Square logging (go-forward model)\n- No historical backfill was forced today.\n- Going forward, each Square save writes structured rows to a new log table.\n- Front desk can open customer-specific Square history from visit screen.\n\n---\n\n## Implemented Changes (Detailed)\n\n## A. Totals + Reporting API changes\n\n### Files updated\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### What changed\n1. Year comparison `last_year` switched from prior-year comparable-day MTD to prior-year **full month**.\n2. Added `last_year_mtd` payload key for optional MTD comparisons.\n3. Added strict today payment aggregation function for `today_totals` to avoid NULL-date inflation.\n\n### Outcome\n- Year comparison now aligns with accounting expectation for full month.\n- Today pills align with strict payment rows and expected values (example target discussed: cash `32.06`, credit including tips `424.25`).\n\n---\n\n## B. Backend rebuild tooling\n\n### File added\n- `backend/totals_rebuild.php`\n\n### File updated\n- `backend/index.php` (link added to Totals Rebuild page)\n\n### Mirror route added\n- `webui/backend/totals_rebuild.php` (thin loader to canonical backend page)\n\n### What changed\n- Added backend UI to run:\n - customer totals rebuild for date range\n - daily totals rebuild for date range\n- Added quick compare widget showing today payments vs daily table buckets.\n\n### Outcome\n- Rebuild operations no longer require SSH for normal operations.\n\n---\n\n## C. Customer totals date attribution correction\n\n### Files updated\n- `backend/customer_totals_sync_service.php`\n- `frontend/api/visit_save.php` (local totals sync path)\n\n### What changed\n- Removed logic that shifted `customer_totals.customer_date` to latest payment date.\n- Restored visit-date attribution to prevent old deposits being dragged into today.\n\n### Outcome\n- Eliminated recurring `$30` day-shift drift pattern in daily totals.\n\n---\n\n## D. Visit save and frontend stability improvements\n\n### Files updated\n- `frontend/api/visit_save.php`\n- `frontend/api/visit_delete.php`\n- `frontend/visit.html`\n\n### What changed\n- Added local totals sync helper flow in visit save to avoid include/redeclare fatal paths.\n- Ensured save updates:\n - payment ledger sync\n - `customer_totals` upsert for visit\n - daily totals rebuild for affected date\n- Fixed deposit behavior edge in visit form load/save path.\n\n### Outcome\n- Visit save is stable and returns valid JSON in known fatal edge paths previously reported.\n\n---\n\n## E. Next-day customer emails cron fix\n\n### File updated\n- `backend/jobs/send_nextday_customer_emails.php`\n\n### Problem\n- Cron failed with:\n - `Unknown column 'sa.status' in 'WHERE'`\n\n### Fix\n- Added schema-aware status resolution:\n - prefers `status` if present\n - falls back to `label` or `status_label` when available\n - if none present, job still runs without status predicate\n\n### Outcome\n- Job no longer hard-fails on column mismatch.\n\n---\n\n## F. Range parsing -> unit price behavior\n\n### Files updated\n- `frontend/visit.html`\n- `frontend/api/visit_save.php`\n- `backend/visit.php`\n- `backend/visit_service.php`\n\n### What changed\n- Implemented parser to extract last numeric token from range label.\n- Applies value to `unit_price`.\n- UI only triggers on blur to avoid interrupting typing.\n- Server-side also enforces same rule for consistency.\n\n### Outcome\n- Staff can enter flexible range text while keeping reliable unit pricing for repeat visits.\n\n---\n\n## G. Square reporting and customer history\n\n### Files added\n- `backend/square_payments_report.php`\n- `webui/backend/square_payments_report.php` (mirror route)\n- `frontend/api/square_customer_log.php`\n- `frontend/square_customer.html`\n\n### Files updated\n- `backend/index.php` (Square Details link)\n- `frontend/visit.html` (Square Customer button behavior)\n- `frontend/api/visit_save.php` (log writes + table creation + duplicate guard)\n\n### New table\n- `square_customer_log` (auto-created in visit save path)\n\n### Captured fields\n- customer/visit/order identifiers\n- customer name/phone/email snapshots\n- event_type/message\n- Square reference fields:\n - payment_id\n - receipt_number\n - checkout_id\n - card_brand\n - last_4\n- payment method/type\n- amount/tip/total\n- structured details JSON\n- created_at timestamp\n\n### Behavior\n- On Square save, log entries are written for customer.\n- Duplicate guard prevents repeated identical inserts from repeated save clicks.\n- Visit screen `Square Customer` button now opens local customer history page pre-filtered by current visit customer.\n- Customer page includes quick external Square Directory open button.\n\n### Outcome\n- Front desk has immediate traceability for Square transactions/messages per customer going forward.\n\n---\n\n## Verification Performed\n\n## Syntax checks completed\nUsing `C:\\php\\php.exe -l` on all modified/added PHP files in this session:\n\n- `backend/customer_totals_sync_service.php`\n- `frontend/api/visit_save.php`\n- `backend/index.php`\n- `webui/backend/totals_rebuild.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- `backend/jobs/send_nextday_customer_emails.php`\n- `backend/visit_service.php`\n- `backend/square_payments_report.php`\n- `webui/backend/square_payments_report.php`\n- `backend/visit.php`\n- `frontend/api/square_customer_log.php`\n\nAll lint checks passed with no syntax errors.\n\n## Data validation evidence reviewed\n- Workbook validation confirmed:\n - strict today rows matched expected payment set\n - prior-year MTD (`3858.83`) vs full-month (`10215.20`) mismatch source identified\n- Query results validated that full-month April 2025 totals are correct in both:\n - `daily_customer_totals`\n - `customer_totals`\n\n---\n\n## Operational Runbook (Current)\n\n## Rebuild UI\n- URL: `/backend/totals_rebuild.php`\n- Recommended range for live rebuild window:\n - `2026-04-10` to current date\n\n## Next-day emails dry-run\n```bash\nphp8.3 /mnt/drive1/customerdb/backend/jobs/send_nextday_customer_emails.php --dry-run\n```\n\n## Square detail report\n- URL: `/backend/square_payments_report.php`\n\n## Customer-specific Square history from visit\n- Visit page button: `Square Customer`\n- Opens local page:\n - `/square_customer.html?...`\n\n---\n\n## Open Items / Pending (Next Session)\n\n1. Confirm production deployment of all new files and mirror routes.\n2. Validate next-day cron in production after schema-aware status fix.\n3. Add optional backend module card for direct access to customer Square history page.\n4. Add optional import/backfill utility for `square_customer_log` from existing `visit_activity_log` for partial historical visibility (if desired).\n5. Monitor today/tomorrow totals for drift and confirm no reintroduction of payment-date shifting.\n6. Validate front-desk workflow on range blur behavior across both frontend and backend visit editors.\n\n---\n\n## Risks / Notes\n\n- Existing repository is heavily dirty with many unrelated changes; work was targeted to requested paths only.\n- Some backend pages are mirrored through `webui/backend` thin loaders; ensure deployment copies both canonical and mirror files where required.\n- Square log is go-forward by design; historical completeness is not guaranteed unless a dedicated backfill is added.\n\n---\n\n## Quick Diff Focus (Today’s primary touched paths)\n\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- `backend/totals_rebuild.php`\n- `webui/backend/totals_rebuild.php`\n- `backend/index.php`\n- `backend/customer_totals_sync_service.php`\n- `frontend/api/visit_save.php`\n- `frontend/api/visit_delete.php`\n- `frontend/visit.html`\n- `backend/visit.php`\n- `backend/visit_service.php`\n- `backend/jobs/send_nextday_customer_emails.php`\n- `backend/square_payments_report.php`\n- `webui/backend/square_payments_report.php`\n- `frontend/api/square_customer_log.php`\n- `frontend/square_customer.html`\n\n---\n\n## Status Summary\n\n- **DONE**\n - Year comparison full-month fix\n - Today strict payment-date totals fix\n - Backend totals rebuild UI + link + mirror route\n - Range blur-only auto-fill and parser\n - Next-day email schema mismatch fix\n - Square details report page\n - Square go-forward per-customer log (table + writes + API + page + visit button integration)\n\n- **VERIFIED**\n - Syntax checks pass on all changed PHP paths\n - Workbook query analysis validated root causes and corrected logic direction\n - Year comparison now aligned to full-month model\n\n- **BLOCKED / NOT COMPLETED TODAY**\n - Historical Square backfill into new log table (not done by design; go-forward only)\n - Broader production smoke test across all modules after deployment\n\n","is_text_editable":1,"can_edit_inline":1}
{"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}