Data Management

Encrypted values: Some API properties are returned as encrypted values (commonly fields prefixed with _, such as _id). Pass these values back to the API unchanged when required by an endpoint. See Encrypted Values below.

Encrypted Values

Infomaxim may return encrypted identifiers for data safety. These are designed to be round-tripped by clients without local decryption.

Common patterns

  • Underscore-prefixed properties such as _id, _parent_id, and similar endpoint-specific properties.
  • Legacy secure token format in some payloads: _[iv:ciphertext].

Usage guidance

  • Treat encrypted values as opaque strings in frontend and middleware code.
  • Do not convert encrypted values to numbers.
  • Do not split on : or remove characters from token values.
  • When filtering with whereConditions, prefer raw numeric IDs when known; otherwise pass the encrypted token unchanged and let the API normalize supported formats.
// response
{
  "meta_title": "Jane Smith",
  "_id": "75ae847bb70c07995712ad659ffd4bca:8b43a60f2d0597c83fa4ace3e81bb563"
}

// request
{
  "table": "aurora_app_users",
  "relatedTable": "aurora_related",
  "relatedTable_id": "_[75ae847bb70c07995712ad659ffd4bca:8b43a60f2d0597c83fa4ace3e81bb563]"
}

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.

    Property name: Use orderBy (camelCase) in request payloads.

    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 | string

    Controls how the related table is joined. Accepts two distinct forms depending on the value type:

    • Numeric ID — joins through the Aurora_Related bridging table and filters linked records to those whose parent or linked ID matches this value. Used for tracked many-to-many relationships.
    • Field name string — performs a direct foreign-key join (LEFT OUTER JOIN [relatedTable] ON [relatedTable].id = [table].[fieldName]), bypassing the bridging table entirely. The value must be a valid SQL identifier (/^[A-Za-z_][A-Za-z0-9_]*$/). A qualified tableName.fieldName form is also accepted when the FK column is on a table other than the primary table. Not available when isReverseRelated is true.

    Examples:

    • Bridging table: relatedTable: "categories", relatedTable_id: 5
    • Direct FK join: relatedTable: "categories", relatedTable_id: "category_id"
    • Qualified FK join: relatedTable: "categories", relatedTable_id: "products.category_id"

    Note: When secure is true, a non-numeric string value is treated as an encrypted ID and decrypted server-side to a numeric ID before the bridging-table join is performed.

  • 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 a direct FK join. relatedTable_id: "category_id" instructs the API to join on [categories].id = [products].category_id, bypassing the Aurora_Related bridging table.

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"
}

POST/getApp

Retrieves frontend app configuration for the resolved application context.

App Resolution Order

  1. site-id request header
  2. Host/domain resolution from request host headers (for example host or x-forwarded-host)

Headers (recommended)

site-id: 42

Success Response (200 OK)

{
  "splash_logo": "/files/splash-logo.png",
  "splash_background": "/files/splash-bg.jpg",
  "logo": "/files/logo.png",
  "logo_app": "/files/logo-app.png",
  "banner": "/files/banner.jpg",
  "background": "#ffffff",
  "colour1": "#111111",
  "colour2": "#222222",
  "stripe_live_public_key": "pk_live_xxx",
  "_id": "encrypted-app-id",
  "_publicKey": "AABBCC"
}

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