evo-flow fails to boot on all current ClickHouse versions: TTL on toStartOfDay(DateTime64) is rejected (BAD_TTL_EXPRESSION), and 24.8 lacks a required setting

[mt-alarcon]

Summary

evo-flow-community fails to boot against every currently-available ClickHouse release we tested (24.8, 24.9, 24.12, 25.3) — two different, non-overlapping compatibility errors bracket the entire range, so no official ClickHouse tag lets the service start today.

Environment

• evo-flow-community image: evoapicloud/evo-flow-community:1.0.0 (sha256:fabbc4d04d066b7c9c5c350e5b86263b655ceeed9d75259b20bf00da846ab8a8)
• Deployed as part of the evo-crm-community v1.0.0 family (Docker Swarm), RUN_MODE=api, QUEUE_MODE=direct, STORAGE_MODE=clickhouse
• ClickHouse tested: 24.8, 24.9, 24.12, 25.3 (all pulled from clickhouse/clickhouse-server on Docker Hub)

What happens

On ClickHouse 24.8

Boot fails immediately with an unknown-setting error before any table is created:

Failed to connect to ClickHouse: Setting max_bytes_ratio_before_external_group_by is
neither a builtin setting nor started with the prefix 'SQL_' registered for
user-defined settings: Maybe you meant ['max_bytes_before_external_group_by',
'max_bytes_before_external_sort'].

This setting doesn't exist in 24.8 — it was added in a later ClickHouse release, so 24.8 is below the minimum version evo-flow-community needs.

On ClickHouse 24.9, 24.12, and 25.3

The unknown-setting error is gone, but boot now fails on table initialization:

Failed to initialize ClickHouse database: TTL expression result column should have
DateTime or Date type, but has DateTime64(3).
Error: TTL expression result column should have DateTime or Date type, but has
DateTime64(3).
  code: '450',
  type: 'BAD_TTL_EXPRESSION'

Root cause

In src/modules/processing/clickhouse/clickhouse.service.ts, at least two CREATE TABLE statements define a TTL expression as toStartOfDay(<DateTime64(3) column>) + INTERVAL 24 HOUR:

-- updated_computed_property_state
CREATE TABLE IF NOT EXISTS ${databaseName}.updated_computed_property_state (
  type Enum('contact_property' = 1, 'segment' = 2),
  computed_property_id LowCardinality(String),
  state_id LowCardinality(String),
  contact_id String,
  computed_at DateTime64(3)
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(computed_at)
ORDER BY computed_at
TTL toStartOfDay(computed_at) + INTERVAL 24 HOUR
SETTINGS index_granularity = 8192

-- updated_property_assignments_v2 has the same pattern on `assigned_at`

On current ClickHouse releases, toStartOfDay() applied to a DateTime64 argument preserves the sub-second type (returns DateTime64, not DateTime), and ClickHouse's TTL clause requires the expression to resolve to a plain DateTime/Date. So toStartOfDay(computed_at) + INTERVAL 24 HOUR now evaluates to DateTime64(3), which TTL rejects with BAD_TTL_EXPRESSION.

This appears to be a genuine ClickHouse behavioral change between older and current releases (older ClickHouse silently truncated toStartOfDay(DateTime64) to DateTime; current ClickHouse preserves the type). Because the max_bytes_ratio_before_external_group_by setting used elsewhere in the client requires a ClickHouse version newer than the one where the loose toStartOfDay behavior still held, there is no version where both requirements are satisfiable with the SQL as written.

Suggested fix

Cast the TTL expression to a plain DateTime explicitly, e.g.:

TTL toDateTime(toStartOfDay(computed_at)) + INTERVAL 24 HOUR
-- or
TTL toStartOfDay(toDateTime(computed_at)) + INTERVAL 24 HOUR

for both updated_computed_property_state.computed_at and updated_property_assignments_v2.assigned_at (and any other TTL expression in this file following the same pattern — I did not audit beyond the two I hit).

Reproduction

1. Deploy evo-crm-community v1.0.0 stack (Swarm) with evo-flow-community pointed at a fresh ClickHouse 24.9+ instance.
2. Watch evo-flow-community logs on boot.
3. Observe BAD_TTL_EXPRESSION on updated_computed_property_state initialization; service crash-loops indefinitely.

Impact

Without a working evo-flow-community, the CRM's Segments feature (which the CRM now hard-requires config for — EVO_FLOW_* env vars, else /api/v1/segments raises EvoFlow::ConfigurationError) cannot function end-to-end, even though the CRM itself boots fine and doesn't 500 on unauthenticated requests to that route.

---

Found while pinning the evo-crm-community v1.0.0 family by digest for a fresh-install deploy (MTA digital / EvoNexus). Happy to test a patched image against our stack if useful.