padreug
46e910ba25
Implemented comprehensive role-based permission management system:
Database:
- Added m004_add_rbac_tables migration
- roles table: Define named permission bundles (Employee, Contractor, etc.)
- role_permissions table: Map roles to account permissions
- user_roles table: Assign users to roles with optional expiration
- Created 4 default roles: Employee (default), Contractor, Accountant, Manager
Models (models.py):
- Role, CreateRole, UpdateRole
- RolePermission, CreateRolePermission
- UserRole, AssignUserRole
- RoleWithPermissions, UserWithRoles
CRUD Operations (crud.py):
- Role management: create_role, get_role, get_all_roles, update_role, delete_role
- get_default_role() - get auto-assigned role for new users
- Role permissions: create_role_permission, get_role_permissions, delete_role_permission
- User role assignment: assign_user_role, get_user_roles, revoke_user_role
- Helper functions:
- get_user_permissions_from_roles() - resolve user permissions via roles
- check_user_has_role_permission() - check role-based access
- auto_assign_default_role() - auto-assign default role to new users
Permission Resolution Order:
1. Individual account_permissions (direct grants/exceptions)
2. Role-based permissions (via user_roles → role_permissions)
3. Inherited permissions (hierarchical account names)
4. Deny by default
Next: API endpoints, UI, and permission resolution logic integration
🤖 Generated with Claude Code
597 lines
19 KiB
Python
597 lines
19 KiB
Python
"""
|
|
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
|
|
},
|
|
)
|