Data Management

Special Fields

Data returned includes special fields that provide additional context or metadata about the records.

{
    "meta_master_id": The ID column value of the master record,
    "meta_current_id": The ID column value of the current version of the record,
    "meta_status": The row life cycle status (New, Staged, Published, Deleted),  
    "meta_title": The title field value of the record (if applicable),
    "meta_updated": The last updated timestamp of the record,
  }

Data Management

The data management endpoints provide flexible querying, creation, updating, and deletion of records.

POST/getDataRows

Queries database records with advanced filtering, sorting, and pagination.

Request Body

{
  "table": "products",
  "fields": "id, title, price, category, inStock",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "equals",
        "value": "electronics"
      },
      {
        "field": "price",
        "operator": "less_than",
        "value": "1000"
      },
      {
        "field": "inStock",
        "operator": "equals",
        "value": "1"
      }
    ]
  },
  "orderBy": "price DESC",
  "isPaging": true,
  "perPage": 50,
  "page": 1,
  "isLive": true
}

Parameters Reference

  • table string required

    Name of the database table to query. This should match a table name configured in your Infomaxim application schema.

    Example: "products", "users", "orders"

  • fields string

    Comma-separated list of field names to retrieve from the table. Controls which columns are returned in the result set.

    Options:

    • "*" - Returns all fields from the table (default if not specified)
    • "id, name, email" - Returns only specified fields
    • "COUNT(*) as total" - Aggregate functions are supported

    Best Practice: Always specify only the fields you need to reduce payload size and improve performance.

  • whereConditions object

    Structured WHERE clause for filtering records.

    Structure:

    {
  "operator": "AND" | "OR",
  "conditions": [
    {
      "field": "column_name",
      "operator": "equals" | "not_equals" | "greater_than" | "less_than" | 
                  "greater_than_or_equal" | "less_than_or_equal" | 
                  "like" | "not_like" | "in" | "not_in" | "between" | 
                  "is_null" | "is_not_null",
      "value": "search_value" | ["array", "of", "values"]
    }
  ]
}

    Supported Operators:

    • equals, = - Exact match (e.g., status = 'active')
    • not_equals, != - Not equal (e.g., status != 'deleted')
    • greater_than, > - Greater than (e.g., price > 100)
    • less_than, < - Less than (e.g., quantity < 10)
    • greater_than_or_equal, >= - Greater than or equal
    • less_than_or_equal, <= - Less than or equal
    • like - Pattern matching with % wildcard (e.g., "%wireless%")
    • not_like - Inverse pattern matching
    • in - Match any value in array (e.g., ["active", "pending"])
    • not_in - Match none of values in array
    • between - Range between two values (value must be array: [min, max])
    • is_null - Field is NULL (no value needed)
    • is_not_null - Field is not NULL (no value needed)

    See the WHERE Clause section for detailed examples.

  • orderBy string

    Specifies the field(s) to sort results by and the sort direction.

    Format: "field_name [ASC|DESC]"

    Examples:

    • "price DESC" - Sort by price, highest first
    • "createdAt ASC" - Sort by creation date, oldest first
    • "lastName ASC, firstName ASC" - Multi-field sorting
    • "RAND()" - Random order (for SQL Server: "NEWID()")

    Default: If not specified, results are returned in the order they appear in the database (typically by primary key).

  • isPaging boolean

    Enables pagination of results. When set to true, limits the number of results returned and provides pagination metadata.

    Values:

    • true - Enable pagination (use with page and perPage)
    • false - Disable pagination, return all matching records (default)

    Important: For large datasets, always use pagination to avoid performance issues and timeout errors.

  • perPage number

    Number of records to return per page when pagination is enabled.

    Range: 1 to 100

    Default: 50

    Recommendation: Use smaller values (10-20) for mobile devices, larger values (50-100) for desktop applications to balance performance and user experience.

  • page number

    The page number to retrieve when pagination is enabled. Pages are 1-indexed (first page is 1, not 0).

    Default: 1 (first page)

    Example: With perPage: 50, page 1 returns records 1-50, page 2 returns records 51-100, etc.

  • distinct boolean

    Returns only unique/distinct records, eliminating duplicates.

    Values:

    • true - Return only distinct records
    • false - Allow duplicate records (default)

    Example: Get unique categories: fields: "category", distinct: true

  • record boolean

    Returns a single object instead of an array when set to true. Useful when you expect exactly one result.

    Values:

    • true - Return single object (first record only)
    • false - Return array of objects (default)

    Example: Get single product by ID: whereConditions: {...}, record: true

  • rows number

    Maximum number of rows to return from the query. Different from pagination - this limits the total result set.

    Default: -1 (no limit)

    Example: Get top 10 records: rows: 10, orderBy: "price DESC"

  • relatedTable string

    Name of a related table to join with the main table. Used for fetching related data in a single query.

    Example: Get products with their categories: table: "products", relatedTable: "categories"

    Note: Works in conjunction with relatedTable_id parameter.

  • relatedTable_id number or string

    ID of the related record or the foreign key field name for relationship joins.

    Example: relatedTable: "categories", relatedTable_id: 5

    Note: Can accept encrypted IDs when using secure mode.

  • isReverseRelated boolean

    Reverses the direction of the relationship join.

    Values:

    • true - Reverse the join direction
    • false - Normal join direction (default)
  • secureId string

    Encrypted record ID for secure single-record retrieval. When provided, automatically adds a WHERE clause for this ID.

    Example: table: "products", secureId: "encrypted_id_string"

    Note: The ID is decrypted server-side and used safely in the query.

  • isLive boolean

    Returns only live/published records from versioned tables.

    Values:

    • true - Return only published/live data
    • false - Return all versions (default)

    Note: Use this for production data queries to ensure only published content is shown.

  • isPublished boolean

    Alias for isLive. Returns only published records.

  • isStage boolean

    Returns staging/preview versions of records instead of published versions.

    Values:

    • true - Return staging data
    • false - Return published data (default)
  • isAppGrid boolean

    Returns raw data directly from the table, bypassing the versioning system entirely.

    Values:

    • true - Return raw, unversioned data
    • false - Use versioning system (default)
  • fullText boolean

    Enables full-text search mode using SQL Server's full-text search capabilities.

    Values:

    • true - Enable full-text search (use with s_keyword)
    • false - Standard search (default)
  • s_keyword string

    Search keyword(s) for full-text search. Used when fullText is enabled.

    Example: s_keyword: "wireless headphones", fullText: true

    Note: Automatically filters out common noise words and uses SQL Server full-text search.

  • debugOn boolean

    Includes the generated SQL query and parameters in the response for debugging purposes.

    Values:

    • true - Include debug information in response
    • false - Normal response (default)

    Warning: Only use in development environments. Do not enable in production as it exposes query structure.

