История о том, как просьба «глянь, что у меня тут с табличками» превратилась в production-сервис с распознаванием по фото, защищённой авторизацией админкой и автопушем в Google Sheets. Со всеми граблями, фейлами и инсайтами.

---

Контекст

Моя девушка работает в IT-департаменте организации с 16 территориальными филиалами, складом и парой клиник. На балансе примерно 5000 единиц оргтехники: моноблоки, МФУ, ноутбуки, принтеры, СКЗИ. Раз в год — инвентаризация.

Исходные данные:

• Главная выгрузка из учётной системы — Выгрузка.xlsx, 4792 строки, 77 колонок.

• 6 инвентарных книг по материально-ответственным лицам (МОЛам) — формат .xls, бухгалтерская форма. Задача звучала так: «Сделай сервис, чтобы можно было загрузить фотку шильдика, и он мне сказал, у кого эта железка стоит». Звучит просто. На практике это половина задачи. Вторая половина обнаружилась по ходу.

---

Стек

• Backend: Python 3.12, FastAPI, SQLAlchemy 2, SQLite (на старте — оказался уместен и потом).

• ETL: pandas + openpyxl для .xlsx, xlrd 1.2 для .xls (про эту боль — отдельно).

• AI: Claude Opus 4.6 через OpenRouter (vision-API).

• Интеграции: gspread + Google Service Account для пуша в Google Sheets.

• Frontend: Jinja2 + ванильный CSS с дизайн-токенами (без React/Vue — overkill для админки).

• Деплой: Docker Compose, single container. Никаких микросервисов, никакого Postgres, никакого Kubernetes. Один контейнер, одна SQLite, один FastAPI.

---

Боль №1: .xls против pandas 2.x

Открываем файлы. Выгрузка.xlsx — современный формат, читается pandas + openpyxl без проблем. А вот 6 книг МОЛов — старый .xls, бухгалтерский экспорт из 1С.

import pandas as pd
df = pd.read_excel("mol_1.xls", engine="xlrd")

Получаем:

ImportError: Pandas requires version '2.0.1' or newer of 'xlrd'
(version '1.2.0' currently installed).

Ставим свежий xlrd:

pip install -U xlrd

Запускаем снова:

xlrd.biffh.XLRDError: Excel xlsx file; not supported

Сюрприз: xlrd 2.0 выпилили поддержку старого .xls. А pandas 2.x требует xlrd ≥ 2.0. Получился deadlock: ставить либо pandas 1.x, либо обходить pandas.

Выбрал второе — читать .xls напрямую через xlrd 1.2:

import xlrd
 
def parse_book(xls_path):
    wb = xlrd.open_workbook(str(xls_path))
    sh = wb.sheet_by_name("Инвентарная книга")
    rows = []
    for r in range(sh.nrows):
        first = sh.cell(r, 0).value
        if not first or first.lower().startswith(("счет", "мол", "итого", "инв.")):
            continue
        rows.append({
            "inv_number": first,
            "serial_number": sh.cell(r, 19).value,
            "book_initial_cost": _to_float(sh.cell(r, 32).value),
            # ...
        })
    return rows

В таблицах оказались служебные строки-разделители (Счёт 0901…, МОЛ …-ос, Итого по МОЛу…), которые надо было пропускать. И стрёмные ячейки типа КАДРЫ Инвентарная книга (полная) по состоянию на … в первой строке (когда в .xls плохо распарсились merged cells).

Урок: для старых форматов офиса берите дедушкин xlrd 1.2 и читайте напрямую. Не пытайтесь подружить его с современным pandas — даже не пытайтесь.

---

Боль №2: нормализация номеров (та, которая «казалось бы простая»)

Я наивно думал, что инвентарные и серийные номера — это просто строки. Сверять их через == и всё. Хах.

Реальная выборка из книги (привожу обезличенные примеры в том же формате, что встречались):

002410124-001287    ← образцовая запись
002410124-0003750.  ← лишние нули и точка в конце
002410124--003917   ← двойной дефис (опечатка)
002410134003663.    ← вообще без дефиса
210134-12           ← короткий формат
110134-_001146      ← подчёркивание-разделитель

А серийник вообще:

