# Datalakes

A **datalake** is the storage layer for one tenant slice: one
Postgres database — a writer profile and a reader profile onto it
— plus one cloud-storage bucket.

Masking is **not** a second database under the same roof. A
datalake carries a `type` (`raw`, `tokenized`, or `redacted`), and
the derived tiers are *separate datalake rows* pointing back at the
raw one through `primary_datalake_id`, filled by replication rather
than written directly. §2b covers which tier to read, and why
reading the wrong one is the subtlest bug in this guide.

Every other resource in the platform (data sources, tools, AI
agents, workflows, etc.) scopes under a datalake. A tenant can own
many datalakes; group them however the tenant's data actually
divides.

```typescript
import type { PlatformApi } from '@alvera-ai/platform-sdk'

const api: PlatformApi = /* see AGENTS.md */
const { data: created } = await api.datalakes.create(tenantSlug, body)
const datalakeSlug = created.slug    // canonical — use for downstream scoping
```

SDK namespace: `api.datalakes`.

## 1. Wire shape

The request type splits across two layers: top-level database +
identity fields, plus one polymorphic cloud-storage embed.

```typescript
import type {
  DatalakeRequestWritable,
  DatalakeResponse,
} from '@alvera-ai/platform-sdk'
```

See `type_naming.md` for the `Writable` convention and the
universal server-derived field set. (The SDK exports types only —
there is no runtime validator; validation is server-authoritative.)

### Polymorphic cloud-storage embed

`cloud_storage` is polymorphic. The discriminator field is
**`cloud_storage_type`**
(see `mutations.md` "Polymorphic discriminator naming") with three
branches:

| `cloud_storage_type` | TypeScript branch                       |
|----------------------|-----------------------------------------|
| `"aws"`              | `DatalakeCloudStorageAwsRequest`        |
| `"r2"`               | `DatalakeCloudStorageR2Request`         |
| `"custom"`           | `DatalakeCloudStorageCustomRequest`     |

Each branch carries its own field set; the shared keys are
`bucket`, `region`, `access_key_id`, `secret_access_key`. `custom`
adds `endpoint` (used for LocalStack and self-hosted S3-compatible
stores).

The `aws` branch also carries an `auth_method` field —
`"access_key"` (default) or `"iam_role"` — that pairs with the
credentials the same way the DB-profile `auth_method` does. See
§2 "AWS cloud-storage `auth_method = iam_role` forbids
credentials" for the conditional the type cannot encode.

There is one embed, not a pair. The old
`unregulated_cloud_storage` / `regulated_cloud_storage` pairing
died with the two-tier lake — a derived lake is its own datalake
row and carries its own `cloud_storage`.

### Cloud storage is also the cold-archive destination

Beyond being the platform's primary blob store for inline payloads
(image uploads, large context payloads, presigned-URL ingest), the
configured `cloud_storage` bucket is the **cold-archive
destination** for the datalake. As
data flows through the pipeline, two NDJSON streams land under
well-defined key prefixes inside the bucket the credentials point at:

```
   {datalake_slug}/data-activations/{client_slug}/{batch_id}/{dataset_table}.ndjson
   {datalake_slug}/workflows/{wf_slug}/merged/{batch_id}.ndjson
   {datalake_slug}/workflows/{wf_slug}/merged/{batch_id}/{decision_key}.ndjson
```

The merged NDJSONs are produced by background jobs
after each Data Activation Client batch and each workflow run
completes. The platform never streams the merged bytes through its
own process — the merge happens via DuckDB's `httpfs` extension
operating directly against the bucket.

The same DuckDB-over-`httpfs` pattern is available to **consumers**
for cold-data analytics. With the datalake's cloud-storage
credentials (or a separately-provisioned reader key on the same
bucket), an external DuckDB process can read the archived NDJSONs
directly:

```sql
INSTALL httpfs; LOAD httpfs;
CREATE OR REPLACE SECRET (TYPE S3, KEY_ID '...', SECRET '...', ENDPOINT '...');
SELECT * FROM read_ndjson_auto(
  's3://{bucket}/{datalake_slug}/data-activations/{client_slug}/*/{dataset_table}.ndjson'
);
```

The platform does NOT expose a query API over the archive itself —
cold reads happen externally so the platform's processes aren't
responsible for analytical workloads. Live data lives in the
datalake's Postgres halves (queryable via the dataset search
two-step pattern in `data_activation_clients.md` §6.6); historical
data lives in the cloud-storage buckets configured here.

### Canonical create body

Paste-ready TypeScript body. There are two database-role groups —
writer and reader — sharing an identical eight-field shape; the
type lists all 16 explicitly with no role-level union.

