rust-sqlite-cli-architecture

What this skill does

# 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:

```text
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 o