02 / Vehicle entry & exit workflow
Two photos.
One consistent vehicle record.
A workshop workflow connects incoming vehicle photos to entry and exit records. Its database case tackles a specific failure: two first-entry requests arriving together could create two active records for the same plate.
Keep the operational decision in one place.
Telegram receives a photo. The workflow extracts a plate, routes uncertain readings for review and asks PostgreSQL to decide the vehicle’s entry or exit state. Google Sheets provides a reporting view, with separate refresh and failure handling.
in Telegram
database record
reporting view
This is useful when photo intake and reporting need to work together. A spreadsheet remains familiar for reviewing information while the database owns the entry/exit decision.
My contribution: connect the flow and reproduce the failure.
I used AI to connect photo intake, record handling and reporting, then build a test that sends two requests for the same vehicle at once. That made the duplicate-record problem reproducible and let me check the correction, rollback and separate-vehicle behavior.
The hard part was the first entry.
The earlier database function locked an existing active record before changing it. When a vehicle had no record yet, there was no row to lock. Two simultaneous calls could both decide to insert an entry.
The fix coordinates calls for the same plate before checking its state. A reproducible test uses two real PostgreSQL connections and a controlled waiting point to demonstrate the original failure and the corrected behavior.
Two simultaneous first-entry requests
Before the fix
2
active records for one plate
After the fix
1
active record; second request treated as a duplicate
A result another person can check.
The case retains the original function, the test harness and structured results. It covers the fresh schema and upgrade path, rollback, separate plates and the surrounding entry/exit transitions.
Technical decisions and verification boundary
A transaction-scoped advisory lock is taken for the plate before lookup. Existing row locking remains. The checked contract uses PostgreSQL READ COMMITTED; direct table writes and stronger snapshot isolation require their own concurrency contract.
These checks cover local database behavior. They do not establish extraction accuracy or end-to-end delivery across Telegram and reporting. Existing duplicate records need separate cleanup. The repository contains the implementation and reproduction steps.