Two timestamps, three actions

A user clicks Delete invoice. Before you reach for DELETE FROM, ask the three questions an experienced engineer asks first: what vanishes from the screen, what survives in the database, and what, if anything, can come back. Now hold a second scene next to it. A project is finished. The user wants it off their active list and out of the way, but not gone, because next quarter they might need to look at it. Is that the same button? The same column? The same promise?

The two scenes look identical in the UI, but they are nothing alike underneath, and confusing them is how teams ship the bugs that surface the day after launch. This lesson turns two facts into code. The first: most “deletes” a SaaS user clicks are writes, not row removals. The second: “archive” and “delete” are two different promises to the user even when they touch the same column. By the end you’ll have a two-timestamp schema and three Server Actions, softDelete, archive, and restore, that drive the lifecycle of every row a user can touch.

Soft delete versus hard delete

Two separate decisions hide inside the word “delete,” and the whole lesson rests on keeping them apart. The first one: when the user removes something, does the row actually leave the table?

Start with the model you already have. A hard delete is the literal thing: DELETE FROM invoices WHERE id = …. The row is gone. Foreign keys either cascade the deletion to their children or block it, and your only path back is restoring from a backup, which in practice means it’s not coming back. You reach for this less often than you’d guess, but it’s not exotic, so keep it in mind.

A soft delete does something quieter. Instead of removing the row, you stamp it: UPDATE invoices SET deleted_at = now() WHERE id = …. The row is still in the table, fully intact, with one new fact attached: the moment it was deleted. Restoring it is just clearing that stamp. The audit trail survives, and nothing referencing the row broke, because nothing was removed.

One sentence carries the rest of the chapter: soft delete is a write, not a delete. It sounds like wordplay, but it has more consequences than anything else here. The instant a “delete” becomes an UPDATE, the row stays visible to anything that doesn’t explicitly hide it, which means every read you’ve ever written is now a place this row can leak back into view. Hold that thought; we’ll come back to the cost it runs up.

The two operations are one keyword apart. The consequences are not.

Hard delete

await db
  .delete(invoices)
  .where(eq(invoices.id, invoiceId));

The row is gone. Recovery is from backup only, which is to say, not really.

Soft delete

await db
  .update(invoices)
  .set({ deletedAt: sql`now()` })
  .where(eq(invoices.id, invoiceId));

The row stays. Every read from now on has to remember to filter it out, and that’s the whole catch.

So when do you reach for each? The trigger is re-reference and consequences, not taste. Soft-delete anything a user might point at again or that carries a compliance obligation: invoices, projects, customers, the comment someone left on a thread. Hard-delete the throwaway rows no one ever re-references: session rows, password-reset tokens, draft autosaves the user never named, expired email-verification records. Treat it as a threshold rather than a rule. The more a record is woven into the rest of the system, the further it tilts toward soft delete.

Make that call a few times now, so it becomes a habit rather than something you have to look up.

Drop each record into the deletion strategy you'd ship it with. Lean on the trigger, not the list: would a user (or an auditor) ever point at this row again? Drag each item into the bucket it belongs to, then press Check.

Soft delete Re-referenced or casts a compliance shadow — keep the row, stamp it

Hard delete Transient artifact no one re-references — drop the row

A paid invoice

A customer record

A user’s comment on a thread

A password-reset token

An expired email-verification row

A draft autosave the user never named

Check Reset

Soft delete versus archive

The second decision is the one that genuinely trips people. It has nothing to do with whether the row stays in the table; that question is already settled. This one is about what you promise the user.

A soft delete is invisible. The user clicks Delete, the row drops out of every list they can see, and from their point of view it’s gone. The only way it ever resurfaces is through an admin, or through a hidden “deleted items” view most products never build. Restoring it is not something the user does in the normal course of their day. It’s a recovery action, a safety net for “we removed that by mistake, can you get it back?”

An archive is explicit. The user clicks Archive, and the row moves somewhere they can still find it, an “Archived” tab they can open whenever they like. Restoring is a first-class thing they do themselves. The whole point of archiving is that the user expects to see it again, and you’d break the contract if they couldn’t.

