V3 architecture research: choose the durable state model and storage engine

[Loufe]

Parent: #33. Prompted by the first-class History work in #79 and the live/replay mismatch in #81.

Status

This is an open architecture investigation, not a decision and not an implementation issue.

The goal is to find the best durable-state architecture for CRFty's actual domain and expected lifetime, even if that means superseding accepted ADRs or replacing substantial pre-release work. Do not optimize for preserving the current journal merely because it already exists, and do not treat SQLite/Turso as selected merely because they currently look promising.

When this investigation concludes, record the decision in one or more ADRs and update the affected issues. Until then, ADR-004 and ADR-009 remain the accepted description of the implemented system.

Why this reopened

ADR-004 chose an append-only NDJSON journal because:

• the durable state fit in memory;
• the reducer already owned the single transaction boundary;
• History and Statistics were understood as projections of operational file records, verdicts, runs, and outputs;
• inspectable/redactable plaintext was considered valuable;
• avoiding database schema migration machinery was considered a maintenance advantage.

#79 exposed a false premise. Historical statistical evidence is not current operational state:

• it must survive deletion, movement, replacement, and loss of the source;
• it must not be reached through a current standing verdict;
• it grows independently and needs filtered/paginated queries;
• terminal completion and the corresponding observation must land atomically;
• privacy scrub must clear the readable History path while preserving its hash and all statistical evidence.

As the journal has been fleshed out, it has also acquired framing, exact schema-version rejection, semantic replay validation, torn-tail recovery, corruption acknowledgement, compaction policy, Windows replacement retries, and a large crash-injection suite. That is real storage-engine responsibility, not merely serialization.

The current format has JOURNAL_SCHEMA_VERSION = 16 and refuses every other version before decoding its payload. That detects incompatibility but does not preserve user state across a released schema change. Avoiding explicit migrations has not eliminated schema evolution; it has left the update path undefined.

Problem statement

Choose how V3 should represent, transact, recover, query, evolve, inspect, and scrub:

1. Operational durable state
   • queue items and ordering;
   • path bindings and current file observations;
   • analyses and standing operational decisions;
   • conversion runs and phase spans;
   • the output transaction ledger needed for crash recovery;
   • durable ID allocation.
2. Historical statistical observations
   • append-only operation facts used by History, Statistics, and Estimation;
   • independent of current files, verdicts, runs, and outputs;
   • native and translated observations sharing one model;
   • potentially large and request-driven rather than mirrored to TypeScript.
3. Settings
   • small and human-editable today;
   • a different lifecycle from conversion/recovery state;
   • still subject to evolution across released application versions.
4. Ephemeral backend state
   • active process handles, cancellation, telemetry, tool availability, session coordination, analysis generations, channels, and subscribers;
   • not durable merely because an embedded database exists.

The selected design must minimize lifetime correctness and maintenance burden, not only initial implementation size.

Invariants the solution must enforce

Transaction and publication

