PermuteDocs
Go to Permute

Guide

Define database schemas and update records

Save and publish managed table schemas, read filtered rows, and update records with version checks and safe retries.

Workflow

  1. 1Create and wait for a database
  2. 2Save and publish a typed schema
  3. 3Insert and filter records
  4. 4Update with __version
  5. 5Handle a conflict

Complete example

typescript
import { PermuteApiError, PermuteClient } from '@permute/sdk';

const orgClient = new PermuteClient({ apiKey: process.env.PERMUTE_API_KEY! });
const client = orgClient.withWorkspace(process.env.PERMUTE_WORKSPACE_ID!);

// Creation needs an eligible Enterprise plan and workspace database admin access.
let database = await client.databases.create({ name: 'CRM', key: 'crm' });
while (database.status === 'provisioning') {
  await new Promise((resolve) => setTimeout(resolve, 2000));
  database = await client.databases.get(database.id);
}
if (database.status !== 'ready') throw new Error('Database is not ready');

const current = await client.databases.definition(database.id);
const saved = await client.databases.saveDefinition(database.id, {
  expectedDraft: current.draft
    ? { revisionId: current.draft.revisionId, sequence: current.draft.sequence }
    : null,
  definition: {
    tables: [
      ...(current.draft?.definition.tables ?? current.published?.definition.tables ?? []),
      {
        name: 'customers',
        columns: [
          { name: 'id', type: 'text', nullable: false },
          { name: 'name', type: 'text', nullable: false },
        ],
        primaryKey: ['id'],
      },
    ],
  },
});
const history = await client.revisions.list(database.id);
if (history.draft?.revisionId !== saved.revisionId) {
  throw new Error('Schema draft changed; review before publishing');
}
await client.revisions.publish(database.id, { expectedVersion: history.version });
console.log('Database ID:', database.id);

const insert = {
  requestId: 'customer-acme-insert-1',
  mutations: [{ type: 'insert' as const, table: 'customers', values: { id: 'acme', name: 'Acme' } }],
};
await client.databases.transact(database.id, insert);
// If the response is uncertain, resend the identical requestId and payload.
await client.databases.transact(database.id, insert);

const filtered = await client.query({
  sourceIds: [database.id],
  sql: "SELECT id, name FROM app.customers WHERE id = 'acme'",
});
console.log(filtered.rows);

const rows = await client.databases.records(database.id, 'customers', { limit: 25 });
const customer = rows.items.find((row) => row.id === 'acme');
if (!customer || typeof customer.__version !== 'string') throw new Error('Customer not found');

await client.databases.transact(database.id, {
  requestId: 'customer-acme-update-1',
  mutations: [{
    type: 'update', table: 'customers', key: { id: 'acme' },
    expectedVersion: customer.__version, values: { name: 'Acme Inc.' },
  }],
});

try {
  await client.databases.transact(database.id, {
    requestId: 'customer-acme-stale-update-1',
    mutations: [{
      type: 'update', table: 'customers', key: { id: 'acme' },
      expectedVersion: customer.__version, values: { name: 'Acme LLC' },
    }],
  });
} catch (error) {
  if (!(error instanceof PermuteApiError) || error.status !== 409) throw error;
  const current = await client.databases.records(database.id, 'customers');
  console.log('Record changed; review before retrying:', current.items);
}

Schema drafts and publication

client.databases.definition returns draft and published schemas. saveDefinition replaces the complete table list and requires the current draft ref (revisionId and sequence), or null for the first save. Preserve existing tables when adding one. History, restore, and publication use client.revisions.

Publishing adds tables and nullable columns, then advances the published schema. Existing rows read NULL in new columns and keep their record-version tokens. Preserve existing columns, types, nullability and primary keys. Table or column removal and adding required columns are rejected. Restoring changes the draft only; it does not restore rows or undo schema changes.

client.databases.createTable is a convenience for adding one table to the draft. Since 0.4.0, callers must publish the database before writing records to that table. Direct transactions require database admin permission; use a published business action for a narrower delegated write.

Add a column to an existing table

Use the same definition and publication APIs to extend a table while preserving its records. New columns must explicitly set nullable: true. The complete definition must retain every existing table and column. This example extends the customers table from the guide above.

DDL is applied before the published ref advances. If publication fails partway through, some additions may already be live; retry the same definition to resume. A conflicting live schema stops publication. Existing action revisions retain their schema bindings; update and publish the action contract and binding when exposing a new field.

typescript
const schema = await client.databases.definition(database.id);
if (!schema.published || schema.draft?.revisionId !== schema.published.revisionId) {
  throw new Error('Review the unpublished schema draft first');
}
const definition = structuredClone(schema.published.definition);
const customers = definition.tables.find((table) => table.name === 'customers');
if (!customers) throw new Error('Customers table not found');
if (customers.columns.some((column) => column.name === 'notes')) {
  throw new Error('Notes column already exists; inspect the current definition');
}
customers.columns.push({ name: 'notes', type: 'text', nullable: true });
const saved = await client.databases.saveDefinition(database.id, {
  expectedDraft: { revisionId: schema.draft.revisionId, sequence: schema.draft.sequence },
  definition,
});
const refs = await client.revisions.list(database.id);
if (refs.draft?.revisionId !== saved.revisionId ||
    refs.published?.revisionId !== schema.published.revisionId ||
    refs.published?.sequence !== schema.published.sequence) {
  throw new Error('Schema changed; review before publishing');
}
await client.revisions.publish(database.id, { expectedVersion: refs.version });

Write rules

Run this example once with a fresh database key. Table creation and insertion are one-time operations. Use a new requestId for each new mutation; reuse it only when retrying the same payload after an uncertain response.

Record reads return __version for managed tables. An update or delete needs the complete primary key and the version just read. A 409 means the record changed or the request ID was reused with different data (read current state and decide what to write).

Filtered reads use client.query with read-only SQL against the database. client.databases.records provides bounded current-state pages but does not accept a filter. Pagination is not a stable snapshot.