Skip to content
FreehandFreehand

SQLite storage

Use this guide to change a schema or query and verify recovery with a real SQLite database. Freehand stores non-secret settings in freehand.db; domain owners continue to validate settings and coordinate saves.

Ownership and tools

Section titled “Ownership and tools”
Concern Owner
Defaults, compatibility rules, validation internal/config
Save coordination, native rollback, captured request profiles internal/settings
Database, backups, typed adapters, credential-reference lifecycle internal/storage
Schema and version history Embedded goose migrations in internal/storage/schema/
Application SQL internal/storage/queries; sqlc output in internal/storage/dbgen
Actual API keys Windows Credential Manager or macOS Keychain

Runtime dependency versions live in go.mod; the generation command pins sqlc in build/scripts/storage/main.go. Respect the driver’s libc version relationship when upgrading. SQLite adds no SQLite-specific CGO dependency; the native audio and Wails build requirements still apply.

  • Directoryinternal/storage/
    • Directoryschema/ embedded goose migrations, beginning with 00001_initial.sql
      • …
    • Directoryqueries/ parameterized application SQL
      • …
    • Directorydbgen/ committed sqlc output; never edit by hand
      • …
    • store.go infrastructure SQL boundary
    • recovery.go infrastructure SQL boundary
Schema and settings ownership

00001_initial.sql creates the STRICT settings, connection, vocabulary, remembered-model, and credential-reference schema. Foreign keys and explicit deletion rules protect related rows. Saved connections use stable server records with explicit capability memberships; remembered model preferences share the settings transaction. Go validates complete domain settings before writing and after reading. Generated rows and database handles never cross into Wails or domain services. Disposable window placement stays in window-state.json, independent of settings recovery.

Change the schema or a query

Section titled “Change the schema or a query”

  1. Add the next five-digit goose SQL migration with a version greater than every migration on the target branch. Never edit, delete, or fill gaps below published versions. Runtime startup runs forward only; migrations must remain transactional. The contract check rejects Goose’s NO TRANSACTION annotation regardless of case.

  2. Add explicit, parameterized application queries under queries/. sqlc.yaml reads the same migration directory used by the executable.

  3. Generate from the repository root:

    Generate queries from the repository root
    go run ./build/scripts/storage

  4. Map generated rows in storage adapters, update domain validation as needed, and add file-backed upgrade and recovery tests. Commit SQL and generated Go together; never manually edit dbgen.

  5. Run the contract check against the target branch and the affected tests:

    Validate the storage contract and affected packages
    go run ./build/scripts/storage -check -base main
    go test ./internal/storage ./internal/settings ./build/scripts/storage

The equivalent Wails tasks are storage:generate and storage:check.

CI rejects Required action
Stale or untracked generated queries Regenerate and commit SQL with generated Go
Changed published migrations Add a new migration; preserve target-branch files byte for byte
Query/import boundary violations Keep application queries in sqlc and database handles inside storage

An alpha-only Git base permits this explicit new lineage; it does not exempt a published schema/00001_initial.sql from immutability. Handwritten infrastructure SQL in store.go and recovery.go is limited to connection settings, identity/version inspection, integrity checks, and backups.

Recovery and verification

Section titled “Recovery and verification”
  • Exercise real SQLite migrations with temporary, file-backed databases.
  • Reopen after writes, interrupted upgrades, and recovery.
  • Validate native credential behavior separately on Windows and macOS.
  • Send unknown/newer schemas and uncertain commits into recovery rather than silently loading defaults.
  • Preserve the database identity and forward-only migration boundary.

Record results and limitations in the issue or pull request, not this guide.

Saved-settings recoveryThe user-facing recovery procedure.
Native acceptanceValidate platform vaults and desktop behavior.