Wallet database

00_wallet_database.sql

-- https://docs.google.com/spreadsheets/d/1BFdaeOUeQ2hbAWazSYXBjEsSVJVpaFI_FaQXkr00KD8/edit#gid=131411458

-- Таблица пользователей для песочницы
create table garage_users
(
    id       serial primary key,
    nickname text
);


--
-- Глобальные / локальные настройки операций
--


-- Тип операции
create type wallet_operation_type as enum (
    'deposit',
    'withdrawal',
    'convert'
    );

-- Статус операции
create type active_status_type as enum (
    'inactive',
    'active'
    );

create type currency_type as enum (
    'gold',
    'usd'
    );

-- Доступные валютные пары
create table wallet_currency_pairs
(
    id          serial primary key,
    source_type currency_type not null,
    target_type currency_type not null
);

create unique index ux_wallet_currency_pairs on wallet_currency_pairs (source_type, target_type);

-- Заводим стандартные валютные пары (использовать enum?)
insert into wallet_currency_pairs(source_type, target_type)
values ('usd', 'gold'), -- id=1
       ('gold', 'usd') -- id=2
;


-- Глобальные / локальные настройки операций
create table wallet_operation_configs
(
    id               serial primary key,
    op               wallet_operation_type not null,
    currency_pair_id int /*references wallet_currency_pairs*/,
    convert_ratio    float4,
    min              decimal(22, 6),
    max              decimal(22, 6),
    user_id          int /*references garage_users*/,
    status           active_status_type    not null default 'inactive',
    commission       decimal(22, 6),
    fee              decimal(22, 6)
);

-- Ограничение на единственную глобальную настройку для op='convert' для определённой валютной пары
create unique index ux_wallet_operation_configs_base_convert on wallet_operation_configs (op, currency_pair_id, (user_id is null)) where user_id is null and op = 'convert';
-- Ограничение на единственную глобальную настройку для op!='convert'
create unique index ux_wallet_operation_configs_base_not_convert on wallet_operation_configs (op, (user_id is null)) where user_id is null and op != 'convert';
-- Ограничение на единственную локальную настройку для конкретного пользователя для op='convert' для определённой валютной пары
create unique index ux_wallet_operation_configs_user_id_convert on wallet_operation_configs (op, currency_pair_id, user_id) where op = 'convert';
-- Ограничение на единственную локальную настройку для конкретного пользователя для op!='convert'
create unique index ux_wallet_operation_configs_user_id_not_convert on wallet_operation_configs (op, user_id) where op != 'convert';
-- convert_ratio можно задавать только для op='convert'
alter table wallet_operation_configs
    add constraint ck_wallet_operation_configs_convert_ratio check (op = 'convert' or op != 'convert' and convert_ratio is null );

create index ix_wallet_operation_configs_user_id on wallet_operation_configs (user_id);
create index ix_wallet_operation_configs_user_id_op on wallet_operation_configs (user_id, op);


--
-- Глобальные / локальные настройки кошельков
--


create type blockchain_network as enum (
    'bsc', 'ethereum'
    );

-- Доступные валюты
create table wallet_currencies
(
    id           serial primary key,
    network      blockchain_network not null,
    currency     varchar(5)         not null,
    confirmation int                not null default 0,
    sc_address   bytea,
    status       active_status_type not null
);

-- Привязка deposit_wallet к доступным валютам
create table wallet_currency_deposits
(
    id                 serial primary key,
    wallet_currency_id int references wallet_currencies not null,
    deposit_wallet     bytea,
    balance            decimal(22, 6)
);

-- На кого распространяется данная настройка
create type currency_wallet_filter_type as enum (
    'all', -- На всех пользователей
    'user' -- Только на одного пользователя
    );

-- Глобальные / локальные настройки кошельков
create table wallet_currency_deposit_configs
(
    id                         serial primary key,
    wallet_currency_deposit_id int references wallet_currency_deposits not null,
    filter_type                currency_wallet_filter_type             not null,
    user_id                    int /*references garage_users*/,
    status                     active_status_type                      not null
);


