database

Imported from kardelitaitu/kasirmu (.agents/skills/database/SKILL.md). Install upstream with npx skills add kardelitaitu/kasirmu --skill database. Copyright stays with the author.

kasir.mu Database

kasir.mu is offline-first: the terminal's SQLite file is the system of record, and everything else — the cloud, analytics, the PostgreSQL replica — is downstream of it. A migration that misbehaves does not degrade a feature; it stops the till from opening.

This skill is the map of that database. rust-backend covers the language rules (Money, error types, transactions); this skill covers the system: where the file lives, how connections are configured, how the migration runner works, which invariants are named and enforced, and how the PostgreSQL replica is generated.

When to use

• Adding, editing, or reviewing a file under crates/kasirmu-core/migrations/.
• Adding or changing an entry in the registry (crates/kasirmu-core/src/migrations.rs).
• Changing connection setup or any PRAGMA.
• Writing SQL that reads or writes a money, rate, or multiplier column.
• Regenerating the PostgreSQL schema, or seeing the PG drift gate fail.
• Debugging a startup failure whose message names migrations, a checksum, drift, duplicate column name, or already exists.
• Adding a backup, restore, or export path.
• Writing tests for anything under crates/kasirmu-core/src/db/.

1. Where things live

2. Where the database file lives

Two different resolution stories, and they do not share code:

• Desktop and tablet clients — <app_data_dir>/kasir.db, unconditionally. resolve_db_path (apps/desktop-tauri/src/state.rs:757-763) joins the Tauri app-data directory with the literal file name. The tablet twin is apps/mobile-tauri/src/state.rs. There is no env-var override and no dev/prod variant on this path — if you need a different file in a test, construct the state directly rather than looking for a switch that does not exist.
• Server and CLI processes — the OZ_DB_PATH environment variable, defaulting to the relative kasir.db. The Dockerfiles set it to /data/kasir.db.
• Per-store files — store-<store_id>.sqlite, beside the global database, via store_db_path in platform/core/src/database/manager.rs. Each store gets its own connection, lazily opened and cached.

3. Connection setup and PRAGMAs

There is no single shared opener. Each process repeats the pattern, so a new entry point must set the PRAGMAs itself. The full set observed in the repository:

The rule that matters: foreign_keys is per-connection and SQLite's default is OFF. It is not inherited, not persisted in the file, and not implied by another connection having set it. Every opener sets it explicitly, and the reason is written down at crates/kasirmu-core/src/migrations.rs:411-413. A new connection that forgets it will silently accept orphaned child rows.

busy_timeout is set in only two places (crates/kasirmu-core/src/migrations.rs:404 and crates/kasirmu-local-api/src/lib.rs:166). Its absence elsewhere is intentional, not an oversight: without it, SQLite fails immediately on write-lock contention instead of waiting.

There is no cache_size PRAGMA anywhere in the repository.

4. The runtime handle

One connection, behind a mutex. There is no pool.

• Desktop: pub db: Arc<Mutex<Connection>> in apps/desktop-tauri/src/state.rs — a tokio::sync::Mutex, so commands lock it with .lock().await.
• Tablet: a bare tokio::sync::Mutex<Connection>, not wrapped in Arc.
• Per-store: Arc<std::sync::Mutex<HashMap<String, Arc<Mutex<Connection>>>>> in platform/core/src/database/manager.rs — note this one is std::sync::Mutex, not tokio's. Mixing the two up is an easy compile error.
• platform/core/src/database/pool.rs defines a Pool type, but it has no production call site — it is referenced only by its own tests. Do not treat it as the runtime path.

Tauri commands receive State<'_, AppState> and reach the database through state.db.lock().await, or through AppState::resolve_scope / resolve_store when the work is store-scoped. Most domain work is then delegated to kasirmu_bridge.

5. The migration system

The registry is the source of truth

Migrations are .sql files embedded at compile time and run in array order, which is canonical and deliberately not filename order (crates/kasirmu-core/src/migrations.rs:12-15). pub const ALL opens at crates/kasirmu-core/src/migrations.rs:46 and holds 63 entries (re-counted 22-09-26; it was 58 entries at :43 when this section was written, and the array has grown by five migrations since). Each entry is two fields:

