Portfolio

Portfolio management, holdings, performance tracking, and asset information.

11 tables in this group.

portfolios

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`user_id`	`uuid`	No
`name`	`text`	No
`description`	`text`	Yes
`total_value`	`numeric`	Yes	0
`currency`	`text`	Yes	'USD'::text
`active`	`bool`	Yes	true
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

Users can create their own portfolios — INSERT for {public}
Users can delete their own portfolios — DELETE for {public}
Users can update their own portfolios — UPDATE for {public}
Users can view their own portfolios — SELECT for {public}

Indexes:

portfolios_pkey
portfolios_user_id_name_key

portfolio_holdings

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`portfolio_id`	`uuid`	No
`symbol`	`text`	No
`quantity`	`numeric`	No	0
`average_cost`	`numeric`	Yes
`current_price`	`numeric`	Yes
`total_value`	`numeric`	Yes
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

Users can create holdings in their portfolios — INSERT for {public}
Users can delete holdings in their portfolios — DELETE for {public}
Users can update holdings in their portfolios — UPDATE for {public}
Users can view holdings of their portfolios — SELECT for {public}

Indexes:

portfolio_holdings_pkey
portfolio_holdings_portfolio_id_symbol_key

portfolio_positions

Column	Type	Nullable
`id`	`uuid`	No
`portfolio_id`	`uuid`	No
`asset_symbol`	`varchar(20)`	No
`asset_name`	`varchar(100)`	Yes
`asset_category`	`varchar(20)`	Yes
`quantity`	`numeric`	No
`available_quantity`	`numeric`	Yes
`locked_quantity`	`numeric`	Yes
`average_cost_usd`	`numeric`	No
`current_price_usd`	`numeric`	No
`current_value_usd`	`numeric`	No
`total_cost_usd`	`numeric`	Yes
`unrealized_pnl_usd`	`numeric`	Yes
`realized_pnl_usd`	`numeric`	Yes
`unrealized_pnl_percent`	`numeric`	Yes
`allocation_percent`	`numeric`	No
`target_allocation_percent`	`numeric`	No
`allocation_drift`	`numeric`	Yes
`status`	`varchar(20)`	Yes
`is_rebalance_target`	`bool`	Yes
`first_acquired_at`	`timestamp`	Yes
`last_updated_at`	`timestamp`	No
`last_trade_at`	`timestamp`	Yes
`position_metadata`	`json`	Yes

RLS Policies:

portfolio_positions_service_all — ALL for {public}

Indexes:

idx_portfolio_positions_portfolio_id
ix_portfolio_positions_asset_symbol
portfolio_positions_pkey

portfolio_value_history

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`portfolio_id`	`uuid`	No
`recorded_at`	`timestamptz`	No	now()
`total_value`	`numeric`	Yes
`cash_value`	`numeric`	Yes
`invested_value`	`numeric`	Yes
`pnl`	`numeric`	Yes

RLS Policies:

Users can insert own portfolio value history — INSERT for {public}
Users can view own portfolio value history — SELECT for {public}

Indexes:

idx_portfolio_value_history_portfolio_recorded
portfolio_value_history_pkey

portfolio_performance_metrics

Column	Type	Nullable
`metrics_id`	`uuid`	No
`portfolio_id`	`uuid`	No
`calculation_date`	`date`	No
`total_return_percent`	`numeric`	No
`daily_return_percent`	`numeric`	No
`volatility_annualized`	`numeric`	No
`sharpe_ratio`	`numeric`	No
`max_drawdown_percent`	`numeric`	No
`current_drawdown_percent`	`numeric`	No
`var_95_percent`	`numeric`	No
`benchmark_btc_outperformance`	`numeric`	No
`benchmark_market_outperformance`	`numeric`	No
`trades_executed_count`	`int4`	No
`total_fees_paid_usd`	`numeric`	No
`created_at`	`timestamp`	No

RLS Policies:

portfolio_performance_metrics_service_all — ALL for {public}

Indexes:

idx_portfolio_performance_metrics_portfolio_id
ix_portfolio_performance_metrics_calculation_date
portfolio_performance_metrics_pkey

assets

Column	Type	Nullable
`id`	`uuid`	No
`portfolio_id`	`uuid`	No
`symbol`	`varchar(20)`	No
`name`	`varchar(100)`	Yes
`asset_type`	`varchar(20)`	Yes
`quantity`	`numeric`	No
`available_quantity`	`numeric`	Yes
`locked_quantity`	`numeric`	Yes
`average_cost`	`numeric`	Yes
`current_price`	`numeric`	Yes
`market_value`	`numeric`	Yes
`unrealized_pnl`	`numeric`	Yes
`realized_pnl`	`numeric`	Yes
`total_cost`	`numeric`	Yes
`target_allocation`	`numeric`	Yes
`current_allocation`	`numeric`	Yes
`first_acquired_at`	`timestamp`	Yes
`last_updated_at`	`timestamp`	Yes
`asset_metadata`	`json`	Yes

RLS Policies:

assets_public_select — SELECT for {public}
assets_service_delete — DELETE for {public}
assets_service_insert — INSERT for {public}
assets_service_update — UPDATE for {public}