```typescript
import type { DatalakeRequestWritable } from '@alvera-ai/platform-sdk'

// Secrets resolved upstream by your code (secret store, config loader, etc.)
const pgHost = '...'
const pgUser = '...'
const pgPass = '...'
const pgReaderHost = '...'
const pgReaderUser = '...'
const pgReaderPass = '...'
const awsAccessKeyId = '...'
const awsSecretAccessKey = '...'

const body: DatalakeRequestWritable = {
  name: 'Production Lake',
  description: 'Primary datalake',
  timezone: 'America/New_York',
  pool_size: 5,

  // `type` defaults to 'raw' — the lake the platform writes.
  // A derived lake sets type: 'tokenized' | 'redacted' AND
  // primary_datalake_id; see §2b before creating one.
  type: 'raw',

  db_writer_host: pgHost,
  db_writer_port: 5432,
  db_writer_name: 'alvera_production',
  db_writer_schema: 'tenant_acme',
  db_writer_auth_method: 'password',
  db_writer_user: pgUser,
  db_writer_pass: pgPass,
  db_writer_enable_ssl: true,

  db_reader_host: pgReaderHost,
  db_reader_port: 5432,
  db_reader_name: 'alvera_production',
  db_reader_schema: 'tenant_acme',
  db_reader_auth_method: 'password',
  db_reader_user: pgReaderUser,
  db_reader_pass: pgReaderPass,
  db_reader_enable_ssl: true,

  // One bucket. Scope multiple lakes onto a shared bucket with
  // `base_path` rather than provisioning a bucket per lake.
  cloud_storage: {
    cloud_storage_type: 'aws',
    region: 'us-east-1',
    bucket: 'acme-platform',
    base_path: 'production',
    access_key_id: awsAccessKeyId,
    secret_access_key: awsSecretAccessKey,
  },
}

const { data: created } = await api.datalakes.create(tenantSlug, body)
// created.id, created.slug    — server-derived; use slug for downstream scoping
// created.status === 'new'    — migration NOT yet started; see §5 Lifecycle
```

## 2. Rules the type cannot encode

### `auth_method = "iam_role"` relaxes the password requirement

All eight `*_pass` fields are typed as required strings. When a
role's `auth_method` is `"iam_role"`, its matching `*_pass` value
is **server-cleared at write time** — the value submitted is
ignored. The typed validator does not encode this conditional, so
the field is still structurally required.

The convention is to supply a sentinel literal (e.g.
`"unused-iam-role"`) so a reviewer sees intent. **Real credentials
do not belong in this slot** — the value never leaves the
validator.

### AWS cloud-storage `auth_method = iam_role` forbids credentials

Mirrors the DB `auth_method` rule above but with the **opposite
disposition** on the credential fields. Inside an AWS cloud-storage
embed:

- `auth_method: "access_key"` (default) — `access_key_id` and
  `secret_access_key` are **required**.
- `auth_method: "iam_role"` — `access_key_id` and
  `secret_access_key` MUST be omitted. Supplying either yields a
  field-level rejection at create. Unlike the DB case, the values
  are NOT silently server-cleared — the platform treats their
  presence as a configuration conflict.

There is a single embed, so this conditional is evaluated once
per body.

### Database identifiers follow strict format rules

The two `db_*_schema` and two `db_*_name` fields must be
lowercase identifiers (letters, digits, underscore) — uppercase
letters and dashes are rejected with a `/db_<role>_schema` or
`/db_<role>_name` pointer. Both `db_*_host` fields must match
a hostname pattern; values like `"invalid_host!"` (with
non-hostname punctuation) are rejected.

This is the only field-format constraint surfaced before the
synchronous reachability probe runs (see §8 Gotcha 9) — a
malformed identifier never gets as far as a connection attempt.

### Schema names must be unique within a database

`db_*_schema` fields name the PostgreSQL schema the platform
creates inside the target database. Sibling datalakes sharing a
`db_*_name` (same physical database) **must** use distinct
schema values. Collision produces a 422 at create time.

The reader and writer normally share a schema — they are two
connection profiles into the same logical schema. A derived
(tokenized or redacted) lake is a separate datalake row and so
brings its own schema; it must not collide with its raw parent's.

### The discriminator can't change on `update()`

Per `mutations.md`, `cloud_storage_type` on update must match the
existing row's discriminator. To change the embed type (e.g. `aws`
→ `r2`), `delete()` and `create()` fresh — you can't change body
type on `update()`.

## 2b. The three lakes, and which one to read

A datalake is not one database. It is three, and the other two are
**derived**: fully managed, read-only, and written only by
replication from the raw lake.

```
   raw          every column, every real value.        the superset.
    │
    ├── tokenized    columns declared privacy_requirement: "tokenize"
    │                come back as a token; everything else passes through.
    │
    └── redacted     tokenize AND redact columns are scrubbed.
```

The tier is chosen per call, not per datalake — `mode` on
`executeSql`, `dataAccessMode` on a dataset search. Nothing about
the datalake decides it.

### How the copies get turned on

Nothing in this SDK turns them on directly, and that is worth stating
before the mechanism, because the field involved is one you cannot send.

**Through the `alvera` CLI it is one boolean on the datalake block:**

```toml
[datalakes.my-lake]
enable_tokenization = true
```

`enable_tokenization` is a **CLI-only manifest key**. The CLI strips it
before the request reaches the API — there is no such field on
`DatalakeRequest`, and adding one to an SDK call does nothing. What it
drives is a separate endpoint, `POST /datalakes/<slug>/tokenization`,
which the CLI calls on your behalf after the raw lake reaches `ready`.
Calling that endpoint yourself is the SDK-level equivalent.

Four things about it that are not guessable from the field:

**Both copies or neither.** They are created, destroyed and billed
together. There is no way to ask for a tokenized lake without a redacted
one.

**Order is handled for you, and it matters.** A derived lake's own
migration creates its tables and starts the copy, so the raw lake must be
`ready` first. `apply` turns the copies on after that, then waits for
both to reach `ready` themselves.

**It only goes one way.** Nothing turns tokenization off — no endpoint
deletes a derived lake — which is why the platform declined to make it an
authored field in the first place: `true → false` would let a caller
declare a state the platform cannot honour.

