Compound Table Indexes

Compound Table Indexes#

Compound table indexes allow plugin schemas to declare named, multi-column indexes — including compound unique constraints — across 1–16 logical fields on a single table. This enables expressing durable tuple identities (e.g., issuer + providerSubject) without manufacturing a deterministic compound ID column. The feature was introduced in PR #10402 and resolves issue #7862.

Prior to this feature, the schema only supported per-field boolean index flags , which cannot express multi-field uniqueness contracts or carry them consistently through schema generation, migrations, and adapters.

Schema API#

Indexes are defined in a table's indexes array alongside its fields. Each entry conforms to DBTableIndex (added to packages/core/src/db/type.ts):

Property	Type	Description
`fields`	`[string, ...string[]]`	1–16 logical field names in the table
`name`	`string?`	Optional portable name (≤ 63 UTF-8 bytes, schema-wide unique, case-insensitive)
`unique`	`boolean?`	Enforces uniqueness across the tuple

Example (from the PR description) :

{
  id: "my-plugin",
  schema: {
    compoundIdentity: {
      fields: {
        issuer: { type: "string" },
        providerSubject: { type: "string" },
      },
      indexes: [
        { fields: ["issuer", "providerSubject"], name: "compound_identity_uidx", unique: true }
      ]
    }
  }
}

Constraints on unique indexes :

Every field in a unique index must be required (non-nullable) — null semantics differ across databases.
Fields of type json, string[], or number[] cannot be indexed (not portably indexable).
Index names are validated as schema-wide, case-insensitive, and at most 63 UTF-8 bytes; long generated names are deterministically hashed.

Implementation Overview#

The core logic lives in newly added files introduced by the PR:

File	Role
`packages/core/src/db/database-index.ts`	`getDatabaseIndexName()`, `resolveDatabaseTableIndexes()`, `resolveDatabaseSchemaIndexes()` — validation, name generation, and field-to-column mapping
`packages/core/src/db/type.ts`	`DBTableIndex` interface; `indexes` property on `BetterAuthDBSchema` table entries
`packages/core/src/db/internal.ts`	Internal resolution types passed between resolvers and adapters
`packages/better-auth/src/db/get-migration.ts`	SQL migration: introspects existing indexes (per-dialect queries), validates conflicts, emits `CREATE INDEX` after table/column creation
`packages/mongo-adapter/src/mongodb-adapter.ts`	Lazily creates MongoDB indexes before the first index-enforcing write (`create`, `update`, `updateMany`, `increment`)
`packages/cli/src/generators/drizzle.ts`	Emits `.index("name").on(col1, col2)` / `.uniqueIndex("name").on(...)` in Drizzle table builders
`packages/cli/src/generators/prisma.ts`	Emits `@@index([col1, col2], map: "name")` / `@@unique(...)` block attributes in Prisma schema

Adapter Behavior#

SQL (PostgreSQL, MySQL, SQLite, SQL Server) :

Migration introspects existing indexes using dialect-specific queries and validates that existing same-named indexes match the expected definition (columns, order, uniqueness).
MySQL and SQL Server enforce indexed string column byte limits (3 072 bytes and 1 700 bytes respectively); unbounded columns emit a remediation error rather than silently truncating.
New indexes are emitted as CREATE INDEX / CREATE UNIQUE INDEX statements deferred until after table and column creation.

MongoDB :

createIndex() is called with the resolved column names, index name, and uniqueness flag before the first write that would enforce the index.
Setup is cached per collection to avoid redundant attempts.

End-to-end adapter parity is verified in e2e/adapter/test/adapter-factory/compound-index-test-suite.ts across all five supported backends.

Migration Notes#

Existing installations must generate and apply a migration after adding indexes to a schema — indexes are not created retroactively .
An existing index with the same name but different columns or uniqueness must be corrected or dropped manually before applying the migration .
MySQL / SQL Server deployments with unbounded VARCHAR columns targeted by a unique index will receive a blocking error with remediation instructions rather than silent data loss .