Skip to content

Multi-Tenancy and Row-Level Security

Applies to: All subscriptions

Purpose

Explain how Complifly keeps one tenant's data invisible to another, what the scoping tiers are, and — most importantly for anyone writing a query, script or report against this database — why an isolation mistake produces silence rather than an error.

Audience

Security reviewers and auditors, database administrators, anyone writing reporting or operational SQL against a Complifly database.

Prerequisites

Reference

The model

Isolation is enforced in two places, not one:

  1. In the application. Every request carries a verified identity. The tenant scope is derived from that identity — never from a client-supplied parameter — and is applied to each query.
  2. In the database. SQL Server Row-Level Security policies filter rows by the session's scope. Even a query that forgot to filter returns only rows in scope.

The second layer is what makes the model defensible to an auditor: isolation does not depend on every developer remembering a WHERE clause. It also introduces the property that everything else on this page follows from.

The failure mode is silence

This is the single most important operational fact about the Complifly database.

A query executed without the correct session scope does not raise an error, does not warn, and does not fail. It returns zero rows and reports success. A verification query written the same way agrees with it. Nothing downstream catches it.

The practical consequences:

  • A migration or backfill that forgets to set scope updates nothing and reports success.
  • A reporting script that forgets scope shows an empty report, which looks like "no data this month" rather than "wrong scope".
  • A cleanup routine that forgets scope deletes nothing, and its residue accumulates every run.

Design every script against this. Never treat "the command completed" as evidence.

Scoping tiers, and why they are not interchangeable

Complifly scopes data by several different keys. Choosing the wrong one is a real defect, not a stylistic preference — using an organisation identifier where a company identifier is required silently widens or narrows what a user sees.

Tier Typical use Note
GSTIN Compliance documents, feature flags, most transactional data The most common tier. One legal entity may hold many GSTINs
Organisation Cross-GSTIN administration and audit Derived from the verified token
Company Accounting objects such as the chart of accounts and posting rules Resolved from party master data, not from the token's organisation claim
User Data visible only to its owner Narrowest tier

A privileged cross-tenant bypass exists for genuine platform administration. It is deliberately narrow, and its use should be visible in the audit trail. Any customer-side script should assume it is unavailable.

What this means for your own scripts

Any SQL you write against a Complifly database — reporting, extraction, reconciliation, cleanup — must:

  1. Set session scope explicitly before the statement, using the same identity the application would use.
  2. Cross-check counts against storage metadata, not against another scoped query. Reading sys.partitions (index_id IN (0,1)) reports row counts from storage and is not filtered by Row-Level Security. It is the only check that catches a half-applied change.
  3. Fail loudly on divergence. If the scoped count and the storage count disagree in a way you did not expect, stop. Do not proceed on the assumption that the scoped view is complete.
  4. Reset any elevated scope before the connection returns to the pool, inside error handling so it resets on failure too. A connection returned with elevated scope grants the next unrelated request cross-tenant visibility.

Verify: The exact session-context keys and the recommended pattern for third-party reporting tools are platform implementation details. Request them from Complifly before building an external reporting integration. Recorded as assumption F5 in the Assumptions Register.

Evidencing isolation to an auditor

Isolation is demonstrated, not asserted. The evidence a reviewer will accept:

Evidence How to produce it
Policies exist on tenant tables Query the database's security-policy catalogue and show a policy bound to each tenant-scoped table
Policies are enforced, not merely defined Sign in as a user in tenant A, query a document known to belong to tenant B, and show that it is not returned
Scope is server-derived Show that the tenant scope comes from the verified token, and that changing a client-supplied parameter does not change what is returned
Bypass is controlled Show which identities can bypass scope, and that their use is recorded in the audit trail
New objects inherit the model Show that a policy ships with the table that needs it, rather than in a later pass

Validation

Run this after any installation, migration, restore or upgrade. A green application is not evidence.

Check Method Pass condition
Policies present List security policies in the database One policy for every tenant-scoped table. A newly added table with no policy is a finding, not a to-do
Cross-tenant read blocked Sign in as tenant A; request a document identifier belonging to tenant B Not found. Anything else is a critical defect — stop and escalate
Scope is server-derived Alter a client-supplied identifier in a request and repeat The result does not change
Migration applied fully Compare the migration's reported row count against the sys.partitions count for the same table Equal. Divergence means a half-applied change — do not proceed
No leaked elevation After running any elevated maintenance script, run an ordinary scoped query on the same connection pool Returns only in-scope rows
Cleanup actually cleans After a test run, count the test rows using storage metadata Zero. A non-zero count means the cleanup was scope-blind and silently did nothing

Troubleshooting

Symptom Cause Action
A report is empty for a period known to have data Query ran without scope; RLS filtered everything Set scope, re-run, and cross-check against sys.partitions
A migration reported success but nothing changed Data statements ran without scope elevation, or elevation was set in a different batch from the statement Elevation must be set in the same batch as the statement — a new batch may receive a different pooled connection. Re-run correctly and verify against storage metadata
Test data accumulates run after run Cleanup statements are scope-blind and delete nothing Set scope in the cleanup; verify the count falls to zero
A user sees fewer records than expected Correct isolation, wrong expectation — or the wrong scoping tier for that object Confirm which tier the object uses; company-scoped objects do not follow the token's organisation claim
A user sees records they should not Scope taken from a client-suppliable value, or an over-broad bypass Treat as a security incident. Escalate immediately
After restoring a backup, a tenant sees nothing Scope identifiers differ between environments Reconcile the tenant identifiers; do not disable policies to "make it work"
An external reporting tool returns nothing The tool connects without setting session scope Do not remove the policies. Request the supported reporting pattern from Complifly