Secom Fingerprint Integration Guide

Installation guide for integrating the Secom (S-One) fingerprint access-control system with the Hereby Platform attendance management.

Platform Version: v1.0.0 Target Audience: System Administrators, IT Staff

Integration Overview

Flow:

Secom terminal sends fingerprint/card scan events to Secom Manager
Secom Manager inserts events into secom_events table via ODBC in real time
Hereby background job reads pending events and creates/updates attendance records

Sync delay: typically 1–2 minutes

Prerequisites

Item	Requirement
OS	Windows 10/11 or Windows Server 2019+
Secom Manager	Secom Manager (Attendance/Cafeteria) — version with Secom Link support
PostgreSQL ODBC Driver	psqlodbc 17.00.0007 (32-bit)
Network	Secom PC → Hereby DB server TCP/5432 open
DB Server	PostgreSQL 16 (Hereby Platform default)

Step 1: DB Account & IP Permission Setup

Secom Manager connects to Hereby's database using a dedicated PostgreSQL account (hereby_secom) that has INSERT-only access to secom_events and is restricted to specific IP addresses.

1-1. Run Migration (Create Role)

The migration creates the hereby_secom PostgreSQL role:

bash

make db-migrate

Verify:

bash

pnpm --filter @hereby/database typeorm migration:show -d src/config/data-source.ts
# [X] AddSecomDbRole1773401000000  ← must be checked

1-2. Provision Per-Client Account

The provisioning script sets the password and prepares the IP allow configuration:

bash

make provision-secom \
  CLIENT=CLINIC01 \
  PASSWORD='Secom@2024!' \
  SECOM_IP=192.168.1.50/32

Parameter	Description
`CLIENT`	Client code (identifier, e.g. CLINIC01)
`PASSWORD`	Password for the `hereby_secom` account
`SECOM_IP`	Secom PC IP address in CIDR notation (e.g. `192.168.1.50/32`)
`PG_HBA`	(optional) Path to `pg_hba.conf` — auto-appends entry if provided
`DRY_RUN=true`	(optional) Print what would be applied without executing

1-3. Apply pg_hba.conf

The script outputs the required pg_hba.conf entry:

# Secom fingerprint integration — client: CLINIC01
hostssl  hereby_platform  hereby_secom  192.168.1.50/32  scram-sha-256

Apply in Docker environment:

bash

# Exec into the postgres container
docker exec -it hereby-postgres bash

# Append the entry
echo "# Secom fingerprint — client: CLINIC01" >> /var/lib/postgresql/data/pg_hba.conf
echo "hostssl  hereby_platform  hereby_secom  192.168.1.50/32  scram-sha-256" \
  >> /var/lib/postgresql/data/pg_hba.conf

# Reload configuration
psql -U hereby_admin -d hereby_platform -c "SELECT pg_reload_conf();"

1-4. Verify Permissions

bash

# Test INSERT as hereby_secom — must succeed
PGPASSWORD='Secom@2024!' psql \
  -h localhost -p 5432 \
  -U hereby_secom -d hereby_platform \
  -c "INSERT INTO hereby.secom_events (
        id, event_datetime, terminal_id, employee_number,
        organization_id, \"createdAt\", \"updatedAt\"
      ) VALUES (
        gen_random_uuid(), now(), 'TEST-01', '99999',
        '<org-UUID>', now(), now()
      );"
# INSERT 0 1  ← success

# Test SELECT — must fail
PGPASSWORD='Secom@2024!' psql \
  -h localhost -p 5432 \
  -U hereby_secom -d hereby_platform \
  -c "SELECT * FROM hereby.secom_events LIMIT 1;"
# ERROR: permission denied for table secom_events  ← expected

Account Security Principles

hereby_secom is INSERT-only on secom_events. No SELECT/UPDATE/DELETE.
Connections from any IP not listed in pg_hba.conf are rejected at the network level.
Passwords are never stored in version-controlled files — set at runtime via the script.
When replacing the Secom PC: re-run the script with the new IP, then remove the old pg_hba.conf entry.

Step 2: Install PostgreSQL ODBC Driver

1-1. Download and Install

Download psqlodbc_17_x86.msi (32-bit) from the PostgreSQL ODBC page
Run psqlodbc-setup.exe and complete installation
No reboot required

32-bit Required

Secom Manager uses 32-bit ODBC. Always install the 32-bit (x86) version.

1-2. Configure System DSN

Open Start Menu → search ODBC Data Sources (32-bit)
INFO
On 64-bit Windows, the 32-bit ODBC administrator is at C:\Windows\SysWOW64\odbcad32.exe
System DSN tab → Add
Select PostgreSQL Unicode → Finish
Enter connection details:

