Files
0pt/scripts/bi-p005-arrow-roundtrip.mjs
Claude Opus 5 84bb9faa4f
CI (SIAX Cloud) / security (push) Successful in 41s
Security Scan (SIAX Cloud) / security (push) Successful in 40s
CI (SIAX Cloud) / integration (push) Successful in 52s
CI (SIAX Cloud) / quality (push) Successful in 1m5s
CI (SIAX Cloud) / security (pull_request) Successful in 31s
Security Scan (SIAX Cloud) / security (pull_request) Successful in 29s
CI (SIAX Cloud) / integration (pull_request) Successful in 45s
CI (SIAX Cloud) / quality (pull_request) Successful in 53s
feat(analytics): BI-P002..BI-P012 analytics core — domain model, tiers, contracts, Arrow, ClickHouse engine, isolation, governance
Implements the eight BI program prompts that make 0pt the canonical SIAX
analytics capability. Every file is NEW: not one existing file is modified,
and src/analytics/clickhouse-analytics.ts is byte-identical to main.

BI-P002 — the nineteen canonical entities, verbatim, with tenant_id +
project_id on every tenant-owned record. sql/008 gives each one a table.
The four version entities BI-P002 names as immutable are enforced by BEFORE
UPDATE/DELETE triggers, not by a TypeScript `readonly` — proven by direct SQL
against a live Postgres.

BI-P003 — RAW/NORMALIZED/CURATED/SERVING with the read rule enforced:
assertReadableByBiSurface refuses a RAW dataset to a dashboard unless a
recorded exception exists. All eight required declarations are NOT NULL.

BI-P004 — DatasetContract and a fail-closed compatibility gate. Fifteen
breaking-change classes are each tested twice: refused under a minor bump,
accepted under a major one.

BI-P005 — Arrow IPC alongside JSON, with a hand-written FlatBuffers writer so
no npm dependency (or CVE surface) is added. Conformance comes from three
independent implementations, not from self-agreement: pyarrow 25.0.1 and
DuckDB 1.5.5 read identical values from both ClickHouse's Arrow and ours, and
ClickHouse's own Arrow C++ ingests our 20,000-row batch with exact aggregates.

BI-P009 — SiaxAnalyticsEngine with all eight capabilities. The existing
ClickHouseAnalytics is COMPOSED, never replaced. executeQuery (analyst SQL) is
declared and deliberately refused with CAPABILITY_UNAVAILABLE until BI-P017 and
BI-P033 land, rather than opening the raw-SQL hole BI-P011 enumerates.

BI-P010 — four purpose-built tables, not one giant one, and the difference is
measured against the counterfactual: 48.8ms/40,960 rows vs 174.4ms/5,400,000
rows for the same question; 5/660 granules vs 660/1149.

BI-P011 — layered isolation, each layer proven with the layer above removed.
Without the forced predicate the same statement returns 50,050 (both tenants);
with it, t_alpha gets 50. Without a row policy an unrestricted user sees 2
tenants; the restricted user running the IDENTICAL statement sees 1.

BI-P012 — every named limit, fired individually and identified by ClickHouse
error code, plus the half usually skipped: an unrelated tenant is timed while
a real CPU bomb is in flight and must still return the CORRECT answer.

Three defects found by writing tests that can fail:
  * `CHECK (array_length(purposes,1) >= 1)` can NEVER fail — array_length is
    NULL for '{}' and a NULL CHECK passes. Fixed with coalesce(); the test
    that caught it is pinned.
  * EXPLAIN ESTIMATE run under the caller's own max_rows_to_read fails with
    code 158 exactly when the estimate would be over budget. Planner calls now
    use a narrower envelope.
  * result_overflow_mode=break returns HTTP 200 with 65,409 of 100,000 rows
    and no indication it is short. Every SELECT ships `throw`.

Verified rather than assumed: server7's ClickHouse holds `opt_scraped` (51
tables, 407,793,471 rows, `persons`/`cz_owners` included) — the b0ts plane this
module is prohibited from touching — has no `opt_analytics` database, zero
tenant_id columns and zero row policies. Nothing there was modified; every
proof ran against disposable containers.

