# Soft delete

You get a `notes` table where `archive` hides a row instead of deleting it, ordinary reads skip it, and `restore` brings it back. The program below runs that cycle and prints the SQL behind every step. Read it first, then the output, then the notes on each step.

Detail lives on [Traits](/docs/schema/traits#archive) and [Write rows](/docs/queries/write-rows).

## Setup

Three things must exist before the program runs: a Postgres database, the `notes` table, and a client.

The table comes from the schema in the program (`app`). The test that runs this page applies it by rendering the catalog (`app.catalog`) to DDL, the SQL that creates tables, and executing each statement against an empty database. In your own project, create the table with `bunx okm push` on a database you can recreate, or with `okm generate init` then `okm migrate apply` for reviewed migrations ([First migration](/docs/start/first-migration)). For this schema the DDL is:

```sql title="notes.sql"
create table "public"."notes" (
  "archiveId" uuid,
  "archivedAt" timestamptz,
  "id" uuid not null,
  "title" text not null,
  constraint "notes_pkey" primary key ("id")
);

create unique index "notes_title_idx" on "public"."notes" ("title") where ("archivedAt" IS NULL);
```

The client, called `db` throughout, comes from `connect(url, { schema })`. An app keeps one in a module and imports it where it needs it:

```ts
import { connect } from "okmodel/pg/postgresjs";
import { app } from "./archive.ts";

export const db = connect(process.env.DATABASE_URL!, { schema: app });
```

The program opens its own client with `await using`, which closes the pool when the function returns.

## Run it

`archivable()` adds two guarded columns, `archivedAt` and `archiveId`. A guarded column is one your input cannot set. A unique on that table becomes partial: `title` is unique among live rows only, as the `where ("archivedAt" IS NULL)` on `notes_title_idx` in the DDL above shows, so an archived note does not block a new note with the same title.

Every id is generated with `crypto.randomUUID()`, so the function can run again on the same database without a collision. The ids differ on every run, so the printout replaces the note id with `<note>` and any other uuid with `<archive>`. `.sql()` builds the statements a call would send and runs none of them.

```ts title="archive.ts"
import { connect } from "okmodel/pg/postgresjs";
import { id, schema, table, text } from "okmodel/pg";
import { archivable } from "okmodel/traits";
import { OkmError } from "okmodel";

const notes = table(
  "notes",
  { id: id({ default: "none" }), title: text().unique() },
  { traits: [archivable()] },
);
export const app = schema({ tables: [notes] });

const UUID = /[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/g;

export async function retire(url: string): Promise<void> {
  await using db = connect(url, { schema: app });
  const note = crypto.randomUUID();
  const say = (label: string, value: unknown): void => {
    const text = typeof value === "string" ? value : JSON.stringify(value);
    console.log(`${label}: ${text}`.replaceAll(note, "<note>").replace(UUID, "<archive>"));
  };

  await db.notes.insert({ id: note, title: "Keep" });

  const archivePlan = await db.notes.archive({ where: { id: note } }).sql();
  say("archive sql", archivePlan.statements[0]?.text ?? "");
  say("archive params", archivePlan.statements[0]?.params);
  const saved = await db.notes.archive({ where: { id: note } });
  say("archive", saved);

  const find = { where: { id: note }, limit: 1 } as const;
  say("find sql", (await db.notes.find(find).sql()).text);
  say("find", await db.notes.find(find));

  say("withArchived sql", (await db.notes.withArchived().find(find).sql()).text);
  const withArchived = await db.notes.withArchived().find(find);
  say(
    "withArchived",
    withArchived.map((row) => ({ id: row.id, title: row.title, archived: row.archivedAt != null })),
  );

  say("onlyArchived sql", (await db.notes.onlyArchived().find(find).sql()).text);
  const onlyArchived = await db.notes.onlyArchived().find(find);
  say(
    "onlyArchived",
    onlyArchived.map((row) => ({ id: row.id, title: row.title })),
  );

  const restorePlan = await db.notes.restore({ archiveId: saved.archiveId }).sql();
  say("restore sql", restorePlan.statements[0]?.text ?? "");
  say("restore params", restorePlan.statements[0]?.params);
  say("restore", await db.notes.restore({ archiveId: saved.archiveId }));
  say(
    "find after restore",
    (await db.notes.find(find)).map((row) => ({ id: row.id, title: row.title })),
  );

  await db.notes.archive({ where: { id: note } });
  say("archive again", await db.notes.archive({ where: { id: note } }));
  try {
    await db.notes.archive({ where: { id: note } }).expect(1);
  } catch (error) {
    if (error instanceof OkmError) say(error.code, error.message);
  }
}
```

```text title="archive.out"
archive sql: update "notes" as t set "archivedAt" = now(), "archiveId" = $1::uuid where t."archivedAt" is null and t."id" = $2
archive params: ["<archive>","<note>"]
archive: {"count":1,"archiveId":"<archive>"}
find sql: select t."id", t."title", to_char(t."archivedAt" AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'), t."archiveId" from "notes" t where t."archivedAt" is null and t."id" = $1 limit $2
find: []
withArchived sql: select t."id", t."title", to_char(t."archivedAt" AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'), t."archiveId" from "notes" t where t."id" = $1 limit $2
withArchived: [{"id":"<note>","title":"Keep","archived":true}]
onlyArchived sql: select t."id", t."title", to_char(t."archivedAt" AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'), t."archiveId" from "notes" t where t."archivedAt" is not null and t."id" = $1 limit $2
onlyArchived: [{"id":"<note>","title":"Keep"}]
restore sql: update "notes" as t set "archivedAt" = null, "archiveId" = null where t."archivedAt" is not null and t."archiveId" = $1
restore params: ["<archive>"]
restore: {"count":1}
find after restore: [{"id":"<note>","title":"Keep"}]
archive again: {"count":0,"archiveId":"<archive>"}
not_found: archive on notes changed 0 rows. expect was 1.
```

## What each step ran

### 1. Archive

`archive({ where })` is one `update`, not a `delete`. The `where` that you passed becomes `t."id" = $2`, and the call adds `t."archivedAt" is null`, so only a live row can be archived. The new `archiveId` is `$1`. It is a fresh uuid for each operation.

`archive` returns `{ count, archiveId }`. Keep the `archiveId`: it names this one operation, including any child rows cascaded with it (`archivable({ cascade })`). `.sql()` makes its own `archiveId`, so the id in `archive params` is not the one the real call stored. That is why both print as `<archive>`.

### 2. Read the row back

| Call | Set | Filter in the SQL |
| --- | --- | --- |
| `find` / `one` / `page` | Active rows only | `t."archivedAt" is null` |
| `withArchived()` | Active and archived | none |
| `onlyArchived()` | Archived only | `t."archivedAt" is not null` |

The default read adds its filter itself. You write no `archivedAt` condition. `withArchived()` and `onlyArchived()` are the two deliberate ways around that.

### 3. Restore

`restore({ archiveId })` clears `archivedAt` and `archiveId` on every row that carries that id, so a cascaded child comes back with its parent. `restore({ where })` restores those rows and the children that carry their `archiveId`. The result is `{ count }`.

### 4. Archive twice

Archiving a row that is already archived matches nothing, so it returns `{ count: 0 }` and a new, unused `archiveId`. `.expect(1)` turns that into an `OkmError` with code `not_found` that names the table and the count it saw.

## What can go wrong

| Code | When | Fix |
| --- | --- | --- |
| [OKM1052](/docs/reference/errors#okm1052) | `archive` or `restore` on a table that is not archivable. | Add `archivable()` to that table's `traits`. |
| [OKM1061](/docs/reference/errors#okm1061) | `strategy: "table"`. | Use `strategy: "column"`, the default. |
| [OKM1101](/docs/reference/errors#okm1101) | `find` with no `limit`. | Pass `limit`, or set `connect({ limits })`. |
| `not_found` | `.expect(1)` saw another count: `archive on notes changed 0 rows. expect was 1.` The row is missing or already archived. | Read with `withArchived()` to tell the two apart. |

| Next | You use it for |
| --- | --- |
| [Traits](/docs/schema/traits#archive) | `cascade`, partial uniques, and `strategy`. |
| [Write rows](/docs/queries/write-rows) | `archive` beside `update` and `delete`. |

Index: [/llms.txt](/llms.txt)