castle/docs/DOCUMENTATION.md

Castle Accounting Extension - Comprehensive Documentation

Overview

The Castle Accounting extension for LNbits implements a double-entry bookkeeping system designed for cooperative/communal living spaces (like "castles"). It tracks financial relationships between a central entity (the Castle) and multiple users, handling both Lightning Network payments and manual/cash transactions.

Architecture

Core Accounting Concepts

The system implements traditional double-entry bookkeeping principles:

• Every transaction affects at least two accounts
• Debits must equal credits (balanced entries)
• Five account types: Assets, Liabilities, Equity, Revenue, Expenses
• Accounts have "normal balances" (debit or credit side)

Account Types & Normal Balances

Account Type	Normal Balance	Increases With	Decreases With	Purpose
Asset	Debit	Debit	Credit	What Castle owns or is owed
Liability	Credit	Credit	Debit	What Castle owes to others
Equity	Credit	Credit	Debit	Member contributions, retained earnings
Revenue	Credit	Credit	Debit	Income earned by Castle
Expense	Debit	Debit	Credit	Costs incurred by Castle

User-Specific Accounts

The system creates per-user accounts for tracking individual balances:

• Accounts Receivable - {user_id[:8]} (Asset) - User owes Castle
• Accounts Payable - {user_id[:8]} (Liability) - Castle owes User
• Member Equity - {user_id[:8]} (Equity) - User's equity contributions

Balance Interpretation:

• balance > 0 and account is Liability → Castle owes user (user is creditor)
• balance < 0 (or positive Asset balance) → User owes Castle (user is debtor)

Database Schema

Core Tables

accounts

