Product Metadata, Resources, and Roles

1. tbl_master_products — Product table

Purpose / Introduction

tbl_master_products stores product-level metadata used by Aventian to manage provisioning and behavior for each supported product (e.g., cleversort). This is the canonical registry of products under the Aventian umbrella.

Key fields (ERD-derived)

• id — product identifier (PK)
• name — product code or key (e.g., cleversort)
• base_url — product dashboard base URL (for redirects)
• logo, display_name, brand_color — UI metadata
• created_at, updated_at
• allowed_resource_type_ids (list / reference) — resource types that product uses (optional)

Usability & relationships

• tbl_master_products.id → referenced by tbl_master_resources.product_id, tbl_template_roles.product_id, tbl_roles.product_id, and tbl_team_product_assoc.product_id.
• On product onboarding, Aventian reads provisioning script references from this table to perform DB creation & seeding.

Cleversort example

A tbl_master_products row for Cleversort:

id	name	base_url	logo
2	Cleversort	https://qa.v2.cleversort.com	https://storage.googleapis.com/assets-aventian-qa/products/cleversort.svg

2. tbl_master_resources — Resource table

Purpose / Introduction

tbl_master_resources catalogs resource types / resource definitions exposed by a product. These are the objects on which privileges apply (e.g., project, task, board).

Key fields

• id — resource identifier (PK)
• name — human-friendly resource name (e.g., project, task)
• product_id — FK to tbl_master_products (which product owns this resource)
• parent_id — optional FK to another resource (for hierarchical resources)
• resource_type_id — type classification (maps to tbl_master_resource_type)
• is_active, icon_name, created_at, updated_at

Usability & relationships

• tbl_master_privileges.resource_id references this table (privileges are associated to resources).
• tbl_roles.resource_type_id or tbl_roles.product_id can reference resource type for scoping built-in or template roles.
• Resources help organize privileges and UI exposure.

Resource type

• Application resource: modules most commonly used within the application.
• Project resource: resources used within the project module.
• System resources: invisible resources handled by the application itself.
• Hybrid resources: partly within the application and within the project.

Cleversort example resources

A tbl_master_resources rows for Cleversort:

id	resource_name	product_name	parent_name	resource_type
5	Team Management	Cleversort		Application
6	Project Management	Cleversort		Project
7	Widget Management	Cleversort		Project
8	Task Management	Cleversort		Project
10	Settings	Cleversort	Team Management	Application
11	Notifications	Cleversort	Team Management	Application
12	Projects	Cleversort	Team Management	Application
13	Projects Hybrid	Cleversort	Team Management	Hybrid
14	Optional Fields	Cleversort	Project Management	Project
15	Custom Fields	Cleversort	Project Management	Project
16	Members	Cleversort	Project Management	Project
17	Folders	Cleversort	Team Management	Application
18	Optional Fields	Cleversort	Widget Management	Project
19	Custom Fields	Cleversort	Widget Management	Project

3. tbl_master_privileges — Master privileges table

Purpose / Introduction

tbl_master_privileges defines the atomic actions that can be granted on a resource (e.g., create_project, edit_task, view_board, transfer_ownership).

Key fields

• id — privilege id (PK)
• name — human-friendly name
• key — programmatic key (e.g., create_project)
• description
• resource_id — FK → tbl_master_resources.id
• is_active, created_at, updated_at

Usability & relationships

• Privileges are associated to roles via tbl_role_privilege_assoc and to template roles via tbl_template_role_privilege_assoc.
• tbl_user_direct_privilege allows assigning privilege directly to a user (bypassing role), optionally per team_id.

Cleversort example privileges

Some Cleversort privileges:

