Skip to content

Ledger - Billing & Invoicing Service

{: .no_toc }

Port: 5030 Database: PostgreSQL Repository: hivematrix-ledger

Table of Contents

  1. Overview
  2. Architecture
  3. Billing Calculation Engine
  4. Database Schema
  5. API Reference
  6. Configuration
  7. Installation & Setup
  8. Development
  9. Monitoring & Logging
  10. Troubleshooting
  11. Security
  12. Backup & Recovery
  13. Integration with Other Services
  14. Performance Optimization
  15. Changelog
  16. See Also

Overview

Ledger is HiveMatrix's comprehensive billing calculation and invoicing platform that automates monthly billing for managed service providers (MSPs). It calculates customer bills based on billing plans, usage data, custom overrides, and generates industry-standard CSV invoices compatible with accounting systems.

Ledger integrates the former Archive service (billing snapshots) directly into its codebase, providing a unified solution for both billing calculation and historical record-keeping.

Primary Responsibilities

  • Billing Calculation - Calculate monthly charges for users, assets, backups, and support tickets
  • Flexible Override System - Per-client custom rates, manual items, and one-off charges
  • Invoice Generation - CSV format invoices compatible with QuickBooks and other accounting software
  • Billing Snapshots - Immutable historical records of finalized bills (merged from Archive service)
  • Bulk Operations - Generate invoices for all clients simultaneously
  • Dashboard Analytics - Real-time billing metrics and company comparisons

Key Features

✅ Multi-Tier Billing Plans - Flexible plans with contract terms and support levels ✅ Per-Item Pricing - Users, workstations, servers, VMs, switches, firewalls ✅ Backup Billing - Tiered storage pricing with base fees and overage charges ✅ Ticket Hour Tracking - Automated billable hours from support tickets ✅ Custom Overrides - Client-specific rates for any billing component ✅ Manual Items - Add assets/users not in Codex for billing ✅ Custom Line Items - One-off, recurring, or yearly charges ✅ Prepaid Hours - Monthly or yearly prepaid support hour packages ✅ Billing Snapshots - Archive finalized bills with complete audit trail ✅ Bulk Invoicing - Generate all invoices at once for month-end processing

Architecture

Data Flow

Ledger operates as a calculation engine that pulls all base data from Codex (the single source of truth) and applies billing logic:

External Sources (PSA, Datto RMM)
    ↓
Codex (Master Data Repository)
    - Companies, Contacts
    - Assets, Tickets
    - Billing Plans
    ↓
Ledger (Billing Engine)
    - Fetches data via Codex API
    - Applies billing plan rates
    - Applies client overrides
    - Adds manual items
    - Calculates totals
    ↓
Output
    - Real-time billing calculations
    - CSV invoices
    - Billing snapshots (Archive)

Merged Archive Service

Historical Note: Archive was originally a separate service for storing billing snapshots. It has been fully integrated into Ledger to simplify the architecture and improve performance.

Benefits of Merge: - ✅ Eliminates HTTP overhead between services - ✅ Atomic transactions (billing + archive in one database operation) - ✅ Simpler deployment (one less service to manage) - ✅ Better data integrity - ✅ Faster snapshot creation

Migration: Old archive_client.py HTTP calls replaced with direct database writes via app/archive/snapshot.py

Billing Calculation Engine

How Billing Works

For each company and billing period (year/month):

  1. Fetch Base Data from Codex:
  2. Company profile (billing plan, contract term)
  3. Active users/contacts
  4. Active assets (workstations, servers, etc.)
  5. Backup usage data

  6. Support tickets with hours

  7. Determine Billing Plan:

  8. Company's default plan from Codex

  9. Apply client-specific plan override if exists

  10. Calculate Base Charges:

  11. User charges: Count active users × per-user rate
  12. Asset charges: Count assets by type × per-asset rate
  13. Backup charges: Base fee + overage beyond included TB

  14. Ticket charges: Total hours × hourly rate (if billed hourly)

  15. Apply Overrides:

  16. Client-specific custom rates
  17. Asset-specific billing type changes
  18. User-specific free/paid/custom status
  19. Add manual assets/users

  20. Add custom line items (one-off, recurring, yearly)

  21. Generate Output:

  22. Itemized receipt with all charges
  23. Total amount due
  24. CSV invoice for download
  25. Optional: Create billing snapshot for archive

Billing Plan Structure

Fetched from Codex (not stored in Ledger database):

{
  "plan_name": "Gold MSP Plan",
  "contract_term": "1 Year",
  "support_level": "Billed Hourly",
  "rates": {
    "per_user_cost": 15.00,
    "per_workstation_cost": 75.00,
    "per_server_cost": 125.00,
    "per_vm_cost": 50.00,
    "per_switch_cost": 100.00,
    "per_firewall_cost": 150.00,
    "per_hour_ticket_cost": 150.00,
    "backup_base_fee_workstation": 5.00,
    "backup_base_fee_server": 10.00,
    "backup_included_tb": 1.0,
    "backup_per_tb_fee": 25.00
  }
}

Support Levels: - Billed Hourly - Tickets charged at per_hour_ticket_cost × hours - Flat Monthly - Support included in base price (no ticket charges)

Contract Terms: - Month to Month - 1 Year - 2 Year - 3 Year

Different contract terms can have different pricing (e.g., 3 Year cheaper than Month to Month).

Example Calculation

Company: Acme Corporation Billing Period: October 2024 Billing Plan: Gold MSP Plan (1 Year, Billed Hourly)

Base Rates from Plan: - Per User: $15 - Per Workstation: $75 - Per Server: $125 - Per Hour: $150 - Backup Base (Workstation): $5 - Backup Base (Server): $10 - Included Backup: 1 TB - Per TB Overage: $25

Inventory from Codex: - 25 Active Users - 20 Workstations - 3 Servers - 5 Support Tickets (12.5 total hours) - Backup Usage: 1.8 TB

Calculation:

User Charges:     25 users × $15        = $375.00
Workstation:      20 × $75              = $1,500.00
Server:           3 × $125              = $375.00
Backup Base:      20 × $5 + 3 × $10    = $130.00
Backup Overage:   0.8 TB × $25         = $20.00
Ticket Hours:     12.5 hrs × $150      = $1,875.00
                                         ----------
TOTAL:                                   $4,275.00

With Overrides:

If Acme has a custom override: Per Workstation = $65 (instead of $75)

Workstation:      20 × $65              = $1,300.00 (saves $200)
New TOTAL:                               $4,075.00

Database Schema

Override & Operational Models

ClientBillingOverride

Per-client custom rates and plan overrides.