-- Ограничение на единственный глобальный кошелёк со status='active'
create unique index ux_wallet_currency_deposit_configs_status_active on wallet_currency_deposit_configs (status, (user_id is null)) where user_id is null and status = 'active';
-- Ограничение на единственный локальный кошелёк для конкретного пользователя со status='active'
create unique index ux_wallet_currency_deposit_configs_user_id_status_active on wallet_currency_deposit_configs (status, user_id) where status = 'active';
-- Поле user_id может быть заполнено только для filter_type='user'
alter table wallet_currency_deposit_configs
    add constraint ck_wallet_currency_deposit_configs_user_id check (filter_type = 'user' and user_id is not null or
                                                                     filter_type != 'user' and user_id is null );

create index ix_wallet_currency_deposit_configs_user_id on wallet_currency_deposit_configs (user_id);


--
-- Transaction History
--

-- Under construct

create type wallet_transaction_status as enum (
    'pending',
    'approved',
    'rejected'
    );

create type wallet_transaction_type as enum (
    'deposit',
    'withdrawal'
    );

create table user_wallet_transactions
(
    id                serial8 primary key,
    created_at        timestamp                       not null default now(),
    user_id           int /*references garage_users*/ not null,

    transaction_type  wallet_transaction_type         not null,
    network           blockchain_network              not null,
    currency          varchar(10)                     not null,
    user_wallet       bytea                           not null,
    deposit_wallet    bytea,
    tx_id             bytea,

    source_amount     decimal(22, 6)                  not null,
    final_amount      decimal(22, 6)                  not null,

    commission        decimal(22, 6)                  not null,
    fee               decimal(22, 6)                  not null,

    confirmed         bool                            not null,
    confirmed_date    timestamp(0),

    status            wallet_transaction_status       not null,
    status_changed_at timestamp                       not null default now()
);

-- final_amount и комиссия должны коррелировать
alter table user_wallet_transactions
    add constraint ck_user_wallet_transactions_final_amount check (final_amount = (source_amount - fee) * (1 - commission));

-- confirmed_date должен быть заполнен только при confirmed=true
alter table user_wallet_transactions
    add check ( not confirmed and confirmed_date is null or confirmed and confirmed_date is not null );


create table user_wallet_convert_transactions
(
    id                 serial8 primary key,
    created_at         timestamp                       not null default now(),
    user_id            int /*references garage_users*/ not null,

    from_currency      currency_type                   not null,
    to_currency        currency_type                   not null,

    source_from_amount decimal(22, 6)                  not null,
    final_to_amount    decimal(22, 6)                  not null,

    convert_ratio      float4                          not null,
    commission         decimal(22, 6)                  not null,
    fee                decimal(22, 6)                  not null
);

alter table user_wallet_convert_transactions
    add constraint ck_user_wallet_transactions_final_amount check (final_to_amount = (source_from_amount - fee) * convert_ratio * (1 - commission));


create type user_transaction_type as enum (
    'deposit',
    'widhdrawal',
    'convert',
    'inapp',
    'reject'
    );

create table user_transaction_history
(
    id                     serial8 primary key,
    user_id                int /*references garage_users*/ not null,
    transaction_type       user_transaction_type           not null,
    currency_type          currency_type                   not null,
    amount                 decimal(22, 6)                  not null,
    value_after            decimal(22, 6)                  not null,
    dttm                   timestamp                       not null default now(),
    is_confirmed           bool                            not null,
    related_transaction_id int8 references user_transaction_history,
    receipt                jsonb                           not null
);


insert into wallet_operation_configs(op, status)
values ('deposit', 'active')

01_use_example.sql

--
-- Пример настроек операций
--


-- Настройка операции конвертирования USD/GOLD для всех пользователей
insert into wallet_operation_configs(op, currency_pair_id, convert_ratio, user_id, status, commission, fee, min, max)
values ('convert', 1, 1, null, 'active', 0.3, 10, 1, 1000);

-- Fail
-- Нельзя добавить вторую общую настройку для операции 'convert' для валюты USD/GOLD, т.к. глобальная настройка уже существует
insert into wallet_operation_configs(op, currency_pair_id, convert_ratio, user_id, status, commission, fee, min, max)
values ('convert', 1, 2, null, 'active', 0.3, 10, 1, 1000);

-- Настройка операции конвертирования USD/GOLD для пользователя с user_id=1. От общий настроек отличается только величина fee
insert into wallet_operation_configs(op, currency_pair_id, convert_ratio, user_id, status, commission, fee)
values ('convert', 1, 1, 1, null, null, 5);