Migration {
    id: "20260813_init.sql",
    sql: include_str!("../migrations/20260813_init.sql"),
},

There is no down field — the registry carries no reverse SQL, by design (DB-03).

The count trap. crates/kasirmu-core/migrations/ contains 64 *.sql files but the registry has 63 entries (both re-counted 22-09-26; this note used to say 59 and 58). The extra file is the generated 20260813_init.pg.sql, which is not a migration and must never be added to the registry. A doc or gate that quotes "64 migrations" is quoting a file count, not a registry count. The parity test excludes .pg.sql for exactly this reason. The numbers move every time a migration lands, so re-count rather than repeating these figures.

The tracking table

schema_migrations, created by the runner (platform/core/src/database/migrations.rs:197-201; re-measured 22-09-26 — the file was split into migrations.rs (578 lines) plus statements.rs and proofs.rs, which moved this block down from the :177-181 this section used to cite):

CREATE TABLE IF NOT EXISTS schema_migrations (
    id         TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    checksum   TEXT
)

id is the migration's file name, not a number and not a timestamp prefix. A database whose schema_migrations rows read 20260813_init.sql is behaving correctly.

Databases created before checksum tracking existed lack the checksum column; the runner adds it and backfills once.

What run does

platform_core::database::run(conn, migrations) — note it takes &mut Connection, because Connection::transaction requires it. kasirmu_core::migrations::run wraps it and then applies the runtime PRAGMAs of §3.

For each migration, in registry order:

1. Not applied → apply it, and insert its tracking row inside the same transaction, so there is no partial DDL.
2. Applied, no stored checksum → backfill the checksum once.
3. Applied, checksum matches → nothing.
4. Applied, checksum differs → log migration definition drift detected, re-apply the script, then rewrite the stored checksum.

Checksums are SHA-256 over the SQL after line-ending canonicalisation, with a legacy CRLF form accepted and rewritten on sight. This is why editing a committed migration is never harmless, even to fix a typo in a comment: it changes the checksum and forces the re-apply path.

The drift re-apply tries the whole script first and, only on a duplicate-object error, falls back to executing statement by statement and skipping the statements whose effect is provably already present. It cannot rescue a migration that consumes the state it transforms — a column converted then dropped, a table renamed — which is the DB-03 class. cosmetic_edit_to_any_migration_re_applies_cleanly in crates/kasirmu-core/src/migrations_tests.rs pins which migrations are in which set.

6. The named invariants

These identifiers are real and each is enforced somewhere. They are the vocabulary to use in a commit message or a review comment.

DB-06, DB-07 and DB-09 do not exist. The numbering has gaps. Do not infer a missing invariant from a neighbouring one, and do not "fill in" an identifier you have not found — there is no single canonical list document, and each identifier is defined only in the source file that enforces it.

7. Schema conventions

Exact-decimal values are fixed-point integers. Never REAL or DOUBLE.

scripts/verify-migration-column-types.py enforces this on every *.sql under crates/kasirmu-core/migrations/ (the PG file included). It strips comments first, then looks for a column-name-plus-float-type pair, so naming a float in a comment cannot trip it. The gate is wired into .githooks/pre-commit as the migration column-type lint and runs with --staged-only there.

Its whitelist (scripts/verify-migration-column-types.py:142-169) has 12 entries, each anchored to a file, table, column and a reason:

