34 lines
1.3 KiB
SQL
34 lines
1.3 KiB
SQL
CREATE TABLE users (
|
|
user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash CHAR(64) DEFAULT NULL,
|
|
api_key VARCHAR(255) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_login TIMESTAMP NULL DEFAULT NULL,
|
|
status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active'
|
|
);
|
|
|
|
CREATE TABLE device_tokens (
|
|
token_id CHAR(36) NOT NULL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL,
|
|
platform ENUM('ios','android','web') NOT NULL,
|
|
token VARBINARY(512) NOT NULL,
|
|
status ENUM('active','invalid','expired') NOT NULL DEFAULT 'active',
|
|
app_version VARCHAR(50),
|
|
locale VARCHAR(10),
|
|
topics VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen_at TIMESTAMP NULL DEFAULT NULL,
|
|
last_successful_delivery TIMESTAMP NULL DEFAULT NULL,
|
|
last_failed_delivery TIMESTAMP NULL DEFAULT NULL,
|
|
hashed_token VARCHAR(64) NOT NULL,
|
|
|
|
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX idx_user_id ON device_tokens(user_id);
|
|
CREATE INDEX idx_platform ON device_tokens(platform);
|
|
CREATE INDEX idx_topics ON device_tokens(topics); |