The lifecycle query helper

A teammate ships a new route. They write db.query.invoices.findMany({ where: eq(invoices.status, 'open') }) through the org-scoped client, so the tenant filter is there and the page works in dev. Two weeks later a customer files a ticket: an invoice they deleted is back, sitting in a report. The query never filtered deletedAt IS NULL, and review missed it because dev had no soft-deleted rows to leak.

This is the org-filter bug again: a missing line that compiles, passes review, and stays invisible. The fix is the same move, supply the filter by construction instead of trusting the author to type it. By the end of this lesson you’ll have a small per-entity helper exposing active(), archived(), and includingDeleted(), stacking the lifecycle filter on top of the org filter. A read that forgets either one becomes a grep-able shape instead of a silent leak.

Composing the lifecycle filter with the org filter

tenantDb(orgId) baked the org filter into the call shape, so an unscoped read is a visibly different client, not a missing where to catch in review. A filter you can forget will be forgotten, so the defense is the call shape, not your diligence.

deleted_at IS NULL is a second such filter: omit it and the query still compiles, still passes review, and still leaks. Reach for the same tool, with two differences. The predicate is isNull(deletedAt), not eq(organizationId, orgId). And the lifecycle has three intents, not one: live rows for a normal list, archived rows for the Archived tab, deleted rows for an admin recovery screen. So the surface is three named methods.

The filters compose. tenantDb(orgId) pins the org; the helper sits on top and pins the lifecycle state. One call produces a single WHERE with both predicates and-ed together, and the caller typed neither.

Your call ordering & paging — not a filter .orderBy(…).limit(20)

Lifecycle layer this helper — scopedInvoices deleted_at IS NULL AND archived_at IS NULL

Tenancy layer tenantDb(orgId) organization_id = :orgId

=

one WHERE the database runs WHERE organization_id = :orgId AND deleted_at IS NULL AND archived_at IS NULL Both predicates supplied by construction — the caller typed neither.

Two filters stack into one WHERE. The lifecycle and tenancy layers each fold in a predicate, the caller adds only ordering, and the three collapse into the single clause the database runs.

The helper’s three methods

Method	Resulting predicate	When you reach for it
active()	deleted_at IS NULL AND archived_at IS NULL	The default. Every everyday list and detail read.
archived()	deleted_at IS NULL AND archived_at IS NOT NULL	The Archived tab. Live rows the user set aside.
includingDeleted()	(no lifecycle predicate; org scope still applies)	The escape hatch: admin recovery, exports, audit views. Never the default.

The first two cover the everyday surface. includingDeleted() is deliberately heavier: it hands back exactly the deleted rows the other two exist to keep out of normal views, so reaching for it is a privileged act, not a convenience.

These three states map one-to-one onto the ?status=active|archived|all filter from the previous chapter, so there is no fourth product state for an onlyDeleted() or a trashed boolean to serve. The escape hatch is named loudly on purpose: grep includingDeleted lists every unscoped lifecycle read, so the name is the audit trail.

The escape hatch is part of the API, not a workaround around it.

Building the scoped query helper

Two passes: the shared filter predicates, then the per-entity helper that uses them.

The shared predicates are tiny SQL-fragment builders that take a table and return a predicate. They live in db/queries/lifecycle.ts, so “active” and “archived” have one definition that flows into the helper methods below and into the hand-written queries you’ll meet in a moment.

src/db/queries/lifecycle.ts

export const activeFilter = (table: LifecycleTable) =>
  and(isNull(table.deletedAt), isNull(table.archivedAt));

export const archivedFilter = (table: LifecycleTable) =>
  and(isNull(table.deletedAt), isNotNull(table.archivedAt));

Now the per-entity helper, in db/queries/invoices.ts: a factory that closes over the org id and exposes three methods. Each returns a chainable Drizzle builder, already filtered by org and lifecycle, ready for the caller to add .orderBy(...), .limit(...), or whatever the page needs.