**`[tokenization.<slug>]` is retired.** If you meet a manifest carrying
that block, it is pre-0.18 and the CLI now refuses it by name.

### What turning them on actually requires

One boolean, three prerequisites — and the platform **probes** each and
**refuses** when it is missing. It never issues `CREATE DATABASE` or
`CreateBucket`, deliberately: a platform that can create one can create
it in the wrong place, and a datalake's storage belongs to the operator.

**1 · Two more databases.** Named from the raw lake's own database name
with the mode appended — `<raw>_tokenized` and `<raw>_redacted`. Postgres
truncates an over-long identifier *silently*, which would land two
derived lakes on one physical database, so a name that would exceed the
63-byte identifier limit is refused rather than truncated. Long raw
database names are where that bites.

**2 · On two more Postgres servers.** A derived lake does not live beside
the raw one. Locally the tokenized copy is on `:5433` and the redacted on
`:5434`, beside the raw lake on `:5432`. Separate servers on purpose: a
derived lake runs `postgresql_anonymizer` and PL/Python, which the raw
lake — holding the real values — must not have.

**3 · Three buckets, not one.** The raw lake's bucket plus
`<bucket>-tokenized` and `<bucket>-redacted`.

**The failure mode is worth knowing in advance, because it is
mis-attributed by default.** A missing copy bucket is reported against
the **primary** lake, naming the lake rather than the bucket that is
actually absent — and it surfaces minutes into a create, as a 422 that
reads like a platform defect. It has been diagnosed as one. If a create
fails on cloud storage for a lake declaring the copies, check that all
three buckets exist before looking anywhere else.

### What decides what a reader sees

**`privacy_requirement` on the column.** That single declaration is
the whole masking policy — `tokenize` for PII, `redact` for free
text, `none` to pass through. There is no second schema to write
sensitive values into, and no changeset quietly masking on the way
past: the raw value goes in, and the tier the reader asked for
decides what comes out. Dropping or changing that declaration
silently changes who can see what.

### Which tier to read

**Read `raw` unless you have a reason not to.** It is the superset:
every column is present with its real value, so any assertion about
what landed — a row exists, a foreign key was stamped, a count is
right — is answerable there. It also does not depend on replication
having caught up, which matters more than it sounds like: a derived
lake is populated asynchronously, so a read against one is a question
about *replication* as much as about your data. Every read-back in
the cookbook corpus reads `raw` for exactly this reason.

**Read `tokenized` when the point IS the masking** — proving a
`tokenize` column really is masked for a consumer that should not
see it, or reproducing what an AI agent sees. An agent's
`data_access` is a separate, stricter thing: it takes
`'raw' | 'tokenized'` only, because an agent is never pointed at the
redacted lake.

**Read `redacted` when you are checking the strictest projection.**

### Two traps

**A derived lake is not there until it is provisioned.** A datalake
is created as the raw tier alone; the tokenized and redacted copies
are provisioned separately and migrate on their own jobs. A read
against a tier that does not exist yet fails with a missing-relation
error, not an empty result.

**Filter and assert on a `none` column.** In `tokenized` a
`tokenize` column returns its token, so a `WHERE` on one matches
nothing and an equality assertion on one compares a token to a
plaintext value. Pick a `none` column — a business key, a status —
for anything you filter or assert on.

### Has the copy actually arrived?

Because a derived lake is populated asynchronously, "the copy
exists" and "the copy is complete" are different questions.
`derivedLakeTables` answers the second, per table.

```typescript
const { data: tables } = await api.datalakes.derivedLakeTables(
  tenantSlug,
  datalakeSlug,   // the PRIMARY lake — see the addressing note below
  'tokenized',    // which copy: 'tokenized' | 'redacted'
)

for (const t of tables.data) {
  // `caught_up` is the answer. `label` is the same state in words.
  console.log(t.table, t.label, t.caught_up, t.resync_empties)
}

const behind = tables.data.filter((t) => !t.caught_up)
```

**You address a derived lake by naming its PRIMARY lake plus a
mode.** A derived lake does have a slug of its own, but that slug is
not an address: the lookup resolves primaries only and answers `404`
for a derived slug deliberately. Passing the tokenized lake's own
slug is not a working alternative spelling.

**Read `caught_up`, never the state's name.** There are five states,
and one of them reads as finished without being finished:

| `state` | `caught_up` | what it means |
|---|---|---|
| `waiting_to_start` | `false` | queued |
| `copying` | `false` | first pass running |
| `finished_copy` | **`false`** | first pass done — changes made *during* it are NOT applied yet |
| `catching_up` | `false` | applying those changes |
| `caught_up` | **`true`** | arrived |

`finished_copy` is how a lake gets called ready while rows are still
missing. Only `caught_up` means arrived, and the boolean is there so
you never have to pattern-match the word.

`label` is that same state in words, identical to what the
platform's own Tokenization screen shows. Use it verbatim rather
than inventing a second vocabulary — a table reading "Copying" in
one surface must not read something else in another.

`resync_empties` says what repairing *that* table would empty, so a
surface can state the cost before offering the action. It is `null`
for tables already caught up — the platform does not spend a query
per table answering a question about tables nobody will repair — and
an array otherwise, with the named table first. **`null` and `[]`
are different answers**, and `[]` never occurs, since a table is
always in its own set. Type it as optional-nullable, not as an empty
array default.

### Repairing one table

