Connecting Booking Systems to Access Control

How I connected a booking system to an access control system so door permissions follow room allocations automatically, without anyone remembering to do it.

The Integration Gap

A university college has two separate systems that need to work together:

• KX (Kinetic Solutions) - the accommodation system. Staff book students into rooms here. It knows "John Doe is in Cripps A 26 from October to July."
• Salto Space - the door lock system. Controls which keycards open which doors. It knows "John Doe's card can open Cripps A26."

The problem isn't just "two systems don't talk". It's ownership. KX is where decisions happen (who gets which room, and when). Salto is where the door actually opens. That gap creates a question nobody wants to answer: who turns a booking into access?

Three departments touch this process: Accommodation creates bookings, Porters deal with keys and lockouts, Student Services handle arrivals and changes. Without automation, everyone assumes it's "someone else's job".

"You do it." "No, you do it."

Before the integration: Manual work. Someone opens Salto, finds the right doors (bedroom, building entrance, shared facilities), and copies dates from KX. Slow. Repetitive. Error-prone. A single typo becomes a student standing outside their room.

I built an integration that makes this automatic. KX stays the source of truth. Salto gets updated on a schedule. Access follows bookings without anyone remembering to do it.

The Disconnect: Why a JOIN is Impossible

KX shows "Cripps A 26". Salto shows "Cripps A26" with a GUID. Close enough for humans, impossible for code.

KX knows who lives where and when. Salto controls which keycards open which doors. But they don't share a common identifier. Looking at these tables as an engineer, there is no reliable way to JOIN them.

The Data Reality (BEFORE)

Table: KX Rooms (Source)

Table: Salto Doors (Target) - BEFORE

The Problem: No foreign key relationship exists. String matching ("Cripps A 26" vs "Cripps A26") is fragile and error-prone. GUIDs are meaningless to KX. One typo in manual entry means a student can't get into their own room.

Creating a Common Language

1. Mapping doors to rooms

KX shows "Cripps A 26". Salto shows "Cripps A26" with a cryptic GUID. To connect them, I needed a shared key.

Before: Salto's ExtLockID was a GUID. After: renamed to KX_360 so it matches the KX database ID.

I created a naming convention. Rename every door's ExtLockID in Salto to include the KX Room_ID.

Pattern: KX_ + Room_ID. "Cripps A 26" (Room_ID 360) in KX becomes KX_360 in Salto. Now the code can match them.

How I matched every door

1. Exported all rooms from KX to a CSV file
2. Exported all doors from Salto to another CSV
3. Used an LLM to match names like "Cripps A 26" to "Cripps A26" (same room, different formatting)
4. Generated SQL to update Salto ExtLockID values to the KX_ naming pattern (400+ doors)

The Data Reality (AFTER)

Table: KX Rooms (Unchanged)

Table: Salto Doors (Modified) — AFTER

The Solution: Now the connection is reliable:

1SELECT
2    *
3FROM kx_rooms
4INNER JOIN salto_doors
5ON CONCAT('KX_', kx_rooms.Room_ID) = salto_doors.ExtLockID

SQL script (one-time setup)

1-- Run once to set ExtLockID based on LLM mapping
2UPDATE SALTOSPACE.dbo.tb_Locks SET ExtLockID = 'KX_360' WHERE id_lock = 937;
3UPDATE SALTOSPACE.dbo.tb_Locks SET ExtLockID = 'KX_361' WHERE id_lock = 898;
4UPDATE SALTOSPACE.dbo.tb_Locks SET ExtLockID = 'KX_420' WHERE id_lock = 922;
5-- ... (hundreds more)

In Salto’s SQL Server database this field is called ExtLockID; when we mirror the data into MariaDB for the staging and business-rules layer, we treat the same value as ExtDoorID. It’s the same identifier travelling through the pipeline.

Handling what KX cannot see

Individual room doors are the easy part. But students also need access to shared spaces: building entrance, stairwell, laundry room. KX only knows bedrooms (bookable rooms). Salto knows all doors. This gap needed explicit modeling.

Zone mapping: KX groups rooms into "blocks" (Cripps A = block 53). Salto zones are renamed to KXZ_53 using the same pattern.

A student in Cripps A room 26 (room ID 360, block ID 53) automatically gets:

1. Their bedroom door (KX_360)
2. All common areas in their building (KXZ_53) — entrance, stairs, corridors, laundry

What gets synced vs what stays manual

What gets synced automatically:

• Student bedroom doors (KX_360)
• Shared bathrooms between rooms (via Notes field)
• Building zones/common areas (KXZ_53)
• Entrance doors via zones
• Stairwells, elevators via zones