Columns: - company_account_number (String) - Unique identifier - override_billing_plan_enabled (Boolean) - Use custom plan? - billing_plan (String) - Custom plan name - override_support_level_enabled (Boolean) - Override support level? - support_level (String) - Custom support level - override_puc_enabled (Boolean) - Override per-user cost? - per_user_cost (Decimal) - Custom per-user rate - override_pwc_enabled (Boolean) - Override per-workstation cost? - per_workstation_cost (Decimal) - override_psc_enabled (Boolean) - Override per-server cost? - per_server_cost (Decimal) - override_pvc_enabled (Boolean) - Override per-VM cost? - per_vm_cost (Decimal) - override_pswitchc_enabled (Boolean) - Override per-switch cost? - per_switch_cost (Decimal) - override_pfirewallc_enabled (Boolean) - Override per-firewall cost? - per_firewall_cost (Decimal) - override_phtc_enabled (Boolean) - Override hourly ticket cost? - per_hour_ticket_cost (Decimal) - override_bbfw_enabled (Boolean) - Override backup base fee (workstation)? - backup_base_fee_workstation (Decimal) - override_bbfs_enabled (Boolean) - Override backup base fee (server)? - backup_base_fee_server (Decimal) - override_bit_enabled (Boolean) - Override included backup TB? - backup_included_tb (Decimal) - override_bpt_enabled (Boolean) - Override per-TB overage fee? - backup_per_tb_fee (Decimal) - override_prepaid_hours_monthly_enabled (Boolean) - prepaid_hours_monthly (Decimal) - Monthly prepaid support hours - override_prepaid_hours_yearly_enabled (Boolean) - prepaid_hours_yearly (Decimal) - Yearly prepaid support hours

Use Case: Company wants custom rate of $12/user instead of plan's $15/user.

AssetBillingOverride

Change billing type for specific assets (override Codex asset type).

Columns: - asset_id (Integer) - Asset ID from Codex (unique) - billing_type (String) - 'Workstation', 'Server', 'VM', 'Switch', 'Firewall', 'Custom', 'No Charge' - custom_cost (Decimal) - If billing_type = 'Custom'

Use Case: Codex says asset is "Workstation" ($75) but client wants it billed as "Server" ($125).

Example: 

# Override asset 12345 to be billed as Server
AssetBillingOverride(
    asset_id=12345,
    billing_type='Server',
    custom_cost=None
)

UserBillingOverride

Change billing type for specific users (override default "paid").

Columns: - user_id (Integer) - User ID from Codex (unique) - billing_type (String) - 'Paid', 'Free', 'Custom' - custom_cost (Decimal) - If billing_type = 'Custom'

Use Case: Mark CEO as free user, or charge custom rate for executive.

ManualAsset

Assets added manually (not in Codex/Datto).

Columns: - company_account_number (String) - Company identifier - hostname (String) - Asset name - billing_type (String) - 'Workstation', 'Server', etc. - custom_cost (Decimal) - If billing_type = 'Custom' - notes (Text) - Optional description

Use Case: Client has 2 BYOD devices not monitored by RMM that should be billed monthly.

ManualUser

Users added manually (not in Codex/PSA).

Columns: - company_account_number (String) - Company identifier - full_name (String) - User name - billing_type (String) - 'Paid', 'Free', 'Custom' - custom_cost (Decimal) - If billing_type = 'Custom' - notes (Text) - Optional description

Use Case: Client has contractors not in PSA system that should be billed.

CustomLineItem

One-off, recurring, or yearly custom charges.

Columns: - company_account_number (String) - Company identifier - name (String) - Item name (e.g., "Network Upgrade") - description (Text) - Detailed description - monthly_fee (Decimal) - Recurring monthly charge - one_off_fee (Decimal) - One-time charge for specific month - one_off_year (Integer) - Year for one-off charge - one_off_month (Integer) - Month for one-off charge - yearly_fee (Decimal) - Annual charge (billed specific month each year) - yearly_bill_month (Integer) - Month (1-12) to bill yearly fee

Use Cases: - Recurring: $500/month for cloud hosting - One-Off: $2,500 for network upgrade in March 2024 - Yearly: $1,200 for annual license renewal every January

Examples:

# Monthly recurring charge
CustomLineItem(
    company_account_number='620547',
    name='Cloud Hosting',
    description='Azure VM hosting',
    monthly_fee=500.00
)

# One-time charge for March 2024
CustomLineItem(
    company_account_number='620547',
    name='Network Upgrade',
    description='Replaced core switch',
    one_off_fee=2500.00,
    one_off_year=2024,
    one_off_month=3
)

# Annual charge billed every January
CustomLineItem(
    company_account_number='620547',
    name='SSL Certificate Renewal',
    description='Wildcard SSL cert',
    yearly_fee=1200.00,
    yearly_bill_month=1
)

ClientFeatureOverride

Custom feature pricing per client (future use).

Columns: - company_account_number (String) - feature_type (String) - Feature identifier - override_enabled (Boolean) - value (String) - Custom value

Unique Constraint: (company_account_number, feature_type)

TicketDetail

Ticket hours cache for billing calculations (synced from Codex).

Columns: - company_account_number (String) - ticket_id (BigInteger) - Unique ticket ID - ticket_number (String) - Display number - subject (String) - status (String) - priority (String) - total_hours_spent (Decimal) - created_at (String) - ISO timestamp - last_updated_at (String) - ISO timestamp (indexed)

Index: idx_ticket_updated on last_updated_at for fast filtering by year/month.

Note: This table is not actively used in current implementation. Ticket data fetched directly from Codex API.

SchedulerJob

Automated sync job configuration.

Columns: - job_name (String) - Unique job identifier - script_path (String) - Path to script - schedule_cron (String) - Cron expression - enabled (Boolean) - Active/inactive - last_run (String) - ISO timestamp - last_status (String) - 'Success', 'Failure', 'Running' - last_run_log (Text) - Output from last run - description (Text)

Archive Models (Merged from Archive Service)

BillingSnapshot

Immutable billing record for a company/period.

Columns: - company_account_number (String, indexed) - company_name (String) - invoice_number (String, unique, indexed) - Format: ACCOUNT-YYYYMM - billing_year (Integer, indexed) - billing_month (Integer, indexed) - invoice_date (String) - When invoice was generated - due_date (String) - Payment due date - archived_at (String) - When snapshot was created - billing_plan (String) - Plan at time of snapshot - contract_term (String) - Contract term at time of snapshot - support_level (String) - Support level at time of snapshot - total_amount (Decimal) - Total bill - total_user_charges (Decimal) - total_asset_charges (Decimal) - total_backup_charges (Decimal) - total_ticket_charges (Decimal) - total_line_item_charges (Decimal) - user_count (Integer) - Billable users - asset_count (Integer) - Billable assets - billable_hours (Decimal) - Total ticket hours - billing_data_json (Text) - Complete billing calculation (JSON blob) - invoice_csv (Text) - CSV invoice content - created_by (String) - User email or 'auto-scheduler' - notes (Text) - Optional notes

Indexes: - idx_company_period on (company_account_number, billing_year, billing_month) - idx_archived_at on archived_at

Purpose: Permanent record of finalized bills. Cannot be modified after creation.

SnapshotLineItem

Individual line items from a billing snapshot (denormalized for reporting).

Columns: - snapshot_id (Integer, FK to BillingSnapshot, indexed) - line_type (String) - 'user', 'asset', 'backup', 'ticket', 'custom' - item_name (String) - User/Asset name or custom item name - description (Text) - Full description - quantity (Decimal) - Quantity (default 1) - rate (Decimal) - Per-item rate - amount (Decimal) - Total for this line (quantity × rate)

