Implement separate History and Audit Log persistence (ADR 0004)

[adwinying]

Problem Statement

SQLcery currently uses one rotating audit.log implementation for two different user needs: connection-scoped Statement recall and a global audit trail. History is reconstructed by filtering that shared file by connection name, so same-named global and project Connections collide, direct Connection Strings lack a safe identity, History and Audit cannot be managed independently, and rotation destroys older audit records. The combined execution path also persists some Slash Commands, truncates all summaries to 120 characters, and cannot provide durable pre-execution audit evidence or fail-closed behavior.

Users need fast, relevant History for the active Connection while retaining a complete, globally ordered, externally consumable Audit Log for every Statement attempted against a live Session. These stores require different identity, retention, durability, failure, and concurrency semantics.

Solution

Replace the combined persistence mechanism with two independent stores built around one shared Connection Identity.

History becomes a bounded, per-Connection Identity JSON document used only for interactive recall. It retains the 1,000 most recent unique Statement strings, deduplicated by exact text and ordered by latest submission. History failures do not block database work.

The Audit Log becomes a mandatory, unbounded JSONL event stream. Every Statement attempted against a live Session produces a durably synced submitted event before database execution and a correlated, durably synced completed event afterward. Audit failures prevent unsafe subsequent execution, while all file work runs outside the UI loop. Both stores safely coordinate concurrent SQLcery processes.

User Stories

1. As a SQLcery user, I want History scoped to the active Connection Identity, so that recall contains only relevant Statements.
2. As a user with a global prod Connection and a project-local prod Connection, I want separate Histories, so that identically named Connections do not leak recall into one another.
3. As a user working in multiple projects with same-named Connections, I want each project Connection to retain separate History, so that project context remains meaningful.
4. As a user who reaches the same database through different Connection definitions, I want those definitions treated as different identities, so that recall follows my configured route rather than inferred database equivalence.
5. As a user of a direct Connection String, I want stable History for the exact supplied string, so that later Sessions using that same string recover the same recall list.
6. As a user of a direct Connection String, I do not want credentials exposed in filenames or Audit metadata, so that persistence does not add a plaintext DSN leak.
7. As a user, I want successful Statements retained in History, so that I can quickly rerun useful work.
8. As a user, I want failed Statements retained in History, so that I can edit and retry them.
9. As a user, I want cancelled and timed-out Statements retained in History, so that interrupted work is recoverable.
10. As a user, I want exact duplicate Statements represented once at their latest position, so that recall is not noisy.
11. As a user, I want whitespace-different Statements treated as distinct, so that SQLcery does not silently normalize meaningful input.
12. As a user, I want History bounded to 1,000 unique Statements per Connection Identity, so that recall storage and startup cost remain controlled.
13. As a user, I want History persisted across Sessions, so that restarting SQLcery does not erase useful recall.
14. As a user, I want a History write failure reported without blocking Statement execution, so that a convenience feature cannot prevent database work.
15. As a user, I want current-session recall to keep working after a History write failure, so that transient filesystem failures do not erase the in-memory experience.
16. As a user running concurrent SQLcery Sessions, I want each Session's Statements preserved on disk, so that concurrent History updates do not overwrite one another.
17. As a user running concurrent Sessions, I accept that another Session's new History appears after restart rather than live, so that recall does not require continuous cross-process synchronization.
18. As an operator, I want a global ordered Audit Log across all Connections, so that external tools can inspect all Statement attempts in one stream.
19. As an operator, I want every attempted Statement logged before it reaches the database, so that a crash cannot erase evidence that execution was attempted.
20. As an operator, I want each terminal execution outcome logged, so that I can distinguish success, failure, cancellation, and timeout.
21. As an operator, I want unmatched submissions to mean “outcome unknown,” so that SQLcery never fabricates an outcome after interruption.
22. As an operator, I want Audit events correlated by execution identity, so that interleaved concurrent executions can be reconstructed reliably.
23. As an operator, I want Connection name, origin, and opaque identity on submissions, so that same-named project and global Connections are distinguishable.
24. As an operator, I want canonical project configuration origins recorded, so that external tooling can identify the project responsible for an event.
25. As an operator, I want query row counts and affected-row counts in completion metadata, so that outcomes are useful without storing returned data.
26. As a security-conscious user, I do not want result rows or cell values in Audit, so that query results are not duplicated into local logs.
27. As an operator debugging failures, I want useful driver error text retained, so that the Audit Log explains failures.
28. As an operator, I want pathological error text bounded and visibly marked when shortened, so that one driver error cannot threaten an unrotated fail-closed log.
29. As an operator, I want Audit writes durably synced, so that successful write acknowledgements survive ordinary process interruption.
30. As an operator, I want Audit logging mandatory and non-configurable, so that completeness guarantees do not depend on runtime settings.
31. As an operator, I want Audit retention unbounded by SQLcery, so that application-managed rotation never discards evidence.
32. As an operator, I want a failed submission append to prevent database execution, so that no Statement begins without durable audit evidence.
33. As an operator, I want a failed completion append retained for retry, so that transient filesystem failure need not permanently lose the outcome.
34. As a user, I want the next submission to flush a pending completion first, so that SQLcery cannot accumulate unaudited executions.
35. As a user, I want a persistent audit-failure indication while execution is blocked, so that the reason is visible and actionable.
36. As a user, I want quitting to remain possible during an Audit failure, so that SQLcery never traps me in the process.
37. As an operator, I accept that quitting with a pending completion leaves an unmatched submission, so that the log truthfully represents an unknown terminal outcome.
38. As a user, I want audit fsync and file locking performed outside the UI loop, so that persistence cannot freeze keyboard input or rendering.
39. As an operator running concurrent SQLcery processes, I want complete non-corrupted Audit events, so that cross-process writes remain consumable JSONL.
40. As a user, I want Slash Commands that only open UI or expand SQL excluded from both stores, so that persistence represents database execution rather than editor actions.
41. As a user of an expanded Slash Command, I want the resulting Statement recorded only when I submit it, so that reviewed but unexecuted templates are not audited.
42. As a user without a live Session or with incomplete SQL, I want no Audit events written, so that rejected client-side input is not represented as database execution.
43. As a developer using the pre-release build, I accept wiping the old persistence files, so that the new implementation does not carry ambiguous migration code.
44. As a user, I want deleting History storage to leave Audit untouched, so that recall management cannot destroy audit evidence.

