VTRC Knowledge Manual (V2)

ธีม

Phase 3 — Data Integrity & API Ecosystem ​

⚠️ SUPERSEDED / ถูกแทนที่แล้ว — This audit snapshot is not authoritative.
อย่าใช้ตัดสินใจ operational โดยไม่ verify โค้ด / Do not use for operational decisions without verifying code.

Authoritative docs: VitePress volumes (docs/volume-00 … docs/volume-16) · docs/audits/docs-verify-2026-07-11-delta.md · docs/volume-17-appendices/08-changelog-errata.md

Known stale claims ในไฟล์นี้: NestJS เป็น live END_POINT_CENTRALIZE target · cu-central port 9000 เป็น listen (จริงคือ EXPOSE เท่านั้น) · dbHRMI_NBC แทน dbHRMI_Center_NBC · KBANK bank name errors อาจปรากฏ

i3 Gateway Knowledge Base · VTRC Platform Evidence basis: Sequelize model definitions (vtrc-api/api/src/models/, cu-central-api/main-api/src/models/), TypeORM entities (centralize-api/src/entities/), GraphQL typeDefs (vtrc-api/api/src/typeDefs/), REST routes (vtrc-api/api/src/routes.js, centralize-api/src/modules/*/*.controller.ts). Modernization target framing: Go (Hexagonal / DDD) for backends · Nuxt 3 (Feature-Sliced Design) for frontends.

3.1 Persistence Topology — Four Logical Databases ​

The platform persists data across four logical databases, owned by three different services. This is the single largest source of architectural complexity.

#DatabaseEngineOwner ServiceSchema(s)Role
DB-1vtrc (MariaDB)MariaDBvtrc-api (db conn)vtrcApplication state: users, sessions, roles, leave, withdraw, fund, notifications, posts, files, master data
DB-2vtrc-centralize (MariaDB)MariaDBvtrc-api (db2 conn)vtrc-centralizeCross-schema user/division join tables (the centralize/user model lives here)
DB-3dbHRMI_Center (MSSQL)MSSQLcentralize-api (TypeORM center) · cu-central-api (db)dbHRMI_Center (+ NBC variant)HR Master Data — Employee, EmployeeGroup, EmployeeLevel, TechnicalPosition, PaySlip, TimeTemp
DB-4dbHRMI_Center_NBC (MSSQL)MSSQLcentralize-api (TypeORM nbc)NBC databaseNBC-grouped HR mirror (*.nbc.entity.ts variants) — not dbHRMI_NBC

Evidence:

  • vtrc-api/api/src/connector.js:8-48 — two Sequelize instances db + db2.
  • centralize-api/src/app.module.ts:23-60 — two TypeOrmModule.forRootAsync({ name: 'center' | 'nbc' }).
  • centralize-api/src/entities/employeeGroup.entity.ts:3-6 — @Entity({ name: 'emEmplGrup_reStructure', database: 'dbHRMI_Center' }).
  • cu-central-api/main-api/src/index.js:8,98-117 — db + dbHr (both MSSQL).

Data ownership risk ​

RiskEvidenceImpact
Cross-database writes from one service — vtrc-api writes to both vtrc (db) and vtrc-centralize (db2)vtrc-api/api/src/index.js:101-120 authenticates both; models/centralize/user/user.js registers on db2No transactional boundary across schemas; partial writes possible
syncModelToTable() runs on every bootvtrc-api/api/src/index.js:104 calls syncModelToTable(); centralize-api explicitly uses synchronize: false (app.module.ts:33,52)Sequelize auto-sync = schema drift risk in prod; the NestJS side correctly refuses it
Two model definitions for the same conceptual entity — User exists in (a) vtrc-api/models/centralize/user/user.js (MariaDB UUID), (b) centralize-api/entities/user.entity.ts (MSSQL auth_user table), (c) cu-central-api/models/hrmi/employee/employee.js (MSSQL EmpID string)Three different PK strategies for "user/employee"Polysemic identity; no canonical employee ID
MSSQL trustServerCertificate: truecentralize-api/src/app.module.ts:36-38,55-57Disables TLS verification on DB connection — MITM risk on internal network
logging: ['error','query'] in productioncentralize-api/src/app.module.ts:34,53Full SQL (potentially with PII) logged in prod

3.2 Entity-Relationship Diagrams (Mermaid) ​

Three ERDs follow, one per core domain: Identity & Access, Leave & Approvals, Welfare Withdrawal (Hospital). Cardinality is read from Model.associate(...) calls.

3.2.1 ERD — Identity & Access Management (MariaDB vtrc + vtrc-centralize) ​

mermaid
erDiagram
    Users ||--o{ UserDivision : "has many"
    Users ||--o{ Sessions : "owns sessions"
    Users }o--o{ Roles : "via JSON role field"
    Roles ||--o{ RoleAccess : "defines"
    RoleAccess }o--|| MasterConfig : "menu/action?"

    Users {
        UUID userId PK
        UUID personId
        JSON role "JSON-encoded array"
        TEXT token "current JWT"
        TEXT refreshToken
        TEXT refId "links to MSSQL EmpCode"
        STRING sourceDb
    }
    UserDivision {
        UUID userDivisionId PK
        UUID userId FK
        STRING division
    }
    Sessions {
        UUID sessionId PK
        UUID userId FK
        STRING refreshToken
        JSON userSession
        DATE expireDate
        ENUM client "WEB|MOBILE|WEB_ADMIN"
        TEXT currentProfile
        TEXT tokenCD "Chula token"
    }
    Roles {
        UUID roleId PK
        STRING roleLabel
        STRING roleName
        BOOLEAN isActive
    }
    RoleAccess {
        UUID roleAccessId PK
        UUID roleId FK
        STRING menu
        STRING action
    }

    %% Cross-schema bridge (vtrc-centralize)
    Users }o..|| AuthUser_MSSQL : "conceptually maps via refId/EmpCode"

    AuthUser_MSSQL {
        UUID userId PK "centralize-api/entities/user.entity.ts"
        STRING userName
        STRING password
        STRING email
        UUID userLevel FK
    }

Identity notes:

  • Users.role is a JSON-encoded TEXT column with custom getter/setter (user.js:19-28) — i.e. RBAC is not enforced relationally. There is no join table between Users and Roles; roles are denormalized into a JSON blob. This makes permission audits expensive.
  • Sessions.client ENUM = WEB | MOBILE | WEB_ADMIN — this is the device-class axis of auth (matches APP_TYPE in device keys).
  • Sessions.tokenCD / refreshTokenCD — the Chula (CU) SSO tokens are cached inside the VTRC session row, confirming cu-central-api is the upstream identity provider for CU-affiliated users.

3.2.2 ERD — Leave & Approval Workflow (MariaDB vtrc) ​

mermaid
erDiagram
    LeaveDocument ||--o{ Approver : "approval chain"
    LeaveDocument }o--|| LeaveHistory : "isHistory link"
    LeaveDocument }o--o| ShiftSchedule : "shiftId (CU)"
    LeaveDocument {
        UUID wdLeaveId PK
        STRING empCode "FK to CU employee"
        STRING leaveType
        STRING leaveTypeLabel
        DATE leaveStartDate
        DATE leaveEndDate
        FLOAT leaveDay
        ENUM status "PEDDING_CANCLE|PENDING|MOREINFO|APPROVE|REJECT|CANCEL"
        INT level "current approval level"
        INT orderNo
        INT isHistory
        UUID refId
        UUID shiftId
        STRING orgCode
        FLOAT leaveTotalUse
        FLOAT leaveUsed
        FLOAT leaveDayAll
        ENUM clientType "WEB_ADMIN|WEB_CLIENT"
    }
    Approver {
        UUID approverId PK
        UUID wdLeaveId FK
        UUID empId
        STRING empCode
        INT orderNo "approval sequence"
        INT level
        STRING status
        STRING noteApprove
    }
    LeaveHistory {
        UUID historyId PK
        UUID refTaskHistoryId "targetKey for belongsTo"
        JSON payload
    }
    ShiftSchedule {
        UUID shiftId PK "from CU/MSSQL"
        STRING shiftCode
        STRING shiftTime
    }
    LeaveAattch {
        UUID attachId PK
        UUID wdLeaveId FK
        STRING fileId
    }
    LeaveDocument ||--o{ LeaveAattch : "attachments"

Leave notes:

  • The state machine is encoded in the status ENUM plus a numeric level + orderNo. (See Phase 4 for the full transition map.)
  • Typo in ENUM: 'PEDDING_CANCLE' — both "PEDDING" (should be PENDING) and "CANCLE" (should be CANCEL). This is in production schema (leave.js:59), meaning every consumer must spell it identically wrong forever.
  • LeaveDocument references empCode (CU employee code) rather than userId — leave is employee-keyed, not user-keyed. Cross-domain join happens via refId.
  • clientType ENUM distinguishes backoffice (WEB_ADMIN) vs end-user (WEB_CLIENT) actions on the same row.

3.2.3 ERD — Welfare Withdrawal: Hospital (MariaDB vtrc) ​

The highest-stakes financial flow in the platform (employee claims hospital reimbursement → approval chain → bank export).

mermaid
erDiagram
    WDHospital ||--o{ WDSlip : "payment slips"
    WDHospital ||--o{ WDHospitalDocument : "claim attachments"
    WDHospital ||--o{ Approver : "approval chain (shared table)"
    WDHospital }o--o{ MasterApprover : "configured approvers via orgCode"
    WDHospital }o--|| WDHistory : "history link"
    WDHospital }o--o| WDExport : "bank export batch"
    WDHospital }o--|| Sickness : "sicknessId"
    WDHospital }o--|| Hospital : "hospitalId"
    WDHospital }o--|| HospitalBankAccount : "hospitalBankAccountId"

    WDHospital {
        UUID wdHospitalId PK
        STRING empCode
        STRING empFirstName
        STRING empLastName
        STRING withdrawIdCard
        STRING relation "self|spouse|child|parent"
        INT age
        STRING orgCode
        STRING department
        STRING division
        DECIMAL totalWithdraw
        DECIMAL totalCurrentWithdraw
        DECIMAL previousCurrentWithdraw
        ENUM status "PENDING|MOREINFO|APPROVE|REJECT|PAID|DRAFT|FAILED|SUCCESS|CANCEL|CASH|EDIT|CANCELDOC|PAIDCASH"
        ENUM bank "KBANK|OTHER"
        STRING hospitalId FK
        STRING sicknessId FK
        STRING hospitalBankAccountId FK
        DATE treatmentStartDate
        DATE treatmentEndDate
        DATE effectivePayDate
        UUID wdExportId FK
        UUID refId
        INT level
        INT isHistory
        STRING documentNo
        STRING withdrawNo
    }
    WDSlip {
        UUID wdSlipId PK
        UUID wdHospitalId FK
        DECIMAL amount
        STRING slipNo
    }
    WDHospitalDocument {
        UUID documentId PK
        UUID wdHospitalId FK
        STRING fileId
    }
    Approver {
        UUID approverId PK
        UUID wdHospitalId FK
        INT orderNo
        INT level
        STRING status
    }
    MasterApprover {
        UUID masterApproverId PK
        STRING orgCode
        STRING approverEmpCode
        INT level
        INT orderNo
    }
    WDHistory {
        UUID historyId PK
        UUID refTaskHistoryId
        JSON payload
    }
    WDExport {
        UUID wdExportId PK
        STRING exportRefCode
        DATE exportDate
        ENUM bank "KBANK|OTHER"
    }
    Sickness {
        STRING sicknessId PK
        STRING sicknessName
    }
    Hospital {
        STRING hospitalId PK
        STRING hospitalName
        ENUM hospitalType
    }
    HospitalBankAccount {
        STRING hospitalBankAccountId PK
        STRING hospitalId FK
        STRING accountNo
        STRING accountName
        ENUM bank
    }

Withdrawal notes:

  • 13-state ENUM on WDHospital.status — the richest state machine in the system (Phase 4 decodes it).
  • Money columns are DECIMAL(12,2) ✅ (good — not FLOAT).
  • WDHospital.belongsTo(Hospital) is declared twice with different aliases (hospitalWithName keyed on hospitalName string, hospital keyed on hospitalId) — evidence of a data-migration half-step where hospitals were referenced by name first, then by ID.
  • Duplicated column coaCodeName is declared twice in the same model (hospital.js:14 and hospital.js:138) — Sequelize silently keeps the last. A latent bug.
  • Duplicated column isJuristic is declared twice (hospital.js:159 and hospital.js:189).

3.2.4 ERD — HR Master Data (MSSQL dbHRMI_Center, owned by centralize-api / cu-central-api) ​

The HR master is a third-party / upstream system (HRMI = Human Resource Management Information). VTRC is a read-mostly consumer with some re-structured mirror tables.

mermaid
erDiagram
    Employee ||--o{ EmployeeGroup : "via EmpGroupID"
    Employee ||--o{ EmployeeLevel : "via level"
    Employee ||--o{ TechnicalPosition : "via position"
    Employee ||--o{ Uvi3PaySlipHD : "salary header (VIEW)"
    Uvi3PaySlipHD ||--o{ Uvi3PaySlipDT : "slip lines"
    Employee ||--o{ HrTimeTempImport : "shift/time imports"
    Employee ||--o{ WorkingProfile : "working history"
    Employee ||--o{ EmpIdentity : "national ID"
    Employee ||--o{ ProfileHistory : "changes"

    Employee {
        STRING EmpID PK "36-char (UUID-as-string)"
        STRING EmpCode "business key"
        STRING EmpCardNoID
        STRING EmpGroupID FK
        DATE StartDate
        INT ProbationDays
        DATE WorkingDate
        BOOLEAN IsOldEmp
        STRING ShiftID FK
    }
    EmployeeGroup {
        STRING EmplGrupID PK
        STRING EmplGrupCode
        STRING EmplGrupName
        BOOLEAN IsDeleted
        BOOLEAN IsInactive
        STRING SourceDB
    }
    EmployeeLevel {
        STRING levelId PK
        STRING levelCode
        STRING levelName
    }
    TechnicalPosition {
        STRING techPosId PK
        STRING techPosCode
        STRING techPosName
    }
    Uvi3PaySlipHD {
        STRING EmpCode PK "VIEW uvi3PaySlipHD"
        STRING Period
        DATE PayDate
        DECIMAL NetPay
    }
    Uvi3PaySlipDT {
        STRING EmpCode PK "VIEW uvi3PaySlipDT"
        STRING ItemCode
        DECIMAL Amount
    }
    HrTimeTempImport {
        STRING EmpCode FK
        DATE WorkDate
        STRING ShiftCode
    }

HR master notes:

  • The slip data is exposed via SQL Server VIEWs (uvi3PaySlipHD.view.js, uvi3PaySlipDT.view.js) — VTRC does not own the slip tables; it reads pre-computed views from the HRMI system.
  • Employee.EmpID is a STRING(36) holding a UUID — stringly-typed primary key instead of a native UUID type. This is a portability landmine for any Go rewrite using native uuid types.
  • The *.nbc.entity.ts variants in centralize-api are parallel entity definitions for the NBC database (different MSSQL DB, same host) — meaning the same logical concept (EmployeeGroup, EmployeeLevel, TechnicalPosition) is mirrored in two physical databases. centralize-api queries both depending on the employee's group.

3.3 API Surface Inventory ​

The platform exposes three distinct API styles:

StyleServicePathCount (approx)Auth
GraphQL (Apollo v2)vtrc-api/api/graphql (prod) / /api-uat/graphql (UAT)~250 operations across 43 modulesapiKey header + Authorization: Bearer + @auth directive
GraphQL (Apollo v2)cu-central-api/api/graphql (Dockerfile EXPOSE 9000 ≠ listen)~150 operations across 30 modulesapiKey + JWT + AD bind
REST (Express)vtrc-api${API_PREFIX_PATH}/...~40 endpoints (routes.js 1,301 LOC)URL-embedded :device/:timestamp/:hash (1h validity)
REST (NestJS + Swagger)centralize-api/api/... (+ /docs Swagger)18 endpoints across 3 controllersNestJS JWT guard + @Public()

3.4 Top 15 Critical API Endpoints (OpenAPI-style) ​

Selected by business criticality × blast radius. Each entry cites the exact source location.

GraphQL — vtrc-api (operations are POST to /api/graphql) ​

#1 · Mutation.login — End-user authentication ​

yaml
operation: login
service: vtrc-api
auth: none (public)
source: vtrc-api/api/src/typeDefs/authentication.js:26-32
resolves_to: lib/repositories/auth/auth.js
purpose: |
  Primary login for end-user (web + mobile). Verifies EmpCode + password
  against CU directory (via centralize/ bridge → cu-central-api → LDAP/AD).
request_payload:
  lsid: ID!              # login-session id from verifyEmpCodeLogin
  password: String!      # user password (sent in clear over TLS; hashed server-side)
  clientInfo: ClientInfo!  # { appVersion, userAgent }
  apiKey: String!        # device key (WEB / MOBILE)
  organization: ID!      # organization id (multi-tenant switch)
response:
  Auth: { accessToken: String, refreshToken: String }
errors:
  - AUTH_LIMIT            # too many attempts
  - PROFILE_INVALID       # emp code not found
  - VERIFY_CODE_INVALID   # captcha/OTP mismatch
notes:
  - First step is `verifyEmpCodeLogin` (Query) which returns `lsid` + captcha enforcement.
  - Triggers `Session` row creation with `client` ENUM (WEB|MOBILE|WEB_ADMIN).

#2 · Mutation.loginBackoffice — Admin authentication ​

yaml
operation: loginBackoffice
service: vtrc-api
auth: none (public)
source: vtrc-api/api/src/typeDefs/authentication.js:34-41
purpose: Backoffice admin login. Same signature as #1; differs in RBAC routing.
response: Auth { accessToken, refreshToken }
notes: accessToken carries role JSON; downstream @auth(accessMenu) gates apply.

#3 · Query.refreshToken — JWT rotation ​

yaml
operation: refreshToken
service: vtrc-api
auth: none (uses refresh token)
source: vtrc-api/api/src/typeDefs/authentication.js:6
invoked_by: vtrc-web/src/config/apollo.js:74-80 (errorLink on TOKEN_EXPIRED)
request: { token: String, refreshToken: String }
response: AccessTokens { accessToken: String }
notes: |
  Frontend's apollo errorLink intercepts TOKEN_EXPIRED, calls this operation
  via a side-channel apolloFetch (not the main client), and replays the
  failed operation. This is the single most fragile auth path — see Phase 5.

#4 · Query.fetchSlip — Payslip fetch ​

yaml
operation: fetchSlip
service: vtrc-api
auth: "@auth(accessRole: ['USER'])"
source: vtrc-api/api/src/typeDefs/slip.js:4-11
resolves_to: lib/controllers/slip → centralize/controllers/slip.js → centralize-api / cu-central-api
purpose: |
  Fetch an employee's payslip (income/expense breakdown) for a given
  identityCard + month + year. Most-called end-user operation.
request:
  identityCard: String!   # national ID (PII)
  month: String!
  year: String!
  profileKey: String      # selects which profile (multi-role users)
  typeSlip: String
response: Slip { paymentDate, income, expense }
downstream: |
  Calls centralize-api MSSQL VIEWs uvi3PaySlipHD + uvi3PaySlipDT.
  Result cached in Redis (getCacheData/setCacheData).

#5 · Query.fetchUrlSlip — Encrypted payslip PDF URL ​

yaml
operation: fetchUrlSlip
service: vtrc-api
auth: "@auth(accessRole: 'USER')"
source: vtrc-api/api/src/typeDefs/slip.js:22-27
resolves_to: routes.js GET /readfile/prs/:year/:month/:pid/:empcode/:device/:timestamp/:hash
purpose: |
  Returns a time-limited (1h) URL that, when opened, triggers server-side
  PDF generation (ejs → html-pdf → qpdf --encrypt) protected by the user's
  identity-card number as the PDF password.
response: UrlSlip { url, ... }
critical_risk: |
  routes.js:73 constructs `qpdf --encrypt ${allData.passwordPDF} ...` and
  passes it to execSync(). passwordPDF derives from identityCard. This is a
  COMMAND-INJECTION vector if identityCard can contain shell metacharacters.
  See Phase 5 entry SEC-1.

#6 · Query.fetchUrlTax / fetchUrlTax6Month / fetchUrlTaxYears — Tax document URLs ​

yaml
operations: [fetchUrlTax, fetchUrlTax6Month, fetchUrlTaxYears]
service: vtrc-api
auth: "@auth(accessRole: 'USER')"
source: vtrc-api/api/src/typeDefs/slip.js:29-48
purpose: |
  Three tax-document variants: annual, 6-month, multi-year. Same qpdf/REST
  pipeline as #5. Password = identityCard.

#7 · Query.timeAttendanceByDate — Clock-in/out log ​

yaml
operation: timeAttendanceByDate
service: vtrc-api
auth: "@auth(accessRole: ['USER'])"
source: vtrc-api/api/src/typeDefs/timeAttendance.js:5-10
resolves_to: centralize/controllers/timeAttendance.js → cu-central-api → MSSQL HrTimeTemp
purpose: |
  Returns the list of clock-in/out timestamps for a given day. This is the
  READ side of the time-attendance flow; the WRITE side (clock-in/out) is
  performed upstream in the HRMI system, not in VTRC.
request:
  date: Date!             # yyyy-mm-dd
  sorting: SortingType!   # ASC | DESC
  profileKey: String
response: TimeAttendanceResponse { date, times: [TimeAttendance { timeAttendanceId, timestamp }] }
notes: |
  Phase 4 sequence diagram focuses on this domain — it spans 4 services
  (frontend → vtrc-api → centralize-api → cu-central-api/MSSQL).

#8 · Query.fetchSalaryCert — Salary certificate ​

yaml
operation: fetchSalaryCert
service: vtrc-api
auth: "@auth(accessRole: ['USER'])"
source: vtrc-api/api/src/typeDefs/slip.js:13-16
purpose: Salary certificate data (for loan applications, visa, etc.).

GraphQL — cu-central-api (operations are POST to /graphql on port 9000) ​

#9 · cu-central-api authentication (LDAP bind) ​

yaml
operation: (login / authentication family)
service: cu-central-api
auth: apiKey + AD bind
source: cu-central-api/main-api/src/typeDefs/authentication.js
        + lib/auth/activeDirectory.js + lib/jwtToken.js
purpose: |
  The actual identity verification against Chula Active Directory.
  vtrc-api delegates here for CU-affiliated users.
downstream: |
  ldapjs bind → search → returns user DN → verify密码 against AD.
  On success: issue cu-central-api JWT, cache tokenCD in vtrc-api Session.
risks:
  - ldapjs is unmaintained (see Phase 1).
  - SHA512-crypt fallback via sha512crypt-node (unmaintained).

#10 · cu-central-api payslip (MSSQL VIEW reader) ​

yaml
operation: (payroll slip family)
service: cu-central-api
source: cu-central-api/main-api/src/typeDefs/payrollSlip.js
        + lib/payrollSlip.js + models/hrmi/salary/uvi3PaySlip*.view.js
purpose: |
  Reads the SQL Server VIEW uvi3PaySlipHD + uvi3PaySlipDT. This is the
  upstream source of truth for all payslip data shown in #4 and #5.
notes:
  - payrollSlip_removed.js exists (a removed variant) — schema archaeology.
  - Slip data is read-only; VTRC never writes back to HRMI.

REST — vtrc-api/routes.js (Express) ​

#11 · GET /api/readfile/prs/:year/:month/:pid/:empcode/:device/:timestamp/:hash — Encrypted payslip PDF ​

yaml
method: GET
service: vtrc-api (Express REST, parallel to GraphQL)
source: vtrc-api/api/src/routes.js:51-99
auth: |
  URL-embedded signed hash; validity = 1 hour (timestamp check at line 53).
purpose: |
  Generates an EJS-rendered PDF of the payslip, encrypts it with qpdf using
  the user's identityCard as the password, returns the file, deletes after 5s.
pipeline: |
  createslip(req) → ejs.renderFile(report-template.ejs) → html-pdf.create()
  → execSync('qpdf --encrypt <password> <password> 40 -- <in> <out>') → res.sendFile()
critical_risk: |
  SEC-1: command injection via identityCard (Phase 5).
  PERF-1: blocking execSync in event loop (Phase 5).
  The file is deleted 5s after sendFile — race condition if download is slow.

#12 · GET /api/tax/:year/:month/:pid/:empcode/:device/:timestamp/:hash — Encrypted tax PDF ​

yaml
method: GET
service: vtrc-api (Express REST)
source: vtrc-api/api/src/routes.js:101-130+
purpose: Same pipeline as #11 but uses tax-template.ejs and 6-month/year variants.
critical_risk: Same qpdf injection + blocking exec.

#13 · POST /api/uploadExcel & POST /api/downloadExcel — Excel import/export ​

yaml
methods: POST (upload), GET/POST (download)
service: vtrc-api (Express REST)
source: vtrc-api/api/src/routes.js (multer cpUpload, 100 files)
       + lib/controllers/excel/excel.js + exceljs
auth: apiKey header
purpose: |
  Bulk import (withdraw/hospital, fund, master data) and bulk export
  (reports, fund rate change, fail-payment lists).
notes: |
  upload.fields([{ name: 'attachFiles', maxCount: 100 }]) — 100-file batch.
  graphqlUploadExpress maxFileSize: 20MB, maxFiles: 100 (index.js:25).
risk: |
  No visible virus/extension validation in routes.js head; exceljs has had
  prototype-pollution advisories historically.

REST — centralize-api (NestJS + Swagger, under /api) ​

#14 · GET /api/employee (and 12 sibling employee lookups) ​

yaml
method: GET
service: centralize-api (NestJS)
source: centralize-api/src/modules/employee/employee.controller.ts:29-118
auth: JwtAuthGuard (global), ValidationPipe whitelist
swagger: /docs
purpose: |
  Employee master-data read API. 13 GET endpoints cover:
  employee (list), employee/cu, employee/manager, employee/organize,
  employee/level, employee/division, employee/department,
  employee/posManage, employee/position, employee/posTechnical,
  employee/group, employee/getByEmpCode, employee/getEmployeeDisaster,
  employee/bankAccount.
query_params: typed DTOs (GetEmployeeQueryDto, GetManagersQueryDto, ...)
response: typed entities (class-transformer + class-validator)
notes: |
  This is the ONLY API surface with proper DTO validation, Swagger docs,
  and a global guard. It is the model for all modernization work.

#15 · POST /api/hrPaySlip and POST /api/hrTimeTemp/import ​

yaml
methods: POST, POST
service: centralize-api (NestJS)
source:
  - centralize-api/src/modules/hrPaySlip/hrpayslip.controller.ts:13-19
  - centralize-api/src/modules/hrTimeTemp/hrtimetemp.controller.ts:13-19
auth: JwtAuthGuard + ValidationPipe
purpose: |
  hrPaySlip: batch fetch payslip detail rows by EmpCode list (EmpCodeListDto).
  hrTimeTemp/import: import shift/time-temp records (GethrTimeTempQueryDto).
notes: |
  Both are POST-with-body for read-style operations (EmpCodeListDto),
  suggesting the team chose POST to avoid URL-length limits on multi-emp queries.

3.5 API Anti-Patterns Catalogue ​

#Anti-patternEvidenceImpact
API-1Two API styles in one service (GraphQL + REST)vtrc-api/index.js:27,95Two auth models, two error formats, two logging paths, doubled attack surface
API-2URL-embedded auth hash for RESTroutes.js:51 /:device/:timestamp/:hashCannot revoke; replay within 1h; logged in access logs (leaks hash)
API-3execSync in request handlerroutes.js:73Blocks event loop; one slow PDF starves all other requests on that replica
API-4No rate limiting visibleindex.js has no express-rate-limitLogin/OTP/refresh endpoints unprotected
API-5CORS origin: '*' in centralize-apicentralize-api/src/main.ts:31-35Any origin can call the HR data service
API-6playground: true and introspection: true in prodvtrc-api/index.js:68-69Schema exposed publicly
API-7GraphQL no-cache fetch policyvtrc-web/src/config/apollo.js:173-181Defeats Apollo cache; every nav hits network — frontend perf killer
API-8JSON-encoded role array in DBmodels/centralize/user/user.js:19-28RBAC not relational; audit impossible via SQL
API-9PII in URLs (identityCard, empCode)routes.js:51 path paramsPII lands in nginx access logs, browser history, CDN logs
API-10@auth directive bypassable via RESTroutes.js is not guarded by directiveREST endpoints are auth-light compared to GraphQL
API-11forceLoging mutation (typo, no auth)authentication.js:63-68Public mutation for error logging — abuse vector
API-12Mixed naming — ResposeStatus (typo), PEDDING_CANCLE (typo), finanace.js (typo)Multiple filesTypos baked into public GraphQL schema = permanent BC burden

3.6 Modernization Mapping — Data & API → Go (Hex/DDD) + Nuxt 3 (FSD) ​

CurrentModernized (target)
4 logical DBs, 2 engines, cross-schema writesOne logical store per bounded context; identity, payroll, leave, welfare, fund, attendance each own their schema. MSSQL HRMI becomes an anti-corruption layer (ACL) adapter — never written to.
syncModelToTable() auto-syncExplicit migrations (goose / atlas / golang-migrate). Forward-only, reviewed.
GraphQL + REST dual surfaceOne driving adapter per transport. GraphQL via gqlgen; REST via chi/echo. Same use-cases behind both.
execSync(qpdf) inlineDedicated document-renderer service (Go + chromedp or outsource to a headless-Chrome sidecar). PDF generation = outbound port.
URL-embedded hash authSigned JWT with short TTL + refresh-token rotation; PDFs served via authenticated streaming endpoint.
JSON role blobRelational user_roles + role_permissions + menu_access. Casbin or OPA for policy.
4.5K-LOC queries.jsFSD entities/<slice>/api/operations.ts — operations co-located with their feature.
no-cache Apollo policyNuxt 3 useFetch + Pinia Colada or @nuxtjs/apollo with normalized cache; stale-while-revalidate.
Swagger only on centralize-apiOpenAPI on all REST services; GraphQL schema published via federation / supergraph.

Phase 3 complete. Proceed to 04-business-logic.md for the RBAC matrix, hardcoded business rules, and the clock-in/clock-out sequence diagram.