67 lines
1.9 KiB
SQL
67 lines
1.9 KiB
SQL
-- Drop the placeholder user table from 0001 and replace with better-auth's schema
|
|
DROP TABLE IF EXISTS user;
|
|
|
|
-- better-auth user table with app-specific additionalFields
|
|
CREATE TABLE user (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
email_verified INTEGER NOT NULL DEFAULT 0,
|
|
image TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
-- additionalFields
|
|
roles TEXT NOT NULL DEFAULT '[]',
|
|
display_name TEXT,
|
|
mobile_number TEXT,
|
|
whatsapp_number TEXT,
|
|
avatar_url TEXT
|
|
);
|
|
|
|
-- better-auth session table
|
|
CREATE TABLE session (
|
|
id TEXT PRIMARY KEY,
|
|
expires_at TEXT NOT NULL,
|
|
token TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
ip_address TEXT,
|
|
user_agent TEXT,
|
|
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_session_token ON session(token);
|
|
CREATE INDEX idx_session_user ON session(user_id);
|
|
|
|
-- better-auth account table
|
|
CREATE TABLE account (
|
|
id TEXT PRIMARY KEY,
|
|
account_id TEXT NOT NULL,
|
|
provider_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
|
|
access_token TEXT,
|
|
refresh_token TEXT,
|
|
id_token TEXT,
|
|
access_token_expires_at TEXT,
|
|
refresh_token_expires_at TEXT,
|
|
scope TEXT,
|
|
password TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_account_user ON account(user_id);
|
|
CREATE UNIQUE INDEX idx_account_provider ON account(provider_id, account_id);
|
|
|
|
-- better-auth verification table
|
|
CREATE TABLE verification (
|
|
id TEXT PRIMARY KEY,
|
|
identifier TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_verification_identifier ON verification(identifier);
|