"""Chapters 13-21: UI routes, domain model, workflows, database, ERD, API, auth, roles, config.""" import csv import collections from pathlib import Path from helpers import Builder, ACCENT, MUTED, WARN, OK, INFO import facts as F RAW = Path(__file__).resolve().parents[2] / "docs" / "audit" / "raw" AUD = Path(__file__).resolve().parents[2] / "docs" / "audit" def _csv(name): with open(AUD / name, newline="", encoding="utf-8") as fh: return list(csv.DictReader(fh)) def ch13_routes(b: Builder) -> None: b.h1("13. Complete UI route and navigation reference") b.p("Three client applications expose routes: the ERP router (95 path records — 78 page " "routes, 15 legacy redirects and 2 layout wrappers), the POS router (9 records) and " "the B2B file-based page tree (42 pages). The ERP route table is in chapter 8.2 and " "the POS table in chapter 9.2; this chapter covers cross-application properties, the " "B2B tree, and the reconciliation between navigation and reachability.") b.h2("13.1 Route classes") b.table(["Class", "ERP", "POS", "B2B"], [["Public (no session)", "/login", "/login", "/, /catalog, /products/[slug], /login, /register, /register/status, /forgot-password, /reset-password, /membership, /galon, /changelog"], ["Authenticated, no specific permission", "/, /changelog, /contact, catch-all", "/shifts, /settings, /changelog, /contact", "/cart, /checkout, /checkout/confirmation"], ["Permission-gated", "74 page routes carry meta.perm", "4 records carry meta.permission", "admin/* and account/* via middleware"], ["Alias / redirect", "/accounting/journals, /accounting/general-ledger, /journals, /ledger, /reports/cashflow, /purchasing/orders, /distribution/customer-orders", "/ → /pos", "—"]], caption="Route classes per application", widths=[1.5, 2.0, 1.5, 1.5], font_size=7.8) b.h2("13.2 B2B page tree") b.table(["Path", "Purpose", "Guard"], [["/", "Storefront home: featured products, categories, search entry", "public"], ["/catalog", "Catalogue with server-side filtering", "public"], ["/products/[slug]", "Product detail", "public"], ["/cart", "Cart with quantity and price preview", "client"], ["/checkout", "Warehouse picker, address, payment method", "auth"], ["/checkout/confirmation", "Post-order confirmation and payment hand-off", "auth"], ["/login, /register, /register/status, /forgot-password, /reset-password", "Account access and approval status", "guest middleware"], ["/membership", "Public membership information", "public"], ["/galon", "Galon (water) ordering entry", "public"], ["/account", "Account overview", "auth"], ["/account/profile, /account/settings, /account/notifications", "Profile, preferences, notification feed", "auth"], ["/account/users", "Sub-users of the same customer account", "auth"], ["/account/addresses, /account/addresses/new", "Address book", "auth"], ["/account/orders, /account/membership", "Own orders and membership state", "auth"], ["/orders, /orders/[id], /orders/[id]/tracking", "Order list, detail, shipment tracking", "auth"], ["/payments, /payments/[id]", "Payment list and proof upload / Snap", "auth"], ["/quick-order", "Order by SKU list", "auth"], ["/order-import", "Bulk order import from a file", "auth"], ["/returns", "Return request against an own order", "auth"], ["/support, /support/tickets, /support/[id]", "Support tickets", "auth"], ["/admin", "Commerce admin dashboard", "admin middleware"], ["/admin/customers, /admin/customers/[id]", "Customer administration", "admin"], ["/admin/orders, /admin/orders/[id]", "Order administration", "admin"], ["/admin/products", "Product presentation and featured flags", "admin"], ["/admin/promotions", "Promotions / vouchers", "admin"], ["/admin/analytics", "Analytics", "admin"]], caption="B2B page tree (42 files under app/pages)", widths=[2.2, 3.0, 1.3], font_size=7.6) b.h2("13.3 Navigation vs reachability") b.table(["Situation", "Evidence", "Assessment"], [["Route exists, not in navigation", "ERP: /accounting/journals/:type sub-pages are in the sidebar, but /system/reports/:id/designer and /settings are reached by buttons only.", "Intentional — action pages, not destinations."], ["Navigation entry, role-restricted in the menu only", "navigation.js gives journal sub-pages an extra roles list; the router does not check it.", "Menu is narrower than the router (see 8.3)."], ["Navigation entry with no page", "None found: every routeName in navigation.js resolves to a declared route.", "Verified."], ["Route with no permission", "ERP /changelog, /contact, launcher, catch-all; POS /shifts, /settings; both require a session only.", "Intentional."], ["Page file with no route", "ERP finance/CashflowPage.vue and finance/InvoicesPage.vue exist but no route resolves to them (their paths redirect to receivables / cashier-cash).", "Dead code; safe to delete or re-wire."], ["B2B documented but absent", "/admin/catalog and /reorder appear in apps/b2b/docs/ROUTES.md; no page file exists.", "Documentation ahead of implementation."]], caption="Navigation/reachability reconciliation", widths=[2.0, 2.8, 1.7], font_size=7.8) b.h2("13.4 Permission keys used by the UI") b.p("The ERP router uses 48 distinct permission keys and the sidebar 46. All of them " "exist in the backend catalogue (verified by set difference). The two keys that " "appear only in the router are system.reports.manage (report designer) and " "system.settings.view (settings page). The POS router uses 4 keys, all present in " "the catalogue.") def ch14_domain(b: Builder) -> None: b.h1("14. Business domain model") b.p("The backend is partitioned into business domains that map one-to-one onto tables " "and modules. The table below is the authoritative map; it is derived from the " "module directories, the sqlc query files and the schema, not from the older " "domain-map.md (which describes the Laravel original).") b.table(["Domain", "Core tables", "Owning module", "Primary consumers"], [["Identity & access", "users, roles, role_permissions, personal_access_tokens, password_reset_tokens, stores, store_users, activity_logs", "auth, system", "ERP, POS, B2B"], ["Catalogue", "products, categories, units, brands, product_units, product_barcodes", "catalog", "ERP, POS, B2B"], ["Partners", "customers, suppliers, buyer_addresses", "partners, account", "ERP, B2B"], ["Membership", "memberships, membership_tiers, membership_tier_history, membership_tier_config_history", "membership", "POS, B2B, ERP"], ["Inventory", "warehouses, stocks, stock_movements, stock_opnames(+items), stock_transfers(+items)", "inventory, warehouses", "ERP, POS"], ["Purchasing", "purchase_orders(+items), goods_receipts(+items), vouchers", "purchasing", "ERP"], ["Sales", "sales_orders, sales_order_items, sales_order_status_history, sales_returns(+items), sales_vouchers, sales_targets", "sales, salestargets", "ERP, POS, B2B"], ["POS operations", "cashier_shifts, cashier_drawer_entries", "cashier", "POS"], ["Galon", "galon_transactions", "galon", "POS, B2B"], ["Finance", "invoices, payments, cashflows, cash_accounts, payment_vouchers(+lines), advances(+transactions)", "finance", "ERP"], ["Accounting", "accounts, accounting_departments, accounting_periods, journal_entries, journal_lines", "accounting", "ERP"], ["Distribution", "delivery_orders(+items), shipments(+items), shipment_tracking_events, dispatches, fleet, delivery_status_history, lhpp_groups(+items, history), warehouse_deposits", "distribution, lhpp, warehousedeposit", "ERP"], ["Sales tracking", "sales_visits, sales_activities, sales_routes, sales_locations", "salestracking", "ERP"], ["HR", "employees, attendances, leaves, payrolls, performances", "hr", "ERP"], ["Support", "tickets, ticket_comments", "tickets", "ERP, POS, B2B"], ["Reporting", "report_templates", "reports/templates, reportproxy", "ERP, POS, B2B"], ["Platform", "app_settings, document_number_sequences, schema_migrations", "settings, pkg/seq, dbmigrate", "backend"]], caption="Domain → table → module map", widths=[1.25, 2.6, 1.55, 1.1], font_size=7.6) b.h2("14.1 Aggregate boundaries and invariants") b.table(["Aggregate", "Invariant enforced in code", "Enforced in the database"], [["Sales order", "Only a draft (or pending_approval) order can be confirmed; paid orders cannot be cancelled; regular orders reserve stock, POS/e-commerce deduct it.", "status CHECK; sales_order_items snapshot trigger blocks price/quantity edits"], ["Sales order line", "unit_price, discount, conversion factor, base quantity and unit labels are immutable after creation.", "sales_order_items_snapshot_immutable trigger"], ["Invoice", "status ∈ unpaid/partial/paid/overdue/cancelled; a cancelled invoice keeps its original total (write-off, not zeroing).", "status CHECK"], ["Cashflow", "A generated cashflow (reference_type + reference_id set) cannot be updated in an amount/reference-changing way or deleted; a locked accounting period blocks insert/update/delete.", "enforce_cashflow_contract() trigger"], ["Journal entry", "Posting requires debit = credit and non-zero; only a posted entry can be reversed; reversal mirrors lines and links both ways.", "service-level (no DB constraint on balance)"], ["Stock", "One row per (product, warehouse); movements are append-only with before/after quantities.", "UNIQUE(product_id, warehouse_id)"], ["Shift", "Only an open shift can be closed; expected cash is computed, variance stored.", "status CHECK"], ["Membership", "Tier changes and tier-config changes are always recorded with actor and reason.", "history tables (no constraint, service-enforced)"]], caption="Aggregate invariants and where they live", widths=[1.1, 3.0, 2.4], font_size=7.8) def ch15_workflows(b: Builder) -> None: b.h1("15. End-to-end business workflows") b.p("Twenty-one workflows were traced from the HTTP entry point through the service, " "the transaction and the tables it writes. Each entry names the endpoints, the " "permission gate and the tables. Where a workflow has a meaningful decision or " "failure branch, a diagram follows.") b.table(["ID", "Workflow", "Module", "Endpoints", "Tables written"], [[w[0], w[1], w[2], w[3], w[4]] for w in F.WORKFLOWS], caption="Workflow index (detail follows for the nine most operationally significant)", widths=[0.45, 1.35, 0.95, 2.3, 1.45], font_size=7.2) b.h2("15.1 POS checkout (W-09)") b.figure(Path("../diagrams/rendered/fig-pos-checkout.png"), "POS checkout, verified flow. The three bands correspond to capture, the single " "transaction, and post-commit work.") b.figure(Path("../diagrams/rendered/fig-pos-payment.png"), "Payment-method branch inside POST /api/v1/pos.") b.table(["Step", "System action", "Failure branch"], [["1", "Resolve store binding from the caller's store_users list (platform roles skip).", "Out-of-scope store → 404"], ["2", "Map customer_id 0 to the Pelanggan Umum placeholder; only an explicit customer is priced with membership benefits.", "Missing customer → 400 customer is required"], ["3", "Validate payment_method against cash/transfer/giro/cheque/qris/kartu.", "Unknown method → 400"], ["4", "QRIS with no MIDTRANS_SERVER_KEY fails before any write.", "503 GATEWAY_DISABLED"], ["5", "Resolve the authoritative price per line from product_units (client price ignored); snapshot unit, conversion, base quantity.", "Unknown product → 404; qty < 0.001 → 400"], ["6", "Apply either a voucher or a manual discount (never both); clamp the discount to subtotal+tax.", "Both supplied → 400; voucher unusable → 409"], ["7", "Reject a cash tender below the total.", "400 cash_received must be ≥ total"], ["8", "Allocate SO and invoice numbers from document_number_sequences inside the transaction.", "Sequence error → whole tx rolls back"], ["9", "Insert the order, items, stock deduction (row lock) and one stock movement per line.", "Insufficient stock when ALLOW_NEGATIVE_STOCK=false → 409"], ["10", "Insert the invoice; for a settled method insert the payment and set the order to invoiced.", "—"], ["11", "Burn the redeemed voucher as the final write of the same transaction.", "—"], ["12", "Commit, then (QRIS only) charge the provider with the gateway reference already persisted.", "Provider error → order stays unpaid; sweep reconciles"]], caption="POS checkout step trace", widths=[0.4, 3.4, 2.4], font_size=7.6) b.h2("15.2 Sales order lifecycle (W-08)") b.figure(Path("../diagrams/rendered/fig-b2b-order.png"), "Order lifecycle as implemented. Draft → confirmed reserves stock for regular " "orders; POS and e-commerce orders deduct stock at checkout instead.") b.table(["Transition", "Endpoint", "Precondition", "Effect"], [["create", "POST /sales-orders", "items ≥ 1, customer exists, tax 0-100", "draft (or pending_approval), stock reserved"], ["confirm", "POST /sales-orders/{id}/confirm", "status draft or pending_approval", "confirmed, approved_by/at stamped"], ["fulfil", "PATCH /sales-orders/{id}/status", "picking → shipped → delivered", "status + history row"], ["invoice", "POST /invoices", "confirmed or delivered", "invoice issued, order invoiced"], ["pay", "POST /payments", "amount ≤ remaining", "payment row, invoice + order updated"], ["cancel", "POST /sales-orders/{id}/cancel", "not paid (paid requires the return flow)", "cancelled, reservation released or stock restocked"]], caption="Sales-order state transitions", widths=[0.9, 2.0, 1.9, 1.7], font_size=7.8) b.h2("15.3 Inventory movement (W-03, W-04, W-05)") b.p("Every stock change goes through one of three paths, and all three write " "stock_movements with quantity_before and quantity_after:") b.bullets([ "Manual stock-in / stock-out: POST /api/v1/inventory/stock-in and /stock-out. Stock-out locks the row and rejects when the available quantity is short unless negative stock is allowed.", "Opname: a snapshot of every stock row becomes the item list; finalising applies each difference through the same adjustment path and writes movement_type='opname'.", "Transfer: from_warehouse → to_warehouse with requested/sent/received quantities; the receive step writes transfer_in on the destination and transfer_out on the source.", ]) b.note("Ordering guarantee", "Transfers lock rows in a deterministic order " "(TestTransferLockOrder) so two opposite transfers between the same pair of " "warehouses cannot deadlock.", "verified") b.h2("15.4 Purchasing (W-06, W-07)") b.table(["Step", "Endpoint", "Effect"], [["Create PO", "POST /purchase-orders", "draft; totals computed from lines"], ["Approve", "POST /purchase-orders/{id}/approve", "draft → approved, approver stamped (segregation: 000054)"], ["Receive", "POST /goods-receipts", "GR + items; stock increased; PO partial or received"], ["Pay debt", "POST /purchase-orders/{id}/pay", "payment row + cashflow expense + PO paid/remaining"], ["Voucher", "POST /vouchers", "payment voucher linked to PO/supplier"]], caption="Purchasing workflow", widths=[1.1, 2.4, 3.0]) b.h2("15.5 Finance: invoice, payment, cashflow (W-14, W-16)") b.p("Invoices are raised from confirmed or delivered orders; payments are polymorphic " "(payable_type/payable_id) and update the payable's paid/remaining amounts in the " "same transaction. Cashflows are the cash ledger: a generated row (with a " "reference) is immutable, and corrections are reversing entries. A database " "trigger additionally refuses any cashflow write whose transaction_date falls in a " "locked accounting period.") b.note("Verified at the database", "The trigger raises SQLSTATE P0001 with message " "'accounting_period_locked' or 'generated_cashflow_immutable'. Both messages are " "mapped by the service layer rather than surfaced raw.", "verified") b.h2("15.6 Accounting (W-15)") b.numbered([ "Create a journal in draft with lines referencing accounts (and optionally departments).", "The service validates the journal type, resolves the store binding, computes totals, and allocates a journal number from the type prefix.", "Post: locks the row, requires status draft, debit = credit and non-zero totals, then stamps posted_by/posted_at.", "Reverse: locks the row, requires status posted, mirrors every line with debit/credit swapped, links original ↔ reversal both ways.", "A locked accounting period blocks posting through the cashflow trigger and through period checks.", ]) b.h2("15.7 Returns (W-11)") b.p("A return is line-level against an existing order. The service requires the order " "to be delivered, invoiced or paid — or confirmed and fully paid (the POS case). " "Per line it refuses a quantity above the remaining unreturned quantity, computes " "the refund pro-rata from the immutable line snapshot, and restocks in base units " "using the snapshotted conversion factor. Damaged items are recorded but not " "restocked. A cash refund also writes a drawer cash_out entry so the shift's " "expected cash stays truthful.") b.h2("15.8 Distribution and LHPP (W-17, W-18)") b.p("A delivery order is created against a sales order, then dispatched (fleet + " "driver), shipped (shipment with tracking number and barcode), and tracked through " "shipment_tracking_events. LHPP groups delivery orders per driver and date; adding " "or moving a DO writes lhpp_group_history with actor and reason. A driver's cash " "hand-over is a warehouse_deposit, optionally linked to the shift and drawer entry.") b.h2("15.9 Report generation (W-21)") b.figure(Path("../diagrams/rendered/fig-ci-cd.png"), "Report rendering travels the same authenticated path as any other read: the " "backend resolves the template and its permission, shapes the data, then calls " "the internal NextReport service.", width=6.6) b.numbered([ "Client requests GET /api/v1/reports/render?template=&format=pdf|html&.", "The handler resolves the active/default template row for the code (404 when none).", "templatePerms[code] is checked against the caller; buyer-reachable templates additionally verify order/invoice ownership.", "The owning service shapes the document data (invoice, PO, DO, GR, analytical report or slip).", "The backend POSTs {template, data, format} to NEXTREPORT_URL/render.", "NextReport renders HTML with the vendored engine and, for PDF, prints through Chromium.", "The bytes stream back with the matching content type.", ]) def ch16_database(b: Builder) -> None: b.h1("16. Complete database architecture") b.h2("16.1 Engine and topology") b.table(["Property", "Value", "Evidence"], [["Engine", "PostgreSQL 16 (compose.prod.yml uses postgres:16-alpine; the legacy apps/backend/docker-compose.yml uses 17-alpine — finding F-10)", "infra/containers/compose.prod.yml"], ["Database / user", "erp / erp by default, overridable via POSTGRES_USER/POSTGRES_DB", "compose + .env.example"], ["Schemas", "One schema: public. No custom schema, no search_path manipulation.", "Verified: information_schema lists only public"], ["Extensions", "plpgsql only", "pg_extension"], ["Connection pool", "MaxConns 25, MinConns 2, MaxConnLifetime 1h, MaxConnIdleTime 15m", "pkg/database/database.go"], ["Topology", "Single primary inside the container network; no replica, no external access", "compose.prod.yml (no published port)"], ["Migration system", "Custom runner (internal/dbmigrate) applying embedded migrations/*.sql, one transaction per file, tracked in schema_migrations", "internal/dbmigrate/dbmigrate.go"], ["Transaction isolation", "PostgreSQL default (READ COMMITTED) with explicit SELECT … FOR UPDATE on contended rows", "repository queries"], ["Seed", "seed.sql (bootstrap: admin, units, categories, warehouses, tiers, roles, one product) + optional seed_testdata.sql and seed_location_demo.sql", "apps/backend/seed.sql, apps/backend/scripts/seed.sh"]], caption="Database architecture facts", widths=[1.35, 3.4, 1.75], font_size=7.8) b.h2("16.2 Object census") b.table(["Object type", "Count", "Notes"], [["Base tables", str(F.COUNTS["tables"]), f"{F.COUNTS['tables_from_migrations']} from the migrations + schema_migrations created by the runner; includes 8 framework tables"], ["Columns", str(F.COUNTS["columns"]), "Every column is listed in chapter 18"], ["Primary keys", str(F.COUNTS["primary_keys"]), "One per table"], ["Foreign keys", str(F.COUNTS["foreign_keys"]), "63 reference users; 14 products; 12 employees; 12 warehouses"], ["Unique constraints", str(F.COUNTS["unique_constraints"]), "Document numbers, (product, warehouse), (employee, period), (store, user)"], ["Check constraints", str(F.COUNTS["check_constraints"]), "Status/type vocabularies and numeric guards"], ["Indexes", str(F.COUNTS["indexes"]), "268 secondary + primary keys; includes 13 unique indexes"], ["Sequences", str(F.COUNTS["sequences"]), "BIGSERIAL identity sequences (not document numbers)"], ["Functions", str(F.COUNTS["functions"]), "enforce_cashflow_contract, sales_order_items_snapshot_guard"], ["Triggers", str(F.COUNTS["triggers"]), "cashflows_contract_guard (BEFORE INSERT/UPDATE/DELETE), sales_order_items_snapshot_immutable (BEFORE UPDATE)"], ["Views / materialized views", "0", "No view objects exist; 'views' in migration names refer to permissions"], ["Enums / domains", "0", "All vocabularies are TEXT + CHECK"], ["RLS policies", "0", "Tenancy is enforced in the service layer, not by RLS"], ["Row-level grants", "Not used", "The application connects as the owner role"]], caption="Database object census (after applying all 123 migrations)", widths=[1.6, 0.7, 4.2], font_size=8.0) b.h2("16.3 Table inventory") b.p("The complete table list with purpose, domain, size and sensitivity follows. Sizes " "are from the audit database (empty except the bootstrap seed), so they describe " "structure, not production volume.") inv = _csv("database-inventory.csv") tables = collections.OrderedDict() for r in inv: t = r["table"] if t not in tables: tables[t] = {"domain": r["domain"], "purpose": r["table_purpose"], "cols": 0, "class": r["classification"]} tables[t]["cols"] += 1 rows = [[t, v["domain"], str(v["cols"]), v["class"], v["purpose"]] for t, v in tables.items()] b.landscape() b.table(["Table", "Domain", "Cols", "Classification", "Purpose"], rows, caption="Table inventory (92 tables; column counts from information_schema)", widths=[1.55, 1.05, 0.4, 1.15, 5.55], font_size=7.2) b.portrait() b.h2("16.4 Tenancy and scoping") b.p("Multi-tenancy is application-enforced. The chain is: users → store_users → stores " "→ warehouses. Every scoped service asks the context for the caller's store list " "and refuses out-of-scope writes with 404 rather than 403, so an operator cannot " "probe for the existence of another outlet's order. admin and owner are treated as " "platform identities and skip the store filter entirely.") b.note("No RLS", "There is no row-level security. The database user owns every table, " "so a bug that bypasses the service layer would expose all tenants. This is a " "deliberate trade (single-tenant deployment per database today), but it means " "the service layer is the only tenancy boundary.", "gap") b.h2("16.5 Migration history and safety") b.p("All 123 migrations applied cleanly, in order, inside a single transaction each, to " "a fresh PostgreSQL 16 database. Applying them statement-by-statement (autocommit) " "fails on 000058 because it depends on a TEMP TABLE ... ON COMMIT DROP (F-09).") b.landscape() b.table(["File", "Purpose"], [[m[0], m[1]] for m in F.MIGRATIONS], caption="Migration history in execution order (123 files)", widths=[3.3, 6.6], font_size=7.2) b.portrait() b.note("Destructive and data-mutating migrations", "Four migrations change data rather " "than schema: 000083 truncates cashflows; 000090 clamps negative stock and " "rebuilds reservations; 000092 reconciles sales-order status with delivery " "orders; 000116 deactivates duplicate cash accounts. There are no DOWN " "migrations, so rollback is a restore, not a reverse migration.", "warning") def ch17_objects(b: Builder) -> None: b.h1("17. Complete database object inventory") b.h2("17.1 Functions") b.table(["Function", "Returns", "Purpose", "Raised errors"], [["enforce_cashflow_contract()", "trigger", "Refuses insert/update/delete on cashflows when the transaction_date's accounting period is locked; refuses amount/reference changes or deletes on generated rows.", "accounting_period_locked, generated_cashflow_immutable (SQLSTATE P0001)"], ["sales_order_items_snapshot_guard()", "trigger", "Refuses any update that changes product_id, quantity, unit_price, discount_percent/amount, subtotal, conversion_factor, base_quantity, min_purchase, unit labels or unit ids.", "restrict_violation"]], caption="Database functions", widths=[1.7, 0.6, 2.6, 1.6], font_size=7.8) b.h2("17.2 Triggers") b.table(["Trigger", "Table", "Timing", "Function"], [["cashflows_contract_guard", "cashflows", "BEFORE INSERT OR UPDATE OR DELETE, FOR EACH ROW", "enforce_cashflow_contract"], ["sales_order_items_snapshot_immutable", "sales_order_items", "BEFORE UPDATE, FOR EACH ROW", "sales_order_items_snapshot_guard"]], caption="Triggers", widths=[2.2, 1.4, 2.0, 1.9]) b.h2("17.3 Indexes of operational significance") b.table(["Index", "Table", "Why it matters"], [["sales_orders (customer_id, status, order_date)", "sales_orders", "Receivables and order lists"], ["invoices (customer_id, status, due_date)", "invoices", "Aging and overdue scans"], ["stocks UNIQUE (product_id, warehouse_id)", "stocks", "One row per product per warehouse; also the lock target"], ["stock_movements (reference_type, reference_id)", "stock_movements", "Trace a movement back to its document"], ["document_number_sequences PK (prefix, doc_date)", "document_number_sequences", "The atomic allocator's conflict target"], ["payments (payable_type, payable_id)", "payments", "Polymorphic payable lookup"], ["cashflows (type, transaction_date)", "cashflows", "Cash statement range scans"], ["activity_logs (module, action, created_at), (model_type, model_id)", "activity_logs", "Audit search"], ["attendances UNIQUE (employee_id, attendance_date)", "attendances", "One attendance per employee per day"], ["payrolls UNIQUE (employee_id, period_month, period_year)", "payrolls", "One payroll per employee per period"], ["report_templates UNIQUE (report_code, version) + partial default index", "report_templates", "Template versioning and active lookup"], ["idx_report_templates_code_status", "report_templates", "Listing templates by code and status"]], caption="Indexes that carry business meaning", widths=[2.9, 1.4, 2.2], font_size=7.6) b.p("The complete index list (269 entries) is in docs/audit/raw/indexes.csv and summarised " "per table in Appendix C.") b.h2("17.4 Constraints") b.p("112 check constraints encode the state vocabularies. The most consequential:") b.table(["Table", "Constraint", "Permitted values"], [["sales_orders", "sales_orders_status_check", "draft, pending_approval, confirmed, picking, shipped, delivered, invoiced, paid, cancelled"], ["sales_orders", "sales_orders_order_type_check", "regular, pos, consignment, ecommerce"], ["sales_orders", "sales_orders_source_type_check", "NOO, EC, AO (NULL allowed)"], ["invoices", "invoices_status_check", "unpaid, partial, paid, overdue, cancelled"], ["users", "role validation", "Dropped in 000057; validated by internal/system validRole against AllRoles + the roles registry"], ["stocks", "quantity / reserved bounds", "No CHECK on sign; negative stock is a service-level policy (ALLOW_NEGATIVE_STOCK)"]], caption="State vocabularies enforced by CHECK constraints", widths=[1.2, 2.2, 3.1], font_size=7.8) b.h2("17.5 Sequences, extensions, views, RLS") b.bullets([ f"{F.COUNTS['sequences']} BIGSERIAL sequences back the surrogate ids. Document numbers do not use sequences.", "The only extension is plpgsql (installed by default).", "There are no views, no materialized views, no enum types, no domains and no RLS policies.", "No database roles or grants beyond the owner are created by the migrations.", ]) def ch18_dictionary(b: Builder) -> None: b.h1("18. Full database data dictionary") b.p("Every column of every table, in ordinal order, with its exact type, nullability, " "default and the constraint/index context of its table. Column meaning is taken " "from migration comments, service validation and query usage; where the source " "does not establish a meaning, the note says 'Meaning not verified beyond " "type/default' rather than inventing one. Tables are grouped by domain and set in " "landscape so no row is clipped.") inv = _csv("database-inventory.csv") by_domain = collections.OrderedDict() for r in inv: by_domain.setdefault(r["domain"], []).append(r) order = ["Auth", "Tenancy", "Catalog", "Partners", "Membership", "Inventory", "Purchasing", "Sales", "POS", "Galon", "Finance", "Accounting", "Distribution", "Sales tracking", "HR", "Support", "Reports", "System", "E-commerce", "Framework", "Unclassified"] b.landscape() for domain in order: rows = by_domain.get(domain) if not rows: continue b.h2(f"18.{order.index(domain)+1} {domain}") tables = collections.OrderedDict() for r in rows: tables.setdefault(r["table"], []).append(r) for tname, cols in tables.items(): purpose = cols[0]["table_purpose"] cls = cols[0]["classification"] b.h3(f"{tname} — {purpose}") b.p(f"{len(cols)} columns. Classification: {cls}.", size=8, color=MUTED, space_after=2) body = [] for c in cols: default = c["default"] if default.startswith("nextval"): default = "nextval(seq)" if len(default) > 60: default = default[:57] + "…" flags = [] if c["primary_key"]: flags.append("PK") if c["foreign_keys"] and c["column"].endswith("_id"): flags.append("FK") body.append([c["column"], c["data_type"], c["nullable"], default or "—", ", ".join(flags), c["column_note"]]) b.table(["Column", "Type", "Null", "Default", "Key", "Note"], body, widths=[1.5, 1.65, 0.4, 1.5, 0.45, 4.3], font_size=6.9) b.portrait() def ch19_erd(b: Builder) -> None: b.h1("19. Complete ERD and domain ERDs") b.p("The diagrams are generated from the live schema: docs/diagrams/gen_erd.py reads the " "introspection dumps and emits Mermaid erDiagram sources, which were rendered to " "PNG at 2x for embedding. Only declared foreign keys are drawn; relationships that " "exist only in application logic (for example payments.payable_id, " "cashflows.reference_id, sales_orders.sales_id → employees) are called out in text " "because they are polymorphic or logical rather than declared.") b.figure(Path("../diagrams/rendered/erd-full.png"), "Full schema overview: all 92 tables, primary and foreign keys only. At this " "scale the diagram is an index; use the domain ERDs for readable detail.", width=9.4) domains = [ ("erd-auth-tenancy", "Identity, tenancy and platform tables"), ("erd-catalog", "Catalogue, partners and membership"), ("erd-inventory", "Warehouses, stock and movements"), ("erd-sales", "Sales orders, returns, vouchers, cart"), ("erd-purchasing", "Purchasing, goods receipts and vouchers"), ("erd-finance", "Invoices, payments, cashflows, advances"), ("erd-accounting", "Chart of accounts and journals"), ("erd-pos", "Cashier shifts and drawer"), ("erd-distribution", "Delivery, shipments, fleet, LHPP"), ("erd-hr", "Employees, attendance, leave, payroll"), ("erd-salestracking", "Visits, activities, routes, GPS"), ("erd-support-reports", "Tickets and report templates"), ("erd-framework", "Laravel framework tables retained for parity"), ] b.landscape() for name, title in domains: b.h2(f"19.{domains.index((name,title))+1} {title}") b.figure(Path(f"../diagrams/rendered/{name}.png"), title, width=9.4) b.portrait() b.h2("19.14 Relationship index") b.table(["Table", "Declared FK targets", "Logical (not declared)"], [["sales_orders", "customers, warehouses, employees (sales_id), stores, users (supervisor_id, approved_by), cashier_shifts", "—"], ["sales_order_items", "sales_orders, products, product_units, units", "—"], ["payments", "users (created_by), warehouse_deposits (deposit_id)", "payable_type/payable_id → invoices | purchase_orders (polymorphic)"], ["cashflows", "users (created_by, attachment_uploaded_by), cash_accounts, stores, cashflows (reversal_of)", "reference_type/reference_id → any document (polymorphic)"], ["journal_entries", "stores, users, journal_entries (reversal_of, reversal_entry_id)", "source_type/source_id → any document (polymorphic)"], ["invoices", "sales_orders, customers", "—"], ["delivery_orders", "sales_orders, customers, warehouses, users", "—"], ["shipments", "delivery_orders, users", "—"], ["lhpp_group_items", "lhpp_groups, delivery_orders, users", "—"], ["memberships", "customers, users (created_by)", "tier is a text code, not an FK to membership_tiers"], ["employees", "users", "—"], ["tickets", "users (reporter_id, assigned_to_id)", "source_app/source_path are free text"], ["activity_logs", "users", "model_type/model_id → any table (polymorphic)"]], caption="Relationship index, distinguishing declared FKs from logical links", widths=[1.5, 3.6, 4.8], font_size=7.6) def ch20_api(b: Builder) -> None: b.h1("20. API architecture and complete endpoint reference") b.h2("20.1 Conventions") b.table(["Property", "Convention"], [["Base path", "/api/v1 (health and ready are mounted at the root)"], ["Success envelope", "{ data: …, meta: { request_id, pagination? } }"], ["Error envelope", "{ error: { code, message, details? } } — meta.request_id is also present"], ["Auth", "Authorization: Bearer ; only /health, /ready, the Midtrans webhooks, GET /uploads/{key} and the auth entry points are reachable without one"], ["Money", "JSON numbers, NUMERIC(15,2) in the database, decimal in Go"], ["Quantities", "JSON numbers, up to 4 decimals"], ["Dates", "YYYY-MM-DD; datetimes ISO-8601 with offset; 'today' resolves in Asia/Jakarta"], ["Pagination", "?page= and ?per_page=; response meta.pagination {current_page, per_page, total, last_page}"], ["Idempotency", "Idempotency-Key mandatory on POST /pos, /sales-orders, /payments, /invoices, /cart/checkout and the other money endpoints; replay on identical payload, 409 on a different one"], ["Rate limits", "300/min/IP generic (fail-open), 10/min/IP on auth entry points (fail-closed), 60/min/IP on tracking"], ["CORS", "Allowlist from CORS_ORIGINS; '*' is non-credentialed"]], caption="API conventions", widths=[1.5, 5.0], font_size=8.0) b.h2("20.2 Operation distribution") b.table(["Tag", "Operations", "Tag", "Operations", "Tag", "Operations"], [[F.API_TAGS[i][0], str(F.API_TAGS[i][1]), F.API_TAGS[i+11][0] if i+11 < len(F.API_TAGS) else "", str(F.API_TAGS[i+11][1]) if i+11 < len(F.API_TAGS) else "", F.API_TAGS[i+22][0] if i+22 < len(F.API_TAGS) else "", str(F.API_TAGS[i+22][1]) if i+22 < len(F.API_TAGS) else ""] for i in range(11)], caption="373 operations by OpenAPI tag", widths=[1.6, 0.7, 1.7, 0.7, 1.7, 0.7], font_size=7.8) b.h2("20.3 Request/response example") b.code("""GET /api/v1/products?per_page=2 (no Authorization header — catalogue reads are public) 200 OK { "data": [{ "id": 1, "code": "SKU-2006", "name": "Galon Indodes", "category_id": 41, "unit_id": 44, "category": { "id": 41, "name": "Depot Galon", "is_active": false }, "unit": { "id": 44, "name": "GALON", "symbol": "galon" }, "selling_price": "8000", "min_stock": "50", "total_stock": "48", "is_active": true, "is_featured": false, "image": "...", "image_url": "..." }], "meta": { "pagination": { "current_page": 1, "per_page": 2, ... }, "request_id": "732cd755c22220d180ab0008bcd591c4" } }""", caption="Public catalogue read, actual response from the audit run (purchase_price is absent — it requires inventory.cost.view)") b.note("Cost visibility", "purchase_price is omitted from the product payload unless the " "caller holds inventory.cost.view. Verified by internal/catalog/cost_view_test.go " "and by the runtime response above, which has no purchase_price key.", "verified") b.h2("20.4 Complete endpoint reference") b.p("All 373 method-routed operations, grouped by tag, in landscape. Two further routes " "are served but not listed here because they are mounted with HandleFunc and are " "therefore absent from the OpenAPI document: /api/v1/reports/render (accepts GET and " "a POST body) and /api/v1/reports/preview (finding F-24). The permission column shows the " "gate applied by the router; 'none (auth only)' means the route requires a session " "but no specific key, and 'conditional' marks the two routes whose gate is a role " "carve-out closure (buyer self-confirm, cashier QRIS cancel).") api = _csv("api-inventory.csv") by_tag = collections.OrderedDict() for r in api: by_tag.setdefault(r["tag"], []).append(r) b.landscape() for tag in sorted(by_tag): rows = by_tag[tag] b.h3(f"{tag} — {len(rows)} operations") b.table(["Method", "Path", "Summary", "Auth", "Permission gate"], [[r["method"], r["path"], r["summary"], r["auth"], r["permission_gate"]] for r in rows], widths=[0.65, 3.0, 3.2, 0.6, 2.45], font_size=6.9) b.portrait() b.h2("20.5 Contract and frontend mapping") b.table(["Layer", "Artefact", "Reconciliation"], [["Backend router", "cmd/server/main.go + internal/system/handler.go", "373 operations (AST parse)"], ["OpenAPI contract", "apps/backend/docs/openapi.yaml", "373 operations — exact set equality with the router"], ["Generated contract", "packages/contracts/src/routes.txt / routes.js", "373 route strings — drift check passes"], ["Frontend guards", "erp/src/services/backendContract.js, pos/src/services/backendContract.js, b2b/app/services/backendContract.ts", "Each imports BACKEND_ROUTES and asserts every http.( call found in its service layer is a documented route; a second lexer cross-checks that no call was silently dropped"], ["Frontend calls", "erp/src/services/apiCalls.js, pos/src/services/api/*, b2b/app/services/apiCalls.ts", "Call sites; the contract test in each app catches a call to an undocumented path"]], caption="Contract layers and their reconciliation result", widths=[1.3, 2.6, 2.6], font_size=8.0) def ch21_auth(b: Builder) -> None: b.h1("21. Authentication and authorization") b.h2("21.1 Token model") b.p("There is no JWT and no refresh token. A login mints an opaque token with " "crypto/rand.Text (>=128 bits of entropy) and stores only its sha256 hash in " "personal_access_tokens, together with an expiry (TOKEN_TTL_HOURS, default 24) and " "last_used_at. Every request looks the hash up; a missing, expired or revoked row is " "401. Logout deletes the row, so revocation is immediate and stateless on the client.") b.figure(Path("../diagrams/rendered/fig-auth-lifecycle.png"), "Login, session hydration and logout. The bcrypt comparison is equalised on the " "not-found path with a dummy hash to avoid a timing oracle.", width=6.4) b.table(["Aspect", "Implementation", "Notes"], [["Password storage", "bcrypt (cost from bcrypt.DefaultCost)", "users.password"], ["Login throttling", "10/min/IP, fail-closed", "Login, register, forgot, reset share the limiter"], ["Account state", "users.is_active; a disabled account gets 403 ACCOUNT_DISABLED", "Checked on every authenticate"], ["Token scope", "personal_access_tokens row per login; abilities column exists but is not enforced", "Unverified: no code reads abilities"], ["Session storage (client)", "ERP/POS: sessionStorage or localStorage (remember me); B2B: localStorage + km_auth_token cookie for SSR", "Cookie is converted to a bearer header by the Nitro proxy"], ["Password reset", "Single-use token in password_reset_tokens, delivered by SMTP (or logged in dev)", "Reset link points at B2B_BASE_URL"], ["CORS", "Allowlist; '*' allowed but non-credentialed", "Finding F-12"], ["CSRF", "Not applicable: no cookie-authenticated state-changing API calls (the B2B cookie is converted to a header server-side)", "Verified by reading the proxy"], ["Security headers", "nosniff, DENY framing, no-referrer, permissions policy", "pkg/middleware/security.go"]], caption="Authentication controls", widths=[1.4, 3.0, 2.1], font_size=7.8) b.h2("21.2 Authorisation model") b.figure(Path("../diagrams/rendered/fig-authz-model.png"), "Permission evaluation: application scope, then admin bypass, then per-user " "override (exact or glob deny), then role globs.") b.p("Two independent axes exist. The permission axis answers 'what may this identity " "do'; the application axis answers 'which frontend may it enter'. They are separate " "columns (users.role vs users.application_access) and separate checks " "(u.Can(perm) vs u.CanAccessApp(app)).") b.table(["Rule", "Implementation", "Consequence"], [["admin and owner bypass every permission", "u.Can returns true before any lookup", "They cannot be locked out of a permission; the UI still hides pages they lack navigation for"], ["A role absent from the matrix gets nothing", "globsFor returns nil; deny by omission", "A custom role created without rows is deny-all until rows are added"], ["Per-user override wins over role globs", "exact key first, then glob deny, then role globs", "An override can both grant and deny; a wildcard deny beats every role glob"], ["Out-of-scope tenant access returns 404", "ResolveStoreBinding / checkScope", "No existence oracle across outlets"], ["Route-level gate is the only server boundary", "RequirePermission / RequireAnyPermission / RequireApp / RequireAdmin", "Frontend guards are UX (stated in code comments)"]], caption="Authorisation rules and their consequences", widths=[1.9, 2.4, 2.2], font_size=7.8) b.h2("21.3 Application-access axis") b.table(["application_access", "May enter", "Assigned to"], [["platform", "ERP, POS, B2B, admin console", "admin, owner"], ["erp", "ERP only", "gm, bm, spv, sales, finance, finance_head, warehouse, hr_manager, hr_staff, employee, store_manager, system"], ["pos", "POS only", "cashier"], ["ecommerce", "B2B storefront", "buyer"], ["ecommerce_admin", "B2B storefront + admin console", "a customer with is_ecommerce_admin = true"]], caption="Application access values", widths=[1.6, 2.6, 2.3]) b.note("Measured state of the matrix", "After all 123 migrations the database holds 304 " "role_permissions rows across 14 roles; hr_staff and system hold none. 76 catalogue " "keys have no holder at all, and 71 of them are enforced on a live route, so on a " "fresh deployment those operations are reachable only by admin and owner. The " "detailed per-key consequence is in chapter 22.3 and finding F-22.", "warning") b.note("Enforcement reality", "RequireApp is mounted on 24 buyer routes plus two " "notifications routes. There is no RequireApp(AppERP) gate on internal routes, so " "a POS or buyer token is stopped only by the permission matrix — which is exactly " "what the ERP router guard's comment says. Treat the matrix as the boundary.", "verified") b.h2("21.4 Input validation, uploads and secrets") b.table(["Control", "Implementation"], [["JSON decoding", "DisallowUnknownFields is not used; handlers decode into typed structs and validate required fields explicitly"], ["Numeric bounds", "Tax 0-100, discount 0-100, quantities > 0, cash ≥ total, negative amounts rejected per handler"], ["Body size", "BODY_SIZE_LIMIT_BYTES (10 MiB) enforced before the handler"], ["Uploads", "POST /api/v1/uploads requires a session; files land under UPLOAD_DIR and are served by GET /uploads/{key}; payment proofs are additionally checked against the payments table"], ["SQL injection", "All queries are parameterised (pgx placeholders); no string-built SQL with user input was found"], ["Secrets", "Read from environment only; never logged. The backend logs errors, not DSNs. MIDTRANS_SERVER_KEY absent disables the gateway rather than failing open"], ["Sensitive responses", "purchase_price gated by inventory.cost.view; payment proofs ownership-checked; buyer routes ownership-checked"]], caption="Input, upload and secret controls", widths=[1.5, 5.0], font_size=8.0) def ch22_roles(b: Builder) -> None: b.h1("22. User roles and permission matrix") b.h2("22.1 Permission catalogue") b.p("The catalogue is compiled into the binary (internal/auth/catalog.go) and published " "by GET /api/v1/permissions, which the ERP role editor renders verbatim. The " "frontend holds no copy of the labels.") b.table(["Group", "Keys"], [[g, str(n)] for g, n in F.PERMISSION_GROUPS] + [["Total", "201"]], caption="Permission catalogue groups", widths=[4.0, 1.0]) b.h2("22.2 Roles") b.table(["Role", "Permission rows", "Intent"], [[r, str(n), note] for (r, n), note in zip(F.ROLE_ROWS, [ "Technical superuser; bypasses every check.", "Business owner; bypasses every check.", "General manager: catalogue, outlets, membership tiers, consolidated reporting.", "Branch manager: distribution, invoicing, sales supervision.", "Supervisor: sales-order supervision and tracking.", "Field sales: own orders, visits, customers.", "Outlet head: outlet stock, outlet cashier accounts, outlet reports.", "Cashier: sell, return, shift, customer enrolment.", "Storefront buyer: own cart, orders, invoices, returns.", "Finance operator: invoices, payments, cashflow, journals, reports.", "Head of finance: same as finance plus approvals.", "Warehouse operator: stock in/out, opname, transfers, goods receipts.", "HR manager: employees, attendance, leave, payroll, performance.", "HR staff: no rows after the launch reset — deny-all until granted.", "Employee: self-service only (clock in/out, own attendance, own payslips).", "System integration identity: no rows after the launch reset — deny-all until granted.", ])], caption="Seeded roles and their row counts (from role_permissions after all migrations)", widths=[1.3, 0.9, 4.3], font_size=7.8) b.note("Row counts are not capability counts", "A single glob row such as 'hr.*' " "expands to many keys at evaluation time, so hr_manager's 6 rows cover the whole " "HR surface while store_manager's 48 rows are mostly explicit keys. Count rows, " "never infer breadth from them.", "note") b.h2("22.3 Role × capability matrix (measured)") b.p("This matrix is not a design intent. It was computed by expanding every " "role_permissions row with the same glob rule the backend uses " "(internal/auth/matrix.go globsFor + matchesAnyGlob) on a database with all 123 " "migrations applied. A dash means the role holds no grant for that key, so the " "API answers 403. admin and owner are omitted because they bypass every check.") header = ["Surface (permission key)"] + F.ROLE_COLUMNS rows = [[f"{label} — {key}"] + vals for label, key, vals in F.CAPABILITY_MATRIX] b.landscape() b.table(header, rows, caption="Role × capability matrix, measured from role_permissions after all migrations", widths=[3.0] + [0.62] * len(F.ROLE_COLUMNS), font_size=7.2) b.portrait() b.note("Read this before planning any rollout", "76 of the 201 catalogue keys are held " "by no role at all after a clean migration, and 71 of those are gated on a live " "route: the whole catalogue-write family, supplier read, every financial report, " "the chart of accounts, user/role administration, the activity log, store " "administration and every distribution key. On a fresh deployment only admin and " "owner can perform those actions. See findings F-22 and F-23; the intended matrix " "is recoverable from the pre-000061 state (860 rows) through " "PUT /role-permissions.", "warning") b.h2("22.4 UI visibility vs backend enforcement") b.p("The ERP hides a page when auth.can(meta.perm) is false; the POS redirects to /pos " "when the permission is missing. Neither is a security control. The server decides: " "a hidden page can still be called by URL or by curl, and the API answers 403. " "Conversely, a visible page can fail with 403 if the matrix changed after the " "session hydrated — the ERP re-syncs /auth/me on navigation to reduce that window.") def ch23_config(b: Builder) -> None: b.h1("23. Configuration and environment variables") b.h2("23.1 Backend variables") b.p("Every variable read by pkg/config.Load(), with its default, whether it is required, " "whether it carries a secret, and what happens when it is missing or invalid. No " "real value is reproduced here.") b.landscape() b.table(["Variable", "Default", "Req.", "Secret", "Purpose", "Failure mode"], [[n, d, r, s, p, f] for n, d, r, s, p, f in F.ENV_BACKEND], caption="Backend environment variables (36)", widths=[2.0, 1.5, 0.45, 0.5, 2.9, 2.55], font_size=6.9) b.portrait() b.h2("23.2 Frontend variables") b.table(["Variable", "App", "Default", "Visibility", "Purpose"], [[n, a, d, v, p] for n, a, d, p, v in [(x[0], x[1], x[2], x[3], x[4]) for x in F.ENV_FRONTEND]], caption="Frontend environment variables (public build-time unless noted)", widths=[2.0, 0.6, 1.4, 1.0, 1.5], font_size=7.6) b.h2("23.3 Configuration findings") b.table(["Finding", "Detail"], [["Declared but unread", "SMTP_FROM exists in .env.example and in the Config struct but Load() never reads it (F-07)."], ["Read but undocumented in .env.example", "PUBLIC_API_BASE_URL, AUTO_MIGRATE, ALLOW_NEGATIVE_STOCK, IDEMPOTENCY_TTL_HOURS, TRUSTED_PROXIES, RATE_LIMIT_TRACKING_PER_MINUTE, LOG_GR_MOVEMENTS, FIX_PAYDEBT_REFERENCE."], ["Insecure default for production", "CORS_ORIGINS defaults to http://localhost:3000; the compose stack does not set it, so a production deployment keeps the localhost default unless the operator adds it (F-12)."], ["Insecure default for production (2)", "ALLOW_NEGATIVE_STOCK defaults to true; deploy/.env.prod.example sets false, but infra/containers/compose.prod.yml does not pass it, so the container keeps the permissive default."], ["Documented but not wired", "compose.prod.yml passes none of SMTP_*, CORS_ORIGINS, PUBLIC_API_BASE_URL, TRUSTED_PROXIES or MIDTRANS_SWEEP_TOKEN to the backend container (the only API-base variable it sets is NUXT_PUBLIC_API_BASE_URL for the B2B service). They can be added to the environment block, but as committed they are absent, so the backend runs on its compiled defaults."], ["Public vs secret", "VITE_* and NUXT_PUBLIC_* are compiled into client bundles. No secret is referenced by those names (verified by searching the app sources for MIDTRANS_SERVER_KEY and DATABASE_URL)."]], caption="Configuration issues", widths=[2.0, 4.5], font_size=7.8)