Field	Value
Data Source	`hereby`
Description	(optional) Hereby Platform
Database	`hereby_platform`
Server	`<Hereby DB server IP>`
Port	`5432`
User Name	`hereby_admin`
Password	`<DB password>`
SSL Mode	`require`

Click Test → confirm "Connection successful"
Click Save

SSL Certificate Error

If you get SSL errors, change SSL Mode to prefer or install the AWS RDS CA certificate on the system.

Step 2: Run Hereby DB Migration

Create the secom_events table for storing fingerprint scan events.

bash

# Run on the Hereby Platform server
make db-migrate

Verify migration:

bash

pnpm --filter @hereby/database typeorm migration:show -d src/config/data-source.ts

[X] AddSecomEventTable1773400000000 should be checked.

secom_events Table Schema

Column	Type	Description
`id`	UUID	Primary key
`event_datetime`	TIMESTAMPTZ	Fingerprint scan timestamp
`terminal_id`	VARCHAR(100)	Device terminal identifier
`card_number`	VARCHAR(100)	Card/badge number
`employee_number`	VARCHAR(100)	Secom employee number (maps to Hereby employeeNumber)
`employee_name`	VARCHAR(200)	Employee name from Secom system
`raw_event_type`	VARCHAR(50)	Raw Secom event code (e.g. "1"=in, "2"=out)
`auth_type`	VARCHAR(50)	Authentication method (fingerprint, card, PIN, etc.)
`ack_mode`	VARCHAR(50)	Secom acknowledgment mode
`ack_time`	TIMESTAMPTZ	Secom acknowledgment timestamp
`raw_data`	JSONB	Full raw payload for audit
`event_type`	ENUM	Normalized direction (check_in / check_out / unknown)
`status`	ENUM	Processing status (pending / processed / error / ignored)
`error_message`	TEXT	Error detail when status = error
`processed_at`	TIMESTAMPTZ	When attendance record was created
`organization_id`	UUID	Owning organization
`employee_id`	UUID	Resolved employee (filled after processing)
`attendance_id`	UUID	Created attendance record ID
`createdAt`	TIMESTAMPTZ	Record creation timestamp
`updatedAt`	TIMESTAMPTZ	Last update timestamp

Step 3: Configure Secom Manager ERP Settings

3-1. Open Secom Link Settings

Launch Secom Manager
File → ERP Settings → Secom Link

3-2. Connection Settings

Field	Value
Provider	`ODBC`
DSN	`hereby`
User ID	`hereby_admin`
Password	`<DB password>`

3-3. Query Configuration

Source Query (reads from Secom DB):

sql

SELECT
  CONVERT(VARCHAR, EventDate, 120) AS event_datetime,
  TerminalID                        AS terminal_id,
  AckMode                           AS ack_mode,
  CONVERT(VARCHAR, AckTime, 120)    AS ack_time,
  CardNo                            AS card_number,
  EmpID                             AS secom_employee_id,
  EmpName                           AS employee_name,
  EmpNo                             AS employee_number,
  EventType                         AS raw_event_type,
  AuthType                          AS auth_type
FROM Alarm
WHERE EventDate >= @StartDate
  AND EventDate <= @EndDate

Target Query (inserts into Hereby DB):

sql

INSERT INTO hereby.secom_events (
  id,
  event_datetime,
  terminal_id,
  ack_mode,
  ack_time,
  card_number,
  employee_number,
  employee_name,
  raw_event_type,
  auth_type,
  organization_id,
  "createdAt",
  "updatedAt"
) VALUES (
  gen_random_uuid(),
  @event_datetime,
  @terminal_id,
  @ack_mode,
  @ack_time,
  @card_number,
  @employee_number,
  @employee_name,
  @raw_event_type,
  @auth_type,
  '<organization UUID>',  -- find in Hereby Admin Panel
  NOW(),
  NOW()
)

Finding your Organization UUID

Check the Hereby Admin Panel → Settings → Organization Info, or query the DB:

sql

SELECT id, name FROM hereby.organizations WHERE code = 'YOUR_ORG_CODE';

Step 4: Employee Number Mapping

The Secom employee number must match the employeeNumber field in Hereby.

4-1. Verify Hereby Employee Numbers

Admin Panel → Employee Management → check the Employee Number column for each employee.

Or query directly:

sql

SELECT employee_number, name, email
FROM hereby.employees
WHERE "deletedAt" IS NULL
ORDER BY employee_number;

4-2. Map in Secom Manager

Secom Manager → Registration Management → Employee Management
For each employee, enter the Hereby employee number in the Employee Number field
Ensure exact match with the Hereby employee number

Warning

Incorrect employee number mapping will cause one person's attendance to appear under another employee's record. Always verify before go-live.

Step 5: Activation and Testing

5-1. Test Synchronization