Advanced Query Examples

Example 1: Complex Filtering with Multiple Conditions
{
  "table": "products",
  "fields": "id, name, price, category, inStock",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "in",
        "value": ["electronics", "computers"]
      },
      {
        "field": "price",
        "operator": "between",
        "value": ["100", "1000"]
      },
      {
        "field": "inStock",
        "operator": "greater_than",
        "value": "0"
      },
      {
        "field": "name",
        "operator": "like",
        "value": "%wireless%"
      }
    ]
  },
  "orderBy": "price ASC",
  "isPaging": true,
  "perPage": 25,
  "page": 1
}

Returns products in electronics or computers categories, priced between $100-$1000, currently in stock, with "wireless" in the name.

Example 2: Full-Text Search Query
{
  "table": "products",
  "fields": "id, title, price, description",
  "s_keyword": "wireless bluetooth headphones",
  "fullText": true,
  "orderBy": "rank DESC",
  "isPaging": true,
  "perPage": 50
}

Performs a full-text search for products containing the keywords, ranked by relevance.

Example 3: Related Table Query
{
  "table": "products",
  "fields": "products.id, products.title, products.price, categories.name as categoryName",
  "relatedTable": "categories",
  "relatedTable_id": "category_id",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "products.status",
        "operator": "equals",
        "value": "active"
      }
    ]
  },
  "isLive": true,
  "orderBy": "products.title ASC"
}

