Segment Rule Operators

Operator Reference

Comparison Operators

eq (equals)

Exact match.

{"source": "profile", "custom_field_id": 10, "op": "eq", "value": "Bucharest"}
{"source": "form", "template_id": 5, "custom_field_id": 11, "op": "eq", "value": "Big pain"}

SQL:

-- Profile
cfv.custom_field_id = 10 AND cfv.value = 'Bucharest'

-- Form
f.values->>'field_11' = 'Big pain'

Type support: Strings, numbers, dates (as ISO strings)

---

neq (not equals)

Inverse of eq.

{"source": "profile", "custom_field_id": 20, "op": "neq", "value": "inactive"}

SQL:

cfv.custom_field_id = 20 AND cfv.value != 'inactive'

---

gt (greater than)

Numeric or date comparison.

{"source": "profile", "custom_field_id": 13, "op": "gt", "value": 50}
{"source": "appointments", "metric": "last_date", "op": "gt", "value": "2025-01-01"}

SQL:

-- Numeric
cfv.custom_field_id = 13 AND cfv.value::numeric > 50

-- Date
a.started_at > '2025-01-01'

Type coercion: String values are cast to NUMERIC or TIMESTAMPTZ as needed.

---

gte (greater than or equal)

{"source": "profile", "custom_field_id": 13, "op": "gte", "value": 65}
{"source": "appointments", "metric": "count", "op": "gte", "value": 2}

SQL:

cfv.custom_field_id = 13 AND cfv.value::numeric >= 65
COUNT(*) >= 2

---

lt (less than)

{"source": "profile", "custom_field_id": 13, "op": "lt", "value": 18}

SQL:

cfv.custom_field_id = 13 AND cfv.value::numeric < 18

---

lte (less than or equal)

{"source": "appointments", "metric": "count", "op": "lte", "value": 1}

SQL:

COUNT(*) <= 1

---

String Operators

contains

Case-insensitive substring match.

{"source": "profile", "custom_field_id": 16, "op": "contains", "value": "diabetes"}
{"source": "form", "template_id": 5, "custom_field_id": 17, "op": "contains", "value": "headache"}

SQL:

-- Profile
cfv.custom_field_id = 16 AND LOWER(cfv.value) LIKE LOWER('%diabetes%')

-- Form
LOWER(f.values->>'field_17') LIKE LOWER('%headache%')

Case sensitivity: Always case-insensitive for consistency.

---

Set Operators

in

Value is in a list.

{"source": "profile", "custom_field_id": 12, "op": "in", "value": ["A+", "A-", "AB+"]}
{"source": "form", "template_id": 5, "custom_field_id": 11, "op": "in", "value": ["Big pain", "Extreme pain"]}

SQL:

-- Profile
cfv.custom_field_id = 12 AND cfv.value IN ('A+', 'A-', 'AB+')

-- Form
f.values->>'field_11' IN ('Big pain', 'Extreme pain')

Value type: Must be an array. Single values should use eq instead.

---

Existence Operators

exists

Field has any non-null value.

{"source": "profile", "custom_field_id": 15, "op": "exists"}
{"source": "form", "template_id": 5, "custom_field_id": 21, "op": "exists"}

SQL:

-- Profile
cfv.custom_field_id = 15 AND cfv.value IS NOT NULL AND cfv.value != ''

-- Form
f.values ? 'field_21' AND f.values->>'field_21' IS NOT NULL AND f.values->>'field_21' != ''

Value: Ignored (can be null or omitted).

---

empty

Field is null or empty string.

{"source": "profile", "custom_field_id": 16, "op": "empty"}

SQL:

cfv.custom_field_id = 16 AND (cfv.value IS NULL OR cfv.value = '')

Inverse of exists.

---

Rule Sources

source: "form"

Query form response values.

Required fields:

• template_id — which form template to query
• custom_field_id — which custom field to query (maps to field_{id} key in forms.values JSONB)

Example:

{
  "source": "form",
  "template_id": 5,
  "custom_field_id": 11,
  "op": "eq",
  "value": "Big pain"
}

Multi-instance resolution: Latest completed/signed form wins (see rule-engine.md).

Supported operators: All operators except appointment-specific ones.

---

source: "profile"

Query custom field values (patient profile data).

Required fields:

• custom_field_id — which custom field to query (must exist with entity_type='patient')

Example:

{
  "source": "profile",
  "custom_field_id": 10,
  "op": "eq",
  "value": "Bucharest"
}

Validation: custom_field_id must exist in custom_fields for this organization with entity_type='patient'.

Supported operators: All operators except appointment-specific ones.

---

source: "appointments"

Query appointment aggregates.

Required fields:

• metric — what to measure ("count" or "last_date")

Optional fields:

• filters — additional constraints (status, template_id, date range)

