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

Related Rows

Table row relationships are managed through a bridging table. This enables much greater flexibility in representing many-to-many relationships between records.

It also makes it possible to track when and who created or modified relationships, and to add a relationship type, along with other boolean relationship states.

Related rows can be queried using the relatedTable and relatedTable_id parameters in the /getDataRows endpoint.

{
    // required fields
    "parent_id": The ID of the parent record in the relationship,
    "parent_table": The name of the parent table,
    "name": The name of the child table,
    "linked": The master_id of the related child record,
    "sibling": boolean,
    
    // managed by the Admin system
    "linked_date": timestamp of when the relationship was created,
    "linked_user": ID of the Admin user who created the relationship,

    // optional fields
    "type": The relationship type ID (if applicable).
    "attr1": Custom boolean attribute field 1,
    "attr2": Custom boolean attribute field 2,
    "attr3": Custom boolean attribute field 3,
    "sort_order": Integer defining the sort order of the relationship
    "display_order": Integer defining the display order of the relationship
    "reltype": The name of the relationship type (if applicable)  
    "roperator": The relationship operator (if applicable)
    "rvalue": The relationship value (if applicable)
  }

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

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 */
    }
  }
}

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
`like`	LIKE	Pattern matching
`not_like`	NOT LIKE	Inverse pattern matching
`in`	IN	Match any value in array
`not_in`	NOT IN	Match none of values in array
`between`	BETWEEN	Range comparison
`is_null`	IS NULL	Null check
`is_not_null`	IS NOT NULL	Not 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