Who Can See This Data? Designing Tenant-Safe APIs
A system-design walkthrough of organization roles, database queries, pagination, and the information an API can reveal without returning a single private record.
Imagine a team adding an invoice dashboard to a SaaS application. Alice works for Acme. She signs in, opens the dashboard, and sees Acme’s invoices.
Then someone asks what happens if Alice changes organization_id to Beta, or opens a link with a Beta invoice ID.
Both requests reach the same API with Alice’s valid credentials. The server has to decide which records she can read.
I’ll work through the read side of an invoice dashboard, from the API contract and data model to the queries and tests. The same design applies to other records owned by an organization.
First, decide what we are protecting
Our application serves multiple organizations. Each organization is a tenant: a boundary around one customer’s data.
One user can belong to several organizations. Alice might be an administrator at Acme and a viewer at another company. Each membership records her role in that organization.
In this example, a viewer can read invoices. An administrator can also manage them.
We need two endpoints:
GET /invoices?organization_id=...&status=...&page=1&limit=10
GET /invoices/{invoice_id}
The list can span every organization the caller can read from. Supplying organization_id narrows that set to one organization.
The detail endpoint looks for the requested ID within the caller’s allowed organizations. If there’s no match, it returns 404.
The count needs the same protection as the rows. If total_count includes hidden invoices, a response with zero private invoice objects can still reveal another company’s activity.
The guarantee I want is:
For otherwise unchanged authorized data and request parameters, adding invoices to an inaccessible organization must not change the caller’s returned records or count.
This guarantee covers the records and metadata in the response.
We’re designing reads here. Issuing invoices, calculating tax, and collecting payments each need their own permissions and business rules.
Identity, scopes, and roles answer different questions
Authentication identifies the caller. A validated session might establish that this request belongs to Alice.
An API scope describes an operation a credential can perform. invoices:read lets the request through the invoice-read gate. The tenant check still has to decide which invoices are visible.
Role-based access control, or RBAC, maps a membership’s role to permissions. Alice’s Acme role might grant invoice_read. That permission applies within Acme.
Resource authorization applies the rule to a specific record. Does this invoice belong to an organization where Alice’s membership grants invoice reads?
For a user request in this design:
Readable invoice
= valid authenticated caller
+ credential permits invoice reads
+ membership grants invoice_read in the invoice's organization
+ organization is allowed by this credential's organization restriction
Alice might have invoice-read permission at both Acme and Beta. If she signs in with a token restricted to Acme, she can read only Acme’s invoices.
An Acme administrator’s role applies within Acme. Platform-wide access needs its own explicit policy.
Model ownership before writing endpoints
I would start with four tables and a small role-permission mapping in application code.
In the diagram, PK means primary key and FK means foreign key. In memberships, user_id points to users.id and organization_id points to organizations.id. Together they form one composite primary key, so the pair (user_id, organization_id) must be unique. Alice can have a row for Acme and another for Beta, with a different role in each. If we need membership history later, I’d store it separately and keep one current row per pair.
invoices.organization_id is required. Its foreign key points to an existing organization, so every invoice has an owner. The access check then uses the membership table to decide who can read that owner’s invoices.
For this model, every invoice has a number. At the database level, make number NOT NULL and enforce uniqueness within its organization:
UNIQUE (organization_id, number)
Acme and Beta can each issue INV-001. Acme can issue that number only once under this rule. A product that numbers invoices by legal entity or series would use a different uniqueness constraint.
amount_minor stores an integer in the currency’s smallest unit. For a USD invoice, 1250 means $12.50. The currency column tells us how to interpret the amount.
An initial read index is:
CREATE INDEX invoices_org_created_id_idx
ON invoices (organization_id, created_at DESC, id DESC);
The index supports newest-first reads within one organization. A list spanning several organizations may still need a sort, so I’d check the query plan against the actual workload.
For a small, fixed set of roles, an application mapping is enough:
ROLE_PERMISSIONS = {
"viewer": {"invoice_read"},
"administrator": {"invoice_read", "invoice_manage"},
}
If customers need to define their own roles, I’d add tables for roles, permissions, and the links between them. For two fixed roles, a mapping in code is enough.
The high-level design: one API, explicit responsibilities
One API and one PostgreSQL database are enough to start. The diagram shows how the endpoint, service, authorization resolver, and repositories work together.
Cookie or token; IDs and filters"] --> A["Authentication middleware
Validate credential; create AuthSubject"] A --> G["Endpoint dependency
Check caller type and invoice-read scope"] G --> E["Invoice endpoint
Validate query/path inputs"] E --> S["Invoice service
Choose required permission"] S --> P["Authorization resolver
Membership + role + credential restrictions"] P --> AR["Authorization repository"] AR --> DB[("PostgreSQL
Organizations, memberships, invoices")] P --> S S --> R["Invoice repository
Build authorized query; count and paginate"] R --> DB R --> S S --> E E --> O["Response serializer
Return public fields only"]
The application components run inside the same API process. Multiple API replicas can run that code against the same database.
The middleware validates the credential. The endpoint checks its scope and validates the inputs. The service decides which domain permission the operation needs, and the repositories run the queries.
The authorization resolver works out which organizations the caller can read from. It takes the full authenticated subject, meaning the validated user ID and credential context, because the token’s organization restriction matters too.
The API takes the caller’s identity from the validated credential and their role from server-controlled membership records.
The endpoint checks that IDs are valid UUIDs and status values are supported. For pagination, I’d default to page 1 and ten items, require a positive page number, and accept a limit from 1 to 100. A well-formed ID still needs an access check.
Low-level design: start with the authorized query
List and detail reads share a query builder that starts with the caller’s allowed organizations. Each endpoint adds its own filters to that starting point.
| Component | Input | Output |
|---|---|---|
| Read dependency | Validated AuthSubject | Accepted subject or authentication/scope error |
| Authorization resolver | Subject and invoice_read permission | Accessible organization IDs |
| Repository base query | Accessible organization IDs | Query restricted to those organizations |
| List service | Subject, filters, pagination | Authorized page and matching count |
| Detail service | Subject, invoice ID | Authorized invoice or no match |
| Endpoint | Service result | Public response or appropriate HTTP error |
For Alice, the resolver considers active memberships, role permissions, organization availability, and any credential organization restriction.
The two service methods look like this in pseudocode.
async def list_invoices(session, subject, filters, pagination):
org_ids = await authorization.get_accessible_org_ids(
session, subject, permission="invoice_read"
)
repository = InvoiceRepository.from_session(session)
matching = repository.statement_by_org_ids(org_ids)
matching = repository.apply_filters(matching, filters)
total_count = await repository.count(matching)
ordered = repository.order_newest_first(matching)
items = await repository.fetch_page(ordered, pagination)
return {"items": items, "pagination": {
"page": pagination.page,
"limit": pagination.limit,
"total_count": total_count,
}}
async def get_invoice(session, subject, invoice_id):
org_ids = await authorization.get_accessible_org_ids(
session, subject, permission="invoice_read"
)
repository = InvoiceRepository.from_session(session)
statement = repository.statement_by_org_ids(org_ids)
statement = repository.filter_id(statement, invoice_id)
return await repository.get_one_or_none(statement)
An empty allowed set must match zero rows. Keep the organization condition even when the list is empty.
That’s worth testing explicitly. A guard like if org_ids: add_filter() would skip the access condition when the caller has zero allowed organizations.
Follow Alice’s list request
Suppose Alice can read Acme’s invoices and cannot read Beta’s. She sends:
GET /invoices?organization_id=beta&page=1&limit=10
Here, acme and beta are readable stand-ins for real UUIDs.
The resulting query has two independent conditions:
SELECT id, organization_id, number, status,
amount_minor, currency, created_at
FROM invoices
WHERE organization_id = ANY(:allowed_org_ids)
AND organization_id = :requested_org_id
ORDER BY created_at DESC, id DESC
LIMIT :limit OFFSET :offset;
Values prefixed with : are bound parameters. The application binds allowed_org_ids as a UUID array. With ANY, an empty array matches zero rows.
The server supplies the allowed set. Alice supplies the requested organization. The AND means every row must satisfy both conditions.
No invoice can satisfy both “belongs to Acme” and “belongs to Beta.” The list is empty.
If the API later accepts several organization IDs in one filter, the same rule applies. A request for Acme and Beta returns only the matching Acme rows.
Follow the detail request separately
The detail route gets its own request and runs the same policy. Here’s what happens when Alice asks for a Beta invoice.
The key query is:
SELECT id, organization_id, number, status,
amount_minor, currency, created_at
FROM invoices
WHERE organization_id = ANY(:allowed_org_ids)
AND id = :invoice_id;
With both conditions in the query, the repository returns either an authorized invoice or zero rows.
Some object-level rules need data from the record itself. In those cases, every caller has to enforce the policy before returning, logging, or otherwise using the record. Ownership is enough to express the rule in SQL for this feature.
A UUID can still appear in a shared link or an export. The detail query applies the ownership check every time.
What the status code tells the caller
Consider two requests:
Existing Beta invoice, inaccessible to Alice → 403
Completely nonexistent invoice → 404
That response difference confirms that the first invoice exists. It is an existence leak.
For this design, a detail query that finds no authorized match returns the same 404 status and error body for an unknown ID and an inaccessible one. HTTP semantics
The API still distinguishes a missing credential, a missing scope, and invalid input.
| Situation | Response in this design |
|---|---|
| No valid authentication | 401 |
| Valid credential lacks required API scope | 403 |
| Malformed identifier or invalid query input | Request-validation error |
| List has no authorized matches | 200 with empty items and zero count |
| Detail has no authorized match | 404 |
| Database unavailable | Server error |
A generic 404 can make troubleshooting harder when a legitimate user expects access. Internal logs can record the request ID and policy outcome while keeping raw credentials and invoice contents out of the logs.
The shared response removes that visible status-code difference.
Pagination is part of the security boundary
The tempting implementation is:
- Fetch invoices.
- Count and paginate them.
- Remove anything Alice cannot read.
Even if the final invoice objects are safe, the response can be wrong.
Imagine Acme has two invoices and Beta has five. If total_count is calculated before authorization, Alice sees seven. She has learned something about data outside her access.
Pagination also breaks. If a page contains ten invoices and nine are inaccessible, Alice receives one. An accessible invoice may remain on a later page even though the current page looks nearly empty.
The order needs to be:
Build the count and page queries from the same authorized, filtered dataset. In the pseudocode, count(matching) counts every visible row that also passes any organization or status filter Alice supplied. The page query uses the same filters, sorts the result, and applies LIMIT and OFFSET. The offset is (page - 1) * limit. If Alice asks for a page past the end, items is empty and total_count still reflects all the matches.
For example:
{
"items": [{"id": "inv_103", "organization_id": "acme", "status": "open"}],
"pagination": {
"page": 1,
"limit": 1,
"total_count": 2
}
}
The item shows a few response fields and uses readable stand-ins for UUIDs.
For 200 authorized matches and a page size of ten, the first response contains ten items and a count of 200.
I also use id as a tie-breaker after created_at. Several invoices can have the same creation timestamp. A non-unique order leaves the database free to rearrange ties between requests.
The tie-breaker makes the order predictable for a fixed dataset. A new invoice inserted between two requests can still shift an offset-based page.
What changes when this gets larger?
The starting design resolves accessible organization IDs, then queries invoices. That is straightforward when a caller belongs to a modest number of organizations.
Resolving IDs separately costs an extra database round trip. For a user with many memberships, the API also has to build a large array and send it back to PostgreSQL. One option is to put the policy directly in the invoice query.
SELECT i.id, i.organization_id, i.number, i.created_at
FROM invoices AS i
WHERE EXISTS (
SELECT 1
FROM memberships AS m
JOIN organizations AS o ON o.id = m.organization_id
WHERE m.user_id = :authenticated_user_id
AND m.organization_id = i.organization_id
AND m.active
AND o.active
AND m.role = ANY(:roles_granting_invoice_read)
)
AND (
:credential_unrestricted
OR i.organization_id = ANY(:credential_org_ids)
)
ORDER BY i.created_at DESC, i.id DESC
LIMIT :limit OFFSET :offset;
credential_unrestricted and credential_org_ids come from the validated credential. An empty restriction allows zero organizations. An unrestricted credential still has to pass the membership check.
Read EXISTS as a yes-or-no question for each invoice. Does its owner have an active membership for this user that grants invoice reads? The final condition applies the credential restriction to the same invoice.
EXISTS checks whether a match is present and keeps the result at one row per invoice. In this schema, a join on both membership key columns also finds at most one row. Revisit that assumption if you later join invoice line items or allow multiple roles per membership. A one-to-many join can inflate the count and consume several slots on a page for the same invoice.
Exact counts can become expensive because the database must count the authorized matching dataset. If the product only needs “is there another page?”, fetching one extra item may be a better contract.
Deep offset pagination is another cost. A cursor based on (created_at, id) tells the query where to resume and can avoid walking a large offset. Each page request still applies the current access policy.
I’d use query plans, response times, and realistic tenant sizes to choose among these options.
Revocation and what a read can see
I’d start by reading permissions and invoice data from the primary database. A read replica can lag behind a membership change, so moving these queries to a replica would require a clear policy for how long revoked access could remain visible.
Caching introduces a question about freshness. If Alice is removed from Acme, how long can the API keep using the old decision?
I’d read permissions from the database on each request to start. If measurements justify caching, I’d define an acceptable revocation delay and invalidate entries when memberships, roles, or organization status change. The cache key also needs the caller, required permission, and credential restriction.
A primary database can still have a race between separate statements. Suppose the service resolves Acme as accessible, an administrator removes Alice, and the invoice query then runs with the previously resolved ID.
The request may complete under the earlier decision. A transaction at the default PostgreSQL READ COMMITTED isolation level does not automatically make those statements share one snapshot.
Embedding the membership predicate in the invoice query evaluates the permission and data against the same snapshot. At READ COMMITTED, a revocation committed before that statement starts takes effect for that read. A revocation committed after it starts can still overlap an in-flight read.
Likewise, count and page queries can observe different data during concurrent writes. For this dashboard, I’d accept small differences during concurrent updates and apply the same policy and filters to both queries. If the product needs one consistent view, run permission resolution, count, and page in one PostgreSQL REPEATABLE READ transaction. That gives all three the same view of committed data and also means a revocation committed after the snapshot starts takes effect on a later request. PostgreSQL transaction isolation
There’s a separate caching boundary at the HTTP layer. A proxy that reuses a response by URL alone could serve Alice’s invoice list, or her 404, to another caller. I’d send Cache-Control: no-store on these endpoints and keep them out of shared response caches. HTTP caching standard
If the membership lookup fails, return a server error. An empty list means the policy completed and found zero authorized matches.
Would database row-level security be better?
PostgreSQL row-level security, or RLS, can enforce record visibility inside the database. It is a credible defense against an application query forgetting its tenant condition.
The API still has to provide the database with the correct request identity or tenant context. With connection pooling, set that context for the transaction so it ends before another request reuses the connection. I’d run application queries with a separate role that neither owns the tables nor has BYPASSRLS, and test with that same role. PostgreSQL table owners normally bypass row policies, and superusers and roles with BYPASSRLS always bypass them. PostgreSQL row security policies
For this module, I would begin with centralized query construction and strong cross-tenant tests. In a higher-assurance system, I would consider RLS as another enforcement layer.
Both approaches need tests through the actual API and database. A shared helper can be missed, and database policies can be misconfigured.
Test the boundary, not just a successful response
Here is the fixture I would use:
Alice: invoice-read access to Acme; no access to Beta
Acme: 2 matching invoices
Beta: 5 matching invoices
The first test requests a page size of one. It should assert:
# Illustrative assertions for an integration test.
assert response.status_code == 200
assert len(body["items"]) == 1
assert body["items"][0]["organization_id"] == acme.id
assert body["pagination"]["total_count"] == 2
Then add 100 Beta invoices newer than the Acme invoices and repeat the exact request. Compare the returned IDs and count with the first response. Newer Beta invoices would take the first page if filtering happened after pagination.
That second step tests the isolation rule directly. A response can contain only Acme invoices and still expose Beta through its total or page boundaries.
For detail access, request an Acme invoice, a Beta invoice, and an unknown ID. Expect success for Acme and the same public not-found shape for Beta and the unknown ID.
Test a user with zero memberships both with and without an organization filter. Both lists should be empty. This catches a query builder that skips the condition for an empty allowed set.
Give Alice read access to both organizations. First confirm an unrestricted credential can read Beta, then repeat with an Acme-only credential. The Beta filter should return an empty page with a zero total, and the Beta detail should return 404.
I’d also test an inactive membership and an inactive organization separately, and give two visible invoices the same timestamp to exercise the ID tie-breaker. A status filter should change both the page and the count, and a page past the end should still report the matching total.
The list and detail tests need to exercise the real endpoint, service, and database query. If the authorization helper is mocked to return Acme, the test cannot establish that the helper correctly combines membership, roles, and credential restrictions.
One access rule for every read
An invoice belongs to an organization, and a membership gives a user a role in that organization. I’d combine that role with the credential’s restrictions to decide which invoices the caller can see. The list, total count, and detail lookup would all use that same access rule.
Then I’d test the rule by adding newer invoices to a tenant the caller can’t read. The caller’s first page and total count should stay the same.