CREATE TABLE accounts (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    account_type TEXT NOT NULL,  -- asset, liability, equity, revenue, expense
    description TEXT,
    user_id TEXT,  -- NULL for system accounts, user_id for user-specific accounts
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

journal_entries

CREATE TABLE journal_entries (
    id TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    entry_date TIMESTAMP NOT NULL,
    created_by TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reference TEXT  -- Optional reference (e.g., payment_hash, invoice number)
);

entry_lines

CREATE TABLE entry_lines (
    id TEXT PRIMARY KEY,
    journal_entry_id TEXT NOT NULL,
    account_id TEXT NOT NULL,
    amount INTEGER NOT NULL,  -- Amount in satoshis (positive = debit, negative = credit)
    description TEXT,
    metadata TEXT DEFAULT '{}'  -- JSON: {fiat_currency, fiat_amount, fiat_rate, btc_rate}
);

extension_settings

CREATE TABLE extension_settings (
    id TEXT NOT NULL PRIMARY KEY,  -- Always "admin"
    castle_wallet_id TEXT,  -- LNbits wallet ID for Castle operations
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

user_wallet_settings

CREATE TABLE user_wallet_settings (
    id TEXT NOT NULL PRIMARY KEY,  -- user_id
    user_wallet_id TEXT,  -- User's LNbits wallet ID
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

manual_payment_requests

CREATE TABLE manual_payment_requests (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    amount INTEGER NOT NULL,  -- Amount in satoshis
    description TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',  -- pending, approved, rejected
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP,
    reviewed_by TEXT,
    journal_entry_id TEXT  -- Link to the journal entry when approved
);

Metadata System

Each entry_line can store metadata as JSON to preserve original fiat amounts:

{
    "fiat_currency": "EUR",
    "fiat_amount": 250.0,
    "fiat_rate": 1074.192,  // sats per fiat unit at time of transaction
    "btc_rate": 0.000931    // fiat per sat at time of transaction
}

Critical Design Decision: Fiat balances are calculated by summing fiat_amount from metadata, NOT by converting current satoshi balances. This preserves historical accuracy and prevents exchange rate fluctuations from affecting accounting records.

Transaction Flows

1. User Adds Expense (Liability Model)

Use Case: User pays for groceries with cash, Castle reimburses them

User Action: Add expense via UI

POST /castle/api/v1/entries/expense
{
    "description": "Biocoop groceries",
    "amount": 36.93,
    "currency": "EUR",
    "expense_account": "food",  // Account ID or name
    "is_equity": false,  // Creates liability, not equity
    "user_wallet": "wallet_id",
    "reference": null
}

Journal Entry Created:

Date: 2025-10-22
Description: Biocoop groceries (36.93 EUR)

DR Food & Supplies                  39,669 sats
    CR Accounts Payable - af983632          39,669 sats

Metadata on both lines:
{
    "fiat_currency": "EUR",
    "fiat_amount": 36.93,
    "fiat_rate": 1074.192,
    "btc_rate": 0.000931
}

Effect: Castle owes user €36.93 (39,669 sats)

2. Castle Adds Receivable

Use Case: User stays in a room, owes Castle for accommodation

Castle Admin Action: Add receivable via UI

POST /castle/api/v1/entries/receivable
{
    "description": "room 5 days",
    "amount": 250.0,
    "currency": "EUR",
    "revenue_account": "accommodation_revenue",
    "user_id": "af98363202614068...",
    "reference": null
}

Journal Entry Created:

Date: 2025-10-22
Description: room 5 days (250.0 EUR)

DR Accounts Receivable - af983632   268,548 sats
    CR Accommodation Revenue                 268,548 sats

Metadata:
{
    "fiat_currency": "EUR",
    "fiat_amount": 250.0,
    "fiat_rate": 1074.192,
    "btc_rate": 0.000931
}

Effect: User owes Castle €250.00 (268,548 sats)

3. User Pays with Lightning

User Action: Click "Pay Balance" → Generate invoice → Pay

Step A: Generate Invoice

POST /castle/api/v1/generate-payment-invoice
{
    "amount": 268548
}

Returns:

{
    "payment_hash": "...",
    "payment_request": "lnbc...",
    "amount": 268548,
    "memo": "Payment from user af983632 to Castle",
    "check_wallet_key": "castle_wallet_inkey"
}

Note: Invoice is generated on Castle's wallet, not user's wallet. User polls using check_wallet_key.

Step B: User Pays Invoice (External Lightning wallet or LNbits wallet)

Step C: Record Payment

POST /castle/api/v1/record-payment
{
    "payment_hash": "..."
}

Journal Entry Created:

Date: 2025-10-22
Description: Lightning payment from user af983632
Reference: payment_hash

DR Lightning Balance                268,548 sats
    CR Accounts Receivable - af983632       268,548 sats

Effect: User's debt reduced by 268,548 sats

4. Manual Payment Request Flow

Use Case: User wants Castle to pay them in cash instead of Lightning

Step A: User Requests Payment

POST /castle/api/v1/manual-payment-requests
{
    "amount": 39669,
    "description": "Please pay me in cash for groceries"
}

Creates manual_payment_request with status='pending'

Step B: Castle Admin Reviews

Admin sees pending request in UI:

• User: af983632
• Amount: 39,669 sats (€36.93)
• Description: "Please pay me in cash for groceries"

Step C: Castle Admin Approves

POST /castle/api/v1/manual-payment-requests/{id}/approve

Journal Entry Created:

Date: 2025-10-22
Description: Manual payment to user af983632
Reference: manual_payment_request_id

DR Accounts Payable - af983632      39,669 sats
    CR Lightning Balance                    39,669 sats

Effect: Castle's liability to user reduced by 39,669 sats

Alternative: Castle Admin Rejects

POST /castle/api/v1/manual-payment-requests/{id}/reject

No journal entry created, request marked as 'rejected'.

Balance Calculation Logic

User Balance Calculation

From crud.py:get_user_balance():

total_balance = 0
fiat_balances = {}  # e.g., {"EUR": 250.0, "USD": 100.0}

for account in user_accounts:
    account_balance = get_account_balance(account.id)  # Sum of debits - credits (or vice versa)

    # Calculate satoshi balance
    if account.account_type == AccountType.LIABILITY:
        total_balance += account_balance  # Positive = Castle owes user
    elif account.account_type == AccountType.ASSET:
        total_balance -= account_balance  # Positive asset = User owes Castle, so negative balance

    # Calculate fiat balance from metadata
    # Beancount-style: positive amount = debit, negative amount = credit
    for line in account_entry_lines:
        if line.metadata.fiat_currency and line.metadata.fiat_amount:
            if account.account_type == AccountType.LIABILITY:
                # For liabilities, negative amounts (credits) increase what castle owes
                if line.amount < 0:
                    fiat_balances[currency] += fiat_amount  # Castle owes more
                else:
                    fiat_balances[currency] -= fiat_amount  # Castle owes less
            elif account.account_type == AccountType.ASSET:
                # For assets, positive amounts (debits) increase what user owes
                if line.amount > 0:
                    fiat_balances[currency] -= fiat_amount  # User owes more (negative balance)
                else:
                    fiat_balances[currency] += fiat_amount  # User owes less

Result:

• balance > 0: Castle owes user (LIABILITY side dominates)
• balance < 0: User owes Castle (ASSET side dominates)
• fiat_balances: Net fiat position per currency

Castle Balance Calculation

From views_api.py:api_get_my_balance() (super user):

all_balances = get_all_user_balances()

total_liabilities = sum(b.balance for b in all_balances if b.balance > 0)  # What Castle owes
total_receivables = sum(abs(b.balance) for b in all_balances if b.balance < 0)  # What is owed to Castle
net_balance = total_liabilities - total_receivables

# Aggregate all fiat balances
total_fiat_balances = {}
for user_balance in all_balances:
    for currency, amount in user_balance.fiat_balances.items():
        total_fiat_balances[currency] += amount

Result:

• net_balance > 0: Castle owes users (net liability)
• net_balance < 0: Users owe Castle (net receivable)

UI/UX Design

Perspective-Based Display

The UI adapts based on whether the viewer is a regular user or Castle admin (super user):

User View

Balance Display:

• Green text: Castle owes them (positive balance, incoming money)
• Red text: They owe Castle (negative balance, outgoing money)

Transaction Badges:

• Green "Receivable": Castle owes them (Accounts Payable entry)
• Red "Payable": They owe Castle (Accounts Receivable entry)

Castle Admin View (Super User)

Balance Display:

• Red text: Castle owes users (positive balance, outgoing money)
• Green text: Users owe Castle (negative balance, incoming money)

Transaction Badges:

• Green "Receivable": User owes Castle (Accounts Receivable entry)
• Red "Payable": Castle owes user (Accounts Payable entry)

Outstanding Balances Table: Shows all users with non-zero balances:

• Username + truncated user_id
• Amount in sats and fiat
• "You owe" or "Owes you"

Transaction List

Each user sees transactions that affect their accounts. The query uses:

SELECT DISTINCT je.*
FROM journal_entries je
JOIN entry_lines el ON je.id = el.journal_entry_id
WHERE el.account_id IN (user_account_ids)
ORDER BY je.entry_date DESC, je.created_at DESC

This ensures users see ALL transactions affecting them, not just ones they created.

Default Chart of Accounts

Created by m001_initial migration:

Assets

• cash - Cash on hand
• bank - Bank Account
• lightning - Lightning Balance
• accounts_receivable - Money owed to the Castle

Liabilities

• accounts_payable - Money owed by the Castle

Equity

• member_equity - Member contributions
• retained_earnings - Accumulated profits

Revenue

• accommodation_revenue - Revenue from stays
• service_revenue - Revenue from services
• other_revenue - Other revenue

Expenses

• utilities - Electricity, water, internet
• food - Food & Supplies
• maintenance - Repairs and maintenance
• other_expense - Miscellaneous expenses

API Endpoints

Account Management

• GET /api/v1/accounts - List all accounts
• POST /api/v1/accounts - Create new account (admin only)
• GET /api/v1/accounts/{id}/balance - Get account balance
• GET /api/v1/accounts/{id}/transactions - Get account transaction history

Journal Entries

• GET /api/v1/entries - Get all journal entries (admin only)
• GET /api/v1/entries/user - Get current user's journal entries
• GET /api/v1/entries/{id} - Get specific journal entry
• POST /api/v1/entries - Create raw journal entry (admin only)
• POST /api/v1/entries/expense - Create expense entry (user)
• POST /api/v1/entries/receivable - Create receivable entry (admin only)
• POST /api/v1/entries/revenue - Create direct revenue entry (admin only)

Balance & Payments

• GET /api/v1/balance - Get current user's balance (or Castle total if super user)
• GET /api/v1/balance/{user_id} - Get specific user's balance
• GET /api/v1/balances/all - Get all user balances (admin only, enriched with usernames)
• POST /api/v1/generate-payment-invoice - Generate invoice for user to pay Castle
• POST /api/v1/record-payment - Record Lightning payment to Castle

Manual Payments

• POST /api/v1/manual-payment-requests - User creates manual payment request
• GET /api/v1/manual-payment-requests - User gets their manual payment requests
• GET /api/v1/manual-payment-requests/all - Admin gets all requests (optional status filter)
• POST /api/v1/manual-payment-requests/{id}/approve - Admin approves request
• POST /api/v1/manual-payment-requests/{id}/reject - Admin rejects request

Settings

• GET /api/v1/settings - Get Castle settings (super user only)
• PUT /api/v1/settings - Update Castle settings (super user only)
• GET /api/v1/user/wallet - Get user's wallet settings
• PUT /api/v1/user/wallet - Update user's wallet settings
• GET /api/v1/users - Get all users with configured wallets (admin only)

Utilities

• GET /api/v1/currencies - Get list of allowed fiat currencies

Current Issues & Limitations

1. Critical Bug: User Account Creation Inconsistency

Status: FIXED in latest version

Issue: Expenses were creating user accounts with wallet.wallet.id (wallet ID) while receivables used wallet.wallet.user (user ID). This created duplicate accounts for the same user.

Fix Applied: Modified api_create_expense_entry() to use wallet.wallet.user consistently.

Impact: Users who added expenses before this fix will have orphaned accounts. Requires database reset or migration to consolidate.

2. No Account Merging/Consolidation Tool

Issue: If accounts were created with different user identifiers, there's no tool to merge them.

Recommendation: Build an admin tool to:

• Detect duplicate accounts for the same logical user
• Merge entry_lines from old account to new account
• Update balances
• Archive old account

3. No Audit Trail

Issue: No tracking of who modified what and when (beyond created_by on journal entries).

Missing:

• Edit history for journal entries
• Deletion tracking (currently no deletion support)
• Admin action logs

Recommendation: Add audit_log table:

CREATE TABLE audit_log (
    id TEXT PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    user_id TEXT NOT NULL,
    action TEXT NOT NULL,  -- create, update, delete, approve, reject
    entity_type TEXT NOT NULL,  -- journal_entry, manual_payment_request, account
    entity_id TEXT NOT NULL,
    changes TEXT,  -- JSON of before/after
    ip_address TEXT
);

4. No Journal Entry Editing or Deletion

Issue: Once created, journal entries cannot be modified or deleted.

Current Workaround: Create reversing entries.

Recommendation: Implement:

• Soft delete (mark as void, create reversing entry)
• Amendment system (create new entry, link to original, mark original as amended)
• Strict permissions (only super user, only within N days of creation)

5. No Multi-Currency Support Beyond Metadata

Issue: System stores fiat amounts in metadata but doesn't support:

• Mixed-currency transactions
• Currency conversion at reporting time
• Multiple fiat currencies in same entry

Current Limitation: Each entry_line can have one fiat amount. If user pays €50 + $25, this requires two separate journal entries.

Recommendation: Consider:

• Multi-currency line items
• Exchange rate table
• Reporting in multiple currencies

6. No Equity Distribution Logic

Issue: System has member_equity accounts but no logic for:

• Profit/loss allocation to members
• Equity withdrawal requests
• Voting/governance based on equity

Recommendation: Add:

• Periodic close books process (allocate net income to member equity)
• Equity withdrawal workflow (similar to manual payment requests)
• Member equity statements

7. No Reconciliation Tools

Issue: No way to verify that:

• Lightning wallet balance matches accounting balance
• Debits = Credits across all entries
• No orphaned entry_lines

Recommendation: Add /api/v1/reconcile endpoint that checks:

{
    "lightning_wallet_balance": 1000000,  # From LNbits
    "lightning_account_balance": 1000000,  # From accounting
    "difference": 0,
    "total_debits": 5000000,
    "total_credits": 5000000,
    "balanced": true,
    "orphaned_lines": 0,
    "issues": []
}

8. No Batch Operations

Issue: Adding 50 expenses requires 50 API calls.

Recommendation: Add:

• POST /api/v1/entries/batch - Create multiple entries in one call
• CSV import functionality
• Template system for recurring entries

9. No Date Range Filtering

Issue: Can't easily get "all transactions in October" or "Q1 revenue".

Recommendation: Add date range parameters to all list endpoints:

GET /api/v1/entries/user?start_date=2025-01-01&end_date=2025-03-31

10. No Reporting

Issue: No built-in reports for:

• Income statement (P&L)
• Balance sheet
• Cash flow statement
• Per-user statements

Recommendation: Add reporting endpoints:

• GET /api/v1/reports/income-statement?start=...&end=...
• GET /api/v1/reports/balance-sheet?date=...
• GET /api/v1/reports/user-statement/{user_id}?start=...&end=...

Recommended Refactoring

Phase 1: Data Integrity & Cleanup (High Priority)

1. Account Consolidation Migration

   • Create m005_consolidate_user_accounts.py
   • Detect accounts with wallet_id as user_id
   • Migrate entry_lines to correct accounts
   • Mark old accounts as archived

2. Add Balance Validation

   • Add GET /api/v1/validate endpoint
   • Check all journal entries balance (debits = credits)
   • Check no orphaned entry_lines
   • Check fiat balance calculation consistency

3. Add Soft Delete Support

   • Add deleted_at column to all tables
   • Add void status to journal_entries
   • Create reversing entry when voiding

Phase 2: Feature Completeness (Medium Priority)

4. Audit Trail

   • Add audit_log table
   • Log all creates, updates, deletes
   • Add /api/v1/audit-log endpoint with filtering

5. Reconciliation Tools

   • Add /api/v1/reconcile endpoint
   • Add UI showing reconciliation status
   • Alert when out of balance

6. Reporting

   • Add income statement generator
   • Add balance sheet generator
   • Add user statement generator (PDF?)
   • Add CSV export for all reports

7. Date Range Filtering

   • Add start_date, end_date to all list endpoints
   • Update UI to support date range selection

Phase 3: Advanced Features (Low Priority)

8. Batch Operations

   • Add batch entry creation
   • Add CSV import
   • Add recurring entry templates

9. Multi-Currency Enhancement

   • Store exchange rates in database
   • Support mixed-currency entries
   • Add currency conversion at reporting time

10. Equity Management

    • Add profit allocation logic
    • Add equity withdrawal workflow
    • Add member equity statements

Phase 4: Export & Integration

11. Beancount Export (See next section)

Beancount Export Strategy

Beancount is a plain-text double-entry accounting system. Exporting to Beancount format enables:

• Professional-grade reporting
• Tax preparation
• External auditing
• Long-term archival

Beancount File Format

;; Account declarations
2025-01-01 open Assets:Lightning:Balance
2025-01-01 open Assets:AccountsReceivable:User-af983632
2025-01-01 open Liabilities:AccountsPayable:User-af983632
2025-01-01 open Equity:MemberEquity:User-af983632
2025-01-01 open Revenue:Accommodation
2025-01-01 open Expenses:Food

;; Transactions
2025-10-22 * "Biocoop groceries"
  fiat-amount: "36.93 EUR"
  fiat-rate: "1074.192 EUR/BTC"
  Expenses:Food                           39669 SATS
  Liabilities:AccountsPayable:User-af983632  -39669 SATS

2025-10-22 * "room 5 days"
  fiat-amount: "250.0 EUR"
  fiat-rate: "1074.192 EUR/BTC"
  Assets:AccountsReceivable:User-af983632  268548 SATS
  Revenue:Accommodation                   -268548 SATS

2025-10-22 * "Lightning payment from user af983632"
  payment-hash: "ffbdec55303..."
  Assets:Lightning:Balance                 268548 SATS
  Assets:AccountsReceivable:User-af983632 -268548 SATS

Export Implementation Plan

Add Endpoint:

@castle_api_router.get("/api/v1/export/beancount")
async def export_beancount(
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    format: str = "text",  # text or file
    wallet: WalletTypeInfo = Depends(require_admin_key),
) -> Union[str, FileResponse]:
    """Export all accounting data to Beancount format"""

Export Logic:

1. Generate Account Declarations

   async def generate_beancount_accounts() -> list[str]:
       accounts = await get_all_accounts()
       lines = []
   
       for account in accounts:
           beancount_type = map_account_type(account.account_type)
           beancount_name = format_account_name(account.name, account.user_id)
           lines.append(f"2025-01-01 open {beancount_type}:{beancount_name}")
   
       return lines
   

2. Generate Transactions

   async def generate_beancount_transactions(
       start_date: Optional[datetime] = None,
       end_date: Optional[datetime] = None
   ) -> list[str]:
       entries = await get_all_journal_entries_filtered(start_date, end_date)
       lines = []
   
       for entry in entries:
           # Header
           date = entry.entry_date.strftime("%Y-%m-%d")
           lines.append(f'{date} * "{entry.description}"')
   
           # Add reference as metadata
           if entry.reference:
               lines.append(f'  reference: "{entry.reference}"')
   
           # Add fiat metadata if available
           for line in entry.lines:
               if line.metadata.get("fiat_currency"):
                   lines.append(f'  fiat-amount: "{line.metadata["fiat_amount"]} {line.metadata["fiat_currency"]}"')
                   lines.append(f'  fiat-rate: "{line.metadata["fiat_rate"]}"')
                   break
   
           # Add entry lines
           for line in entry.lines:
               account = await get_account(line.account_id)
               beancount_name = format_account_name(account.name, account.user_id)
               beancount_type = map_account_type(account.account_type)
   
               # Beancount-style: amount is already signed (positive = debit, negative = credit)
               amount = line.amount
   
               lines.append(f"  {beancount_type}:{beancount_name}  {amount} SATS")
   
           lines.append("")  # Blank line between transactions
   
       return lines
   

3. Helper Functions

   def map_account_type(account_type: AccountType) -> str:
       mapping = {
           AccountType.ASSET: "Assets",
           AccountType.LIABILITY: "Liabilities",
           AccountType.EQUITY: "Equity",
           AccountType.REVENUE: "Income",  # Beancount uses "Income" not "Revenue"
           AccountType.EXPENSE: "Expenses",
       }
       return mapping[account_type]
   
   def format_account_name(name: str, user_id: Optional[str]) -> str:
       # Convert "Accounts Receivable - af983632" to "AccountsReceivable:User-af983632"
       # Convert "Food & Supplies" to "FoodAndSupplies"
       name = name.replace(" - ", ":User-")
       name = name.replace(" & ", "And")
       name = name.replace(" ", "")
       return name
   

4. Add Custom Commodity (SATS)

   def generate_commodity_declaration() -> str:
       return """
   2025-01-01 commodity SATS
       name: "Satoshi"
       asset-class: "cryptocurrency"
   """
   

UI Addition:

Add export button to Castle admin UI:

<q-btn color="primary" @click="exportBeancount">
  Export to Beancount
  <q-tooltip>Download accounting data in Beancount format</q-tooltip>
</q-btn>

async exportBeancount() {
  try {
    const response = await LNbits.api.request(
      'GET',
      '/castle/api/v1/export/beancount',
      this.g.user.wallets[0].adminkey
    )

    // Create downloadable file
    const blob = new Blob([response.data], { type: 'text/plain' })
    const url = window.URL.createObjectURL(blob)
    const link = document.createElement('a')
    link.href = url
    link.download = `castle-accounting-${new Date().toISOString().split('T')[0]}.beancount`
    link.click()
    window.URL.revokeObjectURL(url)

    this.$q.notify({
      type: 'positive',
      message: 'Beancount file downloaded successfully'
    })
  } catch (error) {
    LNbits.utils.notifyApiError(error)
  }
}

Beancount Verification

After export, users can verify with Beancount:

# Check file is valid
bean-check castle-accounting-2025-10-22.beancount

# Generate reports
bean-report castle-accounting-2025-10-22.beancount balances
bean-report castle-accounting-2025-10-22.beancount income
bean-web castle-accounting-2025-10-22.beancount

Testing Strategy

Unit Tests Needed

1. Balance Calculation

   • Test asset vs liability balance signs
   • Test fiat balance aggregation
   • Test mixed debit/credit entries

2. Journal Entry Validation

   • Test debits = credits enforcement
   • Test metadata preservation
   • Test user account creation

3. Transaction Flows

   • Test expense → payable flow
   • Test receivable → payment flow
   • Test manual payment approval flow

4. Beancount Export

   • Test account name formatting
   • Test transaction format
   • Test metadata preservation
   • Test debits = credits in output

Integration Tests Needed

1. End-to-End User Flow

   • User adds expense
   • Castle adds receivable
   • User pays via Lightning
   • Verify balances at each step

2. Manual Payment Flow

   • User requests payment
   • Admin approves
   • Verify journal entry created
   • Verify balance updated

3. Multi-User Scenarios

   • Multiple users with positive balances
   • Multiple users with negative balances
   • Verify Castle net balance calculation

Security Considerations

Current Implementation

1. Super User Checks

   • Implemented as wallet.wallet.user == lnbits_settings.super_user
   • Applied to: settings, receivables, manual payment approval/rejection, viewing all balances

2. User Isolation

   • Users can only see their own balances and transactions
   • Users cannot create receivables (only Castle admin can)
   • Users cannot approve their own manual payment requests

3. Wallet Key Requirements

   • require_invoice_key: Read access to user's data
   • require_admin_key: Write access, Castle admin operations

Potential Vulnerabilities

1. No Rate Limiting

   • API endpoints have no rate limiting
   • User could spam expense/payment requests

2. No Input Validation Depth

   • Description fields accept arbitrary text (XSS risk in UI)
   • Amount fields should have max limits
   • Currency validation relies on exchange rate API

3. No CSRF Protection

   • LNbits may handle this at framework level
   • Verify with LNbits security docs

4. Manual Payment Request Abuse

   • User could request payment for more than they're owed
   • Recommendation: Add validation to check amount <= user_balance

Recommendations

1. Add Input Validation

   class ExpenseEntry(BaseModel):
       description: str = Field(..., max_length=500, min_length=1)
       amount: float = Field(..., gt=0, le=1_000_000)  # Max 1M sats or fiat
       # ... etc
   

2. Add Rate Limiting

   from slowapi import Limiter
   
   limiter = Limiter(key_func=get_remote_address)
   
   @limiter.limit("10/minute")
   @castle_api_router.post("/api/v1/entries/expense")
   async def api_create_expense_entry(...):
       ...
   

3. Add Manual Payment Validation

   async def create_manual_payment_request(user_id: str, amount: int, description: str):
       # Check user's balance
       balance = await get_user_balance(user_id)
       if balance.balance <= 0:
           raise ValueError("You have no positive balance to request payment for")
       if amount > balance.balance:
           raise ValueError(f"Requested amount exceeds your balance of {balance.balance} sats")
       # ... proceed with creation
   

4. Sanitize User Input

   • Escape HTML in descriptions before displaying
   • Validate reference fields are alphanumeric only

Performance Considerations

Current Bottlenecks

1. Balance Calculation

   • get_user_balance() iterates through all entry_lines for user's accounts
   • get_all_user_balances() calls get_user_balance() for each user
   • No caching

2. Transaction List

   • Fetches all entry_lines for each journal_entry
   • No pagination (hardcoded limit of 100)

3. N+1 Query Problem

   • get_journal_entries_by_user() fetches entries, then calls get_entry_lines() for each
   • Could be optimized with JOIN

Optimization Recommendations

1. Add Balance Cache

   # New table
   CREATE TABLE user_balance_cache (
       user_id TEXT PRIMARY KEY,
       balance INTEGER NOT NULL,
       fiat_balances TEXT,  -- JSON
       last_updated TIMESTAMP NOT NULL
   );
   
   # Update cache after each transaction
   async def update_balance_cache(user_id: str):
       balance = await get_user_balance(user_id)
       await db.execute(
           "INSERT OR REPLACE INTO user_balance_cache ...",
           ...
       )
   

2. Add Pagination

   @castle_api_router.get("/api/v1/entries/user")
   async def api_get_user_entries(
       wallet: WalletTypeInfo = Depends(require_invoice_key),
       limit: int = 100,
       offset: int = 0,  # Add offset
   ) -> dict:
       entries = await get_journal_entries_by_user(
           wallet.wallet.user, limit, offset
       )
       total = await count_journal_entries_by_user(wallet.wallet.user)
       return {
           "entries": entries,
           "total": total,
           "limit": limit,
           "offset": offset,
       }
   

3. Optimize Query with JOIN

   async def get_journal_entries_by_user_optimized(user_id: str, limit: int = 100):
       # Single query that fetches entries and their lines
       rows = await db.fetchall(
           """
           SELECT
               je.id, je.description, je.entry_date, je.created_by,
               je.created_at, je.reference,
               el.id as line_id, el.account_id, el.debit, el.credit,
               el.description as line_description, el.metadata
           FROM journal_entries je
           JOIN entry_lines el ON je.id = el.journal_entry_id
           WHERE el.account_id IN (
               SELECT id FROM accounts WHERE user_id = :user_id
           )
           ORDER BY je.entry_date DESC, je.created_at DESC
           LIMIT :limit
           """,
           {"user_id": user_id, "limit": limit}
       )
   
       # Group by journal entry
       entries_dict = {}
       for row in rows:
           if row["id"] not in entries_dict:
               entries_dict[row["id"]] = JournalEntry(
                   id=row["id"],
                   description=row["description"],
                   entry_date=row["entry_date"],
                   created_by=row["created_by"],
                   created_at=row["created_at"],
                   reference=row["reference"],
                   lines=[]
               )
           entries_dict[row["id"]].lines.append(EntryLine(...))
   
       return list(entries_dict.values())
   

4. Add Database Indexes

   -- Already have these (good!)
   CREATE INDEX idx_accounts_user_id ON accounts (user_id);
   CREATE INDEX idx_entry_lines_account ON entry_lines (account_id);
   CREATE INDEX idx_journal_entries_date ON journal_entries (entry_date);
   
   -- Add these for optimization
   CREATE INDEX idx_entry_lines_journal_and_account ON entry_lines (journal_entry_id, account_id);
   CREATE INDEX idx_manual_payment_requests_user_status ON manual_payment_requests (user_id, status);
   

Migration Path for Existing Data

If Castle is already in production with the old code:

Migration Script: m005_fix_user_accounts.py

async def m005_fix_user_accounts(db):
    """
    Fix user accounts created with wallet_id instead of user_id.
    Consolidate entry_lines to correct accounts.
    """
    from lnbits.core.crud.wallets import get_wallet

    # Get all accounts
    accounts = await db.fetchall("SELECT * FROM accounts WHERE user_id IS NOT NULL")

    # Group accounts by name prefix (e.g., "Accounts Receivable", "Accounts Payable")
    wallet_id_accounts = []  # Accounts with wallet IDs
    user_id_accounts = {}    # Map: user_id -> account_id

    for account in accounts:
        if not account["user_id"]:
            continue

        # Check if user_id looks like a wallet_id (longer, different format)
        # Wallet IDs are typically longer and contain different patterns
        # We'll try to fetch a wallet with this ID
        try:
            wallet = await get_wallet(account["user_id"])
            if wallet:
                # This is a wallet_id, needs migration
                wallet_id_accounts.append({
                    "account": account,
                    "wallet": wallet,
                    "actual_user_id": wallet.user
                })
        except:
            # This is a proper user_id, keep as reference
            user_id_accounts[account["user_id"]] = account["id"]

    # For each wallet_id account, migrate to user_id account
    for item in wallet_id_accounts:
        old_account = item["account"]
        actual_user_id = item["actual_user_id"]

        # Find or create correct account
        account_name_base = old_account["name"].split(" - ")[0]  # e.g., "Accounts Receivable"
        new_account_name = f"{account_name_base} - {actual_user_id[:8]}"

        new_account = await db.fetchone(
            "SELECT * FROM accounts WHERE name = :name AND user_id = :user_id",
            {"name": new_account_name, "user_id": actual_user_id}
        )

        if not new_account:
            # Create new account
            await db.execute(
                """
                INSERT INTO accounts (id, name, account_type, description, user_id, created_at)
                VALUES (:id, :name, :type, :description, :user_id, :created_at)
                """,
                {
                    "id": urlsafe_short_hash(),
                    "name": new_account_name,
                    "type": old_account["account_type"],
                    "description": old_account["description"],
                    "user_id": actual_user_id,
                    "created_at": datetime.now()
                }
            )
            new_account = await db.fetchone(
                "SELECT * FROM accounts WHERE name = :name AND user_id = :user_id",
                {"name": new_account_name, "user_id": actual_user_id}
            )

        # Migrate entry_lines
        await db.execute(
            """
            UPDATE entry_lines
            SET account_id = :new_account_id
            WHERE account_id = :old_account_id
            """,
            {"new_account_id": new_account["id"], "old_account_id": old_account["id"]}
        )

        # Mark old account as archived (add archived column first if needed)
        await db.execute(
            "DELETE FROM accounts WHERE id = :id",
            {"id": old_account["id"]}
        )

Conclusion

The Castle Accounting extension provides a solid foundation for double-entry bookkeeping in LNbits. The core accounting logic is sound, with proper debit/credit handling and user-specific account isolation.

Strengths

✅ Correct double-entry bookkeeping implementation ✅ User-specific account separation ✅ Metadata preservation for fiat amounts ✅ Lightning payment integration ✅ Manual payment workflow ✅ Perspective-based UI (user vs Castle view)

Immediate Action Items

1. ✅ Fix user account creation bug (COMPLETED)
2. Deploy migration to consolidate existing accounts
3. Add balance cache for performance
4. Implement Beancount export
5. Add reconciliation endpoint

Long-Term Goals

1. Full audit trail
2. Comprehensive reporting
3. Journal entry editing/voiding
4. Multi-currency support
5. Equity management features
6. External system integrations (accounting software, tax tools)

The refactoring path is clear: prioritize data integrity, then add reporting/export, then enhance with advanced features. The system is production-ready for basic use cases but needs the recommended enhancements for a full-featured cooperative accounting solution.