DQVRZER1380030794C3000              ← норм
DQVUYER00K144003B43000 555-2358     ← с хвостом через пробел
410134-1482/DQVRZER1380040856C3000  ← инвентарный код склеен через слеш
CNDRPBV9RQ./555-1929                ← мусор после точки и слеша
`-                                  ← вообще не серийник, мусорный плейсхолдер

Сначала написал функцию нормализации:

def norm_key(value):
    s = str(value).strip().upper()
    # хвост через пробел/слеш — берём первый токен
    for sep in (" ", "/", "\\"):
        if sep in s:
            s = s.split(sep, 1)[0]
    # выкидываем мусор, оставляем буквы, цифры, дефис
    s = re.sub(r"[^A-Z0-9-]", "", s)
    # схлопываем двойные дефисы
    while "--" in s:
        s = s.replace("--", "-")
    # без дефиса и слишком длинный — вставляем дефис после первых 9 цифр
    if "-" not in s and s.isdigit() and len(s) >= 12:
        s = s[:9] + "-" + s[9:]
    # ведущие нули после дефиса
    parts = s.split("-")
    if len(parts) == 2 and parts[1].isdigit():
        parts[1] = parts[1].lstrip("0") or "0"
        s = "-".join(parts)
    return s or None

Этого хватило для 80% случаев. Но не для всех. Какие-то номера в книге писали с двойным дефисом, какие-то без дефиса вообще. Дописал fallback по чистым цифрам:

def digits_only(value):
    s = str(value).strip().upper()
    for sep in (" ", "/", "\\"):
        if sep in s:
            s = s.split(sep, 1)[0]
    digs = "".join(ch for ch in s if ch.isdigit())
    return digs.lstrip("0") or digs or None

При импорте делаю четыре попытки: точный матч по inv_norm, потом по serial_norm, потом по inv_digits, потом по serial_digits. Этим обходом удалось склеить ещё около 50 записей.

Ключевой баг, который я обнаружил поздно

Сделал универсальную values_match(), которая считает совпадение, если хоть какая-то нормализованная форма пересекается. Применил для сравнения и инвентарных номеров, и серийников.

И обнаружил, что записи Z78VBJACB002YJA и Z78VBZJACB002TLX (разные железки) система считает одной.

Разбор:

Оба превращаются в 78002. Совпало.

Урок: для инвентарных номеров формата XXXNNN-NNNNNN цифровой fallback нужен — иначе не сшить разные форматы записи. Для серийников он категорически вреден — в серийниках буквы значимы.

Пришлось разделить логику:

def inv_match(a, b):
    """Для инв.номеров: норм + digit-fallback."""
    ka = all_keys(a, with_digits=True)
    kb = all_keys(b, with_digits=True)
    return bool(ka and kb and (ka & kb))
 
def serial_match(a, b):
    """Для серийников: ТОЛЬКО полная alphanumeric нормализация."""
    ka = all_keys(a, with_digits=False)
    kb = all_keys(b, with_digits=False)
    return bool(ka and kb and (ka & kb))

Один из лучших примеров того, как похожие задачи имеют разные правила, и не стоит писать «универсальный» хелпер, если данные неоднородные.

---

Боль №3: «главная таблица — Выгрузка»

В первоначальном ТЗ я предположил: книги МОЛов — это детализация Выгрузки, плюс несколько строк, которых в Выгрузке нет. Решение очевидное: загрузить и то, и другое, сматчить по ключам, остальное создать новыми записями.

После первого прогона:

• Master Выгрузка: 4792 актива

• Книги МОЛов: 432 строки

• Сматчено: 305

• Создано новых из книг: 113 Показал заказчице. Реакция: «Зачем создавать новых? Главная таблица — Выгрузка. Если в книге МОЛа что-то есть, а в Выгрузке нет — это просто ошибка в книге, надо в отчёт, а не в БД».

Переписываю логику. Теперь книги — только источник обогащения. Они проставляют МОЛ, филиал, заполняют book_* поля. Если строка из книги не находит пары в Выгрузке — она пишется в reconciliation_issue со статусом only_in_book для отчёта, но в БД активов её нет.

def import_book(db, xls_path):
    rows = parse_book(xls_path)
    run = ReconciliationRun(started_at=datetime.utcnow())
    db.add(run)
    db.flush()
 
    for r in rows:
        asset = find_match(db, r)  # 4 попытки матча
        if asset is None:
            db.add(ReconciliationIssue(
                run_id=run.id, kind="only_in_book",
                inv_number=r["inv_number"],
                serial_number=r["serial_number"],
                description="Есть в книге, нет в Выгрузке",
            ))
            continue
        # обновляем book_* поля
        asset.book_inv_number = r["inv_number"]
        asset.book_serial_number = r["serial_number"]
        asset.book_row_no = r["book_row_no"]
        asset.mol_id = mol.id
        # ...

Урок: если ваш продукт работает с уже существующим источником истины — не создавайте параллельную реальность. Лучше отдельный отчёт-журнал того, что отклоняется от стандарта.

---

Боль №4: задвоение оборудования

В примечаниях к активам в Выгрузке встречаются записи вида:

• Фактическое задвоение инвентарника (каб. 105)

• записан как 002410124-003000, но дублируется с DQVRZER138017016AA3000

• 002410124-003740 задвоен с RBU0X16179 То есть в учёте у бухгалтерии одна и та же физическая железка иногда числится дважды под разными номерами. Сделал детектор:

def is_duplicated(notes):
    if not notes:
        return False
    return any(k in notes.lower() for k in ("задво", "дубл", "повтор"))
 
def extract_candidates(notes):
    """Извлекает похожие на инв.№ и серийники строки из примечания."""
    out = set()
    for m in re.finditer(r"\b\d{5,9}-_?\d+\b", notes):
        out.add(m.group())
    for m in re.finditer(r"\b[A-Z][A-Z0-9]{5,}\b", notes, re.I):
        out.add(m.group())
    return list(out)

В карточке появляется красный баннер «Задвоение» со ссылками на найденные парные активы.

Потом заказчица показала пример: у актива инвентарный номер Выгрузки 002410134-002722, серийник PHCLK09100. А в книге МОЛа тот же инвентарный номер, но серийник PHCL628191. То есть это разные железки под одним номером. Текстового «задвоен» в примечании нет, но фактически это и есть задвоение.

Дописал детектор «молчаливого» задвоения: ищем в БД другие активы, у которых хоть какой-то ключ (inv_norm, serial_norm, book_inv_digits, book_serial_digits) пересекается с текущим:

def find_duplicates_by_data(db, asset):
    """Активы с общими ключами — потенциальные задвоения."""
    pairs = {}
    for column, value, reason in build_checks(asset):
        for other in db.query(Asset).filter(
            Asset.id != asset.id, column == value
        ).limit(10):
            pairs.setdefault(other.id, (other, reason))
    return list(pairs.values())

В базе из 4792 активов 747 имеют пересечения ключей с другими. Это много. Возможно, это часть учётной реальности (одна позиция переведена с одного баланса на другой, но не списана со старого), но в любом случае об этом нужно знать.

---

Распознавание по фото

Самая «вау-фича» в ТЗ. Реализовал через OpenRouter — у них единый API ко всем моделям, удобно. Использовал anthropic/claude-opus-4.6 с vision.

SYSTEM_PROMPT = (
    "Ты помощник по инвентаризации. На фото — серийный номер (S/N, "
    "Serial, P/N, заводской) либо инвентарный номер на корпусе устройства. "
    "Извлеки инвентарный номер (типичный формат '002410124-XXXXXX' или "
    "'210134-12') и/или серийный номер. "
    'Верни СТРОГО JSON без пояснений: {"inv_number": "...", '
    '"serial_number": "...", "confidence": 0.0, "notes": "..."}.'
)
 
async def recognize_image(image_bytes, content_type="image/jpeg"):
    b64 = base64.b64encode(image_bytes).decode()
    payload = {
        "model": "anthropic/claude-opus-4.6",
        "messages": [
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": [
                {"type": "text", "text": "Извлеки номера. Верни JSON."},
                {"type": "image_url", "image_url": {
                    "url": f"data:{content_type};base64,{b64}"
                }},
            ]},
        ],
        "temperature": 0,
        "max_tokens": 400,
    }
    async with httpx.AsyncClient(timeout=60) as c:
        r = await c.post(f"{BASE}/chat/completions",
                         headers={"Authorization": f"Bearer {KEY}"},
                         json=payload)
        return _extract_json(r.json()["choices"][0]["message"]["content"])

Цена за запрос на Opus 4.6 в районе 1-3 центов. Точность для чётких фото шильдиков HP/Acer — практически 100%. Для затёртых наклеек СКЗИ хуже, но и человек там часто не разберёт.

Дальше поток такой: фото → распознанные inv_number и serial_number → ищем в БД через find_by_inv_or_serial, который нормализует ключ и пробует точное совпадение, fallback по цифрам, fallback по book_inv/book_serial. Если нашли — открывается карточка. Если нет — кнопка «Добавить новый актив» с предзаполненными полями.

UI распознавания переписал с примитивного <input type="file"> и отображения сырого JSON на полноценный drag&drop с preview и аккуратными карточками результата. Получилось похоже на хороший SaaS.

---

Кнопки инвентаризации и Google Sheets

Заказчица: «Нужно две кнопки на карточке: Соответствует и Не соответствует. И чтобы при выборе второй можно было описать, что именно не сошлось. И в Google-таблицу автоматически складывать».

Простая схема:

@app.post("/asset/{asset_id}/verify")
async def asset_verify(asset_id, request, db):
    form = await request.form()
    status = form.get("status")  # 'ok' | 'fail'
    comment = form.get("comment", "").strip() or None
 
    asset = db.get(Asset, asset_id)
    record = Verification(asset_id=asset.id, status=status, comment=comment)
    db.add(record)
    asset.verification_status = status
    asset.verification_comment = comment
    asset.verified_at = datetime.utcnow()
 
    try:
        push_verification(asset, record)  # → Google Sheets
        record.pushed_to_sheets_at = datetime.utcnow()
    except Exception:
        logging.exception("Sheets push failed")
        # не валим запрос — в БД сохранили
 
    db.commit()

Для Google Sheets — gspread + service account. Сервисный аккаунт нужно отдельно «расшарить» в таблицу как редактора:

def push_verification(asset, verification):
    if not settings.google_credentials_path:
        return
    creds = Credentials.from_service_account_file(
        settings.google_credentials_path,
        scopes=["https://www.googleapis.com/auth/spreadsheets",
                "https://www.googleapis.com/auth/drive"])
    client = gspread.authorize(creds)
    ws = client.open_by_key(settings.google_spreadsheet_id).worksheet("Проверено")
    next_row = len(ws.col_values(1)) + 1
    ws.append_row(asset_to_row(asset), value_input_option="USER_ENTERED")
    # покраска по статусу
    color = GREEN if asset.verification_status == "ok" else RED
    ws.format(f"A{next_row}:L{next_row}", {"backgroundColor": color})

Строки автоматически красятся: зелёным «соответствует», красным «не соответствует». Параллельно сохраняется в verification (полная история проверок) и в asset.verification_status (текущий статус).

---

Деплой и неожиданности с SSH

Заказчица дала сервер с рутовым доступом по паролю. Подключаюсь:

$ ssh root@XX.XX.XX.XX
Connection closed by XX.XX.XX.XX port 22

Соединение рвётся ещё до баннера. Проверяю TCP — открыт. nmap на стандартные альтернативные SSH-порты — все «открыты», но это иллюзия (cloud-провайдер маскирует closed в open). На https:// той же машины тем временем работает какое-то другое приложение. То есть SSH-демон специально режет внешние соединения.

Спрашиваю заказчицу. «А, перепутала, вот другой сервер». Подключаюсь — порядок. Ubuntu 24.04, чистая, без Docker. Память 1.9 ГБ. Класс.

# Ставим Docker
curl -fsSL https://get.docker.com | sh
 
# Заливаем проект (1.1 МБ архив)
scp inventory-service.tar.gz root@SERVER:/opt/
ssh root@SERVER 'cd /opt && tar xzf inventory-service.tar.gz \
  && cd inventory-service \
  && docker compose -f docker-compose.prod.yml up -d --build'

Через 3 минуты приложение крутится на 80 порту. Заказчица заходит, говорит: «А пароль где?»

Точно. Открыто всему интернету без авторизации. Срочно делаю Basic Auth middleware:

@app.middleware("http")
async def basic_auth_middleware(request, call_next):
    if not (settings.app_username and settings.app_password):
        return await call_next(request)
    auth = request.headers.get("authorization", "")
    if auth.startswith("Basic "):
        try:
            decoded = base64.b64decode(auth[6:]).decode()
            user, _, pwd = decoded.partition(":")
            if (secrets.compare_digest(user, settings.app_username)
                    and secrets.compare_digest(pwd, settings.app_password)):
                return await call_next(request)
        except Exception:
            pass
    return Response(status_code=401,
                    headers={"WWW-Authenticate": 'Basic realm="Inventory"'})

Первая попытка деплоя упала с UnicodeEncodeError: 'latin-1' codec can't encode characters — потому что я заботливо написал realm="Инвентарь" (по-русски). HTTP-заголовки только ASCII. Поправил, передеплоил — за 3 минуты на сервер со свежей сборкой Docker.

---

Цифры в финале

После всех итераций:

---

Чему меня это научило

«Сделай по ТЗ» — это не финальный план, а отправная точка. Документ из 11 разделов на 250 строк, который я написал в начале, изменился к финалу примерно на 60%. И это нормально. Главное — гибкая архитектура.

Эксельные данные всегда грязнее, чем выглядят. Двойные дефисы, неразрывные пробелы, маскированные NULL-ы (., `-, nan), merge-ячейки, ошибочные ведущие нули. К моменту, когда я закончил писать normalize.py, там было 5 функций и 80 строк только для одной задачи «привести инвентарный номер к канону».

