references/datastore-basics.md
> **⚠️ PRE-FLIGHT CHECK:** Confirm `.catalystrc` exists before writing any code. `catalyst.json` is created automatically by the first feature command (`catalyst functions:add -ni`, `catalyst slate:create -ni`, etc.) — its absence before that step is expected and not an error.
## System Columns (auto-managed, in every table)
- `ROWID` — unique row identifier (bigint, auto-increment)
- `CREATORID` — user ID of the creator
- `CREATEDTIME` — timestamp of creation (project timezone, no UTC offset)
- `MODIFIEDTIME` — timestamp of last modification
**Never create these columns** — Catalyst adds them automatically.
---
## CRUD Operations (Node.js SDK)
```javascript
const table = catalystApp.datastore().table('Employees');
// or by ID: catalystApp.datastore().table(TABLE_ID)
// TABLE_ID from Console → Cloud Scale → Data Store → click table
// INSERT
const row = await table.insertRow({
Name: 'Alice',
Email: 'alice@example.com',
Department: 'Engineering',
Salary: 85000
});
console.log('Inserted ROWID:', row.ROWID);
// GET by ROWID
const row = await table.getRow(ROWID);
// GET all rows (paginated) — use getPagedRows(), NOT getAllRows() which is deprecated
// Default page size: 200 rows. maxRows is optional.
// Response shape: { data, next_token, more_records }
// data rows are already flat — NO table-name wrapper (unlike ZCQL results)
function fetchAllRows(nextToken = undefined) {
table.getPagedRows({ nextToken, maxRows: 200 })
.then(({ data, next_token, more_records }) => {
console.log('rows:', data);
// data: [{ ROWID, CREATORID, CREATEDTIME, Name, ... }, ...]
if (more_records) fetchAllRows(next_token);
});
}
// UPDATE (must include ROWID)
const updated = await table.updateRow({ ROWID: '12345', Salary: 90000 });
// DELETE
await table.deleteRow(ROWID);
// BULK INSERT (up to 200 rows)
const rows = await table.insertRows([
{ Name: 'Bob', Email: 'bob@example.com' },
{ Name: 'Carol', Email: 'carol@example.com' }
]);
// BULK UPDATE (each must have ROWID)
await table.updateRows([
{ ROWID: '123', Name: 'Robert' },
{ ROWID: '456', Name: 'Caroline' }
]);
// BULK DELETE
await table.deleteRows([ROWID_1, ROWID_2]);
```
---
## ZCQL
Catalyst's SQL-like query language.
```javascript
const zcql = catalystApp.zcql();
// SELECT
const result = await zcql.executeZCQLQuery(
"SELECT * FROM Employees WHERE Department = 'Engineering'"
);
// INSERT
await zcql.executeZCQLQuery(
"INSERT INTO Employees (Name, Email) VALUES ('Dave', 'dave@example.com')"
);
// UPDATE
await zcql.executeZCQLQuery(
"UPDATE Employees SET Salary = 95000 WHERE ROWID = 12345"
);
// DELETE
await zcql.executeZCQLQuery(
"DELETE FROM Employees WHERE Department = 'Obsolete'"
);
// Aggregate
const result = await zcql.executeZCQLQuery(
"SELECT COUNT(ROWID) AS total, AVG(Salary) AS avg_salary FROM Employees"
);
```
### Result Unwrapping
`executeZCQLQuery` wraps results under the table name key:
```javascript
const result = await zcql.executeZCQLQuery("SELECT * FROM Employees");
// Raw: [{ Employees: { ROWID: "123", Name: "Alice" } }, ...]
// Unwrap:
const rows = result.map(r => r.Employees).filter(Boolean);
// Generic helper:
function unwrapZcql(result, tableName) {
return result.map(r => r[tableName]).filter(Boolean);
}
```
The table name key is **case-sensitive** and must match the console exactly.
### Reserved Column Names
`priority` is a reserved keyword — `CatalystbyZoho_Create_Column` returns `INVALID_OPERATION: Column name cannot contain reserved keywords`. Rename the column (e.g. `task_priority`, `urgency`) before retrying.
### ZCQL Silent Failures
> ⚠️ **Two features accepted without error but not supported:**
>
> **`LIKE` wildcards (`%`) are not functional.**
> `WHERE title LIKE 'A%'` returns `[]` — no error, no rows.
> Use `=` for exact match. For prefix/substring search, fetch with `getPagedRows()` and filter in application code.
>
> **`AS` column aliases are silently dropped.**
> `SELECT title AS t FROM Todos` returns the key `title`, not `t` — no error thrown.
> Use original column names in all downstream code; remap keys in application code if needed.
### ZCQL Differences from SQL
- Table/column names are **case-sensitive**
- String values use **single quotes only**
- No multi-statement transactions (no BEGIN/COMMIT/ROLLBACK)
- Maximum **300 rows** per query — paginate with `LIMIT offset, count`
- Maximum **20 columns** per SELECT — use explicit column names on tables with > 20 columns
- Supported: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `LIKE`, `IN`, `NOT IN`, `BETWEEN`, `ORDER BY`, `GROUP BY`, `HAVING`, `COALESCE`, `DISTINCT`
- INNER JOIN and LEFT JOIN supported (same Data Store only)
### Pagination
```javascript
// Offset-based (preferred)
const PAGE_SIZE = 300;
let offset = 0;
let allResults = [];
while (true) {
const batch = await zcql.executeZCQLQuery(
`SELECT * FROM Employees ORDER BY ROWID LIMIT ${offset}, ${PAGE_SIZE}`
);
const rows = batch.map(r => r.Employees).filter(Boolean);
if (rows.length === 0) break;
allResults = allResults.concat(rows);
if (rows.length < PAGE_SIZE) break;
offset += PAGE_SIZE;
}
```
### JOINs
```sql
-- INNER JOIN
SELECT E.Name, D.DeptName
FROM Employees E
INNER JOIN Departments D ON E.DeptId = D.ROWID
-- LEFT JOIN
SELECT E.Name, D.DeptName
FROM Employees E
LEFT JOIN Departments D ON E.DeptId = D.ROWID
```
JOINs are subject to the same 300-row result limit.
---
## Column Types
- `varchar` — requires `max_length`; **hard cap is 255** — values above 255 are silently clamped to 255 by the API with no error. Use `text` for anything longer.
- `text` — large text, auto max 10,000 chars
- `int`, `bigint`, `double`, `decimal`
- `boolean`, `date`, `datetime`
- `foreign key` — requires `parent_table`, `parent_column`, `constraint_type`
- `encrypted text` — for sensitive data; `search_index_enabled` NOT allowed
- `text area` — for large text
### Boolean Columns — Critical Behavior
⚠️ **Boolean columns in Catalyst DataStore are stored as TEXT strings `"true"` and `"false"`, not as JavaScript booleans.**
This is one of the most common silent bugs in DataStore apps. In JavaScript, the string `"false"` is **truthy** — so any boolean check against an unconverted value will behave incorrectly.
```javascript
// ❌ WRONG — string "false" is truthy, all booleans appear true
const { data } = await table.getPagedRows({ maxRows: 200 });
// data[0].completed === "false" (string, not boolean)
if (data[0].completed) {
// This block EXECUTES even though the value is "false"!
}
// ❌ WRONG — storing a JS boolean; DataStore coerces it to the string "false"
await table.insertRow({ title: 'Buy milk', completed: false });
// Stored as: "false" (string) — not a bug, but makes reading back consistent
```
```javascript
// ✅ CORRECT — always convert boolean columns on read
const { data } = await table.getPagedRows({ maxRows: 200 });
const rows = data.map(todo => ({
...todo,
completed: todo.completed === 'true' || todo.completed === true
}));
// ✅ CORRECT — explicit string on write (self-documenting)
await table.insertRow({ title: 'Buy milk', completed: 'false' });
// ✅ CORRECT — convert boolean to string on update
await table.updateRow({
ROWID: id,
completed: isCompleted ? 'true' : 'false'
});
```
**Reusable helper (recommended for any table with boolean columns):**
```javascript
function convertBooleanFields(row, booleanColumns = []) {
const result = { ...row };
booleanColumns.forEach(col => {
if (col in result) {
result[col] = result[col] === 'true' || result[col] === true;
}
});
return result;
}
// Usage
const { data } = await table.getPagedRows({ maxRows: 200 });
const todos = data.map(row => convertBooleanFields(row, ['completed', 'isActive']));
```
**Why this happens:** DataStore maps the `boolean` column type to TEXT internally for compatibility across SDK and ZCQL query methods. The values `"true"` and `"false"` are always strings at the API boundary.
---
## Data Store Permissions
By default, the App User role has **Read-only** access. Insert, Update, Delete operations return `"No privileges to perform this action"`.
**Fix Option 1 (console):**
Console → Data Store → {Table} → Permissions → App User → check Select, Insert, Update, Delete.
**Fix Option 2 (admin-scope SDK):**
```javascript
const adminApp = catalyst.initialize(req, { scope: 'admin' });
const dataStore = adminApp.datastore();
```
---
## CREATEDTIME Timezone Behavior
Catalyst stores `CREATEDTIME` in the **project's configured timezone** without a UTC offset marker. `new Date(row.CREATEDTIME)` treats it as UTC — causing off-by-hours errors.
```javascript
// WRONG:
const created = new Date(row.CREATEDTIME);
// CORRECT:
function parseCatalystTime(catalystTimestamp, tzOffsetMinutes) {
// tzOffsetMinutes: positive = ahead of UTC. IST = +330
const utcDate = new Date(catalystTimestamp.replace(' ', 'T').replace(/:(\d{3})$/, '.$1') + 'Z');
return new Date(utcDate.getTime() - tzOffsetMinutes * 60 * 1000);
}
// For IST (UTC+5:30):
const created = parseCatalystTime(row.CREATEDTIME, 330);
```
---
## Emoji / 4-byte UTF-8
Data Store does NOT support emoji or 4-byte UTF-8 characters. They are silently stored as `?`.
```javascript
// Strip before inserting
function stripEmoji(str) {
return str.replace(/[\u{10000}-\u{10FFFF}]/gu, '');
}
const safeName = stripEmoji(userInput);
await table.insertRow({ Name: safeName });
```
---
## Transactions
Data Store does NOT support multi-statement transactions.
**Workarounds:**
- Use single ZCQL statements for bulk operations
- Use optimistic concurrency: read `MODIFIEDTIME`, verify before writing
- Use Circuits for multi-step workflows with saga patterns — **US DC only**; Circuits is not available in EU, AU, IN, JP, SA, or CA data centers
> ⚠️ **DC restriction:** Circuits is **not available** in EU, AU, IN, JP, SA, or CA data centers.
> Before recommending Circuits, confirm the user's data center. For restricted DCs, use single ZCQL statements or optimistic concurrency instead.
---
## Concurrency Limits
- Default: **10 concurrent executions** per function per environment
- HTTP 429 returned when queue is full
- Contact Catalyst support to increase the limit
## Common Errors
| Error | Cause | Fix |
|-------|-------|-----|
| `HTTP 429 Too Many Requests` on bulk write | Bulk write queue is full | Reduce batch size; implement exponential backoff; contact Catalyst support to increase limit |
| ZCQL returns fewer rows than expected | ZCQL SELECT hard limit is **300 rows** per query (not 200) | Paginate with `LIMIT offset, 300` — e.g., `LIMIT 0, 300`, then `LIMIT 300, 300`; for full-table scans use `getPagedRows()` (default: 200 rows per page; returns `{ data, next_token, more_records }`) |
| `Column not found` on insert | Column name case mismatch or column not yet created | Column names are case-sensitive; verify in Console → Data Store |
| `ZCQL query exceeds 20-column SELECT limit` | ZCQL limits SELECT to 20 columns per query | Split into multiple queries or use `SELECT *` (counts as 1) |
| Boolean field is always `true` in frontend | DataStore boolean columns return strings `"true"`/`"false"` — string `"false"` is truthy in JavaScript | Convert on read: `row.completed === 'true' \|\| row.completed === true`; use `convertBooleanFields()` helper |
SKILL.md
---
name: catalyst-datastore
description: "Catalyst Data Store — relational cloud database with ZCQL, CRUD operations, table permissions, and result pagination. Requires MCP connection — check for CatalystbyZoho_* tools before any operation. Trigger on 'Data Store', 'ZCQL', 'create table', 'executeZCQLQuery', 'table permissions', 'ROWID', 'boolean column', 'boolean always true', 'truthy string', 'boolean stored as string', or 'DataStore data types'. You MUST load this skill whenever writing code that reads or writes Data Store data — ZCQL result wrapping, boolean-as-string behavior, and App User permissions are non-obvious and cause silent bugs if skipped."
metadata:
version: "2.1.0"
---
## ⚠️ PREREQUISITES — READ THIS FIRST
**Before ANY Data Store operation, you MUST verify MCP connectivity.**
- Search for `CatalystbyZoho_*` tools in your available tool list.
- **If NOT found:** STOP. Do NOT write any code, scaffold files, or instruct Console steps. Load `catalyst-zoho-mcp` skill and guide the user through MCP setup. Do NOT proceed until the user confirms `CatalystbyZoho_*` tools are visible.
- **If found:** Run `CatalystbyZoho_List_All_Organizations` → `CatalystbyZoho_List_All_Projects` to set project context, then continue to "How It Works".
### Execution Checklist (MUST follow in order)
- [ ] Step 0: Confirm `CatalystbyZoho_*` tools visible → if none found → STOP, set up MCP first
- [ ] Step 1: Run `CatalystbyZoho_List_All_Organizations`
- [ ] Step 2: Run `CatalystbyZoho_List_All_Projects`
- [ ] Step 3: For table creation → `CatalystbyZoho_Create_Table` | For existing tables → `CatalystbyZoho_List_All_Tables`
- [ ] Step 4: Proceed with the operation
---
## How It Works
1. **Create or locate the table via MCP** — Creating a table? Use `CatalystbyZoho_Create_Table` directly. Do NOT instruct the user to open the Catalyst Console or create the table manually. Reading/writing data? Use `CatalystbyZoho_List_All_Tables` to get table IDs. Never ask the user to copy table IDs.
2. **Load `references/datastore-basics.md`** — for CRUD operations, ZCQL syntax, result unwrapping, and permissions setup.
3. **Unwrap ZCQL results** — Always remind: `rows.map(r => r.TableName)`. Raw ZCQL results are wrapped; accessing without unwrapping is the #1 Data Store bug.
4. **Permissions** — App User permissions are OFF by default. If the query involves a logged-in user reading data, check Console → Table → Scopes & Permissions.
5. **Pagination** — ZCQL max 300 rows per query. Use `LIMIT offset, count` for larger datasets.
## Hallucination Guards
**Never assert project-specific values from an empty or unread directory.** If the working directory has no project files, do not invent or assume timezone, table names, project ID, environment names, or any other project-specific detail. Ask the developer to supply them.
- Timezone: do not state a timezone for ZCQL date queries unless you have read it from a real project config file. Ask the developer what timezone their project uses.
- Table names / column names: only use names returned by `CatalystbyZoho_List_All_Tables` or explicitly provided by the user.
**Default to native Node.js response syntax.** When writing function code alongside Data Store operations, use `res.writeHead()` + `res.end()` — not Express methods (`res.status()`, `res.json()`), unless the user explicitly chose the Express template.
## Security Checklist
- **App User write permissions are off by default.** The App User role has SELECT enabled by default, but INSERT, UPDATE, and DELETE must be manually enabled per table. Go to Console → Table → Scopes and Permissions → App User role → check Insert/Update/Delete for any table your authenticated users need to write.
- **App Administrator has all permissions by default.** Never assign the App Administrator role to regular end-users — it grants full read/write/delete access to all tables.
## Triggers
Use this skill for: "Data Store", "ZCQL", "catalyst table", "create table", "query data", `executeZCQLQuery`, "table permissions", "App User permissions", "ROWID", "CREATEDTIME", "Data Store CRUD", "JOIN in ZCQL", "pagination ZCQL", "data store column types", "insert row", "update row", "delete row", or "relational data on Catalyst".
## References
| Reference | Load when the query is about… |
|-----------|-------------------------------|
| `references/datastore-basics.md` | CRUD, ZCQL queries, result unwrapping, pagination, column types, App User permissions setup, CREATEDTIME timezone gotcha, emoji limitation |