Valid product, wrong supplier

The shape of the problem

EgyGomla is a B2B sourcing platform I built for Egyptian traders. Two of its tables hold sample requests and private-label requests. Each row carries a supplier and, optionally, a catalogue product.

The foreign key from the request to the product named only catalog_products(id). So the database asked one question: does this product exist? It never asked whether the product belongs to the supplier written on the same row.

That means a row pairing supplier A with supplier B's product satisfied every constraint. The product is real and the supplier is real. The relationship is wrong.

There was a check. The service verified that the product belonged to the supplier, so requests arriving through the API were covered. My own migration comment describes the weakness better than I would paraphrase it: that check protects the path that runs it and no other, "not a repair script, not a future module, not a bulk import".

I want to be exact about the scope here. I did not find corrupted data, and no customer was affected. This was a gap between what the application enforced and what the database enforced, and the cheapest time to close it is before something writes a bad row.

The fix

The rule moves into the key itself. First, the parent table gets a unique constraint over the pair, so the pair can be referenced:

ALTER TABLE "catalog_products"
  ADD CONSTRAINT "catalog_products_id_supplier_organization_id_key"
  UNIQUE ("id", "supplier_organization_id");

This adds no real restriction. id is already the primary key, so any (id, supplier) pair is already unique. The constraint exists only so that something can point at the pair.

Then the child tables swap their single-column key for a composite one:

ALTER TABLE "sample_requests"
  DROP CONSTRAINT "sample_requests_product_id_fkey";
ALTER TABLE "sample_requests"
  ADD CONSTRAINT "sample_requests_product_id_supplier_organization_id_fkey"
  FOREIGN KEY ("product_id", "supplier_organization_id")
  REFERENCES "catalog_products" ("id", "supplier_organization_id")
  ON DELETE RESTRICT ON UPDATE CASCADE;

The same change is applied to private_label_requests. Now the database checks the whole pair against the catalogue. In plain terms: the pair (P42, A) must exist as a row in the products table, and the row says P42 belongs to B, so the insert is rejected.

The pairing becomes a fact the database keeps rather than an invariant the application remembers.

In a LinkedIn post I called the column supplier_id to keep the example simple. The real column is supplier_organization_id, because suppliers are organizations in this schema.

The detail that is easy to get wrong: NULL

A request can name no product at all. In that case product_id is NULL, and the request should be valid.

PostgreSQL foreign keys default to MATCH SIMPLE: if any referencing column is NULL, the constraint is not checked. That is exactly what "this request names no product" should mean, so the default is what I want here. With MATCH FULL, a request with no product would also have to null out its supplier, which is wrong.

I mention it because the same rule cuts the other way. When a column must always be checked, MATCH SIMPLE plus a nullable column means a writer that forgets to fill it in switches the check off without any error. The same trap came up again on a different constraint, and the answer there was to make the column NOT NULL.

Keep the service check

I did not delete the application validation. The database is the backstop and the service is the user interface of the rule.

If the composite key rejects an insert, the driver surfaces a foreign-key error. The repository translates it:

// Simplified from the repository.
if (errorCode(error) === "P2003") {
  throw new DomainValidationError(
    "The linked product is not available for this supplier.",
  );
}

The message is deliberately vague. It does not confirm whether the product exists or who owns it. A buyer probing product ids should not be able to learn another supplier's catalogue from the difference between two error messages.

What this does not do

It would be easy to oversell this, so here are the limits:

It enforces one stored relationship: this product belongs to this supplier. It is not an authorization system and it is not tenant isolation. Something still has to decide who may create a request in the first place.
It does not fix a wrong row by changing the supplier. It rejects the pair.
It cannot see anything outside the columns it covers. A different table with a similar mismatch needs its own key.

How I would test it

A constraint that is never exercised is a comment. The test I want is at the database level, with direct inserts that skip the application entirely:

-- Illustrative test cases, not from the repository.
-- 1. Valid pair: product P42 owned by B, request names (P42, B)  -> accepted
-- 2. Wrong pair: product P42 owned by B, request names (P42, A)  -> rejected
-- 3. No product: request names (NULL, A)                         -> accepted

The three cases cover the intended path, the bug, and the NULL semantics. If all three behave, the rule lives in the schema and a future import script cannot bypass it by forgetting to call the service.

The same pattern, applied wider

The same mechanism shows up again in a later migration that makes a wrong-type tenant unrepresentable. Without it, the database would store a buyer organization as the owner of a catalogue product, or a supplier in the buyer slot of a request. Only whichever query remembered to filter on type would refuse it. The fix has three parts: a denormalised type column on the child row, a composite foreign key that forces it to agree with the organization row, and a CHECK that pins it to the one legal value for that slot.

The schema now has thirteen composite keys on organizations(id, type). A later design decision about marketplace accounts, which I describe in another article, refuses to disturb that network.

Two practical habits came out of that migration:

Count the violators first. Before adding the constraints, the migration counts existing rows that would break them and refuses to continue, naming the table and the number, rather than failing on a bare constraint name or silently changing data. A violation there is a real finding and gets reconciled by hand.
Mind the deploy order. The new columns are NOT NULL with no default, so every writer has to supply them before the migration runs. An older release cannot insert into those tables once the migration is applied.

The costs

Composite keys are not free:

The parent needs a unique constraint over the extra pair, and every referencing table carries a copy of the owning column.
Every writer must supply the extra columns, including seeds, scripts and old app versions.
ON UPDATE CASCADE means a change to the referenced column ripples down. That is intended, and it also means ownership changes need thought.
Error handling needs work, because a raw foreign-key violation is a poor message.

I accept those costs for rules where a silent wrong row is expensive and the data is shared across tenants.

A review you can run on your own schema

Look for tables that store a tenant or owner id next to a resource id. For each one, ask whether the database proves that the resource belongs to that tenant, or only proves that both exist. If it is the second, write down which code paths currently enforce the rule, and which could write around them: imports, repair scripts, admin tools, new services.

What I'd do differently

Add the composite key when the table is created. Retrofitting it took a separate migration that dropped and recreated the key. A new table can have it from the first commit.
Test at the database level from the start. I would write the three insert cases as part of the migration review.
Look for the pattern earlier. The same shape existed in other tables, and a wider migration followed two days later.
Say what the fix covers. It closes one gap. Calling it tenant isolation would be wrong.

Mohamed Elgohary, Mobile & Product Engineer, Cairo. mohamedelgohary.dev

postgresql
data-integrity
foreign-keys
multi-tenancy
prisma
egygomla