IWM Platform

English

Русский

English

Русский

Theme

Investment Schema ​

Investment Strategies ​

sql
CREATE TABLE investment.strategies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id UUID REFERENCES investment.strategy_categories(id),

    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(200) NOT NULL UNIQUE,

    short_description VARCHAR(500),
    description TEXT,
    terms TEXT,

    risk_level VARCHAR(20) NOT NULL,  -- LOW, MEDIUM, HIGH, VERY_HIGH
    min_amount DECIMAL(20,2) NOT NULL,
    max_amount DECIMAL(20,2),
    duration_months INT,

    expected_return_min DECIMAL(5,2),
    expected_return_max DECIMAL(5,2),

    image_url VARCHAR(500),
    documents_urls JSONB,

    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    -- DRAFT, ACTIVE, PAUSED, CLOSED

    -- Denormalized
    participants_count INT DEFAULT 0,
    total_invested DECIMAL(20,2) DEFAULT 0,

    published_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_strategies_status ON investment.strategies(status);
CREATE INDEX idx_strategies_category ON investment.strategies(category_id);
CREATE INDEX idx_strategies_slug ON investment.strategies(slug);

Strategy Categories ​

sql
CREATE TABLE investment.strategy_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id UUID REFERENCES investment.strategy_categories(id),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Participation Steps ​

sql
CREATE TABLE investment.participation_steps (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    strategy_id UUID NOT NULL REFERENCES investment.strategies(id) ON DELETE CASCADE,

    step_number INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,

    step_type VARCHAR(20) NOT NULL,
    -- INFO, FORM, DOCUMENT_UPLOAD, KYC_CHECK, PAYMENT, CONFIRMATION

    config JSONB,  -- Form fields, validation rules, etc.
    is_required BOOLEAN DEFAULT TRUE,

    UNIQUE(strategy_id, step_number)
);

CREATE INDEX idx_steps_strategy ON investment.participation_steps(strategy_id);

Participations ​

sql
CREATE TABLE investment.participations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id),
    strategy_id UUID NOT NULL REFERENCES investment.strategies(id),

    current_step INT NOT NULL DEFAULT 1,
    step_data JSONB,  -- Collected data from each step

    amount DECIMAL(20,2),
    currency VARCHAR(3) DEFAULT 'RUB',

    status VARCHAR(20) NOT NULL DEFAULT 'IN_PROGRESS',
    -- IN_PROGRESS, PENDING_APPROVAL, ACTIVE, COMPLETED, CANCELLED

    referring_partner_id UUID REFERENCES mlm.partners(id),

    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    submitted_at TIMESTAMP,
    approved_at TIMESTAMP,
    completed_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_participations_user ON investment.participations(user_id);
CREATE INDEX idx_participations_strategy ON investment.participations(strategy_id);
CREATE INDEX idx_participations_status ON investment.participations(status);

Investment Portfolios ​

sql
CREATE TABLE investment.portfolios (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id),
    participation_id UUID NOT NULL UNIQUE REFERENCES investment.participations(id),
    strategy_id UUID NOT NULL REFERENCES investment.strategies(id),

    invested_amount DECIMAL(20,2) NOT NULL,
    current_value DECIMAL(20,2) NOT NULL,
    total_returns DECIMAL(20,2) DEFAULT 0,

    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    -- ACTIVE, MATURED, WITHDRAWN

    start_date DATE NOT NULL,
    maturity_date DATE,

    last_updated_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_portfolios_user ON investment.portfolios(user_id);
CREATE INDEX idx_portfolios_status ON investment.portfolios(status);