Implementation Decisions

• Replace the combined History/Audit abstraction with separate History Store and Audit Log components. Neither component reads from or writes to the other's persistence path.
• Introduce one shared Connection Identity value derived during connection resolution and passed through Session creation and switching. Both stores consume this value so scoping cannot drift.
• For a named Connection, identity consists of the canonical configuration origin plus Connection name. A project-local definition wins when that name is present locally; global and project origins remain distinct. Canonical origins are absolute and resolve filesystem aliases where possible.
• For a direct Connection String, identity derives from the exact accepted string. Hash the domain-separated identity with SHA-256; never persist the raw Connection String. Use the same opaque lowercase hexadecimal identity in Audit metadata and the History filename.
• Extend layered Connection loading to retain per-Connection provenance rather than only the final merged map. Connection Frecency remains unchanged and name-based.
• Store History as an ordered JSON array of strings under the data directory's history collection, one file per opaque Connection Identity.
• On History update, take the per-identity inter-process lock, reload the latest file, remove an exact-text duplicate, append the submitted Statement, retain the newest 1,000 entries, write a temporary file in the same directory, and atomically replace the target.
• Keep the active Session's History as an in-memory snapshot plus local additions. Do not live-merge concurrent process writes into the visible snapshot; restart or Connection reopen reloads durable changes.
• A History persistence error is fail-open: update the in-memory snapshot, surface an error, and allow future Statement execution. A later update retries by rewriting the current merged bounded state.
• Audit is one unrotated JSONL stream. It is mandatory and has no disable switch.
• Assign every execution a unique opaque execution identity. Both event types carry that identity; event ordering alone is not used for correlation.
• A submitted event contains event type, execution identity, connection identity, connection name when present, connection origin, exact Statement text, and an RFC3339Nano UTC timestamp.
• A completed event contains event type, execution identity, RFC3339Nano UTC terminal timestamp, terminal outcome, and metadata-only result summary.
• Terminal outcomes are exactly success, failure, cancelled, and timed_out. Context cancellation and deadline expiration map deterministically to the last two outcomes; other adapter errors map to failure.
• Completion summaries contain query row count, affected-row count, generic success text, or driver error text. Never serialize Result Set rows, columns, or cell values.
• Bound only the human-readable error string to 4 KiB of valid UTF-8 including an explicit truncation marker. Never truncate the outcome or numeric metadata.
• A complete Statement committed to a live Session starts an execution worker. That worker acquires the Audit lock, appends the submitted event in one complete JSONL record, syncs it durably, releases the lock, and only then invokes the Adapter.
• Client-side rejected input, including incomplete SQL or submission without a live Session, emits no Audit event and never reaches the Adapter.
• After Adapter completion, cancellation, or timeout, the execution worker independently attempts the History update and appends and syncs the completed Audit event. The UI loop receives messages describing results and persistence failures; it performs no file I/O.
• If writing submitted fails, do not call the Adapter, keep the Statement available for retry, and expose the Audit error.
• If writing completed fails, keep exactly one pending completion event in interaction state, show a persistent audit-failure indicator, and block new execution. The next submit first retries that pending event and continues with the new Statement only after durable success.
• Do not block quitting when a completion is pending. Quitting may abandon the in-memory completion, leaving the durable submitted event unmatched and therefore explicitly unknown.
• History persistence remains independent of Audit completion persistence and is attempted even when the completion event fails.
• Serialize Audit appends and History read-modify-replace transactions with OS-released advisory inter-process locks through a small cross-platform locking dependency. A process crash must not leave stale ownership. Preserve Windows and Unix behavior.
• Keep current default file and directory permission behavior; permission hardening is not part of this feature.
• Provide no legacy migration or parser. Development users delete the existing audit.log, rotated backup, and old combined persistence before using the new stores.
• Remove Audit rotation and every code path that derives History from Audit records.
• Remove persistence of failed or non-expanding Slash Commands. A future Slash Command that directly executes SQL must participate in the same execution/audit protocol rather than bypassing it.
• Preserve dependency injection at the application boundary for fake Audit and History implementations. Favor cohesive store interfaces that express user-visible operations and failures rather than exposing filesystem mechanics to the Model.