Returns active products with their category information using the relationship system.

Example 4: Get Single Record
{
  "table": "products",
  "fields": "id, title, price, description, images",
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "id",
        "operator": "equals",
        "value": "123"
      }
    ]
  },
  "record": true,
  "isLive": true
}

Returns a single product object (not an array) for the specified ID.

Example 5: Get Distinct Values
{
  "table": "products",
  "fields": "category",
  "distinct": true,
  "orderBy": "category ASC",
  "isLive": true
}

Returns a list of all unique product categories, sorted alphabetically.

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "rows": [
      {
        "id": 101,
        "title": "Wireless Headphones",
        "price": 299.99,
        "category": "electronics",
        "inStock": 1
      },
      {
        "id": 102,
        "title": "Smart Watch",
        "price": 399.99,
        "category": "electronics",
        "inStock": 1
      }
    ],
    "pagination": {
      "total": 45,
      "page": 1,
      "perPage": 50,
      "totalPages": 1
    }
  }
}

Example Usage

// JavaScript Example
const response = await fetch('http://localhost:3001/getDataRows', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    table: 'products',
    fields: 'id, title, price',
    whereConditions: {
      operator: 'AND',
      conditions: [
        { field: 'category', operator: 'equals', value: 'electronics' },
        { field: 'price', operator: 'less_than', value: '1000' }
      ]
    },
    orderBy: 'price DESC',
    isPaging: true,
    perPage: 50,
    page: 1,
    isLive: true
  })
});

const data = await response.json();
console.log(data.data.rows);

POST/saveRow

Creates a new record or updates an existing record in the database.

Create New Record

{
  "table": "products",
  "data": {
    "title": "New Product",
    "price": 499.99,
    "category": "electronics",
    "description": "A brand new product",
    "inStock": 1
  }
}

Update Existing Record

{
  "table": "products",
  "key": 101,
  "keyfield": "id",
  "data": {
    "title": "Updated Product Name",
    "price": 449.99,
    "inStock": 0
  }
}

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "id": 103,
    "message": "Record saved successfully"
  }
}

Saving with Binary Attachments

The /saveRow endpoint supports saving binary attachments (images) directly within the data payload. When a data property contains a base64-encoded data URL, the system automatically:

  • Parses and extracts the image data from the base64 string
  • Saves the file to the asset storage system
  • Creates a corresponding entry in the aurora_assets table
  • Replaces the property value with the new asset filename
Supported Format

Binary attachments must be provided as base64-encoded data URLs in the following format:

data:image/<type>;base64,<base64-encoded-data>

Supported image types: png, jpeg, jpg, gif, webp, svg+xml

Request Example with Binary Attachment
{
  "table": "products",
  "data": {
    "title": "Product with Image",
    "price": 299.99,
    "category": "electronics",
    "product_image": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg=="
  }
}
JavaScript Example
// Convert file to base64 data URL
async function fileToBase64(file) {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();
    reader.readAsDataURL(file);
    reader.onload = () => resolve(reader.result);
    reader.onerror = error => reject(error);
  });
}

// Save record with binary attachment
async function saveProductWithImage(fileInput) {
  const base64Image = await fileToBase64(fileInput.files[0]);
  
  const response = await fetch('http://localhost:3001/saveRow', {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + accessToken,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      table: 'products',
      data: {
        title: 'Product with Image',
        price: 299.99,
        product_image: base64Image  // Base64 data URL
      }
    })
  });
  
  const result = await response.json();
  console.log('Saved with asset:', result);
}
Response Example