Relationship: Many-to-one with BillingSnapshot

Purpose: Searchable line items for reporting and analysis.

ScheduledSnapshot

Configuration for automated monthly archival.

Columns: - enabled (Boolean) - Active/inactive - day_of_month (Integer) - Day to run (1-31, default 1) - hour (Integer) - Hour to run (0-23, default 2am) - snapshot_previous_month (Boolean) - Archive last month's bills - snapshot_all_companies (Boolean) - All companies or specific list - last_run_at (String) - ISO timestamp - last_run_status (String) - 'success', 'partial', 'failed' - last_run_count (Integer) - Snapshots created - last_run_log (Text) - Output/errors - created_at (String) - updated_at (String)

Use Case: Run on 1st of each month at 2am to archive all previous month bills.

SnapshotJob

Track individual snapshot creation jobs.

Columns: - id (String) - UUID primary key - job_type (String) - 'manual', 'scheduled', 'bulk' - status (String) - 'running', 'completed', 'failed' - target_year (Integer) - target_month (Integer) - target_account_numbers (Text) - JSON array, null = all - total_companies (Integer) - completed_companies (Integer) - failed_companies (Integer) - started_at (String) - completed_at (String) - output (Text) - JSON output with details - error (Text) - Error message if failed - success (Boolean) - Overall success - triggered_by (String) - Username or 'scheduler'

Purpose: Track long-running bulk snapshot operations with progress.

API Reference

Dashboard & UI Routes

Main Dashboard

GET /

Description: Main billing dashboard with metrics and quick actions.

Permission: Requires billing or admin permission level.

Features: - Total monthly revenue across all clients - Number of clients - Average bill per client - Billing metrics charts - Quick links to client list and bulk operations

Client List

GET /clients?filter={filter_type}

Description: List all clients with billing summary data.

Permission: Requires billing or admin permission level.

Query Parameters: - filter (optional) - Filter clients by status: - active - Only active companies - inactive - Only inactive companies - overdue - Companies with overdue payments (future use)

Response: HTML page with client table (loads data via API)

Features: - Search and filter clients - Sort by name, account number, or total bill - Quick links to client details and invoices

Client Billing Details

GET /client/<account_number>?year={year}&month={month}

Description: Detailed billing breakdown for a specific client.

Permission: Requires billing or admin permission level.

URL Parameters: - account_number (required) - Company account number

Query Parameters: - year (optional) - Billing year (default: current year) - month (optional) - Billing month (default: current month)

Features: - Itemized charges for all users - Itemized charges for all assets - Backup usage and charges - Ticket hours and charges - Custom line items - Total amount breakdown - Historical comparison (previous months) - Link to client settings - Download CSV invoice

Example: 

/client/620547?year=2024&month=10

Client Settings

GET/POST /client/<account_number>/settings

Description: Manage billing overrides and custom items for a client.

Permission: Requires billing or admin permission level.

Sections: 1. Billing Plan Override - Change plan or support level 2. Rate Overrides - Custom per-user, per-asset, hourly rates 3. Asset Overrides - Change billing type for specific assets 4. User Overrides - Mark users as free, paid, or custom 5. Manual Assets - Add/remove assets not in Codex 6. Manual Users - Add/remove users not in Codex 7. Custom Line Items - Add/edit/delete custom charges

POST Actions: - save_rate_overrides - Save client billing plan/rate overrides - add_manual_asset - Add manual asset - add_manual_user - Add manual user - add_line_item - Add custom line item - Delete actions via query params (?delete_manual_asset=<id>)

Invoice Routes

Download Invoice CSV

GET /invoice/<account_number>/download?year={year}&month={month}

Description: Download CSV invoice for a specific client and period.

Permission: Requires billing or admin permission level.

Query Parameters: - year (optional) - Billing year (default: current year) - month (optional) - Billing month (default: current month)

Response: CSV file download

Filename: {company_name}-{invoice_number}.csv

Format: QuickBooks-compatible CSV with columns: - InvoiceNo - Customer - InvoiceDate - DueDate - Item(Product/Service) - Description - Qty - Rate - Amount

Example: 

InvoiceNo,Customer,InvoiceDate,DueDate,Item(Product/Service),Description,Qty,Rate,Amount
620547-202410,Acme Corp,2024-10-31,2024-11-30,Managed Services,User: John Doe (Paid),1,15.00,15.00
620547-202410,Acme Corp,2024-10-31,2024-11-30,Managed Services,Workstation: ACME-PC-001,1,75.00,75.00

Bulk Invoice Download

GET /invoices/bulk/download?year={year}&month={month}

Description: Download ZIP file containing CSV invoices for all companies.

Permission: Requires billing or admin permission level.

Query Parameters: - year (optional) - Billing year (default: current year) - month (optional) - Billing month (default: current month)

Response: ZIP file containing CSV invoices

Filename: invoices-{year}-{month:02d}.zip

Contents: 

invoices-2024-10.zip
├── Acme Corporation-620547-202410.csv
├── Wayne Enterprises-987654-202410.csv
└── Stark Industries-555123-202410.csv

Use Case: Download all invoices at month-end for batch import to accounting system.

Billing API Routes

Get Invoice Summary

GET /api/invoice/<account_number>/summary?year={year}&month={month}

Description: Get billing summary without full itemization.

Permission: Requires billing, admin, or service token.

Response: 

{
  "account_number": "620547",
  "company_name": "Acme Corporation",
  "invoice_number": "620547-202410",
  "billing_period": "October 2024",
  "year": 2024,
  "month": 10,
  "billing_plan": "Gold MSP Plan",
  "contract_term": "1 Year",
  "support_level": "Billed Hourly",
  "totals": {
    "total": 4275.00,
    "user_charges": 375.00,
    "asset_charges": 1875.00,
    "backup_charges": 150.00,
    "ticket_charges": 1875.00,
    "line_item_charges": 0.00
  },
  "counts": {
    "users": 25,
    "workstations": 20,
    "servers": 3,
    "vms": 0,
    "switches": 1,
    "firewalls": 1,
    "billable_hours": 12.5
  }
}

Accept Bill (Create Snapshot)

POST /api/bill/accept
Content-Type: application/json

{
  "account_number": "620547",
  "year": 2024,
  "month": 10,
  "notes": "Approved by John Doe"
}

Description: Finalize bill and create immutable billing snapshot.

Permission: Requires billing or admin permission level.

Request Body: - account_number (required) - Company account number - year (required) - Billing year - month (required) - Billing month - notes (optional) - Notes about this bill

Response (Success): 

{
  "success": true,
  "message": "Bill archived successfully",
  "invoice_number": "620547-202410"
}

Response (Already Archived): 

{
  "success": false,
  "message": "This bill has already been archived",
  "invoice_number": "620547-202410"
}

What Happens: 1. Calculates billing for specified period 2. Generates CSV invoice 3. Creates BillingSnapshot record 4. Creates SnapshotLineItem records for each charge 5. Returns invoice number

