133 lines
4.6 KiB
SQL
133 lines
4.6 KiB
SQL
-- ==========================================
|
||
-- 1️⃣ Master Tenant
|
||
-- ==========================================
|
||
CREATE TABLE tenants (
|
||
tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
code VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(150) NOT NULL,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 2️⃣ Master Bank
|
||
-- ==========================================
|
||
CREATE TABLE banks (
|
||
bank_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
code VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(150) NOT NULL,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 3️⃣ Kredensial users
|
||
-- ==========================================
|
||
CREATE TABLE users (
|
||
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
|
||
bank_id UUID NOT NULL REFERENCES banks(bank_id),
|
||
partnerServiceId TEXT,
|
||
client_id TEXT,
|
||
client_secret TEXT,
|
||
api_key TEXT,
|
||
api_secret TEXT,
|
||
username TEXT,
|
||
password TEXT,
|
||
token_access TEXT,
|
||
token_refresh TEXT,
|
||
token_expiry TIMESTAMPTZ,
|
||
extra_json JSONB,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
UNIQUE (tenant_id),
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 3️⃣ Kredensial Master Access
|
||
-- ==========================================
|
||
CREATE TABLE access (
|
||
access_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
access_type VARCHAR(50) NOT NULL,
|
||
access_value TEXT NOT NULL,
|
||
is_created BOOLEAN NOT NULL DEFAULT FALSE,
|
||
is_updated BOOLEAN NOT NULL DEFAULT FALSE,
|
||
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
UNIQUE (user_id, access_type)
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 3️⃣ Kredensial User Access
|
||
-- ==========================================
|
||
CREATE TABLE user_access (
|
||
access_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
access_type VARCHAR(50) NOT NULL,
|
||
access_value TEXT NOT NULL,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
UNIQUE (user_id, access_type)
|
||
);
|
||
|
||
|
||
-- ==========================================
|
||
-- 4️⃣ Parameter
|
||
-- ==========================================
|
||
CREATE TABLE parameters (
|
||
parameter_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
param_key VARCHAR(100) NOT NULL,
|
||
param_value TEXT NOT NULL,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
UNIQUE (user_id, param_key)
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 5️⃣ Log Activity
|
||
-- ==========================================
|
||
CREATE TABLE activity_logs (
|
||
log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
action VARCHAR(100) NOT NULL,
|
||
status VARCHAR(50) NOT NULL,
|
||
message TEXT,
|
||
extra_json JSONB,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- ==========================================
|
||
-- 6️⃣ Dashboard
|
||
-- ==========================================
|
||
CREATE TABLE dashboards (
|
||
dashboard_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
title VARCHAR(150) NOT NULL,
|
||
value DECIMAL(20,2) NOT NULL,
|
||
date TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
|
||
|
||
-- ==========================================
|
||
-- 7️⃣ Auth Logs
|
||
-- ==========================================
|
||
CREATE TABLE auth_logs (
|
||
log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(user_id),
|
||
action VARCHAR(100) NOT NULL,
|
||
status VARCHAR(50) NOT NULL,
|
||
message TEXT,
|
||
extra_json JSONB,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|