Attendance Management System

Overview

A v1 internal attendance management service built on top of an existing access-control / fingerprint-device security system (which writes raw commute logs into MSSQL). The service models work plans (WorkPlan), actual commute logs (WorkLog), tardy and early-departure aggregates (TardyLog / LeftEarlyLog), and remote work — all on a single domain so attendance data, which previously lived only inside the security system, becomes a usable internal operations tool.

Because the legacy MSSQL is read-only and has to live separately from Django’s PostgreSQL, and because device data isn’t always complete (missing fingerprints, after-midnight departures, half-day leaves), v1 was built around three explicit decision criteria: a one-way boundary API instead of merging both DBs into a single ORM, deterministic explicit handlers instead of signal-based automation, and idempotent updates that don’t break on partial data.

Tech Stack

• Backend: Python, Django, PostgreSQL, MSSQL (pyodbc), NGINX
• Infra: Docker, Docker Compose, Naver Cloud Platform (NCP)

My Role

One of two backend engineers on a team of PM 1 / FE 1 / BE 2. I owned the heterogeneous-DB integration, the commute-domain model, work-plan generation, tardy / early-departure / remote-work logic, and NCP deployment for the v1 cycle (2024-02 to 2024-05). I did not participate in the later v2 advancement, so this writeup is scoped to the v1 design decisions on origin/main.

Key Contributions

• Kept the legacy MSSQL out of the ORM behind a one-way boundary API.

  • Instead of folding both DBs into one ORM, I built an explicit boundary API between the MSSQL commute source and Django’s domain models. Cron reads MSSQL and drops a CSV; the Django REST endpoint receives it and interprets all domain semantics — user matching, work-type filtering, time validation — at the boundary before anything lands in PostgreSQL. MSSQL is the security system’s source of record and Django must never write to it; every external row passes through one validation point; and when MSSQL is down, Django keeps running on the data it already has.

• Used explicit handlers, not signals, to derive aggregate logs.

  • A pre-save signal on the commute log was tempting, but v1 calls the tardy / early-departure log creators explicitly from inside the upload endpoint. Derived logs must only be created when real attendance data arrives, three states (missing fingerprint / corrected / partial) have to be handled in one comparison, and decisively — when the row is for today, judgment is deferred so a person isn’t falsely marked tardy at 9 AM on the same morning.

• A single operational-correctness policy across partial data, time boundaries, and external-dependency failure.

  • Devices record 0–6 AM departures as the next day, half-day leaves vary by whether lunch is included, the public-holiday API occasionally fails, and cron can run late or twice. After-midnight departures are folded into the previous shift; half-day departure-time math branches on lunch by arrival time; a failed holiday-API call silently continues with an empty result, so missing holidays show as “regular workday” — correctable after the fact, partial load preferred to a halted system. The cron processes both yesterday and today with an existence check, so neither clock skew nor a repeated run causes drift.

Troubleshooting

• People falsely marked “left early” by mid-day, in-progress data.

  • Problem: Initially the code created tardy / early-departure logs the moment any device data came in. The device records arrival and departure as separate events, so anyone who had checked in in the morning but hadn’t yet left for the day looked, at that snapshot, like a no-departure case — and got falsely marked “left early.” The real issue was deciding from data on a still-in-progress day.
  • Solution: Adding the rule “when work_date == today, defer judgment” to the comparison logic — i.e., never derive aggregate logs from an in-progress day — stopped the same false-positives from ever recurring.

• Repeated CSV uploads incorrectly mutating existing rows — a same-input safety defect.

  • Problem: Device CSVs arrive several times a day (morning / afternoon / night), and network retries on top mean the same day’s data hits the endpoint repeatedly within short intervals. The initial cron created compensating tardy logs with a lookup key that included the actual clock-in time, so when a later upload arrived with a slightly different clock-in time, it would either create an extra row or mutate the existing row’s status incorrectly — leaving the same (user, date) in a non-deterministic state.
  • Solution: Narrowed the lookup key to the work-plan alone, enforcing “at most one tardy log per (user, date)” at the data layer. The cumulative tardy counter was moved to an atomic increment expression so concurrent uploads no longer race. Uploading the same CSV multiple times now converges to the same result.

Impact

A system where the boundary between the legacy security system and the new domain stays clean while attendance data finally surfaces as an operations tool. The most satisfying part wasn’t any single feature — it was using three decision criteria (“don’t merge the DBs, bridge them with an API,” “automation is fine but it has to be deterministic,” “don’t break on partial data”) consistently across idempotency, time boundaries, and external API failure handling.