26 lines
883 B
SQL
26 lines
883 B
SQL
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nickname TEXT NOT NULL UNIQUE,
|
|
nickname_key TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL,
|
|
email_key TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE user_sessions (
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);
|
|
CREATE TABLE password_reset_tokens (
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
used_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX idx_password_reset_user ON password_reset_tokens(user_id);
|