tbl_employeeletters

-1 rows


Description

Module: HRMS Document Management
Purpose: Stores all employee-generated letters and documents with comprehensive tracking and acknowledgment capabilities.
Data: Each row represents a single letter/document generated for an employee, containing both HTML content and file-based documents.
Process Usage:
- Used for generating offer letters, appointment letters, salary slips, and other HR communications.
- Supports both HTML-based and DOCX template-based document generation.
- Enables employee acknowledgment workflow for critical documents.
Key Points:
- Dual storage approach: HTML content for immediate viewing and file storage for official documents.
- Comprehensive audit trail with creation/modification tracking.
- Flexible publishing controls for ESS portal visibility.
Business Impact: Central repository for all employee communications with legal compliance through digital acknowledgments.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_employeeletters_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each employee letter record.
employeeid varchar 50 √ null

FK: public.tbl_employee.employeeid
Meaning: The employee for whom this letter/document was generated.
Dependency: Links to employee master data for personalization and access control.
lettertemplateid int8 19 null
tbl_lettertemplate.id fk__tbl_emplo__lette__61a69f0a R

FK: public.tbl_lettertemplate.id
Meaning: Reference to the template used for generating this letter.
Usage: Determines the base structure and formatting rules applied during generation.
htmlcontent varchar 2147483647 √ null

Stores the fully rendered HTML content of the letter.
Format: HTML with inline CSS
Usage: Primary content used when useDocumentFile is false, displayed directly in browser.
Example: Salary structures, offer letters with dynamic data binding.
footerfrombottom varchar 50 √ null

Format: CSS measurement (px, cm, mm)
Purpose: Controls vertical positioning of document footer from bottom edge.
Usage: Fine-tunes page layout for consistent printing across devices.
headerfromtop varchar 50 √ null

Format: CSS measurement (px, cm, mm)
Purpose: Controls vertical positioning of document header from top edge.
Usage: Ensures proper header placement while avoiding content overlap.
header varchar 2147483647 √ null

Format: HTML content
Purpose: Custom header content applied to the document.
Usage: Company letterhead, logos, or standard header information.
footer varchar 2147483647 √ null

Format: HTML content
Purpose: Custom footer content applied to the document.
Usage: Page numbers, confidentiality notices, company contact information.
watermark varchar 100 √ null

Format: Text or image reference
Purpose: Watermark text/image to be overlaid on document.
Examples: “CONFIDENTIAL”, “DRAFT”, “APPROVED” or company logo as background.
status varchar 20 √ null

Workflow States: PENDING, NEED_TO_DISCUSS, HISTORY, DELETED, ACTIVE
Meaning:
- PENDING: Generated but not yet published/approved
- ACTIVE: Published and visible to employee
- HISTORY: Archived version, superseded by newer document
- DELETED: Soft-deleted record
- NEED_TO_DISCUSS: Requires HR review before publishing
publishoness bool 1 null

Flag Type: Boolean
True: Document is visible on Employee Self-Service portal
False: Document is hidden from employee view
Business Rule: Controls document visibility in employee’s document library.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
signature varchar 1000 √ null

Format: Base64 encoded image or digital signature data
Purpose: Stores employee’s digital signature when document is acknowledged.
Collection: Captured via signature pad or upload during acknowledgment process.
date date 13 √ null

Format: Timestamp
Purpose: Date and time when employee acknowledged the document.
Legal Significance: Establishes when the acknowledgment occurred.
location varchar 255 √ null

Format: String
Purpose: Geographic location from where acknowledgment was performed.
Collection: Typically captured via IP geolocation or user input.
name varchar 255 √ null
signature_name varchar 255 √ null

Format: String
Purpose: Full name as entered by employee during digital signing.
Legal Value: Represents the signatory’s identity for audit purposes.
ip varchar 255 √ null

Format: IPv4/IPv6 address
Purpose: IP address of the device used for acknowledgment.
Audit Value: Provides traceability for security and compliance.
requireacknowledgement bool 1 √ null

Flag Type: Boolean
True: Employee must digitally acknowledge this document
False: No acknowledgment required (read-only document)
Usage: Critical for legal documents like offer letters, policy agreements.
usedocumentfile bool 1 false

Flag Type: Boolean
True: Letter uses DOCX template stored in S3 (fileName, fileUrl)
False: Letter uses HTML content (htmlContent)
Business Logic: Determines the rendering method and storage mechanism for the document.
filename text 2147483647 √ null

Format: String
Pattern: EmployeeDocument/{employeeId}{documentType}{timestamp}.docx
Example: EmployeeDocument/FC580_offer_letter_1758265332392.docx
Storage: Amazon S3 bucket for document files.
fileurl text 2147483647 √ null

Format: Pre-signed S3 URL
Purpose: Secure, time-limited access to the document file in S3 storage.
Validity: Typically 1 hour from generation
Usage: Direct download link for DOCX-based documents.
salarystructurehtml text 2147483647 √ null

Indexes

Constraint Name Type Sort Column(s)
tbl_employeeletters_pk_tbl_employeeletters Primary key Asc id

Relationships

Close relationships within degrees of separation