Here is the part that makes this confusing. The two often use the exact same column shape: deletedAt and archivedAt are both just nullable timestamps. But they are different promises to the user, surfaced through different parts of the UI. Same storage primitive, different product contract. The database can’t tell you which you meant; only the product decision can.

Because the storage is the same, the decision is a product one, and real apps land in one of three shapes. Naming all three turns the course’s pick from a habit into a reasoned choice:

• Archive-only. Every “remove” is really an archive, with no separate user-facing delete at all. This is Notion pages and Linear projects: you archive, and that’s the strongest “remove” the UI offers.
• Soft-delete-only. One “Delete” button, the row disappears, and restoring is admin-only. No archive concept exists in the product’s vocabulary.
• Both. Archive means “I’m done with this”, and soft delete is the recovery net under “I deleted this by mistake.” Two different user intentions, two different surfaces.

This course commits to both, with a specific division of labor: archive is the primary user surface, soft delete is the admin recovery surface. The reasoning is short. It hands users a self-service lifecycle they fully control (archive, browse, restore) while keeping a recovery net underneath that never clutters their view. The user lives in archive; soft delete is the floor that catches mistakes.

Now look at the whole lifecycle as one picture, since it’s the map the rest of this lesson keeps pointing back to.

stateDiagram-v2
  direction LR
  state "Soft-deleted" as Soft
  state "Hard-deleted" as Hard
  [*] --> Active
  Active --> Archived : archive
  Archived --> Active : restore
  Active --> Soft : delete (softDelete)
  Archived --> Soft : delete (softDelete)
  Soft --> Active : restore
  Soft --> Hard : hard delete
  Hard --> [*]
  classDef terminal fill:#fde2e2,stroke:#dc2626,color:#7f1d1d
  class Hard terminal

A row’s state isn’t a column you read; it’s derived from two timestamps (deletedAt, archivedAt), and the three actions are the edges that move it. An archived row can still be deleted, which is the Archived → Soft-deleted path, where delete overrides archive. Hard-deleted is the one exit with no way back.

Read the diagram as the lesson’s title made literal: two timestamps, three actions. A row’s state isn’t a field you look up. It’s derived from whether deletedAt and archivedAt are set. archive, softDelete, and restore are the three edges that move the row between states. Notice the path from Archived straight to Soft-deleted: a row can be archived and then deleted, both timestamps set at once, with deleted winning as the effective state. The states aren’t mutually exclusive flags; they’re a small machine.

One quick check, since collapsing these two ideas back together is the most common mistake on this topic.

A user clicks Archive on a project, then goes looking for it again the next day. Pick the description that matches what actually happened in the database and what the user can do next.

The project’s row never left the table — one timestamp column flipped from null to a time — and it now lives under an Archived tab the user can open and restore from on their own.

The project vanishes from every list, the row is still in the table, but only an admin can bring it back — the user has no way to do it themselves.

The row is gone from the table; getting the project back means restoring a database backup.

Nothing was written — “Archived” is just a label the browser hides the row behind, so the state evaporates on the next request from another device.

The schema: two timestamps in a shared helper

The schema is the mechanical payoff of the decision you just made. You’ve committed to two lifecycle states a user can reach plus an admin recovery state, so the schema needs to encode three positions on the machine, and the cleanest encoding is two nullable timestamp columns.

It’s worth naming the alternative so the choice is reasoned. You could model the lifecycle as a single status enum (active | archived | deleted) and keep separate timestamps for the audit trail. That’s a legitimate shape. But two nullable timestamps win on the things that matter here. The schema is simpler. The column names document themselves: deletedAt is the deletion timestamp, with no enum-to-meaning lookup. The index story is identical either way. And the deciding factor: a row can be archived and deleted at the same time, which an enum forces you to flatten into one value and lose. The senior instinct is to optimize the schema for the queries and UI affordances it has to serve, not for the tidiness of a single column.

