Data Model
Database schema and data relationships for MS Word Integration
👤 Aditya Naresh📅 Updated: Mar 18, 2026🏷️ reporting
Data Model
The MS Word Integration feature uses three primary database tables to manage external editing sessions, track file storage, and maintain report relationships.
Core Tables
External Report Edits
The main table tracking external editing sessions:
| Column | Type | Default | Nullable | Description |
|---|---|---|---|---|
id | bigint | auto_increment | No | Primary key |
word_file_path | longtext | '' | No | S3 path to the uploaded Word document |
pdf_file_path | longtext | '' | No | S3 path to the converted PDF document |
auth_token | longtext | - | No | JWT token for session authentication |
is_active | smallint | 0 | No | Flag indicating if the edit session is currently active |
created_at | bigint | - | No | Creation timestamp |
updated_at | bigint | - | No | Last update timestamp |
author_id | int | - | Yes | Reference to the lab user currently editing (FK to labUser.labUserId) |
lab_report_id | int | - | No | Reference to the associated lab report (FK to labReportRelation.labReportId) |
Indexes:
external_re_lab_rep_b50252_idxon (lab_report_id,author_id)
Key Fields:
word_file_path: S3 path to the uploaded Word documentpdf_file_path: S3 path to the converted PDF documentauth_token: JWT token for session authenticationis_active: Flag indicating if the edit session is currently activeauthor_id: Reference to the lab user currently editinglab_report_id: Reference to the associated lab report
Lab Report Relation
Links to the main lab report table (partial schema shown):
| Column | Type | Default | Nullable | Description |
|---|---|---|---|---|
labReportId | int | auto_increment | No | Primary key |
isPartialFill | int | - | No | Set to 1 when report is edited externally |
lastUpdated | datetime(6) | - | Yes | Timestamp of last modification |
completedTests | int | - | No | Set to 1 to indicate partial completion |
billId_id | int | - | Yes | Foreign key to billing table |
collectedSampleId_id | int | - | Yes | Foreign key to collected samples |
docForId_id | int | - | Yes | Foreign key to doctor information |
labId_id | int | 1 | No | Foreign key to lab information |
labUserId_id | int | - | Yes | Foreign key to lab user |
orgId_id | int | - | Yes | Foreign key to organization |
profileTestId_id | int | - | Yes | Foreign key to profile test |
reportFormatId_id | int | - | Yes | Foreign key to report format |
reportID_id | int | - | No | Foreign key to report definition |
reportMetaId_id | int | - | Yes | Foreign key to report metadata |
submittedBy_id | int | - | Yes | Foreign key to submitting user |
userDetailsId_id | int | - | Yes | Foreign key to patient details |
Relevant Fields for MS Word Integration:
isPartialFill: Set to 1 when report is edited externallycompletedTests: Set to 1 to indicate partial completionlastUpdated: Timestamp of last modification
Report Results
Stores individual test result values (used for placeholder data):
| Column | Type | Default | Nullable | Description |
|---|---|---|---|---|
valueId | int | auto_increment | No | Primary key |
profileTestId | int | - | No | Reference to the test profile |
value | longtext | - | No | The test result value |
order | int | - | No | Display order of the result |
index | int | - | No | Index position in results |
highlight | int | - | No | Flag for highlighting the result |
automatedValue | int | - | No | Flag indicating if value is automated |
dictionaryId_id | int | - | Yes | Foreign key to test dictionary (FK to testDictionary.id) |
reportForId_id | int | - | No | Foreign key to lab report (FK to labReportRelation.labReportId) |
Data Relationships
Placeholder Data Mapping
The system maps database fields to template placeholders using the following relationships:
| Placeholder | Source Table | Field Path | Description |
|---|---|---|---|
patient_name | userDetails | fullName | Patient's full name |
patient_id | userDetails | labUserId | Patient ID |
patient_gender | userDetails | sex | Patient gender |
patient_age | userDetails | age | Patient age |
patient_dob | userDetails | dateOfBirth | Date of birth |
test_name | allTests | testName | Test name |
test_code | allTests | testCode | Test code |
lab_name | labs | labName | Lab name |
lab_address | labs | labAddress | Lab address |
report_time | calculated | current_time | Current timestamp |
registration_time | billing | billTime | Bill creation time |
Session Management
Session States
- Created: Initial state when edit session is started
- Active: User is currently editing the document
- Inactive: Session has been ended or timed out
Concurrency Control
- Only one active session per lab report
- Authorship can be transferred using "Take Authorship" feature
- Sessions automatically expire after inactivity
File Storage
S3 Path Structure
ExternalReportEdit/
├── {patient_id}/
│ ├── {timestamp}_{patient_name}.docx
│ └── {timestamp}_{patient_name}.pdfFile Lifecycle
- Template Download: Original template fetched from S3
- Processing: Placeholders replaced, temporary file created
- Upload: Edited files uploaded to S3 with patient-specific paths
- Cleanup: Temporary files removed, final paths stored in database
Data Validation
- File Type Validation: Only
.docxand.pdffiles accepted - Size Limits: Configurable maximum file sizes
- Path Sanitization: S3 paths validated and sanitized
- Token Validation: JWT tokens verified for each API call