Irongist API Documentation

Base URL

POST /api/{keyId}/{action}

Actions: insert, select, update, delete

Example: POST /api/abc123def456/select

Authentication

X-API-KEY: your_api_key_here

Column Types

Type	Storage Format	Normalization	Example Input
text	Encrypted string	Lowercase, trimmed	"Hello World" → "hello world"
number	Encrypted string	Normalized numeric (removes trailing zeros)	"123.00" → "123"
date	Encrypted string	Y-m-d format	"2024-12-25"
timestamp	Encrypted string	Y-m-d H:i:s format	"2024-12-25 14:30:00"
email	Encrypted string	Lowercase, trimmed	"User@Example.com" → "user@example.com"

System Fields

Every table has these automatic system fields:

• id - Auto-incrementing primary key (number)
• createdAt - Unix timestamp when row was created
• updatedAt - Unix timestamp when row was last updated

Insert

Insert a new record into the table.

Request Example

POST /api/abc123/insert
X-API-KEY: your_api_key

{
    "first_name": "John",
    "last_name": "Doe",
    "email": "john@example.com",
    "age": 30
}

Response Example

{
    "action": "insert",
    "record": {
        "id": 1,
        "createdAt": 1775946157,
        "updatedAt": 1775946157,
        "first_name": "John",
        "last_name": "Doe",
        "email": "john@example.com",
        "age": 30
    }
}

Select

Query records from the table.

Request Parameters

Parameter	Type	Required	Default	Description
select	array	✅ Yes	-	Fields to return (system + custom fields)
where	object	❌ No	{}	Filter conditions
orderBy	object	❌ No	id ASC	Sorting (system fields only)
limit	integer	❌ No	50	Max records to return (max 100)
offset	integer	❌ No	0	Number of records to skip

Basic Examples

Get all records (first 50)

{
    "select": ["id", "first_name", "email", "createdAt"]
}

Get all records with custom limit and offset (pagination)

{
    "select": ["first_name", "email"],
    "limit": 20,
    "offset": 0
}

Page 2 (records 21-40)

{
    "select": ["first_name", "email"],
    "limit": 20,
    "offset": 20
}

With exact match (uses index - fast)

{
    "select": ["first_name", "email"],
    "where": {
        "email": { "eq": "john@example.com" }
    }
}

With multiple conditions (implicit AND)

{
    "select": ["first_name", "email", "age"],
    "where": {
        "email": { "eq": "john@example.com" },
        "age": { "gt": 18 }
    }
}

With IN operator (uses index - fast)

{
    "select": ["first_name", "email"],
    "where": {
        "id": { "in": [1, 2, 3, 4, 5] }
    }
}

With BETWEEN operator (range)

{
    "select": ["first_name", "age"],
    "where": {
        "age": { "between": [18, 65] }
    }
}

With LIKE operator (pattern matching)

{
    "select": ["first_name", "email"],
    "where": {
        "first_name": { "like": "%john%" }
    }
}

Order By Examples

Sort by createdAt descending (newest first)

{
    "select": ["first_name", "email"],
    "orderBy": {
        "createdAt": "DESC"
    },
    "limit": 10
}

Sort by id ascending (oldest first)

{
    "select": ["first_name", "email"],
    "orderBy": {
        "id": "ASC"
    }
}

Multiple sort fields

{
    "select": ["first_name", "email"],
    "orderBy": {
        "createdAt": "DESC",
        "id": "ASC"
    },
    "limit": 20
}

Complex WHERE Examples

OR condition

{
    "select": ["first_name", "email"],
    "where": {
        "OR": [
            { "email": { "eq": "john@example.com" } },
            { "email": { "eq": "jane@example.com" } }
        ]
    }
}

Nested AND/OR

{
    "select": ["first_name", "email", "age"],
    "where": {
        "AND": [
            {
                "OR": [
                    { "first_name": { "like": "%john%" } },
                    { "first_name": { "like": "%jane%" } }
                ]
            },
            { "age": { "gte": 18 } },
            { "status": { "eq": "active" } }
        ]
    },
    "orderBy": { "createdAt": "DESC" },
    "limit": 50,
    "offset": 0
}

Complex pagination example

{
    "select": ["id", "first_name", "email", "createdAt", "updatedAt"],
    "where": {
        "AND": [
            { "status": { "eq": "active" } },
            { "createdAt": { "gte": 1700000000 } }
        ]
    },
    "orderBy": { "createdAt": "DESC" },
    "limit": 25,
    "offset": 50
}

Response Format

{
    "data": [
        {
            "id": 1,
            "createdAt": 1775946157,
            "updatedAt": 1775946157,
            "first_name": "John",
            "email": "john@example.com"
        }
    ],
    "limit": 50,
    "offset": 0
}

Update

Update existing records.

Request Example

POST /api/abc123/update
X-API-KEY: your_api_key

{
    "update": {
        "first_name": "Jonathan",
        "age": 31
    },
    "where": {
        "id": { "eq": 1 }
    }
}

Response Example

{
    "action": "update",
    "affected": 1
}

Update Multiple Records

{
    "update": {
        "status": "inactive"
    },
    "where": {
        "age": { "lt": 18 }
    }
}

Invalid Request (Will Be Rejected)

{
    "update": {
        "status": "inactive"
    }
    // Missing "where" clause - REJECTED
}

Delete

Delete records from the table.

Request Example

