# Relational Database
The `Relational Database` class provides a consistent way to interact with SQL databases (PostgreSQL, MySQL, SQLite) without writing raw SQL.
### Quick start: The internal PostgreSQL instance
Heisenware provides a pre-initialized instance called `internal-postgres`. It is globally available and ready for use. Simply select `internal-postgres` in your function's instance field to start creating tables and managing data.
### Connecting an external database
To connect to your own external database, you must use the `create` function. How you configure this depends on where your database is located:
* **Cloud/public database**: If your database is accessible over the internet, you can create the instance directly in your cloud environment.
* **Local database (via Agent)**: If your database is inside a private network (e.g., on a shopfloor server), you must first deploy an [Edge Agent](/app-builder/build-backend/function-explorer/agents.md) in that network. You then create the database instance *within* that Agent to establish a secure bridge.
{% hint style="info" %}
**One set of functions**: Whether you are using the internal database or an external connection, the functions for querying, inserting, and managing data are identical.
{% endhint %}
## Connection & Database Management
### **create**
Initializes the database connection. This is the first step for any external database.
{% hint style="success" %}
Skip this step for the `internal-postgres`. It is already instantiated for you.
{% endhint %}
Parameters
* `options`: An object for configuring the database connection.
* `dialect`: The database dialect (e.g., `'postgres'`, `'mysql'`).
* `database`: The name of the database.
* `username`: The username for authentication.
* `password`: The password for authentication.
* `host`: The hostname or IP address of the database server.
* `port`: The port number.
* `ssl`: Whether to use SSL. Defaults to `true` for some dialects.
Example
```yaml
# options
dialect: 'postgres'
database: 'mydb'
username: 'user'
password: 'pass'
host: 'localhost'
ssl: true
```
### **isConnected**
Checks if the database connection is currently active. Returns `true` if connected, `false` otherwise.
Output
Returns `true` if connected, `false` otherwise.
### **getAllTables**
Retrieves a list of all tables that currently exist in the database.
Output An array of table name strings.
### **reset**
{% hint style="danger" %}
DANGER: This function drops and recreates the entire database, deleting ALL tables and data. Use with extreme caution.
{% endhint %}
Output
Returns `true` if the reset was successful.
## Schema & Table Definition
### **defineTable**
Defines a table's schema. If the table doesn't exist, it's created. If it exists, this function will attempt to alter it by adding any new fields.
When creating a table, several fields are automatically added unless you explicitly define an `id` field yourself:
* `id`: A unique `UUID` that serves as the table's primary key.
* `createdAt`: A `date` timestamp recording when the row was created.
* `updatedAt`: A `date` timestamp tracking the last time the row was modified.
Parameters
* `name`: The name of the table (e.g., `'users'`).
* `fields`: An object defining the table's columns.
* Keys are the field names (must be in camelCase).
* Values can be a simple string for the data type or an object for more options.
* Supported data types: `string`, `text`, `integer`, `number`, `boolean`, `date`, `json`, `jsonb`, `file`, `uniquestring`, `uniqueinteger`.
* `options`: An optional object.
* `trackHistory`: If `true`, a separate history table is created to log all changes to records in this table.
{% hint style="info" %}
As a best practice, use English and camelCase for naming tables and fields (e.g., firstName, dateOfBirth). Avoid **spaces**, **dashes**, and other special characters.
{% endhint %}
{% hint style="info" %}
When using PostgreSQL, we highly recommend the jsonb datatype for JSON data. It's more efficient and allows nested properties to be used in filtering expressions.
{% endhint %}
Example 1: Simple table
```yaml
# name
users
# fields
name: string
email: uniquestring
age: integer
```
Example 2: Table with custom primary key and JSONB
```yaml
# name
products
# fields
id: { type: string, primaryKey: true }
name: string
price: number
specs: jsonb
```
{% hint style="warning" %}
Always use id for the name of the primary key, even when overriding the default. Other names may cause unexpected behavior.
{% endhint %}
#### Advanced Field Configuration
For more control, you can provide an object for a field's value with the following properties:
* `type`: The data type string (e.g., `'string'`, `'integer'`). Required.
* `primaryKey` (boolean): Sets this field as the primary key, overriding the default `id` field.
* `unique` (boolean): Ensures all values in this column are unique. Use `'uniquestring'` or `'uniqueinteger'` as a shorthand.
* `allowNull` (boolean): If `false`, this field must have a value.
* `defaultValue`: Sets a default value if none is provided. Can be a literal (`'active'`, `0`) or a special value (like `'NOW'` for the current time).
* `autoIncrement` (boolean): Automatically increments an integer primary key for each new row.
Example 1: Advanced table with constraints
```yaml
# name
employees
# fields
employeeId: { type: integer, primaryKey: true, autoIncrement: true }
email: { type: string, allowNull: false, unique: true }
status: { type: string, defaultValue: active }
hireDate: { type: date, defaultValue: NOW }
```
Example 2: Unique constraint across multiple columns
To make a combination of fields unique, define an arbitrary string (here: `timeAndId`) that is assigned to the corresponding fields.
```yaml
# name
machineHistory
# fields
machineId: { unique: timeAndId, type: string }
timestamp: { unique: timeAndId, type: date }
data: jsonb
```
### **getTableSchema**
Retrieves the schema definition for a given table.
Parameters
* `name`: The name of the table.
### **deleteTable**
Deletes an entire table and all its data.
Parameters
* `name`: The name of the table to delete.
## Querying and Filtering Data
### **getTableData**
Fetches rows from one or more tables, with powerful options for filtering, joining, sorting, and selecting specific fields. This is the primary function for reading data.
#### Parameters
* `name`: The name of the table (`string`) or an array of table names for a multi-table JOIN query.
* `options`: An optional object to refine the query.
* `filter`: An array defining the conditions rows must meet. For multi-table queries, this must include the JOIN conditions. See "Filtering Explained" below.
* `fields`: An array of strings to select specific columns. For multi-table queries, use dot notation (e.g., `'users.name'`).
* `order`: An array specifying the sort order: `['fieldName', 'DIRECTION']`, where `DIRECTION` is `'ASC'` or `'DESC'`.
* `limit`: The maximum number of rows to return.
* `offset`: The number of rows to skip. Useful for pagination.
* `autoJoin` (boolean): For single-table queries, automatically includes data from related tables. Defaults to `true`.
* `locale`: A locale string (e.g., `'en-US'`) to format date/time values in the output.
* `dateStyle` / `timeStyle`: Controls the verbosity of formatted dates (`'full'`, `'long'`, `'medium'`, `'short'`, `'hidden'`).
#### Filtering Explained
The `filter` option uses a special array syntax to build precise queries.
**Simple Conditions**
A simple condition is an array of three elements: `[fieldName, operator, value]`.
* `fieldName`: The name of the column. For `jsonb` fields, use dot notation to access nested keys (e.g., `'specs.dimensions.width'`). For multi-table queries, always prefix with the table name (e.g., `'users.name'`).
* `operator`: A string for the comparison (see table below).
* `value`: The value to compare against.
**Compound Conditions**
Combine conditions using `'and'` or `'or'`:
* AND: `[ [condition1], 'and', [condition2] ]` - Both must be true.
* OR: `[ [condition1], 'or', [condition2] ]` - At least one must be true.
**Available Operators**
| Operator(s) | Description | Example Value |
| ------------------- | -------------------------------------------------- | -------------------------- |
| `=`, `eq`, `is` | Equals | `'John'` or `100` |
| `<>`, `ne`, `isnot` | Not equals | `'John'` or `100` |
| `>`, `gt` | Greater than | `99` |
| `>=`, `gte` | Greater than or equal to | `100` |
| `<`, `lt` | Less than | `100` |
| `<=`, `lte` | Less than or equal to | `100` |
| `contains` | String field contains the value (case-insensitive) | `'oh'` (matches 'John') |
| `notcontains` | String field does not contain the value | `'Peter'` |
| `startswith` | String field starts with the value | `'J'` |
| `endswith` | String field ends with the value | `'oe'` (matches 'Doe') |
| `between` | Value is between two values in an array | `[18, 30]` or `['A', 'D']` |
| `in` | Value is one of several possibilities in an array | `['active', 'pending']` |
#### Query Examples
**Example 1: Simple Filter and Field Selection**
*Goal: Get the `name` and `email` for all active users.*
```yaml
# name
users
# options
filter: ['status', '=', 'active']
fields: ['name', 'email']
```
**Example 2: Date Range Filter**
*Goal: Find all orders placed in January 2025.*
```yaml
# name
orders
# options
filter: ['createdAt', 'between', ['2025-01-01', '2025-01-31T23:59:59Z']]
```
**Example 3: Compound 'AND' Filter**
*Goal: Find products that are in stock (`quantity > 0`) and cost more than 50.*
```yaml
# name
products
# options
filter: [ ['quantity', '>', 0], 'and', ['price', '>', 50] ]
```
**Example 4: Sorting and Limiting**
*Goal: Get the 5 most recent high-priority (`'high'` or `'critical'`) tickets.*
```yaml
# name
tickets
# options
filter: ['priority', 'in', ['high', 'critical']]
order: ['createdAt', 'DESC']
limit: 5
```
**Example 5: Multi-Table JOIN**
*Goal: Retrieve the names of users and the titles of the posts they have created.*
```yaml
# name
- users
- posts
# options
# The first filter condition defines the JOIN: users.id must equal posts.userId
filter: [ ['users.id', '=', 'posts.userId'] ]
fields: ['users.name', 'posts.title']
```
**Example 6: JOIN with a `WHERE` Clause**
*Goal: Retrieve the post titles for a specific user named 'Alice'.*
```yaml
# name
- users
- posts
# options
filter: [
['users.id', '=', 'posts.userId'], # JOIN condition
'and',
['users.name', '=', 'Alice'] # WHERE condition
]
fields: ['posts.title']
```
**Example 7: JOIN with Nested `JSONB` Filter**
*Goal: Find all orders for 'Alice' where the shipment was marked as high priority in its `details` JSON field.*
```yaml
# name
- users
- orders
- shipments
# options
fields: ['users.name', 'orders.product', 'shipments.trackingNumber']
filter: [
['users.id', '=', 'orders.userId'], # Join 1
'and',
['orders.shipmentId', '=', 'shipments.id'], # Join 2
'and',
['users.name', '=', 'Alice'], # Where 1
'and',
['shipments.details.priority', '=', true] # Where 2 (JSON)
]
```
Output
An array of objects, where each object represents a row that matches the specified criteria.
### **findRow**
Finds and returns the first row that matches the provided filter. Returns `null` if no match is found.
Parameters
* `table`: The name of the table.
* `options`: An object containing a `filter` and optional `fields`.
## Referencing the Current User with the `$USER` Variable
The `$USER` variable enables referencing the current logged-in user in your app, particularly useful for apps requiring user authentication. A recommended practice involves defining the username as a unique key using the data type `uniqueString` when creating the table. This configuration enables utilizing the [`upsertRow`](#inserting-or-updating-a-row) function, capable of both updating and inserting rows based on the existence of the unique key.
$USER in combination with upsertRow function
## Data Manipulation (CRUD)
### **addRow**
Adds a single new row to a table.
Parameters
* `table`: The name of the table.
* `data`: An object where keys are column names and values are the data to insert.
Example
```yaml
# table
users
# data
name: Jane Doe
email: jane.doe@example.com
age: 34
```
### **addRows**
Adds multiple rows to a table in a single, efficient bulk operation.
Parameters
* `table`: The name of the table.
* `data`: An array of data objects to insert.
Example
```yaml
# table
products
# data
- name: 'Thingamajig'
price: 19.99
stock: 100
- name: 'Widget'
price: 25.50
stock: 250
```
### **upsertRow**
Atomically "updates or inserts" a row. It checks for a row's existence and either updates it or creates a new one. By default, it checks using the primary key (`id`). The optional `uniqueKey` parameter lets you use another business key (like an email) for the check.
Parameters
* `table`: The name of the table.
* `data`: The data object to upsert.
* `uniqueKey`: (Optional) An object specifying a unique key for collision detection.
Example 1: Upsert using default primary key (id)
Goal: Update the user with a specific ID, or create them if they don't exist.
```yaml
# table
users
# data
id: 'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
name: Jane Smith
age: 36
```
Example 2: Upsert using a custom unique key (email)
Goal: Find a user by email. If they exist, update their age. If not, create them.
```yaml
# table
users
# data
name: Jane Doe
age: 35
# uniqueKey
email: 'jane.doe@example.com'
```
### **changeRow**
Changes the content of a specific row identified by its primary key.
Parameters
* `table`: The name of the table.
* `id`: The primary key (`id`) of the row to change.
* `data`: An object containing the fields and new values.
* `options`: An optional object.
* `patch`: If `true`, partially updates nested JSON objects instead of replacing them.
Example: Update a user's age and status
```yaml
# table
users
# id
'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
# data
age: 37
status: 'active'
```
### **updateRow**
Updates specific field of an existing row, based on its primary key. This function essentially behaves like a standard SQL UPDATE.
{% hint style="info" %}
Fields not included in the `data` object are left untouched. For JSON columns, this method **replaces** the entire object with the provided value. To merge data into an existing JSON object, use `patchRow()` instead.
{% endhint %}
Parameters
* `table`: The name of the table.
* `data`: The data object, which must contain the `id` property.
Example
BEFORE: Row in 'settings' table:
```json
{
id: 1,
name: 'Config A',
settings: { theme: 'dark', notifications: true }
}
```
Call `updateRow` with the following data:
```yaml
# table
settings
# data
id: 1
settings: {
notifications: false,
timezone: UTC
}
```
AFTER: The row is now:
```json
{
id: 1,
name: 'Config A',
settings: { notifications: false, timezone: 'UTC' }
}
```
The `name` field was untouched, but the `theme` key in the JSON is gone.
### **patchRow**
Patches a row with new data, intelligently merging nested JSON objects.
{% hint style="info" %}
This method is ideal for partial updates. For JSON columns, it merges the new data into the existing object, preserving any original keys that are not part of the patch.
{% endhint %}
Example
BEFORE: Row in 'settings' table:
```json
{
id: 1,
name: 'Config A',
settings: { theme: 'dark', notifications: true }
}
```
Call `patchRow` with the following data:
```yaml
# table
settings
# data
id: 1
settings: {
notifications: false,
timezone: UTC
}
```
AFTER: The row is now:
```json
{
id: 1,
name: 'Config A',
settings: { theme: 'dark', notifications: false, timezone: 'UTC' }
}
```
The original `theme` key is preserved, and the new data is merged in.
### **deleteRow**
Deletes a single row from a table identified by its primary key.
Parameters
* `table`: The name of the table.
* `id`: The primary key of the row to delete.
Example
```yaml
# table
users
# id
'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
```
### **clearTable**
Deletes all rows from a table, but leaves the table structure intact.
Parameters
* `name`: The name of the table to clear.
Example
```yaml
# name
logs
```
## Relationships and Associations
These functions allow you to define the logical connections between your tables, creating a relational data model. Establishing relationships is key to ensuring data integrity and enabling powerful, cross-table queries. The typical workflow is a three-step process:
1. Define Tables: Create your tables using `defineTable`.
2. Define the Relationship: Use one of the association functions (`mandatorilyHasOne`, etc.) to tell the database how the tables are connected.
3. Link Records: Use the foreign key fields created in step 2 to connect specific rows. For many-to-many relationships, you'll use the `associateRow` function.
### **optionallyHasOne**
Creates a one-to-many relationship where the child record can exist without being linked to a parent record. This is achieved by creating a foreign key column that is nullable (can be empty).
* Hint: "A `child` has zero or one `parent`. A `parent` may have many `children`."
Parameters
* `childTable`: The name of the table that will receive the foreign key (e.g., `'posts'`).
* `parentTable`: The name of the table being referenced (e.g., `'users'`).
* `role`: (Optional) A string in `PascalCase` (e.g., `'Owner'`) used to create a distinct relationship when there are multiple connections between the same two tables.
### **mandatorilyHasOne**
Creates a one-to-many relationship where the child record cannot exist without being linked to a parent record. This creates a foreign key column that is non-nullable (must have a value).
* Hint: "A `child` must have exactly one `parent`. A `parent` may have many `children`."
Parameters
* `childTable`: The name of the table that will receive the foreign key (e.g., `'employees'`).
* `parentTable`: The name of the table being referenced (e.g., `'companies'`).
* `role`: (Optional) A string in `PascalCase` (e.g., `'Manager'`) used to create a distinct relationship.
### **optionallyHasMany**
Creates a many-to-many relationship between two tables. This automatically generates a hidden "junction" or "join" table to manage the complex associations.
* Hint: "A `child` can have many `parents`. A `parent` can have many `children`."
Parameters
* `childTable`: The name of the first table in the relationship.
* `parentTable`: The name of the second table in the relationship.
### **associateRow**
Links existing records together. This function is primarily used to create and manage the links for a many-to-many relationship after it has been defined.
Parameters
* `sourceTable`: The name of the source table.
* `sourceId`: The ID of the row in the source table.
* `targetTable`: The name of the target table.
* `targetId`: The ID (`string`) or an array of IDs (`array of strings`) of the row(s) in the target table.
### Relationship Strategies and Examples
This section provides a practical guide to choosing and implementing the correct relationship for your data model.
#### **One-to-Many (Mandatory)**
This is the most common type of relationship. Use it when a record (the "child") would be meaningless or invalid without its corresponding owner (the "parent").
**Scenario**: An `employee` must belong to a `company`. An employee record cannot exist in the database without being assigned to a company.
{% stepper %}
{% step %}
**Define tables**
First, create the companies and employees tables.
```yaml
# (call defineTable)
# name
companies
# fields
name: string
# (call defineTable)
# name
employees
# fields
firstName: string
lastName: string
```
{% endstep %}
{% step %}
Define the relationships
Next, declare that an employee mandatorily has one company.
```yaml
# (call mandatorilyHasOne)
# childTable
employees
# parentTable
companies
```
{% hint style="info" %}
This action adds a non-nullable `companyId` foreign key column to the `employees` table.
{% endhint %}
{% endstep %}
{% step %}
Link Records
Because the companyId is mandatory, you must provide it when creating a new employee record.
```yaml
# (call addRow)
# table
employees
# data
firstName: Ada
lastName: Lovelace
# You must provide the ID of an existing company
companyId: 'a1b2c3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a'
```
{% endstep %}
{% endstepper %}
#### **One-to-Many (Optional)**
Use this relationship when the link between a child and a parent is optional. The child record can be created first and linked to the parent at a later time.
**Scenario**: A blog `post` can optionally be assigned to a `category`. A post can exist without a category.
{% stepper %}
{% step %}
Define Tables
```
# (call defineTable)
# name
posts
# fields
title: string
content: text
# (call defineTable)
# name
categories
# fields
name: string
```
{% endstep %}
{% step %}
Define the Relationship
Declare that a post optionally has one category.
```yaml
# (call optionallyHasOne)
# childTable
posts
# parentTable
categories
```
{% hint style="info" %}
This adds a nullable `categoryId` foreign key column to the `posts` table.
{% endhint %}
{% endstep %}
{% step %}
Link Records
You can create a post without a category. Later, you can link it by updating the record.
```yaml
# (call addRow to create the post initially)
# table
posts
# data
title: 'My First Post'
content: '...'
# categoryId is not needed here
# (call patchRow later to link it to a category)
# table
posts
# data
id: 'f1e2d3c4-b5a6-4a3b-8c2d-1f2e3d4c5b6a' # ID of the post
categoryId: 'c1b2a3d4-e5f6-4a3b-8c2d-1f2e3d4c5b6a' # ID of the category
```
{% endstep %}
{% endstepper %}
#### **Many-to-Many**
Use this when records in two tables can have multiple links to each other.
**Scenario**: An `order` can contain many `products`, and a `product` can be included in many different `orders`.
{% stepper %}
{% step %}
Define Tables
```
# (call defineTable)
# name
orders
# fields
orderDate: date
# (call defineTable)
# name
products
# fields
name: string
price: number
```
{% endstep %}
{% step %}
Define the Relationship
Declare the many-to-many relationship between orders and products.
```yaml
# (call optionallyHasMany)
# childTable
orders
# parentTable
products
```
{% hint style="info" %}
This automatically creates a hidden "junction table" (e.g., `__orders2products`) to store the links between order IDs and product IDs.
{% endhint %}
{% endstep %}
{% step %}
Link Records
To connect the records, you must use the associateRow function. You can link one order to multiple products by providing an array of product IDs.
```yaml
# (call associateRow)
# sourceTable
orders
# sourceId
'o1d2e3r4-b5a6-4a3b-8c2d-1f2e3d4c5b6a' # ID of the order
# targetTable
products
# targetId is an array of product IDs
- 'p1r2o3d4-b5a6-4a3b-8c2d-1f2e3d4c5b6a'
- 'p5r6o7d8-b5a6-4a3b-8c2d-1f2e3d4c5b6a'
```
{% endstep %}
{% endstepper %}
#### **Advanced: Multiple Relationships with Roles**
Use the `role` parameter when you need to define more than one distinct relationship between the same two tables.
**Scenario**: A `document` has both an `owner` and an `editor`. Both the owner and the editor are records from the `users` table.
{% stepper %}
{% step %}
Define Tables
```
# (call defineTable)
# name
users
# fields
name: string
# (call defineTable)
# name
documents
# fields
title: string
```
{% endstep %}
{% step %}
Define Relationships (with Roles)
Create two distinct one-to-many relationships, specifying a role for each.
```yaml
# (call optionallyHasOne for the Owner)
# childTable
documents
# parentTable
users
# role
Owner
# (call optionallyHasOne for the Editor)
# childTable
documents
# parentTable
users
# role
Editor
```
{% hint style="info" %}
This adds two separate foreign keys to the `documents` table: `ownerId` and `editorId`. The role name directly determines the name of the foreign key.
{% endhint %}
{% endstep %}
{% step %}
Link Records
When creating a document, you can now provide IDs for both the owner and the editor using the specific foreign key fields.
```yaml
# (call addRow)
# table
documents
# data
title: 'Q4 Financial Report'
ownerId: 'u1s2e3r4-b5a6-4a3b-8c2d-1f2e3d4c5b6a' # A user's ID
editorId: 'u5s6e7r8-b5a6-4a3b-8c2d-1f2e3d4c5b6a' # A different user's ID
```
{% endstep %}
{% endstepper %}
## Audit Logging
The `Database` class features a robust, built-in Audit Logging system. This feature replaces the legacy history tracking (`trackHistory`) and is designed to create a secure, detailed, and queryable trail of all data changes.
Audit logs track exactly what changed, when it changed, and who changed it. It automatically calculates the differences (`diff`) between old and new values for updates, and stores full snapshots for creations and deletions.
{% hint style="warning" %}
**Deprecation Notice**
The `trackHistory` option in `defineTable` and the `getHistoricalData` function are deprecated as of February 2025. Please use `auditLog` and `getAuditLog` for all future implementations.
{% endhint %}
#### Enabling Audit Logs
To enable audit logging for a table, set the `auditLog` option to `true` when defining the table schema using the `defineTable` function.
```yaml
# name
orders
# fields
orderNumber: string
status: string
total: number
# options
auditLog: true
```
When enabled, the database automatically creates a hidden, parallel table (e.g., `ordersAuditLog`) that securely records all `CREATE`, `UPDATE`, and `DELETE` actions performed on the main table.
#### Tracking the Actor (`actorId`)
To know *who* made a change, all data manipulation functions (such as `addRow`, `updateRow`, `patchRow`, `upsertRow`, and `deleteRow`) now accept an optional `actorId` within their options object.
In a typical Heisenware application, you will want to bind this to the currently authenticated user using the `$USER` variable or their specific user ID.
Example: Updating a row and logging the actor
```yaml
# table
orders
# data
id: 'order-123'
status: 'shipped'
# options
actorId: 'admin-alice'
```
#### Retrieving Audit Logs (`getAuditLog`)
The `getAuditLog` function allows you to retrieve and filter the recorded history. It provides powerful filtering capabilities, including natural language time parsing (e.g., 'yesterday', '-1h') and field-level tracking.
Parameters
* `table`: The name of the table to query (e.g., `'orders'`).
* `options`: An object for filtering the logs.
* `id`: Filters logs for a specific record's primary key.
* `actorId`: Filters logs by the user who made the change.
* `action`: Filters by the specific action type (`'CREATE'`, `'UPDATE'`, or `'DELETE'`).
* `changedField`: Only returns logs where a specific field was modified.
* `start`: Earliest time to include (can be natural language like `'yesterday'` or `'-1h'`).
* `stop`: Latest time to include. Defaults to `'now'`.
**Example 1: View all changes to a specific record**
*Goal: See the complete lifecycle of order 'order-999'.*
```yaml
# table
orders
# options
id: 'order-999'
```
**Example 2: Track specific field modifications**
*Goal: Find out who changed the 'status' field of an order, and when.*
```yaml
# table
orders
# options
id: 'order-999'
changedField: status
```
**Example 3: Monitor user activity**
*Goal: See all deletions performed by a specific admin in the last 24 hours.*
```yaml
# table
products
# options
action: DELETE
actorId: admin-alice
start: -24h
```
#### Understanding the Log Structure
The `getAuditLog` function returns an array of log entries, ordered from newest to oldest. The most important part of the log is the `diff` object, which varies depending on the `action`:
1\. CREATE
When a record is created, there is no "old" state. The `new` property contains the complete inserted record.
```json
{
"action": "CREATE",
"actorId": "admin-alice",
"diff": {
"old": null,
"new": { "id": "order-123", "status": "pending", "total": 150.00 }
},
"createdAt": "2025-08-20T10:00:00.000Z"
}
```
2\. UPDATE
When a record is updated, the `diff` object *only* contains the fields that actually changed, showing both their `old` and `new` values.
```json
{
"action": "UPDATE",
"actorId": "admin-alice",
"diff": {
"status": {
"old": "pending",
"new": "shipped"
}
},
"createdAt": "2025-08-21T14:30:00.000Z"
}
```
3\. DELETE
When a record is deleted, there is no "new" state. The `old` property contains the final snapshot of the record before it was destroyed, allowing for potential data recovery.
```json
{
"action": "DELETE",
"actorId": "admin-alice",
"diff": {
"old": { "id": "order-123", "status": "shipped", "total": 150.00 },
"new": null
},
"createdAt": "2025-08-25T09:15:00.000Z"
}
```
## "Magic" Functions (Auto-Schema)
These functions create and alter tables on the fly. Use them for rapid prototyping or when dealing with unpredictable data structures.
**autoUpsertRow**
Upserts a row. If the table or columns don't exist, they're created automatically based on the provided data.
Parameters
* `table`: The name of the table.
* `data`: The data object to upsert.
* `uniqueKey`: An optional unique key for collision detection.
### **autoAddRows**
Bulk-inserts data. Like `autoUpsertRow`, it creates or alters the table schema as needed based on the first data object in the array.
Parameters
* `table`: The name of the table.
* `data`: An array of data objects to insert.
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.heisenware.com/app-builder/build-backend/function-explorer/storage/relational-database.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.