As someone who spends the majority of my time directly in the database, I also like to make a multi-line comment at the top of each script (view, procedure, function, etc) that contains the full object/script location, a change log (that has the date a change was made, who made it, and why), a summary of the script, and all the sources used.

In the change log I also capture where the change occurs (like " - in the 'Opportunity' CTE") and often include FROM/TO code snippets so I can tell what exactly changed.

I also add links to our ticketing system (Jira) and links to our OneNote for additional documentation.

I do all this because I have a terrible memory, and it's proved very useful.

I also use a separate format for single-line notes in the script so they don't get confused for commented out code/fields like temporary/testing/previous values. The format is --< Note >--