""" Castle Extension Database Migrations This file contains a single squashed migration that creates the complete database schema for the Castle extension. MIGRATION HISTORY: This is a squashed migration that combines m001-m016 from the original incremental migration history. The complete historical migrations are preserved in migrations_old.py.bak for reference. Key schema decisions reflected in this migration: 1. Hierarchical Beancount-style account names (e.g., "Assets:Bitcoin:Lightning") 2. No journal_entries/entry_lines tables (Fava is source of truth) 3. User-specific equity accounts created dynamically (Equity:User-{user_id}) 4. Parent-only accounts removed (hierarchy implicit in colon-separated names) 5. Multi-currency support via balance_assertions 6. Granular permission system via account_permissions Original migration sequence (Nov 2025): - m001: Initial accounts, journal_entries, entry_lines tables - m002: Extension settings - m003: User wallet settings - m004: Manual payment requests - m005: Added flag/meta to journal entries - m006: Migrated to hierarchical account names - m007: Balance assertions - m008: Renamed Lightning account - m009: Added OnChain Bitcoin account - m010: User equity status - m011: Account permissions - m012: Updated default accounts with detailed hierarchy - m013: Removed parent-only accounts (Assets:Bitcoin, Equity) - m014: Removed legacy equity accounts (MemberEquity, RetainedEarnings) - m015: Converted entry_lines to single amount field - m016: Dropped journal_entries and entry_lines tables (Fava integration) """ async def m001_initial(db): """ Initial Castle database schema (squashed from m001-m016). Creates complete database structure for Castle accounting extension: - Accounts: Chart of accounts with hierarchical Beancount-style names - Extension settings: Castle-wide configuration - User wallet settings: Per-user wallet configuration - Manual payment requests: User-submitted payment requests to Castle - Balance assertions: Reconciliation and balance checking - User equity status: Equity contribution eligibility - Account permissions: Granular access control Note: Journal entries are managed by Fava/Beancount (external source of truth). Castle submits entries to Fava and queries Fava for journal data. """ # ========================================================================= # ACCOUNTS TABLE # ========================================================================= # Core chart of accounts with hierarchical Beancount-style naming. # Examples: "Assets:Bitcoin:Lightning", "Expenses:Food:Groceries" # User-specific accounts: "Assets:Receivable:User-af983632" await db.execute( f""" CREATE TABLE accounts ( id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, account_type TEXT NOT NULL, description TEXT, user_id TEXT, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) await db.execute( """ CREATE INDEX idx_accounts_user_id ON accounts (user_id); """ ) await db.execute( """ CREATE INDEX idx_accounts_type ON accounts (account_type); """ ) # ========================================================================= # EXTENSION SETTINGS TABLE # ========================================================================= # Castle-wide configuration settings await db.execute( f""" CREATE TABLE extension_settings ( id TEXT NOT NULL PRIMARY KEY, castle_wallet_id TEXT, fava_url TEXT NOT NULL DEFAULT 'http://localhost:3333', fava_ledger_slug TEXT NOT NULL DEFAULT 'castle-ledger', fava_timeout REAL NOT NULL DEFAULT 10.0, updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) # ========================================================================= # USER WALLET SETTINGS TABLE # ========================================================================= # Per-user wallet configuration await db.execute( f""" CREATE TABLE user_wallet_settings ( id TEXT NOT NULL PRIMARY KEY, user_wallet_id TEXT, updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) # ========================================================================= # MANUAL PAYMENT REQUESTS TABLE # ========================================================================= # User-submitted payment requests to Castle (reviewed by admins) await db.execute( f""" CREATE TABLE manual_payment_requests ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, amount INTEGER NOT NULL, description TEXT NOT NULL, notes TEXT, status TEXT NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, reviewed_at TIMESTAMP, reviewed_by TEXT, journal_entry_id TEXT ); """ ) await db.execute( """ CREATE INDEX idx_manual_payment_requests_user_id ON manual_payment_requests (user_id); """ ) await db.execute( """ CREATE INDEX idx_manual_payment_requests_status ON manual_payment_requests (status); """ ) # ========================================================================= # BALANCE ASSERTIONS TABLE # ========================================================================= # Reconciliation and balance checking at specific dates # Supports multi-currency (satoshis + fiat) with tolerance checking await db.execute( f""" CREATE TABLE balance_assertions ( id TEXT PRIMARY KEY, date TIMESTAMP NOT NULL, account_id TEXT NOT NULL, expected_balance_sats INTEGER NOT NULL, expected_balance_fiat TEXT, fiat_currency TEXT, tolerance_sats INTEGER DEFAULT 0, tolerance_fiat TEXT DEFAULT '0', checked_balance_sats INTEGER, checked_balance_fiat TEXT, difference_sats INTEGER, difference_fiat TEXT, notes TEXT, status TEXT NOT NULL DEFAULT 'pending', created_by TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, checked_at TIMESTAMP, FOREIGN KEY (account_id) REFERENCES accounts (id) ); """ ) await db.execute( """ CREATE INDEX idx_balance_assertions_account_id ON balance_assertions (account_id); """ ) await db.execute( """ CREATE INDEX idx_balance_assertions_status ON balance_assertions (status); """ ) await db.execute( """ CREATE INDEX idx_balance_assertions_date ON balance_assertions (date); """ ) # ========================================================================= # USER EQUITY STATUS TABLE # ========================================================================= # Manages equity contribution eligibility for users # Equity-eligible users can convert expenses to equity contributions # Creates dynamic user-specific equity accounts: Equity:User-{user_id} await db.execute( f""" CREATE TABLE user_equity_status ( user_id TEXT PRIMARY KEY, is_equity_eligible BOOLEAN NOT NULL DEFAULT FALSE, equity_account_name TEXT, notes TEXT, granted_by TEXT NOT NULL, granted_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, revoked_at TIMESTAMP ); """ ) await db.execute( """ CREATE INDEX idx_user_equity_status_eligible ON user_equity_status (is_equity_eligible) WHERE is_equity_eligible = TRUE; """ ) # ========================================================================= # ACCOUNT PERMISSIONS TABLE # ========================================================================= # Granular access control for accounts # Permission types: read, submit_expense, manage # Supports hierarchical inheritance (parent account permissions cascade) await db.execute( f""" CREATE TABLE account_permissions ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, account_id TEXT NOT NULL, permission_type TEXT NOT NULL, granted_by TEXT NOT NULL, granted_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, expires_at TIMESTAMP, notes TEXT, FOREIGN KEY (account_id) REFERENCES accounts (id) ); """ ) # Index for looking up permissions by user await db.execute( """ CREATE INDEX idx_account_permissions_user_id ON account_permissions (user_id); """ ) # Index for looking up permissions by account await db.execute( """ CREATE INDEX idx_account_permissions_account_id ON account_permissions (account_id); """ ) # Composite index for checking specific user+account permissions await db.execute( """ CREATE INDEX idx_account_permissions_user_account ON account_permissions (user_id, account_id); """ ) # Index for finding permissions by type await db.execute( """ CREATE INDEX idx_account_permissions_type ON account_permissions (permission_type); """ ) # Index for finding expired permissions await db.execute( """ CREATE INDEX idx_account_permissions_expires ON account_permissions (expires_at) WHERE expires_at IS NOT NULL; """ ) # ========================================================================= # DEFAULT CHART OF ACCOUNTS # ========================================================================= # Insert comprehensive default accounts with hierarchical names. # These accounts cover common use cases and can be extended by admins. # # Note: User-specific accounts (e.g., Assets:Receivable:User-xxx) are # created dynamically when users interact with the system. # # Note: Equity accounts (Equity:User-xxx) are created dynamically when # admins grant equity eligibility to users. import uuid from .account_utils import DEFAULT_HIERARCHICAL_ACCOUNTS for name, account_type, description in DEFAULT_HIERARCHICAL_ACCOUNTS: await db.execute( f""" INSERT INTO accounts (id, name, account_type, description, created_at) VALUES (:id, :name, :type, :description, {db.timestamp_now}) """, { "id": str(uuid.uuid4()), "name": name, "type": account_type.value, "description": description } ) async def m002_add_account_is_active(db): """ Add is_active field to accounts table for soft delete functionality. This enables marking accounts as inactive when they're removed from Beancount while preserving historical data and permissions. Inactive accounts: - Cannot have new permissions granted - Are filtered out of default queries - Can be reactivated if account is re-added to Beancount Default: All existing accounts are marked as active (TRUE). """ await db.execute( """ ALTER TABLE accounts ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE """ ) # Create index for faster queries filtering by is_active await db.execute( """ CREATE INDEX idx_accounts_is_active ON accounts (is_active) """ ) async def m003_add_account_is_virtual(db): """ Add is_virtual field to accounts table for virtual parent accounts. Virtual parent accounts: - Exist only in Castle DB (metadata-only, not in Beancount) - Used solely for permission inheritance - Allow granting permissions on top-level accounts like "Expenses", "Assets" - Are not synced to/from Beancount - Cannot be deactivated by account sync (they're intentionally metadata-only) Use case: Grant permission on "Expenses" → user gets access to all Expenses:* children Default: All existing accounts are real (is_virtual = FALSE). """ await db.execute( """ ALTER TABLE accounts ADD COLUMN is_virtual BOOLEAN NOT NULL DEFAULT FALSE """ ) # Create index for faster queries filtering by is_virtual await db.execute( """ CREATE INDEX idx_accounts_is_virtual ON accounts (is_virtual) """ ) # Insert default virtual parent accounts for permission management import uuid virtual_parents = [ ("Assets", "asset", "All asset accounts"), ("Liabilities", "liability", "All liability accounts"), ("Equity", "equity", "All equity accounts"), ("Income", "revenue", "All income accounts"), ("Expenses", "expense", "All expense accounts"), ] for name, account_type, description in virtual_parents: await db.execute( f""" INSERT INTO accounts (id, name, account_type, description, is_active, is_virtual, created_at) VALUES (:id, :name, :type, :description, TRUE, TRUE, {db.timestamp_now}) """, { "id": str(uuid.uuid4()), "name": name, "type": account_type, "description": description, }, ) async def m004_add_rbac_tables(db): """ Add Role-Based Access Control (RBAC) tables. This migration introduces a flexible RBAC system that complements the existing individual permission grants: - Roles: Named bundles of permissions (Employee, Contractor, Admin, etc.) - Role Permissions: Define what accounts each role can access - User Roles: Assign users to roles - Default Role: Auto-assign new users to a default role Permission Resolution Order: 1. Individual account_permissions (exceptions/overrides) 2. Role-based permissions via user_roles 3. Inherited permissions (hierarchical account names) 4. Deny by default """ # ========================================================================= # ROLES TABLE # ========================================================================= # Define named roles (Employee, Contractor, Admin, etc.) await db.execute( f""" CREATE TABLE roles ( id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, description TEXT, is_default BOOLEAN NOT NULL DEFAULT FALSE, created_by TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) await db.execute( """ CREATE INDEX idx_roles_name ON roles (name); """ ) await db.execute( """ CREATE INDEX idx_roles_is_default ON roles (is_default) WHERE is_default = TRUE; """ ) # ========================================================================= # ROLE PERMISSIONS TABLE # ========================================================================= # Define which accounts each role can access and with what permission type await db.execute( f""" CREATE TABLE role_permissions ( id TEXT PRIMARY KEY, role_id TEXT NOT NULL, account_id TEXT NOT NULL, permission_type TEXT NOT NULL, notes TEXT, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE, FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE ); """ ) await db.execute( """ CREATE INDEX idx_role_permissions_role_id ON role_permissions (role_id); """ ) await db.execute( """ CREATE INDEX idx_role_permissions_account_id ON role_permissions (account_id); """ ) await db.execute( """ CREATE INDEX idx_role_permissions_type ON role_permissions (permission_type); """ ) # ========================================================================= # USER ROLES TABLE # ========================================================================= # Assign users to roles await db.execute( f""" CREATE TABLE user_roles ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, role_id TEXT NOT NULL, granted_by TEXT NOT NULL, granted_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, expires_at TIMESTAMP, notes TEXT, FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE ); """ ) await db.execute( """ CREATE INDEX idx_user_roles_user_id ON user_roles (user_id); """ ) await db.execute( """ CREATE INDEX idx_user_roles_role_id ON user_roles (role_id); """ ) await db.execute( """ CREATE INDEX idx_user_roles_expires ON user_roles (expires_at) WHERE expires_at IS NOT NULL; """ ) # Composite index for checking specific user+role assignments await db.execute( """ CREATE INDEX idx_user_roles_user_role ON user_roles (user_id, role_id); """ ) # ========================================================================= # CREATE DEFAULT ROLES # ========================================================================= # Insert standard roles that most organizations will use import uuid # Define default roles and their descriptions default_roles = [ ( "employee", "Employee", "Standard employee role with access to common expense accounts", True, # This is the default role for new users ), ( "contractor", "Contractor", "External contractor with limited expense account access", False, ), ( "accountant", "Accountant", "Accounting staff with read access to financial accounts", False, ), ( "manager", "Manager", "Management role with broader expense approval and account access", False, ), ] for slug, name, description, is_default in default_roles: await db.execute( f""" INSERT INTO roles (id, name, description, is_default, created_by, created_at) VALUES (:id, :name, :description, :is_default, :created_by, {db.timestamp_now}) """, { "id": str(uuid.uuid4()), "name": name, "description": description, "is_default": is_default, "created_by": "system", # System-created default roles }, )