import 'server-only';
import { and, eq, type SQL } from 'drizzle-orm';
import { db } from '@/db';
import { activeFilter, archivedFilter } from '@/db/queries/lifecycle';
import { invoices } from '@/db/schema';

export const scopedInvoices = (orgId: string) => {
  const inOrg = eq(invoices.organizationId, orgId);
  return {
    active: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, activeFilter(invoices), extra)).$dynamic(),
    archived: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, archivedFilter(invoices), extra)).$dynamic(),
    includingDeleted: (extra?: SQL) =>
      db.select().from(invoices).where(and(inOrg, extra)).$dynamic(),
  };
};

The header. import 'server-only' because this reaches the DB client; activeFilter and archivedFilter come from the shared lifecycle module.

import 'server-only';
import { and, eq, type SQL } from 'drizzle-orm';
import { db } from '@/db';
import { activeFilter, archivedFilter } from '@/db/queries/lifecycle';
import { invoices } from '@/db/schema';

export const scopedInvoices = (orgId: string) => {
  const inOrg = eq(invoices.organizationId, orgId);
  return {
    active: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, activeFilter(invoices), extra)).$dynamic(),
    archived: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, archivedFilter(invoices), extra)).$dynamic(),
    includingDeleted: (extra?: SQL) =>
      db.select().from(invoices).where(and(inOrg, extra)).$dynamic(),
  };
};

The factory closes over orgId and builds the org predicate once. The tenancy chapter’s tenantDb bakes the same eq(organizationId, orgId) into findMany; here we spell it inline because list reads need a chainable builder, which findMany isn’t.

import 'server-only';
import { and, eq, type SQL } from 'drizzle-orm';
import { db } from '@/db';
import { activeFilter, archivedFilter } from '@/db/queries/lifecycle';
import { invoices } from '@/db/schema';

export const scopedInvoices = (orgId: string) => {
  const inOrg = eq(invoices.organizationId, orgId);
  return {
    active: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, activeFilter(invoices), extra)).$dynamic(),
    archived: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, archivedFilter(invoices), extra)).$dynamic(),
    includingDeleted: (extra?: SQL) =>
      db.select().from(invoices).where(and(inOrg, extra)).$dynamic(),
  };
};

active() ands three things into one where: the org predicate, the lifecycle predicate, and the caller’s optional extra. The stacking figure in code.

import 'server-only';
import { and, eq, type SQL } from 'drizzle-orm';
import { db } from '@/db';
import { activeFilter, archivedFilter } from '@/db/queries/lifecycle';
import { invoices } from '@/db/schema';

export const scopedInvoices = (orgId: string) => {
  const inOrg = eq(invoices.organizationId, orgId);
  return {
    active: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, activeFilter(invoices), extra)).$dynamic(),
    archived: (extra?: SQL) =>
      db.select().from(invoices)
        .where(and(inOrg, archivedFilter(invoices), extra)).$dynamic(),
    includingDeleted: (extra?: SQL) =>
      db.select().from(invoices).where(and(inOrg, extra)).$dynamic(),
  };
};

The one load-bearing decision: return a builder, not a finished query. Without .$dynamic(), Drizzle locks .where(), .limit(), and .orderBy() to a single call, so chaining a clause onto a pre-filtered query is a compile-time type error. .$dynamic() lifts that, the documented idiom for a reusable, pre-scoped builder the caller keeps extending.

← Previous 1 / 1 Next →

Two things about this module. The methods take an extra predicate instead of letting the caller add a second .where(), because a second .where() replaces the first rather than merging; passing extra ands it into the single where. And note what’s missing: no onlyDeleted(), no raw passthrough to the unscoped shape. That’s the no-allOrgs-flag rule from the tenancy chapter again, the moment you add a way to drop the filter, you’ve reintroduced the bug.

Before you make this structural, write the filter by hand once, so the helper hides something you understand rather than something you trust.

Return the active invoices for org 1 — not deleted, not archived. You supply both the org filter and the lifecycle filters by hand. This is the read you're about to make structural; feel the filter first.

