System
Unified timeline of data changes (saves, imports, deletes) and backend errors. Filter by type, area, severity, or search.
29 results
{"status":200,"duration_ms":95,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"status":200,"duration_ms":39,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"status":200,"duration_ms":3,"request_path":"/backend/api/sysadmin.php?action=logs_list&dir=customerdb","request_method":"GET"}
{"status":200,"duration_ms":26,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"status":200,"duration_ms":14,"request_path":"/backend/api/sysadmin.php?action=scripts_read&file=customerdb_backup.sh&source=drive1","request_method":"GET"}
{"status":200,"duration_ms":0,"request_path":"/backend/api/sysadmin.php?action=scripts_read&file=cleanup_old_backups.sh&source=drive1","request_method":"GET"}
{"status":200,"duration_ms":4,"request_path":"/backend/api/sysadmin.php?action=scripts_notify_save","request_method":"POST"}
{"status":200,"duration_ms":54,"request_path":"/backend/api/sysadmin.php?action=scripts_run","request_method":"POST"}
{"status":200,"duration_ms":13,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"status":200,"duration_ms":13,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"status":200,"duration_ms":9,"request_path":"/backend/sysadmin.php","request_method":"HEAD"}
{"status":200,"duration_ms":59961,"request_path":"/backend/api/sysadmin.php?action=scripts_run","request_method":"POST"}
{"status":200,"duration_ms":16,"request_path":"/backend/api/sysadmin.php?action=scripts_read&file=cleanup_old_backups.sh&source=drive1","request_method":"GET"}
{"status":200,"duration_ms":5,"request_path":"/backend/api/sysadmin.php?action=scripts_notify_save","request_method":"POST"}
{"status":200,"duration_ms":4,"request_path":"/backend/api/sysadmin.php?action=backups_list&dir=backups","request_method":"GET"}
{"status":200,"duration_ms":12,"request_path":"/backend/api/sysadmin.php?action=scripts_read&file=copy_documents.sh&source=drive1","request_method":"GET"}
{"status":200,"duration_ms":5,"request_path":"/backend/api/sysadmin.php?action=scripts_list","request_method":"GET"}
{"status":200,"duration_ms":14,"request_path":"/backend/api/sysadmin.php?action=jump_links_list","request_method":"GET"}
{"status":200,"duration_ms":32,"request_path":"/backend/api/sysadmin.php?action=logs_read&dir=customerdb&file=backup.log&lines=500","request_method":"GET"}
{"status":200,"duration_ms":13,"request_path":"/backend/api/sysadmin.php?action=logs_read&dir=customerdb&file=reminder.log&lines=500","request_method":"GET"}
{"status":200,"duration_ms":0,"request_path":"/backend/api/sysadmin.php?action=logs_list&dir=customerdb","request_method":"GET"}
{"status":200,"duration_ms":2,"request_path":"/backend/api/sysadmin.php?action=logs_list&dir=customerdb","request_method":"GET"}
{"status":200,"duration_ms":20,"request_path":"/backend/sysadmin.php","request_method":"GET"}
{"file":"/mnt/drive3/customerdb/backend/api/sysadmin.php","line":478,"severity_code":2}
{"file":"/mnt/drive3/customerdb/backend/api/sysadmin.php","line":438,"severity_code":2}
{"file_name":"2026-04-25-codex-changes.md","mime_type":"application/octet-stream"}
[]
{"backend_document_id":"25","document_type":"upload","title":"Codex - Documentation 04-25-2026","slug":"codex-documentation-04-25-2026","summary_text":"2026-04-25-codex-changes.md\r\nIt includes:\r\n\r\neverything created/updated/removed\r\ndatabase schema objects added\r\nDB data changes performed\r\nAPI endpoints/actions touched\r\nprocedures/functions and scripts added/changed\r\ncron/notification behavior\r\nvalidation performed today","content_markdown":null,"content_html":null,"file_name":"2026-04-25-codex-changes.md","stored_name":"20260425-182957-0b251cd7.md","mime_type":"application/octet-stream","file_size_bytes":"6092","storage_path":"/mnt/drive3/customerdb/backend/documents_storage/20260425-182957-0b251cd7.md","is_deleted":"0","created_at":"2026-04-25 18:29:57","updated_at":"2026-04-25 18:29:57","editor_content":"# CustomerDB Changes Log\nDate: 2026-04-25\nAuthor: Codex\n\n## Scope\nThis document summarizes all work completed today in this session, including created/updated/removed files, database schema objects introduced by code, API behaviors touched, and operational procedures/scripts added.\n\n## 1) Files Created\n- `backend/search_data_service.php`\n- `backend/search_data.php`\n- `webui/backend/search_data.php` (compatibility loader)\n- `webui/backend/search_data_service.php` (compatibility loader)\n- `/mnt/drive1/scripts/cleanup_customerdb_branches.sh` (runtime ops script; outside repo tree)\n\n## 2) Files Updated\n- `backend/module_service.php`\n- `backend/index.php`\n- `backend/page_helpers.php`\n- `webui/settings.html`\n- `backend/api/sysadmin.php`\n- `backend/sysadmin.php`\n- `backend/jobs/cron_runtime_service.php`\n- `backend/cron_manager.php`\n- `backend/search_data.php` (restyling updates for spreadsheet-like sections)\n- `backend/search_data_service.php` (provider detection/compat fixes)\n\n## 3) Files Removed\n- None.\n\n## 4) Database Schema Objects Added (by code)\nImplemented in `backend/search_data_service.php` via `app_install_search_data_schema(...)`:\n\n### Tables\n1. `backend_search_dataset`\n2. `backend_search_import_batch`\n3. `backend_search_dataset_field`\n4. `backend_search_data_row`\n\n### Indexes / Constraints (high level)\n- Unique dataset key by provider (`provider_name`, `dataset_key`)\n- Import batch indexes by dataset/status\n- Unique field key per dataset\n- Unique row identity hash per dataset\n- Analytical indexes on date/clicks/impressions\n\n## 5) Database Data Changes Performed Today\n- A cron job row was inserted in `cron_job`:\n - Label: `Cleanup Repo Branch Folders`\n - Schedule: `20 3 * * *`\n - Command: `bash /mnt/drive1/scripts/cleanup_customerdb_branches.sh`\n - Enabled: `1`\n- Search data smoke import was executed once in this environment for validation, which inserted dataset/import/data rows in the new search tables.\n\n## 6) New/Updated Backend Module\n### Module: Search Data Imports (`Bing / Google CSV + AI`)\n- New page: `backend/search_data.php`\n- New service: `backend/search_data_service.php`\n- Registered in backend module overview and dashboard card.\n- Added to module jump links and settings module visibility list.\n\n### Functional capabilities added\n- Adaptive CSV import for Google/Bing (and unknown/future providers).\n- Header evolution tracking (new columns are cataloged without code changes).\n- Batch import auditing (rows seen/inserted/updated/skipped).\n- Query context construction from imported tables.\n- Ollama-based AI question answering over imported data using existing settings:\n - `ollama_url`\n - `ollama_default_model`\n\n## 7) API Surface and Call Behavior Changes\n## Updated endpoint\n- `backend/api/sysadmin.php`\n\n### Updated actions\n1. `action=backups_list`\n - Added new `dir` key support:\n - `repo-branches` => `/mnt/drive3/customerdb_repo/branches`\n2. `action=backups_delete`\n - Previously file-only delete.\n - Now supports both file and folder delete.\n - Added safe recursive folder deletion with path-boundary checks.\n - Response now includes:\n - `deleted_type` (`file` or `folder`)\n\n### Existing actions used by UI (not newly created today, but part of touched API flow)\n- `logs_list`, `logs_read`, `logs_clear`\n- `scripts_list`, `scripts_read`, `scripts_save`, `scripts_run`, `scripts_notify_save`\n- `backups_list`, `backups_delete`\n\n## 8) Sysadmin UI Changes\nUpdated `backend/sysadmin.php`:\n- Backups tab now includes:\n - `customerdb_repo/branches/` source (`data-backupdir=\"repo-branches\"`)\n- Directory rows now include a `Delete Folder` button.\n- Delete handler updated to support file vs folder confirmation text.\n\n## 9) Cron / Ops Procedure Changes\n## Updated cron runtime service\nUpdated `backend/jobs/cron_runtime_service.php`:\n- Added constants:\n - `APP_BRANCH_CLEANUP_SCRIPT_PATH`\n - `APP_BRANCH_CLEANUP_TARGET_PATH`\n- Added helper:\n - `app_cron_ensure_branch_cleanup_script()`\n- `app_cron_migrate_command_paths(...)` now:\n - Ensures command normalization for `Cleanup Repo Branch Folders`\n - Ensures cleanup script exists on disk\n\n## Updated cron manager seeding\nUpdated `backend/cron_manager.php`:\n- Added seeded/ensured job:\n - `Cleanup Repo Branch Folders`\n - `20 3 * * *`\n - `bash /mnt/drive1/scripts/cleanup_customerdb_branches.sh`\n\n## Script created\n`/mnt/drive1/scripts/cleanup_customerdb_branches.sh`\n- Deletes first-level folders older than 5 days under:\n - `/mnt/drive3/customerdb_repo/branches`\n- Emits clear stdout status suitable for cron logs/notifications.\n\n## Notifications\nNo new notification channels were added.\nThe new cron job uses existing cron execution + notification flow:\n- Telegram (depending on settings)\n- Email (depending on settings)\n\n## 10) Jump-To-Module Improvements\nUpdated `backend/page_helpers.php`:\n- Jump dropdown list now sorted A-Z by label.\n- List expanded to include missing backend destinations.\n- Corrected invalid links for queue pages to existing routes:\n - `/upcoming.html`\n - `/workload.html`\n\n## 11) Styling/UI Updates Requested\nUpdated `backend/search_data.php`:\n- For sections:\n - `CSV Files`\n - `Dataset Totals`\n - `Recent Imports`\n- Removed rounded corners and applied spreadsheet-like flat table style (Excel-like grid presentation).\n\n## 12) Compatibility / Robustness Fixes Applied\nIn `backend/search_data_service.php`:\n- Added CSV parsing compatibility for `fgetcsv` escape parameter.\n- Replaced `mb_substr` with `substr` in one path to avoid missing extension errors in this environment.\n- Improved provider detection rules for Google export naming patterns:\n - `KeywordReport`\n - `PageTrafficReport`\n - `SearchPerformanceOverview`\n\n## 13) Validation Performed\n- PHP syntax checks (`php -l`) passed for all modified PHP files.\n- Search data smoke import executed successfully in this environment.\n- Verified script path existence after direct creation:\n - `/mnt/drive1/scripts/cleanup_customerdb_branches.sh`\n\n## 14) Notes\n- Existing unrelated local repo changes were not reverted.\n- No destructive git operations were used.\n","is_text_editable":1,"can_edit_inline":1}
{"type":"UnexpectedValueException","file":"/mnt/drive1/customerdb/webui/backend/api/sysadmin.php","line":290,"trace":"#0 /mnt/drive1/customerdb/webui/backend/api/sysadmin.php(290): DirectoryIterator->__construct()\n#1 {main}"}
{"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}