id	name	key	description	resource_type
37	Create New Custom Fields	project:customfields:create	Add new custom fields	Custom Fields
38	Edit Custom Fields	project:customfields:edit	Modify custom fields	Custom Fields
39	Delete Custom Fields	project:customfields:delete	Remove custom fields	Custom Fields
49	Add Custom Fields	widget:customfields:add	Add new custom fields to widget	Custom Fields
50	Edit Custom Fields	widget:customfields:edit	Edit custom fields of widget	Custom Fields
51	Delete Custom Fields	widget:customfields:delete	Delete custom fields of the widget	Custom Fields
41	Manage Folders	project:folder:manage	Full control over all folder operations in a project	Folders
40	Manage Project Members	project:member:manage	Add or remove members	Members
26	Team Notification (user specific)	team:notification:manage	To control project activity such as overdue task notification	Notifications
36	Optional Fields	project:optionalfields:edit	Modify optional project fields	Optional Fields
48	Optional Fields	widget:optionalfields:edit	Modify optional widget-level fields	Optional Fields
30	View Project	project:view	View project details	Project Management
31	Edit project metadata	project:edit	Edit project title	Project Management
32	Archive project	project:archive	Archive the project	Project Management
33	Clone Project	project:clone	Duplicate a project	Project Management
27	Projects	team:all:projects	List all projects and abitity to associate with the project	Projects
29	Create Project	team:project:create	Create a new project	Projects
34	Project color	project:color:own	Set a color for the project	Projects
35	Favorite Project	project:favourite:own	Make a project favourite/unfavourite	Projects
28	Projects	team:project:restricted		Projects Hybrid
21	View Settings	team:settings:view	View all the settings of organization	Settings
22	Edit optional fields	team:settings:optionalfields:edit	Modify oganization level optional fields	Settings
23	Create Custom fields	team:settings:customfields:create	add custom fields for organization	Settings
24	Edit Custom Fields	team:settings:customfields:edit	Update custom field configurations	Settings
25	Delete Custom Fields	team:settings:customfields:delete	Remove organization custom fields	Settings
53	View Task	task:view	View all tasks	Task Management
54	Add Task	task:add	Create a new task	Task Management
55	Add Stage	stage:add	Add a new stage	Task Management
56	Edit Task	task:edit	Update task details	Task Management
57	Edit Stage	stage:edit	Update stage details	Task Management
58	Edit Block	block:edit	Update Block details	Task Management
59	Delete Task	task:delete	Remove a task	Task Management
60	Delete Stage	stage:delete	Remove a stage	Task Management
42	View widget	widget:view	View widget within a project	Widget Management
43	Create new widget	widget:create	Add a new widget	Widget Management
44	Update widget	widget:edit	Edit widget details	Widget Management
45	Rearrange widget	widget:move	Move widget positions	Widget Management
46	Delete widget	widget:delete	Remove a widget	Widget Management
47	Clone widget	widget:clone	Duplicate a widget	Widget Management
52	Make widget Private	widget:private	Restrict widget visibility	Widget Management

4. tbl_roles — Built-in and Invisible System Roles

tbl_roles stores role definitions. Roles can be:

• Built-in (product-specific): preset roles with a fixed privileges set, visible in UI for assignment (but not editable).
• Invisible system roles: special roles not shown in UI and assigned by system logic (e.g., team owner gets Ownership Role).
• Custom roles: created by users (not covered here — product and team-specific).

Key fields (ERD)

• id — role id (PK)
• name — role name (e.g., Administrator, Member)
• description — explanation; invisible roles may have description invisible system role
• team_id — nullable; built-in/invisible roles have NULL team_id (global for product)
• product_id — FK → tbl_master_products (scopes role to product)
• resource_type_id — scope indicator
• is_visible — if false, not listed in UI (used for invisible roles)
• created_by, created_at, updated_by, updated_at

Built-in role characteristics

• description indicates Built-in-role
• team_id = NULL
• Pre-wired to a fixed set of privileges through tbl_role_privilege_assoc
• UI shows these roles but users cannot edit the role privilege set

Invisible system role characteristics

• description indicates invisible system role
• team_id = NULL
• is_visible = false (or similar flag)
• System logic assigns them to users (e.g., assign Ownership Role to team owner)
• Still wired to privileges via tbl_role_privilege_assoc

Relational associations

• Role ↔ Privilege: Many-to-many via tbl_role_privilege_assoc (role_id, privilege_id)
• Role ↔ User: Many-to-many via tbl_user_role_assoc (user_id, role_id, team_id) (note team_id set for per-team assignments)

Cleversort example privileges

id	name	description	product_name	resource_type
3	Admin	Built-in Role	Cleversort	Application
2	Member	Built-in Role	Aventian	Application
1	Administrator	Built-in Role	Aventian	Application
5	Collaborator	Built-in Role	Cleversort	Hybrid
4	Project Admin	Built-in Role	Cleversort	Project
23	Team Owner	Invisible system role	Aventian	system

5. tbl_template_roles — Template roles

Purpose

Template roles are product-scoped role blueprints. They define a set of privileges which are not directly assignable to users; instead, editing a template produces a custom role instantiated for a team.

Key fields

• id — template role id
• name, description
• product_id — FK to tbl_master_products
• resource_type_id
• created_by, created_at, updated_by, updated_at

Privilege association

• tbl_template_role_privilege_assoc (role_id, privilege_id) links template roles to privileges.

Behavior

• Template roles provide a reusable starting point.
• When a user edits or instantiates a template role for a team, a new tbl_roles record (custom role) is created with the template’s privileges copied.

Cleversort example privileges

id	name	description	product_name	resource_type
-1	Member	Cleversort Team Member	Cleversort	Application
-3	Editor	Aventian Editor	Aventian	Application
-2	Editor	Cleversort Project Editor	Cleversort	Project

6. Integration Checklist (Actionable)

Use this checklist to onboard Cleversort or similar product:

• Product registration & metadata

  • Add product to tbl_master_products with id, name, base_url, etc.
  • Register allowed resource types (if used) and link to product.

• Resources & privileges

  • Insert product resources into tbl_master_resources (projects, tasks, boards, etc.).
  • Insert master privileges into tbl_master_privileges and link to resources.

• Roles & templates

  • Create built-in roles in tbl_roles (product-level, team_id = NULL) and associate privileges via tbl_role_privilege_assoc.
  • Create invisible system role(s) in tbl_roles with resource_type_id = 4 and associate privileges.
  • Create template roles in tbl_template_roles and associate privileges via tbl_template_role_privilege_assoc.