POST /api/abc123/delete
X-API-KEY: your_api_key

{
    "where": {
        "id": { "eq": 1 }
    }
}

Response Example

{
    "action": "delete",
    "deleted": 1
}

Delete Multiple Records

{
    "where": {
        "status": { "eq": "inactive" }
    }
}

Invalid Requests (Will Be Rejected)

Missing where clause

{
    // No "where" field - REJECTED
}

Empty where clause

{
    "where": {}  // Empty object - REJECTED
}

WHERE Operators Reference

Operator	Description	Indexed?	Example
eq	Equals	✅ Yes	{"age": {"eq": 25}}
neq	Not equals	❌ No	{"status": {"neq": "deleted"}}
gt	Greater than	❌ No	{"age": {"gt": 18}}
gte	Greater than or equal	❌ No	{"age": {"gte": 18}}
lt	Less than	❌ No	{"age": {"lt": 65}}
lte	Less than or equal	❌ No	{"age": {"lte": 65}}
like	Pattern matching	❌ No	{"name": {"like": "%john%"}}
in	In array	✅ Yes	{"id": {"in": [1,2,3]}}
between	Range	❌ No	{"age": {"between": [18,65]}}

Pagination Guide

Use limit and offset to paginate through results.

Pagination Formulas

• Page 1: {"limit": 20, "offset": 0}
• Page 2: {"limit": 20, "offset": 20}
• Page 3: {"limit": 20, "offset": 40}
• Page N: {"limit": L, "offset": (N-1) * L}

Complete Pagination Example

{
    "select": ["id", "first_name", "email", "createdAt"],
    "where": {
        "status": { "eq": "active" }
    },
    "orderBy": { "createdAt": "DESC" },
    "limit": 20,
    "offset": 40
}

Performance Guide

Fast Operations (Use Index)

• ✅ eq on any field (system or custom)
• ✅ in on any field
• ✅ Queries with only eq and in operators

Slow Operations (Full Scan)

• ⚠️ like - Pattern matching requires scanning all rows
• ⚠️ gt, gte, lt, lte - Range queries on encrypted data
• ⚠️ between - Range queries
• ⚠️ neq - Not equals
• ⚠️ Mixed queries with any slow operator

Best Practices

• Always use limit to restrict result size
• Use indexed operators for WHERE clauses on large tables
• Avoid deep nesting (>3 levels) of AND/OR conditions
• Keep limit reasonable (10-50 for good performance)
• Use specific eq filters before range filters

Limits & Constraints

Constraint	Value	Description
Max limit	100	Maximum records per request
Default limit	50	Default if not specified
Max nesting depth	10	Maximum AND/OR nesting levels
ORDER BY fields	id, createdAt, updatedAt	Only system fields support ordering
Min text length	1	Text fields cannot be empty

Error Responses

Error Format

{
    "error": "Error message description",
    "status": 400
}

Common Error Codes

• 400 - Bad Request (invalid JSON, missing fields, invalid operator)
• 401 - Unauthorized (invalid or missing API key)
• 404 - Not Found (table doesn't exist)
• 500 - Internal Server Error

Example Error Responses

Invalid Field

{
    "error": "Invalid field in select: unknown_field",
    "status": 400
}

Missing Required Fields

{
    "error": "For insert action please make sure all columns are present, missing: first_name",
    "status": 400
}

Invalid Value Type

{
    "error": "Invalid email value for email",
    "status": 400
}

Complete Working Examples

Example 1: User Registration Flow

// 1. Insert new user
POST /api/users/insert
{
    "first_name": "John",
    "last_name": "Doe",
    "email": "john@example.com",
    "age": 30,
    "status": "active"
}

// 2. Query the user
POST /api/users/select
{
    "select": ["first_name", "last_name", "email", "createdAt"],
    "where": {
        "email": { "eq": "john@example.com" }
    }
}

// 3. Update user
POST /api/users/update
{
    "update": {
        "age": 31,
        "status": "inactive"
    },
    "where": {
        "id": { "eq": 1 }
    }
}

// 4. Delete user
POST /api/users/delete
{
    "where": {
        "id": { "eq": 1 }
    }
}

Example 2: Paginated User List

// Get active users, page 1 (records 1-20)
{
    "select": ["id", "first_name", "last_name", "email", "createdAt"],
    "where": {
        "status": { "eq": "active" }
    },
    "orderBy": { "createdAt": "DESC" },
    "limit": 20,
    "offset": 0
}

// Get active users, page 2 (records 21-40)
{
    "select": ["id", "first_name", "last_name", "email", "createdAt"],
    "where": {
        "status": { "eq": "active" }
    },
    "orderBy": { "createdAt": "DESC" },
    "limit": 20,
    "offset": 20
}

Example 3: Search Functionality

{
    "select": ["id", "first_name", "last_name", "email"],
    "where": {
        "OR": [
            { "first_name": { "like": "%john%" } },
            { "last_name": { "like": "%john%" } },
            { "email": { "like": "%john%" } }
        ]
    },
    "limit": 50,
    "offset": 0
}

Example 4: Advanced Filtering with Range

{
    "select": ["id", "first_name", "age", "createdAt"],
    "where": {
        "AND": [
            { "status": { "eq": "active" } },
            { "age": { "between": [25, 40] } },
            { "createdAt": { "gte": 1700000000 } }
        ]
    },
    "orderBy": { "age": "DESC" },
    "limit": 30,
    "offset": 0
}