After successful save, the response includes the new record ID. The binary attachment field is automatically converted to the asset filename:

{
  "status": "Success",
  "id": 104,
  "data": {
    "id": 104
  }
}

The product_image field in the database will contain the new asset filename (e.g., "104_product_image_20231215123456.png") instead of the base64 data.

Important Notes
  • Image Detection: Only properties with values starting with data:image are processed as binary attachments. Other data URL types (e.g., data:application/pdf) are stored as-is without processing. Use the /files/upload endpoint for non-image files.
  • Automatic Asset Creation: Each binary attachment creates a new record in the aurora_assets table with versioning support.
  • File Naming: Generated filenames follow the pattern: <assetId>_<fieldName>_<timestamp>.<extension>. For example, if you save a PNG image to a field named product_image, the resulting filename might be 104_product_image_202312151234567890.png, where 104 is the asset ID and the timestamp is automatically generated.
  • Multiple Attachments: You can include multiple binary attachment fields in a single save request. Each will be processed and stored independently.
  • Size Considerations: Base64 encoding increases file size by approximately 33%. For files larger than 5MB, consider using the dedicated /files/upload endpoint instead, as large base64 payloads can significantly increase request size and processing time.
Alternative: Using File Upload Endpoint

For larger files or when you need more control over the upload process, use the dedicated /files/upload endpoint. After uploading, reference the returned filename in your /saveRow request:

// Step 1: Upload file using /files/upload endpoint
const formData = new FormData();
formData.append('file', fileInput.files[0]);

const uploadResponse = await fetch('http://localhost:3001/files/upload', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'x-file-id': 'unique_id_123',
    'name': fileInput.files[0].name,
    'size': fileInput.files[0].size.toString(),
    'app-id': '1'
  },
  body: formData
});

const uploadResult = await uploadResponse.json();

// Step 2: Save record with file reference
const saveResponse = await fetch('http://localhost:3001/saveRow', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    table: 'products',
    data: {
      title: 'Product with Uploaded Image',
      price: 299.99,
      product_image: uploadResult.data.filename  // Reference the uploaded file
    }
  })
});

POST/deleteRow

Deletes a record from the database.

Request Body

{
  "table": "products",
  "id": 103
}

Success Response (200 OK)

{
  "status": "Success",
  "message": "Record deleted successfully"
}

POSTgetApp/:domain

Retrieves application configuration by domain name.

URL Parameters

  • domain string required

    Domain name of the application (e.g., "example.com").

Success Response (200 OK)

{
  "status": "Success",
  "data": {
    "appID": 1,
    "name": "My Application",
    "domain": "example.com",
    "settings": {
      /* Application-specific settings */
    }
  }
}

POST/initFolders

Initializes the folder structure for a specific table. This endpoint creates a root folder if one doesn't exist and associates all existing records with that root folder. This is typically called when enabling folder organization for a table for the first time.

Authentication

Required - JWT Bearer token authentication

Authorization: Bearer <access_token>

Request Body

{
  "table": "products"
}

Request Body Parameters

  • table string required

    The name of the database table to initialize folder organization for. This should be the table_database_name as configured in the aurora_tables system table.

    Example: "products", "articles", "events"

Business Logic

  • Authentication: Validates JWT token and retrieves user context
  • Root Check: Checks if a root folder already exists for the specified table in aurora_folders
  • Root Creation: If no root folder exists, creates one with title 'Root' and parent_id of 0
  • Record Association: Retrieves all existing records from the specified table and creates relationship entries in aurora_related linking each record to the root folder
  • Folder Table: Uses 'aurora_folders' as the folder relationship table

When to Use

  • When enabling folder organization for an existing table with existing data
  • As part of an application setup or migration process
  • When the folder structure needs to be reset to a single root folder

Success Response (200 OK)

// Returns the root folder ID (numeric)
123

The response is the ID of the root folder. If a root folder already exists, it returns the existing root folder ID. If one was created, it returns the new root folder ID.

Example Usage

