Flagman: схема базы данных
Черновик 0.2, по ТЗ v1.1. Модель данных веб-платформы для PostgreSQL. Комментарии в коде объясняют, какое правило из ТЗ закрепляет каждое ограничение.
-- Flagman: схема БД веб-платформы. Версия 0.2, черновик от 05.11.2026, по ТЗ v1.1. PostgreSQL 17.
-- Эталон модели данных для этапа 0: в проекте превратится в миграции. Правила ТЗ закреплены ограничениями самой базы.
-- Деньги: целые числа в минимальных единицах валюты из таблицы currencies.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'Таблица % только для добавления записей', TG_TABLE_NAME;
END $$;
CREATE TABLE currencies (
code text PRIMARY KEY,
scale smallint NOT NULL CHECK (scale BETWEEN 0 AND 18)
);
INSERT INTO currencies VALUES ('USD', 2), ('USDT', 6), ('RUB', 2), ('INR', 2);
-- ─── Пользователи и доступ ───
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text NOT NULL,
email_verified_at timestamptz,
password_hash text NOT NULL,
totp_secret_encrypted bytea,
locale text NOT NULL DEFAULT 'ru',
customer_type text NOT NULL DEFAULT 'individual' CHECK (customer_type IN ('individual', 'company')),
company_details jsonb,
blocked_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_unique ON users (lower(email));
CREATE TABLE user_roles (
user_id uuid NOT NULL REFERENCES users ON DELETE CASCADE,
role text NOT NULL CHECK (role IN ('client', 'partner', 'support', 'finance', 'admin')),
PRIMARY KEY (user_id, role)
);
CREATE TABLE legal_documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
kind text NOT NULL CHECK (kind IN ('offer', 'privacy', 'acceptable_use', 'partner_agreement')),
locale text NOT NULL,
version int NOT NULL,
body text NOT NULL,
published_at timestamptz,
UNIQUE (kind, locale, version)
);
CREATE TABLE document_acceptances (
user_id uuid NOT NULL REFERENCES users,
document_id uuid NOT NULL REFERENCES legal_documents,
accepted_at timestamptz NOT NULL DEFAULT now(),
ip inet,
PRIMARY KEY (user_id, document_id)
);
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
actor_id uuid REFERENCES users,
action text NOT NULL,
entity_type text NOT NULL,
entity_id text NOT NULL,
before_data jsonb,
after_data jsonb,
reason text,
ip inet,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_log_entity ON audit_log (entity_type, entity_id);
CREATE TRIGGER audit_log_append_only BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION forbid_change();
-- ─── Каталог ───
CREATE TABLE products (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
code text NOT NULL UNIQUE,
title text NOT NULL,
active boolean NOT NULL DEFAULT true
);
-- Срок тарифа: либо дни, либо календарные месяцы. НДС хранится в базисных пунктах: 700 = 7%.
CREATE TABLE plans (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
product_id uuid NOT NULL REFERENCES products,
code text NOT NULL UNIQUE,
title text NOT NULL,
price_minor bigint NOT NULL CHECK (price_minor >= 0),
currency text NOT NULL REFERENCES currencies,
period_days int CHECK (period_days > 0),
period_months int CHECK (period_months > 0),
trial_days int NOT NULL DEFAULT 0 CHECK (trial_days >= 0),
vat_rate_bp int NOT NULL DEFAULT 0 CHECK (vat_rate_bp BETWEEN 0 AND 10000),
traffic_mode text NOT NULL CHECK (traffic_mode IN ('partner_nodes', 'project_servers')),
payment_methods text[] NOT NULL CHECK (cardinality(payment_methods) > 0 AND payment_methods <@ ARRAY['usdt', 'card', 'bank_invoice', 'gateway']),
max_devices int NOT NULL DEFAULT 1 CHECK (max_devices > 0),
active boolean NOT NULL DEFAULT true,
CHECK ((period_days IS NULL) <> (period_months IS NULL))
);
-- Каталог по ТЗ v1.1. Рубли у годовых тарифов и трафик через серверы проекта пока допущения (раздел 1.6, В-23).
INSERT INTO products (code, title) VALUES ('subscription_30d', 'Подписка на 30 дней'), ('annual', 'Годовая подписка');
INSERT INTO plans (product_id, code, title, price_minor, currency, period_days, period_months, trial_days, vat_rate_bp, traffic_mode, payment_methods)
SELECT p.id, v.code, v.title, v.price_minor, v.currency, v.period_days, v.period_months, 30, v.vat_rate_bp, v.traffic_mode, v.payment_methods
FROM (VALUES
('subscription_30d', 'subscription_30d', '$150 за 30 дней', 15000::bigint, 'USD', 30, NULL::int, 0, 'partner_nodes', ARRAY['usdt']),
('annual', 'annual_comfort', 'Комфорт', 1500000::bigint, 'RUB', NULL::int, 12, 700, 'project_servers', ARRAY['card', 'bank_invoice']),
('annual', 'annual_premium', 'Премиум', 2500000::bigint, 'RUB', NULL::int, 12, 700, 'project_servers', ARRAY['card', 'bank_invoice'])
) AS v(product_code, code, title, price_minor, currency, period_days, period_months, vat_rate_bp, traffic_mode, payment_methods)
JOIN products p ON p.code = v.product_code;
CREATE TABLE router_models (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
code text NOT NULL UNIQUE,
title text NOT NULL,
support_status text NOT NULL CHECK (support_status IN ('supported', 'pilot', 'unsupported')),
warning text,
allowed_for_nodes boolean NOT NULL DEFAULT false
);
CREATE TABLE software_builds (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
router_model_id uuid NOT NULL REFERENCES router_models,
version text NOT NULL,
delivery text NOT NULL CHECK (delivery IN ('download', 'app_store', 'preinstalled')),
url text,
sha256 text,
published_at timestamptz,
UNIQUE (router_model_id, version)
);
-- Заявки компаний на счёт.
CREATE TABLE leads (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
plan_id uuid REFERENCES plans,
company text,
contact text NOT NULL,
details text,
status text NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_progress', 'won', 'lost')),
assignee_id uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now()
);
-- ─── Подписки ───
CREATE TABLE subscriptions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users,
plan_id uuid NOT NULL REFERENCES plans,
status text NOT NULL CHECK (status IN ('pending_activation', 'trial', 'pending_payment', 'active', 'expiring', 'grace', 'terminated', 'suspended')),
-- Срок считается с активации устройства. current_until без отсрочки, в сетевой комплекс уходит дата с отсрочкой.
activated_at timestamptz,
current_until timestamptz,
-- Точка выхода выбирается только в подписке через узлы партнёров.
exit_country text,
exit_city text,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (exit_city IS NULL OR exit_country IS NOT NULL)
);
CREATE INDEX subscriptions_user ON subscriptions (user_id);
CREATE INDEX subscriptions_until ON subscriptions (current_until) WHERE status IN ('trial', 'active', 'expiring', 'grace');
CREATE TABLE licenses (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
subscription_id uuid NOT NULL UNIQUE REFERENCES subscriptions,
activation_code_hash text NOT NULL,
status text NOT NULL CHECK (status IN ('issued', 'activated', 'revoked')),
entitlement_version int NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE devices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
license_id uuid NOT NULL REFERENCES licenses,
external_id text NOT NULL UNIQUE,
hardware_id text,
router_model_id uuid REFERENCES router_models,
software_version text,
status text NOT NULL CHECK (status IN ('not_activated', 'online', 'offline', 'replaced')),
last_seen_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Пробный период один раз на учётную запись и на устройство, записи не меняются (Б-02).
CREATE TABLE trial_usages (
user_id uuid NOT NULL UNIQUE REFERENCES users,
hardware_id text NOT NULL UNIQUE,
subscription_id uuid NOT NULL REFERENCES subscriptions,
started_at timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER trial_usages_append_only BEFORE UPDATE OR DELETE ON trial_usages
FOR EACH ROW EXECUTE FUNCTION forbid_change();
CREATE TABLE waitlist_entries (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users,
plan_id uuid NOT NULL REFERENCES plans,
status text NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'invited', 'cancelled', 'converted')),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX waitlist_one_waiting ON waitlist_entries (user_id, plan_id) WHERE status = 'waiting';
-- ─── Платежи ───
CREATE TABLE networks (
id text PRIMARY KEY,
title text NOT NULL,
family text NOT NULL CHECK (family IN ('tron', 'evm', 'ton', 'solana')),
required_confirmations int NOT NULL CHECK (required_confirmations > 0),
enabled boolean NOT NULL DEFAULT false
);
-- Зачисляются только переводы контракта из этого списка: поддельный токен с названием USDT не пройдёт (П-03).
CREATE TABLE token_contracts (
network_id text NOT NULL REFERENCES networks,
address text NOT NULL,
currency text NOT NULL REFERENCES currencies,
decimals smallint NOT NULL CHECK (decimals BETWEEN 0 AND 18),
PRIMARY KEY (network_id, address)
);
CREATE TABLE wallets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
network_id text NOT NULL REFERENCES networks,
purpose text NOT NULL CHECK (purpose IN ('operational', 'cold', 'fee', 'payout')),
address text NOT NULL,
UNIQUE (network_id, address)
);
-- Постоянный адрес клиента в каждой сети, адрес не может принадлежать двум клиентам (П-02).
CREATE TABLE deposit_addresses (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users,
network_id text NOT NULL REFERENCES networks,
address text NOT NULL,
derivation_path text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, network_id),
UNIQUE (network_id, address)
);
CREATE TABLE invoices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
number bigint GENERATED ALWAYS AS IDENTITY UNIQUE,
user_id uuid NOT NULL REFERENCES users,
subscription_id uuid NOT NULL REFERENCES subscriptions,
plan_id uuid NOT NULL REFERENCES plans,
kind text NOT NULL CHECK (kind IN ('initial', 'renewal')),
amount_minor bigint NOT NULL CHECK (amount_minor > 0),
currency text NOT NULL REFERENCES currencies,
-- Ставка и сумма НДС фиксируются в счёте: смена ставки в тарифе его не меняет.
vat_rate_bp int NOT NULL DEFAULT 0 CHECK (vat_rate_bp BETWEEN 0 AND 10000),
vat_minor bigint NOT NULL DEFAULT 0 CHECK (vat_minor >= 0),
payment_method text NOT NULL CHECK (payment_method IN ('usdt', 'card', 'bank_invoice', 'gateway')),
network_id text REFERENCES networks,
status text NOT NULL CHECK (status IN ('open', 'partially_paid', 'confirming', 'paid', 'expired', 'cancelled', 'exception')),
expires_at timestamptz NOT NULL,
paid_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (payment_method <> 'usdt' OR network_id IS NOT NULL)
);
-- На подписку не больше одного незакрытого счёта: автопродление не создаст дубль.
CREATE UNIQUE INDEX invoices_one_open ON invoices (subscription_id) WHERE status IN ('open', 'partially_paid', 'confirming');
CREATE INDEX invoices_oldest_open ON invoices (user_id, created_at) WHERE status IN ('open', 'partially_paid');
-- Защита от двойного зачисления: одно событие блокчейна записывается один раз (П-06).
CREATE TABLE blockchain_transfers (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
network_id text NOT NULL REFERENCES networks,
tx_hash text NOT NULL,
log_index int NOT NULL,
token_contract text NOT NULL,
from_address text NOT NULL,
to_address text NOT NULL,
amount_raw numeric(78, 0) NOT NULL CHECK (amount_raw > 0),
block_number bigint,
confirmations int NOT NULL DEFAULT 0,
status text NOT NULL CHECK (status IN ('seen', 'confirmed', 'reverted', 'exception')),
seen_at timestamptz NOT NULL DEFAULT now(),
confirmed_at timestamptz,
UNIQUE (network_id, tx_hash, log_index),
FOREIGN KEY (network_id, token_contract) REFERENCES token_contracts (network_id, address)
);
CREATE INDEX blockchain_transfers_to ON blockchain_transfers (network_id, to_address);
-- Перевод может покрыть счёт только один раз.
CREATE TABLE payment_allocations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
transfer_id uuid NOT NULL REFERENCES blockchain_transfers,
invoice_id uuid NOT NULL REFERENCES invoices,
amount_minor bigint NOT NULL CHECK (amount_minor > 0),
created_by uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (transfer_id, invoice_id)
);
-- Оплата картой или по счёту: подтверждает сотрудник. Один платёжный документ не зачисляется дважды (П-15).
CREATE TABLE manual_payments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
invoice_id uuid NOT NULL REFERENCES invoices,
method text NOT NULL CHECK (method IN ('card', 'bank_invoice')),
amount_minor bigint NOT NULL CHECK (amount_minor > 0),
currency text NOT NULL REFERENCES currencies,
received_on date NOT NULL,
document_ref text NOT NULL CHECK (length(trim(document_ref)) > 0),
note text,
confirmed_by uuid NOT NULL REFERENCES users,
confirmed_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (method, document_ref)
);
CREATE INDEX manual_payments_invoice ON manual_payments (invoice_id);
CREATE TABLE payment_exceptions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
transfer_id uuid REFERENCES blockchain_transfers,
invoice_id uuid REFERENCES invoices,
reason text NOT NULL CHECK (reason IN ('underpaid', 'overpaid', 'late', 'no_open_invoice', 'wrong_token', 'wrong_network', 'aml_flag', 'reorg')),
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'applied', 'refunded', 'closed')),
resolution text,
assignee_id uuid REFERENCES users,
resolved_by uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now(),
resolved_at timestamptz,
CHECK (transfer_id IS NOT NULL OR invoice_id IS NOT NULL)
);
CREATE TABLE internal_transfers (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
network_id text NOT NULL REFERENCES networks,
from_address text NOT NULL,
to_wallet_id uuid NOT NULL REFERENCES wallets,
amount_raw numeric(78, 0) NOT NULL CHECK (amount_raw > 0),
request_key text NOT NULL UNIQUE,
tx_hash text,
status text NOT NULL CHECK (status IN ('requested', 'signed', 'sent', 'confirmed', 'failed')),
created_at timestamptz NOT NULL DEFAULT now()
);
-- ─── Партнёры и узлы ───
CREATE TABLE partners (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL UNIQUE REFERENCES users,
full_name text NOT NULL,
country text NOT NULL DEFAULT 'IN',
city text,
phone text,
referral_code text NOT NULL UNIQUE,
status text NOT NULL CHECK (status IN ('registered', 'active', 'on_hold', 'terminated')),
created_at timestamptz NOT NULL DEFAULT now()
);
-- Один приглашённый имеет одного пригласившего, себя пригласить нельзя (У-03, У-04).
CREATE TABLE referrals (
referred_partner_id uuid PRIMARY KEY REFERENCES partners,
referrer_partner_id uuid NOT NULL REFERENCES partners,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (referred_partner_id <> referrer_partner_id)
);
CREATE INDEX referrals_referrer ON referrals (referrer_partner_id);
-- Реквизиты не редактируются, а заводятся новой версией. Выплата на новую версию возможна после hold_until (ПК-2).
-- Партнёры получают USDT, для него обязательна сеть. Остальные способы оставлены на случай провайдера (В-05).
CREATE TABLE payout_details (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
partner_id uuid NOT NULL REFERENCES partners,
method text NOT NULL CHECK (method IN ('upi', 'bank', 'usdt', 'other')),
usdt_network_id text REFERENCES networks,
details_encrypted bytea NOT NULL,
masked text NOT NULL,
hold_until timestamptz NOT NULL,
active boolean NOT NULL DEFAULT true,
created_by uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (method <> 'usdt' OR usdt_network_id IS NOT NULL)
);
CREATE UNIQUE INDEX payout_details_one_active ON payout_details (partner_id) WHERE active;
CREATE TABLE nodes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
partner_id uuid NOT NULL REFERENCES partners,
external_id text UNIQUE,
router_model_id uuid REFERENCES router_models,
equipment_owner text NOT NULL CHECK (equipment_owner IN ('project', 'partner')),
country text NOT NULL DEFAULT 'IN',
city text,
isp text,
public_ip inet,
declared_downlink_mbps int,
status text NOT NULL CHECK (status IN ('draft', 'checking', 'active', 'offline', 'suspended', 'retired')),
created_at timestamptz NOT NULL DEFAULT now(),
retired_at timestamptz
);
-- Один публичный IP не даёт двух оплачиваемых узлов (У-05).
-- Один партнёр равен одному узлу. После вывода узла можно подключить новый (У-05).
CREATE UNIQUE INDEX nodes_one_per_partner ON nodes (partner_id) WHERE status <> 'retired';
CREATE UNIQUE INDEX nodes_unique_active_ip ON nodes (public_ip) WHERE public_ip IS NOT NULL AND status <> 'retired';
CREATE INDEX nodes_partner ON nodes (partner_id);
-- Основа начислений. Записи собираются с этапа I, даже пока начислений ещё нет. Исправление: новая ревизия, старая помечается superseded_at.
CREATE TABLE availability_intervals (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
external_id text NOT NULL,
revision int NOT NULL,
node_id uuid NOT NULL REFERENCES nodes,
started_at timestamptz NOT NULL,
ended_at timestamptz NOT NULL,
counted boolean NOT NULL DEFAULT true,
exclusion_reason text,
received_at timestamptz NOT NULL DEFAULT now(),
superseded_at timestamptz,
UNIQUE (external_id, revision),
CHECK (ended_at > started_at),
EXCLUDE USING gist (node_id WITH =, tstzrange(started_at, ended_at) WITH &&) WHERE (superseded_at IS NULL)
);
-- ─── Начисления и выплаты ───
-- month: календарный месяц по Москве (UTC+3), первый день месяца. Интервалы в UTC режутся по этим границам.
CREATE TABLE accrual_periods (
month date PRIMARY KEY CHECK (extract(day FROM month) = 1),
status text NOT NULL CHECK (status IN ('open', 'data_complete', 'calculated', 'closed')),
data_complete_at timestamptz,
closed_at timestamptz
);
CREATE TABLE accruals (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
month date NOT NULL REFERENCES accrual_periods,
partner_id uuid NOT NULL REFERENCES partners,
node_id uuid NOT NULL REFERENCES nodes,
kind text NOT NULL CHECK (kind IN ('node', 'referral', 'adjustment')),
rate_minor bigint NOT NULL,
counted_seconds bigint NOT NULL CHECK (counted_seconds >= 0),
month_seconds bigint NOT NULL CHECK (month_seconds > 0),
amount_minor bigint NOT NULL,
currency text NOT NULL DEFAULT 'USD' REFERENCES currencies,
reason text,
created_by uuid REFERENCES users,
approved_by uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (counted_seconds <= month_seconds),
CHECK (kind = 'adjustment' OR amount_minor >= 0),
-- Корректировку подтверждает второй сотрудник (раздел 3.1 ТЗ).
CHECK (kind <> 'adjustment' OR (reason IS NOT NULL AND approved_by IS NOT NULL AND created_by <> approved_by))
);
-- За месяц по узлу не больше одного начисления каждого вида одному партнёру: пересчёт не задвоит сумму.
CREATE UNIQUE INDEX accruals_once ON accruals (month, partner_id, node_id, kind) WHERE kind <> 'adjustment';
CREATE INDEX accruals_partner ON accruals (partner_id, month);
-- $20 за реферала получает только партнёр со своим работающим узлом: в этом месяце есть начисление за подтверждённое время (У-02).
-- Поэтому начисления за узлы считаются раньше реферальных.
CREATE FUNCTION accrual_referral_requires_own_node() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM accruals
WHERE month = NEW.month AND partner_id = NEW.partner_id AND kind = 'node' AND counted_seconds > 0
) THEN
RAISE EXCEPTION 'Партнёр % без подтверждённого времени своего узла за этот месяц', NEW.partner_id;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER accruals_referral_own_node BEFORE INSERT ON accruals
FOR EACH ROW WHEN (NEW.kind = 'referral') EXECUTE FUNCTION accrual_referral_requires_own_node();
CREATE TABLE payout_statements (
month date PRIMARY KEY REFERENCES accrual_periods,
status text NOT NULL CHECK (status IN ('draft', 'approved', 'running', 'stopped', 'completed')),
approved_by uuid REFERENCES users,
approved_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
CHECK (status IN ('draft') OR approved_by IS NOT NULL)
);
-- В ведомости одна строка на партнёра. Сумма в USD, выплата в USDT по курсу 1:1, комиссию несёт платформа.
-- Ключ идемпотентности проверяет сервис подписи или провайдер и защищает от повторной выплаты (ВП-1).
CREATE TABLE payout_lines (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
month date NOT NULL REFERENCES payout_statements,
partner_id uuid NOT NULL REFERENCES partners,
payout_details_id uuid REFERENCES payout_details,
carried_in_minor bigint NOT NULL DEFAULT 0 CHECK (carried_in_minor >= 0),
accrued_minor bigint NOT NULL,
amount_minor bigint NOT NULL CHECK (amount_minor >= 0),
currency text NOT NULL DEFAULT 'USD' REFERENCES currencies,
status text NOT NULL CHECK (status IN ('in_statement', 'approved', 'sent', 'completed', 'failed', 'carried_over', 'held')),
idempotency_key text NOT NULL UNIQUE,
provider text,
provider_ref text,
payout_currency text REFERENCES currencies,
payout_amount_minor bigint,
payout_network_id text REFERENCES networks,
payout_tx_hash text,
fx_rate numeric(20, 8),
fee_minor bigint,
fee_payer text NOT NULL DEFAULT 'platform' CHECK (fee_payer IN ('platform', 'partner')),
failure_code text,
sent_at timestamptz,
completed_at timestamptz,
UNIQUE (month, partner_id),
-- 1 цент = 10 000 минимальных единиц USDT.
CHECK (payout_currency IS DISTINCT FROM 'USDT' OR (payout_amount_minor = amount_minor * 10000 AND payout_network_id IS NOT NULL)),
-- Отправить можно только от порога $10 (долларовая строка, минимальная единица 1 цент).
CHECK (status NOT IN ('approved', 'sent', 'completed') OR amount_minor >= 1000)
);
CREATE INDEX payout_lines_status ON payout_lines (status);
-- ─── Финансовый журнал: двойная запись, только добавление ───
CREATE TABLE ledger_accounts (
code text PRIMARY KEY,
title text NOT NULL,
kind text NOT NULL CHECK (kind IN ('asset', 'liability', 'income', 'expense')),
currency text NOT NULL REFERENCES currencies
);
CREATE TABLE ledger_transactions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
source_type text NOT NULL,
source_id text NOT NULL,
kind text NOT NULL,
reverses_id uuid REFERENCES ledger_transactions,
memo text,
created_by uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now(),
-- Одно событие-источник не создаст две проводки одного вида, даже при повторной обработке.
UNIQUE (source_type, source_id, kind)
);
CREATE TABLE ledger_entries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
transaction_id uuid NOT NULL REFERENCES ledger_transactions,
account_code text NOT NULL REFERENCES ledger_accounts,
debit_minor bigint NOT NULL DEFAULT 0 CHECK (debit_minor >= 0),
credit_minor bigint NOT NULL DEFAULT 0 CHECK (credit_minor >= 0),
CHECK ((debit_minor = 0) <> (credit_minor = 0))
);
CREATE INDEX ledger_entries_tx ON ledger_entries (transaction_id);
CREATE INDEX ledger_entries_account ON ledger_entries (account_code);
CREATE TRIGGER ledger_transactions_append_only BEFORE UPDATE OR DELETE ON ledger_transactions
FOR EACH ROW EXECUTE FUNCTION forbid_change();
CREATE TRIGGER ledger_entries_append_only BEFORE UPDATE OR DELETE ON ledger_entries
FOR EACH ROW EXECUTE FUNCTION forbid_change();
-- На момент фиксации транзакции дебет равен кредиту в каждой валюте.
CREATE FUNCTION ledger_check_balanced() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF EXISTS (
SELECT 1
FROM ledger_entries e JOIN ledger_accounts a ON a.code = e.account_code
WHERE e.transaction_id = NEW.transaction_id
GROUP BY a.currency
HAVING sum(e.debit_minor) <> sum(e.credit_minor)
) THEN
RAISE EXCEPTION 'Проводка % не сбалансирована', NEW.transaction_id;
END IF;
RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER ledger_entries_balanced AFTER INSERT ON ledger_entries
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION ledger_check_balanced();
CREATE TABLE reconciliation_runs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
scope text NOT NULL CHECK (scope IN ('blockchain', 'payout_provider', 'network')),
period_start timestamptz NOT NULL,
period_end timestamptz NOT NULL,
status text NOT NULL CHECK (status IN ('running', 'matched', 'discrepancies', 'failed')),
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (scope, period_start, period_end)
);
CREATE TABLE reconciliation_items (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
run_id uuid NOT NULL REFERENCES reconciliation_runs,
entity_type text NOT NULL,
entity_ref text NOT NULL,
details jsonb NOT NULL,
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'resolved')),
resolution text,
resolved_by uuid REFERENCES users,
resolved_at timestamptz
);
-- ─── Поддержка и уведомления ───
CREATE TABLE tickets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users,
subject text NOT NULL,
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'waiting_user', 'waiting_support', 'closed')),
assignee_id uuid REFERENCES users,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE ticket_messages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_id uuid NOT NULL REFERENCES tickets ON DELETE CASCADE,
author_id uuid NOT NULL REFERENCES users,
body text NOT NULL,
internal boolean NOT NULL DEFAULT false,
attachments jsonb NOT NULL DEFAULT '[]',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE notifications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users,
kind text NOT NULL,
channel text NOT NULL CHECK (channel IN ('in_app', 'email', 'telegram')),
payload jsonb NOT NULL,
dedupe_key text NOT NULL,
sent_at timestamptz,
read_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
-- Напоминание за 3 дня по конкретному счёту не уйдёт дважды. Поддержка в Telegram на старте просто ссылка.
UNIQUE (user_id, channel, dedupe_key)
);
-- ─── Интеграция с сетевым комплексом ───
CREATE TABLE integration_outbox (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
target text NOT NULL,
command text NOT NULL,
entity_ref text NOT NULL,
version int,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'acknowledged', 'failed')),
attempts int NOT NULL DEFAULT 0,
next_attempt_at timestamptz NOT NULL DEFAULT now(),
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX integration_outbox_due ON integration_outbox (next_attempt_at) WHERE status IN ('pending', 'failed');
-- Повторно доставленное событие отклоняется по event_id.
CREATE TABLE integration_inbox (
event_id text PRIMARY KEY,
source text NOT NULL,
type text NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL,
processed_at timestamptz,
error text,
received_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX integration_inbox_unprocessed ON integration_inbox (received_at) WHERE processed_at IS NULL;
CREATE TABLE settings (
key text PRIMARY KEY,
value jsonb NOT NULL,
updated_by uuid REFERENCES users,
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO settings (key, value) VALUES
('node_capacity', '5'),
('renewal_invoice_days_before', '7'),
('grace_days', '3'),
('billing_timezone', '"Europe/Moscow"'),
('amount_tolerance_usdt_minor', '500000'),
('payout_threshold_usd_minor', '1000'),
('node_rate_usd_minor', '10000'),
('referral_rate_usd_minor', '2000'),
('payout_details_hold_hours', '72');