PgSqlTriggers

A PostgreSQL Trigger Control Plane for Rails

Production-grade PostgreSQL trigger management for Rails with lifecycle management, safe deploys, versioning, drift detection, and a mountable UI.

Rails teams use PostgreSQL triggers for data integrity, performance, and billing logic. But triggers today are:

• Managed manually
• Invisible to Rails
• Unsafe to deploy
• Easy to drift

PgSqlTriggers brings triggers into the Rails ecosystem with:

• Lifecycle management
• Safe deploys
• Versioning
• UI control
• Emergency SQL escape hatches

• Ruby 3.0+
• Rails 6.1+
• PostgreSQL (any supported version)

# Gemfile
gem 'pg_sql_triggers'

bundle install
rails generate pg_sql_triggers:install
rails db:migrate

# app/triggers/users_email_validation.rb
PgSqlTriggers::DSL.pg_sql_trigger "users_email_validation" do
  table :users
  on :insert, :update
  function :validate_user_email
  version 1
  enabled false
  when_env :production
end

rails generate trigger:migration add_email_validation
rake trigger:migrate

Navigate to http://localhost:3000/pg_sql_triggers to manage triggers visually.

Screenshots are available in the docs/screenshots directory.

Comprehensive documentation is available in the docs directory:

• Getting Started - Installation and basic setup
• Usage Guide - DSL syntax, migrations, and drift detection
• Web UI - Using the web dashboard
• Kill Switch - Production safety features
• Configuration - Complete configuration reference
• API Reference - Console API and programmatic access

Define triggers using a Rails-native Ruby DSL with versioning and environment control.

Manage trigger functions and definitions with a migration system similar to Rails schema migrations.

Automatically detect when database triggers drift from your DSL definitions.

Multi-layered safety mechanism preventing accidental destructive operations in production environments.

Visual interface for managing triggers, running migrations, and executing SQL capsules. Includes:

• Quick Actions: Enable/disable, drop, and re-execute triggers from dashboard
• Last Applied Tracking: See when triggers were last applied with human-readable timestamps
• Breadcrumb Navigation: Easy navigation between dashboard, tables, and triggers
• Permission-Aware UI: Buttons show/hide based on user role

Comprehensive audit trail for all trigger operations:

• Track who performed each operation (actor tracking)
• Before and after state capture
• Success/failure logging with error messages
• Reason tracking for drop and re-execute operations

Emergency SQL execution feature for critical operations with Admin permission requirements, kill switch protection, and comprehensive logging.

Operational controls for trigger lifecycle management with drop and re-execute capabilities, drift comparison, and required reason logging.

Three-tier permission system (Viewer, Operator, Admin) with customizable authorization.

PgSqlTriggers provides a comprehensive console API for managing triggers programmatically:

# Query triggers
triggers = PgSqlTriggers::Registry.list
enabled = PgSqlTriggers::Registry.enabled
disabled = PgSqlTriggers::Registry.disabled
user_triggers = PgSqlTriggers::Registry.for_table(:users)

# Check drift status
drift_info = PgSqlTriggers::Registry.diff
drifted = PgSqlTriggers::Registry.drifted
in_sync = PgSqlTriggers::Registry.in_sync
unknown = PgSqlTriggers::Registry.unknown_triggers
dropped = PgSqlTriggers::Registry.dropped

# Enable/disable triggers
PgSqlTriggers::Registry.enable("users_email_validation", actor: current_user, confirmation: "EXECUTE TRIGGER_ENABLE")
PgSqlTriggers::Registry.disable("users_email_validation", actor: current_user, confirmation: "EXECUTE TRIGGER_DISABLE")

# Drop and re-execute triggers
PgSqlTriggers::Registry.drop("old_trigger", actor: current_user, reason: "No longer needed", confirmation: "EXECUTE TRIGGER_DROP")
PgSqlTriggers::Registry.re_execute("drifted_trigger", actor: current_user, reason: "Fix drift", confirmation: "EXECUTE TRIGGER_RE_EXECUTE")

# Execute SQL capsules
capsule = PgSqlTriggers::SQL::Capsule.new(
  name: "emergency_fix",
  environment: "production",
  purpose: "Fix critical data issue",
  sql: "UPDATE users SET status = 'active' WHERE id = 123"
)
PgSqlTriggers::SQL::Executor.execute(capsule, actor: current_user, confirmation: "EXECUTE SQL")

See the API Reference for complete documentation of all console APIs.

For working examples and complete demonstrations, check out the example repository.

• Rails-native: Works seamlessly with Rails conventions
• Explicit over magic: No automatic execution
• Safe by default: Requires explicit confirmation for destructive actions
• Power with guardrails: Emergency SQL escape hatches with safety checks

After checking out the repo, run bin/setup to install dependencies. Run rake spec to run tests. Run bin/console for an interactive prompt.

To install this gem locally, run bundle exec rake install. To release a new version, update the version number in version.rb, and run bundle exec rake release.

See COVERAGE.md for detailed coverage information.

Total Coverage: 84.97%

Bug reports and pull requests are welcome on GitHub at https://github.com/samaswin/pg_sql_triggers.

See LICENSE file for details.