Schema Presets (Cross-engine)

nuxt-auto-api ships per-dialect column preset helpers so every table gets timestamps, softDelete, tenant, id, audit, and json columns with one import — and because auto-api's features are convention-driven (detect deletedAt by name; updatedAt self-manages via drizzle $onUpdate), importing a preset activates the feature automatically. No extra config.

Why per-dialect

Drizzle has no cross-dialect column abstraction: integer comes from sqlite-core, timestamp/jsonb from pg-core, datetime/json from mysql-core. Presets are thin per-dialect modules behind three subpath exports — exactly how you already choose sqliteTable vs pgTable.

Usage (SQLite example)

import { sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { id, timestamps, tenant, softDelete, audit, liveUnique, json } from '@websideproject/nuxt-auto-api/schema/sqlite'

const sd = softDelete()   // { columns, indexes(t) }

export const articles = sqliteTable('articles', {
  ...id(),                  // uuid text PK
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  ...tenant(),              // organizationId text
  ...timestamps(),          // createdAt + updatedAt (self-managing)
  ...audit(),               // createdBy + updatedBy (filled by audit-stamp plugin)
  ...sd.columns,            // deletedAt, deletedBy, deletionId, deletedReason
}, t => [
  ...sd.indexes(t),         // articles_trash_idx (deletedAt) + articles_batch_idx (deletionId)
  ...tenant.indexes(t),     // articles_tenant_org_idx
  liveUnique(t, t.slug, 'articles_slug_live'),  // partial unique among live rows
])

Postgres and MySQL are identical except the import path.

Preset reference

softDelete() options

softDelete({
  by:     true,   // include deletedBy (userId who trashed). default: true
  batch:  true,   // include deletionId (batch restore/purge). default: true
  reason: true,   // include deletedReason. default: true
})

Index naming

softDelete().indexes(t) and tenant.indexes(t) auto-namespace their index names with the owning table (e.g. articles_trash_idx, articles_batch_idx, articles_tenant_org_idx). This is required because SQLite and Postgres index names must be globally unique within a database — a fixed trash_idx would collide the moment a second table adopts the preset. The table name is read from the column inside the index callback, so you don't pass it yourself.

Cross-engine differences

MySQL liveUnique caveat

MySQL has no filtered indexes. Use liveUniqueMysql() which documents the generated-column workaround:

import { liveUniqueMysql } from '@websideproject/nuxt-auto-api/schema/mysql'
// See the generated migration comment for the required ALTER TABLE statement.

The audit-stamp plugin

createdBy/updatedBy are filled server-side automatically when the columns exist — no extra config:

• createdBy is set on create (null for system/task writes).
• updatedBy is overwritten on every create + update (client-supplied value is ignored).
• Raw drizzle writes (privacy erasure, migrations) bypass it — intended.

The plugin is registered in the default plugin set; tables without the columns are unaffected.

"Import = feature on" principle