Flexurio 无代码 API 引擎：用 JSON 配置生成多数据库 REST 接口

Flexurio No‑Code API Engine

Declarative, database‑driven REST endpoints generated from JSON configuration.

Ship full CRUD plus advanced data operations (GET / POST / PUT / DELETE / PATCH / TRACE, import & export) for any table — without writing boilerplate code. Built with Rust + Actix Web.

flx-nocode-api · supports MySQL · PostgreSQL · SQLite · MSSQL · MongoDB

Table of Contents

1. What is Flexurio?
2. How it works
3. Quick start (SQLite)
4. Installation
5. Environment variables (.env)
6. Configuration layout (LOC_CONFIG)
7. Entity schema reference
8. Custom ID generation (function)
9. Master‑Detail (Header‑Detail) transactional orchestration
10. Declarative Action Triggers & ERP Cascading Workflows (action_triggers)
11. Document Immutability & State Machine Governance (locked_when & state_machine)
12. Database seeding (seed)
13. Hooks & validation
14. Formula placeholders
15. Endpoint reference
16. Authentication & authorization
17. Import & export
18. MCP server (Model Context Protocol)
19. Column encryption
20. Logging & observability
21. Database feature flags (compile‑time)
22. Multi‑target build script (build.sh)
23. Troubleshooting
24. Security checklist
25. Contributing & license

1. What is Flexurio?

Flexurio No‑Code API lets you stand up secure, multi‑database REST endpoints by describing each entity (table) in a JSON file. You write configuration, not code; the engine generates the HTTP surface, validates requests, talks to the database, and enforces authentication.

Typical use cases:

• Rapid prototyping of admin / data panels.
• Internal tooling and back‑office APIs.
• Putting a clean REST API over an existing MySQL / Postgres / SQLite / MSSQL / MongoDB database.
• Computed / formula‑driven flows and change‑capture journaling (PATCH & TRACE).

2. How it works

At startup the engine:

1. Reads the active config profile path from the LOC_CONFIG environment variable.
2. Loads enabled route names from LOC_CONFIG/routes.json.
3. Loads one entity schema per route from LOC_CONFIG/entity/<route>.json.
4. Ensures the core tables (flx_users, flx_roles) exist and seeds a default admin if none exists.
5. Registers a uniform REST surface for every route (only the HTTP methods you enable per schema).
6. Applies JWT authentication to all non‑public routes (with public overrides and an optional IP allow‑list).

                ┌────────────────────┐
   routes.json  │  enabled routes    │
                └─────────┬──────────┘
                          │  for each route
                          ▼
        LOC_CONFIG/entity/<route>.json  ──►  TableSchema
                          │
                          ▼
   ┌──────────────────────────────────────────────────────────┐
   │  Actix‑web router                                          │
   │   GET /<route>     POST /<route>    PUT /<route>/{id}      │
   │   DELETE /<route>/{id}   PATCH /<route>   TRACE /<route>   │
   │   POST /import/<route>   GET /export/<route>               │
   │   GET /validate/<route>  POST /generate/table/<route>      │
   └──────────────────────────────────────────────────────────┘
                          │
                          ▼
        DB adapter (MySQL · Postgres · SQLite · MSSQL · MongoDB)

3. Quick start (SQLite)

The fastest way to try Flexurio — no external database needed.

# 1. Get a binary (build from source shown here; see §4 for installers)
cargo build --release

# 2. Create a minimal .env
cat > .env <<'EOF'
DB_TYPE=sqlite
SQLITE_URL=sqlite://data.db
LOC_CONFIG=config
SECRET_KEY=replace_with_a_long_random_secret
ENCRYPT_KEY=replace_with_another_random_secret
PORT=8080
REQUIRE_AUTH=True
DEBUG=True
LOGGING=True
EOF

# 3. Run it
./target/release/flx-nocode-api

On first start the engine creates flx_users / flx_roles if missing and, if there is no admin yet, prints a generated password to the console:

Your admin Password: 1234

Log in with email admin and that password (see §16).

4. Installation

Requirements

• macOS or Linux (Windows works natively or via WSL).
• A database. SQLite needs nothing extra and is ideal for a first run.
• Rust toolchain only if you build from source.

4.1 Installer script (macOS / Linux)

curl -fsSL https://raw.githubusercontent.com/flexurio/flx-nocode-api/main/install-flexurio.sh | bash

The script detects your OS/architecture, downloads the matching asset from the latest GitHub release, installs the binary plus a convenient flexurio wrapper into ~/.local/bin, and adds it to your PATH. The flexurio command automatically reads the .env in the current working directory.

Reload your shell afterwards (e.g. source ~/.zshrc), then run flexurio from any folder containing a .env.

4.2 Windows

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\install-flexurio.ps1
# open a new terminal, then:
flexurio

4.3 Manual download (latest release)

# macOS (Apple Silicon)
curl -fsSL -o flx-nocode-aarch64-apple-darwin.pkg \
  https://github.com/flexurio/flx-nocode-api/releases/latest/download/flx-nocode-aarch64-apple-darwin.pkg
sudo installer -pkg flx-nocode-aarch64-apple-darwin.pkg -target /

# Linux (x86_64)
curl -fsSL -o flx-nocode-x86_64-unknown-linux-gnu \
  https://github.com/flexurio/flx-nocode-api/releases/latest/download/flx-nocode-x86_64-unknown-linux-gnu
chmod +x flx-nocode-x86_64-unknown-linux-gnu
install -m 0755 flx-nocode-x86_64-unknown-linux-gnu "$HOME/.local/bin/flx-nocode"

4.4 Build from source

git clone https://github.com/flexurio/flx-nocode-api.git
cd flx-nocode-api
cargo build --release
./target/release/flx-nocode-api

To build a smaller binary with only the database backend(s) you need, see §21.

4.5 Docker

docker-compose.yaml is included as a reference. Mount your static/, config/, and .env:

services:
  rust-app:
    build: .
    container_name: flx-nocode-api
    restart: always
    ports:
      - "2121:8080"        # access at http://localhost:2121
    volumes:
      - "./static:/app/static"
      - "./config:/app/config"
      - "./.env:/app/.env"

5. Environment variables (.env)

Copy the bundled example and edit it:

cp env .env

Pick exactly one DB_TYPE and its matching URL. Avoid duplicate keys.

Core

Locations

Authorization & external JWT (converter token)

Converter‑token mode is fail‑closed: if it is active and none of the verification variables are set, all converter‑token requests are rejected. See §16.

Limits, uploads & rate limiting

Performance & server tuning

Logging

Logging runs on a non‑blocking background thread, so it never blocks the request path. In production prefer LOG_MIN_LEVEL=info (most SQL/param logs are at debug) and consider sampling under high load.

6. Configuration layout (LOC_CONFIG)

LOC_CONFIG/
  routes.json          # enabled routes + public routes
  rules.json           # (optional) role / endpoint authorization rules
  entity/
    <route>.json       # one schema per route — file name must match the route

Several sample profiles are included: config, config/example, config/pos, config/tms, configmftl.

routes.json

{
  "routes": ["flx_users", "flx_roles", "banks", "bank_types"],
  "route_publics": ["login", "register"]
}

• routes — entities to expose. Each must have a matching entity/<name>.json.
• route_publics — routes reachable without a JWT.

Adding a new route

