Error:
- column "created_at" does not exist
- There is a column named "created_at" in table "clpr_claims", but it cannot be referenced from this part of the query
Root Cause:
- existing_claim CTE only selected id and payload
- But INSERT query tried to use: (SELECT created_at FROM existing_claim)
- PostgreSQL couldn't find created_at in existing_claim CTE
Solution:
- Added created_at to existing_claim CTE SELECT clause
- Now created_at is available for use in INSERT query
Files:
- docs/SQL_CLAIMSAVE_UPSERT_SIMPLE.sql: Added created_at to existing_claim CTE
Problem:
- Multiple records created with same claim_id but different IDs
- Example: ID=0eb051ec... (correct) vs ID=b532b1b3... (duplicate)
- Different SQL queries used different approaches:
* Some used claim_id as UUID for ID (partial.claim_id_str::uuid)
* Others searched by payload->>'claim_id' and created new UUID if not found
Root Cause:
- SQL_CLAIMSAVE_UPSERT_SIMPLE.sql only searched by ID:
WHERE id = claim_id_str::uuid
- If record existed with different ID but same claim_id in payload,
it wasn't found and new record was created
Solution:
1. existing_claim now searches both by ID and payload->>'claim_id':
WHERE id = claim_id_str::uuid OR payload->>'claim_id' = claim_id_str
ORDER BY priority (ID match first), then updated_at DESC
2. INSERT uses ID from existing_claim if found:
COALESCE((SELECT id FROM existing_claim), partial.claim_id_str::uuid)
3. This ensures same record is always used, preventing duplicates
Files:
- docs/SQL_CLAIMSAVE_UPSERT_SIMPLE.sql: Fixed search logic and INSERT ID
Problem:
- When uploading files on Step 3, wizard_plan was reset to NULL
- wizard_plan is created on Step 2 (StepDescription) and saved by claimsave_primary
- form_get workflow on Step 3 doesn't receive wizard_plan again
- Old SQL was overwriting wizard_plan with NULL
Solution:
- Add 'existing_claim' CTE to read current payload from DB
- Modified all parsers to fallback to DB values if field not in incoming payload:
* wizard_plan_parsed - preserves generated wizard plan
* answers_prefill_parsed - preserves AI-generated prefill
* coverage_report_parsed - preserves coverage analysis
* ai_agent1_facts_parsed - preserves fact extraction
* ai_agent13_rag_parsed - preserves RAG analysis
* problem_description_parsed - preserves user description
Flow:
1. Step 2: User describes problem → claimsave_primary saves wizard_plan ✅
2. Step 3: User uploads files → form_get/claimsave preserves wizard_plan from DB ✅
File: docs/SQL_CLAIMSAVE_UPSERT_SIMPLE.sql
Next: Update n8n workflow 'form_get' node 'claimsave' with this SQL
Database changes:
- Add unified_id, contact_id, phone columns to clpr_claims table
- Create indexes for fast lookup by these fields
- Migrate existing data from payload to new columns
- SQL migration: docs/SQL_ALTER_CLPR_CLAIMS_ADD_FIELDS.sql
SQL improvements:
- Simplify claimsave query: remove complex claim_lookup logic
- Use UPSERT (INSERT ON CONFLICT) with known claim_id
- Always return claim (fix NULL issue)
- Store unified_id, contact_id, phone directly in table columns
- SQL: docs/SQL_CLAIMSAVE_UPSERT_SIMPLE.sql
Workflow enhancements:
- Add branch for form submissions WITHOUT files
- Create 6 new nodes: extract, prepare, save, redis, respond
- Separate flow for has_files=false in IF node
- Instructions: docs/N8N_FORM_GET_NO_FILES_INSTRUCTIONS.md
- Node config: docs/N8N_FORM_GET_NO_FILES_BRANCH.json
Migration stats:
- Total claims: 81
- With unified_id: 77
- Migrated from payload: 2
Next steps:
1. Add 6 nodes to n8n workflow form_get (ID: 8ZVMTsuH7Cmw7snw)
2. Connect TRUE branch of IF node to extract_webhook_data_no_files
3. Test form submission without files
4. Verify PostgreSQL save and Redis event
