59 lines
1.8 KiB
SQL
59 lines
1.8 KiB
SQL
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL,
|
|
email_normalized TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL CHECK (role IN ('barkeeper', 'thekencrew')),
|
|
locked_at_utc TEXT,
|
|
password_changed_at_utc TEXT NOT NULL,
|
|
created_at_utc TEXT NOT NULL,
|
|
updated_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
token_hash TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at_utc TEXT NOT NULL,
|
|
expires_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX sessions_user_idx ON sessions(user_id);
|
|
CREATE INDEX sessions_expiry_idx ON sessions(expires_at_utc);
|
|
|
|
CREATE TABLE invitations (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL,
|
|
email_normalized TEXT NOT NULL,
|
|
role TEXT NOT NULL CHECK (role IN ('barkeeper', 'thekencrew')),
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_by_user_id TEXT NOT NULL REFERENCES users(id),
|
|
created_at_utc TEXT NOT NULL,
|
|
expires_at_utc TEXT NOT NULL,
|
|
accepted_at_utc TEXT,
|
|
revoked_at_utc TEXT
|
|
);
|
|
|
|
CREATE INDEX invitations_email_idx ON invitations(email_normalized, created_at_utc);
|
|
|
|
CREATE TABLE password_reset_tokens (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_at_utc TEXT NOT NULL,
|
|
expires_at_utc TEXT NOT NULL,
|
|
consumed_at_utc TEXT
|
|
);
|
|
|
|
CREATE INDEX password_resets_user_idx ON password_reset_tokens(user_id, created_at_utc);
|
|
|
|
CREATE TABLE login_attempts (
|
|
id TEXT PRIMARY KEY,
|
|
email_key TEXT NOT NULL,
|
|
address_key TEXT NOT NULL,
|
|
attempted_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX login_attempts_email_idx ON login_attempts(email_key, attempted_at_utc);
|
|
CREATE INDEX login_attempts_address_idx ON login_attempts(address_key, attempted_at_utc);
|