// JavaScript Example - Initialize folders for a products table
const response = await fetch('http://localhost:3001/initFolders', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    table: 'products'
  })
});

const rootFolderId = await response.json();
console.log('Root folder ID:', rootFolderId);

// Now you can use this root folder ID to:
// 1. Query items in the root folder using getrelated
// 2. Create subfolders by inserting into aurora_folders with parent_id = rootFolderId
// 3. Move items to subfolders using pasteItemToFolder

Related Tables

  • aurora_folders: Stores the folder hierarchy with fields: id, title, parent_id, table_name
  • aurora_related: Stores the relationships between records and folders
  • aurora_tables: Contains table metadata including whether the table supports live/staged versioning

POST/counter

Atomically increments or decrements a numeric field in a database record. This endpoint is useful for counters like view counts, stock quantities, or any numeric value that needs atomic updates.

Authentication

No authentication required for basic usage. Table-level permissions are checked if configured.

Request Body

{
  "data": {
    "table": "products",
    "field": "view_count",
    "id": 101,
    "value": 1
  }
}

Request Body Parameters

  • data object required

    Object containing the counter operation details.

  • data.table string required

    The database table containing the field to update.

    Example: "products", "articles", "events"

  • data.field string required

    The name of the numeric field to increment/decrement. Must be alphanumeric with underscores only (validated server-side).

    Example: "view_count", "stock_quantity", "likes"

  • data.id number | string required

    The ID of the record to update. Can be prefixed with underscore (_id) for encrypted IDs.

  • data.value number required

    The value to add to the field. Use positive numbers to increment, negative numbers to decrement.

    Examples:

    • 1 - Increment by 1
    • -1 - Decrement by 1
    • 5 - Increment by 5
    • -10 - Decrement by 10

Business Logic

  • Property Decryption: Any property prefixed with underscore (_) is automatically decrypted
  • Permission Check: Validates table-level write permissions if configured
  • Field Validation: Validates field name matches pattern /^[a-zA-Z_][a-zA-Z0-9_]*$/ to prevent SQL injection
  • Value Validation: Ensures value is a valid number
  • Atomic Update: Uses a single SQL statement to atomically read and update the value, preventing race conditions

Security Features

  • Field Name Validation: Only alphanumeric characters and underscores allowed
  • Parameterized Query: All values are passed as SQL parameters
  • Table Permissions: Respects table-level write permissions
  • Numeric Validation: Value must be a finite number

Success Response (200 OK)

{
  "rowsAffected": [1]
}

Error Responses

// 400 Bad Request - Invalid field name
{
  "status": "Error",
  "message": "Invalid field name"
}

// 400 Bad Request - Invalid value
{
  "status": "Error",
  "message": "Value must be numeric"
}

// 403 Forbidden - Permission denied
{
  "status": "Error",
  "message": "Write permission denied for table"
}

Common Use Cases

Use Case 1: Increment View Counter
{
  "data": {
    "table": "articles",
    "field": "view_count",
    "id": 50,
    "value": 1
  }
}

Increments the view_count field for article 50 by 1.

Use Case 2: Decrease Stock Quantity
{
  "data": {
    "table": "products",
    "field": "stock_quantity",
    "id": 101,
    "value": -1
  }
}

Decrements the stock_quantity field for product 101 by 1 (e.g., after a purchase).

Use Case 3: Add Multiple Likes
{
  "data": {
    "table": "posts",
    "field": "like_count",
    "id": 200,
    "value": 5
  }
}

Increments the like_count field for post 200 by 5.

Example Usage

// JavaScript Example - Track page view
const response = await fetch('http://localhost:3001/counter', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    data: {
      table: 'articles',
      field: 'view_count',
      id: 50,
      value: 1
    }
  })
});

const result = await response.json();
if (result.rowsAffected[0] > 0) {
  console.log('Counter updated successfully');
}