Use Case: Finalize bill after review, locking it for historical record.

Check if Bill Archived

GET /api/bill/check-archived/<account_number>?year={year}&month={month}

Description: Check if a bill has been archived.

Permission: Requires billing, admin, or service token.

Response: 

{
  "archived": true,
  "invoice_number": "620547-202410",
  "archived_at": "2024-10-31T23:59:59Z"
}

or

{
  "archived": false
}

Get Billing Data

GET /api/billing/<account_number>?year={year}&month={month}

Description: Get complete billing calculation for a company/period.

Permission: Requires billing, admin, or service token.

Response: 

{
  "client": {
    "account_number": "620547",
    "name": "Acme Corporation",
    "billing_plan": "Gold MSP Plan",
    "contract_term_length": "1 Year",
    "contract_end_date": "2025-12-31"
  },
  "billing_plan_name": "Gold MSP Plan",
  "support_level_display": "Billed Hourly",
  "contract_term": "1 Year",
  "effective_rates": {
    "per_user_cost": 15.00,
    "per_workstation_cost": 75.00,
    "per_server_cost": 125.00,
    "per_hour_ticket_cost": 150.00
  },
  "receipt_data": {
    "billed_users": [...],
    "billed_assets": [...],
    "backup_charge": 150.00,
    "backup_usage_tb": 1.8,
    "billable_hours": 12.5,
    "ticket_charge": 1875.00,
    "total_user_charges": 375.00,
    "total_asset_charges": 1875.00,
    "total_line_item_charges": 0.00,
    "total": 4275.00
  }
}

Get Dashboard Data

GET /api/billing/dashboard?year={year}&month={month}

Description: Get billing data for all companies (bulk operation).

Permission: Requires billing or admin permission level.

Query Parameters: - year (optional) - Billing year (default: current year) - month (optional) - Billing month (default: current month)

Response: 

{
  "year": 2024,
  "month": 10,
  "companies": [
    {
      "account_number": "620547",
      "name": "Acme Corporation",
      "total": 4275.00,
      "user_count": 25,
      "asset_count": 23,
      "billable_hours": 12.5,
      "billing_plan": "Gold MSP Plan",
      "archived": false
    },
    {
      "account_number": "987654",
      "name": "Wayne Enterprises",
      "total": 8500.00,
      "user_count": 50,
      "asset_count": 45,
      "billable_hours": 25.0,
      "billing_plan": "Platinum MSP Plan",
      "archived": true,
      "invoice_number": "987654-202410"
    }
  ],
  "totals": {
    "total_revenue": 12775.00,
    "total_companies": 2,
    "average_bill": 6387.50
  }
}

Performance: Uses bulk Codex API to fetch all company data in single call.

Get Billing Plans

GET /api/plans

Description: List all billing plans from Codex.

Permission: Requires billing, admin, or service token.

Response: 

[
  {
    "plan_name": "Bronze MSP Plan",
    "contract_term": "Month to Month",
    "support_level": "Billed Hourly",
    "per_user_cost": 12.00,
    "per_workstation_cost": 65.00
  },
  {
    "plan_name": "Gold MSP Plan",
    "contract_term": "1 Year",
    "support_level": "Billed Hourly",
    "per_user_cost": 15.00,
    "per_workstation_cost": 75.00
  }
]

Override Management API

Get Client Overrides

GET /api/overrides/client/<account_number>

Description: Get all billing overrides for a company.

Permission: Requires billing, admin, or service token.

Response: 

{
  "overrides": {
    "company_account_number": "620547",
    "billing_plan": "Platinum MSP Plan",
    "support_level": null,
    "per_user_cost": null,
    "per_workstation_cost": 65.00,
    "per_server_cost": null,
    "prepaid_hours_monthly": 10.0
  }
}

or

{
  "overrides": null,
  "message": "No overrides configured"
}

Set Client Overrides

PUT /api/overrides/client/<account_number>
Content-Type: application/json

{
  "billing_plan": "Platinum MSP Plan",
  "per_workstation_cost": 65.00,
  "prepaid_hours_monthly": 10.0
}

Description: Set or update billing overrides for a company.

Permission: Requires billing or admin permission level.

Request Body: Any combination of override fields (only provided fields are updated)

Response: 

{
  "success": true,
  "message": "Overrides updated successfully"
}

Get Asset Overrides

GET /api/overrides/assets/<account_number>

Description: Get asset billing overrides for a company.

Response: 

{
  "overrides": [
    {
      "asset_id": 12345,
      "hostname": "ACME-PC-001",
      "billing_type": "Server",
      "custom_cost": null
    },
    {
      "asset_id": 12346,
      "hostname": "ACME-BYOD-01",
      "billing_type": "Custom",
      "custom_cost": 50.00
    }
  ]
}

Set Asset Override

POST /api/overrides/asset
Content-Type: application/json

{
  "asset_id": 12345,
  "billing_type": "Server",
  "custom_cost": null
}

Description: Override billing type for a specific asset.

Request Body: - asset_id (required) - Asset ID from Codex - billing_type (required) - 'Workstation', 'Server', 'VM', 'Switch', 'Firewall', 'Custom', 'No Charge' - custom_cost (optional) - Required if billing_type='Custom'

Response: 

{
  "success": true,
  "message": "Asset override saved"
}

Archive API Routes

List Snapshots

GET /archive/api/snapshots?account_number={account}&year={year}&month={month}&limit={limit}&offset={offset}

Description: List billing snapshots with optional filtering.

Permission: Requires billing, admin, or service token.

Query Parameters: - account_number (optional) - Filter by company - year (optional) - Filter by year - month (optional) - Filter by month - limit (optional) - Number of results (default: 50) - offset (optional) - Pagination offset (default: 0)

Response: 

{
  "snapshots": [
    {
      "id": 123,
      "company_account_number": "620547",
      "company_name": "Acme Corporation",
      "invoice_number": "620547-202410",
      "billing_year": 2024,
      "billing_month": 10,
      "total_amount": 4275.00,
      "archived_at": "2024-10-31T23:59:59Z",
      "created_by": "admin@example.com"
    }
  ],
  "total": 1,
  "limit": 50,
  "offset": 0
}

Get Snapshot Details

GET /archive/api/snapshot/<snapshot_id>

Description: Get complete snapshot data including line items.

Permission: Requires billing, admin, or service token.

Response: 

{
  "snapshot": {
    "id": 123,
    "company_account_number": "620547",
    "company_name": "Acme Corporation",
    "invoice_number": "620547-202410",
    "billing_year": 2024,
    "billing_month": 10,
    "invoice_date": "2024-10-31",
    "due_date": "2024-11-30",
    "billing_plan": "Gold MSP Plan",
    "contract_term": "1 Year",
    "support_level": "Billed Hourly",
    "total_amount": 4275.00,
    "total_user_charges": 375.00,
    "total_asset_charges": 1875.00,
    "total_backup_charges": 150.00,
    "total_ticket_charges": 1875.00,
    "user_count": 25,
    "asset_count": 23,
    "billable_hours": 12.5,
    "archived_at": "2024-10-31T23:59:59Z",
    "created_by": "admin@example.com"
  },
  "line_items": [
    {
      "line_type": "user",
      "item_name": "John Doe",
      "description": "User: John Doe (Paid)",
      "quantity": 1.00,
      "rate": 15.00,
      "amount": 15.00
    },
    {
      "line_type": "asset",
      "item_name": "ACME-PC-001",
      "description": "Workstation: ACME-PC-001",
      "quantity": 1.00,
      "rate": 75.00,
      "amount": 75.00
    }
  ]
}

