bot_staff — employee shift tracking system in Telegram

The shift tracking system consists of two interconnected Telegram bots over shared PostgreSQL tables (bots.bot_warehouse_staff — shifts, bot_warehouse_staff_user — employees): the user bot records timestamps, while the admin bot reads and aggregates them. Both are built on pyTelegramBotAPI (declarative handlers, ReplyKeyboardMarkup/InlineKeyboardMarkup, bot.polling loop with ApiTelegramException handling, including error 409 for duplicate launches). Database access is managed via psycopg2 with DictCursor and configured TCP keepalive.

User bot

The user bot implements registration as a finite state machine (waiting_for_name, waiting_for_role) using in-memory dictionaries. Employees are identified by telegram_username — upon re-entry, the profile is automatically pulled from the database. Name and role changes are supported with cascading updates across all related records in both tables; in case of a conflict on telegram_username, the old record is transferred to the new name, and saving is resilient to race conditions due to INSERT ... ON CONFLICT ... DO UPDATE. Shift recording is based on invariants: a new shift cannot be started if an old one is still open (checking for a record with check_point_start and without check_point_finish) and a shift cannot be closed if it was not started; closing updates the last unfinished record by addressing it via the system ctid through a subquery ORDER BY check_point_start DESC LIMIT 1.

Admin bot

The admin bot provides two actions. “Push Report” generates a daily text summary: data is grouped by employees, sorted by time within each group, and the type of timestamp (started/ended) is determined for each record; long summaries are split into parts under the Telegram limit. “Download Report” exports a complete report for all time in .xlsx format, generated via openpyxl: a workbook with two sheets (employees and shifts) featuring bold headers, alignment, and auto-width adjustment; the file is sent as a document and deleted after sending.