Restore
Restore
Restore file to captured state
Every captured change carries its SHA-256-addressed content in BLOB_STORE. Restoring writes that content back to the original path — current file gets backed up to <path>.drift_restore_backup_<epoch> before overwrite.
What will change
Preview -- nothing has been written yet.
| Target file | /var/www/vhosts/3dshawn.com/site1/_alert_dispatch.pl |
| Site | DriftSense self-monitor on local |
| Kind | local |
| Captured at | 2026-07-12 00:02:08 |
| Captured SHA | 50aef6cbce02f3e212b0302289efed426c35ba295f6c82008a8af3f3a87c9d8b |
Current state on disk
Live check just now. If SHAs match, the restore is a no-op.
| File present | yes |
| Size | 22933 bytes |
| Current SHA | a53566efc691 |
| Same as captured? | no -- restore will change it |
What restore will change — live diff
Left column shows current on-disk content; right shows what restore will write. +1 additions, -185 deletions, 408 unchanged context lines.
| 1 | 1 | #!/usr/bin/perl |
| 2 | 2 | #====================================================================== |
| 3 | 3 | # DriftSense -- alert dispatcher |
| 4 | 4 | # |
| 5 | 5 | # Cron entry point (NOT a CGI). Walks FILE_CHANGES + SCHEMA_CHANGE rows |
| 6 | 6 | # inserted since the last cursor tick, evaluates every ACTIVE rule |
| 7 | 7 | # against each new row, and POSTs matching payloads to the configured |
| 8 | 8 | # webhook (Slack / Discord / generic HTTP). Every attempt is logged |
| 9 | 9 | # to ALERT_DELIVERIES for the operator's audit trail. |
| 10 | 10 | # |
| 11 | 11 | # Config: /etc/drift_sense/drift_sense.conf (via MODS::Config). |
| 12 | 12 | # Log: /var/log/drift_sense/alert_dispatch.log (via cron redirect). |
| 13 | 13 | #====================================================================== |
| 14 | 14 | use strict; |
| 15 | 15 | use warnings; |
| 16 | 16 | use lib '/var/www/vhosts/3dshawn.com/site1'; |
| 17 | 17 | use POSIX (); |
| 18 | use Symbol (); | |
| 19 | 18 | use JSON::PP; |
| 20 | 19 | use MODS::Config; |
| 21 | 20 | use MODS::DBConnect; |
| 22 | 21 | use MODS::Webhook; |
| 23 | 22 | |
| 24 | 23 | $| = 1; |
| 25 | 24 | my $ts = POSIX::strftime('%Y-%m-%d %H:%M:%S', localtime); |
| 26 | 25 | |
| 27 | 26 | my $cfg = MODS::Config->new; |
| 28 | 27 | my $db = MODS::DBConnect->new; |
| 29 | 28 | my $dbh = $db->db_connect or die "[$ts] cannot connect to DriftSense storage DB\n"; |
| 30 | 29 | |
| 31 | 30 | my $public_url = $cfg->settings('public_url') || 'https://watchtower.3dshawn.com'; |
| 32 | 31 | |
| 33 | 32 | # ---- Fetch active rules --------------------------------------------- |
| 34 | 33 | my @rules = $db->db_readwrite_multiple($dbh, q~ |
| 35 | 34 | SELECT alert_rule_id, rule_name, match_kind, match_path_glob, |
| 36 | 35 | match_status_list, exclude_ts_only, |
| 37 | 36 | delivery_kind, delivery_url, delivery_email, |
| 38 | 37 | rate_limit_per_min, coalesce_window_s, |
| 39 | 38 | frequency_threshold, frequency_window_min, |
| 40 | 39 | UNIX_TIMESTAMP(last_frequency_fire_at) AS last_freq_fire_epoch |
| 41 | 40 | FROM ALERT_RULES |
| 42 | 41 | WHERE is_active = 1 |
| 43 | 42 | ~, __FILE__, __LINE__); |
| 44 | 43 | |
| 45 | 44 | unless (@rules) { |
| 46 | 45 | print "[$ts] no active alert rules, exiting\n"; |
| 47 | 46 | $db->db_disconnect($dbh); |
| 48 | 47 | exit 0; |
| 49 | 48 | } |
| 50 | 49 | |
| 51 | 50 | # ---- Fetch cursor --------------------------------------------------- |
| 52 | 51 | my %cursor; |
| 53 | 52 | foreach my $r ($db->db_readwrite_multiple($dbh, q~ |
| 54 | 53 | SELECT source_kind, last_seen_id FROM ALERT_CURSOR |
| 55 | 54 | ~, __FILE__, __LINE__)) { |
| 56 | 55 | $cursor{$r->{source_kind}} = $r->{last_seen_id}; |
| 57 | 56 | } |
| 58 | 57 | $cursor{file} //= 0; |
| 59 | 58 | $cursor{schema} //= 0; |
| 60 | 59 | |
| 61 | 60 | # ---- Pull new FILE_CHANGES since cursor ------------------------------ |
| 62 | 61 | my $file_max = $cursor{file}; |
| 63 | 62 | my @file_new = $db->db_readwrite_multiple($dbh, qq~ |
| 64 | 63 | SELECT fc.file_changes_id AS id, fc.file_name, fc.status, fc.blob_sha, |
| 65 | 64 | fc.is_ts_only, fc.date_time, |
| 66 | 65 | UNIX_TIMESTAMP(fc.date_time) AS ts_epoch, |
| 67 | 66 | s.server_name |
| 68 | 67 | FROM FILE_CHANGES fc |
| 69 | 68 | LEFT JOIN SERVERS s ON s.server_id = fc.server_id |
| 70 | 69 | WHERE fc.file_changes_id > $cursor{file} |
| 71 | 70 | ORDER BY fc.file_changes_id ASC |
| 72 | 71 | LIMIT 500 |
| 73 | 72 | ~, __FILE__, __LINE__); |
| 74 | 73 | foreach my $r (@file_new) { |
| 75 | 74 | $file_max = $r->{id} if $r->{id} > $file_max; |
| 76 | 75 | } |
| 77 | 76 | |
| 78 | 77 | # ---- Pull new SCHEMA_CHANGE since cursor ----------------------------- |
| 79 | 78 | my $schema_max = $cursor{schema}; |
| 80 | 79 | my @schema_new = $db->db_readwrite_multiple($dbh, qq~ |
| 81 | 80 | SELECT sc.schema_change_id AS id, sc.database_name, sc.table_name, |
| 82 | 81 | sc.changes, sc.is_ts_only, sc.change_datetime, |
| 83 | 82 | UNIX_TIMESTAMP(sc.change_datetime) AS ts_epoch, |
| 84 | 83 | s.server_name |
| 85 | 84 | FROM SCHEMA_CHANGE sc |
| 86 | 85 | LEFT JOIN SERVERS s ON s.server_id = sc.server_id |
| 87 | 86 | WHERE sc.schema_change_id > $cursor{schema} |
| 88 | 87 | ORDER BY sc.schema_change_id ASC |
| 89 | 88 | LIMIT 500 |
| 90 | 89 | ~, __FILE__, __LINE__); |
| 91 | 90 | foreach my $r (@schema_new) { |
| 92 | 91 | $schema_max = $r->{id} if $r->{id} > $schema_max; |
| 93 | 92 | } |
| 94 | 93 | |
| 95 | 94 | my $total_new = scalar(@file_new) + scalar(@schema_new); |
| 96 | 95 | if ($total_new == 0) { |
| 97 | 96 | print "[$ts] no new changes since cursor (file=$cursor{file} schema=$cursor{schema})\n"; |
| 98 | 97 | $db->db_disconnect($dbh); |
| 99 | 98 | exit 0; |
| 100 | 99 | } |
| 101 | 100 | |
| 102 | 101 | # ---- Evaluate + deliver --------------------------------------------- |
| 103 | 102 | # HTTP transport is MODS::Webhook (shells out to /usr/bin/curl) so we |
| 104 | 103 | # don't need LWP::Protocol::https / IO::Socket::SSL as CPAN installs. |
| 105 | 104 | |
| 106 | 105 | my $delivered = 0; |
| 107 | 106 | my $skipped = 0; |
| 108 | 107 | my $failed = 0; |
| 109 | 108 | |
| 110 | 109 | foreach my $row (@file_new) { _process_row($row, 'file'); } |
| 111 | 110 | foreach my $row (@schema_new) { _process_row($row, 'schema'); } |
| 112 | 111 | |
| 113 | 112 | # ---- Frequency-mode rules ------------------------------------------ |
| 114 | 113 | # For any rule where frequency_threshold > 0, we ignore the per-row loop |
| 115 | 114 | # above and instead: query how many file changes matching the rule's |
| 116 | 115 | # path glob happened in the last window_min minutes. If it crosses the |
| 117 | 116 | # threshold and we haven't fired within the same window, fire once with |
| 118 | 117 | # the top offender. |
| 119 | 118 | foreach my $rule (@rules) { |
| 120 | 119 | next unless ($rule->{frequency_threshold} || 0) > 0; |
| 121 | 120 | my $win = int($rule->{frequency_window_min} || 60); |
| 122 | 121 | my $thr = int($rule->{frequency_threshold}); |
| 123 | 122 | |
| 124 | 123 | # Re-fire cooldown: don't fire more than once per window |
| 125 | 124 | my $last = int($rule->{last_freq_fire_epoch} || 0); |
| 126 | 125 | if ($last && (time - $last) < $win * 60) { |
| 127 | 126 | next; |
| 128 | 127 | } |
| 129 | 128 | |
| 130 | 129 | # Find files whose match_path_glob-hit change count > threshold |
| 131 | 130 | my $glob = $rule->{match_path_glob} // ''; |
| 132 | 131 | my $glob_sql = ''; |
| 133 | 132 | if (length $glob) { |
| 134 | 133 | # Approx: SQL LIKE. Convert glob wildcards to SQL % / _. |
| 135 | 134 | my $like = $glob; |
| 136 | 135 | $like =~ s/\*\*/%/g; |
| 137 | 136 | $like =~ s/\*/%/g; |
| 138 | 137 | $like =~ s/\?/_/g; |
| 139 | 138 | my $q_like = $dbh->quote($like); |
| 140 | 139 | $glob_sql = " AND fc.file_name LIKE $q_like"; |
| 141 | 140 | } |
| 142 | 141 | my @hot = $db->db_readwrite_multiple($dbh, qq~ |
| 143 | 142 | SELECT fc.file_name, |
| 144 | 143 | COUNT(*) AS ct, |
| 145 | 144 | MAX(fc.file_changes_id) AS latest_id, |
| 146 | 145 | s.server_name |
| 147 | 146 | FROM FILE_CHANGES fc |
| 148 | 147 | LEFT JOIN SERVERS s ON s.server_id = fc.server_id |
| 149 | 148 | WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL $win MINUTE) |
| 150 | 149 | $glob_sql |
| 151 | 150 | GROUP BY fc.file_name |
| 152 | 151 | HAVING ct >= $thr |
| 153 | 152 | ORDER BY ct DESC |
| 154 | 153 | LIMIT 5 |
| 155 | 154 | ~, __FILE__, __LINE__); |
| 156 | 155 | next unless @hot; |
| 157 | 156 | |
| 158 | 157 | # Fire once with the top offender's info |
| 159 | 158 | my $top = $hot[0]; |
| 160 | 159 | my $fake_row = { |
| 161 | 160 | id => $top->{latest_id}, |
| 162 | 161 | file_name => $top->{file_name}, |
| 163 | 162 | server_name => $top->{server_name} // 'local', |
| 164 | 163 | date_time => POSIX::strftime('%Y-%m-%d %H:%M:%S', localtime), |
| 165 | 164 | ts_epoch => time, |
| 166 | 165 | status => 'frequency_alert', |
| 167 | 166 | }; |
| 168 | 167 | my $payload = _build_payload($rule, $fake_row, 'file'); |
| 169 | 168 | # Enrich summary with count + threshold |
| 170 | 169 | my $extra = "frequency alert: $top->{file_name} changed $top->{ct} times in the last $win min (threshold: $thr)"; |
| 171 | 170 | if (ref($payload->{generic_webhook}) eq 'HASH') { |
| 172 | 171 | $payload->{generic_webhook}{frequency_summary} = $extra; |
| 173 | 172 | $payload->{generic_webhook}{frequency_count} = $top->{ct}; |
| 174 | 173 | $payload->{generic_webhook}{frequency_window} = $win; |
| 175 | 174 | $payload->{generic_webhook}{summary} = $extra; |
| 176 | 175 | } |
| 177 | 176 | my ($status, $http_code, $body, $err) = _deliver($rule, $payload); |
| 178 | 177 | _log_delivery($rule, $fake_row, 'file', $status, $http_code, $body, $err); |
| 179 | 178 | if ($status eq 'sent') { |
| 180 | 179 | $delivered++; |
| 181 | 180 | $db->db_readwrite($dbh, qq~ |
| 182 | 181 | UPDATE ALERT_RULES |
| 183 | 182 | SET fire_count = fire_count + 1, |
| 184 | 183 | last_fired_at = NOW(), |
| 185 | 184 | last_frequency_fire_at = NOW() |
| 186 | 185 | WHERE alert_rule_id = $rule->{alert_rule_id} |
| 187 | 186 | ~, __FILE__, __LINE__); |
| 188 | 187 | } else { |
| 189 | 188 | $failed++; |
| 190 | 189 | } |
| 191 | 190 | } |
| 192 | 191 | |
| 193 | 192 | # ---- Advance cursor atomically -------------------------------------- |
| 194 | 193 | if ($file_max > $cursor{file}) { |
| 195 | 194 | $db->db_readwrite($dbh, |
| 196 | 195 | "UPDATE ALERT_CURSOR SET last_seen_id = $file_max WHERE source_kind = 'file'", |
| 197 | 196 | __FILE__, __LINE__); |
| 198 | 197 | } |
| 199 | 198 | if ($schema_max > $cursor{schema}) { |
| 200 | 199 | $db->db_readwrite($dbh, |
| 201 | 200 | "UPDATE ALERT_CURSOR SET last_seen_id = $schema_max WHERE source_kind = 'schema'", |
| 202 | 201 | __FILE__, __LINE__); |
| 203 | } | |
| 204 | ||
| 205 | # ---- Pin-drift alerts (Feature 5 wave 4) --------------------------- | |
| 206 | # For each Named Release with alert_delivery_url set, check whether any | |
| 207 | # pin's current-latest SHA differs from the pinned SHA. Log the drift | |
| 208 | # to PIN_DRIFT_LOG (with notified=0) and, if not yet notified, POST an | |
| 209 | # alert to the release's delivery URL, then set notified=1. | |
| 210 | foreach my $rel ($db->db_readwrite_multiple($dbh, q~ | |
| 211 | SELECT named_release_id, name, alert_delivery_kind, alert_delivery_url | |
| 212 | FROM NAMED_RELEASES | |
| 213 | WHERE alert_delivery_kind IS NOT NULL | |
| 214 | AND alert_delivery_kind != 'none' | |
| 215 | AND alert_delivery_url IS NOT NULL | |
| 216 | AND alert_delivery_url != '' | |
| 217 | ~, __FILE__, __LINE__)) { | |
| 218 | ||
| 219 | my $rid = $rel->{named_release_id}; | |
| 220 | my @drifts; | |
| 221 | foreach my $pin ($db->db_readwrite_multiple($dbh, qq~ | |
| 222 | SELECT p.pin_id, p.file_name, p.blob_sha AS pinned_sha, | |
| 223 | fc.server_id | |
| 224 | FROM NAMED_RELEASE_PINS p | |
| 225 | LEFT JOIN FILE_CHANGES fc ON fc.file_changes_id = p.file_changes_id | |
| 226 | WHERE p.named_release_id = $rid | |
| 227 | ~, __FILE__, __LINE__)) { | |
| 228 | my $q_file = $dbh->quote($pin->{file_name}); | |
| 229 | my $sid = int($pin->{server_id} || 0); | |
| 230 | my $cur = $db->db_readwrite($dbh, qq~ | |
| 231 | SELECT blob_sha, file_changes_id FROM FILE_CHANGES | |
| 232 | WHERE server_id = $sid AND file_name = $q_file | |
| 233 | ORDER BY file_changes_id DESC LIMIT 1 | |
| 234 | ~, __FILE__, __LINE__); | |
| 235 | my $cur_sha = $cur ? $cur->{blob_sha} : ''; | |
| 236 | next unless $cur_sha && $cur_sha ne $pin->{pinned_sha}; | |
| 237 | ||
| 238 | # Have we already logged + notified this (pin, current_sha) tuple? | |
| 239 | my $q_pinned = $dbh->quote($pin->{pinned_sha}); | |
| 240 | my $q_current = $dbh->quote($cur_sha); | |
| 241 | my $seen = $db->db_readwrite($dbh, qq~ | |
| 242 | SELECT drift_id, notified FROM PIN_DRIFT_LOG | |
| 243 | WHERE pin_id = $pin->{pin_id} | |
| 244 | AND current_sha = $q_current | |
| 245 | ORDER BY drift_id DESC LIMIT 1 | |
| 246 | ~, __FILE__, __LINE__); | |
| 247 | if ($seen && $seen->{notified}) { | |
| 248 | next; # already notified for this exact drift state | |
| 249 | } | |
| 250 | ||
| 251 | # Log the drift | |
| 252 | my $q_fname = $dbh->quote($pin->{file_name}); | |
| 253 | my $cid = int($cur->{file_changes_id} || 0); | |
| 254 | $db->db_readwrite($dbh, qq~ | |
| 255 | INSERT INTO PIN_DRIFT_LOG | |
| 256 | (named_release_id, pin_id, file_name, pinned_sha, current_sha, file_changes_id, notified) | |
| 257 | VALUES | |
| 258 | ($rid, $pin->{pin_id}, $q_fname, $q_pinned, $q_current, $cid, 0) | |
| 259 | ~, __FILE__, __LINE__); | |
| 260 | push @drifts, { | |
| 261 | file_name => $pin->{file_name}, | |
| 262 | pinned_sha => $pin->{pinned_sha}, | |
| 263 | current_sha => $cur_sha, | |
| 264 | change_id => $cid, | |
| 265 | }; | |
| 266 | } | |
| 267 | ||
| 268 | next unless @drifts; | |
| 269 | ||
| 270 | # Build a summary payload | |
| 271 | my $summary = sprintf('Named release "%s" -- %d pinned file(s) have drifted', $rel->{name}, scalar @drifts); | |
| 272 | my $public_url = $cfg->settings('public_url') || 'https://watchtower.3dshawn.com'; | |
| 273 | my $files_list = join("\n", map { | |
| 274 | " * $_->{file_name} (pinned: " . substr($_->{pinned_sha}, 0, 12) . | |
| 275 | " -> current: " . substr($_->{current_sha}, 0, 12) . ")" | |
| 276 | } @drifts); | |
| 277 | my $link = "$public_url/restore_release.cgi?release_id=$rid"; | |
| 278 | ||
| 279 | my $slack_body = { | |
| 280 | text => "*DriftSense pin drift*: $summary", | |
| 281 | attachments => [{ | |
| 282 | color => '#f43f5e', | |
| 283 | fields => [ | |
| 284 | { title => 'Release', value => $rel->{name}, short => 1 }, | |
| 285 | { title => 'Drifted files', value => scalar(@drifts), short => 1 }, | |
| 286 | { title => 'Details', value => $files_list, short => 0 }, | |
| 287 | ], | |
| 288 | actions => [{ type => 'button', text => 'Restore all pinned', url => $link }], | |
| 289 | }], | |
| 290 | }; | |
| 291 | my $discord_body = { | |
| 292 | content => "**DriftSense pin drift**: $summary", | |
| 293 | embeds => [{ | |
| 294 | title => $rel->{name}, | |
| 295 | url => $link, | |
| 296 | color => 16005694, | |
| 297 | description => $files_list, | |
| 298 | }], | |
| 299 | }; | |
| 300 | my $generic_body = { | |
| 301 | source => 'drift_sense', | |
| 302 | alert_type => 'pin_drift', | |
| 303 | release_id => $rid, | |
| 304 | release_name => $rel->{name}, | |
| 305 | drifted_count=> scalar @drifts, | |
| 306 | summary => $summary, | |
| 307 | files => \@drifts, | |
| 308 | restore_url => $link, | |
| 309 | }; | |
| 310 | ||
| 311 | my $body_ref = $rel->{alert_delivery_kind} eq 'slack' ? $slack_body | |
| 312 | : $rel->{alert_delivery_kind} eq 'discord' ? $discord_body | |
| 313 | : $generic_body; | |
| 314 | ||
| 315 | my ($code, $rbody, $err) = MODS::Webhook::post_json( | |
| 316 | $rel->{alert_delivery_url}, $body_ref, | |
| 317 | timeout => 8, | |
| 318 | user_agent => 'DriftSense/1.0 (pin-drift)', | |
| 319 | ); | |
| 320 | ||
| 321 | if (!$err && $code >= 200 && $code < 400) { | |
| 322 | # Mark all just-logged rows as notified | |
| 323 | foreach my $d (@drifts) { | |
| 324 | my $q_current = $dbh->quote($d->{current_sha}); | |
| 325 | $db->db_readwrite($dbh, qq~ | |
| 326 | UPDATE PIN_DRIFT_LOG SET notified = 1 | |
| 327 | WHERE named_release_id = $rid | |
| 328 | AND file_name = @{[ $dbh->quote($d->{file_name}) ]} | |
| 329 | AND current_sha = $q_current | |
| 330 | AND notified = 0 | |
| 331 | ~, __FILE__, __LINE__); | |
| 332 | } | |
| 333 | $delivered++; | |
| 334 | print "[$ts] pin-drift alert delivered for release '$rel->{name}' (" . scalar(@drifts) . " drifted)\n"; | |
| 335 | } else { | |
| 336 | $failed++; | |
| 337 | print "[$ts] pin-drift alert FAILED for release '$rel->{name}': " . ($err // "HTTP $code") . "\n"; | |
| 338 | } | |
| 339 | ||
| 340 | # Bookkeep the check timestamp | |
| 341 | $db->db_readwrite($dbh, qq~ | |
| 342 | UPDATE NAMED_RELEASES SET last_pin_drift_check_at = NOW() | |
| 343 | WHERE named_release_id = $rid | |
| 344 | ~, __FILE__, __LINE__); | |
| 345 | 202 | } |
| 346 | 203 | |
| 347 | 204 | $db->db_disconnect($dbh); |
| 348 | 205 | print "[$ts] processed +$total_new new changes: $delivered delivered, $skipped skipped, $failed failed\n"; |
| 349 | 206 | exit 0; |
| 350 | 207 | |
| 351 | 208 | #--------------------------------------------------------------------- |
| 352 | 209 | sub _process_row { |
| 353 | 210 | my ($row, $kind) = @_; |
| 354 | 211 | foreach my $rule (@rules) { |
| 355 | 212 | next unless _rule_matches($rule, $row, $kind); |
| 356 | 213 | |
| 357 | 214 | # Rate limit check |
| 358 | 215 | if ($rule->{rate_limit_per_min} > 0) { |
| 359 | 216 | my $rlq = $db->db_readwrite($dbh, qq~ |
| 360 | 217 | SELECT COUNT(*) AS n FROM ALERT_DELIVERIES |
| 361 | 218 | WHERE alert_rule_id = $rule->{alert_rule_id} |
| 362 | 219 | AND fired_at >= DATE_SUB(NOW(), INTERVAL 1 MINUTE) |
| 363 | 220 | AND delivery_status IN ('sent','pending') |
| 364 | 221 | ~, __FILE__, __LINE__); |
| 365 | 222 | if ($rlq && $rlq->{n} >= $rule->{rate_limit_per_min}) { |
| 366 | 223 | _log_delivery($rule, $row, $kind, 'skipped', undef, undef, |
| 367 | 224 | "rate limit ($rule->{rate_limit_per_min}/min) reached"); |
| 368 | 225 | $skipped++; |
| 369 | 226 | next; |
| 370 | 227 | } |
| 371 | 228 | } |
| 372 | 229 | |
| 373 | 230 | # Build payload + POST |
| 374 | 231 | my $payload = _build_payload($rule, $row, $kind); |
| 375 | 232 | my ($status, $http_code, $body, $err) = _deliver($rule, $payload); |
| 376 | 233 | _log_delivery($rule, $row, $kind, $status, $http_code, $body, $err); |
| 377 | 234 | |
| 378 | 235 | if ($status eq 'sent') { $delivered++; } |
| 379 | 236 | elsif ($status eq 'skipped') { $skipped++; } |
| 380 | 237 | else { $failed++; } |
| 381 | 238 | |
| 382 | 239 | # Bump the rule's fire_count + last_fired_at |
| 383 | 240 | if ($status eq 'sent') { |
| 384 | 241 | $db->db_readwrite($dbh, qq~ |
| 385 | 242 | UPDATE ALERT_RULES |
| 386 | 243 | SET fire_count = fire_count + 1, |
| 387 | 244 | last_fired_at = NOW() |
| 388 | 245 | WHERE alert_rule_id = $rule->{alert_rule_id} |
| 389 | 246 | ~, __FILE__, __LINE__); |
| 390 | 247 | } |
| 391 | 248 | } |
| 392 | 249 | } |
| 393 | 250 | |
| 394 | 251 | #--------------------------------------------------------------------- |
| 395 | 252 | sub _rule_matches { |
| 396 | 253 | my ($rule, $row, $kind) = @_; |
| 397 | 254 | |
| 398 | 255 | # Kind filter |
| 399 | 256 | return 0 if $rule->{match_kind} ne 'either' && $rule->{match_kind} ne $kind; |
| 400 | 257 | |
| 401 | 258 | # ts-only exclusion (files only) |
| 402 | 259 | return 0 if $kind eq 'file' && $rule->{exclude_ts_only} && $row->{is_ts_only}; |
| 403 | 260 | |
| 404 | 261 | # Path glob |
| 405 | 262 | my $glob = $rule->{match_path_glob} // ''; |
| 406 | 263 | if (length $glob) { |
| 407 | 264 | my $target = $kind eq 'file' |
| 408 | 265 | ? ($row->{file_name} // '') |
| 409 | 266 | : (($row->{database_name} // '') . '.' . ($row->{table_name} // '')); |
| 410 | 267 | my $re = _glob_to_regex($glob); |
| 411 | 268 | return 0 unless $target =~ $re; |
| 412 | 269 | } |
| 413 | 270 | |
| 414 | 271 | # Status filter (files only) |
| 415 | 272 | if ($kind eq 'file' && $rule->{match_status_list}) { |
| 416 | 273 | my %allowed = map { s/^\s+|\s+$//g; $_ => 1 } |
| 417 | 274 | split /,/, $rule->{match_status_list}; |
| 418 | 275 | return 0 unless $allowed{ $row->{status} // '' }; |
| 419 | 276 | } |
| 420 | 277 | |
| 421 | 278 | return 1; |
| 422 | 279 | } |
| 423 | 280 | |
| 424 | 281 | sub _glob_to_regex { |
| 425 | 282 | my $g = shift; |
| 426 | 283 | my $re = quotemeta $g; |
| 427 | 284 | # Handle glob patterns: escaped meta chars back to regex meaning |
| 428 | 285 | $re =~ s{\\\*\\\*}{.*}g; # ** -> .* |
| 429 | 286 | $re =~ s{\\\*}{[^/]*}g; # * -> [^/]* |
| 430 | 287 | $re =~ s{\\\?}{.}g; # ? -> . |
| 431 | 288 | return qr/^$re$/; |
| 432 | 289 | } |
| 433 | 290 | |
| 434 | 291 | #--------------------------------------------------------------------- |
| 435 | 292 | sub _build_payload { |
| 436 | 293 | my ($rule, $row, $kind) = @_; |
| 437 | 294 | |
| 438 | 295 | my $where = $kind eq 'file' |
| 439 | 296 | ? ($row->{file_name} // '(unknown file)') |
| 440 | 297 | : (($row->{database_name} // '?') . '.' . ($row->{table_name} // '?')); |
| 441 | 298 | my $when_utc = $row->{date_time} || $row->{change_datetime} || ''; |
| 442 | 299 | my $server = $row->{server_name} || 'local'; |
| 443 | 300 | my $status_h = $kind eq 'file' ? ($row->{status} // '') : 'DDL change'; |
| 444 | 301 | my $link_id = $row->{id}; |
| 445 | 302 | my $link = "$public_url/diff.cgi?kind=$kind&id=$link_id"; |
| 446 | 303 | |
| 447 | 304 | my $summary = $kind eq 'file' |
| 448 | 305 | ? "$status_h: $where on $server" |
| 449 | 306 | : "$where changed on $server"; |
| 450 | 307 | |
| 451 | 308 | my $slack = { |
| 452 | 309 | text => "*DriftSense*: $summary", |
| 453 | 310 | attachments => [{ |
| 454 | 311 | color => $kind eq 'schema' ? '#f59e0b' : '#14b8a6', |
| 455 | 312 | fields => [ |
| 456 | 313 | { title => 'What', value => $where, short => 0 }, |
| 457 | 314 | { title => 'When', value => $when_utc, short => 1 }, |
| 458 | 315 | { title => 'Server', value => $server, short => 1 }, |
| 459 | 316 | ], |
| 460 | 317 | actions => [{ |
| 461 | 318 | type => 'button', text => 'View diff', url => $link, |
| 462 | 319 | }], |
| 463 | 320 | }], |
| 464 | 321 | }; |
| 465 | 322 | my $discord = { |
| 466 | 323 | content => "**DriftSense**: $summary", |
| 467 | 324 | embeds => [{ |
| 468 | 325 | title => $where, |
| 469 | 326 | url => $link, |
| 470 | 327 | color => $kind eq 'schema' ? 16101915 : 1349286, # amber, teal |
| 471 | 328 | fields => [ |
| 472 | 329 | { name => 'When', value => $when_utc || '?', inline => \1 }, |
| 473 | 330 | { name => 'Server', value => $server, inline => \1 }, |
| 474 | 331 | ], |
| 475 | 332 | }], |
| 476 | 333 | }; |
| 477 | 334 | my $generic = { |
| 478 | 335 | source => 'drift_sense', |
| 479 | 336 | rule_id => $rule->{alert_rule_id}, |
| 480 | 337 | rule_name => $rule->{rule_name}, |
| 481 | 338 | kind => $kind, |
| 482 | 339 | summary => $summary, |
| 483 | 340 | target => $where, |
| 484 | 341 | server => $server, |
| 485 | 342 | when_utc => $when_utc, |
| 486 | 343 | ts_epoch => $row->{ts_epoch}, |
| 487 | 344 | diff_url => $link, |
| 488 | 345 | status => $status_h, |
| 489 | 346 | }; |
| 490 | 347 | |
| 491 | 348 | return { |
| 492 | 349 | slack => $slack, |
| 493 | 350 | discord => $discord, |
| 494 | 351 | generic_webhook => $generic, |
| 495 | 352 | summary => $summary, |
| 496 | 353 | }; |
| 497 | 354 | } |
| 498 | 355 | |
| 499 | 356 | #--------------------------------------------------------------------- |
| 500 | 357 | sub _deliver { |
| 501 | 358 | my ($rule, $payload) = @_; |
| 502 | 359 | my $kind = $rule->{delivery_kind}; |
| 503 | 360 | my $url = $rule->{delivery_url}; |
| 504 | 361 | |
| 505 | 362 | if ($kind eq 'email') { |
| 506 | my $to = $rule->{delivery_email} || ''; | |
| 507 | return ('failed', undef, undef, 'no delivery_email configured') unless length $to; | |
| 508 | my $summary = $payload->{summary} // 'DriftSense alert'; | |
| 509 | my $target = ($payload->{generic_webhook} && $payload->{generic_webhook}{target}) // '(unknown)'; | |
| 510 | my $server = ($payload->{generic_webhook} && $payload->{generic_webhook}{server}) // 'local'; | |
| 511 | my $link = ($payload->{generic_webhook} && $payload->{generic_webhook}{diff_url}) // ''; | |
| 512 | ||
| 513 | my $mail_body = "DriftSense captured a change matching alert rule: $rule->{rule_name}\n\n" | |
| 514 | . "Target : $target\n" | |
| 515 | . "Server : $server\n" | |
| 516 | . "Summary: $summary\n" | |
| 517 | . ($link ? "Diff : $link\n" : '') | |
| 518 | . "\n(Automated notification from DriftSense.)\n"; | |
| 519 | ||
| 520 | my $from = $cfg->settings('alert_from_email') || 'drift_sense@localhost'; | |
| 521 | my $subj = "[DriftSense] " . substr($summary, 0, 120); | |
| 522 | ||
| 523 | my $sendmail = '/usr/sbin/sendmail'; | |
| 524 | $sendmail = '/usr/lib/sendmail' unless -x $sendmail; | |
| 525 | return ('failed', undef, undef, "no sendmail binary") unless -x $sendmail; | |
| 526 | ||
| 527 | require IPC::Open3; | |
| 528 | my ($wtr, $rdr, $err_fh); | |
| 529 | $err_fh = Symbol::gensym(); | |
| 530 | my $pid = eval { IPC::Open3::open3($wtr, $rdr, $err_fh, $sendmail, '-t', '-i') }; | |
| 531 | return ('failed', undef, undef, "spawn: $@") if $@ || !$pid; | |
| 532 | binmode $wtr; | |
| 533 | print {$wtr} "From: $from\r\n"; | |
| 534 | print {$wtr} "To: $to\r\n"; | |
| 535 | print {$wtr} "Subject: $subj\r\n"; | |
| 536 | print {$wtr} "Content-Type: text/plain; charset=utf-8\r\n"; | |
| 537 | print {$wtr} "\r\n"; | |
| 538 | print {$wtr} $mail_body; | |
| 539 | close $wtr; | |
| 540 | do { local $/; <$rdr> // ''; }; | |
| 541 | do { local $/; <$err_fh> // ''; }; | |
| 542 | close $rdr; close $err_fh; | |
| 543 | waitpid $pid, 0; | |
| 544 | my $rc = $? >> 8; | |
| 545 | return $rc == 0 | |
| 546 | ? ('sent', 0, "delivered to $to", undef) | |
| 547 | : ('failed', $rc, undef, "sendmail exit $rc"); | |
| 363 | return ('skipped', undef, undef, 'email delivery not yet implemented'); | |
| 548 | 364 | } |
| 549 | 365 | unless (length($url // '')) { |
| 550 | 366 | return ('failed', undef, undef, "no delivery URL configured"); |
| 551 | 367 | } |
| 552 | 368 | |
| 553 | 369 | my $body_ref = $kind eq 'slack' ? $payload->{slack} |
| 554 | 370 | : $kind eq 'discord' ? $payload->{discord} |
| 555 | 371 | : $payload->{generic_webhook}; |
| 556 | 372 | |
| 557 | 373 | # MODS::Webhook handles JSON encoding + curl transport; returns |
| 558 | 374 | # (http_code, response_body, error_message). |
| 559 | 375 | my ($code, $rbody, $err) = MODS::Webhook::post_json( |
| 560 | 376 | $url, $body_ref, |
| 561 | 377 | timeout => 8, |
| 562 | 378 | user_agent => 'DriftSense/1.0 (alert-dispatch)', |
| 563 | 379 | ); |
| 564 | 380 | $rbody //= ''; |
| 565 | 381 | $rbody = substr($rbody, 0, 500) if length($rbody) > 500; |
| 566 | 382 | |
| 567 | 383 | if (!$err && $code >= 200 && $code < 400) { |
| 568 | 384 | return ('sent', $code, $rbody, undef); |
| 569 | 385 | } |
| 570 | 386 | return ('failed', $code, $rbody, $err || "HTTP $code"); |
| 571 | 387 | } |
| 572 | 388 | |
| 573 | 389 | #--------------------------------------------------------------------- |
| 574 | 390 | sub _log_delivery { |
| 575 | 391 | my ($rule, $row, $kind, $status, $http_code, $body, $err) = @_; |
| 576 | 392 | my $summary = substr(($rule->{rule_name} // '') . ' :: ' . ($row->{file_name} |
| 577 | 393 | || ($row->{database_name} . '.' . $row->{table_name}) |
| 578 | 394 | || '?'), 0, 490); |
| 579 | 395 | my $q_summary = $dbh->quote($summary); |
| 580 | 396 | my $q_status = $dbh->quote($status); |
| 581 | 397 | my $q_body = $dbh->quote($body // ''); |
| 582 | 398 | my $q_err = $dbh->quote($err // ''); |
| 583 | 399 | my $q_code = defined($http_code) ? int($http_code) : 'NULL'; |
| 584 | 400 | |
| 585 | 401 | $db->db_readwrite($dbh, qq~ |
| 586 | 402 | INSERT INTO ALERT_DELIVERIES |
| 587 | 403 | (alert_rule_id, source_kind, source_id, match_summary, |
| 588 | 404 | delivery_status, http_status, http_response, error_message) |
| 589 | 405 | VALUES |
| 590 | 406 | ($rule->{alert_rule_id}, '$kind', $row->{id}, $q_summary, |
| 591 | 407 | $q_status, $q_code, $q_body, $q_err) |
| 592 | 408 | ~, __FILE__, __LINE__); |
| 593 | 409 | } |
Confirm restore
Backs current content to <target>.drift_restore_backup_<epoch>, writes the captured content, verifies SHA post-write.