Testing Decisions

• Good tests assert externally observable behavior: Adapter call ordering, emitted persistence records, visible Model state, execution blocking or continuation, and durable files. Avoid assertions about private helper calls, concrete lock-library internals, temporary filenames, or goroutine scheduling.
• Use the App Model as the highest behavioral seam. Inject fake Audit, History, Adapter, and clock/identity sources to prove that submitted succeeds before Adapter invocation; completed follows every terminal outcome; submitted failures prevent execution; completion failures create one blocked pending event; next submit retries before executing; quitting stays available; and History failures do not block execution.
• Extend existing execution lifecycle tests for success, ordinary failure, cancellation, timeout, and stale messages. Verify Audit work occurs through worker commands and does not run synchronously in the UI update loop.
• Extend existing Slash Command submission tests to prove UI-opening and editor-expanding commands write neither store, while the later submitted expanded Statement uses the normal protocol.
• Test History Store behavior against temporary directories: initial load, persistence across reopen, exact-text deduplication, latest-position ordering, whitespace distinction, 1,000-entry cap, malformed data behavior, atomic replacement failure, and recovery on a later write.
• Test Audit Log behavior against temporary directories: valid JSONL schemas, full Statement preservation, connection metadata, unique correlation, event ordering within an execution, durable append failures, no rotation, metadata-only summaries, 4 KiB UTF-8-safe error truncation, and explicit truncation marking.
• Exercise concurrency through independently opened store instances and, where required to validate OS lock release and cross-process exclusion, subprocess-backed integration tests. Assert no lost History entries, no malformed/interleaved Audit lines, and no stale lock after forced process exit.
• Extend Connection loading and resolution tests for global-only, project-only, same-name local override, same names across projects, canonical project origins, global origin, direct Connection String identity, stable hashing, and non-persistence of raw DSNs.
• Keep a Run-level wiring smoke test proving a resolved Connection Identity and independent Audit/History dependencies reach the Model during startup and Connection switching.
• Use existing fake Adapter, Model update-loop, temporary-directory persistence, layered-config, cancellation, and timeout tests as prior art.
• Run the repository-managed formatting, unit test, and lint tasks through mise.

Out of Scope

• A user-facing clear-History command or settings UI.
• Audit disablement, sampling, filtering, rotation, truncation, archival, or retention policy.
• Importing, parsing, or preserving development-format combined History/Audit files.
• Recording result rows, Result Set cell values, or full query outputs.
• Live synchronization of visible History across already-running SQLcery processes.
• Merging Connection Identities because they currently target the same database.
• Changing Connection Frecency identity or ordering behavior.
• Persisting Slash Commands that do not directly execute against the database.
• Blocking application exit to guarantee a pending completion event.
• File permission hardening beyond current defaults.
• A user-facing Audit viewer or recovery UI.

Further Notes

• ADR 0004 and the domain glossary are the source of truth for History, Audit Log, and Connection Identity terminology.
• The implementation intentionally makes Audit stronger than History: Audit is global, unbounded, durable, mandatory, and fail-closed; History is scoped, bounded, replaceable, and fail-open.
• Durability cannot prove the database's terminal outcome after abrupt process death. The paired event protocol preserves the truthful distinction: a durable submission without a durable completion has an unknown outcome.
• The old persistence is development-only and may be deleted manually; implementation complexity for backward compatibility is explicitly rejected.