V1__Initial_user_schema.sql
3.12 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- V1__Initial_user_schema.sql
-- 创建 users 表
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
first_name VARCHAR(100),
last_name VARCHAR(100),
phone VARCHAR(20),
last_login_at TIMESTAMP,
enabled BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);
-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_enabled ON users(enabled);
CREATE INDEX idx_users_created_at ON users(created_at);
-- 创建 user_roles 表（用于角色管理，如果需要的话）
CREATE TABLE user_roles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
role_name VARCHAR(50) NOT NULL DEFAULT 'USER',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE (user_id, role_name)
);
-- 创建 user_sessions 表（用于会话管理）
CREATE TABLE user_sessions (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
session_token VARCHAR(512) UNIQUE NOT NULL,
device_info VARCHAR(255),
ip_address VARCHAR(45),
expires_at TIMESTAMP NOT NULL,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 创建 email_verification_tokens 表
CREATE TABLE email_verification_tokens (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
token VARCHAR(255) UNIQUE NOT NULL,
expires_at TIMESTAMP NOT NULL,
used BOOLEAN DEFAULT false,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 创建 password_reset_tokens 表
CREATE TABLE password_reset_tokens (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
token VARCHAR(255) UNIQUE NOT NULL,
expires_at TIMESTAMP NOT NULL,
used BOOLEAN DEFAULT false,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 创建索引
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at);
CREATE INDEX idx_email_verification_tokens_token ON email_verification_tokens(token);
CREATE INDEX idx_email_verification_tokens_expires_at ON email_verification_tokens(expires_at);
CREATE INDEX idx_password_reset_tokens_token ON password_reset_tokens(token);
CREATE INDEX idx_password_reset_tokens_expires_at ON password_reset_tokens(expires_at);
-- 插入初始管理员用户（可选）
-- INSERT INTO users (username, email, password, first_name, last_name, enabled, created_at, updated_at)
-- VALUES ('admin', 'admin@ecommerce.com', '$2a$12$SomeHashedPassword', 'System', 'Administrator', true, NOW(), NOW());
--
-- INSERT INTO user_roles (user_id, role_name)
-- VALUES (1, 'ADMIN');