You’ll declare these columns on every entity table in the app. Invoices, projects, customers, all of them get the same lifecycle. Repeating three column definitions across a dozen tables is exactly the kind of duplication that drifts out of sync the moment someone edits one copy. So you declare the shape once, in a small lifecycleColumns helper, and spread it into each table. Same convention everywhere, defined in one place.

const lifecycleColumns = {
  deletedAt: timestamp({ withTimezone: true }),
  archivedAt: timestamp({ withTimezone: true }),
  updatedAt: timestamp({ withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
};

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().$defaultFn(() => uuidv7()),
  organizationId: uuid().notNull(),
  amount: numeric({ precision: 12, scale: 2 }).notNull(),
  ...lifecycleColumns,
});

Both are nullable timestamps, and the nullability is the encoding. A null timestamp means “this row is not in that state”; a set timestamp means “it entered that state, at this moment.” No row is removed to express a lifecycle change. Only a column flips from null to a time.

const lifecycleColumns = {
  deletedAt: timestamp({ withTimezone: true }),
  archivedAt: timestamp({ withTimezone: true }),
  updatedAt: timestamp({ withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
};

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().$defaultFn(() => uuidv7()),
  organizationId: uuid().notNull(),
  amount: numeric({ precision: 12, scale: 2 }).notNull(),
  ...lifecycleColumns,
});

updatedAt carries the audit trail: when this row last changed. $onUpdate makes Drizzle stamp the column on every update it issues, so you never hand-set it. The caveat is that $onUpdate is an application-layer value. It fires when drizzle-orm runs the UPDATE, not as a database trigger, so a raw SQL UPDATE that bypasses Drizzle won’t touch it. The new Date() here is the sanctioned exception to the “no Date in domain code” rule: this is the Drizzle storage seam, where Date is the boundary type. A later lesson leans on updatedAt to make concurrent edits honest.