Create Snapshot

POST /archive/api/snapshot/create
Content-Type: application/json

{
  "account_number": "620547",
  "year": 2024,
  "month": 10,
  "notes": "Monthly billing snapshot"
}

Description: Create billing snapshot for a company/period.

Permission: Requires billing or admin permission level.

Request Body: - account_number (required) - Company account number - year (required) - Billing year - month (required) - Billing month - notes (optional) - Notes about snapshot

Response (Success): 

{
  "success": true,
  "invoice_number": "620547-202410",
  "message": "Snapshot created successfully"
}

Response (Already Exists): 

{
  "success": false,
  "message": "This bill has already been archived",
  "invoice_number": "620547-202410"
}

Bulk Snapshot Creation

POST /archive/api/snapshot/bulk
Content-Type: application/json

{
  "year": 2024,
  "month": 10,
  "account_numbers": ["620547", "987654"],
  "notes": "Automated month-end snapshot"
}

Description: Create snapshots for multiple companies at once.

Permission: Requires billing or admin permission level.

Request Body: - year (required) - Billing year - month (required) - Billing month - account_numbers (optional) - List of account numbers (null = all companies) - notes (optional) - Notes for all snapshots

Response: 

{
  "job_id": "job-uuid-12345",
  "status": "running",
  "total_companies": 2,
  "message": "Bulk snapshot job started"
}

Check Job Status: 

GET /archive/api/snapshot/job/<job_id>

Response: 

{
  "id": "job-uuid-12345",
  "job_type": "bulk",
  "status": "completed",
  "target_year": 2024,
  "target_month": 10,
  "total_companies": 2,
  "completed_companies": 2,
  "failed_companies": 0,
  "started_at": "2024-10-31T14:00:00Z",
  "completed_at": "2024-10-31T14:02:30Z",
  "output": {
    "success": [
      "620547-202410",
      "987654-202410"
    ],
    "failed": []
  }
}

Configuration

Database Configuration

File: instance/ledger.conf

Format: INI-style configuration (use RawConfigParser)

[database]
connection_string = postgresql://user:password@localhost/ledger_db

Environment Fallback: If config file doesn't exist, uses SQLite at instance/ledger.db

Environment Variables

File: .flaskenv (auto-generated by Helm's config_manager.py)

# Core Service (for JWT validation)
CORE_SERVICE_URL=http://localhost:5000

# Helm Service (for centralized logging)
HELM_SERVICE_URL=http://localhost:5004

# Service Identity
SERVICE_NAME=ledger

# Logging
LOG_LEVEL=INFO
ENABLE_JSON_LOGGING=true

# Flask Secret Key
SECRET_KEY=random-secret-key-here

Services Configuration

File: services.json (symlink to ../hivematrix-helm/services.json)

Format: JSON mapping service names to URLs

{
  "core": "http://localhost:5000",
  "codex": "http://localhost:5010",
  "helm": "http://localhost:5004"
}

Usage: call_service('codex', '/api/companies') looks up Codex URL from this file.

Installation & Setup

Prerequisites

  1. PostgreSQL 12+ installed and running
  2. Python 3.8+ with pip
  3. Codex service running (required for billing data)
  4. Core service running (required for JWT authentication)

Install Ledger

Via Helm: 

cd hivematrix-helm
source pyenv/bin/activate
python install_manager.py install ledger
./start.sh

Manual: 

cd hivematrix-ledger

# Create virtual environment
python3 -m venv pyenv
source pyenv/bin/activate

# Install dependencies
pip install -r requirements.txt

# Symlink services.json
ln -sf ../hivematrix-helm/services.json services.json

# Run interactive setup
python init_db.py

Interactive Setup (init_db.py)

The setup wizard prompts for:

1. PostgreSQL Configuration - Host: localhost - Port: 5432 - Database name: ledger_db - Username: postgres - Password: Your PostgreSQL password - Tests connection before saving

2. Database Initialization - Creates all tables (overrides, snapshots, etc.) - No seed data needed (fetches from Codex)

3. Configuration Sync - Saves to instance/ledger.conf - Updates Helm's master_config.json - Syncs to .flaskenv

First-Time Setup

After installation:

1. Verify Codex is Running: 

cd hivematrix-helm
python cli.py status codex

2. Verify Codex Has Data: 

# Check companies exist
curl -H "Authorization: Bearer $(python create_test_token.py 2>/dev/null)" \
  http://localhost:5010/codex/api/companies | jq

3. Start Ledger: 

cd hivematrix-helm
python cli.py start ledger

4. Access Dashboard: - Direct: http://localhost:5030/ - Via Nexus: https://your-server/ledger/

Development

Running Locally

Development Server: 

cd hivematrix-ledger
source pyenv/bin/activate
python run.py

Development Mode: 

# run.py
if __name__ == '__main__':
    app.run(host='127.0.0.1', port=5030, debug=True)  # Enable debug mode

Code Structure

hivematrix-ledger/
├── app/
│   ├── __init__.py              # Flask app setup, load config
│   ├── routes.py                # UI routes (dashboard, clients, settings)
│   ├── api_routes.py            # Override management API
│   ├── admin_routes.py          # Admin functions
│   ├── auth.py                  # @token_required, @billing_required
│   ├── billing_engine.py        # Core billing calculation logic
│   ├── codex_client.py          # Fetch data from Codex API
│   ├── invoice_generator.py    # Generate CSV invoices
│   ├── service_client.py        # Service-to-service call helper
│   ├── middleware.py            # PrefixMiddleware for Nexus proxy
│   ├── error_responses.py       # RFC 7807 error handlers
│   ├── structured_logger.py     # JSON logging with correlation IDs
│   ├── helm_logger.py           # Centralized logging to Helm
│   ├── rate_limit_key.py        # Per-user rate limiting
│   ├── version.py               # Git-based version generation
│   ├── archive/
│   │   ├── __init__.py
│   │   ├── routes.py            # Archive API endpoints
│   │   └── snapshot.py          # Snapshot creation (replaces archive_client)
│   └── templates/
│       ├── dashboard.html       # Main dashboard
│       ├── clients.html         # Client list
│       ├── client_details.html  # Billing breakdown
│       ├── client_settings.html # Override management
│       ├── error.html           # Error page
│       └── archive/             # Archive UI templates
├── instance/
│   └── ledger.conf              # Database config
├── models.py                    # All database models
├── extensions.py                # db = SQLAlchemy()
├── init_db.py                   # Interactive setup wizard
├── run.py                       # Application entry point
├── health_check.py              # Health check library
├── requirements.txt             # Python dependencies
├── .flaskenv                    # Environment variables (auto-generated)
└── services.json                # Symlink to Helm's registry

Key Components

billing_engine.py: - get_billing_data_for_client() - Main calculation function - Fetches data from Codex - Applies billing plan rates - Applies all overrides - Calculates backup charges with overage - Calculates ticket hour charges - Adds custom line items - Returns complete billing breakdown

codex_client.py: - get_company_data() - Fetch company by account number - get_all_companies() - Fetch all companies - get_all_companies_with_details() - Bulk fetch (optimized) - get_billing_plan_from_codex() - Fetch plan rates - get_company_tickets() - Fetch tickets for billing period

invoice_generator.py: - generate_invoice_csv() - Create QuickBooks-compatible CSV - generate_invoice_number() - Format: ACCOUNT-YYYYMM - generate_bulk_invoices_zip() - ZIP of all invoices - get_invoice_summary() - Summary without itemization

archive/snapshot.py: - create_snapshot() - Create billing snapshot (replaces HTTP call to Archive) - check_if_archived() - Check if bill already archived - Direct database writes instead of service calls - Stores complete billing calculation as JSON

Adding Custom Billing Logic

Example: Add Per-Switch Discount

1. Add Override Field (models.py): 

class ClientBillingOverride(db.Model):
    # ... existing fields ...

    override_switch_discount_enabled = db.Column(db.Boolean, default=False)
    switch_discount_percentage = db.Column(db.Numeric(5, 2))  # e.g., 10.00 for 10%

2. Update Billing Engine (billing_engine.py): 

# In get_billing_data_for_client()

# Apply switch discount if enabled
if rate_overrides and rate_overrides.override_switch_discount_enabled:
    discount_pct = float(rate_overrides.switch_discount_percentage or 0)

    for asset in asset_charges:
        if asset['type'] == 'Switch':
            original_cost = asset['cost']
            discount_amount = original_cost * (discount_pct / 100)
            asset['cost'] = original_cost - discount_amount
            asset['discount_applied'] = discount_amount

3. Update UI (client_settings.html): 

<div class="form-group">
  <label>
    <input type="checkbox" name="override_switch_discount_enabled">
    Enable Switch Discount
  </label>
  <input type="number" name="switch_discount_percentage"
         placeholder="10.00" step="0.01">
  <span>% discount on all switches</span>
</div>

4. Update Route Handler (routes.py): 

if 'override_switch_discount_enabled' in request.form:
    rate_overrides.override_switch_discount_enabled = True
    rate_overrides.switch_discount_percentage = request.form.get('switch_discount_percentage')

5. Run Migration: 

# Add migration for new columns
flask db migrate -m "Add switch discount override"
flask db upgrade

Testing

Manual Testing with JWT: 

cd hivematrix-helm
source pyenv/bin/activate

# Generate test token
TOKEN=$(python create_test_token.py 2>/dev/null)

# Test get billing data
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5030/api/billing/620547?year=2024&month=10" | jq

# Test create snapshot
curl -X POST \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"account_number":"620547","year":2024,"month":10}' \
  http://localhost:5030/api/bill/accept | jq

