candidate_offer_document

-1 rows


Description

Module: HRMS Recruitment & Onboarding
Purpose: Tracks the collection and verification status of all documents required from a candidate after a job offer is made.
Data: One row per required document type for a single job offer.
Process Usage:
- Populated when an offer is generated with a document checklist.
- Updated by candidates via the portal (uploading files) and by HR during verification.
- Used to gate the final “Ready to Join” status.
Key Points:
- Manages mandatory vs. optional documents.
- Stores file paths/links, verification status, and remarks.
- Can handle multiple file uploads for a single document type.
Business Impact: Critical for legal/compliance adherence, background verification, and ensuring a smooth, auditable onboarding process.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id serial 10 √ nextval('candidate_offer_document_id_seq'::regclass)

Primary key
Format: Integer (auto-increment)
Used as: Unique reference for a specific document requirement within an offer.
candidate_offer_id int8 19 null

FK: public.tbl_candidate_offer.id
Meaning: The specific job offer this document requirement belongs to.
Dependency: Core link to associate documents with a candidate and their offer terms.
document_type_id int8 19 null

Foreign key identifying the broad category of the document, such as “Identification”, “Education”, etc.
document_title_id int8 19 null

FK: public.tbl_document_title.id
Meaning: The specific title of the required document (e.g., “Passport”, “Degree Certificate”).
Logic: Links to a master table of predefined document titles for consistency.
label_for_candidate varchar 100 √ null
is_mandatory bool 1 √ null

Boolean flag for document requirement.
Logic:
- true: Candidate cannot complete onboarding without this document being verified.
- false: Optional/Good-to-have document.
verification_required bool 1 √ null

Boolean flag indicating if a manual check is needed.
Logic:
- true: Triggers a task for the HR verification team.
- false: Document is for record-keeping only, no approval workflow needed.
is_multiple_allowed bool 1 √ null
document_index int8 19 null
action_employee_id varchar 500 √ null
action_employee_time timestamp 29,6 √ null

Timestamp when the last action was taken by an HR employee. Stored as: UTC UI: Converts to user’s local timezone Format: 2025-10-07T13:20:50Z → 07 Oct 2025, 6:50pm (IST)
remarks varchar 100 √ null
status varchar 100 √ null

Enum: Current state of the document in the workflow.
- PENDING: Awaiting candidate submission.
- SUBMITTED: Candidate has uploaded the document; pending verification.
- VERIFIED: HR has approved the document.
- REJECTED: HR has rejected the document; requires re-submission.
Used for: Filtering dashboards for the verification team and updating candidate portal status.
final_remarks varchar 100 √ null

Free text for verifier comments.
Usage:
- Stores the reason for rejection if status is REJECTED.
- Can contain notes or observations from the HR verifier.
attachment varchar 255 √ null

Stores the reference to the uploaded file.
Format: Text (likely a URL or a system file path)
Example: s3://bucket-name/candidate-docs/offer-123/passport.pdf
Logic: The application backend uses this path to retrieve the document for viewing/download.
createdby varchar 500 √ null
createddate timestamp 29,6 √ null

Timestamp when this document requirement was first created. Stored as: UTC UI: Converts to user’s local timezone Format: 2025-10-07T13:20:50Z → 07 Oct 2025, 6:50pm (IST)
modifiedby varchar 500 √ null
modifieddate timestamp 29,6 √ null

Timestamp of the last update to this record.
Stored as: UTC
UI: Converts to user’s local timezone
Format: 2025-10-08T18:01:47Z → 08 Oct 2025, 11:31pm (IST)
form_data text 2147483647 √ null

Stores structured data associated with a document.
Format: JSONB or TEXT
Usage: For documents that are forms, this holds the submitted field data.
Example: {"passport_no": "Z567890", "expiry_date": "2030-12-31"}

Indexes

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

Relationships

Close relationships within degrees of separation