# WORK
## Feature Request
- Design the architecture for a Rust crate named `sqlite-provider` that provides a backend-agnostic abstraction over the SQLite3 C API.
- Include a provider SPI trait for backends (libsqlite3, sqlcipher, libsql) with versioning considerations.
- Provide safe wrapper types (Connection, Statement, Value) with lifetime handling and error mapping.
- Provide an extension framework for scalar functions with safe routing from C callbacks into Rust.
- Provide a virtual table abstraction with lifecycle management (xCreate/xConnect/xDisconnect/xDestroy).
- Write the design in English to `.codex/design/sqlite-provider`.
## Agent Work Plan
1. Baseline setup: confirm repo state, create `.codex` directories, and capture initial knowledge entries.
2. Draft the high-level architecture and core trait sketches (provider SPI, safe API wrappers) including versioning strategy.
3. Draft extension framework and virtual table abstractions with lifecycle and safety strategies.
4. Finalize the design doc with usage example and panic/FFI safety notes; update progress tracking.
5. Review design doc completeness and API correctness; refine SPI signatures and add aggregate-function support details.
6. Record new findings and update knowledge entries after refinements.
7. Backend constraints review: inspect current design doc and enumerate backend-specific constraints for libsqlite3, SQLCipher, and libsql (compatibility, keying, and API extensions).
8. Update the design doc to bake in backend constraints (open options, optional keying APIs, backend identification/versioning, extension hooks).
9. Record backend constraint findings and design updates in `.codex/knowledge`; update work progress.
10. Review the local `sqlite3.h` interface surface and record concrete API facts in `.codex/knowledge`.
11. Compare the design doc against `sqlite3.h` and list missing features or mismatches; summarize for the user.
12. Update work progress after the review.
13. Update the design doc with `create_window_function` SPI entry and feature gating.
14. Add optional extension traits section covering hooks/backup/blob/serialize/WAL/metadata.
15. Add explicit notes on `RawBytes` length semantics and `result_*` ownership guarantees; update knowledge entries.
16. Add safe wrapper API sketches for optional extension traits.
17. Tighten `OwnedBytes`/`free` ownership model in the serialize trait and update safety notes.
18. Record new design updates in `.codex/knowledge` and update progress.
19. Flesh out safe wrapper callback registration for trace/authorizer hooks.
20. Add `SerializedDb::into_vec()` helper to detach backend allocation.
21. Record new wrapper changes in `.codex/knowledge` and update progress.
22. Add typed authorizer action constants/enum and structured `TraceEvent` decoding for trace callbacks.
23. Record new trace/authorizer updates in `.codex/knowledge` and update progress.
24. Add `AuthorizerResult` enum and per-variant trace docs; update knowledge tracking.
25. Add AuthorizerResult return-code docs and example usage for trace/authorizer handlers.
26. Read both design documents (`.codex/design/sqlite-provider.md` and `sqlite-provider.md`) and capture concrete structural/API differences.
27. Compare SPI coverage (versioning, open options, prepare/bind/value/column, UDF/window, vtab/module) and list feature deltas.
28. Compare safety/ownership/lifetime strategies (RawBytes vs slice, ownership for results, callback handling) and extract trade-offs.
29. Summarize pros/cons of each design version and identify when each is preferable.
30. Record comparison facts in `.codex/knowledge` with file locations.
31. Update work progress after completing the comparison write-up.
32. Initialize the Rust crate skeleton (`Cargo.toml`, `src/lib.rs`, module layout) and note any design feasibility adjustments.
33. Implement core SPI types plus `error`/`value` modules (flags, `RawBytes`, `Error`, `Value`, `ValueRef`) with unit tests.
34. Implement `Connection`/`Statement`/`Row` wrappers (prepare/step/bind/column access) and add MockApi-based tests.
35. Implement function UDF framework (`Context`, scalar/aggregate/window registration + trampolines) with tests.
36. Implement optional extension wrappers (hooks/backup/blob/serialize/WAL/metadata) and tests for callback handles + serialize.
37. Implement virtual table traits and minimal module glue; document any backend constraints in knowledge.
38. Add integration tests for key flows (prepare/step, UDF, aggregate, serialize, hooks) and run `cargo test`.
39. Update `.codex/knowledge` with concrete code facts/locations and update work progress.
40. Post-implementation review: compare implemented modules to design doc, note deviations (SPI additions, vtab generics), and verify critical components are present.
41. Scan for performance/readability/test-coverage improvements and record any follow-ups.
42. Update work progress after the review sweep.
43. Review current implementation against `.codex/design/sqlite-provider.md`, note any gaps or feasibility concerns, and identify performance/test improvements.
44. Reduce hot-path allocations in virtual table argument handling; keep the API simple and add concise safety comments where needed.
45. Add targeted doc comments for tricky lifetime/ownership semantics in public APIs (e.g., RawBytes, result_text/blob usage).
46. Add integration tests for window function registration and virtual table feature gating; add unit tests for new vtab argument handling.
47. Run `cargo test`, then update `.codex/knowledge` with new facts and progress.
48. Review current changes (manual inspection since no git) against the design doc and confirm coverage; note any remaining gaps or improvement opportunities.
49. Apply any remaining improvements discovered during the review (or record that none are needed).
50. Update progress/knowledge to reflect the review outcome.
51. Plan and implement the `sqlite-provider-abi` crate: add workspace member, cdylib config, and ABI registry design; document constraints in WORK.md.
52. Implement ABI handle types and provider registry (core SPI + optional extensions + extras) in `sqlite-provider-abi`.
53. Implement C ABI symbols from `sqlite3.h`, including utility functions and error handling; keep hot paths allocation-light.
54. Add minimal tests for ABI helpers and run `cargo test`.
55. Update `.codex/knowledge` with ABI design/implementation facts and update progress.
56. Review `sqlite-provider-abi` symbol coverage against `sqlite3.h` using `rg` + `comm`, confirm all required `sqlite3_*` symbols are exported (non-function typedefs excluded).
57. Review ABI shim for design-doc alignment and potential safety/perf issues; record any gaps or improvements (or confirm none).
58. Run tests (`cargo test`, `cargo test -p sqlite-provider-abi`) and capture results for the ABI shim.
59. Update `.codex/knowledge` with symbol coverage verification + test execution facts; update work progress.
60. Add `sqlite-provider-sqlite3` workspace crate implementing a dynamic `libsqlite3` backend adapter (`Sqlite3Api` + optional metadata); keep dependencies minimal (`libc` only) and document any safety assumptions inline.
61. Add ABI-shim integration tests for `sqlite3_exec`/`sqlite3_get_table` using a mock provider with predictable row data and column names.
62. Add ABI-shim end-to-end integration test that registers the `libsqlite3` adapter (if available) and validates `sqlite3_exec` + `sqlite3_get_table` against a real in-memory database; skip when `libsqlite3` cannot be loaded.
63. Run `cargo test` and `cargo test -p sqlite-provider-abi`; capture results and update `.codex/knowledge` with new adapter/test facts.
64. Post-change review: scan new adapter/test code for safety/perf issues or missing coverage; record any follow-up improvements (or confirm none).
65. Update progress/knowledge to reflect the review outcome.
66. Integration-test discovery pass: re-read current ABI tests and verify whether they are true black-box dynamic-loading tests or in-process Rust-link tests.
67. Identify ABI initialization constraints for dynamic loading (provider registration and backend bootstrap path), and define a production-safe initialization design.
68. Design the black-box test architecture (consumer process, dynamic loading path, symbol mapping strategy, and result assertions).
69. Define test directory structure plus build/run orchestration changes (Makefile/scripts/env vars) that guarantee the cdylib artifact is built and explicitly loaded.
70. Specify detailed scenario coverage: open/close, exec DDL+DML, prepare/bind/step/column/finalize, and backend error propagation through the C ABI.
71. Write `.codex/design/integration-tests.md` in English with concrete code-location references and implementation-oriented snippets.
72. Implementation phase (next): add ABI bootstrap hooks/default backend registration path and add the new black-box integration test assets.
73. Validation phase (next): run the new black-box test target(s), collect results, and update knowledge/progress with any follow-up fixes.
74. Feasibility audit for `.codex/design/integration-tests.md` against current code: verify required symbols, bootstrap constraints, and cross-crate dependency direction before edits.
75. Module A (ABI bootstrap): add idempotent default-backend bootstrap helper and exported `sqlite3_provider_init_default`, then wire it into `sqlite3_initialize` and `sqlite3_open_v2`.
76. Module A validation: run focused ABI tests around open/exec/get_table and ensure no regressions in existing Rust-linked integration tests.
77. Module B (orchestration): add deterministic artifact locator script and root `Makefile` targets for building/running black-box tests.
78. Module C (black-box tests): add Python `ctypes` consumer tests that dynamically load only the produced cdylib and cover lifecycle, exec, prepare/bind/step/column/finalize, and error propagation.
79. Module C validation: run the black-box test entrypoint and resolve any ABI mismatch findings.
80. Documentation pass: add concise doc comments/safety notes for the new public bootstrap ABI and any tricky ownership points touched in this change.
81. Knowledge capture: record concrete code facts and run results in `.codex/knowledge/abi-blackbox-integration-tests.md`.
82. Final review: compare implementation against the design doc, scan for performance/readability regressions, and update progress status.
83. Delivery pass (user request): re-read `.codex/design/integration-tests.md` and verify that each requested deliverable file exists and matches bootstrap constraints.
84. Tighten Python ABI typing for `sqlite3_exec` error out-parameter to `char**` semantics in the black-box runner while preserving explicit `sqlite3_provider_init_default` bootstrap call.
85. Re-run the black-box suite strictly through `make test-abi-blackbox` (no `cargo test`) and confirm pass/fail status.
86. Record concrete 2026-02-13 delivery verification facts and updated file locations in `.codex/knowledge/abi-blackbox-integration-tests.md`.
87. Update work progress/review notes for this delivery pass.
88. New request intake: review current completed plan state and existing design/knowledge docs before creating a new design artifact.
89. Define an SDET-oriented test strategy for SQLite local-first edge-node behavior covering concurrency, JSON1, analytics SQL correctness, and rollback atomicity.
90. Write `.codex/design/test-suites.md` with distinct scenario sections, each including strategy plus representative `pytest` + `sqlite3` code snippets and explicit PRAGMA configuration comments.
91. Capture concrete delivery facts and file locations for this test-suite design in `.codex/knowledge/sqlite3-api-test-suite-design.md`.
92. Update `WORK.md` progress/review notes for the new design-delivery task.
93. New review request intake: read `.codex/design/test-suites.md` completely and perform a codebase-grounded feasibility/quality review.
94. Re-read unfamiliar runtime paths across `sqlite-provider`, `sqlite-provider-abi`, `sqlite-provider-sqlite3`, and tests to eliminate ambiguity before proposing revisions.
95. Identify strengths/weaknesses/gaps of the current test-suite design against production requirements (reliability, performance hot path awareness, and deploy readiness).
96. Write revised, self-contained design document to `.codex/design/abc_v2.md` with concrete implementation guidance and code-location references.
97. Record review/revision facts in `.codex/knowledge/sqlite3-api-test-suite-review.md`.
98. Update `WORK.md` progress/review notes for this review/revision delivery.
99. New implementation intake: read `.codex/design/test-suites.md` completely, re-read existing runtime/test code paths, and perform a feasibility audit against current ABI/test harness constraints.
100. Decompose the implementation into single-responsibility modules (shared helpers, scenario tests, orchestration) and record the module-by-module execution/testing plan in `WORK.md`.
101. Module 1 (shared helper): add `tests/sqlite_provider_py/conftest.py` with deterministic connection policy (`WAL`, sync mode, FK, busy timeout) and bounded lock-retry helper.
102. Module 1 validation: run the new Python test package in collection/targeted mode to confirm helper wiring and import/package layout.
103. Module 2 (scenario tests): add `test_concurrency_busy.py`, `test_json1_hybrid.py`, `test_window_cte_integrity.py`, and `test_transaction_atomicity.py` aligned with design assertions and anti-flake guards.
104. Module 2 validation: run each scenario test module individually and address reliability/performance issues (timeouts, deterministic ordering, minimal per-row Python overhead).
105. Module 3 (orchestration): update `Makefile` with a dedicated target for the new pytest suite and keep existing ABI black-box target unchanged.
106. Full validation: run the new pytest target plus required Rust tests (`cargo test <test_case_name>` focused runs and baseline `cargo test`) and resolve regressions.
107. Documentation/readability pass: add concise comments/docstrings for tricky test orchestration details only where needed.
108. Knowledge/progress update: record concrete implementation facts and run outcomes in `.codex/knowledge`, then update `WORK.md` progress and review notes.
109. Shim-enforcement request intake: ensure SQL scenario tests route through `libsqlite_provider_abi.so` only, and define symbol-origin verification for `sqlite3_open` and `sqlite3_exec`.
110. Module A (ctypes harness): replace Python `sqlite3`-based helpers with C-ABI wrappers in `tests/sqlite_provider_py/conftest.py`, including `sqlite3_provider_init_default` bootstrap and `dladdr` origin assertions.
111. Module A validation: run syntax checks and direct helper initialization to verify symbol-origin assertions and ABI bootstrap behavior.
112. Module B (scenario migration): rewrite all `tests/sqlite_provider_py` scenarios to use the ctypes C-ABI wrapper only (no Python `sqlite3` module calls).
113. Module B validation: execute migrated scenario functions via a direct Python harness with `SQLITE3_SPI_LIB` configured, and confirm all assertions pass.
114. Module C (orchestration): update `Makefile` target `test-sqlite-provider-py` to build ABI first and inject `SQLITE3_SPI_LIB` from the locator script.
115. Full validation for shim-enforcement pass: run `make test-sqlite-provider-py`, `make test-abi-blackbox`, focused `cargo test <test_case_name>`, and baseline cargo suites; capture environment blockers if present.
116. Knowledge/progress update: record concrete code locations and validation outcomes for this shim-enforcement pass in `.codex/knowledge` and `WORK.md`.
117. Unsafe-warning intake: map Rust 2024 `unsafe_op_in_unsafe_fn` warnings to concrete call sites in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, `src/value.rs`, and `src/vtab.rs`.
118. Safety patch module: wrap each warned unsafe operation in explicit `unsafe { ... }` blocks with narrow scope; keep semantics unchanged and add concise safety comments only where needed for non-obvious raw-pointer assumptions.
119. Validation pass: run crate tests (`cargo test`) and confirm the warning set is cleared (or capture exact residual blockers if the environment prevents full execution).
120. Knowledge capture: record precise touched functions and safety rationale in `.codex/knowledge/sqlite-provider-impl.md`.
121. Progress/review update: mark completion status in `WORK.md` and append dated review notes for this warning-remediation pass.
122. Clippy warning intake (2026-02-16): map the reported `type_complexity`, `not_unsafe_ptr_arg_deref`, `len_without_is_empty`, `too_many_arguments`, `missing_safety_doc`, and `unnecessary_cast` findings to concrete call sites in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, and `src/vtab.rs`.
123. Safety/API patch module: add a trace callback type alias, mark `Connection::progress_handler` as `unsafe` with safety docs, and add `Blob::is_empty`; keep behavior unchanged.
124. SPI lint-hardening module: add unsafe-trait `# Safety` docs and narrowly scoped Clippy allows for SQLite C-signature-heavy APIs where argument counts/doc granularity are intentionally inherited from upstream ABI.
125. Virtual-table cleanup module: remove redundant raw-pointer casts in vtab glue while preserving existing associated-type bounds and ownership behavior.
126. Validation pass (Clippy): run `cargo clippy --all-targets --all-features` and fix any residual diagnostics from this warning set.
127. Knowledge/progress update: record concrete code locations and validation results in `.codex/knowledge/sqlite-provider-impl.md`, then mark completion and append dated review notes in `WORK.md`.
## Agent Work Progress
- [x] 1. Baseline setup: confirm repo state, create `.codex` directories, and capture initial knowledge entries.
- [x] 2. Draft the high-level architecture and core trait sketches (provider SPI, safe API wrappers) including versioning strategy.
- [x] 3. Draft extension framework and virtual table abstractions with lifecycle and safety strategies.
- [x] 4. Finalize the design doc with usage example and panic/FFI safety notes; update progress tracking.
- [x] 5. Review design doc completeness and API correctness; refine SPI signatures and add aggregate-function support details.
- [x] 6. Record new findings and update knowledge entries after refinements.
- [x] 7. Backend constraints review: inspect current design doc and enumerate backend-specific constraints for libsqlite3, SQLCipher, and libsql (compatibility, keying, and API extensions).
- [x] 8. Update the design doc to bake in backend constraints (open options, optional keying APIs, backend identification/versioning, extension hooks).
- [x] 9. Record backend constraint findings and design updates in `.codex/knowledge`; update work progress.
- [x] 10. Review the local `sqlite3.h` interface surface and record concrete API facts in `.codex/knowledge`.
- [x] 11. Compare the design doc against `sqlite3.h` and list missing features or mismatches; summarize for the user.
- [x] 12. Update work progress after the review.
- [x] 13. Update the design doc with `create_window_function` SPI entry and feature gating.
- [x] 14. Add optional extension traits section covering hooks/backup/blob/serialize/WAL/metadata.
- [x] 15. Add explicit notes on `RawBytes` length semantics and `result_*` ownership guarantees; update knowledge entries.
- [x] 16. Add safe wrapper API sketches for optional extension traits.
- [x] 17. Tighten `OwnedBytes`/`free` ownership model in the serialize trait and update safety notes.
- [x] 18. Record new design updates in `.codex/knowledge` and update progress.
- [x] 19. Flesh out safe wrapper callback registration for trace/authorizer hooks.
- [x] 20. Add `SerializedDb::into_vec()` helper to detach backend allocation.
- [x] 21. Record new wrapper changes in `.codex/knowledge` and update progress.
- [x] 22. Add typed authorizer action constants/enum and structured `TraceEvent` decoding for trace callbacks.
- [x] 23. Record new trace/authorizer updates in `.codex/knowledge` and update progress.
- [x] 24. Add `AuthorizerResult` enum and per-variant trace docs; update knowledge tracking.
- [x] 25. Add AuthorizerResult return-code docs and example usage for trace/authorizer handlers.
- [x] 26. Read both design documents (`.codex/design/sqlite-provider.md` and `sqlite-provider.md`) and capture concrete structural/API differences.
- [x] 27. Compare SPI coverage (versioning, open options, prepare/bind/value/column, UDF/window, vtab/module) and list feature deltas.
- [x] 28. Compare safety/ownership/lifetime strategies (RawBytes vs slice, ownership for results, callback handling) and extract trade-offs.
- [x] 29. Summarize pros/cons of each design version and identify when each is preferable.
- [x] 30. Record comparison facts in `.codex/knowledge` with file locations.
- [x] 31. Update work progress after completing the comparison write-up.
- [x] 32. Initialize the Rust crate skeleton (`Cargo.toml`, `src/lib.rs`, module layout) and note any design feasibility adjustments.
- [x] 33. Implement core SPI types plus `error`/`value` modules (flags, `RawBytes`, `Error`, `Value`, `ValueRef`) with unit tests.
- [x] 34. Implement `Connection`/`Statement`/`Row` wrappers (prepare/step/bind/column access) and add MockApi-based tests.
- [x] 35. Implement function UDF framework (`Context`, scalar/aggregate/window registration + trampolines) with tests.
- [x] 36. Implement optional extension wrappers (hooks/backup/blob/serialize/WAL/metadata) and tests for callback handles + serialize.
- [x] 37. Implement virtual table traits and minimal module glue; document any backend constraints in knowledge.
- [x] 38. Add integration tests for key flows (prepare/step, UDF, aggregate, serialize, hooks) and run `cargo test`.
- [x] 39. Update `.codex/knowledge` with concrete code facts/locations and update work progress.
- [x] 40. Post-implementation review: compare implemented modules to design doc, note deviations (SPI additions, vtab generics), and verify critical components are present.
- [x] 41. Scan for performance/readability/test-coverage improvements and record any follow-ups.
- [x] 42. Update work progress after the review sweep.
- [x] 43. Review current implementation against `.codex/design/sqlite-provider.md`, note any gaps or feasibility concerns, and identify performance/test improvements.
- [x] 44. Reduce hot-path allocations in virtual table argument handling; keep the API simple and add concise safety comments where needed.
- [x] 45. Add targeted doc comments for tricky lifetime/ownership semantics in public APIs (e.g., RawBytes, result_text/blob usage).
- [x] 46. Add integration tests for window function registration and virtual table feature gating; add unit tests for new vtab argument handling.
- [x] 47. Run `cargo test`, then update `.codex/knowledge` with new facts and progress.
- [x] 48. Review current changes (manual inspection since no git) against the design doc and confirm coverage; note any remaining gaps or improvement opportunities.
- [x] 49. Apply any remaining improvements discovered during the review (or record that none are needed).
- [x] 50. Update progress/knowledge to reflect the review outcome.
- [x] 51. Plan and implement the `sqlite-provider-abi` crate: add workspace member, cdylib config, and ABI registry design; document constraints in WORK.md.
- [x] 52. Implement ABI handle types and provider registry (core SPI + optional extensions + extras) in `sqlite-provider-abi`.
- [x] 53. Implement C ABI symbols from `sqlite3.h`, including utility functions and error handling; keep hot paths allocation-light.
- [x] 54. Add minimal tests for ABI helpers and run `cargo test`.
- [x] 55. Update `.codex/knowledge` with ABI design/implementation facts and update progress.
- [x] 56. Review `sqlite-provider-abi` symbol coverage against `sqlite3.h` using `rg` + `comm`, confirm all required `sqlite3_*` symbols are exported (non-function typedefs excluded).
- [x] 57. Review ABI shim for design-doc alignment and potential safety/perf issues; record any gaps or improvements (or confirm none).
- [x] 58. Run tests (`cargo test`, `cargo test -p sqlite-provider-abi`) and capture results for the ABI shim.
- [x] 59. Update `.codex/knowledge` with symbol coverage verification + test execution facts; update work progress.
- [x] 60. Add `sqlite-provider-sqlite3` workspace crate implementing a dynamic `libsqlite3` backend adapter (`Sqlite3Api` + optional metadata); keep dependencies minimal (`libc` only) and document any safety assumptions inline.
- [x] 61. Add ABI-shim integration tests for `sqlite3_exec`/`sqlite3_get_table` using a mock provider with predictable row data and column names.
- [x] 62. Add ABI-shim end-to-end integration test that registers the `libsqlite3` adapter (if available) and validates `sqlite3_exec` + `sqlite3_get_table` against a real in-memory database; skip when `libsqlite3` cannot be loaded.
- [x] 63. Run `cargo test` and `cargo test -p sqlite-provider-abi`; capture results and update `.codex/knowledge` with new adapter/test facts.
- [x] 64. Post-change review: scan new adapter/test code for safety/perf issues or missing coverage; record any follow-up improvements (or confirm none).
- [x] 65. Update progress/knowledge to reflect the review outcome.
- [x] 66. Integration-test discovery pass: re-read current ABI tests and verify whether they are true black-box dynamic-loading tests or in-process Rust-link tests.
- [x] 67. Identify ABI initialization constraints for dynamic loading (provider registration and backend bootstrap path), and define a production-safe initialization design.
- [x] 68. Design the black-box test architecture (consumer process, dynamic loading path, symbol mapping strategy, and result assertions).
- [x] 69. Define test directory structure plus build/run orchestration changes (Makefile/scripts/env vars) that guarantee the cdylib artifact is built and explicitly loaded.
- [x] 70. Specify detailed scenario coverage: open/close, exec DDL+DML, prepare/bind/step/column/finalize, and backend error propagation through the C ABI.
- [x] 71. Write `.codex/design/integration-tests.md` in English with concrete code-location references and implementation-oriented snippets.
- [x] 72. Implementation phase (next): add ABI bootstrap hooks/default backend registration path and add the new black-box integration test assets.
- [x] 73. Validation phase (next): run the new black-box test target(s), collect results, and update knowledge/progress with any follow-up fixes.
- [x] 74. Feasibility audit for `.codex/design/integration-tests.md` against current code: verify required symbols, bootstrap constraints, and cross-crate dependency direction before edits.
- [x] 75. Module A (ABI bootstrap): add idempotent default-backend bootstrap helper and exported `sqlite3_provider_init_default`, then wire it into `sqlite3_initialize` and `sqlite3_open_v2`.
- [x] 76. Module A validation: run focused ABI tests around open/exec/get_table and ensure no regressions in existing Rust-linked integration tests.
- [x] 77. Module B (orchestration): add deterministic artifact locator script and root `Makefile` targets for building/running black-box tests.
- [x] 78. Module C (black-box tests): add Python `ctypes` consumer tests that dynamically load only the produced cdylib and cover lifecycle, exec, prepare/bind/step/column/finalize, and error propagation.
- [x] 79. Module C validation: run the black-box test entrypoint and resolve any ABI mismatch findings.
- [x] 80. Documentation pass: add concise doc comments/safety notes for the new public bootstrap ABI and any tricky ownership points touched in this change.
- [x] 81. Knowledge capture: record concrete code facts and run results in `.codex/knowledge/abi-blackbox-integration-tests.md`.
- [x] 82. Final review: compare implementation against the design doc, scan for performance/readability regressions, and update progress status.
- [x] 83. Delivery pass (user request): re-read `.codex/design/integration-tests.md` and verify that each requested deliverable file exists and matches bootstrap constraints.
- [x] 84. Tighten Python ABI typing for `sqlite3_exec` error out-parameter to `char**` semantics in the black-box runner while preserving explicit `sqlite3_provider_init_default` bootstrap call.
- [x] 85. Re-run the black-box suite strictly through `make test-abi-blackbox` (no `cargo test`) and confirm pass/fail status.
- [x] 86. Record concrete 2026-02-13 delivery verification facts and updated file locations in `.codex/knowledge/abi-blackbox-integration-tests.md`.
- [x] 87. Update work progress/review notes for this delivery pass.
- [x] 88. New request intake: review current completed plan state and existing design/knowledge docs before creating a new design artifact.
- [x] 89. Define an SDET-oriented test strategy for SQLite local-first edge-node behavior covering concurrency, JSON1, analytics SQL correctness, and rollback atomicity.
- [x] 90. Write `.codex/design/test-suites.md` with distinct scenario sections, each including strategy plus representative `pytest` + `sqlite3` code snippets and explicit PRAGMA configuration comments.
- [x] 91. Capture concrete delivery facts and file locations for this test-suite design in `.codex/knowledge/sqlite3-api-test-suite-design.md`.
- [x] 92. Update `WORK.md` progress/review notes for the new design-delivery task.
- [x] 93. New review request intake: read `.codex/design/test-suites.md` completely and perform a codebase-grounded feasibility/quality review.
- [x] 94. Re-read unfamiliar runtime paths across `sqlite-provider`, `sqlite-provider-abi`, `sqlite-provider-sqlite3`, and tests to eliminate ambiguity before proposing revisions.
- [x] 95. Identify strengths/weaknesses/gaps of the current test-suite design against production requirements (reliability, performance hot path awareness, and deploy readiness).
- [x] 96. Write revised, self-contained design document to `.codex/design/abc_v2.md` with concrete implementation guidance and code-location references.
- [x] 97. Record review/revision facts in `.codex/knowledge/sqlite3-api-test-suite-review.md`.
- [x] 98. Update `WORK.md` progress/review notes for this review/revision delivery.
- [x] 99. New implementation intake: read `.codex/design/test-suites.md` completely, re-read existing runtime/test code paths, and perform a feasibility audit against current ABI/test harness constraints.
- [x] 100. Decompose the implementation into single-responsibility modules (shared helpers, scenario tests, orchestration) and record the module-by-module execution/testing plan in `WORK.md`.
- [x] 101. Module 1 (shared helper): add `tests/sqlite_provider_py/conftest.py` with deterministic connection policy (`WAL`, sync mode, FK, busy timeout) and bounded lock-retry helper.
- [x] 102. Module 1 validation: run the new Python test package in collection/targeted mode to confirm helper wiring and import/package layout.
- [x] 103. Module 2 (scenario tests): add `test_concurrency_busy.py`, `test_json1_hybrid.py`, `test_window_cte_integrity.py`, and `test_transaction_atomicity.py` aligned with design assertions and anti-flake guards.
- [x] 104. Module 2 validation: run each scenario test module individually and address reliability/performance issues (timeouts, deterministic ordering, minimal per-row Python overhead).
- [x] 105. Module 3 (orchestration): update `Makefile` with a dedicated target for the new pytest suite and keep existing ABI black-box target unchanged.
- [ ] 106. Full validation: run the new pytest target plus required Rust tests (`cargo test <test_case_name>` focused runs and baseline `cargo test`) and resolve regressions.
- [x] 107. Documentation/readability pass: add concise comments/docstrings for tricky test orchestration details only where needed.
- [x] 108. Knowledge/progress update: record concrete implementation facts and run outcomes in `.codex/knowledge`, then update `WORK.md` progress and review notes.
- [x] 109. Shim-enforcement request intake: ensure SQL scenario tests route through `libsqlite_provider_abi.so` only, and define symbol-origin verification for `sqlite3_open` and `sqlite3_exec`.
- [x] 110. Module A (ctypes harness): replace Python `sqlite3`-based helpers with C-ABI wrappers in `tests/sqlite_provider_py/conftest.py`, including `sqlite3_provider_init_default` bootstrap and `dladdr` origin assertions.
- [x] 111. Module A validation: run syntax checks and direct helper initialization to verify symbol-origin assertions and ABI bootstrap behavior.
- [x] 112. Module B (scenario migration): rewrite all `tests/sqlite_provider_py` scenarios to use the ctypes C-ABI wrapper only (no Python `sqlite3` module calls).
- [x] 113. Module B validation: execute migrated scenario functions via a direct Python harness with `SQLITE3_SPI_LIB` configured, and confirm all assertions pass.
- [x] 114. Module C (orchestration): update `Makefile` target `test-sqlite-provider-py` to build ABI first and inject `SQLITE3_SPI_LIB` from the locator script.
- [x] 115. Full validation for shim-enforcement pass: run `make test-sqlite-provider-py`, `make test-abi-blackbox`, focused `cargo test <test_case_name>`, and baseline cargo suites; capture environment blockers if present.
- [x] 116. Knowledge/progress update: record concrete code locations and validation outcomes for this shim-enforcement pass in `.codex/knowledge` and `WORK.md`.
- [x] 117. Unsafe-warning intake: map Rust 2024 `unsafe_op_in_unsafe_fn` warnings to concrete call sites in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, `src/value.rs`, and `src/vtab.rs`.
- [x] 118. Safety patch module: wrap each warned unsafe operation in explicit `unsafe { ... }` blocks with narrow scope; keep semantics unchanged and add concise safety comments only where needed for non-obvious raw-pointer assumptions.
- [x] 119. Validation pass: run crate tests (`cargo test`) and confirm the warning set is cleared (or capture exact residual blockers if the environment prevents full execution).
- [x] 120. Knowledge capture: record precise touched functions and safety rationale in `.codex/knowledge/sqlite-provider-impl.md`.
- [x] 121. Progress/review update: mark completion status in `WORK.md` and append dated review notes for this warning-remediation pass.
- [x] 122. Clippy warning intake (2026-02-16): map the reported `type_complexity`, `not_unsafe_ptr_arg_deref`, `len_without_is_empty`, `too_many_arguments`, `missing_safety_doc`, and `unnecessary_cast` findings to concrete call sites in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, and `src/vtab.rs`.
- [x] 123. Safety/API patch module: add a trace callback type alias, mark `Connection::progress_handler` as `unsafe` with safety docs, and add `Blob::is_empty`; keep behavior unchanged.
- [x] 124. SPI lint-hardening module: add unsafe-trait `# Safety` docs and narrowly scoped Clippy allows for SQLite C-signature-heavy APIs where argument counts/doc granularity are intentionally inherited from upstream ABI.
- [x] 125. Virtual-table cleanup module: remove redundant raw-pointer casts in vtab glue while preserving existing associated-type bounds and ownership behavior.
- [x] 126. Validation pass (Clippy): run `cargo clippy --all-targets --all-features` and fix any residual diagnostics from this warning set.
- [x] 127. Knowledge/progress update: record concrete code locations and validation results in `.codex/knowledge/sqlite-provider-impl.md`, then mark completion and append dated review notes in `WORK.md`.
## Review Notes
- Git diff/staging review is unavailable because there is no `.git` directory in the repo root.
- Design adjustments implemented: `Sqlite3Api::user_data` is a static associated function, `Sqlite3Api::declare_vtab` added for vtab schema declaration, and vtab traits are generic over the provider (`VirtualTable<P>`, `VTabCursor<P>`).
- 2026-02-11 manual review: design doc coverage looks complete; added vtab arg buffering, generic `create_module<T>` call site support, window/vtab integration tests, and alignment-safe aggregate storage in mocks.
- 2026-02-11 post-change review: new `sqlite-provider-sqlite3` adapter + ABI exec/get_table tests reviewed; no additional follow-ups identified.
- 2026-02-12 integration-test feasibility review: design is fully implementable. One optional caveat remains by design: if `sqlite-provider-abi` is compiled without `default-backend`, automatic bootstrap returns `SQLITE_MISUSE`; keep default features enabled for production black-box loading flows.
- 2026-02-12 bootstrap hardening: guarded default-backend initialization with `OnceLock` to avoid concurrent duplicate bootstrap work and transient leaked provider-state allocations.
- 2026-02-12 post-completion sweep: compared implementation against `.codex/design/integration-tests.md` and current ABI surface; no additional correctness/performance follow-ups identified beyond completed bootstrap hardening.
- 2026-02-13 delivery pass: verified requested deliverables are present (`sqlite-provider-abi/Cargo.toml`, `sqlite-provider-abi/src/lib.rs`, `scripts/locate_sqlite_abi_lib.sh`, `Makefile`, `tests/abi_blackbox/test_sqlite_abi.py`), refined Python `sqlite3_exec` `err_out` typing to `c_char_p*`, and re-validated via `make test-abi-blackbox` (all tests passing).
- 2026-02-13 SDET design delivery: added `.codex/design/test-suites.md` with four advanced SQLite reliability scenarios (busy retry/WAL contention, JSON1 deep query + partial mutation, window-function oracle validation, rollback atomicity) and recorded concrete facts in `.codex/knowledge/sqlite3-api-test-suite-design.md`.
- 2026-02-13 SDET review/revision delivery: reviewed `.codex/design/test-suites.md` against current runtime/test surfaces, produced revised design `.codex/design/abc_v2.md` with repository-grounded feasibility/performance guidance and CI acceptance criteria, and recorded evidence in `.codex/knowledge/sqlite3-api-test-suite-review.md`; revalidated repo health via `cargo test` and `make test-abi-blackbox` (all passing).
- 2026-02-13 SDET implementation delivery: added `tests/sqlite_provider_py/` scenario suite (`conftest.py`, concurrency, JSON1, window/CTE, rollback atomicity) plus `Makefile` target `test-sqlite-provider-py`; `make test-sqlite-provider-py` currently fails in this environment because `/usr/bin/python3` lacks `pytest`, so scenario logic was validated via direct function execution with a minimal local `pytest` stub, while baseline regressions were rechecked with `make test-abi-blackbox`, focused `cargo test <test_case_name>`, `cargo test`, and `cargo test -p sqlite-provider-abi` (all passing).
- 2026-02-13 shim-enforcement delivery: migrated `tests/sqlite_provider_py` from Python `sqlite3` to ctypes C-ABI wrappers, added `dladdr` origin assertions requiring `sqlite3_open`/`sqlite3_exec` to resolve from `libsqlite_provider_abi.so`, and updated `Makefile` to inject `SQLITE3_SPI_LIB`; validated via direct shim bootstrap check + direct scenario execution harness + ABI/cargo regressions, while `make test-sqlite-provider-py` remains blocked by missing `pytest` module in the environment.
- 2026-02-16 rust-2024 warning cleanup: fixed the requested `unsafe_op_in_unsafe_fn` call sites in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, `src/value.rs`, and `src/vtab.rs` by adding explicit `unsafe { ... }` blocks; `cargo check --lib` is now clean and `cargo test` passes, with remaining warnings isolated to `tests/integration.rs` mock unsafe methods.
- 2026-02-16 clippy warning-set cleanup: resolved reported `src/*` findings (`type_complexity`, `not_unsafe_ptr_arg_deref`, `len_without_is_empty`, `too_many_arguments`, `missing_safety_doc`, `unnecessary_cast`) via targeted patches in `src/connection.rs`, `src/function.rs`, `src/provider.rs`, and `src/vtab.rs`; verified with `cargo clippy --lib` (clean) and `cargo clippy --all-targets --all-features` (remaining warnings are unrelated pre-existing `tests/integration.rs` diagnostics).