• loyalty_tiers.earn_multiplier — historical column, converted to earn_multiplier_millionths by a later migration.
• products.popularity_score — an analytics score recomputed from sales history, display-ranked, never money. (Also whitelisted in the PG twin and in the per-tenant-uniqueness rebuild's products_new.)
• tables.pos_x, pos_y, width, height — floor-plan canvas geometry, display-only. (Also whitelisted in the PG twin.)

A whitelist entry that matches nothing is itself a failure. If you remove or rename one of those columns, the gate fails until you remove its entry — stale exemptions are treated as drift, not as harmless leftovers. Adding a float of your own requires a justified whitelist entry, which is a deliberate speed bump: the reviewer sees your reason in the diff.

Indexes follow idx_<table>_<purpose> by observation, but no lint enforces it — unlike the fixed-point rule, which is machine-checked. Do not cite a naming lint that does not exist.

8. The PostgreSQL replica

SQLite is the source of truth. Postgres is a generated replica. The contract is docs/records/sqlite-pg-roles.md; read it before touching anything PG-shaped.

• crates/kasirmu-core/migrations/20260813_init.pg.sql is generated, by scripts/generate-pg-migration.py. Its single output path is fixed at scripts/generate-pg-migration.py:66, and the file it writes carries a DO NOT EDIT BY HAND header. Never hand-edit it.
• The generator parses registry order out of crates/kasirmu-core/src/migrations.rs, applies every migration to a throwaway in-memory SQLite database, dumps the resulting sqlite_master state, and translates it: INTEGER → BIGINT, REAL → DOUBLE PRECISION, autoincrement to identity, STRICT dropped, tables emitted in foreign-key topological order, triggers ported through a hand-written plpgsql map, and a curated RLS appendix appended.
• It fails closed in both directions on trigger parity — a trigger with no plpgsql entry is an error, and so is an entry with no trigger — and on stale RLS coverage or exemption lists.
• --check (branch at scripts/generate-pg-migration.py:611) renders twice and compares the two renders to prove determinism, then compares the result against the committed file and exits non-zero on drift without touching the working tree. The pre-commit hook runs it whenever a migration, the registry, or the generator is staged.

After any migration change, run the generator and re-stage the PG file. Forgetting this is the most common way to turn a clean migration into a red gate. The cloud server auto-applies the PG schema on boot, so it must stay idempotent and deterministic — which is why a hand-edit that "works" locally is still wrong.

To re-sync a shared dev database after a PG schema change, use scripts/reset-dev-pg.sh.

9. Backup, restore, and recovery

• Backup uses rusqlite's online Backup API (Store::backup in crates/kasirmu-core/src/db/mod.rs), not VACUUM INTO — it is safe against a live connection.
• Integrity is PRAGMA integrity_check; there is also a tenant-integrity check that fails loudly on foreign-tenant rows and runs at desktop boot.
• Restore is crates/kasirmu-cli/src/commands/backup.rs. It is order-sensitive: it checkpoints the WAL with PRAGMA wal_checkpoint(TRUNCATE), deletes the -wal and -shm sidecars, and only then copies the file in. Skipping the sidecar deletion produces a torn restore — the copied file is silently re-mixed with the previous database's write-ahead log. Store deletion removes the same sidecars.
• Shell wrappers: scripts/backup-db.sh and scripts/restore-db.sh.
• The one recovery journal is the cross-database topology Apply journal in crates/kasirmu-bridge/src/topology/persistence.rs. It is deliberately retained until both databases are restored, which is what makes compensation retryable after a crash. Do not "tidy it up" on a successful apply.
• Stock-ledger self-healing writes one deterministic compensating movement per shortfall (crates/kasirmu-core/src/db/products_stock_adjust/ledger.rs), rather than mutating the ledger in place.

Integration coverage lives in crates/kasirmu-core/tests/backup_restore_integration.rs and crates/kasirmu-core/tests/corruption_recovery_integration.rs.

10. Transaction discipline — and the deviation you must know about

rust-backend states the rule as: all writes happen in a transaction, and a function that writes must take &mut Connection, never &Connection.

The Store facade in crates/kasirmu-core/src/db/mod.rs deliberately deviates from the second half, and documents why: Store borrows &Connection, because checked transactions require &mut Connection, which would force every caller to hold a mutable borrow of the shared connection for the whole write. So Store uses Connection::unchecked_transaction() instead.

The facade's four rules, which are the contract the code actually follows:

1. Standalone atomic commands own their transaction.
2. Composable methods never nest — a nested unchecked_transaction() fails at runtime with cannot start a transaction within a transaction.
3. Read-only methods never open a transaction.
4. Error paths roll back.

If you follow rust-backend literally and change a Store method to take &mut Connection, you break the facade. If you add a new composable method and open your own transaction, you break rule 2 at runtime rather than at compile time. Both statements are true; the facade contract is the one the code obeys.

PRAGMA foreign_keys is a no-op inside a transaction. That is the whole reason DB-05 exists: rebuild migrations that toggle foreign keys in their own SQL were silently running with enforcement ON, risking cascade deletion on populated child tables. The runner now reads the current setting, turns enforcement off before opening the transaction, and restores the previous value afterwards — and a failure to restore is logged without masking the original error.

11. Testing database code

• Tests live in sibling *_tests.rs files wired at the bottom of the production file:

  #[cfg(test)]
  #[path = "mod_tests.rs"]
  mod tests;

  Never inline. This is a repository-wide rule, not a database one.
• Use kasirmu_core::migrations::fresh_db() for a migrated in-memory database. It builds a LazyLock snapshot once, runs all 63 migrations into it, then clones it per test through the SQLite backup::Backup API — orders of magnitude faster than re-running execute_batch per test. It is #[doc(hidden)] and test-only.
• For a database you intend to migrate yourself, open in memory and run migrations explicitly, the way crates/kasirmu-core/src/migrations_tests.rs does.
• platform/core/src/database/manager_tests.rs uses tempfile::tempdir() for the per-store-file tests, which need real files.
• Tauri state has test constructors (AppState::for_test and friends) in apps/desktop-tauri/src/state.rs; the cloud has an in-memory connector in apps/cloud-server/src/db.rs.

12. Verification

Fast, targeted:

cargo test -p kasirmu-core migrations
python scripts/verify-migration-column-types.py
python scripts/generate-pg-migration.py --check

Before pushing (these are also pre-commit and pre-push gates):

bash scripts/check.sh

The pre-commit hook runs the migration column-type lint when migrations are staged and the PG drift guard when a migration, the registry, or the generator is staged. The staged-scoped form is what the hook uses; the whole-tree form is what CI uses.

13. Common pitfalls

1. Editing an applied migration. It changes the checksum and forces a re-apply. Add a new migration instead — this is what "forward-only" means in practice.
2. Adding the PG file to the registry. 20260813_init.pg.sql is generated output, not a migration. Adding it breaks DB-01 parity and applies the entire PG schema to SQLite.
3. Assuming registry order is filename order. It is not. Several entries are ordered deliberately against their names, and inserting a new migration at the end of the array is not always the same as inserting it last in time.
4. Quoting "64 migrations". That is a file count. The registry holds 63.
5. Forgetting foreign_keys = ON on a new connection. SQLite's default is OFF, per connection. Nothing else turns it on for you.
6. Toggling PRAGMA foreign_keys inside a transaction. It is a silent no-op. This is the bug DB-05 was written to contain.
7. Using a float for money or a rate. The lint catches it, but only if the column name precedes the type keyword in a form the regex recognises. *_minor and *_millionths are the only sanctioned shapes.
8. Leaving a whitelist entry behind after removing the column it exempts. The gate fails on stale entries deliberately.
9. Hand-editing the .pg.sql file. Run the generator and re-stage.
10. Restoring without deleting the -wal and -shm sidecars. Produces a torn restore that looks like corruption later.
11. Opening a transaction inside a composable Store method. Fails at runtime, not at compile time, and only on the path that nests.
12. Inventing a DB-NN identifier. DB-06, DB-07 and DB-09 do not exist.

See also

• rust-backend — the language-level rules this skill assumes: the Money struct, thiserror/anyhow, clippy, doc comments, and the sibling-test convention. Read it first if you are new to the crates.
• tauri-ipc — how a Tauri command reaches the database handle and how domain types cross the IPC boundary.
• tdd — the red-green-refactor loop to use when fixing a migration or a Store bug.
• codebase-memory — graph-first discovery, which AGENTS.md requires before grepping for symbols. Useful for tracing every caller of a Store method before you change its signature.
• skill-drift-guard — run it after a migration rename, a column change, or any edit that invalidates a path or count quoted here.

last audited 22-09-26 by Budak-Korporat