Custom Tables

Custom tables are small typed tables that belong to one project. Use them for configuration your rules and promotions read at decision time: tier thresholds and multipliers, SKU eligibility lists, store exclusions. A rule reads a table through a custom_table resource, so you change a threshold by updating a row, not by editing every expression that uses it.

Choose a custom table when the data changes more often than the rules, is shared by several rules or promotions, or is too large for a payload resource. If the value is per customer and changes with every purchase, keep it in the wallet or the customer’s metadata instead.

Tables and columns

POST /projects/{projectId}/storage/tables
{
  "name": "tiers",
  "description": "Loyalty tiers",
  "columns": [
    { "name": "name", "type": "text", "required": true, "unique": true },
    { "name": "minLifetimePoints", "type": "integer", "required": true },
    { "name": "multiplier", "type": "real", "required": true, "defaultValue": 1 },
    { "name": "freeShipping", "type": "boolean", "defaultValue": 0 },
    { "name": "perks", "type": "json" }
  ],
  "indexes": [{ "columns": ["minLifetimePoints"] }]
}

Column type	Stored as	Comes back as	Important distinction
`text`	text	string
`integer`	integer	number	Use for money in minor units (cents) and point counts
`real`	floating point	number	Use for multipliers and rates, not money
`boolean`	integer	`1` or `0`	Write `1`/`0`, and compare with `1`/`0`, not `true`
`json`	JSON text	string	Parse it in expressions: `JSON.parse($('tier').perks)`
`datetime`	text	string	Store ISO 8601 so `before`/`after` and sorting work

Table and column names match ^[a-zA-Z][a-zA-Z0-9_]{0,63}$. Table names must not start with _.
Every table gets the system columns id (text primary key), created_at and updated_at. You cannot define columns with those names.
Column options: required (default false), unique, defaultValue (string, number, boolean or null). A required column without a defaultValue must be present on every insert.
Indexes take columns and optional unique and name (default idx_<table>_<columns>).
Values are not type-checked on insert. Send the JSON type that matches the column.

Change a table with PATCH /projects/{projectId}/storage/tables/{tableName}, using addColumns, removeColumns, renameColumns ([{ "from": "a", "to": "b" }]), addIndexes or removeIndexes. DELETE drops the table and its rows. Both need the storage:admin scope, which project API keys do not carry today, so run them from the dashboard (see authentication).

Limits

Limit	Value
Tables per project	20
Columns per table	50
Indexes per table	10
Rows per table	50,000
Row size (JSON of the inserted row)	8,192 bytes
Rows per bulk insert request	1,000
Rows per query	1,000 (default 50)

Rows

Every row needs a string id that you choose. Storelayer does not generate one. Use a stable natural key such as "gold" or a SKU, so lookups and updates can address the row directly. Inserting an existing id fails; it does not overwrite.

POST /projects/{projectId}/storage/tables/tiers/rows/bulk
{
  "rows": [
    { "id": "bronze", "name": "Bronze", "minLifetimePoints": 0,    "multiplier": 1,   "freeShipping": 0, "perks": [] },
    { "id": "silver", "name": "Silver", "minLifetimePoints": 1000, "multiplier": 1.25, "freeShipping": 0, "perks": ["birthday_bonus"] },
    { "id": "gold",   "name": "Gold",   "minLifetimePoints": 5000, "multiplier": 1.5, "freeShipping": 1, "perks": ["birthday_bonus", "early_access"] }
  ]
}

POST .../rows inserts one row and returns it. POST .../rows/bulk returns { "inserted": 3 }.
An existing id fails with A row with this id already exists. Use PATCH .../rows/{rowId} with only the changed columns to update a row.
Unknown columns, missing required columns and unique violations are rejected. In a bulk request, unknown-column and missing-column errors name the failing row’s id.
GET .../rows/{rowId} reads one row, and DELETE .../rows/{rowId} removes it.

Querying

POST /projects/{projectId}/storage/tables/tiers/rows/query
{
  "filter": { "AND": [ { "field": "minLifetimePoints", "op": "lte", "value": 2500 } ] },
  "sort": [ { "field": "minLifetimePoints", "direction": "desc" } ],
  "select": ["name", "multiplier"],
  "limit": 1,
  "offset": 0
}

The response is { "rows": [...], "total": <count matching the filter> }. Paginate with limit and offset.

Filter op	Meaning
`eq`, `neq`, `gt`, `gte`, `lt`, `lte`	Comparison
`in`, `notIn`	`value` is an array
`contains`, `startsWith`, `endsWith`	Substring match (SQL `LIKE` with `%` added for you)
`like`	Raw `LIKE` pattern; you supply `%` and `_`
`isNull`, `isNotNull`	No `value`

A filter group has AND or OR. If a group has both, only AND is used. To combine them, nest a group: { "AND": [ { "field": "freeShipping", "op": "eq", "value": 1 }, { "OR": [ ... ] } ] }.
Fields in filter, sort and select must be real columns or id, created_at, updated_at. Anything else is rejected.
select always includes id.

