Native JSON events: cutover readiness tracker

[aspicer]

Track the work left before enabling native JSON event reads and, later, retiring the legacy events table. Every item names work someone has to do and what done looks like. Do not put customer queries or private operational data here.

Where things stand (Sep 29)

Merged: the native schema and base property reads (#91515), correctness fixes (#104783), the retained mutation log (#97215), flag compatibility with the $false sentinel (#91506), the native CI lane (#106280), the HogQL port of point-in-time person properties (#91452), and the UDF wrapper fix (#105651).

In the merge queue: #107043 (moved event properties read from temporary_properties). #106380 (native-only regression tests) is approved and joins the queue when its checks pass. #106274 (the useNewEventsSchema modifier, the rollout switch) left the queue on Sep 29 when a cancelled MCP run failed its batch, and needs to be queued again.

Open: #107546 moves the table to dynamic JSON paths, adds the null-key columns, turns date inference off, and stores dotted keys flat. The production table is re-backfilled after it lands. It conflicts with master, so its CI does not run until master is merged in. #106152 is open and listed below.

Rollout mechanism: staff set useNewEventsSchema in a project's team modifiers in Django admin. An explicit modifier is part of the query cache key, so the switch recomputes on native and clearing it restores the legacy cache entries. The final global flip uses the instance setting. Customers cannot set the key on their project (#106274); the per-query override stays available to API callers during the trial.

Before the backfill starts

• Cleaner UDF and pin. The event cleaner stops expanding dotted keys into nested objects and encodes a dot inside a key segment as %2E in the null-key paths, so nested.deep and a flat key named a.b stay distinct. Rebuild the binaries and move the production UDF pin to a build with this, fix(clickhouse): close inherited descriptors in udf wrapper scripts #105651 and the $false fix from feat(hogql): flag compatibility for native events and a $false sentinel #91506. Done when the pinned build is the one the backfill runs.
• Backfill query. Recreate the table with the feat(clickhouse): use dynamic JSON paths in the native events schema #107546 DDL, then copy every event with no retention cutoff, with the insert setting json_type_escape_dots_in_keys = 1, type_json_skip_duplicated_paths = 1, input_format_try_infer_dates = 0 and input_format_try_infer_datetimes = 0, the same four the ingestion view sets. Done when the copy runs with them.
• $feature_flag_called in both paths. The live ingestion view keeps these events; the copy currently drops them. Pick one and make both paths match, coordinated with Stop advertising $feature_flag_called in taxonomy, autocomplete, and product surfaces #88126.
• AI prompt and response text. The native cleaner omits $ai_input and $ai_output_choices; the AI events table keeps that text only for its retention window, and older traces read it from the legacy table today. Get the LLM analytics owner's written agreement that older traces lose it, or a replacement source. Done when the answer is recorded here.

Before enabling native reads for any project

• Read-side setting as a profile default on the events cluster. ClickHouse restores %2E to a dot only when the reading query sets json_type_escape_dots_in_keys. HogQL and the export templates set it per query (feat(clickhouse): use dynamic JSON paths in the native events schema #107546, 0a9745a), but mutations run with server settings, and ad-hoc readers set nothing. Add it to the default profile on the events cluster. Done when a clickhouse-client session on that cluster prints a dotted key with a dot.
• Rebuild nulls in whole-document reads. The JSON column cannot store null, so the cleaner drops those fields and feat(clickhouse): use dynamic JSON paths in the native events schema #107546 records their paths in properties_null_keys, temporary_properties_null_keys and person_properties_null_keys. Nothing reads those arrays yet. Batch exports and the events API put the nulls back. Done when a native export and the events API return null for a fixture that sent one.
• Land fix(hogql): read rebuilt flags through every native JSON function #106152, flag reads through JSON functions. JSONExtractRaw(properties, '$feature/x'), arrayJoin(JSONExtractArrayRaw(properties, '$active_feature_flags')), JSONLength, JSONType and toString(properties) do not see the rebuilt flags on native. It also has to decide how these functions tell a string variant named "true" from boolean true. Rebase onto master, drop the inherited legacy-table rewrites, land.
• Decide the whole-document flag shape. On native, SELECT properties, the event properties panel and exports show flags as one $feature_flags object; the legacy table shows many $feature/<key> properties. Decide whether whole-document reads rebuild the legacy shape or expose the map, then implement it, including property restrictions and generated column aliases.
• Batch exports. Pin exports to the legacy table now: the export source builds its query with the project's default modifiers, so switching a project would move its exports too. Before exports move: custom export columns are persisted as compiled ClickHouse expressions and schemas saved with property restrictions omit their original HogQL, so a saved export must recompile against the table it reads at run time; native exports serialize declared array paths as [] on every event; the native field rewrite relocates $feature/<key> paths but not JSON-function calls on them. Done when an export created before enablement, one created after, a restricted schema and a historical backfill all produce the same rows from both tables apart from the recorded expected differences.
• Land chore(tests): prove event queries read native-only fixtures #106380, native-only regression tests. Fixtures that exist only in the native table, with the legacy table asserted empty, so a query that fell back to legacy fails. Approved and submitted to the merge queue on Sep 29, covering the events-list boolean filters and daily trends. Done when it merges, before the first project switches.
• Deletions cover the native table. feat(deletes): skip sharded_events_json, configurable per run #102195 skips sharded_events_json in deletes_job on purpose. Re-enable it, then run a catch-up over deletion requests already marked verified, because re-enabling does not revisit them. chore(clickhouse): test adhoc deletes across events clusters #99416 (draft) adds a two-cluster test. See deletion coverage.
• person_id squash covers the native table. chore(clickhouse): drop events_json from the person_id squash targets #105148 removed sharded_events_json from the weekly squash, so native rows keep the absorbed person_id and merged users count twice. Add the table back once its cluster resolves reliably, and repair rows stranded in the meantime, for example by copying person_id from the squashed legacy rows by uuid; the repair needs the legacy table. chore(clickhouse): make squash coverage a checked invariant #98865 records the exclusion.
• Property removal on the native table. Removing one property from all of a project's events is not implemented for the JSON columns. Implement it for properties, temporary_properties and person_properties, including $unparseable_properties. The deletion job reads a dot in a requested name as nesting on both tables; with dotted keys stored flat, the predicate, the verify step and the JSONDropKeys key list need the same %2E encoding to reach a flat dotted key (Sep 29 comment).
• Compare and measure. Run the warm insights of the first trial project against both tables with the feat(hogql): add a useNewEventsSchema query modifier #106274 modifier and record the drift here, classified against the expected differences: empty and null values, JSON scalar types, number formatting (1.50 is stored as 1.5), flag arrays and order, omitted properties, deduplication; nulls and empty strings inside $set are dropped; object keys come back sorted; numbers above the signed 64-bit range inside an object read come back as JSON strings; an empty $set or $unset reads as missing; a key sent with a literal %2E reads back with a dot; an event carrying both a.b and a%2Eb keeps the first value; posthog-go before 1.13.2 listed off flags as active. Measure insert and merge cost with up to 1024 dynamic paths per part, and reads of wide objects past a column's dynamic-path budget (200k local synthetic events: a whole-object read of a wide $set took 20 s native against 0.8 s legacy, a $set.email filter read 9 MB against 177 MB). HogQL reads every native path through the generic Dynamic expression, even declared typed paths such as $browser; measure that too. Done when the numbers and the rollback criteria are written here.

Before retiring the legacy events table

Legacy writes stop and storage is dropped only after the items above and these are done.

• Durable source for $set history. Point-in-time person properties (fix(persons): build properties_at_time via HogQL instead of raw SQL #91452) and the event details panel read $set and $set_once from old events. On native those live in temporary_properties for 60 days and in the mutation log (feat(clickhouse): retain event person mutation payloads on aux #97215) for 30, so anything older disappears with the legacy table. Choose a durable source or change the product contract, and finish feat(events): show retained person property mutations #97217 against whichever it is.
• Move the direct readers. These query the legacy table by name and bypass the schema switch: products/feature_flags/backend/flag_analytics.py, products/growth/backend/temporal/health_checks/sdk_outdated.py, and the MCP analytics metrics in posthog/tasks/usage_report.py (their dedup expression matches the legacy sort key). Move or retire each, then add the semgrep rule against new raw events SQL outside the switch.

Done

• feat(clickhouse): update native JSON events schema #91515: native schema and base property reads.
• fix(hogql): run CI on the native-JSON events table and fix what broke #104783: native-mode correctness fixes and first coverage.
• feat(clickhouse): retain event person mutation payloads on aux #97215: retained mutation-log storage.
• feat(hogql): flag compatibility for native events and a $false sentinel #91506: flag compatibility layer and the $false sentinel. Historical rows whose variant was literally "false" are fixed by the re-backfill, which runs every row through the new cleaner.
• chore(ci): test native JSON events without doubling the suite #106280: continuous native-schema CI. Backend CI reruns the directories in .github/new-events-schema-targets.txt against the native table on every PR and in the merge queue.
• fix(persons): build properties_at_time via HogQL instead of raw SQL #91452: point-in-time person properties through HogQL.
• fix(clickhouse): close inherited descriptors in udf wrapper scripts #105651: UDF wrapper closes inherited descriptors.
• Sent types on declared paths: feat(clickhouse): use dynamic JSON paths in the native events schema #107546 declares only low-cardinality strings, the flags map, the join keys and the cleaner-coerced arrays, so every other path keeps the type it was sent with. Effective with the re-backfill.
• Date inference: feat(clickhouse): use dynamic JSON paths in the native events schema #107546 turns it off and casts all three JSON columns explicitly inside the ingestion view, so a date string is stored and returned as the string it was. Effective with the re-backfill.
• Dotted keys flat, application side: feat(clickhouse): use dynamic JSON paths in the native events schema #107546 (0a9745a) sets json_type_escape_dots_in_keys and type_json_skip_duplicated_paths on the ingestion view and fixture inserts, HogQL encodes a dot inside a key segment as %2E on native reads, and native queries and export templates set the read-side setting. The cleaner and cluster parts are above.
• Null keys recorded: feat(clickhouse): use dynamic JSON paths in the native events schema #107546 writes dropped null paths to the three *_null_keys columns. Reading them back is above.

Keeping this tracker current

Consult this issue first when working on native JSON events. Add an item only when it names work someone has to do, with a code or PR reference and what done looks like. Check an item off when the work is in production, not when a PR merges. Keep merge decisions distinct from permission to enable a project or retire storage.

[aspicer]

CI coverage for native reads: #106280 (draft) runs the test paths in .github/new-events-schema-targets.txt (12 directories plus 28 files, about 21% of recorded test time) on the JSON table on every backend run, as ~7 extra jobs instead of the 51-job doubling. The full doubling stays available through the test-new-events-schema label. The list is maintained by hand; a new directory of tests that read event properties needs adding.

Confirmed gaps the test lanes cannot see, because test fixtures always write the legacy table: hand-written SQL that reads events without going through the schema switch passes in both modes and breaks only when legacy storage is retired. Known sites: products/feature_flags/backend/flag_analytics.py (JSONExtract on properties), products/growth/backend/temporal/health_checks/sdk_outdated.py (mat_$lib), posthog/models/person/point_in_time_properties.py (reads $set, which the ingest cleaner moves to temporary_properties). A lint over raw events SQL would catch new ones.

Also: posthog/dags/tests/test_data_deletion_requests.py cannot run in the Django CI job in either mode because that job loads only the versioned JSONDropKeys_v12 UDF, so the JSON leg covers deletes through test_deletes.py and test_deletion_targets.py only.

[aspicer]

The scalar-type compatibility audit needs to cover individual JSON value reads as well as batch exports and whole-properties reads. Converting a boolean or number to a JSON string changes these outputs even when ordinary HogQL property access casts it back successfully.

Replay capture diagnostics has a direct native SQL path that bypasses HogQL: it calls toJSONString on individual property subcolumns, decodes the results with json.loads, and returns those values. This is a separate consumer to include in any type-erasure experiment. Source.

HogQL also exposes stored JSON types without selecting the whole properties blob. The native rewrite for toJSONString(properties.some_key) reads the subcolumn with as_json=True. A test that changes only whole-document reconstruction would miss this path. Source.

The events REST list endpoint fits the existing whole-properties HogQL category: its backing query selects properties through EventsQueryRunner. It is another consumer of that behavior, rather than a separate native read mechanism. Source. Native batch exports serialize the properties document directly. Source.

Completion condition for the type-compatibility item: verify the chosen contract for batch exports, whole-document and individual-value JSON reads through HogQL, and direct native readers such as replay diagnostics. These are confirmed read paths, not an exhaustive reader inventory or evidence that every consumer breaks.

[aspicer]

Note for the "Resolve native property-removal behavior" item. Not a new blocker.

The deletion job reads a dot in a requested property name as nesting, on both tables. jsonhas_expr (posthog/models/data_deletion_request.py) compiles sub.prop to JSONHas(properties, 'sub', 'prop') for the legacy table, and json_property_presence_expr (posthog/models/event/sql.py) compiles it to the nested subcolumn path properties.sub.prop for the native table. A flat key whose name contains a dot (service.name as one key) therefore cannot be targeted on either table today: the mutation matches no rows, and the verify step passes because it looks the same way. This predates the native work.

Once #107546 stores dotted keys flat (ClickHouse json_type_escape_dots_in_keys, so service.name is stored as the single path service%2Ename), reaching such a key on the native table needs the same encoding in three places at once: the presence predicate, the verify step, and the key list passed to JSONDropKeys. Mutations also run with server-level settings, not the query's, so the read-side setting has to be a profile default on the events cluster for the drop-keys rewrite to see dots.

Decide the request spelling for a flat dotted key, or document that a dot always means nesting, when the deletes job is re-enabled for sharded_events_json.