Wallet database schema

1 The Wallet file

The wallet file is a single SQLite database. The schema is created by Frame::NewWallet in src/frame.cpp. The schema version changes from time to time and is defined by WALLET_FILE_SCHEMA_VERSION in src/db_accessors.h:290. Every SQLite table either has an INTEGER PRIMARY KEY or is declared WITHOUT ROWID, so that the legacy “integer primary key autoincrements as a side effect of the rowid alias” quirk never silently changes row identity semantics.

The schema consists of base tables and indices, then views and INSTEAD OF triggers that read and write the joined data through the views, allowing us to interact with database as if it was structured as the view is structured.

2 PRAGMAs

Five PRAGMA statements are issued before the schema is created, on src/frame.cpp:408-412:

The two magic numbers are read back from the opened wallet as the very first check that the file is in fact a wallet, before anything else is attempted.

3 Tables

Four base tables, all STRICT. All have an INTEGER PRIMARY KEY aliased to ROWID.

3.1 Names

src/frame.cpp:414-417

CREATE TABLE "Names"(
    "ROWID"   INTEGER PRIMARY KEY,
    "name"   TEXT NOT NULL UNIQUE
) STRICT;

Stores the user’s Zooko nicknames – human-readable names that the user has chosen for keys they control. name is UNIQUE NOT NULL, because in the Zooko-triangle design a nickname identifies exactly one key from the user’s perspective.

3.2 PetNames

src/frame.cpp:419-422

CREATE TABLE "PetNames"(
    "ROWID"   INTEGER PRIMARY KEY,
    "name"   TEXT NOT NULL UNIQUE
) STRICT;

Stores petnames – the private, locally unique labels the user assigns to keys they do not control but have been referred to. Same shape as Names; the distinction is semantic. Currently the wallet writes into Names and reads through UserZookoIDs, but PetNames and UserPetZookoIDs exist so that the schema can hold a key we only know by public key and a local label.

3.3 Keys

src/frame.cpp:424-437

CREATE TABLE "Keys"(
    "ROWID"   INTEGER PRIMARY KEY,
    "pubkey" BLOB NOT NULL UNIQUE,
    "use" INTEGER NOT NULL,
    "name_id" INTEGER,
    "petname_id" INTEGER,
    FOREIGN KEY ("name_id")    REFERENCES "Names"("ROWID"),
    FOREIGN KEY ("petname_id") REFERENCES "PetNames"("ROWID"),
    CHECK (
        ("use" = 1 AND "name_id" IS NOT NULL)
        OR
        ("use" = 2 AND ("name_id" IS NOT NULL OR "petname_id" IS NOT NULL))
    )
) STRICT;

The single source of truth for the public keys the wallet knows about.

• pubkey is the ristretto255 elliptic-curve point; it is the unforgeable globally-unique identifier from Zooko’s triangle.
• use is a small integer that describes the wallet’s relationship to the secret key behind the pubkey. At present the wallet only ever writes use = 1, meaning “we hold the master secret from which this key is derived”. use = 2 is “we know only the public key”; the CHECK constraint enforces that a use = 1 row must carry a name_id (we own the key, so we give it a nickname) and that a use = 2 row must carry at least one of name_id or petname_id (we have some local label for it).
• name_id and petname_id are foreign keys into Names and PetNames respectively; either, both, or neither may be set.

3.4 Misc

src/frame.cpp:443-446

CREATE TABLE "Misc"(
    "ROWID"  INTEGER PRIMARY KEY,
    "m" ANY
) STRICT;

A free-form key-value table keyed by ROWID. Its m column has type ANY (declared by STRICT to allow any SQLite type) so that each row may carry an unrelated blob of bytes without forcing every row to share one schema. This is the per-wallet metadata bag.

The currently used rowids are documented in src/db_accessors.h:293-300:

Rowids 3 and 4 are reserved from the prior schema version 0_1 and deliberately left unused.

