How to approach upgrading a user and giving them more access/data?
I have two main user types: viewers (who can only view) and writers (who can view and post). Both share common fields, but writers have additional specialized fields. I also support anonymous users.
The flow works like this:
• A user starts as anonymous.
• They can either stay anonymous or upgrade to viewer (with limited posting rights).
• A viewer can remain as-is or upgrade further to writer (with many more fields and permissions).
Right now, my schema has:
• a base user profile table (with shared fields and anonymous-related info),
• a separate viewer table,
• and a separate writer table.
The problem: upgrading from viewer → writer causes headaches with ID consistency, data integrity, and data being spread across three tables.
My idea: simplify it to just two tables
• a base profile table, and
• a viewer/writer table with a userType field.
In this second table, all writer-specific fields would be nullable and only allowed if userType = writer (going to enforce this with RLS)
What do you think of this approach?
3 Replies
My initial thoughts are could this be simplified to one table
profiles where there is a role column for anon , viewer and writer and maybe have a bjson column to store either the writer/viewer specific fields or any field that might not be queried as much.Your second approach is basically the one that Wordpress uses. This can become hard to maintain if you should add more columns further down the line. The approach ihm40 suggested is the route I'd take as it's easier to maintain, although I'd opt for a separate table instead of the
jsonb column in my case.In this second table, all writer-specific fields would be nullable and only allowed if userType = writer (going to enforce this with RLS)I assume you intend to consolidate the viewer- and writer-fields into a single table. Since RLS operate on a per row level (i.e., the whole row, or none; one cannot have it in parts), how are you going to prevent the viewer from modifying the writer-specific columns?