111 lines
3.7 KiB
SQL
111 lines
3.7 KiB
SQL
-- PatchBay SQLite schema, shared by patchbayd (embedded at build time) and the
|
|
-- web frontend (copied into the package on install). Must stay idempotent.
|
|
|
|
PRAGMA journal_mode = WAL;
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
-- Users besides the sysop (who lives in patchbay.conf).
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL,
|
|
pwhash TEXT NOT NULL,
|
|
created INTEGER NOT NULL
|
|
);
|
|
|
|
-- Pending email 2FA logins.
|
|
CREATE TABLE IF NOT EXISTS login_codes (
|
|
token TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL,
|
|
codehash TEXT NOT NULL,
|
|
expires INTEGER NOT NULL,
|
|
attempts INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Failed login attempts for rate limiting (per remote address).
|
|
CREATE TABLE IF NOT EXISTS login_failures (
|
|
addr TEXT NOT NULL,
|
|
ts INTEGER NOT NULL
|
|
);
|
|
|
|
-- Authorised clients. pubkey is "type base64" without comment.
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
pubkey TEXT NOT NULL UNIQUE,
|
|
added_by TEXT NOT NULL,
|
|
created INTEGER NOT NULL,
|
|
hostname TEXT NOT NULL DEFAULT '',
|
|
last_addr TEXT NOT NULL DEFAULT '',
|
|
last_seen INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Patch graph. type: client_source, client_sink, public_sink, splitter,
|
|
-- tunnel_source, tunnel_sink. host: service address (sources) / bind address
|
|
-- (sinks). Tunnel nodes use iface; their client_id NULL means the target.
|
|
-- origin (sinks): '' | 'proxy_v2' (PROXY v2 header to the service) |
|
|
-- 'transparent' (source spoofing, source must be on a client).
|
|
-- Databases created before iface/origin existed are migrated by both programs.
|
|
CREATE TABLE IF NOT EXISTS nodes (
|
|
id INTEGER PRIMARY KEY,
|
|
type TEXT NOT NULL,
|
|
client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
|
|
host TEXT NOT NULL DEFAULT '',
|
|
port INTEGER NOT NULL DEFAULT 0,
|
|
proto TEXT NOT NULL DEFAULT 'tcp',
|
|
label TEXT NOT NULL DEFAULT '',
|
|
iface TEXT NOT NULL DEFAULT '',
|
|
origin TEXT NOT NULL DEFAULT '',
|
|
x REAL NOT NULL DEFAULT 0,
|
|
y REAL NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS links (
|
|
id INTEGER PRIMARY KEY,
|
|
from_node INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
to_node INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
|
UNIQUE (to_node)
|
|
);
|
|
|
|
-- Listening sockets per host, reported by the daemons. client_id 0 = target.
|
|
CREATE TABLE IF NOT EXISTS services (
|
|
client_id INTEGER NOT NULL,
|
|
proto TEXT NOT NULL,
|
|
addr TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
pid INTEGER NOT NULL,
|
|
process TEXT NOT NULL,
|
|
updated INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS services_client ON services (client_id);
|
|
|
|
-- tun interfaces per host as reported by the daemons. client_id 0 = target.
|
|
CREATE TABLE IF NOT EXISTS interfaces (
|
|
client_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
addr TEXT NOT NULL,
|
|
updated INTEGER NOT NULL
|
|
);
|
|
|
|
-- Traffic per connection (= per sink node). tier: 'm' minute, 'h' hour, 'd' day.
|
|
-- bytes_in: towards the source service, bytes_out: back to the connecting peer.
|
|
CREATE TABLE IF NOT EXISTS stats (
|
|
sink_id INTEGER NOT NULL,
|
|
tier TEXT NOT NULL,
|
|
ts INTEGER NOT NULL,
|
|
bytes_in INTEGER NOT NULL DEFAULT 0,
|
|
bytes_out INTEGER NOT NULL DEFAULT 0,
|
|
conns INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (sink_id, tier, ts)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS stats_tier_ts ON stats (tier, ts);
|
|
|
|
INSERT OR IGNORE INTO settings (key, value) VALUES
|
|
('stats_minute_hours', '48'),
|
|
('stats_hour_days', '90'),
|
|
('stats_day_days', '0');
|