Enforcing multi-tenancy at the database layer in Ecto

Every multi-tenant app starts with the same quiet assumption: rows belong to a user, and one user must never touch another user's rows. In Ecto, the way you honour that assumption is by scoping every query:

from(c in Category, where: c.user_id == ^user.id)

Do that consistently and you are safe. The problem is the word consistently. Application-layer scoping is a convention held together by every developer remembering to add the clause on every read and — more dangerously — on every write. Forget it once, on one changeset, and the database will happily let user A attach user B's category to their own subscription. Nothing crashes. No error is logged. The corruption is silent, and you find it months later when someone's private category name shows up in the wrong account.

SlashFeed hit exactly this class of risk as its schema grew. The fix was not more discipline — it was moving the guarantee down a layer, into PostgreSQL, where "belongs to the same user" becomes a constraint the database enforces on every write regardless of how the write arrives. This post is the end-to-end version of that work: why query-time scoping is not enough, the composite foreign key mechanism, the two migration patterns you need, the MATCH SIMPLE subtlety for nullable columns, and how to test that the constraint is really in the database and not just in your Elixir.

Two mechanisms, and why we skipped the famous one

The Ecto guide on multi-tenancy with foreign keys describes two complementary mechanisms. It is worth being precise about what each one buys you, because the popular one is not the one that closes the gap.

Mechanism 1 — automatic query scoping. You override Repo.prepare_query/3 and Repo.default_options/1 so that every query automatically gets a WHERE user_id = ? appended unless it explicitly opts out:

def prepare_query(_op, query, opts) do
  cond do
    opts[:skip_user_id] || opts[:ecto_query] in [:schema_migration, :preload] ->
      {query, opts}
    user_id = opts[:user_id] ->
      {Ecto.Query.where(query, user_id: ^user_id), opts}
    true ->
      raise "expected :user_id or :skip_user_id"
  end
end

This is elegant when every table has a user_id. SlashFeed is a hybrid: feeds and entries are deliberately global, shared resources — the "Hacker News" feed exists once and a thousand users subscribe to it through their own user_feeds row. On top of that, feed fetching runs in Oban workers and a Broadway pipeline with no user in scope, and every admin page queries across all users on purpose. Under Mechanism 1, all of those would need skip_user_id: true. You would end up with more opt-outs than opt-ins, which inverts the safety model — now the dangerous path is the one you forget to annotate. So we skipped it.

Crucially, prepare_query only guards reads anyway. It rewrites queries. It does nothing for a plain Repo.insert/2 of a changeset carrying a hand-picked category_id. The silent-corruption scenario is a write, and Mechanism 1 never sees it.

Mechanism 2 — composite foreign keys. This is the one that actually closes the gap, and it guards writes at the source. That is the whole rest of this post.

Why a simple foreign key is not enough

Consider user_feeds (a feed subscription). It has a user_id and a nullable category_id pointing at categories, which also has a user_id. The original migration:

add :category_id,
    references(:categories, type: :binary_id, on_delete: :nilify_all)

That foreign key enforces exactly one thing: the category_id points at a category row that exists. It says nothing about whose category it is. So this insert is, as far as the database is concerned, perfectly valid:

# user_a's subscription, pointed at user_b's category
%UserFeed{}
|> UserFeed.changeset(%{user_id: user_a.id, category_id: category_b.id})
|> Repo.insert()

The application layer normally prevents this by loading the category through get_category!(user, id) first, which scopes by the current user. But that protection lives one abstraction above the database. A future refactor that builds the changeset directly, a bulk import, an admin script, a Repo.insert_all/3 — anything that reaches the database without going through that specific function — bypasses the check entirely. The database offers zero enforcement. The constraint is a convention, not a guarantee.

In SlashFeed's schema this pattern — a per-user table holding a foreign key to another per-user table — occurred in six places: user_feeds.category_id, both foreign keys on user_feed_tags, entry_tags.tag_id, chat_messages.conversation_id, and chat_query_logs.conversation_id. Every one of them was a silent cross-tenant write waiting to happen.

The composite foreign key

A composite foreign key references two columns instead of one. Instead of "category_id must be some category that exists," it says "(category_id, user_id) must be a pair that exists in categories as (id, user_id)." Because a category's id already uniquely determines its user_id, the only way the pair matches is if the category's owner is the same user as the subscription's owner. Cross-user references stop being expressible.

PostgreSQL requires one prerequisite: to reference (id, user_id), that exact tuple must be backed by a unique index on the target table. So step one is a migration that adds those supporting indexes:

defmodule BoringRSS.Repo.Migrations.AddMultiTenancySupportingIndexes do
  use Ecto.Migration

  def change do
    # user_feeds.category_id → categories.(id, user_id)
    create unique_index(:categories, [:id, :user_id], name: :categories_id_user_id_index)

    # user_feed_tags.tag_id and entry_tags.tag_id → tags.(id, user_id)
    create unique_index(:tags, [:id, :user_id], name: :tags_id_user_id_index)

    # user_feed_tags.user_feed_id → user_feeds.(id, user_id)
    create unique_index(:user_feeds, [:id, :user_id], name: :user_feeds_id_user_id_index)

    # chat_messages / chat_query_logs → chat_conversations.(id, user_id)
    create unique_index(:chat_conversations, [:id, :user_id],
             name: :chat_conversations_id_user_id_index
           )
  end