When a table is stuck or wrong, `resyncTable` empties it in the copy
and copies it again from the start.

```typescript
const { data: repair } = await api.datalakes.resyncTable(
  tenantSlug, datalakeSlug, 'tokenized',
  { table: 'legal_entities' },
)

repair.emptied  // EVERY table this emptied — often more than the one asked for
repair.status   // always 'copying'

// 202 = started. There is no completion callback, so poll.
let done = false
while (!done) {
  const { data: after } = await api.datalakes.derivedLakeTables(
    tenantSlug, datalakeSlug, 'tokenized',
  )
  done = after.data.some((t) => t.table === repair.table && t.caught_up)
}
```

**It can empty more than the table you named.** Postgres refuses to
truncate any table with an inbound foreign key — whether or not the
referencing tables hold rows — so repairing a parent empties its
whole dependent closure, transitively, in one statement. All of them
are re-copied by the same run. On real data one request has reached
nine tables. `emptied` is returned rather than assumed for exactly
this reason: a caller that emptied nine tables should be told nine.
Read `resync_empties` first if you are going to ask someone to
confirm.

**`202` means started, not done.** The repair empties the table and
a fresh first pass begins; it finishes later. Poll
`derivedLakeTables` until that table reads `caught_up`.

**It does not roll back.** A failure part-way leaves the table empty
and says so. That is deliberate — the rows to put back are the ones
that broke the copy in the first place. The recovery is to call it
again once the cause is addressed; the endpoint handles the case
where a previous attempt left the table off the publication.

**The table name is gated.** `table` must be one the copy carries or
one the original can send. Anything else is a `422` and **nothing is
emptied** — that gate is what stands between a typo and an emptied
table. Read `derivedLakeTables` for the list rather than
constructing names.

The `422` bodies here are sentences written for a person, not codes
— a lake with no such copy, a name that is neither carried nor
sendable, a dependent that could be emptied but not copied back
(ending `Nothing has been emptied.`). Surface them; do not parse
them.

## 3. Field ownership

Datalake's fields split across three POV-relevant categories —
distinguishing them by whether they round-trip determines how a
consumer constructs the PUT body (see §5 Update).

**Server-derived (Response-only).** The universal set (`id`,
`slug`, `created_at`, `updated_at`, `status`, `status_reason`)
is documented in `type_naming.md`. Datalake adds no extras here
— but the `status` field has a richer lifecycle than most
resources (see §5).

**Caller-supplied (round-trip).** Present on both
`DatalakeRequestWritable` and `DatalakeResponse`:

```
identification         name, description
tiering                type, primary_datalake_id
global settings        timezone, pool_size
db profiles (×2)       db_{writer,reader}_{host, port, name,
                               schema, user, auth_method,
                               enable_ssl}
cloud storage          cloud_storage.{cloud_storage_type, bucket,
                                      region, base_path,
                                      + branch-specific fields
                                      per §1}
```

**Write-only (Request-only).** Present on
`DatalakeRequestWritable`, absent from `DatalakeResponse`. The
consumer must re-supply these on every PUT from their secret
store (see §5 Update for the canonical pattern):

```
db credentials (×2)    db_writer_pass
                       db_reader_pass

cloud storage          cloud_storage.access_key_id
  credentials (×2)     cloud_storage.secret_access_key
```

The `slug` returned in the Response is the canonical reference
for all downstream scoping. Never pre-compute it client-side —
see `type_naming.md` "Never pre-compute the slug client-side".

## 4. Error envelopes

The platform returns a standardized JSON:API envelope — see
`errors.md` for the canonical shape (`{ errors: [{ source:
{ pointer }, title, detail }, ...] }`) and the two-layer
validation model (structural cast → server-side semantic check).

All validation errors come back from the server as a thrown 422
`AlveraApiError` (the SDK does not validate client-side). See
`errors.md` for the typed narrowing pattern.

Datalake-specific common rejections:

| `source.pointer`                                | Typical cause                                        |
|-------------------------------------------------|------------------------------------------------------|
| `/name`                                         | name collision within tenant (uniqueness)            |
| `/type`                                         | not one of `raw` / `tokenized` / `redacted`          |
| `/primary_datalake_id`                          | missing on a derived lake, or supplied on a raw one  |
| `/cloud_storage/cloud_storage_type`             | discriminator value doesn't match any oneOf branch   |
| `/db_writer_schema`                             | schema-name collision within the target DB; or identifier format violation (uppercase / dash) |
| `/db_writer_name`                               | identifier format violation                          |
| `/db_writer_host`                               | hostname format violation, OR synchronous probe could not reach the host |
| `/cloud_storage`                                | synchronous probe could not reach the bucket with the supplied credentials |
| `/cloud_storage/access_key_id`                  | supplied under `auth_method: "iam_role"` (must be omitted) |
| `/pool_size`                                    | out of the inclusive range 1–50                      |
| `/timezone`                                     | not in the closed 8-zone US whitelist (valid IANA values like `UTC` are also rejected — see gotcha 11) |

For polymorphic embeds, the `cloud_storage_type` value is the
first thing to inspect — it routes downstream field validation to
one of the three branches.

## 5. Lifecycle

Datalakes have the richest lifecycle of any platform resource.
The status enum (wire-level values):

```
new         POST returned; migration not yet started
processing  platform provisioning the per-datalake schemas and
              supporting infrastructure
ready       both DBs migrated; downstream Datalake-DB-resident
              resources can be created
```