2. Handling shared bathrooms

Some bathrooms are shared between two or three rooms. These don't have their own KX room ID—they're facilities, not bedrooms.

Solution: Use Salto's "Notes" field to list which rooms share that bathroom. If the Notes field says KX_360,KX_361, then anyone in room 360 or 361 gets access.

_Shared facilities solution: Individual rooms get KX_ names. Shared bathrooms keep their GUIDs but use the Notes field to list which rooms they serve._

What a student gets (complete example)

A student booked into Cripps A room 26 (room ID 360, block ID 53) automatically receives:

1. Their bedroom door (KX_360)
2. Any shared bathroom listed in Notes (e.g., KX_360,KX_361)
3. All common areas in their building via zone (KXZ_53)

No porter typed anything. No manual sync. The booking in KX flows to door access automatically.

What we're building toward: The staging table

Before diving into the architecture, it helps to see what we're generating. The heart of this operation isn't the Python script—it's the database schema. Salto's Database Sync reads from a staging table we populate. Here's what a single student row looks like:

 1crsid:                'jd456'
 2FirstName:            'John'
 3LastName:             'Doe'
 4ToBeProcessedBySalto: 1        // Ready for import
 5
 6ExtDoorIDList:        {KX_360, 0, 2024-10-01T00:00:00, 2025-06-28T00:00:00}, {KX_361}
 7                      ^ Room 360 (time-limited) + Shared Bathroom 361 (permanent)
 8
 9ExtZoneIDList:        {KXZ_53, 0, 2024-10-01T00:00:00, 2025-06-28T00:00:00}
10                      ^ Cripps Building zone access

Key columns: ExtDoorIDList and ExtZoneIDList contain formatted access grants.

Format: Permanent access uses {ExtDoorID}. Time-limited access uses {ExtDoorID, 0, StartDate, EndDate}.

When ToBeProcessedBySalto = 1, Salto reads the row, parses the lists, and grants permissions. Our entire job is generating those strings correctly. The rest of this article explains how.

Architecture

The system follows a strict Extract → Stage → Calculate → Apply pattern. By decoupling the logic into a staging database, the process is:

• Idempotent: Can run multiple times safely without side effects
• Auditable: The staging table can be inspected before Salto applies changes
• Portable: Can run in a container, on a server, or locally during outages, no complex infrastructure dependencies
• Decoupled: Python moves data. SQL contains business logic.

 1┌───────────────┐   ┌───────────────┐   ┌───────────────┐
 2│   KX Live     │   │  CSV Cards    │   │ Salto System  │
 3│ (SQL Server)  │   │ (File System) │   │ (SQL Server)  │
 4│    Room       │   │  Valid Card   │   │ Users, Doors  │
 5│  Allocations  │   │   Holders     │   │    Zones      │
 6└───────┬───────┘   └───────┬───────┘   └───────┬───────┘
 7        │                   │                   │
 8        └───────────────────┼───────────────────┘
 9                            ▼
10                  ┌───────────────────┐
11                  │    db_tool.py     │
12                  │   (Python ETL)    │
13                  └─────────┬─────────┘
14                            ▼
15                  ┌───────────────────┐
16                  │   MySQL/MariaDB   │
17                  │ Staging Database  │
18                  └─────────┬─────────┘
19                            ▼
20                  ┌───────────────────┐
21                  │  Business Rules   │
22                  │   (SQL Views)     │
23                  └─────────┬─────────┘
24                            │
25              ┌─────────────┴─────────────┐
26              ▼                           ▼
27    ┌───────────────────┐       ┌───────────────────┐
28    │    Validation     │       │  Salto Space      │
29    │     Reports       │       │  Database Sync    │
30    │  (Excel Files)    │       │ (reads staging)   │
31    └───────────────────┘       └───────────────────┘

Data flows from three sources, merges in staging, business rules apply via SQL views, and output splits into validation reports (for humans) and access lists (for Salto).

The sync tool

I needed a simple tool to pull data from multiple sources and to run SQL files against each database reliably. db_tool.py does exactly that: it executes a SQL file against a chosen source and writes the results into another database or an export file.

1┌─────────────────────────────────────────────────────────────────────────────┐
2│  SYNC MODE (--to)                   │  EXPORT MODE (--out)                  │
3│                                     │                                       │
4│  SQL Server ─┐                      │  SQL Server ─┐                        │
5│  MySQL ──────┼──► db_tool.py ──►    │  MySQL ──────┼──► db_tool.py ──►      │
6│  .sql File ──┘       │              │  .sql File ──┘       │                │
7│                      ▼              │                      ▼                │
8│               MySQL / SQL Server    │               Excel / CSV             │
9└─────────────────────────────────────────────────────────────────────────────┘

