PSA/docs/archive/qbo_forms_migration_plan.md

(Archived) QBO Invoice Sync Forms Migration Plan (for 20250509175818_add_qbo_invoice_sync_forms.cjs)

Status: Archived. Alga PSA no longer uses the legacy QBO workflow-based sync described by this document. The currently supported QuickBooks integration path is QuickBooks CSV (manual export/import) via the shared accounting export pipeline.

This document outlines the plan and details for the database migration that registers the System Forms and associated Task Definitions for the QuickBooks Online Invoice Sync workflow. This plan assumes that FormRegistry.getForm() will serve schemas as they are stored, without performing server-side resolution of named $refs within allOf constructs. Therefore, all schemas stored by this migration must be fully self-contained.

I. Prerequisites

1. system_workflow_form_definitions Table: This table must exist. If not created by a prior migration, this migration should create it.
   • Columns: definition_id (UUID PK), name (TEXT UNIQUE NOT NULL), description (TEXT), version (TEXT NOT NULL), status (TEXT NOT NULL), category (TEXT), tags (TEXT[]), json_schema (JSONB NOT NULL), ui_schema (JSONB), default_values (JSONB), created_by (UUID), created_at (TIMESTAMPTZ NOT NULL), updated_at (TIMESTAMPTZ NOT NULL).
2. system_workflow_task_definitions Table: This table must exist. If not created by a prior migration, this migration should create it.
   • Columns: task_type (TEXT PK), name (TEXT NOT NULL), description (TEXT), form_id (TEXT NOT NULL, references system_workflow_form_definitions.name), form_type (TEXT NOT NULL, should be 'system'), default_priority (TEXT), default_sla_days (INTEGER), created_at (TIMESTAMPTZ), updated_at (TIMESTAMPTZ), created_by (UUID).
3. Helper Functions for Schema Composition: The migration script will need JavaScript helper functions to compose final schemas from base definitions and extensions. Examples:
   • composeSchema(baseJsonSchema, extensionJsonSchema)
   • composeUiSchema(baseUiSchema, extensionUiSchema)
   • composeDefaultValues(baseDefaults, extensionDefaults)

II. Migration Script Logic (exports.up)

The up function will perform the following steps within a transaction:

1. Define Base Generic Form Schemas (as JavaScript Objects):

   • baseQboMappingErrorForm: For mapping errors.
   • baseQboLookupErrorForm: For lookup errors.
   • baseQboApiErrorForm: For QBO API errors.
   • baseWorkflowErrorForm: For general workflow errors.
   • Task 1 (Define Base Schemas): Fully define the jsonSchema, uiSchema, and defaultValues for these four base forms.

