---
name: constraints-reviewer
description: Review data constraints and referential integrity in Rails models and migrations.
allowed-tools: Read Grep Glob Bash
---

# Constraints & Referential Integrity Review

Review database constraints, foreign keys, and data integrity rules.

## Database-Level Enforcement

```ruby
# PROBLEM: Model-only validation (race condition vulnerable)
validates :email, uniqueness: true

# SOLUTION: Database constraint + model validation
add_index :users, :email, unique: true
validates :email, uniqueness: true
```

## Race Condition Prevention

```ruby
# PROBLEM: Check-then-insert race condition
def claim_slot
  return false if Slot.where(user_id: user.id).exists?
  Slot.create!(user_id: user.id)
end

# SOLUTION: Database constraint with rescue
def claim_slot
  Slot.create!(user_id: user.id)
rescue ActiveRecord::RecordNotUnique
  false
end
```

## Missing Constraints Checklist

| Validation | Required DB Constraint |
|------------|----------------------|
| `presence: true` | `NOT NULL` |
| `uniqueness: true` | Unique index |
| `belongs_to` | Foreign key |
| `enum` | Check constraint |
| `numericality: { in: }` | Check constraint |

## Foreign Key Cascades

```ruby
# PROBLEM: Orphaned records on deletion
has_many :comments

# SOLUTION: Dependent handling
has_many :comments, dependent: :destroy  # For callbacks
# OR database-level:
add_foreign_key :comments, :posts, on_delete: :cascade
```

## Polymorphic Associations

```ruby
# PROBLEM: No referential integrity for polymorphics
belongs_to :commentable, polymorphic: true

# SOLUTION: Validate type + consider alternatives
ALLOWED_TYPES = %w[Post Article].freeze
validates :commentable_type, inclusion: { in: ALLOWED_TYPES }

# Better: separate tables instead of polymorphic
belongs_to :post, optional: true
belongs_to :article, optional: true
validates :post_id, presence: true, unless: :article_id?
```

## Orphan Detection Queries

```ruby
# Find orphaned comments (missing parent)
Comment.left_joins(:post).where(posts: { id: nil })

# Find missing foreign keys
ActiveRecord::Base.connection.tables.each do |table|
  columns = ActiveRecord::Base.connection.columns(table)
  columns.select { |c| c.name.end_with?('_id') }.each do |col|
    # Check if FK exists
  end
end
```

## Review Checklist

- [ ] All `uniqueness` validations have unique indexes?
- [ ] All `presence` validations have NOT NULL constraints?
- [ ] All `_id` columns have foreign keys?
- [ ] Cascade rules defined for deletions?
- [ ] Polymorphic types validated?
- [ ] No orphaned records possible?

## Output Format

```markdown
## Constraints Review: [PASS/WARN/FAIL]

### Missing Database Constraints
- [model]: [validation without DB enforcement]

### Foreign Key Issues
- [table]: [missing FK on _id column]

### Race Condition Risks
- [code location]: [check-then-insert pattern]

### Orphan Risks
- [association]: [could create orphans]

### Recommendations
1. [Prioritized fixes]
```