GraceSolutions
09a09d4a68
Phase 1 foundations: - Go backend with Chi router framework - SQLite database with WAL mode and foreign keys - Database migrations for users, roles, credentials, AD connections, schedules, rules, and audit - CLI commands: init, run, install, uninstall, start, stop, migrate, backup, restore, doctor - Configuration loading from environment variables - Centralized logging with file rotation (lumberjack) - Crypto package for Argon2id password hashing and AES-GCM encryption - Auth service with session management - Audit service for event logging - Scheduler with 6-field cron support - REST API routes scaffolded for all major resources - CORS support with localhost defaults for development - Docker support with Dockerfile and docker-compose.yml - Multi-platform build script (PowerShell) - Project structure per design specification Version format: yyyy.MM.dd.HHmm All PKs are UUIDv4, all timestamps UTC
115 lines
3.6 KiB
SQL
115 lines
3.6 KiB
SQL
-- Rules schema migration
|
|
|
|
-- Rules table
|
|
CREATE TABLE IF NOT EXISTS rules (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
ad_connection_id TEXT NOT NULL,
|
|
object_type TEXT NOT NULL,
|
|
base_dn_override TEXT,
|
|
search_scope_override TEXT,
|
|
schedule_id TEXT,
|
|
execution_mode TEXT NOT NULL DEFAULT 'Apply',
|
|
group_join_operator TEXT NOT NULL DEFAULT 'AND',
|
|
max_parallelism INTEGER,
|
|
stop_on_error INTEGER NOT NULL DEFAULT 0,
|
|
last_run_utc TEXT,
|
|
last_run_result TEXT,
|
|
created_utc TEXT NOT NULL,
|
|
updated_utc TEXT NOT NULL,
|
|
deleted_utc TEXT,
|
|
FOREIGN KEY (ad_connection_id) REFERENCES ad_connections(id),
|
|
FOREIGN KEY (schedule_id) REFERENCES schedules(id)
|
|
);
|
|
|
|
-- Rule condition groups
|
|
CREATE TABLE IF NOT EXISTS rule_condition_groups (
|
|
id TEXT PRIMARY KEY,
|
|
rule_id TEXT NOT NULL,
|
|
name TEXT,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
join_operator TEXT NOT NULL DEFAULT 'AND',
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
negate INTEGER NOT NULL DEFAULT 0,
|
|
created_utc TEXT NOT NULL,
|
|
updated_utc TEXT NOT NULL,
|
|
deleted_utc TEXT,
|
|
FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Rule conditions
|
|
CREATE TABLE IF NOT EXISTS rule_conditions (
|
|
id TEXT PRIMARY KEY,
|
|
condition_group_id TEXT NOT NULL,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
attribute_name TEXT NOT NULL,
|
|
operator TEXT NOT NULL,
|
|
value_type TEXT NOT NULL DEFAULT 'String',
|
|
comparison_value TEXT,
|
|
custom_ldap_expression TEXT,
|
|
negate INTEGER NOT NULL DEFAULT 0,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
case_sensitive INTEGER NOT NULL DEFAULT 0,
|
|
created_utc TEXT NOT NULL,
|
|
updated_utc TEXT NOT NULL,
|
|
deleted_utc TEXT,
|
|
FOREIGN KEY (condition_group_id) REFERENCES rule_condition_groups(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Rule actions
|
|
CREATE TABLE IF NOT EXISTS rule_actions (
|
|
id TEXT PRIMARY KEY,
|
|
rule_id TEXT NOT NULL,
|
|
action_type TEXT NOT NULL,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
configuration_json TEXT NOT NULL DEFAULT '{}',
|
|
rollback_mode TEXT,
|
|
created_utc TEXT NOT NULL,
|
|
updated_utc TEXT NOT NULL,
|
|
deleted_utc TEXT,
|
|
FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Rule runs (execution history)
|
|
CREATE TABLE IF NOT EXISTS rule_runs (
|
|
id TEXT PRIMARY KEY,
|
|
rule_id TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
started_utc TEXT NOT NULL,
|
|
completed_utc TEXT,
|
|
duration_ms INTEGER,
|
|
objects_matched INTEGER NOT NULL DEFAULT 0,
|
|
objects_processed INTEGER NOT NULL DEFAULT 0,
|
|
actions_executed INTEGER NOT NULL DEFAULT 0,
|
|
actions_failed INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT,
|
|
execution_mode TEXT NOT NULL,
|
|
triggered_by TEXT,
|
|
created_utc TEXT NOT NULL,
|
|
FOREIGN KEY (rule_id) REFERENCES rules(id)
|
|
);
|
|
|
|
-- Rule run action details
|
|
CREATE TABLE IF NOT EXISTS rule_run_actions (
|
|
id TEXT PRIMARY KEY,
|
|
rule_run_id TEXT NOT NULL,
|
|
rule_action_id TEXT NOT NULL,
|
|
object_dn TEXT NOT NULL,
|
|
action_type TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
details_json TEXT,
|
|
error_message TEXT,
|
|
duration_ms INTEGER,
|
|
created_utc TEXT NOT NULL,
|
|
FOREIGN KEY (rule_run_id) REFERENCES rule_runs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (rule_action_id) REFERENCES rule_actions(id)
|
|
);
|
|
|
|
-- Indexes for rule runs
|
|
CREATE INDEX IF NOT EXISTS idx_rule_runs_rule_id ON rule_runs(rule_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rule_runs_started_utc ON rule_runs(started_utc);
|
|
CREATE INDEX IF NOT EXISTS idx_rule_run_actions_rule_run_id ON rule_run_actions(rule_run_id);
|