47 lines
2.5 KiB
SQL
47 lines
2.5 KiB
SQL
CREATE TABLE instance_memberships (
|
|
instance_id TEXT NOT NULL REFERENCES instances(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('user', 'manager')),
|
|
created_by TEXT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (instance_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX instance_memberships_user_idx ON instance_memberships(user_id, instance_id);
|
|
|
|
CREATE TABLE permission_overrides (
|
|
instance_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
permission TEXT NOT NULL CHECK (permission IN ('instance.view', 'instance.start', 'instance.stop', 'instance.restart', 'instance.update', 'instance.configure', 'instance.delete', 'instance.welcome.edit', 'metrics.view', 'players.view', 'players.kick', 'players.ban', 'players.unban', 'announcements.send', 'logs.view', 'mods.manage', 'backup.create', 'backup.list', 'backup.export', 'backup.restore', 'backup.delete', 'request.create')),
|
|
effect TEXT NOT NULL CHECK (effect IN ('allow', 'deny')),
|
|
created_by TEXT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (instance_id, user_id, permission),
|
|
FOREIGN KEY (instance_id, user_id) REFERENCES instance_memberships(instance_id, user_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE installation_requests (
|
|
id TEXT PRIMARY KEY,
|
|
requested_by TEXT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
template_id TEXT NOT NULL,
|
|
template_version TEXT NOT NULL,
|
|
suggested_name TEXT,
|
|
player_estimate INTEGER CHECK (player_estimate IS NULL OR (player_estimate >= 1 AND player_estimate <= 10000)),
|
|
desired_schedule TEXT,
|
|
mods_requested INTEGER NOT NULL DEFAULT 0 CHECK (mods_requested IN (0, 1)),
|
|
message TEXT,
|
|
status TEXT NOT NULL CHECK (status IN ('pending', 'approved', 'refused', 'cancelled')),
|
|
reviewed_by TEXT REFERENCES users(id) ON DELETE RESTRICT,
|
|
review_reason TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
reviewed_at TEXT,
|
|
FOREIGN KEY (template_id, template_version) REFERENCES template_versions(template_id, version) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX installation_requests_pending_idx ON installation_requests(requested_by, template_id, template_version) WHERE status = 'pending';
|
|
CREATE INDEX installation_requests_status_idx ON installation_requests(status, created_at);
|
|
CREATE INDEX installation_requests_requester_idx ON installation_requests(requested_by, created_at DESC);
|