The master secret is never written to this database. As the comment at src/frame.cpp:508-512 records, the database is assumed to be backed up; the OS credential store is not. The master secret lives in the OS keychain (via secrets_storage::SaveMasterSecret), keyed by the wallet’s extended public key, optionally encrypted with a user-supplied passphrase. The mode byte at row 6 tells the loader which format to expect.

4 Indices

Three unique indices on Keys (src/frame.cpp:439-441):

CREATE UNIQUE INDEX i_pubkey       ON Keys (pubkey);
CREATE UNIQUE INDEX i_key_name     ON Keys (name_id)    WHERE name_id    IS NOT NULL;
CREATE UNIQUE INDEX i_key_petname  ON Keys (petname_id) WHERE petname_id IS NOT NULL;

The pubkey column is already declared UNIQUE NOT NULL, so the i_pubkey index is redundant with the column’s own implicit index – it is named explicitly so the rest of the program can refer to it by name in EXPLAIN QUERY PLAN output and so that the design intent is documented at the schema level.

i_key_name and i_key_petname are partial unique indices, guarded by WHERE ... IS NOT NULL. They enforce that each nickname and each petname is bound to at most one key. The partial WHERE clause is what makes the CHECK constraint on Keys actually meaningful: without it, several rows could share the same name_id (or petname_id), and the meaning of a Zooko nickname – one nickname, one key – would be lost. It also makes the INNER JOIN in the views (src/frame.cpp:454, 462) cheap: the join is on a unique key.

5 Views

Two read-only views join the base tables into the shape the rest of the program actually wants to read. src/frame.cpp:

CREATE VIEW UserZookoIDs AS
SELECT
    "Names".name AS name,
    "Keys".pubkey AS pubkey
FROM "Names" INNER JOIN "Keys"
ON "Names"."ROWID"="Keys"."name_id" AND "Keys"."use"=1
ORDER BY LOWER("name"), "name" COLLATE BINARY;

CREATE VIEW UserPetZookoIDs AS
SELECT
    "PetNames".name AS petname,
    "Keys".pubkey AS pubkey
FROM "PetNames" INNER JOIN "Keys"
ON "PetNames"."ROWID"="Keys"."petname_id" AND "Keys"."use"=2
ORDER BY LOWER("petname"), "petname" COLLATE BINARY;

UserZookoIDs selects only the rows where use = 1 (we own the key) joined to Names (the user’s nicknames for them). UserPetZookoIDs is the symmetric case for use = 2 and PetNames.

The ORDER BY LOWER(name), name COLLATE BINARY clause is the case insensitive sort the wallet UI needs: alphabetic order ignoring case, with a stable tiebreaker on the raw byte sequence so that two names that differ only in case sort deterministically. The same ORDER BY is duplicated on the m_find_position lookup in src/display_wallet.cpp:13-15, so that the count-and-insert trick used by display_wallet::add_name_event_handler (src/display_wallet.cpp:142-185) actually finds the position the new row will occupy in the view’s order.

The views are read in two places:

• display_wallet::display_wallet constructs m_read_names_and_keys (src/display_wallet.cpp:10) with the SQL SELECT * FROM "UserZookoIDs". display_wallet::refresh_from_database (src/display_wallet.cpp:199-243) steps through the result and lays out a row of two wxStaticText widgets for each (name, pubkey) pair – the nickname on the left, the base58-encoded public key on the right. For each row it also re-derives the public key from m_MasterSecret(name) and throws if it does not match the stored pubkey, which is the wallet’s self-test against silent corruption of either the database or the master secret.
• m_find_position (src/display_wallet.cpp:13-15) does SELECT COUNT(*) FROM UserZookoIDs WHERE LOWER(name) < LOWER(?1) OR UNLIKELY(LOWER(name)=LOWER(?1) AND name<?1) – a COUNT(*) over the same sorted view that returns the index at which the new name should be inserted into the two wxBoxSizers.

6 Triggers

