-- Fresh Schema Migration
-- Drops all tables and recreates them with correct better-auth schema
-- Drop tables in reverse dependency order
DROP TABLE IF EXISTS tool_execution;
DROP TABLE IF EXISTS user_service_token;
DROP TABLE IF EXISTS shared_service;
DROP TABLE IF EXISTS invitation;
DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS organization;
DROP TABLE IF EXISTS api_key;
DROP TABLE IF EXISTS oauth_consent;
DROP TABLE IF EXISTS oauth_refresh_token;
DROP TABLE IF EXISTS oauth_access_token;
DROP TABLE IF EXISTS oauth_client;
DROP TABLE IF EXISTS jwks;
DROP TABLE IF EXISTS verification;
DROP TABLE IF EXISTS session;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS conversations;
DROP TABLE IF EXISTS user;
-- ============================================================================
-- BETTER-AUTH CORE TABLES
-- ============================================================================
CREATE TABLE user (
id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
email_verified INTEGER DEFAULT 0 NOT NULL,
image TEXT,
role TEXT DEFAULT 'user',
banned INTEGER DEFAULT 0,
ban_reason TEXT,
ban_expires INTEGER,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE session (
id TEXT PRIMARY KEY NOT NULL,
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
token TEXT NOT NULL UNIQUE,
expires_at INTEGER NOT NULL,
ip_address TEXT,
user_agent TEXT,
impersonated_by TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE account (
id TEXT PRIMARY KEY NOT NULL,
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 INTEGER,
refresh_token_expires_at INTEGER,
scope TEXT,
id_token TEXT,
password TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE verification (
id TEXT PRIMARY KEY NOT NULL,
identifier TEXT NOT NULL,
value TEXT NOT NULL,
expires_at INTEGER NOT NULL,
created_at INTEGER,
updated_at INTEGER
);
-- ============================================================================
-- JWKS (JWT Key Rotation)
-- ============================================================================
CREATE TABLE jwks (
id TEXT PRIMARY KEY NOT NULL,
public_key TEXT NOT NULL,
private_key TEXT NOT NULL,
created_at INTEGER NOT NULL
);
-- ============================================================================
-- OAUTH PROVIDER TABLES
-- ============================================================================
CREATE TABLE oauth_client (
id TEXT PRIMARY KEY NOT NULL,
client_id TEXT NOT NULL UNIQUE,
client_secret TEXT,
redirect_uris TEXT NOT NULL,
name TEXT,
icon TEXT,
metadata TEXT,
public INTEGER DEFAULT 0,
scopes TEXT,
skip_consent INTEGER DEFAULT 0,
enable_end_session INTEGER DEFAULT 0,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE oauth_access_token (
id TEXT PRIMARY KEY NOT NULL,
token TEXT NOT NULL UNIQUE,
client_id TEXT NOT NULL,
user_id TEXT REFERENCES user(id) ON DELETE CASCADE,
scopes TEXT,
expires_at INTEGER NOT NULL,
created_at INTEGER NOT NULL
);
CREATE TABLE oauth_refresh_token (
id TEXT PRIMARY KEY NOT NULL,
token TEXT NOT NULL UNIQUE,
client_id TEXT NOT NULL,
user_id TEXT REFERENCES user(id) ON DELETE CASCADE,
session_id TEXT,
scopes TEXT,
expires_at INTEGER NOT NULL,
revoked INTEGER,
created_at INTEGER NOT NULL
);
CREATE TABLE oauth_consent (
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
client_id TEXT NOT NULL,
scopes TEXT NOT NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY (user_id, client_id)
);
-- ============================================================================
-- API KEY TABLE (better-auth v1.4+)
-- ============================================================================
CREATE TABLE api_key (
id TEXT PRIMARY KEY NOT NULL,
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
name TEXT,
start TEXT,
prefix TEXT,
key TEXT NOT NULL,
enabled INTEGER DEFAULT 1,
rate_limit_enabled INTEGER,
rate_limit_time_window INTEGER,
rate_limit_max INTEGER,
request_count INTEGER DEFAULT 0,
last_request INTEGER,
remaining INTEGER,
refill_interval INTEGER,
refill_amount INTEGER,
last_refill_at INTEGER,
expires_at INTEGER,
permissions TEXT,
metadata TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
-- ============================================================================
-- ORGANIZATION TABLES
-- ============================================================================
CREATE TABLE organization (
id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
logo TEXT,
metadata TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE member (
id TEXT PRIMARY KEY NOT NULL,
organization_id TEXT NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member',
created_at INTEGER NOT NULL
);
CREATE TABLE invitation (
id TEXT PRIMARY KEY NOT NULL,
organization_id TEXT NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
email TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'member',
status TEXT NOT NULL DEFAULT 'pending',
inviter_id TEXT REFERENCES user(id) ON DELETE SET NULL,
expires_at INTEGER NOT NULL,
created_at INTEGER NOT NULL
);
-- ============================================================================
-- CUSTOM TABLES
-- ============================================================================
CREATE TABLE shared_service (
id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE,
display_name TEXT NOT NULL,
type TEXT NOT NULL,
config_encrypted TEXT NOT NULL,
configured_by TEXT NOT NULL REFERENCES user(id),
configured_at INTEGER NOT NULL,
last_verified_at INTEGER,
status TEXT DEFAULT 'active'
);
CREATE TABLE user_service_token (
id TEXT PRIMARY KEY NOT NULL,
user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
service TEXT NOT NULL,
access_token_encrypted TEXT NOT NULL,
refresh_token_encrypted TEXT,
expires_at INTEGER,
scopes TEXT,
service_user_id TEXT,
service_email TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE TABLE tool_execution (
id TEXT PRIMARY KEY NOT NULL,
user_id TEXT REFERENCES user(id) ON DELETE SET NULL,
session_id TEXT,
organization_id TEXT,
tool_name TEXT NOT NULL,
auth_type TEXT,
service_used TEXT,
success INTEGER NOT NULL,
error_message TEXT,
duration_ms INTEGER,
created_at INTEGER NOT NULL
);
-- ============================================================================
-- CONVERSATION MEMORY TABLES
-- ============================================================================
CREATE TABLE conversations (
id TEXT PRIMARY KEY NOT NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
metadata TEXT
);
CREATE TABLE messages (
id TEXT PRIMARY KEY NOT NULL,
conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
role TEXT NOT NULL,
content TEXT NOT NULL,
tool_calls TEXT,
tool_call_id TEXT,
created_at INTEGER NOT NULL
);
