Multi-Tenancy Rules

CRMBuilder is a multi-tenant platform. Every agency and their clients share the same database, separated by id_company. Getting this wrong is the most common source of data leaks and broken queries.

The golden rule

Every query against a tenant-scoped table must include a WHERE id_company = :id_company clause.

The value comes from the authenticated session JWT — never from user input.

const id_company = session.user.id_company; // always from JWT

Which tables require id_company

Most tables in the CRM are tenant-scoped. The reliable way to check is the Schema Explorer — tables with an id_company column are tenant-scoped and require it on every read and write.

Common scoped tables:

Table	Notes
contacts	Always scope — largest table in the DB
deals	Scope via id_company
companies	The company records table (not to be confused with the tenant)
campaigns_items	Uses customer_id not id_customer — common gotcha
automations	Scope or automations fire across tenants
automation_actions	Scoped via parent automation
smslog	Scope — and note: text column is a BLOB, requires CONVERT(text, CHAR) for string operations
pipeline_stages	Scope by id_company
pb_users	Tenant's team members

Tables that are NOT tenant-scoped

A small number of tables are truly global and must not be filtered by id_company:

Table	Notes
pb_settings	Global settings table — has no id_company column

Attempting to query pb_settings WHERE id_company = X will throw an error. Check the Schema Explorer before adding scoping to any unfamiliar table.

Auth identifier gotcha

pb_users uses login as the authentication identifier — not email.

-- Correct
SELECT * FROM pb_users WHERE login = :login AND id_company = :id_company;

-- Wrong — email column may not exist or may not be the auth field
SELECT * FROM pb_users WHERE email = :email;

The JWT issued on login encodes login, id, and id_company. Always destructure from the token — never re-query for these values using user-supplied input.

Writing new features

When scaffolding any new table or query:

1. Add id_company INT NOT NULL to every new table that stores tenant data
2. Add a DB index on id_company for any table that will grow large
3. On INSERT, pull id_company from session.user.id_company
4. On SELECT/UPDATE/DELETE, always include WHERE id_company = ? as the first condition
5. Never expose raw IDs in URLs without verifying the record belongs to the session's id_company

Common mistakes

Missing scope on JOIN

-- Wrong — joins across tenants
SELECT c.*, d.title
FROM contacts c
JOIN deals d ON d.id_contact = c.id;

-- Correct
SELECT c.*, d.title
FROM contacts c
JOIN deals d ON d.id_contact = c.id AND d.id_company = c.id_company
WHERE c.id_company = :id_company;

Trusting URL params for id_company

// Wrong — user can spoof this
const id_company = req.params.id_company;

// Correct — always from the verified session
const id_company = session.user.id_company;

Querying pb_settings with a tenant scope

-- Wrong — pb_settings has no id_company column
SELECT * FROM pb_settings WHERE id_company = 5;

-- Correct
SELECT * FROM pb_settings;