From dev-assistant
/etendo:alter-db — Create or modify tables, columns, views, and references in the Etendo Application Dictionary via webhooks
npx claudepluginhub etendosoftware/etendo_claude_marketplace --plugin dev-assistantThis skill uses the workspace's default tool permissions.
**Arguments:** `$ARGUMENTS` (optional description, e.g., "create table SMFT_customer with name and email")
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Searches prompts.chat for AI prompt templates by keyword or category, retrieves by ID with variable handling, and improves prompts via AI. Use for discovering or enhancing prompts.
Guides agent creation for Claude Code plugins with file templates, frontmatter specs (name, description, model), triggering examples, system prompts, and best practices.
Arguments: $ARGUMENTS (optional description, e.g., "create table SMFT_customer with name and email")
First, read skills/etendo-_guidelines/SKILL.md, skills/etendo-_context/SKILL.md, and skills/etendo-_webhooks/SKILL.md.
For AD XML structure details, read references/application-dictionary.md. For display logic, references, sequences, and data access levels, read references/advanced-ad.md.
These EtendoRX headless endpoints provide read/query capabilities for inspecting existing tables and columns. Base URL: {ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/.
| Endpoint | Methods | Use for |
|---|---|---|
Table | GET, PUT | Read table metadata, check if a table exists |
Column | GET, PUT | Read column metadata for a table |
ModulePrefix | GET | Look up a module's DB prefix |
Reference | GET | Look up reference types (String, Integer, etc.) |
ValidationSetup | GET, POST, PUT | Manage validation rules |
TableTreeConfiguration | GET, POST, PUT | Configure tree structures for tables |
Use headless GET for reading/querying existing data. For creation or structured operations, always use webhooks.
After creating or modifying any table, column, or view, you must run this sequence in order:
CheckTablesColumnHook (TableChecker) — Detects column changes and runs column registration proceduresSyncTerms — Synchronizes terms/translations across the systemElementsHandler — Reads elements and auto-corrects missing descriptions/help
TABLE_IDis required forCheckTablesColumnHook. For new tables, use the ID returned byCreateAndRegisterTable. For adding columns to existing tables, look up the table ID first viaGetWindowTabOrTableInfoor headlessTableendpoint.
# Run after every table/column/view creation:
curl -s -X POST "${ETENDO_URL}/webhooks/CheckTablesColumnHook" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{\"TableID\": \"${TABLE_ID}\"}"
curl -s -X POST "${ETENDO_URL}/webhooks/SyncTerms" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" -d '{}'
curl -s -X POST "${ETENDO_URL}/webhooks/ElementsHandler" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{\"TableID\": \"${TABLE_ID}\"}"
Never skip or reorder these steps. They ensure the Application Dictionary stays consistent.
Resolve:
_webhooks skill — section "Prerequisite: Bearer Token")./gradlew resources.up if it's down)If $ARGUMENTS describes it clearly, use it. Otherwise, ask:
For new table: ask for name (suggest {PREFIX}_tablename), list of columns.
For new column: table, name, type, nullable, default.
For new view: ask for name, source query.
For inspect: use the GetWindowTabOrTableInfo webhook or headless Table/Column endpoints.
Inspect existing structures:
# Via webhook (returns detailed AD info):
curl -s -X POST "${ETENDO_URL}/webhooks/GetWindowTabOrTableInfo" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{\"TableName\": \"{PREFIX_TableName}\"}"
# Via headless (simpler metadata):
curl -s -H "Authorization: Bearer ${ETENDO_TOKEN}" \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/Table?dBTableName={PREFIX_TableName}"
Display a summary of what will be created. Ask for confirmation before executing.
Important: In Etendo, a physical PostgreSQL table is invisible to the system unless it has an AD_TABLE record. Similarly, physical columns need AD_COLUMN records with metadata (type, reference, nullable, etc.) to be usable in forms and entities.
Use the CreateAndRegisterTable webhook. This creates the physical table in PostgreSQL AND registers it in AD_TABLE in a single call:
ETENDO_URL="http://localhost:8080/etendo" # or the port from context.json
MODULE_ID="{ad_module_id}"
RESP=$(curl -s -X POST "${ETENDO_URL}/webhooks/CreateAndRegisterTable" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d '{
"Name": "{LogicalName}",
"DBTableName": "{PREFIX_TableName}",
"ModuleID": "'${MODULE_ID}'",
"DataAccessLevel": "3",
"Description": "{description}",
"Help": "{description}",
"JavaClass": "{javapackage}.data.{EntityName}"
}')
echo $RESP
TABLE_ID=$(echo $RESP | python3 -c "import sys,json,re; r=json.load(sys.stdin); m=re.search(r\"ID: '([A-F0-9a-f]{32})'\",r.get('message','')); print(m.group(1) if m else r.get('error','FAIL'))")
echo "Table ID: $TABLE_ID"
Do not use get_uuid() or manual SQL — the webhook handles it internally.
For each column, use CreateColumn:
curl -s -X POST "${ETENDO_URL}/webhooks/CreateColumn" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d '{
"tableID": "'${TABLE_ID}'",
"columnNameDB": "{column_name}",
"name": "{Visible Name}",
"referenceID": "{REF_ID}",
"moduleID": "'${MODULE_ID}'",
"canBeNull": "{true|false}",
"defaultValue": "{value}"
}'
Most used Reference IDs:
| ID | Type | When to use |
|---|---|---|
10 | String | Short texts, names (AD default length 60, webhook creates VARCHAR 200) |
14 | Text | Long descriptions, notes |
11 | Integer | Whole numbers, numeric codes |
22 | Amount/Decimal | Prices, scores, durations |
15 | Date | Dates |
20 | Yes/No | Checkboxes, boolean flags |
17 | List | Fields with a closed list of values |
19 | TableDir | FK to another table — only works if your module owns the table |
30 | Search | FK to any table (required for extension columns on other modules' tables) |
AD_COLUMN.FIELDLENGTH controls the maximum input length in the UI. A value of 0 makes the field uneditable (zero-length input box). Always set an appropriate value:
| Reference | Recommended fieldlength |
|---|---|
| String (10) | 60–200 (match your VARCHAR size) |
| Text (14) | 2000 or more |
| Integer (11) | 10 |
| Amount/Number (22) | 10 |
| Date (15) | 19 |
| DateTime (16) | 19 |
| Yes/No (20) | 1 |
| List (17) | 60 |
| TableDir (19) / Search (30) | 32 |
The CreateColumn webhook should set this automatically. If creating columns via SQL, you must set fieldlength explicitly — the default of 0 will break the UI.
When your module adds a column to a table owned by another module (not just core — any other module), the webhook detects this automatically (moduleID ≠ table's owner module) and adds the EM_{PREFIX}_ prefix. This keeps extensions logically separate in the schema.
How it works:
"columnNameDB": "Is_Course" (without your prefix)EM_SMFT_Is_Course (adds EM_ + your module's DB prefix)"SMFT_Is_Course", the webhook detects the prefix is already there and avoids duplicationRestrictions on extension columns:
COPDEV_ExternalTableDirRef. Use Search (ref 30) instead for FK references on extension columns.AD_ELEMENT record — CreateColumn creates it automatically, but columns created via SQL need the element sync (see Step 6).Use the CreateView webhook for database views:
RESP=$(curl -s -X POST "${ETENDO_URL}/webhooks/CreateView" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d '{
"Name": "{LogicalName}",
"DBTableName": "{PREFIX_ViewName}",
"ModuleID": "'${MODULE_ID}'",
"DataAccessLevel": "3",
"Description": "{description}",
"Help": "{description}"
}')
echo $RESP
The CreateView webhook checks columns and registers the view automatically.
References define field value constraints — either a fixed list of values or a table lookup. These use headless endpoints (base URL: {ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/).
17)A list reference provides a closed set of values (e.g., Status: Active/Inactive/Pending).
# 1. Look up the module ID via prefix:
MODULE_RESP=$(curl -s -H "Authorization: Bearer ${ETENDO_TOKEN}" \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ModulePrefix?name={PREFIX}")
# Save the "module" property (NOT "id") — this is the AD_MODULE_ID
# 2. Create the reference header (parentReference="17" for list type):
HEADER_RESP=$(curl -s -X POST \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ReferencesHeader" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{
\"name\": \"{ReferenceName}\",
\"description\": \"{description}\",
\"helpComment\": \"{help}\",
\"parentReference\": \"17\",
\"module\": \"${MODULE_ID}\"
}")
# Extract the header ID from the response
# 3. Create list items (one per value):
curl -s -X POST \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ReferencesList" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{
\"reference\": \"${HEADER_ID}\",
\"name\": \"{ItemName}\",
\"searchKey\": \"{first 3 letters}\",
\"module\": \"${MODULE_ID}\"
}"
# Repeat for each list item
18)A table reference points to another table, showing a specific column as display value.
# 1. Look up the target table:
TABLE_RESP=$(curl -s -H "Authorization: Bearer ${ETENDO_TOKEN}" \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/Table?dBTableName={target_table}")
# Save the table ID and name
# 2. Get columns of that table (set _endRow to 200):
COLS_RESP=$(curl -s -H "Authorization: Bearer ${ETENDO_TOKEN}" \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ViewColumn?table=${TABLE_ID}&_endRow=200")
# Ask user which column is the key column and which is the display column
# 3. Create the reference header (parentReference="18" for table type):
HEADER_RESP=$(curl -s -X POST \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ReferencesHeader" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{
\"name\": \"{ReferenceName}\",
\"description\": \"{description}\",
\"helpComment\": \"{help}\",
\"parentReference\": \"18\",
\"module\": \"${MODULE_ID}\"
}")
# 4. Create the table reference tab with key and display columns:
curl -s -X POST \
"${ETENDO_URL}/sws/com.etendoerp.etendorx.datasource/ReferencesTab" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{
\"reference\": \"${HEADER_ID}\",
\"table\": \"${TABLE_ID}\",
\"keyColumn\": \"${KEY_COL_ID}\",
\"displayColumn\": \"${DISPLAY_COL_ID}\"
}"
After creating a reference, remember its header ID — you'll use it as the referenceID when creating columns with CreateColumn (instead of the standard IDs like 17 or 18).
After creating tables, columns, or views, run the mandatory post-creation hooks:
# 1. TableChecker — detect and register column changes
curl -s -X POST "${ETENDO_URL}/webhooks/CheckTablesColumnHook" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{\"TableID\": \"${TABLE_ID}\"}"
# 2. SyncTerms — synchronize terms/translations
curl -s -X POST "${ETENDO_URL}/webhooks/SyncTerms" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" -d '{}'
# 3. ElementsHandler — auto-correct elements for this table
curl -s -X POST "${ETENDO_URL}/webhooks/ElementsHandler" \
-H "Authorization: Bearer ${ETENDO_TOKEN}" \
-H "Content-Type: application/json" \
-d "{\"TableID\": \"${TABLE_ID}\", \"Mode\": \"READ_ELEMENTS\"}"
This sequence is mandatory — skipping it leads to inconsistencies in the Application Dictionary (missing element translations, unregistered columns, etc.).
After creating columns (via webhook or SQL), verify that fieldlength is set correctly:
-- Find columns with fieldlength = 0 (will be uneditable in the UI)
SELECT c.columnname, c.fieldlength, r.name as reference
FROM ad_column c
JOIN ad_reference r ON c.ad_reference_id = r.ad_reference_id
WHERE c.ad_table_id = '${TABLE_ID}' AND c.fieldlength = 0;
If any are found, fix them using the recommended values from the fieldlength table in Step 5.
Important — columns created via SQL vs webhook:
The CreateColumn webhook creates the physical column, the AD_COLUMN, and the AD_ELEMENT all in one call. But if a column was created directly via SQL (ALTER TABLE ADD COLUMN), only the physical column exists — there is no AD_COLUMN and no AD_ELEMENT. In that case:
CheckTablesColumnHook detects the physical column and creates the missing AD_COLUMNAD_ELEMENT — you must sync elements manuallyAD_ELEMENT, RegisterFields will fail with NPERun this SQL after CheckTablesColumnHook to create and link missing elements:
-- Create missing AD_ELEMENT records
INSERT INTO ad_element (ad_element_id, ad_client_id, ad_org_id, isactive,
created, createdby, updated, updatedby, columnname, name, printname)
SELECT get_uuid(), '0', '0', 'Y', now(), '0', now(), '0',
c.columnname, c.name, c.name
FROM ad_column c
WHERE c.ad_table_id = '{TABLE_ID}'
AND NOT EXISTS (
SELECT 1 FROM ad_element e
WHERE LOWER(e.columnname) = LOWER(c.columnname)
);
-- Link columns to their elements
UPDATE ad_column c SET ad_element_id = (
SELECT ad_element_id FROM ad_element e
WHERE LOWER(e.columnname) = LOWER(c.columnname) LIMIT 1
)
WHERE c.ad_table_id = '{TABLE_ID}'
AND c.ad_element_id IS NULL;
With Tomcat DOWN (important — export.database requires Tomcat stopped):
JAVA_HOME=$(/usr/libexec/java_home -v 17 2>/dev/null || echo "$JAVA_HOME")
JAVA_HOME=${JAVA_HOME} ./gradlew resources.down
JAVA_HOME=${JAVA_HOME} ./gradlew export.database -Dmodule={javapackage} > /tmp/etendo-export.log 2>&1
tail -5 /tmp/etendo-export.log
# IMPORTANT: bring services back up after export
JAVA_HOME=${JAVA_HOME} ./gradlew resources.up
Wait for containers to be healthy before running smartbuild or other webhook-dependent operations.
+ Table {tablename} created and registered in AD
Columns added: {N}
Table ID: {ad_table_id}
Post-creation hooks: TableChecker + SyncTerms + Elements ✓
Next steps:
/etendo:window -> expose the table in the UI
/etendo:smartbuild -> recompile and deploy