Evidence: evidence/bi-p002-p012/{test-transcript.txt,bi-p010-benchmark.json,
bi-p005-arrow-roundtrip.json,server7-clickhouse-probe.md}
Docs: docs/analytics/BI_ANALYTICS_CORE.md

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
2026-09-06 23:01:27 +02:00

162 lines
6.4 KiB
JavaScript
Executable File

#!/usr/bin/env node
/**
* BI-P005 DoD, executed literally:
*
* "Same query result roundtrips ClickHouse -> Arrow -> DuckDB/Python
* without semantic loss for supported types."
*
* Pulls a result from a LIVE ClickHouse as Arrow IPC, hands the identical
* bytes to pyarrow and DuckDB, and records what each of them read back. Also
* runs the reverse direction — 0pt's own encoder's bytes decoded by pyarrow +
* DuckDB — because the encoder is hand-rolled and the only conformance
* evidence worth having comes from somebody else's parser.
*
* CLICKHOUSE_URL=http://localhost:18123 CLICKHOUSE_AUTH=user:pass \
* node scripts/bi-p005-arrow-roundtrip.mjs
*
* pyarrow/DuckDB run in a throwaway `python:3.12-slim` container, so the host
* needs docker but no Python packages. Output:
* evidence/bi-p002-p012/bi-p005-arrow-roundtrip.json
*
* The 0pt-encoded fixture is produced by `pnpm vitest run
* tests/analytics/arrow-fixture.emit.ts`-style code inlined here via a tiny
* tsx-free path: the encoder is re-implemented ONLY in the sense that this
* script shells out to vitest to write the file, so the bytes tested are the
* bytes the library produces.
*/
import fs from "node:fs";
import path from "node:path";
import os from "node:os";
import { execFileSync } from "node:child_process";
const URL_ = process.env.CLICKHOUSE_URL;
const AUTH = process.env.CLICKHOUSE_AUTH;
if (!URL_) {
console.error("CLICKHOUSE_URL is required — this script measures, it does not simulate.");
process.exit(2);
}
const work = fs.mkdtempSync(path.join(os.tmpdir(), "bi-p005-"));
const headers = AUTH ? { Authorization: `Basic ${Buffer.from(AUTH).toString("base64")}` } : {};
const QUERY = `
SELECT toString(t) AS tenant_id, toInt64(n) AS n, toFloat64(v) AS v, b AS flag,
toDateTime64(ts, 3, 'UTC') AS ts
FROM (
SELECT arrayJoin([
('t_alpha', 1, 1.5, true, '2026-01-01 00:00:00.000'),
('t_beta', 2, 2.5, false, '2026-06-15 08:09:10.123'),
('t_gamma', 3, 3.25, true, '2026-09-06 12:34:56.789')
]) AS tup, tup.1 AS t, tup.2 AS n, tup.3 AS v, tup.4 AS b, tup.5 AS ts
)`;
async function chArrow(sql, settings) {
const u = new URL(URL_);
u.searchParams.set("query", sql);
for (const [k, v] of Object.entries(settings)) u.searchParams.set(k, v);
const res = await fetch(u.toString(), { method: "POST", headers, body: "" });
const buf = Buffer.from(await res.arrayBuffer());
if (!res.ok) throw new Error(`CH ${res.status}: ${buf.toString("utf8").slice(0, 300)}`);
return buf;
}
const PY = `
import pyarrow as pa, pyarrow.ipc as ipc, duckdb, json, sys, os
out = {}
for name in sorted(os.listdir('/w')):
if not name.endswith('.arrow'):
continue
tbl = ipc.open_stream(pa.BufferReader(open('/w/'+name,'rb').read())).read_all()
con = duckdb.connect(); con.register('t', tbl)
out[name] = {
"pyarrow_version": pa.__version__,
"duckdb_version": duckdb.__version__,
"schema": [{"name": f.name, "type": str(f.type)} for f in tbl.schema],
"num_rows": tbl.num_rows,
"pyarrow_values": json.loads(json.dumps(tbl.to_pydict(), default=str)),
"duckdb_types": [(r[0], r[1]) for r in con.execute("describe select * from t").fetchall()],
"duckdb_rows": [[str(c) for c in row] for row in con.execute("select * from t order by n").fetchall()],
}
print(json.dumps(out))
`;
async function main() {
const version = (await (async () => {
const u = new URL(URL_);
u.searchParams.set("query", "SELECT version() FORMAT TSVRaw");
const r = await fetch(u.toString(), { method: "POST", headers, body: "" });
return (await r.text()).trim();
})());
// Direction 1: ClickHouse's own Arrow C++ output.
const chBytes = await chArrow(`${QUERY} FORMAT ArrowStream`, {
output_format_arrow_string_as_string: "1",
output_format_arrow_low_cardinality_as_dictionary: "0",
});
fs.writeFileSync(path.join(work, "01_clickhouse_encoded.arrow"), chBytes);
// Direction 2: 0pt's own encoder. Driven through the SHIPPED module via
// tests/analytics/arrow-fixture.test.ts (ARROW_FIXTURE_DIR) so the bytes
// pyarrow sees are the library's, not a copy pasted into this script.
execFileSync(
"npx",
["vitest", "run", "tests/analytics/arrow-fixture.test.ts"],
{ stdio: "inherit", env: { ...process.env, ARROW_FIXTURE_DIR: work } },
);
// The reader script goes in as a FILE, not as `python -c "..."`: passing a
// multi-line program through a shell argument mangles the newlines.
fs.writeFileSync(path.join(work, "read_arrow.py"), PY);
const raw = execFileSync(
"docker",
[
"run", "--rm", "-v", `${work}:/w`, "-w", "/w", "python:3.12-slim",
"bash", "-lc",
"pip install --quiet pyarrow duckdb pytz >/dev/null 2>&1 && python /w/read_arrow.py",
],
{ encoding: "utf8", maxBuffer: 32 * 1024 * 1024 },
);
const decoded = JSON.parse(raw);
const evidence = {
generated_at: new Date().toISOString(),
clickhouse_version: version,
query: QUERY.trim(),
directions: {
"clickhouse -> arrow -> pyarrow/duckdb": decoded["01_clickhouse_encoded.arrow"],
"0pt encoder -> arrow -> pyarrow/duckdb": decoded["02_0pt_encoded.arrow"],
},
byte_sizes: {
clickhouse_encoded: chBytes.length,
opt_encoded: fs.statSync(path.join(work, "02_0pt_encoded.arrow")).size,
},
verdict: null,
};
// The DoD is "without semantic loss": the two independent readers must agree
// on the SAME values for both encoders.
const a = evidence.directions["clickhouse -> arrow -> pyarrow/duckdb"];
const b = evidence.directions["0pt encoder -> arrow -> pyarrow/duckdb"];
const same =
JSON.stringify(a.pyarrow_values) === JSON.stringify(b.pyarrow_values) &&
JSON.stringify(a.duckdb_rows) === JSON.stringify(b.duckdb_rows) &&
JSON.stringify(a.duckdb_types) === JSON.stringify(b.duckdb_types);
evidence.verdict = same
? "PASS — pyarrow and DuckDB read identical values and types from BOTH encoders"
: "FAIL — the two encoders do not round-trip to the same values";
const out = path.join(process.cwd(), "evidence", "bi-p002-p012", "bi-p005-arrow-roundtrip.json");
fs.mkdirSync(path.dirname(out), { recursive: true });
fs.writeFileSync(out, JSON.stringify(evidence, null, 2) + "\n");
console.log(evidence.verdict);
console.log(`wrote ${out}`);
if (!same) process.exitCode = 1;
}
main()
.catch((e) => {
console.error(e);
process.exitCode = 1;
})
.finally(() => fs.rmSync(work, { recursive: true, force: true }));