# Test get overrides
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5030/api/overrides/client/620547" | jq

Database Queries: 

psql -U postgres -d ledger_db

-- List all client overrides
SELECT company_account_number, billing_plan,
       per_user_cost, per_workstation_cost
FROM client_billing_overrides;

-- Count snapshots by month
SELECT billing_year, billing_month, COUNT(*) as snapshot_count
FROM billing_snapshots
GROUP BY billing_year, billing_month
ORDER BY billing_year DESC, billing_month DESC;

-- Find clients with custom rates
SELECT company_account_number,
       CASE WHEN override_puc_enabled THEN per_user_cost END as custom_user_cost,
       CASE WHEN override_pwc_enabled THEN per_workstation_cost END as custom_ws_cost
FROM client_billing_overrides
WHERE override_puc_enabled OR override_pwc_enabled;

Monitoring & Logging

Health Check

Endpoint: GET /health

Checks: - Database connectivity - Disk space availability - Core service availability - Codex service availability

Response (200 - Healthy): 

{
  "status": "healthy",
  "timestamp": "2024-11-22T10:30:00Z",
  "service": "ledger",
  "checks": {
    "database": {
      "status": "healthy",
      "message": "Connected to PostgreSQL"
    },
    "disk": {
      "status": "healthy",
      "usage_percent": 45.2,
      "available_gb": 120.5
    },
    "dependencies": {
      "core": {
        "status": "healthy",
        "response_time_ms": 15
      },
      "codex": {
        "status": "healthy",
        "response_time_ms": 45
      }
    }
  }
}

Response (503 - Unhealthy): 

{
  "status": "degraded",
  "timestamp": "2024-11-22T10:30:00Z",
  "service": "ledger",
  "checks": {
    "database": {
      "status": "unhealthy",
      "error": "Connection refused"
    },
    "dependencies": {
      "codex": {
        "status": "unhealthy",
        "error": "Service unavailable"
      }
    }
  }
}

Structured Logging

Log Format: JSON with correlation IDs

Example Log Entry: 

{
  "timestamp": "2024-11-22T10:30:00Z",
  "level": "INFO",
  "service": "ledger",
  "correlation_id": "req-abc123",
  "user": "billing@example.com",
  "message": "Bill archived",
  "extra": {
    "account_number": "620547",
    "invoice_number": "620547-202410",
    "total_amount": 4275.00
  }
}

View Centralized Logs: 

cd hivematrix-helm
source pyenv/bin/activate

# View Ledger logs
python logs_cli.py ledger --tail 50

# Filter by level
python logs_cli.py ledger --level ERROR --tail 100

# Real-time monitoring
watch -n 2 'python logs_cli.py ledger --tail 20'

Rate Limiting

Configuration: - Per-user limits: 10000 requests/hour, 500 requests/minute - Key: JWT subject (sub claim) or IP address fallback - Storage: In-memory (resets on restart)

Rate Limit Headers: 

X-RateLimit-Limit: 500
X-RateLimit-Remaining: 498
X-RateLimit-Reset: 1700654400

Rate Limit Exceeded (429): 

{
  "type": "https://httpstatuses.com/429",
  "title": "Too Many Requests",
  "status": 429,
  "detail": "Rate limit exceeded. Try again later."
}

Metrics to Monitor

Application: - Billing calculation time per company - Number of bills calculated per day - Number of snapshots created per month - Override usage statistics

Database: - Query execution times - Connection pool usage - Snapshot table growth rate - Database size

Business: - Total monthly revenue (sum of all bills) - Average bill per company - Number of archived bills vs. pending - Custom override adoption rate

Troubleshooting

Ledger Shows No Billing Data

Symptom: Dashboard empty, no companies listed

Check Codex Service: 

cd hivematrix-helm
python cli.py status codex

# Verify Codex has data
TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  http://localhost:5010/codex/api/companies | jq

Check services.json: 