Indexes:

assets_pkey
idx_assets_portfolio_id
ix_assets_symbol

asset_info

Column	Type	Nullable
`id`	`uuid`	No
`symbol`	`varchar(20)`	No
`name`	`varchar(100)`	No
`category`	`varchar(20)`	Yes
`market_cap_usd`	`numeric`	Yes
`circulating_supply`	`numeric`	Yes
`total_supply`	`numeric`	Yes
`max_supply`	`numeric`	Yes
`is_tradeable`	`bool`	Yes
`min_trade_amount`	`numeric`	Yes
`price_precision`	`int4`	Yes
`quantity_precision`	`int4`	Yes
`volatility_score`	`numeric`	Yes
`liquidity_score`	`numeric`	Yes
`sentiment_score`	`numeric`	Yes
`coingecko_id`	`varchar(100)`	Yes
`coinmarketcap_id`	`varchar(100)`	Yes
`contract_address`	`varchar(100)`	Yes
`blockchain`	`varchar(50)`	Yes
`listed_at`	`timestamp`	Yes
`last_updated_at`	`timestamp`	Yes
`asset_metadata`	`json`	Yes

RLS Policies:

asset_info_public_select — SELECT for {public}
asset_info_service_delete — DELETE for {public}
asset_info_service_insert — INSERT for {public}
asset_info_service_update — UPDATE for {public}

Indexes:

asset_info_pkey
ix_asset_info_symbol

benchmarks

Column	Type	Nullable
`id`	`uuid`	No
`name`	`varchar(100)`	No
`symbol`	`varchar(20)`	No
`description`	`varchar(500)`	Yes
`benchmark_type`	`varchar(20)`	Yes
`data_provider`	`varchar(50)`	Yes
`external_id`	`varchar(100)`	Yes
`is_active`	`bool`	Yes
`created_at`	`timestamp`	No
`updated_at`	`timestamp`	Yes
`benchmark_metadata`	`json`	Yes

RLS Policies:

benchmarks_service_all — ALL for {public}

Indexes:

benchmarks_name_key
benchmarks_pkey
benchmarks_symbol_key

performance_snapshots

Column	Type	Nullable
`id`	`uuid`	No
`portfolio_id`	`uuid`	No
`snapshot_date`	`date`	No
`period`	`varchar(20)`	Yes
`portfolio_value`	`numeric`	No
`portfolio_value_previous`	`numeric`	Yes
`cash_value`	`numeric`	Yes
`invested_value`	`numeric`	Yes
`period_return`	`numeric`	Yes
`cumulative_return`	`numeric`	Yes
`annualized_return`	`numeric`	Yes
`volatility`	`numeric`	Yes
`max_drawdown`	`numeric`	Yes
`current_drawdown`	`numeric`	Yes
`sharpe_ratio`	`numeric`	Yes
`sortino_ratio`	`numeric`	Yes
`calmar_ratio`	`numeric`	Yes
`information_ratio`	`numeric`	Yes
`beta`	`numeric`	Yes
`alpha`	`numeric`	Yes
`tracking_error`	`numeric`	Yes
`created_at`	`timestamp`	No
`updated_at`	`timestamp`	Yes
`benchmark_return`	`numeric`	Yes
`risk_free_rate`	`numeric`	Yes
`snapshot_metadata`	`json`	Yes

RLS Policies:

performance_snapshots_service_all — ALL for {public}

Indexes:

idx_performance_snapshots_portfolio_id
ix_performance_snapshots_snapshot_date
performance_snapshots_pkey

rebalancing_events

Column	Type	Nullable
`event_id`	`uuid`	No
`portfolio_id`	`uuid`	No
`strategy_id`	`uuid`	No
`trigger_reason`	`varchar(30)`	No
`trades_executed`	`json`	No
`execution_latency_ms`	`int4`	No
`total_cost_usd`	`numeric`	No
`expected_benefit`	`numeric`	No
`actual_benefit`	`numeric`	Yes
`executed_at`	`timestamp`	No

RLS Policies:

rebalancing_events_service_all — ALL for {public}

Indexes:

ix_rebalancing_events_portfolio_id
ix_rebalancing_events_strategy_id
ix_rebalancing_events_trigger_reason
rebalancing_events_pkey

rebalancing_strategies

Column	Type	Nullable
`strategy_id`	`uuid`	No
`portfolio_id`	`uuid`	No
`strategy_type`	`varchar(30)`	No
`threshold_percent`	`numeric`	No
`min_trade_amount_usd`	`numeric`	No
`sentiment_weight`	`numeric`	No
`volatility_adjustment`	`bool`	No
`is_enabled`	`bool`	No
`performance_score`	`numeric`	Yes
`last_optimization_at`	`timestamp`	Yes
`created_at`	`timestamp`	No
`updated_at`	`timestamp`	No

RLS Policies:

rebalancing_strategies_service_all — ALL for {public}

Indexes:

ix_rebalancing_strategies_portfolio_id
ix_rebalancing_strategies_strategy_type
rebalancing_strategies_pkey

Portfolio

On this page