at first i misunderstood your OP and thought you meant the note was to be shared between the person making the note and the post creator, or to just mark the spot. i see you just basically want a sticky note feature for users to make a personal note on a post, like an extended text bookmark without the time feature. as @Lhc_fl says, this is a good idea and i can see being useful in some instances.
I haven’t played with staff notes yet, but I should look at the code for staff notes to see if extending it for sticky notes to oneself is realistic. Each time I dig into the code I learn a little more and things seem a little less opaque.
The way user notes appear to be implemented, it looks like it would be difficult to modify it to work for personal sticky notes. (The text of the note goes in a json array in the plugin_store_notes table, so it isn’t clear if it can have any formatting. In-house ads are also stored in that table, but they use HTML formatting.)
Also, it doesn’t appear that there’s a way to edit a user note. I suppose it could be deleted and re-posted, though that would change the timestamp.
For me, I would suggest creating a table with three keys:
id, post_id, user_id (corresponding to a user can only record on one post),
and then depending on your idea, you can directly record data on this table, or publish the user’s notes as a special post (so that if you do not enable the plugin , the original notes can still be retained)
My first thought is that the sticky note should refer to a specific post, similar to a reply, not just the topic. In a lengthy discussion, I could see a user wishing to have sticky notes attached to several posts in a topic.
Using a sticky_post table has its merits, but there are likely multiple places where it would be necessary to exclude a sticky_post from display or processing beyond just the displaying of a topic, such as when sending emails, and it would be necessary to search the sticky_post table for each post to see if it was a sticky note from this user and thus handled differently than a sticky note made by a different user.
A separate post_type would have merit but that might need to be a part of discourse core, and there may be other issues with that approach of which I am not currently aware.
There are also post custom fields, that might be a better approach than topic custom fields, though it likely has many of the same issues and limitations as a sticky_notes tables, as would putting that information in the plugin_store_notes table.
A combination approach, in which there is a topic custom fields entry that indicates that there are sticky notes in this topic, might have potential. If there are none, then the post custom fields table need not be searched for a sticky note indicator.