View schema & seed rows

Schema (Drizzle)

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id').notNull(),
  status: text('status').notNull(),
  deletedAt: timestamp('deleted_at'),
  archivedAt: timestamp('archived_at'),
});

Seed rows (SQL)

INSERT INTO invoices (id, organization_id, status, deleted_at, archived_at) VALUES
  (1, 1, 'open',     NULL,                 NULL),
  (2, 1, 'paid',     NULL,                 NULL),
  (3, 1, 'void',     '2026-05-12 09:00Z',  NULL),
  (4, 1, 'sent',     NULL,                 '2026-05-20 16:00Z'),
  (5, 2, 'open',     NULL,                 NULL),
  (6, 2, 'draft',    NULL,                 NULL);

Run query Feedback Reset

The editor schema uses plain integer ids and snake_case column names to fit the in-browser database, a teaching cut. The production table carries the UUIDv7 ids and shared lifecycleColumns from the previous lesson, but the predicates are identical.

That and(eq(...), isNull(...), isNull(...)) you just typed is exactly what active() produces, except you write it once and never again at a call site. That call site is next.

The call site: review the shape, not each predicate

The canonical list read is three lines that mirror the action opener from the previous lesson: resolve the org user, build the scoped helper, ask for active rows.

const { orgId } = await requireOrgUser();
const scoped = scopedInvoices(orgId);
const rows = await scoped.active().orderBy(desc(invoices.createdAt)).limit(20);

The handler has no isNull(deletedAt) and no eq(organizationId, …). Both filters come from scoped.active(); the caller adds only ordering and a page size. For an extra condition, pass one into the method, scoped.active(eq(invoices.status, 'open')), and the helper ands it in.

Set the wrong shape next to the right one.

Hand-written (leaks)

const rows = await db
  .select()
  .from(invoices)
  .where(eq(invoices.status, 'open'));

Returns this org’s open invoices and last week’s soft-deleted ones. The lifecycle filter is missing, and because it reaches for bare db, so is the org filter.

Through the helper (scoped)

const scoped = scopedInvoices(orgId);
const rows = await scoped.active().orderBy(desc(invoices.createdAt)).limit(20);

Org and lifecycle filters both supplied upstream. The caller adds only ordering and a page size.

You no longer scan the where for a missing isNull. You check one thing: did this read go through the entity helper, or touch bare db or from(invoices) directly? It’s the same mechanical check you run for tenancy, and lifecycle rides the same signal because both filters live in one layer.

Joins and raw SQL: where the helper stops

The helper covers single-table reads cleanly. Two cases need discipline on top of it, and a helper that leaks silently is the exact bug this lesson exists to prevent.

Joins are the trickier case. A query joining invoices to invoice_lines must apply the lifecycle filter to both tables, or you return live invoices stapled to soft-deleted line items. The helper filters only the table it’s built around, so the joined child rows leak through.

You could add join methods like scoped.active().withLines(), but every join shape needs its own method and the helper sprawls. Instead, any join goes through a named function in db/queries/<entity>.ts that applies the shared filter to each joined table, reusing the same exported predicate builders.

src/db/queries/invoices.ts

export const listInvoicesWithLines = (orgId: string) =>
  db.select()
    .from(invoices)
    .innerJoin(invoiceLines, eq(invoiceLines.invoiceId, invoices.id))
    .where(
      and(
        eq(invoices.organizationId, orgId),
        activeFilter(invoices),
        activeFilter(invoiceLines),
      ),
    );

The join now lives in a known place, is reviewed once, and reuses the single definition of “active” instead of ad-hoc chains scattered through route handlers.

