Workspace Management

Overview

Workspace Management is an important component in the public system, responsible for managing user workspaces, project organization, and team information. It provides the following main functions:

Member permission management
Organization hierarchy management (workspace, project, library)
User-related functions
Notification management

User System
Message Notification System

Business Database
Message Queue
Cache

Login calls the login interface of the user system to obtain user information, where the system or page layout menu rendering is determined by user role/user type and other information through the frontend. If there are fields that are strongly associated with Flow business in the future and not suitable for direct maintenance in the user system, this part of the logic can be handed over to the Workspace module, which is not considered for now.

Workspace Management

Workspace is the top-level organizational structure in the Flow system. A workspace can contain multiple projects, and each workspace is completely independent. The permission control described later is all based on permissions within the workspace, while permission management for the workspace itself is separate.

Workspace includes the following operations:

Create workspace
Query workspace list
Delete workspace
Update workspace information
- Set workspace administrators
- Set workspace members
Permission control
- Owner and workspace administrators can modify workspace information
Transfer workspace (Owner change)

Permission Inheritance Strategy

Permission inheritance mode

Workspace
- This is the highest level, typically defining a user's roles and permissions across the entire workspace. For example, administrators can manage all projects and users.
Project
- At the project level, users can be assigned different roles, such as administrator, editor, reader, etc. These roles determine the user's operational permissions within the project.
Repository
- Within a library in a project, permissions can be further refined. For example, even if a user has read and write permissions in a project, they may only be given read permission in a specific library. Permission inheritance rules
Principle of least privilege In the absence of explicitly set library-level permissions, users will inherit project-level permissions. If lower permissions are set at the library level, the library-level permissions will override the project-level permissions.
Highest permission priority If a user has higher permissions in a project but is assigned lower permissions in a library, the lower permissions in the library will be applied. If no permissions are explicitly set in the library, the user will use the higher permissions from the project.

API Interface Design

Create Workspace

Description: Create a new workspace
Method: POST
URL: /api/v1/workspaces

Request Example:

json

{
  "name": "Example Workspace",
  "description": "Workspace description information",
  "owner_id": "Main user UUID",
  "tenant_id": "Tenant UUID",
  "members": ["Member UUID1", "Member UUID2"]  // Optional initial member list
}

Response Example:

json

{
  "code": 200,
  "message": "Workspace created successfully",
  "data": {
    "workspace_id": "Generated workspace UUID",
  }
}

Query Workspace List

Description: Get a list of workspaces accessible to the current user
Method: GET
URL: /api/v1/workspaces

Response Example:

json

[
  {
    "workspace_id": "UUID",
    "name": "Example Workspace",
    "description": "Workspace description information",
    "owner_id": "Main user UUID",
    "created_at": "Timestamp"
  }
]

Get Workspace Details

Description: Get detailed information about a specified workspace
Method: GET
URL: /api/v1/workspaces/

Response Example:

json

{
  "workspace_id": "UUID",
  "name": "Example Workspace",
  "description": "Workspace description information",
  "owner_id": "Main user UUID",
  "members": [
    {
      "user_id": "UUID",
      "role": "OWNER | ADMIN | EDITOR | VIEWER"
    }
  ],
  "created_at": "Timestamp",
  "updated_at": "Timestamp"
}

Update Workspace Information

Description: Update the basic information and member configuration of a workspace
Method: PATCH
URL: /api/v1/workspaces/

Request Example:

json

{
  "name": "Updated workspace name",
  "description": "Updated description information",
  "admin_ids": ["Admin UUID1", "Admin UUID2"],
  "member_ids": ["Member UUID1", "Member UUID2"]
}

Response Example:

json

{
  "code": 200,
  "message": "Workspace information updated successfully"
}

Delete Workspace
- Description: Delete a specified workspace
- Method: DELETE
- URL: /api/v1/workspaces/
- Response Example:
  json
```
{
  "code": 200,
  "message": "Workspace deleted successfully"
}
```

Transfer Workspace

Description: Change the owner of a workspace
Method: PUT
URL: /api/v1/workspaces/{workspaceId}/transfer

Request Example:

json

{
  "new_owner_id": "New owner UUID",
  "reason": "Transfer reason explanation"
}

Response Example:

json

{
  "code": 200,
  "message": "Workspace ownership transferred successfully"
}

Database Design

Workspace Table (workspaces)

sql

