The Data Lifecycle of a Record¶

Every record has a lifecycle. Understanding it prevents subtle bugs.

Birth → Life → Death¶

   CREATE              READ / UPDATE (many times)            DELETE
 ┌────────┐          ┌───────────────────────┐          ┌────────┐
 │ insert │ ───────▶ │ exists, may change     │ ───────▶ │ removed │
 └────────┘          └───────────────────────┘          └────────┘

Timestamps Tell the Story¶

Almost every table should carry two timestamps:

Column	Set when
`created_at`	The row is first inserted
`updated_at`	Any time the row changes

sql

2 lines

1created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
2updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

These give you free auditing, sorting ("newest first"), and debugging ("when did this change?").

Soft Delete vs. Hard Delete¶

There are two ways to "delete":

Hard delete — the row is physically removed (DELETE FROM ...).
Soft delete — a deleted_at column is set; the row stays but is hidden from normal queries.

sql

5 lines

1-- Soft delete
2UPDATE tasks SET deleted_at = now() WHERE id = 42;
3
4-- Normal reads must filter
5SELECT * FROM tasks WHERE deleted_at IS NULL;

Soft deletes preserve history and enable "undo" and "trash" features. We cover them in depth in Chapter 6.

State Transitions¶

Beyond existence, records often have a status field with its own lifecycle:

TODO ──▶ IN_PROGRESS ──▶ DONE
  │                        ▲
  └────────── (skip) ──────┘

Modeling valid transitions explicitly — and rejecting invalid ones — is part of writing correct Update logic.