fix: SQL schema divergence between install.sql and install.mysql.sql #36

New Issue

2026-06-29T14:07:14Z

jmiller commented

2026-06-29 14:07:14 +00:00

Problem

Two SQL install files exist with different schemas for the same tables:

source/plugins/.../install.sql (older):

work_orders: uses site_address, site_lat/lng, work_type ENUM, labor_cost/parts_cost, missing category, payment_status, photos_before/after
technicians: skills VARCHAR(500), missing specialties JSON, certifications, license_number, rating, total_jobs, photo
equipment: missing condition_rating, last_service_date, next_service_date, service_address
checklists: has separate checklist_items table with item_type ENUM

source/packages/.../install.mysql.sql (newer, richer):

work_orders: uses service_address, service_lat/lng, category ENUM (plumbing/electrical/hvac…), payment_status, photos_before/after JSON, pm_agreement_id
technicians: specialties JSON, certifications, license_number, rating, total_jobs, photo
equipment: condition_rating ENUM, last_service_date, next_service_date, service_address
checklists: items JSON column (no separate items table)

Fix

Reconcile to a single authoritative schema (the install.mysql.sql version appears more complete)
Delete the older install.sql file once duplicate directories are cleaned up (#35)
Add sql/updates/ migration files for any schema changes needed for existing installations

## Problem Two SQL install files exist with different schemas for the same tables: **`source/plugins/.../install.sql`** (older): - `work_orders`: uses `site_address`, `site_lat/lng`, `work_type` ENUM, `labor_cost`/`parts_cost`, missing `category`, `payment_status`, `photos_before/after` - `technicians`: `skills` VARCHAR(500), missing `specialties` JSON, `certifications`, `license_number`, `rating`, `total_jobs`, `photo` - `equipment`: missing `condition_rating`, `last_service_date`, `next_service_date`, `service_address` - `checklists`: has separate `checklist_items` table with `item_type` ENUM **`source/packages/.../install.mysql.sql`** (newer, richer): - `work_orders`: uses `service_address`, `service_lat/lng`, `category` ENUM (plumbing/electrical/hvac…), `payment_status`, `photos_before/after` JSON, `pm_agreement_id` - `technicians`: `specialties` JSON, `certifications`, `license_number`, `rating`, `total_jobs`, `photo` - `equipment`: `condition_rating` ENUM, `last_service_date`, `next_service_date`, `service_address` - `checklists`: `items` JSON column (no separate items table) ## Fix - [ ] Reconcile to a single authoritative schema (the `install.mysql.sql` version appears more complete) - [ ] Delete the older `install.sql` file once duplicate directories are cleaned up (#35) - [ ] Add `sql/updates/` migration files for any schema changes needed for existing installations

jmiller commented

2026-06-29 14:07:16 +00:00

Branch created: feature/36-fix-sql-schema-divergence-between-instal

git fetch origin
git checkout feature/36-fix-sql-schema-divergence-between-instal

Branch created: [`feature/36-fix-sql-schema-divergence-between-instal`](https://git.mokoconsulting.tech/MokoConsulting/MokoSuiteField/src/branch/feature/36-fix-sql-schema-divergence-between-instal) ```bash git fetch origin git checkout feature/36-fix-sql-schema-divergence-between-instal ```

Sign in to join this conversation.

Priority Medium

Type Feature

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: MokoConsulting/MokoSuiteField#36