Rust Sqlite CLI Architecture

Rust SQLite CLI Architecture

Use this skill when designing or reviewing a Rust command-line application that stores durable local state in SQLite. The output is an implementation-ready architecture plan, not a pile of generic database advice. It should identify where data lives, how schema changes land, which commands own transactions, how tests prove safety, and how users recover when something goes wrong.

Critical Constraints

• Treat the database as user data, not an internal cache, unless the product explicitly says it can be deleted without loss.
• Pick one canonical database location and make overrides explicit through a flag, environment variable, or config value.
• Never run destructive schema changes without a tested backup and rollback path.
• Every mutating command needs an explicit transaction boundary.
• Migrations are source-controlled, ordered, repeatable, and tested from older fixtures.
• User-facing errors must explain the next action without exposing raw SQL as the main message.
• Recovery commands must exist before the tool is used for important data.

When SQLite Fits

SQLite is a strong fit when the CLI needs local durable state, offline operation, fast startup, simple deployment, and one-machine ownership. Examples include task stores, local indexes, audit logs, sync queues, caches that must survive restart, and portable project databases.

Choose another storage design when the product requires heavy multi-writer concurrency across machines, central policy enforcement, server-side audit, or very large binary payloads. A CLI can still use SQLite as a local queue or cache in those systems, but the architecture should name the server of record.

Inputs To Collect

Before designing modules or tables, gather these facts:

• Primary commands and which ones read, mutate, import, export, sync, or delete.
• Data ownership: per user, per workspace, per repository, or per explicit file.
• Portability needs: copyable database file, project-relative database, or platform data directory.
• Durability expectations: cache, rebuildable index, or authoritative user data.
• Concurrency expectations: one process, shell pipelines, background daemon, scheduled runs, or multiple terminals.
• Upgrade expectations: how old an installed database might be in the field.
• Privacy and backup expectations for sensitive or irreplaceable data.

Architecture Procedure

1. Define the storage contract. State the default path, override mechanism, file permissions, and whether the database is authoritative. Do not hide important data under an ambiguous temp or cache path.

2. Draw the command-to-data map. For each command, list the tables it reads and writes, whether it needs a transaction, and what invariant must hold after it exits.

3. Choose module boundaries. Keep CLI parsing, domain decisions, database access, migrations, and output rendering separate enough that transaction tests can call the domain layer without scraping terminal text.

4. Design the schema for operations. Model stable entities as tables with primary keys, foreign keys, and indexes that match command queries. Use JSON columns only for opaque payloads or bounded extension fields, not for data that commands must filter or join.

5. Define connection setup. Open one connection per command unless the product has a daemon mode. Apply required connection settings consistently, including foreign-key enforcement, busy timeout, and any journal-mode decision.

6. Write the migration policy. Decide whether normal command startup applies pending migrations or whether users run an explicit upgrade command. For authoritative data, prefer a preflight check, backup, migration, integrity check, and clear failure path.

7. Specify transaction boundaries. Every mutating command begins a transaction after validation and commits only after all database invariants are satisfied. Render output after commit so a successful message cannot precede a rolled-back write.

8. Plan operational commands. Include commands or documented workflows for doctor, backup, restore, export, import, schema-version, and optional compaction.

9. Build the test matrix. Cover fresh database creation, migration from prior fixtures, transaction rollback, command integration, concurrent-process behavior, backup/restore, import validation, and corruption diagnosis.

Recommended File Shape

Adapt names to the repository, but preserve the separation of responsibilities:

src/
  main.rs              # process entry point and error-to-exit mapping
  cli.rs               # argument parsing and command enum
  commands/            # command handlers, one file per workflow
  domain/              # validation and state-transition rules
  db/
    mod.rs             # connection factory and common database errors
    migrations/        # ordered migration files or embedded migration sources
    schema.rs          # schema-version checks and migration runner
    repo_*.rs          # small query modules grouped by aggregate or workflow
tests/
  cli/                 # black-box command tests
  fixtures/db-v*.sqlite

The key rule is direction: commands may call domain and database modules; the database layer should not know about terminal formatting, color, progress bars, or command-line flags.

Data Location Rules

• Per-user tools should default to a platform data directory and print the path in diagnostic commands.
• Per-project tools should prefer an explicit project metadata directory or a user-selected path checked into the project policy.
• Support --database <path> or an equivalent override for tests, recovery, and advanced operation.
• Refuse to create parent directories with broad permissions for sensitive state.
• Document sidecar files if the journal mode creates them, because backup and cleanup procedures must include them or checkpoint first.

