The problem
A pharmaceutical group (a manufacturer with two brands, a distributor and an IT company) ran on five disconnected systems: a home-grown Node.js BI for sales, Excel files from distributors, a vendor portal for FaceID attendance, an order book in a legacy app, and 1C for accounting. Every report was assembled by hand, people existed under different names in each system (Cyrillic in one, Latin in another), and nobody could answer “who sold what last month” without opening three tools.
Leadership asked for one database. I chose Odoo 19 Community on Docker rather than writing another custom system: the HR, sales, inventory and access models already exist, and the work is in the domain logic, the migration and the guarantees.
What I built
pfl_sales— a faithful port of the old BI. Fact table for distributor sell-out and direct pharmacy sales, plans and adjustments, channel rules, name aliases. Three report pages (regions, distributors, home) are server-side SQL ported line by line from the old JavaScript, rendered with OWL client actions, filters kept in the URL, Excel export. An Excel import pipeline (ETL in plain Python, testable without Odoo) reads the same distributor files the old system did, previews what would change, then writes.lidermed_hr— attendance from FaceID. A cron pulls check-ins from a paid vendor API with a strict request budget, applies the company’s rules (lunch, Saturday hours, “late by one second is late”), and turns validated leave into “on leave” instead of “absent”. Leave approval is a RACI flow enforced in code: the consultant approves step one, the CEO the final step, nobody skips.lidermed_sale,lidermed_sla— order entry and inter-department deadlines. Seven order statuses with a single table of who may press what; free order types (samples, promo, exchange) with quotas and value caps; an SLA tracker that measures how long an order sat in each department in business minutes, with escalation to department heads and the CEO.lidermed_access— one person, one set of rights. Roles are attached to job positions, so an employee gets their rights from HR data; Odoo login and Telegram are two channels of the same user. Writes that would bypass roles are refused at the ORM level.lidermed_analytics— a clean analytics schema with real PostgreSQL row-level security. Tables without personal fields, column comments the agent reads as documentation, RLS policies derived from the Odoo rules, and a per-employee database role whose password is never stored.- Telegram agent (“Lidermed AI”). An employee asks in Uzbek; an LLM picks from 15 read-only tools (sales analysis with filters and comparisons, “why did it drop”, attendance, inactive customers, forecast, churn risk, scenario); every call runs as that employee through the Odoo API, so a regional manager sees only their own reps. A daily summary goes out at 08:00. The only write path is leave approval buttons, which call the RACI-checked Odoo methods as the pressing user.
How I know it is right
- “Port exactly, then prove parity.” A report or import counts as done only when its test shows zero differences against the legacy database restored side by side: region report, distributor report, home page, Excel import row by row, FaceID month totals, exports.
- Rights are tested three ways. An end-to-end test links a real regional manager and requires that Odoo web, the agent’s ORM path and the agent’s SQL path return the same number. The RLS test covers company and manager scope and refuses writes (22 checks).
- Honest ML. Forecast and churn models are backtested by time against a naive baseline. For next-month sales per rep, nothing beat “last month’s value” on nine months of history, so that is what ships; for pharmacy churn, logistic regression beat the rule baseline on top-100 precision (0.87 vs 0.66) and is used. Both tables are in the repo.
- Agent evaluation. A 50-question set across eight categories scores tool choice, the answer, and faithfulness (every number in the reply must exist in a tool result).
Decisions worth noting
- Entities are matched by legacy id, never by name: the old system had renamed people mid-way.
- Reports bypass ORM record rules on purpose (raw SQL), so the manager scope is enforced again in SQL and tested; the analytics layer repeats it a third time in RLS.
- Free LLM tiers only: tool arguments are parsed leniently, money is passed as formatted strings, multi-step “why” analysis runs on the server rather than in the model.
- Every UI label ships in Uzbek Latin and Russian; a test checks that no module has an untranslated string.
Status
In daily use for sales reporting and attendance since mid-October 2026; order entry and SLA went live on 22 October. Telephony integration, pricelists and the 1C bridge are still open.