The platform console UI renders `processing` as the label
"Migrating..." — the wire-level enum value is `processing`.
Code that switches on status MUST use the wire values, not the
UI labels.

The status enum has NO terminal failure value. If a migration
fails, the datalake remains in `processing` (or `new` if it
never started); failures surface in the admin console's Migration
Logs view, which is not exposed over the SDK. Consumers polling
for readiness must therefore rely on a sensible timeout — see §5.

### Create

```typescript
const { data: created } = await api.datalakes.create(tenantSlug, body)
// created.status === 'new'
// created.slug   === '<server-derived>'
```

`create()` returns immediately with `status: "new"`. **Migration is
not auto-enqueued** — the caller explicitly invokes it.

### Migrate (explicit, async)

```typescript
const { data: enqueued } = await api.datalakes.migrate(
  tenantSlug,
  created.slug,
)
// enqueued.status      === 'enqueued'
// enqueued.job_id      === <number>
// enqueued.datalake_id === created.id
```

`api.datalakes.migrate(...)` enqueues the schema-creation job. The
response is the *job acknowledgement* (`status: "enqueued"`), NOT
the datalake row — it carries `job_id` + `datalake_id` +
`enqueued_at` only. To learn the datalake's live status, GET
separately:

```typescript
const { data: dl } = await api.datalakes.get(tenantSlug, created.id)
// dl.status === 'new' | 'processing' | 'ready'
```

**`datalakes.get` keys on the UUID `id`, not the slug** (the facade
signature is `get(tenantSlug, id)`, and the validated setup cookbooks
poll with `ctx.datalakeId`). Passing the slug 422s with `Invalid
format. Expected :uuid`. Stash `created.id` from the create response.

Use the canonical `waitUntilReady` from `async.md`:

```typescript
const dl = await waitUntilReady(
  () => api.datalakes.get(tenantSlug, created.id),
  (status) => status === 'ready' ? 'ready' : 'pending',
  { intervalMs: 15_000, timeoutMs: 5 * 60_000 },
)
```

Local-dev migration takes seconds to a minute; remote deployments
can run several minutes. **Most** downstream resource creates
must wait for the datalake to reach `ready` — but the rule has
one exception worth understanding.

Downstream resources split into two categories by storage
location:

| Where the resource's state lives | Examples                                       | Wait for `ready`? |
|----------------------------------|------------------------------------------------|-------------------|
| Platform DB (tenant-wide)        | Data sources                                   | No                |
| Per-datalake DB (tenant + datalake schemas) | Tools, AI agents, generic tables, interoperability contracts, data activation clients, workflows | **Yes**           |

Data sources can be created against a `new`-status datalake
because their metadata writes to the platform DB, not the
per-datalake schemas. Everything else needs the per-datalake
schemas in place — creating them against an unready datalake
produces server-side errors (table-not-found, capability-gate,
etc.) that are recoverable but indicate a missing wait.

In practice: a sibling datalake left unmigrated is still a valid
target for data-source creation, since data sources are
platform-DB-resident and don't depend on per-datalake readiness.

### Update

`update()` replaces the whole resource — resupply the full body
(see `mutations.md`). Common pattern: get current → re-supply
write-only fields from your secret store → mutate target fields
→ pass the whole shape to `update()`. Changing the
cloud-storage discriminator is rejected — `delete()` and
`create()` fresh instead.

**`DatalakeResponse` omits write-only fields.** Eight fields on
`DatalakeRequestWritable` — the four `*_pass` fields plus both
cloud-storage embeds' `access_key_id` / `secret_access_key` — are
write-only: present on the request type, absent from the response
type. A naive spread of `current` into the update body is therefore
missing those 8 required fields. The server rejects this with a 422
`AlveraApiError` pointing at the missing fields (see `errors.md`).
Re-supply them explicitly from your secret store:

```typescript
const { data: current } = await api.datalakes.get(tenantSlug, datalakeId)
// `current` is a DatalakeResponse — write-only fields (DB passwords +
// cloud-storage secrets) are not present on the response type.

const next = {
  ...current,

  // Re-supply every write-only field from your secret store:
  db_writer_pass: pgPass,
  db_reader_pass: pgReaderPass,
  cloud_storage: {
    ...current.cloud_storage,
    access_key_id: awsAccessKeyId,
    secret_access_key: awsSecretAccessKey,
  },

  // Mutate your target fields:
  description: 'Now with revised description',
}

const { data: updated } = await api.datalakes.update(
  tenantSlug,
  datalakeId,
  next as unknown as DatalakeRequestWritable,   // see the note below
)
// updated.id          === datalakeId    — path-key stable
// updated.description === 'Now with revised description'
```

The canonical pattern: caller re-supplies the write-only DB +
cloud-storage fields verbatim, mutates only what changes (e.g.
`description`). The four write-only fields are enumerated in
§3 Field ownership.

**`update()` fires server-side connectivity probes before persisting.**
Submitting a body produces a successful 200 only after two
probes pass:

- **Cloud-storage probe** — verifies the bucket exists + the
  supplied `access_key_id` / `secret_access_key` (or IAM role)
  can list it.
- **DB-connection probe** — verifies the platform can connect
  to the supplied host:port with the supplied user + auth
  method.