Usage is simple: pass a SQL file, pick a source, and pick a destination.

 1# Sync room allocations from KX Live to local MariaDB staging
 2python db_tool.py sync \
 3    --truncate \
 4    --sql queries/sync-room-allocations-from-kxlive.sql \
 5    --from kxlive \
 6    --to mariavdt \
 7    --table kxroomallocations
 8 
 9# Export a validation report to Excel
10python db_tool.py export \
11    --table critical_students_losing_access_early \
12    --from mariavdt \
13    --out exports/critical_students_losing_access_early.xlsx

The --truncate flag clears the table first. Clean slate each run, no stale data.

Deciding who wins: A political decision

When KX says one thing and Salto says another, what wins? This wasn't a technical question—it was a political one. We evaluated three strategies before the departments agreed on a path forward.

The Verdict: Preserves KX

After testing all scenarios, Accommodation, Porters, and Student Services chose Strategy 2.

• Single source of truth: Room allocations live in KX. Period.
• Eliminates "shadow state": No more "but the porter said..." arguments. One place to check: KX.
• Forces process discipline: If someone needs early access, the change happens in KX.

The tradeoff? Manual overrides must be reflected in KX or kept outside the sync. This took convincing, but it stopped mystery lockouts caused by stale manual data.

Deep Dive: The SQL Behind "Preserves KX"

This is simplified pseudocode for the preserves-KX logic – the real production view is more verbose and joins via internal Salto IDs, but the core idea is the same.

 1SELECT
 2    consolidated_kx.crsid,
 3    CASE
 4        -- New access: KX dates only
 5        WHEN salto_users_locks.ExtDoorID IS NULL THEN
 6            CONCAT('{', salto_doors.ExtDoorID, ', 0, ',
 7                   DATE_FORMAT(consolidated_kx.EarliestArrivalDate, '%Y-%m-%dT%H:%i:%s'), ', ',
 8                   DATE_FORMAT(consolidated_kx.LatestDepartureDate, '%Y-%m-%dT%H:%i:%s'),
 9                   '}')
10 
11        -- Existing access: KX overwrites Salto dates
12        WHEN salto_users_locks.UsePeriod = 1
13            AND consolidated_kx.EarliestArrivalDate IS NOT NULL THEN
14            CONCAT('{', salto_doors.ExtDoorID, ', 0, ',
15                   DATE_FORMAT(consolidated_kx.EarliestArrivalDate, '%Y-%m-%dT%H:%i:%s'), ', ',
16                   DATE_FORMAT(consolidated_kx.LatestDepartureDate, '%Y-%m-%dT%H:%i:%s'),
17                   '}')
18 
19        -- Fallback: permanent access (no dates in KX)
20        ELSE CONCAT('{', salto_doors.ExtDoorID, '}')
21    END AS door_access_string
22FROM consolidated_kx
23INNER JOIN salto_doors ON
24    -- Direct room match
25    consolidated_kx.ExtDoorID = salto_doors.ExtDoorID
26        OR
27        -- Shared facility match (bathrooms via Notes field)
28    FIND_IN_SET(consolidated_kx.ExtDoorID, salto_doors.Notes) > 0
29LEFT JOIN salto_users_locks ON
30    salto_users_locks.crsid = consolidated_kx.crsid
31        AND salto_users_locks.ExtDoorID = salto_doors.ExtDoorID;

The FIND_IN_SET join is the key to shared bathrooms. If salto_doors.Notes contains KX_360,KX_361, then any student with ExtDoorID = KX_360 or KX_361 gets access to that bathroom.

The hourly pipeline

The whole sync runs automatically every hour. Each run produces staging rows like the one shown earlier—with ExtDoorIDList and ExtZoneIDList strings that Salto parses to grant access. Here's the flow:

 1┌─────────────────────────────────────────────────────────────────┐
 2│  STEP 1: LOAD                                                   │
 3│  Pull fresh data from three sources into staging database       │
 4│  - KX room allocations (SQL Server)                             │
 5│  - Salto users, doors, zones (SQL Server)                       │
 6│  - Valid card holders (CSV file)                                │
 7└────────────────────────────┬────────────────────────────────────┘
 8                             │
 9                             ▼
