/**
* Drizzle ORM Schema for better-auth + Custom Tables
*
* better-auth manages: user, session, account, verification, oauthClient,
* oauthAccessToken, oauthRefreshToken, oauthConsent, jwks, apiKey
*
* Custom tables: toolExecution (audit log)
*/
import { sqliteTable, text, integer, primaryKey } from 'drizzle-orm/sqlite-core';
// =============================================================================
// BETTER-AUTH MANAGED TABLES
// These schemas are defined here for type safety but managed by better-auth CLI
// Run: npx @better-auth/cli generate --config ./src/lib/auth-cli.ts
// =============================================================================
export const user = sqliteTable('user', {
id: text('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull().unique(),
emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
image: text('image'),
role: text('role').default('user'),
banned: integer('banned', { mode: 'boolean' }).default(false),
banReason: text('ban_reason'),
banExpires: integer('ban_expires', { mode: 'timestamp' }),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});
export const session = sqliteTable('session', {
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
token: text('token').notNull().unique(),
expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
ipAddress: text('ip_address'),
userAgent: text('user_agent'),
impersonatedBy: text('impersonated_by'),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});
export const account = sqliteTable('account', {
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
accountId: text('account_id').notNull(),
providerId: text('provider_id').notNull(),
accessToken: text('access_token'),
refreshToken: text('refresh_token'),
accessTokenExpiresAt: integer('access_token_expires_at', { mode: 'timestamp' }),
refreshTokenExpiresAt: integer('refresh_token_expires_at', { mode: 'timestamp' }),
scope: text('scope'),
idToken: text('id_token'),
password: text('password'),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});
export const verification = sqliteTable('verification', {
id: text('id').primaryKey(),
identifier: text('identifier').notNull(),
value: text('value').notNull(),
expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
createdAt: integer('created_at', { mode: 'timestamp' }),
updatedAt: integer('updated_at', { mode: 'timestamp' }),
});
// OAuth Provider Plugin Tables
export const oauthClient = sqliteTable('oauth_client', {
id: text('id').primaryKey(),
clientId: text('client_id').notNull().unique(),
clientSecret: text('client_secret'), // Hashed, null for public clients
redirectUris: text('redirect_uris').notNull(), // JSON array
name: text('name'),
icon: text('icon'),
metadata: text('metadata'), // JSON
public: integer('public', { mode: 'boolean' }).default(false),
scopes: text('scopes'), // Space-separated
skipConsent: integer('skip_consent', { mode: 'boolean' }).default(false),
enableEndSession: integer('enable_end_session', { mode: 'boolean' }).default(false),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});
export const oauthAccessToken = sqliteTable('oauth_access_token', {
id: text('id').primaryKey(),
token: text('token').notNull().unique(),
clientId: text('client_id').notNull(),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
scopes: text('scopes'),
expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});
export const oauthRefreshToken = sqliteTable('oauth_refresh_token', {
id: text('id').primaryKey(),
token: text('token').notNull().unique(),
clientId: text('client_id').notNull(),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
sessionId: text('session_id'),
scopes: text('scopes'),
expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
revoked: integer('revoked', { mode: 'timestamp' }),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});
export const oauthConsent = sqliteTable(
'oauth_consent',
{
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
clientId: text('client_id').notNull(),
scopes: text('scopes').notNull(),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
},
(table) => ({
pk: primaryKey({ columns: [table.userId, table.clientId] }),
})
);
// JWKS Table (JWT Key Rotation - better-auth v1.4.0+)
export const jwks = sqliteTable('jwks', {
id: text('id').primaryKey(),
publicKey: text('public_key').notNull(),
privateKey: text('private_key').notNull(),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});
// API Key Plugin Table (better-auth v1.4+)
export const apiKey = sqliteTable('api_key', {
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
name: text('name'),
start: text('start'), // Starting characters of key (for display)
prefix: text('prefix'), // Optional prefix (plain text)
key: text('key').notNull(), // Hashed key
enabled: integer('enabled', { mode: 'boolean' }).default(true),
// Rate limiting
rateLimitEnabled: integer('rate_limit_enabled', { mode: 'boolean' }),
rateLimitTimeWindow: integer('rate_limit_time_window'), // milliseconds
rateLimitMax: integer('rate_limit_max'),
requestCount: integer('request_count').default(0),
lastRequest: integer('last_request', { mode: 'timestamp' }),
// Refill/quota
remaining: integer('remaining'),
refillInterval: integer('refill_interval'), // milliseconds
refillAmount: integer('refill_amount'),
lastRefillAt: integer('last_refill_at', { mode: 'timestamp' }),
// Metadata
expiresAt: integer('expires_at', { mode: 'timestamp' }),
permissions: text('permissions'), // JSON
metadata: text('metadata'), // JSON
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(),
});
// =============================================================================
// CUSTOM TABLES
// =============================================================================
// Tool Executions (Audit log - simple version)
export const toolExecution = sqliteTable('tool_execution', {
id: text('id').primaryKey(),
userId: text('user_id').references(() => user.id, { onDelete: 'set null' }),
sessionId: text('session_id'),
toolName: text('tool_name').notNull(),
success: integer('success', { mode: 'boolean' }).notNull(),
errorMessage: text('error_message'),
durationMs: integer('duration_ms'),
createdAt: integer('created_at', { mode: 'timestamp' }).notNull(),
});
// =============================================================================
// TYPE EXPORTS
// =============================================================================
export type User = typeof user.$inferSelect;
export type NewUser = typeof user.$inferInsert;
export type Session = typeof session.$inferSelect;
export type Account = typeof account.$inferSelect;
export type ToolExecution = typeof toolExecution.$inferSelect;
