JSON REST API

Knex Schema and Migrations

RestApiKnexPlugin exposes a small schema-management surface for table-backed resources.

Use it when you want to:

This is a Knex/table feature surface. It does not exist for non-table resources.

Overview

Once a resource is backed by RestApiKnexPlugin, these scope methods are available:

Example:

await api.addResource('memberships', {
  schema: {
    id: { type: 'id' },
    workspaceId: { type: 'id', required: true },
    userId: { type: 'id', required: true },
    role: { type: 'string', enum: ['owner', 'member'], required: true, defaultTo: 'member' }
  },

  indexes: [
    {
      name: 'uq_memberships_workspace_user',
      unique: true,
      columns: ['workspaceId', 'userId']
    }
  ],

  foreignKeys: [
    {
      name: 'fk_memberships_workspace_user',
      columns: ['workspaceId', 'userId'],
      referencedTableName: 'workspace_users',
      referencedColumns: ['workspace_id', 'user_id'],
      deleteRule: 'CASCADE',
      updateRule: 'RESTRICT'
    }
  ],

  checkConstraints: [
    {
      name: 'chk_memberships_workspace_positive',
      clause: 'workspace_id > 0'
    }
  ],

  tableName: 'memberships'
})

Default Column Naming

Table-backed resources keep logical field names in the resource schema and map them to physical columns for Knex operations.

By default, physical columns use snake_case:

This keeps the API surface expressive without repeating storage.column on every camelCase field.

You only need storage.column when a field should use a non-standard column name:

await api.addResource('profiles', {
  schema: {
    id: { type: 'id' },
    displayName: { type: 'string', required: true },
    legacyRef: { type: 'string', storage: { column: 'legacy_profile_ref' } }
  },
  tableName: 'profiles'
})

If you need physical columns to match the logical field names exactly for a whole resource, opt out explicitly:

await api.addResource('verbatim_profiles', {
  storage: { naming: 'exact' },
  schema: {
    id: { type: 'id' },
    displayName: { type: 'string', required: true }
  },
  tableName: 'verbatim_profiles'
})

Logical IDs and idProperty

idProperty names the physical primary-key column for table-backed resources. The API contract still uses the logical resource id.

await api.addResource('profiles', {
  idProperty: 'user_id',
  schema: {
    id: { type: 'id', required: true, storage: { column: 'user_id' } },
    displayName: { type: 'string', required: true },
    loginCount: { type: 'number', defaultTo: 0 }
  },
  tableName: 'profiles'
})

With this definition:

The resource id is not part of attributes:

await api.resources.profiles.post({
  inputRecord: {
    data: {
      type: 'profiles',
      id: '42',
      attributes: {
        displayName: 'Mercury'
      }
    }
  }
})

ID normalization

If your primary keys need canonicalization beyond the default trim-and-stringify behavior, configure normalizeId.

await api.use(RestApiPlugin, {
  normalizeId: (value) => {
    if (value === null || value === undefined) return null

    const normalized = String(value).trim()
    return normalized ? normalized.toUpperCase() : null
  }
})

You can override that per resource:

await api.addResource('profiles', {
  idProperty: 'user_id',
  normalizeId: (value) => {
    if (value === null || value === undefined) return null

    const normalized = String(value).trim()
    return normalized ? normalized.toLowerCase() : null
  },
  schema: {
    id: { type: 'id', required: true, storage: { column: 'user_id' } },
    displayName: { type: 'string', required: true }
  },
  tableName: 'profiles'
})

For table-backed resources, normalizeId is applied before:

If the normalizer returns an empty value, existing-resource operations fail as not_found, while explicit POST document ids fail validation on data.id.

Create Tables

Create the table directly from the resource definition:

await api.resources.memberships.createKnexTable()

createKnexTable() understands:

This is the same table metadata used by migration generation and diffing.

Field-Only Helpers

Two helpers only operate on columns:

await api.resources.memberships.addKnexFields({
  fields: {
    noteCount: { type: 'number', defaultTo: 0, storage: { column: 'note_count' } }
  }
})

await api.resources.memberships.alterKnexFields({
  fields: {
    role: { type: 'string', enum: ['owner', 'member', 'viewer'] }
  }
})

Important:

Live Table Snapshots

Use introspectKnexTableSnapshot() to inspect the physical table shape:

const snapshot = await api.resources.memberships.introspectKnexTableSnapshot()

Returned shape:

{
  dialect: 'sqlite',
  schemaName: 'main',
  tableName: 'memberships',
  tableCollation: '',
  idColumn: 'id',
  primaryKeyColumns: ['id'],
  hasWorkspaceIdColumn: true,
  hasUserIdColumn: true,
  columns: [...],
  indexes: [...],
  foreignKeys: [...],
  checkConstraints: [...]
}

Column entries are normalized and include information such as:

Current live introspection support:

Create Migrations

Use generateKnexMigration() to emit a Knex migration string from the resource definition:

const migration = await api.resources.memberships.generateKnexMigration()
console.log(migration)

This generates exports.up and exports.down for full table creation/drop.

It includes the same table metadata as createKnexTable():

Diff Migrations

Use generateKnexMigrationDiff() to compare the desired schema against the live table snapshot:

const diff = await api.resources.memberships.generateKnexMigrationDiff()

Returned shape:

{
  migration,
  warnings,
  plan
}

plan is normalized into:

The generated migration is intentionally additive-first:

Important Limits

This is a schema-vs-live-table diff, not a migration-history engine.

It answers:

It does not answer:

Other important limits:

When to Use Which Helper