Electronic queue for international freight at a land border crossing
The operating circuit of a broker-agent: from free-form correspondence in two languages to structured requests, statuses, and an encrypted vault of credentials
Context
International freight through land border crossings runs on electronic queues: a truck crosses only against a pre-reserved slot, and a missed window means an idle vehicle, a blown delivery deadline, and contract penalties. A market of broker-agents has grown around the procedure — operators who file and shepherd bookings on the carrier's behalf. The client is a cross-border logistics operator on a busy direction, with a large share of carriers corresponding in Chinese.
Before the project the process ran on manual labor: requests arrived as free-form messenger correspondence in two languages, truck and cargo details were cross-checked by hand, and clients' credentials for the external reservation portal sat in scattered notes. The portal exposes no public API, so classic integration was off the table — and a broker holding other people's credentials cannot afford a single leak.
Approach
We split the system into two circuits over one database. A Telegram bot handles intake: an 11-step form on a finite state machine — from language selection to final confirmation — validates every answer before the transition, so half-filled requests cannot exist. The carrier picks Russian or Chinese at entry; the form logic stays single, only the texts switch.
The operator works in a server-rendered FastAPI + Jinja2 panel: session login, a request feed with statuses, a request card with booking fields, and a vault of carrier credentials. Server rendering is deliberate — it shrinks the attack surface and needs no separate frontend team.
Architecture
The domain core is three models — operator, carrier, application — with the request lifecycle formalized as statuses from intake to a confirmed booking with number and slot. Both circuits write to the same SQLAlchemy models, so a request created in the messenger appears in the operator's feed instantly, with no sync layer. Storage is SQLite with a migration path to Postgres via DATABASE_URL; a seed script makes from-scratch deployment reproducible.
The bot runs on aiogram 3.4 under Python 3.9: the dispatcher is created inside main() with handlers in a separate Router, turning an event-loop initialization constraint into a structural decision. Since the portal has no API, the final booking action stays with the operator, while the system owns everything around it and leaves room for later portal automation via a controlled browser.
Security model
Operator passwords are hashed with pbkdf2_sha256: the database holds a computationally expensive hash, useless for recovering the original. Carrier credentials are the opposite case — they must be shown back to the operator at booking time — so they are encrypted with symmetric Fernet, the key held in environment variables, never in code or the database. A database leaked without the key is unreadable.
The separation of the two cryptographic modes — irreversible hashing for our own users, reversible encryption for client credentials — is made at the architecture level and verified across the full cycle, from operator login to saving a booking with the complete field set.
Outcome & what shipped
The broker's operating process moved from personal memory and message feeds into a system: structured bilingual requests, a status and history per truck, and an encrypted credential vault. The language barrier is removed at the product level — a Chinese-speaking carrier goes through exactly the same scenario as a Russian-speaking one, without a translator and without a rise in detail errors.
The full cycle is verified on end-to-end scenarios, including a request with a Chinese-language cargo name and a booking saved with status, number, slot, carrier, and note. The next stage is scoped: a Playwright module that pre-fills the external portal with mandatory operator confirmation — automating the routine while keeping the final action human.
What we built
Telegram bot with a bilingual form
A step-by-step survey on a finite state machine, Russian and Chinese languages, request creation in the database and operator notification.
Status-check command
The carrier checks the state of their own request right in the bot, without contacting the operator.
Operator web panel
Server-rendered on Jinja2: session login, a request feed with statuses, a request card with booking fields.
Carrier-credential vault
Storage and display of external-portal access credentials under reversible encryption.
Security model
Operator passwords on pbkdf2_sha256, carrier credentials on Fernet with the key in environment variables.
Data schema and portable storage
Three models — operator, carrier, request; SQLite with a migration path to Postgres via DATABASE_URL.
Seed script
Creates a default operator account — reproducible from-scratch deployment of the system.
Engineering challenges
Two languages in one form without branching the code
The dialog runs in Russian and Chinese. The finite state machine holds the chosen language and substitutes texts as the steps go, so the form logic is single and the scenarios for both languages are identical and predictable.
Running aiogram on Python 3.9
Because of event-loop initialization specifics, the bot dispatcher is created inside main() and all handlers are moved to a separate Router. This removes the startup conflict and keeps the handlers isolated from the entry point.
Storing other people's passwords that must be returned
Carrier credentials can't just be hashed — the operator must see them at booking time. The solution is reversible Fernet encryption with the key in environment variables, separate from the hashing of the operators' own passwords.
An external portal with no public API
The reservation portal has no open interface, so the booking is done by the operator manually. The system takes on everything around the booking and leaves portal automation as a separate, controlled stage.