cd hivematrix-ledger
cat services.json
# Should contain: {"codex": "http://localhost:5010", ...}

Check Ledger logs: 

cd hivematrix-helm
python logs_cli.py ledger --tail 50 | grep -i codex

Common Issues: - Codex service not running - Codex has no companies synced from PSA - services.json has wrong Codex URL - Network connectivity between services

Billing Calculation Returns Null

Symptom: /api/billing/<account> returns 500 error or null data

Verify Company Exists in Codex: 

TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  http://localhost:5010/codex/api/companies/620547 | jq

Check Billing Plan: 

# Verify company has a billing plan assigned
curl -H "Authorization: Bearer $TOKEN" \
  http://localhost:5010/codex/api/companies/620547 | jq '.billing_plan'

# Verify billing plan exists in Codex
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5010/codex/api/billing-plan?plan_name=Gold+MSP+Plan&contract_term=1+Year" | jq

Common Issues: - Company has no billing plan assigned in Codex - Billing plan name misspelled - Billing plan not defined in Codex - Contract term mismatch (e.g., plan for "1 Year" but company has "Month to Month")

Fix: 1. Log into Codex admin panel 2. Assign billing plan to company 3. Ensure plan exists with correct contract term 4. Retry billing calculation

Backup Charges Are Zero

Symptom: No backup charges despite assets having backup data

Verify Backup Data in Codex: 

TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  http://localhost:5010/codex/api/companies/620547/assets | jq '.[] | {hostname, backup_usage_tb}'

Check Backup Plan Rates: 

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5010/codex/api/billing-plan?plan_name=Gold+MSP+Plan&contract_term=1+Year" | \
  jq '.backup_base_fee_workstation, .backup_per_tb_fee'

Common Issues: - Datto RMM sync hasn't run in Codex - Assets have backup_usage_tb = 0 or null - Billing plan has backup_per_tb_fee = 0 - All usage within included TB (no overage)

Debug Calculation: 

# In billing_engine.py, add debug logging
print(f"Asset {asset['hostname']}: backup_usage={asset.get('backup_usage_tb')} TB")
print(f"Backup charge calculation: base={base_fee}, overage={overage_charge}")

Ticket Hours Not Showing

Symptom: Billable hours = 0 despite tickets existing

Verify Tickets in Codex: 

TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5010/codex/api/companies/620547/tickets?year=2024" | \
  jq '.[] | {ticket_number, total_hours_spent, last_updated_at}'

Check Billing Period: Ledger filters tickets by last_updated_at year/month. Ensure tickets were updated in the billing period.

Check Support Level: If company's support level is "Flat Monthly", ticket hours are calculated but not charged.

Common Issues: - Ticket sync hasn't run in Codex - Tickets don't have total_hours_spent populated - Tickets are from wrong year/month - Support level prevents charging

Cannot Create Snapshot (Already Archived)

Symptom: "This bill has already been archived" error

Check Existing Snapshot: 

psql -U postgres -d ledger_db

SELECT invoice_number, archived_at, created_by
FROM billing_snapshots
WHERE company_account_number = '620547'
  AND billing_year = 2024
  AND billing_month = 10;

Solutions:

1. View Existing Snapshot: 

TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5030/archive/api/snapshots?account_number=620547&year=2024&month=10" | jq

2. Delete Snapshot (if incorrect): 

-- DANGER: Only if snapshot was created in error
DELETE FROM snapshot_line_items WHERE snapshot_id = 123;
DELETE FROM billing_snapshots WHERE id = 123;

3. Create Snapshot for Different Period: Change year/month to period not yet archived.

Overrides Not Being Applied

Symptom: Custom rates not reflected in billing calculation

Verify Override Exists: 

TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5030/api/overrides/client/620547" | jq

Check Override Enabled Flag: 

SELECT company_account_number,
       override_pwc_enabled,
       per_workstation_cost
FROM client_billing_overrides
WHERE company_account_number = '620547';

Common Issues: - Override exists but override_*_enabled = false - Override value is null - Wrong account number (typo) - Caching issue (restart Ledger service)

Debug: Add logging to billing_engine.py: 

if rate_overrides:
    print(f"Found overrides for {account_number}")
    print(f"PWC override enabled: {rate_overrides.override_pwc_enabled}")
    print(f"PWC value: {rate_overrides.per_workstation_cost}")

Custom Line Items Not Appearing

Symptom: Custom line items not in invoice

Verify Line Item Exists: 

SELECT name, monthly_fee, one_off_fee, yearly_fee
FROM custom_line_items
WHERE company_account_number = '620547';

Check Billing Period: - Monthly: Always included if monthly_fee > 0 - One-off: Only in specified one_off_year and one_off_month - Yearly: Only in month specified by yearly_bill_month

Example Debug: 

-- Check one-off for October 2024
SELECT * FROM custom_line_items
WHERE company_account_number = '620547'
  AND one_off_year = 2024
  AND one_off_month = 10;

Slow Billing Calculations

Symptom: Dashboard takes 30+ seconds to load

Enable Query Logging: 

# In app/__init__.py
import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Check Codex Bulk API: Ledger should use /api/companies/bulk for dashboard, not individual company calls.

Optimize: 

# In routes.py dashboard
# Instead of:
for company in get_all_companies():
    billing_data = get_billing_data_for_client(...)  # N+1 queries

# Use:
all_companies_data = get_all_companies_with_details(include_tickets=True, year=2024)
# Single bulk call

Database Indexes: 

-- Add index for faster queries
CREATE INDEX idx_client_overrides_account ON client_billing_overrides(company_account_number);
CREATE INDEX idx_snapshots_lookup ON billing_snapshots(company_account_number, billing_year, billing_month);

Security

Authentication

All endpoints require JWT authentication except: - /health - Public health check

Token Validation: - Validates signature against Core's JWKS endpoint - Checks expiration timestamp - Verifies issuer (hivematrix-core) - Extracts user/service identity

Authorization

