Vendor Default GL

Vendor Default GL — Technical Spec

Supports the no-PO path of AP invoice automation (skills/ap_invoice_automation): each QB vendor gets a default GL account to code a bill against when it isn't tied to a purchase order.

Tables

qb_gl_accounts — QB chart of accounts mirror (658 rows as of seed): qb_id, name, fully_qualified_name, account_type, account_sub_type, classification, parent_id, active, gl_type (IS = Revenue/Expense, BS = Asset/Liability/Equity, derived from classification), synced_at.

vendor_gl_default — one row per QB vendor: qb_vendor_id (PK, = QB Vendor.Id), vendor_name (unique, = QB DisplayName), vendor_active, default_gl_id (FK → qb_gl_accounts.qb_id, nullable), default_gl_name (snapshot), gl_type (derived from the chosen GL, never typed directly), confidence (smallint 0–100), source (auto seeded / manual edited here), lines_analyzed, inv_lines, bills, spend, last_bill_date, alt_gls (other GLs seen, with %), note, updated_by, updated_at. DDL: skills/im2_app/ddl/vendor_gl_default.sql.

Seed rule (2026-09-16, one-time load — skills/im2_app/scripts/vendor_gl/)

QB Bills since 2025-01-01 (3,360 bills, 255 vendors billed). Inventory-item lines excluded. Non-inventory item lines resolved to the item's expense GL. Account-based lines taken as their listed GL. Default GL = most common GL by line count; Confidence % = its share of qualifying lines. Flagged in Note (by the seed script, reviewable on screen) when confidence < 75%, the GL has no parent, fewer than 3 lines were analyzed, or the dollar-weighted GL differs from the line-count winner. load.py uses ON CONFLICT DO NOTHING — a re-run never overwrites a source='manual' row.

Code

app/vendor_gl.py — plain APIRouter (router = APIRouter()), same pattern as po_rel.py: imports TEMPLATES, current_user, require_action, can, db, dictcur, rows from im2, mounted at the bottom of im2.py alongside the other routers (from vendor_gl import router as vendor_gl_router / app.include_router(vendor_gl_router)) — required by the circular-import rule (this module imports from im2, so im2 must finish defining everything first).

Routes:

• GET /vendor-gl — inquiry, any signed-in user. Sortable (whitelisted columns only, sort/dir query params, NULLS LAST), filter vendor (ILIKE), chips chip=low_confidence|no_gl|no_parent. Row click opens the edit screen only when can(user, 'vendor_gl_edit').
• GET /vendor-gl.xlsx — same filter/sort, openpyxl export, bold header, frozen row 1, autofilter.
• GET/POST /vendor-gl/{qb_vendor_id} — gated by require_action(user, 'vendor_gl_edit') (called in the route body after Depends(current_user) — require_action is a plain function that raises HTTPException(403), it is not a Depends() factory; this is the #1 thing a future handoff without the live file will get wrong). Validates: Default GL exists, is active, and has a parent; Confidence is an integer 0–100 (required whenever a GL is set); Note ≤ 500 chars. All errors surface together; nothing saves on any failure. Concurrency: the form carries a hidden updated_at; any mismatch against the current row blocks the save. On save: source='manual', updated_by, updated_at = now() (Python datetime.now(timezone.utc)), one audit_log row per changed field — insert into audit_log (sku, field_name, old_value, new_value, changed_by, source) with sku='VENDORGL:{qb_vendor_id}', source='vendor_gl' — same 6-column insert shape used everywhere else in im2.py; changed_at defaults in the DB, it is never passed explicitly.

Permission: ACTION_DEFS/ACTION_DEFAULTS gained vendor_gl_edit → ("admin", "level1", "level2"). The inquiry screen needs no permission key (signed-in only). base.html's screen_names dict gained vendor_gl / vendor_gl_edit.

Docs: this file and docs/tips/vendor_gl.md, served at /docs/tech/vendor_gl and /docs/tips/vendor_gl by the existing docs.py install. Convention on every other screen is the Help button opens /docs/tips/{slug} (short operator tips), which cross-links to the tech spec automatically by matching slug — /docs/help/{slug} is reserved for a small number of screens with a separate, longer guide (e.g. RO Import Guide). This screen only needed tips + tech.

Menu: app/menu.html has an Accounting section (added 2026-09-16) with one tile, "Vendor Default GL (AP automation)" → /vendor-gl.

Known gaps / not built

• No bulk edit; one vendor at a time via the form.
• No history view of prior manual edits beyond the Change Log (audit_log with source='vendor_gl').
• AP invoice automation's no-PO path does not yet read this table — this screen only replaces the interim Excel map. See skills/ap_invoice_automation/SKILL.md.

Scope clarification (Dave, 2026-09-20)

This Default GL is used only when a PO is issued with no inventory or non-inventory item line (i.e. a pure expense/no-item purchase) — not as a general fallback for every AP line. Keep AP-automation integration scoped to that no-item-line case. When an item is present on the PO line, the item's own GL (ExpenseAccountRef for non-inventory, or the inventory item's asset/COGS accounts) always overrides the vendor default — the vendor default only applies to no-item lines.

Ver 2 · 2026-10-24 — Vendor file, AP Approval Category

• Screen renamed Vendor. vendor_gl_default now holds every active QB vendor (933 on first sync; 682 added). Rows added with source='qb_sync', no GL.
• New table vendor_ap_category(id, name unique, approver_username -> slack_roster, sort_order, active, updated_by, updated_at), seeded with Admin Supplies, Admin Services, Shop Supplies, Shop Services, Subcontractors, Material COGS. New column vendor_gl_default.ap_category_id.
• Routes: GET /vendor-gl-categories, POST /vendor-gl-categories/add, POST /vendor-gl-categories/{id} (edit permission vendor_gl_edit; changes written to audit_log as VENDORCAT:{id}). Vendor edit form has a category dropdown (change audited as ap_category_id). List/xlsx have Category + Approver columns and a No category chip.
• Sync: /opt/im2/scripts/load_qb_vendors.py (run by cron /im2/qb_vendor_sync) also upserts new/renamed vendors into vendor_gl_default via IM2_DSN and flags vendors no longer active in QB; never overwrites GL/category/note.

Ver 3 · 2026-10-24 — simplified

Vendor list/edit/xlsx: GL, type, confidence, approver columns removed (DB columns kept, unused). /vendor-gl/new removed. Category table endpoints (/vendor-gl-categories*) now require tables_edit and are linked from /tables.

Ver 4 · 2026-10-25 — Approval Limit

• New column vendor_gl_default.approval_limit integer (whole dollars, null = none; check >= 0). DDL: ddl/vendor_gl_approval_limit.sql.
• Route POST /vendor-gl/{qb_vendor_id}/limit (permission vendor_gl_edit); parser strips $ and commas, rejects cents/non-digits; change audited to audit_log as approval_limit on VENDORGL:{id}. Also on the edit form, sortable (sort=limit), and in the xlsx (with Class). Intended use: AP Invoice Approvals app.