Aadhib

Case study · Active

Turning physical access logs into an enterprise software workflow

Gate hardware produces rows. People ask questions about presence. The work is entirely in the distance between those two things.

Role
Architecture and delivery
Published
Reading time
1 min
SQL ServerSystems integration
01

Context

Physical access control systems are good at their job — deciding whether a credential opens a door — and produce a large volume of event data as a side effect. That data sits in a database designed around hardware: readers, credentials, timestamps.

02

Problem

The questions people ask are about human beings, not credentials. Who was on site. Was this contractor here when they said. Does attendance reconcile. Answering any of those means joining hardware events to employee identity, and in most organisations it means someone writing SQL each time somebody asks.

03

Constraints

01
The access system is life-safety adjacent
You read from it carefully and you do not become something it has to depend on.
02
Two systems disagree constantly
The credential database and the employee directory drift — joiners, leavers, lost badges, temporary credentials. The reconciliation has to expect disagreement rather than assume consistency.
03
Presence data is sensitive
It reveals a great deal about individuals. What is shown, and to whom, has to be constrained deliberately.
04

Approach

I built a read-side application over the existing access-control data. I joined events to employee identity, modelled the reconciliation cases explicitly rather than assuming clean joins, and presented the results as the views people actually ask for — leaving the access-control system as the untouched source of truth.

05

Architecture

Access control systemRemains the source of truth. Read from, never written to.
Identity reconciliationCredential events joined to employee records, with the mismatch cases handled explicitly.
Operational viewsPresence and attendance in the shapes people actually ask for.
06

Solution

A software layer that turns credential events into attributable presence information, with an interface built for the supervisors and administrators who answer these questions rather than for whoever is comfortable writing a query.

What I have deliberately left out

No site layout, no credential scheme, no reader topology, no access-control configuration. The engineering pattern is useful to describe. The security posture of a specific facility is not.

Where the difficulty actually lives

Not in the query. In the fact that a person can have two credentials, that a badge reported lost is sometimes still in use, that contractors exist in one system and not the other, and that every one of those cases needs a defined behaviour rather than a NULL.

07

Lessons

  1. 01The integration is the product. The interface is comparatively easy; reconciling two systems that model people differently is where the time goes.
  2. 02Assume the join will fail and design for it. Clean data is a temporary condition, not a starting assumption.
  3. 03Sensitive data constrains the interface, not just the database. The question "who can see this view" is a product decision.
  4. 04Read-only integrations age far better than bidirectional ones, especially against systems with safety implications.

Stack

What it runs on

Source
Access control databaseSQL Server
Application
Identity reconciliationOperational views

The project

01Employee access & gate management
SYSTEM NOTEWhy enterprise integrations are harder than the API documentation suggests
All case studies