Database Schema Generation#

Better Auth derives its database schema from a single canonical source — BetterAuthDBSchema — which is assembled in packages/core/src/db/get-tables.ts and consumed by adapters, CLI generators, and the migration engine. Understanding how this schema represents the implicit id primary key, merges plugin fields, and maps logical field names to physical columns for compound indexes is essential when extending the schema or debugging generated output.

Schema Assembly (getAuthTables / buildAuthTables)#

getAuthTables is the single entry point for the canonical schema. It delegates to buildAuthTables, which:

1. Merges plugin fields — iterates options.plugins, spreads each plugin's fields into the matching table entry, and deduplicates indexes via mergeTableIndexes 1.
2. Lays out core tables — user, session, account, and verification are defined inline with their built-in fields. Plugin-contributed fields for these same table keys are spread in after the built-in fields 2.
3. Appends additionalFields — top-level config fields for user, session, account, and verification are spread last, so they extend but cannot rename core columns 3.

The implicit id primary key is not declared in BetterAuthDBSchema's fields map. Each adapter and generator injects it independently at generation/migration time. This keeps the schema declaration lean but means consumers that iterate fields will not see id unless they add it themselves.

getAuthTablesWithResolvedIndexes wraps buildAuthTables and immediately calls resolveDatabaseSchemaIndexes to validate and resolve all compound indexes, returning { indexesByTable, tables }. This is what generators and the migration engine actually call.

Field-to-Column Mapping (DBFieldAttribute.fieldName)#

Every field in BetterAuthDBSchema may carry a fieldName property, which overrides the physical column name used in the database. Resolution happens in resolveDatabaseTableIndexes: when building columns from an index's logical fields array, each logical field name is looked up in the fields map and its fieldName (or the logical name if absent) becomes the physical column name 4.

logical field name ──→ fields[name].fieldName ?? name ──→ physical column name

Generators (Drizzle, Prisma) and the migration engine all consume these resolved ResolvedDBTableIndex objects, so they always work with physical column names 5.

Compound Index Resolution (database-index.ts)#

packages/core/src/db/database-index.ts is the authoritative resolver for table-level indexes. Key exports:

The DBTableIndex schema API (defined in packages/core/src/db/type.ts) accepts 5:

Feature introduced in PR #10402; prior to that, only per-field boolean index flags existed 5.

Drizzle Schema Generator#

packages/cli/src/generators/drizzle.ts converts BetterAuthDBSchema into Drizzle ORM TypeScript. It uses two independent code paths for uniqueness constraints 6:

• Column-level: appends .unique() whenever attr.unique is truthy.
• Table-level: pushes an index("…_idx") or uniqueIndex("…_uidx") entry when attr.index is truthy; rendered via assignIndexes.

Known bug: a field with both unique: true and index: true (e.g., organization.slug) triggers both paths, generating a duplicate unique constraint 6. The proposed fix is to suppress column-level .unique() when a table-level uniqueIndex will already be emitted. Track issue #10332 for status.

For compound indexes (multi-field DBTableIndex entries), the generator emits .index("name").on(col1, col2) / .uniqueIndex("name").on(...) using the resolved physical column names 5.

Adapter & Migration Behavior#

MySQL and SQL Server enforce indexed string column byte limits; unbounded columns trigger a blocking error with remediation instructions rather than silent truncation 5.

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

Key Files#