Multi-Tenant Analytics: Isolating Sites Properly

Ownership columns do not survive contact

The first version of multi-tenancy is usually a user_id on the sites table. It works until the first person asks to share a site with a colleague, and then every query needs rewriting.

Start with a membership table instead:

CREATE TABLE site_members (
  id         SERIAL PRIMARY KEY,
  site_id    VARCHAR(64) NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  user_id    INTEGER     NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role       VARCHAR(20) NOT NULL DEFAULT 'viewer'
                         CHECK (role IN ('owner','admin','viewer')),
  UNIQUE (site_id, user_id)
);

A single ownership column is just this table with a cardinality restriction you will later regret.

Enforce in middleware

Scattering the check across route handlers guarantees one gets missed. Enforce it once:

router.use(authMiddleware);
router.use('/:siteId', validateSiteId, authorizeSiteAccess);

authorizeSiteAccess resolves the site, checks membership, attaches the role, and rejects otherwise. Every route below inherits it. A new endpoint is protected by default rather than by remembering.

The layers people forget

Two paths routinely bypass the middleware because they do not look like routes.

The AI/tool layer. Tools receive a ctx with a site id. That id must be set by the caller from an authorised session — never taken from model input. A model that can pass an arbitrary site id is a model that can read another tenant's data.

The SQL editor. A valid SELECT against a permitted table still reads every tenant's rows unless the predicate is enforced. Filtered views per session, or predicate injection into the parsed AST — never string concatenation.

Roles worth having

Three cover almost everything: owner (billing, deletion), admin (settings, integrations, invites), viewer (read). Resist adding more until a real request forces it; permission systems grow faster than the need for them.

Compare with an ordering helper rather than equality checks:

roleAtLeast(req.userRole, 'admin')

role === 'admin' quietly excludes owners, which is a bug that surfaces as a confused support ticket.

Deletion has to cascade

ON DELETE CASCADE on every table carrying a site_id. When someone deletes a site, the events, sessions, goals, keywords, integrations and memberships must all go.

Orphaned rows in an analytics store are not just untidy — they show up in aggregates that no longer have a site to belong to, and produce totals nobody can explain.