appsmith-statistics-app/AGENTS.md

AGENTS.md — Statistics App (Appsmith)

Project Overview

Internal statistics dashboard built on Appsmith (file-based git sync mode). Displays filtered data from a PostgreSQL / xTuple ERP database (xTuple_GoLive datasource). The app is a collection of JSON widget definitions, SQL query files, and Appsmith configuration — there is no traditional application code.

Live URL: https://appsmith.mpeapp.com/applications/6947cc068872ae1d129983a0/pages/6947cc068872ae1d129983a3

Tech Stack

Layer	Technology
Platform	Appsmith (file format v5, 64-column grid layout)
Database	PostgreSQL via xTuple ERP (mpe and public schemas)
Datasource	xTuple_GoLive (production) / xTuple_Sandbox (development)
Version control	Git (Bitbucket), Appsmith git sync

Repository Structure

statistics-app/
├── AGENTS.md                 # This file — keep up to date after every change
├── application.json          # Page registry, app metadata
├── metadata.json             # Appsmith schema versions
├── theme.json                # UI theme (colors, fonts, borders)
├── datasources/
│   └── xTuple_GoLive.json    # PostgreSQL connection config (credentials managed by Appsmith server, not stored here)
├── pages/                    # One directory per page
│   └── <Page Name>/
│       ├── <Page Name>.json  # Page definition (gitSyncId required)
│       ├── widgets/          # Widget JSON files
│       │   ├── Heading.json
│       │   ├── Container1/   # Navigation sidebar widgets
│       │   └── Tabs1/        # Tabbed content (if applicable)
│       └── queries/          # SQL queries
│           └── <query_name>/
│               ├── <query_name>.txt      # Raw SQL (source of truth)
│               └── metadata.json         # Appsmith action config
└── .cursor/skills/           # AI agent workflows (Cursor-specific)

Pages

Section	Page	Key Data
Sales	Capacity Planning (default)	mpe.poormancapacity
Sales	Units Shipped	Orders shipped by series / power input, date-filtered
Sales	Units Ordered	Orders placed by series / power input, date-filtered
Engineering	Pending POs	Purchase orders via mpe.get_prototype_po_dashboard_data()
Engineering	Pending Revisions	Revisions via mpe.get_prototype_dashboard_data()
Engineering	xGen	8 tabs: build info, compatibility, firmware, test measurements
Operations	Engineering Holds	Work orders with active engineering holds
Operations	Job Drawing Status	CAD drawing builds from mpe.inventortools
Operations	Unused Items	Items with no inventory transactions for X days (default 365), filterable via input widget
Operations	WO Shortages	2 tabs: WO shortage details (MTS/MTO) for FA dept top-level WOs; Critical Parts aggregation. Uses mpe.itematmdept, mpe.wogrpext, womatl, qtynetable()
Operations	External Process	2 tabs: At Vendor (open POs for outside process items, item_type='O', classcode_code='EXTLAB'); Awaiting Shipment (manufactured material issued but no PO released). Uses poitem, pohead, womatl, classcode, itemsrc

Critical Conventions

gitSyncId (REQUIRED)

Every page JSON and query metadata.json must have a gitSyncId as the first field. Format: "<24-char-hex>_<uuid-v4>". Without it, Appsmith silently deletes the entity on next sync.

Generate:

python3 -c "import uuid; print(uuid.uuid4().hex[:24] + '_' + str(uuid.uuid4()))"

Widget IDs

Every widgetId across all pages must be globally unique. Use short descriptive strings with a 2-3 character page prefix (e.g., ui1btn3jds, eh1heading1).

Navigation

• Every page has identical nav sidebar (Container1/) with buttons for all pages.
• Active page button: buttonColor = {{appsmith.theme.colors.backgroundColor}} (dynamic binding).
• Inactive buttons: buttonColor = #ffffff (static, no dynamic binding).
• parentId for nav widgets = the Canvas widgetId inside that page's Container1.json (varies per page — always read it).
• Current nav sections: Sales (rows 0–16), Engineering (rows 18–34), Operations (rows 36–60).