A body that's structurally valid but carries stale or wrong
credentials therefore fails at `update()` time, not at later
write time. The 422 envelope (see `errors.md`) names the failing
probe via `source.pointer` (e.g. `/cloud_storage` or
`/db_writer_host`). This makes drift-then-update
workflows fail fast and locally rather than mid-pipeline.

Most field changes do NOT re-trigger migration. Schema-name or
auth-method changes DO — the status reverts to `processing` and
downstream operations must wait again.

`api.datalakes.update(...)` accepts the datalake's `id` OR `slug`
as the path key (same as `.get`); both are stable across migrate
status changes.

### Delete

`delete()` requires the datalake to be empty of dependent resources
(no data sources, tools, AI agents, workflows scoped under it). A
non-empty datalake `delete()` returns a 422 with a `source.pointer`
naming the blocking dependency type. Cleanup is operator-driven in
most deployments.

### Read shapes

`api.datalakes` exposes five distinct read methods, each
returning a different shape:

| Method                               | Returns                                                     |
|--------------------------------------|-------------------------------------------------------------|
| `.list(tenantSlug, query?)`          | Paged list: `{ data: DatalakeResponse[], meta }` — full Flop query (type_naming.md) |
| `.get(tenantSlug, id)`               | One row: `DatalakeResponse` — UUID only (slug 422s)         |
| `.metadata(tenantSlug, slug)`        | **Markdown string** describing the lake                     |
| `.indexMetadata(tenantSlug)`         | **Markdown string** listing every lake                      |
| `.systemDatasets(tenantSlug, slug)`  | `{ datasets: string[] }` — platform dataset names             |

The two `metadata` methods return `string`, not a structured
object — they're agent-facing summaries (`data: string`, not `data:
{...}`). Test the shape with `typeof data === 'string'`.

`.indexMetadata(tenantSlug)` body begins with `# Datalakes on
tenant` and lists every datalake by slug — useful for agent-side
discovery without paging through `.list`.

`.systemDatasets(...)` enumerates the dataset names the platform
auto-creates for the datalake. GH-859 removed the per-industry
catalogs: every datalake now gets the same platform-defined set
(`legal_entity`, `message`, `action_log`, `document`,
`beneficial_owner`), and the subject-specific tables an industry
used to bring are modelled as generic tables instead. Still
discover the catalog at runtime rather than hardcoding it —
the set is server-owned and this call is the only authority on
it. The array is sorted, unique, and **excludes
operator-defined generic tables** — those live under
`api.genericTables.list(...)`. The
literal placeholder `"generic_table"` is also absent. Each name
can be drilled into via
`api.datasets.metadataDetails(tenantSlug, datalakeSlug, name)` for
agent-facing markdown, or the whole-domain catalog can be fetched
in one shot via `api.datasets.metadata(tenantSlug, datalakeSlug)`. Pair `.systemDatasets()` with a
`page_size: 1` probe per name as a post-migration smoke (proves
each platform-built table is queryable; the post-ready probe
code is in §8 Gotcha 3).

```typescript
const { data: catalog } = await api.datalakes.systemDatasets(
  tenantSlug,
  datalakeSlug,
)
// catalog.datasets: sorted, unique array of platform dataset names

for (const name of catalog.datasets) {
  const { data: md } = await api.datasets.metadataDetails(tenantSlug, datalakeSlug, name)
  // md is a string — agent-facing markdown describing the dataset
}
```

### Talk to data — `textToSql` + `executeSql`

Two query methods turn the datalake into a BI surface: natural language → SQL →
rows. They are **split on purpose** so an agent or UI can show, edit, and re-run
the SQL between the two calls.

| Method                                                     | Returns                                                   |
|------------------------------------------------------------|-----------------------------------------------------------|
| `.textToSql(tenantSlug, datalakeSlug, body)`               | `{ sql, model, provider, explanation }`                   |
| `.executeSql(tenantSlug, datalakeSlug, body, options?)`    | `{ data, meta }` — or a CSV **string** with `{ format: 'csv' }` |

- **`textToSql`** body is `{ prompt, mode }` (`mode` ∈ `'raw' | 'tokenized' |
  'redacted'`, selecting which lake tier to target — see §2b). It runs ordered multi-provider LLM failover and
  returns the generated `sql`, the winning `model` + `provider`, and a best-effort
  `explanation` (`null` when the explainer is unavailable). **Only the prompt + the
  schema cross the LLM boundary — never datalake rows**, so it is safe in both modes.
  It returns SQL for review; it does **not** execute it. The review step is
  load-bearing, not ceremony: the model can emit SQL that parses but fails
  live — e.g. `WHERE some_boolean = 1` 422s on Postgres (booleans compare to
  `true`/`false`, never `1`). Surface the SQL to a human (or a checking step)
  and expect to edit before `executeSql`.
- **`executeSql` returns rows as POSITIONAL ARRAYS, not objects keyed by
  column.** `data` is `unknown[][]`; the column names are in `meta.columns`, in
  the same order. So `row.legal_entity_id` is silently `undefined` — not an
  error, just absent — and a probe written against it fails with a message
  built from its own bug while the data is perfectly correct. Zip them:

  ```ts
  const { data: res } = await api.datalakes.executeSql(tenantSlug, datalakeSlug, {
    sql, mode: 'raw',
  })
  const cols = res.meta.columns
  const rows = res.data.map((r) => Object.fromEntries(cols.map((c, i) => [c, r[i]])))
  // now rows[0].legal_entity_id is real
  ```

  Worth knowing before you write a richer check than the scaffolds ship: their
  seeded example only asserts `data.length > 0`, which behaves identically for
  both shapes, so nothing in the example reveals this.
- **`executeSql`** body is `{ sql, mode, page?, page_size? }`. It runs the SQL
  **read-only** (INSERT/UPDATE/DELETE/DDL are rejected) against the
  mode-selected lake tier, with Flop-inspired pagination (`page_size` capped server-side). The JSON
  shape is `{ data, meta }`; pass `options = { format: 'csv' }` to get the page as a
  CSV string attachment instead.

```typescript
const { data: gen } = await api.datalakes.textToSql(tenantSlug, datalakeSlug, {
  prompt: 'count legal entities created this month',
  mode: 'raw',
})
// gen.sql — review / edit before running

