organization_role

Column	Type	PK	Required	Default
organization_role_id	TEXT	Yes	Yes
person_id	TEXT	No	Yes
organization_id	TEXT	No	Yes
organization_role_type_id	ULID	No	Yes
elaboration	TEXT	No	No
created_at	TIMESTAMPTZ	No	No	CURRENT_TIMESTAMP
created_by	TEXT	No	No	'UNKNOWN'
updated_at	TIMESTAMPTZ	No	No
updated_by	TEXT	No	No
deleted_at	TIMESTAMPTZ	No	No
deleted_by	TEXT	No	No
activity_log	TEXT	No	No

Foreign Keys

Column Name	Foreign Key
organization_role_type_id	organization_role_type_id references organization_role_type.organization_role_type_id
organization_id	organization_id references party.party_id
person_id	person_id references party.party_id

Indexes

Column Name	Index Name
person_id	organization_role_person_id_organization_id_organization_role_type_id_unique
organization_id	organization_role_person_id_organization_id_organization_role_type_id_unique
organization_role_type_id	organization_role_person_id_organization_id_organization_role_type_id_unique
person_id	idx_organization_role__person_id__organization_id__organization_role_type_id
organization_id	idx_organization_role__person_id__organization_id__organization_role_type_id
organization_role_type_id	idx_organization_role__person_id__organization_id__organization_role_type_id
organization_role_id	sqlite_autoindex_organization_role_1

SQL DDL

CREATE TABLE `organization_role` (
	`organization_role_id` text PRIMARY KEY NOT NULL,
	`person_id` text NOT NULL,
	`organization_id` text NOT NULL,
	`organization_role_type_id` ULID NOT NULL,
	`elaboration` text,
	`created_at` TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
	`created_by` text DEFAULT 'UNKNOWN',
	`updated_at` TIMESTAMPTZ,
	`updated_by` text,
	`deleted_at` TIMESTAMPTZ,
	`deleted_by` text,
	`activity_log` text,
	FOREIGN KEY (`person_id`) REFERENCES `party`(`party_id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`organization_id`) REFERENCES `party`(`party_id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`organization_role_type_id`) REFERENCES `organization_role_type`(`organization_role_type_id`) ON UPDATE no action ON DELETE no action,
	CONSTRAINT "elaboration_check_valid_json" CHECK(json_valid("organization_role"."elaboration") OR "organization_role"."elaboration" IS NULL)
)