Manager Time ReviewGuarded NL2SQLHistorical Context ModelingOffline Deployment

Work-Hour Review & Insights Platform

An internal work-hour review platform that converts fragmented monthly attendance data into reliable manager-facing review insights while keeping the language model outside the data trust boundary.

AI Application Engineer2026/07 - PresentPixArt

Work-Hour Review · System Architecture

Guarded NL2SQLHistorical Org ScopeOffline Intranet
  1. 01

    Encrypted HR

    CP950 / Encrypted XLSX

  2. 02

    Pandas ETL

    Leave & Holiday Rules

  3. 03

    Scoped Snapshot

    Month-Scoped SQLite

  4. Guardrail04

    Guarded SQL

    Single-SELECT AST Whitelist

  5. 05

    Evidence Audit

    Grounded Reply & Logs

  6. 06

    Manager Portal

    React / FastAPI / Docker

Server resolves manager scope & period before SQL execution—LLM never touches the primary database.

Read-Only Snapshot · Audit Logged
Workflow overview. Some names and data are anonymized or recreated for confidentiality.

Before

Internal managers had to manually stitch together fragmented, encrypted, and legacy HR exports to review attendance and work hours, with no retrospective organization tracking or self-service conversational query capabilities.

After

Delivered a unified analytics platform with automated multi-format ingestion, month-scoped historical hierarchy tracking, and a guarded NL2SQL workflow that generates evidence-backed answers within strict role boundaries.

Context & Background

  • Internal managers need reliable, timely work-hour review insights from operational records (attendance, leave, business trips, holidays, and reporting lines).
  • Source records arrive in incompatible operational formats, including encrypted workbooks, legacy spreadsheet exports, and Traditional Chinese files with CP950/Big5 encodings.
  • Historical organizational context must be preserved so reports remain accurate as employees and managers move across departments over time.

Constraints

  • Strict security & confidentiality: managers must never see data outside their reporting scope, and model output must not control authorization or database writes.
  • The platform had to be packaged for offline/intranet deployment with zero cloud dependencies, including embedded Chinese embedding models.
  • Model-generated SQL must be validated against a strict read-only single-SELECT whitelist and run only on temporary scoped data snapshots.

Problem Solved

  • Eliminated high manual overhead and human error in preparing monthly departmental work-hour and attendance review reports.
  • Prevented authorization leaks by enforcing data permissions server-side rather than in prompt context.
  • Ensured retrospective historical reporting accuracy despite ongoing organizational restructuring.

My Role

  • Architected the full-stack work-hour review application across FastAPI, SQLAlchemy, React, TypeScript, and Docker Compose.
  • Designed the security-first NL2SQL supervisor workflow, including temporary filtered SQLite snapshot generation, single-SELECT table whitelisting, and query auditing.
  • Built ETL pipelines for encrypted workbooks, legacy exports, and non-UTF-8 CSV data, normalizing complex attendance and holiday logic.

System Design

  • Raw Data Pipeline: Monthly HR files → Pandas ETL (decryption, CP950 decoding, leave expansion) → Normalized CSVs → Transactional DB sync.
  • Relational Store: Month-scoped employee, department, and manager-hierarchy tables preserving historical reporting context for retrospective queries.
  • Guarded NL2SQL Architecture: Authenticated manager → Server resolves scope and period → Isolated temporary SQLite snapshot → Intent supervisor & SQL validation → Verified evidence → Grounded response synthesis with audit trail.

Implementation Highlights

  • Implemented encrypted workbook decryption, CP950/Big5 fallback decoding, multi-day leave expansion, and holiday-aware attendance calculations.
  • Constructed temporary role-filtered SQLite snapshots containing only permitted employees and departments for safe SQL query execution.
  • Built AST-level / regex-based SQL validator enforcing single-SELECT, whitelisted tables, bounded row limits, and rejection of multi-fact-table joins.
  • Containerized with Docker, Nginx, FastAPI, and local Chinese text embedding assets for air-gapped intranet environments.

Outcome & Impact

  • Replaced manual stitching of multiple operational spreadsheets with an automated, reproducible end-to-end data pipeline.
  • Delivered an intuitive manager analytics portal combining structured dashboard drill-downs with natural-language Q&A.
  • Established defense-in-depth enterprise governance with role scoping, audit logging, rate limiting, and evidence traceability.

Tech Stack

FastAPIReactTypeScriptSQLAlchemyPostgreSQL / SQLitepandasDockerNL2SQLAudit Logging