Multi tenancy with foreign keys

Multi tenancy is the practice of serving many independent customers — tenants — from one application and one database, while guaranteeing that no tenant ever sees another’s data. The cheapest place to draw that boundary is a foreign key: every tenant-scoped table carries an org_id (some applications call it tenant_id) pointing at the orgs table, and the rule of the system becomes deceptively simple — every query filters by org_id, and every insert sets it. Get that right everywhere and tenants are isolated; miss it once and data leaks across the boundary.

This guide ports Elixir Ecto’s “Multi tenancy with foreign keys” approach. The running example is a blogging platform where each organization owns its posts. The shape of the data is the easy part; the discipline of never forgetting the scope is the hard part, and it is exactly where lode diverges from Ecto most sharply — so we lead with the data and then spend the bulk of the guide on the scoping discipline.

The migration

Two tables: orgs (the tenants) and posts (tenant-scoped data). Every scoped table gets an org_id column and a foreign key back to orgs(id). Migrations are ordinary values built with the lode/migration module from the lode_sql package (see Getting Started for running them):

import lode/migration
import lode/migration/ddl

pub fn add_orgs_and_posts() -> migration.Migration {
  migration.new(20_260_613_090_000)
  |> migration.create_table("orgs", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("name", ddl.Text) |> ddl.not_null,
  ])
  |> migration.create_table("posts", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("org_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("orgs")),
    ddl.column("title", ddl.Text) |> ddl.not_null,
    ddl.column("body", ddl.Text) |> ddl.not_null,
  ])
  // An index on the scoping column. The index matters: every tenant-scoped
  // query filters on org_id, so it wants to be fast.
  |> migration.create_index("posts", ["org_id"], unique: False)
}

The org_id column is not_null: a post with no organization is a bug, not a state to represent. ddl.references(ddl.reference("orgs")) adds the foreign key that guarantees referential integrity — you cannot point a post at an organization that does not exist — and ON DELETE behaviour is a pipe away with ddl.on_delete(ddl.Cascade) (or Restrict/SetNull).

The schemas

Schemas are plain values (no macros — see DESIGN.md in the repository). The only thing that makes Post tenant-scoped is the org_id field; otherwise it is an ordinary schema:

import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result

pub type Org {
  Org(id: Int, name: String)
}

pub type Post {
  Post(id: Int, org_id: Int, title: String, body: String)
}

pub fn org_schema() -> Schema(Org) {
  schema.new(
    source: "orgs",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "name", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use name <- result.try(schema.require_string(row, "name"))
      Ok(Org(id:, name:))
    },
    dump: fn(o: Org) {
      dict.from_list([#("id", VInt(o.id)), #("name", VString(o.name))])
    },
  )
}

pub fn post_schema() -> Schema(Post) {
  schema.new(
    source: "posts",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "org_id", of: primitive.integer()),
      schema.field(name: "title", of: primitive.string()),
      schema.field(name: "body", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use org_id <- result.try(schema.require_int(row, "org_id"))
      use title <- result.try(schema.require_string(row, "title"))
      use body <- result.try(schema.require_string(row, "body"))
      Ok(Post(id:, org_id:, title:, body:))
    },
    dump: fn(p: Post) {
      dict.from_list([
        #("id", VInt(p.id)),
        #("org_id", VInt(p.org_id)),
        #("title", VString(p.title)),
        #("body", VString(p.body)),
      ])
    },
  )
}

The key difference: scoping is not automatic

Here is where Ecto and lode part ways, and it is the heart of this guide.

Ecto’s foreign-key tenancy hangs entirely on a repo callback. You implement prepare_query/3 to inject where: q.org_id == ^org_id into every query, and default_options(:all) to make the org id flow in implicitly, so that an innocent Repo.all(Post) silently becomes WHERE org_id = $1. The scope is applied by the framework; application code never writes the filter, and that is the whole appeal — you cannot forget what you never had to write.

