Handover workspace

ERS, Todo, OfferReview, and Docu in one view

Imported from live server docs, code structure, and deployment notes.

Jul 30, 2026, 10:27 AM

ERS

Offer Letter and Renewal System — v0.1 Spec (Code-Ready)

**Version:** 0.1.0 **Stack:** Postgres + Next.js (App Router) + VPS (Hostinger) + local filesystem uploads **Auth:** Email + Password (no 2FA) **Notifications:** In-app + Email **Audit Log:** Included **Admin Settings:** Included **Current release identity:** Login, sidebar, About, and System Health show generated build metadata **Current implementation addendum:** 2026-07-14 analytics and AI extensions

docs/specs/ERS-v0.1-spec.md

Updated Jul 14, 2026, 7:17 AM

Offer Letter and Renewal System — v0.1 Spec (Code-Ready)

Version: 0.1.0
Stack: Postgres + Next.js (App Router) + VPS (Hostinger) + local filesystem uploads
Auth: Email + Password (no 2FA)
Notifications: In-app + Email
Audit Log: Included
Admin Settings: Included
Current release identity: Login, sidebar, About, and System Health show generated build metadata Current implementation addendum: 2026-07-14 analytics and AI extensions

Current naming note:

  • Product name is now Offer Letter and Renewal System.
  • Historical filenames retain the original ERS-v* labels for traceability.

1) Goal

Internal system to manage employee offer letters and contract renewals:

  • HR prepares renewal and new-hire offer-letter cases
  • Manager submits renewal justification when requested
  • CEO reviews, compares compensation vs peers (same grade) for renewals, then approves/rejects
  • CEO can digitally sign the offer letter by placing a signature onto the PDF
  • CEO can analyze compensation position, salary increases, budget impact, and decision risks
  • AI can suggest signature placement and compare the contract with previous signed contracts

Primary outcomes:

  • Track offer-letter cases, renewals, salary, grade
  • Compare salary vs peers at same grade
  • Capture justification and decision
  • Full audit trail
  • Management-grade data quality and CSV export controls

2) Roles & Permissions (RBAC)

Roles

  • CEO (Admin): full access; final decision; signature management
  • HR: create and manage renewal and new-hire cases, upload docs, request manager justification, email notifications
  • Manager: respond to assigned justification requests; view assigned packets

Permission Rules (hard)

  • HR & Manager cannot edit a case after CEO decision (locked_at set).
  • After CEO signs the offer (status=SIGNED) or marks a signed offer declined (status=DECLINED), case is closed for all roles (read-only).
  • Renewal CEO decision requires:
    • Latest OFFER_LETTER exists
    • Real PerformanceSnapshot exists with a review period
    • ManagerJustification has submitted_at
  • New-hire CEO decision requires:
    • Latest OFFER_LETTER exists
  • CEO can access /dashboard and salary analytics; HR is redirected to /hr/renewals.
  • Managers cannot access management analytics or its CSV export.
  • AI contract results are advisory and do not bypass approval or signing permissions.

3) Key Entities (Data Model)

3.1 User

  • id (uuid)
  • email (unique)
  • name
  • role (enum: CEO | HR | MANAGER)
  • password_hash
  • is_active (bool default true)
  • created_at, updated_at

3.2 Employee

  • id (uuid)
  • employee_code (unique, optional)
  • full_name
  • email (unique, optional)
  • department
  • role_title
  • grade (free text e.g. "G5")
  • manager_user_id (fk users.id)
  • employment_type (enum: PERMANENT | CONTRACT)
  • status (enum: ACTIVE | INACTIVE)
  • created_at, updated_at

3.3 RenewalCase

  • id (uuid)
  • employee_id (fk)
  • contract_start_date, contract_end_date
  • renewal_start_date, renewal_end_date
  • current_monthly_salary (numeric)
  • proposed_monthly_salary (numeric)
  • currency (default "MYR")
  • proposed_grade (string nullable) // free text; if null use employee.grade
  • status (enum):
    • DRAFT
    • PENDING_MANAGER
    • READY_FOR_CEO
    • APPROVED
    • REJECTED
    • SIGNED
    • DECLINED
  • justification_request_sent_at (timestamptz nullable)
  • manager_justification_due_at (timestamptz nullable)

