70 lines
2.4 KiB
SQL
70 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
registrations_remaining INTEGER DEFAULT 3,
|
|
domain_invite_codes INTEGER DEFAULT 3,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_users_username ON users(username);
|
|
|
|
CREATE TABLE IF NOT EXISTS invite_codes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(32) UNIQUE NOT NULL,
|
|
created_by INTEGER,
|
|
used_by INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
used_at TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (used_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE INDEX idx_invite_codes_code ON invite_codes(code);
|
|
|
|
CREATE TABLE IF NOT EXISTS domain_invite_codes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(32) UNIQUE NOT NULL,
|
|
created_by INTEGER,
|
|
used_by INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
used_at TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (used_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE INDEX idx_domain_invite_codes_code ON domain_invite_codes(code);
|
|
CREATE INDEX idx_domain_invite_codes_created_by ON domain_invite_codes(created_by);
|
|
CREATE INDEX idx_domain_invite_codes_used_by ON domain_invite_codes(used_by);
|
|
|
|
CREATE TABLE IF NOT EXISTS domains (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
tld VARCHAR(20) NOT NULL,
|
|
ip VARCHAR(255) NOT NULL,
|
|
user_id INTEGER,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
denial_reason TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(name, tld),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE INDEX idx_domains_name_tld ON domains(name, tld);
|
|
CREATE INDEX idx_domains_user_id ON domains(user_id);
|
|
CREATE INDEX idx_domains_status ON domains(status);
|
|
|
|
CREATE TABLE IF NOT EXISTS dns_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
domain_id INTEGER NOT NULL,
|
|
record_type VARCHAR(10) NOT NULL,
|
|
name VARCHAR(255) NOT NULL DEFAULT '@', -- @ for root, or subdomain name
|
|
value VARCHAR(1000) NOT NULL,
|
|
ttl INTEGER DEFAULT 3600,
|
|
priority INTEGER, -- For MX records
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_dns_records_domain_type ON dns_records(domain_id, record_type);
|
|
CREATE INDEX idx_dns_records_name ON dns_records(name); |