Asset system overview

The asset system is the core component for tracking various types of financial assets. It uses a polymorphic design pattern where the asset table serves as the base table, with specific asset types having their own dedicated tables that reference it.

Asset table

Definition

The asset table is the foundation for all asset types in the system. It stores common attributes shared across all asset types while specific details are stored in type-specific tables.

Columns

Primary key

• asset__pkey on (id)

Unique constraints

• asset__id_key on (id)

Foreign keys

• asset__space_id_fkey references space(id)

Asset Types

The system supports various types of assets through the asset_types enum:

enum asset_types {
    'account'
    'company_equity'
    'liability'
    'private_fund'
    'private_investment'
    'real_estate'
}

Asset status

Assets can have different statuses managed through the asset_status enum:

enum asset_status {
    'draft'
    'active'
    'archived'
    'deleted'
}

Views

asset_standing

The asset_standing view provides the current standing/value of assets. It combines information from various asset-specific tables and includes:

• Current valuations in both local and preferred currencies
• Asset metadata (names, logos, institutions)
• Type-specific details
• Exchange rate conversions

asset_value_history

The asset_value_history view maintains a historical record of asset values by:

• Tracking transactions and value updates
• Computing running balances
• Supporting different asset types
• Maintaining currency information

Filters

The filters JSONB column supports several flags:

• deleted: Indicates if the asset is soft-deleted
• hidden: Controls asset visibility
• in_net_worth: Determines if the asset should be included in net worth calculations

Triggers

event_delete_on_asset_soft_delete

Triggers when an asset is soft-deleted (when deleted_at is set). It:

• Updates the asset's filters
• Sets deleted flag to true
• Sets hidden flag to true
• Sets in_net_worth to false
• Updates the updated_at timestamp

Functions

Update ownership share

function update_asset_ownership_share(p_asset_id uuid, p_ownership_share double precision)

Updates an asset's ownership share percentage:

• Validates the share is between 0.01 and 100
• Updates the asset's ownership_share
• Raises an exception if the asset is not found

Update asset visibility

function update_asset_hidden(asset_id uuid, hidden_value boolean)

Updates the asset's hidden status in the filters JSONB:

• Sets the hidden flag in filters
• Updates the updated_at timestamp
• Returns the updated asset record

Update net worth inclusion

function update_asset_in_net_worth(asset_id uuid, in_net_worth_value boolean)

Updates whether the asset should be included in net worth calculations:

• Sets the in_net_worth flag in filters
• Returns the updated asset record

Usage notes

1. Soft deletion

   • Use the soft deletion mechanism (deleted_at) rather than hard deleting records
   • The trigger will automatically update relevant filters

2. Value tracking

   • Use the asset_standing view for current values
   • Use asset_value_history for historical analysis
   • Values are tracked in both local and preferred currencies