358 lines
15 KiB
PL/PgSQL
358 lines
15 KiB
PL/PgSQL
-- Panel hosting control panel — initial schema
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
CREATE EXTENSION IF NOT EXISTS "citext";
|
|
|
|
-- ─── Enums ───────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TYPE user_status AS ENUM ('active', 'suspended', 'pending');
|
|
CREATE TYPE user_role AS ENUM ('superadmin', 'admin', 'reseller', 'user');
|
|
|
|
CREATE TYPE server_status AS ENUM ('online', 'offline', 'maintenance', 'provisioning');
|
|
CREATE TYPE site_status AS ENUM ('active', 'suspended', 'creating', 'deleting', 'error');
|
|
CREATE TYPE ssl_type AS ENUM ('letsencrypt', 'custom', 'self_signed');
|
|
CREATE TYPE ssl_status AS ENUM ('pending', 'active', 'expired', 'error');
|
|
CREATE TYPE db_engine AS ENUM ('mysql', 'postgresql');
|
|
CREATE TYPE backup_type AS ENUM ('full', 'files', 'database');
|
|
CREATE TYPE backup_status AS ENUM ('pending', 'running', 'completed', 'failed');
|
|
CREATE TYPE job_status AS ENUM ('pending', 'running', 'completed', 'failed', 'cancelled');
|
|
CREATE TYPE dns_record_type AS ENUM ('A', 'AAAA', 'CNAME', 'MX', 'TXT', 'NS', 'SRV', 'PTR');
|
|
|
|
-- ─── Users & auth ────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
email CITEXT NOT NULL UNIQUE,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role user_role NOT NULL DEFAULT 'user',
|
|
status user_status NOT NULL DEFAULT 'pending',
|
|
locale VARCHAR(10) NOT NULL DEFAULT 'ru',
|
|
timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/Moscow',
|
|
last_login_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
|
|
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
|
|
|
|
CREATE TABLE api_keys (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR(128) NOT NULL,
|
|
key_hash TEXT NOT NULL UNIQUE,
|
|
key_prefix VARCHAR(16) NOT NULL,
|
|
last_used_at TIMESTAMPTZ,
|
|
expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
|
|
|
|
-- ─── Servers (managed nodes) ─────────────────────────────────────────────────
|
|
|
|
CREATE TABLE servers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
name VARCHAR(128) NOT NULL,
|
|
hostname VARCHAR(255) NOT NULL,
|
|
ip_address INET NOT NULL,
|
|
ssh_port INT NOT NULL DEFAULT 22,
|
|
status server_status NOT NULL DEFAULT 'provisioning',
|
|
agent_token_hash TEXT,
|
|
agent_version VARCHAR(32),
|
|
os_info JSONB NOT NULL DEFAULT '{}',
|
|
resources JSONB NOT NULL DEFAULT '{}',
|
|
settings JSONB NOT NULL DEFAULT '{}',
|
|
last_seen_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_servers_hostname ON servers(hostname);
|
|
|
|
-- User access to servers (multi-tenant / reseller model)
|
|
CREATE TABLE server_access (
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
server_id BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
|
|
can_manage BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (user_id, server_id)
|
|
);
|
|
|
|
-- ─── Sites (websites) ────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE sites (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
server_id BIGINT NOT NULL REFERENCES servers(id) ON DELETE RESTRICT,
|
|
owner_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
name VARCHAR(128) NOT NULL,
|
|
document_root VARCHAR(512) NOT NULL,
|
|
php_version VARCHAR(16),
|
|
status site_status NOT NULL DEFAULT 'creating',
|
|
settings JSONB NOT NULL DEFAULT '{}',
|
|
disk_quota_mb INT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (server_id, name)
|
|
);
|
|
|
|
CREATE INDEX idx_sites_owner_id ON sites(owner_id);
|
|
CREATE INDEX idx_sites_server_id ON sites(server_id);
|
|
|
|
CREATE TABLE domains (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
domain CITEXT NOT NULL,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
ssl_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (domain)
|
|
);
|
|
|
|
CREATE INDEX idx_domains_site_id ON domains(site_id);
|
|
|
|
-- Only one primary domain per site
|
|
CREATE UNIQUE INDEX idx_domains_site_primary
|
|
ON domains(site_id) WHERE is_primary = true;
|
|
|
|
-- ─── Databases ───────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE databases (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
server_id BIGINT NOT NULL REFERENCES servers(id) ON DELETE RESTRICT,
|
|
name VARCHAR(64) NOT NULL,
|
|
engine db_engine NOT NULL DEFAULT 'mysql',
|
|
charset VARCHAR(32) NOT NULL DEFAULT 'utf8mb4',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (server_id, engine, name)
|
|
);
|
|
|
|
CREATE INDEX idx_databases_site_id ON databases(site_id);
|
|
|
|
CREATE TABLE database_users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
database_id BIGINT NOT NULL REFERENCES databases(id) ON DELETE CASCADE,
|
|
username VARCHAR(64) NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
privileges TEXT[] NOT NULL DEFAULT '{ALL}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (database_id, username)
|
|
);
|
|
|
|
-- ─── FTP ─────────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE ftp_accounts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
username VARCHAR(64) NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
home_path VARCHAR(512) NOT NULL,
|
|
quota_mb INT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (site_id, username)
|
|
);
|
|
|
|
-- ─── SSL certificates ────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE ssl_certificates (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
domain_id BIGINT NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
|
type ssl_type NOT NULL,
|
|
status ssl_status NOT NULL DEFAULT 'pending',
|
|
issuer VARCHAR(255),
|
|
cert_pem TEXT,
|
|
key_pem TEXT,
|
|
chain_pem TEXT,
|
|
issued_at TIMESTAMPTZ,
|
|
expires_at TIMESTAMPTZ,
|
|
auto_renew BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_ssl_certificates_domain_id ON ssl_certificates(domain_id);
|
|
CREATE INDEX idx_ssl_certificates_expires_at ON ssl_certificates(expires_at);
|
|
|
|
-- ─── Mail ────────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE mail_domains (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
domain CITEXT NOT NULL UNIQUE,
|
|
dkim_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE mail_accounts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
mail_domain_id BIGINT NOT NULL REFERENCES mail_domains(id) ON DELETE CASCADE,
|
|
email CITEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
quota_mb INT NOT NULL DEFAULT 1024,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_mail_accounts_mail_domain_id ON mail_accounts(mail_domain_id);
|
|
|
|
-- ─── DNS ─────────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE dns_zones (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
server_id BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
|
|
domain CITEXT NOT NULL,
|
|
serial BIGINT NOT NULL DEFAULT 1,
|
|
ttl INT NOT NULL DEFAULT 3600,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (server_id, domain)
|
|
);
|
|
|
|
CREATE TABLE dns_records (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
zone_id BIGINT NOT NULL REFERENCES dns_zones(id) ON DELETE CASCADE,
|
|
type dns_record_type NOT NULL,
|
|
name VARCHAR(255) NOT NULL DEFAULT '@',
|
|
content TEXT NOT NULL,
|
|
ttl INT NOT NULL DEFAULT 3600,
|
|
priority INT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_dns_records_zone_id ON dns_records(zone_id);
|
|
|
|
-- ─── Cron jobs ───────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE cron_jobs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
schedule VARCHAR(128) NOT NULL,
|
|
command TEXT NOT NULL,
|
|
run_as VARCHAR(64) NOT NULL DEFAULT 'www-data',
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_cron_jobs_site_id ON cron_jobs(site_id);
|
|
|
|
-- ─── Backups ─────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE backups (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
site_id BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
type backup_type NOT NULL,
|
|
status backup_status NOT NULL DEFAULT 'pending',
|
|
storage_path VARCHAR(1024),
|
|
size_bytes BIGINT,
|
|
error_message TEXT,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_backups_site_id ON backups(site_id);
|
|
CREATE INDEX idx_backups_status ON backups(status);
|
|
|
|
-- ─── Async job queue (panel → agent tasks) ───────────────────────────────────
|
|
|
|
CREATE TABLE jobs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
server_id BIGINT REFERENCES servers(id) ON DELETE SET NULL,
|
|
site_id BIGINT REFERENCES sites(id) ON DELETE SET NULL,
|
|
user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
|
|
type VARCHAR(64) NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}',
|
|
status job_status NOT NULL DEFAULT 'pending',
|
|
result JSONB,
|
|
error_message TEXT,
|
|
attempts INT NOT NULL DEFAULT 0,
|
|
max_attempts INT NOT NULL DEFAULT 3,
|
|
scheduled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_jobs_status_scheduled ON jobs(status, scheduled_at)
|
|
WHERE status IN ('pending', 'running');
|
|
CREATE INDEX idx_jobs_server_id ON jobs(server_id);
|
|
|
|
-- ─── Audit log ───────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE audit_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
|
|
action VARCHAR(64) NOT NULL,
|
|
resource VARCHAR(64) NOT NULL,
|
|
resource_id BIGINT,
|
|
details JSONB NOT NULL DEFAULT '{}',
|
|
ip_address INET,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
|
|
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource, resource_id);
|
|
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
|
|
|
|
-- ─── updated_at trigger ──────────────────────────────────────────────────────
|
|
|
|
CREATE OR REPLACE FUNCTION set_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_users_updated_at
|
|
BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_servers_updated_at
|
|
BEFORE UPDATE ON servers FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_sites_updated_at
|
|
BEFORE UPDATE ON sites FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_ftp_accounts_updated_at
|
|
BEFORE UPDATE ON ftp_accounts FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_ssl_certificates_updated_at
|
|
BEFORE UPDATE ON ssl_certificates FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_mail_accounts_updated_at
|
|
BEFORE UPDATE ON mail_accounts FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_dns_zones_updated_at
|
|
BEFORE UPDATE ON dns_zones FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_dns_records_updated_at
|
|
BEFORE UPDATE ON dns_records FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|
|
|
|
CREATE TRIGGER trg_cron_jobs_updated_at
|
|
BEFORE UPDATE ON cron_jobs FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
|