lode has no such hook. A Repo is a plain value wrapping an adapter (repo.new(adapter)), and there is no prepare_query callback and no default_options mechanism — repo.all/one/get_by/insert/update/delete each take the repo explicitly and run the query exactly as given, with nothing spliced in. (You can confirm this by grepping lode/src/lode/repo.gleam for prepare or default_options; there is nothing to find.) That is an intentional design choice — no global or process-dictionary repo, no implicit query rewriting — but it means tenant scoping must be explicit.

Automatic query scoping (prepare_query). Ecto injects the tenant filter via prepare_query/3 + default_options(:all). repo.with_prepare(r, fn(q) { query.where(q, ...) }) does the same in lode: it returns a repo whose reads and bulk writes run every query through the hook first, so scoping is implicit. Build one prepared repo per tenant (e.g. inside the Tenant boundary below) so the scope can’t be forgotten — implicit and safe. TASK:prepare-query

The rest of this guide builds an explicit pattern that is hard to misuse. It proceeds in two steps: a composable scope helper, then a tenant-aware boundary that makes the helper unskippable.

Step 1: a composable scope helper

The scope is one equality on org_id. Following the query-builder style from Dynamic queries, we write a typed accessor for the column and a one-line helper that pipes a where onto any query:

import lode/query.{type Query}
import lode/query/expr.{type FieldRef, FieldRef}

// A typed accessor for posts.org_id at binding 0. Hand-written here;
// `lode/codegen` emits these from the schema spec (see getting-started.html).
fn post_org_id() -> FieldRef(Post, Int) {
  FieldRef(binding: 0, name: "org_id", lode_type: primitive.integer())
}

fn post_id() -> FieldRef(Post, Int) {
  FieldRef(binding: 0, name: "id", lode_type: primitive.id())
}

/// Scope any query over `posts` to a single organization. This is the explicit
/// analogue of Ecto's prepare_query injection — applied by hand, but composable
/// like every other query combinator.
pub fn for_org(q: Query, org_id: Int) -> Query {
  query.where(q, expr.eq(field: post_org_id(), to: org_id))
}

for_org composes exactly like query.where and friends, because it is a query.where. Any read becomes a scoped read by piping through it:

import lode/repo.{type Repo}
import lode/error.{type LodeError}

pub fn list_posts(r: Repo, org_id: Int) -> Result(List(Post), LodeError) {
  repo.all(
    r,
    post_schema(),
    query.from(source: "posts", alias: "p") |> for_org(org_id),
  )
}

This works, and for_org is the right primitive — but on its own it has the same weakness as writing the where by hand: nothing forces a caller to pipe through it. A new endpoint can call repo.all(r, post_schema(), query.from(...)) with no scope at all, and it will compile, run, and leak every tenant’s posts. Ecto closed that hole with the framework callback. We close it with the type system.

Step 2: a tenant-aware boundary

Make the org id un-droppable by bundling it with the repo into a Tenant value, and route every tenant-scoped operation through functions that take a Tenant rather than a bare Repo. Now “the repo” and “which org” travel together, and the scope helper is applied inside the boundary where callers cannot bypass it:

pub type Tenant {
  Tenant(repo: Repo, org_id: Int)
}

pub fn list_posts(tenant: Tenant) -> Result(List(Post), LodeError) {
  repo.all(
    tenant.repo,
    post_schema(),
    query.from(source: "posts", alias: "p") |> for_org(tenant.org_id),
  )
}

pub fn get_post(tenant: Tenant, id: Int) -> Result(Option(Post), LodeError) {
  repo.one(
    tenant.repo,
    post_schema(),
    query.from(source: "posts", alias: "p")
      |> for_org(tenant.org_id)
      |> query.where(expr.eq(field: post_id(), to: id)),
  )
}

