# Generic tables A **generic table** is an operator-defined table whose schema the caller authors at create time, distinct from the platform's industry- built **system datasets** (see `datalakes.md` §5 `.systemDatasets`). The SDK namespace is `api.genericTables`. Generic tables are **Datalake-DB-resident**: creation triggers an async schema-migration job that builds a physical Postgres table in the datalake's schema. The parent datalake MUST be `status: 'ready'` before the job can run end-to-end — see `datalakes.md` §5 Lifecycle. > ## ⚠ `api.genericTables` manages table DEFINITIONS, never rows > > This namespace is DDL, not DML. It creates, updates (see §2), reads, > and deletes the *table* — its schema. **It does not write rows.** > There is **no** `POST /generic-tables/:id/rows`, no `insertRow`, no > `updateRow` — the endpoint an agent reaches for does not exist. > > **Rows are written by exactly one path: a bound data activation > client's `.ingest` / `.ingestFile`** (see `data_activation_clients.md`). > Ingest is asynchronous — it returns **`202` + a batch id, NOT the > saved row.** Every "Save" you see in a CRUD-looking UI over a generic > table is an ingest under the hood, and it lands eventually, not > inline. > > - To **seed / add rows**: `.ingest` through the table's default > manual-upload DAC (gotcha 7). > - To **edit a row**: re-ingest it with the same unique-column value — > ingest is an **upsert** (full-row replace on the unique key), so > "update row X" is just "ingest X again with new values". There is > no partial row patch. > - To **read rows**: `api.datasets.search` (paged) or > `api.datalakes.executeSql` (SQL) — never a `genericTables` verb. ## 1. Wire shape ```typescript import type { GenericTableColumnRequest, GenericTableResponse, } from '@alvera-ai/platform-sdk' const { data: created } = await api.genericTables.create( tenantSlug, datalakeSlug, { title: 'Contact Us Submissions', description: 'Inbound contact-form rows', columns: [ { name: 'submission_id', title: 'Submission ID', type: 'string', description: 'Unique submission identifier', is_unique: true, privacy_requirement: 'none', }, { name: 'email', title: 'Email', type: 'string', description: 'Submitter email', is_unique: false, // The masking pair: WHETHER, and WHAT it is so the right // function masks it. See "a pair" below. privacy_requirement: 'tokenize', semantic_kind: 'email', }, // ... more columns ], }, ) // created.id — server-derived UUID // created.title === body.title // created.name — server-derived: `alvera_custom_` // created.status === 'new' | 'processing' (migration not yet done) ``` The response returns immediately — the row exists in Platform-DB, but the *physical* Postgres table does not yet. Consumers MUST poll `.get(id)` until `status === 'deployed'` before treating the table as queryable. See §5 Lifecycle. (**`.get` keys on the UUID `id`, not `name`** — see §2.) ## 2. Rules the type cannot encode ### Two identifiers: the UUID `id` (API key) vs the `name` (SQL table) A created generic table carries **two** server-derived identifiers, and they are used in different places — mixing them up is the most common mistake against this resource: ``` id server-derived UUID — the API path key AND the FK. .get(id) / .update(id) / .delete(id) all take it, and workflows / DACs / contracts reference the table via generic_table_id = id. name 'alvera_custom_' — the PHYSICAL Postgres table name. Used in executeSql (`FROM alvera_custom_…`) and to recognise the table's auto-provisioned DAC by name. NOT an API path key. ``` `name` is server-derived from `title` — slugify + `alvera_custom_` prefix: ``` title: 'Contact Us Submissions' ↓ slugify + prefix name: 'alvera_custom_contact_us_submissions' ``` **`.get` / `.update` / `.delete` key on the UUID `id`, NOT `name`.** Passing `name` (or any non-UUID) to `.get` 422s with `Invalid format. Expected :uuid` — the same id/slug trap as other resources. To go from a known `name` back to its `id`, `list()` with a `handle` filter and read the row's `id`. Never pre-compute either identifier client-side — read both back from the create response and pass `id` to CRUD, `name` to `executeSql`. ### `columns` is operator-authored schema Each column declares storage + semantics in one shot: ``` name required string — snake_case identifier (physical Postgres column name) title required string — human label (UI / docs) type required enum — see "Column types" below is_array optional bool — column holds an array of `type` values description required string — narrative; surfaces in catalogs is_unique required bool — Postgres UNIQUE constraint at migration time privacy_requirement required enum — 'none' | 'tokenize' | 'redact' semantic_kind optional enum — 'string' | 'text' | 'email' | 'phone' | 'date' | 'json' | 'image' | 'pdf' | 'attachment'; nullable ``` #### `privacy_requirement` and `semantic_kind` are a pair They answer two different questions and a masked column needs both. **`privacy_requirement` says WHETHER the column is masked** in the derived lakes — see the table in §2 below. Immutable once the column exists. **`semantic_kind` says WHAT the value is, which decides WHICH function masks it.** A tokenized email should come back looking like an email; a tokenized phone like a phone. Without one, the platform can only treat the value as free text, so **a masked column with no `semantic_kind` gets a worse mask than it should** — still masked, but less useful to everything downstream that expected the shape to survive. It is optional, and nothing fails without it, which is exactly why it is easy to miss. **Set it on every column you mark `tokenize` or `redact`.** On a `none` column it is documentation and costs nothing. #### Column types ``` string free-form text integer whole numbers float approximate numeric (IEEE 754) decimal exact numeric, stored as `numeric` boolean true / false date calendar date (no time component) datetime timestamp with timezone time time of day (no date component) jsonb arbitrary JSON document uuid identifier; reads back as a uuid string, not raw bytes ``` `decimal` and `float` are distinct on purpose: `decimal` emits a Postgres `numeric` and comes back as an exact Decimal, while `float` is binary floating point and cannot load one. Money and anything else that must not drift belongs in `decimal`. Each type can be paired with `is_array: true` to model an array column (`string[]`, `integer[]`, etc.) — with one exception that has its own section below, because it is the single most expensive mistake available on this page. See **`jsonb` + `is_array`**. The wire enum lives in the generated SDK as the `type` field of `GenericTableColumnRequest` — consult it for the live set, since new types land independently of corpus revisions. #### Privacy × type interactions `privacy_requirement` decides what each of the three lakes hands back for this column. **All three lakes carry every column** — the copies are full copies, not projections — so the question is never whether the column is there but what value it holds. (See `datalakes.md` §1 for the lake split, and note that the tokenized and redacted copies exist only after an explicit `provisionTokenization` call.) Measured against a live three-lake tenant, one row read three ways: | `privacy_requirement` | raw | tokenized | redacted | |---|---|---|---| | `none` | `Family Medicine` | `Family Medicine` | `Family Medicine` | | `tokenize` | `Rosalind Achterberg` | `tok_7a1de41f5aba…` | `xxxx-berg` | | `redact` | the real value | masked | masked | Three things that table is trying to make unmissable: - **`none` is byte-identical everywhere.** It is not "raw only". A masking policy that altered a `none` column would silently break every join in the system, so these are the columns you filter and join on in every lake. - **`tokenize` and `redact` differ in the TOKENIZED lake, not the redacted one.** A tokenized column yields a stable `tok_` — the same input gives the same token, so a machine can join across tables on it without ever resolving it. A redacted column yields an unjoinable placeholder. That is the whole distinction: **`tokenize` preserves referential identity without disclosing it; `redact` destroys both.** - **The redacted lake masks both.** `xxxx-` is for a person glancing at a support screen, and it is deliberately useless as a key. There is **no restriction tying `jsonb` to `redact`.** A jsonb column may be `none`, `tokenize` or `redact` like any other. An older rule did refuse anything but `redact`, because tokenizing a JSON document once produced a value the column could not hold; that rule was **removed rather than relaxed**, because masking a JSON column is now well-defined (see the next section). If you are reading an older copy of this corpus that says otherwise, it is describing a 422 that no longer happens. One type-level quirk the typed validator does not encode: - **Tokenize widens primitive types at the schema layer.** A `{ type: 'integer', privacy_requirement: 'tokenize' }` column surfaces in the tokenized tier as a string-typed token, not an integer. This is a quirk of how the platform stores tokens (one opaque string format across all source types) — your queries against the tokenized lake must treat the column as a string. This affects `integer`, `float`, `boolean`, `is_array string`, `date`, `datetime`, and `time`. #### `jsonb` + `is_array` — the rule that is not what it looks like Declaring `{ type: 'jsonb', is_array: true }` is how you model a column holding a **JSON array** — a list of objects, say. It is required alongside `type: 'jsonb'` when the template renders a list: without it the column is a single JSON document, and each row carrying an array is refused with `upsert_row_failed %{: ["is invalid"]}` **while the batch reports itself green**. **`is_array: true` describes the JSON document. It is NOT an instruction to make the Postgres column an array type.** That misreading is the most expensive one available here, because of what it implies for a masked column. A masked `jsonb` column is `jsonb` in the raw lake and **`text` in both copies**, and those types are *supposed* to differ. Logical replication ships the publisher's text rendering and feeds it to the subscriber column's input function: ``` jsonb -> text always succeeds (any jsonb renders to a string, and any string is valid text) jsonb -> text[] always fails ([{"code":"A1C"}] is JSON, and NOT a Postgres array literal) ``` So the invariant is **scalar-ness, not type parity**: a jsonb column is never a Postgres array type, in any lake. Get this wrong and the apply worker stops on the first row carrying an array — and the failure has no error surface a client can see. You get derived lakes that exist, report `status: 'ready'`, and never fill. Because the copies hold text, a masked jsonb column comes back as a masked *string* rather than as an array, and it will not parse as JSON. That is expected: on that side the column really is text. The privacy choice is **immutable post-create** — `update()` exists but its migration is additive-only and never re-privacy's an existing column (see §2 update note), so the only way to change a column's `privacy_requirement` is `delete()` + `create()`. **Privacy is operator-decided per column, not implied by name.** A field called `message` can be `redact` in one tenant and `none` in another; the SDK doesn't pattern-match column names to default privacy levels. Pick deliberately at design time — recreating the table is the only way to flip a column's treatment later. ### At least one column MUST be `is_unique: true` The platform requires at least one unique column to identify rows for the data activation upsert path. A body with zero unique columns returns 422 on `/columns` with a message naming the missing-unique-column rule. The unique column is what bound data activation clients use as the upsert key. ### Reserved column names The platform creates every generic table with the same seven base columns, and they are yours to read but never to declare in `columns[N].name`: ``` id uuid primary key inserted_at timestamp updated_at timestamp checksum the row's content fingerprint checksum_changed whether the last write changed it legal_entity_id the subject the contract's MDM resolve stamped alvera_data_activation_log_id the data_activation_logs row of the ingest that wrote the row ``` **Where the batch, the client, the user and the source went.** A row does not carry them. It carries `alvera_data_activation_log_id`, a pointer to the `data_activation_logs` row of the ingest that wrote it, and the batch, the client, the user and the source live on that log row. The pointer is **null on a row nothing ingested** — a row an app wrote straight into the table. To pick out one ingest's rows in SQL, go through the log row: ``` .alvera_data_activation_log_id IN (SELECT id FROM data_activation_logs WHERE batch_id = '') ``` Because those four are no longer platform columns, they are no longer reserved: a table may declare its own `batch_id`, `client_id`, `user_id` or `source_uri`, and it is plain data like any other column. **`id` may be named once: inside the unique combination.** A definition must name a unique combination, and a table is sometimes unique only together with its id — say `(organization_id, id)`. Declaring `id` with `is_unique: true` puts it in that combination and adds no column (every table already has one). Declared any other way, `id` is a reserved name like the rest. And it may join the combination, never be the whole of it: create accepts a table whose only unique column is `id`, but the column sync drops reserved names before it reads the unique columns, finds none, and fails every migrate of the lake, which stays at `new`. Mark a column of the table's own unique. **`legal_entity_id` is the one that catches people.** It is not a column you declare and fill — a contract's `mdm_input_config` resolves the subject and the platform stamps it. Declaring a column of that name does not shadow the stamp silently: the create is REJECTED with 422, and the message names the offending column (`contain reserved system column names: legal_entity_id`). You lose the create, not the linkage — so read the 422 as the platform telling you the column is already there, and drop it from your definition. There is correspondingly **nothing to guard against in a contract hook**: a table that declares the column is never created, so an `assert` on its absence can never fire. There is no longer a per-domain reserved list. GH-859 removed the industry datalakes and their MDM foreign keys, so names like `patient_id`, `party_id` and `customer_id` are now ordinary user-defined columns you may declare freely. Reserved-name rejections surface as 422 on `/columns/N/name`. ### Title cannot start with `alvera_system` The `alvera_system_` prefix is reserved for system-curated generic tables the platform manages internally. A `title` whose slugified form would start with `alvera_system` is rejected at create with 422 on `/title`. Pick a title that slugifies to something else (e.g. avoid leading "Alvera System" in the title text). ### `update()` exists — but the schema migration is ADDITIVE-only There **is** an `update()` (`PUT /generic-tables/:id`, full-replace — the same body shape as `create`, all fields required). It exists so a declarative `alvera apply` can resend a table's full manifest and reconcile drift. On update the platform re-runs the schema migration for the new column set — but the migration is **strictly additive**: - **New columns in the body are added** to the physical table (test-pinned) and to each lake's copy of it. - **Columns you omit are NOT dropped.** The migration never auto-drops a column ("keep down as noop to avoid destructive change"), so a shrunken `columns` array does not remove storage. - **Existing columns are NOT retyped or re-privacy'd.** The additive migration only adds; it does not `ALTER` an existing column's type or `privacy_requirement`. So `update()` is the right tool to **add a column** to a live table without losing data. To **drop or retype** a column you still `delete()` + `create()` fresh — the additive migration won't do it, and column `privacy_requirement` remains immutable in place (§2 privacy note). One validation guardrail: an update that would leave the table with **zero** `is_unique` columns is rejected (422), same rule as create. ## 3. Field ownership **Server-derived (Response-only).** Universal set from `type_naming.md`, plus: ``` name string — `alvera_custom_` status enum — 'new' | 'processing' | 'deployed' | 'stale' (no 'failed' — a failed migration just never reaches 'deployed'; 'stale' = drifted definition) ``` **Caller-supplied (round-trip).** ``` title required string — human-readable label description optional string — narrative columns required array — column definitions (see §2) ``` **Write-only (Request-only).** None. ## 4. Error envelopes Standard JSON:API envelopes per `errors.md`. Common rejections: | `source.pointer` | Cause | |----------------------------------|----------------------------------------------------| | `/title` | empty; generates a name colliding with existing; or starts with `alvera_system` prefix | | `/columns` | empty array; duplicate `column.name`; zero columns marked `is_unique: true`; or a RESERVED column name (see §2) | | `/columns/0/name` | too short/long, or not matching `[a-z][a-z0-9_]*` | | `/columns/0/type` | value not in supported type enum (see §2 column types) | | `/columns/0/privacy_requirement` | value not in enum, OR `tokenize` on a `jsonb` column | **Reserved names point at `/columns`, not at the offending index** — measured, not inferred. The check runs on the parent changeset (`add_error(changeset, :columns, …)`), so it names *every* offending column at once rather than the first it meets: ``` alvera: plan halted at generic_table "lead-submissions": server rejected request: /columns: contain reserved system column names: legal_entity_id ``` Note where that halted: at **plan**, not apply — the server answering during plan's refresh, before any mutation. The per-column rows above are indexed because those validations run on the column embed itself. All of these come back as a 422 `AlveraApiError` thrown from the SDK on submit — structural cases (Layer 1) and semantic conflicts like name collision / deployed-name reuse (Layer 2). The SDK does not validate client-side; see `errors.md`. ## 5. Lifecycle Two-phase create, parallel to datalakes (see `datalakes.md` §5): ``` .create ──→ status: 'new' or 'processing' │ │ (async migration job — see async.md) ▼ status: 'deployed' ◄── safe to bind / query ``` ### Create + poll ```typescript const { data: created } = await api.genericTables.create( tenantSlug, datalakeSlug, body, ) // created.status === 'new' (or 'processing' if worker raced) const deadline = Date.now() + 60_000 while (Date.now() < deadline) { const { data: row } = await api.genericTables.get( tenantSlug, datalakeSlug, created.id!, // UUID id, NOT created.name ) if (row.status === 'deployed') break await new Promise((r) => setTimeout(r, 1_000)) } ``` **The status enum has no terminal-failure value** — it is `new → processing → deployed` (plus `stale`, an already-deployed table whose definition drifted; never a fresh-create state). A migration that fails does not flip to a `failed` status; the row simply never reaches `deployed`, so bound the poll with a **timeout** (as above) rather than watching for a failure value. (This is the same timeout-bounded idiom every validated cookbook runs, passing the UUID `id`.) `.get(id)` distinguishes "row never created" (404) from "deployment in progress" (200 with a sub-`deployed` status) — two distinct signals on the same endpoint. Use the 404 to short-circuit retry loops; use the 200+status to drive the poll above. ### Read shapes ``` .list(tenantSlug, datalakeSlug, query?) Paged: { data, meta } (full Flop query — type_naming.md) .get(tenantSlug, datalakeSlug, id) One row, by UUID id (NOT name/slug; resolve a name → id via list + handle filter) ``` Plus the polymorphic dataset surface for per-table markdown: ```typescript const { data: md } = await api.datasets.metadataDetails( tenantSlug, datalakeSlug, 'generic_table', { genericTableId }, ) // md: string — agent-facing markdown describing the table's schema ``` For the whole-domain catalog (every system + generic table type registered to this datalake) use the sibling `api.datasets.metadata(tenantSlug, datalakeSlug)` — same shape as `tools.metadata` / `dataSources.metadata`. See `datalakes.md` §5 `.systemDatasets` for the system-dataset sibling of this verb; the `'generic_table'` kind is the entry point for generic-table metadata. ### Update `update(tenantSlug, datalakeSlug, id, body)` — `PUT`, full-replace (same body shape as `create`, addressed by **UUID `id`**, not `name`). Re-runs the schema migration for the new column set, **additively**: new columns are added; omitted columns are not dropped; existing columns are not retyped (see §2). Its intended use is declarative drift reconciliation (`alvera apply` resends the whole manifest) and adding a column to a live table without data loss. Rejected (422) if the new column set has no `is_unique` column. It writes the schema, not rows (rows still flow through the DAC — see the box at the top). ### Delete `delete()` removes the row and drops the physical table. Tables bound to active data activation clients, workflows, or interoperability contracts reject with a 422 naming the dependency. Detach dependents first. ## 6. Gotchas 1. **`.get` / `.update` / `.delete` take the UUID `id`, not `name`.** The API path key is the UUID `id`, and downstream bindings (`generic_table_id` on workflows / DACs / contracts) are that same `id`. The `name` (`alvera_custom_`) is the *physical SQL table name* — for `executeSql` and for recognising the table's auto-provisioned DAC — not an API key. Stash **both** `data.id` and `data.name` from create: `id` for CRUD + bindings, `name` for SQL. Passing `name` to `.get` 422s (`Invalid format. Expected :uuid`); resolve `name → id` via `list()` with a `handle` filter (§2). 2. **`status` reaches `deployed` asynchronously.** A bare `.create` response with `status: 'new'` is NOT yet bindable. The poll loop above is non-optional for any spec that creates a generic table and immediately uses it. A 60-second timeout floor survives a saturated worker queue on a busy box; bump for automated test environments with slower migrations. 3. **Column `privacy_requirement` is immutable post-create.** `tokenize` columns surface as an opaque stable token in the tokenized lake as a stable token; `redact` masks it in both copies; `none` passes the real value through to all three unchanged. `update()`'s additive migration never re-privacy's an existing column, so recreate the table to change one. 4. **System-dataset enumeration EXCLUDES generic tables.** `api.datalakes.systemDatasets(...)` returns only the industry-built name list; operator-defined generic tables surface only via `api.genericTables.list(...)`. The literal string `"generic_table"` is also absent from the system-datasets enum — see `datalakes.md` §5. 5. **Title-collision returns at create time.** Two generic tables cannot share a `title` that slugifies to the same `name` within one datalake. The 422 surfaces on `create()`, well before the migration job runs. 6. **Generic tables can carry agentic write-back columns.** They are not exclusively ingestion targets. Columns the data activation client fills at ingestion (e.g. `submission_id`, `name`, `email`) sit alongside columns reserved for downstream resources to update (e.g. a reply column written by a workflow, or a scoring column written by an agent). Plan the column set for the full lifecycle — though you CAN **add** a forgotten column later via `update()`'s additive migration (§2), without losing data. Dropping or retyping still needs delete + recreate. 7. **A new generic table auto-provisions a default manual-upload DAC — ingest through it, don't create one.** Alongside the identity contract (see `interoperability_contracts.md` §5), the platform stands up a default data activation client named `"
DataActivationClient"` (`is_default: true`) for every generic table, so a table is immediately ingestable with no extra declaration. To seed rows, discover that default DAC by name/`is_default` and `.ingest` through it — never hand-create a second DAC (untracked, non-idempotent, collides on re-run). See `data_activation_clients.md` §2.