CEO decision:

  • ceo_decision (enum: NONE | APPROVE | REJECT)
  • ceo_decision_at (timestamptz nullable)
  • ceo_decision_notes (text nullable)
  • offer_declined_at (timestamptz nullable)
  • offer_decline_notes (text nullable)
  • offer_decline_recorded_by_user_id (fk users.id nullable)

Locking:

  • locked_at (timestamptz nullable)

Meta:

  • created_by_user_id (fk users.id)
  • created_at, updated_at

Implementation note:

  • Current implementation also uses RenewalCase for new-hire offer-letter approvals.

3.4 PerformanceSnapshot (per RenewalCase)

  • id (uuid)
  • renewal_case_id (fk)
  • review_period (string e.g. "2025 H2")
  • overall_score (numeric nullable)
  • score_breakdown (jsonb nullable)
  • hr_notes (text nullable)
  • manager_comments (text nullable)
  • created_at, updated_at

Implementation note:

  • New-hire cases currently use a system marker review period of __NEW_HIRE__ so the workflow can branch without a schema change.

3.5 ManagerJustification (one per RenewalCase)

  • id (uuid)
  • renewal_case_id (fk unique)
  • manager_user_id (fk users.id nullable for guest submissions)
  • recommendation (enum: RENEW | DO_NOT_RENEW | RENEW_WITH_CONDITIONS)
  • justification (text)
  • conditions (text nullable)
  • submitted_at (timestamptz nullable)
  • submitted_by_name (text nullable)
  • submitted_by_email (text nullable)
  • last_updated_at (timestamptz)

3.5b GuestJustificationToken (one-time external link)

  • id (uuid)
  • renewal_case_id (fk)
  • token_hash (unique)
  • manager_name (nullable)
  • manager_email
  • created_by_user_id (fk users.id)
  • expires_at
  • used_at (nullable)
  • revoked_at (nullable)
  • created_at

3.6 Document (versioned)

  • id (uuid)
  • renewal_case_id (fk)
  • doc_type (enum: OFFER_LETTER | SIGNED_OFFER | PERFORMANCE_REPORT | OTHER)
  • file_name
  • mime_type
  • file_size
  • storage_key (string) // relative path in VPS filesystem
  • uploaded_by_user_id (fk users.id)
  • uploaded_at (timestamptz)
  • version (int default 1)
  • is_latest (bool default true)

Versioning rule:

  • Uploading same (renewal_case_id + doc_type) again:
    • set previous is_latest=false
    • increment version

3.7 EmployeeCompensation (for peer comparison)

We start empty and fill over time from approved renewals.

  • id (uuid)
  • employee_id (fk)
  • effective_date (date)
  • monthly_salary (numeric)
  • currency (string default MYR)
  • grade (string)
  • source (enum: RENEWAL_APPROVED | MANUAL)
  • renewal_case_id (fk nullable)
  • voided_at (timestamptz nullable)
  • void_reason (text nullable)
  • voided_by_user_id (fk users.id nullable)
  • created_at

Creation rule:

  • When CEO approves a case:
    • insert EmployeeCompensation using proposed salary + effective_date=renewal_start_date
    • grade = proposed_grade ?? employee.grade

Analytics selection rule:

  • Use at most one record per active employee: latest effective_date <= as_of_date.
  • Exclude rows where voided_at is not null.
  • Do not count historical records as separate peers.
  • Future-effective approved compensation remains projected until effective.
  • If no effective compensation exists, latest renewal current salary may be used as an explicitly inferred fallback.

3.7b ContractAiAnalysis

  • id (uuid)
  • renewal_case_id (fk)
  • document_id (fk latest OFFER_LETTER)
  • analysis_type (SIGNATURE_PLACEMENT | CONTRACT_VARIATION)
  • status, confidence
  • result_json (structured analysis output)
  • error_message (nullable)
  • model (nullable)
  • prompt_version
  • created_by_user_id (fk users.id)
  • created_at

