NAME

App::Moneymoor::DB - SQLCipher connection wrapper, idempotent schema migration, and the transaction seam every gateway writes through.

SYNOPSIS

use MacOS::NativeLib <sqlcipher>;   # macOS only; see PORTABILITY
use App::Moneymoor::DB;

my $db = App::Moneymoor::DB.new(:db-path("$*HOME/.moneymoor/budget.db"));
my $result = $db.connect('correct horse battery staple');

if $result ~~ Failure {
    say "could not open: { $result.exception.message }";
    $result.so;                     # mark handled
    exit 1;
}

my @rows = $db.query-all(
    'SELECT id, name FROM accounts WHERE closed = 0 ORDER BY sort_order, id');
my $row  = $db.query-one('SELECT * FROM accounts WHERE id = ?', $id);

$db.execute('UPDATE accounts SET name = ? WHERE id = ?', 'Current', $id);

# Multi-statement atomicity — either both writes land or neither does.
$db.run-txn: {
    $db.execute('UPDATE assignments SET amount = ? WHERE id = ?', $a, $x);
    $db.execute('UPDATE assignments SET amount = ? WHERE id = ?', $b, $y);
};

$db.disconnect;

DESCRIPTION

A single-connection wrapper over DBIish's SQLCipher driver. connect($passphrase) opens the file (creating it when absent), keys the encryption layer, applies connection pragmas, and runs migrations. A bad passphrase returns a Failure via fail rather than throwing, so a login screen can report it without a CATCH.

v0.1 is a headless library used from one thread, so this layer is deliberately thin: one handle, no WAL, no writer actor. The method surface (execute / query-one / query-all / run-txn) is the same shape as App::Cantina::DB's actor-backed DB, so a future TUI can drop the actor in underneath without touching a single gateway.

DBIISH TRAPS THIS LAYER ABSORBS

Three DBDish behaviours bite every caller that talks to the driver directly. They are handled once, here:

• Unfinalized SELECTs hold a read transaction. A statement handle whose rows have not been consumed keeps an implicit read transaction open on the connection, which makes a later DDL statement or BEGIN IMMEDIATE fail. query-one / query-all always drain with .allrows(:array-of-hash) into a real Array, and the pragma helper drains with .allrows.eager.

• Several pragmas return a row. busy_timeout, journal_mode, foreign_keys and friends answer with a value. Executed and left unfetched they trip DBIish's deferred "rows() may not be accurate" warning at handle finalization — hence pragma, which drains and hands back whatever came out.

• Leaked statement handles hold schema locks. DDL issued through a handle that is never disposed can block a later ALTER / DROP with SQLITE_LOCKED. Writes go through DBDish's .do, which disposes the statement handle in a LEAVE block.

WRONG KEY VS PLAINTEXT FILE

On an existing file, connect proves the key by reading sqlite_master. When that read fails the file is either encrypted with a different passphrase or not encrypted at all. The two are distinguishable — a SQLCipher database begins with random-looking bytes, a plain SQLite file begins with the ASCII header SQLite format 3 — so the caller gets a specific message for the "you pointed me at an unencrypted DB" case, and a hedged one ("invalid passphrase (or corrupted profile)") otherwise, because genuine corruption is not distinguishable from a wrong key.

SCHEMA

All money columns are INTEGER pence. All dates are TEXT in YYYY-MM-DD, and so are all budget-period keys — a period is named by its own start date, so assignments.period_start holds '2026-03-01' under the calendar-month scheme and '2026-08-14' under a scheme anchored on payday. See App::Moneymoor::Util::Period.

• budget_meta — a two-column key/value table for facts about the file itself rather than about the money in it: schema_rev (which transforming migrations have run) and period_scheme, the budget's own period scheme as JSON, written by Service::Workspace.change-scheme and read back by it at construction. A key that is absent is not an error — for schema_rev it means revision 0, and for the scheme it means the calendar month.

• accounts — type is cash / credit / tracking. cash and credit are on budget; tracking accounts hold assets or debts you want visible without them funding envelopes.

• category_groups — display grouping. system = 1 marks groups the engine owns (the credit-card payment group); the gateway refuses to delete them.

