IWM Platform

Русский

English

Русский

English

Тема

Схема операций (Аудит, Уведомления, Надежность) ​

Журнал аудита ​

sql
CREATE TABLE core.audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    event_type VARCHAR(100) NOT NULL,
    event_category VARCHAR(50) NOT NULL,

    actor_id UUID,
    actor_type VARCHAR(20) NOT NULL,  -- USER, ADMIN, SYSTEM

    target_type VARCHAR(50),
    target_id UUID,

    action VARCHAR(20) NOT NULL,  -- CREATE, UPDATE, DELETE, READ
    changes JSONB,  -- {field: {old, new}}

    ip_address INET,
    user_agent TEXT,
    session_id UUID,
    request_id UUID
);

-- Партиционирование по месяцам для производительности
-- CREATE TABLE core.audit_log_2024_01 PARTITION OF core.audit_log
--     FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE INDEX idx_audit_actor ON core.audit_log(actor_id, created_at DESC);
CREATE INDEX idx_audit_target ON core.audit_log(target_type, target_id);
CREATE INDEX idx_audit_event ON core.audit_log(event_type, created_at DESC);

Финансовый журнал аудита (Неизменяемый) ​

sql
CREATE TABLE mlm.financial_audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    event_type VARCHAR(50) NOT NULL,
    partner_id UUID NOT NULL,

    amount DECIMAL(20,2),
    currency VARCHAR(3),
    balance_before DECIMAL(20,2),
    balance_after DECIMAL(20,2),

    source_type VARCHAR(30),
    source_id UUID,

    metadata JSONB,
    checksum VARCHAR(64) NOT NULL,  -- SHA256 для целостности
    previous_checksum VARCHAR(64)   -- Проверка цепочки
);

-- Запрет модификаций
-- REVOKE UPDATE, DELETE ON mlm.financial_audit_log FROM app_user;

CREATE INDEX idx_fin_audit_partner ON mlm.financial_audit_log(partner_id, created_at DESC);
CREATE INDEX idx_fin_audit_type ON mlm.financial_audit_log(event_type);

Уведомления ​

sql
CREATE TABLE core.notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    type VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT,
    action_url VARCHAR(500),

    priority VARCHAR(20) DEFAULT 'NORMAL',
    -- LOW, NORMAL, HIGH, URGENT

    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP,

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

CREATE INDEX idx_notifications_user ON core.notifications(user_id, created_at DESC);
CREATE INDEX idx_notifications_unread ON core.notifications(user_id, is_read) WHERE is_read = FALSE;

Настройки уведомлений ​

sql
CREATE TABLE core.notification_preferences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    event_type VARCHAR(50) NOT NULL,
    email_enabled BOOLEAN DEFAULT TRUE,
    sms_enabled BOOLEAN DEFAULT FALSE,
    push_enabled BOOLEAN DEFAULT TRUE,
    in_app_enabled BOOLEAN DEFAULT TRUE,

    UNIQUE(user_id, event_type)
);

CREATE INDEX idx_notif_prefs_user ON core.notification_preferences(user_id);

Ключи идемпотентности (Предотвращение дублирования операций) ​

sql
CREATE TABLE core.idempotency_keys (
    key VARCHAR(255) PRIMARY KEY,
    request_hash VARCHAR(64) NOT NULL,
    response JSONB,

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

-- Периодическая очистка старых ключей (задача pg-boss)
CREATE INDEX idx_idempotency_expires ON core.idempotency_keys(expires_at);

Промокоды ​

sql
CREATE TABLE product.promo_codes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) NOT NULL UNIQUE,

    discount_type VARCHAR(20) NOT NULL,  -- PERCENTAGE, FIXED_AMOUNT
    discount_value DECIMAL(20,2) NOT NULL,

    min_order_amount DECIMAL(20,2),
    max_discount_amount DECIMAL(20,2),  -- Лимит для процентных скидок

    usage_limit INT,  -- NULL = без ограничений
    usage_count INT DEFAULT 0,
    per_user_limit INT,  -- NULL = без ограничений на пользователя

    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP,

    applicable_to VARCHAR(20),  -- ALL, FIRST_ORDER, SPECIFIC_PRODUCTS
    product_ids UUID[],  -- Если applicable_to = SPECIFIC_PRODUCTS

    is_active BOOLEAN DEFAULT TRUE,
    created_by UUID REFERENCES core.users(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_promo_codes_code ON product.promo_codes(code) WHERE is_active = TRUE;
CREATE INDEX idx_promo_codes_valid ON product.promo_codes(valid_from, valid_to) WHERE is_active = TRUE;

Использование промокодов ​

sql
CREATE TABLE product.promo_code_usage (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    promo_code_id UUID NOT NULL REFERENCES product.promo_codes(id),
    user_id UUID NOT NULL REFERENCES core.users(id),
    order_id UUID REFERENCES product.orders(id),

    discount_applied DECIMAL(20,2) NOT NULL,
    used_at TIMESTAMP NOT NULL DEFAULT NOW(),

    UNIQUE(promo_code_id, order_id)
);

CREATE INDEX idx_promo_usage_user ON product.promo_code_usage(user_id, promo_code_id);

Резервирование запасов ​

sql
CREATE TABLE product.inventory_reservations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    product_id UUID NOT NULL REFERENCES product.products(id),
    cart_id UUID REFERENCES product.carts(id),
    order_id UUID REFERENCES product.orders(id),

    quantity INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'RESERVED',
    -- RESERVED, COMMITTED, RELEASED, EXPIRED

    reserved_at TIMESTAMP NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,
    committed_at TIMESTAMP,

    CONSTRAINT positive_quantity CHECK (quantity > 0)
);

CREATE INDEX idx_reservations_product ON product.inventory_reservations(product_id, status);
CREATE INDEX idx_reservations_expires ON product.inventory_reservations(expires_at) WHERE status = 'RESERVED';
CREATE INDEX idx_reservations_cart ON product.inventory_reservations(cart_id) WHERE status = 'RESERVED';

Ограничение частоты запросов (Опционально - если не используется Redis) ​

sql
CREATE TABLE core.rate_limits (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    identifier VARCHAR(255) NOT NULL,  -- IP, user_id или API-ключ
    endpoint VARCHAR(100) NOT NULL,

    request_count INT NOT NULL DEFAULT 1,
    window_start TIMESTAMP NOT NULL DEFAULT NOW(),

    UNIQUE(identifier, endpoint, window_start)
);

-- Автоочистка старых записей
CREATE INDEX idx_rate_limits_cleanup ON core.rate_limits(window_start);

Связанные документы ​