Metrics:

metric: "count"

Number of appointments matching filters.

{
  "source": "appointments",
  "metric": "count",
  "op": "gte",
  "value": 2,
  "filters": {
    "status": "done"
  }
}

SQL:

SELECT COUNT(*) FROM appointments a
WHERE a.patient_person_id = p.patient_person_id
  AND a.organization_id = :org_id
  AND a.status = 'done'
HAVING COUNT(*) >= 2

Supported operators: eq, neq, gt, gte, lt, lte

metric: "last_date"

Date of most recent appointment matching filters.

{
  "source": "appointments",
  "metric": "last_date",
  "op": "gte",
  "value": "now-30d"
}

SQL:

SELECT MAX(a.started_at) FROM appointments a
WHERE a.patient_person_id = p.patient_person_id
  AND a.organization_id = :org_id
HAVING MAX(a.started_at) >= (NOW() - INTERVAL '30 days')

Supported operators: eq, neq, gt, gte, lt, lte

Relative dates supported: now-30d, now-6M, now-1y (see Relative Dates section).

---

Appointment Filters

When using source: "appointments", add optional filters to narrow results:

status

Filter by appointment status.

{
  "source": "appointments",
  "metric": "count",
  "op": "gte",
  "value": 2,
  "filters": {
    "status": "done"
  }
}

Possible values: done, upcoming, cancelled, rescheduled, etc. (depends on appointment status enum)

template_id

Filter by appointment template.

{
  "source": "appointments",
  "metric": "count",
  "op": "gte",
  "value": 1,
  "filters": {
    "template_id": 10
  }
}

Use case: "Patients with at least 1 follow-up appointment" (where follow-ups use template 10).

after / before

Filter by appointment date range.

{
  "source": "appointments",
  "metric": "count",
  "op": "gte",
  "value": 1,
  "filters": {
    "after": "2025-01-01",
    "before": "2025-12-31"
  }
}

Relative dates supported:

{
  "source": "appointments",
  "metric": "count",
  "op": "gte",
  "value": 2,
  "filters": {
    "after": "now-6M"
  }
}

Means: "At least 2 appointments in the last 6 months"

---

Relative Dates

Instead of hardcoded dates, use relative date syntax that resolves at evaluation time.

Syntax

Token	Meaning
now	Current timestamp
now-Nd	N days ago
now-NM	N months ago
now-Ny	N years ago
now+Nd	N days from now

Examples

// Appointments in last 30 days
{"source": "appointments", "metric": "last_date", "op": "gte", "value": "now-30d"}

// Appointments in last 6 months
{"source": "appointments", "metric": "count", "op": "gte", "value": 1, "filters": {"after": "now-6M"}}

// No appointments in last year
{"source": "appointments", "metric": "last_date", "op": "lt", "value": "now-1y"}

// Profile field: registered in last 90 days
{"source": "profile", "custom_field_id": 14, "op": "gte", "value": "now-90d"}

Resolution Timing

Relative dates are resolved at evaluation time, not at rule creation time.

Implication: A segment with "value": "now-30d" always means "30 days ago from now", not "30 days ago from when the segment was created".

Benefit: Segments automatically stay current. No need to update rules daily.

Caveat: matched_at timestamp shows when the patient was last confirmed to match, not when they first matched.

---

Operator Compatibility Matrix

Operator	Form	Profile	Appointments (count)	Appointments (last_date)
eq	✅	✅	✅	✅
neq	✅	✅	✅	✅
gt	✅	✅	✅	✅
gte	✅	✅	✅	✅
lt	✅	✅	✅	✅
lte	✅	✅	✅	✅
contains	✅	✅	❌	❌
in	✅	✅	❌	❌
exists	✅	✅	❌	❌
empty	✅	✅	❌	❌

Note: Appointment rules only support numeric/date comparisons (eq, neq, gt, gte, lt, lte).

---

Type Coercion

PostgreSQL performs automatic type coercion for operators:

String → Number

-- Profile value stored as TEXT '50'
cfv.value::numeric > 40  -- Coerced to 50, comparison succeeds

Validation: If coercion fails (e.g., 'abc'::numeric), the rule doesn't match (treated as NULL).

String → Date

-- Profile value stored as TEXT '2025-01-15'
cfv.value::timestamptz > '2025-01-01'::timestamptz  -- Coerced to timestamp, comparison succeeds

Supported formats: ISO 8601 (YYYY-MM-DD, YYYY-MM-DDTHH:MM:SSZ)

JSONB → Text

-- Form value stored as JSONB string
f.values->>'age' = '50'  -- JSONB text extraction, string comparison

Implication: Form fields storing numbers as strings ({"age": "50"}) work with eq but need explicit casting for gt/lt.

Recommendation: Store numbers as JSON numbers ({"age": 50}) for proper type support.