1. Add the name to routes in routes.json.
2. Create entity/<route>.json (copy an existing one; ensure table and the file name align).
3. (Optional) POST /generate/table/<route> to create the physical table (requires auto_generate: true).
4. GET /validate/<route> to confirm the schema matches the database.
5. Use the CRUD endpoints.

7. Entity schema reference

Each entity/<route>.json deserializes into a TableSchema (see src/model.rs). Sections:

Each HTTP method is only registered when its section sets "enable_method": true.

columns[]

{
  "name": "id",
  "type_data": "varchar(15)",
  "auto_increment": false,
  "nullable": false,
  "function": "{request.id_trans}/%Y/%m/000ID",
  "function_endpoint": "",
  "function_endpoint_path": "data",
  "encrypt": false,
  "default": null          // alias: "default_value"
}

ID generation fields at a glance

The three function* fields work together to auto‑generate an id; they only apply to POST/insert:

• function — the format. Split on /; tokens like %Y/%m/%d (date), {request.field} (request value), and NNNID (zero‑padded running number) are resolved, everything else is literal.
• function_endpoint — where the running number comes from. Leave empty → the engine uses MAX(id)+1 for ids sharing the same prefix. Set it → the engine GETs that URL (with the built prefix appended as ?prefix=… and the request's Authorization header forwarded) and uses the returned number. There is no fallback: if the call fails the insert is aborted.
• function_endpoint_path — how to read the number out of the endpoint's JSON response (default data).

A column with no id generation simply sets "function": "" and omits the two endpoint fields. See §8 for worked examples.

get (read pipeline)

"get": {
  "enable_method": true,
  "columns": ["banks.id", "banks.name", "bank_types.name"],
  "parameters": ["name.eq", "bank_type_id.eq"],
  "join_tables": [
    { "table": "bank_types", "columns": ["name"], "logical": "banks.bank_type_id = bank_types.id", "type_join": "left" }
  ],
  "column_groups": [],
  "having": [],
  "order_by": ["banks.id"],
  "where_clause": []       // alias: "where_clauses"
}

• parameters declares which query‑string filters are accepted, in the form column.operator (e.g. name.eq, created_at.gte). Clients then call GET /banks?name.eq=BCA.
• Pagination is controlled by LIMIT_DEFAULT / LIMIT_MAX.

8. Custom ID generation (function)

Set function on a column (typically id) to build a formatted identifier on insert — e.g. SO/2026/01/0001. The pattern is split on / and each token is resolved:

Example pattern and the value it produces:

{ "name": "id", "type_data": "varchar(15)", "function": "{request.id_trans}/%Y/%m/000ID" }

request.id_trans = "SO"   →   SO/2026/01/0001

Where the running number comes from

By default the …ID token is computed as MAX(id) + 1 for ids sharing the same prefix, inside the same transaction as the insert. You can instead fetch it from an external endpoint — useful when sequence numbers are owned by another service.

Add function_endpoint (and optionally function_endpoint_path) to the same column:

{
  "name": "id",
  "type_data": "varchar(15)",
  "function": "{request.id_trans}/%Y/%m/000ID",
  "function_endpoint": "http://localhost:8080/api/next-sequence",
  "function_endpoint_path": "data"
}

Behavior when function_endpoint is set:

1. The URL is interpolated ({request.field} placeholders are filled from the request body).
2. The already‑built prefix is appended as a query param, URL‑encoded — e.g. ?prefix=SO%2F2026%2F01 — so the endpoint can scope the sequence per prefix.
3. A GET is sent; the inbound request's Authorization header is forwarded.
4. The response must be JSON. The number is read from function_endpoint_path (dotted path, default data) and coerced to an integer.
5. The number is zero‑padded to the token width and spliced into the id.

Expected response shape (with the default path data):

{ "data": 1 }

→ produces SO/2026/01/0001.

No fallback: if the endpoint times out, returns a non‑2xx status, or the field is missing/non‑numeric, the insert is aborted with an error. Leave function_endpoint empty to use the built‑in MAX(id)+1 strategy.

Implementation: src/nocode/repositories/data_create_repo.rs (fetch_next_number_from_endpoint and query_next_number_from_max).

9. Master‑Detail (Header‑Detail) transactional orchestration

Flexurio provides first-class, atomic transactional orchestration for Master‑Detail (Header‑Detail / Parent‑Child) business workflows — such as Purchase Orders with line items, Invoices with tax charges, or Sales Orders with products.

Instead of writing multiple manual API requests and managing partial failure rollbacks on the frontend, clients send a single payload with nested items. The engine orchestrates parent generation, foreign key injection, and child batching within a single ACID database transaction.

                ┌────────────────────────────────────────────────────────┐
   Single POST  │  { id_trans: "PO", customer: "ACME", items: [...] }   │
                └───────────────────────────┬────────────────────────────┘
                                            │
                                            ▼
                       ┌────────────────────────────────────────┐
                       │  Atomic Database Transaction (ACID)     │
                       │                                        │
                       │  1. INSERT Header (e.g. PO/2026/01/001)│
                       │  2. Extract/Auto-gen Parent PK         │
                       │  3. Inject po_id into each child item  │
                       │  4. Bulk INSERT Detail Items           │
                       │  5. COMMIT (or ROLLBACK all on error)  │
                       └────────────────────────────────────────┘

9.1 Schema Configuration (details[])

Configure one or more detail relationships inside LOC_CONFIG/entity/<parent_route>.json:

{
  "table": "transaction_purchase_orders",
  "primary_key": {
    "columns": ["id"]
  },
  "columns": [
    { "name": "id", "type_data": "varchar(20)", "function": "{request.id_trans}/%Y/%m/000ID" },
    { "name": "customer", "type_data": "varchar(100)" },
    { "name": "total_amount", "type_data": "decimal(15,2)" }
  ],
  "details": [
    {
      "field": "items",
      "target_table": "transaction_purchase_order_items",
      "foreign_key_column": "po_id",
      "parent_key_column": "id",
      "columns": ["item_code", "description", "qty", "unit_price", "subtotal"],
      "update_strategy": "replace",
      "cascade_delete": true
    }
  ],
  "post": { "enable_method": true, "columns": ["id_trans", "customer", "total_amount"] },
  "put": { "enable_method": true, "columns": ["customer", "total_amount"] },
  "get": { "enable_method": true, "columns": ["id", "customer", "total_amount"] },
  "del": { "enable_method": true, "type_delete": "hard" }
}

9.2 Creating Master‑Detail Records (POST)

Send a POST /<parent_route> with multipart/form-data or JSON containing the nested items array:

{
  "id_trans": "PO",
  "customer": "PT Maju Bersama",
  "total_amount": 1500000,
  "items": [
    {
      "item_code": "ITM-001",
      "description": "Mechanical Keyboard",
      "qty": 2,
      "unit_price": 500000,
      "subtotal": 1000000
    },
    {
      "item_code": "ITM-002",
      "description": "Ergonomic Mouse",
      "qty": 1,
      "unit_price": 500000,
      "subtotal": 500000
    }
  ]
}

Execution Lifecycle:

1. The engine generates or assigns the parent primary key (e.g. PO/2026/01/0001 via function pattern or auto-increment).
2. The parent header is inserted into transaction_purchase_orders.
3. The generated id is automatically injected as po_id: "PO/2026/01/0001" into each item in items.
4. All child items are bulk-inserted into transaction_purchase_order_items.
5. The entire operation is committed atomically. If any detail record fails validation or DB constraint, the parent record is automatically rolled back.

9.3 Updating Master‑Detail Records (PUT)

Send PUT /<parent_route>/{id} with the updated header fields and new/modified detail items:

{
  "customer": "PT Maju Bersama Perkasa",
  "total_amount": 2000000,
  "items": [
    { "item_code": "ITM-001", "description": "Mechanical Keyboard", "qty": 4, "unit_price": 500000, "subtotal": 2000000 }
  ]
}

Under "update_strategy": "replace" (default), existing child items for that parent are deleted and the new list is inserted within the transaction.

9.4 Reading Master‑Detail Records (GET)

When calling GET /<parent_route> or GET /<parent_route>/{id}, Flexurio automatically queries and embeds matching child records inside each parent item under the declared field name:

{
  "success": true,
  "data": [
    {
      "id": "PO/2026/01/0001",
      "customer": "PT Maju Bersama",
      "total_amount": 1500000,
      "items": [
        { "id": 1, "po_id": "PO/2026/01/0001", "item_code": "ITM-001", "qty": 2, "subtotal": 1000000 },
        { "id": 2, "po_id": "PO/2026/01/0001", "item_code": "ITM-002", "qty": 1, "subtotal": 500000 }
      ]
    }
  ],
  "total_data": 1
}

10. Declarative Action Triggers & ERP Cascading Workflows (action_triggers)

In enterprise ERP systems, a single primary transactional state change frequently triggers multiple automated secondary operations across inventory and financial ledgers.

Flexurio provides a native, declarative, engine-level action trigger framework configured under "action_triggers" (or alias "triggers") in the entity JSON schema. All trigger operations run within the same ACID database transaction as the primary update, ensuring complete financial rollbacks on error.

                  ┌─────────────────────────────────────────────────────────┐
    Single PATCH  │  PATCH /transaction_sales_order/105                     │
                  │  { "status": "SHIPPED" }                                │
                  └───────────────────────────┬─────────────────────────────┘
                                              │
                                              ▼
                         ┌────────────────────────────────────────┐
                         │  Single Atomic Transaction (ACID)      │
                         │                                        │
                         │  1. UPDATE status -> "SHIPPED"         │
                         │  2. Evaluate Action Trigger Condition  │
                         │     (status: "CONFIRMED" -> "SHIPPED") │
                         │  3. ITERATE detail items & DEDUCT      │
                         │     stock from transaction_product_lot │
                         │     [Assert: qty_available >= 0]       │
                         │  4. INSERT AR invoice draft into       │
                         │     transaction_account_receivable     │
                         │  5. INSERT BATCH balanced GL lines     │
                         │     (Debit AR / Credit Revenue)        │
                         │  6. Write Audit Log (action: "TRIGGER")│
                         │  7. COMMIT (or ROLLBACK ALL on error)  │
                         └────────────────────────────────────────┘

10.1 The ERP Cascading Workflow Challenge

📦 Key Case Study: Sales Order Fulfillment (transaction_sales_order)

When a warehouse manager approves the dispatch of goods by updating a Sales Order's status from 'CONFIRMED' (or 'APPROVED') to 'SHIPPED' (via PATCH /transaction_sales_order/{id}), the backend engine must automatically execute 3 critical cascading actions within a single atomic database transaction:

1. Inventory Stock Deduction: Automatically query line items from transaction_sales_order_item and deduct the corresponding quantity (qty) from the finished goods lot inventory in transaction_product_lot.
2. Accounts Receivable (AR) Invoicing: Automatically create an AR invoice draft in transaction_account_receivable matching the Sales Order's total amount and customer details.
3. General Ledger (GL) Auto-Posting: Automatically insert balanced double-entry journal lines into transaction_general_ledger_line (Debit Accounts Receivable / Credit Sales Revenue).

Why pre_process and post_process Were Insufficient

Historically, developers attempted to use put.post_process with raw SQL strings. However, relying on post_process fails in complex ERP scenarios due to 4 fundamental architectural limitations:

1. Lack of Conditional Execution (Runs Unconditionally):
   • Raw SQL in post_process executes every single time an update is made, regardless of which field was modified.
   • Example Problem: If an operator updates only the shipping address via PATCH /transaction_sales_order/105 with {"shipping_address": "Jakarta"}, static post_process SQL would erroneously deduct stock again. It cannot evaluate state transitions such as "Only execute when status transitions from 'CONFIRMED' to 'SHIPPED'".
2. Inability to Dynamically Loop Over Relational Detail Arrays (BOM / Line Items):
   • A static SQL string cannot perform per-row dynamic iteration over relational child items.
   • Example Problem: A Sales Order may contain $N$ different line items. A static UPDATE query cannot dynamically iterate over child rows in transaction_sales_order_item and deduct matching stock in transaction_product_lot without resorting to complex, database-specific Stored Procedures.
3. Database Portability & Maintenance Overhead:
   • Forcing multi-table logic into stored procedures (JSON_TABLE in MySQL vs json_to_recordset in PostgreSQL) breaks Flexurio's cross-database no-code portability promise (SQLite, MySQL, PostgreSQL, MSSQL).
4. Lack of Payload Variable Extraction for Indirect Data:
   • post_process can only substitute direct fields present in the incoming HTTP request body. When receiving a clean status update payload {"status": "SHIPPED"}, variables like {product_id} and {qty} do not exist in the request body, causing placeholders to fail or evaluate to NULL.

Critical Anti-Patterns: Why Client-Side Orchestration is Strongly Discouraged

Developers must avoid two common anti-patterns:

• Anti-Pattern 1: Sequential Frontend HTTP Calls

  1. PATCH /transaction_sales_order/105        (Update status -> SHIPPED)
  2. PUT   /transaction_product_lot/1          (Deduct stock Lot 1)
  3. PUT   /transaction_product_lot/2          (Deduct stock Lot 2)
  4. POST  /transaction_account_receivable     (Create AR Invoice)
  5. POST  /transaction_general_ledger_line    (Post GL Lines)

  • Loss of Transactional Atomicity (No ACID Guarantee): If the client loses connection or crashes at Step 3, the database is left in a corrupted, half-processed state.
  • Security & Data Tampering Risk: A malicious user can intentionally skip calling the stock deduction or AR creation endpoints.
  • High Latency & Overhead: 5+ sequential round-trip network calls introduce significant lag.
  • Complex Client Rollback Logic: Writing manual compensation/rollback code on the frontend is fragile and error-prone.

• Anti-Pattern 2: Client Payload Detail Forcing

  PATCH /transaction_sales_order/105
  { "status": "SHIPPED", "items": [{ "product_id": 12, "qty": 5 }] }

  • Forcing clients to re-send detail items in status update payloads creates severe security vulnerabilities (payload tampering) and violates the Single Source of Truth (SSOT) since items already reside canonically in database detail tables.

Entity Schema & Data Flow Visualization

graph TD
    A["transaction_sales_order<br/>(Header: id=105, status=SHIPPED)"] -->|"Iterate Details"| B["transaction_sales_order_item<br/>(Line Items: Product 12 qty=5, Product 14 qty=10)"]
    B -->|"Deduct Stock"| C["transaction_product_lot<br/>(Lot A1: 100-5=95, Lot B2: 50-10=40)"]
    A -->|"Auto-Generate Draft"| D["transaction_account_receivable<br/>(Invoice: Total 250,000, Due +30d)"]
    A -->|"Auto-Post Balanced Entries"| E["transaction_general_ledger_line<br/>(Debit AR 250,000 / Credit Sales 250,000)"]

Loading

Below are the 5 canonical database tables involved in the PATCH /transaction_sales_order/105 shipment event:

1. transaction_sales_order (Header Document - Trigger Source)

2. transaction_sales_order_item (Relational Detail Items)

3. transaction_product_lot (Inventory Stock - Deducted)

4. transaction_account_receivable (AR Invoice - Auto-Generated Draft)

5. transaction_general_ledger_line (GL Journal Entries - Auto-Posted Balanced Voucher)

10.2 The Solution: Declarative Action Trigger Engine

Flexurio solves this natively with an enterprise Declarative Action Trigger Engine:

• ACID Transaction Scope: All trigger actions run within the primary update transaction (tx). If any action fails (e.g., negative stock, DB constraint), tx.rollback() is executed automatically.
• State Transition Evaluation: Triggers evaluate state transitions by comparing the pre-fetched old_record against new_record. A trigger with "from": "CONFIRMED" and "to": "SHIPPED" fires only when status actually transitions. Repeated updates or unrelated field modifications do not fire the trigger.
• First-Class PATCH /{route}/{id} Support: Clients can send partial JSON payloads (e.g. {"status": "SHIPPED"}) without resending the entire entity or detail arrays.
• Automated Audit Trail: Every executed trigger logs an audit entry to LOC_AUDIT via write_audit with action: "TRIGGER" for regulatory compliance.
• Cross-Database Unified Syntax: Works out of the box across MySQL, PostgreSQL, SQLite, and MSSQL.

10.3 Schema Configuration Reference (action_triggers[])

Add an "action_triggers" (or "triggers") array to LOC_CONFIG/entity/<route>.json:

Supported Action Types (actions[].type)

Concurrency & Atomic Row-Locking (atomic: true)

High-volume ERP environments face race condition risks during inventory deductions and account balance updates (e.g. concurrent checkout requests attempting to fulfill orders against the same remaining stock).

To eliminate dirty reads and stock overdrafts:

• Flexurio defaults atomic: true on update actions.
• For PostgreSQL and MySQL, the engine automatically injects a SELECT ... FOR UPDATE row lock on the target records prior to modification.
• For SQLite and MSSQL, operations are serialized within the exclusive ACID database transaction.
• If another transaction has locked the row, the incoming request waits until the lock is released or throws a transaction timeout, guaranteeing complete serializability.

Boundary Validations & Accounting Balance Assertion (validate)

Use validate inside update and insert_batch actions to enforce business invariants:

1. Lower-Bound Quantity Check (validate.min)

Prevents negative inventory balances:

"validate": {
  "min": {
    "qty_available": 0
  },
  "error_message": "Insufficient inventory for product {item.product_id} lot {item.lot_number}"
}

If the resulting calculated value drops below the threshold, the transaction rolls back immediately with an HTTP 400 Bad Request.

2. Double-Entry Accounting Balancing (validate.assert_balanced)

In financial ledgers, unbalanced journal entries violate fundamental accounting principles:

"validate": {
  "assert_balanced": {
    "debit_field": "debit",
    "credit_field": "credit",
    "tolerance": 0.001
  },
  "error_message": "General Ledger transaction unbalanced: total debits must equal total credits"
}

The engine sums all rows in insert_batch and verifies $|\sum \text{debits} - \sum \text{credits}| \le \text{tolerance}$. If unbalanced, the entire transaction is rejected.

Embedded Arithmetic Formula Evaluator ({calc: ...})

Flexurio includes an enterprise recursive-descent math evaluator for dynamic calculations inside set, values, rows, and accumulate:

• Syntax: {calc: expression} (e.g. {calc: item.qty * lookup.product.cost_price} or {calc: parent.subtotal * 0.11}).
• Operators: +, -, *, /, % with standard operator precedence and parentheses (...).
• Math Functions:
  • round(value, decimals) — e.g. round(parent.subtotal * 0.11, 2)
  • floor(value)
  • ceil(value)
  • abs(value)
  • min(a, b)
  • max(a, b)
• Direct Variable Support: Variables inside formulas can be referenced with or without braces: {calc: item.qty * product.cost_price} or {calc: {item.qty} * {product.cost_price}}.

Conditional Action Execution (condition & {if: ...})

Individual actions within an action_triggers pipeline can be conditionally executed:

{
  "type": "insert",
  "target_table": "transaction_shipping_notice",
  "condition": {
    "field": "shipping_method",
    "from": "*",
    "to": "COURIER"
  },
  "values": { ... }
}

Inline ternary conditional strings are also supported:

"{if: parent.is_taxable == 1 ? {calc: parent.subtotal * 0.11} : 0}"

Template Interpolation Tokens

Strings inside filter, set, values, rows, and error_message support dynamic token replacement:

Note on Action update: The set dictionary in update actions supports arithmetic modifications ("qty": "qty - {item.qty}"), as well as non-numeric column assignments including status transitions ("status": "FULFILLED"), dates ("closed_at": "{now:YYYY-MM-DD}"), booleans ("is_closed": true), and null.

State Machine & locked_when Harmony: When a document is locked (e.g., status IN ('APPROVED', 'POSTED')), users with authorized roles listed in state_machine.transitions[].roles can still submit status transitions (e.g., APPROVED $\to$ SHIPPED). The engine strictly permits only the status change while prohibiting modifications to any locked business columns.

10.4 Complete Production Configuration Example

Here is the complete configuration for LOC_CONFIG/entity/transaction_sales_order.json featuring master data lookup, line item COGS accumulation, lot inventory deduction with row-level locks, AR invoice creation, and balanced GL journal posting:

{
  "table": "transaction_sales_order",
  "primary_key": {
    "columns": ["id"],
    "auto_increment": true
  },
  "columns": [
    { "name": "id", "type_data": "int", "auto_increment": true, "nullable": false },
    { "name": "so_number", "type_data": "varchar(50)", "nullable": false },
    { "name": "customer_id", "type_data": "int", "nullable": false },
    { "name": "order_date", "type_data": "date", "nullable": false },
    { "name": "subtotal", "type_data": "decimal(15,2)", "default_value": "0" },
    { "name": "total_amount", "type_data": "decimal(15,2)", "default_value": "0" },
    { "name": "status", "type_data": "varchar(20)", "default_value": "'DRAFT'" },
    { "name": "notes", "type_data": "text", "nullable": true }
  ],
  "locked_when": {
    "status": ["SHIPPED", "PAID", "POSTED", "CANCELLED"],
    "except_columns": ["notes"]
  },
  "state_machine": {
    "field": "status",
    "initial": "DRAFT",
    "transitions": [
      { "from": "DRAFT", "to": "CONFIRMED", "roles": ["sales", "sales_manager", "admin"] },
      { "from": "CONFIRMED", "to": "SHIPPED", "roles": ["warehouse", "logistics", "admin"] },
      { "from": "SHIPPED", "to": "PAID", "roles": ["finance", "admin"] },
      { "from": ["DRAFT", "CONFIRMED"], "to": "CANCELLED", "roles": ["sales_manager", "admin"] }
    ]
  },
  "details": [
    {
      "field": "items",
      "target_table": "transaction_sales_order_item",
      "foreign_key_column": "sales_order_id",
      "parent_key_column": "id",
      "columns": ["product_id", "lot_number", "qty", "unit_price"],
      "update_strategy": "replace"
    }
  ],
  "action_triggers": [
    {
      "name": "sales_order_fulfillment",
      "event": "on_update",
      "condition": {
        "field": "status",
        "from": ["CONFIRMED", "APPROVED"],
        "to": "SHIPPED"
      },
      "actions": [
        {
          "type": "iterate_detail",
          "target_table": "transaction_sales_order_item",
          "actions": [
            {
              "type": "lookup",
              "target_table": "master_product",
              "filter": {
                "id": "{item.product_id}"
              },
              "as": "product"
            },
            {
              "type": "accumulate",
              "accumulate": {
                "total_cogs": "{calc: item.qty * product.cost_price}"
              }
            },
            {
              "type": "update",
              "target_table": "transaction_product_lot",
              "atomic": true,
              "filter": {
                "product_id": "{item.product_id}",
                "lot_number": "{item.lot_number}"
              },
              "set": {
                "qty_available": "qty_available - {item.qty}"
              },
              "validate": {
                "min": {
                  "qty_available": 0
                },
                "error_message": "Insufficient inventory for product {item.product_id} lot {item.lot_number}"
              }
            }
          ]
        },
        {
          "type": "insert",
          "target_table": "transaction_account_receivable",
          "values": {
            "so_id": "{parent.id}",
            "customer_id": "{parent.customer_id}",
            "invoice_date": "{now:YYYY-MM-DD}",
            "due_date": "{now+30d:YYYY-MM-DD}",
            "amount": "{parent.total_amount}",
            "status": "UNPAID"
          }
        },
        {
          "type": "insert_batch",
          "target_table": "transaction_general_ledger_line",
          "validate": {
            "assert_balanced": {
              "debit_field": "debit",
              "credit_field": "credit",
              "tolerance": 0.001
            },
            "error_message": "GL Transaction unbalanced: debits and credits must match"
          },
          "rows": [
            {
              "reference_id": "{parent.id}",
              "account_code": "1120",
              "description": "AR - Invoice {parent.so_number}",
              "debit": "{parent.total_amount}",
              "credit": 0
            },
            {
              "reference_id": "{parent.id}",
              "account_code": "4100",
              "description": "Sales Revenue - {parent.so_number}",
              "debit": 0,
              "credit": "{parent.subtotal}"
            },
            {
              "reference_id": "{parent.id}",
              "account_code": "2150",
              "description": "VAT Output Tax (11%)",
              "debit": 0,
              "credit": "{calc: parent.total_amount - parent.subtotal}"
            },
            {
              "reference_id": "{parent.id}",
              "account_code": "5100",
              "description": "Cost of Goods Sold - {parent.so_number}",
              "debit": "{acc.total_cogs}",
              "credit": 0
            },
            {
              "reference_id": "{parent.id}",
              "account_code": "1300",
              "description": "Inventory Finished Goods Asset",
              "debit": 0,
              "credit": "{acc.total_cogs}"
            }
          ]
        }
      ]
    }
  ],
  "get": { "enable_method": true, "columns": ["id", "so_number", "customer_id", "subtotal", "total_amount", "status", "notes"] },
  "post": { "enable_method": true, "columns": ["so_number*", "customer_id*", "order_date*", "subtotal*", "total_amount*", "status", "notes"] },
  "put": { "enable_method": true, "columns": ["so_number", "customer_id", "order_date", "subtotal", "total_amount", "status", "notes"] }
}

10.5 Request & Response Lifecycle

1. Firing the Shipment Trigger (PATCH)

The client sends a clean partial update request:

PATCH /transaction_sales_order/105 HTTP/1.1
Host: api.flexurio.com
Authorization: Bearer <token>
Content-Type: application/json

{
  "status": "SHIPPED"
}

Success Response (200 OK):

{
  "success": true,
  "message": "Data updated successfully",
  "total_data": 1,
  "data": {
    "id": 105,
    "status": "SHIPPED"
  }
}

2. Automatic Rollback on Stock Validation Failure

If any lot does not have enough stock to satisfy line item quantities, the engine halts immediately and executes a rollback:

Validation Failure Response (400 Bad Request):

{
  "success": false,
  "message": "Trigger 'sales_order_fulfillment' action failed: Insufficient inventory for product 12 lot LOT-2026-A1",
  "total_data": 0,
  "data": null
}

Result: The Sales Order status remains CONFIRMED, no inventory is deducted, no AR invoice is created, and no GL lines are posted. Database integrity is 100% preserved.

3. Automatic Rollback on Unbalanced Accounting Voucher

If journal debits do not equal credits within the specified tolerance, the trigger transaction immediately rolls back:

{
  "success": false,
  "message": "Trigger 'sales_order_fulfillment' action failed: GL Transaction unbalanced: debits and credits must match (total_debit=250000.00, total_credit=225000.00)",
  "total_data": 0,
  "data": null
}

10.6 Two-Step Delivery (Delivery Order) & Partial Fulfillment Pattern

In enterprise supply chain operations, shipping is frequently handled via a dedicated Delivery Order (Surat Jalan) entity rather than modifying the Sales Order directly. A single Sales Order can be fulfilled across multiple partial shipments.

Flexurio supports this natively by placing action_triggers on transaction_delivery_order:

{
  "table": "transaction_delivery_order",
  "action_triggers": [
    {
      "name": "delivery_order_dispatch",
      "event": "on_update",
      "condition": {
        "field": "status",
        "from": "DRAFT",
        "to": "DISPATCHED"
      },
      "actions": [
        {
          "type": "iterate_detail",
          "target_table": "transaction_delivery_order_item",
          "actions": [
            {
              "type": "update",
              "target_table": "transaction_product_lot",
              "atomic": true,
              "filter": {
                "product_id": "{item.product_id}",
                "lot_number": "{item.lot_number}"
              },
              "set": {
                "qty_available": "qty_available - {item.qty_shipped}"
              },
              "validate": {
                "min": { "qty_available": 0 },
                "error_message": "Insufficient stock for product {item.product_id} lot {item.lot_number}"
              }
            },
            {
              "type": "update",
              "target_table": "transaction_sales_order_item",
              "filter": {
                "sales_order_id": "{parent.sales_order_id}",
                "product_id": "{item.product_id}"
              },
              "set": {
                "qty_delivered": "qty_delivered + {item.qty_shipped}"
              }
            }
          ]
        }
      ]
    }
  ]
}

11. Document Immutability & State Machine Governance (locked_when & state_machine)

Enterprise ERP and accounting software require strict governance guarantees over document lifecycles to comply with audit regulations (such as SOX, GAAP, and IFRS) and prevent unauthorized status manipulations.

Flexurio provides two native, declarative engine features directly inside each entity schema:

1. Document Immutability Lock (locked_when)
2. State Machine Transition Matrix (state_machine)

11.1 Document Immutability Lock (locked_when)

Once a transactional document (e.g. Sales Order, Purchase Order, Bank Disbursement) reaches a finalized status such as SHIPPED, INVOICED, PAID, or POSTED, modifying line items, quantities, or financial totals corrupts inventory and ledger integrity.

Schema Definition

Add "locked_when" to LOC_CONFIG/entity/<route>.json:

{
  "table": "transaction_sales_order",
  "locked_when": {
    "status": ["SHIPPED", "PAID", "POSTED", "CANCELLED"],
    "except_columns": ["notes", "internal_memo"]
  }
}

Behavior & Enforcement

• Updates (PUT / PATCH):
  • Before applying any update, the engine reads the current record from the database.
  • If the record matches the locked_when conditions and the incoming payload contains fields outside except_columns, the transaction is rolled back immediately:

    {
      "success": false,
      "message": "Cannot modify locked record: field 'status' is currently 'SHIPPED'",
      "total_data": 0,
      "data": null
    }

• Deletions (DELETE):
  • Deleting a locked record is strictly forbidden. The delete request is rejected with HTTP 400 Bad Request:

    {
      "success": false,
      "message": "Cannot delete locked record: field 'status' is currently 'SHIPPED'",
      "total_data": 0,
      "data": null
    }

11.2 Declarative State Machine Transition Matrix (state_machine)

Business documents must transition through predictable lifecycles (e.g. DRAFT $\to$ CONFIRMED $\to$ SHIPPED $\to$ PAID). Unregulated updates could allow an operator to skip mandatory approval steps or resurrect a cancelled order.

Schema Definition

Add "state_machine" to LOC_CONFIG/entity/<route>.json:

{
  "table": "transaction_sales_order",
  "state_machine": {
    "field": "status",
    "initial": "DRAFT",
    "transitions": [
      { "from": "DRAFT", "to": "CONFIRMED", "roles": ["sales", "sales_manager", "admin"] },
      { "from": "CONFIRMED", "to": "SHIPPED", "roles": ["warehouse", "logistics", "admin"] },
      { "from": "SHIPPED", "to": "PAID", "roles": ["finance", "admin"] },
      { "from": ["DRAFT", "CONFIRMED"], "to": "CANCELLED", "roles": ["sales_manager", "admin"] }
    ]
  }
}

Behavior & Enforcement

1. Illegal State Transitions:
   • If a client attempts an illegal state jump (e.g. jumping from DRAFT directly to PAID), the engine rejects the request with HTTP 400 Bad Request:

     {
       "success": false,
       "message": "Illegal state transition on 'status': cannot transition from 'DRAFT' to 'PAID'",
       "total_data": 0,
       "data": null
     }

2. Role-Based Transition Guards:
   • The user's role is extracted from their authenticated JWT claims (role or rl).
   • If a warehouse operator (role: "warehouse") attempts to confirm a draft order (which requires role "sales" or "sales_manager"), the engine rejects the request with HTTP 403 Forbidden:

     {
       "success": false,
       "message": "Unauthorized state transition on 'status': role 'warehouse' is not permitted to transition from 'DRAFT' to 'CONFIRMED'",
       "total_data": 0,
       "data": null
     }

12. Database seeding (seed)

Flexurio supports declarative database seeding for initial master data, lookup tables, and test fixtures.

   LOC_SEED/ (or seed/)
     ├── banks.json          # JSON seed
     ├── bank_types.csv      # CSV seed with schema-aware type casting
     └── init_roles.sql      # Multi-statement raw SQL script

12.1 Enabling Seeding in Entity Schema

Add "seed": true to LOC_CONFIG/entity/<route>.json:

{
  "table": "banks",
  "seed": true,
  "columns": [
    { "name": "id", "type_data": "int", "auto_increment": true },
    { "name": "name", "type_data": "varchar(50)", "nullable": false },
    { "name": "code", "type_data": "varchar(10)", "nullable": false }
  ]
}

When "seed": true is enabled, the engine registers two administrative endpoints:

• POST /seed/<route>
• POST /generate/seed/<route>

Security: Seed endpoints require an Admin role token (admin, administrator, or bitmask 127 / */127). Non-admin requests receive 403 Forbidden.

12.2 Supported Seed File Formats

Seed files are stored in the directory configured by LOC_SEED (default: seed/). The engine automatically detects and loads files named <route>.* or <table_name>.*:

1. JSON (<LOC_SEED>/<route>.json)

An array of objects matching column names:

[
  { "name": "Bank Central Asia", "code": "BCA" },
  { "name": "Bank Mandiri", "code": "MANDIRI" },
  { "name": "Bank Rakyat Indonesia", "code": "BRI" }
]

2. CSV (<LOC_SEED>/<route>.csv)

Comma-separated values with a header row matching column names. Flexurio uses the entity schema to perform schema-aware type casting (converting integers, decimals, booleans, dates, timestamps, and JSON strings, while omitting empty auto-increment PKs):

name,code
Bank Central Asia,BCA
Bank Mandiri,MANDIRI
Bank Rakyat Indonesia,BRI

3. SQL Script (<LOC_SEED>/<route>.sql)

Raw multi-statement DDL/DML script. The engine parses and splits statements safely (preserving semicolons within quotes and ignoring line/block comments) and executes them inside a transaction:

-- Initial seed for banks
INSERT INTO banks (name, code) VALUES ('Bank Central Asia', 'BCA');
INSERT INTO banks (name, code) VALUES ('Bank Mandiri', 'MANDIRI');
INSERT INTO banks (name, code) VALUES ('Bank Rakyat Indonesia', 'BRI');

12.3 Triggering a Seed Run

Trigger seeding by sending an authenticated POST request:

curl -X POST http://localhost:8080/seed/banks \
  -H "Authorization: Bearer <ADMIN_JWT_TOKEN>"

Response:

{
  "success": true,
  "message": "Seeding for 'banks' completed successfully from 'seed/banks.json' (3 records inserted)",
  "total_data": 3,
  "data": null
}

13. Hooks & validation

post, put, and del can run extra logic around the main database operation. All hook strings are prefixed to indicate their kind; an empty string or a value without a recognized prefix is ignored.

These are the actual field names the engine reads. (Older drafts of this README referred to before/after; those keys are not used.)

SQL: hooks

"post": {
  "enable_method": true,
  "pre_process":  "SQL:UPDATE counters SET val = val + 1 WHERE name = 'menus'",
  "post_process": "SQL:INSERT INTO audit_logs(entity, action, user_id) VALUES('menus','CREATE',{request.created_by_id})",
  "columns": ["name"]
}

• Placeholders are bound as parameters automatically (SQL‑injection safe) — you do not write ? yourself.
• Each hook is a single statement. For multi‑table cascading transactional workflows (such as ERP lot deduction, AR generation, and GL posting), use declarative Action Triggers (§10). For stored procedures, use patch.pre_process_sp.
• pre_process / post_process run in the operation's transaction on SQL backends; validate_data runs first and can reject the request.

API: validation (validate_data)

Call an external endpoint and assert on its response before allowing the write. Format:

API:<METHOD>:<URL>|<operator>:<response_path>:<request_path>

Example — only allow a role that the /roles endpoint returns:

"validate_data": "API:GET:http://127.0.0.1:8080/roles|in:data:request.role"

SQL: validation expects a query returning an is_valid boolean:

"validate_data": "SQL:SELECT CASE WHEN email NOT LIKE '%@%' THEN FALSE ELSE TRUE END AS is_valid FROM customers WHERE email = {request.email}"

Required fields (* suffix)

The post.columns array lists the fields the POST endpoint accepts. To make a field mandatory, append a * to its name. The * is only a marker — the engine strips it and uses the real column name everywhere.

"post": {
  "enable_method": true,
  "columns": ["name*", "email*", "phone"]
}

Here name and email are required; phone is optional. A required field is rejected when it is absent, null, an empty string, or the literal string "null", returning:

400 Bad Request — Missing required field: name

Notes:

• A field is also treated as required (even without *) when its columns[] definition has "nullable": false and "auto_increment": false. The * suffix is the explicit way to enforce it for any column.
• Empty datetime/timestamp values ("" or "null") are coerced to SQL NULL before the required check runs.
• PUT (put.columns) uses the exact same * convention for updates.

14. Formula placeholders

Placeholders are available inside hooks and formula values:

Notes:

• For PUT, the path parameter /{id} is not auto‑injected into hooks — include id in the request body if a formula needs it.
• For POST with auto‑increment ids, the new id is not available via {request.*}. To reference it afterwards, use a custom id (columns[].function) or supply id yourself.
• Bindings are numeric/string‑inferred automatically. On PostgreSQL, ? placeholders are rewritten to $1, $2, … internally.

15. Endpoint reference

For a route <route> listed in routes.json (each method requires enable_method: true in its schema section):

Core / system endpoints:

16. Authentication & authorization

Login

curl -X POST http://localhost:8080/login \
  -H "Authorization: Basic $(printf 'admin:1234' | base64)"

• Credentials are passed via HTTP Basic auth (Basic base64(email:password)).
• Success returns a JWT with claims such as id, nm (name), rl (roles), and optional cs (custom claim from CUSTOME_JWT_QUERY).
• Send it on every protected request: Authorization: Bearer <token>.

The default admin (email admin) is seeded on first start; its generated password is printed to the console.

Authorization

• Routes in route_publics (plus /login, /register) are public.
• All other routes require a valid Bearer token.
• IPs/CIDRs in WHITE_LIST_IP bypass token checks.
• Fine‑grained, role‑/endpoint‑based rules can be defined in LOC_CONFIG/rules.json (per‑method permission_id, allowed_fields, and if conditions such as $user.role / $user.id).

Converter‑token mode (external IdP)

When routes.json defines a non‑default converter_token mapping, JWTs are issued by an external identity provider and Flexurio verifies their signature using the CONVERTER_JWT_* variables (§5). This is fail‑closed: with no verification key configured, converter‑token requests are rejected (unless CONVERTER_JWT_INSECURE_SKIP_VERIFY=true).

17. Import & export

• Import — POST /import/<route> with a multipart file. CSV and XLSX are supported; column headers must match the entity's insertable columns. Rows are inserted in batches (IMPORT_BATCH_SIZE).
• Export — GET /export/<route>?type=csv|xlsx returns the route's data in the requested format (defaults to CSV; falls back to CSV if XLSX generation fails). The same filtering as GET /<route> applies.

18. MCP server (Model Context Protocol)

Flexurio ships a built‑in MCP server so AI clients (Claude Desktop, Claude Code, Cursor, MCP Inspector, …) can discover your entities and read or write data through the same engine, validation and authorization as the REST API. It uses the official Rust SDK (rmcp) and supports protocol revisions 2024-11-05 through 2026-07-28.

18.1 Transports

18.2 Capabilities

Tools (each maps to the REST call shown and goes through the same service layer):

Every tool publishes an inputSchema (JSON Schema 2020‑12, entity restricted to valid names), an outputSchema, and returns structuredContent. Mistakes the model can fix (unknown entity, bad arguments, validation or authorization failures) come back as results with isError: true plus a hint; only unknown tool names are JSON‑RPC errors. Write tools are hidden when MCP_WRITE_TOOLS_ENABLED=false, and a tool is hidden when no entity enables its operation.

Resources: flexurio://guide (usage guide), flexurio://entities (catalogue), flexurio://entity/{name} (template, one per entity), flexurio://rules (rules.json, only for roles in MCP_ADMIN_ROLES).

Prompts: explore_entity, safe_write (inspect → propose → confirm → execute → verify), data_quality_report. Completions suggest entity names for prompt arguments and the resource template.

18.3 Authentication & authorization

• /mcp is protected by the normal JWT middleware. Get a token with POST /login (or use an externally issued JWT in converter‑token mode) and send Authorization: Bearer <token>.
• An unauthenticated request receives 401 with WWW-Authenticate: Bearer resource_metadata="…/.well-known/oauth-protected-resource/mcp", and the Protected Resource Metadata document (RFC 9728) is served at /.well-known/oauth-protected-resource[/mcp]. List your OAuth authorization server(s) in MCP_OAUTH_AUTHORIZATION_SERVERS and verify their tokens with the CONVERTER_JWT_* variables to enable the full MCP OAuth flow.
• Each tool call is authorized against rules.json exactly like the equivalent REST method + path (e.g. delete_record → DELETE /<route>/{id}). There is no MCP‑only bypass.
• Host headers are validated against MCP_ALLOWED_HOSTS (DNS‑rebinding protection) and, optionally, Origin against MCP_ALLOWED_ORIGINS.
• Values of sensitive‑looking fields (MCP_REDACT_FIELDS, default password,secret,token,api_key,apikey) are replaced with ***REDACTED*** before results reach the model.

18.4 Client configuration

Claude Code (HTTP):

TOKEN=$(curl -s -X POST http://localhost:8080/login \
  -H "Authorization: Basic $(printf 'admin:<password>' | base64)" | jq -r .data)
claude mcp add --transport http flexurio http://localhost:8080/mcp \
  --header "Authorization: Bearer $TOKEN"

Claude Desktop / any stdio client (claude_desktop_config.json):

{
  "mcpServers": {
    "flexurio": {
      "command": "/usr/local/bin/flx-nocode-api",
      "args": ["mcp", "--stdio"],
      "cwd": "/path/to/folder/with/.env",
      "env": { "MCP_STDIO_EMAIL": "admin" }
    }
  }
}

MCP Inspector: make mcp-inspect (HTTP) or npx @modelcontextprotocol/inspector flx-nocode-api mcp --stdio.

18.5 Configuration

Metrics: flx_mcp_requests, flx_mcp_tool_calls, flx_mcp_tool_errors on /metrics. Build without MCP: cargo build --release --no-default-features --features "mysql postgres sqlite mssql mongodb".

19. Column encryption

Set "encrypt": true on a column to store its value encrypted at rest using ENCRYPT_KEY (AES‑GCM). The engine encrypts on write and decrypts on read transparently. Keep ENCRYPT_KEY secret and stable — rotating it requires re‑encrypting existing data.

20. Logging & observability

• Structured logs cover endpoint registration and query execution; control verbosity with the LOG_*, DEBUG, and LOGGING variables (§5).
• GET /healthz for liveness/readiness probes.
• GET /metrics exposes Prometheus metrics.
• An audit trail is written to LOC_AUDIT.
• Logs and static assets are served under /static (e.g. GET /static/log/). Keep the audit log outside static/ so it is not publicly served.

21. Database feature flags (compile‑time)

Database backends are gated behind Cargo features so you can build a lean binary with only what you need.

[features]
default  = ["mysql", "postgres", "sqlite", "mssql", "mongodb", "mcp"]
mysql    = ["sqlx/mysql", "sqlx/chrono"]
postgres = ["sqlx/postgres", "sqlx/chrono"]
sqlite   = ["sqlx/sqlite", "sqlx/chrono"]
mssql    = ["tiberius/chrono", "bb8"]
mongodb  = ["dep:mongodb"]
mcp      = ["dep:rmcp", ...]   # MCP server, see §18

If you disable a backend but set DB_TYPE to it at runtime, the app exits with an error (e.g. mysql feature disabled).

# Only MySQL
cargo build --release --no-default-features --features mysql

# MySQL + SQLite
cargo build --release --no-default-features --features "mysql sqlite"

# Everything (default)
cargo build --release

# Build & run in one step
cargo run --release --no-default-features --features mysql

Smaller builds compile faster, produce smaller binaries, and remove unused code paths from production.

22. Multi‑target build script (build.sh)

build.sh produces per‑database, per‑OS binaries with feature‑gated builds, and optionally signs/notarizes macOS artifacts when Apple credentials are present.

Use ./build.sh or bash build.sh (not sh build.sh) — macOS ships Bash 3.2.

./build.sh [--db <list>] [--os <list>] [--arch <list>] [--help]

• --db — mysql,postgres,sqlite,all (default all).
• --os — macos,windows,linux,all (default all).
• --arch — x86_64,aarch64,all (filters after OS expansion, default all).

OS group expansion:

• macos → x86_64-apple-darwin, aarch64-apple-darwin
• windows → x86_64-pc-windows-gnu
• linux → x86_64-unknown-linux-gnu, aarch64-unknown-linux-gnu

Examples:

./build.sh                                   # all DBs, all OS targets
./build.sh --db mysql                        # MySQL only, all OS
./build.sh --db mysql,sqlite --os macos      # MySQL + SQLite for macOS (both arches)
./build.sh --db postgres --os macos --arch aarch64
./build.sh --db mysql --os windows,linux --arch x86_64

Artifacts land in release/ as flx-nocode-<driver>-<target> (Windows adds .exe; signed macOS produces .pkg). With --db all (default) a single combined multi‑driver binary is emitted per target as flx-nocode-<target> for installer compatibility.

For each driver the script runs cargo build --release --target <triple> --no-default-features --features <driver>. macOS signing/notarization activates when APPLE_ID, APPLE_TEAM_ID, an app‑specific password, APPLE_IDENTITY, APPLE_IDENTITY_INS, PRIMARY_BUNDLE_ID, and KEYCHAIN_PROFILE are set; otherwise it just copies the binary.

23. Troubleshooting

24. Security checklist

• Use long, random SECRET_KEY and ENCRYPT_KEY; keep them out of version control.
• Rotate keys periodically (reissue tokens; re‑encrypt data if ENCRYPT_KEY changes).
• Grant the database user least privilege.
• Keep the audit log (LOC_AUDIT) outside the publicly served static/ directory.
• Terminate TLS at a reverse proxy (nginx / traefik / Caddy).
• In converter‑token mode, always configure signature verification — avoid CONVERTER_JWT_INSECURE_SKIP_VERIFY=true in production.
• Validate any externally‑supplied formula inputs.
• MCP: set MCP_ALLOWED_HOSTS to your public hostname(s), keep MCP_WRITE_TOOLS_ENABLED=false for read‑only assistants, and give AI clients a dedicated low‑privilege user whose rules.json rules grant only what they need.

25. Query language, validation rules & hardening (v0.4.9)

25.1 Startup config lint (CONFIG_LINT)

Every entity file is linted before the server binds. Errors (invalid identifiers, unknown columns in get.columns / post.columns / order_by / parameters, unknown filter operators, unknown trigger events, details[] pointing at a missing table or column, duplicate tables, state_machine.field not a column) stop the process with exit code 3. Warnings (unknown JSON keys, empty get.parameters, foreign key to a table that is not a route, bare text defaults) are logged only.

25.2 GET query syntax

All of the following work even when get.parameters is empty; filters still require the column to be declared there.

25.3 Column validation rules

Keys live directly on the column object and are checked on POST, PUT, PATCH and detail items. Violations return 400 Invalid field '<col>': <reason>.

{ "name": "status", "type_data": "varchar(20)", "enum": ["DRAFT", "APPROVED"], "message": "Status tidak dikenal" },
{ "name": "qty",    "type_data": "int", "min": 0, "max": 1000 },
{ "name": "code",   "type_data": "varchar(10)", "pattern": "^[A-Z]{3}-[0-9]+$", "min_length": 5, "max_length": 10 },
{ "name": "email",  "type_data": "varchar(100)", "email": true },
{ "name": "site",   "type_data": "text", "url": true }

Related behaviour changes:

• POST: a NOT NULL column with a default, auto_increment or function is no longer required, and absent columns are omitted from the INSERT so database defaults apply. * in post.columns still forces a field.
• PUT/PATCH: JSON null clears a nullable column; on a NOT NULL column it returns 400. An empty form string still means "skip".
• Booleans: JSON true/false (and 1/0, yes/no) bind natively on bool/boolean and as 0/1 on bit/tinyint(1).
• PUT on a missing or soft-deleted id returns 404.

25.4 Field-level authorization (allowed_fields)

rules.json → allows[].allowed_fields is now enforced. For POST/PUT/PATCH any body key outside the list (except id) returns 403. For GET the response objects are trimmed to the list; such responses bypass the shared cache. An empty list means no restriction.

25.5 Optimistic locking

If an entity has an integer column named version, every PUT/PATCH increments it and the UPDATE is guarded with AND version = <current>. Send If-Match: <version> (or version in the body) to assert the version you read; a mismatch or a concurrent write returns 409 Conflict.

25.6 Write queue & cache semantics

• 202 Accepted is returned only after the job is durably LPUSHed to Redis. WRITE_QUEUE_FAST_ACK is deprecated and ignored.
• Workers use BLMOVE into a per-worker processing list and acknowledge after execution; in-flight jobs from a crashed process are re-queued at the next boot. Unparseable payloads go to flx:wq:dlq.
• Read-cache operations against Redis are bounded by REDIS_CACHE_TIMEOUT_MS (default 100 ms) and the connection reconnects automatically.
• L1 cache capacity is a byte budget (L1_CACHE_MAX_BYTES, default 256 MB) and is invalidated per route on writes.
• Exports: EXPORT_LIMIT_DEFAULT, EXPORT_LIMIT_MAX, EXPORT_CONCURRENCY (excess requests get 503 + Retry-After).

25.7 Not implemented (documented for clarity)

• details[].update_strategy values other than "replace".
• action_triggers[].condition.expression (use field/operator/value).
• Row-level security / multi-tenancy, config hot reload, schema migrations beyond CREATE TABLE IF NOT EXISTS, OpenAPI generation, webhooks and scheduled triggers.

26. Contributing & license

Contributing

1. Fork and branch (feat/<name>).
2. Make focused commits; keep example configs valid.
3. Run cargo build / cargo test.
4. Open a PR with a clear description and test notes.

License — see LICENSE (and LICENSE-AGPL).

Credits — Flexurio Engineering Team. Built with Rust + Actix Web.

TL;DR

1. Configure .env (DB_TYPE, URL, SECRET_KEY, ENCRYPT_KEY, LOC_CONFIG).
2. List routes in routes.json; add entity/<route>.json schemas.
3. Run the binary. 4. (Optional) POST /generate/table/<route>. 5. GET /validate/<route>.
4. Log in, then call the REST endpoints with Authorization: Bearer <token>.

Happy building. 🚀