• categories — kind is standard / payment / rta. Exactly one rta row is seeded by migrations (Ready to Assign, the inflow target). One payment row exists per credit account, linked by the UNIQUE payment_account_id and created atomically with the account by Gateway::Account.create. target_pence is the envelope's target amount, 0 meaning "no target" — never NULL, so no read site has to guard for one. It is added by ensure-column rather than by the CREATE, because budget files predating it exist; see SCHEMA EVOLUTION.

Four more C<ensure-column>s say what B<kind> of target it is.
      C<target_kind> is C<refill> (the default and the whole of v0.1:
      "available should be this much each period"), C<set_aside> ("put
      this much in each period") or C<by_period> ("reach this much by
      period E"), under a C<CHECK> on those three. C<target_period>
      and C<target_start> are nullable date strings used only by
      C<by_period> — the goal and the stamped plan start, each read as
      B<the period containing it>, so a scheme change re-derives the
      plan rather than invalidating it. C<target_repeat> is C<0> for a
      one-shot goal and C<R E<gt>= 1> for one that repeats every C<R>
      periods. What the tuple B<means> is
      L<App::Moneymoor::Service::Target>'s subject; what may be stored
      in it is C<Gateway::Category>'s.

C<carry_overspend> is an C<ensure-column> of the same shape and
      nothing to do with targets: C<0> (the default, and what every
      legacy row means) puts the envelope on rule 3's forcing rule —
      cash overspending resets it to zero and charges Ready to Assign —
      and C<1> carries the negative forward instead, as a payment
      envelope's always has. See L<App::Moneymoor::Service::Budget>.

• payees — names only; deleting one nulls the reference on its transactions.

• transactions — amount is signed from the account's point of view (inflow positive, outflow negative). transfer_peer_id points at the other leg of a transfer.

• splits — the categorized parts of a transaction; their sum must equal the transaction's amount (enforced in Gateway::Transaction, inside one SQL transaction). Transfers between two on-budget accounts carry no splits.

• assignments — one row per (period_start, category_id), the money you gave a category in that budget period. Enforced by a UNIQUE index, so the gateway can upsert.

Deterministic ordering is (date ASC, id ASC) for transactions and (period_start ASC, id ASC) for assignments — the budget derivation depends on it, so the indices exist to make it cheap. Period keys are fixed-width ISO dates, so SQLite's text ordering on period_start is chronological ordering, exactly as the engine's own lt / gt comparisons are.

SCHEMA EVOLUTION

run-migrations is replayed in full on every connect, so every migration has to be safe to run against a file that has already had it. Three patterns cover every case, and the first two are preferred precisely because they need no bookkeeping:

• Additive tables / indices — every CREATE is wrapped in IF NOT EXISTS, so replaying is a no-op and new objects simply appear.

• Additive columns — ensure-column checks PRAGMA table_info and issues ALTER TABLE ... ADD COLUMN only when the column is missing. SQLite has no ADD COLUMN IF NOT EXISTS, and this is the only idempotent substitute. A CHECK constraint and a REFERENCES clause (with a NULL default) may ride along on the added column; PRIMARY KEY, UNIQUE and non-constant NOT NULL defaults may not. An index over a newly added column must be created after the ensure-column call, or it fails on a legacy file where the column does not exist yet. categories.target_pence is the worked example: a NOT NULL DEFAULT 0 column whose default is also the right value for every pre-existing row, which is what makes the migration a single line with no backfill behind it. The four target_kind / target_period / target_start / target_repeat columns that followed it are the same shape again, CHECK constraint and nullable dates included: a legacy row reads as 'refill' with no dates and no repeat, which is exactly what it always meant. carry_overspend is the sixth, and the clearest statement of why the pattern works: its default of 0 is not merely a sensible value for a legacy row, it is the rule every period in that file was already derived under.

• One-shot transforming migrations, gated on schema_rev in budget_meta. For anything that rewrites data the user already has.

Why a transform cannot be replay-idempotent

The other two patterns are idempotent because they are statements about the desired shape: "there should be a table like this", "there should be a column like this". Running them twice asks for the same shape twice.

A transform is not a statement about a shape, it is a function applied to rows — and applying it twice applies it twice. period_start || '-01' takes '2026-03' to '2026-03-01' and then takes that to '2026-03-01-01'. There is no IF NOT ALREADY DONE to wrap it in, because "already done" is not visible in the schema: after the rename the column looks exactly the same whether the rewrite ran or not. So the fact that it ran has to be recorded, and budget_meta.schema_rev is that record. SCHEMA-REV is the revision this code writes; a file stamped with it, or with anything higher, skips the transform entirely.

Guards on the data are a useful second line — the WHERE length(...) = 7 above means a double-run would be a no-op rather than a corruption — but they are not the mechanism, because not every transform has a predicate that distinguishes done from not-done.

The worked example: months to period starts

Revision 1 re-keys assignments from calendar months ('2026-03') to budget-period starts ('2026-03-01'). Its four steps, in one run-txn:

• drop the two indices naming the old column — SQLite carries an index across a RENAME COLUMN, so leaving them would leave the index set described by history rather than by the CREATEs;

• ALTER TABLE assignments RENAME COLUMN month TO period_start;

• UPDATE ... SET period_start = period_start || '-01' WHERE length(period_start) = 7 — under monthly/1, the scheme every pre-period file was implicitly using, the period containing a month starts on its first day, so the entire re-key is a suffix;

• stamp schema_rev = 1.

Two things make it safe. SQLite's DDL is transactional, so the rename, the rewrite and the stamp commit or roll back together and a crash mid-migration reopens the file as either the old shape or the new one, never as a mixture. And the legacy shape is detected by asking PRAGMA table_info for a month column rather than by trusting the stamp — the real dogfood file has never had a budget_meta table at all, so its schema_rev reads as absent on the very connect that creates the table, and a fresh file reads exactly the same. The column is the fact; the stamp only says whether the transform has been applied.

The new indices are created after the gate, for the same reason an index over an ensure-column column is: an index on period_start cannot be created while a legacy file still calls it month.

What still has no pattern

Widening a CHECK constraint (say, a fourth account type) is not additive — SQLite stores CHECK as part of the table text — and needs the rename/recreate/copy/drop dance with a dedicated fresh connection. v0.1 ships no such migration; when one is needed, port App::Mindmoor::DB's !ensure-status-check-values.

SEED DATA

Migrations seed two system rows, both guarded by INSERT ... SELECT ... WHERE NOT EXISTS so re-running is a no-op:

• the rta category ("Ready to Assign") — the inflow target. It is not an envelope: it has no carry, and assigning to it is rejected by Gateway::Assignment.

• the Credit Card Payments group (system = 1) — the home for the payment category of every credit account.

ATTRIBUTES

• db-path — required at construction. The file is created on first connect.

METHODS

• connect($passphrase) — open, key, pragma, migrate. Returns self, or a Failure when the key does not open the file.

• disconnect — dispose the handle. Safe to call twice.

• is-connected(-- Bool)>

• handle — the raw DBIish connection, for the rare caller that needs last_insert_rowid() semantics this class does not wrap.

• execute($sql, *@bind) — write path (.do under the hood).

• query-one($sql, *@bind -- Hash)> — first row, or an empty Hash when nothing matched.

• query-all($sql, *@bind -- Array)> — every row as a Hash.

• last-insert-id(-- Int)> — the rowid of the most recent insert on this connection.

• ensure-column($table, $column, $decl) — additive column migration; no-op when the column already exists.

• get-meta(Str:D $key -- Str)> — one budget_meta value, or the Str type object when the key has never been written. Absence is an answer, not an error, and is deliberately distinguishable from a stored empty string.

• set-meta(Str:D $key, Str:D $value) — upsert one budget_meta value.

• in-transaction(-- Bool)> — True inside a run-txn closure.

• run-txn(&work) — run &work inside BEGIN IMMEDIATE … COMMIT, rolling back and rethrowing on exception. Re-entrant: a run-txn nested inside another joins the outer transaction instead of nesting (SQLite has no nested transactions without savepoints).

PORTABILITY

The sqlcipher shared library has to be findable by NativeCall. On macOS that means <use MacOS::NativeLib <sqlcipher>;> before use DBIish (Homebrew installs it outside the default search path). On Linux the loader manages alone only for soname-0 builds; distros that ship libsqlcipher.so.1 (Debian 13, Ubuntu 24.04+) need DBIISH_SQLCIPHER_LIB pointed at the library, because DBIish's own lookup hardcodes .so.0. On Windows the DLL must be on PATH. This module deliberately does not use MacOS::NativeLib itself — it is a macOS-only distribution and depending on it here would make every Linux consumer install it.