• One engine-owned writer remains the authority.
• A command's durable changes are all-or-nothing.
• A reportable terminal completion and its HistoryRecord are one atomic transaction.
• Failed/stopped/non-reportable outcomes create no statistical observation.
• Effects, replies, and UI-visible state never outrun durable commit.
• Restart restores every committed transition and no uncommitted transition.
• Reconnect obtains current truth without replaying frontend-owned business logic (V3 post-release: replace durable deltas and the TS fold with snapshot push #78).

Model boundaries

• History has no referential-integrity dependency on current file records, paths, verdicts, outputs, queue items, or runs.
• Deleting current operational state cannot cascade into History.
• Runs remain execution/recovery evidence; History remains reporting/estimation evidence.
• Derived aggregates are not authoritative persisted facts unless measurement proves materialization necessary.
• Business transition rules remain in pure Rust domain code, not duplicated in SQL triggers, stored procedures, frontend folds, and replay validators.

Evolution

• Released user data survives normal forward application updates, including users who skip releases.
• The current application code operates on one current schema; old application/domain APIs do not remain live as compatibility shims.
• A database/file produced by a newer application is refused clearly rather than misread.
• Pre-3.0 development migrations may be squashed; released migrations or equivalent transforms cannot be silently discarded.
• The project must resolve the conflict between preserving persisted user data and the current blanket “no migration helpers” rule. A likely policy boundary is: zero compatibility for source/runtime APIs, forward migration for released persisted data.
• V2 interpretation stays exclusively in the disposable Python translator. V2-to-V3 translation is not a runtime schema migration.

Privacy and path fidelity

• Privacy-on recording persists no readable History path.
• Statistical facts are append-only, but the source locator has one explicit permitted mutation: irreversible raw-path → anonymous identifier redaction.
• Scrub clears the optional readable path from current History records. It makes no physical-erasure promise about storage remnants.
• Scrub does not alter History IDs, measurements, outcomes, Statistics, or Estimation.
• Operational recovery paths remain outside “Scrub History.”
• Native non-Unicode paths round-trip without lossy display conversion.
• Cross-platform database copying has defined behavior for platform-native path encodings.

Operability

• A second process cannot become a competing writer.
• Corruption, an unsupported future schema, and an abnormal prior shutdown remain distinguishable.
• The recovery/degraded UX is explicitly designed; replacing the journal must not quietly delete the existing corruption contract.
• The durable format is inspectable with ordinary maintained tools or has a first-class export path. “Plaintext” alone is not sufficient if the coherent current state is only obtainable by replay.
• No server, account, network connection, replication service, or online backup is required.

Serious candidate families

The investigation should compare these as architectures, not just crates.

A. Current typed NDJSON journal plus snapshot compaction

Strengths:

• Naturally matches reducer-emitted transitions.
• One append/fsync before publication is easy to state.
• Text is directly visible.
• Current implementation and extensive failure tests already exist.

Questions/costs:

• Every historical delta and snapshot shape becomes a durable compatibility surface.
• Replay validation currently duplicates live transition validation (V3: reducer and journal replay validate different transition relations #81).
• Compaction is a custom storage protocol; History scrub is an ordinary logical record transformation.
• Querying growing History requires a new index/store or full replay/load.
• Human-readable individual events are not the same as a human-readable current model.
• A released schema-update mechanism remains undefined.

Research whether a deliberately stable event schema with upcasters/copy-and-transform can make this simpler than current-state migration. Do not assume event sourcing is justified merely because the reducer emits deltas.

B. Canonical whole-state snapshot file

Examples: one atomic JSON/CBOR snapshot rewritten after each durable batch, possibly now viable once History leaves the operational snapshot.

Strengths:

• Very small mechanism.
• One current representation rather than an event lifetime.
• Easy offline inspection if JSON.

Questions/costs:

• Rewriting and fsyncing the whole file.
• Serialized Rust/domain shape still requires schema evolution.
• A separate History store breaks atomic terminal + History commit unless a reconciliation/outbox protocol is added.
• Putting History back into the snapshot loses pagination and bounded startup.

C. Multiple dedicated plaintext files

Examples: operational snapshot, append-only History, settings, output-recovery ledger.

Strengths:

• Clear human-facing separation.
• Each artifact may have a format optimized for its lifecycle.

Questions/costs:

• Cross-file atomicity is not provided by the filesystem.
• Terminal completion, output settlement, and History require a transaction manifest, idempotent reconciliation, or a coordinator journal.
• The design risks rebuilding a database transaction manager from files.
• Crash recovery spans several artifacts; History scrub must still affect only readable History path fields.

D. SQLite-compatible relational current-state database

Possible engines/bindings include bundled SQLite through rusqlite and the native-Rust Turso Database.

Strengths:

• Atomic multi-model transactions.
• Defined schema, constraints, indexes, pagination, and aggregation.
• Incremental updates and on-demand reads.
• Standard forward schema migrations.
• Widely available inspection tools and a stable, documented application-file format.
• Direct support for V3: replace parked imports with first-class statistical History observations #79/V3: serve first-class History by request/response #77/V3 Analysis: derive applicable levels, size/time predictions, and confidence #57 query shapes.

Questions/costs:

• Rust/domain-to-row mapping code.
• Schema migrations become an explicit permanent responsibility after release.
• Need to decide how much to normalize and where nullable variant columns or child tables are clearer.
• SQLite engine/binding selection has packaging, sync/async, maturity, and maintenance tradeoffs.
• WAL lifecycle, checkpointing, and Windows file behavior must be verified against the exact chosen engine.

E. Hybrid SQLite-compatible database

Examples:

• relational history_records plus one serialized operational snapshot row;
• relational aggregate-root tables with serialized leaf payloads;
• current-state tables plus an internal durable change log/outbox.

Strengths:

• Atomicity and History queries without immediately mapping every operational value to columns.
• May preserve the current pure reducer with less initial persistence code.

Questions/costs:

• Opaque JSON/CBOR payloads can merely hide the same serde/schema migration cliff.
• A whole-state row gives up incremental update and constraint benefits.
• A change log can accidentally retain the journal's replay/versioning burden.
• Need a principled boundary: real columns for identity/lifecycle/query/integrity, serialized leaves only where their internal structure is truly storage-opaque.

F. Embedded transactional key/value store

Candidates worth evaluating include pure-Rust redb and LMDB through heed.

Strengths:

• Embedded ACID transactions and single-writer semantics.
• Natural keyed aggregate storage.
• redb is pure Rust and crash-safe by default.
• Could fit a reducer returning complete aggregate replacements.

Questions/costs:

• No relational query language.
• CRFty owns secondary indexes, joins, estimator cohorts, pagination order, and integrity constraints.
• Values still require a durable encoding and migration strategy.
• Inspectability and cross-language tooling are weaker than SQLite.
• Engine file-format/API evolution and repair behavior must be assessed, not inferred from “ACID.”

G. Event-sourced database with rebuildable projections

Persist stable domain events as the enduring record and rebuild current/read tables.

Strengths:

• Audit/replay and rebuildable projections.
• Read schemas can change without rewriting the event record.

Questions/costs:

• Event schemas themselves evolve and must remain interpretable or be transformed.
• Snapshots, projection versioning, duplicate validation paths, and replay tooling remain.
• CRFty needs immediate consistency and has one local writer; it does not need distributed consumers or historical reconstruction of every queue edit.
• History observations are naturally append-only, but that does not imply the entire operational model should be event sourced.

H. Analytical, client/server, or OS-specific databases

Include as control candidates and reject only with explicit reasons:

• DuckDB is embedded SQL but optimized for bulk analytical work rather than many small operational transactions.
• PostgreSQL/MySQL add deployment and process administration with no CRFty requirement that justifies them.
• OS-native stores lose Windows/Linux portability.
• Frontend IndexedDB cannot own output-recovery state when the webview disconnects or restarts.

State ownership alternatives

Storage engine and runtime ownership are separate decisions.

Long-lived in-memory durable mirror

The current driver owns a full AppState, mutates it through the reducer, persists changes, then publishes.

• Minimal domain refactor.
• Fast reads.
• Requires a structural guarantee that the mirror and database never diverge.
• Persistence failure needs candidate-state rollback or fatal restart.

Database-authoritative, transaction-local domain objects

The driver owns one connection. For each command/batch:

begin transaction
  -> load required typed records
  -> pure transition
  -> persist complete resulting records
commit
  -> publish current operational snapshot/reply/effects

• No long-lived duplicate durable cache.
• The embedded engine's page cache supplies the actual cache.
• Closely resembles a Unit of Work.
• Requires splitting the monolithic reducer or initially loading a broader transaction working set.

Database-authoritative with a measured read cache

Start transaction-local and add caches only for demonstrated hot paths. Cache invalidation remains structurally simple if the driver is the only writer, but it should not be introduced without a measurement.

The investigation must also state why backend ephemeral process/session state remains in memory even if durable state becomes database-authoritative.

Engine-specific questions

Bundled SQLite / rusqlite

• Fit with the current synchronous dedicated driver.
• Bundled C SQLite packaging on Windows and Linux.
• Transaction and migration APIs.
• Integrity checks and WAL/rollback-journal behavior.
• Dependency/API stability and binary-size impact.

Turso Database

• Use local embedded mode only; cloud/network features are irrelevant.
• Pin and test the exact Rust crate rather than assuming SQLite checklist equivalence.
• Native async integration cost inside the otherwise synchronous driver.
• WAL-only behavior and WAL growth/checkpoint policy (wal_autocheckpoint is currently absent).
• Same-connection statement lifecycle: every write statement must be fully consumed/dropped before the next write.
• Transaction completion, explicit WAL checkpointing, and SQLite-file interoperability through the Rust API.
• Transactional DDL/migration behavior.
• Windows file close/checkpoint/replacement behavior.
• The compatibility guarantee allows returning to SQLite format, but the Rust API and operational behavior are still separate maintenance considerations.

redb / LMDB-class stores

• Exact durability mode and fsync contract.
• Typed value encoding and evolution.
• Range/pagination/index implementation burden.
• Compaction behavior and logical History scrub updates.
• Non-Unicode paths and inspection/export tooling.
• Recent engine migration/correctness history and the project's tolerance for owning more query logic.

Required experiments

Use disposable spikes, not production abstractions.

1. Same-schema engine comparison

Implement a minimal representative schema/workload in bundled SQLite and Turso:

• open and migrate;
• add/reserve/prepare/run/settle;
• atomically settle a reportable run and insert History;
• delete a queue item while retaining run and History;
• paginate/filter History;
• compute representative estimator cohorts;
• restart and recover.

Record code size, conceptual complexity, packaging friction, and failure behavior. Do not build a generic storage trait merely to make the spike look uniform.

2. Schema mapping walk-through

Draft enough schema to execute these cases:

• replace-mode crash at every output-ledger boundary;
• current file changes at a known path;
• repeated conversion of the same source;
• queue deletion and retry lineage;
• identical/conflicting re-import of one HistoryId;
• privacy-on recording and retroactive scrub;
• statistics/estimation after the source disappears;
• durable monotonic ID allocation.

Use this to compare fully relational, aggregate-row hybrid, and serialized-snapshot hybrid designs.

3. Evolution rehearsal

Create artificial released schemas 1, 2, and 3:

• add a nullable fact;
• rename or split a field;
• add an enum outcome;
• transform existing values;
• skip directly from v1 to v3;
• kill the process during migration;
• open a future-version database;
• preserve meaningful data, not only table shape.

Perform the equivalent exercise for any serialized/KV candidate. “No migration framework” is not a pass unless the candidate demonstrates a safer update story.

4. Durability/fault tests

At controlled kill points:

• before commit;
• during commit/fsync;
• after commit but before publication;
• during startup recovery;
• during schema migration;
• during checkpoint/compaction;
• during the privacy scrub transaction.

Verify the user observes either the old state or complete new state, never a hybrid.

5. Privacy contract

Persist History records with readable paths, scrub, restart, and verify that every current History record has no readable path while its hash/anonymous identifier, ID, measurements, outcome, Statistics, and Estimation results are unchanged. Verify that a second scrub is a no-op and operational paths intentionally outside History remain usable.

This is the Python feature contract: a logical record transformation. Compaction, vacuuming, database replacement, forensic byte searching, and physical-storage erasure are not acceptance criteria.

6. Native path proof

Round-trip:

• ordinary Unicode paths;
• Windows unpaired surrogate units;
• Unix non-UTF-8 bytes;
• database copied to the other OS;
• History export/import and anonymous records.

Define a stable application encoding rather than depending accidentally on Rust's current OsStr internal representation.

7. Realistic scale

Generate representative operational state plus 10,000 and 100,000 History observations. Measure:

• startup/current snapshot;
• terminal commit;
• History page/filter;
• Statistics;
• estimator sample selection;
• file/WAL growth;
• compaction/vacuum;
• memory.

Indexes, caching, materialized aggregates, and extra read connections require evidence from these measurements.

8. Human inspection and recovery

For each finalist, demonstrate:

• inspect schema/current queue/runs/History with maintained off-the-shelf tools;
• export History to a durable documented interchange format;
• distinguish corruption from unsupported schema;
• define the application's degraded/read-only/recovery behavior;
• explain which sidecar files are part of a live database.

Preliminary evidence, not conclusions

• SQLite explicitly recommends a defined relational schema as an application file format, contrasting it with custom and pile-of-files formats. It calls out atomic transactions, incremental updates, extensibility, queries, and schema-as-documentation:
  https://www.sqlite.org/appfileformat.html
• SQLite documents WAL/journal files as part of database state during crash recovery:
  https://www.sqlite.org/fileformat.html
  https://www.sqlite.org/wal.html
• Turso's current compatibility contract and gaps:
  https://github.com/tursodatabase/turso/blob/main/COMPAT.md
• Turso's native Rust API:
  https://docs.rs/turso/latest/turso/
• rusqlite recommends its bundled SQLite build for applications that control their own database:
  https://github.com/rusqlite/rusqlite
• redb provides single-writer ACID transactions, immediate durability, crash recovery, integrity checking, and compaction, but remains a key/value store:
  https://docs.rs/redb/latest/redb/
• DuckDB explicitly says many small transactions are not its primary design goal:
  https://duckdb.org/docs/stable/connect/concurrency
• Microsoft recommends traditional CRUD when event-sourcing complexity is not justified and calls out event schema/projection complexity:
  https://learn.microsoft.com/en-us/azure/architecture/patterns/event-sourcing
• Unit of Work describes the transaction-local object/write coordination being considered here:
  https://martinfowler.com/eaaCatalog/unitOfWork.html
• Chromium retains historical SQLite schemas and migration tests for its History database:
  https://chromium.googlesource.com/chromium/src/+/refs/heads/main/components/test/data/history/
• Firefox Places cites reliability, flexibility, and maintainability in its move to a SQLite-backed History/Bookmarks backend:
  https://firefox-source-docs.mozilla.org/browser/places/index.html
• Fossil is useful hybrid prior art: immutable enduring artifacts with rebuildable relational projections:
  https://fossil-scm.org/home/doc/trunk/www/fossil-is-not-relational.md

These sources strongly justify SQLite as the baseline to beat. They do not by themselves choose fully relational versus hybrid mapping, the Rust engine/binding, or long-lived versus transaction-local state.

Settings must not be forgotten

Keeping config.json separate may still be correct because it is small, human-editable, and has no required atomic relationship with a conversion terminal transition. But it does not escape evolution:

• adding, renaming, or removing settings affects old installed files;
• the current strict deserializer can quarantine/reset an older shape;
• additive defaults, an explicit config version/transform, or moving settings into the database each have different maintenance and usability costs.

The final architecture must state the settings update policy rather than treating database migration as the only compatibility problem.

Affected issues and ADRs

• V3: replace parked imports with first-class statistical History observations #79: first-class History model and atomic native observation creation.
• V3: reducer and journal replay validate different transition relations #81: one live/persisted transition relation; may change substantially if replay disappears.
• V3: serve first-class History by request/response #77: request-driven/paginated History.
• V3 post-release: replace durable deltas and the TS fold with snapshot push #78: snapshot push and deletion of the TypeScript durable fold.
• V3 Analysis: derive applicable levels, size/time predictions, and confidence #57: estimation directly over historical observations.
• V3 privacy: history scrub as a durable transform, and wire anonymize_history #51: logical removal of readable paths from History while retaining hashes and statistical evidence.
• V3 shell: queue-add orchestration, item-id allocation, and settings sourcing belong behind the engine command surface #84: durable ID allocation belongs behind the engine boundary.
• ADR-002: likely retained in principle (one driver/pure domain transitions), but state ownership may change.
• ADR-004: append-only journal decision; candidate for supersession.
• ADR-009: journal compaction; candidate for supersession.
• ADR-011: corruption acknowledgement; its user-facing requirements must be retained or consciously replaced.
• ADR-012/015: History projection/import assumptions already being superseded by V3: replace parked imports with first-class statistical History observations #79.
• ADR-017: native path persistence must be resolved by the selected durable format.

Decision outputs

Before closing this investigation:

• Publish the candidate comparison and experimental results.
• Select the durable architecture, not merely a crate.
• Select or deliberately defer the storage engine/binding.
• Define current-state versus History table/aggregate boundaries.
• Define backend state ownership and command transaction flow.
• Define the released schema/config evolution policy.
• Define privacy scrub, path encoding, corruption, and recovery behavior.
• Estimate removed current-journal mechanisms against added persistence/migration mechanisms by actual code, not intuition.
• Write the replacement ADR(s), explicitly superseding affected accepted ADRs.
• Update V3: replace parked imports with first-class statistical History observations #79, V3: reducer and journal replay validate different transition relations #81, V3 privacy: history scrub as a durable transform, and wire anonymize_history #51, V3 Analysis: derive applicable levels, size/time predictions, and confidence #57, V3: serve first-class History by request/response #77, V3 post-release: replace durable deltas and the TS fold with snapshot push #78, and V3 shell: queue-add orchestration, item-id allocation, and settings sourcing belong behind the engine command surface #84 where the decision changes their acceptance criteria or sequencing.

Closing this issue means the architecture decision is made and documented. It does not mean the resulting persistence rewrite is implemented.

[Loufe]

Correction after checking the Python implementation (scrub_history_paths): earlier revisions of this issue incorrectly expanded Scrub History into physical byte erasure across journals, WALs, freelists, snapshots, and replacement files. That was not the existing product contract. The issue body now defines the actual behavior: clear the optional readable History path while retaining the one-way hash and every technical/statistical field. secure_delete, compaction/vacuum for privacy, database rebuilding, and forensic byte-search tests are not storage-engine requirements.

[Loufe]

Turso-direction architecture review

I completed the model/transaction review under the assumption that Turso is the leading engine, without treating the engine choice as an excuse to preserve the journal-shaped architecture.

The detailed review is now in docs/TURSO_ARCHITECTURE_REVIEW.md on the V3 worktree. Its recommendation is:

• one local SQLite-format Turso database in ordinary WAL mode;
• one connection owned by the existing driver thread, driven by a current-thread Tokio runtime;
• BEGIN IMMEDIATE, synchronous=FULL, and commit-before-publication;
• relational operational state, History, and settings—no durable event log and no serialized whole-DurableState row;
• database-authoritative transaction-local domain objects, with no long-lived Rust durable mirror;
• pure Rust transition rules, with runtime changes/effects/replies/UI snapshots applied only after commit;
• first-class history_records with no operational foreign keys, deterministic native IDs, direct import idempotence, nullable readable paths, retained anonymous IDs, and request-driven queries;
• full post-commit operational snapshots to the frontend; History publishes revision/count and is queried by page;
• tagged BLOB path storage (unix_bytes / windows_utf16le) so native media paths round-trip without lossy UTF-8 conversion;
• settings moved into the same database, with privacy-conservative logger bootstrap;
• direct cutover: no dual write, journal importer, generic storage trait, ORM, or speculative cache.

The review also recommends removing some current duplication instead of mapping it byte-for-byte:

• a terminal outcome belongs to the run; a queue row points at the run rather than carrying another copy;
• a standing verdict identifies the current decisive native run rather than carrying copied statistical summaries;
• History is the sole reporting/estimation evidence and is never reached through a verdict/run/output chain.

Turso result

The required SQL surface exists in Turso 0.7.1: local databases, immediate transactions, explicit commit/rollback, tables/indexes/checks/foreign keys, WAL, synchronous=FULL, schema/application versions, checkpoints, integrity checks, and structured corruption/not-a-database errors.

The relevant risk is engine maturity and exact integration behavior, not a missing CRFty CRUD feature. The review deliberately excludes cloud/network features, MVCC, CDC, generated columns, custom types, in-place vacuum, and online backup concerns.

One concrete adoption concern emerged: Builder::new_local currently accepts the database location as &str, not Path. Stored media paths are unaffected because CRFty can encode them itself, but a non-Unicode application-data directory must be explicitly tested/resolved. Windows packaging, statement lifecycle, kill-during-commit truth, transactional DDL, WAL close/checkpoint behavior, and clean SQLite interoperability are the other bounded gates.

Next architecture step

Keep this issue open. Before rewriting the ADRs or beginning the persistence cutover, make one disposable spike against the pinned Turso crate that proves:

1. packaged Linux/Windows open and database-path behavior;
2. atomic terminal-run + History insertion;
3. before/during/after-commit kill behavior and restart truth;
4. rollback-safe schema/data migrations and future-schema refusal;
5. WAL growth/checkpoint/clean SQLite inspection;
6. 100,000-row History pagination and estimator cohorts;
7. native path codec round trips;
8. error classification for corruption, non-database input, full/read-only, busy, constraint, and I/O failures.

If that proof passes, the ADR should select the architecture above and Turso. If Turso fails the local durability, packaging, or database-location gates, bundled SQLite through rusqlite can implement the same schema/ownership architecture without reopening the domain decision.

I also corrected #79's remaining stale journal language: Scrub History is now consistently defined as the idempotent logical removal of the optional readable path, with no compaction/vacuum/physical-erasure requirement, and its sequencing now avoids adding first-class History to the journal immediately before deleting that storage path.

[Loufe]

The architecture review is now committed and pushed on the V3 branch: https://github.com/Loufe/AB-AV1-GUI/blob/rewrite/docs/TURSO_ARCHITECTURE_REVIEW.md (commit 7cd6d0c).

[Loufe]

Representative History schema/export workload is now specified in #90. The #89 mapping spike should cover the observation fact plus History-owned system/execution profiles, runtime/phase/search child rows, and versioned content-feature payloads, as well as streaming pathless research export. This remains storage-engine independent.

[Loufe]

History-schema research remains open

#90 has been rewritten to record the current evidence without treating either the original seven logical headings or the later two-table sketch as an accepted physical schema.

The important mapping consequence for this issue is:

• the relational/storage spike should compare the actual candidates rather than assume one table per logical concept;
• the strongest current core is an environment/profile shape, an observation fact, and genuinely repeated CRF-search/sample attempts;
• phase measurements and a reusable execution profile remain conditional on demonstrated query/cardinality/integrity value;
• runtime facts and content summaries currently have no independently justified identity;
• the public/export contract remains independent of internal table layout;
• there is one canonical statistical bundle with an optional local-source attachment, not separate personal and research statistical schemas.

The Turso-direction spike should therefore instantiate at least the two-, three-, and four-table candidates in #90 with representative search/encode observations, pagination/cohort queries, export, and a schema evolution. This keeps #89 focused on measurable maintenance/query/transaction costs rather than choosing normalization by intuition.

Relevant prior art cited in #90 includes OpenTelemetry's explicit separation of a logical Resource model from its physical encoding, ASV's reusable machine metadata plus per-machine result records, Phoronix/OpenBenchmarking's hardware/software context alongside results, and published content-aware encoding-time work:

• https://opentelemetry.io/docs/specs/otel/resource/data-model/
• https://asv.readthedocs.io/en/stable/using.html#machine-information
• https://github.com/phoronix-test-suite/phoronix-test-suite
• https://arxiv.org/abs/2401.16067

No storage engine, table count, or schema has been selected by this update.