Raw and hand-tuned queries are the other carve-out. A reporting query tuned with EXPLAIN ANALYZE may not fit through the helper, and a raw db.execute(sql`…`) bypasses it entirely. A performance escape is fine; escaping the discipline silently is the bug. So these queries live in one location, db/queries/reports/*.ts, each with a comment naming the performance reason and the WHERE clauses that replace the helper’s filters, and each unit-tested for both the lifecycle and tenancy predicates.

One gap stays open that types can’t close. Types make the helper shape easy and the bypass shape obvious, but they can’t stop someone writing db.select().from(invoices) in a fresh file. A lint rule that flags direct db.select().from(<entityTable>) or db.query.<entity> outside the helper module catches that; you build it later in this unit, so for now, name it in review.

The review below is the highest-value exercise in this lesson: a short pull request with three planted bugs, the three shapes this lesson exists to stop. Comment on each line where you’d block the merge.

Review this PR for a teammate. Three issues would leak data in production — block the merge on each. Click any line to leave a review comment, then press Submit review.

src/app/(app)/reports/route.ts

src/app/(app)/reports/route.ts

import { db } from '@/db';
import { invoices } from '@/db/schema';

export async function GET() {
  const open = await db.query.invoices.findMany({
    where: eq(invoices.status, 'open'),
  });
  return Response.json(open);
}

src/app/(app)/invoices/actions.ts

src/app/(app)/invoices/actions.ts

import { db } from '@/db';
import { scopedInvoices } from '@/db/queries/invoices';
import { invoices } from '@/db/schema';
import { requireOrgUser } from '@/auth';

export async function exportAllInvoices() {
  const { orgId } = await requireOrgUser();
  const scoped = scopedInvoices(orgId);
  const rows = await scoped.includingDeleted().orderBy(desc(invoices.createdAt));
  return rows;
}

src/db/queries/invoices.ts

src/db/queries/invoices.ts

export const listInvoicesWithLines = (orgId: string) =>
  db.select()
    .from(invoices)
    .innerJoin(invoiceLines, eq(invoiceLines.invoiceId, invoices.id))
    .where(
      and(
        eq(invoices.organizationId, orgId),
        activeFilter(invoices),
      ),
    );

Submit review

Gate includingDeleted() behind an admin check

includingDeleted() is the helper’s most dangerous surface. The org scope still applies, but deleted and archived rows come back, which is exactly the data the helper exists to keep out of normal views.

So gate every includingDeleted() call at the action or route layer with the authedAction(role, …) wrapper from the roles and RBAC chapter, restricted to the role that owns recovery and admin tooling. The helper makes the data reachable; the wrapper decides who reaches it.

src/app/(app)/invoices/actions.ts

export const restoreInvoice = authedAction(
  'admin',
  restoreInvoiceSchema,
  async ({ id }, { orgId }) => {
    const scoped = scopedInvoices(orgId);
    const [invoice] = await scoped.includingDeleted(eq(invoices.id, id));
    if (!invoice) return err('not-found', 'Invoice not found');
    // clear deletedAt, return ok(...)
  },
);

An ungated includingDeleted() re-introduces, through the back door, the exact leak the helper was built to prevent, which is why the name is loud enough to spot in review.

Note

The audit-log entries around the escape hatch, recording who pulled deleted data and when, come later in the security and audit-log chapter. The gated escape hatch is where they will hang.

Reads and writes are scoped by the same layer

The lifecycle writes from the previous lesson, softDelete, archive, and restore, go through the same scoped client, so tenancy sits on the WHERE of every UPDATE just as it does on every read. A request that forges a different orgId, or targets an already-deleted row, affects zero rows. No gap opens where reads are careful and writes are trusting.

That “zero rows affected” is the seam into the next lesson. Right now it’s a silent success: the row you weren’t allowed to touch stays untouched. Optimistic concurrency adds a third predicate to that same UPDATE WHERE, a version precondition, so two tabs editing one row can’t clobber each other. When the version doesn’t match, zero rows affected becomes an honest 409 the user can act on.

External resources

Dynamic query building

orm.drizzle.team

The official reference for $dynamic() — the load-bearing call behind the chainable, pre-scoped helper you built.

PostgreSQL soft-delete strategies

dev.to

A wider survey of the pattern, including using row-level security so the filter is enforced by the database, not by every query author.