10┌─────────────────────────────────────────────────────────────────┐
11│  STEP 2: CALCULATE                                              │
12│  Run SQL views to apply business rules                          │
13│  - Match users to their doors (direct + shared facilities)      │
14│  - Calculate zone access (building entrances)                   │
15│  - Build ExtDoorIDList and ExtZoneIDList strings                │
16└────────────────────────────┬────────────────────────────────────┘
17                             │
18                             ▼
19┌─────────────────────────────────────────────────────────────────┐
20│  STEP 3: UPDATE                                                 │
21│  Write calculated access lists back to staging table            │
22│  - Format ready for Salto consumption                           │
23└────────────────────────────┬────────────────────────────────────┘
24                             │
25                             ▼
26┌─────────────────────────────────────────────────────────────────┐
27│  STEP 4: SALTO SYNC                                             │
28│  Salto import process reads staging table                       │
29│  - Assigns door/zone permissions to cards                       │
30│  - Students can now open their doors                            │
31└────────────────────────────┬────────────────────────────────────┘
32                             │
33                             ▼
34┌─────────────────────────────────────────────────────────────────┐
35│  STEP 5: VALIDATION                                             │
36│  Generate Excel reports for manual review                       │
37│  - Who's missing expected access?                               │
38│  - Any conflicting room allocations?                            │
39│  - What changed since last run?                                 │
40└─────────────────────────────────────────────────────────────────┘

The whole thing runs as a single bash script:

 1#!/bin/bash
 2 
 3# 1. Load fresh data from all sources
 4python db_tool.py sync --truncate --sql queries/sync-room-allocations-from-kxlive.sql --from kxlive --to mariavdt --table kxroomallocations
 5python db_tool.py sync --truncate --sql queries/sync-salto-doors.sql --from vsalto --to mariavdt --table salto_doors
 6python db_tool.py sync --truncate --sql queries/sync-salto-users.sql --from vsalto --to mariavdt --table salto_users
 7 
 8# 2. Run SQL views to calculate access lists
 9mysql -D salto < queries/update-staging-with-access-lists.sql
10 
11# 3. Export validation reports
12python db_tool.py export --table access_date_mismatches --from mariavdt --out exports/access_date_mismatches.xlsx
13python db_tool.py export --table users_wrong_room --from mariavdt --out exports/users_wrong_room.xlsx

Salto's Database Sync picks up the staging table automatically. Students can open their doors.

Flagging what the sync can't fix

The sync works great when data is clean. But it can't fix missing keycards, misconfigured doors, or students with access to wrong rooms. That's what validation reports are for—they flag issues needing human judgment.

Daily validation reports

Example 1: Students Missing Door Access

Action: Check if door exists in Salto with correct ExtDoorID, verify student has valid card, run sync manually if needed.

Example 2: Students Missing Zone Access

Action: Verify zone mapping between KX sublocation IDs and Salto ExtZoneIDs, then re-run sync.

Example 3: Users with Access to Wrong Rooms

Action: Review and let the sync remove automatic KX-managed doors that are not in the proposed list. Manual doors remain untouched and must be handled separately.

Business Impact

Operational Efficiency

Eliminated manual data entry between KX and Salto. Accommodation team no longer waits for Porters to update access. Access granted automatically, including evenings and weekends when offices are closed.

Risk Reduction

• Eliminated "phantom access": Departed students no longer keep access after leaving
• No more shortcuts: Students get correct time-bounded dates from KX instead of permanent access "because it's easier"
• One source of truth: Only Accommodation updates KX. Porters don't mirror updates in Salto.
• Fewer late-night emergencies: Reduced lockouts from "someone forgot to update"

Reliability

Successfully handled full annual intake volume. Zero manual intervention required during peak periods. The system runs hourly without supervision.

Cost

$0 in additional software licensing. Built with existing infrastructure (Python, SQL, scheduled tasks).

_The complete mapping: Individual rooms use KX_ naming (Cripps A26 = KX360), shared bathrooms use Notes field to list which rooms they serve (KX_360,KX_361). This floor plan shows how naming convention connects KX room allocations to Salto door access.

Takeaways

1. Define an ID contract. KX_ for rooms. KXZ_ for zones. Both systems speak the same language. No mapping table to maintain—just a naming convention.

2. Model the doors KX can't see. Bathrooms, staircases, entrances—they need explicit relationships (Notes field, zone mappings) because the booking system doesn't know they exist.

3. Put business logic in SQL, not code. When the Bursar asked why a student was locked out, I ran SELECT * and showed exactly what the query returned. Anyone with database access could verify the logic.

4. The hardest part isn't technical. Convincing porters that "KX is the single source of truth" took months. People hate losing manual overrides. But once everyone agreed, the mystery lockouts stopped.

5. Let allocations drive access, automatically. The booking is the decision. The door lock is the enforcement. Sync them hourly, and responsibility stops being "someone else's job."