133 lines
5.8 KiB
SQL
133 lines
5.8 KiB
SQL
-- PostgreSQL 17 schema for Amnezia VPN Share Panel (Go)
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'admin' CHECK (role IN ('admin')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS site_settings (
|
|
skey VARCHAR(64) PRIMARY KEY,
|
|
svalue TEXT,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS share_links (
|
|
id SERIAL PRIMARY KEY,
|
|
token CHAR(32) NOT NULL UNIQUE,
|
|
server_id INT NULL DEFAULT NULL,
|
|
expires_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
max_uses SMALLINT NOT NULL DEFAULT 5,
|
|
validity_days SMALLINT NULL DEFAULT NULL,
|
|
use_count SMALLINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
cleaned_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
traffic_quota_gb NUMERIC(12,3) NULL DEFAULT NULL,
|
|
traffic_used_gb NUMERIC(12,3) NOT NULL DEFAULT 0,
|
|
traffic_quota_blocked_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
subscription_until DATE NULL DEFAULT NULL,
|
|
allowed_server_ids JSONB NULL DEFAULT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_share_expires ON share_links (expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS share_link_creations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
share_link_id INT NOT NULL REFERENCES share_links(id) ON DELETE CASCADE,
|
|
server_id INT NOT NULL,
|
|
protocol VARCHAR(32) NOT NULL,
|
|
client_id VARCHAR(512) NOT NULL,
|
|
connection_name VARCHAR(128) NOT NULL,
|
|
response_json TEXT NULL DEFAULT NULL,
|
|
traffic_bytes_baseline BIGINT NOT NULL DEFAULT 0,
|
|
panel_traffic_bytes_total BIGINT NULL DEFAULT NULL,
|
|
panel_traffic_synced_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ NULL DEFAULT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_share_link ON share_link_creations (share_link_id);
|
|
CREATE INDEX IF NOT EXISTS idx_share_creations_deleted ON share_link_creations (share_link_id, deleted_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS panel_server_labels (
|
|
panel_server_id INT PRIMARY KEY,
|
|
title VARCHAR(512) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_panel_server_labels_updated ON panel_server_labels (updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS share_renewal_codes (
|
|
id SERIAL PRIMARY KEY,
|
|
code VARCHAR(64) NOT NULL UNIQUE,
|
|
add_days SMALLINT NOT NULL DEFAULT 30,
|
|
max_uses INT NOT NULL DEFAULT 1,
|
|
use_count INT NOT NULL DEFAULT 0,
|
|
share_link_id INT NULL DEFAULT NULL,
|
|
code_expires_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
note VARCHAR(255) NULL DEFAULT NULL,
|
|
code_target TEXT NOT NULL DEFAULT 'guest' CHECK (code_target IN ('guest', 'member')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_share_renewal_link ON share_renewal_codes (share_link_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS share_renewal_redemptions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
renewal_code_id INT NOT NULL REFERENCES share_renewal_codes(id) ON DELETE CASCADE,
|
|
share_link_id INT NOT NULL REFERENCES share_links(id) ON DELETE CASCADE,
|
|
add_days SMALLINT NOT NULL,
|
|
redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (renewal_code_id, share_link_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_redemption_link ON share_renewal_redemptions (share_link_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS members (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
email VARCHAR(255) NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_login_at TIMESTAMPTZ NULL DEFAULT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS member_subscriptions (
|
|
member_id INT PRIMARY KEY REFERENCES members(id) ON DELETE CASCADE,
|
|
expires_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
validity_days SMALLINT NULL DEFAULT NULL,
|
|
max_configs SMALLINT NOT NULL DEFAULT 5,
|
|
config_count SMALLINT NOT NULL DEFAULT 0,
|
|
cleaned_at TIMESTAMPTZ NULL DEFAULT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS member_configs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
member_id INT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
|
|
server_id INT NOT NULL,
|
|
protocol VARCHAR(32) NOT NULL,
|
|
client_id VARCHAR(512) NOT NULL,
|
|
connection_name VARCHAR(128) NOT NULL,
|
|
response_json TEXT NULL DEFAULT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ NULL DEFAULT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_member_configs_member ON member_configs (member_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS member_renewal_redemptions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
renewal_code_id INT NOT NULL REFERENCES share_renewal_codes(id) ON DELETE CASCADE,
|
|
member_id INT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
|
|
add_days SMALLINT NOT NULL,
|
|
redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (renewal_code_id, member_id)
|
|
);
|
|
|
|
-- scs session store
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
data BYTEA NOT NULL,
|
|
expiry TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sessions_expiry_idx ON sessions (expiry);
|