block-accounting/backend/migrations/blockd.sql

207 lines
7.3 KiB
MySQL
Raw Permalink Normal View History

2024-05-24 17:44:24 +00:00
create table if not exists users (
id uuid primary key ,
name varchar(250),
email varchar(200),
phone varchar(16),
tg varchar(200),
public_key bytea not null unique,
2024-05-24 23:39:32 +00:00
mnemonic varchar(500) not null,
2024-05-24 17:44:24 +00:00
seed bytea not null unique,
created_at timestamp default current_timestamp,
2024-05-25 20:38:47 +00:00
updated_at timestamp default current_timestamp,
2024-05-24 17:44:24 +00:00
activated_at timestamp default null
);
create index if not exists index_users_seed
on users (seed);
create index if not exists index_users_public_key
on users (public_key);
create index if not exists index_users_name
on users using hash (name);
create index if not exists index_users_seed
on users using hash (seed);
create table if not exists access_tokens (
user_id uuid not null references users(id),
token varchar(350) not null,
token_expired_at timestamp,
refresh_token varchar(350) not null,
refresh_token_expired_at timestamp,
created_at timestamp default current_timestamp,
remote_addr varchar(100)
);
create index if not exists index_access_tokens_token_refresh_token
on access_tokens (token, refresh_token);
create index if not exists index_access_tokens_token_refresh_token_exp
on access_tokens (token, refresh_token, token_expired_at, refresh_token_expired_at);
create table if not exists organizations (
id uuid primary key unique,
name varchar(300) default 'My Organization' not null,
address varchar(750) not null,
wallet_seed bytea not null,
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp
);
create index if not exists index_organizations_id
on organizations (id);
create table employees (
id uuid primary key,
2024-05-26 09:26:53 +00:00
name varchar(250) default 'Employee',
2024-05-24 17:44:24 +00:00
user_id uuid,
organization_id uuid not null references organizations(id),
2024-05-26 09:26:53 +00:00
wallet_address bytea not null,
2024-05-24 17:44:24 +00:00
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp
);
create index if not exists index_employees_id_organization_id
on employees (id, organization_id);
create index if not exists index_user_id_organization_id
on employees (user_id, organization_id);
create table organizations_users (
organization_id uuid not null references organizations(id),
2024-05-26 09:26:53 +00:00
user_id uuid default null,
2024-05-24 17:44:24 +00:00
employee_id uuid default null,
position varchar(300),
added_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
deleted_at timestamp default null,
is_admin bool default false,
is_owner bool default false,
2024-05-26 09:26:53 +00:00
primary key(organization_id, user_id, employee_id)
2024-05-24 17:44:24 +00:00
);
create index if not exists index_organizations_users_organization_id_user_id_is_admin
on organizations_users (organization_id, user_id, is_admin);
create index if not exists index_organizations_users_organization_id_user_id
on organizations_users (organization_id, user_id);
create index if not exists index_organizations_users_organization_id_employee_id
on organizations_users (organization_id, employee_id);
create index if not exists index_transactions_confirmations_tx_id_user_id_organization_id
on transactions_confirmations (tx_id, user_id, organization_id);
create table multisigs (
id uuid primary key,
2024-05-25 22:55:35 +00:00
organization_id uuid not null references organizations(id),
2024-05-26 18:20:14 +00:00
address bytea not null,
confirmations smallint default 0,
2024-05-26 00:30:35 +00:00
title varchar(350) default 'New Multi-Sig',
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp
2024-05-24 17:44:24 +00:00
);
create table multisig_owners (
multisig_id uuid references multisigs(id),
2024-05-24 22:45:56 +00:00
owner_id uuid references users(id),
2024-05-24 17:44:24 +00:00
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
primary key (multisig_id, owner_id)
);
2024-05-26 00:30:35 +00:00
create index if not exists idx_multisig_owners_multisig_id
2024-05-25 22:55:35 +00:00
on multisig_owners (multisig_id);
2024-05-26 00:30:35 +00:00
create index if not exists idx_multisig_owners_owner_id
2024-05-25 22:55:35 +00:00
on multisig_owners (owner_id);
2024-05-24 17:44:24 +00:00
create table multisig_confirmations (
multisig_id uuid references multisigs(id),
2024-05-24 22:45:56 +00:00
owner_id uuid references users(id),
2024-05-29 22:06:37 +00:00
confirmed_entity_id uuid not null,
confirmed_entity_type smallint default 0,
created_at timestamp default current_timestamp
2024-05-24 17:44:24 +00:00
primary key (multisig_id, owner_id)
2024-05-25 22:55:35 +00:00
);
2024-05-26 00:30:35 +00:00
create index if not exists idx_multisig_confirmations_owners_multisig_id
2024-05-25 22:55:35 +00:00
on multisig_confirmations (multisig_id);
2024-05-26 00:30:35 +00:00
create index if not exists idx_multisig_confirmations_owners_owner_id
2024-05-25 22:55:35 +00:00
on multisig_confirmations (owner_id);
2024-05-27 21:29:05 +00:00
create index if not exists idx_multisig_confirmations_owners_multisig_id_owner_id
on multisig_confirmations (multisig_id, owner_id);
2024-05-29 22:06:37 +00:00
create table multisig_confirmations_counter (
multisig_id uuid references multisigs(id),
confirmed_entity_id uuid not null,
confirmed_entity_type smallint default 0,
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
count bigint default 0
);
create index if not exists idx_multisig_confirmations_counter_multisig_id_confirmed_entity_id
on multisig_confirmations (multisig_id, confirmed_entity_id);
2024-05-25 22:55:35 +00:00
create table invites (
2024-05-26 00:30:35 +00:00
link_hash varchar(64) primary key,
2024-05-26 19:39:25 +00:00
organization_id uuid,
2024-05-25 22:55:35 +00:00
created_by uuid not null references users(id),
created_at timestamp default current_timestamp,
expired_at timestamp default null,
used_at timestamp default null
2024-05-27 21:29:05 +00:00
);
create table payrolls (
id uuid primary key,
title varchar(250) default 'New Payroll',
description text not null,
address bytea not null,
payload bytea default null,
organization_id uuid not null references organizations(id),
tx_index bytea default null,
multisig_id uuid references multisigs(id),
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp
);
2024-05-28 22:46:30 +00:00
create table if not exists transactions (
id uuid primary key,
description text default 'New Transaction',
organization_id uuid not null,
created_by uuid not null,
amount decimal default 0,
to_addr bytea not null,
tx_index bigint default 0,
max_fee_allowed decimal default 0,
deadline timestamp default null,
confirmations_required bigint default 1,
2024-05-29 22:06:37 +00:00
multisig_id uuid not null,
2024-05-28 22:46:30 +00:00
multisig_id uuid default null,
status int default 0,
created_at timestamp default current_timestamp,
updated_at timestamp default current_timestamp,
confirmed_at timestamp default null,
cancelled_at timestamp default null,
commited_at timestamp default null
);
create index if not exists index_transactions_id_organization_id
on transactions (organization_id);
create index if not exists index_transactions_id_organization_id_created_by
on transactions (organization_id, created_by);
create index if not exists index_transactions_organization_id_deadline
on transactions (organization_id, deadline);