CREATE TABLE workspaces (
  workspace_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  type INT NOT NULL, -- Workspace type, 1 for LCMS, 2 for TMS, default 1
  name VARCHAR(255) NOT NULL,
  description TEXT,
  owner_id UUID NOT NULL,  -- No longer needs foreign key constraint
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Workspace Members Table (workspace_members)

sql

CREATE TABLE workspace_members (
  workspace_id UUID NOT NULL,
  user_id UUID NOT NULL,
  role VARCHAR(50) DEFAULT 'MEMBER',  -- Role: OWNER, ADMIN, EDITOR, VIEWER
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (workspace_id, user_id),
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

Permission Management

Based on the different roles of users in this system, manage the resources that users can access and the operations they can perform, essentially permission management based on RBAC.

API Interface Design

User Role Assignment Interface
- Description: Assign system roles to users in a specified workspace
- Method: POST
- URL: /api/v1/workspaces/{workspaceId}/users/{userId}/roles
- Request Example:
  json
```
{
  "roles": ["EDITOR", "VIEWER"]
}
```
- Response Example:
  json
```
{
  "code": 200,
  "message": "User roles updated successfully"
}
```
Role Permission Configuration Interface (globally unified, no need to specify a workspace; in the early stages of the project, this can be fixed without the need for dynamic configuration)
- Description: Configure the permissions that a specified role has
- Method: PUT
- URL: /api/v1/roles/{roleId}/permissions
- Request Example:
  json
```
{
  "permissions": ["CREATE_PROJECT", "DELETE_PROJECT", "UPDATE_USER"]
}
```
- Response Example:
  json
```
{
  "code": 200,
  "message": "Role permissions updated successfully"
}
```
Current User Permission Query Interface
- Description: Get the permissions that the current user has in the current workspace
- Method: GET
- URL: /api/v1/workspaces/{workspaceId}/users/{userId}/permissions
- Response Example:
  json
```
{
  "code": 200,
  "message": "Current user permissions retrieved successfully",
  "data": [
    "CREATE_PROJECT",
    "VIEW_CONTENT",
    "MANAGE_TEAM"
  ]
}
```

Database Design

Roles Table (workspace_roles)

sql

CREATE TABLE workspace_roles (
  role_id UUID PRIMARY KEY,
  role_name VARCHAR(255) UNIQUE NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

User Role Mapping Table (workspace_user_roles)

sql

CREATE TABLE workspace_user_roles (
  workspace_id UUID NOT NULL,  -- Added workspace ID for implementing permission isolation
  user_id UUID NOT NULL,
  role_id UUID NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (workspace_id, user_id, role_id),
  CONSTRAINT fk_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id),
  CONSTRAINT fk_workspace_roles FOREIGN KEY (role_id) REFERENCES workspace_roles(role_id)
);

Role Permission Mapping Table (workspace_role_permissions)

sql

CREATE TABLE workspace_role_permissions (
  role_id UUID NOT NULL,
  permission VARCHAR(100) NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, permission),
  CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES workspace_roles(role_id)
);

Permission Deny Rules Table (workspace_deny_rules)

sql

CREATE TABLE workspace_deny_rules (
    rule_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    scope_type VARCHAR(50) NOT NULL, -- 'WORKSPACE', 'PROJECT', 'REPOSITORY'
    scope_id UUID NOT NULL,          -- workspace_id, project_id or repository_id
    permission VARCHAR(100) NOT NULL, -- Specific denied permission
    created_by UUID NOT NULL,        -- User who created the rule
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason TEXT,                     -- Record the reason for creating this rule
    CONSTRAINT uq_deny_rule UNIQUE (user_id, scope_type, scope_id, permission)
);

-- Create indexes to improve query performance
CREATE INDEX idx_deny_rules_user ON workspace_deny_rules(user_id);
CREATE INDEX idx_deny_rules_scope ON workspace_deny_rules(scope_type, scope_id);

Permission Check Implementation Description

When actually using these permission tables, the system should perform permission checks according to the following logic:

Check Order
- First check if the user has explicit permission settings at the repository level
- If there are no repository-level permissions, check project-level permissions
- If there are no project-level permissions, check workspace-level permissions
- Finally, check if there are deny rules for that permission
Permission Determination Rules
- If explicit permission settings are found at a certain level, use that permission
- If no explicit permission settings are found, inherit the permission from the previous level
- If there are deny rules for specific permissions, those permissions will be removed
- If no permission settings are found at all, access is denied by default

Permission Inheritance Example

sql

WITH user_permissions AS (
    -- Check repository-level permissions
    SELECT 'REPOSITORY' as level, role, array_agg(p.permission) as permissions
    FROM workspace_repository_members m
    JOIN workspace_role_permissions p ON m.role = p.role
    WHERE repository_id = :repository_id AND user_id = :user_id
    GROUP BY role
    
    UNION
    
    -- Check project-level permissions
    SELECT 'PROJECT' as level, role, array_agg(p.permission) as permissions
    FROM workspace_project_members m
    JOIN workspace_role_permissions p ON m.role = p.role
    WHERE project_id = :project_id AND user_id = :user_id
    GROUP BY role
    
    UNION
    
    -- Check workspace-level permissions
    SELECT 'WORKSPACE' as level, role, array_agg(p.permission) as permissions
    FROM workspace_members m
    JOIN workspace_role_permissions p ON m.role = p.role
    WHERE workspace_id = :workspace_id AND user_id = :user_id
    GROUP BY role
),
denied_permissions AS (
    -- Get all applicable deny rules
    SELECT permission
    FROM workspace_deny_rules
    WHERE user_id = :user_id
    AND (
        (scope_type = 'REPOSITORY' AND scope_id = :repository_id) OR
        (scope_type = 'PROJECT' AND scope_id = :project_id) OR
        (scope_type = 'WORKSPACE' AND scope_id = :workspace_id)
    )
)
SELECT 
    up.level,
    up.role,
    array(
        SELECT unnest(up.permissions)
        EXCEPT
        SELECT permission FROM denied_permissions
    ) as effective_permissions
FROM user_permissions up
ORDER BY 
    CASE up.level
        WHEN 'REPOSITORY' THEN 1
        WHEN 'PROJECT' THEN 2
        WHEN 'WORKSPACE' THEN 3
    END
LIMIT 1;

Permission Deny Rules API Interface Design

1. Create Deny Rule

Description: Create a new permission deny rule
Method: POST
URL: /api/v1/deny-rules

Request Example:

json

{
    "user_id": "UUID",
    "scope_type": "WORKSPACE",
    "scope_id": "workspace-uuid",
    "permission": "CREATE_PROJECT",
    "reason": "Temporarily restrict user's project creation permission"
}

Response Example:

json

{
    "code": 200,
    "message": "Deny rule created successfully",
    "data": {
        "rule_id": "Generated rule UUID"
    }
}

2. Delete Deny Rule

Description: Delete a specified permission deny rule
Method: DELETE
URL: /api/v1/deny-rules/

Response Example:

json

{
    "code": 200,
    "message": "Deny rule deleted successfully"
}

3. Query User's Deny Rules

Description: Query the deny rules for a specified user in a specific scope
Method: GET
URL: /api/v1/users/{userId}/deny-rules?scope_type=WORKSPACE&scope_id=xxx

Response Example:

json

{
    "code": 200,
    "data": {
        "rules": [
            {
                "rule_id": "UUID",
                "scope_type": "WORKSPACE",
                "scope_id": "workspace-uuid",
                "permission": "CREATE_PROJECT",
                "created_at": "2024-03-20T10:00:00Z",
                "reason": "Temporarily restrict user's project creation permission"
            }
        ]
    }
}

Project Management

Project management in LCMS is responsible for creating and managing project-related containers and associating them with the corresponding workspace. It provides CRUD operations for containers, member management, etc., but is limited to container structure and does not involve internal content, so it essentially only maintains the project structure.

The project structure is divided into Project and Repository. Project is a container for Repository, and Repository is the content of the project. Repository can be understood as a document in the project, and Project can be understood as a folder in the project. They both have extension tables to maintain more extensible metadata. Permissions are integrated, which means that if a user has permission for a Project, they also have permission for all Repositories under that Project, unless Repository permissions are set separately.

Project Management API Interface Design

1. Create Project

Description: Create a new project under a specified workspace
Method: POST
URL: /api/v1/workspaces/{workspaceId}/projects
Request Example:

json

{
  "name": "Example Project",
  "description": "Project description information",
  "members": ["Member UUID1", "Member UUID2"]  // Optional initial member list
}

Response Example:

json

{
  "code": 200,
  "message": "Project created successfully",
  "data": {
    "project_id": "Generated project UUID",
  }
}

2. Query Project List

Description: Get all projects under a specified workspace
Method: GET
URL: /api/v1/workspaces/{workspaceId}/projects
Response Example:

json

[
  {
    "project_id": "UUID",
    "name": "Example Project",
    "description": "Project description information",
    "owner_id": "Project owner UUID",
    "created_at": "Timestamp"
  }
]

3. Get Project Details

Description: Get detailed information about a specified project, including project information and member list
Method: GET
URL: /api/v1/workspaces/{workspaceId}/projects/
Response Example:

json

{
  "project_id": "UUID",
  "name": "Example Project",
  "description": "Project description information",
  "owner_id": "Project owner UUID",
  "members": [
    {
      "user_id": "UUID",
      "role": "OWNER | ADMIN | MEMBER"
    }
  ],
  "created_at": "Timestamp",
  "updated_at": "Timestamp"
}

4. Update Project

Description: Update the basic information and member configuration of a project
Method: PATCH
URL: /api/v1/workspaces/{workspaceId}/projects/
Request Example:

json

{
  "name": "Updated project name",
  "description": "Updated project description",
  "member_ids": ["Member UUID1", "Member UUID2"]
}

Response Example:

json

{
  "code": 200,
  "message": "Project updated successfully"
}

5. Delete Project

Description: Delete a specified project
Method: DELETE
URL: /api/v1/workspaces/{workspaceId}/projects/
Response Example:

json

{
  "code": 200,
  "message": "Project deleted successfully"
}

Repository Management API Interface Design

Repository (Repository) as a document management unit in the project, mainly used to manage content documents in the project.

1. Create Document

Description: Create a new document under a specified project
Method: POST
URL: /api/v1/workspaces/{workspaceId}/projects/{projectId}/repositories
Request Example:

json

{
  "name": "Repository title",
}

Response Example:

json

{
  "code": 200,
  "message": "Repository created successfully",
  "data": {
    "repository_id": "Repository UUID",
  }
}

Permission Adjustment Interface

Adjust Project Member Permissions Interface

Description: Used to adjust a member's permissions at the project level in a specified project
Method: PATCH
URL: /api/v1/workspaces/{workspaceId}/projects/{projectId}/members/{memberId}/permissions

Request Example:

json

{
  "permissions": ["ADMIN", "MEMBER"]
}

Response Example:

json

{
  "code": 200,
  "message": "Project member permissions updated successfully"
}

Adjust Repository Member Permissions Interface

Description: Used to adjust a member's permissions in a specified repository
Method: PATCH
URL: /api/v1/workspaces/{workspaceId}/projects/{projectId}/repositories/{repositoryId}/members/{memberId}/permissions

Request Example:

json

{
  "permissions": ["EDITOR", "VIEWER"]
}

Response Example:

json

{
  "code": 200,
  "message": "Repository member permissions updated successfully"
}

Database Design

Workspace Projects Table (workspace_projects)

sql

CREATE TABLE workspace_projects (
  project_id UUID PRIMARY KEY,
  workspace_id UUID NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  owner_id UUID NOT NULL,  -- No longer needs foreign key constraint
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_project_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(workspace_id)
);

Project Repositories Table (workspace_repositories)

sql

CREATE TABLE workspace_repositories (
  repository_id UUID PRIMARY KEY,
  project_id UUID NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  creator_id UUID NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_repository_project FOREIGN KEY (project_id) REFERENCES workspace_projects(project_id)
);

Extended Project Metadata Table: Used to store additional metadata for projects (key-value structure)

sql

CREATE TABLE workspace_project_metadata (
  project_id UUID NOT NULL,
  meta_key VARCHAR(255) NOT NULL,
  meta_value TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (project_id, meta_key),
  CONSTRAINT fk_workspace_project_metadata FOREIGN KEY (project_id) REFERENCES workspace_projects(project_id)
);

Extended Repository Metadata Table: Used to store additional metadata for repositories (key-value structure)

sql

CREATE TABLE workspace_repository_metadata (
  repository_id UUID NOT NULL,
  meta_key VARCHAR(255) NOT NULL,
  meta_value TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (repository_id, meta_key),
  CONSTRAINT fk_workspace_repository_metadata FOREIGN KEY (repository_id) REFERENCES workspace_repositories(repository_id)
);

Project Member Permissions Table (workspace_project_members)

sql

CREATE TABLE workspace_project_members (
  project_id UUID NOT NULL,
  user_id UUID NOT NULL,
  role VARCHAR(50) NOT NULL,  -- OWNER, ADMIN, EDITOR, VIEWER
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (project_id, user_id),
  CONSTRAINT fk_project_member_project FOREIGN KEY (project_id) REFERENCES workspace_projects(project_id)
);

CREATE INDEX idx_project_members_user ON workspace_project_members(user_id);

Repository Member Permissions Table (workspace_repository_members)

sql

CREATE TABLE workspace_repository_members (
  repository_id UUID NOT NULL,
  user_id UUID NOT NULL,
  role VARCHAR(50) NOT NULL,  -- OWNER, ADMIN, EDITOR, VIEWER
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (repository_id, user_id),
  CONSTRAINT fk_repository_member_repository FOREIGN KEY (repository_id) REFERENCES workspace_repositories(repository_id)
);

CREATE INDEX idx_repository_members_user ON workspace_repository_members(user_id);

Notification System

The notification system is responsible for handling the message notification functionality of the entire LCMS platform. The system uses a message queue for decoupling, implementing asynchronous notification processing. The main functions include:

Receiving and processing notifications from other systems
Managing user notification subscriptions
Processing notification sending and status tracking
Maintaining notification history records

API Interface Design

1. Get User Notification List

Description: Get all notifications for a specified user (supports pagination)
Method: GET
URL: /api/v1/users/{userId}/notifications

Query Parameters:

json

{
  "page": 1,
  "page_size": 20,
  "status": "UNREAD | READ | ALL",
  "type": "ALERT | INFO | WARNING | ALL"
}

Response Example:

json

{
  "code": 200,
  "message": "Notification list retrieved successfully",
  "data": {
    "total": 100,
    "items": [
      {
        "notification_id": "UUID",
        "title": "System Notification",
        "content": "Your operation was successful",
        "type": "INFO",
        "category": {
          "id": "UUID",
          "name": "System Notification"
        },
        "status": "UNREAD",
        "created_at": "2023-10-01T12:00:00Z"
      }
    ]
  }
}

2. Update Notification Status

Description: Update the status of a notification (e.g., mark as read)
Method: PATCH
URL: /api/v1/notifications/
Request Example:
json
```
{
  "status": "READ"
}
```

Response Example:

json

{
  "code": 200,
  "message": "Notification status updated successfully"
}

3. Manage Notification Subscriptions

Description: Manage user subscriptions to notifications in a specific scope
Method: POST
URL: /api/v1/notification-subscriptions

Request Example:

json

{
  "user_id": "UUID",
  "scope": {
    "type": "WORKSPACE | PROJECT | REPOSITORY",
    "workspace_id": "UUID",
    "project_id": "UUID",      // Optional, required when type is PROJECT or REPOSITORY
    "repository_id": "UUID"    // Optional, required when type is REPOSITORY
  },
  "category_ids": ["UUID1", "UUID2"]
}

Response Example:

json

{
  "code": 200,
  "message": "Subscription set successfully"
}

4. Cancel Notification Subscription

Description: Cancel user subscriptions to notifications in a specific scope
Method: DELETE
URL: /api/v1/notification-subscriptions

Request Example:

json

{
  "user_id": "UUID",
  "scope": {
    "type": "WORKSPACE | PROJECT | REPOSITORY",
    "workspace_id": "UUID",
    "project_id": "UUID",      // Optional
    "repository_id": "UUID"    // Optional
  },
  "category_ids": ["UUID1", "UUID2"]
}

Response Example:

json

{
  "code": 200,
  "message": "Subscription canceled successfully"
}

Database Design

Notifications Table (notifications)

sql

CREATE TABLE notifications (
  notification_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  category_id UUID NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  type VARCHAR(50) NOT NULL,  -- ALERT | INFO | WARNING
  status VARCHAR(20) DEFAULT 'UNREAD',  -- UNREAD | READ | FAILED | SENT
  scope JSONB NOT NULL,  -- Store notification-related scope information
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notification_category FOREIGN KEY (category_id) REFERENCES notification_categories(category_id)
);

CREATE INDEX idx_notifications_user_status ON notifications(user_id, status);
CREATE INDEX idx_notifications_created_at ON notifications(created_at);

Notification Categories Table (notification_categories)

sql

CREATE TABLE notification_categories (
  category_id UUID PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  description TEXT,
  is_system BOOLEAN DEFAULT false,  -- Whether it is a system preset category
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Notification Subscriptions Table (notification_subscriptions)

sql

CREATE TABLE notification_subscriptions (
  subscription_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  category_id UUID NOT NULL,
  scope JSONB NOT NULL,  -- Store subscription scope: workspace, project, or repository level
  subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_subscription_category FOREIGN KEY (category_id) REFERENCES notification_categories(category_id),
  CONSTRAINT uq_subscription_scope UNIQUE (user_id, category_id, (scope->>'type'), (scope->>'workspace_id'), (scope->>'project_id'), (scope->>'repository_id'))
);

CREATE INDEX idx_subscriptions_user ON notification_subscriptions(user_id);
CREATE INDEX idx_subscriptions_scope ON notification_subscriptions USING gin(scope);

Notification Logs Table (notification_logs)

sql

CREATE TABLE notification_logs (
  log_id UUID PRIMARY KEY,
  notification_id UUID NOT NULL,
  attempt_count INT DEFAULT 1,
  status VARCHAR(20) NOT NULL,  -- SUCCESS | FAILED
  error_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_log_notification FOREIGN KEY (notification_id) REFERENCES notifications(notification_id)
);

CREATE INDEX idx_notification_logs_notification ON notification_logs(notification_id);

Message Queue Interface

The system receives notification requests from other systems through a message queue, with the following message format:

json

{
  "message_id": "UUID",
  "event_type": "NOTIFICATION",
  "payload": {
    "user_id": "UUID",
    "category_id": "UUID",
    "title": "Notification Title",
    "content": "Notification Content",
    "type": "ALERT | INFO | WARNING",
    "scope": {
      "type": "WORKSPACE | PROJECT | REPOSITORY",
      "workspace_id": "UUID",
      "project_id": "UUID",      // Optional
      "repository_id": "UUID"    // Optional
    }
  },
}

Notification System Flow Chart

Dictionary Service

The dictionary service is responsible for providing dictionary (enumeration) services to various services in Flow. It retrieves dictionary values based on dictionary keys. Dictionary item values support various data types, including strings, numbers, booleans, JSON, etc. Dictionary item values support internationalization and sorting.

Database Design

Dictionaries Table (dictionaries)

sql

CREATE TABLE dictionaries (
  dictionary_id UUID PRIMARY KEY,
  code VARCHAR(100) NOT NULL,  -- Dictionary code, used for API calls
  name VARCHAR(255) NOT NULL,  -- Dictionary name
  description TEXT,            -- Dictionary description
  workspace_id UUID,           -- Workspace ID, null indicates tenant-level dictionary
  is_system BOOLEAN DEFAULT false,  -- Whether it is a system preset dictionary (currently users are not allowed to create dictionaries, so this should all be true for now)
  status VARCHAR(20) DEFAULT 'ENABLED',  -- Status: ENABLED | DISABLED
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_dictionary_code UNIQUE (code, workspace_id)
);

CREATE INDEX idx_dictionaries_workspace ON dictionaries(workspace_id);

Dictionary Items Table (dictionary_items)

sql

CREATE TABLE dictionary_items (
  item_id UUID PRIMARY KEY,
  dictionary_id UUID NOT NULL,
  value_type VARCHAR(20) NOT NULL,  -- Value type: STRING | NUMBER | BOOLEAN | JSON
  value_string VARCHAR(1000),       -- String value
  value_number DECIMAL(20, 6),      -- Number value
  value_boolean BOOLEAN,            -- Boolean value
  value_json JSONB,                 -- JSON value
  sort_order INT DEFAULT 0,         -- Sort order
  status VARCHAR(20) DEFAULT 'ENABLED',  -- Status: ENABLED | DISABLED
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dictionary_item FOREIGN KEY (dictionary_id) REFERENCES dictionaries(dictionary_id),
  CONSTRAINT uq_dictionary_item_value UNIQUE (dictionary_id, value_string, value_number, value_boolean)
);

CREATE INDEX idx_dictionary_items_dictionary ON dictionary_items(dictionary_id);
CREATE INDEX idx_dictionary_items_sort ON dictionary_items(dictionary_id, sort_order);

Dictionary Item Internationalization Table (dictionary_item_i18n)

sql

CREATE TABLE dictionary_item_i18n (
  i18n_id UUID PRIMARY KEY,
  item_id UUID NOT NULL,
  locale VARCHAR(20) NOT NULL,  -- Language code, such as zh_CN, en_US
  label VARCHAR(500) NOT NULL,  -- Display label
  description TEXT,             -- Description
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dictionary_item_i18n FOREIGN KEY (item_id) REFERENCES dictionary_items(item_id),
  CONSTRAINT uq_dictionary_item_locale UNIQUE (item_id, locale)
);

CREATE INDEX idx_dictionary_item_i18n_item ON dictionary_item_i18n(item_id);
CREATE INDEX idx_dictionary_item_i18n_locale ON dictionary_item_i18n(locale);

API Interface Design

1. Create Dictionary (not implemented in this phase)

Description: Create a new dictionary
Method: POST
URL: /api/v1/dictionaries

Request Example:

json

{
  "code": "user_status",
  "name": "User Status",
  "description": "Enumeration of user statuses in the system",
  "workspace_id": "UUID"  // Optional
}

Response Example:

json

{
  "code": 200,
  "message": "Dictionary created successfully",
  "data": {
    "dictionary_id": "Generated dictionary UUID"
  }
}

2. Add Dictionary Item (not implemented in this phase)

Description: Add a dictionary item to a specified dictionary
Method: POST
URL: /api/v1/dictionaries/{dictionaryId}/items

Request Example:

json

{
  "value_type": "STRING",
  "value_string": "ACTIVE",
  "sort_order": 1,
  "i18n": [
    {
      "locale": "zh_CN",
      "label": "激活",
      "description": "User is in active state"
    },
    {
      "locale": "en_US",
      "label": "Active",
      "description": "User is in active state"
    }
  ]
}

Response Example:

json

{
  "code": 200,
  "message": "Dictionary item added successfully",
  "data": {
    "item_id": "Generated dictionary item UUID"
  }
}

3. Get Dictionary List (not implemented in this phase)

Description: Get a list of dictionaries, supports filtering by workspace
Method: GET
URL: /api/v1/dictionaries?workspace_id=xxx

Response Example:

json

{
  "code": 200,
  "data": {
    "items": [
      {
        "dictionary_id": "UUID",
        "code": "user_status",
        "name": "User Status",
        "description": "Enumeration of user statuses in the system",
        "is_system": false,
        "status": "ENABLED",
        "created_at": "2024-03-20T10:00:00Z"
      }
    ]
  }
}

4. Get Dictionary Items List (not implemented in this phase)

Description: Get all dictionary items for a specified dictionary
Method: GET
URL: /api/v1/dictionaries/{dictionaryId}/items?locale=zh_CN

Response Example:

json

{
  "code": 200,
  "data": {
    "items": [
      {
        "item_id": "UUID",
        "value_type": "STRING",
        "value": "ACTIVE",  // Returns the value of the corresponding type based on value_type
        "label": "激活",    // Returns the label corresponding to the requested locale
        "description": "User is in active state",
        "sort_order": 1,
        "status": "ENABLED"
      }
    ]
  }
}

5. Update Dictionary Item (not implemented in this phase)

Description: Update dictionary item information
Method: PATCH
URL: /api/v1/dictionaries/{dictionaryId}/items/

Request Example:

json

{
  "value_string": "ACTIVE_USER",
  "sort_order": 2,
  "i18n": [
    {
      "locale": "zh_CN",
      "label": "已激活"
    }
  ]
}

Response Example:

json

{
  "code": 200,
  "message": "Dictionary item updated successfully"
}

6. Delete Dictionary Item (not implemented in this phase)

Description: Delete a specified dictionary item
Method: DELETE
URL: /api/v1/dictionaries/{dictionaryId}/items/

Response Example:

json

{
  "code": 200,
  "message": "Dictionary item deleted successfully"
}

7. Batch Get Dictionaries (not implemented in this phase)

Description: Batch get dictionary data based on dictionary codes
Method: GET
URL: /api/v1/dictionaries/batch?codes=source_language,target_language&locale=zh_CN

Response Example:

json

{
  "code": 200,
  "data": {
    "dictionaries": {
      "source_language": [
        {
          "value": "zh_CN",
          "label": "Chinese (Simplified)",
          "sort_order": 1
        },
        {
          "value": "en_US",
          "label": "English (US)",
          "sort_order": 2
        }
      ],
      "target_language": [
        {
          "value": "zh_CN",
          "label": "Chinese (Simplified)",
          "sort_order": 1
        },
        {
          "value": "en_US",
          "label": "English (US)",
          "sort_order": 2
        }
      ]
    }
  }
}

8. Get All System Built-in Dictionaries Under a Workspace (only this interface is implemented in this phase)

Description: Get all system built-in dictionaries and their dictionary items under the current workspace
Method: GET
URL: /api/v1/workspaces/{workspaceId}/system-dictionaries?locale=zh_CN

Response Example:

json

{
  "code": 200,
  "data": {
    "dictionaries": [
      {
        "dictionary_id": "UUID1",
        "code": "source_language",
        "name": "Source Language",
        "description": "List of source languages supported by the system",
        "items": [
          {
            "value": "zh_CN",
            "label": "Chinese (Simplified)",
            "description": "Simplified Chinese used in mainland China",
            "sort_order": 1
          },
          {
            "value": "en_US",
            "label": "English (US)",
            "description": "American English",
            "sort_order": 2
          }
        ]
      },
      {
        "dictionary_id": "UUID2",
        "code": "target_language",
        "name": "Target Language",
        "description": "List of target languages supported by the system",
        "items": [
          {
            "value": "zh_CN",
            "label": "Chinese (Simplified)",
            "description": "Simplified Chinese used in mainland China",
            "sort_order": 1
          },
          {
            "value": "en_US",
            "label": "English (US)",
            "description": "American English",
            "sort_order": 2
          }
        ]
      },
      {
        "dictionary_id": "UUID3",
        "code": "user_status",
        "name": "User Status",
        "description": "Enumeration of user statuses in the system",
        "items": [
          {
            "value": "ACTIVE",
            "label": "Active",
            "description": "User is in active state",
            "sort_order": 1
          },
          {
            "value": "INACTIVE",
            "label": "Inactive",
            "description": "User is in inactive state",
            "sort_order": 2
          }
        ]
      }
    ]
  }
}

SQL Query Example

The following is an example SQL query for implementing the dictionary service API interface (mainly to demonstrate the logic, not the final implementation definition):

Query for Getting All System Built-in Dictionaries Under a Workspace

sql

-- Get all system built-in dictionaries and their dictionary items under a workspace
-- Corresponding API: GET /api/v1/workspaces/{workspaceId}/system-dictionaries?locale=zh_CN

-- Step 1: Get all system built-in dictionaries
WITH system_dictionaries AS (
    SELECT 
        d.dictionary_id,
        d.code,
        d.name,
        d.description,
        d.is_system
    FROM 
        dictionaries d
    WHERE 
        d.is_system = true
        AND (d.workspace_id = :workspace_id OR d.workspace_id IS NULL)
        AND d.status = 'ENABLED'
),
-- Step 2: Get all dictionary items for each dictionary
dictionary_items_with_i18n AS (
    SELECT 
        di.dictionary_id,
        di.item_id,
        CASE di.value_type
            WHEN 'STRING' THEN di.value_string
            WHEN 'NUMBER' THEN CAST(di.value_number AS VARCHAR)
            WHEN 'BOOLEAN' THEN CAST(di.value_boolean AS VARCHAR)
            WHEN 'JSON' THEN CAST(di.value_json AS VARCHAR)
        END AS value,
        i18n.label,
        i18n.description,
        di.sort_order
    FROM 
        dictionary_items di
    LEFT JOIN 
        dictionary_item_i18n i18n ON di.item_id = i18n.item_id AND i18n.locale = :locale
    WHERE 
        di.status = 'ENABLED'
)
-- Final query: Combine dictionaries and dictionary items
SELECT 
    sd.dictionary_id,
    sd.code,
    sd.name,
    sd.description,
    sd.is_system,
    json_agg(
        json_build_object(
            'value', di.value,
            'label', di.label,
            'description', di.description,
            'sort_order', di.sort_order
        ) ORDER BY di.sort_order ASC
    ) AS items
FROM 
    system_dictionaries sd
LEFT JOIN 
    dictionary_items_with_i18n di ON sd.dictionary_id = di.dictionary_id
GROUP BY 
    sd.dictionary_id, sd.code, sd.name, sd.description, sd.is_system
ORDER BY 
    sd.code;

Core

Business

Data Sync

Business

CAT

Workspace Management ​

Overview ​

Related External Systems ​

Related Middleware ​

User-Related ​

Workspace Management ​

Permission Inheritance Strategy ​

API Interface Design ​

Database Design ​

Workspace Table (workspaces) ​

Workspace Members Table (workspace_members) ​

Permission Management ​

API Interface Design ​

Database Design ​

Roles Table (workspace_roles) ​

User Role Mapping Table (workspace_user_roles) ​

Role Permission Mapping Table (workspace_role_permissions) ​

Permission Deny Rules Table (workspace_deny_rules) ​

Permission Check Implementation Description ​

Permission Deny Rules API Interface Design ​

1. Create Deny Rule ​

2. Delete Deny Rule ​

3. Query User's Deny Rules ​

Project Management ​

Project Management API Interface Design ​

1. Create Project ​

2. Query Project List ​

3. Get Project Details ​

4. Update Project ​

5. Delete Project ​

Repository Management API Interface Design ​

1. Create Document ​

Permission Adjustment Interface ​

Adjust Project Member Permissions Interface ​

Adjust Repository Member Permissions Interface ​

Database Design ​

Workspace Projects Table (workspace_projects) ​

Project Repositories Table (workspace_repositories) ​

Extended Project Metadata Table: Used to store additional metadata for projects (key-value structure) ​

Extended Repository Metadata Table: Used to store additional metadata for repositories (key-value structure) ​

Project Member Permissions Table (workspace_project_members) ​

Repository Member Permissions Table (workspace_repository_members) ​

Notification System ​

API Interface Design ​

1. Get User Notification List ​

2. Update Notification Status ​

3. Manage Notification Subscriptions ​

4. Cancel Notification Subscription ​

Database Design ​

Notifications Table (notifications) ​

Notification Categories Table (notification_categories) ​

Notification Subscriptions Table (notification_subscriptions) ​

Notification Logs Table (notification_logs) ​

Message Queue Interface ​

Notification System Flow Chart ​

Dictionary Service ​

Database Design ​

Dictionaries Table (dictionaries) ​

Dictionary Items Table (dictionary_items) ​

Dictionary Item Internationalization Table (dictionary_item_i18n) ​

API Interface Design ​

1. Create Dictionary (not implemented in this phase) ​

2. Add Dictionary Item (not implemented in this phase) ​

3. Get Dictionary List (not implemented in this phase) ​

4. Get Dictionary Items List (not implemented in this phase) ​

5. Update Dictionary Item (not implemented in this phase) ​

6. Delete Dictionary Item (not implemented in this phase) ​

7. Batch Get Dictionaries (not implemented in this phase) ​

8. Get All System Built-in Dictionaries Under a Workspace (only this interface is implemented in this phase) ​

SQL Query Example ​

Query for Getting All System Built-in Dictionaries Under a Workspace ​

Workspace Management

Overview

Related External Systems

Related Middleware

User-Related

Workspace Management

Permission Inheritance Strategy

API Interface Design

Database Design

Workspace Table (workspaces)

Workspace Members Table (workspace_members)

Permission Management

API Interface Design

Database Design

Roles Table (workspace_roles)

User Role Mapping Table (workspace_user_roles)

Role Permission Mapping Table (workspace_role_permissions)

Permission Deny Rules Table (workspace_deny_rules)

Permission Check Implementation Description

Permission Deny Rules API Interface Design

1. Create Deny Rule

2. Delete Deny Rule

3. Query User's Deny Rules

Project Management

Project Management API Interface Design

1. Create Project

2. Query Project List

3. Get Project Details

4. Update Project

5. Delete Project

Repository Management API Interface Design

1. Create Document

Permission Adjustment Interface

Adjust Project Member Permissions Interface

Adjust Repository Member Permissions Interface

Database Design

Workspace Projects Table (workspace_projects)

Project Repositories Table (workspace_repositories)

Extended Project Metadata Table: Used to store additional metadata for projects (key-value structure)

Extended Repository Metadata Table: Used to store additional metadata for repositories (key-value structure)

Project Member Permissions Table (workspace_project_members)

Repository Member Permissions Table (workspace_repository_members)

Notification System

API Interface Design

1. Get User Notification List

2. Update Notification Status

3. Manage Notification Subscriptions

4. Cancel Notification Subscription

Database Design

Notifications Table (notifications)

Notification Categories Table (notification_categories)

Notification Subscriptions Table (notification_subscriptions)

Notification Logs Table (notification_logs)

Message Queue Interface

Notification System Flow Chart

Dictionary Service

Database Design

Dictionaries Table (dictionaries)

Dictionary Items Table (dictionary_items)

Dictionary Item Internationalization Table (dictionary_item_i18n)

API Interface Design

1. Create Dictionary (not implemented in this phase)

2. Add Dictionary Item (not implemented in this phase)

3. Get Dictionary List (not implemented in this phase)

4. Get Dictionary Items List (not implemented in this phase)

5. Update Dictionary Item (not implemented in this phase)

6. Delete Dictionary Item (not implemented in this phase)

7. Batch Get Dictionaries (not implemented in this phase)

8. Get All System Built-in Dictionaries Under a Workspace (only this interface is implemented in this phase)

SQL Query Example

Query for Getting All System Built-in Dictionaries Under a Workspace