Hi everyone, We’re currently in the design phase of a large-scale Appian BPM implementation for a bank. Appian acts as the backend processor for multiple business domains — including Loan Origination, KYC, and more — with all front-end channels (like CRM) integrating via Kafka events.
To give context, here’s a simplified version of the incoming JSON payload from CRM to our Appian BPM:
Our Design Question We’re wondering whether our database design should mirror this JSON structure, or whether we should use a normalized model that separates document references into a central table. To scope the question further: We’re using a DMS (Document Management System), and every attachment UUID points to a file stored there. Documents can belong to any entity (Applicant, Collateral, Product Info, etc.), and multiple documents can be associated with each entity. Processes vary, and different document types may be required at various stages (requested from the CRM during the ongoing process).
Our Questions to the Community
1. In your Appian projects, do you follow the incoming JSON structure when designing your database schema — or do you normalize?
2. Do you recommend a centralized DOCUMENT_LOG model for document storage across multiple processes/entities?
3. How do you manage document associations when IDs (e.g., applicant_id) are only available after insert and we insert them at once?We appreciate any insights or architectural lessons from large-scale Appian use cases!{ "meta": { "process": "Retail.Murabaha.CarLoan.CreditReview", "eventType": "START_CREDIT_REVIEW", "source": "CRM", "destination": "BPM", "isSynchronously": false, "isAcknowledgment": true, "correlationId": "abc12345-0000-1111-2222-1234567890ab", "transactionId": "def67890-0000-2222-3333-0987654321ba", "submittedBy": 1936, "timestamp": "2024-10-31T10:15:30Z" }, "data": { "application": { "branchCode": "1010", "submissionDate": "2024-10-31T10:15:30Z", "requestedAmount": 10000.0, "currency": "JOD", "tenorMonths": 60, "gracePeriodMonths": 0, "annualProfitRate": 5.5, "dbr": { "standardRatio": 40, "excludingStandingLoans": 40, "includingPendingRequests": 40, "withJoalaVisa": 40, "guarantorDebtor": 40, "overIndebtedness": 40 }, "productInfo": { "fuelType": "1", "carStatus": "1", "newCarOwnerName": "Sara Mahmoud", "relationshipType": "None", "carBrand": "Toyota", "carType": "Sedan", "productionYear": 2022, "chassisNumber": "ABC123456XYZ", "insuranceType": "1", "expiryOfLicense": "2025-12-31", "extraComments": "No remarks", "totalPrice": 35000.000, "greenFinancing": true, "typeOfGreenFinancing": "text", "expectedOwners": [ { "excpectedCarOwnerName": "" } ], "currentOwners": [ { "ownershipIdentityNumber": "", "currentOwnerName": "Mohammad Ali", "currentOwnerStatus": "1", "isCurrentOwnerGuarantor": false, "safewatch": { "existInSafewatchList": true, "complianceDecision": "CSS007", "notes": "", "attachments": { "safewatch": "1e0a2ee5-9284-4380-ac6b-0548c817fd74", "compliance": "32378b57-3873-48f5-9859-2f27476fd73f" } }, "attachments": { "vehicleOwnershipProof": "42378b57-3873-48f5-9859-2f27476fd73f" } } ], "attachments": { "carLicenseAttachment": "ff135649-ddae-4f7f-acc7-4720e2c16aa7" } }, "collaterals": { "suggested": [ { "collateralNumber": 123456, "collateralAmount": 456.000, "collateralExpiryDate": "2020-01-15", "conditionDetails": "1", "Recommendations": "comments", "collateralType": 23, "collateralTypeDesc": "Salary Tranfers", "collateralLongName": "0", "collateralBriefName": "راتب", "attachments": [ { "collateralAttachment": "6d89e0f2-c03f-4d99-a983-6335f6632729" } ] } ], "recommended": [ { "collateralNumber": 123456, "collateralAmount": 456.000, "collateralExpiryDate": "2020-01-15", "conditionDetails": "1", "Recommendations": "comments", "collateralType": 23, "collateralTypeDesc": "Salary Tranfers", "collateralLongName": "0", "collateralBriefName": "راتب", "attachments": [ { "collateralAttachment": "6d89e0f2-c03f-4d99-a983-6335f6632729" } ] } ] }, "applicants": [ { "cif": 48001082, "personalInfo": { "fullNameEnglish": "Mohamed Ahmed Ali", "attachments": { "cbjConsent": "0c6f7cfd-87c0-4bb3-bf9c-ed76adc97e8f", "crifConsent": "1bbefd21-f07f-4e97-99b5-9a27a7761335", "cspdConsent": "1c5badcc-de50-4c97-9b82-4911addaca71" } }, "identityInfo": { "idNumber": "123456789", "attachments": { "idCopy": "5a919a5d-3a43-4b49-81fb-b8af9e4fc850" } }, "contactInfo": { "addressEnglish": "Amman, Queen Rania Street", "mobileNumber": "123456789" }, "workInfo": { "employerClassification": "2", "employerName": "Tech Corp" }, "financialInfo": { "salaryAmount": 500.000, "salaryDetails": [ { "monthYear": "01-2023", "amount": 500.000 }, { "monthYear": "02-2023", "amount": 500.000 } ], "attachments": { "salaryCertificates": "5a919a5d-3a43-4b49-81fb-b8af9e4fc850", "socialSecurityStatement": "0c6f7cfd-87c0-4bb3-bf9c-ed76adc97e8f", "soa6Month": "1bbefd21-f07f-4e97-99b5-9a27a7761335", "clearanceDetails": "1c5badcc-de50-4c97-9b82-4911addaca71" } }, "standingLoans": [ { "loanType": "Personal", "collaterals": [ { "collateralAmount": 456.000 } ] } ], "insuranceInfo": { "hasDiseases": false }, "crifInfo": { "creditCardOutstanding": 5000.000, "dbr": 0.3, "creditRiskLevel": "Low" }, "clearanceInfo": { "name": "أحمد خالد سعيفان" }, "safewatch": { "existInSafewatchList": true, "complianceDecision": "CSS007", "notes": "", "attachments": { "safewatch": "68661ab6-2573-4288-9a66-6a8f9e63454f", "compliance": "cc73516f-eff6-4713-8202-d0b2e5ddfa09" } } } ], "delegators": [//same as applicant json structure], "guarantors": [//same as applicant json structure] } }}
Discussion posts and replies are publicly visible
Generally Appian data structures are not aligned with the response we receive from external systems. The response is mapped into various Appian schemas which are generally simple and normalized!
Its alright to have a centralized Document log as it helps with preventing duplicates as well as easy retrieval. Only thing is indexing should be appropriately applied. Majorly it depends on use cases though to keep a one single source of truth for documents or keep them segregated in separate tables per categories. So good to think about long term scalability and performance along with application maintenance!
3. How do you manage document associations when IDs (e.g., applicant_id) are only available after insert and we insert them at once?
A clear though performance impacting way is to insert applicants one-by-one.
Alternatively you can create a correlation id in both document and applicant schema that can serve as temporary id until applicant_id available. It should be in both the tables. This needs to be done with caution even though its temporary and values like date, time, user etc can help create such a column as well. Later when applicant_id is there using this correlation id documents can be related with applicant_id .
Is it possible to include these correlation IDs while parsing JSON within Appian? Additionally, does inserting them one-by-one, without creating a temporary ID, impact performance, given that they are not considered to be a large number of rows?
If its not a large number of rows then you can update one entity row at a time. For correlation ID's yes, you can create that while parsing json into Appian data sets so that the values can be added to both Document_log and entity table
Would it be possible to share a simplified sample code snippet demonstrating how to parse a JSON payload into Appian data sets while dynamically appending a correlation ID to each record?
In your above payload I see you have a mobile number. That generally is unique to users, I have used that one in the below sample code to generate a correlation id.
a!localVariables( local!map: { { name: "Alice", age: 30, docId: 2345, mobileNumber: "123456789" }, { name: "Bob", age: 25, docId: 6755, mobileNumber: "776656789" } }, local!jsonPayload: a!toJson(local!map), /* Append correlation ID to each record */ local!enhancedData: a!forEach( items: a!fromJson(local!jsonPayload), expression: a!map( data: fv!item, correlationId: concat(fv!item.docId, fv!item.mobileNumber) ) ), /* Return the enhanced dataset */ local!enhancedData )