BLD_sync/CLAUDE.md

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

This is a data synchronization system (BLD_sync) that extracts data from:

• Microsoft Access databases (.accdb) located on network shares

And synchronizes it to:

• Microsoft SQL Server (CompanyDB) with multiple schemas organized by data type

The system supports both full initialization sync and incremental sync driven by a change log table.

Architecture

Data Flow

┌─────────────────────────────────────────────────────────────────────┐
│                         Network File Sources                        │
│  Access DBs (\\192.168.110.114\生产进度表\)                         │
└─────────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────────┐
│                          Sync Scripts                               │
│  • run_incremental_sync.py - Change log driven incremental sync     │
│  • init_full_sync.py      - Full table truncation/reload           │
└─────────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────────┐
│                      SQL Server (CompanyDB)                         │
│  Schemas: productionContractData, productWarehousing,               │
│           workshopOne/Two/Three, machining, contractPlanning,       │
│           inspectionRecords, partsWarehouse, etc.                   │
└─────────────────────────────────────────────────────────────────────┘

Note: The system previously also synced Excel .xlsm production-execution-card files into the warehouseOutbound schema (executionCardData, contractData, customerProductType). That Excel pipeline has been removed; the existing tables and their data remain in CompanyDB but are no longer updated.

Key Components

run_incremental_sync.py - Log-driven incremental sync

• Polls TableChangeLog table for unsynced records (Synced=0)
• Queries by TableAddress to match configured Access file paths
• Connects to the source Access DB, reads latest rows by primary key, then DELETE + INSERT
• Per-batch verification (delete/insert checks by primary key) before marking Synced=1
• Long-running service with configurable polling interval

init_full_sync.py - Full table reload

• Truncates target tables and reloads all data from Access
• Handles IDENTITY_INSERT ON/OFF for tables with identity columns
• Progress logging with row counts and throughput metrics

config/ - Configuration package

• database.py: SQL Server / Access driver settings (read from env via python-dotenv)
• file_sources.py: SYNC_MAPPING — nested dict mapping Access files → tables → SQL targets
• app_settings.py: LOG_TABLE_CONFIG, NTFY_CONFIG, UPTIME_KUMA_CONFIG, POLL_INTERVAL, BATCH_SIZE
• Symbols are re-exported from config/__init__.py (e.g. from config import SYNC_MAPPING)

db_utils.py - Database connection utilities

• get_sql_conn(): SQL Server connection via pyodbc
• get_access_conn(): Access database connection
• fmt_table(): Safe table name formatting [schema].[table]
• generate_insert_sql(): Dynamic INSERT statement generation
• create_table_from_access(): Auto-create SQL Server table from Access cursor.description

ntfy_utils.py - Push notifications

• Sends alerts to ntfy server on errors/completion
• Uses Bearer token authentication

uptime_kuma_utils.py - Heartbeat monitoring

• UptimeKumaMonitor pushes heartbeats to Uptime Kuma; used by the incremental sync service

log_utils.py - Unified logging

• LoggerManager creates a timestamped log file under log/ and archives old logs by year-month

vbareplace.py / vba.txt - Access VBA helper tool

• Standalone drag-and-drop tool that injects the change-log VBA macro into Access forms and refreshes linked tables (packaged via PyInstaller; unrelated to the Python sync scripts)

Common Development Tasks

Install Dependencies

pip install -r requirements.txt

Run Full Initial Sync (Reload All Data)

python init_full_sync.py

Run Incremental Sync Service (Change Log Driven)

python run_incremental_sync.py

Configuration Management

Configuration lives in the config/ package (re-exported via config/__init__.py):

• Database credentials and driver strings come from environment variables (see .env.example), loaded by python-dotenv.
• Modify SYNC_MAPPING in config/file_sources.py to add new Access files/tables.

Important Implementation Details

SQL Server Connection

• Uses ODBC Driver 18 for SQL Server
• Requires TrustServerCertificate=yes due to self-signed cert
• Direct pyodbc connections (no SQLAlchemy); fast_executemany=True for bulk inserts

Access Database Connection

• Driver: {Microsoft Access Driver (*.mdb, *.accdb)}
• Direct file path connection via pyodbc

Type Mapping / Auto Table Creation

• When a SQL Server target table does not exist, it is created from the Access cursor.description.
• Access types map to SQL Server types: int→INT (PK → IDENTITY(1,1) PRIMARY KEY), float→FLOAT, bool→BIT, datetime→DATETIME, Decimal→DECIMAL(p,s), str→NVARCHAR(size) (size > 4000 → NVARCHAR(MAX)).

Identity Column Handling

Tables with identity columns require:

SET IDENTITY_INSERT [schema].[table] ON
-- perform inserts
SET IDENTITY_INSERT [schema].[table] OFF

See has_identity_column() in init_full_sync.py / run_incremental_sync.py for detection logic.

File Path Matching in Incremental Sync

The change log table stores paths in VBA format:

• Network: ;DATABASE=\\server\share\file.accdb
• Local: LOCAL:\path\to\file.accdb or LOCAL=\path\to\file.accdb

The code constructs multiple match patterns for robust matching.

Database Schema Organization

SQL Server schemas by function:

• productionContractData - Contract data by year (25年/26年压力表/温度计)
• productWarehousing - Finished product inspection/warehousing records
• workshopOne/Two/Three - Workshop production records
• machining - Machining process records (膜片焊接, 喷涂寄出, etc.)
• contractPlanning - Work order assignment records
• inspectionRecords - Quality inspection records
• partsWarehouse - Component inventory records
• thermometerRecord - Thermometer calibration/testing records
• solderingData - Soldering operation records
• TIGWelding - TIG welding records
• executionCardIssuanceRecord - Execution card issuance records
• tubeBending - Tube bending records
• warehouseOutbound - (Legacy) Excel-sourced tables: executionCardData, contractData, customerProductType — no longer updated

Notifications

The system uses ntfy for push notifications:

• Configured in NTFY_CONFIG within config/app_settings.py
• Sends on: errors, critical failures, task completion
• Authenticated via Bearer token

Logging

• File logs: log/ directory, one timestamped file per run (<prefix>_<YYYYMMDD>_<HHMMSS>.log)
• Console output: With emoji prefixes for status (✅ ❌ ⚠️ 🔄)
• Old logs are archived into log/Archive/YYYY-MM/ automatically (see LoggerManager.archive_old_logs in log_utils.py; archive_existing_logs.py is a one-time helper for historical logs)