Store each analysis run as an immutable row. UI reads the latest completed row per document and analysis type.

3.8 InAppNotification

  • id (uuid)
  • user_id (fk)
  • type (enum: JUSTIFICATION_REQUESTED | CASE_READY_FOR_CEO | CEO_DECIDED | CASE_SIGNED)
  • title (string)
  • body (text)
  • link_path (string) // e.g. /manager/requests/{id}
  • is_read (bool default false)
  • created_at

3.9 EmailOutbox (durable outbound queue)

  • id (uuid)
  • to_email
  • subject
  • body_html
  • body_text
  • status (enum: PENDING | SENT | FAILED)
  • provider_message_id (string nullable)
  • error_message (text nullable)
  • attempts (int default 0)
  • last_attempt_at (timestamptz nullable)
  • created_at

3.10 AuditLog (required)

  • id (uuid)
  • actor_user_id (fk)
  • entity_type (enum: RENEWAL_CASE | DOCUMENT | JUSTIFICATION | SETTINGS | ANALYTICS | EMPLOYEE | USER | SIGNATURE | NOTIFICATION | EMAIL)
  • entity_id (uuid)
  • action (string)
  • before (jsonb nullable)
  • after (jsonb nullable)
  • created_at (timestamptz)

Minimum audited actions:

  • CREATE_CASE, UPDATE_CASE, REQUEST_JUSTIFICATION, SUBMIT_JUSTIFICATION
  • UPLOAD_DOC
  • CEO_APPROVE, CEO_REJECT
  • SIGNATURE_UPLOAD, PDF_SIGNED
  • SETTINGS_UPDATE
  • RUN_CONTRACT_AI_REVIEW, ANALYTICS_EXPORT_CSV
  • ADMIN_CORRECT_CURRENT_SALARY (guarded support correction with before/after and reason)

3.11 AdminSettings (keyed settings)

  • id (uuid)
  • key (string unique)
  • value_json (jsonb)
  • updated_by_user_id (fk users.id)
  • updated_at

Historical v0 included an editable app_version key. Current releases use immutable build metadata generated from package.json and do not expose this setting in the UI.

Must include keys:

  • company_name
  • upload_max_mb
  • email_from
  • smtp (optional to store; can be env-only)
  • manager_justification_default_due_days
  • peer_comparison_idr_to_myr_rate
  • analytics_large_increase_percent (default 10)
  • analytics_large_increase_amount_myr (default 1000)
  • analytics_high_curve_percentile (default 90)

4) Digital Signature (PDF signing) — v0 Implementation Plan

4.1 Signature Asset

Store CEO signature as PNG with transparency.

Recommended:

  • user_signature table:
    • id (uuid)
    • user_id (fk users.id unique)
    • file_name, mime_type, file_size, storage_key
    • created_at, updated_at

4.2 Signing UX

  • CEO opens case > Offer Letter preview
  • Click “Sign”
  • PDF preview with draggable signature overlay
  • CEO clicks “Apply Signature”
  • AI signature suggestions can be loaded into the same editable placement queue.
  • Frontend sends { pageNumber, x, y, width, height } in PDF points

Coordinate conversion:

  • Convert DOM px coords to PDF points
  • PDF origin is bottom-left; DOM origin is top-left

4.3 Backend stamping

Use pdf-lib server-side:

  • Load OFFER_LETTER
  • Embed signature PNG
  • Draw on given page coords
  • Save new PDF => SIGNED_OFFER

Rule:

  • Must APPROVE before SIGN
    • approve => status=APPROVED
    • sign => create SIGNED_OFFER + status=SIGNED

4.4 AI contract review

  • Analyze the latest offer-letter PDF for signature blocks and safe placement regions.
  • Return page, PDF coordinates, dimensions, confidence, rationale, and warnings.
  • Find a previous signed contract baseline when available.
  • Compare material terms and return risk level, differences, missing clauses, and recommendations.
  • Use OpenAI structured output when configured, with deterministic heuristic fallback.
  • Persist model and prompt version so results are reproducible and auditable.

5) Core Workflows

