Scale Application (RS232)

The Challenge: Shop owners faced critical fraud vulnerabilities in their legacy weighing system: employees could manually adjust or tamper with weight data before it was saved to the Microsoft Access database, creating opportunities for fraudulent transactions and inaccurate records. The legacy system's slow workflow required excessive manual data entry, increasing the risk of human error and reducing operational efficiency. Without proper data integrity controls, there was no way to detect or prevent tampering, making the system vulnerable to manipulation. The lack of integration with operational systems meant data had to be re-entered multiple times, creating inconsistencies and audit trail gaps. For regulatory compliance, especially with the Department of Internal Trade of Thailand, the system needed to provide legally defensible records with proof that weight data had not been modified after capture.

The Solution: I architected and built a two-module microservices system that completely eliminates fraud vulnerabilities while streamlining the weighing workflow. The first module is a .NET Core backend application that handles direct RS232 communication with scale monitor devices, capturing weight data in real-time and streaming it securely to the second module. The second module handles all business logic, weighing processes, and integrates with the FPN-Operation system via REST API using JWT authentication. Weight data can only originate from the first module—no manual input or editing is permitted, ensuring data integrity. The system uses MD5 hashing to validate each weighing record; if any data is tampered with, the hash validation fails and the system blocks the transaction. Queue tickets can be scanned to automatically retrieve data from the FPN-Operation system, eliminating manual data entry. All weighing data is synchronized to the operation system, and comprehensive reporting with CSV export capabilities provides full audit trails. This architecture ensures regulatory compliance, prevents fraud, and significantly improves workflow efficiency.

System Architecture: Hybrid Cloud-Local Model

The system employs a microservices architecture with clear separation of concerns between device communication and business logic. Module 1 (Device Communication Service) is a .NET Core application that runs as a dedicated service, continuously monitoring the RS232 serial port for scale device data streams. It handles low-level serial communication, data parsing, error detection, and device status monitoring. This module publishes weight data events to an internal message queue or REST API endpoint. Module 2 (Business Logic Application) is built with Next.js and TypeScript for the frontend, with a .NET Core backend API that handles all business logic, weighing workflows, user interactions, and external system integrations. The two modules communicate via REST APIs with JWT authentication, ensuring secure data transmission. This separation allows the device communication module to operate independently, maintaining device connections even if the business logic module requires updates or restarts. The architecture supports horizontal scaling, with multiple device communication modules potentially handling different scale devices, while the business logic module can scale to handle increased user load. PostgreSQL serves as the primary database, with Drizzle ORM providing type-safe database access and migrations.

Authentication & Authorization (OAuth + RBAC)

Authentication is implemented using a dual-layer approach: JWT-based authentication for API access and NextAuth framework for user session management. When the device communication module sends weight data to the business logic module, it authenticates using JWT tokens that include the device identifier and permissions. For web application users, NextAuth handles session management, providing secure login, session persistence, and automatic token refresh. User credentials are stored securely in PostgreSQL with password hashing. The system implements role-based access control (RBAC), where different user roles (operator, supervisor, administrator) have different permissions. API endpoints validate JWT tokens on every request, checking both token validity and user permissions. NextAuth sessions are stored server-side with secure HTTP-only cookies, preventing XSS attacks. The authentication system logs all login attempts and API access for audit purposes. This dual approach ensures that both automated device communications and human user interactions are securely authenticated and authorized.

Real-Time Data Pipeline & Consistency

The data pipeline is designed for reliability, integrity, and fraud prevention. When the scale device sends weight data through RS232, Module 1 captures the raw data stream, parses it according to the device protocol, and immediately calculates an MD5 hash of the weight value along with metadata (timestamp, device ID, transaction ID). This hash is stored alongside the weight data. Module 1 then sends the weight data and hash to Module 2 via REST API. Module 2 receives the data, validates the JWT token, and performs business logic operations (associating with queue ticket data from FPN-Operation system, calculating totals, etc.). Before saving to the database, Module 2 recalculates the MD5 hash and compares it with the received hash. If hashes don't match, the transaction is rejected and logged as a potential tampering attempt. Once validated, the data is saved to PostgreSQL with the hash stored for future verification. The system then synchronizes the weighing data to the FPN-Operation system via REST API with JWT authentication. All transactions are logged with timestamps, user IDs, and device identifiers, creating a complete audit trail. The pipeline implements retry logic for failed synchronizations and maintains a queue for offline scenarios.

Database & Performance Optimization

The PostgreSQL database schema is optimized for high-performance queries and data integrity. Weighting records are stored in a normalized schema with foreign keys linking to users, devices, queue tickets, and operational records. Strategic indexes are created on (device_id, created_at) for fast device-specific queries, (user_id, created_at) for user activity tracking, and (queue_ticket_id) for quick lookups when scanning tickets. The MD5 hash column is indexed to enable fast integrity verification queries. To prevent slow aggregation queries for reports, materialized views pre-compute daily and monthly summaries (total weights, transaction counts, device usage statistics) and are refreshed incrementally as new data arrives. Connection pooling via Drizzle ORM ensures efficient database connection management. Query optimization focuses on using indexes effectively, avoiding full table scans, and using EXPLAIN ANALYZE to identify and optimize slow queries. The schema includes check constraints to enforce data validation rules (e.g., weight values must be positive, timestamps must be valid). For CSV export functionality, queries use cursor-based pagination to handle large datasets efficiently without loading all records into memory. The database design supports regulatory compliance requirements with complete audit trails and tamper-evident records through MD5 hash validation.