// Decrement stock after purchase
const stockResponse = await fetch('http://localhost:3001/counter', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    data: {
      table: 'products',
      field: 'stock_quantity',
      id: 101,
      value: -2  // Purchased 2 items
    }
  })
});

Important Notes

  • Atomicity: The update is atomic, preventing race conditions when multiple users update the same counter simultaneously
  • No Bounds Checking: The counter can go negative. If you need to prevent negative values, implement validation in your application logic
  • Field Must Exist: The field must already exist in the table and contain a numeric value (or NULL, which is treated as 0)

POST/pasteItemToFolder

Moves or copies a record to a different folder. This endpoint supports both "cut" (move) and "copy" operations for organizing records within the folder hierarchy.

Authentication

Required - JWT Bearer token authentication

Authorization: Bearer <access_token>

Request Body

{
  "action": "cut",
  "table": "products",
  "id": 101,
  "folder_table": "aurora_folders",
  "from_id": 12,
  "to_id": 15,
  "reltype": ""
}

Request Body Parameters

  • action string required

    The type of paste operation to perform.

    Values:

    • "cut" - Moves the item from the source folder to the destination folder. The item exists in only one folder after the operation.
    • "copy" - Creates a duplicate of the item in the destination folder. The original item remains in the source folder.
  • table string required

    The database table name of the item being moved/copied.

    Example: "products", "articles", "events"

  • id number required

    The current_id of the record being moved/copied. For versioned tables, this is the current version ID.

  • folder_table string required

    The table that stores folder records. Typically "aurora_folders".

  • from_id number required

    The ID of the source folder (where the item is currently located). For items at root level, this is the root folder ID.

  • to_id number required

    The ID of the destination folder (where the item should be placed).

  • reltype string

    The relationship type for the folder relationship. Usually empty string for standard folder organization.

    Default: ""

Business Logic - Cut Operation

  1. Authentication: Validates JWT token and retrieves user context
  2. Version Check: Determines if the table uses versioning (live vs staged content)
  3. Master ID Resolution: For versioned tables, retrieves the master_id from aurora_data; for live tables, uses the provided id directly
  4. Existing Relationship Check: Checks if a relationship exists from the source folder
  5. Update or Insert:
    • If relationship exists: Updates the linked folder ID from from_id to to_id
    • If no relationship exists (e.g., moving from root): Creates a new relationship to the destination folder

Business Logic - Copy Operation

  1. Authentication: Validates JWT token and retrieves user context
  2. Record Duplication: Creates a complete copy of the record in the source table using the engine.copyRecord function
  3. Version Check: Determines if the table uses versioning
  4. New Relationship: Creates a relationship between the new record copy and the destination folder
  5. Returns New ID: The response includes the ID of the newly created copy

Success Response (200 OK)

// Cut operation
{
  "status": "Success",
  "res": {
    "rowsAffected": [1]
  }
}

// Copy operation
{
  "id": 150,
  "status": "Success"
}

For copy operations, the id field contains the ID of the newly created copy.

Common Use Cases

Use Case 1: Move Product to Different Category Folder
{
  "action": "cut",
  "table": "products",
  "id": 101,
  "folder_table": "aurora_folders",
  "from_id": 12,
  "to_id": 15,
  "reltype": ""
}

Moves product 101 from folder 12 to folder 15.

Use Case 2: Copy Article to Another Section
{
  "action": "copy",
  "table": "articles",
  "id": 50,
  "folder_table": "aurora_folders",
  "from_id": 10,
  "to_id": 20,
  "reltype": ""
}

Creates a copy of article 50 in folder 20. The original remains in folder 10.

Use Case 3: Move Item from Root to Subfolder
{
  "action": "cut",
  "table": "events",
  "id": 200,
  "folder_table": "aurora_folders",
  "from_id": 1,
  "to_id": 25,
  "reltype": ""
}

Moves event 200 from root folder (ID 1) to subfolder 25.

Example Usage

