IWM Platform

English

Русский

English

Русский

Theme

Database Consistency (Triggers & Constraints) ​

Auto-Update Timestamps ​

sql
-- Function to auto-update updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON core.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_profiles_updated_at
    BEFORE UPDATE ON core.user_profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_partners_updated_at
    BEFORE UPDATE ON mlm.partners
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_orders_updated_at
    BEFORE UPDATE ON product.orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON product.products
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Soft Delete Columns ​

sql
-- Add soft delete for compliance/audit requirements
ALTER TABLE core.users ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE mlm.partners ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE mlm.commission_transactions ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE product.orders ADD COLUMN deleted_at TIMESTAMP;

-- Indexes for soft delete queries
CREATE INDEX idx_users_deleted ON core.users(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX idx_partners_deleted ON mlm.partners(deleted_at) WHERE deleted_at IS NULL;

Denormalized Counter Triggers ​

sql
-- Auto-update direct_referrals_count when partners are added/removed
CREATE OR REPLACE FUNCTION update_partner_referral_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' AND NEW.sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count + 1
        WHERE id = NEW.sponsor_id;
    ELSIF TG_OP = 'DELETE' AND OLD.sponsor_id IS NOT NULL THEN
        UPDATE mlm.partners
        SET direct_referrals_count = direct_referrals_count - 1
        WHERE id = OLD.sponsor_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_referral_count
    AFTER INSERT OR DELETE ON mlm.partners
    FOR EACH ROW EXECUTE FUNCTION update_partner_referral_count();

Missing FK Constraint ​

sql
-- Add FK for promo_code_id in orders (was missing)
ALTER TABLE product.orders
ADD CONSTRAINT fk_orders_promo_code
FOREIGN KEY (promo_code_id) REFERENCES product.promo_codes(id);

Initial Data ​

Roles ​

sql
INSERT INTO core.roles (name, description, is_system) VALUES
('USER', 'Basic platform user', TRUE),
('PARTNER', 'MLM partner with referral rights', TRUE),
('MODERATOR', 'Content and KYC moderator', TRUE),
('ADMIN', 'Platform administrator', TRUE),
('SUPER_ADMIN', 'Full platform access', TRUE);

Default Ranks ​

sql
INSERT INTO mlm.ranks (name, code, level, description) VALUES
('Новичок', 'STARTER', 1, 'Начальный уровень'),
('Консультант', 'CONSULTANT', 2, 'Первые продажи'),
('Менеджер', 'MANAGER', 3, 'Активная сеть'),
('Директор', 'DIRECTOR', 4, 'Значительный объем'),
('Топ Лидер', 'TOP_LEADER', 5, 'Ведущий партнер');