-- Fail
-- Нельзя добавить вторую настройку для операции 'convert' для валюты USD/GOLD для пользователя с user_id=1, т.к. локальная настройка уже существует
insert into wallet_operation_configs(op, currency_pair_id, convert_ratio, user_id, status, commission, fee)
values ('convert', 1, 1, 1, null, null, 15);

-- Настройка операции withdrawal для всех пользователей
insert into wallet_operation_configs(op, currency_pair_id, convert_ratio, user_id, status, commission, fee)
values ('withdrawal', null, null, null, 'active', 0.2, 3);

-- Пример sql запроса для получений финальных настроек для всех операций для пользователя с user_id=1
with t as (select *
           from wallet_operation_configs
           where (user_id is null or user_id = 1)
           order by user_id)
select op,
       currency_pair_id,
       percentile_disc(0) within group ( order by min )           as min,
       percentile_disc(0) within group ( order by max )           as max,
       percentile_disc(0) within group ( order by convert_ratio ) as convert_ratio,
       percentile_disc(0) within group ( order by status )        as status,
       percentile_disc(0) within group ( order by commission )    as commission,
       percentile_disc(0) within group ( order by fee )           as fee
from t
group by op, currency_pair_id
;

-- Пример sql запроса для получений всех настроек связанных с пользователем user_id=1
with t as (select *
           from wallet_operation_configs
           where (user_id is null or user_id = 1)
           order by user_id)
select *
from t
order by op, user_id
;

--
-- /Пример настроек операций
--



--
-- Пример настройки кошельков
--

-- Создаём валюты
insert into wallet_currencies(networks, currency, confirmation, sc_address, status) -- id=1
values ('BSC', 'BUSD', 12, '0xe9e7CEA3DedcA5984780Bafc599bD69ADd087D56', 'active');

insert into wallet_currencies(networks, currency, confirmation, sc_address, status) -- id=2
values ('BSC', 'USDT', 12, '0x55d398326f99059fF775485246999027B3197955', 'active');

insert into wallet_currencies(networks, currency, confirmation, sc_address, status) -- id=3
values ('Ethereum', 'USDT', 12, '0xdAC17F958D2ee523a2206206994597C13D831ec7', 'inactive');

insert into wallet_currencies(networks, currency, confirmation, sc_address, status) -- id=4
values ('Ethereum', 'USDT', 12, '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48', 'inactive');


-- Привязываем валюту c id=1 к разным кошелькам
insert into wallet_currency_deposits (currency_id, deposit_wallet) -- id = 1
values (1, '0x123');
insert into wallet_currency_deposits (currency_id, deposit_wallet) -- id = 2
values (1, '0x234');
insert into wallet_currency_deposits (currency_id, deposit_wallet) -- id = 2
values (1, '343');

-- Делаем кошелёк с id=1 активным для всех пользователей
insert into wallet_currency_deposit_settings(wallet_currency_deposits_id, filter_type, user_id, status)
values (1, 'all', null, 'active');

--Fail
-- Одновременно активным может быть только один кошелёк со status='active' и filter_type='all'
insert into wallet_currency_deposit_settings(wallet_currency_deposits_id, filter_type, user_id, status)
values (2, 'all', null, 'active');

-- Делаем кошелёк с id=2 активным только для пользователя с user_id=1
insert into wallet_currency_deposit_settings(wallet_currency_deposits_id, filter_type, user_id, status)
values (2, 'user', 1, 'active');

-- Fail
-- для пользователя с user_id=1 уже есть локальная настройка
insert into wallet_currency_deposit_settings(wallet_currency_deposits_id, filter_type, user_id, status)
values (2, 'user', 1, 'active');


-- Пример sql запроса для получений финальных настроек активного кошелька для пользователя с user_id=1
with t as (select wc.*,
                  wcd.deposit_wallet
           from wallet_currency_deposit_settings wcds
                    inner join wallet_currency_deposits wcd on wcd.id = wcds.wallet_currency_deposits_id
                    inner join wallet_currencies wc on wcd.currency_id = wc.id
           where (wcds.user_id is null or wcds.user_id = 1)
             and wcds.status = 'active'
             and wc.status = 'active'
           order by user_id
           limit 1)
select *
from t
;


-- Пример sql запроса для получений всех настроек связанных с пользователем user_id=1. Приоритет отдаётся локальным настройкам.
with t as (select *
           from wallet_currency_deposit_settings
           where (user_id is null or user_id = 1)
           order by user_id)
select *
from t
order by user_id
;

--
-- /Пример настройки кошельков
--