const lifecycleColumns = {
  deletedAt: timestamp({ withTimezone: true }),
  archivedAt: timestamp({ withTimezone: true }),
  updatedAt: timestamp({ withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
};

export const invoices = pgTable('invoices', {
  id: uuid().primaryKey().$defaultFn(() => uuidv7()),
  organizationId: uuid().notNull(),
  amount: numeric({ precision: 12, scale: 2 }).notNull(),
  ...lifecycleColumns,
});

One spread, and invoices has the full lifecycle shape. Every other entity table gets the same line. The shape is declared once and the tables consume it, so you change the lifecycle in one place and every table follows.

← Previous 1 / 1 Next →

A note on naming you’ll see throughout the project’s code. Columns are declared in camelCase (deletedAt) but stored in snake_case (deleted_at). That mapping is configured once on the Drizzle client (casing: 'snake_case'), so you write the TypeScript name and the SQL name follows automatically; you won’t see an explicit 'deleted_at' string on each column. And timestamp({ withTimezone: true }) maps to Postgres timestamptz, which is the only timestamp type you should store an instant in.

Writing the column shape once yourself sticks far better than reading it. The editor below runs a real Postgres in your browser, which can’t use the client-level casing trick, so it spells column names out explicitly and uses a plain integer key. The lifecycle columns are what matter, and they’re identical to the project’s.

Add the three lifecycle columns to invoices: a nullable deleted_at, a nullable archived_at, and a non-null updated_at — each a withTimezone timestamp. deleted_at and archived_at stay nullable (a null means the row isn't in that state); updated_at is .notNull(). This editor has no client-level casing, so spell column names out in snake_case — the lifecycle shape is identical to the project's.

Check schema Feedback Reset

The three actions: softDelete, archive, restore

The schema gives a row its possible states. The three Server Actions are the only sanctioned way to move it between them: the edges of that state machine, made executable.

All three return the same shape every Server Action in the course returns: Result<T>, the { ok: true, data } | { ok: false, error } discriminated union you’ve been using since you first wrote an action. Nothing new there. And each one is tiny:

• softDelete sets deletedAt = now().
• archive sets archivedAt = now().
• restore clears whichever timestamp is set, back to null.

That brevity is the point worth noticing. Each action body is about three lines because two wrappers you already built do the heavy lifting around it. The authedAction(role, schema, fn) wrapper lifts the session lookup, the role check, and the Zod parse out of the body, and hands the body a context whose ctx.db is already tenantDb(orgId), the tenant-scoped client that pins every write to the active organization’s rows. So the body neither parses, nor authorizes, nor re-derives the org. It just reaches for ctx.db and writes. That’s the payoff of having built those wrappers: the bespoke part of a lifecycle action shrinks to one update.

softDelete

export const softDeleteInvoice = authedAction(
  'member',
  z.object({ id: z.uuid() }),
  async (input, ctx) => {
    await ctx.db
      .update(invoices)
      .set({ deletedAt: sql`now()` })
      .where(eq(invoices.id, input.id));
    revalidatePath('/invoices');
    return ok(null);
  },
);

Stamps deletedAt with the current time. The row leaves every user-facing list, and an admin can still recover it.

archive

export const archiveInvoice = authedAction(
  'member',
  z.object({ id: z.uuid() }),
  async (input, ctx) => {
    await ctx.db
      .update(invoices)
      .set({ archivedAt: sql`now()` })
      .where(eq(invoices.id, input.id));
    revalidatePath('/invoices');
    return ok(null);
  },
);

Stamps archivedAt. The row moves to the Archived tab, where the user can find and restore it.

restore

export const restoreInvoice = authedAction(
  'member',
  z.object({ id: z.uuid() }),
  async (input, ctx) => {
    await ctx.db
      .update(invoices)
      .set({ deletedAt: null, archivedAt: null })
      .where(eq(invoices.id, input.id));
    revalidatePath('/invoices');
    return ok(null);
  },
);

Clears both timestamps, returning the row to Active, and returns ok even when nothing actually changed.

The where clause carries only the id predicate, because ctx.db already folds in the org scope, so tenancy is never hand-typed here. (The lifecycle filter on these UPDATEs, only touching non-deleted rows, is the structural concern the next lesson generalizes.) The four shared identifiers, each a concept you already own, are worth a hover:

export const softDeleteInvoice = authedAction(
  'member',
  z.object({ id: z.uuid() }),
  async (input, ctx) => {
    await ctx.db
      .update(invoices)
      .set({ deletedAt: sql`now()` })
      .where(eq(invoices.id, input.id));
    revalidatePath('/invoices');
    return ok(null);
  },
);

Look at what the structure bought you. Every Server Action in the course follows the same five seams: parse, authorize, mutate, revalidate, return. Here the wrappers own parse and authorize, the body owns mutate, and revalidatePath fires the revalidate before the return. The revalidate step matters: skip it and the row you just soft-deleted stays sitting on the user’s screen, because nothing told the cached list to re-fetch. A delete that doesn’t visibly delete is a real bug, and it’s a one-line one.

restore holds the one genuinely non-obvious decision in the trio, so let’s make sure it landed.

A user double-clicks Restore on a row that was already active. The second call runs set({ deletedAt: null, archivedAt: null }) against a row whose timestamps were already null, so the UPDATE touches no real state — and the action still returns ok. Why is ok, not an error, the right return for that second call?

restore is written to be idempotent: it declares a target state — “this row is active” — and that state already holds, so repeating the call is a safe no-op and reporting success is the honest answer.

An error would describe the second call more precisely, but the Result union has no variant that can express “the row was already in the requested state.”

Overwriting a column with the value it already holds still counts as a real change, so the second call isn’t actually a no-op and there’s nothing to special-case.

A Server Action that issues a syntactically valid UPDATE is required by the framework to resolve as ok, regardless of how many rows it changed.

The UI affordances and the visibility filter

The actions are the verbs. Now wire them to what the user actually sees and touches, which is also where the URL-state work from the previous chapter composes back in.

Each lifecycle state gets exactly one affordance, and getting the mapping right is most of the user-facing design:

• Archive is a button on every active row, and, because users archive in batches, a bulk action across selected rows too.
• Restore is a button on rows sitting in the Archived tab. That’s where the user goes looking for “the thing I put away,” so that’s where the way back lives.
• Hard delete is a destructive button that lives on the archive view, behind a confirm dialog, often restricted by role. It is never the default row action. Permanent deletion is the deliberate, confirmed, gone-for-good path, and you have to mean it.

Which list a user is looking at is itself URL state, and it’s exactly the shape you built in the previous chapter. The list filter is tri-state: active is the default (archived and deleted rows both hidden), archived is the Archived tab, and all is an admin view that includes soft-deleted rows. That last one is gated, since a normal user never sees soft-deleted rows by design. This rides directly on the URL-state vocabulary you already have: a ?status=archived parameter read through the same parseAsStringEnum(['active', 'archived', 'all']).withDefault('active') parser you wrote for filters. Nothing new to learn; the mechanics are the previous chapter’s, and you’re just pointing the same tool at the lifecycle.

/invoices?status=archived

Active Archived All

Acme rebrand Archived ⋯

▾ Archive ↺ Restore 🗑 Delete confirm · admin

The tab the user is on is just URL state: ?status=archived is the whole difference between the Active and Archived lists. The actions a row offers depend on the lifecycle state it’s in, and the user sees the label “Archived” with a way back, never the raw deletedAt. Delete is the destructive, confirm-gated path, restricted by role, never the default row action.

A few usability points belong right here, attached to the affordances they qualify rather than collected in a list elsewhere. Archive without a visible Archived tab is a usability bug: you let the user put something away and then gave them no way to find it again. Hard delete without a confirm dialog is destructive UX waiting to ruin someone’s afternoon. And never expose the internal deletedAt to the user as “deleted.” To the user, a row is one of two things: gone from the UI entirely (that’s soft delete) or labeled “Archived” with a way back (that’s archive). It is never a raw timestamp, and never the word “deleted” on a row they can still see. The internal column is your plumbing; the user gets a promise, not the plumbing.

Restore semantics: what comes back

Restore is where students quietly assume something magic happens, so let’s be explicit that nothing does.

When you clear the timestamp, the row returns to Active, and everything it was connected to comes back with it, automatically, because nothing was ever disconnected. Its foreign keys still point where they pointed. Its child rows are still its children. This falls straight out of the sentence from the start of the lesson: soft delete is a write, not a delete, so there was never anything to reconnect. The references survived the whole time.

That said, restore inherits whatever your delete did, and there are two traps worth seeing now.

await ctx.db
  .update(invoices)
  .set({ deletedAt: null, archivedAt: null })
  // line items were never deleted — they survived the soft delete and snap back with the parent
  .where(eq(invoices.id, input.id));

The first trap is cascading restore: restoring an invoice and having its line items come back too. That’s correct only when the delete cascaded in the first place. The rule is symmetry: cascade the restore exactly when you cascaded the soft delete. If deleting the parent stamped the children too, restoring the parent should clear them too. The two operations have to mirror each other, or the data ends up half-restored.

The second trap is the orphan, and it comes in two forms. If your children were hard-deleted when the parent was soft-deleted (a bad pattern, but one that ships in real codebases), then restore simply cannot bring them back. They’re gone, and you’ve restored a parent with missing pieces. That’s the strongest argument for keeping soft-delete cascades together. The subtler form: restore a child whose parent is still soft-deleted, and you’ve produced a row that’s visible in the UI while its parent isn’t, a thing pointing at nothing the user can see. You guard against it by checking the parent’s state before restoring. Surfacing that conflict cleanly, telling the user “you can’t, the parent is gone” instead of silently producing an orphan, is concurrency machinery a later lesson in this chapter builds. For now, just know the hazard is there and has a name.

Soft delete is a write: the cost comes due

Now to pay the cost that sentence ran up. “Soft delete is a write, not a delete” has two structural consequences, and naming them honestly is the bridge into the rest of this chapter, because the fix for each arrives in a later lesson, and the fix only makes sense once you’ve felt the problem.

Consequence one: every read is now a code path that can leak. The soft-deleted row is still in the table, so anything that doesn’t explicitly exclude it will pull it right back in. A count over invoices that forgets deletedAt IS NULL returns a number that’s too high. A join from customers to invoices that forgets it drags deleted rows into the result set. The list, the detail page, the monthly report: each one is a separate place to remember the filter, and each one looks completely correct when you forget. This is the canonical SaaS data-leak failure mode. In a multi-tenant app, the worst version is a query that misses the lifecycle filter and the tenancy filter at once, so a deleted row from one customer shows up to another. The uncomfortable part is that code review barely helps, because the broken query reads exactly like the correct one. The bug is in what isn’t there. The structural fix, making the filtered shape the only shape that compiles so forgetting it isn’t an option, is the next lesson in this chapter. For now, just sit with how easy the mistake is.

Consequence two: ON DELETE CASCADE does not fire on a soft delete. This one catches nearly everyone. You set up a foreign key with onDelete: 'cascade', you ship the soft-delete action, and you assume the children are handled. They are not, because no DELETE happened. You ran an UPDATE. The cascade is wired to an event that never occurs, so it sits there doing nothing while lulling every reviewer who spots it into thinking deletes are covered. The senior move is to make the cascade explicit and atomic: soft-delete the parent and its children in the same transaction, threading tx through both updates. Skip the transaction and an error midway leaves you with a soft-deleted parent and live children, orphans that are visible and half-deleted. That’s not a hypothetical; that’s the bug you write a test for.

Looks handled, isn't

export const invoiceLines = pgTable('invoice_lines', {
  id: uuid().primaryKey().$defaultFn(() => uuidv7()),
  invoiceId: uuid()
    .notNull()
    .references(() => invoices.id, { onDelete: 'cascade' }),
});

// inside the action body — only the parent is touched:
await ctx.db
  .update(invoices)
  .set({ deletedAt: sql`now()` })
  .where(eq(invoices.id, input.id));

The FK cascade is wired to DELETE. The soft delete is an UPDATE. The cascade never fires, so the line items are now orphaned.

Cascade by hand, in one transaction

await db.transaction(async (tx) => {
  await tx
    .update(invoices)
    .set({ deletedAt: sql`now()` })
    .where(and(eq(invoices.id, input.id), eq(invoices.organizationId, ctx.orgId)));
  await tx
    .update(invoiceLines)
    .set({ deletedAt: sql`now()` })
    .where(eq(invoiceLines.invoiceId, input.id));
});

Soft-delete the parent and its children together, in one transaction, all or nothing. This uses the raw db (not ctx.db) and threads tx through both updates, so the org predicate is written out by hand here, where ctx.orgId is the org the wrapper already resolved.

A developer adds onDelete: 'cascade' to the invoice_lines foreign key, ships the softDeleteInvoice action, and reviews the PR confident the children are handled. An invoice is then soft-deleted. What is the actual state of its line items afterward?

They sit there untouched — still in the table, still pointing at a now-deleted parent — because the cascade is bound to a DELETE event that the soft delete’s UPDATE never triggered.

They get their own deletedAt stamped automatically, since the FK cascade carries the parent’s column change down to every child row.

They block the operation: the soft delete fails with a foreign-key violation because live children still reference the parent the developer is trying to remove.

They are removed from the table for good, because a cascade foreign key deletes the children whenever the parent leaves the active set, however that happens.

Uniqueness and the hot path: partial indexes

There’s one more class of bug that soft delete introduces, and it’s the one that separates a toy implementation from a production one. Both fixes are the same Postgres feature, a partial index , applied to two different problems.

The correctness problem: uniqueness breaks after a soft delete. Picture a plain unique constraint on (organizationId, slug) for projects. A user soft-deletes a project named “Acme,” then tries to create a new “Acme,” and the database refuses, because the soft-deleted row still holds that slug. It’s still in the table, so the unique constraint can still see it. From the user’s side this is baffling: they deleted it, so why can’t they reuse the name? The fix is to make the uniqueness rule ignore deleted rows, with a partial unique index that only applies WHERE deleted_at IS NULL. Now uniqueness is enforced among the live rows, exactly where it should be, and the soft-deleted “Acme” is invisible to the constraint.

Two mistakes are easy to make when expressing this in Drizzle, and both fail silently, so read carefully. First, Drizzle’s .unique() shorthand cannot carry a WHERE clause at all, so you have to reach for uniqueIndex().on(...).where(...). Second, and nastier: the predicate inside that .where() must be a raw sql fragment. If you write it with a helper like `eq(t.deletedAt, null)`, Drizzle emits a broken parameterized clause into the generated migration, and your partial index silently doesn't do what you think. Write it as sql${t.deletedAt} is null “ and nothing else. This is the most frequent soft-delete bug after forgetting the read filter, so learn it once and never write it the other way.

The performance problem: the hot read path. The “active rows” query runs constantly. It’s the default list view, fired on basically every page load. A partial composite index on (organization_id, created_at) WHERE deleted_at IS NULL keeps that index small and the scan tight by indexing only the rows the active query ever returns. Why index the deleted rows at all if no hot query ever reads them? The senior reach is to pair a hot composite index with WHERE deleted_at IS NULL whenever the read path always filters deleted rows out. But reach for the default before the conditional: skip the partial when the table is small or the deleted ratio is tiny, because a plain composite index covers both cases without the extra moving part. As with every tenant-scoped index, lead the columns with organizationId.

export const projects = pgTable(
  'projects',
  {
    id: uuid().primaryKey().$defaultFn(() => uuidv7()),
    organizationId: uuid().notNull(),
    slug: text().notNull(),
    createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
    ...lifecycleColumns,
  },
  (t) => [
    uniqueIndex('projects_org_slug_unique')
      .on(t.organizationId, t.slug)
      .where(sql`${t.deletedAt} is null`),
    index('idx_projects_org_created_partial')
      .on(t.organizationId, t.createdAt)
      .where(sql`${t.deletedAt} is null`),
  ],
);

This is the partial unique index. The WHERE deleted_at IS NULL is what lets the user re-create “Acme” after deleting the old one. Uniqueness now applies only among live rows, so the soft-deleted “Acme” is invisible to the constraint.

export const projects = pgTable(
  'projects',
  {
    id: uuid().primaryKey().$defaultFn(() => uuidv7()),
    organizationId: uuid().notNull(),
    slug: text().notNull(),
    createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
    ...lifecycleColumns,
  },
  (t) => [
    uniqueIndex('projects_org_slug_unique')
      .on(t.organizationId, t.slug)
      .where(sql`${t.deletedAt} is null`),
    index('idx_projects_org_created_partial')
      .on(t.organizationId, t.createdAt)
      .where(sql`${t.deletedAt} is null`),
  ],
);

Two things to remember. The .unique() shorthand can’t express a partial index, which is why this is uniqueIndex().where(). And the predicate must be a raw sql “ fragment, because a helper like eq(t.deletedAt, null) emits a broken clause into the migration. This exact line is the correct form.

export const projects = pgTable(
  'projects',
  {
    id: uuid().primaryKey().$defaultFn(() => uuidv7()),
    organizationId: uuid().notNull(),
    slug: text().notNull(),
    createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
    ...lifecycleColumns,
  },
  (t) => [
    uniqueIndex('projects_org_slug_unique')
      .on(t.organizationId, t.slug)
      .where(sql`${t.deletedAt} is null`),
    index('idx_projects_org_created_partial')
      .on(t.organizationId, t.createdAt)
      .where(sql`${t.deletedAt} is null`),
  ],
);

The partial composite index on the hot active-rows path: a small index and a tight scan, because it only covers rows the active query returns. The exception: skip it when the table is small or few rows are ever deleted, since a plain composite covers those without the complexity. Note the naming convention: explicit names, a _partial suffix for partial indexes, and <table>_<cols>_unique for uniques.

← Previous 1 / 1 Next →

This is the one place in the lesson where you can watch the bug and the fix fire against a real database, so it’s worth doing rather than reading.

Add a partial unique index to projects so (organization_id, slug) is unique among live rows only — a soft-deleted slug can be reused, but two live duplicates can't. Add it in the table-extension array at the bottom: uniqueIndex('projects_org_slug_unique').on(t.organizationId, t.slug).where(sql`...`), with the predicate written as a raw sql fragment that reads deleted_at is null. (This editor has no client-level casing, so column names are spelled out in snake_case and the PK is a plain integer — the index shape is identical to the project's.)

Check schema Feedback Reset

Where the deeper concerns live

Two things sit right at the edge of this lesson’s territory. The lesson has to name them, because getting their boundaries wrong is a costly mistake, but it doesn’t own either, and conflating them with soft delete is exactly the junior error to head off.

Caution

Soft delete is not an audit log, and it is not GDPR compliance. Two separate boundaries, both worth getting right.

Every soft-delete, archive, and restore should write a row to an audit log, in the same transaction as the action, but the lifecycle timestamp on the row and the audit log are different things. The row’s state is the current truth; the audit log is the history. Restoring a row clears its deletedAt, but it does not, and must not, erase the deletion from the log. The audit-log table itself comes later in the course; here, just hold the distinction.

And soft delete satisfies no compliance deletion. A GDPR erasure request requires the data actually gone or anonymized beyond recovery, while soft delete leaves it fully intact in the table, which is the opposite of erased. These are two distinct flows: the everyday “Delete” button a user clicks is a soft delete, and a GDPR pipeline is a separate path that nullifies personal data or hard-deletes the row. Same English word, completely different machinery, so never let “we soft-delete” stand in for “we comply.”

The reference shape, end to end

Pull it together into the one shape the rest of this chapter reuses. You haven’t written much bespoke code, which was the point, but the pieces now form a complete lifecycle for the invoice:

• the lifecycleColumns helper, spread into invoices;
• the three actions, softDeleteInvoice, archiveInvoice, and restoreInvoice, each three lines inside the authedAction + tenantDb composition;
• the migration carrying the partial unique index and the partial composite index;
• the Archived tab, filtered through ?status=archived on the URL-state machinery from the previous chapter.

Here’s where each piece lives in the codebase, which also previews where the next lesson’s work will go.

• Directorysrc/

  • Directorydb/

    • schema.ts the lifecycleColumns helper + the entity tables + the partial unique & composite indexes
    • Directoryqueries/

      • invoices.ts next lesson: the home for the tenant-scoped read helpers that make the deletedAt IS NULL filter impossible to forget
  • Directoryapp/

    • Directoryinvoices/

      • actions.ts softDeleteInvoice, archiveInvoice, restoreInvoice
      • page.tsx the list view + the Active | Archived | All tabs, driven by ?status=

The reference shape, grounded in the canonical layout: the schema declares the two timestamps and their indexes, actions.ts holds the three edges, and the list page reads the tab from URL state. The one empty room is db/queries/invoices.ts, which the next lesson fills with the read helpers that make forgetting the lifecycle filter impossible.

That’s the reference shape. The next lesson makes the reads safe, so a hand-written query physically can’t forget the lifecycle filter, and the lesson after that makes concurrent edits honest, so two tabs saving the same invoice can’t silently clobber each other. You’ve laid the foundation both of them build on: a row whose state is two timestamps, moved by three actions.

External resources

The lesson commits to soft delete and archive, but a senior engineer also knows the case against the pattern and the exact Postgres and Drizzle primitives that make it safe.

PostgreSQL — Partial Indexes

postgresql.org

The authoritative reference: enforcing uniqueness on a subset of rows and the WHERE predicate that powers the deleted_at IS NULL index.

Drizzle ORM — Indexes & Constraints

orm.drizzle.team

The exact API the lesson uses — uniqueIndex().on().where() and the raw sql predicate that the .unique() shorthand can't express.

Soft Deletion Probably Isn't Worth It

brandur.org

Brandur's well-known counterpoint: undeletion rarely happens, FKs break, and a deleted_record table may serve you better. Read it to weigh the trade-off yourself.

Postgres FM — Soft delete

postgres.fm

A 38-minute practitioner discussion of soft-delete use cases and implementation options, with a full transcript for skimmers.