5.1 HR creates case

  • Choose Renewal or New Hire
  • Upload and validate the offer-letter PDF; evaluation forms and performance reports are rejected as OFFER_LETTER
  • Review the extracted employee profile; extraction does not persist an employee automatically
  • Require confirmed employee name, department, role title, current grade, applicable grade, employment type, dates, positive salaries, and a three-letter currency code
  • Reject Unknown, Unassigned, and Unspecified placeholders; foreign contractors without an internal grade use an explicit no-internal-grade category
  • Create RenewalCase (DRAFT) and attach the validated OFFER_LETTER

5.2 Renewal path

  • Upsert PerformanceSnapshot
  • Block manager outreach until core HR intake data and the latest OFFER_LETTER are complete
  • Request Manager Justification => status=PENDING_MANAGER + notify manager (in-app + email)

5.3 Manager justification

  • Manager opens “My Requests”
  • Submits recommendation + justification
  • Alternative: HR sends one-time guest link; external manager can submit without account
  • HR marks READY_FOR_CEO => notify CEO (in-app + email)
  • Manager submission alone does not make a renewal ready; performance snapshot and latest offer letter must also be present.

5.4 New-hire path

  • Manager justification is skipped
  • Performance snapshot is skipped
  • Once the latest OFFER_LETTER is present, case can move to READY_FOR_CEO
  • Core HR intake completeness is revalidated before the transition

5.5 CEO decision

  • Show a Decision Brief before the approval controls. Exclude the reviewed employee and compare the proposal against active same-grade peers, with same-grade plus department and exact-role cohorts where available.
  • Show min, P25, median, P75, max, midrank percentile, curve position, peer count, and confidence; also show salary movement, contract term, grade movement, performance position, manager recommendation, required evidence, and contract variation risk.
  • Derive an advisory ALIGNED, REVIEW, CAUTION, or INCOMPLETE posture with readable reasons. It must not execute or replace the CEO decision.
  • Approve => status=APPROVED + locked_at + insert EmployeeCompensation + notify HR/manager
  • Reject => status=REJECTED + locked_at + notify HR/manager

5.6 CEO signs PDF (after approval)

  • Stamp one or more signatures across pages => SIGNED_OFFER + status=SIGNED
  • On successful sign, case is closed (no further edits)
  • Notify HR users (in-app + email via outbox)
  • If the person declines after CEO signature, CEO can mark the case DECLINED. ERS voids the linked approved EmployeeCompensation row, keeps the signed document and audit log, and marks new-hire employee records inactive.

5.7 Management analytics

  • CEO lands on /dashboard, HR on /hr/renewals, and Manager on /manager/requests.
  • Default period is the selected as-of year; filters persist through URL query parameters.
  • Filter by case type: combined, Renewal only, or New Hire only.
  • Deduplicate cases by employee within the filtered period and case type, then disclose excluded older duplicates.
  • Normalize equivalent free-text grades for comparison while preserving separate G scales.
  • Compare proposed salary with the employee's target grade.
  • Separate already-effective compensation from future projected changes to avoid double counting.
  • Exclude DECLINED signed offers and voided compensation rows from default curves, peer stats, and committed budget impact.
  • Exclude open-ended/sentinel terms over 60 months from finite contract-term impact.
  • Export filtered rows as CSV and audit the export.

6) UI Pages

  • /login
  • /dashboard (CEO-only management analytics; Overview, Curve, Increases, Budget; combined or case-type-specific views)
  • /hr/renewals, /hr/renewals/new, /hr/renewals/[id]
  • /manager/requests, /manager/requests/[id]
  • /ceo/renewals, /ceo/renewals/[id], /ceo/signature
  • /guest/justification/[token] (public one-time manager link)
  • /admin/settings, /admin/system-health, /admin/audit, /admin/users
  • /help
  • /notifications

Release identity appears on login, in the sidebar, in About ERS, and on System Health.

7) API Routes (high-level)

Auth:

  • POST /api/auth/login, POST /api/auth/logout, GET /api/auth/me

Employees:

  • GET/POST /api/employees
  • GET/PATCH /api/employees/:id

