# Datatables — client-side playbook

n8n data tables: small operational stores a workflow reads/writes at runtime.
Read with `awesomate_n8n_inspect {what: "datatables"}` (tables, columns, row
counts). Write with `awesomate_n8n_datatable_write` (Support Plus+ plans only).

## Right store or wrong store

| Data | Store |
|---|---|
| Dedupe ledger (seen event ids, sent emails) | Datatable |
| Task queue / retry list a workflow drains | Datatable |
| Lookup map (SKU→label, email→owner) | Datatable |
| Cursor/state between runs (last-synced timestamp) | Datatable |
| Real app data (users, orders, content) | The app's own database — never a datatable |
| Analytics, event history, anything unbounded | External store (app DB, sheet, warehouse) |

Rule of thumb: bounded row count, flat rows, no joins, consumed by the
automation itself. Instance cap is 50 tables (`datatable_cap_reached`), and
creates have a daily plan quota (`quota_exceeded` — limits in
`awesomate_n8n_context`).

## Creating — pass workflowId

```
awesomate_n8n_datatable_write {action: "create", name, columns: [{name, type?}], workflowId}
```

- **Datatables resolve PER PROJECT at runtime.** A workflow in project A
  referencing a table in project B fails on every run with
  `Could not find the data table: '<id>'` — while the table, its columns, and
  permissions are all perfectly intact. When the id matches and structure
  looks fine, the project link is what's broken.
- **Pass `workflowId` whenever the table serves a specific workflow** (nearly
  always). The table is then moved into that workflow's owner project —
  co-location is verified, never assumed.
- Without `workflowId` the server co-locates only when exactly one project
  owns your Claude Code drafts. Otherwise the response carries a
  `projectWarning` ("CLI drafts span N projects — pass workflowId…" or
  "workflowId … has no owner project — table left where n8n placed it").
  **Treat `projectWarning` as a failed co-location**: recreate with the right
  `workflowId` before inserting any rows.
- Column `type`: `string` | `number` | `boolean` | `date` (default `string`).
  Column names: letters/digits/underscores, starting with a letter, no
  spaces — n8n's row API references columns bare, so a spaced name is
  unwritable even though PG would accept it.
- Design the full column set up front — schema is effectively frozen (next
  section).

## Schema is immutable over REST

n8n's REST API cannot alter a table. `action: "add_column"` exists but runs a
direct database write, so it additionally requires the account's
`allow_pg_writes` consent — ON by default, and NOT client-toggleable: a
`consent_required` here means Awesomate disabled it for this account, so the
remediation is recreating the table with the full column set, or asking
Awesomate support to re-enable direct database writes.
There is no rename, drop, or retype at all. Wrong schema and no PG consent →
create a new table, migrate rows, repoint the workflow.

## Writes only touch self-created tables

`insert` / `update` / `delete_rows` / `add_column` work only on tables created
through Claude Code. Anything else → `403 not_self_created` — including tables
provisioned by Awesomate template deploys. Those belong to managed
automations; do not work around the refusal. Reads are unrestricted:
`awesomate_n8n_inspect` lists every table.

## Row operations

| action | Requires | Notes |
|---|---|---|
| `insert` | `rows`: 1–100 objects | Chunk larger batches |
| `update` | non-empty `filter` + `data` object | `data` is one object, not an array |
| `delete_rows` | non-empty `filter` — REQUIRED | There is deliberately no delete-all. To empty a table, delete by filter or recreate it |

## The dedupe gate pattern (rowNotExists)

Insert-if-absent as a barrier before side effects — the main reason a
datatable exists in a client workflow:

1. Table with one key column (e.g. `event_id` string) plus any audit columns.
2. In the workflow, BEFORE the side effect (email, API call, charge): Data
   table node with the **row-does-not-exist** operation matching the key —
   only unseen items pass.
3. Insert the key immediately after the gate, BEFORE the side effect.
   Insert-first gives at-most-once (right for emails and charges);
   insert-after gives at-least-once (duplicates on crash/retry).
4. The gate and insert are separate node steps, not a transaction — a webhook
   delivered twice in the same instant can pass both. Acceptable for dedupe;
   not a locking primitive.

Queue-drain workflows (`splitInBatches` over a table) need `executionTimeout`
set, and `saveExecutionProgress` stays FALSE — both fleet invariants.

## Failure decode

| Symptom | Meaning | Fix |
|---|---|---|
| `Could not find the data table: '<id>'` at runtime | Project scope, not existence | Recreate/move the table with `workflowId`; check `awesomate_n8n_inspect {what: "datatables"}` vs the workflow's project |
| `403 not_self_created` | Table wasn't created through Claude Code | Create your own table; never write agency tables |
| `consent_required` on add_column | `allow_pg_writes` disabled for this account (not client-toggleable) | Recreate with the full schema, or ask Awesomate support |
| `409 column_exists` | Column name collision | Pick another name; check current columns via inspect |
| `429 datatable_cap_reached` / `quota_exceeded` | 50-table cap or daily create quota | Stop; reuse or delete tables, or wait for the daily reset |
| `501 datatables_unsupported` | Instance's n8n version predates data tables | No datatable path — use an external store |