kwenter/AI_LAYER_DESIGN_NOTES.md

AI Layer & Event/Audit Design Notes

These notes keep the useful ideas from an earlier ChatGPT brainstorm. They have been corrected against the real Axpert 11.2 code and the krishtech database. The rest of that conversation is no longer needed.

• Scope: the AI agent, the event/action engine, the SQL layer and the audit engine.
• Out of scope: the platform spec itself (forms, grids, reports, security, workflow). That comes from the code analysis.

---

1. Guiding principles

1. Don't clone the software. Work out the behaviour, the metadata and the database, then build a clean web engine. Write all code from scratch; never copy Axpert code, SQL or assets.
2. Replace the runtime first, then grow into a low-code platform. First run existing Axpert apps (forms, reports, workflow) against the existing database. Then add the designer and the AI-driven app building.
3. The agent defines, the runtime enforces, the database stores. The AI never runs anything directly. It produces application definitions, which are validated, previewed and then run by the runtime.

2. Event → Action model

Events are stored as structured actions (JSON), not raw SQL or script text.

{
  "trigger": "FIELD_CHANGE",
  "field": "customer_code",
  "actions": [
    {
      "type": "LOOKUP",
      "source": "customer",
      "where":  { "customer_code": "$field.customer_code" },
      "select": ["customer_name", "credit_limit"],
      "map":    { "customer_name": "$field.customer_name",
                  "credit_limit":  "$field.credit_limit" }
    }
  ]
}

Triggers map to Axpert's apply values:

Trigger	Axpert apply value
FORM_LOAD	On Form Load
DATA_LOAD	On Data Load
FIELD_ENTER	On Field Enter
FIELD_CHANGE	On Field Exit
BEFORE_SAVE	(new)
AFTER_SAVE	After save transaction
BEFORE_DELETE	(new)
BUTTON_CLICK	(new)

Action types:

Group	Actions
UI	SHOW, HIDE, ENABLE, DISABLE, REQUIRE, SET_VALUE, MESSAGE, ADD_ROW
Data	LOOKUP (preferred), SQL_QUERY, SQL_INSERT, SQL_UPDATE, SQL_DELETE, PROCEDURE_CALL, FUNCTION_CALL
Flow	IF / ELSE_IF / ELSE (condition = expression in the shared expression language)
Navigation	OPEN_FORM, OPEN_FORM_WITH_DATA, OPEN_REPORT, OPEN_PAGE (Axpert: LoadForm, LoadFormAndData, LoadIView, LoadPage, OpenPage)
Other	FILL_GRID, SAVE, PRINT, CALL_API

Rule example, written the way a user would say it:

WHEN customer_type changes
IF   customer_type = "CREDIT"
THEN show credit_limit, enable credit_days, require credit_limit

Converting old Axpert events

Old events are parsed and converted, never executed as raw code:

Axpert <actions> XML / formcontrol tokens / expressions
      ↓ parser
    syntax tree
      ↓ converter
    structured actions (above)

The Axpert format is already known from the code analysis:

• <actions> holds named actions with apply="On Field Exit|…". Each action contains steps:
  • op=0 if (with an iif expression in exprset)
  • op=1 else
  • op=5 task (Field Access, Save, Open TStruct/Iview)
  • op=8 end
• <formcontrol> holds token lists:
  • Triggers: f<field> / f_On Form Load.
  • Control words: cif, celseif, celse, cend, cand, with et/net for equals / not equals.
  • Target prefixes: 8 hide, 9 show, 1/2 enable/disable, 3 set value.
• Advanced users can still hand-write events in an Advanced Event Editor (Monaco).

3. SQL layer

Event → Action Engine → Query Compiler → SQL Executor → DB adapter → Postgres
                                                     ↓
                                    result mapping → form fields

1. The agent prefers LOOKUP, a high-level description of the query. The Query Compiler turns it into SQL for the target database, so the agent doesn't have to think about database-specific syntax.
2. Raw SQL is allowed for complex cases: aggregates, joins, updates and stored procedures.
3. Every query is parameterized. Queries use :customer_code placeholders that are bound at run time, and are never built by pasting strings together. Axpert does exactly that, and it is a weakness you can sell against.
4. Axpert's SQL placeholders must be supported:
   • :fieldname binds to the current field's value.
   • {field*} expands to an IN-list over all grid rows.
   • Parent values come from the current row or DC context.
