Following the shape of the data
Profiles are configurable professional microsites that individuals and organizations use to present and share their professional identity.
A Profile can be owned by an Account or a Workspace, while authorized Workspace members can manage Workspace-owned Profiles.
Before modeling the database, I had already defined Accounts, Profiles, Workspaces, Access Points and different kinds of Profile content, along with the relationships between them. The next step was deciding how to represent that model in data.
The model combined explicit relationships with content that could stay more self-contained. It also raised a separate question about which product rules the database should enforce.
When relationships carry meaning
A Profile has one owner, either an Account or a Workspace, while authority to manage a Workspace-owned Profile depends on Workspace membership and the capabilities granted through it. The subject represented by the Profile is separate from both.
Accounts may also own Workspaces or participate in them through membership. Access Points exist independently until they are connected to a Profile and can take the form of direct links, QR codes or NFC tags.
If an Account leaves a Workspace, its organizational access should change. If an Access Point is reassigned, the Access Point stays the same while the Profile it points to changes.
I could have represented the same concepts in a document database using separate collections and references. The problem was not storing the references themselves. The system would regularly need to determine who owns a Profile, who can manage it through a Workspace and what an Access Point currently resolves to. Making the relationships explicit gave the database more room to protect them instead of leaving that responsibility entirely to application code.
The relationship between an Account and a Workspace has information and behaviour of its own. It can be created, changed and removed, and it affects what that Account can do inside the Workspace.
I modeled it as its own relation:
workspace_memberships(account_id, workspace_id, ...)
Profile ownership had to remain exclusive. I represented that with two nullable foreign keys and an XOR constraint, so exactly one of owner_account_id or owner_workspace_id had to be set.
I had briefly considered allowing multiple Account owners, but the product did not require it. Billing also introduced questions about responsibility and payment that only existed because of that extra flexibility.
Modeling ownership like membership just because both connect two concepts would have made the schema more symmetrical without making the model more accurate.
What belongs to the database
Using relationships in the schema did not mean every product rule belonged there. I had to decide which rules the database should enforce.
I did not want every business rule expressed as a database constraint. For example, whether a Workspace member is allowed to perform a particular action depends on the authority that member has and the rules around that action. I kept action authorization in application logic.
A Profile currently allows one section of each type. That is a data invariant, but I chose to enforce it in application logic rather than with a database constraint. The composition model may change, and for now I preferred to keep that rule outside the schema. The trade-off is that the database does not independently prevent duplicate section types.
Other rules define states the data should never be in. A foreign key should not point to something that does not exist. A relationship that must be unique should not be duplicated. A public identifier that needs to be unique should remain unique regardless of which application path performs the write.
Choosing the shape of the data
Different parts of the product did not map to data in the same way.
The Settings screen did not map to a single Settings model. Email and password stayed with Authentication. Account preferences had a small relational model of their own. Policy acceptance needed historical records, while storage usage belonged to Profile Media and the applicable allowance came from Billing.
Profile content presented a different kind of modeling problem. An About section has a title, text, an image and a few presentation options. A Gallery contains groups of media, while an Accordion is a list of questions and answers.
Separate columns and tables were possible, but I didn't really need that level of structure. Most of these fields were only used as part of their section, so I kept the content in JSONB:
profile_sections(profile_id, type, position, content JSONB)
The section itself was still part of the relational model. It belonged to a Profile and had a type and position. But I didn't need to break down everything inside it into more tables.
For the section content I had, text and presentation options did not need their own identity or relationships elsewhere in the system.
This was part of why I chose PostgreSQL. I could use regular relational tables where they made sense and JSONB for the parts that didn't need that level of structure.
What does the database lose?
What do I lose if the database doesn't understand the internal structure?
Saving a Profile creates a relationship between two separate records. Storing saved Profile IDs directly on an Account would hide that relationship inside one record. I kept it relational so the database could enforce valid references, prevent duplicate saves and keep the relationship easy to query.
Entitlements can come from subscriptions, physical products or manual grants. I kept payment, subscription state and access separate rather than collapsing them into one record. Entitlements had their own lifecycle and remained traceable to the source that granted them.
Referential integrity, useful constraints, independent lifecycles and important query patterns were reasons to keep data relational.
If I wasn't losing much and the fields naturally belonged together, JSONB was worth considering. Queries, indexing and schema changes also mattered, especially if individual fields might later be useful on their own.
When more structure stopped helping
Gallery, Video and Showcase content could be grouped under user-defined titles. During product design, I had considered whether those groups should become a Collection concept. I also considered a more structured category or taxonomy system, but Profiles had to work across different industries and I did not have a clear use case for that level of structure. The product only needed a way to group presented work under user-defined titles.
The groups existed only inside the Profile presentation. They had no identity or lifecycle of their own, and nothing else in the product needed to reference them. A separate Collection or Group entity would have given them independence the product itself did not require, so the grouping stayed with the presentation data.
A Showcase had its own identity outside the section where it appeared and could exist regardless of how it was selected, grouped or ordered in the presentation. The Showcase remained relational, while its selection and presentation stayed with the section.
The application validates Showcase references in the section content to make sure each Showcase exists and belongs to the same Profile.
The database choice
Working through ownership, membership, content, grouping and access made the database requirements concrete. I needed relational guarantees for concepts with their own identity and lifecycle, while some presentation content worked better without being broken into separate tables.
PostgreSQL fit the model. It let me keep structure where the relationships and invariants mattered, without forcing the same level of structure onto everything else.