Schema Rules

• Enable foreign-key enforcement for every connection.
• Use stable integer or text primary keys; do not rely on row order.
• Store timestamps in one format and name the clock source used by commands.
• Add indexes for the queries on the command map, not for speculative future reports.
• Keep schema metadata in the database, including current migration version and application identity.
• Keep destructive changes explicit: copy-table migrations are safer than in-place mutation when data matters.
• Make uniqueness constraints carry product meaning, then translate violations into user-facing conflict messages.

Migration Policy

A migration plan must answer:

• How pending migrations are detected.
• Whether a backup is created before migration.
• How integrity is checked before and after migration.
• Which migrations are reversible, and which require restore from backup.
• How the tool behaves when the executable is older than the database schema.
• How fixture databases are generated and kept for compatibility tests.

For important user data, the safe default is:

1. Open the database.
2. Check application identity and schema version.
3. Run an integrity check.
4. Create or require a backup.
5. Apply pending migrations inside the narrowest safe transaction scope.
6. Run post-migration integrity and invariant checks.
7. Report the new schema version and backup location.

Transaction Policy

Use one explicit transaction per mutating command. Start it after input validation and connection setup. Commit after database invariants pass. Roll back on any error. Commands that perform read-modify-write decisions should acquire the write intent early enough to avoid stale decisions under concurrent processes.

External side effects need special care:

• If the command writes files and the database, define which side is authoritative and how cleanup works after failure.
• If the command sends network requests, prefer an outbox table or idempotent operation key so retry does not duplicate user-visible effects.
• If output streams a report, collect database state first, commit if needed, then render.

Testing Plan

Build tests around behavior, not driver internals:

• Fresh-start test: no database exists, the first read and first write behave as documented.
• Migration fixture test: every supported older fixture opens, migrates, and preserves expected rows.
• Transaction rollback test: inject a failure after partial work and verify no partial state remains.
• Command integration test: run the compiled binary against a temp database and assert output plus database state.
• Concurrency test: run two processes against the same database for commands that users might execute in parallel.
• Backup/restore test: create data, back it up, restore it elsewhere, and run doctor.
• Import test: malformed input fails before mutation; valid input is atomic.
• Destructive command test: dry-run output matches the rows affected by the real command.

Prefer temp directories and per-test database paths. Tests should not touch a developer's real data directory.

Operational Safety

Add a doctor path that checks database path, application identity, schema version, integrity, foreign-key consistency, journal leftovers, and writability. The command should return a nonzero exit code on unsafe state and include the next command a user can run.

Add backup and export behavior before destructive workflows. A backup preserves the native database for restore; an export gives users an inspectable format for portability. They solve different problems and should not be treated as interchangeable.

For delete, reset, prune, and migration commands:

• Provide dry-run output with row counts or item identifiers.
• Require an explicit confirmation flag for non-interactive use.
• Create or require a backup when data is not rebuildable.
• Log enough context for support without leaking secrets.
• Make interruption behavior clear and tested.

Design Review Checklist

• The architecture names the database location and override mechanism.
• Each command has a declared read/write set and transaction policy.
• Migrations are ordered, source-controlled, and tested from fixtures.
• The executable handles newer database schemas safely.
• Backup, restore, export, and doctor paths are present for important data.
• Tests use isolated database paths and prove rollback behavior.
• Destructive operations have dry-run and confirmation behavior.
• Error messages map database failures to user actions.
• The final design distinguishes rebuildable caches from authoritative data.

Output Specification

Return a concise architecture packet with these sections:

1. Storage contract.
2. Command-to-data map.
3. Module layout.
4. Schema and migration policy.
5. Transaction policy.
6. Testing plan.
7. Operational safety plan.
8. Open risks and decisions.

If implementing code, include only the smallest scaffold needed to prove the architecture: connection setup, migration runner, one read command, one mutating command, and tests for migration plus rollback.

Quality Rubric

The design passes when a reviewer can answer:

• Where is user data stored, and how can a test or operator override it?
• What happens if a command fails halfway through a write?
• What happens when an old database meets a new executable?
• What happens when a new database meets an old executable?
• How does a user back up, inspect, restore, and diagnose the database?
• Which tests prove those answers instead of assuming them?