const { data: page } = await api.datalakes.executeSql(tenantSlug, datalakeSlug, {
  sql: gen.sql,
  mode: 'raw',
  page: 1,
  page_size: 100,
})
// page.data  — array-of-arrays rows, aligned to page.meta.columns
// page.meta  — { columns, num_rows, total_count, page, page_size, total_pages, duration_ms, command }

const { data: csv } = await api.datalakes.executeSql(
  tenantSlug, datalakeSlug,
  { sql: gen.sql, mode: 'raw' },
  { format: 'csv' },
) // csv is a string, not the JSON envelope
```

## 6. Logical name → physical table

The name you author is not the name you query. Datasets are addressed
**logically** everywhere in a manifest or SDK call, and **physically** in SQL:

For a **generic table**, the physical name is the `name` the server
returns from `genericTables.create` / `.get` — query it directly, with
no prefix and no schema qualifier:

```typescript
const { data: table } = await api.genericTables.create(tenantSlug, datalakeSlug, body)
await api.datalakes.executeSql(tenantSlug, datalakeSlug, {
  sql: `SELECT * FROM ${table.name} LIMIT 1`,
  mode: 'raw',
})
```

Do not reconstruct that name from the slug you authored — the server
derives it, and the derivation is not part of the contract.

For the **platform datasets** (`message`, `action_log`, and the rest of
§5's `systemDatasets` catalog) the physical name and schema are likewise
server-owned. Read them, don't assume them: the physical mapping moved
with the GH-859 tier rename and the old `app_regulated` /
`app_unregulated` schema pair no longer follows from `mode`.

**Don't derive these by hand — read them.** Guessing column and table names is
how six turns get spent on `column "error_message" does not exist` (the real
column is `failure_reason`):

- `alvera get-metadata dataset --only <type>` prints the **physical dataset
  name** plus every column, from the Ecto struct itself.
- `alvera get-metadata generic-table --only <slug>` prints the exact **primary
  table alias** to use in a `WHERE` clause, with a worked `SELECT`.

An `execute-sql` failure on an undefined column, relation, table, or schema now
names these verbs in its error, so the correction path is one command away.

## 7. The roster owns the dedupe uri — don't inject it per source

MDM matches on the full identification triple, so a `source_uri` injected
per data source participates in matching. Two sources describing the same
subject then produce two different triples, and the same subject is written
**twice** instead of being reconciled onto one entity.

Derive the dedupe uri **once, at the roster**, and let every data source inherit
it. Never compute a per-source `source_uri` and expect MDM to see through it.

This divergence is **not visible through `get-metadata`**: the roster value and
the per-source values each look well-formed in isolation, and only the duplicate
rows downstream reveal the mismatch. Check the derivation, not the metadata.

## 8. Gotchas

1. **`create()` does NOT auto-enqueue migration.** The create response
   returns immediately with `status: 'new'`; Datalake-DB-resident
   downstream resources (tools, AI agents, generic tables,
   interoperability contracts, data activation clients,
   workflows) MUST wait for `ready` (see §5 Lifecycle for the
   storage-location split). The canonical sequence:

   ```typescript
   const { data: created } = await api.datalakes.create(tenantSlug, body)
   // created.status === 'new'

   await api.datalakes.migrate(tenantSlug, created.slug!)  // migrate is slug-keyed

   const ready = await waitUntilReady(
     () => api.datalakes.get(tenantSlug, created.id!),     // get is UUID-id-keyed
     (s) => s === 'ready' ? 'ready' : 'pending',
     { intervalMs: 15_000, timeoutMs: 5 * 60_000 },
   )
   // ready.status === 'ready' — safe to POST tools, AI agents, etc.
   ```

2. **`migrate` returns the job ack, not the datalake.** The
   `{ status: "enqueued", job_id, ... }` shape can be mistaken for
   the datalake row. To learn live status, `.get` separately and
   poll per `async.md`.

3. **`status: "ready"` is necessary but not sufficient.** A
   partial-migration failure can leave a datalake in `ready`
   with some tables silently missing — a failure mode that
   otherwise only surfaces mid-pipeline (during downstream
   resource creation or live data ingestion), far from its
   root cause.
   Pair the status check with a post-ready probe:

   ```typescript
   const { data: catalog } = await api.datalakes.systemDatasets(
     tenantSlug, datalakeSlug,
   )
   // catalog.datasets is the platform dataset name list —
   // discovered at runtime; don't hardcode names.

   for (const dataset of catalog.datasets) {
     const { data } = await api.datasets.createUserSearch(
       tenantSlug,
       datalakeSlug,
       dataset,
       { search_query: '1 = 1' },
     )
     if (data.status !== 'completed') {
       throw new Error(
         `post-ready probe failed: dataset=${dataset} status=${data.status}`,
       )
     }
   }
   ```

4. **`id` and `slug` are interchangeable as path keys.**
   `.get`, `.update`, `.migrate`, `.metadata` all accept either.
   The `slug` is human-readable; the `id` is a UUID. Pick one
   convention per code path to avoid mixed references.

5. **Schema-name collisions surface at create, not migrate.** A
   sibling datalake reusing a `*_schema` value gets a 422
   immediately — well before the asynchronous schema-creation job
   would have hit the conflict.

6. **`iam_role` auth still requires a `*_pass` value structurally.**
   The TypeScript type (and the server's schema) treat the field as
   a required string. Supply a sentinel literal (e.g.
   `"unused-iam-role"`); the platform clears it server-side.

7. **`cloud_storage_type` lives inside the embed, not at the
   top level.** The discriminator is
   `cloud_storage.cloud_storage_type` — one per body, since a
   datalake carries one bucket.

8. **`delete()` is rare in practice.** Datalakes accrete dependents
   quickly; routine cleanup is via operator-driven console
   actions, not SDK calls. Treat `.delete` as an integration-test
   teardown method, not a production operation.

9. **Create and update run synchronous reachability probes.** Before
   the row is written, the platform opens connections to every
   declared endpoint — both DB profiles (reader and writer) AND the
   cloud-storage bucket — and only proceeds if every probe
   succeeds. A structurally valid body can still fail with
   field-level rejections on `db_*_host` (unreachable, wrong
   credentials, or SSL-mismatch) or `cloud_storage` (bucket
   inaccessible). The same probe runs on update; an update that
   only changes the datalake's `name` still re-probes if the body
   re-supplies any `db_*_host` or `cloud_storage` field.

   Two consumer-relevant consequences:

   - **Multiple probe failures are surfaced in a single envelope.**
     Two bad reader hosts produce two field-level entries —
     consumer agents iterating on a single response can correct
     several fields per round-trip.
   - **`enable_ssl: true` against a non-SSL database fails the
     probe.** The flag looks client-side but is validated by the
     probe attempting an SSL handshake. Match it to what the
     target database actually supports.

10. **`pool_size` is clamped to the inclusive range 1–50.** The
    typed field accepts any integer; the server rejects values
    outside `[1, 50]` with a `/pool_size` pointer. The error message
    mentions a `greater_than: 0` or `less_than_or_equal_to: 50`
    constraint depending on which side was crossed.

11. **`timezone` is a closed whitelist of 8 US timezones — NOT the
    general IANA set.** Exactly: `America/New_York`, `America/Chicago`,
    `America/Denver`, `America/Los_Angeles`, `America/Anchorage`,
    `America/Adak`, `Pacific/Honolulu`, `America/Phoenix`. Everything
    else is rejected on `/timezone` with `must be one of the supported
    US timezones: …` — including values that ARE valid IANA zones
    (`UTC`, `Etc/UTC`, `Africa/Abidjan`). Do NOT source candidates from
    `Intl.supportedValuesOf('timeZone')`; almost all of its ~400 values
    are rejected. The whitelist is projected as the `timezone` enum in
    the OpenAPI schema, so the generated `DatalakeRequest` type carries
    the legal values and TypeScript consumers get them at compile time.

12. **`auth_method` defaults to `"password"` on every DB profile.**
    Omitting `*_auth_method` or supplying `null` is equivalent to
    `"password"` — and consequently requires both `*_user` and
    `*_pass` to be supplied. The IAM-role relaxation in §2 applies
    only when `auth_method` is explicitly `"iam_role"`.

13. **`executeSql` rows are array-of-arrays, not objects.** `data` is
    `Array<Array<unknown>>` — a positional row page with **no column
    keys**, because arbitrary SQL can yield duplicate or expression
    column names that have no safe object key. Read column names from
    `meta.columns` and index rows positionally (`row[i]` ↔
    `meta.columns[i]`); never assume `row.someColumn`. The `?format=csv`
    branch returns a CSV **string** with no `meta` envelope — for the
    structural metadata, use the JSON call.

14. **On a FRESH create, never query the lake before `migrate` has
    run.** The schema does not exist between `create()` and a completed
    migration, so any check that queries the lake in that window fails
    with `relation "…" does not exist` — and if that failing check
    *halts your provisioning flow* (a CLI contract hook's
    `afterCreateOrUpdate` runs exactly there), the halt lands BEFORE
    migrate ever fires. The lake is then created-but-unmigrated, and a
    reconcile-style flow reads it `unchanged` forever after —
    unrecoverable without a manual migrate. Sequence query-the-lake
    checks strictly after migrate + `ready` (in a CLI contract, that
    means `afterMigrate`/`assertReady`, never `afterCreateOrUpdate`);
    the create-then-migrate window is for platform-DB work only (§5's
    storage-location split).

15. **A pooled reader endpoint can serve a stale schema on a
    just-migrated lake.** If a reader profile points at a connection
    pooler (e.g. Neon's `-pooler` host), a per-datalake DB session
    started against the unmigrated schema can keep resolving the old,
    empty `search_path` even after the migration lands — writes then
    fail with `undefined_table` while `executeSql` reads the same table
    fine. Prefer the direct (non-pooler) endpoint for the writer
    profiles; if ingest 202s but nothing lands on a fresh lake, this is
    the first thing to check. (Platform tracking: platform#784.)