aiolabs/libra
8
0
Fork 1
Code Issues 36 Pull requests Projects Releases 6 Packages Wiki Activity Actions
libra/migrations.py
Padreug c174cda48d Rename Castle Accounting extension to Libra 
Full identifier rename: module path lnbits.extensions.castle →
lnbits.extensions.libra, DB ext_castle → ext_libra, URL prefix
/castle/ → /libra/, manifest id castle → libra, fava ledger slug
default castle-ledger → libra-ledger, Beancount source metadata
castle-api → libra-api and link prefixes castle-{entry,tx}- →
libra-{entry,tx}-, column castle_wallet_id → libra_wallet_id, all
Python/JS/HTML identifiers (castle_ext, CastleSettings,
castle_reference, castleWalletConfigured, etc.).

Display name "Castle Accounting" → "Libra" (the scales/balance
metaphor — fits double-entry bookkeeping).

No backward compat: production hosts will be force-updated. Old
castle-prefixed Beancount metadata in existing Fava ledgers is
historical; new entries use libra-* prefixes going forward.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
2026-05-05 10:24:46 +02:00

597 lines
19 KiB
Python
Raw Permalink Blame History

"""
Libra Extension Database Migrations
This file contains a single squashed migration that creates the complete
database schema for the Libra 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 Libra database schema (squashed from m001-m016).
Creates complete database structure for Libra accounting extension:
- Accounts: Chart of accounts with hierarchical Beancount-style names
- Extension settings: Libra-wide configuration
- User wallet settings: Per-user wallet configuration
- Manual payment requests: User-submitted payment requests to Libra
- 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).
Libra 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
# =========================================================================
# Libra-wide configuration settings
await db.execute(
f"""
CREATE TABLE extension_settings (
id TEXT NOT NULL PRIMARY KEY,
libra_wallet_id TEXT,
fava_url TEXT NOT NULL DEFAULT 'http://localhost:3333',
fava_ledger_slug TEXT NOT NULL DEFAULT 'libra-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 Libra (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 Libra 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
},
)
Reference in a new issue View git blame Copy permalink