77 lines
2.6 KiB
MySQL
77 lines
2.6 KiB
MySQL
|
-- Better Auth Core Tables Migration
|
||
|
|
-- Creates user, session, account, and verification tables for Better Auth
|
||
|
|
|
||
|
|
-- User table
|
||
|
|
CREATE TABLE IF NOT EXISTS "user" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
email TEXT NOT NULL UNIQUE,
|
||
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
image TEXT,
|
||
|
|
role TEXT DEFAULT 'user',
|
||
|
|
banned BOOLEAN DEFAULT FALSE,
|
||
|
|
banned_reason TEXT,
|
||
|
|
ban_expires TIMESTAMP,
|
||
|
|
requires_setup BOOLEAN DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Session table
|
||
|
|
CREATE TABLE IF NOT EXISTS "session" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
||
|
|
token TEXT NOT NULL UNIQUE,
|
||
|
|
expires_at TIMESTAMP NOT NULL,
|
||
|
|
ip_address TEXT,
|
||
|
|
user_agent TEXT,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Account table (for OAuth providers)
|
||
|
|
CREATE TABLE IF NOT EXISTS "account" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
||
|
|
account_id TEXT NOT NULL,
|
||
|
|
provider_id TEXT NOT NULL,
|
||
|
|
access_token TEXT,
|
||
|
|
refresh_token TEXT,
|
||
|
|
access_token_expires_at TIMESTAMP,
|
||
|
|
refresh_token_expires_at TIMESTAMP,
|
||
|
|
scope TEXT,
|
||
|
|
id_token TEXT,
|
||
|
|
password TEXT,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Verification table (for magic links, email verification, etc.)
|
||
|
|
CREATE TABLE IF NOT EXISTS "verification" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
identifier TEXT NOT NULL,
|
||
|
|
value TEXT NOT NULL,
|
||
|
|
expires_at TIMESTAMP NOT NULL,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Two-factor authentication table
|
||
|
|
CREATE TABLE IF NOT EXISTS "two_factor" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
||
|
|
secret TEXT NOT NULL,
|
||
|
|
backup_codes TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes for performance
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_session_user_id ON "session"(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_session_token ON "session"(token);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_account_user_id ON "account"(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_account_provider ON "account"(provider_id, account_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_verification_identifier ON "verification"(identifier);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_email ON "user"(email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_two_factor_user_id ON "two_factor"(user_id);
|