Database Design vs. JSON Blueprint – Best Practice for Centralized Document Handling Across Bank Processes
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]
}
}
}