Permission Levels: - Admin - Full access to all billing operations - Billing - Access to billing dashboards, calculations, and overrides - Technician - Read-only access to bills - Client - No access (clients don't see Ledger)

Decorators: 

@billing_required  # Requires 'billing' or 'admin' permission
@admin_required    # Requires 'admin' permission only

Data Security

Sensitive Data: - Billing rates (pricing information) - Customer financial data - Contract terms - Billing overrides

Protection: - All data encrypted in transit (HTTPS via Nexus) - Database credentials in instance/ledger.conf (not in code) - JWT tokens for all API access - Per-user rate limiting prevents abuse - No direct database access from external networks

Billing Snapshot Immutability: - BillingSnapshot records are append-only - No UPDATE endpoint (intentional) - DELETE only via admin SQL access - Audit trail via created_by field

Network Security

Localhost Binding: 

# run.py
app.run(host='127.0.0.1', port=5030)  # Only accessible from localhost

Access Control: - Only Nexus can access Ledger (via reverse proxy) - Firewall blocks direct port 5030 access from internet - All billing operations require authentication

Backup & Recovery

Export Billing Data

Via API: 

# Export all snapshots for a year
TOKEN=$(python create_test_token.py 2>/dev/null)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:5030/archive/api/snapshots?year=2024&limit=1000" \
  -o billing_snapshots_2024.json

Via Database: 

# Dump entire database
pg_dump -U postgres ledger_db > ledger_backup.sql

# Dump only snapshots
pg_dump -U postgres -t billing_snapshots -t snapshot_line_items ledger_db \
  > snapshots_backup.sql

Backup Schedule

Recommended Cron Jobs:

# Backup database daily at 3 AM
0 3 * * * pg_dump -U postgres ledger_db | gzip > /backup/ledger/ledger_$(date +\%Y\%m\%d).sql.gz

# Clean old backups (keep 90 days)
0 4 * * * find /backup/ledger/ -name "*.sql.gz" -mtime +90 -delete

Automated Snapshot Creation:

-- Configure monthly auto-archival
INSERT INTO scheduled_snapshots (enabled, day_of_month, hour, snapshot_previous_month, snapshot_all_companies, created_at)
VALUES (true, 1, 2, true, true, datetime('now'));

Then run scheduler (future feature): 

python scheduler.py  # Checks scheduled_snapshots and runs jobs

Restore from Backup

Restore Database: 

# Stop Ledger service
cd hivematrix-helm
python cli.py stop ledger

# Drop and recreate database
psql -U postgres -c "DROP DATABASE IF EXISTS ledger_db;"
psql -U postgres -c "CREATE DATABASE ledger_db;"

# Restore from backup
psql -U postgres ledger_db < ledger_backup.sql

# Start Ledger service
python cli.py start ledger

Restore Snapshots Only: 

# Restore specific tables
psql -U postgres ledger_db < snapshots_backup.sql

Integration with Other Services

Codex Integration

Purpose: Ledger fetches ALL base data from Codex (single source of truth)

Data Flow: 

Codex → Ledger
- Companies (account number, name, billing plan)
- Users/Contacts (for per-user billing)
- Assets (for per-asset billing, backup usage)
- Tickets (for billable hours)
- Billing Plans (rates and pricing)

API Calls Made to Codex:

# Get all companies (bulk)
response = call_service('codex', '/api/companies/bulk?include_tickets=true&year=2024')

# Get single company with details
response = call_service('codex', '/api/companies/620547')
response = call_service('codex', '/api/companies/620547/assets')
response = call_service('codex', '/api/companies/620547/users')
response = call_service('codex', '/api/companies/620547/tickets?year=2024')

# Get billing plan
response = call_service('codex', '/api/billing-plan?plan_name=Gold+MSP+Plan&contract_term=1+Year')

Performance Optimization:

Ledger uses Codex's bulk API (/api/companies/bulk) to fetch all companies + assets + users + tickets in a single call, reducing API overhead from O(N) to O(1).

Core Integration

Purpose: JWT authentication

Flow: 1. User accesses Ledger via Nexus 2. Nexus includes JWT in request 3. Ledger validates JWT against Core's JWKS endpoint 4. Ledger extracts user identity and permissions 5. Ledger authorizes based on permission level

Service-to-Service: 

# Ledger calling another service
# 1. Request service token from Core
response = requests.post(
    f"{CORE_SERVICE_URL}/service-token",
    json={'calling_service': 'ledger'}
)
service_token = response.json()['token']

# 2. Use token to call Codex
response = requests.get(
    f"{CODEX_URL}/api/companies",
    headers={'Authorization': f'Bearer {service_token}'}
)

Nexus Integration

Purpose: Frontend proxy and global CSS injection

Proxy Configuration: 

# In Nexus
@app.route('/ledger/', defaults={'path': ''})
@app.route('/ledger/<path:path>')
def proxy_ledger(path):
    return proxy_service('ledger', path, inject_html=True)

CSS Injection:

Nexus injects global.css into all Ledger HTML responses.

URL Handling:

Ledger uses PrefixMiddleware (not ProxyFix) to handle /ledger/ prefix: 

# app/middleware.py
app.wsgi_app = PrefixMiddleware(app.wsgi_app, prefix='/ledger')

This ensures url_for('clients') generates /ledger/clients (correct) not /clients (404).

Performance Optimization

Database Connection Pooling

Configuration: 

# app/__init__.py
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 10,           # Max connections
    'pool_recycle': 3600,      # Recycle after 1 hour
    'pool_pre_ping': True,     # Test before use
    'max_overflow': 5,         # Extra connections if needed
}

Monitoring: 

from sqlalchemy import event

@event.listens_for(db.engine, "connect")
def receive_connect(dbapi_conn, connection_record):
    print(f"New connection: {dbapi_conn}")

Bulk API Usage

Instead of N+1 Queries: 

# BAD: N API calls
companies = get_all_companies()
for company in companies:
    assets = get_company_assets(company['account_number'])  # N calls
    users = get_company_users(company['account_number'])    # N calls

Use Bulk API: 

# GOOD: 1 API call
all_data = get_all_companies_with_details(include_tickets=True, year=2024)
# Returns companies with assets, users, tickets already populated

Performance Gain: Dashboard load time: 30s → 2s

Caching (Future Enhancement)

Add Redis caching for billing plans (rarely change): 

import redis
cache = redis.Redis(host='localhost', port=6379)

def get_billing_plan_from_codex(plan_name, contract_term):
    cache_key = f'plan:{plan_name}:{contract_term}'
    cached = cache.get(cache_key)
    if cached:
        return json.loads(cached)

    # Fetch from Codex
    plan = ...

    # Cache for 1 hour
    cache.setex(cache_key, 3600, json.dumps(plan))
    return plan

Database Indexing

Recommended Indexes: 

-- Client overrides lookup
CREATE INDEX idx_client_overrides_account
ON client_billing_overrides(company_account_number);

-- Snapshot queries
CREATE INDEX idx_snapshots_account_period
ON billing_snapshots(company_account_number, billing_year, billing_month);

-- Line item searches
CREATE INDEX idx_line_items_type
ON snapshot_line_items(line_type, item_name);

Changelog

Version 2.0.0 (2024-11-22)

  • Merged Archive service into Ledger for simplified architecture
  • Added direct database snapshot creation (replaces HTTP calls)
  • Comprehensive billing calculation engine
  • Flexible override system (client, asset, user, manual, custom)
  • CSV invoice generation (QuickBooks-compatible)
  • Bulk operations for month-end processing
  • Dashboard analytics with real-time metrics
  • Per-user rate limiting (10000/hour, 500/minute)
  • Structured JSON logging with correlation IDs
  • RFC 7807 error responses
  • Health check monitoring
  • Swagger/OpenAPI documentation
  • PostgreSQL with connection pooling
  • Integration with Codex for all base data
  • Billing snapshot archival system

See Also

Architecture & Design

Configuration & Setup

External Integrations

  • QuickBooks: CSV invoice export format
  • Codex API: /api/companies, /api/assets, /api/users, /api/tickets
  • Billing Plans: Contract terms, hourly rates, asset pricing

Questions or issues? Check the troubleshooting section or file an issue on GitHub.