end

These indexes feel redundant — id is already the primary key — but PostgreSQL does not care that id alone is unique; it needs the composite tuple to be provably unique before it will let a foreign key point at it. On a live database, build them concurrently: true so you do not take a write lock on tables that matter.

The references/2 :with option

For a greenfield table, Ecto expresses the composite foreign key directly in the migration DSL through the underdocumented :with option on references/2. Here is the real definition of agentic_feed_feedback, a table added after the indexes existed:

create table(:agentic_feed_feedback, primary_key: false) do
  add :id, :binary_id, primary_key: true

  # Composite FK: pins user_id so a user can only give feedback on their OWN
  # agentic feed. Requires unique_index(:agentic_feeds, [:id, :user_id]).
  add :agentic_feed_id,
      references(:agentic_feeds,
        type: :binary_id,
        with: [user_id: :user_id],
        on_delete: :delete_all
      ),
      null: false

  add :user_id,
      references(:users, type: :binary_id, on_delete: :delete_all),
      null: false

  add :signal, :string, null: false
  timestamps(updated_at: false, type: :utc_datetime)
end

with: [user_id: :user_id] reads as: also constrain my user_id column against the target's user_id column. One line, and it is now impossible to insert a feedback row whose agentic_feed_id and user_id don't belong together — even if the caller knows a valid agentic_feed_id from another account.

That is the clean case. Retrofitting existing tables is where it gets more interesting, because you have to preserve the exact deletion behaviour the old simple foreign key had.

Two migration patterns: CASCADE vs SET NULL

When you replace a simple foreign key on a live table, the ON DELETE behaviour has to survive the swap. SlashFeed's six gaps split into two groups by that behaviour, and each group needs a different approach.

Pattern A — CASCADE cases: drop and replace

When the original foreign key was ON DELETE CASCADE (deleting the parent deletes the child), you can drop the simple constraint and add a composite one with the same cascade. Because a target row belongs to exactly one user, cascading by (id, user_id) is behaviourally identical to cascading by id — you lose nothing. This is entry_tags.tag_id, both keys on user_feed_tags, and chat_messages.conversation_id:

execute "ALTER TABLE entry_tags DROP CONSTRAINT entry_tags_tag_id_fkey"

execute """
ALTER TABLE entry_tags
  ADD CONSTRAINT entry_tags_tag_id_fkey
  FOREIGN KEY (tag_id, user_id)
  REFERENCES tags (id, user_id)
  ON DELETE CASCADE
"""

I dropped to raw execute/1 here rather than the modify DSL. On a retrofit you are doing a precise drop-then-add of a named constraint, and being explicit about the constraint name is what lets the schema map the eventual violation back to a friendly changeset error (more on that below). Keeping the SQL literal also makes the down/0 reversal unambiguous.

Pattern B — SET NULL cases: add alongside

The two nullable columns — user_feeds.category_id and chat_query_logs.conversation_id — use ON DELETE SET NULL: when you delete a category, subscriptions that referenced it keep existing with category_id reset to NULL. You cannot fold that behaviour into a composite key cleanly, so instead of replacing the simple foreign key you add a second, composite one next to it:

execute """
ALTER TABLE user_feeds
  ADD CONSTRAINT user_feeds_category_user_composite_fk
  FOREIGN KEY (category_id, user_id)
  REFERENCES categories (id, user_id)
  MATCH SIMPLE
"""

Now two constraints coexist and divide the labour:

• The original simple foreign key keeps doing ON DELETE SET NULL — delete a category and the column nullifies as before.
• The new composite foreign key validates ownership on every INSERT and UPDATE — you cannot write a (category_id, user_id) pair that doesn't exist together in categories.

The composite one has no ON DELETE clause because it is not responsible for deletion — the simple key already owns that.

The MATCH SIMPLE clause nobody mentions

There is a subtlety hiding in that MATCH SIMPLE keyword, and it is the reason Pattern B works at all. What happens at the exact moment you delete a category? The simple foreign key fires first and sets category_id to NULL. But the composite key is still watching (category_id, user_id) — and now category_id is NULL while user_id is not. Does the half-null pair violate the composite constraint?

With MATCH SIMPLE — which is PostgreSQL's default match type — the answer is no. MATCH SIMPLE skips the foreign key check entirely whenever any column in the key is NULL. So a nullified category_id sails through, and the SET NULL deletion behaviour is preserved. (The stricter MATCH FULL would require all columns to be null or all non-null, which would reject the half-null row and break the cascade — which is precisely why you do not want it here.)

This is the one-line detail most write-ups miss: composite foreign keys on nullable columns only coexist with SET NULL because MATCH SIMPLE treats a partially-null key as "not applicable." It is doing exactly what you want, silently, and you only notice it if you know to look.

Mapping the violation back to a changeset error

