Database Schema

This page describes the configuration and schema of the Huxley database.

Technology

Huxley is designed and optimized to use PostgreSQL. At this stage in development only the latest stable release has been tested.

Tables

app_perms

Application permission definitions.

Column	Type	PK	Required	Unique	Default
id	UUID	✅	✅	✅	gen_random_uuid()
name	TEXT		✅	✅
description	TEXT
metadata	JSONB		✅
is_active	BOOLEAN		✅		true
created_at	TIMESTAMPTZ		✅		now()
updated_at	TIMESTAMPTZ		✅		now()

app_roles

Application permission roles definitions.

Column	Type	PK	Required	Unique	Default
id	UUID	✅	✅	✅	gen_random_uuid()
name	TEXT		✅	✅
description	TEXT
metadata	JSONB		✅
is_active	BOOLEAN		✅		true
created_at	TIMESTAMPTZ		✅		now()
updated_at	TIMESTAMPTZ		✅		now()

app_role_perms

Application role permission assignments.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
app_role_id	UUID		app_roles(id)	✅
app_perm_id	UUID		app_perms(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

auth_provider_configs

Authentication providers.

Column	Type	PK	Required	Unique	Default
id	UUID	✅	✅	✅	gen_random_uuid()
name	TEXT		✅	✅
provider_type	SMALLINT		✅
is_active	BOOLEAN		✅		true
config	JSONB		✅
metadata	JSONB		✅
created_at	TIMESTAMPTZ		✅		now()
updated_at	TIMESTAMPTZ		✅		now()

git_providers

Git providers.

git_configs

GIt provider configurations.

orgs

Organizations

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
parent_id	UUID		orgs(id)
name	TEXT			✅	✅
slug	TEXT
is_active	BOOLEAN			✅		true
mappings	JSONB			✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

org_perms

Organization permissions.

Column	Type	PK	Required	Unique	Default
id	UUID	✅	✅	✅	gen_random_uuid()
name	TEXT		✅	✅
description	TEXT
is_active	BOOLEAN		✅		true
metadata	JSONB		✅
created_at	TIMESTAMPTZ		✅		now()
updated_at	TIMESTAMPTZ		✅		now()

org_roles

Organization permission roles.

Column	Type	PK	Required	Unique	Default
id	UUID	✅	✅	✅	gen_random_uuid()
name	TEXT		✅	✅
description	TEXT
is_active	BOOLEAN		✅		true
metadata	JSONB		✅
created_at	TIMESTAMPTZ		✅		now()
updated_at	TIMESTAMPTZ		✅		now()

org_role_perms

Organization role permission assignments.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
org_role_id	UUID		org_roles(id)	✅
org_perm_id	UUID		org_perms(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

org_tags

Organization tag assignments.

org_users

Organization user assignments.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
org_id	UUID		orgs(id)	✅
user_id	UUID		users(id)	✅
org_role_id	UUID		org_roles(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

org_variables

Organization variables.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
org_id	UUID		orgs(id)	✅
var_type	SMALLINT			✅
name	TEXT			✅
value	TEXT
secret	BYTEA
is_default	BOOLEAN			✅	✅	false
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

users

Users.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
email	TEXT			✅	✅
name	TEXT			✅
password	TEXT			✅
is_mfa_enabled	BOOLEAN			✅		false
mfa_secret	BYTEA
mfa_recovery_codes	BYTEA
mfa_recovery_codes_remaining	SMALLINT
is_active	BOOLEAN			✅		true
preferences	JSONB			✅
app_role_id	UUID		app_roles(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

user_auth_providers

User auth provider assignments.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
user_id	UUID		users(id)	✅
auth_provider_id	UUID		auth_providers(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

workflows

Workflow definitions.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
rev_id	UUID			✅	✅	gen_random_uuid()
org_id	UUID		orgs(id)	✅
org_vars	JSONB			✅
triggers	JSONB			✅
nodes	JSONB			✅
edges	JSONB			✅
status	SMALLINT			✅
workflow_folder_id	UUID		workflow_folders(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

workflow_execs

Workflow execution history.

workflow_folders

Workflow project folders.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
parent_id	UUID		workflow_folders(id)	✅
org_id	UUID		orgs(id)	✅
name	TEXT			✅
path	TEXT			✅
color	TEXT			✅
icon	TEXT			✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

workflow_projects

Workflow projects.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
project_type	SMALLINT			✅
org_id	UUID		orgs(id)
user_id	UUID		users(id)
name	TEXT			✅
description	TEXT
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

workflow_revs

Workflow revision history.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
workflow_id	UUID		workflow(id)	✅		gen_random_uuid()
org_id	UUID		orgs(id)	✅
org_vars	JSONB			✅
triggers	JSONB			✅
nodes	JSONB			✅
edges	JSONB			✅
status	SMALLINT			✅
workflow_folder_id	UUID		workflow_folders(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

workflow_tags

Workflow tag assignments.

Column	Type	PK	FK	Required	Unique	Default
id	UUID	✅		✅	✅	gen_random_uuid()
workflow_id	UUID		workflows(id)	✅
tag_id	UUID		tags(id)	✅
metadata	JSONB			✅
created_at	TIMESTAMPTZ			✅		now()
updated_at	TIMESTAMPTZ			✅		now()

​Technology

​Tables

​app_perms

​app_roles

​app_role_perms

​auth_provider_configs

​git_providers

​git_configs

​orgs

​org_perms

​org_roles

​org_role_perms

​org_tags

​org_users

​org_variables

​tags

​users

​user_auth_providers

​workflows

​workflow_execs

​workflow_folders

​workflow_projects

​workflow_revs

​workflow_tags

​Diagram