Renewals:

  • GET/POST /api/renewals
  • GET/PATCH /api/renewals/:id
  • POST /api/renewals/:id/request-justification
  • POST /api/renewals/:id/mark-ready
  • POST /api/renewals/:id/approve
  • POST /api/renewals/:id/reject
  • POST /api/renewals/:id/mark-declined

Documents:

  • POST /api/renewals/:id/documents
  • GET /api/documents/:docId/download

Performance snapshot:

  • GET/POST /api/renewals/:id/performance-snapshot

Justification:

  • GET /api/renewals/:id/justification
  • POST/PATCH /api/renewals/:id/justification
  • GET/POST /api/guest/justification/:token

Signing:

  • POST/GET /api/ceo/signature
  • POST /api/renewals/:id/sign-pdf

Contract AI:

  • POST /api/renewals/:id/contract-ai-analysis
  • GET /api/documents/:docId/signature-suggestions

Analytics:

  • GET /api/analytics/export (CEO-only, filtered CSV, audited)

Notifications:

  • GET /api/notifications
  • POST /api/notifications/:id/read

Admin:

  • GET/PATCH /api/admin/settings
  • GET /api/admin/audit
  • GET/POST /api/admin/users
  • PATCH /api/admin/users/:id

Internal:

  • POST /api/internal/email/dispatch (secret-protected)

8) Storage Plan (VPS filesystem)

Base: /var/app/ers_uploads/

  • documents: documents/{renewal_case_id}/{doc_type}/v{version}/{filename}
  • signatures: signatures/{user_id}/{filename}

Store relative path in DB storage_key.

Operational note:

  • Production storage must use a persistent path such as /var/app/ers_uploads.
  • Do not use /tmp or any other temporary directory for documents or signature uploads.

9) Definition of Done (v0)

  • HR can create renewal and new-hire cases end-to-end
  • HR cannot create or advance cases with placeholder department/grade data, incomplete core case fields, or an evaluation document misclassified as an offer letter
  • Manager can submit renewal justification
  • CEO can view peer stats, approve/reject, and sign PDF
  • CEO case review clearly shows peer position and an explainable advisory Decision Brief before the approval controls
  • In-app + email notifications work via outbox + dispatch endpoint
  • Audit log populated
  • Login, sidebar, About, and System Health show immutable build version and Git SHA
  • CEO dashboard shows grade distributions, employee positions, salary increases, budget impact, exceptions, and data coverage.
  • Grade Curve supports both table and accessible SVG chart modes.
  • CSV export follows dashboard filters, is spreadsheet-safe, and is audited.
  • AI contract review persists signature placement and variation analysis.
  • Statistical tests cover quartiles, tied percentiles, small peer groups, and grade normalization.

10) Management Analytics Calculation Rules

10.1 Curve calculation

  • Comparison currency: MYR. IDR uses the configured conversion rate; unsupported currencies are excluded and flagged.
  • Percentile: empirical midrank, (below + 0.5 * equal) / peer_count * 100.
  • FAR_BELOW: below P10.
  • BELOW_CURVE: P10 to below P25.
  • ON_CURVE: P25 through P75.
  • ABOVE_CURVE: above P75 through P90.
  • FAR_ABOVE: above P90.
  • Fewer than 5 peers: INSUFFICIENT_DATA; 5-9 peers: limited confidence.

10.2 Chart definition

  • Vertical whisker: current grade minimum to maximum.
  • Green box: current P25 to P75.
  • Black line: current median.
  • Blue diamond: projected grade average after applicable non-rejected, non-declined proposals.
  • Colored dots: individual proposed salaries; each dot links to the case.

10.3 Budget definition

  • Current payroll: effective salary for the filtered active roster as of the selected date.
  • Projected payroll: current payroll plus selected changes not already effective.
  • Portfolio increase: proposed minus current salary for non-rejected, non-declined cases in the period.
  • Committed: APPROVED or SIGNED; Pending: DRAFT, PENDING_MANAGER, or READY_FOR_CEO.
  • 12-month impact is effective-date adjusted.
  • Finite contract-term impact excludes invalid/open-ended terms over 60 months and reports their count separately.