Не пишите универсальные хелперы для неоднородных данных. История с values_match, который ломал сравнение серийников из-за цифрового fallback — классика. Если в основе ваших данных «инвентарный номер с буквенным префиксом» и «серийник с любым алфавитом» — это разные сущности с разными правилами сравнения.

SQLite — не игрушка. Для 5000 записей и одного пользователя — идеально. Один файл, никакой инфраструктуры, никаких миграций между средами. Перенесли — поехало. В 80% админок Postgres не нужен.

Basic Auth + Docker — рабочий минимум. Если внутрикорпоративный сервис на 5 человек, не нужны OAuth, JWT, refresh-токены и роли. Стандартный Basic Auth + HTTPS-прокси сверху закрывает 95% угроз и пишется за 30 строк.

AI vision действительно работает. Claude Opus 4.6 распознаёт инвентарные и серийные номера с фото шильдиков в полевых условиях (плохое освещение, угол, потёртости) с точностью 90%+. Самостоятельно тренировать OCR-модель под это — миллион долларов и 6 месяцев. OpenRouter — пара центов за вызов и 5 минут на интеграцию.

---

Что бы я сделал по-другому

• Сразу разделил бы матчинг по типам ключей (инвентарный против серийного), а не делал «универсальную функцию», в которую потом вкручивал исключения.

