3fbb1c21e6
- Added portfolio schema to 01-schemas.sql - Created enums: risk_profile, goal_status, rebalance_action, allocation_status - Created tables: portfolios, portfolio_allocations, portfolio_goals - Created tables: rebalance_history, portfolio_snapshots - Added triggers for updated_at and goal progress calculations - Added indexes for performance Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
67 lines
2.5 KiB
PL/PgSQL
67 lines
2.5 KiB
PL/PgSQL
-- =====================================================
|
|
-- PORTFOLIO SCHEMA - PORTFOLIOS TABLE
|
|
-- =====================================================
|
|
-- Description: User portfolios with asset allocations
|
|
-- Schema: portfolio
|
|
-- Author: Database Agent
|
|
-- Date: 2026-01-25
|
|
-- =====================================================
|
|
|
|
CREATE TABLE portfolio.portfolios (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
risk_profile portfolio.risk_profile NOT NULL DEFAULT 'moderate',
|
|
|
|
-- Portfolio values (updated by triggers/jobs)
|
|
total_value DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
total_cost DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
unrealized_pnl DECIMAL(20, 8) NOT NULL DEFAULT 0,
|
|
unrealized_pnl_percent DECIMAL(10, 4) NOT NULL DEFAULT 0,
|
|
|
|
-- Statistics
|
|
day_change_percent DECIMAL(10, 4) DEFAULT 0,
|
|
week_change_percent DECIMAL(10, 4) DEFAULT 0,
|
|
month_change_percent DECIMAL(10, 4) DEFAULT 0,
|
|
all_time_change_percent DECIMAL(10, 4) DEFAULT 0,
|
|
|
|
-- Metadata
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
last_rebalanced_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_portfolios_user_id ON portfolio.portfolios(user_id);
|
|
CREATE INDEX idx_portfolios_risk_profile ON portfolio.portfolios(risk_profile);
|
|
CREATE INDEX idx_portfolios_is_active ON portfolio.portfolios(is_active) WHERE is_active = true;
|
|
|
|
-- Only one primary portfolio per user
|
|
CREATE UNIQUE INDEX idx_portfolios_user_primary
|
|
ON portfolio.portfolios(user_id)
|
|
WHERE is_primary = true;
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE portfolio.portfolios IS 'User investment portfolios with configurable allocations';
|
|
COMMENT ON COLUMN portfolio.portfolios.risk_profile IS 'User risk tolerance: conservative, moderate, aggressive';
|
|
COMMENT ON COLUMN portfolio.portfolios.total_value IS 'Current total value of all allocations in USD';
|
|
COMMENT ON COLUMN portfolio.portfolios.unrealized_pnl IS 'Unrealized profit/loss = total_value - total_cost';
|
|
COMMENT ON COLUMN portfolio.portfolios.is_primary IS 'Only one portfolio can be primary per user';
|
|
|
|
-- Trigger for updated_at
|
|
CREATE OR REPLACE FUNCTION portfolio.update_portfolio_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_portfolios_updated_at
|
|
BEFORE UPDATE ON portfolio.portfolios
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION portfolio.update_portfolio_updated_at();
|