5. No endpoint ever runs SQL sent by the client. Axpert's callExecuteSQL does, and it must not be copied. Named SQL is kept server-side and allow-listed, the equivalent of Axpert's axdirectsql.
6. Database adapter: support Postgres first, because the krishtech customer runs PostgreSQL 16. Add MSSQL or Oracle only if a paying customer needs one.

4. AI agent: writing SQL and definitions automatically

The user describes what they want in business terms and never has to write SQL.

User: "When I select a customer, show their name and credit limit."
  ↓ intent            FIELD_CHANGE on customer_code
  ↓ schema agent      read metadata: Customer form → customer_code, customer_name, credit_limit
  ↓ query planner     LOOKUP customer WHERE customer_code = current RETURN name, credit_limit
  ↓ SQL generator     parameterized SQL (only for complex cases)
  ↓ validation        schema check + test run (EXPLAIN)
  ↓ event definition  attached to the field
  ↓ preview           the user sees it working before publishing

Complex example: "Average monthly sales for the last 6 months, excluding cancelled orders." The agent reads the schema and writes the aggregate SQL itself. The SQL is hidden unless the user opens the advanced details.

Safety limits:

• The agent works only from your metadata and schema (the metadata tables, information_schema). It never guesses table names.
• Every definition the agent produces is validated against the metadata schema (Zod or JSON Schema) before it is saved.
• AI-generated reads run under a read-only database role with row-level permissions applied.
• AI-generated writes (SQL_UPDATE/INSERT/DELETE, procedures) always need a human to approve them before publishing.
• Every AI change is versioned and shows a diff, so it can be rolled back.

Other agent features to build on the same pipeline:

• Build an app from a description, e.g. "Create a GRN form with a PO lookup and item grid."
• Ask a question in plain language and get a report.
• Explain and write expressions.
• Fill a form from a document, e.g. an invoice PDF becomes a purchase bill with its item grid filled.
• Workflow insights and anomaly flags.

5. Audit engine

Axpert today: one <transid>history table per form, with 81 of them in krishtech. Columns: recordid, fieldname, oldvalue, newvalue, rowno, modno, frameno, …. Each form can track all fields or selected fields, and optionally only some users.

Design: a separate Audit Engine module, stored in the same database for V1.

Save / Delete Engine
  → capture OLD values → write data → capture NEW values
  → Audit Engine (policy + change capture)
  → audit tables        ← all in ONE database transaction (consistent)

Tables:

audit_transaction   -- one row per save or delete
  audit_id, app, form (transid), record_id, action (INSERT/UPDATE/DELETE/CANCEL),
  user_id, at (timestamptz), ip, device, source (form/api/agent/import),
  definition_version

audit_change        -- one row per field changed
  audit_id, dc, row_no, row_key, field, old_value, new_value

dc, row_no and row_key are needed so that changes to grid rows can be traced. ChatGPT's version left these out.

Audit policy lives in the form definition, and the agent sets it:

"audit": { "enabled": true, "mode": "selected_fields",
           "fields": ["salary", "designation"], "users": "all" }

"Track changes to salary and designation" → the agent writes this policy, and the runtime does the rest.

Compatibility: per-form views (<transid>history) built on the new tables, so Axpert-style history screens keep working.

Scaling path (not needed for the first version). The rough load: 5,000 stores × 100 transactions × 20 fields is about 10M change rows a day.

V1: same Postgres, audit written in the same transaction (partition audit_change by month)
V2: outbox table → NATS JetStream → audit store (Postgres, or ClickHouse for analytics)

The application model does not change between V1 and V2; only the storage behind the Audit Engine does.

6. Overall runtime shape

User ──► Conversational Agent ──► Application Definition (forms, events, rules, audit policy)
                                            │ validate + preview + publish
                                            ▼
                                        RUNTIME
          ┌──────────────┬──────────────┼──────────────┬──────────────┐
      Form Engine   Event/Action    Rule/Expr      Security       Workflow
                      Engine       (shared)        Engine          Engine
                         │
          ┌──────────────┼──────────────┐
      UI actions     DB actions      Mapping
                         │
                  Query Compiler → SQL Executor → DB adapter
                         │
                    Save Engine ──► Audit Engine
                         │
                  Postgres (business data + new metadata schema + audit)

7. Open decision

Go vs TypeScript for the backend. The expression engine must run in both the browser and the server.

• TypeScript everywhere: one codebase, the simplest option.
• Go backend: the expression engine is written in Go and compiled to WASM for the browser.

Decide this before starting the metadata schema and the expression engine.