Optimize rules engine JSONB queries to utilize GIN indexes with `@>` operator

[jeroenrinzema]

Summary

The rules engine currently generates JSONB queries using -> and ->> operators for path navigation, which cannot leverage existing GIN indexes on JSONB columns. Refactoring equality-based comparisons to use the @> (contains) operator would significantly improve query performance.

Current Behavior

The rules engine generates queries like:

-- User data queries
(u.data->>'tier')::text = 'gold'
(u.data->'subscription'->>'plan')::text = 'enterprise'

-- Organization data queries
(o.data->>'tier')::text = 'premium'
(ou.data->>'admin')::boolean = true

These queries perform sequential scans on the JSONB columns despite GIN indexes existing on users.data, user_events.data, organizations.data, organization_users.data, and organization_events.data.

Proposed Optimization

Refactor equality-based comparisons to use the @> operator:

-- Optimized (uses GIN index)
u.data @> '{"tier": "gold"}'::jsonb
u.data @> '{"subscription": {"plan": "enterprise"}}'::jsonb
o.data @> '{"tier": "premium"}'::jsonb
ou.data @> '{"admin": true}'::jsonb

Scope

Operators that can be optimized with @>:

• EQUALS / NOT_EQUALS (equality checks)
• IS_SET / IS_NOT_SET (can use ? operator)
• Boolean comparisons

Operators that cannot use @> (keep current approach):

• Range comparisons (GREATER_THAN, LESS_THAN, etc.)
• String pattern matching (CONTAINS, STARTS_WITH, ENDS_WITH, MATCHES)

Files to Modify

• internal/rules/query/rule.go - buildColumnPath() and buildComparison() functions
• internal/rules/query/comparison.go - Add @> operator support for equality checks

Expected Impact

• Significant query performance improvement for rule evaluations on large datasets
• Better utilization of existing GIN indexes
• Reduced database load during segment calculations and rule matching