DenozordecandCursor
d5784b9f35
- Updated the agent enrollment process to include an 'invited' status, allowing for better tracking of agent states. - Implemented support for install links that can now include an `install_link_id`, facilitating the transition from invited to pending status upon enrollment. - Enhanced the MikroTik installation script to include the `EvofwInstallLinkId` for better tracking and management. - Added new API endpoints for fetching agent policies and serving MikroTik-specific installation scripts. - Improved the web UI to reflect the new agent statuses and provide copyable installation commands for agents. Co-authored-by: Cursor <cursoragent@cursor.com>
65 lines
2.4 KiB
SQL
65 lines
2.4 KiB
SQL
-- Allow invited agents (created in UI before enroll) + link install invites to agents.
|
|
-- Rebuild agents to widen status CHECK (FK temporarily off).
|
|
|
|
PRAGMA foreign_keys = OFF;
|
|
|
|
CREATE TABLE agents_v2 (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
hostname TEXT,
|
|
platform TEXT NOT NULL DEFAULT 'linux',
|
|
token_prefix TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
policy_mode TEXT NOT NULL DEFAULT 'blacklist',
|
|
policy_generation INTEGER NOT NULL DEFAULT 1,
|
|
last_seen_at TEXT,
|
|
last_seen_ip TEXT,
|
|
last_apply_at TEXT,
|
|
last_apply_status TEXT,
|
|
last_apply_error TEXT,
|
|
last_apply_prefix_count INTEGER DEFAULT 0,
|
|
last_apply_packets_dropped INTEGER NOT NULL DEFAULT 0,
|
|
last_apply_packets_accepted INTEGER NOT NULL DEFAULT 0,
|
|
last_apply_kernel_method TEXT,
|
|
client_version TEXT,
|
|
settings_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by_user_id TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
approved_at TEXT,
|
|
revoked_at TEXT,
|
|
CHECK (status IN ('invited', 'pending', 'approved', 'revoked')),
|
|
CHECK (platform IN ('linux', 'mikrotik')),
|
|
CHECK (policy_mode IN ('blacklist', 'whitelist')),
|
|
CHECK (length(trim(name)) > 0)
|
|
);
|
|
|
|
INSERT INTO agents_v2 (
|
|
id, name, hostname, platform, token_prefix, token_hash, status, policy_mode,
|
|
policy_generation, last_seen_at, last_seen_ip, last_apply_at, last_apply_status,
|
|
last_apply_error, last_apply_prefix_count, last_apply_packets_dropped,
|
|
last_apply_packets_accepted, last_apply_kernel_method, client_version,
|
|
settings_json, created_by_user_id, created_at, approved_at, revoked_at
|
|
)
|
|
SELECT
|
|
id, name, hostname, platform, token_prefix, token_hash, status, policy_mode,
|
|
policy_generation, last_seen_at, last_seen_ip, last_apply_at, last_apply_status,
|
|
last_apply_error, last_apply_prefix_count, last_apply_packets_dropped,
|
|
last_apply_packets_accepted, last_apply_kernel_method, client_version,
|
|
settings_json, created_by_user_id, created_at, approved_at, revoked_at
|
|
FROM agents;
|
|
|
|
DROP TABLE agents;
|
|
ALTER TABLE agents_v2 RENAME TO agents;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_agents_token_hash ON agents (token_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_agents_status ON agents (status);
|
|
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
-- Bind install links to agents
|
|
ALTER TABLE agent_install_links ADD COLUMN agent_id TEXT REFERENCES agents (id) ON DELETE CASCADE;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_install_links_agent
|
|
ON agent_install_links (agent_id);
|