---

Validation Rules

When creating or updating a segment, rules are validated:

Required Fields

Source	Required	Optional
form	template_id, custom_field_id, op	value (omitted for exists/empty)
profile	custom_field_id, op	value (omitted for exists/empty)
appointments	metric, op, value	filters

Value Type Validation

Operator	Value Type	Example
eq, neq	Any	"Bucharest", 50, true
gt, gte, lt, lte	Number or Date	50, "2025-01-01", "now-30d"
contains	String	"diabetes"
in	Array	["A+", "A-", "AB+"]
exists, empty	Ignored	null or omitted

Reference Validation

• Form rules: template_id must exist, custom_field_id must reference a valid custom field
• Profile rules: custom_field_id must exist in custom_fields with entity_type='patient'
• Appointment rules: metric must be "count" or "last_date"

Validation errors return 400 with details:

{
  "status": 400,
  "name": "ValidationError",
  "message": "Segment validation failed",
  "details": {
    "errors": [
      {
        "field": "rules[0].custom_field_id",
        "message": "custom field with ID 999 not found"
      }
    ]
  }
}

---

Edge Cases

Empty String vs NULL

Question: How does exists handle empty strings?

Answer: Both NULL and empty string are treated as "doesn't exist".

-- exists operator
cfv.value IS NOT NULL AND cfv.value != ''

-- empty operator
cfv.value IS NULL OR cfv.value = ''

Implication: A patient with city = '' does NOT match {"custom_field_id": 10, "op": "exists"}.

---

Case Sensitivity

Question: Is eq case-sensitive?

Answer: Yes for exact match, no for contains.

// Case-sensitive
{"custom_field_id": 10, "op": "eq", "value": "Bucharest"}  // Matches "Bucharest", not "bucharest"

// Case-insensitive
{"custom_field_id": 16, "op": "contains", "value": "diabetes"}  // Matches "Diabetes", "DIABETES", "diabetes"

Workaround for case-insensitive eq: Use contains without wildcards (future enhancement: add ieq operator).

---

Multi-Value Fields (Checkboxes)

Question: How do checkbox fields (stored as arrays in JSONB) work with operators?

Answer:

Form field:

{"allergies": ["peanuts", "shellfish"]}

Rule to match patients with peanut allergy:

{"source": "form", "template_id": 5, "custom_field_id": 22, "op": "contains", "value": "peanuts"}

SQL:

-- JSONB array containment
f.values->'allergies' @> '"peanuts"'::jsonb

Alternative (if stored as comma-separated TEXT in profile):

cfv.value LIKE '%peanuts%'

Recommendation: Use JSONB arrays for multi-value fields in forms, not comma-separated strings.

---

Relative Dates with Timezones

Question: How are relative dates handled across timezones?

Answer: Always resolved in UTC.

now := time.Now().UTC()
result := now.AddDate(0, 0, -30) // 30 days ago in UTC

Implication: "Last 30 days" means "last 30 days in UTC", not the organization's local timezone.

Future enhancement: Add timezone-aware relative dates (now-30d@America/New_York).

---

Performance Considerations

Operator Cost Ranking (fastest to slowest)

1. eq, neq — Direct index lookup
2. gt, gte, lt, lte — Range scan with index
3. in — Multiple index lookups (efficient for small lists)
4. exists, empty — NULL check (very fast)
5. contains — Full-text scan (slowest, can't use simple index)

Optimization Tips

Use in for small lists:

// Good (2 values)
{"op": "in", "value": ["A+", "A-"]}

// Bad (100 values)
{"op": "in", "value": ["val1", "val2", ..., "val100"]}
// Consider splitting into multiple segments or using a different approach

Use indexed fields when possible:

• Form fields with GIN index on values JSONB
• Custom fields with B-tree index on value
• Appointment status and started_at with composite index

Avoid contains on large text fields:

• Can't leverage indexes effectively
• Falls back to sequential scan
• Consider full-text search (tsvector) for large text fields (future enhancement)

---

Future Operators

regex

Pattern matching with regular expressions.

{"custom_field_id": 23, "op": "regex", "value": "^\\+40[0-9]{9}$"}

Use case: Validate format, extract patterns.

between

Shorthand for >= X AND <= Y.

{"custom_field_id": 13, "op": "between", "value": [18, 65]}

starts_with, ends_with

Prefix/suffix matching.

{"custom_field_id": 15, "op": "ends_with", "value": "@example.com"}

haversine (Geo)

Distance-based filtering for lat/lon coordinates.

{
  "custom_field_id": 24,
  "op": "haversine",
  "value": {
    "lat": 44.4268,
    "lon": 26.1025,
    "radius_km": 50
  }
}

Use case: "Patients within 50km of Bucharest city center"