DDLs are stable

[entlein]

AE and DX agree on the schemata

[ConstanzeTU]

DDL changes — branch fix/ae-protocol-export-pxexport (session 2026-08-19)

Reporting per the standing rule that every forensic_db DDL change lands here. Source of truth: src/vizier/services/adaptive_export/internal/clickhouse/schema.sql (+ registered in ddl.go KnownTables/PixieTables and apply.go OperatorOwnedTables). Both shipped in AE build aeprod71.

1. 59ed1e0e6 — dx_order_records bridge + dx-assigned order_id (pixie-io#136)

• dx_order_seeds: added column order_id String (first column).
• NEW table dx_order_records — the stamped consulted set (one row per (order_id, finding) dx consulted for a primary kubescape log):

  (order_id, unique_id, src_table, vector, source, time_ Int64, pod, remote_addr,
   path, comm, dns_name, hostname, event_time UInt64 DEFAULT toUInt64(time_))
  ENGINE = ReplacingMergeTree()
  ORDER BY (order_id, src_table, time_, pod, remote_addr, path, comm, dns_name)
  PARTITION BY toYYYYMM(fromUnixTimestamp64Nano(event_time))
  TTL fromUnixTimestamp64Nano(event_time) + INTERVAL 30 DAY DELETE
  

• VIEW dx_anomaly_orders: order_id now selects the stored dx_order_seeds.order_id (dx-assigned hash(uniqueID), 1:1 with the log) instead of the old window hash SHA256('v1|'+pod+'|'+lo+'|'+hi) — which collided for same-instant anomalies. lo/hi (±300s) kept for reference only.

2. 095a17cf4 — dx_src__ views: drop upid, add stack_trace (pixie-io#136)

• Dropped upid from the 6 socket_tracer views: dx_src__redis_events, dx_src__conn_stats, dx_src__http_events, dx_src__dns_events, dx_src__pgsql_events, dx_src__mysql_events (upid is raw 128-bit binary → renders as garbage in px; not needed).
• NEW VIEW dx_src__stack_trace:

  SELECT toString(time_) AS ts, toInt64(toUnixTimestamp64Nano(time_)) AS row_time,
         toUInt64(toUnixTimestamp64Nano(event_time)) AS event_time,
         namespace, pod, container, stack_trace_id, stack_trace, count, hostname
  FROM forensic_db.stack_trace
  

No changes to the kubescape-owned tables (alerts, kubescape_logs) or the 12 pixie socket_tracer base tables.

[ConstanzeTU]

DDL change — e426be3f5 (order-id re-key; ships aeprod72)

Re-key the two order objects so dx owns dedup granularity and AE never re-encodes it:

• VIEW dx_anomaly_orders: dedup clause LIMIT 1 BY unique_id → LIMIT 1 BY order_id. (columns unchanged)
• TABLE dx_order_seeds: ORDER BY changed (see DX evidence chain #99) — no column change.

No-op with current dx (order_id is 1:1 with uniqueID); required so a finer dx dedup (per rule/target/event) isn't hidden by the view.