• Подключил бы Pydantic для парсинга книг МОЛов — конвертировал бы строки в dataclass с валидацией, было бы проще ловить дребезг данных.

• Сразу написал бы тесты на нормализацию. Я полагался на «прогон на боевых данных» — это работает, но баг с серийниками я бы поймал ещё на дев-машине, если бы был хотя бы один pytest-снимок реальных пар.

---

Закрытые двери и открытые задачи

Что не сделал и оставил на потом:

• Telegram-бот для распознавания (заготовка в коде есть, ждёт токен).

• HTTPS — сейчас Basic Auth через HTTP, что наивно. На очереди nginx + Let’s Encrypt либо Cloudflare Tunnel.

• Множественные проверяющие — сейчас идентификация анонимная (только дата).

• Импорт остальных филиалов — у заказчицы есть книги МОЛов пока по одному филиалу, остальные обещают подвезти.

---

Если вам присылают «помоги с табличкой инвентаря в Excel» — это всегда проект на 2 недели, а не «нарисуй пивот». Реальная учётная информация в реальной организации — это многослойный пирог из исторических форматов, неконсистентных правил и устаревших артефактов. И именно в этом она интересна.

P.S. Заказчица довольна. На главной странице — все инструменты, мобильная вёрстка, кнопки соответствия, автопуш в Google-таблицу с цветной заливкой. Открывает с планшета во время инвентаризации, фоткает шильдик, видит карточку, ставит галочку. Не пришлось ни разу лезть в Excel.