Implementation worksheet · 5 min read
A Client Portal Data Model for Projects and Approvals
Model five entities: Client (the organization), User (people, each belonging to one client or to your team), Project (belongs to one client), Deliverable (belongs to one project, carries a status), and Approval (belongs to one deliverable, records who, when, and which version). Files attach to deliverables, not projects. Every query filters by the requesting user's client id — that single discipline is what keeps client A from ever seeing client B.
Portal bugs are almost never UI bugs; they're data-model bugs wearing UI clothes. A file attached to the wrong level, an approval that doesn't record the version it approved, a user tied to a project instead of a client — each becomes a support ticket or a breach.
Put it into practice
1. Separate Client from User
The organization and its people are different entities. Contacts change jobs; the client persists. Access rules hang off the Client; identity hangs off the User.
2. Give Deliverable the status, not Project
Projects contain many deliverables in different states. A project-level status is always a lie about something; per-deliverable status makes 'where are we?' answerable precisely.
3. Make Approval a record, not a boolean
approved_by, approved_at, deliverable_version. When the client says 'we never approved that', this row is the conversation.
4. Attach files to deliverables with versions
Project-level file piles recreate the shared-drive mess inside your portal. Deliverable-scoped, versioned files are the entire feature.
5. Scope every query by client id
Not by hiding links — by filtering data. If a client user's queries are structurally filtered to their client_id, there is no URL to guess.
Entity checklist
Copy this structure into your review document and record your observed result for each row.
| Entity | Key fields | Belongs to |
|---|---|---|
| Client | name, status | — |
| User | email, role, client_id (null = your team) | Client |
| Project | name, client_id, stage | Client |
| Deliverable | project_id, status, current_version | Project |
| Approval | deliverable_id, version, approved_by, approved_at | Deliverable |
A failure worth checking
The project-scoped user: linking users to projects instead of clients. It works until a client adds a second project, and then their team can't see it — or worse, a contractor shared across two clients leaks between them. Users belong to clients; project visibility derives from that.
Common questions
Should internal comments live in the same model?
Yes, with an is_internal flag the client-side queries always exclude — one model, two views. A separate system drifts; a forgotten flag is caught by the same client_id scoping test.
How does this translate to an AI app builder?
Describe the entities and rules in this order — the description IS the model. Greta generates the tables and the role-scoped queries from it; the two-account permission test in the field guides verifies the scoping held.
Basis and scope
This is a proposed implementation method using illustrative examples, not a measured benchmark or a customer case study. Prepared with AI assistance. Validate product-specific behavior against current documentation and your own test environment.