SQL endpoint

POST /projects/{projectId}/storage/sql with { "sql": "...", "maxRows": 100 } runs a single SQLite statement over the project’s tables. It requires the storage:admin scope, which project API keys do not carry today. SELECT, WITH and PRAGMA return { columns, rows, rowCount }. A LIMIT of maxRows (default 1000) is appended when the query has none.

The endpoint is not read-only. INSERT, UPDATE, DELETE, CREATE, ALTER and DROP also run, return rowsAffected, and skip the row validation and limits above. Use it for ad-hoc reads and aggregates (select count(*) ...). Do writes through the row endpoints.

Binding a table to rules and promotions

A custom_table resource reads a table during an execution:

POST /projects/{projectId}/resources
{
  "key": "tier",
  "name": "Customer tier",
  "type": "custom_table",
  "config": {
    "tableName": "tiers",
    "lookupField": "id",
    "lookupExpression": "{{ $('customer').metadata.tier ?? 'bronze' }}"
  },
  "dependsOn": ["customer"]
}

Config	Behavior	`$('tier')` resolves to
`lookupField` + `lookupExpression`	Expression resolved, then `lookupField = value`	First matching row, or `null` if none
`defaultFilter` (`{ "column": value }`)	Equality on every listed column; used when there is no lookup or the lookup value is `null`/`undefined`	Array of up to 50 rows
Neither	All rows	Array of up to 50 rows

A missing row resolves to null, not an error. $('tier').multiplier is then undefined, so comparisons fail and arithmetic gives NaN. Decide the fallback explicitly: default the lookup value (as above with ?? 'bronze'), or default the read ({{ $('tier').multiplier ?? 1 }}).
Whole-table reads stop at 50 rows. For larger tables, always look up by a key.
A table that doesn’t exist resolves to { "_error": "..." }.
Put every resource the lookup expression reads in dependsOn.

Bind the resource like any other: add it to the rule’s resources array ({ "id": "<resource id>", "key": "tier", "type": "custom_table", "value": {} }) or to the promotion’s resources object. For promotions, also bind every dependency ("customer" here), because a promotion resolves only the keys it names. See binding resources.

Worked example: tier multiplier

Goal: customers earn points per dollar multiplied by their tier, and gold customers get a free-shipping promotion. Each customer’s tier ID is stored in their customer metadata (metadata.tier).

1. Create and fill the table with the two requests above (tiers with bronze, silver, gold).

2. Create the resources. customer loads the customer profile, and tier looks up their row:

POST /projects/{projectId}/resources
{
  "key": "customer",
  "name": "Customer",
  "type": "internal",
  "config": { "entity": "user", "userIdExpression": "{{ $('event').userId }}" }
}

Then create tier as shown in Binding a table. A customer without metadata.tier, or one not registered (the customer value is then an _error object), falls back to bronze.

3. Check what resolves:

POST /projects/{projectId}/resources/resolve
{
  "triggerContext": { "event": { "type": "purchase", "userId": "user_123", "payload": { "amount": 4250 } } },
  "resourceKeys": ["tier"]
}

data.tier should be a row such as { "id": "silver", "multiplier": 1.25, "perks": "[\"birthday_bonus\"]", ... }. perks comes back as a JSON string.

4. Use it in expressions. Points for a 42.50 purchase (amount 4250 cents) at 1 point per dollar:

{{ Math.floor($('event').payload.amount / 100 * ($('tier').multiplier ?? 1)) }}   → 53 for silver

Use that as the amount expression of a reward action in a rule that binds event, customer and tier (see loyalty program). For the gold promotion, bind { "customer": {}, "tier": {} } and add this condition:

{ "leftValue": "{{ $('tier').freeShipping }}", "operator": "equals", "rightValue": 1 }

A perk check reads the JSON column:

{ "leftValue": "{{ JSON.parse($('tier').perks ?? '[]').includes('early_access') }}", "operator": "is_true" }

5. Change the program by editing data. PATCH /projects/{projectId}/storage/tables/tiers/rows/gold with { "multiplier": 2 } takes effect on the next execution, with no rule changes.

To derive the tier from points instead of storing it, read the whole table (no lookup; fine for a few rows) and compare against the wallet:

{ "leftValue": "{{ ($('wallet').points.lifetimeEarned ?? 0) >= $('tiers').find(t => t.id === 'gold').minLifetimePoints }}", "operator": "is_true" }

Pitfalls

Missing id on insert. Every row needs a caller-chosen string id.
Comparing a boolean column with true. It returns 1/0.
Treating a json column as an object. It returns a string; use JSON.parse.
A lookup with no matching row returns null. Test a key that exists and one that doesn’t.
Renaming or removing a column, or dropping a table, silently breaks resources and expressions that read it. Check GET /projects/{projectId}/resources for custom_table resources with that tableName first.
Writes through /storage/sql skip validation and limits.