This one will be quick.
Imagine this, you get a report from your bug tracker:
Sophie got an error when viewing the diff after her most recent push to her contribution to the @unison/cloud project on Unison Share
(BTW, contributions are like pull requests, but for Unison code)
Okay, this is great, we have something to start with, let's go look up that contribution and see if any of the data there is suspicious.
Uhhh, okay, I know the error is related to one of Sophie's contributions, but how do I actually find it?
I know Sophie's username from the bug report, that helps, but I don't know which project she was working on, or what the contribution ID is, which branches are involved, etc. Okay no problem, our data is relational, so I can dive in and figure it out with a query:
> SELECT * contribution. FROM contributions AS contribution contributionscontribution JOIN projects AS project projectsproject ON contribution.project_id = project. id contribution.project_idproject. JOIN users AS unison_user usersunison_user ON project.owner = unison_user. id project.ownerunison_user. JOIN users AS contribution_author userscontribution_author ON contribution.author_id = contribution_author. id contribution.author_idcontribution_author. JOIN branches AS source_branch branchessource_branch ON contribution.source_branch = source_branch. id contribution.source_branchsource_branch. WHERE contribution_author.username = 'sophie' contribution_author.username AND project.name = 'cloud' project.name AND unison_user.username = 'unison' unison_user.username ORDER BY source_branch.updated_at DESC source_branch.updated_at - [ RECORD 1 ] --------+---------------------------------------------------- id | C - 4567 | C - 9999 project_id | P 21 contribution_number | title | Fix bug the app from deleting the User 's hard drive description | PreventappdeletingUser status | open source_branch | B-1111 target_branch | B-2222 created_at | 2025-05-28 13:06:09.532103+00 updated_at | 2025-05-28 13:54:23.954913+00 author_id | U-1234
It's not the worst query I've ever had to write out, but if you're doing this a couple times a day on a couple different tables, writing out the joins gets pretty old real fast. Especially so if you're writing it in a CLI interface where's it's a royal pain to edit the middle of a query.
Even after we get the data we get a very ID heavy view of what's going on, what's the actual project name? What are the branch names? Etc.
We can solve both of these problems by writing a bunch of joins ONCE by creating a debugging view over the table we're interested in. Something like this:
CREATE VIEW debug_contributions AS debug_contributions SELECT id AS contribution_id, contribution.contribution_id, contribution.project_id, contribution.contribution_number, contribution.title, contribution.description, contribution.status, as source_branch_id, contribution.source_branchsource_branch_id, AS source_branch_name, source_branch.namesource_branch_name, AS source_branch_updated_at, source_branch.updated_atsource_branch_updated_at, as target_branch_id, contribution.target_branchtarget_branch_id, AS target_branch_name, target_branch.nametarget_branch_name, AS target_branch_updated_at, target_branch.updated_attarget_branch_updated_at, contribution.created_at, contribution.updated_at, contribution.author_id, AS author_username, author.usernameauthor_username, AS author_name, author.display_nameauthor_name, AS project_name, project.nameproject_name, '@' || project_owner.username || '/' || project.name AS project_shorthand, project_owner.usernameproject.nameproject_shorthand, AS project_owner_id, project.ownerproject_owner_id, AS project_owner_username project_owner.usernameproject_owner_username FROM contributions AS contribution contributionscontribution JOIN projects AS project ON contribution.project_id = project. id projectsprojectcontribution.project_idproject. JOIN users AS author ON contribution.author_id = author. id usersauthorcontribution.author_idauthor. JOIN users AS project_owner ON project.owner = project_owner. id usersproject_ownerproject.ownerproject_owner. JOIN branches AS source_branch ON contribution.source_branch = source_branch. id branchessource_branchcontribution.source_branchsource_branch. JOIN branches AS target_branch ON contribution.target_branch = target_branch. id ; branchestarget_branchcontribution.target_branchtarget_branch.
Okay, that's a lot to write out at once, but we never need to write that again. Now if we need to answer the same question we did above we do:
SELECT * from debug_contributions debug_contributions WHERE author.username = 'sophie' author.username AND project_shorthand = '@unison/cloud' project_shorthand ORDER BY source_branch_updated_at DESC ; source_branch_updated_at
Which is considerably easier on both my brain and my fingers. I also get all the information I could possibly want in the result!
You can craft one of these debug tables for whatever your needs are for each and every table you work with, and since it's just a view, it's trivial to update or delete, and doesn't take any space in the DB itself.
Obviously querying over project_shorthand = '@unison/cloud' isn't going to be able to use an index, so isn't going to be the most performant query; but these are one off queries, so it's not a concern (to me at least). If you care about that sort of thing you can leave out the computed columns so you won't have to worry about that.
Anyways, that's it, that's the whole trick. Go make some debugging views and save your future self some time.
Hopefully you learned something 🤞! If you did, please consider checking out my book: It teaches the principles of using optics in Haskell and other functional programming languages and takes you all the way from an beginner to wizard in all types of optics! You can get it here. Every sale helps me justify more time writing blog posts like this one and helps me to continue writing educational functional programming content. Cheers!