Compatibility Testing Strategy

This document sketches the guard-rails for the PostgreSQL compatibility bridge. It mixes current coverage with later-stage plans; for the current alpha contract, treat the simple-query path as the supported center of gravity.

Guiding Principles

1. Confidence through layers – run fast unit suites on every change, replay realistic SQL traces in CI, and bootstrap heavy regression runs nightly.
2. Golden results – anything we emulate from PostgreSQL must be exercised against a real Postgres reference and compared for both results and SQLSTATE/diagnostics.
3. Observability baked in – every suite should emit per-query metadata. Tests must assert that compat/stats counters change as expected and capture compat/log output (or archived JSONL) for post-mortem analysis.

Test Layers

1. Translator Unit Tests (zig test)

• Cover SQL→sydraQL transformation rules directly.
• One fixture per SQL construct (DDL, DML, aggregates, JSONB ops, arrays, etc.).
• Assert the generated sydraQL AST, SQLSTATE metadata when a rule falls back, and the stats/logging side effects (translations, fallbacks, cache hits when fixtures opt in).
• Status: scaffolding landed (compat/fixtures/translator.zig, tests/translator/cases.jsonl, and query/translator.zig driving the fixtures).
• Runs automatically via zig build test.

2. Protocol Wire Tests

• Stand up the PG wire front-end inside a test harness.
• Use synthetic libpq/psql scripts to exercise startup, auth, and simple query flows first.
• Assert network-level behaviour (messages, SQLSTATE, parameter status) and verify structured logs capture handshake + query events.
• Extended protocol, COPY, and transaction-heavy suites remain later work.

3. Trace Replay Harness

• Capture real SQL traffic only after the simple-query bridge is stable enough to justify replay work.
• Store anonymised traces in tests/traces/<app>/trace.jsonl with metadata (session, parameters, expected results).
• Provide a replay tool (zig build compat-replay --trace tests/traces/...) that feeds the translator and compares results against a local Postgres instance.
• Failures should emit diff files with SQLSTATE, payload, execution stats, and include the relevant JSONL log excerpt for debugging.

4. ORM Smoke Suites

• Provision containerised sample apps only after the translator/catalog surface is broad enough to make the signal worthwhile.
• Each suite starts the sydra PG endpoint, runs the ORM migrations/tests, and records metrics.
• Capture translator logs + stats snapshots at suite start/end to quantify coverage.
• Managed via Nix flake outputs for reproducibility; triggered nightly and on release branches.

5. Postgres Regression Subset

• Vendor the relevant SQL files from the upstream PG regression suite only after the bridge supports more than the current narrow subset.
• Execute them through the translator, comparing outputs to a real Postgres run.
• Maintain a skiplist for features we intentionally do not support yet; log changes to keep the matrix updated.

6. Performance Budgets

• pgbench-style workloads are later work once the compatibility surface is larger than startup + simple query.
• Capture latency/QPS metrics; fail CI if we regress relative to the baseline by more than a threshold.
• Export results to ./compat-matrix-v0.1 (or a future replacement) for transparency.

Tooling & Infrastructure Checklist

• tests/README.md describing how to run each suite locally.
• CLI to record/replay SQL traces (writes JSONL, redacts literals by default).
• Golden-result storage for translator unit tests (JSON fixtures) to simplify review.
• Utility to diff SQLSTATE/error payloads (compat/sqlstate provides mapping glue).
• Metrics sink (compat/stats + /debug/compat/stats) must be reset between suites to avoid crosstalk.

Schedule & Ownership

Milestone	Deliverable	Notes
M1	Translator unit suite + golden fixtures	Blocks translator development
M2	Wire protocol harness	Needs PG client libs in CI
M3	Trace replay tooling	Requires anonymisation rules
M4	ORM smoke tests	Use docker-compose or Nix shells
M5	Regression subset integration	Start with parser/aggregates JSON suites
M6	Performance budgets	Reuse loadgen tooling

Immediate Next Steps

1. Keep translator fixtures current and runnable under zig build test.
2. Make the wire smoke path honest and buildable for the supported simple-query contract.
3. Defer trace replay, ORM suites, and heavier regression work until the bridge intentionally widens.

Feedback welcome; we will evolve this doc as the compatibility layer matures.