A foreign key violation surfaces from PostgreSQL as an error tied to a constraint name. Ecto's foreign_key_constraint/3 translates a named constraint back into a validation error on a field, so your callers get an ordinary {:error, changeset} instead of a raised Postgrex.Error. The trick is that the name you pass must match the name you gave the constraint in the migration:

def changeset(user_feed, attrs) do
  user_feed
  |> cast(attrs, [:user_id, :feed_id, :category_id, :custom_title])
  |> validate_required([:user_id, :feed_id])
  |> unique_constraint([:user_id, :feed_id])
  |> foreign_key_constraint(:user_id)
  |> foreign_key_constraint(:feed_id)
  |> foreign_key_constraint(:category_id)
  # Composite FK: prevents assigning a category that belongs to a different user.
  # Constraint name matches the one added in the composite-FK migration.
  |> foreign_key_constraint(:category_id, name: :user_feeds_category_user_composite_fk)
end

Two foreign_key_constraint calls target :category_id: one for the plain "does this category exist" foreign key, and one — pinned by name: — for the composite ownership foreign key. A cross-user write trips the second one, and the caller receives an error on category_id exactly as if it had failed a validation. The database is the enforcer; the changeset is just the friendly translator.

Prove it lives in the database

The entire point of this exercise is that the guarantee no longer depends on application code. So the test for it has to bypass the application's ownership checks and talk to the database through raw changesets. If the constraint is real, the insert fails even when nothing in Elixir tried to stop it:

describe "Gap 1: user_feeds.category_id composite FK" do
  test "rejects assigning another user's category to a subscription" do
    user_a = user_fixture()
    user_b = user_fixture()
    feed = feed_fixture()
    category_b = category_fixture(user_b)

    {:ok, user_feed} =
      Repo.insert(
        UserFeed.changeset(%UserFeed{}, %{user_id: user_a.id, feed_id: feed.id})
      )

    # Directly attempt the cross-user assignment — no context function,
    # no get_category!(user, id) guard in the way.
    result =
      user_feed
      |> UserFeed.changeset(%{category_id: category_b.id})
      |> Repo.update()

    assert {:error, changeset} = result
    assert errors_on(changeset).category_id != []
  end
end

For the SET NULL tables you want a second test proving the deletion behaviour survived — that MATCH SIMPLE really did preserve nullification:

test "category deletion still nullifies category_id (SET NULL preserved)" do
  user = user_fixture()
  feed = feed_fixture()
  category = category_fixture(user)

  {:ok, user_feed} =
    Repo.insert(
      UserFeed.changeset(%UserFeed{}, %{
        user_id: user.id, feed_id: feed.id, category_id: category.id
      })
    )

  {:ok, _} = Categories.delete_category(category)

  refreshed = Repo.get!(UserFeed, user_feed.id)
  assert is_nil(refreshed.category_id)
end

And for nullable columns, a test that inserting a NULL foreign key is still allowed — the MATCH SIMPLE skip working as designed:

test "accepts a NULL conversation_id (MATCH SIMPLE preserves nullability)" do
  user = ai_user()

  assert {:ok, log} =
           %ChatQueryLog{}
           |> ChatQueryLog.changeset(%{
             user_id: user.id,
             conversation_id: nil,
             question: "standalone question",
             model: "test-model",
             status: "disabled"
           })
           |> Repo.insert()

  assert is_nil(log.conversation_id)
end

The pattern generalises: for every composite foreign key, write a "cross-user insertion should fail" test, plus the accept-your-own and deletion-behaviour tests. SlashFeed carries one describe block per gap. When someone adds a new per-user table next year and forgets the composite key, the missing "should fail" test is the thing that catches it in review.

What this does not solve

Composite foreign keys are precise, and it is worth being clear about their scope so you don't over-apply them.

They only make sense when a per-user table references another per-user table. Tables whose foreign keys point only at users (a root) or at a global table like entries have no cross-tenant reference to prevent — a simple foreign key is correct there, and adding a composite one would be noise. When SlashFeed added five new tables in one sprint, all five went through the checklist and none needed a composite key: their foreign keys pointed at users or at the shared entries table. The checklist is the deliverable, not a reflex to bolt :with onto everything.

They are also not a substitute for application-layer scoping of reads. The database now guarantees no row can reference another tenant's data, but you still write where: c.user_id == ^user.id to decide which rows a request returns. Composite foreign keys protect integrity on write; query scoping protects visibility on read. You want both.

The takeaway

Application-layer tenant scoping is a promise every developer has to keep on every write, forever. Composite foreign keys turn that promise into a database constraint that keeps itself. The cost is modest — a supporting unique index per referenced table, a with: [user_id: :user_id] on new tables or a drop-and-replace / add-alongside migration on old ones, and a foreign_key_constraint line so the violation reads as a normal changeset error. The payoff is that "user A cannot touch user B's data" stops being a thing you hope you remembered and becomes a thing the database will not let you get wrong — no matter how the write arrives.

If you take one detail from this post, make it MATCH SIMPLE: it is the reason a composite foreign key can sit next to a nullable ON DELETE SET NULL column without breaking it, and it is the piece almost nobody explains.