The four INSTEAD OF triggers on the views (src/frame.cpp:468-504) let the rest of the program treat each view as if it were a single two-column table:

CREATE TRIGGER InsertUserZookoID INSTEAD OF INSERT ON UserZookoIDs FOR EACH ROW BEGIN
    INSERT OR ROLLBACK INTO "Names" VALUES(NULL, NEW."name");
    INSERT OR ROLLBACK INTO "Keys"  VALUES(NULL, NEW."pubkey", 1, last_insert_rowid(), NULL);
END;

CREATE TRIGGER DeleteUserZookoID INSTEAD OF DELETE ON UserZookoIDs FOR EACH ROW BEGIN
    DELETE FROM "Keys"  WHERE "Keys"."pubkey"  = OLD."pubkey";
    DELETE FROM "Names" WHERE "Names"."name"   = OLD."name";
END;

CREATE TRIGGER InsertUserPetZookoID INSTEAD OF INSERT ON UserPetZookoIDs FOR EACH ROW BEGIN
    INSERT OR ROLLBACK INTO "PetNames" VALUES(NULL, NEW."petname");
    INSERT OR ROLLBACK INTO "Keys"     VALUES(NULL, NEW."pubkey", 2, NULL, last_insert_rowid());
END;

CREATE TRIGGER DeleteUserPetZookoID INSTEAD OF DELETE ON UserPetZookoIDs FOR EACH ROW BEGIN
    DELETE FROM "Keys"     WHERE "Keys"."pubkey"     = OLD."pubkey";
    DELETE FROM "PetNames" WHERE "PetNames"."name"   = OLD."petname";
END;

The triggers exist to keep the three-table shape (Names / PetNames / Keys) invisible to the C++ code that uses the wallet. Each INSERT INTO UserZookoIDs VALUES(?, ?) becomes, atomically, an INSERT INTO "Names" followed by an INSERT INTO "Keys" whose name_id is the last_insert_rowid() from the first insert. The use column is hard-coded: 1 for a Zooko nickname, 2 for a petname.

The INSERT OR ROLLBACK choice is deliberate. If the second insert fails (because the UNIQUE index on pubkey rejected a duplicate, or the partial unique index on name_id rejected a duplicate nickname, or the CHECK constraint on Keys rejected the row), the trigger rolls back the enclosing transaction, so the caller sees a single “insertion failed” outcome and neither half of the pair is left orphaned in the database.

The DeleteUserZookoID and DeleteUserPetZookoID triggers do the reverse: delete the Keys row that joined to the view, then delete the underlying Names / PetNames row. The Keys row is deleted first so that the FOREIGN KEY references from Keys.name_id / Keys.petname_id no longer point at a row we are about to remove.

These triggers are reached through two prepared statements in src/db_accessors.h:

• sql_insert_name (src/db_accessors.h:230-239) executes INSERT OR FAIL INTO "UserZookoIDs" VALUES(?1, ?2) with ?1 a UTF-8 name and ?2 a ristretto255 point. This is the only insert path used by display_wallet::add_name_event_handler (src/display_wallet.cpp:156-159) when the user types a new nickname in the dialog.
• sql_insert_petname (src/db_accessors.h:253-262) is the symmetric prepared statement against UserPetZookoIDs. It is not yet called by any UI code; the petname view and trigger exist so that the schema can carry contact entries without a schema migration.

The “OR FAIL” suffix on the prepared statements, combined with “OR ROLLBACK” inside the triggers, propagates the SQLite error to the caller as a C++ exception via MyException. Duplicates – trying to reuse a pubkey or a name – therefore manifest as an exception rather than as a silently-corrupted wallet file.

7 How the rest of the program uses the schema

The wallet code touches the schema in four roles:

