Unique Per Customer, Not Unique Everywhere: Composite Keys

Many database tables need a rule that says "no two rows may have the same value here." A page's address, often called its slug, is a classic example. If two pages both live at "/about," the website does not know which to show.

In a single-customer application, the rule is simple: make the slug column unique. In a platform like ours, where many organizations share the same tables, that simple rule would be a serious problem.

Why a plain unique rule fails

Imagine the page table has a unique rule on the slug column alone. The first church to sign up creates an "about" page. The second church tries to create its own "about" page and gets an error: that address is already taken. By someone they have never heard of.

Worse, workarounds tend to creep in. Developers start appending random numbers to slugs to dodge the conflict, and customers end up with addresses like "/about-2" for no reason they can see.

The composite key

The fix is to make uniqueness relative to the owner. Instead of "slug must be unique," the rule becomes "the combination of account, website and slug must be unique." In database terms, that is a composite unique key: a uniqueness rule across several columns at once.

Now every organization can have its own "/about," "/contact" and "/blog," and the database still guarantees that no single website has two pages at the same address.

We apply the same idea to anything with a human-readable identifier: blog post slugs, category slugs, tag slugs, form names, menu locations and more. It is part of our checklist for every new table, and our standards document calls it out explicitly.

Matching code to the database rule

The database rule is the safety net, but code should rarely hit it. When the application generates a slug from a title, it checks for existing slugs within the same account and website, not across the platform. A check that looked across every customer would add pointless suffixes and, worse, would reveal that a slug exists somewhere else.

Seeders and import scripts follow the same rule. When one of our scripts needs to know whether a page already exists, it asks within the specific account and website it is working on, written out explicitly rather than assumed.

The trash detail that surprised us

Our pages and posts use soft deletion: a deleted item goes to the trash for a while and can be restored. A soft-deleted row is still in the table, which means it still counts toward the unique rule.

That has two consequences we design for. First, if an owner deletes a page called "/services" and then creates a new one with the same address, the application needs to handle the conflict gracefully, either by restoring the old page or by choosing a clear new address. Second, when one of our seeders publishes pages, it checks for trashed pages too, so it does not trip over a row nobody can see in the normal list.

Uniqueness for people works differently

People are the one area where we deliberately share records across accounts, because a person can belong to several organizations. Email addresses for logins are unique across the platform, while memberships connecting people to accounts are unique per account. That is a conscious exception, and it comes with its own rules about listing people safely.

Indexes that serve queries too

A composite unique key is also an index, and most of our queries filter by account and website first. Putting those columns first in the key means the same index that enforces uniqueness also speeds up everyday lookups like "find this account's page at this address." Good schema design often lets one structure do two jobs.

The habit behind the rule

Composite keys are a small example of a big idea: in a shared platform, almost every rule is relative to the customer. Uniqueness, permissions, caches, files and background jobs all need to ask "within whose account?" That question is the heart of how we keep data apart in one database.

Frequently asked questions

What is a composite unique key?

It is a database rule that makes a combination of columns unique rather than a single column. For example, the pair of account and slug can be unique even if the slug alone repeats across accounts.

Why not add numbers to duplicate slugs?

Numbered slugs confuse visitors and hide the real problem. Making slugs unique per account means each organization gets clean addresses.

Do deleted pages still block their address?

In our platform a deleted page stays in the trash for a while, so its address is still reserved until it is permanently removed. The application handles that case when a new page wants the same address.