81 lines
2.7 KiB
SQL
81 lines
2.7 KiB
SQL
create table if not exists users (
|
|
id text primary key,
|
|
email text not null unique,
|
|
username text not null unique,
|
|
password_hash text not null,
|
|
status text not null,
|
|
created_at timestamptz not null,
|
|
updated_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists admin_users (
|
|
id text primary key,
|
|
email text not null unique,
|
|
password_hash text not null,
|
|
role text not null,
|
|
status text not null,
|
|
created_at timestamptz not null,
|
|
updated_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists accounts (
|
|
id text primary key,
|
|
user_id text not null unique references users(id) on delete cascade,
|
|
account_number text not null unique,
|
|
currency char(3) not null,
|
|
status text not null,
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists transactions (
|
|
id text primary key,
|
|
type text not null,
|
|
status text not null,
|
|
reference text not null unique,
|
|
initiated_by_user_id text references users(id),
|
|
initiated_by_admin_id text references admin_users(id),
|
|
description text not null default '',
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists ledger_entries (
|
|
id text primary key,
|
|
transaction_id text not null references transactions(id) on delete cascade,
|
|
account_id text not null references accounts(id) on delete cascade,
|
|
entry_type text not null,
|
|
amount_minor bigint not null check (amount_minor > 0),
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists user_sessions (
|
|
id text primary key,
|
|
user_id text not null references users(id) on delete cascade,
|
|
token_hash text not null unique,
|
|
expires_at timestamptz not null,
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists admin_sessions (
|
|
id text primary key,
|
|
admin_user_id text not null references admin_users(id) on delete cascade,
|
|
token_hash text not null unique,
|
|
expires_at timestamptz not null,
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create table if not exists audit_logs (
|
|
id text primary key,
|
|
actor_admin_id text not null references admin_users(id),
|
|
action text not null,
|
|
target_type text not null,
|
|
target_id text not null,
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null
|
|
);
|
|
|
|
create index if not exists idx_accounts_account_number on accounts(account_number);
|
|
create index if not exists idx_ledger_entries_account_id_created_at on ledger_entries(account_id, created_at desc);
|
|
create index if not exists idx_transactions_created_at on transactions(created_at desc);
|
|
create index if not exists idx_user_sessions_token_hash on user_sessions(token_hash);
|
|
create index if not exists idx_admin_sessions_token_hash on admin_sessions(token_hash);
|
|
create index if not exists idx_audit_logs_created_at on audit_logs(created_at desc);
|