Canvas Widget IDs (for parentId in Container1 children)

Page	Canvas widgetId
Sales - Capacity Planning	x3pc17vp6q
Sales - Units Shipped	wj6fxg5wpg
Sales - Units Ordered	wj6fxg5wpg
Pending POs	pa1canvas01
Pending Revisions	pr1canvas01
xGen	xg1canvas01
Operations - Job Drawing Status	jd1canvas01
Operations - Engineering Holds	eh1canvas01
Operations - Unused Items	ui1canvas01
Operations - WO Shortages	ws1canvas01
Operations - External Process	ep1canvas01

Grid Layout

• 64 columns wide, 10px per row.
• Nav container: rows 0–7. Content starts at row 8.
• Nav buttons: 4 rows each, 2-row gap between sections.

Queries

• SQL source of truth: <query_name>.txt (readable, real newlines).
• metadata.json body field: same SQL but JSON-escaped (\n, \").
• Table binding: {{query_name.data}}.
• All production queries use xTuple_GoLive datasource.
• Database credentials are managed by Appsmith server-side — they are not stored in this repository. The datasource JSON only contains the name and plugin reference. When developing/testing SQL queries locally, use separate read-only credentials provided by the user — never embed them in committed files.

Widget Bindings in SQL (IMPORTANT)

Appsmith uses prepared statements (pluginSpecifiedTemplates[0].value = true). When a query body contains {{Widget.property}}, Appsmith replaces each binding with a typed prepared statement parameter ($1, $2, etc.).

For DATE_PICKER_WIDGET2 (text/date type parameters):

• The NULLIF pattern works: NULLIF('{{DateWidget.selectedDate}}','')::date
• This is safe because the parameter is always typed as text.

For INPUT_WIDGET_V2 with inputType: "NUMBER" (integer type parameters):

• Do NOT use NULLIF('{{Widget.text}}','')::int — this fails because Appsmith may type the parameter as integer, and NULLIF(integer, '') causes a type mismatch error (invalid input syntax for integer: "").
• Instead, use a direct cast: '{{Widget.text}}'::int
• The widget's defaultText property ensures there is always a value on page load, so NULL/empty handling is unnecessary.

General rule: Match the SQL cast to the widget's output type. When in doubt, test with a hardcoded value first to confirm the base query works, then add the binding.

Common Tasks

Add a new page

Provide: page name (<Section> - <Label>), section, SQL query. The workflow:

1. Clone Sales - Capacity Planning as template
2. Generate new gitSyncId values for page and queries
3. Update page name, slug, heading, and widget IDs
4. Register in application.json
5. Add nav button on all pages (highlighted on own page, white elsewhere)
6. If new section needed, add section label (Text<N>) on all pages first
7. Update this AGENTS.md file (add the page to the Pages table, update Canvas Widget IDs table, and note any new conventions)

See .cursor/skills/create-new-page/SKILL.md for full procedure.

Add tabs to a page

Replace standalone table with TABS_WIDGET containing multiple tabs, each with its own query and table. See .cursor/skills/add-tabs-to-page/SKILL.md.

Add a query

Create queries/<name>/ with .txt and metadata.json. Wire to table via tableData: "{{name.data}}". See .cursor/skills/add-query-to-table/SKILL.md.

Launch prep (sandbox → production)

Replace all xTuple_Sandbox references in query metadata.json files with xTuple_GoLive. See .cursor/skills/prepare-for-launch/SKILL.md.

Rules

• After every change to this project, update this AGENTS.md file to reflect the current state — new pages, changed navigation layout, new conventions, etc. This file is the single source of truth for AI agents working in this repo.
• Do not create or modify files in the docs/ directory.
• Template page for cloning: Sales - Capacity Planning.
• Slug format: lowercase, spaces to hyphens (e.g., operations-unused-items).
• Product lines tracked: XR, SL, TS, MS, MT, Harmonic Neutralizer (mapped by item number in SQL CASE statements).
• Database credentials are never stored in this repository. Use the Appsmith-managed xTuple_GoLive datasource for all queries.