97 lines
2.9 KiB
SQL
97 lines
2.9 KiB
SQL
CREATE TABLE IF NOT EXISTS user(
|
|
id BLOB NOT NULL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_user_status_created_at ON user(status, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_permission_global(
|
|
user_id BLOB NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (user_id, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_permission_group(
|
|
user_id BLOB NOT NULL,
|
|
group_name TEXT NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (user_id, group_name, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_account(
|
|
id BLOB NOT NULL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
owner BLOB NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_service_account_status_created_at ON service_account(status, created_at);
|
|
|
|
CREATE INDEX idx_service_account_owner ON service_account(owner);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_account_permission_global(
|
|
service_account_id BLOB NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (service_account_id, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_account_permission_group(
|
|
service_account_id BLOB NOT NULL,
|
|
group_name TEXT NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (service_account_id, group_name, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_account(
|
|
id BLOB NOT NULL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
owner BLOB NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_agent_account_status_created_at ON agent_account(status, created_at);
|
|
|
|
CREATE INDEX idx_agent_account_owner ON agent_account(owner);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_account_permission_global(
|
|
agent_account_id BLOB NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (agent_account_id, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_account_permission_group(
|
|
agent_account_id BLOB NOT NULL,
|
|
group_name TEXT NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
PRIMARY KEY (agent_account_id, group_name, permission)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS credential(
|
|
credential_id BLOB NOT NULL PRIMARY KEY,
|
|
credential_hash TEXT NOT NULL,
|
|
account_id BLOB NOT NULL,
|
|
account_type TEXT NOT NULL,
|
|
credential_type TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
effective_at DATE NULL,
|
|
effective_through DATE NULL,
|
|
created_at DATETIME NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_credential_account_id ON credential(account_id, created_at);
|
|
CREATE INDEX idx_credential_account_type ON credential(account_type, created_at);
|
|
CREATE INDEX idx_credential_credential_type ON credential(credential_type, created_at);
|
|
CREATE INDEX idx_credential_credential_status ON credential(credential_status, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS tag(
|
|
tag_value TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_tag_created_at ON tag(created_at);
|