Data Dictionary PREMIUM

This data dictionary documents the database schema used in the Business Continuity Suite, based on the data synchronization patterns from your Banclo cloud platform. Understanding this structure helps you integrate effectively with the read-only API and webhook system.

Overview

The Business Continuity Suite database stores synchronized loan application data from your main Banclo platform. All entities use hashid identifiers for external references while maintaining internal numeric IDs for database relationships. The system supports hierarchical document management, real-time messaging, and comprehensive audit logging.

Core Entities

LoanRequest

The central entity representing a mortgage/loan application.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"AbC123Def"
applicationId	STRING	Unique application identifier from bank system	Yes	-	"APXY1"
status	STRING	Current application status	Yes	-	"PENDING", "APPROVED", "REJECTED"
isExclusive	BOOLEAN	Whether loan is exclusive to the bank or it came from the bazaar route	No	false	true
metadata	JSONB	Additional application metadata and configuration	No	{}
loanType	STRING	Type of loan being requested	No	"MORTGAGE"	"MORTGAGE", "PERSONAL", "BUSINESS"
principalAmount	DECIMAL	Requested loan amount	No	0	250000.00
currency	STRING	Currency code for the loan	No	"EUR"	"EUR", "USD", "GBP"
cancelComment	STRING	Reason for cancellation if applicable	No	null	"Customer withdrew application"
syncedAt	TIMESTAMP	Last synchronization timestamp	Yes	current	"2024-01-15T10:30:00Z"
syncVersion	INTEGER	Version number for sync tracking	Yes	1	5
createdAt	TIMESTAMP	Record creation timestamp	Yes	current	"2024-01-15T09:00:00Z"
updatedAt	TIMESTAMP	Last update timestamp	Yes	current	"2024-01-15T10:30:00Z"

Key Points:

• Status changes trigger webhook events (loan_request.status_changed)
• Stub records can be created during file sync and later populated
• Sync version increments with each update

---

LoanParticipant

People involved in the loan application process (clients, bank staff, professionals).

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"PRT_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
role	STRING	Participant's role in the process	Yes	-	"MAIN_APPLICANT", "LOAN_ASSIGNEE"
accessVia	ARRAY	How participant gained access	No	[]	["bankMember", "invitation"]
participantType	ENUM	Type of participant	Yes	-	"CLIENT", "USER", "PROFESSIONAL"
status	STRING	Participant status	No	"ACTIVE"	"ACTIVE", "INACTIVE"
firstName	STRING	Participant's first name	Yes	"Unknown"	"John"
lastName	STRING	Participant's last name	Yes	"User"	"Doe"
email	STRING	Email address	Yes	[email protected]	"[email protected]"
phone	STRING	Phone number	No	null	"+1234567890"
clientHashid	STRING	Reference to client record if type=CLIENT	No	null	"CLT_def456"
userHashid	STRING	Reference to user record if type=USER	No	null	"USR_ghi789"
permissions	JSONB	User permissions object	No	{}
company	STRING	Company name for professionals	No	null	"ABC Law Firm"
specialization	STRING	Professional specialization	No	null	"Real Estate Law"
licenseNumber	STRING	Professional license number	No	null	"LAW-12345"
metadata	JSONB	Additional participant information	No	{}

Key Points:

• Participant category is stored in metadata: main_applicant, co_applicant, bank_assignee, bank_member, external_professional
• Bank employees are identified by role or accessVia patterns
• Email serves as fallback identifier when other hashids are unavailable

---

Task

