44 lines
1.4 KiB
MySQL
44 lines
1.4 KiB
MySQL
|
-- Applied idempotently on every startup (CREATE TABLE IF NOT EXISTS).
|
||
|
|
--
|
||
|
|
-- Single-tenant deployments (GATEWAY_MODE=single) use this exact schema
|
||
|
|
-- with one implicit account row auto-seeded at startup — see
|
||
|
|
-- accounts.ensure_single_tenant_account(). Multi-tenant is not a later
|
||
|
|
-- reshape of this; it's the same tables with more than one account row.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
email TEXT,
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
registration_token_hash TEXT NOT NULL,
|
||
|
|
account_token_hash TEXT NOT NULL,
|
||
|
|
box_limit INTEGER NOT NULL DEFAULT 5,
|
||
|
|
route_limit_per_box INTEGER NOT NULL DEFAULT 5
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS boxes (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
account_id TEXT NOT NULL REFERENCES accounts(id),
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
wg_public_key TEXT NOT NULL UNIQUE,
|
||
|
|
wg_peer_id TEXT NOT NULL,
|
||
|
|
wg_allowed_ip TEXT NOT NULL,
|
||
|
|
box_token_hash TEXT NOT NULL,
|
||
|
|
registered_at TEXT NOT NULL,
|
||
|
|
last_seen_at TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS routes (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
box_id TEXT NOT NULL REFERENCES boxes(id),
|
||
|
|
account_id TEXT NOT NULL,
|
||
|
|
app_name TEXT NOT NULL,
|
||
|
|
subdomain TEXT NOT NULL UNIQUE,
|
||
|
|
target_port INTEGER NOT NULL,
|
||
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
||
|
|
created_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_boxes_account ON boxes(account_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_routes_box ON routes(box_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_routes_account ON routes(account_id);
|