Something went wrong. Try again.
A self-hosted household ledger.
Something went wrong. Try again.
2.7 kB · 52 lines
SQL
at main
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253-- MCP server (add-mcp-server). Agents reach the ledger over MCP authenticated by-- a bearer API token, and their categorizations are recorded as a distinct,-- honest provenance source rather than wearing a person's identity.
-- One row per minted token. Only the hash is stored: a leaked database must not-- hand over live agent credentials (sessions set the same precedent). `user_did`-- is the creator on a multi-user server and NULL in single-user local mode-- (add-desktop-local-remote-modes). `last_used_at` turns a forgotten token into-- something visible and revocable rather than an invisible standing grant.CREATE TABLE api_tokens ( id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, scope TEXT NOT NULL CHECK (scope IN ('read', 'readwrite')), user_did TEXT REFERENCES users (did), -- creator; NULL in single-user local mode created_at TEXT NOT NULL, last_used_at TEXT, -- Revocation is soft: a token that has authored agent categorization events -- is referenced by the append-only event log (api_token_id below), so it is -- never deleted. Its label must keep resolving in old provenance; verify -- simply refuses a revoked token. revoked_at TEXT);
-- Widen categorization_events.source to admit 'agent'. SQLite cannot alter a-- CHECK constraint in place, so the table is rebuilt. It is a leaf in the FK-- graph (nothing references it; it only references transactions/categories/-- rules/users/api_tokens), so the drop-and-rename is safe with foreign_keys ON.-- The append-only guarantee is preserved: every existing event is copied-- verbatim, ids intact, api_token_id NULL (none were agent-sourced).CREATE TABLE categorization_events_new ( id INTEGER PRIMARY KEY AUTOINCREMENT, transaction_id INTEGER NOT NULL REFERENCES transactions (id), category_id INTEGER REFERENCES categories (id), -- NULL clears the category source TEXT NOT NULL CHECK (source IN ('rule', 'manual', 'reconciliation', 'agent')), rule_id INTEGER REFERENCES rules (id), actor_did TEXT REFERENCES users (did), api_token_id INTEGER REFERENCES api_tokens (id), created_at TEXT NOT NULL, CHECK (source != 'rule' OR rule_id IS NOT NULL), CHECK (source != 'manual' OR actor_did IS NOT NULL), CHECK (source != 'agent' OR api_token_id IS NOT NULL));
INSERT INTO categorization_events_new (id, transaction_id, category_id, source, rule_id, actor_did, api_token_id, created_at)SELECT id, transaction_id, category_id, source, rule_id, actor_did, NULL, created_atFROM categorization_events;
DROP TABLE categorization_events;ALTER TABLE categorization_events_new RENAME TO categorization_events;CREATE INDEX idx_catevents_transaction ON categorization_events (transaction_id);