Have a test employee use the fingerprint reader
After 1–2 minutes, verify event receipt in the DB:

sql

SELECT
  employee_number,
  employee_name,
  event_datetime,
  raw_event_type,
  event_type,
  status
FROM hereby.secom_events
ORDER BY "createdAt" DESC
LIMIT 10;

A status = 'pending' record confirms successful receipt
Once the processing job runs, status changes to 'processed' and an attendance record is created

5-2. Monitor Processing Status

sql

-- Event count by status
SELECT status, COUNT(*) AS count
FROM hereby.secom_events
WHERE "deletedAt" IS NULL
GROUP BY status;

-- Error events
SELECT employee_number, event_datetime, error_message
FROM hereby.secom_events
WHERE status = 'error' AND "deletedAt" IS NULL
ORDER BY event_datetime DESC
LIMIT 20;

-- Last 24-hour processing summary
SELECT
  DATE_TRUNC('hour', event_datetime) AS hour,
  COUNT(*) FILTER (WHERE status = 'processed') AS processed,
  COUNT(*) FILTER (WHERE status = 'error')     AS errors,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending
FROM hereby.secom_events
WHERE event_datetime >= NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1;

Operations Reference

Data Processing Rules

Situation	Handling
Events older than 20 hours	Auto-processed (manual review recommended)
Events older than 14 days	Manual bulk attendance upload via Admin Panel
Duplicate events	Set to `ignored` status

Secom PC Management

Secom PC must be always on (disable sleep mode)
Stable internet connection required
If network is disrupted, manually resend by date range in Secom Manager: File → ERP Settings → Secom Link → specify date range → Execute

Multi-Site Configuration

Single site, multiple legal entities: separate organization_id needed per entity
Multiple sites, separate Secom systems: each site needs its own Secom PC and account; differentiate by organization_id in the same secom_events table

Troubleshooting

ODBC Connection Failure

[ERROR] Data source name not found

→ Verify System DSN name is exactly hereby (case-sensitive) → Confirm settings were made in the 32-bit ODBC administrator (SysWOW64\odbcad32.exe)

SSL Error

[ERROR] SSL connection required

→ Set SSL Mode to require or prefer in ODBC settings → Verify the Secom PC IP is allowed for SSL connections in the DB server (pg_hba.conf)

Events Stuck in Pending

→ Check API server logs for the processing job → Verify employee_number mapping is correct:

sql

-- Employee numbers not matched to any Hereby employee
SELECT DISTINCT s.employee_number
FROM hereby.secom_events s
LEFT JOIN hereby.employees e ON e.employee_number = s.employee_number
WHERE e.id IS NULL AND s."deletedAt" IS NULL;

Secom Fingerprint Integration Guide ​

Integration Overview ​

Prerequisites ​

Step 1: DB Account & IP Permission Setup ​

1-1. Run Migration (Create Role) ​

1-2. Provision Per-Client Account ​

1-3. Apply pg_hba.conf ​

1-4. Verify Permissions ​

Step 2: Install PostgreSQL ODBC Driver ​

1-1. Download and Install ​

1-2. Configure System DSN ​

Step 2: Run Hereby DB Migration ​

secom_events Table Schema ​

Step 3: Configure Secom Manager ERP Settings ​

3-1. Open Secom Link Settings ​

3-2. Connection Settings ​

3-3. Query Configuration ​

Step 4: Employee Number Mapping ​

4-1. Verify Hereby Employee Numbers ​

4-2. Map in Secom Manager ​

Step 5: Activation and Testing ​

5-1. Test Synchronization ​

5-2. Monitor Processing Status ​

Operations Reference ​

Data Processing Rules ​

Secom PC Management ​

Multi-Site Configuration ​

Troubleshooting ​

ODBC Connection Failure ​

SSL Error ​

Events Stuck in Pending ​

Secom Fingerprint Integration Guide

Integration Overview

Prerequisites

Step 1: DB Account & IP Permission Setup

1-1. Run Migration (Create Role)

1-2. Provision Per-Client Account

1-3. Apply pg_hba.conf

1-4. Verify Permissions

Step 2: Install PostgreSQL ODBC Driver

1-1. Download and Install

1-2. Configure System DSN

Step 2: Run Hereby DB Migration

secom_events Table Schema

Step 3: Configure Secom Manager ERP Settings

3-1. Open Secom Link Settings

3-2. Connection Settings

3-3. Query Configuration

Step 4: Employee Number Mapping

4-1. Verify Hereby Employee Numbers

4-2. Map in Secom Manager

Step 5: Activation and Testing

5-1. Test Synchronization

5-2. Monitor Processing Status

Operations Reference

Data Processing Rules

Secom PC Management

Multi-Site Configuration

Troubleshooting

ODBC Connection Failure

SSL Error

Events Stuck in Pending