Schema Findings & Redesign Notes
This is a living register of structural issues found while documenting the Zoho Creator schema — not a list of bugs, but patterns that make the current schema hard to reason about and that the new Prisma schema should deliberately not carry forward.
How to use this doc: every time a new form is documented (see Overview for the list), re-read it against the categories below and add any new instance you find to the relevant table, with the form + field name(s) as evidence. Don't remove old entries when the new schema is designed — mark them resolved instead, so the doc stays a record of what changed and why.
Forms audited so far: Claim_Form, Claims_Management_Form, Fleet, Jobs, Litigation, Vehicle_Movement, Tasks, Assessment_Audit, Demands, Add_Notes. Not yet audited: Users, Team, Team_Members, User_Activity (stubs, no content yet).
1. Duplicated fields instead of a join
The same conceptual data is re-entered on multiple forms instead of being stored once and related. Zoho Creator's lookup fields can't easily surface a related record's full panel in the UI, so the workaround was to copy the fields onto every form that needs to display them — several form docs even call this out explicitly ("Also appears in ... section below").
| Concept | Appears in | Notes |
|---|---|---|
| PRI status/date/location | Claims_Management_Form (Post Repair Inspection), Litigation (PRI), Assessment_Audit (Post Repair Inspection), Demands (read-only copy sourced from Litigation) | 4-way duplication of the same PRI lifecycle |
| Repair cost / parts cost / total repair cost | Claims_Management_Form (Repair Cost), Assessment_Audit (Demands section) | Two independently-entered "repair cost" numbers for what should be one claim |
| Assessment value / GST / ex-GST | Claims_Management_Form (Assessor Fees), Assessment_Audit (Demands section) | |
Baby seat costs (Baby_Seat, Baby_Seat_Rental_Installation, Cost_of_Damaged_Baby_Seat, New_Baby_Seat_Installation) | Assessment_Audit (Baby Seats), Demands (Claim Details) | Identical field set copy-pasted between forms |
Photos_Received / Date_Photo_s_Taken | Assessment_Audit — appears in both its own "Assessment Audit" section and its "Demands" section | Duplicated within the same form |
| Litigation's Management Assistance / Version & Diagram / Repair Timeline / AFCA sections | Demands carries read-only copies of all of these, sourced via Litigation_ID | Whole sections mirrored rather than joined |
Recommendation: these become single source-of-truth tables (e.g. one RepairAssessment, one PriInspection) referenced by FK from Claim, Litigation, and Demand — not re-entered per form.
2. One-to-many relationships modeled as fixed numbered slots
Zoho Creator lookup fields are single-valued, so a true one-to-many relation was faked by adding a fixed number of near-identical lookup fields.
| Form | Field(s) | Real cardinality | Hard cap |
|---|---|---|---|
Claim_Form | Add_Job_1 … Add_Job_7, PRI_Job_Record, Recto_Job_Record | Claim → Jobs (1:N) | 9 jobs per claim |
Claim_Form | At_Fault_Name/At_Fault_Address/... and its 1/2/3 suffixed duplicates (3 full copies of the at-fault-party block) | Claim → At-fault parties (1:N) | 3 at-fault parties per claim |
Recommendation: model as a real child table (Job.claimId, AtFaultParty.claimId) with no artificial cap.
3. Two parallel "who did this" mechanisms
Some fields reference Zoho's built-in Users module directly; others reference a separate custom User lookup form. Both represent "a staff member," used inconsistently depending on when the field was added.
| Example pair | Forms |
|---|---|
Assigned_To (Users built-in) vs Interested_Party1 (User) | Tasks |
File_Handler_CM (Users built-in) vs Validator (User) | Claims_Management_Form |
CCR_Agent (Users built-in) vs CCR_Agent_Lookup (User) — both present on the same form | Demands |
Recommendation: single User model in Prisma; no reason to preserve the built-in/custom split.
4. Workflow state spread across many independent status fields
Rather than one state machine per entity, each form has a dozen-plus independent status/stage fields that together represent "where is this job/claim right now" — easy for them to drift out of sync with each other.
| Form | Independent status/stage fields (non-exhaustive) |
|---|---|
Jobs | Stage1…Stage_8, Hire_Status1, Repair_Job_Stage, Delivery_Stage, Delivery_Stages, Rental_Job_Stage, Rental_Pickup_Only_Stage, Loan_Car_Swap_Stage, Current_Workflow, Current_Workflow_Stage, Movement_Status |
Claim_Form | Stella_Claim_Status, stella_state, Direct_Claim_Status, Claims_Management_Stage, Update_CC_Stage |
Claims_Management_Form | Repair_Status, Repair_Status1, Quote_Status, Assessment_Status, Validation_Status, PRI_Status, Parts_Lists_Status, On_Hold_Status, Previous_Stage, Previous_Status |
Recommendation: this is the highest-value redesign target — an explicit state machine (or at minimum one status enum + statusHistory table per entity) instead of 10+ parallel flags per record.
5. Cross-app lookups (Accident App ↔ Fleet/Logistics App boundary)
Many fields are prefixed logistics_app.X or test_claims_management_portal.X, meaning the lookup crosses a Zoho Creator application boundary — these forms don't all live in one app today.
| Form | Cross-app references to |
|---|---|
Claim_Form | logistics_app.Add_Job (×9), logistics_app.Add_Car, logistics_app.Vehicle_Purchase_Request |
Claims_Management_Form | logistics_app.Locations, logistics_app.Location_Sub_Location_V1, logistics_app.Cleaning_Service, logistics_app.Crash_Claim_HQ, logistics_app.Internal_Mechanical_locations, logistics_app.Customer_Vehicle_Locations, logistics_app.Baby_Seat_Company |
Fleet | test_claims_management_portal.User, test_claims_management_portal.Repairers, test_claims_management_portal.Claim_Form, test_claims_management_portal.Auto_Grab_API_Log, test_claims_management_portal.Free_Hire |
Jobs | test_claims_management_portal.Claim_Form (×7+), test_claims_management_portal.User, test_claims_management_portal.Repairers, test_claims_management_portal.Rectification |
Vehicle_Movement | test_claims_management_portal.User (×2) |
Demands | logistics_app.Tow_Truck_Usage_Log, logistics_app.Tow_Truck_Drivers, logistics_app.Add_Car |
Recommendation: decide explicitly whether Accident App and Fleet App become one Postgres schema with real FKs, or stay as separate services with an API/event boundary instead of direct cross-database lookups. This is an architecture decision, not just a migration detail — flag before schema design starts.
6. UI-only fields with no business meaning — drop, don't migrate
Zoho Creator layout/plumbing fields that exist purely to control the UI (screen refresh triggers, plaintext spacers, CSS injection). No redesign needed — just exclude from migration.
| Form | Fields |
|---|---|
Claim_Form | plain1, plain, css_style_plain, Refresh_Screen, Page_View |
Fleet | Refresh_Screen, Page_View |
Claims_Management_Form | Refresh_Screen |
Demands | Refresh_Screen, Page_View |
Jobs | Refresh_Screen |
Tasks | Refresh_Screen |
Add_Notes | Refresh_Screen |
7. Naming inconsistencies
Minor individually, but they compound — the same concept spelled differently across (and within) forms makes it hard to know if two fields are "the same thing."
File_NumbervsFile_Number1(both present on the same form inClaims_Management_FormandAssessment_Audit)StatusvsStatus1vsStageused interchangeably for claim/demand phase (Demands,Assessment_Audit)- A field literally named
Field_Name(Claim_Form,Claims_Management_Form) — a placeholder name that was never renamed Notestyped asautonumberonTasks— the name says notes, the type says ID; almost certainly a legacy renamed field, not a real noteYear_field— generic placeholder name (Claim_Form,Fleet,Assessment_Audit)
Recommendation: don't carry these names into Prisma fields even where the underlying data is kept — rename to their actual meaning during migration mapping.
Self-referencing relations (neutral — not necessarily a smell)
Legitimate self-joins exist and just need to be modeled as such in Prisma, not flagged as a problem:
Claim_Form.Related_Claims→Claim_FormFleet.Linked_Vehicles→Add_CarAssessment_Audit.Parent_Repair_Claim→Offer_Details