User Project Table

Overview

Table for managing user accessible projects

Table Definition

Logical Name	Physical Name	Column Name	Data Type	Primary Key	Relation	Unique	Nullable	Default Value	Remarks
User Accessible Project	user_project	id	serial	◯
		user_id	string						Logical reference to user.cognito_sub (no FK constraint)
		project_id	number		project:id
		project_access	enum					'1'	access_level ('0', '1', '2')
		created_at	datetime
		updated_at	datetime
		deleted_at	datetime				◯
		created_by	string		user:cognito_sub
		updated_by	string		user:cognito_sub
		deleted_by	string		user:cognito_sub		◯

Relations

• user_id → user.cognito_sub (logical reference, no FK constraint)
• project_id → project.id

Indexes

• PRIMARY KEY (id)
• UNIQUE INDEX user_project_user_id_project_id_active_idx (user_id, project_id) WHERE deleted_at IS NULL
• INDEX user_project_project_id_user_id_idx (project_id, user_id)

Permission Levels

• 0: No access
• 1: Read
• 2: Read/Write

Notes

• user_id is varchar (Cognito sub), not user.id
• Default project_access is '1'
• Only one active assignment is allowed for each user and project; soft-deleted assignment history is retained