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

For working examples and a complete demonstration of PgSqlTriggers in action, check out the example repository.

Add this line to your application's Gemfile:

gem 'pg_sql_triggers'

And then execute:

$ bundle install

Run the installer:

$ rails generate pg_sql_triggers:install
$ rails db:migrate

This will:

1. Create an initializer at config/initializers/pg_sql_triggers.rb
2. Create migrations for registry table
3. Mount the engine at /pg_sql_triggers

Create trigger definitions using the Ruby DSL:

# 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

Generate and run trigger migrations similar to Rails schema migrations:

# Generate a new trigger migration
rails generate trigger:migration add_validation_trigger

# Run pending trigger migrations
rake trigger:migrate

# Rollback last trigger migration
rake trigger:rollback

# Rollback multiple steps
rake trigger:rollback STEP=3

# Check migration status
rake trigger:migrate:status

# Run a specific migration up
rake trigger:migrate:up VERSION=20231215120000

# Run a specific migration down
rake trigger:migrate:down VERSION=20231215120000

# Redo last migration
rake trigger:migrate:redo

Web UI Migration Management:

You can also manage migrations directly from the web dashboard:

• Apply All Pending Migrations: Click the "Apply All Pending Migrations" button to run all pending migrations at once
• Rollback Last Migration: Use the "Rollback Last Migration" button to undo the most recent migration
• Redo Last Migration: Click "Redo Last Migration" to rollback and re-apply the last migration
• Individual Migration Actions: Each migration in the status table has individual "Up", "Down", or "Redo" buttons for granular control

All migration actions include confirmation dialogs and provide feedback via flash messages.

Trigger migrations are stored in db/triggers/ and follow the same naming convention as Rails migrations (YYYYMMDDHHMMSS_name.rb).

Example trigger migration:

# db/triggers/20231215120000_add_validation_trigger.rb
class AddValidationTrigger < PgSqlTriggers::Migration
  def up
    execute <<-SQL
      CREATE OR REPLACE FUNCTION validate_user_email()
      RETURNS TRIGGER AS $$
      BEGIN
        IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
          RAISE EXCEPTION 'Invalid email format';
        END IF;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER user_email_validation
      BEFORE INSERT OR UPDATE ON users
      FOR EACH ROW
      EXECUTE FUNCTION validate_user_email();
    SQL
  end

  def down
    execute <<-SQL
      DROP TRIGGER IF EXISTS user_email_validation ON users;
      DROP FUNCTION IF EXISTS validate_user_email();
    SQL
  end
end

Run both schema and trigger migrations together:

# Run both schema and trigger migrations
rake db:migrate:with_triggers

# Rollback both (rolls back the most recent migration)
rake db:rollback:with_triggers

# Check status of both
rake db:migrate:status:with_triggers

# Get versions of both
rake db:version:with_triggers

Access trigger information from the Rails console:

# List all triggers
PgSqlTriggers::Registry.list

# List enabled triggers
PgSqlTriggers::Registry.enabled

# List disabled triggers
PgSqlTriggers::Registry.disabled

# Get triggers for a specific table
PgSqlTriggers::Registry.for_table(:users)

# Check for drift
PgSqlTriggers::Registry.diff

# Validate all triggers
PgSqlTriggers::Registry.validate!

Access the web UI at http://localhost:3000/pg_sql_triggers to:

• View all triggers and their status
• Enable/disable triggers
• View drift states
• Execute SQL capsules
• Manage trigger lifecycle
• Run trigger migrations (up/down/redo) directly from the dashboard
  • Apply all pending migrations with a single click
  • Rollback the last migration
  • Redo the last migration
  • Individual migration controls for each migration in the status table

PgSqlTriggers supports three permission levels:

• Viewer: Read-only access (view triggers, diffs)
• Operator: Can enable/disable triggers, apply generated triggers
• Admin: Full access including dropping triggers and executing SQL

Configure custom permission checking:

# config/initializers/pg_sql_triggers.rb
PgSqlTriggers.configure do |config|
  config.permission_checker = ->(actor, action, environment) {
    # Your custom permission logic
    user = User.find(actor[:id])
    user.has_permission?(action)
  }
end

PgSqlTriggers automatically detects drift between your DSL definitions and the actual database state:

• Managed & In Sync: Trigger matches DSL definition
• Managed & Drifted: Trigger exists but doesn't match DSL
• Manual Override: Trigger was modified outside of PgSqlTriggers
• Disabled: Trigger is disabled
• Dropped: Trigger was dropped but still in registry
• Unknown: Trigger exists in DB but not in registry

By default, PgSqlTriggers blocks destructive operations in production:

# config/initializers/pg_sql_triggers.rb
PgSqlTriggers.configure do |config|
  # Enable production kill switch (default: true)
  config.kill_switch_enabled = true
end

Override for specific operations:

PgSqlTriggers::SQL.override_kill_switch do
  # Dangerous operation here
end

# config/initializers/pg_sql_triggers.rb
PgSqlTriggers.configure do |config|
  # Kill switch for production (default: true)
  config.kill_switch_enabled = true

  # Environment detection (default: -> { Rails.env })
  config.default_environment = -> { Rails.env }

  # Custom permission checker
  config.permission_checker = ->(actor, action, environment) {
    # Return true/false based on your authorization logic
    true
  }
end

• 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. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.

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