Work items assigned to participants in the loan process.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"TSK_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
loanBidHashid	STRING	Reference to associated loan bid	No	null	"BID_def456"
bankId	STRING	Bank identifier	No	null	"bank_123"
name	STRING	Task display name	Yes	-	"Upload Income Documents"
slug	STRING	URL-friendly task identifier	Yes	-	"upload_income_documents"
status	ENUM	Current task status	Yes	"PENDING"	"PENDING", "INPROGRESS", "SUBMITTED", "APPROVED", "COMPLETED"
type	STRING	Task type classification	No	null	"DOCUMENT_UPLOAD", "REVIEW"
assigneeHashid	STRING	Who the task is assigned to	No	null	"PRT_ghi789"
assigneeName	STRING	Display name of assignee	No	null	"John Doe"
assigneeType	ENUM	Type of assignee	No	null	"USER", "CLIENT", "PROFESSIONAL", "SYSTEM"
dueDate	TIMESTAMP	Task due date	No	null	"2024-01-20T23:59:59Z"
completedAt	TIMESTAMP	Completion timestamp	No	null	"2024-01-18T14:30:00Z"
data	JSONB	Task configuration and form data	No	{}
llmPrompts	JSONB	AI prompts for task processing	No	null
shortDescription	STRING	Brief task description	Yes	name	"Upload your income documents"
description	STRING	Detailed task description	Yes	name or fallback	"Please upload your recent pay stubs and tax returns"
metadata	JSONB	Additional task configuration	No	{}	{"hasUpload": true, "uploadParams":

Key Points:

• Status "SUBMITTED" triggers task.completed webhook
• Overdue tasks (past due date, not completed) trigger task.overdue webhook
• Slug format uses underscores for backend evaluation
• Task data contains form definitions and user responses

---

TaskResult

Results and outputs from completed tasks.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"TR_abc123"
taskHashId	STRING	Reference to parent task	Yes	-	"TSK_def456"
name	STRING	Result display name	Yes	-	"Income Statement Analysis"
type	STRING	Type of result	No	null	"DOCUMENT", "DATA", "AI_ANALYSIS"
status	STRING	Result processing status	No	null	"PROCESSED", "PENDING", "ERROR"
result	JSONB	Result data and file information	No	{}
filePath	STRING	Local file path if applicable	No	null	"/var/data/documents/doc_123.pdf"
metadata	JSONB	Additional result metadata	No	{}

Key Points:

• Result field preserves download information across syncs (path, downloadedAt, etc.)
• Content type and MIME type information stored in both result and metadata
• File path information maintained for local file access

---

LoanBid

Bank offers/bids on loan requests.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"BID_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
isActive	BOOLEAN	Whether bid is currently active	No	null	true
status	STRING	Bank's bid status	No	null	"PENDING", "ISSUED", "WITHDRAWN"
clientStatus	STRING	Client's response to bid	No	null	"PENDING", "ACCEPTED", "REJECTED"
principalAmount	DECIMAL	Bid amount	No	null	240000.00
totalPrincipalAmount	DECIMAL	Total amount across all facilities	No	null	250000.00
facilitiesConfig	JSONB	Configuration for loan facilities	No	null
config	JSONB	General bid configuration	No	{}
periods	JSONB	Payment period structure	No	[]	[{"period": 1, "payment": 1200}]
charges	JSONB	Associated fees and charges	No	{}
collaterals	JSONB	Required collateral information	No	[]	[{"type": "PROPERTY", "value": 300000}]
issuedOn	TIMESTAMP	When bid was issued	No	null	"2024-01-15T10:00:00Z"
acceptedOn	TIMESTAMP	When client accepted bid	No	null	"2024-01-16T14:30:00Z"
rejectedOn	TIMESTAMP	When client rejected bid	No	null	"2024-01-17T09:15:00Z"
guildHashid	STRING	Reference to associated communication guild	No	null	"GLD_def456"

Key Points:

• Client status changes to "ACCEPTED" trigger bid.accepted webhook
• Client status changes to "REJECTED" trigger bid.rejected webhook
• Multiple facilities can be associated with one bid

---

LoanFacility

Individual loan components within a bid (e.g., primary mortgage, equity line).

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"FAC_abc123"
loanBidHashid	STRING	Reference to parent loan bid	Yes	-	"BID_xyz789"
facilityType	STRING	Type of loan facility	Yes	-	"PRIMARY_MORTGAGE", "EQUITY_LINE"
principalAmount	DECIMAL	Facility amount	Yes	-	200000.00
config	JSONB	Facility-specific configuration	No	{}
periods	JSONB	Payment schedule for this facility	No	[]	[{"period": 1, "payment": 900}]
charges	JSONB	Fees specific to this facility	No	{}
collaterals	JSONB	Collateral for this facility	No	[]	[{"type": "PROPERTY", "value": 250000}]
displayOrder	INTEGER	Order for display purposes	No	0	1
isOptional	BOOLEAN	Whether facility is optional	No	false	true

Key Points:

• Facilities are components of loan bids
• Each facility can have its own terms and conditions
• Display order determines UI presentation sequence

---

DocumentSpace

Hierarchical containers for organizing loan documents.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"DS_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
name	STRING	Space display name	Yes	-	"Income Documents"
type	STRING	Space type classification	No	null	"FOLDER", "CATEGORY"
isDefault	BOOLEAN	Whether this is a default system space	No	false	true
parentSpaceHashid	STRING	Reference to parent space for hierarchy	No	null	"DS_parent123"
status	STRING	Space status	No	null	"ACTIVE", "ARCHIVED"
metadata	JSONB	Additional space configuration	No	{}

Key Points:

• Supports hierarchical organization (parent-child relationships)
• Spaces are created in hierarchy order (parents before children)
• Orphaned spaces are handled gracefully

---

DocumentSpaceItem

Items (documents) within document spaces.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"DSI_abc123"
spaceHashid	STRING	Reference to containing document space	Yes	-	"DS_xyz789"
documentType	STRING	Type of document	No	null	"PDF", "LOAN_DOCUMENT", "PUBLISHED_DOCUMENT"
documentHashid	STRING	Reference to actual document	Yes	-	"DOC_def456"
displayOrder	INTEGER	Order within the space	No	0	5
movedAt	TIMESTAMP	When item was moved to this space	No	null	"2024-01-15T11:00:00Z"
movedFromHashid	STRING	Previous space if item was moved	No	null	"DS_old789"
movedByHashid	STRING	Who moved the item	No	null	"PRT_user123"
metadata	JSONB	Additional item information	No	{}

Key Points:

• Tracks document movement between spaces
• Maintains audit trail of document organization changes
• Links to various document types in the system

---

Document Types Overview

The system manages three distinct types of documents, each serving different purposes in the loan workflow:

• LoanDocument: Original files uploaded by participants (clients, professionals) into document spaces. These are user-provided documents like pay stubs, bank statements, ID copies, etc.

• PublishedDocument: System-generated documents created by the Banclo platform, such as contracts, ESIS disclosures, loan agreements, and other official paperwork. These are produced from templates with loan-specific data.

• Request: Pending workflow items that will eventually result in PublishedDocuments. These represent approval workflows, document generation requests, or other processes that produce official documents once completed.

---

LoanDocument

Original documents uploaded by participants into document spaces.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"LD_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
path	STRING	Original file path from bank system	No	null	"/documents/2024/01/paystub.pdf"
filename	STRING	Original filename	Yes	-	"january_paystub.pdf"
fileExtension	STRING	File extension	No	null	".pdf"
fileSize	INTEGER	File size in bytes	No	null	245760
mimeType	STRING	MIME type of file	No	null	"application/pdf"
result	JSONB	Processing results and metadata	No	{}
createdBy	INTEGER	Reference to user who uploaded (nullable)	No	null	null
createdByParticipantId	INTEGER	Reference to participant who uploaded	No	null	5
createdByHashid	STRING	Hashid of uploader	No	null	"PRT_user123"
requiresSeparateDownload	BOOLEAN	Whether file needs separate download	No	false	true

Key Points:

• Links to FileStorage for download tracking
• Stores processing results from document analysis
• Maintains reference to uploading participant

---

PublishedDocument

Generated documents (contracts, disclosures, etc.).

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"PD_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
loanBidHashid	STRING	Reference to associated loan bid	No	null	"BID_def456"
documentSpaceHashid	STRING	Reference to containing document space	No	null	"DS_ghi789"
name	STRING	Document name	Yes	type + date fallback	"Mortgage Contract 2024-01-15"
type	STRING	Document type	Yes	"DOCUMENT"	"ESIS", "CONTRACT", "DISCLOSURE"
content	JSONB	Document content and data	No	{}	{"template": "contract_v1", "variables":
authorHashid	STRING	Who created the document	No	null	"PRT_bank123"
version	INTEGER	Document version number	No	1	2
status	STRING	Document status	No	"ACTIVE"	"ACTIVE", "DRAFT", "SIGNED"
generatedBy	STRING	What generated the document	No	null	"SYSTEM", "USER"
generatedAt	TIMESTAMP	When document was generated	No	null	"2024-01-15T12:00:00Z"
signedAt	TIMESTAMP	When document was signed	No	null	"2024-01-16T10:30:00Z"
expiresAt	TIMESTAMP	Document expiration	No	null	"2024-06-15T23:59:59Z"
metadata	JSONB	Additional document metadata	No	{}

Key Points:

• Generated from templates with variable data
• Can be associated with specific loan bids
• Supports versioning and lifecycle management

---

Request

Workflow requests for document approvals and processes.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"REQ_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
type	STRING	Request type	No	"DOCUMENT_REQUEST"	"DOCUMENT_REQUEST", "APPROVAL_REQUEST"
status	STRING	Current request status	No	"PENDING"	"PENDING", "APPROVED", "REJECTED", "COMPLETED"
workflowState	STRING	Current workflow state	No	"INITIATED"	"INITIATED", "UNDER_REVIEW", "FINAL_APPROVAL"
numApprovers	INTEGER	Number of required approvers	No	1	3
currentApproverIndex	INTEGER	Current approver in sequence	No	0	1
authorHashid	STRING	Who initiated the request	No	null	"PRT_bank123"
authorDetails	JSONB	Details about the author	No	{}
workflowHashid	STRING	Reference to workflow definition	No	null	"WF_def456"
workflowDetails	JSONB	Workflow configuration	No	{}
audience	JSONB	Who should see this request	No	[]	["PRT_approver1", "PRT_approver2"]
bankHashid	STRING	Associated bank	No	null	"BNK_xyz789"
metadata	JSONB	Additional request data	No	{}

Key Points:

• Status changes trigger specific webhooks (request.approved, request.rejected, etc.)
• Supports multi-step approval workflows
• Tracks approval progress through currentApproverIndex

---

Analysis

Financial analysis and calculations for loan requests.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"AN_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
type	STRING	Analysis type	No	null	"AFFORDABILITY", "RISK_ASSESSMENT"
status	STRING	Analysis status	No	null	"COMPLETED", "PENDING", "ERROR"
data	JSONB	Analysis input data	No	null
snapshot	JSONB	Analysis results snapshot	No	null
formulas	JSONB	Calculation formulas used	No	null
createdAt	TIMESTAMP	When analysis was created	No	null	"2024-01-15T13:00:00Z"
updatedAt	TIMESTAMP	Last update timestamp	No	null	"2024-01-15T13:30:00Z"

Key Points:

• Stores both input data and calculated results
• Formula definitions allow for recalculation
• Results preserved in snapshot for historical reference

---

Worksheet

Spreadsheet-like data for calculations and analysis.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"WS_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
name	STRING	Worksheet name	Yes	-	"Income Analysis Worksheet"
description	STRING	Worksheet description	No	null	"Detailed breakdown of applicant income"
worksheets	JSONB	Worksheet data and structure	No	{}	{"sheets": [{"name": "Income", "data": [[]]}]}
createdFrom	STRING	Template or source used	No	null	"template_income_v1"
createdAt	TIMESTAMP	Creation timestamp	No	null	"2024-01-15T14:00:00Z"
updatedAt	TIMESTAMP	Last update timestamp	No	null	"2024-01-15T14:30:00Z"

Key Points:

• Contains spreadsheet-like data structures
• Can be created from templates
• Supports multiple sheets within one worksheet

---

MaterialisedView

Cached/computed views of data for performance.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"MV_abc123"
loanRequestHashid	STRING	Reference to parent loan request	No	null	"LR_xyz789"
bankHashid	STRING	Associated bank	No	null	"BNK_def456"
data	JSONB	Cached/computed data	No	null	{"summary": {"total_income": 75000}}
lastEvaluatedAt	TIMESTAMP	When view was last computed	No	null	"2024-01-15T15:00:00Z"
analysisSnapshotHashid	STRING	Reference to analysis snapshot	No	null	"AN_ghi789"
worksheetHashid	STRING	Reference to source worksheet	No	null	"WS_jkl012"
evaluationMetadata	JSONB	Metadata about evaluation	No	{}
version	INTEGER	View version	No	null	3
metadata	JSONB	Additional view configuration	No	{}	{"viewRegistry": {"name": "IncomeView"}}

Key Points:

• Improves performance by caching computed results
• Links to source data (worksheets, analysis)
• Tracks when data was last refreshed

---

Guild

Communication groups for loan participants (Discord-like).

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"GLD_abc123"
loanRequestHashid	STRING	Reference to parent loan request	Yes	-	"LR_xyz789"
name	STRING	Guild display name	Yes	-	"Loan Application #123 Discussion"
icon	STRING	Guild icon/avatar	No	null	"🏠"
ownerHashid	STRING	Guild owner identifier	No	null	"PRT_bank123"
ownerType	STRING	Type of owner	No	null	"USER", "SYSTEM"
isArchived	BOOLEAN	Whether guild is archived	No	false	false

Key Points:

• Provides organized communication spaces for loan processes
• Each loan request can have associated communication guilds
• Supports archiving for completed loans

---

Channel

Communication channels within guilds.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"CH_abc123"
guildHashid	STRING	Reference to parent guild	Yes	-	"GLD_xyz789"
name	STRING	Channel name	Yes	-	"general"
topic	STRING	Channel topic/description	No	null	"General discussion about the loan"
isArchived	BOOLEAN	Whether channel is archived	No	false	false

Key Points:

• Channels organize conversations within guilds
• Support topics for context
• Can be archived independently of guilds

---

ChannelMember

Participants who can access specific channels.

Column	Type	Description	Required	Default	Example
channelHashid	STRING	Reference to channel	Yes	-	"CH_abc123"
loanParticipantHashid	STRING	Reference to loan participant	Yes	-	"PRT_def456"
role	STRING	Member role in channel	No	"MEMBER"	"MEMBER", "ADMIN", "MODERATOR"
joinedAt	TIMESTAMP	When member joined channel	No	current	"2024-01-15T16:00:00Z"
isActive	BOOLEAN	Whether membership is active	No	true	true
permissions	JSONB	Member-specific permissions	No	{}
metadata	JSONB	Additional member data	No	{}

Key Points:

• Maps loan participants to channel access
• Supports role-based permissions
• Members can be found by various identifiers (userHashid, clientHashid, email)

---

Message

Individual messages within channels.

Column	Type	Description	Required	Default	Example
hashid	STRING (Virtual)	External identifier for API references	Yes	-	"MSG_abc123"
channelHashid	STRING	Reference to parent channel	Yes	-	"CH_xyz789"
authorHashid	STRING	Message author identifier	Yes	-	"PRT_def456"
authorName	STRING	Display name of author	No	null	"John Doe"
authorEmail	STRING	Author's email address	No	null	"[email protected]"
authorType	STRING	Type of author	No	null	"CLIENT", "USER", "PROFESSIONAL", "SYSTEM"
content	TEXT	Message content (decrypted)	Yes	-	"I have uploaded the requested documents."
attachments	JSONB	File attachments to message	No	[]	[{"name": "document.pdf", "url": "/files/doc.pdf"}]
metadata	JSONB	Additional message data	No	{}

Key Points:

• Messages from clients/professionals trigger message.received webhook
• Content is stored decrypted for offline access
• Supports file attachments and rich metadata

---

Additional Tables

FileStorage

Tracks file download status and local storage information.

AuditLog

Comprehensive audit trail of all system actions and changes (last 30 days retained).

LoanComments

Structured comments and notes associated with loan requests.

---

Data Synchronization Notes

1. Hashid Usage: All external references use hashids to avoid exposing internal numeric IDs
2. Webhook Events: Key status changes trigger webhook notifications for real-time updates
3. Transaction Management: Critical operations use database transactions for consistency
4. File Management: Documents are queued for download and tracked through FileStorage
5. Hierarchy Support: Document spaces support parent-child relationships with proper ordering
6. Audit Trail: Comprehensive logging of all changes with 30-day retention
7. Offline Support: Data is fully synchronized for offline operation with conflict resolution

Version Information

This data dictionary reflects the schema as of the DataStorageService implementation and may be updated as the system evolves. All timestamps are stored in UTC format and webhook events are emitted asynchronously to avoid blocking sync operations.