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>
109 lines
4.2 KiB
PL/PgSQL
109 lines
4.2 KiB
PL/PgSQL
-- =====================================================
|
|
-- PORTFOLIO SCHEMA - PORTFOLIO GOALS TABLE
|
|
-- =====================================================
|
|
-- Description: User financial goals and progress tracking
|
|
-- Schema: portfolio
|
|
-- Author: Database Agent
|
|
-- Date: 2026-01-25
|
|
-- =====================================================
|
|
|
|
CREATE TABLE portfolio.portfolio_goals (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
portfolio_id UUID REFERENCES portfolio.portfolios(id) ON DELETE SET NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Goal targets
|
|
target_amount DECIMAL(20, 2) NOT NULL,
|
|
current_amount DECIMAL(20, 2) NOT NULL DEFAULT 0,
|
|
target_date DATE NOT NULL,
|
|
monthly_contribution DECIMAL(20, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Progress tracking
|
|
progress DECIMAL(5, 2) NOT NULL DEFAULT 0,
|
|
projected_completion_date DATE,
|
|
months_remaining INTEGER,
|
|
required_monthly_contribution DECIMAL(20, 2),
|
|
|
|
-- Status
|
|
status portfolio.goal_status NOT NULL DEFAULT 'active',
|
|
completed_at TIMESTAMPTZ,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_target_amount_positive CHECK (target_amount > 0),
|
|
CONSTRAINT chk_current_amount_positive CHECK (current_amount >= 0),
|
|
CONSTRAINT chk_progress_range CHECK (progress >= 0 AND progress <= 100),
|
|
CONSTRAINT chk_monthly_contribution_positive CHECK (monthly_contribution >= 0)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_portfolio_goals_user_id ON portfolio.portfolio_goals(user_id);
|
|
CREATE INDEX idx_portfolio_goals_portfolio_id ON portfolio.portfolio_goals(portfolio_id);
|
|
CREATE INDEX idx_portfolio_goals_status ON portfolio.portfolio_goals(status);
|
|
CREATE INDEX idx_portfolio_goals_target_date ON portfolio.portfolio_goals(target_date);
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE portfolio.portfolio_goals IS 'User financial goals with progress tracking';
|
|
COMMENT ON COLUMN portfolio.portfolio_goals.target_amount IS 'Target amount to save/invest in USD';
|
|
COMMENT ON COLUMN portfolio.portfolio_goals.current_amount IS 'Current progress toward the goal';
|
|
COMMENT ON COLUMN portfolio.portfolio_goals.progress IS 'Percentage progress (0-100)';
|
|
COMMENT ON COLUMN portfolio.portfolio_goals.projected_completion_date IS 'Estimated date of goal completion based on contributions';
|
|
COMMENT ON COLUMN portfolio.portfolio_goals.required_monthly_contribution IS 'Monthly contribution needed to meet target on time';
|
|
|
|
-- Trigger for updated_at
|
|
CREATE TRIGGER trg_portfolio_goals_updated_at
|
|
BEFORE UPDATE ON portfolio.portfolio_goals
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION portfolio.update_portfolio_updated_at();
|
|
|
|
-- Function to update goal progress
|
|
CREATE OR REPLACE FUNCTION portfolio.update_goal_progress()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Calculate progress percentage
|
|
IF NEW.target_amount > 0 THEN
|
|
NEW.progress := LEAST(100, (NEW.current_amount / NEW.target_amount) * 100);
|
|
END IF;
|
|
|
|
-- Calculate months remaining
|
|
NEW.months_remaining := GREATEST(0,
|
|
EXTRACT(YEAR FROM AGE(NEW.target_date, CURRENT_DATE)) * 12 +
|
|
EXTRACT(MONTH FROM AGE(NEW.target_date, CURRENT_DATE))
|
|
)::INTEGER;
|
|
|
|
-- Calculate required monthly contribution
|
|
IF NEW.months_remaining > 0 THEN
|
|
NEW.required_monthly_contribution := GREATEST(0,
|
|
(NEW.target_amount - NEW.current_amount) / NEW.months_remaining
|
|
);
|
|
ELSE
|
|
NEW.required_monthly_contribution := NEW.target_amount - NEW.current_amount;
|
|
END IF;
|
|
|
|
-- Calculate projected completion date based on current contributions
|
|
IF NEW.monthly_contribution > 0 AND NEW.current_amount < NEW.target_amount THEN
|
|
NEW.projected_completion_date := CURRENT_DATE +
|
|
(CEIL((NEW.target_amount - NEW.current_amount) / NEW.monthly_contribution) * INTERVAL '1 month')::INTERVAL;
|
|
END IF;
|
|
|
|
-- Auto-complete if goal reached
|
|
IF NEW.current_amount >= NEW.target_amount AND NEW.status = 'active' THEN
|
|
NEW.status := 'completed';
|
|
NEW.completed_at := NOW();
|
|
NEW.progress := 100;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_portfolio_goals_progress
|
|
BEFORE INSERT OR UPDATE ON portfolio.portfolio_goals
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION portfolio.update_goal_progress();
|