// JavaScript Example - Move item to folder
const moveResponse = await fetch('http://localhost:3001/pasteItemToFolder', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    action: 'cut',
    table: 'products',
    id: 101,
    folder_table: 'aurora_folders',
    from_id: 12,
    to_id: 15,
    reltype: ''
  })
});

const moveResult = await moveResponse.json();
if (moveResult.status === 'Success') {
  console.log('Item moved successfully');
}

// JavaScript Example - Copy item to folder
const copyResponse = await fetch('http://localhost:3001/pasteItemToFolder', {
  method: 'POST',
  headers: {
    'Authorization': 'Bearer ' + accessToken,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    action: 'copy',
    table: 'products',
    id: 101,
    folder_table: 'aurora_folders',
    from_id: 12,
    to_id: 15,
    reltype: ''
  })
});

const copyResult = await copyResponse.json();
if (copyResult.status === 'Success') {
  console.log('Item copied successfully. New ID:', copyResult.id);
}

Important Notes

  • Versioned Tables: For versioned tables, the operation uses the master_id for relationships. The master_id is retrieved from the aurora_data table based on the current_id.
  • Live Tables: For non-versioned (live) tables, the provided id is used directly.
  • Copy Creates Full Duplicate: The copy operation creates a complete duplicate of the record, including all fields. The new record has a new ID and new version history if applicable.
  • Root Folder Handling: If an item was previously at the root level (no folder relationship), moving it creates a new relationship rather than updating an existing one.
  • Folder Hierarchy: This operation only handles the relationship between items and folders, not the folder hierarchy itself. Use standard saveRow operations to create/modify folders.

Related Endpoints

  • /initFolders: Initialize folder structure for a table
  • /addrelated: Manually create folder relationships
  • /remrelated: Remove folder relationships (unfile an item)
  • /getrelated: Check which folder an item belongs to

Secure WHERE Clause

The Secure WHERE Clause system provides SQL injection-safe filtering for data queries using a structured JSON format.

Structure

{
  "whereConditions": {
    "operator": "AND|OR",
    "conditions": [
      {
        "field": "column_name",
        "operator": "equals|not_equals|greater_than|less_than|greater_than_or_equal|less_than_or_equal|like|not_like|in|not_in|between|is_null|is_not_null",
        "value": "search_value"
      }
    ]
  }
}

Supported Operators

Operator SQL Equivalent Description
equals, ==Exact match
not_equals, !=!=Not equal
greater_than, >>Greater than
less_than, <<Less than
greater_than_or_equal, >=>=Greater than or equal
less_than_or_equal, <=<=Less than or equal
likeLIKEPattern matching
not_likeNOT LIKEInverse pattern matching
inINMatch any value in array
not_inNOT INMatch none of values in array
betweenBETWEENRange comparison
is_nullIS NULLNull check
is_not_nullIS NOT NULLNot null check

Examples

Simple Equality

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "status",
        "operator": "equals",
        "value": "active"
      }
    ]
  }
}

Multiple Conditions with AND

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "equals",
        "value": "electronics"
      },
      {
        "field": "price",
        "operator": "less_than",
        "value": "500"
      },
      {
        "field": "inStock",
        "operator": "equals",
        "value": "1"
      }
    ]
  }
}

IN Operator with Array

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "category",
        "operator": "in",
        "value": ["electronics", "computers", "phones"]
      }
    ]
  }
}

LIKE Pattern Matching

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "title",
        "operator": "like",
        "value": "%wireless%"
      }
    ]
  }
}

BETWEEN Range

{
  "whereConditions": {
    "operator": "AND",
    "conditions": [
      {
        "field": "price",
        "operator": "between",
        "value": ["100", "500"]
      }
    ]
  }
}

Security Features

  • Field Name Validation: Field names are validated against a safe pattern to prevent SQL injection
  • Operator Whitelist: Only predefined, safe operators are allowed
  • Parameterized Queries: All values are passed as SQL parameters, never concatenated
  • Type Validation: Values are validated and sanitized based on the operator