1. Wallet creation. Frame::NewWallet (src/frame.cpp:387-531) creates the schema, computes the wallet’s extended ristretto255 public key from the master secret it was given, writes that key into Misc row 5 (MISC_ROW_EXTENDED_PUBLIC_KEY) and the mode byte (passphrase or not) into row 6 (MISC_ROW_MODE), and hands the master secret off to secrets_storage::SaveMasterSecret (src/secrets_storage.h:70-74) to be stored in the OS credential store. The schema-level rows for the wallet identifier and schema version (rows 1 and 2) are written by the same code.

2. Wallet open and verification. Any code that opens an existing wallet first reads the four well-known Misc rows. This is centralized in the helper sql_read_from_misc (src/db_accessors.h:215-228) – SELECT m FROM Misc WHERE ROWID = ?1, typed via column<T>(0) or read<T>(0) – and applied at three call sites:

   • display_wallet::display_wallet (src/display_wallet.cpp:18-58): reads row 1 (must equal WALLET_FILE_IDENTIFIER), row 2 (must equal WALLET_FILE_SCHEMA_VERSION_CURRENT), row 5 (the stored extended public key) and row 6 (mode byte), loads the master secret from the credential store, and derives the extended public key from that secret to check it matches the stored row 5.
   • Frame::PromptForWalletPassphraseIfNeeded (src/frame.cpp:1207-1242): reads rows 1, 2 and 6 to decide whether to prompt the user for a passphrase before opening the wallet.
   • The unit-test driver WalletCreationUI/WalletValidationUI (src/unit_test.cpp:603-644): reads the same four rows to verify a freshly-created wallet file, then opens a raw cursor on Keys and cross-checks each row against Names and PetNames.

3. Listing and re-displaying the user’s Zooko identities. display_wallet::refresh_from_database (src/display_wallet.cpp:199-243) issues SELECT * FROM "UserZookoIDs", walks the result, derives each public key from the master secret and the nickname, and re-renders the two-column grid of nicknames and base58-encoded public keys. The sort order in the view is what determines the row order in the grid.

4. Adding a Zooko identity. display_wallet::add_name_event_handler (src/display_wallet.cpp:142-185) shows the user a wxTextEntryDialog, computes m_MasterSecret(zookoNickname).timesBase() to get the ristretto255 public key for the chosen nickname, and inserts the pair with m_insert_name(zookoNickname, pubkey) – the sql_insert_name prepared statement against UserZookoIDs, which fires the InsertUserZookoID trigger. It then computes the sort position with m_find_position and inserts two new wxStaticText widgets at that position in the two wxBoxSizers so the GUI stays in alphabetical order without a full refresh.

The unit test (src/unit_test.cpp:627-644) bypasses the views and reads the base tables directly: a SELECT * FROM "Keys" LIMIT 5, followed by per-row lookups in Names (via sql_read_name) and PetNames (via sql_read_petname, src/db_accessors.h:264-274). It asserts that every use = 1 key’s pubkey matches m_MasterSecret(name).timesBase() and that no use = 1 key carries a petname_id, which is the schema’s own CHECK constraint stated as a runtime test.

8 To be done

8.1 Fix config file location.

On linux, the configuration files is at ~/.config/ro/wallet/wallet.conf

It should be at ~/.config/ro/wallet/wallet.conf

And the entries should be prefixed with wallet, so that \FileDialog becomes \wallet\FileDialog, \MainFrame becomes \wallet\MainFrame, and so on and so forth.

With the effect that on Windows the information in the registry located at Computer\HKEY_USERS\S-1-2-33-456789\SOFTWARE\rho\wallet\FileDialog continues to be located at Computer\HKEY_USERS\S-1-2-33-456789\SOFTWARE\rho\wallet\FileDialog

8.2 fix unit test

Modify Unit Test, to check that the views give sensible results as well as directly reading the database.

This will implicitly test the Add User UI and the trigger, in that when unit test is run on a completely fresh system (no configuration file or database) it should have fired the trigger through the Add User UI, and if reading through view then gives sensible results, then the Add User UI, the view, and the trigger are working as intended.

8.3 support messages

Tabbed frame, with the current display