316 lines
11 KiB
TypeScript
316 lines
11 KiB
TypeScript
/**
|
|
* Complete better-auth Database Schema for Drizzle ORM + D1
|
|
*
|
|
* This schema includes all tables required by better-auth core.
|
|
* You can add your own application tables below.
|
|
*
|
|
* ═══════════════════════════════════════════════════════════════
|
|
* CRITICAL NOTES
|
|
* ═══════════════════════════════════════════════════════════════
|
|
*
|
|
* 1. Column names use camelCase (emailVerified, createdAt)
|
|
* - This matches better-auth expectations
|
|
* - If you use snake_case, you MUST use CamelCasePlugin with Kysely
|
|
*
|
|
* 2. Timestamps use INTEGER with mode: "timestamp"
|
|
* - D1 (SQLite) doesn't have native timestamp type
|
|
* - Unix epoch timestamps (seconds since 1970)
|
|
*
|
|
* 3. Booleans use INTEGER with mode: "boolean"
|
|
* - D1 (SQLite) doesn't have native boolean type
|
|
* - 0 = false, 1 = true
|
|
*
|
|
* 4. Foreign keys use onDelete: "cascade"
|
|
* - Automatically delete related records
|
|
* - session deleted when user deleted
|
|
* - account deleted when user deleted
|
|
*
|
|
* ═══════════════════════════════════════════════════════════════
|
|
*/
|
|
|
|
import { integer, sqliteTable, text, index } from "drizzle-orm/sqlite-core";
|
|
import { sql } from "drizzle-orm";
|
|
|
|
// ═══════════════════════════════════════════════════════════════
|
|
// better-auth CORE TABLES
|
|
// ═══════════════════════════════════════════════════════════════
|
|
|
|
/**
|
|
* Users table - stores all user accounts
|
|
*/
|
|
export const user = sqliteTable(
|
|
"user",
|
|
{
|
|
id: text().primaryKey(),
|
|
name: text().notNull(),
|
|
email: text().notNull().unique(),
|
|
emailVerified: integer({ mode: "boolean" }).notNull().default(false),
|
|
image: text(), // Profile picture URL
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
emailIdx: index("user_email_idx").on(table.email),
|
|
})
|
|
);
|
|
|
|
/**
|
|
* Sessions table - stores active user sessions
|
|
*
|
|
* NOTE: Consider using KV storage for sessions instead of D1
|
|
* to avoid eventual consistency issues
|
|
*/
|
|
export const session = sqliteTable(
|
|
"session",
|
|
{
|
|
id: text().primaryKey(),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
token: text().notNull().unique(),
|
|
expiresAt: integer({ mode: "timestamp" }).notNull(),
|
|
ipAddress: text(),
|
|
userAgent: text(),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
userIdIdx: index("session_user_id_idx").on(table.userId),
|
|
tokenIdx: index("session_token_idx").on(table.token),
|
|
})
|
|
);
|
|
|
|
/**
|
|
* Accounts table - stores OAuth provider accounts and passwords
|
|
*/
|
|
export const account = sqliteTable(
|
|
"account",
|
|
{
|
|
id: text().primaryKey(),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
accountId: text().notNull(), // Provider's user ID
|
|
providerId: text().notNull(), // "google", "github", etc.
|
|
accessToken: text(),
|
|
refreshToken: text(),
|
|
accessTokenExpiresAt: integer({ mode: "timestamp" }),
|
|
refreshTokenExpiresAt: integer({ mode: "timestamp" }),
|
|
scope: text(), // OAuth scopes granted
|
|
idToken: text(), // OpenID Connect ID token
|
|
password: text(), // Hashed password for email/password auth
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
userIdIdx: index("account_user_id_idx").on(table.userId),
|
|
providerIdx: index("account_provider_idx").on(
|
|
table.providerId,
|
|
table.accountId
|
|
),
|
|
})
|
|
);
|
|
|
|
/**
|
|
* Verification tokens - for email verification, password reset, etc.
|
|
*/
|
|
export const verification = sqliteTable(
|
|
"verification",
|
|
{
|
|
id: text().primaryKey(),
|
|
identifier: text().notNull(), // Email or user ID
|
|
value: text().notNull(), // Token value
|
|
expiresAt: integer({ mode: "timestamp" }).notNull(),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
identifierIdx: index("verification_identifier_idx").on(table.identifier),
|
|
valueIdx: index("verification_value_idx").on(table.value),
|
|
})
|
|
);
|
|
|
|
// ═══════════════════════════════════════════════════════════════
|
|
// OPTIONAL: Additional tables for better-auth plugins
|
|
// ═══════════════════════════════════════════════════════════════
|
|
|
|
/**
|
|
* Two-Factor Authentication table (if using 2FA plugin)
|
|
*/
|
|
export const twoFactor = sqliteTable(
|
|
"two_factor",
|
|
{
|
|
id: text().primaryKey(),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
secret: text().notNull(), // TOTP secret
|
|
backupCodes: text(), // JSON array of backup codes
|
|
enabled: integer({ mode: "boolean" }).notNull().default(false),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
userIdIdx: index("two_factor_user_id_idx").on(table.userId),
|
|
})
|
|
);
|
|
|
|
/**
|
|
* Organizations table (if using organization plugin)
|
|
*/
|
|
export const organization = sqliteTable("organization", {
|
|
id: text().primaryKey(),
|
|
name: text().notNull(),
|
|
slug: text().notNull().unique(),
|
|
logo: text(),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
});
|
|
|
|
/**
|
|
* Organization members table (if using organization plugin)
|
|
*/
|
|
export const organizationMember = sqliteTable(
|
|
"organization_member",
|
|
{
|
|
id: text().primaryKey(),
|
|
organizationId: text()
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
role: text().notNull(), // "owner", "admin", "member"
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
},
|
|
(table) => ({
|
|
orgIdIdx: index("org_member_org_id_idx").on(table.organizationId),
|
|
userIdIdx: index("org_member_user_id_idx").on(table.userId),
|
|
})
|
|
);
|
|
|
|
// ═══════════════════════════════════════════════════════════════
|
|
// YOUR APPLICATION TABLES
|
|
// ═══════════════════════════════════════════════════════════════
|
|
|
|
/**
|
|
* Example: User profile extension
|
|
*/
|
|
export const profile = sqliteTable("profile", {
|
|
id: text().primaryKey(),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
bio: text(),
|
|
website: text(),
|
|
location: text(),
|
|
phone: text(),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
});
|
|
|
|
/**
|
|
* Example: User preferences
|
|
*/
|
|
export const userPreferences = sqliteTable("user_preferences", {
|
|
id: text().primaryKey(),
|
|
userId: text()
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
theme: text().notNull().default("system"), // "light", "dark", "system"
|
|
language: text().notNull().default("en"),
|
|
emailNotifications: integer({ mode: "boolean" }).notNull().default(true),
|
|
pushNotifications: integer({ mode: "boolean" }).notNull().default(false),
|
|
createdAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
updatedAt: integer({ mode: "timestamp" })
|
|
.notNull()
|
|
.default(sql`(unixepoch())`),
|
|
});
|
|
|
|
// ═══════════════════════════════════════════════════════════════
|
|
// Export all schemas for Drizzle
|
|
// ═══════════════════════════════════════════════════════════════
|
|
export const schema = {
|
|
user,
|
|
session,
|
|
account,
|
|
verification,
|
|
twoFactor,
|
|
organization,
|
|
organizationMember,
|
|
profile,
|
|
userPreferences,
|
|
} as const;
|
|
|
|
/**
|
|
* ═══════════════════════════════════════════════════════════════
|
|
* USAGE INSTRUCTIONS
|
|
* ═══════════════════════════════════════════════════════════════
|
|
*
|
|
* 1. Save this file as: src/db/schema.ts
|
|
*
|
|
* 2. Create drizzle.config.ts:
|
|
* import type { Config } from "drizzle-kit";
|
|
*
|
|
* export default {
|
|
* out: "./drizzle",
|
|
* schema: "./src/db/schema.ts",
|
|
* dialect: "sqlite",
|
|
* driver: "d1-http",
|
|
* dbCredentials: {
|
|
* databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
|
|
* accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
|
|
* token: process.env.CLOUDFLARE_TOKEN!,
|
|
* },
|
|
* } satisfies Config;
|
|
*
|
|
* 3. Generate migrations:
|
|
* npx drizzle-kit generate
|
|
*
|
|
* 4. Apply migrations to D1:
|
|
* wrangler d1 migrations apply my-app-db --local
|
|
* wrangler d1 migrations apply my-app-db --remote
|
|
*
|
|
* 5. Use in your Worker:
|
|
* import { drizzle } from "drizzle-orm/d1";
|
|
* import * as schema from "./db/schema";
|
|
*
|
|
* const db = drizzle(env.DB, { schema });
|
|
*
|
|
* 6. Query example:
|
|
* const users = await db.query.user.findMany({
|
|
* where: (user, { eq }) => eq(user.emailVerified, true)
|
|
* });
|
|
*
|
|
* ═══════════════════════════════════════════════════════════════
|
|
*/
|