# Advanced migrations

SQL a person wrote by hand for one datalake.

The platform writes its own DDL when a generic table is created or gains a
column — `CREATE TABLE` and `ALTER TABLE … ADD COLUMN`, and nothing else.
Everything past that is yours to write here: an index, a foreign key, a column
rename, a type change, a drop. The same migrator runs both.

**One name, two spellings.** Outwardly these are *advanced migrations*, which is
what the API path and this guide call them. The platform stores them in
`datalake_migrations`, and its own logs and internals use that name. Both are
correct; do not coin a third.

    api.advancedMigrations        →  this guide
    /datalakes/{slug}/advanced-migrations

## 1. Wire shape

```typescript
const { data: created } = await api.advancedMigrations.create(
  tenantSlug,
  datalakeSlug,          // scope comes from the PATH — see below
  {
    name: 'AddEmailIndexToContactSubmissions',
    up_statement:
      'CREATE INDEX IF NOT EXISTS idx_contact_submissions_email ' +
      'ON alvera_custom_contact_submissions (email)',
    down_statement:
      'DROP INDEX IF EXISTS idx_contact_submissions_email',
  },
)
// created.id           — server-derived UUID
// created.version      — server-assigned from a sequence; orders execution
// created.datalake_id  — server-derived from the path
// created.checksum     — server-computed; the drift fingerprint
```

Three fields, all required, all strings:

```
name             required string — capitalized identifier, unique per datalake
up_statement     required string — the DDL to run
down_statement   required string — the DDL that reverses it
```

**`datalake_id` is NOT a body field.** This is the one place the shape departs
from every other datalake-scoped kind. Elsewhere — tools, workflows, generic
tables — you send `datalake_id` in the body. Here it is `readOnly`: the scope
comes from `datalakeSlug` in the path, and the server fills the field in. Sending
it is not how you say which lake this belongs to.

**`version` is server-assigned and orders execution.** It comes from a sequence,
not from you, and it is the thing that decides what runs first — not the name,
and not the order you declared them in. Generated migrations carry a year-style
prefix in their names to read well beside yours; that prefix is cosmetic.

## 2. Rules the type cannot encode

### An applied migration is frozen — update and delete both refuse

Once a migration has run successfully, `update()` and `delete()` each answer
**409 Conflict**. The reasons differ and both are worth knowing.

**Update** is refused because a statement that has already executed cannot be
rewritten after the fact: the new text would no longer describe what happened.

**Delete** is refused because the run log cascades on delete, so removing an
applied migration would also erase the record of it having run.

So a migration has exactly two lives. Before it runs it is ordinary and
editable. After it runs it is history. **Changing applied DDL means writing a
NEW migration that alters what the old one built** — the same shape as changing
a generic table column's `privacy_requirement`, which is delete-and-recreate
rather than an edit.

"Applied" means precisely: a run log row exists for this migration with
direction `up` and status `success`. A failed run does not freeze it.

### Write the statement to be safe on a second run

The platform tracks what it has applied and will not run an applied migration
twice, so you do not need `IF NOT EXISTS` for that. You need it for the other
case: a run that failed **partway**. The applied-migrations record stops a second
run; it does not undo a first one that got halfway.

```sql
-- safe to meet again
CREATE INDEX IF NOT EXISTS idx_submissions_email ON … (email)

-- Postgres has no ADD CONSTRAINT IF NOT EXISTS, so guard it yourself
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'fk_submissions_lead'
  ) THEN
    ALTER TABLE alvera_custom_contact_submissions
      ADD CONSTRAINT fk_submissions_lead
      FOREIGN KEY (lead_id) REFERENCES alvera_custom_leads (id);
  END IF;
END $$;
```

The same applies to `down_statement`, which is likelier to be run against a
database that is not in the state you assumed.

### One statement per body

The platform runs each body as a single prepared statement. Two commands in one
`up_statement` are accepted at create and fail at migrate with Postgres `42601`
`cannot insert multiple commands into a prepared statement`. The two examples
above are therefore two migrations, not one body. A `DO $$ … $$` block counts as
one statement, so steps that must travel together go inside one.

### The table names are the platform's, not yours

A generic table you declared as `contact_submissions` is physically
`alvera_custom_contact_submissions`. Read the name back from the created table
rather than composing it — see `generic_tables.md` — because your migration
statement has to name the physical table, and getting it wrong is a runtime
failure inside DDL rather than a validation error.

### Ordering is real, and it is the version's job

Adding an index and then a foreign key that depends on it are two migrations,
and the sequence decides which runs first. Two migrations created in one session
run in creation order because the sequence is monotonic — but if you need B
after A, that is a fact about your two `version` values, not about how you
listed them.

## 3. Field ownership

**Caller-authored (Request).** `name`, `up_statement`, `down_statement`. That is
the whole writable surface.

**Server-derived (Response-only).** `id`, `datalake_id`, `version`, `checksum`,
`inserted_at`, `updated_at`. None may be sent; see `type_naming.md` for why the
Writable types exclude them.

`name` is unique per datalake, so two lakes may each carry a
`AddEmailIndex` and neither collides.

## 4. Error envelopes

Standard JSON:API envelope throughout — see `errors.md`.

| Status | When |
|---|---|
| 422 | a required field is missing, or `name` collides within this datalake |
| 409 | the migration has already been applied — `update` and `delete` both |
| 404 | the id belongs to a different datalake (deliberately not "403": its existence is not leaked) |

A 409 here is not a retryable condition. It is telling you the migration is
history and you want a new one.

## 5. Lifecycle

```
create      →  the row exists; nothing has run yet
migrate     →  the datalake's migrator runs it, in version order
              (POST /datalakes/{slug}/migrate — asynchronous, 202)
applied     →  a run log row: direction up, status success
              from here: update 409, delete 409
```

Creating a migration does **not** run it. The datalake's migrate call does, and
it is asynchronous — it answers 202 as soon as the job is enqueued. Poll the
datalake for `status: ready` the way you would after creating one; see
`datalakes.md`.

## 6. Gotchas

**A created migration that never ran is invisible in the database.** The row
exists, `list()` returns it, and nothing has happened to any table. Reading the
catalog is the only way to know whether it took effect — the migration record
says what you asked for, not what is true.

**Proving one worked means asking the database, not the API.** The response
carries `checksum`, `datalake_id`, `down_statement`, `id`, `inserted_at`,
`name`, `up_statement`, `updated_at` and `version` — no status, no applied
flag, and `version` is execution ORDER rather than proof of execution. So the
honest check is a query against the catalog for the object the statement was
supposed to create:

```typescript
// an index
const { data: indexRows } = await api.datalakes.executeSql(tenantSlug, datalakeSlug, {
  sql: `select 1 from pg_indexes
         where tablename = 'alvera_custom_contact_submissions'
           and indexname = 'idx_contact_submissions_email'`,
  mode: 'raw',
})

// a foreign key
const { data: fkRows } = await api.datalakes.executeSql(tenantSlug, datalakeSlug, {
  sql: `select 1 from information_schema.table_constraints
         where constraint_type = 'FOREIGN KEY'
           and table_name      = 'alvera_custom_contact_submissions'
           and constraint_name = 'fk_submissions_lead'`,
  mode: 'raw',
})
```

**`down_statement` is required and is rarely exercised.** Nothing makes you
prove it works, and it is the statement you will want on your worst day. Write
it as carefully as the `up`.

**A migration belongs to exactly one datalake.** There is no shared or
tenant-level migration. Three lakes needing the same index need three
migrations — which is also why `name` only has to be unique per lake.