Adam Pitel
a9f0e75e1b
Two-tab dashboard tracking external labor workflows (e.g., tin plating): - Tab 1 "At Vendor": Open POs for outside process items (EXTLAB classcode), showing vendor, PO#, finished/manufactured/ext labor PNs, qty, ship date, expected return (7-day default), days out, and WO demand - Tab 2 "Awaiting Shipment": WOs with manufactured material issued but no open PO for the outside process item, showing days waiting in stockroom Nav button added to all 11 pages in Operations section (rows 56-60). Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
162 lines
8.5 KiB
Markdown
162 lines
8.5 KiB
Markdown
# 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:
|
||
```bash
|
||
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.
|