commit c92de1ab24a6fdc0299b0defe2febb7e312f5ad2
parent 7826e182a8903f499b82e1f994a7027869333249
Author: Florian Dold <dold@taler.net>
Date: Mon, 20 Jul 2026 01:22:25 +0200
db: constrain booleans, counts and reserve_pub; fix schema comments
Boolean columns now restrict to 0/1/NULL. coin_availability counts get
a non-negative constraint. reserves.reserve_pub is UNIQUE. Also fixes a
misplaced comment and missing JSON-column annotations.
Diffstat:
2 files changed, 108 insertions(+), 25 deletions(-)
diff --git a/packages/taler-wallet-core/src/db-sqlite-schema.ts b/packages/taler-wallet-core/src/db-sqlite-schema.ts
@@ -62,8 +62,11 @@
* RefreshReason, DenomLossEventType, MerchantContractTokenKind and
* WithdrawalRecordType. Each such column says so where it is declared.
*
- * - INTEGER 0/1 is a boolean; sqlite has no boolean type. These columns are
- * named is_*, has_*, or similar, and carry a CHECK constraint.
+ * - INTEGER 0/1 is a boolean; sqlite has no boolean type. Every column the
+ * mappers convert with boolToDb carries a CHECK constraint restricting it
+ * to 0, 1 or NULL, so a stray value cannot be stored and later read back as
+ * a surprising truthy number. Not every INTEGER column is a boolean:
+ * status enums and counts are integers too, and have their own rules below.
*
* - "JSON" in a column comment means TEXT holding a JSON document, written
* and read whole by JSON.stringify/JSON.parse and never inspected by SQL.
@@ -290,6 +293,7 @@ CREATE TABLE IF NOT EXISTS mailbox_messages (
CREATE TABLE IF NOT EXISTS mailbox_configurations (
mailbox_base_url TEXT PRIMARY KEY,
+ -- JSON: MailboxConfiguration
payload TEXT NOT NULL
);
@@ -323,9 +327,11 @@ CREATE TABLE IF NOT EXISTS reserves (
threshold_granted TEXT,
threshold_next TEXT,
kyc_access_token TEXT,
- aml_review INTEGER
+ aml_review INTEGER CHECK (aml_review IN (0, 1))
);
-CREATE INDEX IF NOT EXISTS reserves_by_reserve_pub
+-- UNIQUE: getReserveByPub is a single-row lookup, so a duplicate would make
+-- it return an arbitrary one of the matches.
+CREATE UNIQUE INDEX IF NOT EXISTS reserves_by_reserve_pub
ON reserves (reserve_pub);
-- Fees are flattened rather than JSON: byFamilyParms indexes four of them,
@@ -353,9 +359,9 @@ CREATE TABLE IF NOT EXISTS denominations (
fee_refresh TEXT NOT NULL,
fee_refund TEXT NOT NULL,
fee_withdraw TEXT NOT NULL,
- is_offered INTEGER NOT NULL,
- is_revoked INTEGER NOT NULL,
- is_lost INTEGER,
+ is_offered INTEGER NOT NULL CHECK (is_offered IN (0, 1)),
+ is_revoked INTEGER NOT NULL CHECK (is_revoked IN (0, 1)),
+ is_lost INTEGER CHECK (is_lost IN (0, 1)),
master_sig BLOB NOT NULL,
verification_status INTEGER NOT NULL,
PRIMARY KEY (exchange_base_url, denom_pub_hash)
@@ -368,9 +374,6 @@ CREATE INDEX IF NOT EXISTS denominations_by_verification_status
CREATE INDEX IF NOT EXISTS denominations_by_family_and_expiry
ON denominations (denomination_family_serial, stamp_expire_withdraw, denom_pub_hash);
--- Deposit and refresh groups keep most of their structure as JSON: the
--- nested pieces (wire details, per-coin status, per-exchange info) are read
--- and written whole, and no query filters on them.
CREATE TABLE IF NOT EXISTS global_currency_exchanges (
id INTEGER PRIMARY KEY AUTOINCREMENT,
currency TEXT NOT NULL,
@@ -396,7 +399,7 @@ CREATE TABLE IF NOT EXISTS bank_accounts (
label TEXT,
-- JSON: string[]
currencies TEXT,
- kyc_completed INTEGER NOT NULL
+ kyc_completed INTEGER NOT NULL CHECK (kyc_completed IN (0, 1))
);
CREATE INDEX IF NOT EXISTS bank_accounts_by_payto_uri
ON bank_accounts (payto_uri);
@@ -577,6 +580,7 @@ CREATE TABLE IF NOT EXISTS purchases (
nonce_pub BLOB NOT NULL,
choice_index INTEGER,
secret_seed BLOB,
+ -- JSON: WalletPurchaseDownloadInfo
download TEXT,
-- The ONLY copy of download.fulfillmentUrl: stripped from the JSON on
-- write and re-inserted on read, so the indexed value cannot drift from
@@ -597,8 +601,8 @@ CREATE TABLE IF NOT EXISTS purchases (
donau_tax_id_salt TEXT,
donau_tax_id TEXT,
donau_year INTEGER,
- shared INTEGER NOT NULL,
- created_from_shared INTEGER,
+ shared INTEGER NOT NULL CHECK (shared IN (0, 1)),
+ created_from_shared INTEGER CHECK (created_from_shared IN (0, 1)),
timestamp INTEGER NOT NULL,
timestamp_accept INTEGER,
timestamp_last_refund_status INTEGER,
@@ -629,6 +633,9 @@ CREATE TABLE IF NOT EXISTS purchase_exchanges (
CREATE INDEX IF NOT EXISTS purchase_exchanges_by_exchange
ON purchase_exchanges (exchange_base_url);
+-- Deposit and refresh groups keep most of their structure as JSON: the
+-- nested pieces (wire details, per-coin status, per-exchange info) are read
+-- and written whole, and no query filters on them.
CREATE TABLE IF NOT EXISTS deposit_groups (
deposit_group_id TEXT PRIMARY KEY,
currency TEXT NOT NULL,
@@ -763,7 +770,7 @@ CREATE TABLE IF NOT EXISTS peer_push_credit (
kyc_last_check_status INTEGER,
kyc_last_check_code INTEGER,
kyc_last_rule_gen INTEGER,
- kyc_last_aml_review INTEGER,
+ kyc_last_aml_review INTEGER CHECK (kyc_last_aml_review IN (0, 1)),
kyc_last_deny INTEGER
);
CREATE INDEX IF NOT EXISTS peer_push_credit_by_status
@@ -820,7 +827,7 @@ CREATE TABLE IF NOT EXISTS peer_pull_credit (
kyc_last_check_status INTEGER,
kyc_last_check_code INTEGER,
kyc_last_rule_gen INTEGER,
- kyc_last_aml_review INTEGER,
+ kyc_last_aml_review INTEGER CHECK (kyc_last_aml_review IN (0, 1)),
kyc_last_deny INTEGER,
-- JSON: TalerErrorDetail
abort_reason TEXT,
@@ -866,7 +873,7 @@ CREATE TABLE IF NOT EXISTS exchanges (
update_status INTEGER NOT NULL,
-- JSON: TalerErrorDetail
unavailable_reason TEXT,
- cachebreak_next_update INTEGER,
+ cachebreak_next_update INTEGER CHECK (cachebreak_next_update IN (0, 1)),
tos_current_etag TEXT,
tos_accepted_etag TEXT,
tos_accepted_timestamp INTEGER,
@@ -877,9 +884,9 @@ CREATE TABLE IF NOT EXISTS exchanges (
current_merge_reserve_row_id INTEGER,
current_account_priv BLOB,
current_account_pub BLOB,
- peer_payments_disabled INTEGER,
- direct_deposit_disabled INTEGER,
- no_fees INTEGER,
+ peer_payments_disabled INTEGER CHECK (peer_payments_disabled IN (0, 1)),
+ direct_deposit_disabled INTEGER CHECK (direct_deposit_disabled IN (0, 1)),
+ no_fees INTEGER CHECK (no_fees IN (0, 1)),
-- The three details_pointer columns are one value. The mapper checks only
-- the master pub and then reads the other two unguarded, so a partially
-- set pointer would yield null typed as string.
@@ -983,13 +990,13 @@ CREATE TABLE IF NOT EXISTS withdrawal_groups (
bank_info TEXT,
-- JSON: WithdrawalExchangeAccountDetails[]
exchange_credit_accounts TEXT,
- is_foreign_account INTEGER,
+ is_foreign_account INTEGER CHECK (is_foreign_account IN (0, 1)),
kyc_payto_hash BLOB,
kyc_access_token TEXT,
kyc_last_check_status INTEGER,
kyc_last_check_code INTEGER,
kyc_last_rule_gen INTEGER,
- kyc_last_aml_review INTEGER,
+ kyc_last_aml_review INTEGER CHECK (kyc_last_aml_review IN (0, 1)),
kyc_last_deny INTEGER,
-- JSON: TalerProtocolDuration
kyc_withdrawal_delay TEXT,
@@ -1015,7 +1022,7 @@ CREATE TABLE IF NOT EXISTS withdrawal_groups (
-- taler_withdraw_uri are present exactly for the bank-integrated variant.
-- The URI is included because the mapper reads it unguarded for that
-- variant, and it is the only copy of the value.
- CHECK ((withdrawal_type = 'bank-integrated') = (bank_info IS NOT NULL))
+ CHECK ((withdrawal_type = 'bank-integrated') = (bank_info IS NOT NULL)),
CHECK (
(withdrawal_type = 'bank-integrated') = (taler_withdraw_uri IS NOT NULL)
)
@@ -1092,9 +1099,13 @@ CREATE TABLE IF NOT EXISTS coin_availability (
currency TEXT NOT NULL,
value TEXT NOT NULL,
exchange_master_pub BLOB,
- fresh_coin_count INTEGER NOT NULL,
- visible_coin_count INTEGER NOT NULL,
- pending_refresh_output_count INTEGER,
+ -- Counts, not flags. A negative value means a decrement ran without a
+ -- matching increment, which is a bug worth failing on rather than
+ -- storing: the coin selector reads these to decide what is spendable.
+ fresh_coin_count INTEGER NOT NULL CHECK (fresh_coin_count >= 0),
+ visible_coin_count INTEGER NOT NULL CHECK (visible_coin_count >= 0),
+ pending_refresh_output_count INTEGER
+ CHECK (pending_refresh_output_count >= 0),
PRIMARY KEY (exchange_base_url, denom_pub_hash, max_age)
);
-- Column order matches the IndexedDB byExchangeAgeAvailability index, because
diff --git a/packages/taler-wallet-core/src/dbtx-sqlite.test.ts b/packages/taler-wallet-core/src/dbtx-sqlite.test.ts
@@ -32,6 +32,7 @@ import { test } from "node:test";
import { BLOB_COLUMNS } from "./db-sqlite-schema.js";
import { conformanceCases } from "./dbtx-conformance-cases.js";
import { makeSqliteRunner } from "./dbtx-runners.js";
+import { initSqliteWalletDb } from "./dbtx-sqlite.js";
/**
* Run every conformance case against one database, then inspect how the
@@ -105,3 +106,74 @@ test("sqlite: BLOB columns really hold blobs", async (t) => {
t.diagnostic(`BLOB columns with no rows to check: ${missing.join(", ")}`);
}
});
+
+/**
+ * The schema's CHECK and UNIQUE constraints, exercised directly.
+ *
+ * A constraint that is silently dropped -- a typo in the DDL, a column
+ * rewritten during a migration -- looks exactly like one that is holding,
+ * because correct code never trips it. These probes write the bad values on
+ * purpose.
+ */
+test("sqlite: schema constraints reject invalid rows", async () => {
+ const impl = await createNodeHelperSqlite3Impl({ enableTracing: false });
+ const db = await impl.open(":memory:");
+ await initSqliteWalletDb(db);
+
+ const run = async (sql: string): Promise<string> => {
+ try {
+ await (await db.prepare(sql)).run({});
+ return "accepted";
+ } catch (e) {
+ return `rejected: ${e instanceof Error ? e.message : String(e)}`;
+ }
+ };
+
+ // Booleans are 0/1/NULL; sqlite would otherwise store any integer, and a
+ // stray 2 reads back as a truthy value that is not `true`.
+ assert.match(
+ await run(
+ "INSERT INTO bank_accounts (bank_account_id, payto_uri, kyc_completed)" +
+ " VALUES ('bad', 'payto://x', 7)",
+ ),
+ /CHECK constraint failed/,
+ "a boolean column must reject a value outside 0/1",
+ );
+ assert.strictEqual(
+ await run(
+ "INSERT INTO bank_accounts (bank_account_id, payto_uri, kyc_completed)" +
+ " VALUES ('good', 'payto://x', 1)",
+ ),
+ "accepted",
+ "a boolean column must still accept 1",
+ );
+
+ // Counts drive coin selection, and are decremented in places without a
+ // floor, so a negative value is a bug rather than a state to store.
+ assert.match(
+ await run(
+ "INSERT INTO coin_availability (exchange_base_url, denom_pub_hash," +
+ " max_age, currency, value, fresh_coin_count, visible_coin_count)" +
+ " VALUES ('https://e/', x'00', 0, 'C', 'C:1', -1, 0)",
+ ),
+ /CHECK constraint failed/,
+ "a negative coin count must be rejected",
+ );
+
+ // getReserveByPub is a single-row lookup.
+ assert.strictEqual(
+ await run(
+ "INSERT INTO reserves (reserve_pub, reserve_priv) VALUES (x'11', x'22')",
+ ),
+ "accepted",
+ );
+ assert.match(
+ await run(
+ "INSERT INTO reserves (reserve_pub, reserve_priv) VALUES (x'11', x'33')",
+ ),
+ /UNIQUE constraint failed/,
+ "two reserves must not share a public key",
+ );
+
+ await db.close();
+});