2. Define Specialized QBO Form Extensions (as JavaScript Objects): For each of the 10 specialized QBO task types, define the "extension" part of their schema – i.e., the properties, UI hints, and default values that are specific to them or override the base.

   • qbo-customer-mapping-lookup-error-form (extends baseQboMappingErrorForm)
   • secret-fetch-error-form (extends baseQboApiErrorForm)
   • qbo-mapping-error-form-specialized (extends baseQboMappingErrorForm - clarify if this name is final or if it's the same as the base)
   • qbo-item-lookup-failed-form (extends baseQboLookupErrorForm)
   • qbo-item-mapping-missing-form (extends baseQboMappingErrorForm)
   • qbo-invoice-no-items-mapped-form (extends baseQboMappingErrorForm)
   • qbo-sync-error-form (extends baseQboApiErrorForm)
   • workflow-execution-error-form (extends baseWorkflowErrorForm)
   • internal-workflow-error-form (extends baseWorkflowErrorForm)
   • Task 2 (Define Extensions): For each of the 10 forms, detail its specific jsonSchema additions/overrides, uiSchema additions/overrides, and defaultValues additions/overrides.

3. Compose and Register Specialized Forms into system_workflow_form_definitions: Iterate through the 10 specialized form definitions:

   • Use the helper functions to compose the finalJsonSchema, finalUiSchema, and finalDefaultValues from the appropriate base and the specific extension.
   • Insert a new record into system_workflow_form_definitions with:
     • name: The specialized form name (e.g., 'qbo-customer-mapping-lookup-error-form').
     • json_schema: The finalJsonSchema (fully self-contained).
     • ui_schema: The finalUiSchema.
     • default_values: The finalDefaultValues.
     • Other metadata: version, status, description, category, created_by, etc.
   • Task 3 (created_by Value): Determine the appropriate value for created_by for these system records.

4. Create/Update system_workflow_task_definitions: For each of the 10 specialized forms registered:

   • Insert/update a record in system_workflow_task_definitions.
   • task_type: The corresponding workflow task type string (e.g., 'qbo_sync_error').
   • name: A descriptive name for the task definition.
   • description: From the form definition.
   • form_id: The name of the specialized system form just registered.
   • form_type: Set explicitly to 'system'.
   • Other defaults like default_priority.
   • Handle conflicts on task_type (e.g., using .onConflict('task_type').merge()).

III. Migration Script Logic (exports.down)

The down function will perform the following steps within a transaction:

1. Delete system_workflow_task_definitions: Delete records for the 10 QBO task types.
2. Delete system_workflow_form_definitions: Delete records for the 10 specialized QBO form names.
3. (Optional) Drop tables if this migration created them and no other data relies on them.

IV. Unanswered Questions & Further Tasks

• Task 1 (Define Base Schemas): Provide complete jsonSchema, uiSchema, and defaultValues for:
  • baseQboMappingErrorForm
  • baseQboLookupErrorForm
  • baseQboApiErrorForm
  • baseWorkflowErrorForm
• Task 2 (Define Extensions): For each of the 10 specialized QBO forms, provide their specific schema "extension" parts relative to their base.
  • Example for qbo-customer-mapping-lookup-error-form:
    • extension.jsonSchema.properties: { algaCustomerId: { type: 'string', title: 'Alga Customer ID', readOnly: true }, guidance: { type: 'string', title: 'Guidance', default: 'Please check customer mapping in QBO settings.', readOnly: true } }
    • extension.jsonSchema.required: ['algaCustomerId'] (in addition to base's required)
    • extension.uiSchema: { 'ui:order': ['errorMessage', 'algaCustomerId', 'guidance', 'resolutionNotes', '*'] }
    • extension.defaultValues: { guidanceText: "Ensure the customer exists in QBO and is correctly mapped." }
  • ... (Repeat for all 10 forms) ...
• Task 3 (created_by Value): Decide on a consistent created_by value (e.g., a specific system user UUID, or null) for records inserted by migrations.
• Task 4 (Schema Composition Helpers): Review and refine the composeSchema, composeUiSchema, and composeDefaultValues JavaScript helper functions within the migration script to ensure they correctly merge all aspects of the schemas as intended (especially ui:order, nested objects, etc.).
• Task 5 (Table Creation): Confirm if system_workflow_form_definitions and system_workflow_task_definitions tables are created by this migration or a preceding one. Adjust up/down accordingly.
• Task 6 (Base Form Registration): Decide if the "base generic forms" should themselves be registered as separate entries in system_workflow_form_definitions or if they only exist as JS objects for composition within this migration.
• Task 7 (Form Name "qbo-mapping-error-form-specialized"): Clarify if "qbo-mapping-error-form-specialized" is a distinct form or if it refers to the general "qbo-mapping-error-form" which itself is composed. The task data implies "qbo-mapping-error-form" is the one being used and is composed. Ensure naming consistency.
• Task 8 (Verify qboInvoiceSyncWorkflow.ts): Confirm that qboInvoiceSyncWorkflow.ts correctly uses the taskType when calling create_human_task, and that these taskTypes will match the task_type entries created in system_workflow_task_definitions by this migration. No formId parameter should be passed from the workflow.

V. Conceptual Code for Migration (Illustrative)

// server/migrations/20250509175818_add_qbo_invoice_sync_forms.cjs

// Helper function for merging/composing schemas (simplified example)
function composeSchema(baseJsonSchema, extensionJsonSchema) {
  // ... implementation ...
}
function composeUiSchema(baseUiSchema = {}, extensionUiSchema = {}) {
  // ... implementation ...
}
function composeDefaultValues(baseDefaults = {}, extensionDefaults = {}) {
  // ... implementation ...
}

exports.up = async function(knex) {
  await knex.transaction(async (trx) => {
    // TODO: Implement table creation for system_workflow_form_definitions if needed (Task 5)
    // TODO: Implement table creation for system_workflow_task_definitions if needed (Task 5)

    // --- Define Base Schemas (Task 1) ---
    const baseQboMappingErrorForm = { jsonSchema: {/*...*/}, uiSchema: {/*...*/}, defaultValues: {/*...*/} };
    // ... other base forms ...

    // --- Define Specialized Form Extensions & Register (Task 2) ---
    const specializedFormsData = [
      {
        name: 'qbo-customer-mapping-lookup-error-form',
        taskType: 'qbo_customer_mapping_lookup_error',
        baseSchemaRef: baseQboMappingErrorForm,
        extension: { jsonSchema: {/*...*/}, uiSchema: {/*...*/}, defaultValues: {/*...*/} },
        description: 'Form for QBO customer mapping lookup errors.'
      },
      // ... other 9 forms ...
    ];

    for (const formData of specializedFormsData) {
      const finalJsonSchema = composeSchema(formData.baseSchemaRef.jsonSchema, formData.extension.jsonSchema);
      const finalUiSchema = composeUiSchema(formData.baseSchemaRef.uiSchema, formData.extension.uiSchema);
      const finalDefaultValues = composeDefaultValues(formData.baseSchemaRef.defaultValues, formData.extension.defaultValues);

      await trx('system_workflow_form_definitions').insert({
        name: formData.name,
        version: '1.0', status: 'ACTIVE', description: formData.description,
        json_schema: finalJsonSchema,
        ui_schema: finalUiSchema,
        default_values: finalDefaultValues,
        // created_by: (Task 3)
        created_at: new Date(), updatedAt: new Date()
      });

      await trx('system_workflow_task_definitions').insert({
        task_type: formData.taskType,
        name: `Handle ${formData.taskType}`, description: formData.description,
        form_id: formData.name,
        form_type: 'system',
        // created_by: (Task 3)
        created_at: new Date(), updatedAt: new Date()
      }).onConflict('task_type').merge();
    }
    // TODO: Decide on base form registration (Task 6)
  });
};

exports.down = async function(knex) {
  await knex.transaction(async (trx) => {
    // ... implementation based on specializedFormsData names and taskTypes ...
  });
};