The get_post case is the one to internalize. Looking a row up by primary key feels safe — ids are unique across the table — but repo.get(r, post_schema(), VInt(id)) issues WHERE id = $1 with no org_id clause, so one tenant can fetch another tenant’s post simply by guessing or enumerating its id. Routing through the Tenant boundary forces the for_org clause alongside the id lookup, turning a cross-tenant read into an empty result. This is precisely the class of bug Ecto’s automatic injection prevents; here, the discipline lives in the boundary module instead of the framework.

Writes carry the same obligation in the other direction — every insert must set org_id, and it must set it from the tenant rather than from user input (a request must never be able to choose its own organization). The tenant-aware insert stamps org_id itself:

import lode/changeset

pub fn create_post(
  tenant: Tenant,
  title title: String,
  body body: String,
) -> Result(Post, LodeError) {
  let cs =
    changeset.change(
      Post(id: 0, org_id: 0, title: "", body: ""),
      post_schema(),
    )
    |> changeset.put_change(field.field("title"), VString(title))
    |> changeset.put_change(field.field("body"), VString(body))
    // The scope is stamped from the tenant, never from request params.
    |> changeset.put_change(field.field("org_id"), VInt(tenant.org_id))
  repo.insert(tenant.repo, post_schema(), cs)
}

Note that org_id is set with put_change directly and is deliberately not among the fields a changeset.cast(.., params, permitted) would accept from user input. The placeholder org_id: 0 in the struct is harmless — put_change overwrites it before the insert.

Updates and deletes need scoping too, and the cleanest way to guarantee it is to make them go through a row the tenant has already read. Fetch with get_post (which is scoped), then repo.update/repo.delete the returned struct:

pub fn delete_post(tenant: Tenant, id: Int) -> Result(Nil, LodeError) {
  use found <- result.try(get_post(tenant, id))
  case found {
    Some(post) -> repo.delete(tenant.repo, post_schema(), post)
    None -> Ok(Nil)
  }
}

Because get_post already filtered by org_id, a cross-tenant id returns None and delete_post is a no-op — the delete can only touch a row the tenant was allowed to see. For bulk writes (repo.update_all/delete_all) there is no read to lean on, so apply for_org to the query directly, exactly as the reads do.

What this buys you, and what it costs

The Tenant boundary recovers most of what Ecto’s callback gives, by different means. Ecto guarantees scoping at the framework layer; lode guarantees it at the module boundary and leans on the type system to enforce the route — if the only public functions over posts take a Tenant, then “every query is scoped” becomes a property you can read off the signatures rather than a convention you hope everyone follows. The cost is honesty about where the line is: code that constructs a raw query.from("posts", ...) and hands it to repo.all outside the boundary is still unscoped, so the discipline is to keep the schema and its queries private to the tenant module and expose only the scoped operations. A code review rule (“no bare post_schema() outside tenant.gleam”) substitutes for the compile-time guarantee Ecto gets for free.

A note on constraint errors

If you let org_id come from a changeset and want the foreign key violation to read nicely, declare it with changeset.foreign_key_constraint(cs, "org_id", "posts_org_id_fkey"). A bad org_id then comes back as Error(error.ChangesetInvalid(errors)) with a field error on org_id ("does not exist"), ready for form rendering — Ecto’s {:error, changeset}. A foreign-key violation with no matching declared constraint still surfaces as the raw Error(error.ConstraintError(kind: error.ForeignKey, ..)).

In a well-built tenant boundary this rarely matters: org_id comes from a validated Tenant, not from request params, so a foreign-key violation signals a server-side bug (a stale tenant), not user error.

Closing remarks

Foreign-key tenancy keeps everything in one schema and one connection, paying for that simplicity with eternal vigilance about the scope. Ecto buys back the vigilance with prepare_query; lode buys it back with an explicit for_org helper threaded through a Tenant boundary, trading framework magic for signatures you can audit.

When per-tenant isolation needs to be stronger than a where clause — separate Postgres schemas or databases, so a forgotten filter cannot leak anything — see Multi tenancy with query prefixes. For the full list of differences from Elixir’s Ecto, see Divergences from Ecto.